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:

  1. Defining the necessary tables (schema).
  2. Writing code in the CML interpreter that automatically creates these tables when needed.
  3. 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).
  4. Rewriting the relevant CML pages to use the new functions, or in many cases to use SQL queries to get and display information.
  5. 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.  See the CML reference guide for the new CML functions.

II. Table Summaries

  1. 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.)

  2. 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.

  3. Tasks data.
    For the new "Tasks" feature.

  4. 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.

    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)
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
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
userid varchar Actual userid (with 'name' combines to form primary key)
name varchar name of user "variable"
value text value of "variable"
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.

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:

  1. 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.
  2. 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.  This function "locks", so only one process can be updating the groups table at a time -- other callers will automatically wait in line.

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.