X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=a68f3d4e5a2398b865598350f5120cf8bfefcc84;hb=2301cc22057ab26352f130aab4867048bcc579f3;hp=9f4cee05287c3b1847a414bf3b6fb09bf6b7c986;hpb=304ba0e7c448ac779e0f0a8a2d355ca8a65e827d;p=friendica.git diff --git a/database.sql b/database.sql index 9f4cee0528..a68f3d4e5a 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 1341 -- ------------------------------------------ @@ -1404,4 +1404,188 @@ 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 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`; +