more work on item table optimisation

13 files changed, 55 insertions, 45 deletions

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;