Committer: ailyin
LJSUP-11530 (prepare LJ code to run on debian squeeze)U trunk/bin/upgrading/update-db-general.pl U trunk/bin/upgrading/update-db.pl U trunk/cgi-bin/LJ/Config.pm U trunk/cgi-bin/LJ/User.pm U trunk/cgi-bin/ljdefaults.pl
Modified: trunk/bin/upgrading/update-db-general.pl =================================================================== --- trunk/bin/upgrading/update-db-general.pl 2012-03-16 13:15:58 UTC (rev 21439) +++ trunk/bin/upgrading/update-db-general.pl 2012-03-16 13:19:48 UTC (rev 21440) @@ -4,2493 +4,2449 @@ mark_clustered(@LJ::USER_TABLES); -register_tablecreate("adopt", <<'EOC'); -CREATE TABLE adopt ( - adoptid int(10) unsigned NOT NULL auto_increment, - helperid int(10) unsigned NOT NULL default '0', - newbieid int(10) unsigned NOT NULL default '0', - changetime datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (adoptid), - KEY (helperid), - KEY (newbieid) +register_tablecreate('acctcode', <<'EOC'); +CREATE TABLE `acctcode` ( + `acid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `userid` int(10) unsigned NOT NULL, + `rcptid` int(10) unsigned NOT NULL DEFAULT '0', + `auth` char(5) NOT NULL, + PRIMARY KEY (`acid`), + KEY `userid` (`userid`), + KEY `rcptid` (`rcptid`) ) EOC -register_tablecreate("adoptlast", <<'EOC'); -CREATE TABLE adoptlast ( - userid int(10) unsigned NOT NULL default '0', - lastassigned datetime NOT NULL default '0000-00-00 00:00:00', - lastadopted datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (userid) +register_tablecreate('actionhistory', <<'EOC'); +CREATE TABLE `actionhistory` ( + `time` int(10) unsigned NOT NULL, + `clusterid` tinyint(3) unsigned NOT NULL, + `what` varchar(20) NOT NULL, + `count` int(10) unsigned NOT NULL DEFAULT '0', + KEY `time` (`time`) ) EOC -register_tablecreate("authactions", <<'EOC'); -CREATE TABLE authactions ( - aaid int(10) unsigned NOT NULL auto_increment, - userid int(10) unsigned NOT NULL default '0', - datecreate datetime NOT NULL default '0000-00-00 00:00:00', - authcode varchar(20) default NULL, - action varchar(50) default NULL, - arg1 varchar(255) default NULL, - PRIMARY KEY (aaid) +register_tablecreate('active_user', <<'EOC'); +CREATE TABLE `active_user` ( + `year` smallint(6) NOT NULL, + `month` tinyint(4) NOT NULL, + `day` tinyint(4) NOT NULL, + `hour` tinyint(4) NOT NULL, + `userid` int(10) unsigned NOT NULL, + `type` char(1) NOT NULL, + PRIMARY KEY (`year`,`month`,`day`,`hour`,`userid`) ) EOC -register_tablecreate("birthdays", <<'EOC'); -CREATE TABLE birthdays ( - userid INT UNSIGNED NOT NULL, - nextbirthday INT UNSIGNED, - PRIMARY KEY (userid), - KEY (nextbirthday) +register_tablecreate('active_user_summary', <<'EOC'); +CREATE TABLE `active_user_summary` ( + `year` smallint(6) NOT NULL, + `month` tinyint(4) NOT NULL, + `day` tinyint(4) NOT NULL, + `hour` tinyint(4) NOT NULL, + `clusterid` tinyint(3) unsigned NOT NULL, + `type` char(1) NOT NULL, + `count` int(10) unsigned NOT NULL DEFAULT '0', + KEY `year` (`year`,`month`,`day`,`hour`) ) EOC -register_tablecreate("clients", <<'EOC'); -CREATE TABLE clients ( - clientid smallint(5) unsigned NOT NULL auto_increment, - client varchar(40) default NULL, - PRIMARY KEY (clientid), - KEY (client) +register_tablecreate('adopt', <<'EOC'); +CREATE TABLE `adopt` ( + `adoptid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `helperid` int(10) unsigned NOT NULL DEFAULT '0', + `newbieid` int(10) unsigned NOT NULL DEFAULT '0', + `changetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + PRIMARY KEY (`adoptid`), + KEY `helperid` (`helperid`), + KEY `newbieid` (`newbieid`) ) EOC -post_create("clients", - "sqltry" => "INSERT INTO clients (client) SELECT DISTINCT client FROM logins", - ); +register_tablecreate('adoptlast', <<'EOC'); +CREATE TABLE `adoptlast` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `lastassigned` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `lastadopted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + PRIMARY KEY (`userid`) +) +EOC -register_tablecreate("clientusage", <<'EOC'); -CREATE TABLE clientusage ( - userid int(10) unsigned NOT NULL default '0', - clientid smallint(5) unsigned NOT NULL default '0', - lastlogin datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (clientid,userid), - UNIQUE KEY userid (userid,clientid) +register_tablecreate('antispam', <<'EOC'); +CREATE TABLE `antispam` ( + `journalid` int(10) unsigned NOT NULL, + `itemid` int(10) unsigned NOT NULL DEFAULT '0', + `type` char(1) NOT NULL, + `posterid` int(10) unsigned NOT NULL DEFAULT '0', + `eventtime` date DEFAULT NULL, + `poster_ip` char(15) DEFAULT NULL, + `email` char(50) DEFAULT NULL, + `user_agent` varchar(128) DEFAULT NULL, + `uniq` char(15) DEFAULT NULL, + `spam` tinyint(3) unsigned DEFAULT NULL, + `confidence` float(4,3) unsigned DEFAULT NULL, + `review` char(1) DEFAULT NULL, + PRIMARY KEY (`journalid`,`itemid`,`type`), + KEY `posterid` (`posterid`,`eventtime`), + KEY `spam` (`spam`), + KEY `review` (`review`), + KEY `eventtime` (`eventtime`) ) EOC -post_create("clientusage", - "sqltry" => "INSERT INTO clientusage SELECT u.userid, c.clientid, l.lastlogin FROM user u, clients c, logins l WHERE u.user=l.user AND l.client=c.client", - ); +register_tablecreate('authactions', <<'EOC'); +CREATE TABLE `authactions` ( + `aaid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `datecreate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `authcode` varchar(20) DEFAULT NULL, + `action` varchar(50) DEFAULT NULL, + `arg1` varchar(255) DEFAULT NULL, + `used` enum('Y','N') DEFAULT 'N', + PRIMARY KEY (`aaid`), + KEY `userid` (`userid`), + KEY `datecreate` (`datecreate`) +) +EOC -register_tablecreate("codes", <<'EOC'); -CREATE TABLE codes ( - type varchar(10) NOT NULL default '', - code varchar(7) NOT NULL default '', - item varchar(80) default NULL, - sortorder smallint(6) NOT NULL default '0', - PRIMARY KEY (type,code) -) PACK_KEYS=1 +register_tablecreate('backupdirty', <<'EOC'); +CREATE TABLE `backupdirty` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `marktime` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`userid`) +) EOC -register_tablecreate("community", <<'EOC'); -CREATE TABLE community ( - userid int(10) unsigned NOT NULL default '0', - ownerid int(10) unsigned NOT NULL default '0', - membership enum('open','closed') NOT NULL default 'open', - postlevel enum('members','select','screened') default NULL, - PRIMARY KEY (userid) +register_tablecreate('birthdays', <<'EOC'); +CREATE TABLE `birthdays` ( + `userid` int(10) unsigned NOT NULL, + `nextbirthday` int(10) unsigned DEFAULT NULL, + PRIMARY KEY (`userid`), + KEY `nextbirthday` (`nextbirthday`) ) EOC -register_tablecreate("dirsearchres2", <<'EOC'); -CREATE TABLE dirsearchres2 ( - qdigest varchar(32) NOT NULL default '', - dateins datetime NOT NULL default '0000-00-00 00:00:00', - userids blob, - PRIMARY KEY (qdigest), - KEY (dateins) +register_tablecreate('blobcache', <<'EOC'); +CREATE TABLE `blobcache` ( + `bckey` varchar(255) NOT NULL, + `dateupdate` datetime DEFAULT NULL, + `value` mediumblob, + PRIMARY KEY (`bckey`) ) EOC -register_tablecreate("duplock", <<'EOC'); -CREATE TABLE duplock ( - realm enum('support','log','comment') NOT NULL default 'support', - reid int(10) unsigned NOT NULL default '0', - userid int(10) unsigned NOT NULL default '0', - digest char(32) NOT NULL default '', - dupid int(10) unsigned NOT NULL default '0', - instime datetime NOT NULL default '0000-00-00 00:00:00', - KEY (realm,reid,userid) +register_tablecreate('blockwatch_events', <<'EOC'); +CREATE TABLE `blockwatch_events` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) ) EOC -register_tablecreate("faq", <<'EOC'); -CREATE TABLE faq ( - faqid mediumint(8) unsigned NOT NULL auto_increment, - question text, - answer text, - sortorder int(11) default NULL, - faqcat varchar(20) default NULL, - uses int(11) NOT NULL default '0', - lastmodtime datetime default NULL, - lastmoduserid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (faqid) +register_tablecreate('captcha_session', <<'EOC'); +CREATE TABLE `captcha_session` ( + `sess` char(20) NOT NULL DEFAULT '', + `sesstime` int(10) unsigned NOT NULL DEFAULT '0', + `lastcapid` int(11) DEFAULT NULL, + `trynum` smallint(6) DEFAULT '0', + PRIMARY KEY (`sess`), + KEY `sesstime` (`sesstime`) ) EOC -register_tablecreate("faqcat", <<'EOC'); -CREATE TABLE faqcat ( - faqcat varchar(20) NOT NULL default '', - faqcatname varchar(100) default NULL, - catorder int(11) default '50', - PRIMARY KEY (faqcat) +register_tablecreate('captchas', <<'EOC'); +CREATE TABLE `captchas` ( + `capid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `type` enum('image','audio') DEFAULT NULL, + `location` enum('blob','mogile') DEFAULT NULL, + `issuetime` int(10) unsigned NOT NULL DEFAULT '0', + `answer` char(10) DEFAULT NULL, + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `anum` smallint(5) unsigned NOT NULL, + PRIMARY KEY (`capid`), + KEY `type` (`type`,`issuetime`), + KEY `userid` (`userid`) ) EOC -register_tablecreate("faquses", <<'EOC'); -CREATE TABLE faquses ( - faqid MEDIUMINT UNSIGNED NOT NULL, - userid INT UNSIGNED NOT NULL, - dateview DATETIME NOT NULL, - PRIMARY KEY (userid, faqid), - KEY (faqid), - KEY (dateview) +# global table for community directory +register_tablecreate('category', <<'EOC'); +CREATE TABLE `category` ( + `catid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `pretty_name` varchar(255) NOT NULL, + `url_path` varchar(120) NOT NULL, + `parentcatid` int(10) unsigned NOT NULL, + `vert_id` int(11) NOT NULL, + PRIMARY KEY (`catid`), + UNIQUE KEY `url_path` (`url_path`,`parentcatid`,`vert_id`), + KEY `parentcatid` (`parentcatid`) ) EOC -register_tablecreate("friendgroup", <<'EOC'); -CREATE TABLE friendgroup ( - userid int(10) unsigned NOT NULL default '0', - groupnum tinyint(3) unsigned NOT NULL default '0', - groupname varchar(30) NOT NULL default '', - sortorder tinyint(3) unsigned NOT NULL default '50', - is_public enum('0','1') NOT NULL default '0', - PRIMARY KEY (userid,groupnum) +register_tablecreate('category_recent_posts', <<'EOC'); +CREATE TABLE `category_recent_posts` ( + `jitemid` int(11) NOT NULL DEFAULT '0', + `timecreate` datetime NOT NULL, + `journalid` int(10) unsigned NOT NULL, + `is_deleted` tinyint(1) NOT NULL DEFAULT '0', + `pic_orig_url` varchar(255) NOT NULL DEFAULT '', + `pic_fb_url` varchar(255) NOT NULL DEFAULT '', + PRIMARY KEY (`journalid`,`jitemid`), + KEY `timecreate` (`timecreate`), + KEY `journalid` (`journalid`) ) EOC -register_tablecreate("friends", <<'EOC'); -CREATE TABLE friends ( - userid int(10) unsigned NOT NULL default '0', - friendid int(10) unsigned NOT NULL default '0', - fgcolor char(7) default NULL, - bgcolor char(7) default NULL, - groupmask int(10) unsigned NOT NULL default '1', - showbydefault enum('1','0') NOT NULL default '1', - PRIMARY KEY (userid,friendid), - KEY (friendid) +# Map journals to categories +register_tablecreate('categoryjournals', <<'EOC'); +CREATE TABLE `categoryjournals` ( + `catid` int(10) unsigned NOT NULL, + `journalid` int(10) unsigned NOT NULL, + PRIMARY KEY (`catid`,`journalid`), + KEY `journalid` (`journalid`) ) EOC -register_tablecreate("interests", <<'EOC'); -CREATE TABLE interests ( - intid int(10) unsigned NOT NULL auto_increment, - interest varchar(255) NOT NULL default '', - intcount mediumint(8) unsigned default NULL, - PRIMARY KEY (intid), - UNIQUE interest (interest) +# Moderation of submissions for Community Directory +register_tablecreate('categoryjournals_pending', <<'EOC'); +CREATE TABLE `categoryjournals_pending` ( + `pendid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `jid` int(10) unsigned NOT NULL DEFAULT '0', + `submitid` int(10) unsigned DEFAULT NULL, + `catid` int(10) unsigned NOT NULL, + `status` char(1) DEFAULT NULL, + `lastupdate` int(10) unsigned NOT NULL, + `modid` int(10) unsigned DEFAULT NULL, + PRIMARY KEY (`pendid`), + KEY `jid` (`jid`), + KEY `catid` (`catid`) ) EOC -register_tablecreate("keywords", <<'EOC'); -CREATE TABLE keywords ( - kwid int(10) unsigned NOT NULL auto_increment, - keyword varchar(80) binary NOT NULL default '', - PRIMARY KEY (kwid), - UNIQUE KEY kwidx (keyword) +# Extra properties for categories +register_tablecreate('categoryprop', <<'EOC'); +CREATE TABLE `categoryprop` ( + `catid` int(10) unsigned NOT NULL, + `propid` smallint(5) unsigned NOT NULL, + `propval` varchar(255) NOT NULL, + KEY `catid` (`catid`,`propid`) ) EOC -register_tablecreate("logproplist", <<'EOC'); -CREATE TABLE logproplist ( - propid tinyint(3) unsigned NOT NULL auto_increment, - name varchar(50) default NULL, - prettyname varchar(60) default NULL, - sortorder mediumint(8) unsigned default NULL, - datatype enum('char','num','bool') NOT NULL default 'char', - des varchar(255) default NULL, - PRIMARY KEY (propid), - UNIQUE KEY name (name) +# Property list for categories +register_tablecreate('categoryproplist', <<'EOC'); +CREATE TABLE `categoryproplist` ( + `propid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(255) DEFAULT NULL, + `des` varchar(255) DEFAULT NULL, + `scope` enum('general','local') NOT NULL DEFAULT 'general', + PRIMARY KEY (`propid`), + UNIQUE KEY `name` (`name`) ) EOC -register_tablecreate("memkeyword", <<'EOC'); -CREATE TABLE memkeyword ( - memid int(10) unsigned NOT NULL default '0', - kwid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (memid,kwid) +# Challenges table (for non-memcache support) +register_tablecreate('challenges', <<'EOC'); +CREATE TABLE `challenges` ( + `ctime` int(10) unsigned NOT NULL DEFAULT '0', + `challenge` char(80) NOT NULL DEFAULT '', + `count` int(5) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`challenge`) ) EOC -register_tablecreate("memorable", <<'EOC'); -CREATE TABLE memorable ( - memid int(10) unsigned NOT NULL auto_increment, - userid int(10) unsigned NOT NULL default '0', - itemid int(10) unsigned NOT NULL default '0', - des varchar(60) default NULL, - security enum('public','friends','private') NOT NULL default 'public', - PRIMARY KEY (memid), - UNIQUE KEY userid (userid,itemid), - KEY (itemid) +register_tablecreate('clients', <<'EOC'); +CREATE TABLE `clients` ( + `clientid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `client` varchar(40) DEFAULT NULL, + PRIMARY KEY (`clientid`), + KEY `client` (`client`) ) EOC -register_tablecreate("moods", <<'EOC'); -CREATE TABLE moods ( - moodid int(10) unsigned NOT NULL auto_increment, - mood varchar(40) default NULL, - parentmood int(10) unsigned NOT NULL default '0', - PRIMARY KEY (moodid), - UNIQUE KEY mood (mood) +register_tablecreate('clientusage', <<'EOC'); +CREATE TABLE `clientusage` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `clientid` smallint(5) unsigned NOT NULL DEFAULT '0', + `lastlogin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + PRIMARY KEY (`clientid`,`userid`), + UNIQUE KEY `userid` (`userid`,`clientid`) ) EOC -register_tablecreate("moodthemedata", <<'EOC'); -CREATE TABLE moodthemedata ( - moodthemeid int(10) unsigned NOT NULL default '0', - moodid int(10) unsigned NOT NULL default '0', - picurl varchar(100) default NULL, - width tinyint(3) unsigned NOT NULL default '0', - height tinyint(3) unsigned NOT NULL default '0', - KEY (moodthemeid), - PRIMARY KEY (moodthemeid,moodid) +register_tablecreate('clustermove', <<'EOC'); +CREATE TABLE `clustermove` ( + `cmid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `userid` int(10) unsigned NOT NULL, + `sclust` tinyint(3) unsigned NOT NULL, + `dclust` tinyint(3) unsigned NOT NULL, + `timestart` int(10) unsigned DEFAULT NULL, + `timedone` int(10) unsigned DEFAULT NULL, + `sdeleted` enum('1','0') DEFAULT NULL, + PRIMARY KEY (`cmid`), + KEY `userid` (`userid`) ) EOC -register_tablecreate("moodthemes", <<'EOC'); -CREATE TABLE moodthemes ( - moodthemeid int(10) unsigned NOT NULL auto_increment, - ownerid int(10) unsigned NOT NULL default '0', - name varchar(50) default NULL, - des varchar(100) default NULL, - is_public enum('Y','N') NOT NULL default 'N', - PRIMARY KEY (moodthemeid), - KEY (is_public), - KEY (ownerid) +register_tablecreate('clustermove_inprogress', <<'EOC'); +CREATE TABLE `clustermove_inprogress` ( + `userid` int(10) unsigned NOT NULL, + `locktime` int(10) unsigned NOT NULL, + `dstclust` smallint(5) unsigned NOT NULL, + `moverhost` int(10) unsigned NOT NULL, + `moverport` smallint(5) unsigned NOT NULL, + `moverinstance` char(22) NOT NULL, + PRIMARY KEY (`userid`) ) EOC -register_tablecreate("news_sent", <<'EOC'); -CREATE TABLE news_sent ( - newsid int(10) unsigned NOT NULL auto_increment, - newsnum mediumint(8) unsigned NOT NULL default '0', - user varchar(15) NOT NULL default '', - datesent datetime default NULL, - email varchar(100) NOT NULL default '', - PRIMARY KEY (newsid), - KEY (newsnum), - KEY (user), - KEY (email) +# tracking where users are active +register_tablecreate('clustertrack2', <<'EOC'); +CREATE TABLE `clustertrack2` ( + `userid` int(10) unsigned NOT NULL, + `timeactive` int(10) unsigned NOT NULL, + `clusterid` smallint(5) unsigned DEFAULT NULL, + PRIMARY KEY (`userid`), + KEY `timeactive` (`timeactive`,`clusterid`) ) EOC -register_tablecreate("noderefs", <<'EOC'); -CREATE TABLE noderefs ( - nodetype char(1) NOT NULL default '', - nodeid int(10) unsigned NOT NULL default '0', - urlmd5 varchar(32) NOT NULL default '', - url varchar(120) NOT NULL default '', - PRIMARY KEY (nodetype,nodeid,urlmd5) +register_tablecreate('cmdbuffer', <<'EOC'); +CREATE TABLE `cmdbuffer` ( + `cbid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `journalid` int(10) unsigned NOT NULL, + `cmd` varchar(30) NOT NULL DEFAULT '', + `instime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `args` text NOT NULL, + PRIMARY KEY (`cbid`), + KEY `cmd` (`cmd`), + KEY `journalid` (`journalid`) ) EOC -register_tablecreate("overrides", <<'EOC'); # global, old -CREATE TABLE overrides ( - user varchar(15) NOT NULL default '', - override text, - PRIMARY KEY (user) +register_tablecreate('codes', <<'EOC'); +CREATE TABLE `codes` ( + `type` varchar(10) NOT NULL DEFAULT '', + `code` varchar(7) NOT NULL DEFAULT '', + `item` varchar(80) DEFAULT NULL, + `sortorder` smallint(6) NOT NULL DEFAULT '0', + PRIMARY KEY (`type`,`code`) +) PACK_KEYS=1 +EOC + +register_tablecreate('comet_history', <<'EOC'); +CREATE TABLE `comet_history` ( + `rec_id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `uid` int(10) unsigned NOT NULL, + `type` varchar(31) DEFAULT NULL, + `message` text, + `status` char(1) DEFAULT 'N', + `added` datetime DEFAULT NULL, + PRIMARY KEY (`rec_id`), + KEY `uid` (`uid`) ) EOC -register_tablecreate("pendcomments", <<'EOC'); -CREATE TABLE pendcomments ( - jid int(10) unsigned NOT NULL, - pendcid int(10) unsigned NOT NULL, - data blob NOT NULL, - datesubmit int(10) unsigned NOT NULL, - PRIMARY KEY (pendcid, jid), - KEY (datesubmit) +register_tablecreate('comm_promo_list', <<'EOC'); +CREATE TABLE `comm_promo_list` ( + `journalid` int(10) unsigned NOT NULL, + `r_start` int(10) unsigned NOT NULL, + `r_end` int(10) unsigned NOT NULL, + KEY `r_start` (`r_start`) ) EOC -register_tablecreate("poll", <<'EOC'); -CREATE TABLE poll ( - pollid int(10) unsigned NOT NULL auto_increment, - itemid int(10) unsigned NOT NULL default '0', - journalid int(10) unsigned NOT NULL default '0', - posterid int(10) unsigned NOT NULL default '0', - whovote enum('all','friends') NOT NULL default 'all', - whoview enum('all','friends','none') NOT NULL default 'all', - name varchar(255) default NULL, - PRIMARY KEY (pollid), - KEY (itemid), - KEY (journalid), - KEY (posterid) +register_tablecreate('commenturls', <<'EOC'); +CREATE TABLE `commenturls` ( + `posterid` int(10) unsigned NOT NULL, + `journalid` int(10) unsigned NOT NULL, + `ip` varchar(15) DEFAULT NULL, + `jtalkid` int(10) unsigned NOT NULL, + `timecreate` int(10) unsigned NOT NULL, + `url` varchar(255) NOT NULL, + KEY `timecreate` (`timecreate`) ) EOC -register_tablecreate("pollitem", <<'EOC'); -CREATE TABLE pollitem ( - pollid int(10) unsigned NOT NULL default '0', - pollqid tinyint(3) unsigned NOT NULL default '0', - pollitid tinyint(3) unsigned NOT NULL default '0', - sortorder tinyint(3) unsigned NOT NULL default '0', - item varchar(255) default NULL, - PRIMARY KEY (pollid,pollqid,pollitid) +register_tablecreate('comminterests', <<'EOC'); +CREATE TABLE `comminterests` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `intid` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`userid`,`intid`), + KEY `intid` (`intid`) ) EOC -register_tablecreate("pollquestion", <<'EOC'); -CREATE TABLE pollquestion ( - pollid int(10) unsigned NOT NULL default '0', - pollqid tinyint(3) unsigned NOT NULL default '0', - sortorder tinyint(3) unsigned NOT NULL default '0', - type enum('check','radio','drop','text','scale') default NULL, - opts varchar(20) default NULL, - qtext text, - PRIMARY KEY (pollid,pollqid) +register_tablecreate('community', <<'EOC'); +CREATE TABLE `community` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `membership` enum('open','closed','moderated') NOT NULL DEFAULT 'open', + `postlevel` enum('members','select','screened') DEFAULT NULL, + PRIMARY KEY (`userid`) ) EOC -register_tablecreate("pollresult", <<'EOC'); -CREATE TABLE pollresult ( - pollid int(10) unsigned NOT NULL default '0', - pollqid tinyint(3) unsigned NOT NULL default '0', - userid int(10) unsigned NOT NULL default '0', - value varchar(255) default NULL, - PRIMARY KEY (pollid,pollqid,userid), - KEY (pollid,userid) +register_tablecreate('content_flag', <<'EOC'); +CREATE TABLE `content_flag` ( + `flagid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `journalid` int(10) unsigned NOT NULL, + `typeid` tinyint(3) unsigned NOT NULL, + `itemid` int(10) unsigned DEFAULT NULL, + `catid` tinyint(3) unsigned NOT NULL, + `reporterid` int(10) unsigned NOT NULL, + `reporteruniq` varchar(15) DEFAULT NULL, + `instime` int(10) unsigned NOT NULL, + `modtime` int(10) unsigned NOT NULL, + `status` char(1) DEFAULT NULL, + `supportid` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`flagid`), + KEY `journalid` (`journalid`,`typeid`,`itemid`), + KEY `instime` (`instime`), + KEY `reporterid` (`reporterid`), + KEY `status` (`status`) ) EOC -register_tablecreate("pollsubmission", <<'EOC'); -CREATE TABLE pollsubmission ( - pollid int(10) unsigned NOT NULL default '0', - userid int(10) unsigned NOT NULL default '0', - datesubmit datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (pollid,userid), - KEY (userid) +# user counters +register_tablecreate('counter', <<'EOC'); +CREATE TABLE `counter` ( + `journalid` int(10) unsigned NOT NULL, + `area` char(1) NOT NULL, + `max` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`journalid`,`area`) ) EOC -register_tablecreate("priv_list", <<'EOC'); -CREATE TABLE priv_list ( - prlid smallint(5) unsigned NOT NULL auto_increment, - privcode varchar(20) NOT NULL default '', - privname varchar(40) default NULL, - des varchar(255) default NULL, - is_public ENUM('1', '0') DEFAULT '1' NOT NULL, - PRIMARY KEY (prlid), - UNIQUE KEY privcode (privcode) +# contextual product prodding history, making sure we don't bug people when +# they don't want it anymore. +# +# -- firstshowtime: when it was first highlighted to them (not all the +# everything page) +# -- recentshowtime: a recent showing time. perhaps not THE most +# recent, though. +# -- acktime: time the user saw the box. either by clicking +# next/no/more info. +# -- nothankstime: also a boolean: time/if user doesn't want to +# see it again +# -- clickthrutime: time user clicked for more info +register_tablecreate('cprod', <<'EOC'); +CREATE TABLE `cprod` ( + `userid` int(10) unsigned NOT NULL, + `cprodid` smallint(5) unsigned NOT NULL, + `firstshowtime` int(10) unsigned DEFAULT NULL, + `recentshowtime` int(10) unsigned DEFAULT NULL, + `acktime` int(10) unsigned DEFAULT NULL, + `nothankstime` int(10) unsigned DEFAULT NULL, + `clickthrutime` int(10) unsigned DEFAULT NULL, + `clickthruver` smallint(5) unsigned DEFAULT NULL, + PRIMARY KEY (`userid`,`cprodid`) ) EOC -register_tablecreate("priv_map", <<'EOC'); -CREATE TABLE priv_map ( - prmid mediumint(8) unsigned NOT NULL auto_increment, - userid int(10) unsigned NOT NULL default '0', - prlid smallint(5) unsigned NOT NULL default '0', - arg varchar(40) default NULL, - PRIMARY KEY (prmid), - KEY (userid), - KEY (prlid) +# global (contextual product prodding, "hey, you've never used polls, wanna +# learn how?") +register_tablecreate('cprodlist', <<'EOC'); +CREATE TABLE `cprodlist` ( + `cprodid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `class` varchar(100) DEFAULT NULL, + PRIMARY KEY (`cprodid`), + UNIQUE KEY `class` (`class`) ) EOC -register_tablecreate("cmdbuffer", <<'EOC'); -CREATE TABLE cmdbuffer ( - cbid INT UNSIGNED NOT NULL AUTO_INCREMENT, - journalid INT UNSIGNED NOT NULL, - cmd VARCHAR(30) NOT NULL default '', - instime datetime NOT NULL default '0000-00-00 00:00:00', - args TEXT NOT NULL, - PRIMARY KEY (cbid), - KEY (cmd), - KEY (journalid) +register_tablecreate('dbinfo', <<'EOC'); +CREATE TABLE `dbinfo` ( + `dbid` tinyint(3) unsigned NOT NULL, + `name` varchar(25) DEFAULT NULL, + `fdsn` varchar(255) DEFAULT NULL, + `rootfdsn` varchar(255) DEFAULT NULL, + `masterid` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`dbid`), + UNIQUE KEY `name` (`name`) ) EOC -register_tablecreate("random_user_set", <<'EOC'); -CREATE TABLE random_user_set ( - posttime INT UNSIGNED NOT NULL, - userid INT UNSIGNED NOT NULL, - PRIMARY KEY (posttime) +register_tablecreate('dbweights', <<'EOC'); +CREATE TABLE `dbweights` ( + `dbid` tinyint(3) unsigned NOT NULL, + `role` varchar(25) NOT NULL, + `norm` tinyint(3) unsigned NOT NULL, + `curr` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`dbid`,`role`) ) EOC -register_tablecreate("schemacols", <<'EOC'); -CREATE TABLE schemacols ( - tablename varchar(40) NOT NULL default '', - colname varchar(40) NOT NULL default '', - des varchar(255) default NULL, - PRIMARY KEY (tablename,colname) +register_tablecreate('debug_notifymethod', <<'EOC'); +CREATE TABLE `debug_notifymethod` ( + `userid` int(10) unsigned NOT NULL, + `subid` int(10) unsigned DEFAULT NULL, + `ntfytime` int(10) unsigned DEFAULT NULL, + `origntypeid` int(10) unsigned DEFAULT NULL, + `etypeid` int(10) unsigned DEFAULT NULL, + `ejournalid` int(10) unsigned DEFAULT NULL, + `earg1` int(11) DEFAULT NULL, + `earg2` int(11) DEFAULT NULL, + `schjobid` varchar(50) DEFAULT NULL ) EOC -register_tablecreate("schematables", <<'EOC'); -CREATE TABLE schematables ( - tablename varchar(40) NOT NULL default '', - public_browsable enum('0','1') NOT NULL default '0', - redist_mode enum('off','insert','replace') NOT NULL default 'off', - des text, - PRIMARY KEY (tablename) +# delayed post Storable object (all props/options) +register_tablecreate('delayedblob2', <<'EOC'); +CREATE TABLE `delayedblob2` ( + `journalid` int(10) unsigned NOT NULL, + `delayedid` int(10) unsigned NOT NULL, + `request_stor` mediumblob, + PRIMARY KEY (`journalid`,`delayedid`) ) EOC -register_tablecreate("stats", <<'EOC'); -CREATE TABLE stats ( - statcat varchar(30) NOT NULL, - statkey varchar(150) NOT NULL, - statval int(10) unsigned NOT NULL, - UNIQUE KEY statcat_2 (statcat,statkey) +register_tablecreate('delayedlog2', <<'EOC'); +CREATE TABLE `delayedlog2` ( + `journalid` int(10) unsigned NOT NULL, + `delayedid` mediumint(8) unsigned NOT NULL, + `posterid` int(10) unsigned NOT NULL, + `subject` char(30) DEFAULT NULL, + `logtime` datetime DEFAULT NULL, + `posttime` datetime DEFAULT NULL, + `security` enum('public','private','usemask') NOT NULL DEFAULT 'public', + `allowmask` int(10) unsigned NOT NULL DEFAULT '0', + `year` smallint(6) NOT NULL DEFAULT '0', + `month` tinyint(4) NOT NULL DEFAULT '0', + `day` tinyint(4) NOT NULL DEFAULT '0', + `rlogtime` int(10) unsigned NOT NULL DEFAULT '0', + `revptime` int(10) unsigned NOT NULL DEFAULT '0', + `is_sticky` tinyint(1) NOT NULL, + PRIMARY KEY (`journalid`,`delayedid`), + KEY `journalid` (`journalid`,`logtime`,`posttime`,`year`,`month`,`day`), + KEY `rlogtime` (`journalid`,`rlogtime`), + KEY `revptime` (`journalid`,`revptime`) ) EOC -register_tablecreate("blobcache", <<'EOC'); -CREATE TABLE blobcache ( - bckey VARCHAR(40) NOT NULL, - PRIMARY KEY (bckey), - dateupdate DATETIME, - value MEDIUMBLOB +register_tablecreate('dirmogsethandles', <<'EOC'); +CREATE TABLE `dirmogsethandles` ( + `conskey` char(40) NOT NULL, + `exptime` int(10) unsigned NOT NULL, + PRIMARY KEY (`conskey`), + KEY `exptime` (`exptime`) ) EOC -register_tablecreate("style", <<'EOC'); -CREATE TABLE style ( - styleid int(11) NOT NULL auto_increment, - user varchar(15) NOT NULL default '', - styledes varchar(50) default NULL, - type varchar(10) NOT NULL default '', - formatdata text, - is_public enum('Y','N') NOT NULL default 'N', - is_embedded enum('Y','N') NOT NULL default 'N', - is_colorfree enum('Y','N') NOT NULL default 'N', - opt_cache enum('Y','N') NOT NULL default 'N', - has_ads enum('Y','N') NOT NULL default 'N', - lastupdate datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (styleid), - KEY (user), - KEY (type) -) PACK_KEYS=1 +register_tablecreate('dirsearchres2', <<'EOC'); +CREATE TABLE `dirsearchres2` ( + `qdigest` varchar(32) NOT NULL DEFAULT '', + `dateins` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `userids` blob, + PRIMARY KEY (`qdigest`), + KEY `dateins` (`dateins`) +) EOC -# cache Storable-frozen pre-cleaned style variables -register_tablecreate("s1stylecache", <<'EOC'); # clustered -CREATE TABLE s1stylecache ( - styleid INT UNSIGNED NOT NULL PRIMARY KEY, - cleandate DATETIME, - type VARCHAR(10) NOT NULL DEFAULT '', - opt_cache ENUM('Y','N') NOT NULL DEFAULT 'N', - vars_stor BLOB, - vars_cleanver SMALLINT UNSIGNED NOT NULL +register_tablecreate('domains', <<'EOC'); +CREATE TABLE `domains` ( + `domain` varchar(80) NOT NULL, + `userid` int(10) unsigned NOT NULL, + PRIMARY KEY (`domain`), + KEY `userid` (`userid`) ) EOC -# caches Storable-frozen pre-cleaned overrides & colors -register_tablecreate("s1usercache", <<'EOC'); # clustered -CREATE TABLE s1usercache ( - userid INT UNSIGNED NOT NULL PRIMARY KEY, - override_stor BLOB, - override_cleanver SMALLINT UNSIGNED NOT NULL, - color_stor BLOB +register_tablecreate('dudata', <<'EOC'); +CREATE TABLE `dudata` ( + `userid` int(10) unsigned NOT NULL, + `area` char(1) NOT NULL, + `areaid` int(10) unsigned NOT NULL, + `bytes` mediumint(8) unsigned NOT NULL, + PRIMARY KEY (`userid`,`area`,`areaid`) ) EOC -register_tablecreate("support", <<'EOC'); -CREATE TABLE support ( - spid int(10) unsigned NOT NULL auto_increment, - reqtype enum('user','email') default NULL, - requserid int(10) unsigned NOT NULL default '0', - reqname varchar(50) default NULL, - reqemail varchar(70) default NULL, - state enum('open','closed') default NULL, - authcode varchar(15) NOT NULL default '', - spcatid int(10) unsigned NOT NULL default '0', - subject varchar(80) default NULL, - timecreate int(10) unsigned default NULL, - timetouched int(10) unsigned default NULL, - timeclosed int(10) unsigned default NULL, - PRIMARY KEY (spid), - INDEX (state), - INDEX (requserid), - INDEX (reqemail) +register_tablecreate('duplock', <<'EOC'); +CREATE TABLE `duplock` ( + `realm` enum('support','log','comment','payments') NOT NULL DEFAULT 'support', + `reid` int(10) unsigned NOT NULL DEFAULT '0', + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `digest` char(32) NOT NULL DEFAULT '', + `dupid` int(10) unsigned NOT NULL DEFAULT '0', + `instime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + KEY `realm` (`realm`,`reid`,`userid`) ) EOC -register_tablecreate("supportcat", <<'EOC'); -CREATE TABLE supportcat ( - spcatid int(10) unsigned NOT NULL auto_increment, - catname varchar(80) default NULL, - sortorder mediumint(8) unsigned NOT NULL default '0', - basepoints tinyint(3) unsigned NOT NULL default '1', - PRIMARY KEY (spcatid) +register_tablecreate('email', <<'EOC'); +CREATE TABLE `email` ( + `userid` int(10) unsigned NOT NULL, + `email` varchar(50) DEFAULT NULL, + PRIMARY KEY (`userid`), + KEY `email` (`email`) ) EOC -register_tablecreate("supportlog", <<'EOC'); -CREATE TABLE supportlog ( - splid int(10) unsigned NOT NULL auto_increment, - spid int(10) unsigned NOT NULL default '0', - timelogged int(10) unsigned NOT NULL default '0', - type enum('req','custom','faqref') default NULL, - faqid mediumint(8) unsigned NOT NULL default '0', - userid int(10) unsigned NOT NULL default '0', - message text, - PRIMARY KEY (splid), - KEY (spid) +register_tablecreate('email_status', <<'EOC'); +CREATE TABLE `email_status` ( + `email` varchar(50) NOT NULL DEFAULT '', + `first_error_time` int(10) unsigned NOT NULL, + `last_error_time` int(10) unsigned NOT NULL, + `error_count` tinyint(3) unsigned NOT NULL, + `disabled` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`email`), + KEY `first_error_time` (`first_error_time`) ) EOC -register_tablecreate("supportnotify", <<'EOC'); -CREATE TABLE supportnotify ( - spcatid int(10) unsigned NOT NULL default '0', - userid int(10) unsigned NOT NULL default '0', - level enum('all','new') default NULL, - KEY (spcatid), - KEY (userid), - PRIMARY KEY (spcatid,userid) +register_tablecreate('embedcontent', <<'EOC'); +CREATE TABLE `embedcontent` ( + `userid` int(10) unsigned NOT NULL, + `moduleid` int(10) unsigned NOT NULL, + `content` text, + PRIMARY KEY (`userid`,`moduleid`) ) EOC -register_tablecreate("supportpoints", <<'EOC'); -CREATE TABLE supportpoints ( - spid int(10) unsigned NOT NULL default '0', - userid int(10) unsigned NOT NULL default '0', - points tinyint(3) unsigned default NULL, - KEY (spid), - KEY (userid) +register_tablecreate('embedcontent_preview', <<'EOC'); +CREATE TABLE `embedcontent_preview` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `moduleid` int(10) NOT NULL DEFAULT '0', + `content` text, + PRIMARY KEY (`userid`,`moduleid`) ) EOC -register_tablecreate("supportpointsum", <<'EOC'); -CREATE TABLE supportpointsum ( - userid INT UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY (userid), - totpoints MEDIUMINT UNSIGNED DEFAULT 0, - lastupdate INT UNSIGNED NOT NULL, - INDEX (totpoints, lastupdate), - INDEX (lastupdate) +register_tablecreate('eventrates', <<'EOC'); +CREATE TABLE `eventrates` ( + `journalid` int(10) unsigned NOT NULL, + `itemid` mediumint(8) unsigned NOT NULL, + `userid` int(10) unsigned NOT NULL, + `changetime` datetime NOT NULL, + PRIMARY KEY (`journalid`,`itemid`,`userid`) ) EOC -post_create("supportpointsum", - "sqltry" => "INSERT IGNORE INTO supportpointsum (userid, totpoints, lastupdate) " . - "SELECT userid, SUM(points), 0 FROM supportpoints GROUP BY userid", - ); +register_tablecreate('eventratescounters', <<'EOC'); +CREATE TABLE `eventratescounters` ( + `journalid` int(10) unsigned NOT NULL, + `itemid` mediumint(8) unsigned NOT NULL, + `count` int(10) unsigned NOT NULL, + PRIMARY KEY (`journalid`,`itemid`) +) +EOC +register_tablecreate('eventtypelist', <<'EOC'); +CREATE TABLE `eventtypelist` ( + `etypeid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `class` varchar(100) DEFAULT NULL, + PRIMARY KEY (`etypeid`), + UNIQUE KEY `class` (`class`) +) +EOC -register_tablecreate("talkproplist", <<'EOC'); -CREATE TABLE talkproplist ( - tpropid smallint(5) unsigned NOT NULL auto_increment, - name varchar(50) default NULL, - prettyname varchar(60) default NULL, - datatype enum('char','num','bool') NOT NULL default 'char', - des varchar(255) default NULL, - PRIMARY KEY (tpropid), - UNIQUE KEY name (name) +register_tablecreate('expunged_users', <<'EOC'); +CREATE TABLE `expunged_users` ( + `userid` int(10) unsigned NOT NULL, + `user` varchar(15) NOT NULL DEFAULT '', + `expunge_time` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`user`), + KEY `expunge_time` (`expunge_time`), + KEY `userid` (`userid`) ) EOC -register_tablecreate("themedata", <<'EOC'); -CREATE TABLE themedata ( - themeid mediumint(8) unsigned NOT NULL default '0', - coltype varchar(30) default NULL, - color varchar(30) default NULL, - KEY (themeid) -) PACK_KEYS=1 +# external user mappings +# note: extuser/extuserid are expected to sometimes be NULL, even +# though they are keyed. (Null values are not taken into account when +# using indexes) +register_tablecreate('extuser', <<'EOC'); +CREATE TABLE `extuser` ( + `userid` int(10) unsigned NOT NULL, + `siteid` int(10) unsigned NOT NULL, + `extuser` varchar(50) DEFAULT NULL, + `extuserid` int(10) unsigned DEFAULT NULL, + PRIMARY KEY (`userid`), + UNIQUE KEY `extuser` (`siteid`,`extuser`), + UNIQUE KEY `extuserid` (`siteid`,`extuserid`) +) EOC -register_tablecreate("themelist", <<'EOC'); -CREATE TABLE themelist ( - themeid mediumint(8) unsigned NOT NULL auto_increment, - name varchar(50) NOT NULL default '', - PRIMARY KEY (themeid) +register_tablecreate('faq', <<'EOC'); +CREATE TABLE `faq` ( + `faqid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, + `question` text, + `summary` text, + `answer` text, + `sortorder` int(11) DEFAULT NULL, + `faqcat` varchar(20) DEFAULT NULL, + `uses` int(11) NOT NULL DEFAULT '0', + `lastmodtime` datetime DEFAULT NULL, + `lastmoduserid` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`faqid`) ) EOC -register_tablecreate("todo", <<'EOC'); -CREATE TABLE todo ( - todoid int(10) unsigned NOT NULL auto_increment, - journalid int(10) unsigned NOT NULL default '0', - posterid int(10) unsigned NOT NULL default '0', - ownerid int(10) unsigned NOT NULL default '0', - statusline varchar(40) default NULL, - security enum('public','private','friends') NOT NULL default 'public', - subject varchar(100) default NULL, - des varchar(255) default NULL, - priority enum('1','2','3','4','5') NOT NULL default '3', - datecreate datetime NOT NULL default '0000-00-00 00:00:00', - dateupdate datetime default NULL, - datedue datetime default NULL, - dateclosed datetime default NULL, - progress tinyint(3) unsigned NOT NULL default '0', - PRIMARY KEY (todoid), - KEY (journalid), - KEY (posterid), - KEY (ownerid) +register_tablecreate('faqcat', <<'EOC'); +CREATE TABLE `faqcat` ( + `faqcat` varchar(20) NOT NULL DEFAULT '', + `faqcatname` varchar(100) DEFAULT NULL, + `catorder` int(11) DEFAULT '50', + PRIMARY KEY (`faqcat`) ) EOC -register_tablecreate("tododep", <<'EOC'); -CREATE TABLE tododep ( - todoid int(10) unsigned NOT NULL default '0', - depid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (todoid,depid), - KEY (depid) +register_tablecreate('faquses', <<'EOC'); +CREATE TABLE `faquses` ( + `faqid` mediumint(8) unsigned NOT NULL, + `userid` int(10) unsigned NOT NULL, + `dateview` datetime NOT NULL, + PRIMARY KEY (`userid`,`faqid`), + KEY `faqid` (`faqid`), + KEY `dateview` (`dateview`) ) EOC -register_tablecreate("todokeyword", <<'EOC'); -CREATE TABLE todokeyword ( - todoid int(10) unsigned NOT NULL default '0', - kwid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (todoid,kwid) +register_tablecreate('friendgroup', <<'EOC'); +CREATE TABLE `friendgroup` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `groupnum` tinyint(3) unsigned NOT NULL DEFAULT '0', + `groupname` varchar(60) NOT NULL, + `sortorder` tinyint(3) unsigned NOT NULL DEFAULT '50', + `is_public` enum('0','1') NOT NULL DEFAULT '0', + PRIMARY KEY (`userid`,`groupnum`) ) EOC -register_tablecreate("txtmsg", <<'EOC'); -CREATE TABLE txtmsg ( - userid int(10) unsigned NOT NULL default '0', - provider varchar(25) default NULL, - number varchar(60) default NULL, - security enum('all','reg','friends') NOT NULL default 'all', - PRIMARY KEY (userid) +# friendgroup2 -- clustered friend groups +register_tablecreate('friendgroup2', <<'EOC'); +CREATE TABLE `friendgroup2` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `groupnum` tinyint(3) unsigned NOT NULL DEFAULT '0', + `groupname` varchar(90) NOT NULL DEFAULT '', + `sortorder` tinyint(3) unsigned NOT NULL DEFAULT '50', + `is_public` enum('0','1') NOT NULL DEFAULT '0', + PRIMARY KEY (`userid`,`groupnum`) ) EOC -register_tablecreate("user", <<'EOC'); -CREATE TABLE user ( - userid int(10) unsigned NOT NULL auto_increment, - user char(15) default NULL, - caps BIGINT UNSIGNED NOT NULL DEFAULT 0, - packed_props BIGINT UNSIGNED NOT NULL DEFAULT 0, - status char(1) NOT NULL default 'N', - statusvis char(1) NOT NULL default 'V', - statusvisdate datetime default NULL, - name char(50) default NULL, - bdate date default NULL, - themeid int(11) NOT NULL default '1', - moodthemeid int(10) unsigned NOT NULL default '1', - opt_forcemoodtheme enum('Y','N') NOT NULL default 'N', - allow_infoshow char(1) NOT NULL default 'Y', - allow_contactshow char(1) NOT NULL default 'Y', - allow_getljnews char(1) NOT NULL default 'N', - opt_showtalklinks char(1) NOT NULL default 'Y', - opt_whocanreply enum('all','reg','friends') NOT NULL default 'all', - opt_gettalkemail char(1) NOT NULL default 'Y', - opt_htmlemail enum('Y','N') NOT NULL default 'Y', - opt_mangleemail char(1) NOT NULL default 'N', - useoverrides char(1) NOT NULL default 'N', - defaultpicid int(10) unsigned default NULL, - has_bio enum('Y','N') NOT NULL default 'N', - txtmsg_status enum('none','on','off') NOT NULL default 'none', - is_system enum('Y','N') NOT NULL default 'N', - journaltype char(1) NOT NULL default 'P', - lang char(2) NOT NULL default 'EN', - PRIMARY KEY (userid), - UNIQUE KEY user (user), - KEY (status), - KEY (statusvis) -) PACK_KEYS=1 +## Queue of delayed Befriending/Defriending events +register_tablecreate('friending_actions_q', <<'EOC'); +CREATE TABLE `friending_actions_q` ( + `rec_id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `userid` int(10) unsigned NOT NULL, + `friendid` int(10) unsigned NOT NULL, + `action` char(1) DEFAULT NULL, + `etime` int(11) DEFAULT NULL, + `jobid` bigint(20) unsigned DEFAULT NULL, + PRIMARY KEY (`rec_id`), + KEY `userid` (`userid`) +) EOC -if ( column_type('user', 'caps') =~ /smallint/i ) { - do_alter('user', qq{ - ALTER TABLE user - DROP COLUMN email, - DROP COLUMN password, - MODIFY COLUMN caps BIGINT UNSIGNED NOT NULL DEFAULT 0, - ADD COLUMN packed_props BIGINT UNSIGNED NOT NULL DEFAULT 0 - }); -} +register_tablecreate('friends', <<'EOC'); +CREATE TABLE `friends` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `friendid` int(10) unsigned NOT NULL DEFAULT '0', + `fgcolor` mediumint(8) unsigned NOT NULL DEFAULT '0', + `bgcolor` mediumint(8) unsigned NOT NULL DEFAULT '16777215', + `groupmask` int(10) unsigned NOT NULL DEFAULT '1', + `showbydefault` enum('1','0') NOT NULL DEFAULT '1', + PRIMARY KEY (`userid`,`friendid`), + KEY `friendid` (`friendid`) +) +EOC -register_tablecreate("userbio", <<'EOC'); -CREATE TABLE userbio ( - userid int(10) unsigned NOT NULL default '0', - bio text, - PRIMARY KEY (userid) +# partitioned: ESN subscriptions: flag on event target (a journal) saying +# whether there are known listeners out there. +# +# verifytime is unixtime we last checked that this has_subs caching row +# is still accurate and people do in fact still subscribe to this. +# then maintenance tasks can background prune this table and fix +# up verifytimes. +register_tablecreate('has_subs', <<'EOC'); +CREATE TABLE `has_subs` ( + `journalid` int(10) unsigned NOT NULL, + `etypeid` int(10) unsigned NOT NULL, + `arg1` int(10) unsigned NOT NULL, + `arg2` int(10) unsigned NOT NULL, + `verifytime` int(10) unsigned NOT NULL, + PRIMARY KEY (`journalid`,`etypeid`,`arg1`,`arg2`) ) EOC -register_tablecreate("userinterests", <<'EOC'); -CREATE TABLE userinterests ( - userid int(10) unsigned NOT NULL default '0', - intid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (userid,intid), - KEY (intid) +# external identities +# +# idtype ::= +# "O" - OpenID +# "L" - LID (netmesh) +# "T" - TypeKey +# ? - etc +register_tablecreate('identitymap', <<'EOC'); +CREATE TABLE `identitymap` ( + `idtype` char(1) NOT NULL, + `identity` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `userid` int(10) unsigned NOT NULL, + PRIMARY KEY (`idtype`,`identity`), + KEY `userid` (`userid`) ) EOC -register_tablecreate("userpic", <<'EOC'); -CREATE TABLE userpic ( - picid int(10) unsigned NOT NULL auto_increment, - userid int(10) unsigned NOT NULL default '0', - contenttype char(25) default NULL, - width smallint(6) NOT NULL default '0', - height smallint(6) NOT NULL default '0', - state char(1) NOT NULL default 'N', - picdate datetime default NULL, - md5base64 char(22) NOT NULL default '', - PRIMARY KEY (picid), - KEY (userid), - KEY (state) +register_tablecreate('includetext', <<'EOC'); +CREATE TABLE `includetext` ( + `incname` varchar(80) NOT NULL, + `inctext` mediumtext, + `updatetime` int(10) unsigned NOT NULL, + PRIMARY KEY (`incname`), + KEY `updatetime` (`updatetime`) ) EOC -register_tablecreate("userpicblob2", <<'EOC'); -CREATE TABLE userpicblob2 ( - userid int unsigned not null, - picid int unsigned not null, - imagedata blob, - PRIMARY KEY (userid, picid) -) max_rows=10000000 +register_tablecreate('incoming_email_handle', <<'EOC'); +CREATE TABLE `incoming_email_handle` ( + `ieid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `timerecv` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`ieid`) +) EOC -register_tablecreate("userpicmap", <<'EOC'); -CREATE TABLE userpicmap ( - userid int(10) unsigned NOT NULL default '0', - kwid int(10) unsigned NOT NULL default '0', - picid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (userid,kwid) +register_tablecreate('infohistory', <<'EOC'); +CREATE TABLE `infohistory` ( + `userid` int(10) unsigned NOT NULL DEFAULT '0', + `what` varchar(15) NOT NULL DEFAULT '', + `timechange` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `oldvalue` varchar(255) DEFAULT NULL, + `other` varchar(30) DEFAULT NULL, + KEY `userid` (`userid`) ) EOC -register_tablecreate("userpicmap2", <<'EOC'); -CREATE TABLE userpicmap2 ( - userid int(10) unsigned NOT NULL default '0', - kwid int(10) unsigned NOT NULL default '0', - picid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (userid, kwid) +register_tablecreate('interests', <<'EOC'); +CREATE TABLE `interests` ( + `intid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `interest` varchar(255) NOT NULL DEFAULT '', + `intcount` mediumint(8) unsigned DEFAULT NULL, + PRIMARY KEY (`intid`), + UNIQUE KEY `interest` (`interest`) ) EOC -register_tablecreate("userpic2", <<'EOC'); -CREATE TABLE userpic2 ( - picid int(10) unsigned NOT NULL, - userid int(10) unsigned NOT NULL default '0', - fmt char(1) default NULL, - width smallint(6) NOT NULL default '0', - height smallint(6) NOT NULL default '0', - state char(1) NOT NULL default 'N', - picdate datetime default NULL, - md5base64 char(22) NOT NULL default '', - comment varchar(255) BINARY NOT NULL default '', - flags tinyint(1) unsigned NOT NULL default 0, - location enum('blob','disk','mogile') default NULL, - PRIMARY KEY (userid, picid) +# inviterecv -- stores community invitations received +register_tablecreate('inviterecv', <<'EOC'); +CREATE TABLE `inviterecv` ( + `userid` int(10) unsigned NOT NULL, + `commid` int(10) unsigned NOT NULL, + `maintid` int(10) unsigned NOT NULL, + `recvtime` int(10) unsigned NOT NULL, + `args` varchar(255) DEFAULT NULL, + PRIMARY KEY (`userid`,`commid`) ) EOC -# - blobids aren't necessarily unique between domains; -# global userpicids may collide with the counter used for the rest. -# so type must be in the key. -# - domain ids are set up in ljconfig.pl. -# - NULL length indicates the data is external-- we need another -# table for more data for that. -register_tablecreate("userblob", <<'EOC'); # clustered -CREATE TABLE userblob ( - journalid INT UNSIGNED NOT NULL, - domain TINYINT UNSIGNED NOT NULL, - blobid MEDIUMINT UNSIGNED NOT NULL, - length MEDIUMINT UNSIGNED, - PRIMARY KEY (journalid, domain, blobid), - KEY (domain) +# invitesent -- stores community invitations sent +register_tablecreate('invitesent', <<'EOC'); +CREATE TABLE `invitesent` ( + `commid` int(10) unsigned NOT NULL, + `userid` int(10) unsigned NOT NULL, + `maintid` int(10) unsigned NOT NULL, + `recvtime` int(10) unsigned NOT NULL, + `status` enum('accepted','rejected','outstanding') NOT NULL, + `args` varchar(255) DEFAULT NULL, + PRIMARY KEY (`commid`,`userid`) ) EOC -register_tablecreate("userproplist", <<'EOC'); -CREATE TABLE userproplist ( - upropid smallint(5) unsigned NOT NULL auto_increment, - name varchar(50) default NULL, - indexed enum('1','0') NOT NULL default '1', - prettyname varchar(60) default NULL, - datatype enum('char','num','bool') NOT NULL default 'char', - des varchar(255) default NULL, - PRIMARY KEY (upropid), - UNIQUE KEY name (name) +register_tablecreate('jabcluster', <<'EOC'); +CREATE TABLE `jabcluster` ( + `clusterid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `address` varchar(255) NOT NULL, + PRIMARY KEY (`clusterid`) ) EOC -# global, indexed -register_tablecreate("userprop", <<'EOC'); -CREATE TABLE userprop ( - userid int(10) unsigned NOT NULL default '0', - upropid smallint(5) unsigned NOT NULL default '0', - value varchar(60) default NULL, - PRIMARY KEY (userid,upropid), - KEY (upropid,value) +register_tablecreate('jablastseen', <<'EOC'); +CREATE TABLE `jablastseen` ( + `userid` int(10) unsigned NOT NULL, + `presence` blob, + `time` int(10) unsigned NOT NULL, + `motd_ver` int(10) unsigned DEFAULT NULL, + PRIMARY KEY (`userid`) ) EOC -# global, not indexed -register_tablecreate("userproplite", <<'EOC'); -CREATE TABLE userproplite ( - userid int(10) unsigned NOT NULL default '0', - upropid smallint(5) unsigned NOT NULL default '0', - value varchar(255) default NULL, - PRIMARY KEY (userid,upropid), - KEY (upropid) +register_tablecreate('jabpresence', <<'EOC'); +CREATE TABLE `jabpresence` ( + `userid` int(10) unsigned NOT NULL, + `reshash` char(22) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + `resource` varchar(255) NOT NULL, + `client` varchar(255) DEFAULT NULL, + `clusterid` int(10) unsigned NOT NULL, + `presence` blob, + `flags` int(10) unsigned NOT NULL, + `priority` int(10) unsigned DEFAULT NULL, + `ctime` int(10) unsigned NOT NULL, + `mtime` int(10) unsigned NOT NULL, + `remoteip` varchar(255) DEFAULT NULL, + PRIMARY KEY (`userid`,`reshash`) ) EOC -# clustered, not indexed -register_tablecreate("userproplite2", <<'EOC'); -CREATE TABLE userproplite2 ( - userid int(10) unsigned NOT NULL default '0', - upropid smallint(5) unsigned NOT NULL default '0', - value varchar(255) default NULL, - PRIMARY KEY (userid,upropid), - KEY (upropid) +register_tablecreate('jabroster', <<'EOC'); +CREATE TABLE `jabroster` ( + `userid` int(10) unsigned NOT NULL, + `contactid` int(10) unsigned NOT NULL, + `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `substate` tinyint(3) unsigned NOT NULL, + `groups` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ljflags` tinyint(3) unsigned NOT NULL, + PRIMARY KEY (`userid`,`contactid`) ) EOC -# clustered -register_tablecreate("userpropblob", <<'EOC'); -CREATE TABLE userpropblob ( - userid INT(10) unsigned NOT NULL default '0', - upropid SMALLINT(5) unsigned NOT NULL default '0', - value blob, - PRIMARY KEY (userid,upropid) +register_tablecreate('jobstatus', <<'EOC'); +CREATE TABLE `jobstatus` ( + `handle` varchar(100) NOT NULL, + `result` blob, + `start_time` int(10) unsigned NOT NULL, + `end_time` int(10) unsigned NOT NULL, + `status` enum('running','success','error') DEFAULT NULL, + `userid` int(10) unsigned DEFAULT NULL, + PRIMARY KEY (`handle`), + KEY `end_time` (`end_time`) ) EOC -register_tablecreate("backupdirty", <<'EOC'); -CREATE TABLE backupdirty ( - userid INT(10) unsigned NOT NULL default '0', - marktime INT(10) unsigned NOT NULL default '0', - PRIMARY KEY (userid) +register_tablecreate('keywords', <<'EOC'); +CREATE TABLE `keywords` ( + `kwid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `keyword` varchar(80) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', + PRIMARY KEY (`kwid`), + UNIQUE KEY `kwidx` (`keyword`) ) EOC -register_tablecreate("zip", <<'EOC'); -CREATE TABLE zip ( - zip varchar(5) NOT NULL default '', - state char(2) NOT NULL default '', - city varchar(100) NOT NULL default '', - PRIMARY KEY (zip), - KEY (state) -) PACK_KEYS=1 +register_tablecreate('knob', <<'EOC'); +CREATE TABLE `knob` ( + `knobname` varchar(255) NOT NULL, + `val` tinyint(3) unsigned DEFAULT NULL, + PRIMARY KEY (`knobname`) +) EOC -register_tablecreate("zips", <<'EOC'); -CREATE TABLE zips ( - FIPS char(2) default NULL, - zip varchar(5) NOT NULL default '', - State char(2) NOT NULL default '', - Name varchar(30) NOT NULL default '', - alloc float(9,7) NOT NULL default '0.0000000', - pop1990 int(11) NOT NULL default '0', - lon float(10,7) NOT NULL default '0.0000000', - lat float(10,7) NOT NULL default '0.0000000', - PRIMARY KEY (zip) +register_tablecreate('links', <<'EOC'); +CREATE TABLE `links` ( + `journalid` int(10) unsigned NOT NULL DEFAULT '0', + `ordernum` tinyint(4) unsigned NOT NULL DEFAULT '0', + `parentnum` tinyint(4) unsigned NOT NULL DEFAULT '0', + `url` varchar(255) DEFAULT NULL, + `title` varchar(255) NOT NULL DEFAULT '', + KEY `journalid` (`journalid`) ) EOC -################# above was a snapshot. now, changes: - -register_tablecreate("log2", <<'EOC'); -CREATE TABLE log2 ( - journalid INT UNSIGNED NOT NULL default '0', - jitemid MEDIUMINT UNSIGNED NOT NULL, - PRIMARY KEY (journalid, jitemid), - posterid int(10) unsigned NOT NULL default '0', - eventtime datetime default NULL, - logtime datetime default NULL, - compressed char(1) NOT NULL default 'N', - anum TINYINT UNSIGNED NOT NULL, - security enum('public','private','usemask') NOT NULL default 'public', - allowmask int(10) unsigned NOT NULL default '0', - replycount smallint(5) unsigned default NULL, - year smallint(6) NOT NULL default '0', - month tinyint(4) NOT NULL default '0', - day tinyint(4) NOT NULL default '0', - rlogtime int(10) unsigned NOT NULL default '0', - revttime int(10) unsigned NOT NULL default '0', - KEY (journalid,year,month,day), +register_tablecreate('log2', <<'EOC'); +CREATE TABLE `log2` ( + `journalid` int(10) unsigned NOT NULL DEFAULT '0', + `jitemid` mediumint(8) unsigned NOT NULL, + `posterid` int(10) unsigned NOT NULL DEFAULT '0', + `eventtime` datetime DEFAULT NULL, + `logtime` datetime DEFAULT NULL, + `compressed` char(1) NOT NULL DEFAULT 'N', + `anum` tinyint(3) unsigned NOT NULL, + `security` enum('public','private','usemask') NOT NULL DEFAULT 'public', + `allowmask` int(10) unsigned NOT NULL DEFAULT '0', + `replycount` smallint(5) unsigned DEFAULT NULL, + `year` smallint(6) NOT NULL DEFAULT '0', + `month` tinyint(4) NOT NULL DEFAULT '0', + `day` tinyint(4) NOT NULL DEFAULT '0', + `rlogtime` int(10) unsigned NOT NULL DEFAULT '0', + `revttime` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`journalid`,`jitemid`), + KEY `journalid` (`journalid`,`year`,`month`,`day`), KEY `rlogtime` (`journalid`,`rlogtime`), KEY `revttime` (`journalid`,`revttime`), KEY `posterid` (`posterid`,`journalid`) ) EOC -register_tablecreate("logtext2", <<'EOC'); -CREATE TABLE logtext2 ( - journalid INT UNSIGNED NOT NULL, - jitemid MEDIUMINT UNSIGNED NOT NULL, - subject VARCHAR(255) DEFAULT NULL, - event TEXT, - PRIMARY KEY (journalid, jitemid) -) max_rows=100000000 +register_tablecreate('loginlog', <<'EOC'); +CREATE TABLE `loginlog` ( + `userid` int(10) unsigned NOT NULL, + `logintime` int(10) unsigned NOT NULL, + `sessid` mediumint(8) unsigned NOT NULL, + `ip` varchar(15) DEFAULT NULL, + `ua` varchar(100) DEFAULT NULL, + KEY `userid` (`userid`,`logintime`) +) EOC -register_tablecreate("logprop2", <<'EOC'); -CREATE TABLE logprop2 ( - journalid INT UNSIGNED NOT NULL, - jitemid MEDIUMINT UNSIGNED NOT NULL, - propid TINYINT unsigned NOT NULL, - value VARCHAR(255) default NULL, - PRIMARY KEY (journalid,jitemid,propid) +register_tablecreate('loginstall', <<'EOC'); +CREATE TABLE `loginstall` ( + `userid` int(10) unsigned NOT NULL, + `ip` int(10) unsigned NOT NULL, + `time` int(10) unsigned NOT NULL, + UNIQUE KEY `userid` (`userid`,`ip`) ) EOC -register_tablecreate("logsec2", <<'EOC'); -CREATE TABLE logsec2 ( - journalid INT UNSIGNED NOT NULL, - jitemid MEDIUMINT UNSIGNED NOT NULL, - allowmask INT UNSIGNED NOT NULL, - PRIMARY KEY (journalid,jitemid) +# summary counts for security on entry keywords +register_tablecreate('logkwsum', <<'EOC'); +CREATE TABLE `logkwsum` ( + `journalid` int(10) unsigned NOT NULL, + `kwid` int(10) unsigned NOT NULL, + `security` int(10) unsigned NOT NULL, + `entryct` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`journalid`,`kwid`,`security`), + KEY `journalid` (`journalid`,`security`) ) EOC -register_tablecreate("talk2", <<'EOC'); -CREATE TABLE talk2 ( - journalid INT UNSIGNED NOT NULL, - jtalkid INT UNSIGNED NOT NULL, - nodetype CHAR(1) NOT NULL DEFAULT '', - nodeid INT UNSIGNED NOT NULL default '0', - parenttalkid INT UNSIGNED NOT NULL, - posterid INT UNSIGNED NOT NULL default '0', - datepost DATETIME NOT NULL default '0000-00-00 00:00:00', - state CHAR(1) default 'A', - PRIMARY KEY (journalid,jtalkid), - KEY (nodetype,journalid,nodeid), - KEY (journalid,state,nodetype), - KEY (posterid) +register_tablecreate('logprop2', <<'EOC'); +CREATE TABLE `logprop2` ( + `journalid` int(10) unsigned NOT NULL, + `jitemid` mediumint(8) unsigned NOT NULL, + `propid` tinyint(3) unsigned NOT NULL, + `value` varchar(255) DEFAULT NULL, + PRIMARY KEY (`journalid`,`jitemid`,`propid`) ) EOC -register_tablecreate("talkprop2", <<'EOC'); -CREATE TABLE talkprop2 ( - journalid INT UNSIGNED NOT NULL, - jtalkid INT UNSIGNED NOT NULL, - tpropid TINYINT UNSIGNED NOT NULL, - value VARCHAR(255) DEFAULT NULL, - PRIMARY KEY (journalid,jtalkid,tpropid) +register_tablecreate('logprop_history', <<'EOC'); +CREATE TABLE `logprop_history` ( + `journalid` int(10) unsigned NOT NULL, + `jitemid` mediumint(8) unsigned NOT NULL, + `propid` tinyint(3) unsigned NOT NULL, + `change_time` int(10) unsigned NOT NULL DEFAULT '0', + `old_value` varchar(255) DEFAULT NULL, + `new_value` varchar(255) DEFAULT NULL, + `note` varchar(255) DEFAULT NULL, + KEY `journalid` (`journalid`,`jitemid`,`propid`) ) EOC -register_tablecreate("talktext2", <<'EOC'); -CREATE TABLE talktext2 ( - journalid INT UNSIGNED NOT NULL, - jtalkid INT UNSIGNED NOT NULL, - subject VARCHAR(100) DEFAULT NULL, - body TEXT, - PRIMARY KEY (journalid, jtalkid) -) max_rows=100000000 +register_tablecreate('logproplist', <<'EOC'); +CREATE TABLE `logproplist` ( + `propid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(50) DEFAULT NULL, + `prettyname` varchar(60) DEFAULT NULL, + `sortorder` mediumint(8) unsigned DEFAULT NULL, + `datatype` enum('char','num','bool') NOT NULL DEFAULT 'char', + `des` varchar(255) DEFAULT NULL, + `scope` enum('general','local') NOT NULL DEFAULT 'general', + PRIMARY KEY (`propid`), + UNIQUE KEY `name` (`name`) +) EOC -register_tablecreate("talkleft", <<'EOC'); -CREATE TABLE talkleft ( - userid INT UNSIGNED NOT NULL, - posttime INT UNSIGNED NOT NULL, - INDEX (userid, posttime), - journalid INT UNSIGNED NOT NULL, - ... (truncated)