From 567320e2bcbb4ae3354b069716b6baf2338064a4 Mon Sep 17 00:00:00 2001 From: Chuck Hagenbuch Date: Sun, 4 Jan 2009 12:38:21 -0500 Subject: [PATCH] Add an index cache as well as the columns cache --- .../Db/lib/Horde/Db/Adapter/Abstract/Schema.php | 7 +- framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php | 37 +++++----- .../Db/lib/Horde/Db/Adapter/Postgresql/Schema.php | 81 +++++++++++----------- .../Db/lib/Horde/Db/Adapter/Sqlite/Schema.php | 39 ++++++----- framework/Db/test/Horde/Db/Adapter/MysqliTest.php | 6 +- .../Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php | 6 +- .../Db/test/Horde/Db/Adapter/Pdo/PgsqlTest.php | 6 +- .../Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php | 6 +- 8 files changed, 101 insertions(+), 87 deletions(-) diff --git a/framework/Db/lib/Horde/Db/Adapter/Abstract/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Abstract/Schema.php index 7cb206dec..35778ce1a 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Abstract/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Abstract/Schema.php @@ -251,7 +251,7 @@ abstract class Horde_Db_Adapter_Abstract_Schema * @param string $name * @return array */ - abstract public function indexes($tableName, $name=null); + abstract public function indexes($tableName, $name = null); /** * Returns an array of Column objects for the table specified by +table_name+. @@ -261,7 +261,7 @@ abstract class Horde_Db_Adapter_Abstract_Schema * @param string $name * @return array */ - abstract public function columns($tableName, $name=null); + abstract public function columns($tableName, $name = null); /** * Creates a new table @@ -714,7 +714,8 @@ abstract class Horde_Db_Adapter_Abstract_Schema */ protected function _clearTableCache($tableName) { - $this->_cache->set("tables/$tableName", null); + $this->_cache->set("tables/columns/$tableName", null); + $this->_cache->set("tables/indexes/$tableName", null); } } diff --git a/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php index 11a70cdf4..3f5608942 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Mysql/Schema.php @@ -202,19 +202,26 @@ class Horde_Db_Adapter_Mysql_Schema extends Horde_Db_Adapter_Abstract_Schema */ public function indexes($tableName, $name=null) { - $indexes = array(); - $currentIndex = null; - foreach ($this->select('SHOW KEYS FROM ' . $this->quoteTableName($tableName)) as $row) { - if ($currentIndex != $row[2]) { - if ($row[2] == 'PRIMARY') continue; - $currentIndex = $row[2]; - $indexes[] = (object)array('table' => $row[0], - 'name' => $row[2], - 'unique' => $row[1] == '0', - 'columns' => array()); + $indexes = @unserialize($this->_cache->get("tables/indexes/$tableName")); + + if (!$indexes) { + $indexes = array(); + $currentIndex = null; + foreach ($this->select('SHOW KEYS FROM ' . $this->quoteTableName($tableName)) as $row) { + if ($currentIndex != $row[2]) { + if ($row[2] == 'PRIMARY') continue; + $currentIndex = $row[2]; + $indexes[] = (object)array('table' => $row[0], + 'name' => $row[2], + 'unique' => $row[1] == '0', + 'columns' => array()); + } + $indexes[count($indexes) - 1]->columns[] = $row[4]; } - $indexes[sizeof($indexes)-1]->columns[] = $row[4]; + + $this->_cache->set("tables/indexes/$tableName", serialize($indexes)); } + return $indexes; } @@ -224,15 +231,12 @@ class Horde_Db_Adapter_Mysql_Schema extends Horde_Db_Adapter_Abstract_Schema */ public function columns($tableName, $name=null) { - // check cache - $rows = @unserialize($this->_cache->get("tables/$tableName")); + $rows = @unserialize($this->_cache->get("tables/columns/$tableName")); - // query to build rows if (!$rows) { $rows = $this->selectAll('SHOW FIELDS FROM ' . $this->quoteTableName($tableName), $name); - // write cache - $this->_cache->set("tables/$tableName", serialize($rows)); + $this->_cache->set("tables/columns/$tableName", serialize($rows)); } // create columns from rows @@ -241,6 +245,7 @@ class Horde_Db_Adapter_Mysql_Schema extends Horde_Db_Adapter_Abstract_Schema $columns[] = $this->componentFactory('Column', array( $row[0], $row[4], $row[1], $row[2] == 'YES')); } + return $columns; } diff --git a/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php index 03a82df8c..b84c3b163 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Postgresql/Schema.php @@ -203,42 +203,48 @@ class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Abstract_Schem */ public function indexes($tableName, $name = null) { - $schemas = array(); - foreach (explode(',', $this->getSchemaSearchPath()) as $p) { - $schemas[] = $this->quote($p); - } + $indexes = @unserialize($this->_cache->get("tables/indexes/$tableName")); - $sql = " - SELECT distinct i.relname, d.indisunique, a.attname - FROM pg_class t, pg_class i, pg_index d, pg_attribute a - WHERE i.relkind = 'i' - AND d.indexrelid = i.oid - AND d.indisprimary = 'f' - AND t.oid = d.indrelid - AND t.relname = " . $this->quote($tableName) . " - AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (" . implode(',', $schemas) . ") ) - AND a.attrelid = t.oid - AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum - OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum - OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum - OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum - OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum ) - ORDER BY i.relname"; - - $result = $this->select($sql, $name); - - $currentIndex = null; - $indexes = array(); - - foreach ($result as $row) { - if ($currentIndex != $row[0]) { - $indexes[] = (object)array('table' => $tableName, - 'name' => $row[0], - 'unique' => $row[1] == 't', - 'columns' => array()); - $currentIndex = $row[0]; + if (!$indexes) { + $schemas = array(); + foreach (explode(',', $this->getSchemaSearchPath()) as $p) { + $schemas[] = $this->quote($p); } - $indexes[sizeof($indexes)-1]->columns[] = $row[2]; + + $sql = " + SELECT distinct i.relname, d.indisunique, a.attname + FROM pg_class t, pg_class i, pg_index d, pg_attribute a + WHERE i.relkind = 'i' + AND d.indexrelid = i.oid + AND d.indisprimary = 'f' + AND t.oid = d.indrelid + AND t.relname = " . $this->quote($tableName) . " + AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (" . implode(',', $schemas) . ") ) + AND a.attrelid = t.oid + AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum + OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum + OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum + OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum + OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum ) + ORDER BY i.relname"; + + $result = $this->select($sql, $name); + + $currentIndex = null; + $indexes = array(); + + foreach ($result as $row) { + if ($currentIndex != $row[0]) { + $indexes[] = (object)array('table' => $tableName, + 'name' => $row[0], + 'unique' => $row[1] == 't', + 'columns' => array()); + $currentIndex = $row[0]; + } + $indexes[sizeof($indexes)-1]->columns[] = $row[2]; + } + + $this->_cache->set("tables/indexes/$tableName", serialize($indexes)); } return $indexes; @@ -249,15 +255,12 @@ class Horde_Db_Adapter_Postgresql_Schema extends Horde_Db_Adapter_Abstract_Schem */ public function columns($tableName, $name = null) { - // check cache - $rows = @unserialize($this->_cache->get("tables/$tableName")); + $rows = @unserialize($this->_cache->get("tables/columns/$tableName")); - // query to build rows if (!$rows) { $rows = $this->columnDefinitions($tableName, $name); - // write cache - $this->_cache->set("tables/$tableName", serialize($rows)); + $this->_cache->set("tables/columns/$tableName", serialize($rows)); } // create columns from rows diff --git a/framework/Db/lib/Horde/Db/Adapter/Sqlite/Schema.php b/framework/Db/lib/Horde/Db/Adapter/Sqlite/Schema.php index 4114978c1..66dad57df 100644 --- a/framework/Db/lib/Horde/Db/Adapter/Sqlite/Schema.php +++ b/framework/Db/lib/Horde/Db/Adapter/Sqlite/Schema.php @@ -124,7 +124,7 @@ class Horde_Db_Adapter_Sqlite_Schema extends Horde_Db_Adapter_Abstract_Schema * * @param string $name */ - public function tables($name=null) + public function tables($name = null) { return $this->selectValues("SELECT name FROM sqlite_master WHERE type = 'table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type = 'table' AND name != 'sqlite_sequence' ORDER BY name"); } @@ -135,20 +135,27 @@ class Horde_Db_Adapter_Sqlite_Schema extends Horde_Db_Adapter_Abstract_Schema * @param string $tableName * @param string $name */ - public function indexes($tableName, $name=null) + public function indexes($tableName, $name = null) { - $indexes = array(); - foreach ($this->select('PRAGMA index_list(' . $this->quoteTableName($tableName) . ')') as $row) { - $index = (object)array('table' => $tableName, - 'name' => $row[1], - 'unique' => (bool)$row[2], - 'columns' => array()); - foreach ($this->select('PRAGMA index_info(' . $this->quoteColumnName($index->name) . ')') as $field) { - $index->columns[] = $field[2]; + $indexes = @unserialize($this->_cache->get("tables/indexes/$tableName")); + + if (!$indexes) { + $indexes = array(); + foreach ($this->select('PRAGMA index_list(' . $this->quoteTableName($tableName) . ')') as $row) { + $index = (object)array('table' => $tableName, + 'name' => $row[1], + 'unique' => (bool)$row[2], + 'columns' => array()); + foreach ($this->select('PRAGMA index_info(' . $this->quoteColumnName($index->name) . ')') as $field) { + $index->columns[] = $field[2]; + } + + $indexes[] = $index; } - $indexes[] = $index; + $this->_cache->set("tables/indexes/$tableName", serialize($indexes)); } + return $indexes; } @@ -156,17 +163,14 @@ class Horde_Db_Adapter_Sqlite_Schema extends Horde_Db_Adapter_Abstract_Schema * @param string $tableName * @param string $name */ - public function columns($tableName, $name=null) + public function columns($tableName, $name = null) { - // check cache - $rows = @unserialize($this->_cache->get("tables/$tableName")); + $rows = @unserialize($this->_cache->get("tables/columns/$tableName")); - // query to build rows if (!$rows) { $rows = $this->selectAll('PRAGMA table_info(' . $this->quoteTableName($tableName) . ')', $name); - // write cache - $this->_cache->set("tables/$tableName", serialize($rows)); + $this->_cache->set("tables/columns/$tableName", serialize($rows)); } // create columns from rows @@ -175,6 +179,7 @@ class Horde_Db_Adapter_Sqlite_Schema extends Horde_Db_Adapter_Abstract_Schema $columns[] = $this->componentFactory('Column', array( $row[1], $row[4], $row[2], !(bool)$row[3])); } + return $columns; } diff --git a/framework/Db/test/Horde/Db/Adapter/MysqliTest.php b/framework/Db/test/Horde/Db/Adapter/MysqliTest.php index c058adf33..0a31f0b89 100644 --- a/framework/Db/test/Horde/Db/Adapter/MysqliTest.php +++ b/framework/Db/test/Horde/Db/Adapter/MysqliTest.php @@ -815,13 +815,13 @@ class Horde_Db_Adapter_MysqliTest extends PHPUnit_Framework_TestCase public function testCachedTableDescription() { // remove any current cache. - $this->_cache->set('tables/cache_table', ''); - $this->assertEquals('', $this->_cache->get('tables/cache_table')); + $this->_cache->set('tables/columns/cache_table', ''); + $this->assertEquals('', $this->_cache->get('tables/columns/cache_table')); $this->_createTestTable('cache_table'); $cols = $this->_conn->columns('cache_table'); - $this->assertNotEquals('', $this->_cache->get('tables/cache_table')); + $this->assertNotEquals('', $this->_cache->get('tables/columns/cache_table')); } diff --git a/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php b/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php index 2ee5de605..7f3652b14 100644 --- a/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Pdo/MysqlTest.php @@ -815,13 +815,13 @@ class Horde_Db_Adapter_Pdo_MysqlTest extends PHPUnit_Framework_TestCase public function testCachedTableDescription() { // remove any current cache. - $this->_cache->set('tables/cache_table', ''); - $this->assertEquals('', $this->_cache->get('tables/cache_table')); + $this->_cache->set('tables/columns/cache_table', ''); + $this->assertEquals('', $this->_cache->get('tables/columns/cache_table')); $this->_createTestTable('cache_table'); $cols = $this->_conn->columns('cache_table'); - $this->assertNotEquals('', $this->_cache->get('tables/cache_table')); + $this->assertNotEquals('', $this->_cache->get('tables/columns/cache_table')); } diff --git a/framework/Db/test/Horde/Db/Adapter/Pdo/PgsqlTest.php b/framework/Db/test/Horde/Db/Adapter/Pdo/PgsqlTest.php index f7b741f3c..f0331e0c0 100644 --- a/framework/Db/test/Horde/Db/Adapter/Pdo/PgsqlTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Pdo/PgsqlTest.php @@ -787,13 +787,13 @@ class Horde_Db_Adapter_Pdo_PgsqlTest extends PHPUnit_Framework_TestCase public function testCachedTableDescription() { // remove any current cache. - $this->_cache->set('tables/cache_table', ''); - $this->assertEquals('', $this->_cache->get('tables/cache_table')); + $this->_cache->set('tables/columns/cache_table', ''); + $this->assertEquals('', $this->_cache->get('tables/columns/cache_table')); $this->_createTestTable('cache_table'); $cols = $this->_conn->columns('cache_table'); - $this->assertNotEquals('', $this->_cache->get('tables/cache_table')); + $this->assertNotEquals('', $this->_cache->get('tables/columns/cache_table')); } diff --git a/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php b/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php index 48eececa9..a89bcc2df 100644 --- a/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php +++ b/framework/Db/test/Horde/Db/Adapter/Pdo/SqliteTest.php @@ -832,13 +832,13 @@ class Horde_Db_Adapter_Pdo_SqliteTest extends PHPUnit_Framework_TestCase public function testCachedTableDescription() { // remove any current cache. - $this->_cache->set('tables/cache_table', ''); - $this->assertEquals('', $this->_cache->get('tables/cache_table')); + $this->_cache->set('tables/columns/cache_table', ''); + $this->assertEquals('', $this->_cache->get('tables/columns/cache_table')); $this->_createTestTable('cache_table'); $cols = $this->_conn->columns('cache_table'); - $this->assertNotEquals('', $this->_cache->get('tables/cache_table')); + $this->assertNotEquals('', $this->_cache->get('tables/columns/cache_table')); } -- 2.11.0