From 89fb28ae9b735a1de0a526548cc3f7c57ce0e941 Mon Sep 17 00:00:00 2001 From: Michael Date: Mon, 25 Jun 2018 04:56:32 +0000 Subject: [PATCH] Content is now stored exclusively in item-content, connected via "icid" field --- database.sql | 34 +++++++++++++++++++++++----- mod/item.php | 4 ++-- src/Database/DBStructure.php | 3 +-- src/Model/Item.php | 43 +++++++++++++++++++++++------------- 4 files changed, 60 insertions(+), 24 deletions(-) diff --git a/database.sql b/database.sql index 9dc0bcb187..cca1163145 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2018.08-dev (The Tazmans Flax-lily) --- DB_UPDATE_VERSION 1270 +-- DB_UPDATE_VERSION 1271 -- ------------------------------------------ @@ -477,6 +477,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `author-name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name of the author of this item', `author-link` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the profile page of the author of this item', `author-avatar` varchar(255) NOT NULL DEFAULT '' COMMENT 'Link to the avatar picture of the author of this item', + `icid` int unsigned COMMENT 'Id of the item-content table entry that contains the whole item content', `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'item title', `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '', `body` mediumtext COMMENT 'item body content', @@ -540,10 +541,33 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `contactid_verb` (`contact-id`,`verb`), INDEX `deleted_changed` (`deleted`,`changed`), INDEX `uid_wall_changed` (`uid`,`wall`,`changed`), - INDEX `uid_eventid` (`uid`,`event-id`), - INDEX `uid_authorlink` (`uid`,`author-link`(190)), - INDEX `uid_ownerlink` (`uid`,`owner-link`(190)) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='All posts'; + INDEX `uid_eventid` (`uid`,`event-id`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts'; + +-- +-- TABLE item-content +-- +CREATE TABLE IF NOT EXISTS `item-content` ( + `id` int unsigned NOT NULL auto_increment, + `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri-plink-hash` char(80) NOT NULL DEFAULT '' COMMENT 'SHA-1 hash from uri and plink', + `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'item title', + `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `body` mediumtext COMMENT 'item body content', + `location` varchar(255) NOT NULL DEFAULT '' COMMENT 'text location where this item originated', + `coord` varchar(255) NOT NULL DEFAULT '' COMMENT 'longitude/latitude pair representing location where this item originated', + `app` varchar(255) NOT NULL DEFAULT '' COMMENT 'application which generated this item', + `rendered-hash` varchar(32) NOT NULL DEFAULT '' COMMENT '', + `rendered-html` mediumtext COMMENT 'item.body converted to html', + `object-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams object type', + `object` text COMMENT 'JSON encoded object structure unless it is an implied object (normal post)', + `target-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams target type if applicable (URI)', + `target` text COMMENT 'JSON encoded target structure if used', + `plink` varchar(255) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri-plink-hash` (`uri-plink-hash`), + INDEX `uri` (`uri`(191)) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; -- -- TABLE locks diff --git a/mod/item.php b/mod/item.php index 4c78af6ca1..2830cae2c9 100644 --- a/mod/item.php +++ b/mod/item.php @@ -93,9 +93,9 @@ function item_post(App $a) { if ($thr_parent || $thr_parent_uri) { if ($thr_parent) { - $parent_item = dba::selectFirst('item', [], ['id' => $thr_parent]); + $parent_item = Item::selectFirst([], ['id' => $thr_parent]); } elseif ($thr_parent_uri) { - $parent_item = dba::selectFirst('item', [], ['uri' => $thr_parent_uri, 'uid' => $profile_uid]); + $parent_item = Item::selectFirst([], ['uri' => $thr_parent_uri, 'uid' => $profile_uid]); } // if this isn't the real parent of the conversation, find it diff --git a/src/Database/DBStructure.php b/src/Database/DBStructure.php index 19930b5e4c..e6bbf22094 100644 --- a/src/Database/DBStructure.php +++ b/src/Database/DBStructure.php @@ -1181,6 +1181,7 @@ class DBStructure "author-name" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Name of the author of this item"], "author-link" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Link to the profile page of the author of this item"], "author-avatar" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Link to the avatar picture of the author of this item"], + "icid" => ["type" => "int unsigned", "relation" => ["item-content" => "id"], "comment" => "Id of the item-content table entry that contains the whole item content"], "title" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "item title"], "content-warning" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""], "body" => ["type" => "mediumtext", "comment" => "item body content"], @@ -1247,8 +1248,6 @@ class DBStructure "deleted_changed" => ["deleted","changed"], "uid_wall_changed" => ["uid","wall","changed"], "uid_eventid" => ["uid","event-id"], - "uid_authorlink" => ["uid","author-link(190)"], - "uid_ownerlink" => ["uid","owner-link(190)"], ] ]; $database["item-content"] = [ diff --git a/src/Model/Item.php b/src/Model/Item.php index 3936a3af47..40b42ea278 100644 --- a/src/Model/Item.php +++ b/src/Model/Item.php @@ -72,10 +72,10 @@ class Item extends BaseObject // Fetch data from the item-content table whenever there is content there foreach (self::CONTENT_FIELDLIST as $field) { - if (!empty($row['item-content-' . $field])) { - $row[$field] = $row['item-content-' . $field]; - unset($row['item-content-' . $field]); + if (is_null($row[$field]) && !is_null($row['item-' . $field])) { + $row[$field] = $row['item-' . $field]; } + unset($row['item-' . $field]); } // We prefer the data from the user's contact over the public one @@ -363,15 +363,15 @@ class Item extends BaseObject $fields['item'] = ['id', 'uid', 'parent', 'uri', 'parent-uri', 'thr-parent', 'guid', 'contact-id', 'owner-id', 'author-id', 'type', 'wall', 'gravity', 'extid', - 'created', 'edited', 'commented', 'received', 'changed', - 'title', 'body', 'app', 'verb', 'object-type', 'object', 'target-type', 'target', + 'created', 'edited', 'commented', 'received', 'changed', 'verb', 'postopts', 'plink', 'resource-id', 'event-id', 'tag', 'attach', 'inform', - 'file', 'location', 'coord', 'allow_cid', 'allow_gid', 'deny_cid', 'deny_gid', + 'file', 'allow_cid', 'allow_gid', 'deny_cid', 'deny_gid', 'private', 'pubmail', 'moderated', 'visible', 'starred', 'bookmark', - 'unseen', 'deleted', 'origin', 'forum_mode', 'mention', - 'rendered-hash', 'rendered-html', 'global', 'content-warning', + 'unseen', 'deleted', 'origin', 'forum_mode', 'mention', 'global', 'id' => 'item_id', 'network']; + $fields['item-content'] = self::CONTENT_FIELDLIST; + $fields['author'] = ['url' => 'author-link', 'name' => 'author-name', 'thumb' => 'author-avatar', 'nick' => 'author-nick']; @@ -462,7 +462,7 @@ class Item extends BaseObject } if (strpos($sql_commands, "`item-content`.") !== false) { - $joins .= " LEFT JOIN `item-content` ON `item-content`.`uri` = `item`.`uri`"; + $joins .= " LEFT JOIN `item-content` ON `item-content`.`id` = `item`.`icid`"; } if ((strpos($sql_commands, "`parent-item`.") !== false) || (strpos($sql_commands, "`parent-author`.") !== false)) { @@ -491,7 +491,7 @@ class Item extends BaseObject foreach ($table_fields as $field => $select) { if (empty($selected) || in_array($select, $selected)) { if (in_array($select, self::CONTENT_FIELDLIST)) { - $selection[] = "`item-content`.`".$select."` AS `item-content-" . $select . "`"; + $selection[] = "`item`.`".$select."` AS `item-" . $select . "`"; } if (is_int($field)) { $selection[] = "`" . $table . "`.`" . $select . "`"; @@ -579,7 +579,7 @@ class Item extends BaseObject $rows = dba::affected_rows(); while ($item = dba::fetch($items)) { - self::updateContent($content_fields, ['uri' => $item['uri']]); + self::updateContent($content_fields, ['id' => $item['icid']]); Term::insertFromTagFieldByItemId($item['id']); Term::insertFromFileFieldByItemId($item['id']); self::updateThread($item['id']); @@ -1446,11 +1446,11 @@ class Item extends BaseObject */ private static function insertContent(&$item) { - logger('Insert content for URI '.$item['uri']); - $fields = ['uri' => $item['uri'], 'plink' => $item['plink'], 'uri-plink-hash' => hash('sha1', $item['plink']).hash('sha1', $item['uri'])]; + unset($item['plink']); + foreach (self::CONTENT_FIELDLIST as $field) { if (isset($item[$field])) { $fields[$field] = $item[$field]; @@ -1458,7 +1458,20 @@ class Item extends BaseObject } } - dba::insert('item-content', $fields, true); + // Do we already have this content? + $item_content = dba::selectFirst('item-content', ['id'], ['uri' => $item['uri']]); + if (DBM::is_result($item_content)) { + $item['icid'] = $item_content['id']; + logger('Assigned content for URI '.$item['uri'].' ('.$item['icid'].')'); + return; + } + + dba::insert('item-content', $fields); + + $item['icid'] = dba::lastInsertId(); + + logger('Insert content for URI '.$item['uri'].' ('.$item['icid'].')'); + } /** @@ -1481,7 +1494,7 @@ class Item extends BaseObject return; } - logger('Update content for URI '.$condition['uri']); + logger('Update content for id '.$condition['id']); dba::update('item-content', $fields, $condition, true); } -- 2.39.5