Caucus 5.0 Schema

Caucus 5.0 Schema
Last revised 3/1/2005
I. Introduction
Caucus 5.0 begins the process of replacing, piece by piece, the "old" flat-file Caucus database with MySQL. Each "piece" means applying these five steps:

Defining the necessary tables (schema).

Writing code in the CML interpreter that automatically creates these tables when needed.

Writing new CML functions that use these tables. Often these are similar in form to the old functions, e.g. the 'new' $user_info(userid intro) vs the 'old' $per_intro(userid).

Rewriting the relevant CML pages to use the new functions, or in many cases to use SQL queries to get and display information.

Writing a "translation" CML script that fetches data for an existing site from the "old" database, and writes it into the "new" database.

The rest of this document concerns itself with step #1, the actual table definitions. More information about the new CML functions can be found at caucuscare.com/TLIB/CMLREF/ref426.html.
II. Table Summaries

User Information.
Information about the user that needs to be accessible by/to people.

user_info - one row per user, one column per user information field. When a manager defines a new "master" user information field, Caucus adds a new column to this table.

user_columns -- one row per user information field. Metadata about the columns in user_info.

user_iface -- one row per field, per interface. For each interface, records which fields show up (are accessible) on which pages, how, and in what order. (This replaces the old way of handling the definition of the new user registration page.)

User "variable" data.
User "variables" that control or modify the actual operation of the software. Typically "sparse" data, in that most users do not have values for "most" variables.

user_data - one row per user per variable. This replaces the old "user variable" concept.

III. Table Details
While Caucus 5.0 is being built with MySQL, in theory we could use it with any relational database via ODBC. For this reason (among others), the precise definition of each table is not shown below. Instead, we simply use "varchar" for short strings, "text" for longer (up to many megabyte) strings, "int" for an integer number, "dec" for a decimal number, "datetime" for a date and time, etc. If you need the precise definition, use your MySQL client to describe a table.

user_info
userid varchar Actual userid (primary key)
active int Is this an 'active' user? 10=yes, 0=deleted, other codes reserved
lname varchar Last name. A user must have a last name, at a minimum, to exist.
... ... (other info fields as defined by the manager)

user_columns
name varchar field name (primary key)
type varchar type of field: string, text, int (number), date (& time), dollars (decimal)
descr varchar short description of field
magic int 0 means normal field, anything else means "magic", i.e. data maintained automatically by Caucus

user_iface
name varchar field name (with 'iface' combines to make a primary key)
iface varchar interface name (e.g. "CC44")
format varchar how an input field should appear: none, text-line, text-box, checkbox, pull-down, or upload
width int width of input field (for text-line or text-box)
choices text list of choices for pull-downs, separated by newlines
required int Is this field required for this interface? 1=yes
position float order of fields for pages in this interface
label text Label that appears next to a field
on_reg int Should this field appear on a registration page?
on_public int Should this field appear on a public "display user" page?
on_self int Should this field appear on a page where the user can modify their own info?
on_mgr int Should this field appear on a page where a manager can see or modify another user's info?
macro varchar Name of macro to be applied to this data when it is displayed

user_data
userid varchar Actual userid (with 'name' combines to form primary key)
name varchar name of user "variable"
value text value of "variable"

user_info
	userid	varchar	Actual userid (primary key)
	active	int	Is this an 'active' user? 10=yes, 0=deleted, other codes reserved
	lname	varchar	Last name. A user must have a last name, at a minimum, to exist.
	...	...	(other info fields as defined by the manager)

user_columns
	name	varchar	field name (primary key)
	type	varchar	type of field: string, text, int (number), date (& time), dollars (decimal)
	descr	varchar	short description of field
	magic	int	0 means normal field, anything else means "magic", i.e. data maintained automatically by Caucus

user_iface
	name	varchar	field name (with 'iface' combines to make a primary key)
	iface	varchar	interface name (e.g. "CC44")
	format	varchar	how an input field should appear: none, text-line, text-box, checkbox, pull-down, or upload
	width	int	width of input field (for text-line or text-box)
	choices	text	list of choices for pull-downs, separated by newlines
	required	int	Is this field required for this interface? 1=yes
	position	float	order of fields for pages in this interface
	label	text	Label that appears next to a field
	on_reg	int	Should this field appear on a registration page?
	on_public	int	Should this field appear on a public "display user" page?
	on_self	int	Should this field appear on a page where the user can modify their own info?
	on_mgr	int	Should this field appear on a page where a manager can see or modify another user's info?
	macro	varchar	Name of macro to be applied to this data when it is displayed

user_data
	userid	varchar	Actual userid (with 'name' combines to form primary key)
	name	varchar	name of user "variable"
	value	text	value of "variable"