From 84d12c3e08ae4b6fe228ce881b0c1570175d1667 Mon Sep 17 00:00:00 2001 From: Friendika Date: Tue, 5 Jul 2011 23:23:43 -0700 Subject: move hidewall to user table - queries are getting too complicated and servers falling over --- boot.php | 2 +- database.sql | 2 +- mod/community.php | 13 ++++++------- mod/dfrn_poll.php | 2 +- mod/msearch.php | 4 ++-- mod/photos.php | 6 +----- mod/search.php | 13 ++++++------- mod/settings.php | 9 ++++----- update.php | 13 ++++++++++++- 9 files changed, 34 insertions(+), 30 deletions(-) diff --git a/boot.php b/boot.php index 8d2d31307..5d3643e06 100644 --- a/boot.php +++ b/boot.php @@ -6,7 +6,7 @@ ini_set('pcre.backtrack_limit', 250000); define ( 'FRIENDIKA_VERSION', '2.2.1032' ); define ( 'DFRN_PROTOCOL_VERSION', '2.21' ); -define ( 'DB_UPDATE_VERSION', 1074 ); +define ( 'DB_UPDATE_VERSION', 1075 ); define ( 'EOL', "
\r\n" ); define ( 'ATOM_TIME', 'Y-m-d\TH:i:s\Z' ); diff --git a/database.sql b/database.sql index b11e72f6c..8ea3e42de 100644 --- a/database.sql +++ b/database.sql @@ -301,7 +301,6 @@ CREATE TABLE IF NOT EXISTS `profile` ( `profile-name` char(255) NOT NULL, `is-default` tinyint(1) NOT NULL DEFAULT '0', `hide-friends` tinyint(1) NOT NULL DEFAULT '0', - `hidewall` tinyint(1) NOT NULL DEFAULT '0', `name` char(255) NOT NULL, `pdesc` char(255) NOT NULL, `dob` char(32) NOT NULL DEFAULT '0000-00-00', @@ -399,6 +398,7 @@ CREATE TABLE IF NOT EXISTS `user` ( `verified` tinyint(1) unsigned NOT NULL DEFAULT '0', `blocked` tinyint(1) unsigned NOT NULL DEFAULT '0', `blockwall` tinyint(1) unsigned NOT NULL DEFAULT '0', + `hidewall` tinyint(1) unsigned NOT NULL DEFAULT '0', `notify-flags` int(11) unsigned NOT NULL DEFAULT '65535', `page-flags` int(11) unsigned NOT NULL DEFAULT '0', `prvnets` tinyint(1) NOT NULL DEFAULT '0', diff --git a/mod/community.php b/mod/community.php index 90cb7a574..73feaabc6 100644 --- a/mod/community.php +++ b/mod/community.php @@ -42,11 +42,11 @@ function community_content(&$a, $update = 0) { $r = q("SELECT COUNT(*) AS `total` - FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id` LEFT JOIN `profile` ON `profile`.`uid` = `item`.`uid` + FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id` LEFT JOIN `user` ON `user`.`uid` = `item`.`uid` WHERE `item`.`visible` = 1 AND `item`.`deleted` = 0 AND `wall` = 1 AND `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' - AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `profile`.`hidewall` = 0 - AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 AND `profile`.`is-default` = 1 " + AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `user`.`hidewall` = 0 + AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 " ); if(count($r)) @@ -61,14 +61,13 @@ function community_content(&$a, $update = 0) { `contact`.`name`, `contact`.`photo`, `contact`.`url`, `contact`.`rel`, `contact`.`network`, `contact`.`thumb`, `contact`.`self`, `contact`.`writable`, `contact`.`id` AS `cid`, `contact`.`uid` AS `contact-uid`, - `user`.`nickname`, `profile`.`hidewall` + `user`.`nickname`, `user`.`hidewall` FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id` LEFT JOIN `user` ON `user`.`uid` = `item`.`uid` - LEFT JOIN `profile` ON `profile`.`uid` = `item`.`uid` WHERE `item`.`visible` = 1 AND `item`.`deleted` = 0 AND `wall` = 1 AND `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' - AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `profile`.`hidewall` = 0 - AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 AND `profile`.`is-default` = 1 + AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `user`.`hidewall` = 0 + AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 ORDER BY `received` DESC LIMIT %d, %d ", intval($a->pager['start']), intval($a->pager['itemspage']) diff --git a/mod/dfrn_poll.php b/mod/dfrn_poll.php index 7d8568188..76803ef1b 100644 --- a/mod/dfrn_poll.php +++ b/mod/dfrn_poll.php @@ -31,7 +31,7 @@ function dfrn_poll_init(&$a) { killme(); } - $r = q("SELECT `hidewall` FROM `profile` LEFT JOIN `user` ON `profile`.`uid` = `user`.`uid` WHERE `user`.`nickname` = '%s' AND `profile`.`is-default` = 1 LIMIT 1", + $r = q("SELECT `hidewall` FROM `user` WHERE `user`.`nickname` = '%s' LIMIT 1", dbesc($a->argv[1]) ); if(count($r) && $r[0]['hidewall']) diff --git a/mod/msearch.php b/mod/msearch.php index 7d9bbe9e7..94def7d89 100644 --- a/mod/msearch.php +++ b/mod/msearch.php @@ -10,13 +10,13 @@ function msearch_post(&$a) { if(! strlen($search)) killme(); - $r = q("SELECT COUNT(*) AS `total` FROM `profile` WHERE `is-default` = 1 AND `hidewall` = 0 AND MATCH `pub_keywords` AGAINST ('%s') ", + $r = q("SELECT COUNT(*) AS `total` FROM `profile` LEFT JOIN `user` ON `user`.`uid` = `profile`.`uid` WHERE `is-default` = 1 AND `user`.`hidewall` = 0 AND MATCH `pub_keywords` AGAINST ('%s') ", dbesc($search) ); if(count($r)) $total = $r[0]['total']; - $r = q("SELECT `pub_keywords`, `username`, `nickname`, `user`.`uid` FROM `user` LEFT JOIN `profile` ON `user`.`uid` = `profile`.`uid` WHERE `is-default` = 1 AND `hidewall` = 0 AND MATCH `pub_keywords` AGAINST ('%s') LIMIT %d , %d ", + $r = q("SELECT `pub_keywords`, `username`, `nickname`, `user`.`uid` FROM `user` LEFT JOIN `profile` ON `user`.`uid` = `profile`.`uid` WHERE `is-default` = 1 AND `user`.`hidewall` = 0 AND MATCH `pub_keywords` AGAINST ('%s') LIMIT %d , %d ", dbesc($search), intval($startrec), intval($perpage) diff --git a/mod/photos.php b/mod/photos.php index 3e0ec5802..4a72bb680 100644 --- a/mod/photos.php +++ b/mod/photos.php @@ -816,11 +816,7 @@ function photos_content(&$a) { } } - $r = q("SELECT `hidewall` FROM `profile` WHERE `uid` = %d AND `is-default` = 1 LIMIT 1", - intval($owner_uid) - ); - - if(count($r) && $r[0]['hidewall'] && (local_user() != $owner_uid) && (! $remote_contact)) { + if($a->data['user']['hidewall'] && (local_user() != $owner_uid) && (! $remote_contact)) { notice( t('Access to this item is restricted.') . EOL); return; } diff --git a/mod/search.php b/mod/search.php index d6ab1bb60..9b465c0e5 100644 --- a/mod/search.php +++ b/mod/search.php @@ -48,11 +48,11 @@ function search_content(&$a) { $search_alg = $s_regx; $r = q("SELECT COUNT(*) AS `total` - FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id` LEFT JOIN `profile` ON `profile`.`uid` = `item`.`uid` + FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id` LEFT JOIN `user` ON `user`.`uid` = `item`.`uid` WHERE `item`.`visible` = 1 AND `item`.`deleted` = 0 - AND (( `wall` = 1 AND `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `profile`.`hidewall` = 0) + AND (( `wall` = 1 AND `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `user`.`hidewall` = 0) OR `item`.`uid` = %d ) - AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 AND `profile`.`is-default` = 1 + AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 $search_alg ", intval(local_user()), dbesc($search) @@ -70,14 +70,13 @@ function search_content(&$a) { `contact`.`name`, `contact`.`photo`, `contact`.`url`, `contact`.`rel`, `contact`.`network`, `contact`.`thumb`, `contact`.`self`, `contact`.`writable`, `contact`.`id` AS `cid`, `contact`.`uid` AS `contact-uid`, - `user`.`nickname`, `profile`.`hidewall` + `user`.`nickname` FROM `item` LEFT JOIN `contact` ON `contact`.`id` = `item`.`contact-id` LEFT JOIN `user` ON `user`.`uid` = `item`.`uid` - LEFT JOIN `profile` ON `profile`.`uid` = `item`.`uid` WHERE `item`.`visible` = 1 AND `item`.`deleted` = 0 - AND (( `wall` = 1 AND `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `profile`.`hidewall` = 0 ) + AND (( `wall` = 1 AND `item`.`allow_cid` = '' AND `item`.`allow_gid` = '' AND `item`.`deny_cid` = '' AND `item`.`deny_gid` = '' AND `user`.`hidewall` = 0 ) OR `item`.`uid` = %d ) - AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 AND `profile`.`is-default` = 1 + AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 $search_alg ORDER BY `received` DESC LIMIT %d , %d ", intval(local_user()), diff --git a/mod/settings.php b/mod/settings.php index 2e942231a..9efa07d76 100644 --- a/mod/settings.php +++ b/mod/settings.php @@ -230,7 +230,7 @@ function settings_post(&$a) { $openidserver = ''; } - $r = q("UPDATE `user` SET `username` = '%s', `email` = '%s', `openid` = '%s', `timezone` = '%s', `allow_cid` = '%s', `allow_gid` = '%s', `deny_cid` = '%s', `deny_gid` = '%s', `notify-flags` = %d, `page-flags` = %d, `default-location` = '%s', `allow_location` = %d, `theme` = '%s', `maxreq` = %d, `expire` = %d, `openidserver` = '%s', `blockwall` = %d WHERE `uid` = %d LIMIT 1", + $r = q("UPDATE `user` SET `username` = '%s', `email` = '%s', `openid` = '%s', `timezone` = '%s', `allow_cid` = '%s', `allow_gid` = '%s', `deny_cid` = '%s', `deny_gid` = '%s', `notify-flags` = %d, `page-flags` = %d, `default-location` = '%s', `allow_location` = %d, `theme` = '%s', `maxreq` = %d, `expire` = %d, `openidserver` = '%s', `blockwall` = %d, `hidewall` = %d WHERE `uid` = %d LIMIT 1", dbesc($username), dbesc($email), dbesc($openid), @@ -248,6 +248,7 @@ function settings_post(&$a) { intval($expire), dbesc($openidserver), intval($blockwall), + intval($hidewall), intval(local_user()) ); if($r) @@ -257,12 +258,10 @@ function settings_post(&$a) { SET `publish` = %d, `net-publish` = %d, `hide-friends` = %d, - `hidewall` = %d WHERE `is-default` = 1 AND `uid` = %d LIMIT 1", intval($publish), intval($net_publish), intval($hide_friends), - intval($hidewall), intval(local_user()) ); @@ -436,8 +435,8 @@ function settings_content(&$a) { '$desc' => t('Hide profile details and all your messages from unknown viewers?'), '$yes_str' => t('Yes'), '$no_str' => t('No'), - '$yes_selected' => (($profile['hidewall']) ? " checked=\"checked\" " : ""), - '$no_selected' => (($profile['hidewall'] == 0) ? " checked=\"checked\" " : "") + '$yes_selected' => (($a->user['hidewall']) ? " checked=\"checked\" " : ""), + '$no_selected' => (($a->user['hidewall'] == 0) ? " checked=\"checked\" " : "") )); diff --git a/update.php b/update.php index 66908b8d3..f2cd5573f 100644 --- a/update.php +++ b/update.php @@ -1,6 +1,6 @@