From da2349bb6a85d13f0aa29046bef3021cf0c884ba Mon Sep 17 00:00:00 2001 From: friendica Date: Thu, 12 Feb 2015 17:45:25 -0800 Subject: provide relief to sites that are severely impacted by the slow ITEM_UNSEEN searches. This does not incorporate any other flag optimisations as that will require a major DB update and possibly involve significant downtime. This is just to bite off a little chunk now and provide some much needed relief. --- boot.php | 2 +- include/ItemObject.php | 2 +- include/api.php | 5 +---- include/attach.php | 4 +++- include/diaspora.php | 4 ++-- include/items.php | 9 ++++----- include/notify.php | 2 +- install/schema_mysql.sql | 2 ++ install/schema_postgres.sql | 2 ++ install/update.php | 12 +++++++++++- mod/channel.php | 14 +++++--------- mod/display.php | 5 +---- mod/home.php | 2 +- mod/item.php | 6 +++--- mod/manage.php | 4 +--- mod/mood.php | 3 +-- mod/network.php | 7 ++----- mod/photos.php | 6 ++---- mod/ping.php | 19 +++++-------------- view/js/main.js | 3 +++ 20 files changed, 52 insertions(+), 61 deletions(-) diff --git a/boot.php b/boot.php index 81e1f6d76..f632931b6 100755 --- a/boot.php +++ b/boot.php @@ -49,7 +49,7 @@ define ( 'RED_PLATFORM', 'redmatrix' ); define ( 'RED_VERSION', trim(file_get_contents('version.inc')) . 'R'); define ( 'ZOT_REVISION', 1 ); -define ( 'DB_UPDATE_VERSION', 1136 ); +define ( 'DB_UPDATE_VERSION', 1137 ); /** * Constant with a HTML line break. diff --git a/include/ItemObject.php b/include/ItemObject.php index c429a0131..136e08eb7 100644 --- a/include/ItemObject.php +++ b/include/ItemObject.php @@ -612,7 +612,7 @@ class Item extends BaseObject { if((! visible_activity($child->data)) || array_key_exists('author_blocked',$child->data)) { continue; } - if($child->data['item_flags'] & ITEM_UNSEEN) + if(intval($child->data['item_unseen'])) $total ++; } } diff --git a/include/api.php b/include/api.php index d00cdcf8a..d5c2a429c 100644 --- a/include/api.php +++ b/include/api.php @@ -1004,10 +1004,7 @@ require_once('include/items.php'); // at the network timeline just mark everything seen. if (api_user() == $user_info['uid']) { - $r = q("UPDATE `item` SET item_flags = ( item_flags & ~%d ) - WHERE (item_flags & %d)>0 and uid = %d", - intval(ITEM_UNSEEN), - intval(ITEM_UNSEEN), + $r = q("UPDATE `item` SET item_unseen = 0 where item_unseen = 1 and uid = %d", intval($user_info['uid']) ); } diff --git a/include/attach.php b/include/attach.php index 5ed2cf6d2..3350a591e 100644 --- a/include/attach.php +++ b/include/attach.php @@ -998,7 +998,8 @@ function file_activity($channel_id, $object, $allow_cid, $allow_gid, $deny_cid, $objtype = ACTIVITY_OBJ_FILE; - $item_flags = ITEM_WALL|ITEM_ORIGIN|ITEM_UNSEEN; + $item_flags = ITEM_WALL|ITEM_ORIGIN; +; $private = (($allow_cid || $allow_gid || $deny_cid || $deny_gid) ? 1 : 0); @@ -1038,6 +1039,7 @@ function file_activity($channel_id, $object, $allow_cid, $allow_gid, $deny_cid, $arr['mid'] = $u_mid; $arr['parent_mid'] = $u_mid; $arr['item_flags'] = $item_flags; + $arr['item_unseen'] = 1; $arr['author_xchan'] = $poster['xchan_hash']; $arr['owner_xchan'] = $poster['xchan_hash']; $arr['title'] = ''; diff --git a/include/diaspora.php b/include/diaspora.php index 4280d9d77..559a9d14d 100755 --- a/include/diaspora.php +++ b/include/diaspora.php @@ -923,8 +923,8 @@ function diaspora_post($importer,$xml,$msg) { $datarray['app'] = $app; - $datarray['item_flags'] = ITEM_UNSEEN|ITEM_THREAD_TOP; - + $datarray['item_flags'] = ITEM_THREAD_TOP; + $datarray['item_unseen'] = 1; $result = item_store($datarray); return; diff --git a/include/items.php b/include/items.php index 91fa1d70a..717a4bf71 100755 --- a/include/items.php +++ b/include/items.php @@ -2043,13 +2043,12 @@ function item_store($arr,$allow_exec = false) { $arr['comment_policy'] = ((x($arr,'comment_policy')) ? notags(trim($arr['comment_policy'])) : 'contacts' ); - $arr['item_flags'] = $arr['item_flags'] | ITEM_UNSEEN; + $arr['item_unseen'] = ((array_key_exists('item_unseen',$arr)) ? intval($arr['item_unseen']) : 1); if($arr['comment_policy'] == 'none') $arr['item_flags'] = $arr['item_flags'] | ITEM_NOCOMMENT; - // handle time travelers // Allow a bit of fudge in case somebody just has a slightly slow/fast clock @@ -2332,8 +2331,8 @@ function item_store_update($arr,$allow_exec = false) { // override the unseen flag with the original - if($arr['item_flags'] & ITEM_UNSEEN) - $arr['item_flags'] = $arr['item_flags'] ^ ITEM_UNSEEN; + if(intval($arr['item_flags'])) + $arr['item_unseen'] = 0; if($orig[0]['item_flags'] & ITEM_VERIFIED) $orig[0]['item_flags'] = $orig[0]['item_flags'] ^ ITEM_VERIFIED; @@ -4562,7 +4561,7 @@ function items_fetch($arr,$channel = null,$observer_hash = null,$client_mode = C } } - $simple_update = (($client_mode & CLIENT_MODE_UPDATE) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " )>0 " : ''); + $simple_update = (($client_mode & CLIENT_MODE_UPDATE) ? " and ( item.item_unseen = 1 ) " : ''); if($client_mode & CLIENT_MODE_LOAD) $simple_update = ''; diff --git a/include/notify.php b/include/notify.php index aa96fa279..eef838664 100644 --- a/include/notify.php +++ b/include/notify.php @@ -32,7 +32,7 @@ function format_notification($item) { 'url' => $item['author']['xchan_url'], 'photo' => $item['author']['xchan_photo_s'], 'when' => relative_date($item['created']), - 'class' => (($item['item_flags'] & ITEM_UNSEEN) ? 'notify-unseen' : 'notify-seen'), + 'class' => (intval($item['item_unseen']) ? 'notify-unseen' : 'notify-seen'), 'message' => strip_tags(bbcode($itemem_text)) ); diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql index 8addc0af9..247b33814 100644 --- a/install/schema_mysql.sql +++ b/install/schema_mysql.sql @@ -688,6 +688,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `item_restrict` int(11) NOT NULL DEFAULT '0', `item_flags` int(11) NOT NULL DEFAULT '0', `item_private` tinyint(4) NOT NULL DEFAULT '0', + `item_unseen` smallint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `uid` (`uid`), KEY `parent` (`parent`), @@ -717,6 +718,7 @@ CREATE TABLE IF NOT EXISTS `item` ( KEY `public_policy` (`public_policy`), KEY `comments_closed` (`comments_closed`), KEY `changed` (`changed`), + KEY `item_unseen` (`item_unseen`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `body` (`body`), FULLTEXT KEY `allow_cid` (`allow_cid`), diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql index b68a7cb97..d852f38e9 100644 --- a/install/schema_postgres.sql +++ b/install/schema_postgres.sql @@ -528,6 +528,7 @@ CREATE TABLE "item" ( "item_restrict" bigint NOT NULL DEFAULT '0', "item_flags" bigint NOT NULL DEFAULT '0', "item_private" numeric(4) NOT NULL DEFAULT '0', + "item_unseen" smallint(1) NOT NULL DEFAULT '0', "item_search_vector" tsvector, PRIMARY KEY ("id") ); @@ -559,6 +560,7 @@ create index "item_uid_mid" on item ("mid","uid"); create index "item_public_policy" on item ("public_policy"); create index "item_comment_policy" on item ("comment_policy"); create index "item_layout_mid" on item ("layout_mid"); +create index "item_unseen" on item ("item_unseen"); -- fulltext indexes create index "item_search_idx" on item USING gist("item_search_vector"); diff --git a/install/update.php b/install/update.php index 2c9e54ff9..d2f1ead3c 100644 --- a/install/update.php +++ b/install/update.php @@ -1,6 +1,6 @@ 0 "); + + if($r1 && $r2 && $r3) + return UPDATE_SUCCESS; + return UPDATE_FAILED; +} diff --git a/mod/channel.php b/mod/channel.php index 1a471718d..788bacf70 100644 --- a/mod/channel.php +++ b/mod/channel.php @@ -147,23 +147,21 @@ function channel_content(&$a, $update = 0, $load = false) { if(($update) && (! $load)) { if ($mid) { $r = q("SELECT parent AS item_id from item where mid = '%s' and uid = %d AND item_restrict = 0 - AND (item_flags & %d) > 0 AND (item_flags & %d) > 0 $sql_extra limit 1", + AND (item_flags & %d) > 0 AND item_unseen = 1 $sql_extra limit 1", dbesc($mid), intval($a->profile['profile_uid']), - intval(ITEM_WALL), - intval(ITEM_UNSEEN) + intval(ITEM_WALL) ); } else { $r = q("SELECT distinct parent AS `item_id`, created from item left join abook on item.author_xchan = abook.abook_xchan WHERE uid = %d AND item_restrict = 0 - AND (item_flags & %d) > 0 AND ( item_flags & %d ) > 0 + AND (item_flags & %d) > 0 AND item_unseen = 1 AND ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra ORDER BY created DESC", intval($a->profile['profile_uid']), intval(ITEM_WALL), - intval(ITEM_UNSEEN), intval(ABOOK_FLAG_BLOCKED) ); } @@ -317,10 +315,8 @@ function channel_content(&$a, $update = 0, $load = false) { } if($is_owner && $update_unseen) { - $r = q("UPDATE item SET item_flags = (item_flags & ~%d) - WHERE (item_flags & %d) > 0 AND (item_flags & %d) > 0 AND uid = %d $update_unseen", - intval(ITEM_UNSEEN), - intval(ITEM_UNSEEN), + $r = q("UPDATE item SET item_unseen = 0 WHERE item_unseen = 1 + AND (item_flags & %d) > 0 AND uid = %d $update_unseen", intval(ITEM_WALL), intval(local_channel()) ); diff --git a/mod/display.php b/mod/display.php index 467c476da..c2e5c2426 100644 --- a/mod/display.php +++ b/mod/display.php @@ -231,10 +231,7 @@ function display_content(&$a, $update = 0, $load = false) { } if($updateable) { - $x = q("UPDATE item SET item_flags = ( item_flags & ~%d ) - WHERE (item_flags & %d)>0 AND uid = %d and parent = %d ", - intval(ITEM_UNSEEN), - intval(ITEM_UNSEEN), + $x = q("UPDATE item SET item_unseen = 0 WHERE item_unseen = 1 AND uid = %d and parent = %d ", intval(local_channel()), intval($r[0]['parent']) ); diff --git a/mod/home.php b/mod/home.php index 854e2df26..db4ae9c42 100644 --- a/mod/home.php +++ b/mod/home.php @@ -170,7 +170,7 @@ function home_content(&$a, $update = 0, $load = false) { $page_mode = 'list'; - $simple_update = (($update) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " ) > 0 " : ''); + $simple_update = (($update) ? " and item.item_unseen = 1 " : ''); if($update && $_SESSION['loadtime']) $simple_update .= " and item.changed > '" . datetime_convert('UTC','UTC',$_SESSION['loadtime']) . "' "; diff --git a/mod/item.php b/mod/item.php index ef0491895..99d28400b 100644 --- a/mod/item.php +++ b/mod/item.php @@ -643,8 +643,7 @@ function item_post(&$a) { } } - if(local_channel() != $profile_uid) - $item_flags |= ITEM_UNSEEN; + $item_unseen = ((local_channel() != $profile_uid) ? 1 : 0); if($post_type === 'wall' || $post_type === 'wall-comment') $item_flags = $item_flags | ITEM_WALL; @@ -694,7 +693,7 @@ function item_post(&$a) { $datarray['aid'] = $channel['channel_account_id']; $datarray['uid'] = $profile_uid; - + $datarray['owner_xchan'] = (($owner_hash) ? $owner_hash : $owner_xchan['xchan_hash']); $datarray['author_xchan'] = $observer['xchan_hash']; $datarray['created'] = $created; @@ -729,6 +728,7 @@ function item_post(&$a) { $datarray['term'] = $post_tags; $datarray['plink'] = $plink; $datarray['route'] = $route; + $datarray['item_unseen'] = $item_unseen; // preview mode - prepare the body for display and send it via json diff --git a/mod/manage.php b/mod/manage.php index 4a7b25068..cb46a1b76 100644 --- a/mod/manage.php +++ b/mod/manage.php @@ -57,9 +57,7 @@ function manage_content(&$a) { $c = q("SELECT id, item_restrict, item_flags FROM item - WHERE (item_restrict = %d) and ( item_flags & %d )>0 and uid = %d", - intval(ITEM_VISIBLE), - intval(ITEM_UNSEEN), + WHERE item_restrict = 0 and item_unseen = 1 and uid = %d", intval($channels[$x]['channel_id']) ); diff --git a/mod/mood.php b/mod/mood.php index 4fe1ceb59..7663b2632 100755 --- a/mod/mood.php +++ b/mod/mood.php @@ -61,11 +61,10 @@ function mood_init(&$a) { $mid = item_message_id(); $action = sprintf( t('%1$s is %2$s','mood'), '[zrl=' . $poster['xchan_url'] . ']' . $poster['xchan_name'] . '[/zrl]' , $verbs[$verb]); - $item_flags = ITEM_WALL|ITEM_ORIGIN|ITEM_UNSEEN; + $item_flags = ITEM_WALL|ITEM_ORIGIN; if(! $parent_mid) $item_flags |= ITEM_THREAD_TOP; - $arr = array(); $arr['aid'] = get_account_id(); diff --git a/mod/network.php b/mod/network.php index 9a4707b4d..236d4c5e0 100644 --- a/mod/network.php +++ b/mod/network.php @@ -346,7 +346,7 @@ function network_content(&$a, $update = 0, $load = false) { else $page_mode = 'client'; - $simple_update = (($update) ? " and ( item.item_flags & " . intval(ITEM_UNSEEN) . " ) > 0 " : ''); + $simple_update = (($update) ? " and item.unseen = 1 " : ''); // This fixes a very subtle bug so I'd better explain it. You wake up in the morning or return after a day // or three and look at your matrix page - after opening up your browser. The first page loads just as it @@ -465,10 +465,7 @@ function network_content(&$a, $update = 0, $load = false) { } if(($update_unseen) && (! $firehose)) - $r = q("UPDATE item SET item_flags = ( item_flags & ~%d) - WHERE (item_flags & %d) > 0 AND uid = %d $update_unseen ", - intval(ITEM_UNSEEN), - intval(ITEM_UNSEEN), + $r = q("UPDATE item SET item_unseen = 0 where item_unseen = 1 AND uid = %d $update_unseen ", intval(local_channel()) ); diff --git a/mod/photos.php b/mod/photos.php index dbe165f60..33854dd76 100644 --- a/mod/photos.php +++ b/mod/photos.php @@ -877,11 +877,9 @@ function photos_content(&$a) { } if((local_channel()) && (local_channel() == $link_item['uid'])) { - q("UPDATE `item` SET item_flags = (item_flags & ~%d) WHERE parent = %d and uid = %d and (item_flags & %d)>0", - intval(ITEM_UNSEEN), + q("UPDATE `item` SET item_unseen = 0 WHERE item_unseen = 1 AND parent = %d AND uid = %d ", intval($link_item['parent']), - intval(local_channel()), - intval(ITEM_UNSEEN) + intval(local_channel()) ); } } diff --git a/mod/ping.php b/mod/ping.php index 6a89ad5cf..f49789be5 100644 --- a/mod/ping.php +++ b/mod/ping.php @@ -151,16 +151,12 @@ function ping_init(&$a) { if(x($_REQUEST, 'markRead') && local_channel()) { switch($_REQUEST['markRead']) { case 'network': - $r = q("update item set item_flags = ( item_flags & ~%d ) where (item_flags & %d) > 0 and uid = %d", - intval(ITEM_UNSEEN), - intval(ITEM_UNSEEN), + $r = q("update item set item_unseen = 0 where item_unseen = 1 and uid = %d", intval(local_channel()) ); break; case 'home': - $r = q("update item set item_flags = ( item_flags & ~%d ) where (item_flags & %d) > 0 and (item_flags & %d) > 0 and uid = %d", - intval(ITEM_UNSEEN), - intval(ITEM_UNSEEN), + $r = q("update item set item_unseen = 0 where item_unseen = 1 and (item_flags & %d) > 0 and uid = %d", intval(ITEM_WALL), intval(local_channel()) ); @@ -190,8 +186,7 @@ function ping_init(&$a) { } if(x($_REQUEST, 'markItemRead') && local_channel()) { - $r = q("update item set item_flags = ( item_flags & ~%d ) where parent = %d and uid = %d", - intval(ITEM_UNSEEN), + $r = q("update item set item_unseen = 0 where parent = %d and uid = %d", intval($_REQUEST['markItemRead']), intval(local_channel()) ); @@ -278,10 +273,8 @@ function ping_init(&$a) { $result = array(); $r = q("SELECT * FROM item - WHERE item_restrict = %d and ( item_flags & %d ) > 0 and uid = %d + WHERE item_restrict = 0 and item_unseen = 1 and uid = %d and author_xchan != '%s' ORDER BY created DESC", - intval(ITEM_VISIBLE), - intval(ITEM_UNSEEN), intval(local_channel()), dbesc($ob_hash) ); @@ -386,10 +379,8 @@ function ping_init(&$a) { if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) { $r = q("SELECT id, item_restrict, item_flags FROM item - WHERE (item_restrict = %d) and ( item_flags & %d ) > 0 and uid = %d + WHERE item_restrict = 0 and item_unseen = 1 and uid = %d and author_xchan != '%s'", - intval(ITEM_VISIBLE), - intval(ITEM_UNSEEN), intval(local_channel()), dbesc($ob_hash) ); diff --git a/view/js/main.js b/view/js/main.js index fac5e1143..17ea42db7 100644 --- a/view/js/main.js +++ b/view/js/main.js @@ -1155,6 +1155,8 @@ $(window).scroll(function () { } if($(window).scrollTop() + $(window).height() > $(document).height() - 100) { +// if($(window).scrollTop() > ($(document).height() - $(window).height() * 1.5 )) { + if((pageHasMoreContent) && (! loadingPage)) { $('#more').hide(); $('#no-more').hide(); @@ -1175,6 +1177,7 @@ $(window).scroll(function () { } if($(window).scrollTop() + $(window).height() > $(document).height() - 100) { +// if($(window).scrollTop() > ($(document).height() - $(window).height() * 1.5 )) { if((pageHasMoreContent) && (! loadingPage) && (! justifiedGalleryActive)) { $('#more').hide(); $('#no-more').hide(); -- cgit v1.2.3