X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=69da511b0348763e279a5ca60ce1b788b5204779;hb=05b6891e89c52f325aaa910f853938b39bf8129e;hp=8269a8bba086e80c68479eb3a439cdf4f43e6a53;hpb=0f8148a4cb7c477b363cccdb5c638cd10d9df531;p=friendica.git diff --git a/database.sql b/database.sql index 8269a8bba0..69da511b03 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 3.5-dev (Asparagus) --- DB_UPDATE_VERSION 1196 +-- Friendica 3.5.2-dev (Asparagus) +-- DB_UPDATE_VERSION 1219 -- ------------------------------------------ @@ -9,14 +9,15 @@ -- CREATE TABLE IF NOT EXISTS `addon` ( `id` int(11) NOT NULL auto_increment, - `name` varchar(255) NOT NULL DEFAULT '', + `name` varchar(190) NOT NULL DEFAULT '', `version` varchar(255) NOT NULL DEFAULT '', `installed` tinyint(1) NOT NULL DEFAULT 0, `hidden` tinyint(1) NOT NULL DEFAULT 0, `timestamp` bigint(20) NOT NULL DEFAULT 0, `plugin_admin` tinyint(1) NOT NULL DEFAULT 0, - PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; + PRIMARY KEY(`id`), + UNIQUE INDEX `name` (`name`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE attach @@ -29,14 +30,14 @@ CREATE TABLE IF NOT EXISTS `attach` ( `filetype` varchar(64) NOT NULL DEFAULT '', `filesize` int(11) NOT NULL DEFAULT 0, `data` longblob NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `allow_cid` mediumtext NOT NULL, - `allow_gid` mediumtext NOT NULL, - `deny_cid` mediumtext NOT NULL, - `deny_gid` mediumtext NOT NULL, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `allow_cid` mediumtext, + `allow_gid` mediumtext, + `deny_cid` mediumtext, + `deny_gid` mediumtext, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE auth_codes @@ -48,19 +49,19 @@ CREATE TABLE IF NOT EXISTS `auth_codes` ( `expires` int(11) NOT NULL DEFAULT 0, `scope` varchar(250) NOT NULL DEFAULT '', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE cache -- CREATE TABLE IF NOT EXISTS `cache` ( - `k` varchar(255) NOT NULL, - `v` text NOT NULL, + `k` varbinary(255) NOT NULL, + `v` mediumtext, `expire_mode` int(11) NOT NULL DEFAULT 0, - `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`k`), - INDEX `updated` (`updated`) -) DEFAULT CHARSET=utf8; + INDEX `expire_mode_updated` (`expire_mode`,`updated`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE challenge @@ -73,7 +74,7 @@ CREATE TABLE IF NOT EXISTS `challenge` ( `type` varchar(255) NOT NULL DEFAULT '', `last_update` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE clients @@ -86,19 +87,19 @@ CREATE TABLE IF NOT EXISTS `clients` ( `icon` text, `uid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`client_id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE config -- CREATE TABLE IF NOT EXISTS `config` ( `id` int(10) unsigned NOT NULL auto_increment, - `cat` varchar(255) NOT NULL DEFAULT '', - `k` varchar(255) NOT NULL DEFAULT '', - `v` text NOT NULL, + `cat` varbinary(255) NOT NULL DEFAULT '', + `k` varbinary(255) NOT NULL DEFAULT '', + `v` mediumtext, PRIMARY KEY(`id`), - INDEX `cat_k` (`cat`(30),`k`(30)) -) DEFAULT CHARSET=utf8; + UNIQUE INDEX `cat_k` (`cat`,`k`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE contact @@ -106,7 +107,7 @@ CREATE TABLE IF NOT EXISTS `config` ( CREATE TABLE IF NOT EXISTS `contact` ( `id` int(11) NOT NULL auto_increment, `uid` int(11) NOT NULL DEFAULT 0, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `self` tinyint(1) NOT NULL DEFAULT 0, `remote_self` tinyint(1) NOT NULL DEFAULT 0, `rel` tinyint(1) NOT NULL DEFAULT 0, @@ -115,65 +116,77 @@ CREATE TABLE IF NOT EXISTS `contact` ( `name` varchar(255) NOT NULL DEFAULT '', `nick` varchar(255) NOT NULL DEFAULT '', `location` varchar(255) NOT NULL DEFAULT '', - `about` text NOT NULL, - `keywords` text NOT NULL, + `about` text, + `keywords` text, `gender` varchar(32) NOT NULL DEFAULT '', + `xmpp` varchar(255) NOT NULL DEFAULT '', `attag` varchar(255) NOT NULL DEFAULT '', `avatar` varchar(255) NOT NULL DEFAULT '', - `photo` text NOT NULL, - `thumb` text NOT NULL, - `micro` text NOT NULL, - `site-pubkey` text NOT NULL, + `photo` text, + `thumb` text, + `micro` text, + `site-pubkey` text, `issued-id` varchar(255) NOT NULL DEFAULT '', `dfrn-id` varchar(255) NOT NULL DEFAULT '', `url` varchar(255) NOT NULL DEFAULT '', `nurl` varchar(255) NOT NULL DEFAULT '', `addr` varchar(255) NOT NULL DEFAULT '', `alias` varchar(255) NOT NULL DEFAULT '', - `pubkey` text NOT NULL, - `prvkey` text NOT NULL, + `pubkey` text, + `prvkey` text, `batch` varchar(255) NOT NULL DEFAULT '', - `request` text NOT NULL, - `notify` text NOT NULL, - `poll` text NOT NULL, - `confirm` text NOT NULL, - `poco` text NOT NULL, + `request` text, + `notify` text, + `poll` text, + `confirm` text, + `poco` text, `aes_allow` tinyint(1) NOT NULL DEFAULT 0, `ret-aes` tinyint(1) NOT NULL DEFAULT 0, `usehub` tinyint(1) NOT NULL DEFAULT 0, `subhub` tinyint(1) NOT NULL DEFAULT 0, `hub-verify` varchar(255) NOT NULL DEFAULT '', - `last-update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `success_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `failure_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `name-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `uri-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `avatar-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `term-date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `last-item` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `priority` tinyint(3) NOT NULL DEFAULT 0, `blocked` tinyint(1) NOT NULL DEFAULT 1, `readonly` tinyint(1) NOT NULL DEFAULT 0, `writable` tinyint(1) NOT NULL DEFAULT 0, `forum` tinyint(1) NOT NULL DEFAULT 0, `prv` tinyint(1) NOT NULL DEFAULT 0, + `contact-type` int(11) NOT NULL DEFAULT 0, `hidden` tinyint(1) NOT NULL DEFAULT 0, `archive` tinyint(1) NOT NULL DEFAULT 0, `pending` tinyint(1) NOT NULL DEFAULT 1, `rating` tinyint(1) NOT NULL DEFAULT 0, - `reason` text NOT NULL, + `reason` text, `closeness` tinyint(2) NOT NULL DEFAULT 99, - `info` mediumtext NOT NULL, + `info` mediumtext, `profile-id` int(11) NOT NULL DEFAULT 0, `bdyear` varchar(4) NOT NULL DEFAULT '', - `bd` date NOT NULL DEFAULT '0000-00-00', + `bd` date NOT NULL DEFAULT '0001-01-01', `notify_new_posts` tinyint(1) NOT NULL DEFAULT 0, `fetch_further_information` tinyint(1) NOT NULL DEFAULT 0, - `ffi_keyword_blacklist` mediumtext NOT NULL, + `ffi_keyword_blacklist` text, PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `nurl` (`nurl`) -) DEFAULT CHARSET=utf8; + INDEX `uid_name` (`uid`,`name`), + INDEX `self_uid` (`self`,`uid`), + INDEX `alias_uid` (`alias`(32),`uid`), + INDEX `pending_uid` (`pending`,`uid`), + INDEX `blocked_uid` (`blocked`,`uid`), + INDEX `uid_rel_network_poll` (`uid`,`rel`,`network`,`poll`(64),`archive`), + INDEX `uid_network_batch` (`uid`,`network`,`batch`(64)), + INDEX `addr_uid` (`addr`(32),`uid`), + INDEX `nurl_uid` (`nurl`(32),`uid`), + INDEX `nick_uid` (`nick`(32),`uid`), + INDEX `dfrn-id` (`dfrn-id`), + INDEX `issued-id` (`issued-id`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE conv @@ -181,59 +194,50 @@ CREATE TABLE IF NOT EXISTS `contact` ( CREATE TABLE IF NOT EXISTS `conv` ( `id` int(10) unsigned NOT NULL auto_increment, `guid` varchar(64) NOT NULL DEFAULT '', - `recips` mediumtext NOT NULL, + `recips` text, `uid` int(11) NOT NULL DEFAULT 0, `creator` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `subject` mediumtext NOT NULL, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `subject` text, PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8; - --- --- TABLE deliverq --- -CREATE TABLE IF NOT EXISTS `deliverq` ( - `id` int(10) unsigned NOT NULL auto_increment, - `cmd` varchar(32) NOT NULL DEFAULT '', - `item` int(11) NOT NULL DEFAULT 0, - `contact` int(11) NOT NULL DEFAULT 0, - PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE event -- CREATE TABLE IF NOT EXISTS `event` ( `id` int(11) NOT NULL auto_increment, + `guid` varchar(255) NOT NULL DEFAULT '', `uid` int(11) NOT NULL DEFAULT 0, `cid` int(11) NOT NULL DEFAULT 0, `uri` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `finish` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `summary` text NOT NULL, - `desc` text NOT NULL, - `location` text NOT NULL, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `start` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `finish` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `summary` text, + `desc` text, + `location` text, `type` varchar(255) NOT NULL DEFAULT '', `nofinish` tinyint(1) NOT NULL DEFAULT 0, `adjust` tinyint(1) NOT NULL DEFAULT 1, `ignore` tinyint(1) unsigned NOT NULL DEFAULT 0, - `allow_cid` mediumtext NOT NULL, - `allow_gid` mediumtext NOT NULL, - `deny_cid` mediumtext NOT NULL, - `deny_gid` mediumtext NOT NULL, + `allow_cid` mediumtext, + `allow_gid` mediumtext, + `deny_cid` mediumtext, + `deny_gid` mediumtext, PRIMARY KEY(`id`), - INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8; + INDEX `uid_start` (`uid`,`start`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE fcontact -- CREATE TABLE IF NOT EXISTS `fcontact` ( `id` int(10) unsigned NOT NULL auto_increment, + `guid` varchar(255) NOT NULL DEFAULT '', `url` varchar(255) NOT NULL DEFAULT '', `name` varchar(255) NOT NULL DEFAULT '', `photo` varchar(255) NOT NULL DEFAULT '', @@ -247,11 +251,12 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `priority` tinyint(1) NOT NULL DEFAULT 0, `network` varchar(32) NOT NULL DEFAULT '', `alias` varchar(255) NOT NULL DEFAULT '', - `pubkey` text NOT NULL, - `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `pubkey` text, + `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`), - INDEX `addr` (`addr`) -) DEFAULT CHARSET=utf8; + INDEX `addr` (`addr`(32)), + INDEX `url` (`url`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE ffinder @@ -262,7 +267,7 @@ CREATE TABLE IF NOT EXISTS `ffinder` ( `cid` int(10) unsigned NOT NULL DEFAULT 0, `fid` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE fserver @@ -271,10 +276,10 @@ CREATE TABLE IF NOT EXISTS `fserver` ( `id` int(11) NOT NULL auto_increment, `server` varchar(255) NOT NULL DEFAULT '', `posturl` varchar(255) NOT NULL DEFAULT '', - `key` text NOT NULL, + `key` text, PRIMARY KEY(`id`), - INDEX `server` (`server`) -) DEFAULT CHARSET=utf8; + INDEX `server` (`server`(32)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE fsuggest @@ -287,10 +292,10 @@ CREATE TABLE IF NOT EXISTS `fsuggest` ( `url` varchar(255) NOT NULL DEFAULT '', `request` varchar(255) NOT NULL DEFAULT '', `photo` varchar(255) NOT NULL DEFAULT '', - `note` text NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `note` text, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE gcign @@ -302,7 +307,7 @@ CREATE TABLE IF NOT EXISTS `gcign` ( PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `gcid` (`gcid`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE gcontact @@ -315,31 +320,33 @@ CREATE TABLE IF NOT EXISTS `gcontact` ( `nurl` varchar(255) NOT NULL DEFAULT '', `photo` varchar(255) NOT NULL DEFAULT '', `connect` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `updated` datetime DEFAULT '0000-00-00 00:00:00', - `last_contact` datetime DEFAULT '0000-00-00 00:00:00', - `last_failure` datetime DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `updated` datetime DEFAULT '0001-01-01 00:00:00', + `last_contact` datetime DEFAULT '0001-01-01 00:00:00', + `last_failure` datetime DEFAULT '0001-01-01 00:00:00', `location` varchar(255) NOT NULL DEFAULT '', - `about` text NOT NULL, - `keywords` text NOT NULL, + `about` text, + `keywords` text, `gender` varchar(32) NOT NULL DEFAULT '', - `birthday` varchar(32) NOT NULL DEFAULT '0000-00-00', + `birthday` varchar(32) NOT NULL DEFAULT '0001-01-01', `community` tinyint(1) NOT NULL DEFAULT 0, + `contact-type` tinyint(1) NOT NULL DEFAULT -1, `hide` tinyint(1) NOT NULL DEFAULT 0, `nsfw` tinyint(1) NOT NULL DEFAULT 0, `network` varchar(255) NOT NULL DEFAULT '', `addr` varchar(255) NOT NULL DEFAULT '', - `notify` text NOT NULL, + `notify` text, `alias` varchar(255) NOT NULL DEFAULT '', `generation` tinyint(3) NOT NULL DEFAULT 0, `server_url` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), - INDEX `nurl` (`nurl`), - INDEX `name` (`name`), - INDEX `nick` (`nick`), - INDEX `addr` (`addr`), + INDEX `nurl` (`nurl`(64)), + INDEX `name` (`name`(64)), + INDEX `nick` (`nick`(32)), + INDEX `addr` (`addr`(64)), + INDEX `hide_network_updated` (`hide`,`network`,`updated`), INDEX `updated` (`updated`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE glink @@ -350,12 +357,11 @@ CREATE TABLE IF NOT EXISTS `glink` ( `uid` int(11) NOT NULL DEFAULT 0, `gcid` int(11) NOT NULL DEFAULT 0, `zcid` int(11) NOT NULL DEFAULT 0, - `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`), - INDEX `cid_uid_gcid_zcid` (`cid`,`uid`,`gcid`,`zcid`), - INDEX `gcid` (`gcid`), - INDEX `zcid` (`zcid`) -) DEFAULT CHARSET=utf8; + UNIQUE INDEX `cid_uid_gcid_zcid` (`cid`,`uid`,`gcid`,`zcid`), + INDEX `gcid` (`gcid`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE group @@ -368,7 +374,7 @@ CREATE TABLE IF NOT EXISTS `group` ( `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE group_member @@ -379,8 +385,10 @@ CREATE TABLE IF NOT EXISTS `group_member` ( `gid` int(10) unsigned NOT NULL DEFAULT 0, `contact-id` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`), - INDEX `uid_gid_contactid` (`uid`,`gid`,`contact-id`) -) DEFAULT CHARSET=utf8; + INDEX `contactid` (`contact-id`), + INDEX `gid_contactid` (`gid`,`contact-id`), + UNIQUE INDEX `uid_gid_contactid` (`uid`,`gid`,`contact-id`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE gserver @@ -391,19 +399,19 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `nurl` varchar(255) NOT NULL DEFAULT '', `version` varchar(255) NOT NULL DEFAULT '', `site_name` varchar(255) NOT NULL DEFAULT '', - `info` text NOT NULL, + `info` text, `register_policy` tinyint(1) NOT NULL DEFAULT 0, `poco` varchar(255) NOT NULL DEFAULT '', `noscrape` varchar(255) NOT NULL DEFAULT '', `network` varchar(32) NOT NULL DEFAULT '', `platform` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `last_poco_query` datetime DEFAULT '0000-00-00 00:00:00', - `last_contact` datetime DEFAULT '0000-00-00 00:00:00', - `last_failure` datetime DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `last_poco_query` datetime DEFAULT '0001-01-01 00:00:00', + `last_contact` datetime DEFAULT '0001-01-01 00:00:00', + `last_failure` datetime DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`), - INDEX `nurl` (`nurl`) -) DEFAULT CHARSET=utf8; + INDEX `nurl` (`nurl`(32)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE hook @@ -415,8 +423,8 @@ CREATE TABLE IF NOT EXISTS `hook` ( `function` varchar(255) NOT NULL DEFAULT '', `priority` int(11) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`), - INDEX `hook_file_function` (`hook`(30),`file`(60),`function`(30)) -) DEFAULT CHARSET=utf8; + UNIQUE INDEX `hook_file_function` (`hook`(50),`file`(80),`function`(60)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE intro @@ -428,13 +436,13 @@ CREATE TABLE IF NOT EXISTS `intro` ( `contact-id` int(11) NOT NULL DEFAULT 0, `knowyou` tinyint(1) NOT NULL DEFAULT 0, `duplex` tinyint(1) NOT NULL DEFAULT 0, - `note` text NOT NULL, + `note` text, `hash` varchar(255) NOT NULL DEFAULT '', - `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `datetime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `blocked` tinyint(1) NOT NULL DEFAULT 1, `ignore` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE item @@ -453,39 +461,41 @@ CREATE TABLE IF NOT EXISTS `item` ( `parent-uri` varchar(255) NOT NULL DEFAULT '', `extid` varchar(255) NOT NULL DEFAULT '', `thr-parent` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `commented` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `received` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `owner-id` int(11) NOT NULL DEFAULT 0, `owner-name` varchar(255) NOT NULL DEFAULT '', `owner-link` varchar(255) NOT NULL DEFAULT '', `owner-avatar` varchar(255) NOT NULL DEFAULT '', + `author-id` int(11) NOT NULL DEFAULT 0, `author-name` varchar(255) NOT NULL DEFAULT '', `author-link` varchar(255) NOT NULL DEFAULT '', `author-avatar` varchar(255) NOT NULL DEFAULT '', `title` varchar(255) NOT NULL DEFAULT '', - `body` mediumtext NOT NULL, + `body` mediumtext, `app` varchar(255) NOT NULL DEFAULT '', `verb` varchar(255) NOT NULL DEFAULT '', `object-type` varchar(255) NOT NULL DEFAULT '', - `object` text NOT NULL, + `object` text, `target-type` varchar(255) NOT NULL DEFAULT '', - `target` text NOT NULL, - `postopts` text NOT NULL, + `target` text, + `postopts` text, `plink` varchar(255) NOT NULL DEFAULT '', `resource-id` varchar(255) NOT NULL DEFAULT '', `event-id` int(11) NOT NULL DEFAULT 0, - `tag` mediumtext NOT NULL, - `attach` mediumtext NOT NULL, - `inform` mediumtext NOT NULL, - `file` mediumtext NOT NULL, + `tag` mediumtext, + `attach` mediumtext, + `inform` mediumtext, + `file` mediumtext, `location` varchar(255) NOT NULL DEFAULT '', `coord` varchar(255) NOT NULL DEFAULT '', - `allow_cid` mediumtext NOT NULL, - `allow_gid` mediumtext NOT NULL, - `deny_cid` mediumtext NOT NULL, - `deny_gid` mediumtext NOT NULL, + `allow_cid` mediumtext, + `allow_gid` mediumtext, + `deny_cid` mediumtext, + `deny_gid` mediumtext, `private` tinyint(1) NOT NULL DEFAULT 0, `pubmail` tinyint(1) NOT NULL DEFAULT 0, `moderated` tinyint(1) NOT NULL DEFAULT 0, @@ -501,7 +511,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `mention` tinyint(1) NOT NULL DEFAULT 0, `network` varchar(32) NOT NULL DEFAULT '', `rendered-hash` varchar(32) NOT NULL DEFAULT '', - `rendered-html` mediumtext NOT NULL, + `rendered-html` mediumtext, `global` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), INDEX `guid` (`guid`), @@ -510,26 +520,18 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `parent-uri` (`parent-uri`), INDEX `extid` (`extid`), INDEX `uid_id` (`uid`,`id`), + INDEX `uid_contactid_id` (`uid`,`contact-id`,`id`), INDEX `uid_created` (`uid`,`created`), INDEX `uid_unseen_contactid` (`uid`,`unseen`,`contact-id`), INDEX `uid_network_received` (`uid`,`network`,`received`), - INDEX `uid_received` (`uid`,`received`), INDEX `uid_network_commented` (`uid`,`network`,`commented`), - INDEX `uid_commented` (`uid`,`commented`), - INDEX `uid_title` (`uid`,`title`), INDEX `uid_thrparent` (`uid`,`thr-parent`), INDEX `uid_parenturi` (`uid`,`parent-uri`), INDEX `uid_contactid_created` (`uid`,`contact-id`,`created`), - INDEX `gcontactid_uid_created` (`gcontact-id`,`uid`,`created`), - INDEX `wall_body` (`wall`,`body`(6)), - INDEX `uid_visible_moderated_created` (`uid`,`visible`,`moderated`,`created`), + INDEX `authorid_created` (`author-id`,`created`), INDEX `uid_uri` (`uid`,`uri`), - INDEX `uid_wall_created` (`uid`,`wall`,`created`), INDEX `resource-id` (`resource-id`), - INDEX `uid_type` (`uid`,`type`), - INDEX `uid_starred` (`uid`,`starred`), INDEX `contactid_allowcid_allowpid_denycid_denygid` (`contact-id`,`allow_cid`(10),`allow_gid`(10),`deny_cid`(10),`deny_gid`(10)), - INDEX `uid_wall_parent_created` (`uid`,`wall`,`parent`,`created`), INDEX `uid_type_changed` (`uid`,`type`,`changed`), INDEX `contactid_verb` (`contact-id`,`verb`), INDEX `deleted_changed` (`deleted`,`changed`), @@ -537,7 +539,7 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `uid_eventid` (`uid`,`event-id`), INDEX `uid_authorlink` (`uid`,`author-link`), INDEX `uid_ownerlink` (`uid`,`owner-link`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE item_id @@ -551,9 +553,9 @@ CREATE TABLE IF NOT EXISTS `item_id` ( PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `sid` (`sid`), - INDEX `service` (`service`), + INDEX `service` (`service`(32)), INDEX `iid` (`iid`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE locks @@ -562,9 +564,9 @@ CREATE TABLE IF NOT EXISTS `locks` ( `id` int(11) NOT NULL auto_increment, `name` varchar(128) NOT NULL DEFAULT '', `locked` tinyint(1) NOT NULL DEFAULT 0, - `created` datetime DEFAULT '0000-00-00 00:00:00', + `created` datetime DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE mail @@ -579,22 +581,20 @@ CREATE TABLE IF NOT EXISTS `mail` ( `contact-id` varchar(255) NOT NULL DEFAULT '', `convid` int(11) unsigned NOT NULL DEFAULT 0, `title` varchar(255) NOT NULL DEFAULT '', - `body` mediumtext NOT NULL, + `body` mediumtext, `seen` tinyint(1) NOT NULL DEFAULT 0, `reply` tinyint(1) NOT NULL DEFAULT 0, `replied` tinyint(1) NOT NULL DEFAULT 0, `unknown` tinyint(1) NOT NULL DEFAULT 0, `uri` varchar(255) NOT NULL DEFAULT '', `parent-uri` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `guid` (`guid`), + INDEX `uid_seen` (`uid`,`seen`), INDEX `convid` (`convid`), - INDEX `reply` (`reply`), - INDEX `uri` (`uri`), - INDEX `parent-uri` (`parent-uri`) -) DEFAULT CHARSET=utf8; + INDEX `uri` (`uri`(64)), + INDEX `parent-uri` (`parent-uri`(64)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE mailacct @@ -607,14 +607,14 @@ CREATE TABLE IF NOT EXISTS `mailacct` ( `ssltype` varchar(16) NOT NULL DEFAULT '', `mailbox` varchar(255) NOT NULL DEFAULT '', `user` varchar(255) NOT NULL DEFAULT '', - `pass` text NOT NULL, + `pass` text, `reply_to` varchar(255) NOT NULL DEFAULT '', `action` int(11) NOT NULL DEFAULT 0, `movetofolder` varchar(255) NOT NULL DEFAULT '', `pubmail` tinyint(1) NOT NULL DEFAULT 0, - `last_check` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `last_check` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE manage @@ -624,8 +624,8 @@ CREATE TABLE IF NOT EXISTS `manage` ( `uid` int(11) NOT NULL DEFAULT 0, `mid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), - INDEX `uid_mid` (`uid`,`mid`) -) DEFAULT CHARSET=utf8; + UNIQUE INDEX `uid_mid` (`uid`,`mid`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE notify @@ -637,8 +637,8 @@ CREATE TABLE IF NOT EXISTS `notify` ( `name` varchar(255) NOT NULL DEFAULT '', `url` varchar(255) NOT NULL DEFAULT '', `photo` varchar(255) NOT NULL DEFAULT '', - `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `msg` mediumtext NOT NULL, + `date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `msg` mediumtext, `uid` int(11) NOT NULL DEFAULT 0, `link` varchar(255) NOT NULL DEFAULT '', `iid` int(11) NOT NULL DEFAULT 0, @@ -646,9 +646,14 @@ CREATE TABLE IF NOT EXISTS `notify` ( `seen` tinyint(1) NOT NULL DEFAULT 0, `verb` varchar(255) NOT NULL DEFAULT '', `otype` varchar(16) NOT NULL DEFAULT '', + `name_cache` tinytext, + `msg_cache` mediumtext, PRIMARY KEY(`id`), - INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8; + INDEX `hash_uid` (`hash`,`uid`), + INDEX `seen_uid_date` (`seen`,`uid`,`date`), + INDEX `uid_date` (`uid`,`date`), + INDEX `uid_type_link` (`uid`,`type`,`link`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE notify-threads @@ -659,34 +664,32 @@ CREATE TABLE IF NOT EXISTS `notify-threads` ( `master-parent-item` int(10) unsigned NOT NULL DEFAULT 0, `parent-item` int(10) unsigned NOT NULL DEFAULT 0, `receiver-uid` int(11) NOT NULL DEFAULT 0, - PRIMARY KEY(`id`), - INDEX `master-parent-item` (`master-parent-item`), - INDEX `receiver-uid` (`receiver-uid`) -) DEFAULT CHARSET=utf8; + PRIMARY KEY(`id`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE oembed -- CREATE TABLE IF NOT EXISTS `oembed` ( - `url` varchar(255) NOT NULL, - `content` text NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `url` varbinary(255) NOT NULL, + `content` mediumtext, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`url`), INDEX `created` (`created`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE parsed_url -- CREATE TABLE IF NOT EXISTS `parsed_url` ( - `url` varchar(255) NOT NULL, + `url` varbinary(255) NOT NULL, `guessing` tinyint(1) NOT NULL DEFAULT 0, `oembed` tinyint(1) NOT NULL DEFAULT 0, - `content` text NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `content` mediumtext, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`url`,`guessing`,`oembed`), INDEX `created` (`created`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE pconfig @@ -694,12 +697,12 @@ CREATE TABLE IF NOT EXISTS `parsed_url` ( CREATE TABLE IF NOT EXISTS `pconfig` ( `id` int(11) NOT NULL auto_increment, `uid` int(11) NOT NULL DEFAULT 0, - `cat` varchar(255) NOT NULL DEFAULT '', - `k` varchar(255) NOT NULL DEFAULT '', - `v` mediumtext NOT NULL, + `cat` varbinary(255) NOT NULL DEFAULT '', + `k` varbinary(255) NOT NULL DEFAULT '', + `v` mediumtext, PRIMARY KEY(`id`), - INDEX `uid_cat_k` (`uid`,`cat`(30),`k`(30)) -) DEFAULT CHARSET=utf8; + UNIQUE INDEX `uid_cat_k` (`uid`,`cat`,`k`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE photo @@ -710,10 +713,10 @@ CREATE TABLE IF NOT EXISTS `photo` ( `contact-id` int(10) unsigned NOT NULL DEFAULT 0, `guid` varchar(64) NOT NULL DEFAULT '', `resource-id` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `title` varchar(255) NOT NULL DEFAULT '', - `desc` text NOT NULL, + `desc` text, `album` varchar(255) NOT NULL DEFAULT '', `filename` varchar(255) NOT NULL DEFAULT '', `type` varchar(128) NOT NULL DEFAULT 'image/jpeg', @@ -723,15 +726,17 @@ CREATE TABLE IF NOT EXISTS `photo` ( `data` mediumblob NOT NULL, `scale` tinyint(3) NOT NULL DEFAULT 0, `profile` tinyint(1) NOT NULL DEFAULT 0, - `allow_cid` mediumtext NOT NULL, - `allow_gid` mediumtext NOT NULL, - `deny_cid` mediumtext NOT NULL, - `deny_gid` mediumtext NOT NULL, + `allow_cid` mediumtext, + `allow_gid` mediumtext, + `deny_cid` mediumtext, + `deny_gid` mediumtext, PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `resource-id` (`resource-id`), - INDEX `guid` (`guid`) -) DEFAULT CHARSET=utf8; + INDEX `uid_contactid` (`uid`,`contact-id`), + INDEX `uid_profile` (`uid`,`profile`), + INDEX `uid_album_scale_created` (`uid`,`album`(32),`scale`,`created`), + INDEX `uid_album_resource-id_created` (`uid`,`album`(32),`resource-id`(64),`created`), + INDEX `resource-id` (`resource-id`(64)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE poll @@ -739,19 +744,19 @@ CREATE TABLE IF NOT EXISTS `photo` ( CREATE TABLE IF NOT EXISTS `poll` ( `id` int(11) NOT NULL auto_increment, `uid` int(11) NOT NULL DEFAULT 0, - `q0` mediumtext NOT NULL, - `q1` mediumtext NOT NULL, - `q2` mediumtext NOT NULL, - `q3` mediumtext NOT NULL, - `q4` mediumtext NOT NULL, - `q5` mediumtext NOT NULL, - `q6` mediumtext NOT NULL, - `q7` mediumtext NOT NULL, - `q8` mediumtext NOT NULL, - `q9` mediumtext NOT NULL, + `q0` text, + `q1` text, + `q2` text, + `q3` text, + `q4` text, + `q5` text, + `q6` text, + `q7` text, + `q8` text, + `q9` text, PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE poll_result @@ -763,7 +768,18 @@ CREATE TABLE IF NOT EXISTS `poll_result` ( PRIMARY KEY(`id`), INDEX `poll_id` (`poll_id`), INDEX `choice` (`choice`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; + +-- +-- TABLE process +-- +CREATE TABLE IF NOT EXISTS `process` ( + `pid` int(10) unsigned NOT NULL, + `command` varbinary(32) NOT NULL DEFAULT '', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + PRIMARY KEY(`pid`), + INDEX `command` (`command`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE profile @@ -776,7 +792,7 @@ CREATE TABLE IF NOT EXISTS `profile` ( `hide-friends` tinyint(1) NOT NULL DEFAULT 0, `name` varchar(255) NOT NULL DEFAULT '', `pdesc` varchar(255) NOT NULL DEFAULT '', - `dob` varchar(32) NOT NULL DEFAULT '0000-00-00', + `dob` varchar(32) NOT NULL DEFAULT '0001-01-01', `address` varchar(255) NOT NULL DEFAULT '', `locality` varchar(255) NOT NULL DEFAULT '', `region` varchar(255) NOT NULL DEFAULT '', @@ -785,34 +801,35 @@ CREATE TABLE IF NOT EXISTS `profile` ( `hometown` varchar(255) NOT NULL DEFAULT '', `gender` varchar(32) NOT NULL DEFAULT '', `marital` varchar(255) NOT NULL DEFAULT '', - `with` text NOT NULL, - `howlong` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `with` text, + `howlong` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `sexual` varchar(255) NOT NULL DEFAULT '', `politic` varchar(255) NOT NULL DEFAULT '', `religion` varchar(255) NOT NULL DEFAULT '', - `pub_keywords` text NOT NULL, - `prv_keywords` text NOT NULL, - `likes` text NOT NULL, - `dislikes` text NOT NULL, - `about` text NOT NULL, + `pub_keywords` text, + `prv_keywords` text, + `likes` text, + `dislikes` text, + `about` text, `summary` varchar(255) NOT NULL DEFAULT '', - `music` text NOT NULL, - `book` text NOT NULL, - `tv` text NOT NULL, - `film` text NOT NULL, - `interest` text NOT NULL, - `romance` text NOT NULL, - `work` text NOT NULL, - `education` text NOT NULL, - `contact` text NOT NULL, + `music` text, + `book` text, + `tv` text, + `film` text, + `interest` text, + `romance` text, + `work` text, + `education` text, + `contact` text, `homepage` varchar(255) NOT NULL DEFAULT '', + `xmpp` varchar(255) NOT NULL DEFAULT '', `photo` varchar(255) NOT NULL DEFAULT '', `thumb` varchar(255) NOT NULL DEFAULT '', `publish` tinyint(1) NOT NULL DEFAULT 0, `net-publish` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), - INDEX `hometown` (`hometown`) -) DEFAULT CHARSET=utf8; + INDEX `uid_is-default` (`uid`,`is-default`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE profile_check @@ -825,7 +842,7 @@ CREATE TABLE IF NOT EXISTS `profile_check` ( `sec` varchar(255) NOT NULL DEFAULT '', `expire` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE push_subscriber @@ -837,10 +854,10 @@ CREATE TABLE IF NOT EXISTS `push_subscriber` ( `topic` varchar(255) NOT NULL DEFAULT '', `nickname` varchar(255) NOT NULL DEFAULT '', `push` int(11) NOT NULL DEFAULT 0, - `last_update` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `last_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `secret` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE queue @@ -849,9 +866,9 @@ CREATE TABLE IF NOT EXISTS `queue` ( `id` int(11) NOT NULL auto_increment, `cid` int(11) NOT NULL DEFAULT 0, `network` varchar(32) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `last` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `content` mediumtext NOT NULL, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `last` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `content` mediumtext, `batch` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), INDEX `cid` (`cid`), @@ -859,7 +876,7 @@ CREATE TABLE IF NOT EXISTS `queue` ( INDEX `last` (`last`), INDEX `network` (`network`), INDEX `batch` (`batch`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE register @@ -867,12 +884,13 @@ CREATE TABLE IF NOT EXISTS `queue` ( CREATE TABLE IF NOT EXISTS `register` ( `id` int(11) unsigned NOT NULL auto_increment, `hash` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `uid` int(11) unsigned NOT NULL DEFAULT 0, `password` varchar(255) NOT NULL DEFAULT '', `language` varchar(16) NOT NULL DEFAULT '', + `note` text, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE search @@ -882,22 +900,21 @@ CREATE TABLE IF NOT EXISTS `search` ( `uid` int(11) NOT NULL DEFAULT 0, `term` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `term` (`term`) -) DEFAULT CHARSET=utf8; + INDEX `uid` (`uid`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE session -- CREATE TABLE IF NOT EXISTS `session` ( `id` bigint(20) unsigned NOT NULL auto_increment, - `sid` varchar(255) NOT NULL DEFAULT '', - `data` text NOT NULL, + `sid` varbinary(255) NOT NULL DEFAULT '', + `data` text, `expire` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`), - INDEX `sid` (`sid`), + INDEX `sid` (`sid`(64)), INDEX `expire` (`expire`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE sign @@ -905,12 +922,12 @@ CREATE TABLE IF NOT EXISTS `session` ( CREATE TABLE IF NOT EXISTS `sign` ( `id` int(10) unsigned NOT NULL auto_increment, `iid` int(10) unsigned NOT NULL DEFAULT 0, - `signed_text` mediumtext NOT NULL, - `signature` text NOT NULL, + `signed_text` mediumtext, + `signature` text, `signer` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), INDEX `iid` (`iid`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE spam @@ -921,13 +938,13 @@ CREATE TABLE IF NOT EXISTS `spam` ( `spam` int(11) NOT NULL DEFAULT 0, `ham` int(11) NOT NULL DEFAULT 0, `term` varchar(255) NOT NULL DEFAULT '', - `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `spam` (`spam`), INDEX `ham` (`ham`), INDEX `term` (`term`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE term @@ -940,19 +957,17 @@ CREATE TABLE IF NOT EXISTS `term` ( `term` varchar(255) NOT NULL DEFAULT '', `url` varchar(255) NOT NULL DEFAULT '', `guid` varchar(255) NOT NULL DEFAULT '', - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `received` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `global` tinyint(1) NOT NULL DEFAULT 0, `aid` int(10) unsigned NOT NULL DEFAULT 0, `uid` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`tid`), INDEX `oid_otype_type_term` (`oid`,`otype`,`type`,`term`), - INDEX `uid_term_tid` (`uid`,`term`,`tid`), - INDEX `type_term` (`type`,`term`), - INDEX `uid_otype_type_term_global_created` (`uid`,`otype`,`type`,`term`,`global`,`created`), - INDEX `otype_type_term_tid` (`otype`,`type`,`term`,`tid`), - INDEX `guid` (`guid`) -) DEFAULT CHARSET=utf8; + INDEX `uid_otype_type_term_global_created` (`uid`,`otype`,`type`,`term`(32),`global`,`created`), + INDEX `uid_otype_type_url` (`uid`,`otype`,`type`,`url`(64)), + INDEX `guid` (`guid`(64)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE thread @@ -962,11 +977,13 @@ CREATE TABLE IF NOT EXISTS `thread` ( `uid` int(10) unsigned NOT NULL DEFAULT 0, `contact-id` int(11) unsigned NOT NULL DEFAULT 0, `gcontact-id` int(11) unsigned NOT NULL DEFAULT 0, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `edited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `commented` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `received` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `owner-id` int(11) unsigned NOT NULL DEFAULT 0, + `author-id` int(11) unsigned NOT NULL DEFAULT 0, + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `wall` tinyint(1) NOT NULL DEFAULT 0, `private` tinyint(1) NOT NULL DEFAULT 0, `pubmail` tinyint(1) NOT NULL DEFAULT 0, @@ -983,31 +1000,27 @@ CREATE TABLE IF NOT EXISTS `thread` ( `mention` tinyint(1) NOT NULL DEFAULT 0, `network` varchar(32) NOT NULL DEFAULT '', PRIMARY KEY(`iid`), - INDEX `created` (`created`), - INDEX `commented` (`commented`), INDEX `uid_network_commented` (`uid`,`network`,`commented`), INDEX `uid_network_created` (`uid`,`network`,`created`), INDEX `uid_contactid_commented` (`uid`,`contact-id`,`commented`), INDEX `uid_contactid_created` (`uid`,`contact-id`,`created`), - INDEX `uid_gcontactid_commented` (`uid`,`gcontact-id`,`commented`), - INDEX `uid_gcontactid_created` (`uid`,`gcontact-id`,`created`), - INDEX `wall_private_received` (`wall`,`private`,`received`), INDEX `uid_created` (`uid`,`created`), - INDEX `uid_commented` (`uid`,`commented`) -) DEFAULT CHARSET=utf8; + INDEX `uid_commented` (`uid`,`commented`), + INDEX `uid_wall_created` (`uid`,`wall`,`created`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE tokens -- CREATE TABLE IF NOT EXISTS `tokens` ( `id` varchar(40) NOT NULL, - `secret` text NOT NULL, + `secret` text, `client_id` varchar(20) NOT NULL DEFAULT '', `expires` int(11) NOT NULL DEFAULT 0, `scope` varchar(200) NOT NULL DEFAULT '', `uid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE user @@ -1022,15 +1035,15 @@ CREATE TABLE IF NOT EXISTS `user` ( `openid` varchar(255) NOT NULL DEFAULT '', `timezone` varchar(128) NOT NULL DEFAULT '', `language` varchar(32) NOT NULL DEFAULT 'en', - `register_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `login_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `register_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `login_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `default-location` varchar(255) NOT NULL DEFAULT '', `allow_location` tinyint(1) NOT NULL DEFAULT 0, `theme` varchar(255) NOT NULL DEFAULT '', - `pubkey` text NOT NULL, - `prvkey` text NOT NULL, - `spubkey` text NOT NULL, - `sprvkey` text NOT NULL, + `pubkey` text, + `prvkey` text, + `spubkey` text, + `sprvkey` text, `verified` tinyint(1) unsigned NOT NULL DEFAULT 0, `blocked` tinyint(1) unsigned NOT NULL DEFAULT 0, `blockwall` tinyint(1) unsigned NOT NULL DEFAULT 0, @@ -1040,24 +1053,25 @@ CREATE TABLE IF NOT EXISTS `user` ( `cntunkmail` int(11) NOT NULL DEFAULT 10, `notify-flags` int(11) unsigned NOT NULL DEFAULT 65535, `page-flags` int(11) unsigned NOT NULL DEFAULT 0, + `account-type` int(11) unsigned NOT NULL DEFAULT 0, `prvnets` tinyint(1) NOT NULL DEFAULT 0, `pwdreset` varchar(255) NOT NULL DEFAULT '', `maxreq` int(11) NOT NULL DEFAULT 10, `expire` int(11) unsigned NOT NULL DEFAULT 0, `account_removed` tinyint(1) NOT NULL DEFAULT 0, `account_expired` tinyint(1) NOT NULL DEFAULT 0, - `account_expires_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `expire_notification_sent` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `account_expires_on` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + `expire_notification_sent` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `service_class` varchar(32) NOT NULL DEFAULT '', `def_gid` int(11) NOT NULL DEFAULT 0, - `allow_cid` mediumtext NOT NULL, - `allow_gid` mediumtext NOT NULL, - `deny_cid` mediumtext NOT NULL, - `deny_gid` mediumtext NOT NULL, - `openidserver` text NOT NULL, + `allow_cid` mediumtext, + `allow_gid` mediumtext, + `deny_cid` mediumtext, + `deny_gid` mediumtext, + `openidserver` text, PRIMARY KEY(`uid`), - INDEX `nickname` (`nickname`) -) DEFAULT CHARSET=utf8; + INDEX `nickname` (`nickname`(32)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE userd @@ -1066,20 +1080,19 @@ CREATE TABLE IF NOT EXISTS `userd` ( `id` int(11) NOT NULL auto_increment, `username` varchar(255) NOT NULL, PRIMARY KEY(`id`), - INDEX `username` (`username`) -) DEFAULT CHARSET=utf8; + INDEX `username` (`username`(32)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE workerqueue -- CREATE TABLE IF NOT EXISTS `workerqueue` ( `id` int(11) NOT NULL auto_increment, - `parameter` text NOT NULL, + `parameter` text, `priority` tinyint(3) unsigned NOT NULL DEFAULT 0, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `pid` int(11) NOT NULL DEFAULT 0, - `executed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - PRIMARY KEY(`id`), - INDEX `created` (`created`) -) DEFAULT CHARSET=utf8; + `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + PRIMARY KEY(`id`) +) DEFAULT COLLATE utf8mb4_general_ci;