From a4100521d7a60a31e2f0c22d12fc0e371c201f8c Mon Sep 17 00:00:00 2001 From: Joe7 Date: Mon, 27 Dec 2010 14:03:11 +0100 Subject: Added limit on select as for the outcome it doesn't matter if there are 20 rows or just 1. Is sufficient to return straight after reading 1 row. --- modules/gallery/libraries/drivers/Cache/Database.php | 1 + 1 file changed, 1 insertion(+) diff --git a/modules/gallery/libraries/drivers/Cache/Database.php b/modules/gallery/libraries/drivers/Cache/Database.php index b7822811..4f57b3da 100644 --- a/modules/gallery/libraries/drivers/Cache/Database.php +++ b/modules/gallery/libraries/drivers/Cache/Database.php @@ -34,6 +34,7 @@ class Cache_Database_Driver extends Cache_Driver { $count = db::build() ->where("key", "=", $id) ->where("expiration", ">=", time()) + ->limit("1") ->count_records("caches"); return $count > 0; } -- cgit v1.2.3 From f4ecb939f5f61f16715df2ed5a88f01d85926eb6 Mon Sep 17 00:00:00 2001 From: Joe7 Date: Mon, 27 Dec 2010 15:35:33 +0100 Subject: Using ON DUPLICATE KEY UPDATE instead of SELECT+UPDATE/INSERT style method (that does 2 trips to Database server and is less optimal). exists() method is not needed anymore thus got removed --- .../gallery/libraries/drivers/Cache/Database.php | 38 +++++----------------- 1 file changed, 8 insertions(+), 30 deletions(-) diff --git a/modules/gallery/libraries/drivers/Cache/Database.php b/modules/gallery/libraries/drivers/Cache/Database.php index 4f57b3da..7eda5b30 100644 --- a/modules/gallery/libraries/drivers/Cache/Database.php +++ b/modules/gallery/libraries/drivers/Cache/Database.php @@ -24,21 +24,6 @@ class Cache_Database_Driver extends Cache_Driver { // Kohana database instance protected $db; - /** - * Checks if a cache id is already set. - * - * @param string cache id - * @return boolean - */ - public function exists($id) { - $count = db::build() - ->where("key", "=", $id) - ->where("expiration", ">=", time()) - ->limit("1") - ->count_records("caches"); - return $count > 0; - } - /** * Sets a cache item to the given data, tags, and lifetime. * @@ -60,22 +45,15 @@ class Cache_Database_Driver extends Cache_Driver { $lifetime += time(); } + $db = Database::instance(); + $tags = $db->escape($tags); foreach ($items as $id => $data) { - if ($this->exists($id)) { - $status = db::build() - ->update("caches") - ->set("tags", $tags) - ->set("expiration", $lifetime) - ->set("cache", serialize($data)) - ->where("key", "=", $id) - ->execute(); - } else { - $status = db::build() - ->insert("caches") - ->columns("key", "tags", "expiration", "cache") - ->values($id, $tags, $lifetime, serialize($data)) - ->execute(); - } + $id = $db->escape($id); + $data = $db->escape(serialize($data)); + $db->query("INSERT INTO {caches} (`key`, `tags`, `expiration`, `cache`) + VALUES ('$id', '$tags', $lifetime, '$data') + ON DUPLICATE KEY UPDATE + `tags`=VALUES(tags), `expiration`=VALUES(expiration), `cache`=VALUES(cache)"); } return true; -- cgit v1.2.3 From 15792c4ddfec9741324c6968cc3e70fb23cef8ad Mon Sep 17 00:00:00 2001 From: Joe7 Date: Mon, 27 Dec 2010 22:16:29 +0100 Subject: Added changes to installer and upgrader scripts to support INSERT ON DUPLICATE KEY UPDATE SYNTAX in cache lib --- installer/install.sql | 2 +- modules/gallery/helpers/gallery_installer.php | 7 ++++++- modules/gallery/module.info | 2 +- 3 files changed, 8 insertions(+), 3 deletions(-) diff --git a/installer/install.sql b/installer/install.sql index 427a3283..baee2b9d 100644 --- a/installer/install.sql +++ b/installer/install.sql @@ -43,7 +43,7 @@ CREATE TABLE {caches} ( `expiration` int(9) NOT NULL, `cache` longblob, PRIMARY KEY (`id`), - KEY `key` (`key`), + UNIQUE KEY `key` (`key`), KEY `tags` (`tags`) ) DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; diff --git a/modules/gallery/helpers/gallery_installer.php b/modules/gallery/helpers/gallery_installer.php index a6b8e6a2..ecabc766 100644 --- a/modules/gallery/helpers/gallery_installer.php +++ b/modules/gallery/helpers/gallery_installer.php @@ -309,7 +309,7 @@ class gallery_installer { module::set_var("gallery", "show_user_profiles_to", "registered_users"); module::set_var("gallery", "extra_binary_paths", "/usr/local/bin:/opt/local/bin:/opt/bin"); - module::set_version("gallery", 41); + module::set_version("gallery", 42); } static function upgrade($version) { @@ -642,6 +642,11 @@ class gallery_installer { module::clear_var("gallery", "_cache"); module::set_version("gallery", $version = 41); } + + if ($version == 41) { + $db->query("ALTER TABLE {caches} DROP INDEX `key`, ADD UNIQUE `key` (`key`)"); + module::set_version("gallery", $version = 42); + } } static function uninstall() { diff --git a/modules/gallery/module.info b/modules/gallery/module.info index 2b684e5e..0cc3f6d1 100644 --- a/modules/gallery/module.info +++ b/modules/gallery/module.info @@ -1,3 +1,3 @@ name = "Gallery 3" description = "Gallery core application" -version = 41 +version = 42 -- cgit v1.2.3 From 3c31237406a4a55bfc0955ca433a1be0aec210a7 Mon Sep 17 00:00:00 2001 From: Joe7 Date: Tue, 28 Dec 2010 18:42:43 +0100 Subject: Truncating table first againt collides when converting INDEX into Unique --- modules/gallery/helpers/gallery_installer.php | 1 + 1 file changed, 1 insertion(+) diff --git a/modules/gallery/helpers/gallery_installer.php b/modules/gallery/helpers/gallery_installer.php index ecabc766..97a3fb83 100644 --- a/modules/gallery/helpers/gallery_installer.php +++ b/modules/gallery/helpers/gallery_installer.php @@ -644,6 +644,7 @@ class gallery_installer { } if ($version == 41) { + $db->query("TRUNCATE TABLE {caches}"); $db->query("ALTER TABLE {caches} DROP INDEX `key`, ADD UNIQUE `key` (`key`)"); module::set_version("gallery", $version = 42); } -- cgit v1.2.3 From c5473ec4c070a4e548b5c2f83c9c0ece372d11d2 Mon Sep 17 00:00:00 2001 From: Joe7 Date: Tue, 28 Dec 2010 22:28:55 +0100 Subject: Coding style fixes: identation on line 48+removed trailing whitespaces, added spaces around =s --- modules/gallery/libraries/drivers/Cache/Database.php | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) diff --git a/modules/gallery/libraries/drivers/Cache/Database.php b/modules/gallery/libraries/drivers/Cache/Database.php index 7eda5b30..2e773ca4 100644 --- a/modules/gallery/libraries/drivers/Cache/Database.php +++ b/modules/gallery/libraries/drivers/Cache/Database.php @@ -45,15 +45,15 @@ class Cache_Database_Driver extends Cache_Driver { $lifetime += time(); } - $db = Database::instance(); + $db = Database::instance(); $tags = $db->escape($tags); foreach ($items as $id => $data) { $id = $db->escape($id); $data = $db->escape(serialize($data)); - $db->query("INSERT INTO {caches} (`key`, `tags`, `expiration`, `cache`) - VALUES ('$id', '$tags', $lifetime, '$data') - ON DUPLICATE KEY UPDATE - `tags`=VALUES(tags), `expiration`=VALUES(expiration), `cache`=VALUES(cache)"); + $db->query("INSERT INTO {caches} (`key`, `tags`, `expiration`, `cache`) + VALUES ('$id', '$tags', $lifetime, '$data') + ON DUPLICATE KEY UPDATE `tags` = VALUES(tags), `expiration` = VALUES(expiration), + `cache` = VALUES(cache)"); } return true; -- cgit v1.2.3