/*=================================================================== */ /* @title Course-conference assignments. */ /* @category Courses */ /* @notes Not sure if this is still used. */ /*=================================================================== */ CREATE TABLE assignments ( asnkey int(11) NOT NULL AUTO_INCREMENT, cnum int(11) NOT NULL, instructor varchar(80) DEFAULT NULL, title varchar(80) DEFAULT NULL, start date DEFAULT NULL, end date DEFAULT NULL, due date DEFAULT NULL, text mediumtext, texttype varchar(32) DEFAULT NULL, item int(11) DEFAULT NULL, taskkey int(11) DEFAULT NULL, PRIMARY KEY (asnkey), KEY cnum (cnum) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Link items to their containing conferences */ /* @category Items Conferences */ /* @notes A many-to-many relationship that links items to conferences. Each row declares that */ /* a particular item appears in a particular conference, and specifies the item number sequence */ /* (e.g. 5.1.2) used to label that item in that conference. */ /*

Note that an item that is shared across multiple conferences may be "hidden" or */ /* "retired" in one conference, but not in another. */ /*==================================================================================== */ CREATE TABLE conf_has_items ( cnum int(11) DEFAULT NULL, /* FK {confs} */ items_id int(11) DEFAULT NULL, /* FK {items} */ item0 int(11) DEFAULT '0', /* item number (label), level 0 */ item1 int(11) DEFAULT '0', /* level 1 (x.n) */ item2 int(11) DEFAULT '0', /* level 2 (x.x.n) */ item3 int(11) DEFAULT '0', item4 int(11) DEFAULT '0', item5 int(11) DEFAULT '0', hidden tinyint(4) DEFAULT '0', /* 1=hidden */ retired tinyint(4) DEFAULT '0', /* 1=retired */ items_parent int(11) DEFAULT '0', /* parent item, for breakouts */ resps_parent int(11) DEFAULT '0', /* parent response, for breakouts. (items_parent, resps_parent) is FK to {resps} */ KEY cnum (cnum), KEY items_id (items_id), KEY item0 (item0), KEY item1 (item1), KEY item2 (item2), KEY item3 (item3), KEY item4 (item4), KEY item5 (item5), KEY items_parent (items_parent), KEY resps_parent (resps_parent) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title User wants to hide a conf (not show in conf list) */ /* @category Conferences */ /* @notes Each row marks a user's request to "hide" a conference from their normal */ /* conference list. */ /*==================================================================================== */ CREATE TABLE confhidden ( userid varchar(240) NOT NULL DEFAULT '', cnum varchar(10) NOT NULL DEFAULT '', hidden tinyint(4) DEFAULT '0', /* 1 => hidden */ PRIMARY KEY (userid,cnum) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Configuration */ /* @notes Store general site configuration information.  */ /* Currently the only row is (for example) name=database_version, value=5105. */ /*==================================================================================== */ CREATE TABLE configuration ( name varchar(128) NOT NULL DEFAULT '', value varchar(255) DEFAULT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Conference order override */ /* @category Conferences */ /* @notes Each time a user overrides default (alpha) conference list order, an entry */ /* is made here that overrides the sort order.  */ /* E.g. if conf XYZ is placed between A and B, it gets alpha='AL' or similar. */ /*==================================================================================== */ CREATE TABLE conforder ( userid varchar(240) NOT NULL DEFAULT '', cnum varchar(10) NOT NULL DEFAULT '', alpha varchar(240) DEFAULT NULL, PRIMARY KEY (userid,cnum) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Confs */ /* @category Conferences */ /*==================================================================================== */ CREATE TABLE confs ( cnum varchar(10) NOT NULL DEFAULT '', parent varchar(10) DEFAULT NULL, /* unused for now */ name varchar(240) DEFAULT NULL, private tinyint(4) DEFAULT '0', /* 1 => conf is private, does not appear in conf lists unless user has access. */ lastAccess int(11) NOT NULL DEFAULT '0', /* time of last access by anyone (in seconds since Jan 1 1970) */ PRIMARY KEY (cnum), KEY parent (parent), KEY name (name), KEY lastAccess (lastAccess) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Logged events */ /* @notes Many "events" (such as a user logging in, or reading an item) are logged */ /* for statistical analysis.  /* More detailed description can be found in the Documentation section of caucus.com. */ /*==================================================================================== */ CREATE TABLE events ( event varchar(12) DEFAULT NULL, /* name of event, e.g. 'login', 'read', 'additem', etc. */ userid varchar(80) DEFAULT NULL, sessionid char(20) DEFAULT NULL, /* Unique identifier of a single user session */ time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, duration int(11) DEFAULT NULL, /* Duration in seconds of the event (e.g. 'addresp' duration is time in seconds from */ /* viewing item until clicking "post" */ size int(11) DEFAULT NULL, /* in bytes (e.g. 'addresp' is size of new response) */ conf int(11) DEFAULT NULL, /* conference number */ item int(11) DEFAULT NULL, /* item id number */ r0 int(11) DEFAULT NULL, /* Response number, or first response in a range */ rn int(11) DEFAULT NULL, /* Last response number in a range */ iface varchar(40) DEFAULT NULL, /* Caucus interface name (e.g. "CC51") */ object varchar(80) DEFAULT NULL, /* Object of event, e.g. user creation is userid of new user */ s1 varchar(80) DEFAULT NULL, /* Arbitrary string #1 */ s2 varchar(80) DEFAULT NULL, s3 varchar(80) DEFAULT NULL, n1 int(11) DEFAULT NULL, /* Arbitrary number #1 */ n2 int(11) DEFAULT NULL, n3 int(11) DEFAULT NULL, KEY event (event), KEY userid (userid), KEY sessionid (sessionid), KEY time (time), KEY conf (conf), KEY iface (iface) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Scores on graded tasks */ /* @category Courses */ /* @notes Conferences that are marked as "courses" have gradeable tasks */ /*==================================================================================== */ CREATE TABLE grade_scores ( userid varchar(80) NOT NULL, taskkey int(11) NOT NULL, /* FK to {grade_tasks} */ scorenumb decimal(8,2) DEFAULT NULL, /* Points scored on task */ scoretext varchar(20) DEFAULT NULL, /* Text (e.g. letter grade or other text) -- unused? */ done date DEFAULT NULL, comment varchar(255) DEFAULT NULL, /* Comment by instructor -- unused? */ PRIMARY KEY (userid,taskkey) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*==================================================================================== */ /* @title Graded tasks */ /* @category Courses */ /* @notes Conferences that are marked as "courses" have gradeable tasks */ /*==================================================================================== */ CREATE TABLE grade_tasks ( taskkey int(11) NOT NULL AUTO_INCREMENT, class varchar(80) DEFAULT NULL, cnum int(11) DEFAULT NULL, title varchar(80) DEFAULT NULL, type varchar(20) DEFAULT NULL, /* ?? */ added date DEFAULT NULL, due date DEFAULT NULL, points decimal(8,2) DEFAULT NULL, weight decimal(5,2) DEFAULT '1.00', comment varchar(255) DEFAULT NULL, asnkey int(11) DEFAULT NULL, /* FK to {assignments} */ PRIMARY KEY (taskkey), KEY class (class), KEY cnum (cnum) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title List of rule rule definitions for all groups (individual user defined groups, */ /* conference groups, manager groups, etc.) */ /* @category Users Groups */ /* @notes 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: */ /*

*/ /* 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. */ /*=================================================================== */ CREATE TABLE grouprules ( grkey int(11) NOT NULL AUTO_INCREMENT, owner varchar(240) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, /* Defines type of group: "CONF", "MGR", or an actual (lower-case) userid. */ name varchar(240) DEFAULT NULL, /* Name of group. For CONF groups, it is the conference number. */ userid varchar(240) DEFAULT NULL, /* User groups only: the userid (or wild card string) for this rule. */ wildcard tinyint(1) DEFAULT '0', /* User groups only: 1 if wildcard, 0 otherwise. */ subowner varchar(240) DEFAULT NULL, /* For sub-groups rules: the sub-group's owner. */ subname varchar(240) DEFAULT NULL, /* For sub-groups rules: the sub-group's name. */ access int(11) DEFAULT '0', /* Access level, coded as an integer. (give examples: unknown) */ optional tinyint(1) DEFAULT '0', /* 0 => normal, 1 => this is an "opt (in or out)" rule. */ bySelf tinyint(1) DEFAULT '0', /* 1 if this rule was added by the user that affects it. */ PRIMARY KEY (grkey), KEY owner (owner), KEY name (name), KEY userid (userid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Groups */ /* @category Groups */ /* @notes 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. */ /*=================================================================== */ CREATE TABLE groups ( userid varchar(240) DEFAULT NULL, /* userid affected by this entry */ owner varchar(240) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, /* identifies group (see {grouprules}) */ name varchar(240) DEFAULT NULL, /* identifies group */ access int(11) DEFAULT '0', /* access level */ active tinyint(4) DEFAULT '0', /* 1 => real rule; 0 => rule being rebuilt; 3 => about to become a real rule */ priority int(11) DEFAULT '0', KEY userid (userid), KEY owner (owner), KEY name (name), KEY access (access), KEY active (active) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Items */ /* @category Items */ /* @notes An Item is a single entry in the items table. It is a "container" */ /* for a linear chain (thread) of responses. Each item is independent of any */ /* conferences, but typically appears in one (or more conferences). */ /*

Items are uniquely (internally) identified by items.id, but when an item */ /* appears in a conference, it has a "number" (a label), such as "5", "17.2", */ /* "3.2.6", etc. "Dotted" item numbers refer to "break-out" items in a hierarchy, */ /* i.e. 3.2.6 is the sixth breakout from the second breakout from item number 3. */ /*=================================================================== */ CREATE TABLE items ( id int(11) NOT NULL AUTO_INCREMENT, userid varchar(240) DEFAULT NULL, /* item creator, FK {user_info} */ title varchar(255) DEFAULT NULL, lastresp int(11) DEFAULT '-1', /* response number of highest undeleted response */ frozen tinyint(4) DEFAULT '0', /* 1=frozen */ qkey int(11) DEFAULT NULL, /* For items of type 'quiz', FK {quiz}, else NULL. */ PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Name Cascade */ /* @category Users */ /* @notes Used to break up long lists of user's names (first name, last name, etc.) */ /* into the "chunks" that appear in group assignments.  */ /* Automatically recalculated every so often. */ /*=================================================================== */ CREATE TABLE name_cascade ( type varchar(10) DEFAULT NULL, /* 'name', 'userid', 'fname', 'lname' */ name varchar(240) DEFAULT NULL, /* Actual breakpoint in chunks of names. */ active tinyint(4) DEFAULT NULL, /* 1 */ KEY type (type), KEY name (name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Numbers */ /* @notes Used for stupid purposes in statistical analysis pages (of event data) */ /* when a sequence of integers is needed. */ /*=================================================================== */ CREATE TABLE nums ( num int(11) NOT NULL DEFAULT '0', PRIMARY KEY (num) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Quizzes. Each quiz has a unique key (qkey); each row represents a single quiz. */ /* @category Quizzes */ /* @notes */ /* "Quizzes" are a new feature in Caucus 5.0, intended to round */ /* out the e-Learning side of Caucus discussions.  */ /* The basic use-case model goes like this: */ /*

    */ /*
  1. Quizzes are just another type of item.  */ /* They belong to a conference, and have the same kind of access control */ /* as a regular ("discussion") item. */ /*
  2. Quizzes are created and edited by organizers (or instructors). */ /* */ /*
  3. Quizzes have a list of editable properties, such as title, when the quiz */ /* is available to be taken, etc. */ /* */ /*
  4. Generally it is assumed that the quizzes are meant to be "taken" */ /* by the regular ("include" level) members, although the organizers may */ /* also "take" their own quizzes. */ /* */ /*
  5. Quizzes in turn contain a list of "Problems".  */ /* Each Problem is a single question, with an optional list of */ /* answers.  */ /* A problem may be "in" one or more Quizzes, but every Problem must */ /* appear in at least one Quiz.  */ /* Problems can be moved around, or copied, to different Quizzes. */ /* */ /*
  6. Quiz takers have a record of their "Score(s)", identified by Quiz and */ /* Problem.  */ /* Takers can only see their own Scores, organizers can see all the scores */ /* for all quizzes in the conference. */ /* */ /*
  7. Quizzes do not have discussion, although one can add a "breakout" */ /* discussion item to a quiz item (or vice versa). */ /* */ /*
  8. Quizzes have 4 states, which appear as icons in the "new" column */ /* of the conference home page item list: */ /* */ /*
*/ /*=================================================================== */ CREATE TABLE quiz ( qkey int(11) NOT NULL AUTO_INCREMENT, userid varchar(240) DEFAULT NULL, /* Quiz owner (writer) */ title varchar(240) DEFAULT NULL, /* Description/name of quiz */ visible0 date DEFAULT NULL, /* Starting date when quiz is visible */ visible1 date DEFAULT NULL, /* Ending date when quiz is visible */ onepage int(11) DEFAULT '0', deleted tinyint(4) DEFAULT '0', /* Is quiz deleted? */ seeAnswers int(11) DEFAULT '0', /* May takers see the correct answers after scoring? */ seesummary varchar(20) DEFAULT NULL, /* Who can see the summary? (editors/takers/anyone) */ includeScore tinyint(4) DEFAULT '1', /* Should this quiz's score be included when computing total across all quizzes? */ PRIMARY KEY (qkey), KEY userid (userid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Each problem may have multiple "rows" of answers, e.g. a three-choice multiple-choice problem has 3 rows which define the possible answers. */ /* @category Quizzes */ /*=================================================================== */ CREATE TABLE quizanswers ( akey int(11) NOT NULL AUTO_INCREMENT, pkey int(11) NOT NULL, /* which problem 'owns' these answers */ text1 varchar(240) DEFAULT NULL, /* text before form element before this answer row */ type varchar(20) DEFAULT NULL, /* type of text in text1 */ text2 varchar(240) DEFAULT NULL, /* text after form element */ answer varchar(240) DEFAULT NULL, /* correct answer (if needed) */ sortorder int(11) DEFAULT NULL, /* sorting order for listing problems */ points int(11) DEFAULT NULL, newrow tinyint(4) DEFAULT '1', /* Start a new row when display this answer element? */ PRIMARY KEY (akey), KEY pkey (pkey) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Maps sets of problems to a quiz. This tells us which quizzes "own" or "contain" which problems. Each row relates (connects) one problem to one quiz. */ /* @category Quizzes */ /*=================================================================== */ CREATE TABLE quizmap ( qkey int(11) NOT NULL, pkey int(11) NOT NULL, sortorder int(11) DEFAULT NULL, /* sort order of this problem in the quiz */ score int(11) DEFAULT NULL, /* score for this problem in this quiz (overrides quizproblems.score) */ skippable int(11) DEFAULT NULL, /* Is this problem skippable? */ editable int(11) DEFAULT NULL, /* Can Taker come back and edit their answer? */ KEY qkey (qkey), KEY pkey (pkey) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Each problem has a unique key (pkey); each row represents a single problem. */ /* @category Quizzes */ /*=================================================================== */ CREATE TABLE quizproblems ( pkey int(11) NOT NULL AUTO_INCREMENT, owner varchar(240) DEFAULT NULL, /* userid of original creator */ category varchar(120) DEFAULT NULL, /* General category of problem (arbitrary text) */ title varchar(240) DEFAULT NULL, /* Name or short description of problem. */ text mediumtext, /* Actual text of problem. May include uploaded files, as per Caucus response (text contains macros which link to files) */ texttype varchar(20) DEFAULT NULL, /* type of answer, drives type of form element needed to input answer: choose 1 (radio), choose N (checkboxes), menu (pulldown), number, short text, long text */ totalpoints int(11) DEFAULT NULL, deleted tinyint(4) DEFAULT '0', floating int(11) DEFAULT '0', /* 0=>tabular layout (columns); 1=>"floating" (flowing) */ reference varchar(255) DEFAULT NULL, PRIMARY KEY (pkey), KEY owner (owner), KEY title (title) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Records Takers' scores on problems. One row per Taker/problem/quiz/session combination. (Allows for a single problem to be shared across multiple quizzes, and also for taking a quiz more than once.) . */ /* @category Quizzes */ /*=================================================================== */ CREATE TABLE quizscore ( userid varchar(240) DEFAULT NULL, /* Taker */ qkey int(11) NOT NULL, pkey int(11) NOT NULL, akey int(11) NOT NULL, session int(11) DEFAULT '0', /* ignored for now */ answer mediumtext, /* Taker's answer set */ points int(11) DEFAULT NULL, /* Points scored for this answer */ status int(11) DEFAULT NULL, /* Status of scoring: unscored=1, waiting for human scoring=2, scored=3. */ submitted datetime DEFAULT NULL, /* When the answers were submitted/scored */ KEY userid (userid), KEY qkey (qkey), KEY pkey (pkey), KEY akey (akey) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Responses */ /* @category Responses */ /* @notes Contains the actual responses (aka comments or postings). */ /* Each response belongs to one, and only one, item. The primary key for a response */ /* is the combination of its response number (label), and the id of its containing */ /* item. Thus the first response in each item is always number 0, the second is */ /* number 1, and so forth. If responses are deleted, the response numbers do */ /* not change (shift). */ /*=================================================================== */ CREATE TABLE resps ( rnum int(11) NOT NULL DEFAULT '0', items_id int(11) NOT NULL, userid varchar(240) DEFAULT NULL, /* author FK {user_info} */ text mediumtext, time datetime DEFAULT NULL, /* creation time */ prop int(11) DEFAULT '0', /* old-style text "property", 3=CML, 2=HTML, 1=Literal, 0=word-wrapped */ bits int(11) DEFAULT '0', /* old-style "bits": 32=anonymous, 16=by-organizer(?), ... ? */ copy_rnum int(11) DEFAULT '0', /* original response copied from */ copy_item int(11) DEFAULT '0', /* original item copied from */ copier varchar(240) DEFAULT NULL, /* userid of copier FK {user_info} */ copy_time datetime DEFAULT NULL, /* date/time copied */ copy_show tinyint(4) DEFAULT '0', /* 1=show, 0=don't show "copied" notice */ copy_cnum int(11) DEFAULT '0', /* original conference copied from */ deleted tinyint(4) DEFAULT '0', /* 1=deleted, 0=normal. 'text' is emptied when deleted. */ author_shows_as varchar(255) DEFAULT NULL, /* Author name, overrides any other value. Usually gets set by */ /* email participation. Normally NULL (empty). */ PRIMARY KEY (items_id,rnum), KEY userid (userid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title How much has each user seen of each item? */ /* @category Responses */ /* @notes Tracks which users have seen which (how many) responses on which */ /* items. This acts like a high-water mark, i.e. if resps_seen.seen is 17, */ /* then response numbers 0 through 17 are "seen", and any higher-numbered */ /* responses are "new". */ /*=================================================================== */ CREATE TABLE resps_seen ( userid varchar(240) NOT NULL DEFAULT '', /* FK {user_info} */ items_id int(11) NOT NULL DEFAULT '0', /* FK {items} */ seen int(11) DEFAULT NULL, /* how many responses in the item has the user seen? */ forgot tinyint(4) DEFAULT '0', /* 1 = user has forgotten the entire item */ PRIMARY KEY (userid,items_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title "Variables" about site, conferences, items. */ /* @category Conferences Items Responses */ /* @notes Name/value "variable" pairs for the site, conferences, and items. */ /*=================================================================== */ CREATE TABLE site_data ( cnum int(11) NOT NULL DEFAULT '0', /* 0 = site-wide variables, else conference number */ inum varchar(100) NOT NULL DEFAULT '-', /* '-' = variables specific to conference cnum, else item number in conf. */ name varchar(240) NOT NULL DEFAULT '', value mediumtext, PRIMARY KEY (cnum,inum,name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Task Categories */ /* @category Tasks */ /* @notes */ /*=================================================================== */ CREATE TABLE task_cats ( category int(11) NOT NULL AUTO_INCREMENT, catname varchar(240) DEFAULT NULL, PRIMARY KEY (category), UNIQUE KEY catname (catname) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Task-to-Item links */ /* @category Tasks */ /* @notes Links tasks to specific conf/item/response(s).  */ /* A response may link to multiple tasks; a task may link to */ /* multiple responses. */ /*=================================================================== */ CREATE TABLE task_links ( tid int(11) NOT NULL DEFAULT '0', cnum int(11) NOT NULL DEFAULT '0', inum int(11) NOT NULL DEFAULT '0', rnum int(11) NOT NULL DEFAULT '0', deleted int(11) DEFAULT '0', /* 1=deleted */ PRIMARY KEY (tid,cnum,inum,rnum), KEY tid (tid), KEY cnum (cnum) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Tasks */ /* @category Tasks */ /* @notes 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.) */ /* (This last statement is probably wrong and should be corrected, 9/18/08.) */ /*

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). */ /*=================================================================== */ CREATE TABLE tasks ( tkey int(11) NOT NULL AUTO_INCREMENT, tid int(11) NOT NULL, /* unknown */ title varchar(64) DEFAULT NULL, lead varchar(64) DEFAULT NULL, /* lead person assigned to task. FK {user_info} */ assigner varchar(64) DEFAULT NULL, /* person who assigned this task. FK {user_info} */ towner varchar(240) DEFAULT NULL, tgroup varchar(255) DEFAULT NULL, /* group allowed to see or modify this task */ target date DEFAULT NULL, /* proposed target (due) date of task */ priority int(11) DEFAULT NULL, category int(11) DEFAULT NULL, /* General category that class belongs to */ status varchar(32) DEFAULT NULL, /* Description of status -- open, done, etc. */ updated datetime DEFAULT NULL, /* When was this task last updated or modified? */ current int(11) DEFAULT NULL, /* Is this the most current version of this task? */ deleted int(11) DEFAULT NULL, /* 1=deleted */ text mediumtext, /* Actual (probably HTML) text of task */ texttype varchar(32) DEFAULT NULL, PRIMARY KEY (tkey), KEY tid (tid), KEY title (title), KEY lead (lead), KEY assigner (assigner), KEY towner (towner), KEY tgroup (tgroup), KEY target (target), KEY priority (priority), KEY category (category), KEY status (status), KEY updated (updated), KEY current (current) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Metadata about columns in {user_info} */ /* @category Users */ /* @notes One row per user information field */ /*=================================================================== */ CREATE TABLE user_columns ( name varchar(255) NOT NULL DEFAULT '', /* field name (column in {user_info} */ type varchar(32) DEFAULT NULL, /* type of field: string, text, int (number), date (& time), dollars (decimal) */ descr varchar(255) DEFAULT NULL, /* short description of field */ magic int(11) DEFAULT '0', /* 0 means normal field, anything else means "magic", i.e. data maintained */ /* automatically by Caucus */ PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title User "variables" that control or modify the actual operation of the software. */ /* @category Users */ /* @notes Typically "sparse" data, in that most users do not have values for */ /* "most" variables. Whereas fields that have data for everyone typically are added */ /* as new columns in {user_info}. */ /*=================================================================== */ CREATE TABLE user_data ( userid varchar(255) NOT NULL DEFAULT '', /* Actual userid (with 'name' combines to form primary key) */ /* name. FK {user_info} */ name varchar(240) NOT NULL DEFAULT '', /* name of user "variable" */ value mediumtext, /* value of "variable" */ PRIMARY KEY (userid,name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Which user fields appear in which interface */ /* @category Users Interfaces */ /* @notes 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.) */ /*=================================================================== */ CREATE TABLE user_iface ( name varchar(240) DEFAULT NULL, /* field name (with 'iface' combines to make a primary key) */ iface varchar(64) DEFAULT NULL, /* interface name (e.g. "CC51") */ format varchar(64) DEFAULT NULL, /* how an input field should appear: none, text-line, text-box, checkbox, pull-down, or upload */ width int(11) DEFAULT NULL, /* width of input field (for text-line or text-box) */ choices mediumtext, /* list of choices for pull-downs, separated by newlines */ required int(11) DEFAULT NULL, /* Is this field required for this interface? 1=yes */ position float DEFAULT NULL, /* order of fields for pages in this interface */ label mediumtext, /* Label that appears next to a field */ on_reg int(11) DEFAULT NULL, /* Should this field appear on a registration page? */ on_public int(11) DEFAULT NULL, /* Should this field appear on a public "display user" page? */ on_self int(11) DEFAULT NULL, /* Should this field appear on a page where the user can modify their own info? */ on_mgr int(11) DEFAULT NULL, /* Should this field appear on a page where a manager can see or modify another user's info? */ macro varchar(255) DEFAULT NULL, /* Name of macro to be applied to this data when it is displayed */ KEY name (name), KEY iface (iface) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title When were users in what conferences? */ /* @category Conferences Users */ /* @notes */ /*=================================================================== */ CREATE TABLE user_in_conf ( userid varchar(250) NOT NULL DEFAULT '', /* FK {user_info} */ cnum int(11) NOT NULL DEFAULT '0', /* FK {confs} */ lastin datetime DEFAULT NULL, /* date/time user was "last in" this conference */ PRIMARY KEY (userid,cnum) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*=================================================================== */ /* @title Basic information about users */ /* @category Users */ /* @notes 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. So this list of columns is */ /* by definition not complete. */ /*=================================================================== */ CREATE TABLE user_info ( userid varchar(255) NOT NULL DEFAULT '', active int(11) DEFAULT NULL, /* Is this an 'active' user? 10=yes, 0=deleted, other codes reserved. */ lname varchar(255) DEFAULT NULL, /* Last name. A user must have a last name, at a minimum, to exist. */ fname varchar(255) DEFAULT NULL, prefix varchar(255) DEFAULT NULL, /* e.g. Mr., Dr., etc. */ suffix varchar(255) DEFAULT NULL, /* e.g. "Esq", "III" */ intro mediumtext, /* personal description aka "introduction" */ phone varchar(255) DEFAULT NULL, email varchar(255) DEFAULT NULL, homepage varchar(255) DEFAULT NULL, picture varchar(255) DEFAULT NULL, /* last part of URL to picture */ registered datetime DEFAULT NULL, laston datetime DEFAULT NULL, /* last on Caucus, automatically maintained */ registeredon datetime DEFAULT NULL, /* when registered, automatically maintained */ regface varchar(255) DEFAULT NULL, /* interface used to register user, automatically maintained */ latitude_longitude varchar(255) DEFAULT NULL, /* as determined by calling MaxMind on the IP address, optional */ PRIMARY KEY (userid), KEY active (active), KEY fname (fname), KEY lname (lname), KEY registered (registered), KEY laston (laston) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;