From cbf4544887c7f496f2b2312727fe7bcb64a6d6c8 Mon Sep 17 00:00:00 2001 From: Alexander Kampmann Date: Thu, 5 Apr 2012 13:27:09 +0200 Subject: [PATCH] added index to config and pconfig table changed config.php to use REPLACE instead of INSERT, this removes one db hit. --- boot.php | 2 +- database.sql | 14 ++- include/config.php | 306 +++++++++++++++++++++++---------------------- update.php | 16 ++- 4 files changed, 178 insertions(+), 160 deletions(-) mode change 100755 => 100644 boot.php mode change 100755 => 100644 database.sql mode change 100755 => 100644 include/config.php mode change 100755 => 100644 update.php diff --git a/boot.php b/boot.php old mode 100755 new mode 100644 index 04e16e64dc..56548160da --- a/boot.php +++ b/boot.php @@ -11,7 +11,7 @@ require_once('include/cache.php'); define ( 'FRIENDICA_PLATFORM', 'Friendica'); define ( 'FRIENDICA_VERSION', '2.3.1288' ); define ( 'DFRN_PROTOCOL_VERSION', '2.23' ); -define ( 'DB_UPDATE_VERSION', 1133 ); +define ( 'DB_UPDATE_VERSION', 1134 ); define ( 'EOL', "
\r\n" ); define ( 'ATOM_TIME', 'Y-m-d\TH:i:s\Z' ); diff --git a/database.sql b/database.sql old mode 100755 new mode 100644 index 327b482c06..a039746e8e --- a/database.sql +++ b/database.sql @@ -34,10 +34,11 @@ CREATE TABLE IF NOT EXISTS `challenge` ( CREATE TABLE IF NOT EXISTS `config` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `cat` char(255) NOT NULL, - `k` char(255) NOT NULL, + `cat` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, + `k` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, `v` text NOT NULL, - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + UNIQUE KEY `access`(`cat`,`k`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; @@ -536,9 +537,10 @@ INDEX ( `batch` ) CREATE TABLE IF NOT EXISTS `pconfig` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `uid` INT NOT NULL DEFAULT '0', -`cat` CHAR( 255 ) NOT NULL , -`k` CHAR( 255 ) NOT NULL , -`v` MEDIUMTEXT NOT NULL +`cat` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL , +`k` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL , +`v` MEDIUMTEXT NOT NULL, +UNIQUE KEY `access`(`cat`, `k`) ) ENGINE = MyISAM DEFAULT CHARSET=utf8; diff --git a/include/config.php b/include/config.php old mode 100755 new mode 100644 index 2cddda0b8d..f93cb52c95 --- a/include/config.php +++ b/include/config.php @@ -1,222 +1,224 @@ config[$k] = $rr['v']; - } else { - $a->config[$family][$k] = $rr['v']; + function load_config($family) { + global $a; + $r = q("SELECT * FROM `config` WHERE `cat` = '%s'", + dbesc($family) + ); + if(count($r)) { + foreach($r as $rr) { + $k = $rr['k']; + if ($rr['cat'] === 'config') { + $a->config[$k] = $rr['v']; + } else { + $a->config[$family][$k] = $rr['v']; + } } } } -}} - -// get a particular config variable given the family name -// and key. Returns false if not set. -// $instore is only used by the set_config function -// to determine if the key already exists in the DB -// If a key is found in the DB but doesn't exist in -// local config cache, pull it into the cache so we don't have -// to hit the DB again for this item. +} +/** + * get a particular config variable given the family name + * and key. Returns false if not set. + * + * If a key is found in the DB but doesn't exist in + * local config cache, pull it into the cache so we don't have + *to hit the DB again for this item. + */ if(! function_exists('get_config')) { -function get_config($family, $key, $instore = false) { + function get_config($family, $key) { + + global $a; - global $a; - if(! $instore) { if(isset($a->config[$family][$key])) { if($a->config[$family][$key] === '!!') { return false; } return $a->config[$family][$key]; } + $ret = q("SELECT `v` FROM `config` WHERE `cat` = '%s' AND `k` = '%s' LIMIT 1", + dbesc($family), + dbesc($key) + ); + if(count($ret)) { + // manage array value + $val = (preg_match("|^a:[0-9]+:{.*}$|", $ret[0]['v'])?unserialize( $ret[0]['v']):$ret[0]['v']); + $a->config[$family][$key] = $val; + return $val; + } + else { + $a->config[$family][$key] = '!!'; + } + return false; } - $ret = q("SELECT `v` FROM `config` WHERE `cat` = '%s' AND `k` = '%s' LIMIT 1", - dbesc($family), - dbesc($key) - ); - if(count($ret)) { - // manage array value - $val = (preg_match("|^a:[0-9]+:{.*}$|", $ret[0]['v'])?unserialize( $ret[0]['v']):$ret[0]['v']); - $a->config[$family][$key] = $val; - return $val; - } - else { - $a->config[$family][$key] = '!!'; - } - return false; -}} - -// Store a config value ($value) in the category ($family) -// under the key ($key) -// Return the value, or false if the database update failed +} +/** + * Store a config value ($value) in the category ($family) + * under the key ($key) + * + * Return the value, or false if the database update failed + */ if(! function_exists('set_config')) { -function set_config($family,$key,$value) { - global $a; - - // manage array value - $dbvalue = (is_array($value)?serialize($value):$value); + function set_config($family,$key,$value) { + global $a; + + // manage array value + $dbvalue = (is_array($value)?serialize($value):$value); - if(get_config($family,$key,true) === false) { $a->config[$family][$key] = $value; - $ret = q("INSERT INTO `config` ( `cat`, `k`, `v` ) VALUES ( '%s', '%s', '%s' ) ", - dbesc($family), - dbesc($key), - dbesc($dbvalue) + $ret = q("REPLACE INTO `config` ( `cat`, `k`, `v` ) VALUES ( '%s', '%s', '%s' ) ", + dbesc($family), + dbesc($key), + dbesc($dbvalue) ); - if($ret) + if($ret) { return $value; + } return $ret; - } - - $ret = q("UPDATE `config` SET `v` = '%s' WHERE `cat` = '%s' AND `k` = '%s' LIMIT 1", - dbesc($dbvalue), - dbesc($family), - dbesc($key) - ); - - $a->config[$family][$key] = $value; - if($ret) - return $value; - return $ret; -}} + } +} if(! function_exists('load_pconfig')) { -function load_pconfig($uid,$family) { - global $a; - $r = q("SELECT * FROM `pconfig` WHERE `cat` = '%s' AND `uid` = %d", - dbesc($family), - intval($uid) - ); - if(count($r)) { - foreach($r as $rr) { - $k = $rr['k']; - $a->config[$uid][$family][$k] = $rr['v']; + function load_pconfig($uid,$family) { + global $a; + $r = q("SELECT * FROM `pconfig` WHERE `cat` = '%s' AND `uid` = %d", + dbesc($family), + intval($uid) + ); + if(count($r)) { + foreach($r as $rr) { + $k = $rr['k']; + $a->config[$uid][$family][$k] = $rr['v']; + } } } -}} - +} +/** + * get a particular user-specific config variable given the family name, + * the user id and key. Returns false if not set. + * + * If a key is found in the DB but doesn't exist in + * local config cache, pull it into the cache so we don't have + * to hit the DB again for this item. + */ if(! function_exists('get_pconfig')) { -function get_pconfig($uid,$family, $key, $instore = false) { + function get_pconfig($uid,$family, $key) { + + global $a; - global $a; - if(! $instore) { if(isset($a->config[$uid][$family][$key])) { if($a->config[$uid][$family][$key] === '!!') { return false; } return $a->config[$uid][$family][$key]; } - } - $ret = q("SELECT `v` FROM `pconfig` WHERE `uid` = %d AND `cat` = '%s' AND `k` = '%s' LIMIT 1", - intval($uid), - dbesc($family), - dbesc($key) - ); - if(count($ret)) { - $val = (preg_match("|^a:[0-9]+:{.*}$|", $ret[0]['v'])?unserialize( $ret[0]['v']):$ret[0]['v']); - $a->config[$uid][$family][$key] = $val; - return $val; - } - else { - $a->config[$uid][$family][$key] = '!!'; + $ret = q("SELECT `v` FROM `pconfig` WHERE `uid` = %d AND `cat` = '%s' AND `k` = '%s' LIMIT 1", + intval($uid), + dbesc($family), + dbesc($key) + ); + + if(count($ret)) { + $val = (preg_match("|^a:[0-9]+:{.*}$|", $ret[0]['v'])?unserialize( $ret[0]['v']):$ret[0]['v']); + $a->config[$uid][$family][$key] = $val; + return $val; + } + else { + $a->config[$uid][$family][$key] = '!!'; + } + return false; } - return false; -}} +} +/** + * Delete a value from config. This function + * deletes both: db value and cache entry. + */ if(! function_exists('del_config')) { -function del_config($family,$key) { - - global $a; - if(x($a->config[$family],$key)) - unset($a->config[$family][$key]); - $ret = q("DELETE FROM `config` WHERE `cat` = '%s' AND `k` = '%s' LIMIT 1", - dbesc($family), - dbesc($key) - ); - return $ret; -}} - - + function del_config($family,$key) { + + global $a; + if(x($a->config[$family],$key)) + unset($a->config[$family][$key]); + $ret = q("DELETE FROM `config` WHERE `cat` = '%s' AND `k` = '%s' LIMIT 1", + dbesc($family), + dbesc($key) + ); + return $ret; + } +} -// Same as above functions except these are for personal config storage and take an -// additional $uid argument. +/** + * Store a user-specific config value ($value) for user $uid in the category ($family) + * under the key ($key). + * + * Return the value, or false if the database update failed + */ if(! function_exists('set_pconfig')) { -function set_pconfig($uid,$family,$key,$value) { + function set_pconfig($uid,$family,$key,$value) { - global $a; + global $a; + + // manage array value + $dbvalue = (is_array($value)?serialize($value):$value); - // manage array value - $dbvalue = (is_array($value)?serialize($value):$value); - if(get_pconfig($uid,$family,$key,true) === false) { $a->config[$uid][$family][$key] = $value; - $ret = q("INSERT INTO `pconfig` ( `uid`, `cat`, `k`, `v` ) VALUES ( %d, '%s', '%s', '%s' ) ", - intval($uid), - dbesc($family), - dbesc($key), - dbesc($dbvalue) + $ret = q("REPLACE INTO `pconfig` ( `uid`, `cat`, `k`, `v` ) VALUES ( %d, '%s', '%s', '%s' ) ", + intval($uid), + dbesc($family), + dbesc($key), + dbesc($dbvalue) ); - if($ret) + if($ret) { return $value; + } return $ret; - } - $ret = q("UPDATE `pconfig` SET `v` = '%s' WHERE `uid` = %d AND `cat` = '%s' AND `k` = '%s' LIMIT 1", - dbesc($dbvalue), - intval($uid), - dbesc($family), - dbesc($key) - ); - - $a->config[$uid][$family][$key] = $value; - if($ret) - return $value; - return $ret; -}} + } +} if(! function_exists('del_pconfig')) { -function del_pconfig($uid,$family,$key) { - - global $a; - if(x($a->config[$uid][$family],$key)) - unset($a->config[$uid][$family][$key]); - $ret = q("DELETE FROM `pconfig` WHERE `uid` = %d AND `cat` = '%s' AND `k` = '%s' LIMIT 1", - intval($uid), - dbesc($family), - dbesc($key) - ); - return $ret; -}} + function del_pconfig($uid,$family,$key) { + + global $a; + if(x($a->config[$uid][$family],$key)) + unset($a->config[$uid][$family][$key]); + $ret = q("DELETE FROM `pconfig` WHERE `uid` = %d AND `cat` = '%s' AND `k` = '%s' LIMIT 1", + intval($uid), + dbesc($family), + dbesc($key) + ); + return $ret; + } +} diff --git a/update.php b/update.php old mode 100755 new mode 100644 index a69742a949..adb96b8319 --- a/update.php +++ b/update.php @@ -1,6 +1,6 @@