/classphp/flourish/fSchema.php
PHP | 2420 lines | 1527 code | 270 blank | 623 comment | 222 complexity | 57187668bdb93133ef830b98f7478b5e MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- <?php
- /**
- * Gets schema information for the selected database
- *
- * @copyright Copyright (c) 2007-2009 Will Bond
- * @author Will Bond [wb] <will@flourishlib.com>
- * @license http://flourishlib.com/license
- *
- * @package Flourish
- * @link http://flourishlib.com/fSchema
- *
- * @version 1.0.0b25
- * @changes 1.0.0b25 One-to-one relationships utilizing the primary key as a foreign key are now properly detected [wb, 2009-09-22]
- * @changes 1.0.0b24 Fixed MSSQL support to work with ODBC database connections [wb, 2009-09-18]
- * @changes 1.0.0b23 Fixed a bug where one-to-one relationships were being listed as many-to-one [wb, 2009-07-21]
- * @changes 1.0.0b22 PostgreSQL UNIQUE constraints that are created as indexes and not table constraints are now properly detected [wb, 2009-07-08]
- * @changes 1.0.0b21 Added support for the UUID data type in PostgreSQL [wb, 2009-06-18]
- * @changes 1.0.0b20 Add caching of merged info, improved performance of ::getColumnInfo() [wb, 2009-06-15]
- * @changes 1.0.0b19 Fixed a couple of bugs with ::setKeysOverride() [wb, 2009-06-04]
- * @changes 1.0.0b18 Added missing support for MySQL mediumint columns [wb, 2009-05-18]
- * @changes 1.0.0b17 Fixed a bug with ::clearCache() not properly reseting the tables and databases list [wb, 2009-05-13]
- * @changes 1.0.0b16 Backwards Compatibility Break - ::setCacheFile() changed to ::enableCaching() and now requires an fCache object, ::flushInfo() renamed to ::clearCache(), added Oracle support [wb, 2009-05-04]
- * @changes 1.0.0b15 Added support for the three different types of identifier quoting in SQLite [wb, 2009-03-28]
- * @changes 1.0.0b14 Added support for MySQL column definitions containing the COLLATE keyword [wb, 2009-03-28]
- * @changes 1.0.0b13 Fixed a bug with detecting PostgreSQL columns having both a CHECK constraint and a UNIQUE constraint [wb, 2009-02-27]
- * @changes 1.0.0b12 Fixed detection of multi-column primary keys in MySQL [wb, 2009-02-27]
- * @changes 1.0.0b11 Fixed an issue parsing MySQL tables with comments [wb, 2009-02-25]
- * @changes 1.0.0b10 Added the ::getDatabases() method [wb, 2009-02-24]
- * @changes 1.0.0b9 Now detects unsigned and zerofill MySQL data types that do not have a parenthetical part [wb, 2009-02-16]
- * @changes 1.0.0b8 Mapped the MySQL data type `'set'` to `'varchar'`, however valid values are not implemented yet [wb, 2009-02-01]
- * @changes 1.0.0b7 Fixed a bug with detecting MySQL timestamp columns [wb, 2009-01-28]
- * @changes 1.0.0b6 Fixed a bug with detecting MySQL columns that accept `NULL` [wb, 2009-01-19]
- * @changes 1.0.0b5 ::setColumnInfo(): fixed a bug with not grabbing the real database schema first, made general improvements [wb, 2009-01-19]
- * @changes 1.0.0b4 Added support for MySQL binary data types, numeric data type options unsigned and zerofill, and per-column character set definitions [wb, 2009-01-17]
- * @changes 1.0.0b3 Fixed detection of the data type of MySQL timestamp columns, added support for dynamic default date/time values [wb, 2009-01-11]
- * @changes 1.0.0b2 Fixed a bug with detecting multi-column unique keys in MySQL [wb, 2009-01-03]
- * @changes 1.0.0b The initial implementation [wb, 2007-09-25]
- */
- class fSchema
- {
- /**
- * The place to cache to
- *
- * @var fCache
- */
- private $cache = NULL;
-
- /**
- * The cached column info
- *
- * @var array
- */
- private $column_info = array();
-
- /**
- * The column info to override
- *
- * @var array
- */
- private $column_info_override = array();
-
- /**
- * A reference to an instance of the fDatabase class
- *
- * @var fDatabase
- */
- private $database = NULL;
-
- /**
- * The databases on the current database server
- *
- * @var array
- */
- private $databases = NULL;
-
- /**
- * The cached key info
- *
- * @var array
- */
- private $keys = array();
-
- /**
- * The key info to override
- *
- * @var array
- */
- private $keys_override = array();
-
- /**
- * The merged column info
- *
- * @var array
- */
- private $merged_column_info = array();
-
- /**
- * The merged key info
- *
- * @var array
- */
- private $merged_keys = array();
-
- /**
- * The relationships in the database
- *
- * @var array
- */
- private $relationships = array();
-
- /**
- * The tables in the database
- *
- * @var array
- */
- private $tables = NULL;
-
-
- /**
- * Sets the database
- *
- * @param fDatabase $database The fDatabase instance
- * @return fSchema
- */
- public function __construct($database)
- {
- $this->database = $database;
- }
-
-
- /**
- * All requests that hit this method should be requests for callbacks
- *
- * @internal
- *
- * @param string $method The method to create a callback for
- * @return callback The callback for the method requested
- */
- public function __get($method)
- {
- return array($this, $method);
- }
-
-
- /**
- * Checks to see if a column is part of a single-column `UNIQUE` key
- *
- * @param string $table The table the column is located in
- * @param string $column The column to check
- * @return boolean If the column is part of a single-column unique key
- */
- private function checkForSingleColumnUniqueKey($table, $column)
- {
- foreach ($this->merged_keys[$table]['unique'] as $key) {
- if (array($column) == $key) {
- return TRUE;
- }
- }
- if (array($column) == $this->merged_keys[$table]['primary']) {
- return TRUE;
- }
- return FALSE;
- }
-
-
- /**
- * Clears all of the schema info out of the object and, if set, the fCache object
- *
- * @internal
- *
- * @return void
- */
- public function clearCache()
- {
- $this->column_info = array();
- $this->databases = NULL;
- $this->keys = array();
- $this->merged_column_info = array();
- $this->merged_keys = array();
- $this->relationships = array();
- $this->tables = NULL;
- if ($this->cache) {
- $prefix = $this->makeCachePrefix();
- $this->cache->delete($prefix . 'column_info');
- $this->cache->delete($prefix . 'databases');
- $this->cache->delete($prefix . 'keys');
- $this->cache->delete($prefix . 'merged_column_info');
- $this->cache->delete($prefix . 'merged_keys');
- $this->cache->delete($prefix . 'relationships');
- $this->cache->delete($prefix . 'tables');
- }
- }
-
-
- /**
- * Sets the schema to be cached to the fCache object specified
- *
- * @param fCache $cache The cache to cache to
- * @return void
- */
- public function enableCaching($cache)
- {
- $this->cache = $cache;
-
- $prefix = $this->makeCachePrefix();
- $this->column_info = $this->cache->get($prefix . 'column_info', array());
- $this->databases = $this->cache->get($prefix . 'databases', NULL);
- $this->keys = $this->cache->get($prefix . 'keys', array());
-
- if (!$this->column_info_override && !$this->keys_override) {
- $this->merged_column_info = $this->cache->get($prefix . 'merged_column_info', array());
- $this->merged_keys = $this->cache->get($prefix . 'merged_keys', array());
- $this->relationships = $this->cache->get($prefix . 'relationships', array());
- }
-
- $this->tables = $this->cache->get($prefix . 'tables', NULL);
- }
-
-
- /**
- * Gets the column info from the database for later access
- *
- * @param string $table The table to fetch the column info for
- * @return void
- */
- private function fetchColumnInfo($table)
- {
- if (isset($this->column_info[$table])) {
- return;
- }
-
- switch ($this->database->getType()) {
- case 'mssql':
- $column_info = $this->fetchMSSQLColumnInfo($table);
- break;
-
- case 'mysql':
- $column_info = $this->fetchMySQLColumnInfo($table);
- break;
-
- case 'oracle':
- $column_info = $this->fetchOracleColumnInfo($table);
- break;
-
- case 'postgresql':
- $column_info = $this->fetchPostgreSQLColumnInfo($table);
- break;
-
- case 'sqlite':
- $column_info = $this->fetchSQLiteColumnInfo($table);
- break;
- }
-
- if (!$column_info) {
- return;
- }
-
- $this->column_info[$table] = $column_info;
- if ($this->cache) {
- $this->cache->set($this->makeCachePrefix() . 'column_info', $this->column_info);
- }
- }
-
-
- /**
- * Gets the `PRIMARY KEY`, `FOREIGN KEY` and `UNIQUE` key constraints from the database
- *
- * @return void
- */
- private function fetchKeys()
- {
- if ($this->keys) {
- return;
- }
-
- switch ($this->database->getType()) {
- case 'mssql':
- $keys = $this->fetchMSSQLKeys();
- break;
-
- case 'mysql':
- $keys = $this->fetchMySQLKeys();
- break;
-
- case 'oracle':
- $keys = $this->fetchOracleKeys();
- break;
-
- case 'postgresql':
- $keys = $this->fetchPostgreSQLKeys();
- break;
-
- case 'sqlite':
- $keys = $this->fetchSQLiteKeys();
- break;
- }
-
- $this->keys = $keys;
- if ($this->cache) {
- $this->cache->set($this->makeCachePrefix() . 'keys', $this->keys);
- }
- }
-
-
- /**
- * Gets the column info from a MSSQL database
- *
- * The returned array is in the format:
- *
- * {{{
- * array(
- * (string) {column name} => array(
- * 'type' => (string) {data type},
- * 'not_null' => (boolean) {if value can't be null},
- * 'default' => (mixed) {the default value-may contain special string CURRENT_TIMESTAMP},
- * 'valid_values' => (array) {the valid values for a char/varchar field},
- * 'max_length' => (integer) {the maximum length in a char/varchar field},
- * 'decimal_places' => (integer) {the number of decimal places for a decimal/numeric/money/smallmoney field},
- * 'auto_increment' => (boolean) {if the integer primary key column is an identity column}
- * ), ...
- * )
- * }}}
- *
- * @param string $table The table to fetch the column info for
- * @return array The column info for the table specified - see method description for details
- */
- private function fetchMSSQLColumnInfo($table)
- {
- $column_info = array();
-
- $data_type_mapping = array(
- 'bit' => 'boolean',
- 'tinyint' => 'integer',
- 'smallint' => 'integer',
- 'int' => 'integer',
- 'bigint' => 'integer',
- 'datetime' => 'timestamp',
- 'smalldatetime' => 'timestamp',
- 'datetime2' => 'timestamp',
- 'date' => 'date',
- 'time' => 'time',
- 'varchar' => 'varchar',
- 'nvarchar' => 'varchar',
- 'char' => 'char',
- 'nchar' => 'char',
- 'real' => 'float',
- 'float' => 'float',
- 'money' => 'float',
- 'smallmoney' => 'float',
- 'decimal' => 'float',
- 'numeric' => 'float',
- 'binary' => 'blob',
- 'varbinary' => 'blob',
- 'image' => 'blob',
- 'text' => 'text',
- 'ntext' => 'text'
- );
-
- // Get the column info
- $sql = "SELECT
- c.column_name AS 'column',
- c.data_type AS 'type',
- c.is_nullable AS nullable,
- c.column_default AS 'default',
- c.character_maximum_length AS max_length,
- c.numeric_scale AS decimal_places,
- CASE
- WHEN
- COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), c.column_name, 'IsIdentity') = 1 AND
- OBJECTPROPERTY(OBJECT_ID(QUOTENAME(c.table_schema) + '.' + QUOTENAME(c.table_name)), 'IsMSShipped') = 0
- THEN '1'
- ELSE '0'
- END AS auto_increment,
- cc.check_clause AS 'constraint'
- FROM
- INFORMATION_SCHEMA.COLUMNS AS c LEFT JOIN
- INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON c.column_name = ccu.column_name AND c.table_name = ccu.table_name AND c.table_catalog = ccu.table_catalog LEFT JOIN
- INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc ON ccu.constraint_name = cc.constraint_name AND ccu.constraint_catalog = cc.constraint_catalog
- WHERE
- c.table_name = '" . $table . "' AND
- c.table_catalog = DB_NAME()";
- $result = $this->database->query($sql);
-
- foreach ($result as $row) {
-
- $info = array();
-
- foreach ($data_type_mapping as $data_type => $mapped_data_type) {
- if (stripos($row['type'], $data_type) === 0) {
- $info['type'] = $mapped_data_type;
- break;
- }
- }
-
- if (!isset($info['type'])) {
- $info['type'] = $row['type'];
- }
-
- // Handle decimal places for numeric/decimals
- if (in_array($row['type'], array('numeric', 'decimal'))) {
- $info['decimal_places'] = $row['decimal_places'];
- }
-
- // Handle decimal places for money/smallmoney
- if (in_array($row['type'], array('money', 'smallmoney'))) {
- $info['decimal_places'] = 2;
- }
-
- // Handle the special data for varchar columns
- if (in_array($info['type'], array('char', 'varchar'))) {
- $info['max_length'] = $row['max_length'];
- }
-
- // If the column has a constraint, look for valid values
- if (in_array($info['type'], array('char', 'varchar')) && !empty($row['constraint'])) {
- if (preg_match('#^\(((?:(?: OR )?\[[^\]]+\]\s*=\s*\'(?:\'\'|[^\']+)+\')+)\)$#D', $row['constraint'], $matches)) {
- $valid_values = explode(' OR ', $matches[1]);
- foreach ($valid_values as $key => $value) {
- $value = preg_replace('#^\s*\[' . preg_quote($row['column'], '#') . '\]\s*=\s*\'(.*)\'\s*$#', '\1', $value);
- $valid_values[$key] = str_replace("''", "'", $value);
- }
- // SQL Server turns CHECK constraint values into a reversed list, so we fix it here
- $info['valid_values'] = array_reverse($valid_values);
- }
- }
-
- // Handle auto increment
- if ($row['auto_increment']) {
- $info['auto_increment'] = TRUE;
- }
-
- // Handle default values
- if ($row['default'] !== NULL) {
- if ($row['default'] == '(getdate())') {
- $info['default'] = 'CURRENT_TIMESTAMP';
- } elseif (in_array($info['type'], array('char', 'varchar', 'text', 'timestamp')) ) {
- $info['default'] = substr($row['default'], 2, -2);
- } elseif ($info['type'] == 'boolean') {
- $info['default'] = (boolean) substr($row['default'], 2, -2);
- } elseif (in_array($info['type'], array('integer', 'float')) ) {
- $info['default'] = str_replace(array('(', ')'), '', $row['default']);
- } else {
- $info['default'] = pack('H*', substr($row['default'], 3, -1));
- }
- }
-
- // Handle not null
- $info['not_null'] = ($row['nullable'] == 'NO') ? TRUE : FALSE;
-
- $column_info[$row['column']] = $info;
- }
-
- return $column_info;
- }
-
-
- /**
- * Fetches the key info for an MSSQL database
- *
- * The structure of the returned array is:
- *
- * {{{
- * array(
- * 'primary' => array(
- * {column name}, ...
- * ),
- * 'unique' => array(
- * array(
- * {column name}, ...
- * ), ...
- * ),
- * 'foreign' => array(
- * array(
- * 'column' => {column name},
- * 'foreign_table' => {foreign table name},
- * 'foreign_column' => {foreign column name},
- * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
- * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
- * ), ...
- * )
- * )
- * }}}
- *
- * @return array The key info arrays for every table in the database - see method description for details
- */
- private function fetchMSSQLKeys()
- {
- $keys = array();
-
- $tables = $this->getTables();
- foreach ($tables as $table) {
- $keys[$table] = array();
- $keys[$table]['primary'] = array();
- $keys[$table]['unique'] = array();
- $keys[$table]['foreign'] = array();
- }
-
- $sql = "SELECT
- c.table_name AS 'table',
- kcu.constraint_name AS constraint_name,
- CASE c.constraint_type
- WHEN 'PRIMARY KEY' THEN 'primary'
- WHEN 'FOREIGN KEY' THEN 'foreign'
- WHEN 'UNIQUE' THEN 'unique'
- END AS 'type',
- kcu.column_name AS 'column',
- ccu.table_name AS foreign_table,
- ccu.column_name AS foreign_column,
- REPLACE(LOWER(rc.delete_rule), ' ', '_') AS on_delete,
- REPLACE(LOWER(rc.update_rule), ' ', '_') AS on_update
- FROM
- INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON c.table_name = kcu.table_name AND c.constraint_name = kcu.constraint_name LEFT JOIN
- INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON c.constraint_name = rc.constraint_name LEFT JOIN
- INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.constraint_name = rc.unique_constraint_name
- WHERE
- c.constraint_catalog = DB_NAME() AND
- c.table_name != 'sysdiagrams'
- ORDER BY
- LOWER(c.table_name),
- c.constraint_type,
- LOWER(kcu.constraint_name),
- kcu.ordinal_position,
- LOWER(kcu.column_name)";
-
- $result = $this->database->query($sql);
-
- $last_name = '';
- $last_table = '';
- $last_type = '';
- foreach ($result as $row) {
-
- if ($row['constraint_name'] != $last_name) {
-
- if ($last_name) {
- if ($last_type == 'foreign' || $last_type == 'unique') {
- if (!isset($keys[$last_table][$last_type])) {
- $keys[$last_table][$last_type] = array();
- }
- $keys[$last_table][$last_type][] = $temp;
- } else {
- $keys[$last_table][$last_type] = $temp;
- }
- }
-
- $temp = array();
- if ($row['type'] == 'foreign') {
-
- $temp['column'] = $row['column'];
- $temp['foreign_table'] = $row['foreign_table'];
- $temp['foreign_column'] = $row['foreign_column'];
- $temp['on_delete'] = 'no_action';
- $temp['on_update'] = 'no_action';
- if (!empty($row['on_delete'])) {
- $temp['on_delete'] = $row['on_delete'];
- }
- if (!empty($row['on_update'])) {
- $temp['on_update'] = $row['on_update'];
- }
-
- } else {
- $temp[] = $row['column'];
- }
-
- $last_table = $row['table'];
- $last_name = $row['constraint_name'];
- $last_type = $row['type'];
-
- } else {
- $temp[] = $row['column'];
- }
- }
-
- if (isset($temp)) {
- if ($last_type == 'foreign') {
- if (!isset($keys[$last_table][$last_type])) {
- $keys[$last_table][$last_type] = array();
- }
- $keys[$last_table][$last_type][] = $temp;
- } else {
- $keys[$last_table][$last_type] = $temp;
- }
- }
-
- return $keys;
- }
-
-
- /**
- * Gets the column info from a MySQL database
- *
- * The returned array is in the format:
- *
- * {{{
- * array(
- * (string) {column name} => array(
- * 'type' => (string) {data type},
- * 'not_null' => (boolean) {if value can't be null},
- * 'default' => (mixed) {the default value-may contain special string CURRENT_TIMESTAMP},
- * 'valid_values' => (array) {the valid values for a char/varchar field},
- * 'max_length' => (integer) {the maximum length in a char/varchar field},
- * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
- * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
- * ), ...
- * )
- * }}}
- *
- * @param string $table The table to fetch the column info for
- * @return array The column info for the table specified - see method description for details
- */
- private function fetchMySQLColumnInfo($table)
- {
- $data_type_mapping = array(
- 'tinyint' => 'integer',
- 'smallint' => 'integer',
- 'mediumint' => 'integer',
- 'int' => 'integer',
- 'bigint' => 'integer',
- 'datetime' => 'timestamp',
- 'timestamp' => 'timestamp',
- 'date' => 'date',
- 'time' => 'time',
- 'enum' => 'varchar',
- 'set' => 'varchar',
- 'varchar' => 'varchar',
- 'char' => 'char',
- 'float' => 'float',
- 'double' => 'float',
- 'decimal' => 'float',
- 'binary' => 'blob',
- 'varbinary' => 'blob',
- 'tinyblob' => 'blob',
- 'blob' => 'blob',
- 'mediumblob' => 'blob',
- 'longblob' => 'blob',
- 'tinytext' => 'text',
- 'text' => 'text',
- 'mediumtext' => 'text',
- 'longtext' => 'text'
- );
-
- $column_info = array();
-
- $result = $this->database->query('SHOW CREATE TABLE ' . $table);
-
- try {
- $row = $result->fetchRow();
- $create_sql = $row['Create Table'];
- } catch (fNoRowsException $e) {
- return array();
- }
-
- preg_match_all('#(?<=,|\()\s+(?:"|\`)(\w+)(?:"|\`)\s+(?:([a-z]+)(?:\(([^)]+)\))?(?: unsigned| zerofill){0,2})(?: character set [^ ]+)?(?: collate [^ ]+)?(?: NULL)?( NOT NULL)?(?: DEFAULT ((?:[^, \']*|\'(?:\'\'|[^\']+)*\')))?( auto_increment)?( COMMENT \'(?:\'\'|[^\']+)*\')?( ON UPDATE CURRENT_TIMESTAMP)?\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
-
- foreach ($matches as $match) {
-
- $info = array();
-
- foreach ($data_type_mapping as $data_type => $mapped_data_type) {
- if (stripos($match[2], $data_type) === 0) {
- if ($match[2] == 'tinyint' && $match[3] == 1) {
- $mapped_data_type = 'boolean';
- }
-
- $info['type'] = $mapped_data_type;
- break;
- }
- }
- if (!isset($info['type'])) {
- $info['type'] = preg_replace('#^([a-z ]+).*$#iD', '\1', $match[2]);
- }
-
- if (stripos($match[2], 'enum') === 0) {
- $info['valid_values'] = preg_replace("/^'|'\$/D", '', explode(",", $match[3]));
- $match[3] = 0;
- foreach ($info['valid_values'] as $valid_value) {
- if (strlen(utf8_decode($valid_value)) > $match[3]) {
- $match[3] = strlen(utf8_decode($valid_value));
- }
- }
- }
-
- // The set data type is currently only supported as a varchar
- // with a max length of all valid values concatenated by ,s
- if (stripos($match[2], 'set') === 0) {
- $values = preg_replace("/^'|'\$/D", '', explode(",", $match[3]));
- $match[3] = strlen(join(',', $values));
- }
-
- // Type specific information
- if (in_array($info['type'], array('char', 'varchar'))) {
- $info['max_length'] = $match[3];
- }
-
- // Grab the number of decimal places
- if (stripos($match[2], 'decimal') === 0) {
- if (preg_match('#^\s*\d+\s*,\s*(\d+)\s*$#D', $match[3], $data_type_info)) {
- $info['decimal_places'] = $data_type_info[1];
- }
- }
-
- // Not null
- $info['not_null'] = (!empty($match[4])) ? TRUE : FALSE;
-
- // Default values
- if (!empty($match[5]) && $match[5] != 'NULL') {
- $info['default'] = preg_replace("/^'|'\$/D", '', $match[5]);
- }
-
- if ($info['type'] == 'boolean' && isset($info['default'])) {
- $info['default'] = (boolean) $info['default'];
- }
-
- // Auto increment fields
- if (!empty($match[6])) {
- $info['auto_increment'] = TRUE;
- }
-
- $column_info[$match[1]] = $info;
- }
-
- return $column_info;
- }
-
-
- /**
- * Fetches the keys for a MySQL database
- *
- * The structure of the returned array is:
- *
- * {{{
- * array(
- * 'primary' => array(
- * {column name}, ...
- * ),
- * 'unique' => array(
- * array(
- * {column name}, ...
- * ), ...
- * ),
- * 'foreign' => array(
- * array(
- * 'column' => {column name},
- * 'foreign_table' => {foreign table name},
- * 'foreign_column' => {foreign column name},
- * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
- * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
- * ), ...
- * )
- * )
- * }}}
- *
- * @return array The keys arrays for every table in the database - see method description for details
- */
- private function fetchMySQLKeys()
- {
- $tables = $this->getTables();
- $keys = array();
-
- foreach ($tables as $table) {
-
- $keys[$table] = array();
- $keys[$table]['primary'] = array();
- $keys[$table]['foreign'] = array();
- $keys[$table]['unique'] = array();
-
- $result = $this->database->query('SHOW CREATE TABLE `' . substr($this->database->escape('string', $table), 1, -1) . '`');
- $row = $result->fetchRow();
-
- // Primary keys
- preg_match_all('/PRIMARY KEY\s+\("(.*?)"\),?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);
- if (!empty($matches)) {
- $keys[$table]['primary'] = explode('","', $matches[0][1]);
- }
-
- // Unique keys
- preg_match_all('/UNIQUE KEY\s+"([^"]+)"\s+\("(.*?)"\),?\n/U', $row['Create Table'], $matches, PREG_SET_ORDER);
- foreach ($matches as $match) {
- $keys[$table]['unique'][] = explode('","', $match[2]);
- }
-
- // Foreign keys
- preg_match_all('#FOREIGN KEY \("([^"]+)"\) REFERENCES "([^"]+)" \("([^"]+)"\)(?:\sON\sDELETE\s(SET\sNULL|SET\sDEFAULT|CASCADE|NO\sACTION|RESTRICT))?(?:\sON\sUPDATE\s(SET\sNULL|SET\sDEFAULT|CASCADE|NO\sACTION|RESTRICT))?#', $row['Create Table'], $matches, PREG_SET_ORDER);
- foreach ($matches as $match) {
- $temp = array('column' => $match[1],
- 'foreign_table' => $match[2],
- 'foreign_column' => $match[3],
- 'on_delete' => 'no_action',
- 'on_update' => 'no_action');
- if (isset($match[4])) {
- $temp['on_delete'] = strtolower(str_replace(' ', '_', $match[4]));
- }
- if (isset($match[5])) {
- $temp['on_update'] = strtolower(str_replace(' ', '_', $match[5]));
- }
- $keys[$table]['foreign'][] = $temp;
- }
- }
-
- return $keys;
- }
-
-
- /**
- * Gets the column info from an Oracle database
- *
- * The returned array is in the format:
- *
- * {{{
- * array(
- * (string) {column name} => array(
- * 'type' => (string) {data type},
- * 'not_null' => (boolean) {if value can't be null},
- * 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
- * 'valid_values' => (array) {the valid values for a char/varchar field},
- * 'max_length' => (integer) {the maximum length in a char/varchar field},
- * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
- * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
- * ), ...
- * )
- * }}}
- *
- * @param string $table The table to fetch the column info for
- * @return array The column info for the table specified - see method description for details
- */
- private function fetchOracleColumnInfo($table)
- {
- $table = strtoupper($table);
-
- $column_info = array();
-
- $data_type_mapping = array(
- 'boolean' => 'boolean',
- 'integer' => 'integer',
- 'timestamp' => 'timestamp',
- 'date' => 'date',
- 'varchar2' => 'varchar',
- 'nvarchar2' => 'varchar',
- 'char' => 'char',
- 'nchar' => 'char',
- 'float' => 'float',
- 'binary_float' => 'float',
- 'binary_double' => 'float',
- 'blob' => 'blob',
- 'bfile' => 'varchar',
- 'clob' => 'text',
- 'nclob' => 'text'
- );
-
- $sql = "SELECT
- LOWER(UTC.COLUMN_NAME) COLUMN_NAME,
- CASE
- WHEN
- UTC.DATA_TYPE = 'NUMBER' AND
- UTC.DATA_PRECISION IS NULL AND
- UTC.DATA_SCALE = 0
- THEN
- 'integer'
- WHEN
- UTC.DATA_TYPE = 'NUMBER' AND
- UTC.DATA_PRECISION = 1 AND
- UTC.DATA_SCALE = 0
- THEN
- 'boolean'
- WHEN
- UTC.DATA_TYPE = 'NUMBER' AND
- UTC.DATA_PRECISION IS NOT NULL AND
- UTC.DATA_SCALE != 0 AND
- UTC.DATA_SCALE IS NOT NULL
- THEN
- 'float'
- ELSE
- LOWER(UTC.DATA_TYPE)
- END DATA_TYPE,
- CASE
- WHEN
- UTC.CHAR_LENGTH <> 0
- THEN
- UTC.CHAR_LENGTH
- WHEN
- UTC.DATA_TYPE = 'NUMBER' AND
- UTC.DATA_PRECISION != 1 AND
- UTC.DATA_SCALE != 0 AND
- UTC.DATA_PRECISION IS NOT NULL
- THEN
- UTC.DATA_SCALE
- ELSE
- NULL
- END LENGTH,
- UTC.NULLABLE,
- UTC.DATA_DEFAULT,
- UC.SEARCH_CONDITION CHECK_CONSTRAINT
- FROM
- USER_TAB_COLUMNS UTC LEFT JOIN
- USER_CONS_COLUMNS UCC ON
- UTC.COLUMN_NAME = UCC.COLUMN_NAME AND
- UTC.TABLE_NAME = UCC.TABLE_NAME AND
- UCC.POSITION IS NULL LEFT JOIN
- USER_CONSTRAINTS UC ON
- UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND
- UC.CONSTRAINT_TYPE = 'C' AND
- UC.STATUS = 'ENABLED'
- WHERE
- UTC.TABLE_NAME = %s
- ORDER BY
- UTC.TABLE_NAME ASC,
- UTC.COLUMN_ID ASC";
- $result = $this->database->query($sql, $table);
-
- foreach ($result as $row) {
-
- $column = $row['column_name'];
-
- // Since Oracle stores check constraints in LONG columns, it is
- // not possible to check or modify the constraints in SQL which
- // ends up causing multiple rows with duplicate data except for
- // the check constraint
- $duplicate = FALSE;
-
- if (isset($column_info[$column])) {
- $info = $column_info[$column];
- $duplicate = TRUE;
- } else {
- $info = array();
- }
-
- if (!$duplicate) {
- // Get the column type
- foreach ($data_type_mapping as $data_type => $mapped_data_type) {
- if (stripos($row['data_type'], $data_type) === 0) {
- $info['type'] = $mapped_data_type;
- break;
- }
- }
-
- if (!isset($info['type'])) {
- $info['type'] = $row['data_type'];
- }
-
- // Handle the length of decimal/numeric fields
- if ($info['type'] == 'float' && $row['length']) {
- $info['decimal_places'] = (int) $row['length'];
- }
-
- // Handle the special data for varchar fields
- if (in_array($info['type'], array('char', 'varchar'))) {
- $info['max_length'] = (int) $row['length'];
- }
- }
-
- // Handle check constraints that are just simple lists
- if (in_array($info['type'], array('varchar', 'char')) && $row['check_constraint']) {
- if (preg_match('/^\s*' . preg_quote($column, '/') . '\s+in\s+\((.*?)\)\s*$/i', $row['check_constraint'], $match)) {
- if (preg_match_all("/(?<!')'((''|[^']+)*)'/", $match[1], $matches, PREG_PATTERN_ORDER)) {
- $info['valid_values'] = str_replace("''", "'", $matches[1]);
- }
- }
- }
-
- if (!$duplicate) {
- // Handle default values
- if ($row['data_default'] !== NULL) {
- if (in_array($info['type'], array('char', 'varchar', 'text'))) {
- $info['default'] = str_replace("''", "'", substr(trim($row['data_default']), 1, -1));
-
- } elseif ($info['type'] == 'boolean') {
- $info['default'] = (boolean) trim($row['data_default']);
-
- } elseif (in_array($info['type'], array('integer', 'float'))) {
- $info['default'] = trim($row['data_default']);
-
- } else {
- $info['default'] = $row['data_default'];
- }
- }
-
- // Not null values
- $info['not_null'] = ($row['nullable'] == 'N') ? TRUE : FALSE;
- }
-
- $column_info[$column] = $info;
- }
-
- $sql = "SELECT
- TRIGGER_BODY
- FROM
- USER_TRIGGERS
- WHERE
- TRIGGERING_EVENT = 'INSERT' AND
- STATUS = 'ENABLED' AND
- TRIGGER_NAME NOT LIKE 'BIN\$%' AND
- TABLE_NAME = %s";
-
- foreach ($this->database->query($sql, $table) as $row) {
- if (preg_match('#SELECT\s+(\w+).nextval\s+INTO\s+:new\.(\w+)\s+FROM\s+dual#i', $row['trigger_body'], $matches)) {
- $column = strtolower($matches[2]);
- $column_info[$column]['auto_increment'] = TRUE;
- }
- }
-
- return $column_info;
- }
-
-
- /**
- * Fetches the key info for an Oracle database
- *
- * The structure of the returned array is:
- *
- * {{{
- * array(
- * 'primary' => array(
- * {column name}, ...
- * ),
- * 'unique' => array(
- * array(
- * {column name}, ...
- * ), ...
- * ),
- * 'foreign' => array(
- * array(
- * 'column' => {column name},
- * 'foreign_table' => {foreign table name},
- * 'foreign_column' => {foreign column name},
- * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
- * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
- * ), ...
- * )
- * )
- * }}}
- *
- * @return array The keys arrays for every table in the database - see method description for details
- */
- private function fetchOracleKeys()
- {
- $keys = array();
-
- $tables = $this->getTables();
- foreach ($tables as $table) {
- $keys[$table] = array();
- $keys[$table]['primary'] = array();
- $keys[$table]['unique'] = array();
- $keys[$table]['foreign'] = array();
- }
-
- $sql = "SELECT
- LOWER(UC.TABLE_NAME) \"TABLE\",
- UC.CONSTRAINT_NAME CONSTRAINT_NAME,
- CASE UC.CONSTRAINT_TYPE
- WHEN 'P' THEN 'primary'
- WHEN 'R' THEN 'foreign'
- WHEN 'U' THEN 'unique'
- END TYPE,
- LOWER(UCC.COLUMN_NAME) \"COLUMN\",
- LOWER(FKC.TABLE_NAME) FOREIGN_TABLE,
- LOWER(FKC.COLUMN_NAME) FOREIGN_COLUMN,
- CASE WHEN FKC.TABLE_NAME IS NOT NULL THEN REPLACE(LOWER(UC.DELETE_RULE), ' ', '_') ELSE NULL END ON_DELETE
- FROM
- USER_CONSTRAINTS UC INNER JOIN
- USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME LEFT JOIN
- USER_CONSTRAINTS FK ON UC.R_CONSTRAINT_NAME = FK.CONSTRAINT_NAME LEFT JOIN
- USER_CONS_COLUMNS FKC ON FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME
- WHERE
- UC.CONSTRAINT_TYPE IN ('U', 'P', 'R') AND
- UC.STATUS = 'ENABLED' AND
- SUBSTR(UC.TABLE_NAME, 1, 4) <> 'BIN\$'
- ORDER BY
- UC.TABLE_NAME ASC,
- UC.CONSTRAINT_TYPE ASC,
- UC.CONSTRAINT_NAME ASC,
- UCC.POSITION ASC";
-
- $result = $this->database->query($sql);
-
- $last_name = '';
- $last_table = '';
- $last_type = '';
- foreach ($result as $row) {
-
- if ($row['constraint_name'] != $last_name) {
-
- if ($last_name) {
- if ($last_type == 'foreign' || $last_type == 'unique') {
- $keys[$last_table][$last_type][] = $temp;
- } else {
- $keys[$last_table][$last_type] = $temp;
- }
- }
-
- $temp = array();
- if ($row['type'] == 'foreign') {
-
- $temp['column'] = $row['column'];
- $temp['foreign_table'] = $row['foreign_table'];
- $temp['foreign_column'] = $row['foreign_column'];
- $temp['on_delete'] = 'no_action';
- $temp['on_update'] = 'no_action';
-
- if (!empty($row['on_delete'])) {
- $temp['on_delete'] = $row['on_delete'];
- }
-
- } else {
- $temp[] = $row['column'];
- }
-
- $last_table = $row['table'];
- $last_name = $row['constraint_name'];
- $last_type = $row['type'];
-
- } else {
- $temp[] = $row['column'];
- }
- }
-
- if (isset($temp)) {
- if ($last_type == 'foreign' || $last_type == 'unique') {
- $keys[$last_table][$last_type][] = $temp;
- } else {
- $keys[$last_table][$last_type] = $temp;
- }
- }
-
- return $keys;
- }
-
-
- /**
- * Gets the column info from a PostgreSQL database
- *
- * The returned array is in the format:
- *
- * {{{
- * array(
- * (string) {column name} => array(
- * 'type' => (string) {data type},
- * 'not_null' => (boolean) {if value can't be null},
- * 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
- * 'valid_values' => (array) {the valid values for a char/varchar field},
- * 'max_length' => (integer) {the maximum length in a char/varchar field},
- * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
- * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
- * ), ...
- * )
- * }}}
- *
- * @param string $table The table to fetch the column info for
- * @return array The column info for the table specified - see method description for details
- */
- private function fetchPostgreSQLColumnInfo($table)
- {
- $column_info = array();
-
- $data_type_mapping = array(
- 'boolean' => 'boolean',
- 'smallint' => 'integer',
- 'int' => 'integer',
- 'bigint' => 'integer',
- 'serial' => 'integer',
- 'bigserial' => 'integer',
- 'timestamp' => 'timestamp',
- 'date' => 'date',
- 'time' => 'time',
- 'uuid' => 'varchar',
- 'character varying' => 'varchar',
- 'character' => 'char',
- 'real' => 'float',
- 'double' => 'float',
- 'numeric' => 'float',
- 'bytea' => 'blob',
- 'text' => 'text',
- 'mediumtext' => 'text',
- 'longtext' => 'text'
- );
-
- // PgSQL required this complicated SQL to get the column info
- $sql = "SELECT
- pg_attribute.attname AS column,
- format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type,
- pg_attribute.attnotnull AS not_null,
- pg_attrdef.adsrc AS default,
- pg_get_constraintdef(pg_constraint.oid) AS constraint
- FROM
- pg_attribute LEFT JOIN
- pg_class ON pg_attribute.attrelid = pg_class.oid LEFT JOIN
- pg_type ON pg_type.oid = pg_attribute.atttypid LEFT JOIN
- pg_constraint ON pg_constraint.conrelid = pg_class.oid AND
- pg_attribute.attnum = ANY (pg_constraint.conkey) AND
- pg_constraint.contype = 'c' LEFT JOIN
- pg_attrdef ON pg_class.oid = pg_attrdef.adrelid AND
- pg_attribute.attnum = pg_attrdef.adnum
- WHERE
- NOT pg_attribute.attisdropped AND
- pg_class.relname = %s AND
- pg_type.typname NOT IN ('oid', 'cid', 'xid', 'cid', 'xid', 'tid')
- ORDER BY
- pg_attribute.attnum,
- pg_constraint.contype";
- $result = $this->database->query($sql, $table);
-
- foreach ($result as $row) {
-
- $info = array();
-
- // Get the column type
- preg_match('#([\w ]+)\s*(?:\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\))?#', $row['data_type'], $column_data_type);
-
- foreach ($data_type_mapping as $data_type => $mapped_data_type) {
- if (stripos($column_data_type[1], $data_type) === 0) {
- $info['type'] = $mapped_data_type;
- break;
- }
- }
-
- if (!isset($info['type'])) {
- $info['type'] = $column_data_type[1];
- }
-
- // Handle the length of decimal/numeric fields
- if ($info['type'] == 'float' && isset($column_data_type[3]) && strlen($column_data_type[3]) > 0) {
- $info['decimal_places'] = (int) $column_data_type[3];
- }
-
- // Handle the special data for varchar fields
- if (in_array($info['type'], array('char', 'varchar')) && !empty($column_data_type[2])) {
- $info['max_length'] = $column_data_type[2];
- }
-
- // In PostgreSQL, a UUID can be the 32 digits, 32 digits plus 4 hyphens or 32 digits plus 4 hyphens and 2 curly braces
- if ($row['data_type'] == 'uuid') {
- $info['max_length'] = 38;
- }
-
- // Handle check constraints that are just simple lists
- if (in_array($info['type'], array('varchar', 'char')) && !empty($row['constraint'])) {
- if (preg_match('/CHECK[\( "]+' . $row['column'] . '[a-z\) ":]+\s+=\s+/i', $row['constraint'])) {
- if (preg_match_all("/(?!').'((''|[^']+)*)'/", $row['constraint'], $matches, PREG_PATTERN_ORDER)) {
- $info['valid_values'] = str_replace("''", "'", $matches[1]);
- }
- }
- }
-
- // Handle default values and serial data types
- if ($info['type'] == 'integer' && stripos($row['default'], 'nextval(') !== FALSE) {
- $info['auto_increment'] = TRUE;
-
- } elseif ($row['default'] !== NULL) {
- if ($row['default'] == 'now()') {
- $info['default'] = 'CURRENT_TIMESTAMP';
- } elseif ($row['default'] == "('now'::text)::date") {
- $info['default'] = 'CURRENT_DATE';
- } elseif ($row['default'] == "('now'::text)::time with time zone") {
- $info['default'] = 'CURRENT_TIME';
- } else {
- $info['default'] = str_replace("''", "'", preg_replace("/^'(.*)'::[a-z ]+\$/iD", '\1', $row['default']));
- if ($info['type'] == 'boolean') {
- $info['default'] = ($info['default'] == 'false' || !$info['default']) ? FALSE : TRUE;
- }
- }
- }
-
- // Not null values
- $info['not_null'] = ($row['not_null'] == 't') ? TRUE : FALSE;
-
- $column_info[$row['column']] = $info;
- }
-
- return $column_info;
- }
-
-
- /**
- * Fetches the key info for a PostgreSQL database
- *
- * The structure of the returned array is:
- *
- * {{{
- * array(
- * 'primary' => array(
- * {column name}, ...
- * ),
- * 'unique' => array(
- * array(
- * {column name}, ...
- * ), ...
- * ),
- * 'foreign' => array(
- * array(
- * 'column' => {column name},
- * 'foreign_table' => {foreign table name},
- * 'foreign_column' => {foreign column name},
- * 'on_delete' => {the ON DELETE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'},
- * 'on_update' => {the ON UPDATE action: 'no_action', 'restrict', 'cascade', 'set_null', or 'set_default'}
- * ), ...
- * )
- * )
- * }}}
- *
- * @return array The keys arrays for every table in the database - see method description for details
- */
- private function fetchPostgreSQLKeys()
- {
- $keys = array();
-
- $tables = $this->getTables();
- foreach ($tables as $table) {
- $keys[$table] = array();
- $keys[$table]['primary'] = array();
- $keys[$table]['unique'] = array();
- $keys[$table]['foreign'] = array();
- }
-
- $sql = "(
- SELECT
- t.relname AS table,
- con.conname AS constraint_name,
- CASE con.contype
- WHEN 'f' THEN 'foreign'
- WHEN 'p' THEN 'primary'
- WHEN 'u' THEN 'unique'
- END AS type,
- col.attname AS column,
- ft.relname AS foreign_table,
- fc.attname AS foreign_column,
- CASE con.confdeltype
- WHEN 'c' THEN 'cascade'
- WHEN 'a' THEN 'no_action'
- WHEN 'r' THEN 'restrict'
- WHEN 'n' THEN 'set_null'
- WHEN 'd' THEN 'set_default'
- END AS on_delete,
- CASE con.confupdtype
- WHEN 'c' THEN 'cascade'
- WHEN 'a' THEN 'no_action'
- WHEN 'r' THEN 'restrict'
- WHEN 'n' THEN 'set_null'
- WHEN 'd' THEN 'set_default'
- END AS on_update,
- CASE WHEN con.conkey IS NOT NULL THEN position('-'||col.attnum||'-' in '-'||array_to_string(con.conkey, '-')||'-') ELSE 0 END AS column_order
- FROM
- pg_attribute AS col INNER JOIN
- pg_class AS t ON col.attrelid = t.oid INNER JOIN
- pg_constraint AS con ON (col.attnum = ANY (con.conkey) AND
- con.conrelid = t.oid) LEFT JOIN
- pg_class AS ft ON con.confrelid = ft.oid LEFT JOIN
- pg_attribute AS fc ON (fc.attnum = ANY (con.confkey) AND
- ft.oid = fc.attrelid)
- WHERE
- NOT col.attisdropped AND
- (con.contype = 'p' OR
- con.contype = 'f' OR
- con.contype = 'u')
- ) UNION (
- SELECT
- t.relname AS table,
- ic.relname AS constraint_name,
- 'unique' AS type,
- col.attname AS column,
- NULL AS foreign_table,
- NULL AS foreign_column,
- NULL AS on_delete,
- NULL AS on_update,
- CASE WHEN ind.indkey IS NOT NULL THEN position('-'||col.attnum||'-' in '-'||array_to_string(ind.indkey, '-')||'-') ELSE 0 END AS column_order
- FROM
- pg_class AS t INNER JOIN
- pg_index AS ind ON ind.indrelid = t.oid INNER JOIN
- pg_namespace AS n ON t.relnamespace = n.oid INNER JOIN
- pg_class AS ic ON ind.indexrelid = ic.oid LEFT JOIN
- pg_constraint AS con ON con.conrelid = t.oid AND con.contype = 'u' AND con.conname = ic.relname INNER JOIN
- pg_attribute AS col ON col.attrelid = t.oid AND col.attnum = ANY (ind.indkey)
- WHERE
- n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
- indisunique = TRUE AND
- indisprimary = FALSE AND
- con.oid IS NULL
- ) ORDER BY 1, 3, 2, 9";
-
- $result = $this->database->query($sql);
-
- $last_name = '';
- $last_table = '';
- $last_type = '';
- foreach ($result as $row) {
-
- if ($row['constraint_name'] != $last_name) {
-
- if ($last_name) {
- if ($last_type == 'foreign' || $last_type == 'unique') {
- $keys[$last_table][$last_type][] = $temp;
- } else {
- $keys[$last_table][$last_type] = $temp;
- }
- }
-
- $temp = array();
- if ($row['type'] == 'foreign') {
-
- $temp['column'] = $row['column'];
- $temp['foreign_table'] = $row['foreign_table'];
- $temp['foreign_column'] = $row['foreign_column'];
- $temp['on_delete'] = 'no_action';
- $temp['on_update'] = 'no_action';
-
- if (!empty($row['on_delete'])) {
- $temp['on_delete'] = $row['on_delete'];
- }
-
- if (!empty($row['on_update'])) {
- $temp['on_update'] = $row['on_update'];
- }
-
- } else {
- $temp[] = $row['column'];
- }
-
- $last_table = $row['table'];
- $last_name = $row['constraint_name'];
- $last_type = $row['type'];
-
- } else {
- $temp[] = $row['column'];
- }
- }
-
- if (isset($temp)) {
- if ($last_type == 'foreign' || $last_type == 'unique') {
- $keys[$last_table][$last_type][] = $temp;
- } else {
- $keys[$last_table][$last_type] = $temp;
- }
- }
-
- return $keys;
- }
-
-
- /**
- * Gets the column info from a SQLite database
- *
- * The returned array is in the format:
- *
- * {{{
- * array(
- * (string) {column name} => array(
- * 'type' => (string) {data type},
- * 'not_null' => (boolean) {if value can't be null},
- * 'default' => (mixed) {the default value-may contain special strings CURRENT_TIMESTAMP, CURRENT_TIME or CURRENT_DATE},
- * 'valid_values' => (array) {the valid values for a char/varchar field},
- * 'max_length' => (integer) {the maximum length in a char/varchar field},
- * 'decimal_places' => (integer) {the number of decimal places for a decimal field},
- * 'auto_increment' => (boolean) {if the integer primary key column is auto_increment}
- * ), ...
- * )
- * }}}
- *
- * @param string $table The table to fetch the column info for
- * @return array The column info for the table specified - see method description for details
- */
- private function fetchSQLiteColumnInfo($table)
- {
- $column_info = array();
-
- $data_type_mapping = array(
- 'boolean' => 'boolean',
- 'serial' => 'integer',
- 'smallint' => 'integer',
- 'int' => 'integer',
- 'integer' => 'integer',
- 'bigint' => 'integer',
- 'timestamp' => 'timestamp',
- 'date' => 'date',
- 'time' => 'time',
- 'varchar' => 'varchar',
- 'char' => 'char',
- 'real' => 'float',
- 'numeric' => 'float',
- 'float' => 'float',
- 'double' => 'float',
- 'decimal' => 'float',
- 'blob' => 'blob',
- 'text' => 'text'
- );
-
- $result = $this->database->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = %s", $table);
-
- try {
- $row = $result->fetchRow();
- $create_sql = $row['sql'];
- } catch (fNoRowsException $e) {
- return array();
- }
-
- preg_match_all('#(?<=,|\()\s*(?:`|"|\[)?(\w+)(?:`|"|\])?\s+([a-z]+)(?:\(\s*(\d+)(?:\s*,\s*(\d+))?\s*\))?(?:(\s+NOT\s+NULL)|(?:\s+NULL)|(?:\s+DEFAULT\s+([^, \']*|\'(?:\'\'|[^\']+)*\'))|(\s+UNIQUE)|(\s+PRIMARY\s+KEY(?:\s+AUTOINCREMENT)?)|(\s+CHECK\s*\(\w+\s+IN\s+\(\s*(?:(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\s*,\s*)*\s*(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\)\)))*(\s+REFERENCES\s+\w+\s*\(\s*\w+\s*\)\s*(?:\s+(?:ON\s+DELETE|ON\s+UPDATE)\s+(?:CASCADE|NO\s+ACTION|RESTRICT|SET\s+NULL|SET\s+DEFAULT))*(?:\s+(?:DEFERRABLE|NOT\s+DEFERRABLE))?)?\s*(?:,|\s*(?=\)))#mi', $create_sql, $matches, PREG_SET_ORDER);
-
- foreach ($matches as $match) {
- $info = array();
-
- foreach ($data_type_mapping as $data_type => $mapped_data_type) {
- if (stripos($match[2], $data_type) === 0) {
- $info['type'] = $mapped_data_type;
- break;
- }
- }
-
- // Type specific information
- if (in_array($info['type'], array('char', 'varchar')) && !empty($match[3])) {
- $info['max_length'] = $match[3];
- }
-
- // Figure out how many decimal places for a decimal
- if (in_array(strtolower($match[2]), array('decimal', 'numeric')) && !empty($match[4])) {
- $info['decimal_places'] = $match[4];
- }
-
- // Not null
- $info['n…
Large files files are truncated, but you can click here to view the full file