summaryrefslogtreecommitdiff
path: root/lib/mysqlschema.php
diff options
context:
space:
mode:
Diffstat (limited to 'lib/mysqlschema.php')
-rw-r--r--lib/mysqlschema.php567
1 files changed, 213 insertions, 354 deletions
diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php
index f9552c1dc..eeabae8cd 100644
--- a/lib/mysqlschema.php
+++ b/lib/mysqlschema.php
@@ -72,72 +72,126 @@ class MysqlSchema extends Schema
*
* Throws an exception if the table is not found.
*
- * @param string $name Name of the table to get
+ * @param string $table Name of the table to get
*
* @return TableDef tabledef for that table.
* @throws SchemaTableMissingException
*/
- public function getTableDef($name)
+ public function getTableDef($table)
{
- $query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS " .
- "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'";
- $schema = $this->conn->dsn['database'];
- $sql = sprintf($query, $schema, $name);
- $res = $this->conn->query($sql);
+ $def = array();
+ $hasKeys = false;
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
- if ($res->numRows() == 0) {
- $res->free();
- throw new SchemaTableMissingException("No such table: $name");
+ // Pull column data from INFORMATION_SCHEMA
+ $columns = $this->fetchMetaInfo($table, 'COLUMNS', 'ORDINAL_POSITION');
+ if (count($columns) == 0) {
+ throw new SchemaTableMissingException("No such table: $table");
}
- $td = new TableDef();
+ foreach ($columns as $row) {
- $td->name = $name;
- $td->columns = array();
+ $name = $row['COLUMN_NAME'];
+ $field = array();
- $row = array();
+ // warning -- 'unsigned' attr on numbers isn't given in DATA_TYPE and friends.
+ // It is stuck in on COLUMN_TYPE though (eg 'bigint(20) unsigned')
+ list($type, $size) = $this->reverseMapType($row['DATA_TYPE']);
+ $field['type'] = $type;
+ if ($size !== null) {
+ $field['size'] = $size;
+ }
+
+ if ($type == 'char' || $type == 'varchar') {
+ if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) {
+ $field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']);
+ }
+ }
+ if ($type == 'numeric') {
+ // Other int types may report these values, but they're irrelevant.
+ // Just ignore them!
+ if ($row['NUMERIC_PRECISION'] !== null) {
+ $field['precision'] = intval($row['NUMERIC_PRECISION']);
+ }
+ if ($row['NUMERIC_SCALE'] !== null) {
+ $field['scale'] = intval($row['NUMERIC_SCALE']);
+ }
+ }
+ if ($row['IS_NULLABLE'] == 'NO') {
+ $field['not null'] = true;
+ }
+ if ($row['COLUMN_DEFAULT'] !== null) {
+ $field['default'] = $row['COLUMN_DEFAULT'];
+ if ($this->isNumericType($type)) {
+ $field['default'] = intval($field['default']);
+ }
+ }
+ if ($row['COLUMN_KEY'] !== null) {
+ // We'll need to look up key info...
+ $hasKeys = true;
+ }
+ if ($row['COLUMN_COMMENT'] !== null && $row['COLUMN_COMMENT'] != '') {
+ $field['description'] = $row['COLUMN_COMMENT'];
+ }
- while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
+ $extra = $row['EXTRA'];
+ if ($extra) {
+ if (preg_match('/(^|\s)auto_increment(\s|$)/i', $extra)) {
+ $field['type'] = 'serial';
+ }
+ // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP'
+ // ^ ...... how to specify?
+ }
- $cd = new ColumnDef();
+ if ($row['CHARACTER_SET_NAME'] !== null) {
+ // @fixme check against defaults?
+ //$def['charset'] = $row['CHARACTER_SET_NAME'];
+ //$def['collate'] = $row['COLLATION_NAME'];
+ }
- $cd->name = $row['COLUMN_NAME'];
+ $def['fields'][$name] = $field;
+ }
- $packed = $row['COLUMN_TYPE'];
+ if ($hasKeys) {
+ // INFORMATION_SCHEMA's CONSTRAINTS and KEY_COLUMN_USAGE tables give
+ // good info on primary and unique keys but don't list ANY info on
+ // multi-value keys, which is lame-o. Sigh.
+ //
+ // Let's go old school and use SHOW INDEX :D
+ //
+ $keyInfo = $this->fetchIndexInfo($table);
+ $keys = array();
+ foreach ($keyInfo as $row) {
+ $name = $row['Key_name'];
+ $column = $row['Column_name'];
- if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) {
- $cd->type = $match[1];
- $cd->size = $match[2];
- } else {
- $cd->type = $packed;
+ if (!isset($keys[$name])) {
+ $keys[$name] = array();
+ }
+ $keys[$name][] = $column;
+
+ if ($name == 'PRIMARY') {
+ $type = 'primary key';
+ } else if ($row['Non_unique'] == 0) {
+ $type = 'unique keys';
+ } else if ($row['Index_type'] == 'FULLTEXT') {
+ $type = 'fulltext indexes';
+ } else {
+ $type = 'indexes';
+ }
+ $keyTypes[$name] = $type;
}
- $cd->nullable = ($row['IS_NULLABLE'] == 'YES') ? true : false;
- $cd->key = $row['COLUMN_KEY'];
- $cd->default = $row['COLUMN_DEFAULT'];
- $cd->extra = $row['EXTRA'];
-
- // Autoincrement is stuck into the extra column.
- // Pull it out so we don't accidentally mod it every time...
- $extra = preg_replace('/(^|\s)auto_increment(\s|$)/i', '$1$2', $cd->extra);
- if ($extra != $cd->extra) {
- $cd->extra = trim($extra);
- $cd->auto_increment = true;
+ foreach ($keyTypes as $name => $type) {
+ if ($type == 'primary key') {
+ // there can be only one
+ $def[$type] = $keys[$name];
+ } else {
+ $def[$type][$name] = $keys[$name];
+ }
}
-
- // mysql extensions -- not (yet) used by base class
- $cd->charset = $row['CHARACTER_SET_NAME'];
- $cd->collate = $row['COLLATION_NAME'];
-
- $td->columns[] = $cd;
}
- $res->free();
-
- return $td;
+ return $def;
}
/**
@@ -150,127 +204,55 @@ class MysqlSchema extends Schema
function getTableProperties($table, $props)
{
- $query = "SELECT %s FROM INFORMATION_SCHEMA.TABLES " .
- "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'";
- $schema = $this->conn->dsn['database'];
- $sql = sprintf($query, implode(',', $props), $schema, $table);
- $res = $this->conn->query($sql);
-
- $row = array();
- $ok = $res->fetchInto($row, DB_FETCHMODE_ASSOC);
- $res->free();
-
- if ($ok) {
- return $row;
+ $data = $this->fetchMetaInfo($table, 'TABLES');
+ if ($data) {
+ return $data[0];
} else {
throw new SchemaTableMissingException("No such table: $table");
}
}
/**
- * Gets a ColumnDef object for a single column.
- *
- * Throws an exception if the table is not found.
- *
- * @param string $table name of the table
- * @param string $column name of the column
+ * Pull some INFORMATION.SCHEMA data for the given table.
*
- * @return ColumnDef definition of the column or null
- * if not found.
+ * @param string $table
+ * @return array of arrays
*/
-
- public function getColumnDef($table, $column)
+ function fetchMetaInfo($table, $infoTable, $orderBy=null)
{
- $td = $this->getTableDef($table);
-
- foreach ($td->columns as $cd) {
- if ($cd->name == $column) {
- return $cd;
- }
+ $query = "SELECT * FROM INFORMATION_SCHEMA.%s " .
+ "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'";
+ $schema = $this->conn->dsn['database'];
+ $sql = sprintf($query, $infoTable, $schema, $table);
+ if ($orderBy) {
+ $sql .= ' ORDER BY ' . $orderBy;
}
-
- return null;
+ return $this->fetchQueryData($sql);
}
/**
- * Creates a table with the given names and columns.
- *
- * @param string $name Name of the table
- * @param array $columns Array of ColumnDef objects
- * for new table.
+ * Pull 'SHOW INDEX' data for the given table.
*
- * @return boolean success flag
+ * @param string $table
+ * @return array of arrays
*/
-
- public function createTable($name, $columns)
+ function fetchIndexInfo($table)
{
- $uniques = array();
- $primary = array();
- $indices = array();
-
- $sql = "CREATE TABLE $name (\n";
-
- for ($i = 0; $i < count($columns); $i++) {
-
- $cd =& $columns[$i];
-
- if ($i > 0) {
- $sql .= ",\n";
- }
-
- $sql .= $this->_columnSql($cd);
- }
-
- $idx = $this->_indexList($columns);
-
- if ($idx['primary']) {
- $sql .= ",\nconstraint primary key (" . implode(',', $idx['primary']) . ")";
- }
-
- foreach ($idx['uniques'] as $u) {
- $key = $this->_uniqueKey($name, $u);
- $sql .= ",\nunique index $key ($u)";
- }
-
- foreach ($idx['indices'] as $i) {
- $key = $this->_key($name, $i);
- $sql .= ",\nindex $key ($i)";
- }
-
- $sql .= ") ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; ";
-
- $res = $this->conn->query($sql);
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
+ $query = "SHOW INDEX FROM `%s`";
+ $sql = sprintf($query, $table);
+ return $this->fetchQueryData($sql);
}
/**
- * Look over a list of column definitions and list up which
- * indices will be present
+ * Close out a 'create table' SQL statement.
+ *
+ * @param array $sql
+ * @param string $name
+ * @param array $def
*/
- private function _indexList(array $columns)
+ function appendCreateTableEnd(array &$sql, $name, array $def)
{
- $list = array('uniques' => array(),
- 'primary' => array(),
- 'indices' => array());
- foreach ($columns as $cd) {
- switch ($cd->key) {
- case 'UNI':
- $list['uniques'][] = $cd->name;
- break;
- case 'PRI':
- $list['primary'][] = $cd->name;
- break;
- case 'MUL':
- $list['indices'][] = $cd->name;
- break;
- }
- }
- return $list;
+ $sql[] = ") ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin";
}
/**
@@ -290,156 +272,6 @@ class MysqlSchema extends Schema
}
/**
- * Drops a table from the schema
- *
- * Throws an exception if the table is not found.
- *
- * @param string $name Name of the table to drop
- *
- * @return boolean success flag
- */
-
- public function dropTable($name)
- {
- $res = $this->conn->query("DROP TABLE $name");
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
- /**
- * Adds an index to a table.
- *
- * If no name is provided, a name will be made up based
- * on the table name and column names.
- *
- * Throws an exception on database error, esp. if the table
- * does not exist.
- *
- * @param string $table Name of the table
- * @param array $columnNames Name of columns to index
- * @param string $name (Optional) name of the index
- *
- * @return boolean success flag
- */
-
- public function createIndex($table, $columnNames, $name=null)
- {
- if (!is_array($columnNames)) {
- $columnNames = array($columnNames);
- }
-
- if (empty($name)) {
- $name = "{$table}_".implode("_", $columnNames)."_idx";
- }
-
- $res = $this->conn->query("ALTER TABLE $table ".
- "ADD INDEX $name (".
- implode(",", $columnNames).")");
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
- /**
- * Drops a named index from a table.
- *
- * @param string $table name of the table the index is on.
- * @param string $name name of the index
- *
- * @return boolean success flag
- */
-
- public function dropIndex($table, $name)
- {
- $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name");
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
- /**
- * Adds a column to a table
- *
- * @param string $table name of the table
- * @param ColumnDef $columndef Definition of the new
- * column.
- *
- * @return boolean success flag
- */
-
- public function addColumn($table, $columndef)
- {
- $sql = "ALTER TABLE $table ADD COLUMN " . $this->_columnSql($columndef);
-
- $res = $this->conn->query($sql);
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
- /**
- * Modifies a column in the schema.
- *
- * The name must match an existing column and table.
- *
- * @param string $table name of the table
- * @param ColumnDef $columndef new definition of the column.
- *
- * @return boolean success flag
- */
-
- public function modifyColumn($table, $columndef)
- {
- $sql = "ALTER TABLE $table MODIFY COLUMN " .
- $this->_columnSql($columndef);
-
- $res = $this->conn->query($sql);
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
- /**
- * Drops a column from a table
- *
- * The name must match an existing column.
- *
- * @param string $table name of the table
- * @param string $columnName name of the column to drop
- *
- * @return boolean success flag
- */
-
- public function dropColumn($table, $columnName)
- {
- $sql = "ALTER TABLE $table DROP COLUMN $columnName";
-
- $res = $this->conn->query($sql);
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
- /**
* Ensures that a table exists with the given
* name and the given column definitions.
*
@@ -589,44 +421,12 @@ class MysqlSchema extends Schema
}
/**
- * Returns the array of names from an array of
- * ColumnDef objects.
- *
- * @param array $cds array of ColumnDef objects
- *
- * @return array strings for name values
- */
-
- private function _names($cds)
- {
- $names = array();
-
- foreach ($cds as $cd) {
- $names[] = $cd->name;
- }
-
- return $names;
- }
-
- /**
- * Get a ColumnDef from an array matching
- * name.
- *
- * @param array $cds Array of ColumnDef objects
- * @param string $name Name of the column
- *
- * @return ColumnDef matching item or null if no match.
+ * Is this column a string type?
*/
-
- private function _byName($cds, $name)
+ private function _isString(array $cd)
{
- foreach ($cds as $cd) {
- if ($cd->name == $name) {
- return $cd;
- }
- }
-
- return null;
+ $strings = array('char', 'varchar', 'text');
+ return in_array(strtolower($cd['type']), $strings);
}
/**
@@ -641,43 +441,102 @@ class MysqlSchema extends Schema
* @return string correct SQL for that column
*/
- private function _columnSql($cd)
+ function columnSql(array $cd)
{
- $sql = "{$cd->name} ";
+ $line = array();
+ $line[] = parent::columnSql($cd);
- if (!empty($cd->size)) {
- $sql .= "{$cd->type}({$cd->size}) ";
- } else {
- $sql .= "{$cd->type} ";
+ if ($cd['type'] == 'serial') {
+ $line[] = 'auto_increment';
}
- if ($this->_isString($cd)) {
- $sql .= " CHARACTER SET utf8 ";
+ if (!empty($cd['description'])) {
+ $line[] = 'comment';
+ $line[] = $this->quoteValue($cd['description']);
}
- if (!empty($cd->default)) {
- $sql .= "default {$cd->default} ";
- } else {
- $sql .= ($cd->nullable) ? "null " : "not null ";
- }
+ return implode(' ', $line);
+ }
+
+ function mapType($column)
+ {
+ $map = array('serial' => 'int',
+ 'integer' => 'int',
+ 'numeric' => 'decimal');
- if (!empty($cd->auto_increment)) {
- $sql .= " auto_increment ";
+ $type = $column['type'];
+ if (isset($map[$type])) {
+ $type = $map[$type];
+ }
+
+ if (!empty($column['size'])) {
+ $size = $column['size'];
+ if ($type == 'int' &&
+ in_array($size, array('tiny', 'small', 'medium', 'big'))) {
+ $type = $size . $type;
+ } else if (in_array($type, array('blob', 'text')) &&
+ in_array($size, array('tiny', 'medium', 'long'))) {
+ $type = $size . $type;
+ }
}
- if (!empty($cd->extra)) {
- $sql .= "{$cd->extra} ";
+ return $type;
+ }
+
+ /**
+ * Map a MySQL native type back to an independent type + size
+ *
+ * @param string $type
+ * @return array ($type, $size) -- $size may be null
+ */
+ protected function reverseMapType($type)
+ {
+ $type = strtolower($type);
+ $map = array(
+ 'decimal' => array('numeric', null),
+ 'tinyint' => array('int', 'tiny'),
+ 'smallint' => array('int', 'small'),
+ 'mediumint' => array('int', 'medium'),
+ 'bigint' => array('int', 'big'),
+ 'tinyblob' => array('blob', 'tiny'),
+ 'mediumblob' => array('blob', 'medium'),
+ 'longblob' => array('blob', 'long'),
+ 'tinytext' => array('text', 'tiny'),
+ 'mediumtext' => array('text', 'medium'),
+ 'longtext' => array('text', 'long'),
+ );
+ if (isset($map[$type])) {
+ return $map[$type];
+ } else {
+ return array($type, null);
}
+ }
- return $sql;
+ function typeAndSize($column)
+ {
+ if ($column['type'] == 'enum') {
+ $vals = array_map(array($this, 'quote'), $column['enum']);
+ return 'enum(' . implode(',', $vals) . ')';
+ } else if ($this->_isString($column)) {
+ return parent::typeAndSize($column) . ' CHARSET utf8';
+ } else {
+ return parent::typeAndSize($column);
+ }
}
/**
- * Is this column a string type?
+ * Filter the given table definition array to match features available
+ * in this database.
+ *
+ * This lets us strip out unsupported things like comments, foreign keys,
+ * or type variants that we wouldn't get back from getTableDef().
+ *
+ * @param array $tableDef
*/
- private function _isString(ColumnDef $cd)
+ function filterDef(array $tableDef)
{
- $strings = array('char', 'varchar', 'text');
- return in_array(strtolower($cd->type), $strings);
+ // @fixme add foreign-key support for MySQL
+ unset($tableDef['foreign keys']);
+ return $tableDef;
}
}
generated by cgit v1.2.3-54-g00ecf (git 2.45.2) at 2024-08-26 11:01:31 +0000