From c7373dcec4ba4f3aa1fc42f2a036c578b8a28153 Mon Sep 17 00:00:00 2001 From: Michael Vogel Date: Thu, 23 Jun 2016 21:15:54 +0200 Subject: [PATCH] author-id/owner-id: Post update function/added database documentation --- boot.php | 2 +- database.sql | 4 +- doc/database/db_item.md | 2 + doc/database/db_thread.md | 2 + include/dbstructure.php | 2 + include/post_update.php | 77 +++++++++++++++++++++++++++++++++++++++ update.php | 2 +- 7 files changed, 88 insertions(+), 3 deletions(-) diff --git a/boot.php b/boot.php index 62baf116da..42767845c2 100644 --- a/boot.php +++ b/boot.php @@ -38,7 +38,7 @@ define ( 'FRIENDICA_PLATFORM', 'Friendica'); define ( 'FRIENDICA_CODENAME', 'Asparagus'); define ( 'FRIENDICA_VERSION', '3.5-dev' ); define ( 'DFRN_PROTOCOL_VERSION', '2.23' ); -define ( 'DB_UPDATE_VERSION', 1197 ); +define ( 'DB_UPDATE_VERSION', 1198 ); /** * @brief Constant with a HTML line break. diff --git a/database.sql b/database.sql index 4a36384d92..c337df586e 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 3.5-dev (Asparagus) --- DB_UPDATE_VERSION 1197 +-- DB_UPDATE_VERSION 1198 -- ------------------------------------------ @@ -523,6 +523,8 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `uid_parenturi` (`uid`,`parent-uri`), INDEX `uid_contactid_created` (`uid`,`contact-id`,`created`), INDEX `gcontactid_uid_created` (`gcontact-id`,`uid`,`created`), + INDEX `authorid_created` (`author-id`,`created`), + INDEX `ownerid_created` (`owner-id`,`created`), INDEX `wall_body` (`wall`,`body`(6)), INDEX `uid_visible_moderated_created` (`uid`,`visible`,`moderated`,`created`), INDEX `uid_uri` (`uid`,`uri`), diff --git a/doc/database/db_item.md b/doc/database/db_item.md index 00fa213c89..7981d29956 100644 --- a/doc/database/db_item.md +++ b/doc/database/db_item.md @@ -24,9 +24,11 @@ Table item | owner-name | Name of the owner of this item | varchar(255) | NO | | | | | owner-link | Link to the profile page of the owner of this item | varchar(255) | NO | | | | | owner-avatar | Link to the avatar picture of the owner of this item | varchar(255) | NO | | | | +| owner-id | Link to the contact table with uid=0 of the owner of this item | int(11) | NO | MUL | 0 | | | author-name | Name of the author of this item | varchar(255) | NO | | | | | author-link | Link to the profile page of the author of this item | varchar(255) | NO | | | | | author-avatar | Link to the avatar picture of the author of this item | varchar(255) | NO | | | | +| author-id | Link to the contact table with uid=0 of the author of this item | int(11) | NO | MUL | 0 | | | title | item title | varchar(255) | NO | | | | | body | item body content | mediumtext | NO | | NULL | | | app | application which generated this item | varchar(255) | NO | | | | diff --git a/doc/database/db_thread.md b/doc/database/db_thread.md index 4c912bec10..d6484b7609 100644 --- a/doc/database/db_thread.md +++ b/doc/database/db_thread.md @@ -7,6 +7,8 @@ Table thread | uid | | int(10) unsigned | NO | MUL | 0 | | | contact-id | | int(11) unsigned | NO | | 0 | | | gcontact-id | Global Contact | int(11) unsigned | NO | | 0 | | +| owner-id | Item owner | int(11) unsigned | NO | MUL | 0 | | +| author-id | Item author | int(11) unsigned | NO | MUL | 0 | | | created | | datetime | NO | MUL | 0000-00-00 00:00:00 | | | edited | | datetime | NO | | 0000-00-00 00:00:00 | | | commented | | datetime | NO | MUL | 0000-00-00 00:00:00 | | diff --git a/include/dbstructure.php b/include/dbstructure.php index 549d077ed3..b503b0ac04 100644 --- a/include/dbstructure.php +++ b/include/dbstructure.php @@ -859,6 +859,8 @@ function db_definition() { "uid_parenturi" => array("uid","parent-uri"), "uid_contactid_created" => array("uid","contact-id","created"), "gcontactid_uid_created" => array("gcontact-id","uid","created"), + "authorid_created" => array("author-id","created"), + "ownerid_created" => array("owner-id","created"), "wall_body" => array("wall","body(6)"), "uid_visible_moderated_created" => array("uid","visible","moderated","created"), "uid_uri" => array("uid","uri"), diff --git a/include/post_update.php b/include/post_update.php index 2bdfe1f6fd..6918bf0fa4 100644 --- a/include/post_update.php +++ b/include/post_update.php @@ -13,6 +13,9 @@ function post_update() { if (!post_update_1194()) return; + + if (!post_update_1198()) + return; } /** @@ -138,4 +141,78 @@ function post_update_1194() { logger("Done", LOGGER_DEBUG); } + +/** + * @brief set the author-id and owner-id in all item entries + * + * This job has to be started multiple times until all entries are set. + * It isn't started in the update function since it would consume too much time and can be done in the background. + * + * @return bool "true" when the job is done + */ +function post_update_1198() { + + // Was the script completed? + if (get_config("system", "post_update_version") >= 1198) + return true; + + logger("Start", LOGGER_DEBUG); + + // Check if the first step is done (Setting "author-id" and "owner-id" in the item table) + $r = q("SELECT `author-link`, `owner-link`, `uid` FROM `item` WHERE `author-id` = 0 AND `owner-id` = 0 LIMIT 1000"); + if (!$r) { + // Are there unfinished entries in the thread table? + $r = q("SELECT COUNT(*) AS `total` FROM `thread` + INNER JOIN `item` ON `item`.`id` =`thread`.`iid` + WHERE `thread`.`author-id` = 0 AND `thread`.`owner-id` = 0 AND + (`thread`.`uid` IN (SELECT `uid` from `user`) OR `thread`.`uid` = 0)"); + + if ($r AND ($r[0]["total"] == 0)) { + set_config("system", "post_update_version", 1198); + logger("Done", LOGGER_DEBUG); + return true; + } + + // Update the thread table from the item table + q("UPDATE `thread` INNER JOIN `item` ON `item`.`id`=`thread`.`iid` + SET `thread`.`author-id` = `item`.`author-id`, + `thread`.`owner-id` = `item`.`owner-id` + WHERE `thread`.`author-id` = 0 AND `thread`.`owner-id` = 0 AND + (`thread`.`uid` IN (SELECT `uid` from `user`) OR `thread`.`uid` = 0)"); + + logger("Updated threads", LOGGER_DEBUG); + return false; + } + + logger("Query done", LOGGER_DEBUG); + + $item_arr = array(); + foreach ($r AS $item) { + $index = $item["author-link"]."-".$item["owner-link"]."-".$item["uid"]; + $item_arr[$index] = array("author-link" => $item["author-link"], + "owner-link" => $item["owner-link"], + "uid" => $item["uid"]); + } + + // Set the "gcontact-id" in the item table and add a new gcontact entry if needed + foreach($item_arr AS $item) { + $author_id = get_contact($item["author-link"], 0); + $owner_id = get_contact($item["owner-link"], 0); + + if ($author_id == 0) + $author_id = -1; + + if ($owner_id == 0) + $owner_id = -1; + + q("UPDATE `item` SET `author-id` = %d, `owner-id` = %d + WHERE `uid` = %d AND `author-link` = '%s' AND `owner-link` = '%s' + AND `author-id` = 0 AND `owner-id` = 0", + intval($author_id), intval($owner_id), intval($item["uid"]), + dbesc($item["author-link"]), dbesc($item["owner-link"])); + } + + logger("Updated items", LOGGER_DEBUG); + return false; +} ?> diff --git a/update.php b/update.php index f2d790aa4c..c254d71c86 100644 --- a/update.php +++ b/update.php @@ -1,6 +1,6 @@