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.

Tasks data.
For the new "Tasks" feature.

Group (access control) data.

grouprules is the list of rule definitions for all groups (individual user defined groups, conference groups, manager groups, etc.)
groups is the full list of each-user-to-each-group mappings, entirely derived (compiled, if you will) from the rule set in grouprules. We use groups to actually test for access to any object controlled by a group, since it's easy to do a JOIN with this table.

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"

tasks
tkey int primary key
tid int ?????
title varchar(64) Task title
lead varchar(64) userid of "lead" person assigned to task
assigner varchar(64) userid of person who assigned this task
tgroup varchar group allowed to see or modify this task
target date Proposed target (due) date of task
priority varchar(32) Priority -- usually numerical or simple alphabetic
category varchar(64) General category that class belongs to
links varchar ??? links to items???
status varchar(32) Description of status -- open, done, etc.
updated timestamp When was this task last updated or modified?
current int Is this the most current version of this task?
deleted int Has task been deleted?
text text Actual (probably HTML) text of task

The Tasks table keeps not only a list of all tasks on the system, but a complete history of every version of every task.
Thus, each task has a unique tid, but each row has a unique primary key, tkey. A single task may have many rows with the same tid and different updateds. Only the most recent row will have current = 1, all others will have a value of 0. (Seems like there ought to be an SQL way to do this by taking the max() of updated, but w/o sub-queries that may not be possible or portable.)
If a task has been deleted, all its rows will have deleted = 1, but the rows are still maintained and are in theory still visible (although certain UI issues, like who is allowed to examine deleted tasks, need to be resolved).
The format of links still needs to be decided on.

grouprules
grkey int primary key
owner varchar Defines type of group: "CONF", "MGR", or an actual (lower-case) userid.
name varchar Name of group. For CONF groups, it is the conference number.
userid varchar User groups only: the userid (or wild card string) for this rule.
wildcard int User groups only: 1 if wildcard, 0 otherwise.
subowner varchar For sub-groups rules: the sub-group's owner.
subname varchar For sub-groups rules: the sub-group's name.
access int Access level, coded as an integer.
optional int 0 => normal, 1 => this is an "opt (in or out)" rule.
bySelf int 1 if this rule was added by the user that affects it.

Every group is identified by an owner, and a name. Userid rules have the userid and wildcard fields, sub-group rules have the subowner and subname fields. All rules must have an access level. An access level of -999 (and no data except for grkey, owner, and name) acts as a placeholder for an otherwise "empty" group.
Only userid rules may have a non-zero "optional" value. The optional rules are tricky, see Groups in Caucus 5 for more explanation. Basically they get used in two ways:

Group owner defines a rule as optional. User does not get access, and is not a member of the group -- but it allows a user to add themself (add a rule) to the group with the identical, non-optional values. This is how "opt-in" is implemented.
Group owner grants someone access in a group. That "someone" may choose to opt-out, effectively by creating an "optional" exclude access rule.
In either case, the rule that the user created (not really created by them, but by a UI in Caucus) is marked as "bySelf". These rules may always be deleted later, in case the user decides to undo their opt-in or opt-out, respectively.
Whenever a (set of) change(s) has been made to grouprules, it is the responsibility of the page to call the CML function $group_update() to force the recompilation of (the relevant portions of) the groups table.

groups
userid varchar userid affected by this entry
owner varchar identifies group (see grouprules above)
name varchar identifies group
access int access level
active int 1 => real rule; 0 => rule being rebuilt; 3 => about to become a real rule

We query groups whenever we need to test access of a specific userid to a specific group, or (in reverse) for the precise membership of a specific group.
Note that all userid wildcard rules from grouprules get expanded into one row in groups per userid that matches the wildcard! Thus for many reasons, changing a group is expensive, but checking access (or membership) is very fast.

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"

tasks
	tkey	int	primary key
	tid	int	?????
	title	varchar(64)	Task title
	lead	varchar(64)	userid of "lead" person assigned to task
	assigner	varchar(64)	userid of person who assigned this task
	tgroup	varchar	group allowed to see or modify this task
	target	date	Proposed target (due) date of task
	priority	varchar(32)	Priority -- usually numerical or simple alphabetic
	category	varchar(64)	General category that class belongs to
	links	varchar	??? links to items???
	status	varchar(32)	Description of status -- open, done, etc.
	updated	timestamp	When was this task last updated or modified?
	current	int	Is this the most current version of this task?
	deleted	int	Has task been deleted?
	text	text	Actual (probably HTML) text of task
	The Tasks table keeps not only a list of all tasks on the system, but a complete history of every version of every task. Thus, each task has a unique tid, but each row has a unique primary key, tkey. A single task may have many rows with the same tid and different updateds. Only the most recent row will have current = 1, all others will have a value of 0. (Seems like there ought to be an SQL way to do this by taking the max() of updated, but w/o sub-queries that may not be possible or portable.) If a task has been deleted, all its rows will have deleted = 1, but the rows are still maintained and are in theory still visible (although certain UI issues, like who is allowed to examine deleted tasks, need to be resolved). The format of links still needs to be decided on.

grouprules
	grkey	int	primary key
	owner	varchar	Defines type of group: "CONF", "MGR", or an actual (lower-case) userid.
	name	varchar	Name of group. For CONF groups, it is the conference number.
	userid	varchar	User groups only: the userid (or wild card string) for this rule.
	wildcard	int	User groups only: 1 if wildcard, 0 otherwise.
	subowner	varchar	For sub-groups rules: the sub-group's owner.
	subname	varchar	For sub-groups rules: the sub-group's name.
	access	int	Access level, coded as an integer.
	optional	int	0 => normal, 1 => this is an "opt (in or out)" rule.
	bySelf	int	1 if this rule was added by the user that affects it.
	Every group is identified by an owner, and a name. Userid rules have the userid and wildcard fields, sub-group rules have the subowner and subname fields. All rules must have an access level. An access level of -999 (and no data except for grkey, owner, and name) acts as a placeholder for an otherwise "empty" group. Only userid rules may have a non-zero "optional" value. The optional rules are tricky, see Groups in Caucus 5 for more explanation. Basically they get used in two ways: Group owner defines a rule as optional. User does not get access, and is not a member of the group -- but it allows a user to add themself (add a rule) to the group with the identical, non-optional values. This is how "opt-in" is implemented. Group owner grants someone access in a group. That "someone" may choose to opt-out, effectively by creating an "optional" exclude access rule. In either case, the rule that the user created (not really created by them, but by a UI in Caucus) is marked as "bySelf". These rules may always be deleted later, in case the user decides to undo their opt-in or opt-out, respectively. Whenever a (set of) change(s) has been made to grouprules, it is the responsibility of the page to call the CML function $group_update() to force the recompilation of (the relevant portions of) the groups table.

groups
	userid	varchar	userid affected by this entry
	owner	varchar	identifies group (see grouprules above)
	name	varchar	identifies group
	access	int	access level
	active	int	1 => real rule; 0 => rule being rebuilt; 3 => about to become a real rule
	We query groups whenever we need to test access of a specific userid to a specific group, or (in reverse) for the precise membership of a specific group. Note that all userid wildcard rules from grouprules get expanded into one row in groups per userid that matches the wildcard! Thus for many reasons, changing a group is expensive, but checking access (or membership) is very fast.