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.  More information about the new CML functions can be found at

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.

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"