1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069 |
- <?php
- if (!defined('STATUSNET')) {
- exit(1);
- }
- class Schema
- {
- static $_static = null;
- protected $conn = null;
-
- protected function __construct($conn = null)
- {
- if (is_null($conn)) {
-
- $user = new User();
- $conn = $user->getDatabaseConnection();
- $user->free();
- unset($user);
- }
- $this->conn = $conn;
- }
-
- static function get($conn = null)
- {
- if (is_null($conn)) {
- $key = 'default';
- } else {
- $key = md5(serialize($conn->dsn));
- }
-
- $type = common_config('db', 'type');
- if (empty(self::$_static[$key])) {
- $schemaClass = ucfirst($type).'Schema';
- self::$_static[$key] = new $schemaClass($conn);
- }
- return self::$_static[$key];
- }
-
- public function getColumnDef($table, $column)
- {
- $td = $this->getTableDef($table);
- if (!empty($td) && !empty($td->columns)) {
- foreach ($td->columns as $cd) {
- if ($cd->name == $column) {
- return $cd;
- }
- }
- }
- return null;
- }
-
- public function createTable($tableName, $def)
- {
- $statements = $this->buildCreateTable($tableName, $def);
- return $this->runSqlSet($statements);
- }
-
- public function buildCreateTable($name, $def)
- {
- $def = $this->validateDef($name, $def);
- $def = $this->filterDef($def);
- $sql = array();
- foreach ($def['fields'] as $col => $colDef) {
- $this->appendColumnDef($sql, $col, $colDef);
- }
-
-
- if (!empty($def['primary key'])) {
- $this->appendPrimaryKeyDef($sql, $def['primary key']);
- }
- if (!empty($def['unique keys'])) {
- foreach ($def['unique keys'] as $col => $colDef) {
- $this->appendUniqueKeyDef($sql, $col, $colDef);
- }
- }
- if (!empty($def['foreign keys'])) {
- foreach ($def['foreign keys'] as $keyName => $keyDef) {
- $this->appendForeignKeyDef($sql, $keyName, $keyDef);
- }
- }
-
- $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, $name, $col, $colDef);
- }
- }
- if (!empty($def['fulltext indexes'])) {
- foreach ($def['fulltext indexes'] as $col => $colDef) {
- $this->appendCreateFulltextIndex($statements, $name, $col, $colDef);
- }
- }
- return $statements;
- }
-
- function startCreateTable($name, array $def)
- {
- return 'CREATE TABLE ' . $this->quoteIdentifier($name) . ' (';
- }
-
- function endCreateTable($name, array $def)
- {
- return ')';
- }
-
- function appendColumnDef(array &$sql, $name, array $def)
- {
- $sql[] = "$name " . $this->columnSql($def);
- }
-
- function appendPrimaryKeyDef(array &$sql, array $def)
- {
- $sql[] = "PRIMARY KEY " . $this->buildIndexList($def);
- }
-
- function appendUniqueKeyDef(array &$sql, $name, array $def)
- {
- $sql[] = "CONSTRAINT $name UNIQUE " . $this->buildIndexList($def);
- }
-
- function appendForeignKeyDef(array &$sql, $name, array $def)
- {
- if (count($def) != 2) {
- throw new Exception("Invalid foreign key def for $name: " . var_export($def, true));
- }
- list($refTable, $map) = $def;
- $srcCols = array_keys($map);
- $refCols = array_values($map);
- $sql[] = "CONSTRAINT $name FOREIGN KEY " .
- $this->buildIndexList($srcCols) .
- " REFERENCES " .
- $this->quoteIdentifier($refTable) .
- " " .
- $this->buildIndexList($refCols);
- }
-
- function appendCreateIndex(array &$statements, $table, $name, array $def)
- {
- $statements[] = "CREATE INDEX $name ON $table " . $this->buildIndexList($def);
- }
-
- function appendCreateFulltextIndex(array &$statements, $table, $name, array $def)
- {
- throw new Exception("Fulltext index not supported in this database");
- }
-
- function appendDropIndex(array &$statements, $table, $name)
- {
- $statements[] = "DROP INDEX $name ON " . $this->quoteIdentifier($table);
- }
- function buildIndexList(array $def)
- {
-
- return '(' . implode(',', array_map(array($this, 'buildIndexItem'), $def)) . ')';
- }
- function buildIndexItem($def)
- {
- if (is_array($def)) {
- list($name, $size) = $def;
- return $this->quoteIdentifier($name) . '(' . intval($size) . ')';
- }
- return $this->quoteIdentifier($def);
- }
-
- public function dropTable($name)
- {
- global $_PEAR;
- $res = $this->conn->query("DROP TABLE $name");
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function createIndex($table, $columnNames, $name=null)
- {
- global $_PEAR;
- 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)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function dropIndex($table, $name)
- {
- global $_PEAR;
- $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name");
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function addColumn($table, $columndef)
- {
- global $_PEAR;
- $sql = "ALTER TABLE $table ADD COLUMN " . $this->_columnSql($columndef);
- $res = $this->conn->query($sql);
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function modifyColumn($table, $columndef)
- {
- global $_PEAR;
- $sql = "ALTER TABLE $table MODIFY COLUMN " .
- $this->_columnSql($columndef);
- $res = $this->conn->query($sql);
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function dropColumn($table, $columnName)
- {
- global $_PEAR;
- $sql = "ALTER TABLE $table DROP COLUMN $columnName";
- $res = $this->conn->query($sql);
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function ensureTable($tableName, $def)
- {
- $statements = $this->buildEnsureTable($tableName, $def);
- return $this->runSqlSet($statements);
- }
-
- function runSqlSet(array $statements)
- {
- global $_PEAR;
- $ok = true;
- foreach ($statements as $sql) {
- if (defined('DEBUG_INSTALLER')) {
- echo "<tt>" . htmlspecialchars($sql) . "</tt><br/>\n";
- }
- $res = $this->conn->query($sql);
- if ($_PEAR->isError($res)) {
- common_debug('PEAR exception on query: '.$sql);
- PEAR_ErrorToPEAR_Exception($res);
- }
- }
- return $ok;
- }
-
- function buildEnsureTable($tableName, array $def)
- {
- try {
- $old = $this->getTableDef($tableName);
- } catch (SchemaTableMissingException $e) {
- return $this->buildCreateTable($tableName, $def);
- }
-
-
- $def = $this->validateDef($tableName, $def);
- $def = $this->filterDef($def);
- $statements = array();
- $fields = $this->diffArrays($old, $def, 'fields', array($this, 'columnsEqual'));
- $uniques = $this->diffArrays($old, $def, 'unique keys');
- $indexes = $this->diffArrays($old, $def, 'indexes');
- $foreign = $this->diffArrays($old, $def, 'foreign keys');
- $fulltext = $this->diffArrays($old, $def, 'fulltext indexes');
-
- foreach ($indexes['del'] + $indexes['mod'] as $indexName) {
- $this->appendDropIndex($statements, $tableName, $indexName);
- }
-
- foreach ($fulltext['del'] + $fulltext['mod'] as $indexName) {
- $this->appendDropIndex($statements, $tableName, $indexName);
- }
-
-
- $phrase = array();
- foreach ($foreign['del'] + $foreign['mod'] as $keyName) {
- $this->appendAlterDropForeign($phrase, $keyName);
- }
- foreach ($uniques['del'] + $uniques['mod'] as $keyName) {
- $this->appendAlterDropUnique($phrase, $keyName);
- }
- if (isset($old['primary key']) && (!isset($def['primary key']) || $def['primary key'] != $old['primary key'])) {
- $this->appendAlterDropPrimary($phrase);
- }
- foreach ($fields['add'] as $columnName) {
- $this->appendAlterAddColumn($phrase, $columnName,
- $def['fields'][$columnName]);
- }
- foreach ($fields['mod'] as $columnName) {
- $this->appendAlterModifyColumn($phrase, $columnName,
- $old['fields'][$columnName],
- $def['fields'][$columnName]);
- }
- foreach ($fields['del'] as $columnName) {
- $this->appendAlterDropColumn($phrase, $columnName);
- }
- if (isset($def['primary key']) && (!isset($old['primary key']) || $old['primary key'] != $def['primary key'])) {
- $this->appendAlterAddPrimary($phrase, $def['primary key']);
- }
- foreach ($uniques['mod'] + $uniques['add'] as $keyName) {
- $this->appendAlterAddUnique($phrase, $keyName, $def['unique keys'][$keyName]);
- }
- foreach ($foreign['mod'] + $foreign['add'] as $keyName) {
- $this->appendAlterAddForeign($phrase, $keyName, $def['foreign keys'][$keyName]);
- }
- $this->appendAlterExtras($phrase, $tableName, $def);
- if (count($phrase) > 0) {
- $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(",\n", $phrase);
- $statements[] = $sql;
- }
-
- foreach ($indexes['mod'] + $indexes['add'] as $indexName) {
- $this->appendCreateIndex($statements, $tableName, $indexName, $def['indexes'][$indexName]);
- }
- foreach ($fulltext['mod'] + $fulltext['add'] as $indexName) {
- $colDef = $def['fulltext indexes'][$indexName];
- $this->appendCreateFulltextIndex($statements, $tableName, $indexName, $colDef);
- }
- return $statements;
- }
- function diffArrays($oldDef, $newDef, $section, $compareCallback=null)
- {
- $old = isset($oldDef[$section]) ? $oldDef[$section] : array();
- $new = isset($newDef[$section]) ? $newDef[$section] : array();
- $oldKeys = array_keys($old);
- $newKeys = array_keys($new);
- $toadd = array_diff($newKeys, $oldKeys);
- $todrop = array_diff($oldKeys, $newKeys);
- $same = array_intersect($newKeys, $oldKeys);
- $tomod = array();
- $tokeep = array();
-
-
- foreach ($same as $name) {
- if ($compareCallback) {
- $same = call_user_func($compareCallback, $old[$name], $new[$name]);
- } else {
- $same = ($old[$name] == $new[$name]);
- }
- if ($same) {
- $tokeep[] = $name;
- continue;
- }
- $tomod[] = $name;
- }
- return array('add' => $toadd,
- 'del' => $todrop,
- 'mod' => $tomod,
- 'keep' => $tokeep,
- 'count' => count($toadd) + count($todrop) + count($tomod));
- }
-
- function appendAlterAddColumn(array &$phrase, $columnName, array $cd)
- {
- $phrase[] = 'ADD COLUMN ' .
- $this->quoteIdentifier($columnName) .
- ' ' .
- $this->columnSql($cd);
- }
-
- function appendAlterModifyColumn(array &$phrase, $columnName, array $old, array $cd)
- {
- $phrase[] = 'MODIFY COLUMN ' .
- $this->quoteIdentifier($columnName) .
- ' ' .
- $this->columnSql($cd);
- }
-
- function appendAlterDropColumn(array &$phrase, $columnName)
- {
- $phrase[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName);
- }
- function appendAlterAddUnique(array &$phrase, $keyName, array $def)
- {
- $sql = array();
- $sql[] = 'ADD';
- $this->appendUniqueKeyDef($sql, $keyName, $def);
- $phrase[] = implode(' ', $sql);
- }
- function appendAlterAddForeign(array &$phrase, $keyName, array $def)
- {
- $sql = array();
- $sql[] = 'ADD';
- $this->appendForeignKeyDef($sql, $keyName, $def);
- $phrase[] = implode(' ', $sql);
- }
- function appendAlterAddPrimary(array &$phrase, array $def)
- {
- $sql = array();
- $sql[] = 'ADD';
- $this->appendPrimaryKeyDef($sql, $def);
- $phrase[] = implode(' ', $sql);
- }
- function appendAlterDropPrimary(array &$phrase)
- {
- $phrase[] = 'DROP CONSTRAINT PRIMARY KEY';
- }
- function appendAlterDropUnique(array &$phrase, $keyName)
- {
- $phrase[] = 'DROP CONSTRAINT ' . $keyName;
- }
- function appendAlterDropForeign(array &$phrase, $keyName)
- {
- $phrase[] = 'DROP FOREIGN KEY ' . $keyName;
- }
- function appendAlterExtras(array &$phrase, $tableName, array $def)
- {
-
- }
-
- function quoteIdentifier($name)
- {
- return $name;
- }
- function quoteDefaultValue($cd)
- {
- if ($cd['type'] == 'datetime' && $cd['default'] == 'CURRENT_TIMESTAMP') {
- return $cd['default'];
- } else {
- return $this->quoteValue($cd['default']);
- }
- }
- function quoteValue($val)
- {
- return $this->conn->quoteSmart($val);
- }
-
- function columnsEqual(array $a, array $b)
- {
- return !array_diff_assoc($a, $b) && !array_diff_assoc($b, $a);
- }
-
- protected function _names($cds)
- {
- $names = array();
- foreach ($cds as $cd) {
- $names[] = $cd->name;
- }
- return $names;
- }
-
- protected function _byName($cds, $name)
- {
- foreach ($cds as $cd) {
- if ($cd->name == $name) {
- return $cd;
- }
- }
- return null;
- }
-
- function columnSql(array $cd)
- {
- $line = array();
- $line[] = $this->typeAndSize($cd);
- if (isset($cd['default'])) {
- $line[] = 'default';
- $line[] = $this->quoteDefaultValue($cd);
- } else if (!empty($cd['not null'])) {
-
- $line[] = 'not null';
- }
- return implode(' ', $line);
- }
-
- function mapType($column)
- {
- return $column;
- }
- function typeAndSize($column)
- {
-
- $type = $column['type'];
- if (isset($column['size'])) {
- $type = $column['size'] . $type;
- }
- $lengths = array();
- if (isset($column['precision'])) {
- $lengths[] = $column['precision'];
- if (isset($column['scale'])) {
- $lengths[] = $column['scale'];
- }
- } else if (isset($column['length'])) {
- $lengths[] = $column['length'];
- }
- if ($lengths) {
- return $type . '(' . implode(',', $lengths) . ')';
- } else {
- return $type;
- }
- }
-
- protected function oldToNew($tableName, array $defs)
- {
- $table = array();
- $prefixes = array(
- 'tiny',
- 'small',
- 'medium',
- 'big',
- );
- foreach ($defs as $cd) {
- $column = array();
- $column['type'] = $cd->type;
- foreach ($prefixes as $prefix) {
- if (substr($cd->type, 0, strlen($prefix)) == $prefix) {
- $column['type'] = substr($cd->type, strlen($prefix));
- $column['size'] = $prefix;
- break;
- }
- }
- if ($cd->size) {
- if ($cd->type == 'varchar' || $cd->type == 'char') {
- $column['length'] = $cd->size;
- }
- }
- if (!$cd->nullable) {
- $column['not null'] = true;
- }
- if ($cd->auto_increment) {
- $column['type'] = 'serial';
- }
- if ($cd->default) {
- $column['default'] = $cd->default;
- }
- $table['fields'][$cd->name] = $column;
- if ($cd->key == 'PRI') {
-
-
- if (!isset($table['primary key'])) {
- $table['primary key'] = array();
- }
- $table['primary key'][] = $cd->name;
- } else if ($cd->key == 'MUL') {
-
-
- $idx = "{$tableName}_{$cd->name}_idx";
- $table['indexes'][$idx] = array($cd->name);
- } else if ($cd->key == 'UNI') {
-
-
- $idx = "{$tableName}_{$cd->name}_idx";
- $table['unique keys'][$idx] = array($cd->name);
- }
- }
- return $table;
- }
-
- function filterDef(array $tableDef)
- {
- return $tableDef;
- }
-
- function validateDef($tableName, array $def)
- {
- if (isset($def[0]) && $def[0] instanceof ColumnDef) {
- $def = $this->oldToNew($tableName, $def);
- }
-
- if (!isset($def['fields'])) {
- throw new Exception("Invalid table definition for $tableName: no fields.");
- }
- return $def;
- }
- function isNumericType($type)
- {
- $type = strtolower($type);
- $known = array('int', 'serial', 'numeric');
- return in_array($type, $known);
- }
-
- protected function fetchQueryData($sql)
- {
- global $_PEAR;
- $res = $this->conn->query($sql);
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- $out = array();
- $row = array();
- while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
- $out[] = $row;
- }
- $res->free();
- return $out;
- }
- }
- class SchemaTableMissingException extends Exception
- {
-
- }
|