From ca870dbf31d31455ab4f5e0f461c5af89e1573fa Mon Sep 17 00:00:00 2001 From: redmatrix Date: Wed, 10 Jun 2015 16:59:04 -0700 Subject: more work on item table optimisation --- mod/bookmarks.php | 6 ++++-- mod/channel.php | 12 ++++++------ mod/display.php | 11 ++++++----- mod/item.php | 8 ++++---- mod/like.php | 4 +++- mod/manage.php | 4 ++-- mod/network.php | 14 +++++++------- mod/photos.php | 5 ++--- mod/ping.php | 9 ++++++--- mod/public.php | 8 +++++--- mod/search.php | 12 ++++++------ mod/viewsrc.php | 3 ++- mod/webpages.php | 4 ++-- 13 files changed, 55 insertions(+), 45 deletions(-) (limited to 'mod') diff --git a/mod/bookmarks.php b/mod/bookmarks.php index a5b9c473f..c3d25253f 100644 --- a/mod/bookmarks.php +++ b/mod/bookmarks.php @@ -9,7 +9,9 @@ function bookmarks_init(&$a) { $u = $a->get_channel(); - $i = q("select * from item where id = %d and uid = %d limit 1", + $item_normal = item_normal(); + + $i = q("select * from item where id = %d and uid = %d $item_normal limit 1", intval($item_id), intval(local_channel()) ); @@ -23,7 +25,7 @@ function bookmarks_init(&$a) { $terms = get_terms_oftype($item['term'],TERM_BOOKMARK); - if($terms && (! $item['item_restrict'])) { + if($terms) { require_once('include/bookmarks.php'); $s = q("select * from xchan where xchan_hash = '%s' limit 1", diff --git a/mod/channel.php b/mod/channel.php index 65af36b59..c0f1f419f 100644 --- a/mod/channel.php +++ b/mod/channel.php @@ -145,7 +145,7 @@ function channel_content(&$a, $update = 0, $load = false) { * Get permissions SQL - if $remote_contact is true, our remote user has been pre-verified and we already have fetched his/her groups */ - + $item_normal = item_normal(); $sql_extra = item_permissions_sql($a->profile['profile_uid']); if(get_pconfig($a->profile['profile_uid'],'system','channel_list_mode') && (! $mid)) @@ -165,7 +165,7 @@ function channel_content(&$a, $update = 0, $load = false) { if(($update) && (! $load)) { if ($mid) { - $r = q("SELECT parent AS item_id from item where mid like '%s' and uid = %d AND item_restrict = 0 + $r = q("SELECT parent AS item_id from item where mid like '%s' and uid = %d $item_normal AND item_wall = 1 AND item_unseen = 1 $sql_extra limit 1", dbesc($mid . '%'), intval($a->profile['profile_uid']) @@ -173,7 +173,7 @@ function channel_content(&$a, $update = 0, $load = false) { } else { $r = q("SELECT distinct parent AS `item_id`, created from item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) - WHERE uid = %d AND item_restrict = 0 + WHERE uid = %d $item_normal AND item_wall = 1 AND item_unseen = 1 AND ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra @@ -207,7 +207,7 @@ function channel_content(&$a, $update = 0, $load = false) { if($load || ($_COOKIE['jsAvailable'] != 1)) { if ($mid) { - $r = q("SELECT parent AS item_id from item where mid = '%s' and uid = %d AND item_restrict = 0 + $r = q("SELECT parent AS item_id from item where mid = '%s' and uid = %d $item_normal AND item_wall = 1 $sql_extra limit 1", dbesc($mid), intval($a->profile['profile_uid']) @@ -219,7 +219,7 @@ function channel_content(&$a, $update = 0, $load = false) { } else { $r = q("SELECT distinct id AS item_id, created FROM item left join abook on item.author_xchan = abook.abook_xchan - WHERE uid = %d AND item_restrict = 0 + WHERE uid = %d $item_normal AND item_wall = 1 and item_thread_top = 1 AND ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra $sql_extra2 @@ -240,7 +240,7 @@ function channel_content(&$a, $update = 0, $load = false) { $items = q("SELECT `item`.*, `item`.`id` AS `item_id` FROM `item` - WHERE `item`.`uid` = %d AND `item`.`item_restrict` = 0 + WHERE `item`.`uid` = %d $item_normal AND `item`.`parent` IN ( %s ) $sql_extra ", intval($a->profile['profile_uid']), diff --git a/mod/display.php b/mod/display.php index 33fb83e70..2f61e688a 100644 --- a/mod/display.php +++ b/mod/display.php @@ -157,6 +157,7 @@ function display_content(&$a, $update = 0, $load = false) { } $observer_hash = get_observer_hash(); + $item_normal = item_normal(); $sql_extra = public_permissions_sql($observer_hash); @@ -175,9 +176,9 @@ function display_content(&$a, $update = 0, $load = false) { if(local_channel()) { $r = q("SELECT * from item - WHERE item_restrict = 0 - and uid = %d + WHERE uid = %d and mid = '%s' + $item_normal limit 1", intval(local_channel()), dbesc($target_item['parent_mid']) @@ -198,13 +199,13 @@ function display_content(&$a, $update = 0, $load = false) { $r = q("SELECT * from item - WHERE item_restrict = 0 - and mid = '%s' + WHERE mid = '%s' AND (((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND item_private = 0 ) and owner_xchan in ( " . stream_perms_xchans(($observer_hash) ? (PERMS_NETWORK|PERMS_PUBLIC) : PERMS_PUBLIC) . " )) OR uid = %d ) $sql_extra ) + $item_normal limit 1", dbesc($target_item['parent_mid']), intval($sysid) @@ -224,7 +225,7 @@ function display_content(&$a, $update = 0, $load = false) { $items = q("SELECT `item`.*, `item`.`id` AS `item_id` FROM `item` - WHERE item_restrict = 0 and parent in ( %s ) ", + WHERE parent in ( %s ) $item_normal ", dbesc($parents_str) ); diff --git a/mod/item.php b/mod/item.php index d631bd1f2..90f681854 100644 --- a/mod/item.php +++ b/mod/item.php @@ -676,7 +676,7 @@ function item_post(&$a) { if($moderated) - $item_restrict = $item_restrict | ITEM_MODERATED; + $item_blocked = ITEM_MODERATED; if(! strlen($verb)) @@ -940,7 +940,7 @@ function item_content(&$a) { if((argc() == 3) && (argv(1) === 'drop') && intval(argv(2))) { require_once('include/items.php'); - $i = q("select id, uid, author_xchan, owner_xchan, source_xchan, item_restrict from item where id = %d limit 1", + $i = q("select id, uid, author_xchan, owner_xchan, source_xchan, item_type from item where id = %d limit 1", intval(argv(2)) ); @@ -966,7 +966,7 @@ function item_content(&$a) { // if this is a different page type or it's just a local delete // but not by the item author or owner, do a simple deletion - if($i[0]['item_restrict'] || ($local_delete && (! $can_delete))) { + if(intval($i[0]['item_type']) || ($local_delete && (! $can_delete))) { drop_item($i[0]['id']); } else { @@ -1092,7 +1092,7 @@ function item_check_service_class($channel_id,$iswebpage) { ); } else { - $r = q("select count(id) as total from item where parent = id and item_restrict = 0 and item_wall = 1 and uid = %d ", + $r = q("select count(id) as total from item where parent = id and item_wall = 1 and uid = %d " . item_normal(), intval($channel_id) ); } diff --git a/mod/like.php b/mod/like.php index 57939885a..ffd302efa 100755 --- a/mod/like.php +++ b/mod/like.php @@ -304,7 +304,9 @@ function like_content(&$a) { $multi_undo = 1; } - $r = q("SELECT id, parent, uid, verb FROM item WHERE verb in ( $verbs ) AND item_restrict = 0 + $item_normal = item_normal(); + + $r = q("SELECT id, parent, uid, verb FROM item WHERE verb in ( $verbs ) $item_normal AND author_xchan = '%s' AND ( parent = %d OR thr_parent = '%s') and uid = %d ", dbesc($observer['xchan_hash']), intval($item_id), diff --git a/mod/manage.php b/mod/manage.php index bb6c010f6..bc538e564 100644 --- a/mod/manage.php +++ b/mod/manage.php @@ -56,8 +56,8 @@ function manage_content(&$a) { $channels[$x]['default_links'] = '1'; - $c = q("SELECT id, item_restrict, item_wall FROM item - WHERE item_restrict = 0 and item_unseen = 1 and uid = %d", + $c = q("SELECT id, item_wall FROM item + WHERE item_unseen = 1 and uid = %d " . item_normal(), intval($channels[$x]['channel_id']) ); diff --git a/mod/network.php b/mod/network.php index 6431883d5..f4c8390dc 100644 --- a/mod/network.php +++ b/mod/network.php @@ -40,7 +40,7 @@ function network_content(&$a, $update = 0, $load = false) { call_hooks('network_content_init', $arr); $channel = $a->get_channel(); - + $item_normal = item_normal(); $datequery = $datequery2 = ''; @@ -198,7 +198,7 @@ function network_content(&$a, $update = 0, $load = false) { info( t('Collection is empty')); } - $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 and item_restrict = 0 ) "; + $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); @@ -221,7 +221,7 @@ function network_content(&$a, $update = 0, $load = false) { intval(local_channel()) ); if($r) { - $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND uid = " . intval(local_channel()) . " AND ( author_xchan = '" . dbesc($r[0]['abook_xchan']) . "' or owner_xchan = '" . dbesc($r[0]['abook_xchan']) . "' ) and item_restrict = 0 ) "; + $sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND uid = " . intval(local_channel()) . " AND ( author_xchan = '" . dbesc($r[0]['abook_xchan']) . "' or owner_xchan = '" . dbesc($r[0]['abook_xchan']) . "' ) $item_normal ) "; $title = replace_macros(get_markup_template("section_title.tpl"),array( '$title' => t('Connection: ') . $r[0]['xchan_name'] )); @@ -404,7 +404,7 @@ function network_content(&$a, $update = 0, $load = false) { $items = q("SELECT item.*, item.id AS item_id, received FROM item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) - WHERE true $uids AND item_restrict = 0 + WHERE true $uids $item_normal and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $simple_update $sql_extra $sql_nets @@ -433,7 +433,7 @@ function network_content(&$a, $update = 0, $load = false) { $r = q("SELECT distinct item.id AS item_id, $ordering FROM item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) - WHERE true $uids AND item.item_restrict = 0 + WHERE true $uids $item_normal AND item.parent = item.id and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets @@ -446,7 +446,7 @@ function network_content(&$a, $update = 0, $load = false) { // this is an update $r = q("SELECT item.parent AS item_id FROM item left join abook on ( item.owner_xchan = abook.abook_xchan $abook_uids ) - WHERE true $uids AND item.item_restrict = 0 $simple_update + WHERE true $uids $item_normal $simple_update and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets ", intval(ABOOK_FLAG_BLOCKED) @@ -463,7 +463,7 @@ function network_content(&$a, $update = 0, $load = false) { $parents_str = ids_to_querystr($r,'item_id'); $items = q("SELECT item.*, item.id AS item_id FROM item - WHERE true $uids AND item.item_restrict = 0 + WHERE true $uids $item_normal AND item.parent IN ( %s ) $sql_extra ", dbesc($parents_str) diff --git a/mod/photos.php b/mod/photos.php index 3338e22ab..52fbe5e4c 100644 --- a/mod/photos.php +++ b/mod/photos.php @@ -135,14 +135,13 @@ function photos_post(&$a) { goaway($a->get_baseurl() . '/' . $_SESSION['photo_return']); } - $r = q("select id, item_restrict from item where resource_id in ( $str ) and resource_type = 'photo' and uid = %d", + $r = q("select id from item where resource_id in ( $str ) and resource_type = 'photo' and uid = %d " . item_normal(), intval($page_owner_uid) ); if($r) { foreach($r as $i) { drop_item($i['id'],false,DROPITEM_PHASE1,true /* force removal of linked items */); - if(! $item_restrict) - proc_run('php','include/notifier.php','drop',$i['id']); + proc_run('php','include/notifier.php','drop',$i['id']); } } diff --git a/mod/ping.php b/mod/ping.php index d43694358..f2ec93f32 100644 --- a/mod/ping.php +++ b/mod/ping.php @@ -43,6 +43,8 @@ function ping_init(&$a) { $vnotify = false; + $item_normal = item_normal(); + if(local_channel()) { $vnotify = get_pconfig(local_channel(),'system','vnotify'); $evdays = intval(get_pconfig(local_channel(),'system','evdays')); @@ -272,7 +274,7 @@ function ping_init(&$a) { $result = array(); $r = q("SELECT * FROM item - WHERE item_restrict = 0 and item_unseen = 1 and uid = %d + WHERE item_unseen = 1 and uid = %d $item_normal and author_xchan != '%s' ORDER BY created DESC limit 300", intval(local_channel()), dbesc($ob_hash) @@ -376,8 +378,9 @@ function ping_init(&$a) { $t1 = dba_timer(); if($vnotify & (VNOTIFY_NETWORK|VNOTIFY_CHANNEL)) { - $r = q("SELECT id, item_restrict, item_flags FROM item - WHERE (item_restrict = 0) and item_unseen = 1 and uid = %d + $r = q("SELECT id, item_flags FROM item + WHERE item_unseen = 1 and uid = %d + $item_normal and author_xchan != '%s'", intval(local_channel()), dbesc($ob_hash) diff --git a/mod/public.php b/mod/public.php index 1f23a1999..3868f6d6c 100644 --- a/mod/public.php +++ b/mod/public.php @@ -10,6 +10,8 @@ function public_content(&$a, $update = 0, $load = false) { if(get_config('system','disable_discover_tab')) return; + $item_normal = item_normal(); + if(! $update) { $maxheight = get_config('system','home_divmore_height'); @@ -95,7 +97,7 @@ function public_content(&$a, $update = 0, $load = false) { $r = q("SELECT distinct item.id AS item_id, $ordering FROM item left join abook on item.author_xchan = abook.abook_xchan - WHERE true $uids AND item.item_restrict = 0 + WHERE true $uids $item_normal AND item.parent = item.id and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets @@ -109,7 +111,7 @@ function public_content(&$a, $update = 0, $load = false) { $r = q("SELECT distinct item.id AS item_id, $ordering FROM item left join abook on item.author_xchan = abook.abook_xchan - WHERE true $uids AND item.item_restrict = 0 + WHERE true $uids $item_normal AND item.parent = item.id $simple_update and ((abook.abook_flags & %d) = 0 or abook.abook_flags is null) $sql_extra3 $sql_extra $sql_nets", @@ -126,7 +128,7 @@ function public_content(&$a, $update = 0, $load = false) { $parents_str = ids_to_querystr($r,'item_id'); $items = q("SELECT item.*, item.id AS item_id FROM item - WHERE true $uids AND item.item_restrict = 0 + WHERE true $uids $item_normal AND item.parent IN ( %s ) $sql_extra ", dbesc($parents_str) diff --git a/mod/search.php b/mod/search.php index 426642141..0363a225f 100644 --- a/mod/search.php +++ b/mod/search.php @@ -125,6 +125,7 @@ function search_content(&$a,$update = 0, $load = false) { } + $item_normal = item_normal(); $pub_sql = public_permissions_sql($observer_hash); require_once('include/identity.php'); @@ -143,7 +144,7 @@ function search_content(&$a,$update = 0, $load = false) { if($load) { $r = null; - + if(ACTIVE_DBTYPE == DBTYPE_POSTGRES) { $prefix = 'distinct on (created, mid)'; $suffix = 'ORDER BY created DESC, mid'; @@ -153,23 +154,22 @@ function search_content(&$a,$update = 0, $load = false) { } if(local_channel()) { $r = q("SELECT $prefix mid, item.id as item_id, item.* from item - WHERE item_restrict = 0 AND ( item_flags & %d ) = 0 - AND ((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND item_private = 0 ) + WHERE ((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND item_private = 0 ) OR ( `item`.`uid` = %d )) OR item.owner_xchan = '%s' ) + $item_normal $sql_extra $suffix $pager_sql ", - intval(ITEM_OBSCURED), intval(local_channel()), dbesc($sys['xchan_hash']) ); } if($r === null) { $r = q("SELECT $prefix mid, item.id as item_id, item.* from item - WHERE item_restrict = 0 AND ( item_flags & %d ) = 0 - AND (((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' + WHERE (((( `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND item_private = 0 ) and owner_xchan in ( " . stream_perms_xchans(($observer) ? (PERMS_NETWORK|PERMS_PUBLIC) : PERMS_PUBLIC) . " )) $pub_sql ) OR owner_xchan = '%s') + $item_normal $sql_extra $suffix $pager_sql", intval(ITEM_OBSCURED), diff --git a/mod/viewsrc.php b/mod/viewsrc.php index 931175a44..cffb01b6e 100644 --- a/mod/viewsrc.php +++ b/mod/viewsrc.php @@ -20,9 +20,10 @@ function viewsrc_content(&$a) { notice( t('Item not found.') . EOL); } + $item_normal = item_normal(); if(local_channel() && $item_id) { - $r = q("select id, item_flags, item_obscured, body from item where item_restrict = 0 and uid in (%d , %d) and id = %d limit 1", + $r = q("select id, item_flags, item_obscured, body from item where uid in (%d , %d) and id = %d $item_normal limit 1", intval(local_channel()), intval($sys['channel_id']), intval($item_id) diff --git a/mod/webpages.php b/mod/webpages.php index 1795b52a0..8e12b5910 100644 --- a/mod/webpages.php +++ b/mod/webpages.php @@ -133,9 +133,9 @@ function webpages_content(&$a) { $sql_extra = item_permissions_sql($owner); $r = q("select * from item_id left join item on item_id.iid = item.id - where item_id.uid = %d and service = 'WEBPAGE' and item_restrict = %d $sql_extra order by item.created desc", + where item_id.uid = %d and service = 'WEBPAGE' and item_type = %d $sql_extra order by item.created desc", intval($owner), - intval(ITEM_WEBPAGE) + intval(ITEM_TYPE_WEBPAGE) ); $pages = null; -- cgit v1.2.3