X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=5387d14c6ca9c260fe8c90d96e4549fd8906d7e3;hb=c4446e200f5729fdd2d920a962bb0cdcb2d2eb6c;hp=ea89847c19aaefd2af5504c3cdef1b6603709108;hpb=18617f6c48f35a0a5df39f95a6460ea66255616d;p=friendica.git diff --git a/database.sql b/database.sql index ea89847c19..5387d14c6c 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2020.09-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1358 +-- DB_UPDATE_VERSION 1360 -- ------------------------------------------ @@ -144,6 +144,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `dfrn-id` (`dfrn-id`(64)), INDEX `issued-id` (`issued-id`(64)), INDEX `network_uid_lastupdate` (`network`,`uid`,`last-update`), + INDEX `uid_lastitem` (`uid`,`last-item`), INDEX `gsid` (`gsid`), FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table'; @@ -464,79 +465,6 @@ CREATE TABLE IF NOT EXISTS `fsuggest` ( PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='friend suggestion stuff'; --- --- TABLE gcign --- -CREATE TABLE IF NOT EXISTS `gcign` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Local User id', - `gcid` int unsigned NOT NULL DEFAULT 0 COMMENT 'gcontact.id of ignored contact', - PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - INDEX `gcid` (`gcid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contacts ignored by friend suggestions'; - --- --- TABLE gcontact --- -CREATE TABLE IF NOT EXISTS `gcontact` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `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', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the contacts profile page', - `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the profile photo', - `connect` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `updated` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `last_discovery` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last contact discovery', - `failed` boolean COMMENT 'Connection failed', - `archive_date` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', - `archived` boolean NOT NULL DEFAULT '0' COMMENT '', - `location` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `about` text COMMENT '', - `keywords` text COMMENT 'puplic keywords (interests)', - `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', - `birthday` varchar(32) NOT NULL DEFAULT '0001-01-01' COMMENT '', - `community` boolean NOT NULL DEFAULT '0' COMMENT '1 if contact is forum account', - `contact-type` tinyint NOT NULL DEFAULT -1 COMMENT '', - `hide` boolean NOT NULL DEFAULT '0' COMMENT '1 = should be hidden from search', - `nsfw` boolean NOT NULL DEFAULT '0' COMMENT '1 = contact posts nsfw content', - `network` char(4) NOT NULL DEFAULT '' COMMENT 'social network protocol', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `notify` varchar(255) COMMENT '', - `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `generation` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `server_url` varchar(255) NOT NULL DEFAULT '' COMMENT 'baseurl of the contacts server', - `gsid` int unsigned COMMENT 'Global Server ID', - PRIMARY KEY(`id`), - UNIQUE INDEX `nurl` (`nurl`(190)), - INDEX `name` (`name`(64)), - INDEX `nick` (`nick`(32)), - INDEX `addr` (`addr`(64)), - INDEX `hide_network_updated` (`hide`,`network`,`updated`), - INDEX `updated` (`updated`), - INDEX `gsid` (`gsid`), - FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='global contacts'; - --- --- TABLE glink --- -CREATE TABLE IF NOT EXISTS `glink` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `cid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `gcid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `zcid` int unsigned NOT NULL DEFAULT 0 COMMENT '', - `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - PRIMARY KEY(`id`), - UNIQUE INDEX `cid_uid_gcid_zcid` (`cid`,`uid`,`gcid`,`zcid`), - INDEX `gcid` (`gcid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='\'friends of friends\' linkages derived from poco'; - -- -- TABLE group -- @@ -1460,6 +1388,63 @@ CREATE VIEW `tag-view` AS SELECT LEFT JOIN `tag` ON `post-tag`.`tid` = `tag`.`id` LEFT JOIN `contact` ON `post-tag`.`cid` = `contact`.`id`; +-- +-- VIEW network-item-view +-- +DROP VIEW IF EXISTS `network-item-view`; +CREATE VIEW `network-item-view` AS SELECT + `item`.`parent-uri-id` AS `uri-id`, + `item`.`parent-uri` AS `uri`, + `item`.`parent` AS `parent`, + `item`.`received` AS `received`, + `item`.`commented` AS `commented`, + `item`.`created` AS `created`, + `item`.`uid` AS `uid`, + `item`.`starred` AS `starred`, + `item`.`mention` AS `mention`, + `item`.`network` AS `network`, + `item`.`unseen` AS `unseen`, + `item`.`gravity` AS `gravity`, + `item`.`contact-id` AS `contact-id` + FROM `item` + INNER JOIN `thread` ON `thread`.`iid` = `item`.`parent` + STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id` AND (NOT `contact`.`blocked` OR `contact`.`pending`) + LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid` + LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id` + LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id` + WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated` + AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`) + AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`) + AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`); + +-- +-- VIEW network-thread-view +-- +DROP VIEW IF EXISTS `network-thread-view`; +CREATE VIEW `network-thread-view` AS SELECT + `item`.`uri-id` AS `uri-id`, + `item`.`uri` AS `uri`, + `item`.`parent-uri-id` AS `parent-uri-id`, + `thread`.`iid` AS `parent`, + `thread`.`received` AS `received`, + `thread`.`commented` AS `commented`, + `thread`.`created` AS `created`, + `thread`.`uid` AS `uid`, + `thread`.`starred` AS `starred`, + `thread`.`mention` AS `mention`, + `thread`.`network` AS `network`, + `thread`.`contact-id` AS `contact-id` + FROM `thread` + STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id` AND (NOT `contact`.`blocked` OR `contact`.`pending`) + STRAIGHT_JOIN `item` ON `item`.`id` = `thread`.`iid` + LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid` + LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id` + LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id` + WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated` + AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`) + AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`) + AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`); + -- -- VIEW owner-view --