X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=a68f3d4e5a2398b865598350f5120cf8bfefcc84;hb=2301cc22057ab26352f130aab4867048bcc579f3;hp=cbb724d18900a4f6778d97990c446a2cd29c22c7;hpb=b096172cdc0a9665b9374c978488abc22523a494;p=friendica.git diff --git a/database.sql b/database.sql index cbb724d189..a68f3d4e5a 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2019.12-rc (Dalmatian Bellflower) --- DB_UPDATE_VERSION 1326 +-- Friendica 2020.06-dev (Red Hot Poker) +-- DB_UPDATE_VERSION 1341 -- ------------------------------------------ @@ -179,7 +179,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `location` varchar(255) DEFAULT '' COMMENT '', `about` text COMMENT '', `keywords` text COMMENT 'public keywords (interests) of the contact', - `gender` varchar(32) NOT NULL DEFAULT '' COMMENT '', + `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -233,7 +233,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `reason` text COMMENT '', `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '', `info` mediumtext COMMENT '', - `profile-id` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `profile-id` int unsigned COMMENT 'Deprecated', `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '', `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', `notify_new_posts` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -254,6 +254,17 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `issued-id` (`issued-id`(64)) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table'; +-- +-- TABLE contact-relation +-- +CREATE TABLE IF NOT EXISTS `contact-relation` ( + `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact the related contact had interacted with', + `relation-cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'related contact who had interacted with the contact', + `last-interaction` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last interaction', + PRIMARY KEY(`cid`,`relation-cid`), + INDEX `relation-cid` (`relation-cid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Contact relations'; + -- -- TABLE conv -- @@ -279,6 +290,7 @@ CREATE TABLE IF NOT EXISTS `conversation` ( `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`), @@ -392,12 +404,13 @@ CREATE TABLE IF NOT EXISTS `gcontact` ( `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', `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 '', + `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 '', @@ -418,6 +431,17 @@ CREATE TABLE IF NOT EXISTS `gcontact` ( INDEX `updated` (`updated`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='global contacts'; +-- +-- TABLE gfollower +-- +CREATE TABLE IF NOT EXISTS `gfollower` ( + `gcid` int unsigned NOT NULL DEFAULT 0 COMMENT 'global contact', + `follower-gcid` int unsigned NOT NULL DEFAULT 0 COMMENT 'global contact of the follower', + `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates that the connection has been deleted', + PRIMARY KEY(`gcid`,`follower-gcid`), + INDEX `follower-gcid` (`follower-gcid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Followers of global contacts'; + -- -- TABLE glink -- @@ -470,6 +494,7 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `info` text COMMENT '', `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '', `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users', + `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)', `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '', `network` char(4) NOT NULL DEFAULT '' COMMENT '', @@ -567,7 +592,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `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 '', - `private` boolean NOT NULL DEFAULT '0' COMMENT 'distribution is restricted', + `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', `visible` boolean NOT NULL DEFAULT '0' COMMENT '', `moderated` boolean NOT NULL DEFAULT '0' COMMENT '', `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'item has been deleted', @@ -934,6 +959,7 @@ CREATE TABLE IF NOT EXISTS `photo` ( `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups', `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id', `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups', + `accessible` boolean NOT NULL DEFAULT '0' COMMENT 'Make photo publicly accessible, ignoring permissions', `backend-class` tinytext COMMENT 'Storage backend class', `backend-ref` text COMMENT 'Storage backend data reference', `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', @@ -994,40 +1020,40 @@ CREATE TABLE IF NOT EXISTS `process` ( CREATE TABLE IF NOT EXISTS `profile` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', - `profile-name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name of the profile', - `is-default` boolean NOT NULL DEFAULT '0' COMMENT 'Mark this profile as default profile', + `profile-name` varchar(255) COMMENT 'Deprecated', + `is-default` boolean COMMENT 'Deprecated', `hide-friends` boolean NOT NULL DEFAULT '0' COMMENT 'Hide friend list from viewers of this profile', `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `pdesc` varchar(255) NOT NULL DEFAULT '' COMMENT 'Title or description', + `pdesc` varchar(255) COMMENT 'Deprecated', `dob` varchar(32) NOT NULL DEFAULT '0000-00-00' COMMENT 'Day of birth', `address` varchar(255) NOT NULL DEFAULT '' COMMENT '', `locality` varchar(255) NOT NULL DEFAULT '' COMMENT '', `region` varchar(255) NOT NULL DEFAULT '' COMMENT '', `postal-code` varchar(32) NOT NULL DEFAULT '' COMMENT '', `country-name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `hometown` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `gender` varchar(32) NOT NULL DEFAULT '' COMMENT '', - `marital` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `with` text COMMENT '', - `howlong` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `sexual` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `politic` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `religion` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `hometown` varchar(255) COMMENT 'Deprecated', + `gender` varchar(32) COMMENT 'Deprecated', + `marital` varchar(255) COMMENT 'Deprecated', + `with` text COMMENT 'Deprecated', + `howlong` datetime COMMENT 'Deprecated', + `sexual` varchar(255) COMMENT 'Deprecated', + `politic` varchar(255) COMMENT 'Deprecated', + `religion` varchar(255) COMMENT 'Deprecated', `pub_keywords` text COMMENT '', `prv_keywords` text COMMENT '', - `likes` text COMMENT '', - `dislikes` text COMMENT '', - `about` text COMMENT '', - `summary` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `music` text COMMENT '', - `book` text COMMENT '', - `tv` text COMMENT '', - `film` text COMMENT '', - `interest` text COMMENT '', - `romance` text COMMENT '', - `work` text COMMENT '', - `education` text COMMENT '', - `contact` text COMMENT '', + `likes` text COMMENT 'Deprecated', + `dislikes` text COMMENT 'Deprecated', + `about` text COMMENT 'Profile description', + `summary` varchar(255) COMMENT 'Deprecated', + `music` text COMMENT 'Deprecated', + `book` text COMMENT 'Deprecated', + `tv` text COMMENT 'Deprecated', + `film` text COMMENT 'Deprecated', + `interest` text COMMENT 'Deprecated', + `romance` text COMMENT 'Deprecated', + `work` text COMMENT 'Deprecated', + `education` text COMMENT 'Deprecated', + `contact` text COMMENT 'Deprecated', `homepage` varchar(255) NOT NULL DEFAULT '' COMMENT '', `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '', `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -1052,6 +1078,24 @@ CREATE TABLE IF NOT EXISTS `profile_check` ( PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='DFRN remote auth use'; +-- +-- TABLE profile_field +-- +CREATE TABLE IF NOT EXISTS `profile_field` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner user id', + `order` mediumint unsigned NOT NULL DEFAULT 1 COMMENT 'Field ordering per user', + `psid` int unsigned COMMENT 'ID of the permission set of this profile field - 0 = public', + `label` varchar(255) NOT NULL DEFAULT '' COMMENT 'Label of the field', + `value` text COMMENT 'Value of the field', + `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', + PRIMARY KEY(`id`), + INDEX `uid` (`uid`), + INDEX `order` (`order`), + INDEX `psid` (`psid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Custom profile fields'; + -- -- TABLE push_subscriber -- @@ -1108,19 +1152,6 @@ CREATE TABLE IF NOT EXISTS `session` ( INDEX `expire` (`expire`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='web session storage'; --- --- TABLE sign --- -CREATE TABLE IF NOT EXISTS `sign` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `iid` int unsigned NOT NULL DEFAULT 0 COMMENT 'item.id', - `signed_text` mediumtext COMMENT '', - `signature` text COMMENT '', - `signer` varchar(255) NOT NULL DEFAULT '' COMMENT '', - PRIMARY KEY(`id`), - UNIQUE INDEX `iid` (`iid`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora signatures'; - -- -- TABLE term -- @@ -1144,6 +1175,31 @@ CREATE TABLE IF NOT EXISTS `term` ( INDEX `guid` (`guid`(64)) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='item taxonomy (categories, tags, etc.) table'; +-- +-- TABLE tag +-- +CREATE TABLE IF NOT EXISTS `tag` ( + `id` int unsigned NOT NULL auto_increment COMMENT '', + `name` varchar(96) NOT NULL DEFAULT '' COMMENT '', + `url` varbinary(255) NOT NULL DEFAULT '' COMMENT '', + PRIMARY KEY(`id`), + UNIQUE INDEX `type_name_url` (`name`,`url`), + INDEX `url` (`url`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions'; + +-- +-- TABLE post-tag +-- +CREATE TABLE IF NOT EXISTS `post-tag` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', + `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `tid` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Contact id of the mentioned public contact', + PRIMARY KEY(`uri-id`,`type`,`tid`,`cid`), + INDEX `uri-id` (`tid`), + INDEX `cid` (`tid`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='post relation to tags'; + -- -- TABLE thread -- @@ -1159,7 +1215,7 @@ CREATE TABLE IF NOT EXISTS `thread` ( `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `wall` boolean NOT NULL DEFAULT '0' COMMENT '', - `private` boolean NOT NULL DEFAULT '0' COMMENT '', + `private` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=public, 1=private, 2=unlisted', `pubmail` boolean NOT NULL DEFAULT '0' COMMENT '', `moderated` boolean NOT NULL DEFAULT '0' COMMENT '', `visible` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -1284,8 +1340,10 @@ CREATE TABLE IF NOT EXISTS `user-item` ( `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'Marker to hide an item from the user', `ignored` boolean COMMENT 'Ignore this thread if set', `pinned` boolean COMMENT 'The item is pinned on the profile page', + `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', PRIMARY KEY(`uid`,`iid`), - INDEX `uid_pinned` (`uid`,`pinned`) + INDEX `uid_pinned` (`uid`,`pinned`), + INDEX `iid_uid` (`iid`,`uid`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User specific item data'; -- @@ -1328,4 +1386,206 @@ CREATE TABLE IF NOT EXISTS `storage` ( PRIMARY KEY(`id`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend'; +-- +-- VIEW tag-view +-- +DROP VIEW IF EXISTS `tag-view`; +CREATE VIEW `tag-view` AS SELECT + `post-tag`.`uri-id` AS `uri-id`, + `item-uri`.`uri` AS `uri`, + `item-uri`.`guid` AS `guid`, + `post-tag`.`type` AS `type`, + `post-tag`.`tid` AS `tid`, + `post-tag`.`cid` AS `cid`, + CASE `cid` WHEN 0 THEN `tag`.`name` ELSE `contact`.`name` END AS `name`, + CASE `cid` WHEN 0 THEN `tag`.`url` ELSE `contact`.`url` END AS `url` + FROM `post-tag` + INNER JOIN `item-uri` ON `item-uri`.id = `post-tag`.`uri-id` + 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 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`; +