X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=2f025fe10d9247a619125c7c55c9a8ed2c10a51f;hb=7a8d9689e50bede8d1095c034cc9838b2503aa04;hp=6c4758c24976a69e98731919a96fded49c4c696d;hpb=2a1cdfa36b05897221831af5206f6e71d4b4932e;p=friendica.git diff --git a/database.sql b/database.sql index 6c4758c249..2f025fe10d 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2020.06-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1343 +-- DB_UPDATE_VERSION 1347 -- ------------------------------------------ @@ -589,6 +589,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', `icid` int unsigned COMMENT 'Id of the item-content table entry that contains the whole item content', `iaid` int unsigned COMMENT 'Id of the item-activity table entry that contains the activity data', + `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', `extid` varchar(255) NOT NULL DEFAULT '' COMMENT '', `post-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Post type (personal note, bookmark, ...)', `global` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -715,24 +716,6 @@ CREATE TABLE IF NOT EXISTS `item-content` ( INDEX `uri-id` (`uri-id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; --- --- TABLE item-delivery-data --- -CREATE TABLE IF NOT EXISTS `item-delivery-data` ( - `iid` int unsigned NOT NULL COMMENT 'Item id', - `postopts` text COMMENT 'External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery', - `inform` mediumtext COMMENT 'Additional receivers of the linked item', - `queue_count` mediumint NOT NULL DEFAULT 0 COMMENT 'Initial number of delivery recipients, used as item.delivery_queue_count', - `queue_done` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries, used as item.delivery_queue_done', - `queue_failed` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of unsuccessful deliveries, used as item.delivery_queue_failed', - `activitypub` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via ActivityPub', - `dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via DFRN', - `legacy_dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via legacy DFRN', - `diaspora` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via Diaspora', - `ostatus` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via OStatus', - PRIMARY KEY(`iid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; - -- -- TABLE item-uri -- @@ -833,6 +816,8 @@ CREATE TABLE IF NOT EXISTS `notify` ( `link` varchar(255) NOT NULL DEFAULT '' COMMENT '', `iid` int unsigned NOT NULL DEFAULT 0 COMMENT 'item.id', `parent` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `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', `seen` boolean NOT NULL DEFAULT '0' COMMENT '', `verb` varchar(100) NOT NULL DEFAULT '' COMMENT '', `otype` varchar(10) NOT NULL DEFAULT '' COMMENT '', @@ -851,6 +836,7 @@ CREATE TABLE IF NOT EXISTS `notify-threads` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `notify-id` int unsigned NOT NULL DEFAULT 0 COMMENT '', `master-parent-item` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `master-parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post', `parent-item` int unsigned NOT NULL DEFAULT 0 COMMENT '', `receiver-uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', PRIMARY KEY(`id`) @@ -1188,6 +1174,36 @@ CREATE TABLE IF NOT EXISTS `tag` ( INDEX `url` (`url`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; +-- +-- TABLE post-category +-- +CREATE TABLE IF NOT EXISTS `post-category` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', + `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', + PRIMARY KEY(`uri-id`,`uid`,`type`,`tid`), + INDEX `uri-id` (`tid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to categories'; + +-- +-- TABLE post-delivery-data +-- +CREATE TABLE IF NOT EXISTS `post-delivery-data` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `postopts` text COMMENT 'External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery', + `inform` mediumtext COMMENT 'Additional receivers of the linked item', + `queue_count` mediumint NOT NULL DEFAULT 0 COMMENT 'Initial number of delivery recipients, used as item.delivery_queue_count', + `queue_done` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries, used as item.delivery_queue_done', + `queue_failed` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of unsuccessful deliveries, used as item.delivery_queue_failed', + `activitypub` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via ActivityPub', + `dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via DFRN', + `legacy_dfrn` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via legacy DFRN', + `diaspora` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via Diaspora', + `ostatus` mediumint NOT NULL DEFAULT 0 COMMENT 'Number of successful deliveries via OStatus', + PRIMARY KEY(`uri-id`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items'; + -- -- TABLE post-tag -- @@ -1347,6 +1363,15 @@ CREATE TABLE IF NOT EXISTS `user-item` ( INDEX `iid_uid` (`iid`,`uid`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User specific item data'; +-- +-- TABLE verb +-- +CREATE TABLE IF NOT EXISTS `verb` ( + `id` int unsigned NOT NULL auto_increment, + `name` varchar(100) NOT NULL DEFAULT '' COMMENT '', + PRIMARY KEY(`id`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Activity Verbs'; + -- -- TABLE worker-ipc -- @@ -1387,6 +1412,23 @@ CREATE TABLE IF NOT EXISTS `storage` ( PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend'; +-- +-- 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`, + `item-uri`.`uri` AS `uri`, + `item-uri`.`guid` AS `guid`, + `post-category`.`type` AS `type`, + `post-category`.`tid` AS `tid`, + `tag`.`name` AS `name`, + `tag`.`url` AS `url` + FROM `post-category` + INNER JOIN `item-uri` ON `item-uri`.id = `post-category`.`uri-id` + LEFT JOIN `tag` ON `post-category`.`tid` = `tag`.`id`; + -- -- VIEW tag-view --