From 9364e446b1edf34c01202828ebb1e7eaf04b3871 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Mon, 11 Oct 2010 19:10:51 -0700 Subject: [PATCH] Start reworking things to build create table stuff (can view via dumpschema.php --build) --- lib/mysqlschema.php | 56 ++---------- lib/pgsqlschema.php | 103 ++------------------- lib/schema.php | 203 ++++++++++++++++++++++++++++++++--------- scripts/dumpschema.php | 18 +++- 4 files changed, 190 insertions(+), 190 deletions(-) diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index 9ccd502b53..a2ca835d93 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -244,59 +244,15 @@ class MysqlSchema extends Schema } /** - * Creates a table with the given names and columns. + * Close out a 'create table' SQL statement. * - * @param string $name Name of the table - * @param array $columns Array of ColumnDef objects - * for new table. - * - * @return boolean success flag + * @param array $sql + * @param string $name + * @param array $def */ - - public function createTable($name, $columns) + function appendCreateTableEnd(array &$sql, $name, array $def) { - $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; + $sql[] = ") ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin"; } /** diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php index 73797a90c3..a8ce21b384 100644 --- a/lib/pgsqlschema.php +++ b/lib/pgsqlschema.php @@ -262,71 +262,6 @@ class PgsqlSchema extends Schema return $out; } - /** - * - * 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. - * - * @return boolean success flag - */ - - public function createTable($name, $columns) - { - $uniques = array(); - $primary = array(); - $indices = array(); - $onupdate = 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); - switch ($cd->key) { - case 'UNI': - $uniques[] = $cd->name; - break; - case 'PRI': - $primary[] = $cd->name; - break; - case 'MUL': - $indices[] = $cd->name; - break; - } - } - - if (count($primary) > 0) { // it really should be... - $sql .= ",\n PRIMARY KEY (" . implode(',', $primary) . ")"; - } - - $sql .= "); "; - - - foreach ($uniques as $u) { - $sql .= "\n CREATE index {$name}_{$u}_idx ON {$name} ($u); "; - } - - foreach ($indices as $i) { - $sql .= "CREATE index {$name}_{$i}_idx ON {$name} ($i)"; - } - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage(). ' SQL was '. $sql); - } - - return true; - } - /** * Translate the (mostly) mysql-ish column types into somethings more standard * @param string column type @@ -343,31 +278,6 @@ class PgsqlSchema extends Schema return $type; } - /** - * 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 ALTER COLUMN TYPE " . - $this->_columnSql($columndef); - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; - } - /** * Return the proper SQL for creating or * altering a column. @@ -375,26 +285,25 @@ class PgsqlSchema extends Schema * Appropriate for use in CREATE TABLE or * ALTER TABLE statements. * - * @param string $tableName - * @param array $tableDef - * @param string $columnName * @param array $cd column to create * * @return string correct SQL for that column */ - function columnSql($name, array $cd) + function columnSql(array $cd) { $line = array(); - $line[] = parent::_columnSql($cd); + $line[] = parent::columnSql($cd); + /* if ($table['foreign keys'][$name]) { foreach ($table['foreign keys'][$name] as $foreignTable => $foreignColumn) { $line[] = 'references'; - $line[] = $this->quoteId($foreignTable); - $line[] = '(' . $this->quoteId($foreignColumn) . ')'; + $line[] = $this->quoteIdentifier($foreignTable); + $line[] = '(' . $this->quoteIdentifier($foreignColumn) . ')'; } } + */ return implode(' ', $line); } diff --git a/lib/schema.php b/lib/schema.php index 40a9fb505d..6c6a3e56f0 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -128,56 +128,133 @@ class Schema public function createTable($name, $columns) { - $uniques = array(); - $primary = array(); - $indices = array(); + $statements = $this->buildCreateTable($tableName, $def); + return $this->runSqlSet($statements); + } - $sql = "CREATE TABLE $name (\n"; + /** + * Build a set of SQL statements to create a table with the given + * name and columns. + * + * @param string $name Name of the table + * @param array $def Table definition array + * + * @return boolean success flag + */ + public function buildCreateTable($name, $def) + { + $sql = array(); - for ($i = 0; $i < count($columns); $i++) { + foreach ($def['fields'] as $col => $colDef) { + $this->appendColumnDef($sql, $col, $colDef); + } - $cd =& $columns[$i]; + // Primary and unique keys are constraints, so go within + // the CREATE TABLE statement normally. + if (!empty($def['primary key'])) { + $this->appendPrimaryKeyDef($sql, $def['primary key']); + } - if ($i > 0) { - $sql .= ",\n"; + if (!empty($def['unique keys'])) { + foreach ($def['unique keys'] as $col => $colDef) { + $this->appendUniqueKeyDef($sql, $col, $colDef); } + } - $sql .= $this->_columnSql($cd); - - switch ($cd->key) { - case 'UNI': - $uniques[] = $cd->name; - break; - case 'PRI': - $primary[] = $cd->name; - break; - case 'MUL': - $indices[] = $cd->name; - break; + // Multi-value indexes are advisory and for best portability + // should be created as separate statements. + $statements = array(); + $statements[] = $this->startCreateTable($name, $def) . "\n" . + implode($sql, ",\n") . "\n" . + $this->endCreateTable($name, $def); + if (!empty($def['indexes'])) { + foreach ($def['indexes'] as $col => $colDef) { + $this->appendCreateIndex($statements, $table, $col, $colDef); } } - if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; - } + return $statements; + } - foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; - } + /** + * Set up a 'create table' SQL statement. + * + * @param string $name table name + * @param array $def table definition + * @param $string + */ + function startCreateTable($name, array $def) + { + return 'CREATE TABLE ' . $this->quoteIdentifier($name) . ' ('; + } - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } + /** + * Close out a 'create table' SQL statement. + * + * @param string $name table name + * @param array $def table definition + * @return string + */ + function endCreateTable($name, array $def) + { + return ')'; + } - $sql .= "); "; + /** + * Append an SQL fragment with a column definition in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendColumnDef(array &$sql, $name, array $def) + { + $sql[] = "$name " . $this->columnSql($def); + } - $res = $this->conn->query($sql); + /** + * Append an SQL fragment with a constraint definition for a primary + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param array $def + */ + function appendPrimaryKeyDef(array &$sql, array $def) + { + $sql[] = "PRIMARY KEY " . $this->buildIndexList($def); + } - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } + /** + * Append an SQL fragment with a constraint definition for a primary + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendUniqueKeyDef(array &$sql, $name, array $def) + { + $sql[] = "UNIQUE $key " . $this->buildIndexList($def); + } - return true; + /** + * Append an SQL statement with an index definition for an advisory + * index over one or more columns on a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendCreateIndex(array &$statements, $table, $name, array $def) + { + $statements[] = "CREATE INDEX $name ON $table " . $this->buildIndexList($def); + } + + function buildIndexList(array $def) + { + // @fixme + return '(' . implode(',', array_map(array($this, 'quoteIdentifier'), $def)) . ')'; } /** @@ -339,21 +416,63 @@ class Schema * alter the table to match the column definitions. * * @param string $tableName name of the table - * @param array $columns array of ColumnDef - * objects for the table + * @param array $def Table definition array * * @return boolean success flag */ public function ensureTable($tableName, $def) { - // XXX: DB engine portability -> toilet + $statements = $this->buildEnsureTable($tableName, $def); + return $this->runSqlSet($statements); + } + /** + * Run a given set of SQL commands on the connection in sequence. + * Empty input is ok. + * + * @fixme if multiple statements, wrap in a transaction? + * @param array $statements + * @return boolean success flag + */ + function runSqlSet(array $statements) + { + $ok = true; + foreach ($statements as $sql) { + $res = $this->conn->query($sql); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + } + return $ok; + } + + /** + * Check a table's status, and if needed build a set + * of SQL statements which change it to be consistent + * with the given table definition. + * + * If the table does not yet exist, statements will + * be returned to create the table. If it does exist, + * statements will be returned to alter the table to + * match the column definitions. + * + * @param string $tableName name of the table + * @param array $columns array of ColumnDef + * objects for the table + * + * @return array of SQL statements + */ + + function buildEnsureTable($tableName, $def) + { try { $old = $this->getTableDef($tableName); } catch (Exception $e) { + // @fixme this is a terrible check :D if (preg_match('/no such table/', $e->getMessage())) { - return $this->createTable($tableName, $columns); + return $this->buildCreateTable($tableName, $def); } else { throw $e; } @@ -481,7 +600,7 @@ class Schema function quoteValue($val) { - return $this->conn->escape($val); + return $this->conn->escapeSimple($val); // ?? } /** @@ -554,11 +673,11 @@ class Schema function columnSql(array $cd) { $line = array(); - $line[] = $this->typeAndSize(); + $line[] = $this->typeAndSize($cd); if (isset($cd['default'])) { $line[] = 'default'; - $line[] = $this->quoted($cd['default']); + $line[] = $this->quoteDefaultValue($cd); } else if (!empty($cd['not null'])) { // Can't have both not null AND default! $line[] = 'not null'; diff --git a/scripts/dumpschema.php b/scripts/dumpschema.php index fd44399a9c..c0bf84f7c4 100644 --- a/scripts/dumpschema.php +++ b/scripts/dumpschema.php @@ -25,7 +25,7 @@ Attempt to pull a schema definition for a given table. END_OF_CHECKSCHEMA_HELP; -$longoptions = array('diff', 'all'); +$longoptions = array('diff', 'all', 'build'); require_once INSTALLDIR.'/scripts/commandline.inc'; function indentOptions($indent) @@ -104,6 +104,20 @@ function dumpTable($tableName, $live) $def = getCoreSchema($tableName); } prettyDumpArray($def, $tableName); +} + +function dumpBuildTable($tableName) +{ + echo "-- \n"; + echo "-- $tableName\n"; + echo "-- \n"; + + $schema = Schema::get(); + $def = getCoreSchema($tableName); + $sql = $schema->buildCreateTable($tableName, $def); + $sql[] = ''; + + echo implode(";\n", $sql); echo "\n"; } @@ -140,6 +154,8 @@ if (count($args)) { $detected = ob_get_clean(); showDiff($defined, $detected); + } else if (have_option('build')) { + dumpBuildTable($tableName); } else { dumpTable($tableName, true); } -- 2.39.5