X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=f2f9a89e2e35ecaf500bce30b92ea37648ffb82d;hb=fb0e31791f51a7e197f298a54eb34aeda89ba9f0;hp=9e9427bdaae77b91bd6827d3b3fa5328a63fbbfa;hpb=83d3ce0802d2a2e2717f580f606d1f5211f23edd;p=friendica.git diff --git a/database.sql b/database.sql index 9e9427bdaa..f2f9a89e2e 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.06-rc (Siberian Iris) --- DB_UPDATE_VERSION 1423 +-- Friendica 2022.05-rc (Siberian Iris) +-- DB_UPDATE_VERSION 1464 -- ------------------------------------------ @@ -16,6 +16,11 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `info` text COMMENT '', `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '', `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users', + `active-week-users` int unsigned COMMENT 'Number of active users in the last week', + `active-month-users` int unsigned COMMENT 'Number of active users in the last month', + `active-halfyear-users` int unsigned COMMENT 'Number of active users in the last six month', + `local-posts` int unsigned COMMENT 'Number of local posts', + `local-comments` int unsigned COMMENT 'Number of local comments', `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)', `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -94,6 +99,18 @@ CREATE TABLE IF NOT EXISTS `user` ( FOREIGN KEY (`parent-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='The local users'; +-- +-- 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 contact -- @@ -102,83 +119,85 @@ CREATE TABLE IF NOT EXISTS `contact` ( `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 '', + `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', + `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `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)', `header` varchar(255) COMMENT 'Header picture', - `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 '', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', `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 '', `subscribe` 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', `failed` boolean COMMENT 'Connection failed', - `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', `last-discovery` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last follower discovery', - `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 '', - `manually-approve` boolean COMMENT '', - `hidden` boolean NOT NULL DEFAULT '0' COMMENT '', + `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Person, organisation, news, community, relay', + `manually-approve` boolean COMMENT 'Contact requests have to be approved manually', `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', `gsid` int unsigned COMMENT 'Global Server ID', + `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', `reason` text COMMENT '', - `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '', + `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', + `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact', + `subhub` boolean NOT NULL DEFAULT '0' COMMENT '', + `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `rating` tinyint NOT NULL DEFAULT 0 COMMENT 'Automatically detected feed poll frequency', + `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Feed poll priority', + `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `hidden` boolean NOT NULL DEFAULT '0' COMMENT '', + `pending` boolean NOT NULL DEFAULT '1' COMMENT 'Contact request is pending', + `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'Contact has been deleted', `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_denylist` text 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)', + `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 '', + `request` varchar(255) COMMENT '', + `confirm` varchar(255) COMMENT '', + `poco` varchar(255) COMMENT '', + `writable` boolean NOT NULL DEFAULT '0' COMMENT '', + `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = false instead', + `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = true instead', + `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '', + `site-pubkey` text COMMENT 'Deprecated', + `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', + `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', + `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', + `usehub` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', + `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT 'Deprecated', + `profile-id` int unsigned COMMENT 'Deprecated', PRIMARY KEY(`id`), INDEX `uid_name` (`uid`,`name`(190)), INDEX `self_uid` (`self`,`uid`), @@ -192,8 +211,6 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `nurl_uid` (`nurl`(128),`uid`), INDEX `nick_uid` (`nick`(128),`uid`), INDEX `attag_uid` (`attag`(96),`uid`), - INDEX `dfrn-id` (`dfrn-id`(64)), - INDEX `issued-id` (`issued-id`(64)), INDEX `network_uid_lastupdate` (`network`,`uid`,`last-update`), INDEX `uid_network_self_lastupdate` (`uid`,`network`,`self`,`last-update`), INDEX `uid_lastitem` (`uid`,`last-item`), @@ -202,22 +219,12 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `uid_self_contact-type` (`uid`,`self`,`contact-type`), INDEX `self_network_uid` (`self`,`network`,`uid`), INDEX `gsid` (`gsid`), + INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) 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 tag -- @@ -225,26 +232,12 @@ 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 '', + `type` tinyint unsigned COMMENT 'Type of the tag (Unknown, General Collection, Follower Collection or Account)', PRIMARY KEY(`id`), UNIQUE INDEX `type_name_url` (`name`,`url`), INDEX `url` (`url`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; --- --- 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`), - INDEX `uid` (`uid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; - -- -- TABLE permissionset -- @@ -332,6 +325,7 @@ CREATE TABLE IF NOT EXISTS `addon` ( -- CREATE TABLE IF NOT EXISTS `apcontact` ( `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', `uuid` varchar(255) COMMENT '', `type` varchar(20) NOT NULL COMMENT '', `following` varchar(255) COMMENT '', @@ -339,10 +333,15 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `inbox` varchar(255) NOT NULL COMMENT '', `outbox` varchar(255) COMMENT '', `sharedinbox` varchar(255) COMMENT '', + `featured` varchar(255) COMMENT 'Address for the collection of featured posts', + `featured-tags` varchar(255) COMMENT 'Address for the collection of featured tags', `manually-approve` boolean COMMENT '', + `discoverable` boolean COMMENT 'Mastodon extension: true if profile is published in their directory', `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', `name` varchar(255) COMMENT '', `about` text COMMENT '', + `xmpp` varchar(255) COMMENT 'XMPP address', + `matrix` varchar(255) COMMENT 'Matrix address', `photo` varchar(255) COMMENT '', `header` varchar(255) COMMENT 'Header picture', `addr` varchar(255) COMMENT '', @@ -363,6 +362,8 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( INDEX `baseurl` (`baseurl`(190)), INDEX `sharedinbox` (`sharedinbox`(190)), INDEX `gsid` (`gsid`), + UNIQUE INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='ActivityPub compatible contacts - used in the ActivityPub implementation'; @@ -429,20 +430,6 @@ CREATE TABLE IF NOT EXISTS `attach` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='file attachments'; --- --- TABLE auth_codes --- -CREATE TABLE IF NOT EXISTS `auth_codes` ( - `id` varchar(40) NOT NULL COMMENT '', - `client_id` varchar(20) NOT NULL DEFAULT '' COMMENT '', - `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`), - INDEX `client_id` (`client_id`), - FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; - -- -- TABLE cache -- @@ -455,20 +442,6 @@ CREATE TABLE IF NOT EXISTS `cache` ( INDEX `k_expires` (`k`,`expires`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Stores temporary data'; --- --- TABLE challenge --- -CREATE TABLE IF NOT EXISTS `challenge` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `challenge` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `type` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `last_update` varchar(255) NOT NULL DEFAULT '' COMMENT '', - PRIMARY KEY(`id`), - INDEX `expire` (`expire`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; - -- -- TABLE config -- @@ -530,6 +503,31 @@ CREATE TABLE IF NOT EXISTS `conversation` ( INDEX `received` (`received`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Raw data and structure information for messages'; +-- +-- TABLE workerqueue +-- +CREATE TABLE IF NOT EXISTS `workerqueue` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented worker task id', + `command` varchar(100) COMMENT 'Task command', + `parameter` mediumtext COMMENT 'Task parameter', + `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Task priority', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date', + `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process id of the worker', + `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Execution date', + `next_try` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Next retrial date', + `retrial` tinyint NOT NULL DEFAULT 0 COMMENT 'Retrial counter', + `done` boolean NOT NULL DEFAULT '0' COMMENT 'Marked 1 when the task was done - will be deleted later', + PRIMARY KEY(`id`), + INDEX `command` (`command`), + INDEX `done_command_parameter` (`done`,`command`,`parameter`(64)), + INDEX `done_executed` (`done`,`executed`), + INDEX `done_priority_retrial_created` (`done`,`priority`,`retrial`,`created`), + INDEX `done_priority_next_try` (`done`,`priority`,`next_try`), + INDEX `done_pid_next_try` (`done`,`pid`,`next_try`), + INDEX `done_pid_retrial` (`done`,`pid`,`retrial`), + INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries'; + -- -- TABLE delayed-post -- @@ -538,9 +536,12 @@ CREATE TABLE IF NOT EXISTS `delayed-post` ( `uri` varchar(255) COMMENT 'URI of the post that will be distributed later', `uid` mediumint unsigned COMMENT 'Owner User id', `delayed` datetime COMMENT 'delay time', + `wid` int unsigned COMMENT 'Workerqueue id', PRIMARY KEY(`id`), UNIQUE INDEX `uid_uri` (`uid`,`uri`(190)), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `wid` (`wid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Posts that are about to be distributed at a later time'; -- @@ -553,6 +554,18 @@ CREATE TABLE IF NOT EXISTS `diaspora-interaction` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Signed Diaspora Interaction'; +-- +-- TABLE endpoint +-- +CREATE TABLE IF NOT EXISTS `endpoint` ( + `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `type` varchar(20) NOT NULL COMMENT '', + `owner-uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', + PRIMARY KEY(`url`), + UNIQUE INDEX `owner-uri-id_type` (`owner-uri-id`,`type`), + FOREIGN KEY (`owner-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='ActivityPub endpoints - used in the ActivityPub implementation'; + -- -- TABLE event -- @@ -562,6 +575,7 @@ CREATE TABLE IF NOT EXISTS `event` ( `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact_id (ID of the contact in contact table)', `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time', `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time', `start` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'event start time', @@ -571,7 +585,6 @@ CREATE TABLE IF NOT EXISTS `event` ( `location` text COMMENT 'event location', `type` varchar(20) NOT NULL DEFAULT '' COMMENT 'event or birthday', `nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1', - `adjust` boolean NOT NULL DEFAULT '1' COMMENT 'adjust to timezone of the recipient (0 or 1)', `ignore` boolean NOT NULL DEFAULT '0' COMMENT '0 or 1', `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'', `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', @@ -580,8 +593,10 @@ CREATE TABLE IF NOT EXISTS `event` ( PRIMARY KEY(`id`), INDEX `uid_start` (`uid`,`start`), INDEX `cid` (`cid`), + INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events'; -- @@ -591,6 +606,7 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'unique id', `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', `request` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -605,9 +621,14 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', `pubkey` text COMMENT '', `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `interacting_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts this contact interactes with', + `interacted_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts that interacted with this contact', + `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments', PRIMARY KEY(`id`), INDEX `addr` (`addr`(32)), - UNIQUE INDEX `url` (`url`(190)) + UNIQUE INDEX `url` (`url`(190)), + UNIQUE INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation'; -- @@ -638,10 +659,13 @@ CREATE TABLE IF NOT EXISTS `group` ( `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', `visible` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the member list is not private', `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the group has been deleted', + `cid` int unsigned COMMENT 'Contact id of forum. When this field is filled then the members are synced automatically.', `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of group', PRIMARY KEY(`id`), INDEX `uid` (`uid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `cid` (`cid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, group info'; -- @@ -683,28 +707,21 @@ CREATE TABLE IF NOT EXISTS `hook` ( UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry'; --- --- TABLE host --- -CREATE TABLE IF NOT EXISTS `host` ( - `id` tinyint unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `name` varchar(128) NOT NULL DEFAULT '' COMMENT 'The hostname', - PRIMARY KEY(`id`), - UNIQUE INDEX `name` (`name`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Hostname'; - -- -- TABLE inbox-status -- CREATE TABLE IF NOT EXISTS `inbox-status` ( `url` varbinary(255) NOT NULL COMMENT 'URL of the inbox', + `uri-id` int unsigned COMMENT 'Item-uri id of inbox url', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry', `success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery', `failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery', `previous` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Previous delivery date', `archive` boolean NOT NULL DEFAULT '0' COMMENT 'Is the inbox archived?', `shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?', - PRIMARY KEY(`url`) + PRIMARY KEY(`url`), + INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Status of ActivityPub inboxes'; -- @@ -713,20 +730,23 @@ CREATE TABLE IF NOT EXISTS `inbox-status` ( CREATE TABLE IF NOT EXISTS `intro` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `fid` int unsigned COMMENT '', + `fid` int unsigned COMMENT 'deprecated', `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `suggest-cid` int unsigned COMMENT 'Suggested contact', `knowyou` boolean NOT NULL DEFAULT '0' COMMENT '', - `duplex` boolean NOT NULL DEFAULT '0' COMMENT '', + `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `note` text COMMENT '', `hash` varchar(255) NOT NULL DEFAULT '' COMMENT '', `datetime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `blocked` boolean NOT NULL DEFAULT '1' COMMENT '', + `blocked` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `ignore` boolean NOT NULL DEFAULT '0' COMMENT '', PRIMARY KEY(`id`), INDEX `contact-id` (`contact-id`), + INDEX `suggest-cid` (`suggest-cid`), INDEX `uid` (`uid`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; -- @@ -833,7 +853,8 @@ CREATE TABLE IF NOT EXISTS `notification` ( `target-uri-id` int unsigned COMMENT 'Item-uri id of the related post', `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post', `created` datetime COMMENT '', - `seen` boolean DEFAULT '0' COMMENT '', + `seen` boolean DEFAULT '0' COMMENT 'Seen on the desktop', + `dismissed` boolean DEFAULT '0' COMMENT 'Dismissed via the API', PRIMARY KEY(`id`), UNIQUE INDEX `uid_vid_type_actor-id_target-uri-id` (`uid`,`vid`,`type`,`actor-id`,`target-uri-id`), INDEX `vid` (`vid`), @@ -879,7 +900,7 @@ CREATE TABLE IF NOT EXISTS `notify` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='notifications'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='[Deprecated] User notifications'; -- -- TABLE notify-threads @@ -972,6 +993,7 @@ CREATE TABLE IF NOT EXISTS `photo` ( `title` varchar(255) NOT NULL DEFAULT '' COMMENT '', `desc` text COMMENT '', `album` varchar(255) NOT NULL DEFAULT '' COMMENT 'The name of the album to which the photo belongs', + `photo-type` tinyint unsigned COMMENT 'User avatar, user banner, contact avatar, contact banner or default', `filename` varchar(255) NOT NULL DEFAULT '' COMMENT '', `type` varchar(30) NOT NULL DEFAULT 'image/jpeg', `height` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', @@ -995,6 +1017,7 @@ CREATE TABLE IF NOT EXISTS `photo` ( INDEX `uid_album_scale_created` (`uid`,`album`(32),`scale`,`created`), INDEX `uid_album_resource-id_created` (`uid`,`album`(32),`resource-id`,`created`), INDEX `resource-id` (`resource-id`), + INDEX `uid_photo-type` (`uid`,`photo-type`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='photo storage'; @@ -1048,13 +1071,24 @@ CREATE TABLE IF NOT EXISTS `post-category` ( `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`), - INDEX `uid` (`uid`), + INDEX `tid` (`tid`), + INDEX `uid_uri-id` (`uid`,`uri-id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; +-- +-- TABLE post-collection +-- +CREATE TABLE IF NOT EXISTS `post-collection` ( + `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 '0 - Featured', + PRIMARY KEY(`uri-id`,`type`), + INDEX `type` (`type`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Collection of posts'; + -- -- TABLE post-content -- @@ -1083,6 +1117,25 @@ CREATE TABLE IF NOT EXISTS `post-content` ( 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 post-delivery +-- +CREATE TABLE IF NOT EXISTS `post-delivery` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `inbox-id` int unsigned NOT NULL COMMENT 'Item-uri id of inbox url', + `uid` mediumint unsigned COMMENT 'Delivering user', + `created` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', + `command` varbinary(32) COMMENT '', + `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed', + `receivers` mediumtext COMMENT 'JSON encoded array with the receiving contacts', + PRIMARY KEY(`uri-id`,`inbox-id`), + INDEX `inbox-id_created` (`inbox-id`,`created`), + INDEX `uid` (`uid`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`inbox-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for posts for the batch processing'; + -- -- TABLE post-delivery-data -- @@ -1102,6 +1155,45 @@ CREATE TABLE IF NOT EXISTS `post-delivery-data` ( 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-history +-- +CREATE TABLE IF NOT EXISTS `post-history` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of edit', + `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'item title', + `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `body` mediumtext COMMENT 'item body content', + `raw-body` mediumtext COMMENT 'Body without embedded media links', + `location` varchar(255) NOT NULL DEFAULT '' COMMENT 'text location where this item originated', + `coord` varchar(255) NOT NULL DEFAULT '' COMMENT 'longitude/latitude pair representing location where this item originated', + `language` text COMMENT 'Language information about this post', + `app` varchar(255) NOT NULL DEFAULT '' COMMENT 'application which generated this item', + `rendered-hash` varchar(32) NOT NULL DEFAULT '' COMMENT '', + `rendered-html` mediumtext COMMENT 'item.body converted to html', + `object-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams object type', + `object` text COMMENT 'JSON encoded object structure unless it is an implied object (normal post)', + `target-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams target type if applicable (URI)', + `target` text COMMENT 'JSON encoded target structure if used', + `resource-id` varchar(32) NOT NULL DEFAULT '' COMMENT 'Used to link other tables to items, it identifies the linked resource (e.g. photo) and if set must also set resource_type', + `plink` varchar(255) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', + PRIMARY KEY(`uri-id`,`edited`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Post history'; + +-- +-- TABLE post-link +-- +CREATE TABLE IF NOT EXISTS `post-link` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `url` varbinary(511) NOT NULL COMMENT 'External URL', + `mimetype` varchar(60) COMMENT '', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri-id-url` (`uri-id`,`url`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Post related external links'; + -- -- TABLE post-media -- @@ -1127,9 +1219,36 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `publisher-image` varbinary(255) COMMENT 'Image of the publisher of the media', PRIMARY KEY(`id`), UNIQUE INDEX `uri-id-url` (`uri-id`,`url`), + INDEX `uri-id-id` (`uri-id`,`id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Attached media'; +-- +-- TABLE post-question +-- +CREATE TABLE IF NOT EXISTS `post-question` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `multiple` boolean NOT NULL DEFAULT '0' COMMENT 'Multiple choice', + `voters` int unsigned COMMENT 'Number of voters for this question', + `end-time` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Question end time', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Question'; + +-- +-- TABLE post-question-option +-- +CREATE TABLE IF NOT EXISTS `post-question-option` ( + `id` int unsigned NOT NULL COMMENT 'Id of the question', + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `name` varchar(255) COMMENT 'Name of the option', + `replies` int unsigned COMMENT 'Number of replies for this question option', + PRIMARY KEY(`uri-id`,`id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Question option'; + -- -- TABLE post-tag -- @@ -1253,13 +1372,13 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date that something in the conversation changed, indicating clients should fetch the conversation again', `commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id which owns this copy of the item', - `pinned` boolean NOT NULL DEFAULT '0' COMMENT 'The thread is pinned on the profile page', + `pinned` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `starred` boolean NOT NULL DEFAULT '0' COMMENT '', `ignored` boolean NOT NULL DEFAULT '0' COMMENT 'Ignore updates for this thread', `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid', `mention` boolean NOT NULL DEFAULT '0' COMMENT '', `pubmail` boolean NOT NULL DEFAULT '0' COMMENT '', - `forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Deprecated', `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id', `unseen` boolean NOT NULL DEFAULT '1' COMMENT 'post has not been seen', `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'Marker to hide the post from the user', @@ -1277,7 +1396,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( INDEX `post-user-id` (`post-user-id`), INDEX `commented` (`commented`), INDEX `uid_received` (`uid`,`received`), - INDEX `uid_pinned` (`uid`,`pinned`), + INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_commented` (`uid`,`commented`), INDEX `uid_starred` (`uid`,`starred`), INDEX `uid_mention` (`uid`,`mention`), @@ -1308,10 +1427,11 @@ CREATE TABLE IF NOT EXISTS `post-user-notification` ( -- TABLE process -- CREATE TABLE IF NOT EXISTS `process` ( - `pid` int unsigned NOT NULL COMMENT '', + `pid` int unsigned NOT NULL COMMENT 'The ID of the process', + `hostname` varchar(32) NOT NULL COMMENT 'The name of the host the process is ran on', `command` varbinary(32) NOT NULL DEFAULT '' COMMENT '', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - PRIMARY KEY(`pid`), + PRIMARY KEY(`pid`,`hostname`), INDEX `command` (`command`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Currently running system processes'; @@ -1356,7 +1476,8 @@ CREATE TABLE IF NOT EXISTS `profile` ( `education` text COMMENT 'Deprecated', `contact` text COMMENT 'Deprecated', `homepage` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', + `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', `thumb` varchar(255) NOT NULL DEFAULT '' COMMENT '', `publish` boolean NOT NULL DEFAULT '0' COMMENT 'publish default profile in local directory', @@ -1367,23 +1488,6 @@ CREATE TABLE IF NOT EXISTS `profile` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='user profiles data'; --- --- TABLE profile_check --- -CREATE TABLE IF NOT EXISTS `profile_check` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id', - `dfrn_id` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `sec` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '', - PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `cid` (`cid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='DFRN remote auth use'; - -- -- TABLE profile_field -- @@ -1476,21 +1580,28 @@ CREATE TABLE IF NOT EXISTS `storage` ( ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend'; -- --- TABLE tokens +-- TABLE subscription -- -CREATE TABLE IF NOT EXISTS `tokens` ( - `id` varchar(40) NOT NULL COMMENT '', - `secret` text COMMENT '', - `client_id` varchar(20) NOT NULL DEFAULT '', - `expires` int NOT NULL DEFAULT 0 COMMENT '', - `scope` varchar(200) NOT NULL DEFAULT '' COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', +CREATE TABLE IF NOT EXISTS `subscription` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented image data id', + `application-id` int unsigned NOT NULL COMMENT '', + `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id', + `endpoint` varchar(511) COMMENT 'Endpoint URL', + `pubkey` varchar(127) COMMENT 'User agent public key', + `secret` varchar(32) COMMENT 'Auth secret', + `follow` boolean COMMENT '', + `favourite` boolean COMMENT '', + `reblog` boolean COMMENT '', + `mention` boolean COMMENT '', + `poll` boolean COMMENT '', + `follow_request` boolean COMMENT '', + `status` boolean COMMENT '', PRIMARY KEY(`id`), - INDEX `client_id` (`client_id`), - INDEX `uid` (`uid`), - FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE RESTRICT ON DELETE CASCADE, + UNIQUE INDEX `application-id_uid` (`application-id`,`uid`), + INDEX `uid_application-id` (`uid`,`application-id`), + FOREIGN KEY (`application-id`) REFERENCES `application` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth usage'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Push Subscription for the API'; -- -- TABLE userd @@ -1508,13 +1619,30 @@ CREATE TABLE IF NOT EXISTS `userd` ( CREATE TABLE IF NOT EXISTS `user-contact` ( `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Contact id of the linked public contact', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', + `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', `blocked` boolean COMMENT 'Contact is completely blocked for this user', `ignored` boolean COMMENT 'Posts from this contact are ignored', `collapsed` boolean COMMENT 'Posts from this contact are collapsed', + `hidden` boolean COMMENT 'This contact is hidden from the others', + `is-blocked` boolean COMMENT 'User is blocked by this contact', + `pending` boolean COMMENT '', + `rel` tinyint unsigned COMMENT 'The kind of the relation between the user and the contact', + `info` mediumtext COMMENT '', + `notify_new_posts` boolean COMMENT '', + `remote_self` boolean COMMENT '', + `fetch_further_information` tinyint unsigned COMMENT '', + `ffi_keyword_denylist` text COMMENT '', + `subhub` boolean COMMENT '', + `hub-verify` varchar(255) COMMENT '', + `protocol` char(4) COMMENT 'Protocol of the contact', + `rating` tinyint COMMENT 'Automatically detected feed poll frequency', + `priority` tinyint unsigned COMMENT 'Feed poll priority', PRIMARY KEY(`uid`,`cid`), INDEX `cid` (`cid`), + UNIQUE INDEX `uri-id_uid` (`uri-id`,`uid`), FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User specific public contact data'; -- @@ -1526,31 +1654,6 @@ CREATE TABLE IF NOT EXISTS `worker-ipc` ( PRIMARY KEY(`key`) ) ENGINE=MEMORY DEFAULT COLLATE utf8mb4_general_ci COMMENT='Inter process communication between the frontend and the worker'; --- --- TABLE workerqueue --- -CREATE TABLE IF NOT EXISTS `workerqueue` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented worker task id', - `command` varchar(100) COMMENT 'Task command', - `parameter` mediumtext COMMENT 'Task parameter', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Task priority', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date', - `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process id of the worker', - `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Execution date', - `next_try` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Next retrial date', - `retrial` tinyint NOT NULL DEFAULT 0 COMMENT 'Retrial counter', - `done` boolean NOT NULL DEFAULT '0' COMMENT 'Marked 1 when the task was done - will be deleted later', - PRIMARY KEY(`id`), - INDEX `command` (`command`), - INDEX `done_command_parameter` (`done`,`command`,`parameter`(64)), - INDEX `done_executed` (`done`,`executed`), - INDEX `done_priority_retrial_created` (`done`,`priority`,`retrial`,`created`), - INDEX `done_priority_next_try` (`done`,`priority`,`next_try`), - INDEX `done_pid_next_try` (`done`,`pid`,`next_try`), - INDEX `done_pid_retrial` (`done`,`pid`,`retrial`), - INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries'; - -- -- VIEW application-view -- @@ -1605,14 +1708,13 @@ CREATE VIEW `post-user-view` AS SELECT `post-thread-user`.`pubmail` AS `pubmail`, `post-user`.`visible` AS `visible`, `post-thread-user`.`starred` AS `starred`, - `post-thread-user`.`pinned` AS `pinned`, `post-user`.`unseen` AS `unseen`, `post-user`.`deleted` AS `deleted`, `post-user`.`origin` AS `origin`, `post-thread-user`.`origin` AS `parent-origin`, - `post-thread-user`.`forum_mode` AS `forum_mode`, `post-thread-user`.`mention` AS `mention`, `post-user`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-user`.`uri-id`) AS `featured`, `post-user`.`network` AS `network`, `post-user`.`vid` AS `vid`, `post-user`.`psid` AS `psid`, @@ -1620,7 +1722,7 @@ CREATE VIEW `post-user-view` AS SELECT `post-content`.`title` AS `title`, `post-content`.`content-warning` AS `content-warning`, `post-content`.`raw-body` AS `raw-body`, - `post-content`.`body` AS `body`, + IFNULL (`post-content`.`body`, '') AS `body`, `post-content`.`rendered-hash` AS `rendered-hash`, `post-content`.`rendered-html` AS `rendered-html`, `post-content`.`language` AS `language`, @@ -1657,7 +1759,6 @@ CREATE VIEW `post-user-view` AS SELECT `contact`.`uri-date` AS `uri-date`, `contact`.`avatar-date` AS `avatar-date`, `contact`.`thumb` AS `thumb`, - `contact`.`dfrn-id` AS `dfrn-id`, `post-user`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -1667,6 +1768,9 @@ CREATE VIEW `post-user-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, + `author`.`uri-id` AS `author-uri-id`, `post-user`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -1676,6 +1780,7 @@ CREATE VIEW `post-user-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-user`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -1706,14 +1811,20 @@ CREATE VIEW `post-user-view` AS SELECT `event`.`location` AS `event-location`, `event`.`type` AS `event-type`, `event`.`nofinish` AS `event-nofinish`, - `event`.`adjust` AS `event-adjust`, `event`.`ignore` AS `event-ignore`, + `post-question`.`id` AS `question-id`, + `post-question`.`multiple` AS `question-multiple`, + `post-question`.`voters` AS `question-voters`, + `post-question`.`end-time` AS `question-end-time`, + EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-user`.`uri-id` AND `post-category`.`uid` = `post-user`.`uid`) AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-user`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, `parent-post`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, + `parent-post-author`.`nick` AS `parent-author-nick`, `parent-post-author`.`network` AS `parent-author-network`, `parent-post-author`.`blocked` AS `parent-author-blocked`, `parent-post-author`.`hidden` AS `parent-author-hidden` @@ -1732,6 +1843,7 @@ CREATE VIEW `post-user-view` AS SELECT LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-user`.`uri-id` AND `post-user`.`origin` + LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-user`.`psid` LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -1768,13 +1880,12 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-thread-user`.`ignored` AS `ignored`, `post-user`.`visible` AS `visible`, `post-thread-user`.`starred` AS `starred`, - `post-thread-user`.`pinned` AS `pinned`, `post-thread-user`.`unseen` AS `unseen`, `post-user`.`deleted` AS `deleted`, `post-thread-user`.`origin` AS `origin`, - `post-thread-user`.`forum_mode` AS `forum_mode`, `post-thread-user`.`mention` AS `mention`, `post-user`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-thread-user`.`uri-id`) AS `featured`, `post-thread-user`.`network` AS `network`, `post-user`.`vid` AS `vid`, `post-thread-user`.`psid` AS `psid`, @@ -1819,7 +1930,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT `contact`.`uri-date` AS `uri-date`, `contact`.`avatar-date` AS `avatar-date`, `contact`.`thumb` AS `thumb`, - `contact`.`dfrn-id` AS `dfrn-id`, `post-thread-user`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -1829,6 +1939,9 @@ CREATE VIEW `post-thread-user-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, + `author`.`uri-id` AS `author-uri-id`, `post-thread-user`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -1838,6 +1951,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread-user`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -1868,8 +1982,13 @@ CREATE VIEW `post-thread-user-view` AS SELECT `event`.`location` AS `event-location`, `event`.`type` AS `event-type`, `event`.`nofinish` AS `event-nofinish`, - `event`.`adjust` AS `event-adjust`, `event`.`ignore` AS `event-ignore`, + `post-question`.`id` AS `question-id`, + `post-question`.`multiple` AS `question-multiple`, + `post-question`.`voters` AS `question-voters`, + `post-question`.`end-time` AS `question-end-time`, + EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-thread-user`.`uri-id` AND `post-category`.`uid` = `post-thread-user`.`uid`) AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread-user`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -1894,6 +2013,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-thread-user`.`uri-id` AND `post-thread-user`.`origin` + LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-thread-user`.`psid` LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-thread-user`.`uid` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -1923,6 +2043,7 @@ CREATE VIEW `post-view` AS SELECT `post`.`visible` AS `visible`, `post`.`deleted` AS `deleted`, `post`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post`.`uri-id`) AS `featured`, `post`.`network` AS `network`, `post`.`vid` AS `vid`, IF (`post`.`vid` IS NULL, '', `verb`.`name`) AS `verb`, @@ -1966,7 +2087,6 @@ CREATE VIEW `post-view` AS SELECT `author`.`uri-date` AS `uri-date`, `author`.`avatar-date` AS `avatar-date`, `author`.`thumb` AS `thumb`, - `author`.`dfrn-id` AS `dfrn-id`, `post`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -1976,6 +2096,9 @@ CREATE VIEW `post-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, + `author`.`uri-id` AS `author-uri-id`, `post`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -1985,6 +2108,7 @@ CREATE VIEW `post-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -1996,6 +2120,12 @@ CREATE VIEW `post-view` AS SELECT `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, `causer`.`contact-type` AS `causer-contact-type`, + `post-question`.`id` AS `question-id`, + `post-question`.`multiple` AS `question-multiple`, + `post-question`.`voters` AS `question-voters`, + `post-question`.`end-time` AS `question-end-time`, + 0 AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -2017,6 +2147,7 @@ CREATE VIEW `post-view` AS SELECT LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post`.`uri-id` + LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post`.`uri-id` LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -2045,6 +2176,7 @@ CREATE VIEW `post-thread-view` AS SELECT `post`.`visible` AS `visible`, `post`.`deleted` AS `deleted`, `post`.`global` AS `global`, + EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-thread`.`uri-id`) AS `featured`, `post-thread`.`network` AS `network`, `post`.`vid` AS `vid`, IF (`post`.`vid` IS NULL, '', `verb`.`name`) AS `verb`, @@ -2088,7 +2220,6 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`uri-date` AS `uri-date`, `author`.`avatar-date` AS `avatar-date`, `author`.`thumb` AS `thumb`, - `author`.`dfrn-id` AS `dfrn-id`, `post-thread`.`author-id` AS `author-id`, `author`.`url` AS `author-link`, `author`.`addr` AS `author-addr`, @@ -2098,6 +2229,9 @@ CREATE VIEW `post-thread-view` AS SELECT `author`.`network` AS `author-network`, `author`.`blocked` AS `author-blocked`, `author`.`hidden` AS `author-hidden`, + `author`.`updated` AS `author-updated`, + `author`.`gsid` AS `author-gsid`, + `author`.`uri-id` AS `author-uri-id`, `post-thread`.`owner-id` AS `owner-id`, `owner`.`url` AS `owner-link`, `owner`.`addr` AS `owner-addr`, @@ -2107,6 +2241,7 @@ CREATE VIEW `post-thread-view` AS SELECT `owner`.`network` AS `owner-network`, `owner`.`blocked` AS `owner-blocked`, `owner`.`hidden` AS `owner-hidden`, + `owner`.`updated` AS `owner-updated`, `owner`.`contact-type` AS `owner-contact-type`, `post-thread`.`causer-id` AS `causer-id`, `causer`.`url` AS `causer-link`, @@ -2118,6 +2253,12 @@ CREATE VIEW `post-thread-view` AS SELECT `causer`.`blocked` AS `causer-blocked`, `causer`.`hidden` AS `causer-hidden`, `causer`.`contact-type` AS `causer-contact-type`, + `post-question`.`id` AS `question-id`, + `post-question`.`multiple` AS `question-multiple`, + `post-question`.`voters` AS `question-voters`, + `post-question`.`end-time` AS `question-end-time`, + 0 AS `has-categories`, + EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread`.`uri-id`) AS `has-media`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, @@ -2139,6 +2280,7 @@ CREATE VIEW `post-thread-view` AS SELECT LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread`.`uri-id` + LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -2156,6 +2298,24 @@ CREATE VIEW `category-view` AS SELECT FROM `post-category` LEFT JOIN `tag` ON `post-category`.`tid` = `tag`.`id`; +-- +-- VIEW collection-view +-- +DROP VIEW IF EXISTS `collection-view`; +CREATE VIEW `collection-view` AS SELECT + `post-collection`.`uri-id` AS `uri-id`, + `post-collection`.`type` AS `type`, + `post`.`author-id` AS `cid`, + `post`.`received` AS `received`, + `post`.`created` AS `created`, + `post-thread`.`commented` AS `commented`, + `post`.`thr-parent-id` AS `thr-parent-id`, + `post`.`author-id` AS `author-id`, + `post`.`gravity` AS `gravity` + FROM `post-collection` + INNER JOIN `post` ON `post-collection`.`uri-id` = `post`.`uri-id` + INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id`; + -- -- VIEW tag-view -- @@ -2166,7 +2326,8 @@ CREATE VIEW `tag-view` AS SELECT `post-tag`.`tid` AS `tid`, `post-tag`.`cid` AS `cid`, CASE `cid` WHEN 0 THEN `tag`.`name` ELSE `contact`.`name` END AS `name`, - CASE `cid` WHEN 0 THEN `tag`.`url` ELSE `contact`.`url` END AS `url` + CASE `cid` WHEN 0 THEN `tag`.`url` ELSE `contact`.`url` END AS `url`, + CASE `cid` WHEN 0 THEN `tag`.`type` ELSE 1 END AS `tag-type` FROM `post-tag` LEFT JOIN `tag` ON `post-tag`.`tid` = `tag`.`id` LEFT JOIN `contact` ON `post-tag`.`cid` = `contact`.`id`; @@ -2243,7 +2404,6 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`self` AS `self`, `contact`.`remote_self` AS `remote_self`, `contact`.`rel` AS `rel`, - `contact`.`duplex` AS `duplex`, `contact`.`network` AS `network`, `contact`.`protocol` AS `protocol`, `contact`.`name` AS `name`, @@ -2251,19 +2411,17 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`location` AS `location`, `contact`.`about` AS `about`, `contact`.`keywords` AS `keywords`, - `contact`.`gender` AS `gender`, `contact`.`xmpp` AS `xmpp`, + `contact`.`matrix` AS `matrix`, `contact`.`attag` AS `attag`, `contact`.`avatar` AS `avatar`, `contact`.`photo` AS `photo`, `contact`.`thumb` AS `thumb`, `contact`.`micro` AS `micro`, `contact`.`header` AS `header`, - `contact`.`site-pubkey` AS `site-pubkey`, - `contact`.`issued-id` AS `issued-id`, - `contact`.`dfrn-id` AS `dfrn-id`, `contact`.`url` AS `url`, `contact`.`nurl` AS `nurl`, + `contact`.`uri-id` AS `uri-id`, `contact`.`addr` AS `addr`, `contact`.`alias` AS `alias`, `contact`.`pubkey` AS `pubkey`, @@ -2274,9 +2432,6 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`poll` AS `poll`, `contact`.`confirm` AS `confirm`, `contact`.`poco` AS `poco`, - `contact`.`aes_allow` AS `aes_allow`, - `contact`.`ret-aes` AS `ret-aes`, - `contact`.`usehub` AS `usehub`, `contact`.`subhub` AS `subhub`, `contact`.`hub-verify` AS `hub-verify`, `contact`.`last-update` AS `last-update`, @@ -2305,9 +2460,7 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`sensitive` AS `sensitive`, `contact`.`baseurl` AS `baseurl`, `contact`.`reason` AS `reason`, - `contact`.`closeness` AS `closeness`, `contact`.`info` AS `info`, - `contact`.`profile-id` AS `profile-id`, `contact`.`bdyear` AS `bdyear`, `contact`.`bd` AS `bd`, `contact`.`notify_new_posts` AS `notify_new_posts`, @@ -2367,6 +2520,193 @@ CREATE VIEW `owner-view` AS SELECT INNER JOIN `contact` ON `contact`.`uid` = `user`.`uid` AND `contact`.`self` INNER JOIN `profile` ON `profile`.`uid` = `user`.`uid`; +-- +-- VIEW account-view +-- +DROP VIEW IF EXISTS `account-view`; +CREATE VIEW `account-view` AS SELECT + `contact`.`id` AS `id`, + `contact`.`url` AS `url`, + `contact`.`nurl` AS `nurl`, + `contact`.`uri-id` AS `uri-id`, + `item-uri`.`guid` AS `guid`, + `contact`.`addr` AS `addr`, + `contact`.`alias` AS `alias`, + `contact`.`name` AS `name`, + `contact`.`nick` AS `nick`, + `contact`.`about` AS `about`, + `contact`.`keywords` AS `keywords`, + `contact`.`xmpp` AS `xmpp`, + `contact`.`matrix` AS `matrix`, + `contact`.`avatar` AS `avatar`, + `contact`.`photo` AS `photo`, + `contact`.`thumb` AS `thumb`, + `contact`.`micro` AS `micro`, + `contact`.`header` AS `header`, + `contact`.`created` AS `created`, + `contact`.`updated` AS `updated`, + `contact`.`network` AS `network`, + `contact`.`protocol` AS `protocol`, + `contact`.`location` AS `location`, + `contact`.`attag` AS `attag`, + `contact`.`pubkey` AS `pubkey`, + `contact`.`prvkey` AS `prvkey`, + `contact`.`subscribe` AS `subscribe`, + `contact`.`last-update` AS `last-update`, + `contact`.`success_update` AS `success_update`, + `contact`.`failure_update` AS `failure_update`, + `contact`.`failed` AS `failed`, + `contact`.`last-item` AS `last-item`, + `contact`.`last-discovery` AS `last-discovery`, + `contact`.`contact-type` AS `contact-type`, + `contact`.`manually-approve` AS `manually-approve`, + `contact`.`unsearchable` AS `unsearchable`, + `contact`.`sensitive` AS `sensitive`, + `contact`.`baseurl` AS `baseurl`, + `contact`.`gsid` AS `gsid`, + `contact`.`info` AS `info`, + `contact`.`bdyear` AS `bdyear`, + `contact`.`bd` AS `bd`, + `contact`.`poco` AS `poco`, + `contact`.`name-date` AS `name-date`, + `contact`.`uri-date` AS `uri-date`, + `contact`.`avatar-date` AS `avatar-date`, + `contact`.`term-date` AS `term-date`, + `contact`.`hidden` AS `global-ignored`, + `contact`.`blocked` AS `global-blocked`, + `contact`.`hidden` AS `hidden`, + `contact`.`archive` AS `archive`, + `contact`.`deleted` AS `deleted`, + `contact`.`blocked` AS `blocked`, + `contact`.`notify` AS `dfrn-notify`, + `contact`.`poll` AS `dfrn-poll`, + `fcontact`.`guid` AS `diaspora-guid`, + `fcontact`.`batch` AS `diaspora-batch`, + `fcontact`.`notify` AS `diaspora-notify`, + `fcontact`.`poll` AS `diaspora-poll`, + `fcontact`.`alias` AS `diaspora-alias`, + `apcontact`.`uuid` AS `ap-uuid`, + `apcontact`.`type` AS `ap-type`, + `apcontact`.`following` AS `ap-following`, + `apcontact`.`followers` AS `ap-followers`, + `apcontact`.`inbox` AS `ap-inbox`, + `apcontact`.`outbox` AS `ap-outbox`, + `apcontact`.`sharedinbox` AS `ap-sharedinbox`, + `apcontact`.`generator` AS `ap-generator`, + `apcontact`.`following_count` AS `ap-following_count`, + `apcontact`.`followers_count` AS `ap-followers_count`, + `apcontact`.`statuses_count` AS `ap-statuses_count`, + `gserver`.`site_name` AS `site_name`, + `gserver`.`platform` AS `platform`, + `gserver`.`version` AS `version` + FROM `contact` + LEFT JOIN `item-uri` ON `item-uri`.`id` = `contact`.`uri-id` + LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id` + LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id` + LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid` + WHERE `contact`.`uid` = 0; + +-- +-- VIEW account-user-view +-- +DROP VIEW IF EXISTS `account-user-view`; +CREATE VIEW `account-user-view` AS SELECT + `ucontact`.`id` AS `id`, + `contact`.`id` AS `pid`, + `ucontact`.`uid` AS `uid`, + `contact`.`url` AS `url`, + `contact`.`nurl` AS `nurl`, + `contact`.`uri-id` AS `uri-id`, + `item-uri`.`guid` AS `guid`, + `contact`.`addr` AS `addr`, + `contact`.`alias` AS `alias`, + `contact`.`name` AS `name`, + `contact`.`nick` AS `nick`, + `contact`.`about` AS `about`, + `contact`.`keywords` AS `keywords`, + `contact`.`xmpp` AS `xmpp`, + `contact`.`matrix` AS `matrix`, + `contact`.`avatar` AS `avatar`, + `contact`.`photo` AS `photo`, + `contact`.`thumb` AS `thumb`, + `contact`.`micro` AS `micro`, + `contact`.`header` AS `header`, + `contact`.`created` AS `created`, + `contact`.`updated` AS `updated`, + `ucontact`.`self` AS `self`, + `ucontact`.`remote_self` AS `remote_self`, + `ucontact`.`rel` AS `rel`, + `contact`.`network` AS `network`, + `ucontact`.`protocol` AS `protocol`, + `contact`.`location` AS `location`, + `ucontact`.`attag` AS `attag`, + `contact`.`pubkey` AS `pubkey`, + `contact`.`prvkey` AS `prvkey`, + `contact`.`subscribe` AS `subscribe`, + `contact`.`last-update` AS `last-update`, + `contact`.`success_update` AS `success_update`, + `contact`.`failure_update` AS `failure_update`, + `contact`.`failed` AS `failed`, + `contact`.`last-item` AS `last-item`, + `contact`.`last-discovery` AS `last-discovery`, + `contact`.`contact-type` AS `contact-type`, + `contact`.`manually-approve` AS `manually-approve`, + `contact`.`unsearchable` AS `unsearchable`, + `contact`.`sensitive` AS `sensitive`, + `contact`.`baseurl` AS `baseurl`, + `contact`.`gsid` AS `gsid`, + `ucontact`.`info` AS `info`, + `contact`.`bdyear` AS `bdyear`, + `contact`.`bd` AS `bd`, + `contact`.`poco` AS `poco`, + `contact`.`name-date` AS `name-date`, + `contact`.`uri-date` AS `uri-date`, + `contact`.`avatar-date` AS `avatar-date`, + `contact`.`term-date` AS `term-date`, + `contact`.`hidden` AS `global-ignored`, + `contact`.`blocked` AS `global-blocked`, + `ucontact`.`hidden` AS `hidden`, + `ucontact`.`archive` AS `archive`, + `ucontact`.`pending` AS `pending`, + `ucontact`.`deleted` AS `deleted`, + `ucontact`.`notify_new_posts` AS `notify_new_posts`, + `ucontact`.`fetch_further_information` AS `fetch_further_information`, + `ucontact`.`ffi_keyword_denylist` AS `ffi_keyword_denylist`, + `ucontact`.`rating` AS `rating`, + `ucontact`.`readonly` AS `readonly`, + `ucontact`.`blocked` AS `blocked`, + `ucontact`.`block_reason` AS `block_reason`, + `ucontact`.`subhub` AS `subhub`, + `ucontact`.`hub-verify` AS `hub-verify`, + `ucontact`.`reason` AS `reason`, + `contact`.`notify` AS `dfrn-notify`, + `contact`.`poll` AS `dfrn-poll`, + `fcontact`.`guid` AS `diaspora-guid`, + `fcontact`.`batch` AS `diaspora-batch`, + `fcontact`.`notify` AS `diaspora-notify`, + `fcontact`.`poll` AS `diaspora-poll`, + `fcontact`.`alias` AS `diaspora-alias`, + `apcontact`.`uuid` AS `ap-uuid`, + `apcontact`.`type` AS `ap-type`, + `apcontact`.`following` AS `ap-following`, + `apcontact`.`followers` AS `ap-followers`, + `apcontact`.`inbox` AS `ap-inbox`, + `apcontact`.`outbox` AS `ap-outbox`, + `apcontact`.`sharedinbox` AS `ap-sharedinbox`, + `apcontact`.`generator` AS `ap-generator`, + `apcontact`.`following_count` AS `ap-following_count`, + `apcontact`.`followers_count` AS `ap-followers_count`, + `apcontact`.`statuses_count` AS `ap-statuses_count`, + `gserver`.`site_name` AS `site_name`, + `gserver`.`platform` AS `platform`, + `gserver`.`version` AS `version` + FROM `contact` AS `ucontact` + INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0 + LEFT JOIN `item-uri` ON `item-uri`.`id` = `ucontact`.`uri-id` + LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id` + LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr' + LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`; + -- -- VIEW pending-view -- @@ -2421,3 +2761,23 @@ CREATE VIEW `workerqueue-view` AS SELECT FROM `process` INNER JOIN `workerqueue` ON `workerqueue`.`pid` = `process`.`pid` WHERE NOT `workerqueue`.`done`; + +-- +-- VIEW profile_field-view +-- +DROP VIEW IF EXISTS `profile_field-view`; +CREATE VIEW `profile_field-view` AS SELECT + `profile_field`.`id` AS `id`, + `profile_field`.`uid` AS `uid`, + `profile_field`.`label` AS `label`, + `profile_field`.`value` AS `value`, + `profile_field`.`order` AS `order`, + `profile_field`.`psid` AS `psid`, + `permissionset`.`allow_cid` AS `allow_cid`, + `permissionset`.`allow_gid` AS `allow_gid`, + `permissionset`.`deny_cid` AS `deny_cid`, + `permissionset`.`deny_gid` AS `deny_gid`, + `profile_field`.`created` AS `created`, + `profile_field`.`edited` AS `edited` + FROM `profile_field` + INNER JOIN `permissionset` ON `permissionset`.`id` = `profile_field`.`psid`;