X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=a8c12f272b41805c1cae49008618ac1538fc4f1a;hb=dab83c8b4093ff99963fc1e594e73c5f8ec6b5ad;hp=9d56957100497f41efbf465dc7db10cac4e55bf2;hpb=879ebb1d7ff4646391875dc85781ffa1f8aafcea;p=friendica.git diff --git a/database.sql b/database.sql index 9d56957100..a8c12f272b 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 3.5.2-dev (Asparagus) --- DB_UPDATE_VERSION 1215 +-- Friendica 3.6-dev (Asparagus) +-- DB_UPDATE_VERSION 1238 -- ------------------------------------------ @@ -17,7 +17,7 @@ CREATE TABLE IF NOT EXISTS `addon` ( `plugin_admin` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), UNIQUE INDEX `name` (`name`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE attach @@ -37,7 +37,7 @@ CREATE TABLE IF NOT EXISTS `attach` ( `deny_cid` mediumtext, `deny_gid` mediumtext, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE auth_codes @@ -49,7 +49,7 @@ 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=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE cache @@ -61,7 +61,7 @@ CREATE TABLE IF NOT EXISTS `cache` ( `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`k`), INDEX `expire_mode_updated` (`expire_mode`,`updated`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE challenge @@ -74,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=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE clients @@ -87,7 +87,7 @@ CREATE TABLE IF NOT EXISTS `clients` ( `icon` text, `uid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`client_id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE config @@ -99,7 +99,7 @@ CREATE TABLE IF NOT EXISTS `config` ( `v` mediumtext, PRIMARY KEY(`id`), UNIQUE INDEX `cat_k` (`cat`,`k`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE contact @@ -169,31 +169,31 @@ CREATE TABLE IF NOT EXISTS `contact` ( `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` text, PRIMARY KEY(`id`), - INDEX `uid_name` (`uid`,`name`), + INDEX `uid_name` (`uid`,`name`(190)), 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 `uid_rel_network_poll` (`uid`,`rel`,`network`(4),`poll`(64),`archive`), + INDEX `uid_network_batch` (`uid`,`network`(4),`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 CHARSET=utf8mb4; + INDEX `dfrn-id` (`dfrn-id`(64)), + INDEX `issued-id` (`issued-id`(64)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE conv -- CREATE TABLE IF NOT EXISTS `conv` ( `id` int(10) unsigned NOT NULL auto_increment, - `guid` varchar(64) NOT NULL DEFAULT '', + `guid` varchar(255) NOT NULL DEFAULT '', `recips` text, `uid` int(11) NOT NULL DEFAULT 0, `creator` varchar(255) NOT NULL DEFAULT '', @@ -202,7 +202,23 @@ CREATE TABLE IF NOT EXISTS `conv` ( `subject` text, PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; + +-- +-- TABLE conversation +-- +CREATE TABLE IF NOT EXISTS `conversation` ( + `item-uri` varbinary(255) NOT NULL, + `reply-to-uri` varbinary(255) NOT NULL DEFAULT '', + `conversation-uri` varbinary(255) NOT NULL DEFAULT '', + `conversation-href` varbinary(255) NOT NULL DEFAULT '', + `protocol` tinyint(1) unsigned NOT NULL DEFAULT 0, + `source` mediumtext, + `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', + PRIMARY KEY(`item-uri`), + INDEX `conversation-uri` (`conversation-uri`), + INDEX `received` (`received`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE event @@ -230,7 +246,7 @@ CREATE TABLE IF NOT EXISTS `event` ( `deny_gid` mediumtext, PRIMARY KEY(`id`), INDEX `uid_start` (`uid`,`start`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE fcontact @@ -255,31 +271,8 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`), INDEX `addr` (`addr`(32)), - INDEX `url` (`url`) -) DEFAULT CHARSET=utf8mb4; - --- --- TABLE ffinder --- -CREATE TABLE IF NOT EXISTS `ffinder` ( - `id` int(10) unsigned NOT NULL auto_increment, - `uid` int(10) unsigned NOT NULL DEFAULT 0, - `cid` int(10) unsigned NOT NULL DEFAULT 0, - `fid` int(10) unsigned NOT NULL DEFAULT 0, - PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; - --- --- TABLE fserver --- -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, - PRIMARY KEY(`id`), - INDEX `server` (`server`(32)) -) DEFAULT CHARSET=utf8mb4; + UNIQUE INDEX `url` (`url`(190)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE fsuggest @@ -295,7 +288,7 @@ CREATE TABLE IF NOT EXISTS `fsuggest` ( `note` text, `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE gcign @@ -307,7 +300,7 @@ CREATE TABLE IF NOT EXISTS `gcign` ( PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `gcid` (`gcid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE gcontact @@ -328,7 +321,7 @@ CREATE TABLE IF NOT EXISTS `gcontact` ( `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, @@ -340,13 +333,13 @@ CREATE TABLE IF NOT EXISTS `gcontact` ( `generation` tinyint(3) NOT NULL DEFAULT 0, `server_url` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), - INDEX `nurl` (`nurl`(64)), + UNIQUE INDEX `nurl` (`nurl`(190)), INDEX `name` (`name`(64)), INDEX `nick` (`nick`(32)), INDEX `addr` (`addr`(64)), - INDEX `hide_network_updated` (`hide`,`network`,`updated`), + INDEX `hide_network_updated` (`hide`,`network`(4),`updated`), INDEX `updated` (`updated`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE glink @@ -361,7 +354,7 @@ CREATE TABLE IF NOT EXISTS `glink` ( PRIMARY KEY(`id`), UNIQUE INDEX `cid_uid_gcid_zcid` (`cid`,`uid`,`gcid`,`zcid`), INDEX `gcid` (`gcid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE group @@ -374,21 +367,19 @@ CREATE TABLE IF NOT EXISTS `group` ( `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE group_member -- CREATE TABLE IF NOT EXISTS `group_member` ( `id` int(10) unsigned NOT NULL auto_increment, - `uid` int(10) unsigned NOT NULL DEFAULT 0, `gid` int(10) unsigned NOT NULL DEFAULT 0, `contact-id` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`), INDEX `contactid` (`contact-id`), - INDEX `gid_contactid` (`gid`,`contact-id`), - UNIQUE INDEX `uid_gid_contactid` (`uid`,`gid`,`contact-id`) -) DEFAULT CHARSET=utf8mb4; + UNIQUE INDEX `gid_contactid` (`gid`,`contact-id`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE gserver @@ -401,6 +392,7 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `site_name` varchar(255) NOT NULL DEFAULT '', `info` text, `register_policy` tinyint(1) NOT NULL DEFAULT 0, + `registered-users` int(10) NOT NULL DEFAULT 0, `poco` varchar(255) NOT NULL DEFAULT '', `noscrape` varchar(255) NOT NULL DEFAULT '', `network` varchar(32) NOT NULL DEFAULT '', @@ -410,8 +402,8 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `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`(32)) -) DEFAULT CHARSET=utf8mb4; + UNIQUE INDEX `nurl` (`nurl`(190)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE hook @@ -424,7 +416,7 @@ CREATE TABLE IF NOT EXISTS `hook` ( `priority` int(11) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`), UNIQUE INDEX `hook_file_function` (`hook`(50),`file`(80),`function`(60)) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE intro @@ -442,7 +434,7 @@ CREATE TABLE IF NOT EXISTS `intro` ( `blocked` tinyint(1) NOT NULL DEFAULT 1, `ignore` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE item @@ -514,48 +506,33 @@ CREATE TABLE IF NOT EXISTS `item` ( `rendered-html` mediumtext, `global` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), - INDEX `guid` (`guid`), - INDEX `uri` (`uri`), + INDEX `guid` (`guid`(191)), + INDEX `uri` (`uri`(191)), INDEX `parent` (`parent`), - INDEX `parent-uri` (`parent-uri`), - INDEX `extid` (`extid`), + INDEX `parent-uri` (`parent-uri`(191)), + INDEX `extid` (`extid`(191)), 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_network_commented` (`uid`,`network`,`commented`), - INDEX `uid_thrparent` (`uid`,`thr-parent`), - INDEX `uid_parenturi` (`uid`,`parent-uri`), + INDEX `uid_network_received` (`uid`,`network`(4),`received`), + INDEX `uid_network_commented` (`uid`,`network`(4),`commented`), + INDEX `uid_thrparent` (`uid`,`thr-parent`(190)), + INDEX `uid_parenturi` (`uid`,`parent-uri`(190)), INDEX `uid_contactid_created` (`uid`,`contact-id`,`created`), INDEX `authorid_created` (`author-id`,`created`), - INDEX `uid_uri` (`uid`,`uri`), - INDEX `resource-id` (`resource-id`), + INDEX `ownerid` (`owner-id`), + INDEX `uid_uri` (`uid`,`uri`(190)), + INDEX `resource-id` (`resource-id`(191)), INDEX `contactid_allowcid_allowpid_denycid_denygid` (`contact-id`,`allow_cid`(10),`allow_gid`(10),`deny_cid`(10),`deny_gid`(10)), - INDEX `uid_type_changed` (`uid`,`type`,`changed`), - INDEX `contactid_verb` (`contact-id`,`verb`), + INDEX `uid_type_changed` (`uid`,`type`(190),`changed`), + INDEX `contactid_verb` (`contact-id`,`verb`(190)), INDEX `deleted_changed` (`deleted`,`changed`), INDEX `uid_wall_changed` (`uid`,`wall`,`changed`), INDEX `uid_eventid` (`uid`,`event-id`), - INDEX `uid_authorlink` (`uid`,`author-link`), - INDEX `uid_ownerlink` (`uid`,`owner-link`) -) DEFAULT CHARSET=utf8mb4; - --- --- TABLE item_id --- -CREATE TABLE IF NOT EXISTS `item_id` ( - `id` int(11) NOT NULL auto_increment, - `iid` int(11) NOT NULL DEFAULT 0, - `uid` int(11) NOT NULL DEFAULT 0, - `sid` varchar(255) NOT NULL DEFAULT '', - `service` varchar(255) NOT NULL DEFAULT '', - PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `sid` (`sid`), - INDEX `service` (`service`(32)), - INDEX `iid` (`iid`) -) DEFAULT CHARSET=utf8mb4; + INDEX `uid_authorlink` (`uid`,`author-link`(190)), + INDEX `uid_ownerlink` (`uid`,`owner-link`(190)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE locks @@ -564,9 +541,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 '0001-01-01 00:00:00', + `pid` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE mail @@ -574,7 +551,7 @@ CREATE TABLE IF NOT EXISTS `locks` ( CREATE TABLE IF NOT EXISTS `mail` ( `id` int(10) unsigned NOT NULL auto_increment, `uid` int(10) unsigned NOT NULL DEFAULT 0, - `guid` varchar(64) NOT NULL DEFAULT '', + `guid` varchar(255) NOT NULL DEFAULT '', `from-name` varchar(255) NOT NULL DEFAULT '', `from-photo` varchar(255) NOT NULL DEFAULT '', `from-url` varchar(255) NOT NULL DEFAULT '', @@ -593,8 +570,9 @@ CREATE TABLE IF NOT EXISTS `mail` ( INDEX `uid_seen` (`uid`,`seen`), INDEX `convid` (`convid`), INDEX `uri` (`uri`(64)), - INDEX `parent-uri` (`parent-uri`(64)) -) DEFAULT CHARSET=utf8mb4; + INDEX `parent-uri` (`parent-uri`(64)), + INDEX `contactid` (`contact-id`(32)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE mailacct @@ -614,7 +592,7 @@ CREATE TABLE IF NOT EXISTS `mailacct` ( `pubmail` tinyint(1) NOT NULL DEFAULT 0, `last_check` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE manage @@ -625,7 +603,7 @@ CREATE TABLE IF NOT EXISTS `manage` ( `mid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), UNIQUE INDEX `uid_mid` (`uid`,`mid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE notify @@ -652,8 +630,8 @@ CREATE TABLE IF NOT EXISTS `notify` ( 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 CHARSET=utf8mb4; + INDEX `uid_type_link` (`uid`,`type`,`link`(190)) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE notify-threads @@ -665,7 +643,7 @@ CREATE TABLE IF NOT EXISTS `notify-threads` ( `parent-item` int(10) unsigned NOT NULL DEFAULT 0, `receiver-uid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE oembed @@ -676,7 +654,7 @@ CREATE TABLE IF NOT EXISTS `oembed` ( `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`url`), INDEX `created` (`created`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE parsed_url @@ -689,7 +667,7 @@ CREATE TABLE IF NOT EXISTS `parsed_url` ( `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`url`,`guessing`,`oembed`), INDEX `created` (`created`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE pconfig @@ -702,7 +680,7 @@ CREATE TABLE IF NOT EXISTS `pconfig` ( `v` mediumtext, PRIMARY KEY(`id`), UNIQUE INDEX `uid_cat_k` (`uid`,`cat`,`k`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE photo @@ -731,12 +709,13 @@ CREATE TABLE IF NOT EXISTS `photo` ( `deny_cid` mediumtext, `deny_gid` mediumtext, PRIMARY KEY(`id`), + INDEX `contactid` (`contact-id`), 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 CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE poll @@ -756,7 +735,7 @@ CREATE TABLE IF NOT EXISTS `poll` ( `q9` text, PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE poll_result @@ -768,7 +747,7 @@ CREATE TABLE IF NOT EXISTS `poll_result` ( PRIMARY KEY(`id`), INDEX `poll_id` (`poll_id`), INDEX `choice` (`choice`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE process @@ -779,7 +758,7 @@ CREATE TABLE IF NOT EXISTS `process` ( `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY(`pid`), INDEX `command` (`command`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE profile @@ -792,7 +771,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 '', @@ -829,7 +808,7 @@ CREATE TABLE IF NOT EXISTS `profile` ( `net-publish` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY(`id`), INDEX `uid_is-default` (`uid`,`is-default`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE profile_check @@ -842,7 +821,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=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE push_subscriber @@ -857,7 +836,7 @@ CREATE TABLE IF NOT EXISTS `push_subscriber` ( `last_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `secret` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE queue @@ -876,7 +855,7 @@ CREATE TABLE IF NOT EXISTS `queue` ( INDEX `last` (`last`), INDEX `network` (`network`), INDEX `batch` (`batch`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE register @@ -890,7 +869,7 @@ CREATE TABLE IF NOT EXISTS `register` ( `language` varchar(16) NOT NULL DEFAULT '', `note` text, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE search @@ -901,7 +880,7 @@ CREATE TABLE IF NOT EXISTS `search` ( `term` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), INDEX `uid` (`uid`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE session @@ -914,7 +893,7 @@ CREATE TABLE IF NOT EXISTS `session` ( PRIMARY KEY(`id`), INDEX `sid` (`sid`(64)), INDEX `expire` (`expire`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE sign @@ -926,25 +905,8 @@ CREATE TABLE IF NOT EXISTS `sign` ( `signature` text, `signer` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY(`id`), - INDEX `iid` (`iid`) -) DEFAULT CHARSET=utf8mb4; - --- --- TABLE spam --- -CREATE TABLE IF NOT EXISTS `spam` ( - `id` int(11) NOT NULL auto_increment, - `uid` int(11) NOT NULL DEFAULT 0, - `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 '0001-01-01 00:00:00', - PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `spam` (`spam`), - INDEX `ham` (`ham`), - INDEX `term` (`term`) -) DEFAULT CHARSET=utf8mb4; + UNIQUE INDEX `iid` (`iid`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE term @@ -963,11 +925,11 @@ CREATE TABLE IF NOT EXISTS `term` ( `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 `oid_otype_type_term` (`oid`,`otype`,`type`,`term`(32)), 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 CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE thread @@ -1004,10 +966,14 @@ CREATE TABLE IF NOT EXISTS `thread` ( INDEX `uid_network_created` (`uid`,`network`,`created`), INDEX `uid_contactid_commented` (`uid`,`contact-id`,`commented`), INDEX `uid_contactid_created` (`uid`,`contact-id`,`created`), + INDEX `contactid` (`contact-id`), + INDEX `ownerid` (`owner-id`), + INDEX `authorid` (`author-id`), INDEX `uid_created` (`uid`,`created`), INDEX `uid_commented` (`uid`,`commented`), - INDEX `uid_wall_created` (`uid`,`wall`,`created`) -) DEFAULT CHARSET=utf8mb4; + INDEX `uid_wall_created` (`uid`,`wall`,`created`), + INDEX `private_wall_received` (`private`,`wall`,`received`) +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE tokens @@ -1020,7 +986,7 @@ CREATE TABLE IF NOT EXISTS `tokens` ( `scope` varchar(200) NOT NULL DEFAULT '', `uid` int(11) NOT NULL DEFAULT 0, PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE user @@ -1062,7 +1028,6 @@ CREATE TABLE IF NOT EXISTS `user` ( `account_expired` tinyint(1) NOT NULL DEFAULT 0, `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, `allow_gid` mediumtext, @@ -1071,7 +1036,7 @@ CREATE TABLE IF NOT EXISTS `user` ( `openidserver` text, PRIMARY KEY(`uid`), INDEX `nickname` (`nickname`(32)) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE userd @@ -1081,7 +1046,7 @@ CREATE TABLE IF NOT EXISTS `userd` ( `username` varchar(255) NOT NULL, PRIMARY KEY(`id`), INDEX `username` (`username`(32)) -) DEFAULT CHARSET=utf8mb4; +) DEFAULT COLLATE utf8mb4_general_ci; -- -- TABLE workerqueue @@ -1093,6 +1058,11 @@ CREATE TABLE IF NOT EXISTS `workerqueue` ( `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `pid` int(11) NOT NULL DEFAULT 0, `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', - PRIMARY KEY(`id`) -) DEFAULT CHARSET=utf8mb4; + `done` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY(`id`), + INDEX `pid` (`pid`), + INDEX `parameter` (`parameter`(64)), + INDEX `priority_created` (`priority`,`created`), + INDEX `executed` (`executed`) +) DEFAULT COLLATE utf8mb4_general_ci;