function qa_db_table_definitions()
{
if (qa_to_override(__FUNCTION__)) {
$args = func_get_args();
return qa_call_override(__FUNCTION__, $args);
}
require_once QA_INCLUDE_DIR . 'db/maxima.php';
require_once QA_INCLUDE_DIR . 'app/users.php';
/*
Important note on character encoding in database and PHP connection to MySQL
[this note is no longer relevant since we *do* explicitly set the connection character set since Q2A 1.5 - see qa-db.php
*/
/*
Other notes on the definitions below
* In MySQL versions prior to 5.0.3, VARCHAR(x) columns will be silently converted to TEXT where x>255
* See box at top of qa-app-recalc.php for a list of redundant (non-normal) information in the database
* Starting in version 1.2, we explicitly name keys and foreign key constraints, instead of allowing MySQL
to name these by default. Our chosen names match the default names that MySQL would have assigned, and
indeed *did* assign for people who installed an earlier version of Q2A. By naming them explicitly, we're
on more solid ground for possible future changes to indexes and foreign keys in the schema.
* There are other foreign key constraints that it would be valid to add, but that would not serve much
purpose in terms of preventing inconsistent data being retrieved, and would just slow down some queries.
* We name some columns here in a not entirely intuitive way. The reason is to match the names of columns in
other tables which are of a similar nature. This will save time and space when combining several SELECT
queries together via a UNION in qa_db_multi_select() - see comments in qa-db.php for more information.
*/
$useridcoltype = qa_db_user_column_type_verify();
$tables = array('users' => array('userid' => $useridcoltype . ' NOT NULL AUTO_INCREMENT', 'created' => 'DATETIME NOT NULL', 'createip' => 'INT UNSIGNED NOT NULL', 'email' => 'VARCHAR(' . QA_DB_MAX_EMAIL_LENGTH . ') NOT NULL', 'handle' => 'VARCHAR(' . QA_DB_MAX_HANDLE_LENGTH . ') NOT NULL', 'avatarblobid' => 'BIGINT UNSIGNED', 'avatarwidth' => 'SMALLINT UNSIGNED', 'avatarheight' => 'SMALLINT UNSIGNED', 'passsalt' => 'BINARY(16)', 'passcheck' => 'BINARY(20)', 'level' => 'TINYINT UNSIGNED NOT NULL', 'loggedin' => 'DATETIME NOT NULL', 'loginip' => 'INT UNSIGNED NOT NULL', 'written' => 'DATETIME', 'writeip' => 'INT UNSIGNED', 'emailcode' => 'CHAR(8) CHARACTER SET ascii NOT NULL DEFAULT \'\'', 'sessioncode' => 'CHAR(8) CHARACTER SET ascii NOT NULL DEFAULT \'\'', 'sessionsource' => 'VARCHAR (16) CHARACTER SET ascii DEFAULT \'\'', 'flags' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'wallposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'PRIMARY KEY (userid)', 'KEY email (email)', 'KEY handle (handle)', 'KEY level (level)', 'kEY created (created, level, flags)'), 'userlogins' => array('userid' => $useridcoltype . ' NOT NULL', 'source' => 'VARCHAR (16) CHARACTER SET ascii NOT NULL', 'identifier' => 'VARBINARY (1024) NOT NULL', 'identifiermd5' => 'BINARY (16) NOT NULL', 'KEY source (source, identifiermd5)', 'KEY userid (userid)'), 'userlevels' => array('userid' => $useridcoltype . ' NOT NULL', 'entitytype' => "CHAR(1) CHARACTER SET ascii NOT NULL", 'entityid' => 'INT UNSIGNED NOT NULL', 'level' => 'TINYINT UNSIGNED', 'UNIQUE userid (userid, entitytype, entityid)', 'KEY entitytype (entitytype, entityid)'), 'userprofile' => array('userid' => $useridcoltype . ' NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_PROFILE_CONTENT_LENGTH . ') NOT NULL', 'UNIQUE userid (userid,title)'), 'userfields' => array('fieldid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT', 'title' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ')', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'flags' => 'TINYINT UNSIGNED NOT NULL', 'permit' => 'TINYINT UNSIGNED', 'PRIMARY KEY (fieldid)'), 'messages' => array('messageid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'type' => "ENUM('PUBLIC', 'PRIVATE') NOT NULL DEFAULT 'PRIVATE'", 'fromuserid' => $useridcoltype . ' NOT NULL', 'touserid' => $useridcoltype . ' NOT NULL', 'fromhidden' => 'TINYINT(1) UNSIGNED NOT NULL DEFAULT 0', 'tohidden' => 'TINYINT(1) UNSIGNED NOT NULL DEFAULT 0', 'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL', 'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL', 'created' => 'DATETIME NOT NULL', 'PRIMARY KEY (messageid)', 'KEY type (type, fromuserid, touserid, created)', 'KEY touserid (touserid, type, created)', 'KEY fromhidden (fromhidden)', 'KEY tohidden (tohidden)'), 'userfavorites' => array('userid' => $useridcoltype . ' NOT NULL', 'entitytype' => "CHAR(1) CHARACTER SET ascii NOT NULL", 'entityid' => 'INT UNSIGNED NOT NULL', 'nouserevents' => 'TINYINT UNSIGNED NOT NULL', 'PRIMARY KEY (userid, entitytype, entityid)', 'KEY userid (userid, nouserevents)', 'KEY entitytype (entitytype, entityid, nouserevents)'), 'usernotices' => array('noticeid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'userid' => $useridcoltype . ' NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL', 'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL', 'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ')', 'created' => 'DATETIME NOT NULL', 'PRIMARY KEY (noticeid)', 'KEY userid (userid, created)'), 'userevents' => array('userid' => $useridcoltype . ' NOT NULL', 'entitytype' => "CHAR(1) CHARACTER SET ascii NOT NULL", 'entityid' => 'INT UNSIGNED NOT NULL', 'questionid' => 'INT UNSIGNED NOT NULL', 'lastpostid' => 'INT UNSIGNED NOT NULL', 'updatetype' => 'CHAR(1) CHARACTER SET ascii', 'lastuserid' => $useridcoltype, 'updated' => 'DATETIME NOT NULL', 'KEY userid (userid, updated)', 'KEY questionid (questionid, userid)'), 'sharedevents' => array('entitytype' => "CHAR(1) CHARACTER SET ascii NOT NULL", 'entityid' => 'INT UNSIGNED NOT NULL', 'questionid' => 'INT UNSIGNED NOT NULL', 'lastpostid' => 'INT UNSIGNED NOT NULL', 'updatetype' => 'CHAR(1) CHARACTER SET ascii', 'lastuserid' => $useridcoltype, 'updated' => 'DATETIME NOT NULL', 'KEY entitytype (entitytype, entityid, updated)', 'KEY questionid (questionid, entitytype, entityid)'), 'cookies' => array('cookieid' => 'BIGINT UNSIGNED NOT NULL', 'created' => 'DATETIME NOT NULL', 'createip' => 'INT UNSIGNED NOT NULL', 'written' => 'DATETIME', 'writeip' => 'INT UNSIGNED', 'PRIMARY KEY (cookieid)'), 'categories' => array('categoryid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'parentid' => 'INT UNSIGNED', 'title' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TITLE_LENGTH . ') NOT NULL', 'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_CAT_CONTENT_LENGTH . ') NOT NULL DEFAULT \'\'', 'qcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'backpath' => 'VARCHAR(' . QA_CATEGORY_DEPTH * (QA_DB_MAX_CAT_PAGE_TAGS_LENGTH + 1) . ') NOT NULL DEFAULT \'\'', 'PRIMARY KEY (categoryid)', 'UNIQUE parentid (parentid, tags)', 'UNIQUE parentid_2 (parentid, position)', 'KEY backpath (backpath(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . '))'), 'pages' => array('pageid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT', 'title' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TITLE_LENGTH . ') NOT NULL', 'nav' => 'CHAR(1) CHARACTER SET ascii NOT NULL', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'flags' => 'TINYINT UNSIGNED NOT NULL', 'permit' => 'TINYINT UNSIGNED', 'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ') NOT NULL', 'heading' => 'VARCHAR(' . QA_DB_MAX_TITLE_LENGTH . ')', 'content' => 'MEDIUMTEXT', 'PRIMARY KEY (pageid)', 'KEY tags (tags)', 'UNIQUE position (position)'), 'widgets' => array('widgetid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT', 'place' => 'CHAR(2) CHARACTER SET ascii NOT NULL', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'tags' => 'VARCHAR(' . QA_DB_MAX_WIDGET_TAGS_LENGTH . ') CHARACTER SET ascii NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_WIDGET_TITLE_LENGTH . ') NOT NULL', 'PRIMARY KEY (widgetid)', 'UNIQUE position (position)'), 'posts' => array('postid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'type' => "ENUM('Q', 'A', 'C', 'Q_HIDDEN', 'A_HIDDEN', 'C_HIDDEN', 'Q_QUEUED', 'A_QUEUED', 'C_QUEUED', 'NOTE') NOT NULL", 'parentid' => 'INT UNSIGNED', 'categoryid' => 'INT UNSIGNED', 'catidpath1' => 'INT UNSIGNED', 'catidpath2' => 'INT UNSIGNED', 'catidpath3' => 'INT UNSIGNED', 'acount' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'amaxvote' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'selchildid' => 'INT UNSIGNED', 'closedbyid' => 'INT UNSIGNED', 'userid' => $useridcoltype, 'cookieid' => 'BIGINT UNSIGNED', 'createip' => 'INT UNSIGNED', 'lastuserid' => $useridcoltype, 'lastip' => 'INT UNSIGNED', 'upvotes' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'downvotes' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'netvotes' => 'SMALLINT NOT NULL DEFAULT 0', 'lastviewip' => 'INT UNSIGNED', 'views' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'hotness' => 'FLOAT', 'flagcount' => 'TINYINT UNSIGNED NOT NULL DEFAULT 0', 'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL DEFAULT \'\'', 'created' => 'DATETIME NOT NULL', 'updated' => 'DATETIME', 'updatetype' => 'CHAR(1) CHARACTER SET ascii', 'title' => 'VARCHAR(' . QA_DB_MAX_TITLE_LENGTH . ')', 'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ')', 'tags' => 'VARCHAR(' . QA_DB_MAX_TAGS_LENGTH . ')', 'name' => 'VARCHAR(' . QA_DB_MAX_NAME_LENGTH . ')', 'notify' => 'VARCHAR(' . QA_DB_MAX_EMAIL_LENGTH . ')', 'PRIMARY KEY (postid)', 'KEY type (type, created)', 'KEY type_2 (type, acount, created)', 'KEY type_4 (type, netvotes, created)', 'KEY type_5 (type, views, created)', 'KEY type_6 (type, hotness)', 'KEY type_7 (type, amaxvote, created)', 'KEY parentid (parentid, type)', 'KEY userid (userid, type, created)', 'KEY selchildid (selchildid, type, created)', 'KEY closedbyid (closedbyid)', 'KEY catidpath1 (catidpath1, type, created)', 'KEY catidpath2 (catidpath2, type, created)', 'KEY catidpath3 (catidpath3, type, created)', 'KEY categoryid (categoryid, type, created)', 'KEY createip (createip, created)', 'KEY updated (updated, type)', 'KEY flagcount (flagcount, created, type)', 'KEY catidpath1_2 (catidpath1, updated, type)', 'KEY catidpath2_2 (catidpath2, updated, type)', 'KEY catidpath3_2 (catidpath3, updated, type)', 'KEY categoryid_2 (categoryid, updated, type)', 'KEY lastuserid (lastuserid, updated, type)', 'KEY lastip (lastip, updated, type)', 'CONSTRAINT ^posts_ibfk_2 FOREIGN KEY (parentid) REFERENCES ^posts(postid)', 'CONSTRAINT ^posts_ibfk_3 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE SET NULL', 'CONSTRAINT ^posts_ibfk_4 FOREIGN KEY (closedbyid) REFERENCES ^posts(postid)'), 'blobs' => array('blobid' => 'BIGINT UNSIGNED NOT NULL', 'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL', 'content' => 'MEDIUMBLOB', 'filename' => 'VARCHAR(' . QA_DB_MAX_BLOB_FILE_NAME_LENGTH . ')', 'userid' => $useridcoltype, 'cookieid' => 'BIGINT UNSIGNED', 'createip' => 'INT UNSIGNED', 'created' => 'DATETIME', 'PRIMARY KEY (blobid)'), 'words' => array('wordid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'word' => 'VARCHAR(' . QA_DB_MAX_WORD_LENGTH . ') NOT NULL', 'titlecount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'contentcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'tagwordcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'tagcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'PRIMARY KEY (wordid)', 'KEY word (word)', 'KEY tagcount (tagcount)'), 'titlewords' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid)', 'CONSTRAINT ^titlewords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^titlewords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'contentwords' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'count' => 'TINYINT UNSIGNED NOT NULL', 'type' => "ENUM('Q', 'A', 'C', 'NOTE') NOT NULL", 'questionid' => 'INT UNSIGNED NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid)', 'CONSTRAINT ^contentwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^contentwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'tagwords' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid)', 'CONSTRAINT ^tagwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^tagwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'posttags' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'postcreated' => 'DATETIME NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid,postcreated)', 'CONSTRAINT ^posttags_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^posttags_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'uservotes' => array('postid' => 'INT UNSIGNED NOT NULL', 'userid' => $useridcoltype . ' NOT NULL', 'vote' => 'TINYINT NOT NULL', 'flag' => 'TINYINT NOT NULL', 'UNIQUE userid (userid, postid)', 'KEY postid (postid)', 'CONSTRAINT ^uservotes_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE'), 'userpoints' => array('userid' => $useridcoltype . ' NOT NULL', 'points' => 'INT NOT NULL DEFAULT 0', 'qposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'cposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aselects' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aselecteds' => 'MEDIUMINT NOT NULL DEFAULT 0', 'qupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'qdownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'adownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'qvoteds' => 'INT NOT NULL DEFAULT 0', 'avoteds' => 'INT NOT NULL DEFAULT 0', 'upvoteds' => 'INT NOT NULL DEFAULT 0', 'downvoteds' => 'INT NOT NULL DEFAULT 0', 'bonus' => 'INT NOT NULL DEFAULT 0', 'PRIMARY KEY (userid)', 'KEY points (points)'), 'userlimits' => array('userid' => $useridcoltype . ' NOT NULL', 'action' => 'CHAR(1) CHARACTER SET ascii NOT NULL', 'period' => 'INT UNSIGNED NOT NULL', 'count' => 'SMALLINT UNSIGNED NOT NULL', 'UNIQUE userid (userid, action)'), 'iplimits' => array('ip' => 'INT UNSIGNED NOT NULL', 'action' => 'CHAR(1) CHARACTER SET ascii NOT NULL', 'period' => 'INT UNSIGNED NOT NULL', 'count' => 'SMALLINT UNSIGNED NOT NULL', 'UNIQUE ip (ip, action)'), 'options' => array('title' => 'VARCHAR(' . QA_DB_MAX_OPTION_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL', 'PRIMARY KEY (title)'), 'cache' => array('type' => 'CHAR(8) CHARACTER SET ascii NOT NULL', 'cacheid' => 'BIGINT UNSIGNED DEFAULT 0', 'content' => 'MEDIUMBLOB NOT NULL', 'created' => 'DATETIME NOT NULL', 'lastread' => 'DATETIME NOT NULL', 'PRIMARY KEY (type,cacheid)', 'KEY (lastread)'), 'usermetas' => array('userid' => $useridcoltype . ' NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL', 'PRIMARY KEY (userid, title)'), 'postmetas' => array('postid' => 'INT UNSIGNED NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL', 'PRIMARY KEY (postid, title)', 'CONSTRAINT ^postmetas_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE'), 'categorymetas' => array('categoryid' => 'INT UNSIGNED NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL', 'PRIMARY KEY (categoryid, title)', 'CONSTRAINT ^categorymetas_ibfk_1 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE CASCADE'), 'tagmetas' => array('tag' => 'VARCHAR(' . QA_DB_MAX_WORD_LENGTH . ') NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL', 'PRIMARY KEY (tag, title)'));
if (QA_FINAL_EXTERNAL_USERS) {
unset($tables['users']);
unset($tables['userlogins']);
unset($tables['userprofile']);
unset($tables['userfields']);
unset($tables['messages']);
} else {
$userforeignkey = 'FOREIGN KEY (userid) REFERENCES ^users(userid)';
$tables['userlogins'][] = 'CONSTRAINT ^userlogins_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userprofile'][] = 'CONSTRAINT ^userprofile_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['posts'][] = 'CONSTRAINT ^posts_ibfk_1 ' . $userforeignkey . ' ON DELETE SET NULL';
$tables['uservotes'][] = 'CONSTRAINT ^uservotes_ibfk_2 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userlimits'][] = 'CONSTRAINT ^userlimits_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userfavorites'][] = 'CONSTRAINT ^userfavorites_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['usernotices'][] = 'CONSTRAINT ^usernotices_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userevents'][] = 'CONSTRAINT ^userevents_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userlevels'][] = 'CONSTRAINT ^userlevels_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['usermetas'][] = 'CONSTRAINT ^usermetas_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
}
return $tables;
}
function qa_db_table_definitions()
{
require_once QA_INCLUDE_DIR . 'qa-db-maxima.php';
require_once QA_INCLUDE_DIR . 'qa-app-users.php';
/*
Important note on character encoding in database and PHP connection to MySQL
All of our TEXT-style columns get UTF-8 character sets and collation from the default defined on the tables.
However, when connecting to MySQL from PHP, we don't issue a SET NAMES 'utf8' query or call mysql_set_charset('utf8').
This saves latency, since it could incur an extra query that needs to go from PHP to MySQL and back.
But as a result, MySQL assumes by default that our connection from PHP is using LATIN-1 encoding.
It would therefore convert the character set of both incoming and outgoing text, which would be a disaster.
We prevent this by retrieving columns using BINARY (e.g. SELECT BINARY name AS name) since binary content is left as is.
And also we precede any text in our queries with the _utf8 introducer, so that MySQL understands it's already UTF-8.
*/
/*
Other notes on the definitions below
* In MySQL versions prior to 5.0.3, VARCHAR(x) columns will be silently converted to TEXT where x>255
* See box at top of qa-app-recalc.php for a list of redundant (non-normal) information in the database
* Starting in version 1.2, we explicitly name keys and foreign key constraints, instead of allowing MySQL
to name these by default. Our chosen names match the default names that MySQL would have assigned, and
indeed *did* assign for people who installed an earlier version of QA. By naming them explicitly, we're
on more solid ground for possible future changes to indexes and foreign keys in the schema.
* There are other foreign key constraints that it would be valid to add, but that would not serve much
purpose in terms of preventing inconsistent data being retrieved, and would just slow down some queries.
* We name some columns here in a not entirely intuitive way. The reason is to match the names of columns in
other tables which are of a similar nature. This will save time and space when combining several SELECT
queries together via a UNION in qa_db_multi_select() - see comments in qa-db.php for more information.
*/
$useridcoltype = qa_db_user_column_type_verify();
$tables = array('users' => array('userid' => $useridcoltype . ' NOT NULL AUTO_INCREMENT', 'created' => 'DATETIME NOT NULL', 'createip' => 'INT UNSIGNED NOT NULL', 'email' => 'VARCHAR(' . QA_DB_MAX_EMAIL_LENGTH . ') NOT NULL', 'handle' => 'VARCHAR(' . QA_DB_MAX_HANDLE_LENGTH . ') NOT NULL', 'avatarblobid' => 'BIGINT UNSIGNED', 'avatarwidth' => 'SMALLINT UNSIGNED', 'avatarheight' => 'SMALLINT UNSIGNED', 'passsalt' => 'BINARY(16)', 'passcheck' => 'BINARY(20)', 'level' => 'TINYINT UNSIGNED NOT NULL', 'loggedin' => 'DATETIME NOT NULL', 'loginip' => 'INT UNSIGNED NOT NULL', 'written' => 'DATETIME', 'writeip' => 'INT UNSIGNED', 'emailcode' => 'CHAR(8) CHARACTER SET ascii NOT NULL DEFAULT \'\'', 'sessioncode' => 'CHAR(8) CHARACTER SET ascii NOT NULL DEFAULT \'\'', 'sessionsource' => 'VARCHAR (16) CHARACTER SET ascii DEFAULT \'\'', 'flags' => 'TINYINT UNSIGNED NOT NULL DEFAULT 0', 'PRIMARY KEY (userid)', 'KEY email (email)', 'KEY handle (handle)', 'KEY level (level)'), 'userlogins' => array('userid' => $useridcoltype . ' NOT NULL', 'source' => 'VARCHAR (16) CHARACTER SET ascii NOT NULL', 'identifier' => 'VARBINARY (1024) NOT NULL', 'identifiermd5' => 'BINARY (16) NOT NULL', 'KEY source (source, identifiermd5)', 'KEY userid (userid)'), 'userprofile' => array('userid' => $useridcoltype . ' NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_PROFILE_CONTENT_LENGTH . ') NOT NULL', 'UNIQUE userid (userid,title)'), 'userfields' => array('fieldid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT', 'title' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ')', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'flags' => 'TINYINT UNSIGNED NOT NULL', 'PRIMARY KEY (fieldid)'), 'cookies' => array('cookieid' => 'BIGINT UNSIGNED NOT NULL', 'created' => 'DATETIME NOT NULL', 'createip' => 'INT UNSIGNED NOT NULL', 'written' => 'DATETIME', 'writeip' => 'INT UNSIGNED', 'PRIMARY KEY (cookieid)'), 'categories' => array('categoryid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'parentid' => 'INT UNSIGNED', 'title' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TITLE_LENGTH . ') NOT NULL', 'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_CAT_CONTENT_LENGTH . ') NOT NULL DEFAULT \'\'', 'qcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'backpath' => 'VARCHAR(' . QA_CATEGORY_DEPTH * (QA_DB_MAX_CAT_PAGE_TAGS_LENGTH + 1) . ') NOT NULL DEFAULT \'\'', 'PRIMARY KEY (categoryid)', 'UNIQUE parentid (parentid, tags)', 'UNIQUE parentid_2 (parentid, position)', 'KEY backpath (backpath(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . '))'), 'pages' => array('pageid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT', 'title' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TITLE_LENGTH . ') NOT NULL', 'nav' => 'CHAR(1) CHARACTER SET ascii NOT NULL', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'flags' => 'TINYINT UNSIGNED NOT NULL', 'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ') NOT NULL', 'heading' => 'VARCHAR(' . QA_DB_MAX_TITLE_LENGTH . ')', 'content' => 'MEDIUMTEXT', 'PRIMARY KEY (pageid)', 'UNIQUE tags (tags)', 'UNIQUE position (position)'), 'widgets' => array('widgetid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT', 'place' => 'CHAR(2) CHARACTER SET ascii NOT NULL', 'position' => 'SMALLINT UNSIGNED NOT NULL', 'tags' => 'VARCHAR(' . QA_DB_MAX_WIDGET_TAGS_LENGTH . ') CHARACTER SET ascii NOT NULL', 'title' => 'VARCHAR(' . QA_DB_MAX_WIDGET_TITLE_LENGTH . ') NOT NULL', 'PRIMARY KEY (widgetid)', 'UNIQUE position (position)'), 'posts' => array('postid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'type' => "ENUM('Q', 'A', 'C', 'Q_HIDDEN', 'A_HIDDEN', 'C_HIDDEN') NOT NULL", 'parentid' => 'INT UNSIGNED', 'categoryid' => 'INT UNSIGNED', 'catidpath1' => 'INT UNSIGNED', 'catidpath2' => 'INT UNSIGNED', 'catidpath3' => 'INT UNSIGNED', 'acount' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'selchildid' => 'INT UNSIGNED', 'userid' => $useridcoltype, 'cookieid' => 'BIGINT UNSIGNED', 'createip' => 'INT UNSIGNED', 'lastuserid' => $useridcoltype, 'lastip' => 'INT UNSIGNED', 'upvotes' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'downvotes' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', 'netvotes' => 'SMALLINT NOT NULL DEFAULT 0', 'lastviewip' => 'INT UNSIGNED', 'views' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'hotness' => 'FLOAT', 'flagcount' => 'TINYINT UNSIGNED NOT NULL DEFAULT 0', 'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL DEFAULT \'\'', 'created' => 'DATETIME NOT NULL', 'updated' => 'DATETIME', 'title' => 'VARCHAR(' . QA_DB_MAX_TITLE_LENGTH . ')', 'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ')', 'tags' => 'VARCHAR(' . QA_DB_MAX_TAGS_LENGTH . ')', 'notify' => 'VARCHAR(' . QA_DB_MAX_EMAIL_LENGTH . ')', 'PRIMARY KEY (postid)', 'KEY type (type, created)', 'KEY type_2 (type, acount, created)', 'KEY type_3 (type, flagcount, created)', 'KEY type_4 (type, netvotes, created)', 'KEY type_5 (type, views, created)', 'KEY type_6 (type, hotness)', 'KEY parentid (parentid, type)', 'KEY userid (userid, type, created)', 'KEY selchildid (selchildid)', 'KEY catidpath1 (catidpath1, type, created)', 'KEY catidpath2 (catidpath2, type, created)', 'KEY catidpath3 (catidpath3, type, created)', 'KEY categoryid (categoryid, type, created)', 'KEY createip (createip, created)', 'KEY updated (updated, type)', 'KEY catidpath1_2 (catidpath1, updated, type)', 'KEY catidpath2_2 (catidpath2, updated, type)', 'KEY catidpath3_2 (catidpath3, updated, type)', 'KEY categoryid_2 (categoryid, updated, type)', 'KEY lastip (lastip, updated, type)', 'CONSTRAINT ^posts_ibfk_2 FOREIGN KEY (parentid) REFERENCES ^posts(postid)', 'CONSTRAINT ^posts_ibfk_3 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE SET NULL'), 'blobs' => array('blobid' => 'BIGINT UNSIGNED NOT NULL', 'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL', 'content' => 'MEDIUMBLOB NOT NULL', 'filename' => 'VARCHAR(' . QA_DB_MAX_BLOB_FILE_NAME_LENGTH . ')', 'userid' => $useridcoltype, 'cookieid' => 'BIGINT UNSIGNED', 'createip' => 'INT UNSIGNED', 'created' => 'DATETIME', 'PRIMARY KEY (blobid)'), 'words' => array('wordid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT', 'word' => 'VARCHAR(' . QA_DB_MAX_WORD_LENGTH . ') NOT NULL', 'titlecount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'contentcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'tagwordcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'tagcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', 'PRIMARY KEY (wordid)', 'KEY word (word)', 'KEY tagcount (tagcount)'), 'titlewords' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid)', 'CONSTRAINT ^titlewords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^titlewords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'contentwords' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'count' => 'TINYINT UNSIGNED NOT NULL', 'type' => "ENUM('Q', 'A', 'C') NOT NULL", 'questionid' => 'INT UNSIGNED NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid)', 'CONSTRAINT ^contentwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^contentwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'tagwords' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid)', 'CONSTRAINT ^tagwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^tagwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'posttags' => array('postid' => 'INT UNSIGNED NOT NULL', 'wordid' => 'INT UNSIGNED NOT NULL', 'postcreated' => 'DATETIME NOT NULL', 'KEY postid (postid)', 'KEY wordid (wordid,postcreated)', 'CONSTRAINT ^posttags_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE', 'CONSTRAINT ^posttags_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)'), 'uservotes' => array('postid' => 'INT UNSIGNED NOT NULL', 'userid' => $useridcoltype . ' NOT NULL', 'vote' => 'TINYINT NOT NULL', 'flag' => 'TINYINT NOT NULL', 'UNIQUE userid (userid, postid)', 'KEY postid (postid)', 'CONSTRAINT ^uservotes_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE'), 'userpoints' => array('userid' => $useridcoltype . ' NOT NULL', 'points' => 'INT NOT NULL DEFAULT 0', 'qposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'cposts' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aselects' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aselecteds' => 'MEDIUMINT NOT NULL DEFAULT 0', 'qupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'qdownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'aupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'adownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', 'qvoteds' => 'INT NOT NULL DEFAULT 0', 'avoteds' => 'INT NOT NULL DEFAULT 0', 'upvoteds' => 'INT NOT NULL DEFAULT 0', 'downvoteds' => 'INT NOT NULL DEFAULT 0', 'PRIMARY KEY (userid)', 'KEY points (points)'), 'userlimits' => array('userid' => $useridcoltype . ' NOT NULL', 'action' => 'CHAR(1) CHARACTER SET ascii NOT NULL', 'period' => 'INT UNSIGNED NOT NULL', 'count' => 'SMALLINT UNSIGNED NOT NULL', 'UNIQUE userid (userid, action)'), 'iplimits' => array('ip' => 'INT UNSIGNED NOT NULL', 'action' => 'CHAR(1) CHARACTER SET ascii NOT NULL', 'period' => 'INT UNSIGNED NOT NULL', 'count' => 'SMALLINT UNSIGNED NOT NULL', 'UNIQUE ip (ip, action)'), 'options' => array('title' => 'VARCHAR(' . QA_DB_MAX_OPTION_TITLE_LENGTH . ') NOT NULL', 'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL', 'PRIMARY KEY (title)'), 'cache' => array('type' => 'CHAR(8) CHARACTER SET ascii NOT NULL', 'cacheid' => 'BIGINT UNSIGNED DEFAULT 0', 'content' => 'MEDIUMBLOB NOT NULL', 'created' => 'DATETIME NOT NULL', 'lastread' => 'DATETIME NOT NULL', 'PRIMARY KEY (type,cacheid)', 'KEY (lastread)'));
if (QA_FINAL_EXTERNAL_USERS) {
unset($tables['users']);
unset($tables['userlogins']);
unset($tables['userprofile']);
} else {
$userforeignkey = 'FOREIGN KEY (userid) REFERENCES ^users(userid)';
$tables['userlogins'][] = 'CONSTRAINT ^userlogins_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userprofile'][] = 'CONSTRAINT ^userprofile_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['posts'][] = 'CONSTRAINT ^posts_ibfk_1 ' . $userforeignkey . ' ON DELETE SET NULL';
$tables['uservotes'][] = 'CONSTRAINT ^uservotes_ibfk_2 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userlimits'][] = 'CONSTRAINT ^userlimits_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
}
return $tables;
}
