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 +++++---- view/js/autocomplete.js | 2 +- 2 files changed, 6 insertions(+), 5 deletions(-) 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); diff --git a/view/js/autocomplete.js b/view/js/autocomplete.js index aa580b163..6b77f0631 100644 --- a/view/js/autocomplete.js +++ b/view/js/autocomplete.js @@ -163,7 +163,7 @@ function string2bb(element) { // Autocomplete contacts contacts = { - match: /(^|\s)(@\!*)([^ \n]{2,})$/, + match: /(^|\s)(@\!*)([^ \n]{3,})$/, index: 3, cache: true, search: function(term, callback) { contact_search(term, callback, backend_url, 'c', extra_channels, spinelement=false); }, -- 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(-) 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 +- Zotlabs/Widget/Activity_filter.php | 59 +++++++++++++++++++------------------- Zotlabs/Widget/Activity_order.php | 4 +-- include/dba/dba_driver.php | 2 +- view/tpl/build_query.tpl | 2 ++ 6 files changed, 66 insertions(+), 53 deletions(-) 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']; diff --git a/Zotlabs/Widget/Activity_filter.php b/Zotlabs/Widget/Activity_filter.php index 4ea0086dd..32ab10c77 100644 --- a/Zotlabs/Widget/Activity_filter.php +++ b/Zotlabs/Widget/Activity_filter.php @@ -16,35 +16,6 @@ class Activity_filter { $tabs = []; - if(feature_enabled(local_channel(),'personal_tab')) { - if(x($_GET,'conv')) { - $conv_active = (($_GET['conv'] == 1) ? 'active' : ''); - $filter_active = 'personal'; - } - - $tabs[] = [ - 'label' => t('Personal Posts'), - 'icon' => 'user-circle', - 'url' => z_root() . '/' . $cmd . '/?f=&conv=1', - 'sel' => $conv_active, - 'title' => t('Show posts that mention or involve me') - ]; - } - - if(feature_enabled(local_channel(),'star_posts')) { - if(x($_GET,'star')) { - $starred_active = (($_GET['star'] == 1) ? 'active' : ''); - $filter_active = 'star'; - } - - $tabs[] = [ - 'label' => t('Starred Posts'), - 'icon' => 'star', - 'url'=>z_root() . '/' . $cmd . '/?f=&star=1', - 'sel'=>$starred_active, - 'title' => t('Show posts that I have starred') - ]; - } if(Apps::system_app_installed(local_channel(), 'Privacy Groups')) { $groups = q("SELECT * FROM pgrp WHERE deleted = 0 AND uid = %d ORDER BY gname ASC", @@ -110,6 +81,36 @@ class Activity_filter { } } + if(feature_enabled(local_channel(),'star_posts')) { + if(x($_GET,'star')) { + $starred_active = (($_GET['star'] == 1) ? 'active' : ''); + $filter_active = 'star'; + } + + $tabs[] = [ + 'label' => t('Starred Posts'), + 'icon' => 'star', + 'url'=>z_root() . '/' . $cmd . '/?f=&star=1', + 'sel'=>$starred_active, + 'title' => t('Show posts that I have starred') + ]; + } + + if(feature_enabled(local_channel(),'personal_tab')) { + if(x($_GET,'conv')) { + $conv_active = (($_GET['conv'] == 1) ? 'active' : ''); + $filter_active = 'personal'; + } + + $tabs[] = [ + 'label' => t('Personal Posts'), + 'icon' => 'user-circle', + 'url' => z_root() . '/' . $cmd . '/?f=&conv=1', + 'sel' => $conv_active, + 'title' => t('Show posts that mention or involve me') + ]; + } + if(feature_enabled(local_channel(),'filing')) { $terms = q("select distinct term from term where uid = %d and ttype = %d order by term asc", intval(local_channel()), diff --git a/Zotlabs/Widget/Activity_order.php b/Zotlabs/Widget/Activity_order.php index 1cba1ce8c..d3fe2a30f 100644 --- a/Zotlabs/Widget/Activity_order.php +++ b/Zotlabs/Widget/Activity_order.php @@ -54,8 +54,8 @@ class Activity_order { } } - // override order for search, filer and cid results - if(x($_GET,'search') || x($_GET,'file') || (! x($_GET,'pf') && x($_GET,'cid')) || x($_GET,'verb') || x($_GET,'tag') || x($_GET,'cat')) { + // override order for some filter results + if(x($_GET,'search') || x($_GET,'file') || (! x($_GET,'pf') && x($_GET,'cid')) || x($_GET,'verb') || x($_GET,'tag') || x($_GET,'cat') || x($_GET,'conv') || x($_GET,'unseen')) { $unthreaded_active = 'active'; $commentord_active = $postord_active = 'disabled'; } diff --git a/include/dba/dba_driver.php b/include/dba/dba_driver.php index 9e9f24bb3..9533acc7f 100755 --- a/include/dba/dba_driver.php +++ b/include/dba/dba_driver.php @@ -484,4 +484,4 @@ function db_columns($table) { } return []; -} \ No newline at end of file +} diff --git a/view/tpl/build_query.tpl b/view/tpl/build_query.tpl index 2c55a8140..fcb22b605 100755 --- a/view/tpl/build_query.tpl +++ b/view/tpl/build_query.tpl @@ -31,6 +31,7 @@ var bParam_verb = "{{$verb}}"; var bParam_net = "{{$net}}"; var bParam_pf = "{{$pf}}"; + var bParam_unseen = "{{$unseen}}"; function buildCmd() { var udargs = ((page_load) ? "/load" : ""); @@ -62,6 +63,7 @@ if(bParam_net != "") bCmd = bCmd + "&net=" + bParam_net; if(bParam_page != 1) bCmd = bCmd + "&page=" + bParam_page; if(bParam_pf != 0) bCmd = bCmd + "&pf=" + bParam_pf; + if(bParam_unseen != 0) bCmd = bCmd + "&unseen=" + bParam_unseen; return(bCmd); } -- cgit v1.2.3 From 25c7e64ded56fcd8030cb898ba187be612c7b0f1 Mon Sep 17 00:00:00 2001 From: Mario Vavti Date: Wed, 24 Oct 2018 23:06:32 +0200 Subject: fix get on {1} --- view/tpl/notifications_widget.tpl | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/view/tpl/notifications_widget.tpl b/view/tpl/notifications_widget.tpl index 9b3d7487e..f43d82301 100644 --- a/view/tpl/notifications_widget.tpl +++ b/view/tpl/notifications_widget.tpl @@ -143,7 +143,7 @@