// 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;
- }
+ $field['type'] = $type = $row['DATA_TYPE'];
if ($type == 'char' || $type == 'varchar') {
if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) {
$field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']);
}
}
- if ($type == 'numeric') {
+ if ($type == 'decimal') {
// Other int types may report these values, but they're irrelevant.
// Just ignore them!
if ($row['NUMERIC_PRECISION'] !== null) {
$field['not null'] = true;
}
if ($row['COLUMN_DEFAULT'] !== null) {
- $field['default'] = $row['COLUMN_DEFAULT'];
- if ($this->isNumericType($type)) {
- $field['default'] = intval($field['default']);
+ // Hack for timestamp cols
+ if ($type == 'timestamp' && $row['COLUMN_DEFAULT'] == 'CURRENT_TIMESTAMP') {
+ // skip
+ } else {
+ $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) {
+ if ($row['COLUMN_COMMENT'] !== null && $row['COLUMN_COMMENT'] != '') {
$field['description'] = $row['COLUMN_COMMENT'];
}
- // $row['EXTRA'] may contain 'autoincrement'
- // ^ type=serial?
- // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP'
- // ^ ...... how to specify?
- // these seem to be the only values in curent use
-
+ $extra = $row['EXTRA'];
+ if ($extra) {
+ if (preg_match('/(^|\s)auto_increment(\s|$)/i', $extra)) {
+ $field['auto_increment'] = true;
+ }
+ // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP'
+ // ^ ...... how to specify?
+ }
+
if ($row['CHARACTER_SET_NAME'] !== null) {
// @fixme check against defaults?
//$def['charset'] = $row['CHARACTER_SET_NAME'];
}
/**
- * Pull info from the query into a fun-fun array of dooooom
+ * Append an SQL statement with an index definition for a full-text search
+ * index over one or more columns on a table.
*
- * @param string $sql
- * @return array of arrays
+ * @param array $statements
+ * @param string $table
+ * @param string $name
+ * @param array $def
*/
- protected function fetchQueryData($sql)
+ function appendCreateFulltextIndex(array &$statements, $table, $name, array $def)
{
- $res = $this->conn->query($sql);
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- $out = array();
- $row = array();
- while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
- $out[] = $row;
- }
- $res->free();
-
- return $out;
+ $statements[] = "CREATE FULLTEXT INDEX $name ON $table " . $this->buildIndexList($def);
}
/**
- * 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.
+ * @param string $name
+ * @param array $def
+ * @return string;
*
- * @return boolean success flag
+ * @fixme ENGINE may need to be set differently in some cases,
+ * such as to support fulltext index.
*/
-
- public function createTable($name, $columns)
+ function endCreateTable($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;
+ $engine = $this->preferredEngine($def);
+ return ") ENGINE=$engine CHARACTER SET utf8mb4 COLLATE utf8mb4_bin";
}
-
- /**
- * Look over a list of column definitions and list up which
- * indices will be present
- */
- private function _indexList(array $columns)
+
+ function preferredEngine($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;
- }
+ if (!empty($def['fulltext indexes'])) {
+ return 'MyISAM';
}
- return $list;
+ return 'InnoDB';
}
/**
}
/**
- * Ensures that a table exists with the given
- * name and the given column definitions.
- *
- * If the table does not yet exist, it will
- * create the table. If it does exist, it will
- * 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
+ * MySQL doesn't take 'DROP CONSTRAINT', need to treat primary keys as
+ * if they were indexes here, but can use 'PRIMARY KEY' special name.
*
- * @return boolean success flag
+ * @param array $phrase
*/
-
- public function ensureTable($tableName, $columns)
+ function appendAlterDropPrimary(array &$phrase)
{
- // XXX: DB engine portability -> toilet
-
- try {
- $td = $this->getTableDef($tableName);
- } catch (SchemaTableMissingException $e) {
- return $this->createTable($tableName, $columns);
- }
-
- $cur = $this->_names($td->columns);
- $new = $this->_names($columns);
-
- $dropIndex = array();
- $toadd = array_diff($new, $cur);
- $todrop = array_diff($cur, $new);
- $same = array_intersect($new, $cur);
- $tomod = array();
- $addIndex = array();
- $tableProps = array();
-
- foreach ($same as $m) {
- $curCol = $this->_byName($td->columns, $m);
- $newCol = $this->_byName($columns, $m);
-
- if (!$newCol->equals($curCol)) {
- $tomod[] = $newCol->name;
- continue;
- }
-
- // Earlier versions may have accidentally left tables at default
- // charsets which might be latin1 or other freakish things.
- if ($this->_isString($curCol)) {
- if ($curCol->charset != 'utf8') {
- $tomod[] = $newCol->name;
- continue;
- }
- }
- }
-
- // Find any indices we have to change...
- $curIdx = $this->_indexList($td->columns);
- $newIdx = $this->_indexList($columns);
-
- if ($curIdx['primary'] != $newIdx['primary']) {
- if ($curIdx['primary']) {
- $dropIndex[] = 'drop primary key';
- }
- if ($newIdx['primary']) {
- $keys = implode(',', $newIdx['primary']);
- $addIndex[] = "add constraint primary key ($keys)";
- }
- }
-
- $dropUnique = array_diff($curIdx['uniques'], $newIdx['uniques']);
- $addUnique = array_diff($newIdx['uniques'], $curIdx['uniques']);
- foreach ($dropUnique as $columnName) {
- $dropIndex[] = 'drop key ' . $this->_uniqueKey($tableName, $columnName);
- }
- foreach ($addUnique as $columnName) {
- $addIndex[] = 'add constraint unique key ' . $this->_uniqueKey($tableName, $columnName) . " ($columnName)";;
- }
+ $phrase[] = 'DROP PRIMARY KEY';
+ }
- $dropMultiple = array_diff($curIdx['indices'], $newIdx['indices']);
- $addMultiple = array_diff($newIdx['indices'], $curIdx['indices']);
- foreach ($dropMultiple as $columnName) {
- $dropIndex[] = 'drop key ' . $this->_key($tableName, $columnName);
- }
- foreach ($addMultiple as $columnName) {
- $addIndex[] = 'add key ' . $this->_key($tableName, $columnName) . " ($columnName)";
- }
+ /**
+ * MySQL doesn't take 'DROP CONSTRAINT', need to treat unique keys as
+ * if they were indexes here.
+ *
+ * @param array $phrase
+ * @param <type> $keyName MySQL
+ */
+ function appendAlterDropUnique(array &$phrase, $keyName)
+ {
+ $phrase[] = 'DROP INDEX ' . $keyName;
+ }
+ /**
+ * Throw some table metadata onto the ALTER TABLE if we have a mismatch
+ * in expected type, collation.
+ */
+ function appendAlterExtras(array &$phrase, $tableName, array $def)
+ {
// Check for table properties: make sure we're using a sane
// engine type and charset/collation.
// @fixme make the default engine configurable?
$oldProps = $this->getTableProperties($tableName, array('ENGINE', 'TABLE_COLLATION'));
- if (strtolower($oldProps['ENGINE']) != 'innodb') {
- $tableProps['ENGINE'] = 'InnoDB';
+ $engine = $this->preferredEngine($def);
+ if (strtolower($oldProps['ENGINE']) != strtolower($engine)) {
+ $phrase[] = "ENGINE=$engine";
}
- if (strtolower($oldProps['TABLE_COLLATION']) != 'utf8_bin') {
- $tableProps['DEFAULT CHARSET'] = 'utf8';
- $tableProps['COLLATE'] = 'utf8_bin';
- }
-
- if (count($dropIndex) + count($toadd) + count($todrop) + count($tomod) + count($addIndex) + count($tableProps) == 0) {
- // nothing to do
- return true;
+ if (strtolower($oldProps['TABLE_COLLATION']) != 'utf8mb4_bin') {
+ $phrase[] = 'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin';
+ $phrase[] = 'DEFAULT CHARACTER SET = utf8mb4';
+ $phrase[] = 'DEFAULT COLLATE = utf8mb4_bin';
}
-
- // For efficiency, we want this all in one
- // query, instead of using our methods.
-
- $phrase = array();
-
- foreach ($dropIndex as $indexSql) {
- $phrase[] = $indexSql;
- }
-
- foreach ($toadd as $columnName) {
- $cd = $this->_byName($columns, $columnName);
-
- $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd);
- }
-
- foreach ($todrop as $columnName) {
- $phrase[] = 'DROP COLUMN ' . $columnName;
- }
-
- foreach ($tomod as $columnName) {
- $cd = $this->_byName($columns, $columnName);
-
- $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd);
- }
-
- foreach ($addIndex as $indexSql) {
- $phrase[] = $indexSql;
- }
-
- foreach ($tableProps as $key => $val) {
- $phrase[] = "$key=$val";
- }
-
- $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase);
-
- common_log(LOG_DEBUG, __METHOD__ . ': ' . $sql);
- $res = $this->conn->query($sql);
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
}
/**
function columnSql(array $cd)
{
$line = array();
- $line[] = parent::_columnSql($cd);
+ $line[] = parent::columnSql($cd);
- if ($cd['type'] == 'serial') {
+ // This'll have been added from our transform of 'serial' type
+ if (!empty($cd['auto_increment'])) {
$line[] = 'auto_increment';
}
- if (!empty($cd['extra'])) {
- $line[] = $cd['extra']; // hisss boooo
- }
-
if (!empty($cd['description'])) {
$line[] = 'comment';
- $line[] = $this->quote($cd['description']);
+ $line[] = $this->quoteValue($cd['description']);
}
return implode(' ', $line);
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);
- }
- }
-
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';
+ $col = parent::typeAndSize($column);
+ if (!empty($column['charset'])) {
+ $col .= ' CHARSET ' . $column['charset'];
+ }
+ if (!empty($column['collate'])) {
+ $col .= ' COLLATE ' . $column['collate'];
+ }
+ return $col;
} else {
return parent::typeAndSize($column);
}
}
+
+ /**
+ * 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
+ */
+ function filterDef(array $tableDef)
+ {
+ foreach ($tableDef['fields'] as $name => &$col) {
+ if ($col['type'] == 'serial') {
+ $col['type'] = 'int';
+ $col['auto_increment'] = true;
+ }
+ if ($col['type'] == 'datetime' && isset($col['default']) && $col['default'] == 'CURRENT_TIMESTAMP') {
+ $col['type'] = 'timestamp';
+ }
+ $col['type'] = $this->mapType($col);
+ unset($col['size']);
+ }
+ if (!common_config('db', 'mysql_foreign_keys')) {
+ unset($tableDef['foreign keys']);
+ }
+ return $tableDef;
+ }
}
projects / quix0rs-gnu-social.git / blobdiff