From be18b9637d8a2c35e4044defc6ce1acaa21dc303 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sat, 17 Nov 2012 22:44:20 +0200 Subject: Improve DB field_data() for IBM Informix and IBM DB2 (PDO) --- .../drivers/pdo/subdrivers/pdo_ibm_driver.php | 29 ++++++--- .../drivers/pdo/subdrivers/pdo_informix_driver.php | 74 +++++++++++++++++++--- 2 files changed, 84 insertions(+), 19 deletions(-) diff --git a/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php b/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php index d8dbc50c0..4d8436899 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php @@ -126,7 +126,8 @@ class CI_DB_pdo_ibm_driver extends CI_DB_pdo_driver { */ protected function _list_tables($prefix_limit = FALSE) { - $sql = 'SELECT "tabname" FROM "syscat"."tables" WHERE "type" = \'T\''; + $sql = 'SELECT "tabname" FROM "syscat"."tables" + WHERE "type" = \'T\' AND LOWER("tabschema") = '.$this->escape(strtolower($this->database)); if ($prefix_limit === TRUE && $this->dbprefix !== '') { @@ -145,27 +146,35 @@ class CI_DB_pdo_ibm_driver extends CI_DB_pdo_driver { * Generates a platform-specific query string so that the column names can be fetched * * @param string $table - * @return string + * @return array */ protected function _list_columns($table = '') { - return 'SELECT "colname" FROM "syscat"."tables" - WHERE "syscat"."tabtype" = \'T\' AND "syscat"."tabname" = '.$this->escape($table); + return 'SELECT "colname" FROM "syscat"."columns" + WHERE LOWER("tabschema") = '.$this->escape(strtolower($this->database)).' + AND LOWER("tabname") = '.$this->escape(strtolower($table)); } // -------------------------------------------------------------------- /** - * Field data query - * - * Generates a platform-specific query so that the column data can be retrieved + * Returns an object with field data * * @param string $table - * @return string + * @return array */ - protected function _field_data($table) + public function field_data($table = '') { - return 'SELECT * FROM '.$this->protect_identifiers($table, TRUE, NULL, FALSE).' FETCH FIRST 1 ROWS ONLY'; + $sql = 'SELECT "colname" AS "name", "typename" AS "type", "default" AS "default", "length" AS "max_length", + CASE "keyseq" WHEN NULL THEN 0 ELSE 1 END AS "primary_key" + FROM "syscat"."columns" + WHERE LOWER("tabschema") = '.$this->escape(strtolower($this->database)).' + AND LOWER("tabname") = '.$this->escape(strtolower($table)).' + ORDER BY "colno"'; + + return (($query = $this->query($sql)) !== FALSE) + ? $query->result_object() + : FALSE; } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php index 03886f442..1db5205fd 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php @@ -128,7 +128,8 @@ class CI_DB_pdo_informix_driver extends CI_DB_pdo_driver { */ protected function _list_tables($prefix_limit = FALSE) { - $sql = 'SELECT "tabname" FROM "systables" WHERE "tabid" > 99 AND "tabtype" = \'T\''; + $sql = 'SELECT "tabname" FROM "systables" + WHERE "tabid" > 99 AND "tabtype" = \'T\' AND LOWER("owner") = '.$this->escape(strtolower($this->username)); if ($prefix_limit === TRUE && $this->dbprefix !== '') { @@ -151,24 +152,79 @@ class CI_DB_pdo_informix_driver extends CI_DB_pdo_driver { */ protected function _list_columns($table = '') { + if (strpos($table, '.') !== FALSE) + { + sscanf($table, '%[^.].%s', $owner, $table); + } + else + { + $owner = $this->username; + } + return 'SELECT "colname" FROM "systables", "syscolumns" - WHERE "systables"."tabid" = "syscolumns"."tabid" AND "systables"."tabtype" = \'T\' AND "systables"."tabname" = ' - .$this->escape($table); + WHERE "systables"."tabid" = "syscolumns"."tabid" + AND "systables"."tabtype" = \'T\' + AND LOWER("systables"."owner") = '.$this->escape(strtolower($owner)).' + AND LOWER("systables"."tabname") = '.$this->escape(strtolower($table)); } // -------------------------------------------------------------------- /** - * Field data query - * - * Generates a platform-specific query so that the column data can be retrieved + * Returns an object with field data * * @param string $table - * @return string + * @return array */ - protected function _field_data($table) + public function field_data($table = '') { - return 'SELECT FIRST 1 * FROM '.$this->protect_identifiers($table, TRUE, NULL, FALSE); + $sql = 'SELECT "syscolumns"."colname" AS "name", + CASE "syscolumns"."coltype" + WHEN 0 THEN \'CHAR\' + WHEN 1 THEN \'SMALLINT\' + WHEN 2 THEN \'INTEGER\' + WHEN 3 THEN \'FLOAT\' + WHEN 4 THEN \'SMALLFLOAT\' + WHEN 5 THEN \'DECIMAL\' + WHEN 6 THEN \'SERIAL\' + WHEN 7 THEN \'DATE\' + WHEN 8 THEN \'MONEY\' + WHEN 9 THEN \'NULL\' + WHEN 10 THEN \'DATETIME\' + WHEN 11 THEN \'BYTE\' + WHEN 12 THEN \'TEXT\' + WHEN 13 THEN \'VARCHAR\' + WHEN 14 THEN \'INTERVAL\' + WHEN 15 THEN \'NCHAR\' + WHEN 16 THEN \'NVARCHAR\' + WHEN 17 THEN \'INT8\' + WHEN 18 THEN \'SERIAL8\' + WHEN 19 THEN \'SET\' + WHEN 20 THEN \'MULTISET\' + WHEN 21 THEN \'LIST\' + WHEN 22 THEN \'Unnamed ROW\' + WHEN 40 THEN \'LVARCHAR\' + WHEN 41 THEN \'BLOB/CLOB/BOOLEAN\' + WHEN 4118 THEN \'Named ROW\' + ELSE "syscolumns"."coltype" + END AS "type", + "syscolumns"."collength" as "max_length", + CASE "sysdefaults"."type" + WHEN \'L\' THEN "sysdefaults"."default" + ELSE NULL + END AS "default" + FROM "syscolumns", "systables", "sysdefaults" + WHERE "syscolumns"."tabid" = "systables"."tabid" + AND "systables"."tabid" = "sysdefaults"."tabid" + AND "syscolumns"."colno" = "sysdefaults"."colno" + AND "systables"."tabtype" = \'T\' + AND LOWER("systables"."owner") = '.$this->escape(strtolower($this->username)).' + AND LOWER("systables"."tabname") = '.$this->escape(strtolower($table)).' + ORDER BY "syscolumns"."colno"'; + + return (($query = $this->query($sql)) !== FALSE) + ? $query->result_object() + : FALSE; } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b