-- ------------------------------------------
-- Friendica 2022.09-dev (Giant Rhubarb)
--- DB_UPDATE_VERSION 1472
+-- DB_UPDATE_VERSION 1475
-- ------------------------------------------
`cookie_hash` varchar(80) NOT NULL COMMENT 'Trusted cookie hash',
`uid` mediumint unsigned NOT NULL COMMENT 'User ID',
`user_agent` text COMMENT 'User agent string',
+ `trusted` boolean NOT NULL DEFAULT '1' COMMENT 'Whenever this browser should be trusted or not',
`created` datetime NOT NULL COMMENT 'Datetime the trusted browser was recorded',
`last_used` datetime COMMENT 'Datetime the trusted browser was last used',
PRIMARY KEY(`cookie_hash`),
`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',
+ `suspended` boolean COMMENT 'Mastodon extension: true if profile is suspended',
`nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`name` varchar(255) COMMENT '',
`about` text COMMENT '',
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages';
---
--- TABLE conversation
---
-CREATE TABLE IF NOT EXISTS `conversation` (
- `item-uri` varbinary(255) NOT NULL COMMENT 'Original URI of the item - unrelated to the table with the same name',
- `reply-to-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'URI to which this item is a reply',
- `conversation-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation URI',
- `conversation-href` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation link',
- `protocol` tinyint unsigned NOT NULL DEFAULT 255 COMMENT 'The protocol of the item',
- `direction` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'How the message arrived here: 1=push, 2=pull',
- `source` mediumtext COMMENT 'Original source',
- `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Receiving date',
- PRIMARY KEY(`item-uri`),
- INDEX `conversation-uri` (`conversation-uri`),
- INDEX `received` (`received`)
-) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Raw data and structure information for messages';
-
--
-- TABLE workerqueue
--
UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`)
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry';
+--
+-- TABLE inbox-entry
+--
+CREATE TABLE IF NOT EXISTS `inbox-entry` (
+ `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
+ `activity-id` varbinary(255) COMMENT 'id of the incoming activity',
+ `object-id` varbinary(255) COMMENT '',
+ `in-reply-to-id` varbinary(255) COMMENT '',
+ `conversation` varbinary(255) COMMENT '',
+ `type` varchar(64) COMMENT 'Type of the activity',
+ `object-type` varchar(64) COMMENT 'Type of the object activity',
+ `object-object-type` varchar(64) COMMENT 'Type of the object\'s object activity',
+ `received` datetime COMMENT 'Receiving date',
+ `activity` mediumtext COMMENT 'The JSON activity',
+ `signer` varchar(255) COMMENT '',
+ `push` boolean COMMENT 'Is the entry pushed or have pulled it?',
+ `trust` boolean COMMENT 'Do we trust this entry?',
+ `wid` int unsigned COMMENT 'Workerqueue id',
+ PRIMARY KEY(`id`),
+ UNIQUE INDEX `activity-id` (`activity-id`),
+ INDEX `object-id` (`object-id`),
+ INDEX `received` (`received`),
+ INDEX `wid` (`wid`),
+ FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Incoming activity';
+
+--
+-- TABLE inbox-entry-receiver
+--
+CREATE TABLE IF NOT EXISTS `inbox-entry-receiver` (
+ `queue-id` int unsigned NOT NULL COMMENT '',
+ `uid` mediumint unsigned NOT NULL COMMENT 'User id',
+ PRIMARY KEY(`queue-id`,`uid`),
+ INDEX `uid` (`uid`),
+ FOREIGN KEY (`queue-id`) REFERENCES `inbox-entry` (`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='Receiver for the incoming activity';
+
--
-- TABLE inbox-status
--
FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts';
+--
+-- TABLE post-activity
+--
+CREATE TABLE IF NOT EXISTS `post-activity` (
+ `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `activity` mediumtext COMMENT 'Original activity',
+ `received` datetime COMMENT '',
+ PRIMARY KEY(`uri-id`),
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Original remote activity';
+
--
-- TABLE post-category
--
--
CREATE TABLE IF NOT EXISTS `post-thread` (
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `conversation-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the conversation uri',
`owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner',
`author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item author',
`causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation',
`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 '',
PRIMARY KEY(`uri-id`),
+ INDEX `conversation-id` (`conversation-id`),
INDEX `owner-id` (`owner-id`),
INDEX `author-id` (`author-id`),
INDEX `causer-id` (`causer-id`),
INDEX `received` (`received`),
INDEX `commented` (`commented`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`conversation-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
--
CREATE TABLE IF NOT EXISTS `post-thread-user` (
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `conversation-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the conversation uri',
`owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner',
`author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item author',
`causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation',
`post-user-id` int unsigned COMMENT 'Id of the post-user table',
PRIMARY KEY(`uid`,`uri-id`),
INDEX `uri-id` (`uri-id`),
+ INDEX `conversation-id` (`conversation-id`),
INDEX `owner-id` (`owner-id`),
INDEX `author-id` (`author-id`),
INDEX `causer-id` (`causer-id`),
INDEX `uid_starred` (`uid`,`starred`),
INDEX `uid_mention` (`uid`,`mention`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`conversation-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
--
-- VIEW application-view
--
+DROP VIEW IF EXISTS `application-view`;
CREATE VIEW `application-view` AS SELECT
`application`.`id` AS `id`,
`application-token`.`uid` AS `uid`,
--
-- VIEW post-user-view
--
+DROP VIEW IF EXISTS `post-user-view`;
CREATE VIEW `post-user-view` AS SELECT
`post-user`.`id` AS `id`,
`post-user`.`id` AS `post-user-id`,
`post-user`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`post-user`.`thr-parent-id` AS `thr-parent-id`,
+ `conversation-item-uri`.`uri` AS `conversation`,
+ `post-thread-user`.`conversation-id` AS `conversation-id`,
`item-uri`.`guid` AS `guid`,
`post-user`.`wall` AS `wall`,
`post-user`.`gravity` AS `gravity`,
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-user`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread-user`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id`
LEFT JOIN `verb` ON `verb`.`id` = `post-user`.`vid`
LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id`
--
-- VIEW post-thread-user-view
--
+DROP VIEW IF EXISTS `post-thread-user-view`;
CREATE VIEW `post-thread-user-view` AS SELECT
`post-user`.`id` AS `id`,
`post-user`.`id` AS `post-user-id`,
`post-user`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`post-user`.`thr-parent-id` AS `thr-parent-id`,
+ `conversation-item-uri`.`uri` AS `conversation`,
+ `post-thread-user`.`conversation-id` AS `conversation-id`,
`item-uri`.`guid` AS `guid`,
`post-thread-user`.`wall` AS `wall`,
`post-user`.`gravity` AS `gravity`,
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread-user`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread-user`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id`
LEFT JOIN `verb` ON `verb`.`id` = `post-user`.`vid`
LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id`
--
-- VIEW post-view
--
+DROP VIEW IF EXISTS `post-view`;
CREATE VIEW `post-view` AS SELECT
`item-uri`.`uri` AS `uri`,
`post`.`uri-id` AS `uri-id`,
`post`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`post`.`thr-parent-id` AS `thr-parent-id`,
+ `conversation-item-uri`.`uri` AS `conversation`,
+ `post-thread`.`conversation-id` AS `conversation-id`,
`item-uri`.`guid` AS `guid`,
`post`.`gravity` AS `gravity`,
`external-item-uri`.`uri` AS `extid`,
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post`.`external-id`
LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid`
LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post`.`uri-id`
--
-- VIEW post-thread-view
--
+DROP VIEW IF EXISTS `post-thread-view`;
CREATE VIEW `post-thread-view` AS SELECT
`item-uri`.`uri` AS `uri`,
`post-thread`.`uri-id` AS `uri-id`,
`post`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`post`.`thr-parent-id` AS `thr-parent-id`,
+ `conversation-item-uri`.`uri` AS `conversation`,
+ `post-thread`.`conversation-id` AS `conversation-id`,
`item-uri`.`guid` AS `guid`,
`post`.`gravity` AS `gravity`,
`external-item-uri`.`uri` AS `extid`,
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post`.`external-id`
LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid`
LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread`.`uri-id`
--
-- VIEW category-view
--
+DROP VIEW IF EXISTS `category-view`;
CREATE VIEW `category-view` AS SELECT
`post-category`.`uri-id` AS `uri-id`,
`post-category`.`uid` AS `uid`,
--
-- 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`,
--
-- VIEW tag-view
--
+DROP VIEW IF EXISTS `tag-view`;
CREATE VIEW `tag-view` AS SELECT
`post-tag`.`uri-id` AS `uri-id`,
`post-tag`.`type` AS `type`,
--
-- VIEW network-item-view
--
+DROP VIEW IF EXISTS `network-item-view`;
CREATE VIEW `network-item-view` AS SELECT
`post-user`.`uri-id` AS `uri-id`,
`parent-post`.`id` AS `parent`,
--
-- VIEW network-thread-view
--
+DROP VIEW IF EXISTS `network-thread-view`;
CREATE VIEW `network-thread-view` AS SELECT
`post-thread-user`.`uri-id` AS `uri-id`,
`parent-post`.`id` AS `parent`,
--
-- VIEW owner-view
--
+DROP VIEW IF EXISTS `owner-view`;
CREATE VIEW `owner-view` AS SELECT
`contact`.`id` AS `id`,
`contact`.`uid` AS `uid`,
--
-- VIEW account-view
--
+DROP VIEW IF EXISTS `account-view`;
CREATE VIEW `account-view` AS SELECT
`contact`.`id` AS `id`,
`contact`.`url` AS `url`,
--
-- 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`,
--
-- VIEW pending-view
--
+DROP VIEW IF EXISTS `pending-view`;
CREATE VIEW `pending-view` AS SELECT
`register`.`id` AS `id`,
`register`.`hash` AS `hash`,
--
-- VIEW tag-search-view
--
+DROP VIEW IF EXISTS `tag-search-view`;
CREATE VIEW `tag-search-view` AS SELECT
`post-tag`.`uri-id` AS `uri-id`,
`post-user`.`uid` AS `uid`,
--
-- VIEW workerqueue-view
--
+DROP VIEW IF EXISTS `workerqueue-view`;
CREATE VIEW `workerqueue-view` AS SELECT
`process`.`pid` AS `pid`,
`workerqueue`.`priority` AS `priority`
--
-- 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`,
projects / friendica.git / blobdiff