From 8b9acf750bde9b21551367f34e57ff549d7d3297 Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Wed, 24 Oct 2018 20:22:27 +0200 Subject: autocomplete performance: this slightly changes the editor contact autocomplete behaviour. queries using *like* with a prepended % to the query string do not make use of indices. this is no big issue when we query abook but can get really slow when xchan table is involved. this commit changes the xchan table only queries to use the *str%* format. this means that the result set for channels we are not connected with will change in a way that xchan_name and xchan_addr will bematched only from the beginning of the name or address. this commit also changes textcomplete to only start the query after the 3rd character. the result set between 2 and 3 characters is mostly very different and only from 3 chars on there is a high possibility the channel we look for is already in the result. --- Zotlabs/Module/Acl.php | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) (limited to 'Zotlabs/Module') diff --git a/Zotlabs/Module/Acl.php b/Zotlabs/Module/Acl.php index ea131e08c..cbb58f5fe 100644 --- a/Zotlabs/Module/Acl.php +++ b/Zotlabs/Module/Acl.php @@ -83,7 +83,8 @@ class Acl extends \Zotlabs\Web\Controller { if($search) { $sql_extra = " AND pgrp.gname LIKE " . protect_sprintf( "'%" . dbesc($search) . "%'" ) . " "; $sql_extra2 = "AND ( xchan_name LIKE " . protect_sprintf( "'%" . dbesc($search) . "%'" ) . " OR xchan_addr LIKE " . protect_sprintf( "'%" . dbesc(punify($search)) . ((strpos($search,'@') === false) ? "%@%'" : "%'")) . ") "; - + $sql_extra2_xchan = "AND ( xchan_name LIKE " . protect_sprintf( "'" . dbesc($search) . "%'" ) . " OR xchan_addr LIKE " . protect_sprintf( "'" . dbesc(punify($search)) . ((strpos($search,'@') === false) ? "%@%'" : "%'")) . ") "; + // This horrible mess is needed because position also returns 0 if nothing is found. // Would be MUCH easier if it instead returned a very large value // Otherwise we could just @@ -226,7 +227,7 @@ class Acl extends \Zotlabs\Web\Controller { else { // Visitors $r = q("SELECT xchan_hash as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, 0 as abook_their_perms, 0 as abook_flags, 0 as abook_self FROM xchan left join xlink on xlink_link = xchan_hash - WHERE xlink_xchan = '%s' AND xchan_deleted = 0 $sql_extra2 order by $order_extra2 xchan_name asc" , + WHERE xlink_xchan = '%s' AND xchan_deleted = 0 $sql_extra2_xchan order by $order_extra2 xchan_name asc" , dbesc(get_observer_hash()) ); @@ -242,7 +243,7 @@ class Acl extends \Zotlabs\Web\Controller { $r2 = q("SELECT abook_id as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, abook_their_perms, abook_flags, abook_self FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel IN ($extra_channels_sql) $known_hashes_sql AND abook_blocked = 0 and abook_pending = 0 and abook_hidden = 0 and xchan_deleted = 0 $sql_extra2 order by $order_extra2 xchan_name asc"); + WHERE abook_channel IN ($extra_channels_sql) $known_hashes_sql AND abook_blocked = 0 and abook_pending = 0 and abook_hidden = 0 and xchan_deleted = 0 $sql_extra2_xchan order by $order_extra2 xchan_name asc"); if($r2) $r = array_merge($r,$r2); @@ -270,7 +271,7 @@ class Acl extends \Zotlabs\Web\Controller { if((count($r) < 100) && $type == 'c') { $r2 = q("SELECT substr(xchan_hash,1,18) as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, 0 as abook_their_perms, 0 as abook_flags, 0 as abook_self FROM xchan - WHERE xchan_deleted = 0 and not xchan_network in ('rss','anon','unknown') $sql_extra2 order by $order_extra2 xchan_name asc" + WHERE xchan_deleted = 0 and not xchan_network in ('rss','anon','unknown') $sql_extra2_xchan order by $order_extra2 xchan_name asc" ); if($r2) { $r = array_merge($r,$r2); -- cgit v1.2.3 From 615402ea83e0ff3fa4b92a1e50ef40f8d18d61f5 Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Wed, 24 Oct 2018 20:34:15 +0200 Subject: wrong var --- Zotlabs/Module/Acl.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'Zotlabs/Module') diff --git a/Zotlabs/Module/Acl.php b/Zotlabs/Module/Acl.php index cbb58f5fe..738e8fbe2 100644 --- a/Zotlabs/Module/Acl.php +++ b/Zotlabs/Module/Acl.php @@ -243,7 +243,7 @@ class Acl extends \Zotlabs\Web\Controller { $r2 = q("SELECT abook_id as id, xchan_hash as hash, xchan_name as name, xchan_photo_s as micro, xchan_url as url, xchan_addr as nick, abook_their_perms, abook_flags, abook_self FROM abook left join xchan on abook_xchan = xchan_hash - WHERE abook_channel IN ($extra_channels_sql) $known_hashes_sql AND abook_blocked = 0 and abook_pending = 0 and abook_hidden = 0 and xchan_deleted = 0 $sql_extra2_xchan order by $order_extra2 xchan_name asc"); + WHERE abook_channel IN ($extra_channels_sql) $known_hashes_sql AND abook_blocked = 0 and abook_pending = 0 and abook_hidden = 0 and xchan_deleted = 0 $sql_extra2 order by $order_extra2 xchan_name asc"); if($r2) $r = array_merge($r,$r2); -- cgit v1.2.3 From 5c4fbbebe1bd633e30a907b4a188d04c2f2f4fb0 Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Wed, 24 Oct 2018 20:58:25 +0200 Subject: refine stream filter and forum notification behaviour (mostly for performance). personal posts will be displayed in unthreaded mode and clicking a forum notification will show only the unseen items of the forum in unthreaded mode. group the filters in the widget - threaded at the top, unthreaded beneath. --- Zotlabs/Module/Network.php | 50 +++++++++++++++++++++++++++------------------- Zotlabs/Module/Ping.php | 2 +- 2 files changed, 31 insertions(+), 21 deletions(-) (limited to 'Zotlabs/Module') diff --git a/Zotlabs/Module/Network.php b/Zotlabs/Module/Network.php index 792f92418..e8ee1eef9 100644 --- a/Zotlabs/Module/Network.php +++ b/Zotlabs/Module/Network.php @@ -128,11 +128,12 @@ class Network extends \Zotlabs\Web\Controller { $xchan = ((x($_GET,'xchan')) ? $_GET['xchan'] : ''); $net = ((x($_GET,'net')) ? $_GET['net'] : ''); $pf = ((x($_GET,'pf')) ? $_GET['pf'] : ''); + $unseen = ((x($_GET,'unseen')) ? $_GET['unseen'] : ''); $deftag = ''; - if(x($_GET,'search') || $file || (!$pf && $cid) || $hashtags || $verb || $category) + if(x($_GET,'search') || $file || (!$pf && $cid) || $hashtags || $verb || $category || $conv || $unseen) $nouveau = true; if($cid) { @@ -220,6 +221,7 @@ class Network extends \Zotlabs\Web\Controller { $sql_extra = ''; if($group) { + $contact_str = ''; $contacts = group_get_members($group); if($contacts) { @@ -232,7 +234,6 @@ class Network extends \Zotlabs\Web\Controller { } } $item_thread_top = ''; - $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND (( author_xchan IN ( $contact_str ) OR owner_xchan in ( $contact_str )) or allow_gid like '" . protect_sprintf('%<' . dbesc($group_hash) . '>%') . "' ) and id = parent $item_normal ) "; $x = group_rec_byhash(local_channel(), $group_hash); @@ -252,9 +253,20 @@ class Network extends \Zotlabs\Web\Controller { if($load || $update) { if(!$pf && $nouveau) { + // This is for nouveau view cid queries (not a public forum) $sql_extra = " AND author_xchan = '" . dbesc($cid_r[0]['abook_xchan']) . "' "; } + elseif($pf && $unseen && $nouveau) { + + $ttype = TERM_FORUM; + // This is for nouveau view public forum cid queries (if a forum notification is clicked) + $p = q("SELECT oid AS parent FROM term WHERE uid = " . intval(local_channel()) . " AND ttype = $ttype AND term = '" . dbesc($cid_r[0]['xchan_name']) . "'"); + + $p = ids_to_querystr($p, 'parent'); + $sql_extra = " AND ( owner_xchan = '" . dbesc($cid_r[0]['abook_xchan']) . "' OR item.parent IN ( $p ) ) AND item_unseen = 1 "; + } else { + // This is for threaded view cid queries (e.g. if a forum is selected from the forum filter) $ttype = (($pf) ? TERM_FORUM : TERM_MENTION); $p1 = q("SELECT DISTINCT parent FROM item WHERE uid = " . intval(local_channel()) . " AND ( author_xchan = '" . dbesc($cid_r[0]['abook_xchan']) . "' OR owner_xchan = '" . dbesc($cid_r[0]['abook_xchan']) . "' ) $item_normal "); @@ -345,7 +357,8 @@ class Network extends \Zotlabs\Web\Controller { '$verb' => $verb, '$net' => $net, '$dbegin' => $datequery2, - '$pf' => (($pf) ? $pf : '0'), + '$pf' => (($pf) ? $pf : '0'), + '$unseen' => $unseen )); } @@ -386,15 +399,7 @@ class Network extends \Zotlabs\Web\Controller { if($conv) { $item_thread_top = ''; - - if($nouveau) { - $sql_extra .= " AND author_xchan = '" . dbesc($channel['channel_hash']) . "' "; - } - else { - $sql_extra .= sprintf(" AND parent IN (SELECT distinct(parent) from item where ( author_xchan = '%s' or item_mentionsme = 1 )) ", - dbesc(protect_sprintf($channel['channel_hash'])) - ); - } + $sql_extra .= " AND ( author_xchan = '" . dbesc($channel['channel_hash']) . "' OR item_mentionsme = 1 ) "; } if($update && ! $load) { @@ -441,9 +446,11 @@ class Network extends \Zotlabs\Web\Controller { $page_mode = 'list'; else $page_mode = 'client'; - - $simple_update = (($update) ? " and item_unseen = 1 " : ''); + $parents_str = ''; + $update_unseen = ''; + + $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 @@ -459,15 +466,15 @@ class Network extends \Zotlabs\Web\Controller { if($update && $_SESSION['loadtime']) $simple_update = " AND (( item_unseen = 1 AND item.changed > '" . datetime_convert('UTC','UTC',$_SESSION['loadtime']) . "' ) OR item.changed > '" . datetime_convert('UTC','UTC',$_SESSION['loadtime']) . "' ) "; - if($load) - $simple_update = ''; + + if($load) + $simple_update = ''; if($static && $simple_update) $simple_update .= " and item_thread_top = 0 and author_xchan = '" . protect_sprintf(get_observer_hash()) . "' "; if($nouveau && $load) { // "New Item View" - show all items unthreaded in reverse created date order - $items = q("SELECT item.*, item.id AS item_id, created FROM item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) $net_query @@ -478,7 +485,12 @@ class Network extends \Zotlabs\Web\Controller { $net_query2 ORDER BY item.created DESC $pager_sql " ); - + + $parents_str = ids_to_querystr($items,'item_id'); + if($parents_str) { + $update_unseen = " AND id IN ( " . dbesc($parents_str) . " )"; + } + require_once('include/items.php'); xchan_query($items); @@ -521,8 +533,6 @@ class Network extends \Zotlabs\Web\Controller { } // Then fetch all the children of the parents that are on this page - $parents_str = ''; - $update_unseen = ''; if($r) { diff --git a/Zotlabs/Module/Ping.php b/Zotlabs/Module/Ping.php index 14627f56e..bc6985301 100644 --- a/Zotlabs/Module/Ping.php +++ b/Zotlabs/Module/Ping.php @@ -650,7 +650,7 @@ class Ping extends \Zotlabs\Web\Controller { dbesc($forums[$x]['xchan_hash']) ); if($r[0]['unseen']) { - $forums[$x]['notify_link'] = (($forums[$x]['private_forum']) ? $forums[$x]['xchan_url'] : z_root() . '/network/?f=&pf=1&cid=' . $forums[$x]['abook_id']); + $forums[$x]['notify_link'] = (($forums[$x]['private_forum']) ? $forums[$x]['xchan_url'] : z_root() . '/network/?f=&pf=1&unseen=1&cid=' . $forums[$x]['abook_id']); $forums[$x]['name'] = $forums[$x]['xchan_name']; $forums[$x]['url'] = $forums[$x]['xchan_url']; $forums[$x]['photo'] = $forums[$x]['xchan_photo_s']; -- cgit v1.2.3