X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=cf430b8ad4e016ea6c7a9e4c6f80f6d46629ed00;hb=8868b7f8d938463169e6bdf98cf7d60f38805b79;hp=0d99e46b58b8b138d70f5ed179a1e4d7c65ee788;hpb=7a82171bfe0dedabb014d0740b75c4a6ecb8d093;p=friendica.git diff --git a/database.sql b/database.sql index 0d99e46b58..cf430b8ad4 100644 --- a/database.sql +++ b/database.sql @@ -4,6 +4,151 @@ -- ------------------------------------------ +-- +-- 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 -- @@ -137,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 -- @@ -162,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 -- @@ -727,18 +765,6 @@ CREATE TABLE IF NOT EXISTS `item-content` ( 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 -- @@ -918,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 -- @@ -1002,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 -- @@ -1153,65 +1214,13 @@ CREATE TABLE IF NOT EXISTS `session` ( ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='web session storage'; -- --- 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`), - 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 +-- 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 `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'; +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 @@ -1401,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 --