X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=cf430b8ad4e016ea6c7a9e4c6f80f6d46629ed00;hb=8ca0186409f9f5da3aaf192f7bf13f4127c1c0bc;hp=2f025fe10d9247a619125c7c55c9a8ed2c10a51f;hpb=1e53f28b1879d17e351ff22cdc4f277719ec7746;p=friendica.git diff --git a/database.sql b/database.sql index 2f025fe10d..cf430b8ad4 100644 --- a/database.sql +++ b/database.sql @@ -1,9 +1,154 @@ -- ------------------------------------------ -- Friendica 2020.06-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1347 +-- DB_UPDATE_VERSION 1348 -- ------------------------------------------ +-- +-- TABLE clients +-- +CREATE TABLE IF NOT EXISTS `clients` ( + `client_id` varchar(20) NOT NULL COMMENT '', + `pw` varchar(20) NOT NULL DEFAULT '' COMMENT '', + `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', + `name` text COMMENT '', + `icon` text COMMENT '', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', + PRIMARY KEY(`client_id`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; + +-- +-- TABLE contact +-- +CREATE TABLE IF NOT EXISTS `contact` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `updated` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of last contact update', + `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self', + `remote_self` boolean NOT NULL DEFAULT '0' COMMENT '', + `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact', + `duplex` boolean NOT NULL DEFAULT '0' COMMENT '', + `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network of the contact', + `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact', + `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by', + `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact', + `location` varchar(255) DEFAULT '' COMMENT '', + `about` text COMMENT '', + `keywords` text COMMENT 'public keywords (interests) of the contact', + `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact', + `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)', + `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)', + `site-pubkey` text COMMENT '', + `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `pubkey` text COMMENT 'RSA public key 4096 bit', + `prvkey` text COMMENT 'RSA private key 4096 bit', + `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `request` varchar(255) COMMENT '', + `notify` varchar(255) COMMENT '', + `poll` varchar(255) COMMENT '', + `confirm` varchar(255) COMMENT '', + `poco` varchar(255) COMMENT '', + `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT '', + `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT '', + `usehub` boolean NOT NULL DEFAULT '0' COMMENT '', + `subhub` boolean NOT NULL DEFAULT '0' COMMENT '', + `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info', + `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful contact update', + `failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed update', + `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last post', + `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `blocked` boolean NOT NULL DEFAULT '1' COMMENT 'Node-wide block status', + `block_reason` text COMMENT 'Node-wide block reason', + `readonly` boolean NOT NULL DEFAULT '0' COMMENT 'posts of the contact are readonly', + `writable` boolean NOT NULL DEFAULT '0' COMMENT '', + `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum', + `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group', + `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT '', + `hidden` boolean NOT NULL DEFAULT '0' COMMENT '', + `archive` boolean NOT NULL DEFAULT '0' COMMENT '', + `pending` boolean NOT NULL DEFAULT '1' COMMENT '', + `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'Contact has been deleted', + `rating` tinyint NOT NULL DEFAULT 0 COMMENT '', + `unsearchable` boolean NOT NULL DEFAULT '0' COMMENT 'Contact prefers to not be searchable', + `sensitive` boolean NOT NULL DEFAULT '0' COMMENT 'Contact posts sensitive content', + `baseurl` varchar(255) DEFAULT '' COMMENT 'baseurl of the contact', + `reason` text COMMENT '', + `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '', + `info` mediumtext COMMENT '', + `profile-id` int unsigned COMMENT 'Deprecated', + `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '', + `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', + `notify_new_posts` boolean NOT NULL DEFAULT '0' COMMENT '', + `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `ffi_keyword_blacklist` text COMMENT '', + PRIMARY KEY(`id`), + 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 `addr_uid` (`addr`(32),`uid`), + INDEX `nurl_uid` (`nurl`(32),`uid`), + INDEX `nick_uid` (`nick`(32),`uid`), + INDEX `dfrn-id` (`dfrn-id`(64)), + INDEX `issued-id` (`issued-id`(64)) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table'; + +-- +-- TABLE item-uri +-- +CREATE TABLE IF NOT EXISTS `item-uri` ( + `id` int unsigned NOT NULL auto_increment, + `uri` varbinary(255) NOT NULL COMMENT 'URI of an item', + `guid` varbinary(255) COMMENT 'A unique identifier for an item', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri` (`uri`), + INDEX `guid` (`guid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='URI and GUID for items'; + +-- +-- TABLE permissionset +-- +CREATE TABLE IF NOT EXISTS `permissionset` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id of this permission set', + `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', + `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', + `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', + `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', + PRIMARY KEY(`id`), + INDEX `uid_allow_cid_allow_gid_deny_cid_deny_gid` (`allow_cid`(50),`allow_gid`(30),`deny_cid`(50),`deny_gid`(30)) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; + +-- +-- TABLE tag +-- +CREATE TABLE IF NOT EXISTS `tag` ( + `id` int unsigned NOT NULL auto_increment COMMENT '', + `name` varchar(96) NOT NULL DEFAULT '' COMMENT '', + `url` varbinary(255) NOT NULL DEFAULT '' COMMENT '', + PRIMARY KEY(`id`), + UNIQUE INDEX `type_name_url` (`name`,`url`), + INDEX `url` (`url`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; + -- -- TABLE 2fa_app_specific_password -- @@ -107,7 +252,9 @@ CREATE TABLE IF NOT EXISTS `auth_codes` ( `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', `expires` int NOT NULL DEFAULT 0 COMMENT '', `scope` varchar(250) NOT NULL DEFAULT '' COMMENT '', - PRIMARY KEY(`id`) + PRIMARY KEY(`id`), + INDEX `client_id` (`client_id`), + CONSTRAINT `auth_codes-client_id-clients-client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; -- @@ -135,19 +282,6 @@ CREATE TABLE IF NOT EXISTS `challenge` ( PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; --- --- TABLE clients --- -CREATE TABLE IF NOT EXISTS `clients` ( - `client_id` varchar(20) NOT NULL COMMENT '', - `pw` varchar(20) NOT NULL DEFAULT '' COMMENT '', - `redirect_uri` varchar(200) NOT NULL DEFAULT '' COMMENT '', - `name` text COMMENT '', - `icon` text COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - PRIMARY KEY(`client_id`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; - -- -- TABLE config -- @@ -160,100 +294,6 @@ CREATE TABLE IF NOT EXISTS `config` ( UNIQUE INDEX `cat_k` (`cat`,`k`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='main configuration storage'; --- --- TABLE contact --- -CREATE TABLE IF NOT EXISTS `contact` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `updated` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of last contact update', - `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self', - `remote_self` boolean NOT NULL DEFAULT '0' COMMENT '', - `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact', - `duplex` boolean NOT NULL DEFAULT '0' COMMENT '', - `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network of the contact', - `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by', - `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact', - `location` varchar(255) DEFAULT '' COMMENT '', - `about` text COMMENT '', - `keywords` text COMMENT 'public keywords (interests) of the contact', - `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', - `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact', - `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)', - `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)', - `site-pubkey` text COMMENT '', - `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `pubkey` text COMMENT 'RSA public key 4096 bit', - `prvkey` text COMMENT 'RSA private key 4096 bit', - `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `request` varchar(255) COMMENT '', - `notify` varchar(255) COMMENT '', - `poll` varchar(255) COMMENT '', - `confirm` varchar(255) COMMENT '', - `poco` varchar(255) COMMENT '', - `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT '', - `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT '', - `usehub` boolean NOT NULL DEFAULT '0' COMMENT '', - `subhub` boolean NOT NULL DEFAULT '0' COMMENT '', - `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info', - `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful contact update', - `failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed update', - `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last post', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `blocked` boolean NOT NULL DEFAULT '1' COMMENT 'Node-wide block status', - `block_reason` text COMMENT 'Node-wide block reason', - `readonly` boolean NOT NULL DEFAULT '0' COMMENT 'posts of the contact are readonly', - `writable` boolean NOT NULL DEFAULT '0' COMMENT '', - `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum', - `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group', - `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT '', - `hidden` boolean NOT NULL DEFAULT '0' COMMENT '', - `archive` boolean NOT NULL DEFAULT '0' COMMENT '', - `pending` boolean NOT NULL DEFAULT '1' COMMENT '', - `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'Contact has been deleted', - `rating` tinyint NOT NULL DEFAULT 0 COMMENT '', - `unsearchable` boolean NOT NULL DEFAULT '0' COMMENT 'Contact prefers to not be searchable', - `sensitive` boolean NOT NULL DEFAULT '0' COMMENT 'Contact posts sensitive content', - `baseurl` varchar(255) DEFAULT '' COMMENT 'baseurl of the contact', - `reason` text COMMENT '', - `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '', - `info` mediumtext COMMENT '', - `profile-id` int unsigned COMMENT 'Deprecated', - `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '', - `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', - `notify_new_posts` boolean NOT NULL DEFAULT '0' COMMENT '', - `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `ffi_keyword_blacklist` text COMMENT '', - PRIMARY KEY(`id`), - 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 `addr_uid` (`addr`(32),`uid`), - INDEX `nurl_uid` (`nurl`(32),`uid`), - INDEX `nick_uid` (`nick`(32),`uid`), - INDEX `dfrn-id` (`dfrn-id`(64)), - INDEX `issued-id` (`issued-id`(64)) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table'; - -- -- TABLE contact-relation -- @@ -304,7 +344,8 @@ CREATE TABLE IF NOT EXISTS `conversation` ( CREATE TABLE IF NOT EXISTS `diaspora-interaction` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `interaction` mediumtext COMMENT 'The Diaspora interaction', - PRIMARY KEY(`uri-id`) + PRIMARY KEY(`uri-id`), + CONSTRAINT `diaspora-interaction-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Signed Diaspora Interaction'; -- @@ -668,7 +709,13 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `icid` (`icid`), INDEX `iaid` (`iaid`), INDEX `psid_wall` (`psid`,`wall`), - INDEX `uri-id` (`uri-id`) + INDEX `uri-id` (`uri-id`), + INDEX `parent-uri-id` (`parent-uri-id`), + INDEX `thr-parent-id` (`thr-parent-id`), + CONSTRAINT `item-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + CONSTRAINT `item-parent-uri-id-item-uri-id` FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + CONSTRAINT `item-thr-parent-id-item-uri-id` FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + CONSTRAINT `item-psid-permissionset-id` FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts'; -- @@ -683,7 +730,8 @@ CREATE TABLE IF NOT EXISTS `item-activity` ( PRIMARY KEY(`id`), UNIQUE INDEX `uri-hash` (`uri-hash`), INDEX `uri` (`uri`(191)), - INDEX `uri-id` (`uri-id`) + INDEX `uri-id` (`uri-id`), + CONSTRAINT `item-activity-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Activities for items'; -- @@ -713,21 +761,10 @@ CREATE TABLE IF NOT EXISTS `item-content` ( UNIQUE INDEX `uri-plink-hash` (`uri-plink-hash`), INDEX `uri` (`uri`(191)), INDEX `plink` (`plink`(191)), - INDEX `uri-id` (`uri-id`) + INDEX `uri-id` (`uri-id`), + CONSTRAINT `item-content-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; --- --- TABLE item-uri --- -CREATE TABLE IF NOT EXISTS `item-uri` ( - `id` int unsigned NOT NULL auto_increment, - `uri` varbinary(255) NOT NULL COMMENT 'URI of an item', - `guid` varbinary(255) COMMENT 'A unique identifier for an item', - PRIMARY KEY(`id`), - UNIQUE INDEX `uri` (`uri`), - INDEX `guid` (`guid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='URI and GUID for items'; - -- -- TABLE locks -- @@ -839,7 +876,8 @@ CREATE TABLE IF NOT EXISTS `notify-threads` ( `master-parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post', `parent-item` int unsigned NOT NULL DEFAULT 0 COMMENT '', `receiver-uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - PRIMARY KEY(`id`) + PRIMARY KEY(`id`), + INDEX `master-parent-uri-id` (`master-parent-uri-id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; -- @@ -906,20 +944,6 @@ CREATE TABLE IF NOT EXISTS `pconfig` ( UNIQUE INDEX `uid_cat_k` (`uid`,`cat`,`k`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='personal (per user) configuration storage'; --- --- TABLE permissionset --- -CREATE TABLE IF NOT EXISTS `permissionset` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id of this permission set', - `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', - `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', - `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', - `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', - PRIMARY KEY(`id`), - INDEX `uid_allow_cid_allow_gid_deny_cid_deny_gid` (`allow_cid`(50),`allow_gid`(30),`deny_cid`(50),`deny_gid`(30)) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; - -- -- TABLE photo -- @@ -990,6 +1014,55 @@ CREATE TABLE IF NOT EXISTS `poll_result` ( INDEX `poll_id` (`poll_id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='data for polls - currently unused'; +-- +-- TABLE post-category +-- +CREATE TABLE IF NOT EXISTS `post-category` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', + `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', + PRIMARY KEY(`uri-id`,`uid`,`type`,`tid`), + INDEX `uri-id` (`tid`), + CONSTRAINT `post-category-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + CONSTRAINT `post-category-tid-tag-id` FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; + +-- +-- TABLE post-delivery-data +-- +CREATE TABLE IF NOT EXISTS `post-delivery-data` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `postopts` text COMMENT 'External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery', + `inform` mediumtext COMMENT 'Additional receivers of the linked item', + `queue_count` mediumint NOT NULL DEFAULT 0 COMMENT 'Initial number of delivery recipients, used as item.delivery_queue_count', + `queue_done` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries, used as item.delivery_queue_done', + `queue_failed` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of unsuccessful deliveries, used as item.delivery_queue_failed', + `activitypub` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via ActivityPub', + `dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via DFRN', + `legacy_dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via legacy DFRN', + `diaspora` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via Diaspora', + `ostatus` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via OStatus', + PRIMARY KEY(`uri-id`), + CONSTRAINT `post-delivery-data-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; + +-- +-- TABLE post-tag +-- +CREATE TABLE IF NOT EXISTS `post-tag` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Contact id of the mentioned public contact', + PRIMARY KEY(`uri-id`,`type`,`tid`,`cid`), + INDEX `tid` (`tid`), + INDEX `cid` (`cid`), + CONSTRAINT `post-tag-uri-id-item-uri-id` FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + CONSTRAINT `post-tag-tid-tag-id` FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + CONSTRAINT `post-tag-cid-contact-id` FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to tags'; + -- -- TABLE process -- @@ -1080,7 +1153,8 @@ CREATE TABLE IF NOT EXISTS `profile_field` ( PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `order` (`order`), - INDEX `psid` (`psid`) + INDEX `psid` (`psid`), + CONSTRAINT `profile_field-psid-permissionset-id` FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Custom profile fields'; -- @@ -1140,82 +1214,13 @@ CREATE TABLE IF NOT EXISTS `session` ( ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='web session storage'; -- --- TABLE term --- -CREATE TABLE IF NOT EXISTS `term` ( - `tid` int unsigned NOT NULL auto_increment COMMENT '', - `oid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `otype` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `term` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `guid` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `global` boolean NOT NULL DEFAULT '0' COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - PRIMARY KEY(`tid`), - INDEX `term_type` (`term`(64),`type`), - 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 COLLATE utf8mb4_general_ci COMMENT='item taxonomy (categories, tags, etc.) table'; - --- --- TABLE tag --- -CREATE TABLE IF NOT EXISTS `tag` ( - `id` int unsigned NOT NULL auto_increment COMMENT '', - `name` varchar(96) NOT NULL DEFAULT '' COMMENT '', - `url` varbinary(255) NOT NULL DEFAULT '' COMMENT '', - PRIMARY KEY(`id`), - UNIQUE INDEX `type_name_url` (`name`,`url`), - INDEX `url` (`url`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; - --- --- TABLE post-category --- -CREATE TABLE IF NOT EXISTS `post-category` ( - `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - PRIMARY KEY(`uri-id`,`uid`,`type`,`tid`), - INDEX `uri-id` (`tid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; - --- --- TABLE post-delivery-data --- -CREATE TABLE IF NOT EXISTS `post-delivery-data` ( - `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', - `postopts` text COMMENT 'External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery', - `inform` mediumtext COMMENT 'Additional receivers of the linked item', - `queue_count` mediumint NOT NULL DEFAULT 0 COMMENT 'Initial number of delivery recipients, used as item.delivery_queue_count', - `queue_done` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries, used as item.delivery_queue_done', - `queue_failed` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of unsuccessful deliveries, used as item.delivery_queue_failed', - `activitypub` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via ActivityPub', - `dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via DFRN', - `legacy_dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via legacy DFRN', - `diaspora` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via Diaspora', - `ostatus` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via OStatus', - PRIMARY KEY(`uri-id`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; - --- --- TABLE post-tag +-- TABLE storage -- -CREATE TABLE IF NOT EXISTS `post-tag` ( - `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', - `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Contact id of the mentioned public contact', - PRIMARY KEY(`uri-id`,`type`,`tid`,`cid`), - INDEX `uri-id` (`tid`), - INDEX `cid` (`tid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to tags'; +CREATE TABLE IF NOT EXISTS `storage` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented image data id', + `data` longblob NOT NULL COMMENT 'file data', + PRIMARY KEY(`id`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend'; -- -- TABLE thread @@ -1270,7 +1275,9 @@ CREATE TABLE IF NOT EXISTS `tokens` ( `expires` int NOT NULL DEFAULT 0 COMMENT '', `scope` varchar(200) NOT NULL DEFAULT '' COMMENT '', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - PRIMARY KEY(`id`) + PRIMARY KEY(`id`), + INDEX `client_id` (`client_id`), + CONSTRAINT `tokens-client_id-clients-client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; -- @@ -1367,7 +1374,7 @@ CREATE TABLE IF NOT EXISTS `user-item` ( -- TABLE verb -- CREATE TABLE IF NOT EXISTS `verb` ( - `id` int unsigned NOT NULL auto_increment, + `id` smallint unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL DEFAULT '' COMMENT '', PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Activity Verbs'; @@ -1403,15 +1410,6 @@ CREATE TABLE IF NOT EXISTS `workerqueue` ( INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries'; --- --- TABLE storage --- -CREATE TABLE IF NOT EXISTS `storage` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented image data id', - `data` longblob NOT NULL COMMENT 'file data', - PRIMARY KEY(`id`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend'; - -- -- VIEW category-view --