X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=2e7a189b39290ff8be6e8011acaadab30a7c244a;hb=2ff5889e9a8cdfa6407f72ab821550023b97bfef;hp=5522b574bbf4cfa833a9e1c5ee74b0202ca1f536;hpb=c1047d699772a6a6b192184df70709cff34675ed;p=friendica.git diff --git a/database.sql b/database.sql index 5522b574bb..2e7a189b39 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.03-rc (Red Hot Poker) --- DB_UPDATE_VERSION 1410 +-- Friendica 2021.09-dev (Siberian Iris) +-- DB_UPDATE_VERSION 1429 -- ------------------------------------------ @@ -94,6 +94,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 -- @@ -120,11 +132,13 @@ CREATE TABLE IF NOT EXISTS `contact` ( `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', @@ -197,23 +211,16 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `uid_network_self_lastupdate` (`uid`,`network`,`self`,`last-update`), INDEX `uid_lastitem` (`uid`,`last-item`), INDEX `baseurl` (`baseurl`(64)), + INDEX `uid_contact-type` (`uid`,`contact-type`), + 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 -- @@ -328,6 +335,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 '', @@ -336,10 +344,12 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `outbox` varchar(255) COMMENT '', `sharedinbox` varchar(255) COMMENT '', `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 '', `photo` varchar(255) COMMENT '', + `header` varchar(255) COMMENT 'Header picture', `addr` varchar(255) COMMENT '', `alias` varchar(255) COMMENT '', `pubkey` text COMMENT '', @@ -358,9 +368,50 @@ 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'; +-- +-- TABLE application +-- +CREATE TABLE IF NOT EXISTS `application` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'generated index', + `client_id` varchar(64) NOT NULL COMMENT '', + `client_secret` varchar(64) NOT NULL COMMENT '', + `name` varchar(255) NOT NULL COMMENT '', + `redirect_uri` varchar(255) NOT NULL COMMENT '', + `website` varchar(255) COMMENT '', + `scopes` varchar(255) COMMENT '', + `read` boolean COMMENT 'Read scope', + `write` boolean COMMENT 'Write scope', + `follow` boolean COMMENT 'Follow scope', + `push` boolean COMMENT 'Push scope', + PRIMARY KEY(`id`), + UNIQUE INDEX `client_id` (`client_id`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth application'; + +-- +-- TABLE application-token +-- +CREATE TABLE IF NOT EXISTS `application-token` ( + `application-id` int unsigned NOT NULL COMMENT '', + `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id', + `code` varchar(64) NOT NULL COMMENT '', + `access_token` varchar(64) NOT NULL COMMENT '', + `created_at` datetime NOT NULL COMMENT 'creation time', + `scopes` varchar(255) COMMENT '', + `read` boolean COMMENT 'Read scope', + `write` boolean COMMENT 'Write scope', + `follow` boolean COMMENT 'Follow scope', + `push` boolean COMMENT 'Push scope', + PRIMARY KEY(`application-id`,`uid`), + INDEX `uid_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 user token'; + -- -- TABLE attach -- @@ -518,6 +569,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', @@ -536,8 +588,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'; -- @@ -547,6 +601,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 '', @@ -563,7 +618,9 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', 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'; -- @@ -709,6 +766,7 @@ CREATE TABLE IF NOT EXISTS `mail` ( `from-photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'contact photo link of the sender', `from-url` varchar(255) NOT NULL DEFAULT '' COMMENT 'profile linke of the sender', `contact-id` varchar(255) COMMENT 'contact.id', + `author-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the author of the mail', `convid` int unsigned COMMENT 'conv.id', `title` varchar(255) NOT NULL DEFAULT '' COMMENT '', `body` mediumtext COMMENT '', @@ -717,7 +775,11 @@ CREATE TABLE IF NOT EXISTS `mail` ( `replied` boolean NOT NULL DEFAULT '0' COMMENT '', `unknown` boolean NOT NULL DEFAULT '0' COMMENT 'if sender not in the contact table this is 1', `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-id` int unsigned COMMENT 'Item-uri id of the related mail', `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related mail', + `thr-parent` varchar(255) COMMENT '', + `thr-parent-id` int unsigned COMMENT 'Id of the item-uri table that contains the thread parent uri', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time of the private message', PRIMARY KEY(`id`), INDEX `uid_seen` (`uid`,`seen`), @@ -725,7 +787,15 @@ CREATE TABLE IF NOT EXISTS `mail` ( INDEX `uri` (`uri`(64)), INDEX `parent-uri` (`parent-uri`(64)), INDEX `contactid` (`contact-id`(32)), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `author-id` (`author-id`), + INDEX `uri-id` (`uri-id`), + INDEX `parent-uri-id` (`parent-uri-id`), + INDEX `thr-parent-id` (`thr-parent-id`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + 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, + FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages'; -- @@ -764,6 +834,33 @@ CREATE TABLE IF NOT EXISTS `manage` ( FOREIGN KEY (`mid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='table of accounts that can manage each other'; +-- +-- TABLE notification +-- +CREATE TABLE IF NOT EXISTS `notification` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uid` mediumint unsigned COMMENT 'Owner User id', + `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', + `type` tinyint unsigned COMMENT '', + `actor-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the actor that caused the 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 '', + PRIMARY KEY(`id`), + UNIQUE INDEX `uid_vid_type_actor-id_target-uri-id` (`uid`,`vid`,`type`,`actor-id`,`target-uri-id`), + INDEX `vid` (`vid`), + INDEX `actor-id` (`actor-id`), + INDEX `target-uri-id` (`target-uri-id`), + INDEX `parent-uri-id` (`parent-uri-id`), + INDEX `seen_uid` (`seen`,`uid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + FOREIGN KEY (`actor-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`target-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'; + -- -- TABLE notify -- @@ -931,7 +1028,7 @@ CREATE TABLE IF NOT EXISTS `post` ( `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the owner of this item', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', `causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', - `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, bookmark, ...)', + `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, image, article, ...)', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', `global` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -1018,6 +1115,19 @@ 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-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 -- @@ -1034,6 +1144,13 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `preview-height` smallint unsigned COMMENT 'Height of the preview picture', `preview-width` smallint unsigned COMMENT 'Width of the preview picture', `description` text COMMENT '', + `name` varchar(255) COMMENT 'Name of the media', + `author-url` varbinary(255) COMMENT 'URL of the author of the media', + `author-name` varchar(255) COMMENT 'Name of the author of the media', + `author-image` varbinary(255) COMMENT 'Image of the author of the media', + `publisher-url` varbinary(255) COMMENT 'URL of the publisher of the media', + `publisher-name` varchar(255) COMMENT 'Name of the publisher of the media', + `publisher-image` varbinary(255) COMMENT 'Image of the publisher of the media', 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 @@ -1097,7 +1214,8 @@ CREATE TABLE IF NOT EXISTS `post-user` ( `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the owner of this item', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', `causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', - `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, bookmark, ...)', + `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, image, article, ...)', + `post-reason` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Reason why the post arrived at the user', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', `global` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -1185,6 +1303,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_wall_received` (`uid`,`wall`,`received`), INDEX `uid_pinned` (`uid`,`pinned`), INDEX `uid_commented` (`uid`,`commented`), INDEX `uid_starred` (`uid`,`starred`), @@ -1459,6 +1578,29 @@ 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'; +-- +-- VIEW application-view +-- +DROP VIEW IF EXISTS `application-view`; +CREATE VIEW `application-view` AS SELECT + `application`.`id` AS `id`, + `application-token`.`uid` AS `uid`, + `application`.`name` AS `name`, + `application`.`redirect_uri` AS `redirect_uri`, + `application`.`website` AS `website`, + `application`.`client_id` AS `client_id`, + `application`.`client_secret` AS `client_secret`, + `application-token`.`code` AS `code`, + `application-token`.`access_token` AS `access_token`, + `application-token`.`created_at` AS `created_at`, + `application-token`.`scopes` AS `scopes`, + `application-token`.`read` AS `read`, + `application-token`.`write` AS `write`, + `application-token`.`follow` AS `follow`, + `application-token`.`push` AS `push` + FROM `application-token` + INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id`; + -- -- VIEW post-user-view -- @@ -1485,10 +1627,12 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`received` AS `received`, `post-thread-user`.`changed` AS `changed`, `post-user`.`post-type` AS `post-type`, + `post-user`.`post-reason` AS `post-reason`, `post-user`.`private` AS `private`, `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`, @@ -1597,7 +1741,9 @@ CREATE VIEW `post-user-view` AS SELECT `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`.`network` AS `parent-author-network` + `parent-post-author`.`network` AS `parent-author-network`, + `parent-post-author`.`blocked` AS `parent-author-blocked`, + `parent-post-author`.`hidden` AS `parent-author-hidden` FROM `post-user` STRAIGHT_JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid` STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-user`.`contact-id` @@ -1643,11 +1789,13 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-thread-user`.`received` AS `received`, `post-thread-user`.`changed` AS `changed`, `post-user`.`post-type` AS `post-type`, + `post-user`.`post-reason` AS `post-reason`, `post-user`.`private` AS `private`, `post-thread-user`.`pubmail` AS `pubmail`, `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`, @@ -1755,7 +1903,9 @@ CREATE VIEW `post-thread-user-view` AS SELECT `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`.`network` AS `parent-author-network` + `parent-post-author`.`network` AS `parent-author-network`, + `parent-post-author`.`blocked` AS `parent-author-blocked`, + `parent-post-author`.`hidden` AS `parent-author-hidden` FROM `post-thread-user` INNER JOIN `post-user` ON `post-user`.`id` = `post-thread-user`.`post-user-id` STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id` @@ -1819,6 +1969,31 @@ CREATE VIEW `post-view` AS SELECT `post-content`.`target-type` AS `target-type`, `post-content`.`target` AS `target`, `post-content`.`resource-id` AS `resource-id`, + `post`.`author-id` AS `contact-id`, + `author`.`url` AS `contact-link`, + `author`.`addr` AS `contact-addr`, + `author`.`name` AS `contact-name`, + `author`.`nick` AS `contact-nick`, + `author`.`thumb` AS `contact-avatar`, + `author`.`network` AS `contact-network`, + `author`.`blocked` AS `contact-blocked`, + `author`.`hidden` AS `contact-hidden`, + `author`.`readonly` AS `contact-readonly`, + `author`.`archive` AS `contact-archive`, + `author`.`pending` AS `contact-pending`, + `author`.`rel` AS `contact-rel`, + `author`.`uid` AS `contact-uid`, + `author`.`contact-type` AS `contact-contact-type`, + IF (`post`.`network` IN ('apub', 'dfrn', 'dspr', 'stat'), true, `author`.`writable`) AS `writable`, + false AS `self`, + `author`.`id` AS `cid`, + `author`.`alias` AS `alias`, + `author`.`photo` AS `photo`, + `author`.`name-date` AS `name-date`, + `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`, @@ -1854,7 +2029,9 @@ CREATE VIEW `post-view` AS SELECT `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`.`network` AS `parent-author-network` + `parent-post-author`.`network` AS `parent-author-network`, + `parent-post-author`.`blocked` AS `parent-author-blocked`, + `parent-post-author`.`hidden` AS `parent-author-hidden` FROM `post` STRAIGHT_JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post`.`author-id` @@ -1914,6 +2091,31 @@ CREATE VIEW `post-thread-view` AS SELECT `post-content`.`target-type` AS `target-type`, `post-content`.`target` AS `target`, `post-content`.`resource-id` AS `resource-id`, + `post-thread`.`author-id` AS `contact-id`, + `author`.`url` AS `contact-link`, + `author`.`addr` AS `contact-addr`, + `author`.`name` AS `contact-name`, + `author`.`nick` AS `contact-nick`, + `author`.`thumb` AS `contact-avatar`, + `author`.`network` AS `contact-network`, + `author`.`blocked` AS `contact-blocked`, + `author`.`hidden` AS `contact-hidden`, + `author`.`readonly` AS `contact-readonly`, + `author`.`archive` AS `contact-archive`, + `author`.`pending` AS `contact-pending`, + `author`.`rel` AS `contact-rel`, + `author`.`uid` AS `contact-uid`, + `author`.`contact-type` AS `contact-contact-type`, + IF (`post`.`network` IN ('apub', 'dfrn', 'dspr', 'stat'), true, `author`.`writable`) AS `writable`, + false AS `self`, + `author`.`id` AS `cid`, + `author`.`alias` AS `alias`, + `author`.`photo` AS `photo`, + `author`.`name-date` AS `name-date`, + `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`, @@ -1949,7 +2151,9 @@ CREATE VIEW `post-thread-view` AS SELECT `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`.`network` AS `parent-author-network` + `parent-post-author`.`network` AS `parent-author-network`, + `parent-post-author`.`blocked` AS `parent-author-blocked`, + `parent-post-author`.`hidden` AS `parent-author-hidden` FROM `post-thread` INNER JOIN `post` ON `post`.`uri-id` = `post-thread`.`uri-id` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-thread`.`author-id` @@ -2081,11 +2285,13 @@ CREATE VIEW `owner-view` AS SELECT `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`, @@ -2189,6 +2395,189 @@ 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`, + `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`.`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`.`request` AS `dfrn-request`, + `contact`.`notify` AS `dfrn-notify`, + `contact`.`poll` AS `dfrn-poll`, + `contact`.`confirm` AS `dfrn-confirm`, + `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` + FROM `contact` + LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id` + LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id` + 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`, + `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`.`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`, + `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`, + `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`, + `ucontact`.`duplex` AS `dfrn-duplex`, + `ucontact`.`ret-aes` AS `dfrn-ret-aes`, + `ucontact`.`site-pubkey` AS `dfrn-site-pubkey`, + `ucontact`.`issued-id` AS `dfrn-issued-id`, + `ucontact`.`dfrn-id` AS `dfrn-id`, + `ucontact`.`aes_allow` AS `dfrn-aes_allow`, + `contact`.`request` AS `dfrn-request`, + `contact`.`notify` AS `dfrn-notify`, + `contact`.`poll` AS `dfrn-poll`, + `contact`.`confirm` AS `dfrn-confirm`, + `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` + FROM `contact` AS `ucontact` + INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0 + LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id` + LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr'; + -- -- VIEW pending-view -- @@ -2226,6 +2615,7 @@ CREATE VIEW `tag-search-view` AS SELECT `post-user`.`gravity` AS `gravity`, `post-user`.`received` AS `received`, `post-user`.`network` AS `network`, + `post-user`.`author-id` AS `author-id`, `tag`.`name` AS `name` FROM `post-tag` INNER JOIN `tag` ON `tag`.`id` = `post-tag`.`tid`