aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorAlexander Kampmann <programmer@nurfuerspam.de>2012-04-05 13:27:09 +0200
committerAlexander Kampmann <programmer@nurfuerspam.de>2012-04-05 13:27:09 +0200
commitcbf4544887c7f496f2b2312727fe7bcb64a6d6c8 (patch)
tree6b327da2db68c6be38d917ee7e869e7546fa096d
parent9ba5fff865ed1d17c86982a5cf939f8e3e45da05 (diff)
downloadvolse-hubzilla-cbf4544887c7f496f2b2312727fe7bcb64a6d6c8.tar.gz
volse-hubzilla-cbf4544887c7f496f2b2312727fe7bcb64a6d6c8.tar.bz2
volse-hubzilla-cbf4544887c7f496f2b2312727fe7bcb64a6d6c8.zip
added index to config and pconfig table
changed config.php to use REPLACE instead of INSERT, this removes one db hit.
-rw-r--r--[-rwxr-xr-x]boot.php2
-rw-r--r--[-rwxr-xr-x]database.sql14
-rw-r--r--[-rwxr-xr-x]include/config.php306
-rw-r--r--[-rwxr-xr-x]update.php16
4 files changed, 178 insertions, 160 deletions
diff --git a/boot.php b/boot.php
index 04e16e64d..56548160d 100755..100644
--- 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', "<br />\r\n" );
define ( 'ATOM_TIME', 'Y-m-d\TH:i:s\Z' );
diff --git a/database.sql b/database.sql
index 327b482c0..a039746e8 100755..100644
--- 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
index 2cddda0b8..f93cb52c9 100755..100644
--- a/include/config.php
+++ b/include/config.php
@@ -1,222 +1,224 @@
<?php
-
/**
*
* Arbitrary configuration storage
* Note:
* Please do not store booleans - convert to 0/1 integer values
* The get_?config() functions return boolean false for keys that are unset,
- * and this could lead to subtle bugs.
+ * and this could lead to subtle bugs.
*
* There are a few places in the code (such as the admin panel) where boolean
* configurations need to be fixed as of 10/08/2011.
+ *
+ * @package config;
*/
-// retrieve a "family" of config variables from database to cached storage
-
+/**
+ * retrieve a "family" of config variables
+ * from database to cached storage
+ */
if(! function_exists('load_config')) {
-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'];
+ 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] === '!<unset>!') {
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] = '!<unset>!';
+ }
+ 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] = '!<unset>!';
- }
- 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] === '!<unset>!') {
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] = '!<unset>!';
+ $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] = '!<unset>!';
+ }
+ 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
index a69742a94..adb96b831 100755..100644
--- a/update.php
+++ b/update.php
@@ -1,6 +1,6 @@
<?php
-define( 'UPDATE_VERSION' , 1133 );
+define( 'UPDATE_VERSION' , 1134 );
/**
*
@@ -1136,3 +1136,17 @@ INDEX ( `username` )
}
+function update_1133() {
+ //there can't be indexes with more than 1000 bytes in mysql,
+ //so change charset to be smaller
+ q("ALTER TABLE `config` CHANGE `cat` `cat` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL ,
+CHANGE `k` `k` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL");
+ //and add the index
+ q("ALTER TABLE `friendica`.`config` ADD UNIQUE `access` ( `cat` , `k` ) ");
+
+ //same thing for pconfig
+ q("ALTER TABLE `pconfig` CHANGE `cat` `cat` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL ,
+ CHANGE `k` `k` CHAR( 255 ) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL");
+
+ q("ALTER TABLE `friendica`.`pconfig` ADD UNIQUE `access` ( `uid` , `cat` , `k` )");
+}