start fixing all the item queries

5 files changed, 45 insertions, 50 deletions

diff --git a/include/items.php b/include/items.php
index cd6ab943d..d755b7050 100755
--- a/include/items.php
+++ b/include/items.php
@@ -974,7 +974,7 @@ function item_store($arr,$force_parent = false) {
 	$arr['allow_gid']     = ((x($arr,'allow_gid'))     ? trim($arr['allow_gid'])             : '');
 	$arr['deny_cid']      = ((x($arr,'deny_cid'))      ? trim($arr['deny_cid'])              : '');
 	$arr['deny_gid']      = ((x($arr,'deny_gid'))      ? trim($arr['deny_gid'])              : '');
-	$arr['private']       = ((x($arr,'private'))       ? intval($arr['private'])             : 0 );
+	$arr['item_private']  = ((x($arr,'item_private'))  ? intval($arr['item_private'])        : 0 );
 	$arr['body']          = ((x($arr,'body'))          ? trim($arr['body'])                  : '');
 	$arr['attach']        = ((x($arr,'attach'))        ? notags(trim($arr['attach']))        : '');
 	$arr['app']           = ((x($arr,'app'))           ? notags(trim($arr['app']))           : '');
@@ -1034,15 +1034,15 @@ function item_store($arr,$force_parent = false) {
 			// This differs from the above settings as it subtly allows comments from 
 			// email correspondents to be private even if the overall thread is not. 
 
-			if($r[0]['private'])
-				$arr['private'] = $r[0]['private'];
+			if($r[0]['item_private'])
+				$arr['item_private'] = $r[0]['item_private'];
 
 			// Edge case. We host a public forum that was originally posted to privately.
 			// The original author commented, but as this is a comment, the permissions
 			// weren't fixed up so it will still show the comment as private unless we fix it here. 
 
-			if((intval($r[0]['item_flags']) & ITEM_UPLINK) && (! $r[0]['private']))
-				$arr['private'] = 0;
+			if((intval($r[0]['item_flags']) & ITEM_UPLINK) && (! $r[0]['item_private']))
+				$arr['item_private'] = 0;
 		}
 		else {
 
@@ -1137,7 +1137,7 @@ function item_store($arr,$force_parent = false) {
 	// Set parent id - and also make sure to inherit the parent's ACL's.
 
 	$r = q("UPDATE `item` SET `parent` = %d, `allow_cid` = '%s', `allow_gid` = '%s',
-		`deny_cid` = '%s', `deny_gid` = '%s', `private` = %d WHERE `id` = %d LIMIT 1",
+		`deny_cid` = '%s', `deny_gid` = '%s', `item_private` = %d WHERE `id` = %d LIMIT 1",
 		intval($parent_id),
 		dbesc($allow_cid),
 		dbesc($allow_gid),
@@ -3748,12 +3748,15 @@ function drop_item($id,$interactive = true) {
 
 
 function first_post_date($uid,$wall = false) {
-	$r = q("select id, created from item 
-		where uid = %d and wall = %d and deleted = 0 and visible = 1 AND moderated = 0 
-		and id = parent
+
+	$wall_sql = (($wall) ? sprintf(" and item_flags & %d ", ITEM_WALL) : "" );
+
+	$r = q("select id, created from item
+		where item_restrict = %d and uid = %d and id = parent $wall_sql
 		order by created asc limit 1",
-		intval($uid),
-		intval($wall ? 1 : 0)
+		intval(ITEM_VISIBLE),
+		intval($uid)
+
 	);
 	if(count($r)) {
 //		logger('first_post_date: ' . $r[0]['id'] . ' ' . $r[0]['created'], LOGGER_DATA);
@@ -3818,7 +3821,7 @@ function posted_date_widget($url,$uid,$wall) {
 function fetch_post_tags($items) {
 
 	$tag_finder = array();
-	if(count($items))		
+	if($items && count($items))		
 		foreach($items as $item)
 			if(! in_array($item['item_id'],$tag_finder))
 				$tag_finder[] = $item['item_id'];
diff --git a/mod/item.php b/mod/item.php
index 978ce6712..ae011d3c6 100644
--- a/mod/item.php
+++ b/mod/item.php
@@ -227,7 +227,7 @@ function item_post(&$a) {
 		$app			   = $orig_post['app'];
 		$title             = escape_tags(trim($_REQUEST['title']));
 		$body              = escape_tags(trim($_REQUEST['body']));
-		$private           = $orig_post['private'];
+		$private           = $orig_post['item_private'];
 
 	}
 	else {
@@ -273,12 +273,12 @@ function item_post(&$a) {
 		if($parent_item) {
 			$private = 0;
 
-			if(($parent_item['private']) 
+			if(($parent_item['item_private']) 
 				|| strlen($parent_item['allow_cid']) 
 				|| strlen($parent_item['allow_gid']) 
 				|| strlen($parent_item['deny_cid']) 
 				|| strlen($parent_item['deny_gid'])) {
-				$private = (($parent_item['private']) ? $parent_item['private'] : 1);
+				$private = (($parent_item['item_private']) ? $parent_item['item_private'] : 1);
 			}
 
 			$str_contact_allow = $parent_item['allow_cid'];
@@ -597,7 +597,7 @@ function item_post(&$a) {
 	$datarray['allow_gid']     = $str_group_allow;
 	$datarray['deny_cid']      = $str_contact_deny;
 	$datarray['deny_gid']      = $str_group_deny;
-	$datarray['private']       = $private;
+	$datarray['item_private']  = $private;
 	$datarray['attach']        = $attachments;
 	$datarray['thr_parent']    = $thr_parent;
 	$datarray['postopts']      = '';
@@ -707,7 +707,7 @@ function item_post(&$a) {
 
 			// Inherit ACL's from the parent item.
 
-			$r = q("UPDATE `item` SET `allow_cid` = '%s', `allow_gid` = '%s', `deny_cid` = '%s', `deny_gid` = '%s', `private` = %d
+			$r = q("UPDATE `item` SET `allow_cid` = '%s', `allow_gid` = '%s', `deny_cid` = '%s', `deny_gid` = '%s', `item_private` = %d
 				WHERE `id` = %d LIMIT 1",
 				dbesc($parent_item['allow_cid']),
 				dbesc($parent_item['allow_gid']),
diff --git a/mod/network.php b/mod/network.php
index 18d5b8e11..3f06ccb3d 100644
--- a/mod/network.php
+++ b/mod/network.php
@@ -414,8 +414,8 @@ function network_content(&$a, $update = 0, $load = false) {
 	$sql_options  = (($star) ? " and starred = 1 " : '');
 
 	$sql_nets = '';
-
-	$sql_extra = " AND `item`.`parent` IN ( SELECT `parent` FROM `item` WHERE `id` = `parent` $sql_options ) ";
+// fixme
+	$sql_extra = " AND `item`.`parent` IN ( SELECT `parent` FROM `item` WHERE id = parent $sql_options ) ";
 
 	if($group) {
 		$r = q("SELECT `name`, `id` FROM `group` WHERE `id` = %d AND `uid` = %d LIMIT 1",
@@ -604,15 +604,12 @@ function network_content(&$a, $update = 0, $load = false) {
 		// "New Item View" - show all items unthreaded in reverse created date order
 
 		$items = q("SELECT `item`.*, `item`.`id` AS `item_id`, 
-			`contact`.`name`, `contact`.`photo`, `contact`.`url`, `contact`.`rel`, `contact`.`writable`,
-			`contact`.`network`, `contact`.`thumb`, `contact`.`dfrn_id`, `contact`.`self`,
-			`contact`.`id` AS `cid`, `contact`.`uid` AS `contact-uid`
-			FROM `item`, `contact`
-			WHERE `item`.`uid` = %d AND `item`.`visible` = 1 
-			AND `item`.`deleted` = 0 and `item`.`moderated` = 0
+			o.xchan_name as oname, o.xchan_photo as ophoto, o.xchan_profile as ourl, 
+			a.xchan_name as aname, a.xchan_photo as aphoto, a.xchan_profile as aurl,
+			FROM `item` left join xchan as o on xchan_hash = owner_xchan left join xchan as a
+			on xchan_hash = author_xchan
+			WHERE `item`.`uid` = %d AND item_restrict = 0 
 			$simple_update
-			AND `contact`.`id` = `item`.`contact-id`
-			AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0
 			$sql_extra $sql_nets
 			ORDER BY `item`.`received` DESC $pager_sql ",
 			intval($_SESSION['uid'])
@@ -647,10 +644,8 @@ function network_content(&$a, $update = 0, $load = false) {
 		}
 		else {
 
-			$r = q("SELECT `item`.`id` AS `item_id`, `contact`.`uid` AS `contact_uid`
-				FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id`
-				WHERE `item`.`uid` = %d AND `item`.`visible` = 1 AND `item`.`deleted` = 0
-				AND `item`.`moderated` = 0 AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0
+			$r = q("SELECT `item`.`id` AS `item_id`
+				FROM `item` WHERE `item`.`uid` = %d AND `item`.`item_restrict` = 0
 				AND `item`.`parent` = `item`.`id`
 				$sql_extra3 $sql_extra $sql_nets
 				ORDER BY `item`.$ordering DESC $pager_sql ",
@@ -661,18 +656,16 @@ function network_content(&$a, $update = 0, $load = false) {
 
 		// Then fetch all the children of the parents that are on this page
 
-		if(count($r)) {
+		if($r && count($r)) {
 
 			$parents_str = ids_to_querystr($r,'item_id');
 
 			$items = q("SELECT `item`.*, `item`.`id` AS `item_id`,
-				`contact`.`name`, `contact`.`photo`, `contact`.`url`, `contact`.`alias`, `contact`.`rel`, `contact`.`writable`,
-				`contact`.`network`, `contact`.`thumb`, `contact`.`dfrn_id`, `contact`.`self`,
-				`contact`.`id` AS `cid`, `contact`.`uid` AS `contact-uid`
-				FROM `item`, `contact`
-				WHERE `item`.`uid` = %d AND `item`.`visible` = 1 AND `item`.`deleted` = 0
-				AND `item`.`moderated` = 0 AND `contact`.`id` = `item`.`contact-id`
-				AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0
+			o.xchan_name as oname, o.xchan_photo as ophoto, o.xchan_profile as ourl, 
+			a.xchan_name as aname, a.xchan_photo as aphoto, a.xchan_profile as aurl,
+			FROM `item` left join xchan as o on xchan_hash = owner_xchan left join xchan as a
+			on xchan_hash = author_xchan
+				WHERE `item`.`uid` = %d AND `item`.`item_restrict` = 0
 				AND `item`.`parent` IN ( %s )
 				$sql_extra ",
 				intval(local_user()),
@@ -696,8 +689,10 @@ function network_content(&$a, $update = 0, $load = false) {
 	// at the top level network page just mark everything seen. 
 	
 	if((! $group) && (! $cid) && (! $star)) {
-		$r = q("UPDATE `item` SET `unseen` = 0 
-			WHERE `unseen` = 1 AND `uid` = %d",
+		$r = q("UPDATE `item` SET item_flags = item_flags & (! %d)
+			WHERE item_flags & %d AND `uid` = %d",
+			intval(ITEM_UNSEEN),
+			intval(ITEM_UNSEEN),
 			intval(local_user())
 		);
 	}
diff --git a/mod/ping.php b/mod/ping.php
index c9c9b5ecf..ff8d977dc 100644
--- a/mod/ping.php
+++ b/mod/ping.php
@@ -99,13 +99,10 @@ function ping_init(&$a) {
 
 	$t1 = dba_timer();
 
-	$r = q("SELECT `item`.`id`,`item`.`parent`, `item`.`verb`, `item`.`wall`, `item`.`author-name`, 
-		`item`.`contact-id`, `item`.`author-link`, `item`.`author-avatar`, `item`.`created`, `item`.`object`, 
-		`pitem`.`author-name` as `pname`, `pitem`.`author-link` as `plink` 
-		FROM `item` INNER JOIN `item` as `pitem` ON  `pitem`.`id`=`item`.`parent`
-		WHERE `item`.`unseen` = 1 AND `item`.`visible` = 1 AND
-		 `item`.`deleted` = 0 AND `item`.`uid` = %d 
-		ORDER BY `item`.`created` DESC",
+	$r = q("SELECT id, item_restrict, item_flags FROM item
+		WHERE item_restrict = %d and item_flags & %d and `item`.`uid` = %d",
+		intval(ITEM_VISIBLE),
+		intval(ITEM_UNSEEN),
 		intval(local_user())
 	);
 
@@ -115,7 +112,7 @@ function ping_init(&$a) {
 		call_hooks('network_ping', $arr);
 	
 		foreach ($r as $it) {
-			if($it['wall'])
+			if($it['item_flags'] & ITEM_WALL)
 				$result['home'] ++;
 			else
 				$result['network'] ++;
diff --git a/mod/profile.php b/mod/profile.php
index 68dc6db72..2724771a7 100644
--- a/mod/profile.php
+++ b/mod/profile.php
@@ -221,7 +221,7 @@ function profile_content(&$a, $update = 0) {
 
 	}
 
-	if(count($r)) {
+	if($r && count($r)) {
 
 		$parents_str = ids_to_querystr($r,'item_id');