more work on item table optimisation

4 files changed, 59 insertions, 37 deletions

diff --git a/include/api.php b/include/api.php
index e4c4b5240..a75aa2c32 100644
--- a/include/api.php
+++ b/include/api.php
@@ -344,10 +344,12 @@ require_once('include/items.php');
 				intval(api_user())
 			);
 
+			$item_normal = item_normal();
+
 			// count public wall messages
 			$r = q("SELECT COUNT(`id`) as `count` FROM `item`
 					WHERE `uid` = %d
-					AND item_wall = 1 and item_restrict = 0 
+					AND item_wall = 1 $item_normal 
 					AND `allow_cid`='' AND `allow_gid`='' AND `deny_cid`='' AND `deny_gid`=''",
 					intval($usr[0]['channel_id'])
 			);
@@ -373,7 +375,7 @@ require_once('include/items.php');
 			$countfollowers = $r[0]['count'];
 		}
 
-		$r = q("SELECT count(`id`) as `count` FROM item where item_starred = 1 and uid = %d and item_restrict = 0",
+		$r = q("SELECT count(`id`) as `count` FROM item where item_starred = 1 and uid = %d " . item_normal(),
 			intval($uinfo[0]['channel_id'])
 		);
 		$starred = $r[0]['count'];
@@ -856,8 +858,10 @@ require_once('include/items.php');
 	function api_get_status($xchan_hash) {
 		require_once('include/security.php');
 
+		$item_normal = item_normal();
+
 		$lastwall = q("SELECT * from item where
-			item_private = 0 and item_restrict = 0
+			item_private = 0 $item_normal
 			and author_xchan = '%s'
 			and allow_cid = '' and allow_gid = '' and deny_cid = '' and deny_gid = ''
 			and verb = '%s'
@@ -919,9 +923,10 @@ require_once('include/items.php');
 		// get last public message
 
 		require_once('include/security.php');
+		$item_normal = item_normal();
 
 		$lastwall = q("SELECT * from item where
-			item_private = 0 and item_restrict = 0
+			item_private = 0 $item_normal
 			and author_xchan = '%s'
 			and allow_cid = '' and allow_gid = '' and deny_cid = '' and deny_gid = ''
 			and verb = '%s'
@@ -992,9 +997,10 @@ require_once('include/items.php');
 		$user_info = api_get_user($a);
 
 		require_once('include/security.php');
+		$item_normal = item_normal();
 
 		$lastwall = q("SELECT * from item where 1
-			and item_private != 0 and item_restrict = 0
+			and item_private != 0 $item_normal
 			and author_xchan = '%s'
 			and allow_cid = '' and allow_gid = '' and deny_cid = '' and deny_gid = ''
 			and verb = '%s'
@@ -1095,7 +1101,9 @@ require_once('include/items.php');
 			$sql_extra .= " and item_private = 0 ";
 		}
 
-		$r = q("SELECT * from item WHERE uid = %d and item_restrict = 0
+		$item_normal = item_normal();
+
+		$r = q("SELECT * from item WHERE uid = %d $item_normal
 			$sql_extra
 			AND id > %d
 			ORDER BY received DESC LIMIT %d ,%d ",
@@ -1161,11 +1169,12 @@ require_once('include/items.php');
 		if ($max_id > 0)
 			$sql_extra = 'AND `item`.`id` <= '.intval($max_id);
 		require_once('include/security.php');
+		$item_normal = item_normal();
 
-        $r = q("select * from item where item_restrict = 0
-			and allow_cid = ''  and allow_gid = ''
+        $r = q("select * from item where allow_cid = ''  and allow_gid = ''
 			and deny_cid  = ''  and deny_gid  = ''
             and item_private = 0
+			$item_normal
 			and uid = " . $sys['channel_id'] . "
 			$sql_extra
 			AND id > %d group by mid
@@ -1221,7 +1230,8 @@ require_once('include/items.php');
 		else
 			$sql_extra .= " AND `item`.`id` = %d";
 
-		$r = q("select * from item where item_restrict = 0 $sql_extra",
+		$item_normal = item_normal();
+		$r = q("select * from item where true $item_normal $sql_extra",
 			intval($id)
 		);
 		xchan_query($r,true);
@@ -1261,7 +1271,9 @@ require_once('include/items.php');
 
 		$observer = get_app()->get_observer();
 
-		$r = q("SELECT * from item where item_restrict = 0 and id = %d limit 1",
+		$item_normal = item_normal();
+
+		$r = q("SELECT * from item where and id = %d $item_normal limit 1",
 			intval($id)
 		);
 
@@ -1624,12 +1636,13 @@ require_once('include/items.php');
 			$sql_extra .= " and item_private = 0 ";
 		}
 
-		$r = q("SELECT * from item WHERE uid = %d and item_restrict = 0
-			and ( item_flags & %d ) > 0 $sql_extra
+		$item_normal = item_normal();
+
+		$r = q("SELECT * from item WHERE uid = %d $item_normal
+			and item_starred = 1 $sql_extra
 			AND id > %d
 			ORDER BY received DESC LIMIT %d ,%d ",
 			intval($user_info['uid']),
-			intval(ITEM_STARRED),
 			intval($since_id),
 			intval($start),	
 			intval($count)
diff --git a/include/expire.php b/include/expire.php
index 8ba9f746b..1af13163d 100644
--- a/include/expire.php
+++ b/include/expire.php
@@ -12,7 +12,7 @@ function expire_run($argv, $argc){
 
 	// perform final cleanup on previously delete items
 
-	$r = q("select id from item where item_deleted = 1 and not (item_restrict & %d)>0 and changed < %s - INTERVAL %s",
+	$r = q("select id from item where item_deleted = 1 and item_pending_remove = 0 and changed < %s - INTERVAL %s",
 		intval(ITEM_PENDING_REMOVE),
 		db_utcnow(), db_quoteinterval('10 DAY')
 	);
@@ -25,8 +25,7 @@ function expire_run($argv, $argc){
 	// physically remove anything that has been deleted for more than two months
 	/** @FIXME - this is a wretchedly inefficient query */
 
-	$r = q("delete from item where ( item_restrict & %d ) > 0 and changed < %s - INTERVAL %s",
-		intval(ITEM_PENDING_REMOVE),
+	$r = q("delete from item where item_pending_remove = 1 and changed < %s - INTERVAL %s",
 		db_utcnow(), db_quoteinterval('36 DAY')
 	);
 
diff --git a/include/items.php b/include/items.php
index 2b00b52f8..c7dbbbda8 100755
--- a/include/items.php
+++ b/include/items.php
@@ -192,6 +192,13 @@ function comments_are_now_closed($item) {
 	return false;
 }
 
+function item_normal() {
+	return " and item.item_hidden = 0 and item.item_type = 0 and item.item_deleted = 0 
+		and item.item_unpublished = 0 and item.item_delayed = 0 and item.item_pending_remove = 0 
+		and item.item_blocked = 0 ";
+}
+
+
 
 /**
  * @brief
@@ -1396,7 +1403,7 @@ function encode_item_flags($item) {
 		$ret[] = 'nsfw';
 	if(intval($item['item_consensus']))
 		$ret[] = 'consensus';
-	if($item['item_private'])
+	if(intval($item['item_private']))
 		$ret[] = 'private';
 
 	return $ret;
@@ -2117,7 +2124,7 @@ function item_store($arr, $allow_exec = false) {
 	$d1 = new DateTime('now +10 minutes', new DateTimeZone('UTC'));
 	$d2 = new DateTime($arr['created'] . '+00:00');
 	if($d2 > $d1)
-		$arr['item_restrict'] = $arr['item_restrict'] | ITEM_DELAYED_PUBLISH;
+		$arr['item_delayed'] = 1;
 
 	$arr['llink'] = z_root() . '/display/' . $arr['mid'];
 
@@ -2334,10 +2341,9 @@ function item_store($arr, $allow_exec = false) {
 
 	// update the commented timestamp on the parent
 
-	$z = q("select max(created) as commented from item where parent_mid = '%s' and uid = %d and not ( item_restrict & %d )>0 ",
+	$z = q("select max(created) as commented from item where parent_mid = '%s' and uid = %d and item_delayed = 0 ",
 		dbesc($arr['parent_mid']),
-		intval($arr['uid']),
-		intval(ITEM_DELAYED_PUBLISH)
+		intval($arr['uid'])
 	);
 
 	q("UPDATE item set commented = '%s', changed = '%s' WHERE id = %d",
@@ -2352,7 +2358,7 @@ function item_store($arr, $allow_exec = false) {
 	// so that we have an item in the DB that's marked deleted and won't store a fresh post
 	// that isn't aware that we were already told to delete it.
 
-	if(! ($arr['item_restrict'] & ITEM_DELETED)) {
+	if(! intval($arr['item_deleted'])) {
 		send_status_notifications($current_post,$arr);
 		tag_deliver($arr['uid'],$current_post);
 	}
@@ -2766,7 +2772,7 @@ function tag_deliver($uid, $item_id) {
 			if($obj['id'] !== $u[0]['channel_hash'])
 				$poke_notify = false;
 		}
-		if($item['item_restrict'] & ITEM_DELETED)
+		if(intval($item['item_deleted']))
 			$poke_notify = false;
 
 		$verb = urldecode(substr($item['verb'],strpos($item['verb'],'#')+1));
@@ -3913,13 +3919,15 @@ function item_expire($uid,$days) {
 	if(! intval($expire_limit))
 		$expire_limit = 5000;
 
+	$item_normal = item_normal();
+
 	$r = q("SELECT * FROM `item`
 		WHERE `uid` = %d
 		AND `created` < %s - INTERVAL %s
 		AND `id` = `parent`
 		$sql_extra
 		AND item_retained = 0
-		AND (item_restrict = 0 ) LIMIT $expire_limit ",
+		$item_normal LIMIT $expire_limit ",
 		intval($uid),
 		db_utcnow(), db_quoteinterval(intval($days).' DAY')
 	);
@@ -4108,9 +4116,8 @@ function delete_item_lowlevel($item, $stage = DROPITEM_NORMAL, $force = false) {
 
 	switch($stage) {
 		case DROPITEM_PHASE2:
-			$r = q("UPDATE item SET item_restrict = ( item_restrict | %d ), body = '', title = '',
+			$r = q("UPDATE item SET item_pending_remove = 1, body = '', title = '',
 				changed = '%s', edited = '%s'  WHERE id = %d",
-				intval(ITEM_PENDING_REMOVE),
 				dbesc(datetime_convert()),
 				dbesc(datetime_convert()),
 				intval($item['id'])
@@ -4194,9 +4201,10 @@ function delete_item_lowlevel($item, $stage = DROPITEM_NORMAL, $force = false) {
 function first_post_date($uid,$wall = false) {
 
 	$wall_sql = (($wall) ? " and item_wall = 1 " : "" );
+	$item_normal = item_normal();
 
 	$r = q("select id, created from item
-		where item_restrict = %d and uid = %d and id = parent $wall_sql
+		where uid = %d and id = parent $item_normal $wall_sql
 		order by created asc limit 1",
 		intval(ITEM_VISIBLE),
 		intval($uid)
@@ -4397,10 +4405,12 @@ function zot_feed($uid,$observer_hash,$arr) {
 		return array();
 	}
 
+	$item_normal = item_normal();
+
 	if(is_sys_channel($uid)) {
 		$r = q("SELECT parent, created, postopts from item
 			WHERE uid != %d
-			and uid in (" . stream_perms_api_uids(PERMS_PUBLIC,10,1) . ") AND item_restrict = 0 
+			and uid in (" . stream_perms_api_uids(PERMS_PUBLIC,10,1) . ") $item_normal
 			AND item_wall = 1
 			and item_private = 0 $sql_extra GROUP BY parent ORDER BY created ASC $limit",
 			intval($uid)
@@ -4408,7 +4418,7 @@ function zot_feed($uid,$observer_hash,$arr) {
 	}
 	else {
 		$r = q("SELECT parent, created, postopts from item
-			WHERE uid = %d AND item_restrict = 0
+			WHERE uid = %d $item_normal
 			AND item_wall = 1
 			$sql_extra GROUP BY parent ORDER BY created ASC $limit",
 			intval($uid)
@@ -4424,10 +4434,10 @@ function zot_feed($uid,$observer_hash,$arr) {
 	
 		$parents_str = ids_to_querystr($r,'parent');
 		$sys_query = ((is_sys_channel($uid)) ? $sql_extra : '');
+		$item_normal = item_normal();
 
 		$items = q("SELECT `item`.*, `item`.`id` AS `item_id` FROM `item`
-			WHERE `item`.`item_restrict` = 0
-			AND `item`.`parent` IN ( %s ) $sys_query ",
+			WHERE `item`.`parent` IN ( %s ) $item_normal $sys_query ",
 			dbesc($parents_str)
 		);
 	}
@@ -4465,6 +4475,8 @@ function items_fetch($arr,$channel = null,$observer_hash = null,$client_mode = C
 	$def_acl = '';
 
 	$item_uids = ' true ';
+	$item_normal = item_normal();
+
 
 	if ($arr['uid']) $uid= $arr['uid'];
 
@@ -4511,7 +4523,7 @@ function items_fetch($arr,$channel = null,$observer_hash = null,$client_mode = C
 			return $result;
 		}
 
-		$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($r[0]['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($r[0]['hash']) . '>%') . "' ) and id = parent $item_normal ) ";
 
 		$x = group_rec_byhash($uid,$r[0]['hash']);
 		$result['headline'] = sprintf( t('Collection: %s'),$x['name']);
@@ -4523,7 +4535,7 @@ function items_fetch($arr,$channel = null,$observer_hash = null,$client_mode = C
 			intval(local_channel())
 		);
 		if ($r) {
-			$sql_extra = " AND item.parent IN ( SELECT DISTINCT parent FROM item WHERE true $sql_options AND uid = " . intval($arr['uid']) . " 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($arr['uid']) . " AND ( author_xchan = '" . dbesc($r[0]['abook_xchan']) . "' or owner_xchan = '" . dbesc($r[0]['abook_xchan']) . "' ) $item_normal ) ";
 			$result['headline'] = sprintf( t('Connection: %s'),$r[0]['xchan_name']);
 		} else {
 			$result['message'] = t('Connection not found.');
diff --git a/include/poller.php b/include/poller.php
index a175d59b5..09c642f6a 100644
--- a/include/poller.php
+++ b/include/poller.php
@@ -90,15 +90,13 @@ function poller_run($argv, $argc){
 	// (time travel posts). Restrict to items that have come of age in the last
 	// couple of days to limit the query to something reasonable. 
 
-	$r = q("select id from item where ( item_restrict & %d ) > 0 and created <= %s  and created > '%s' ",
-		intval(ITEM_DELAYED_PUBLISH),
+	$r = q("select id from item where item_delayed = 1 and created <= %s  and created > '%s' ",
 		db_utcnow(),
 		dbesc(datetime_convert('UTC','UTC','now - 2 days'))
 	);
 	if($r) {
 		foreach($r as $rr) {
-			$x = q("update item set item_restrict = ( item_restrict & ~%d ) where id = %d",
-				intval(ITEM_DELAYED_PUBLISH),
+			$x = q("update item set item_delayed = 0 where id = %d",
 				intval($rr['id'])
 			);
 			if($x) {