X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=320ef38e77177b2f0355a56f8c85ed7f4b1cdf2c;hb=16d486dd2422947713783470d3e0fb165fe528f3;hp=9f4cee05287c3b1847a414bf3b6fb09bf6b7c986;hpb=304ba0e7c448ac779e0f0a8a2d355ca8a65e827d;p=friendica.git diff --git a/database.sql b/database.sql index 9f4cee0528..320ef38e77 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2020.06-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1340 +-- DB_UPDATE_VERSION 1343 -- ------------------------------------------ @@ -666,7 +666,8 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `uid_eventid` (`uid`,`event-id`), INDEX `icid` (`icid`), INDEX `iaid` (`iaid`), - INDEX `psid_wall` (`psid`,`wall`) + INDEX `psid_wall` (`psid`,`wall`), + INDEX `uri-id` (`uri-id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts'; -- @@ -714,24 +715,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 -- @@ -1187,6 +1170,24 @@ CREATE TABLE IF NOT EXISTS `tag` ( INDEX `url` (`url`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; +-- +-- 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 -- @@ -1404,4 +1405,209 @@ 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 owner-view +-- +DROP VIEW IF EXISTS `owner-view`; +CREATE VIEW `owner-view` AS SELECT + `contact`.`id` AS `id`, + `contact`.`uid` AS `uid`, + `contact`.`created` AS `created`, + `contact`.`updated` AS `updated`, + `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`, + `contact`.`nick` AS `nick`, + `contact`.`location` AS `location`, + `contact`.`about` AS `about`, + `contact`.`keywords` AS `keywords`, + `contact`.`gender` AS `gender`, + `contact`.`xmpp` AS `xmpp`, + `contact`.`attag` AS `attag`, + `contact`.`avatar` AS `avatar`, + `contact`.`photo` AS `photo`, + `contact`.`thumb` AS `thumb`, + `contact`.`micro` AS `micro`, + `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`.`addr` AS `addr`, + `contact`.`alias` AS `alias`, + `contact`.`pubkey` AS `pubkey`, + `contact`.`prvkey` AS `prvkey`, + `contact`.`batch` AS `batch`, + `contact`.`request` AS `request`, + `contact`.`notify` AS `notify`, + `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`, + `contact`.`success_update` AS `success_update`, + `contact`.`failure_update` AS `failure_update`, + `contact`.`name-date` AS `name-date`, + `contact`.`uri-date` AS `uri-date`, + `contact`.`avatar-date` AS `avatar-date`, + `contact`.`avatar-date` AS `picdate`, + `contact`.`term-date` AS `term-date`, + `contact`.`last-item` AS `last-item`, + `contact`.`priority` AS `priority`, + `contact`.`blocked` AS `blocked`, + `contact`.`block_reason` AS `block_reason`, + `contact`.`readonly` AS `readonly`, + `contact`.`writable` AS `writable`, + `contact`.`forum` AS `forum`, + `contact`.`prv` AS `prv`, + `contact`.`contact-type` AS `contact-type`, + `contact`.`hidden` AS `hidden`, + `contact`.`archive` AS `archive`, + `contact`.`pending` AS `pending`, + `contact`.`deleted` AS `deleted`, + `contact`.`rating` AS `rating`, + `contact`.`unsearchable` AS `unsearchable`, + `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`, + `contact`.`fetch_further_information` AS `fetch_further_information`, + `contact`.`ffi_keyword_blacklist` AS `ffi_keyword_blacklist`, + `user`.`parent-uid` AS `parent-uid`, + `user`.`guid` AS `guid`, + `user`.`nickname` AS `nickname`, + `user`.`email` AS `email`, + `user`.`openid` AS `openid`, + `user`.`timezone` AS `timezone`, + `user`.`language` AS `language`, + `user`.`register_date` AS `register_date`, + `user`.`login_date` AS `login_date`, + `user`.`default-location` AS `default-location`, + `user`.`allow_location` AS `allow_location`, + `user`.`theme` AS `theme`, + `user`.`pubkey` AS `upubkey`, + `user`.`prvkey` AS `uprvkey`, + `user`.`sprvkey` AS `sprvkey`, + `user`.`spubkey` AS `spubkey`, + `user`.`verified` AS `verified`, + `user`.`blockwall` AS `blockwall`, + `user`.`hidewall` AS `hidewall`, + `user`.`blocktags` AS `blocktags`, + `user`.`unkmail` AS `unkmail`, + `user`.`cntunkmail` AS `cntunkmail`, + `user`.`notify-flags` AS `notify-flags`, + `user`.`page-flags` AS `page-flags`, + `user`.`account-type` AS `account-type`, + `user`.`prvnets` AS `prvnets`, + `user`.`maxreq` AS `maxreq`, + `user`.`expire` AS `expire`, + `user`.`account_removed` AS `account_removed`, + `user`.`account_expired` AS `account_expired`, + `user`.`account_expires_on` AS `account_expires_on`, + `user`.`expire_notification_sent` AS `expire_notification_sent`, + `user`.`def_gid` AS `def_gid`, + `user`.`allow_cid` AS `allow_cid`, + `user`.`allow_gid` AS `allow_gid`, + `user`.`deny_cid` AS `deny_cid`, + `user`.`deny_gid` AS `deny_gid`, + `user`.`openidserver` AS `openidserver`, + `profile`.`publish` AS `publish`, + `profile`.`net-publish` AS `net-publish`, + `profile`.`hide-friends` AS `hide-friends`, + `profile`.`prv_keywords` AS `prv_keywords`, + `profile`.`pub_keywords` AS `pub_keywords`, + `profile`.`address` AS `address`, + `profile`.`locality` AS `locality`, + `profile`.`region` AS `region`, + `profile`.`postal-code` AS `postal-code`, + `profile`.`country-name` AS `country-name`, + `profile`.`homepage` AS `homepage`, + `profile`.`dob` AS `dob` + FROM `user` + INNER JOIN `contact` ON `contact`.`uid` = `user`.`uid` AND `contact`.`self` + INNER JOIN `profile` ON `profile`.`uid` = `user`.`uid`; + +-- +-- VIEW participation-view +-- +DROP VIEW IF EXISTS `participation-view`; +CREATE VIEW `participation-view` AS SELECT + `participation`.`iid` AS `iid`, + `contact`.`id` AS `id`, + `contact`.`url` AS `url`, + `contact`.`name` AS `name`, + `contact`.`protocol` AS `protocol`, + CASE `contact`.`batch` WHEN '' THEN `fcontact`.`batch` ELSE `contact`.`batch` END AS `batch`, + CASE `fcontact`.`network` WHEN '' THEN `contact`.`network` ELSE `fcontact`.`network` END AS `network` + FROM `participation` + INNER JOIN `contact` ON `contact`.`id` = `participation`.`cid` AND NOT `contact`.`archive` + INNER JOIN `fcontact` ON `fcontact`.`id` = `participation`.`fid`; + +-- +-- VIEW pending-view +-- +DROP VIEW IF EXISTS `pending-view`; +CREATE VIEW `pending-view` AS SELECT + `register`.`id` AS `id`, + `register`.`hash` AS `hash`, + `register`.`created` AS `created`, + `register`.`uid` AS `uid`, + `register`.`password` AS `password`, + `register`.`language` AS `language`, + `register`.`note` AS `note`, + `contact`.`self` AS `self`, + `contact`.`name` AS `name`, + `contact`.`url` AS `url`, + `contact`.`micro` AS `micro`, + `user`.`email` AS `email`, + `contact`.`nick` AS `nick` + FROM `register` + INNER JOIN `contact` ON `register`.`uid` = `contact`.`uid` + INNER JOIN `user` ON `register`.`uid` = `user`.`uid`; + +-- +-- 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`, + `item`.`id` AS `iid`, + `item`.`uri` AS `uri`, + `item`.`guid` AS `guid`, + `item`.`uid` AS `uid`, + `item`.`private` AS `private`, + `item`.`wall` AS `wall`, + `item`.`origin` AS `origin`, + `item`.`gravity` AS `gravity`, + `item`.`received` AS `received`, + `tag`.`name` AS `name` + FROM `post-tag` + INNER JOIN `tag` ON `tag`.`id` = `post-tag`.`tid` + INNER JOIN `item` ON `item`.`uri-id` = `post-tag`.`uri-id` + WHERE `post-tag`.`type` = 1; + +-- +-- VIEW workerqueue-view +-- +DROP VIEW IF EXISTS `workerqueue-view`; +CREATE VIEW `workerqueue-view` AS SELECT + `process`.`pid` AS `pid`, + `workerqueue`.`priority` AS `priority` + FROM `process` + INNER JOIN `workerqueue` ON `workerqueue`.`pid` = `process`.`pid` + WHERE NOT `workerqueue`.`done`; +