1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099 |
- <?php
- defined('GNUSOCIAL') || die();
- class Schema
- {
- public 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;
- }
-
- public static function get($conn = null, $dbtype = null)
- {
- if (is_null($conn)) {
- $key = 'default';
- } else {
- $key = md5(serialize($conn->dsn));
- }
- if (is_null($dbtype)) {
- $dbtype = common_config('db', 'type');
- }
- if (empty(self::$_static[$key])) {
- $schemaClass = ucfirst($dbtype) . '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 = [];
- 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 = [];
- $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;
- }
-
- public function startCreateTable($name, array $def)
- {
- return 'CREATE TABLE ' . $this->quoteIdentifier($name) . ' (';
- }
-
- public function endCreateTable($name, array $def)
- {
- return ')';
- }
-
- public function appendColumnDef(array &$sql, string $name, array $def)
- {
- $sql[] = $name . ' ' . $this->columnSql($name, $def);
- }
-
- public function appendPrimaryKeyDef(array &$sql, array $def)
- {
- $sql[] = "PRIMARY KEY " . $this->buildIndexList($def);
- }
-
- public function appendUniqueKeyDef(array &$sql, $name, array $def)
- {
- $sql[] = "CONSTRAINT $name UNIQUE " . $this->buildIndexList($def);
- }
-
- public 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 ' . $this->quoteIdentifier($name) . ' FOREIGN KEY ' .
- $this->buildIndexList($srcCols) .
- ' REFERENCES ' .
- $this->quoteIdentifier($refTable) .
- ' ' .
- $this->buildIndexList($refCols);
- }
-
- public function appendCreateIndex(array &$statements, $table, $name, array $def)
- {
- $statements[] = 'CREATE INDEX ' . $name . ' ON ' .
- $this->quoteIdentifier($table) . ' ' . $this->buildIndexList($def);
- }
-
- public function appendCreateFulltextIndex(array &$statements, $table, $name, array $def)
- {
- throw new Exception("Fulltext index not supported in this database");
- }
-
- public function appendDropIndex(array &$statements, $table, $name)
- {
- $statements[] = "DROP INDEX $name ON " . $this->quoteIdentifier($table);
- }
- public function buildIndexList(array $def)
- {
-
- return '(' . implode(',', array_map([$this, 'buildIndexItem'], $def)) . ')';
- }
- public 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 ' . $this->quoteIdentifier($name));
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function createIndex($table, $columnNames, $name = null)
- {
- global $_PEAR;
- $qry = [];
- if (!is_array($columnNames)) {
- $columnNames = [$columnNames];
- }
- if (empty($name)) {
- $name = "{$table}_" . implode("_", $columnNames) . "_idx";
- }
- $this->appendCreateIndex($qry, $table, $name, $columnNames);
- $res = $this->conn->query(implode('; ', $qry));
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function dropIndex($table, $name)
- {
- global $_PEAR;
- $res = $this->conn->query(
- 'ALTER TABLE ' . $this->quoteIdentifier($table) .
- ' DROP INDEX ' . $name
- );
- if ($_PEAR->isError($res)) {
- PEAR_ErrorToPEAR_Exception($res);
- }
- return true;
- }
-
- public function addColumn($table, $columndef)
- {
- global $_PEAR;
- $sql = 'ALTER TABLE ' . $this->quoteIdentifier($table) .
- ' ADD COLUMN ' . $this->columnSql($name, $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 ' . $this->quoteIdentifier($table) .
- ' MODIFY COLUMN ' . $this->columnSql($name, $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 ' . $this->quoteIdentifier($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);
- }
-
- public function runSqlSet(array $statements)
- {
- global $_PEAR;
- $ok = true;
- foreach ($statements as $sql) {
- if (defined('DEBUG_INSTALLER')) {
- echo "<code>" . htmlspecialchars($sql) . "</code><br/>\n";
- }
- $res = $this->conn->query($sql);
- if ($_PEAR->isError($res)) {
- common_debug('PEAR exception on query: ' . $sql);
- PEAR_ErrorToPEAR_Exception($res);
- }
- }
- return $ok;
- }
-
- public 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 = [];
- $fields = $this->diffArrays($old, $def, 'fields', [$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 = [];
- 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, $tableName);
- }
- 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 ' . $this->quoteIdentifier($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;
- }
- public function diffArrays($oldDef, $newDef, $section, $compareCallback = null)
- {
- $old = isset($oldDef[$section]) ? $oldDef[$section] : [];
- $new = isset($newDef[$section]) ? $newDef[$section] : [];
- $oldKeys = array_keys($old);
- $newKeys = array_keys($new);
- $toadd = array_diff($newKeys, $oldKeys);
- $todrop = array_diff($oldKeys, $newKeys);
- $same = array_intersect($newKeys, $oldKeys);
- $tomod = [];
- $tokeep = [];
-
-
- 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 [
- 'add' => $toadd,
- 'del' => $todrop,
- 'mod' => $tomod,
- 'keep' => $tokeep,
- 'count' => count($toadd) + count($todrop) + count($tomod)
- ];
- }
-
- public function appendAlterAddColumn(array &$phrase, string $columnName, array $cd)
- {
- $phrase[] = 'ADD COLUMN ' .
- $this->quoteIdentifier($columnName) .
- ' ' .
- $this->columnSql($columnName, $cd);
- }
-
- public function appendAlterModifyColumn(array &$phrase, string $columnName, array $old, array $cd)
- {
- $phrase[] = 'MODIFY COLUMN ' .
- $this->quoteIdentifier($columnName) .
- ' ' .
- $this->columnSql($columnName, $cd);
- }
-
- public function appendAlterDropColumn(array &$phrase, $columnName)
- {
- $phrase[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName);
- }
- public function appendAlterAddUnique(array &$phrase, $keyName, array $def)
- {
- $sql = [];
- $sql[] = 'ADD';
- $this->appendUniqueKeyDef($sql, $keyName, $def);
- $phrase[] = implode(' ', $sql);
- }
- public function appendAlterAddForeign(array &$phrase, $keyName, array $def)
- {
- $sql = [];
- $sql[] = 'ADD';
- $this->appendForeignKeyDef($sql, $keyName, $def);
- $phrase[] = implode(' ', $sql);
- }
- public function appendAlterAddPrimary(array &$phrase, array $def)
- {
- $sql = [];
- $sql[] = 'ADD';
- $this->appendPrimaryKeyDef($sql, $def);
- $phrase[] = implode(' ', $sql);
- }
- public function appendAlterDropPrimary(array &$phrase, string $tableName)
- {
- $phrase[] = 'DROP CONSTRAINT PRIMARY KEY';
- }
- public function appendAlterDropUnique(array &$phrase, $keyName)
- {
- $phrase[] = 'DROP CONSTRAINT ' . $keyName;
- }
- public function appendAlterDropForeign(array &$phrase, $keyName)
- {
- $phrase[] = 'DROP FOREIGN KEY ' . $keyName;
- }
- public function appendAlterExtras(array &$phrase, $tableName, array $def)
- {
-
- }
-
- public function quoteIdentifier($name)
- {
- return $this->conn->quoteIdentifier($name);
- }
- public function quoteDefaultValue($cd)
- {
- if (in_array($cd['type'], ['datetime', 'timestamp']) && $cd['default'] === 'CURRENT_TIMESTAMP') {
- return $cd['default'];
- } else {
- return $this->quoteValue($cd['default']);
- }
- }
- public function quoteValue($val)
- {
- return $this->conn->quoteSmart($val);
- }
-
- public function columnsEqual(array $a, array $b)
- {
- return !array_diff_assoc($a, $b) && !array_diff_assoc($b, $a);
- }
-
- protected function _names($cds)
- {
- $names = [];
- 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;
- }
-
- public function columnSql(string $name, array $cd)
- {
- $line = [];
- $line[] = $this->typeAndSize($name, $cd);
- if (isset($cd['default'])) {
- $line[] = 'default';
- $line[] = $this->quoteDefaultValue($cd);
- } elseif (!empty($cd['not null'])) {
-
- $line[] = 'not null';
- }
- return implode(' ', $line);
- }
-
- public function mapType($column)
- {
- return $column;
- }
- public function typeAndSize(string $name, array $column)
- {
-
- $type = $column['type'];
- if (isset($column['size'])) {
- $type = $column['size'] . $type;
- }
- $lengths = [];
- if (isset($column['precision'])) {
- $lengths[] = $column['precision'];
- if (isset($column['scale'])) {
- $lengths[] = $column['scale'];
- }
- } elseif (isset($column['length'])) {
- $lengths[] = $column['length'];
- }
- if ($lengths) {
- return $type . '(' . implode(',', $lengths) . ')';
- } else {
- return $type;
- }
- }
-
- protected function oldToNew($tableName, array $defs)
- {
- $table = [];
- $prefixes = [
- 'tiny',
- 'small',
- 'medium',
- 'big',
- ];
- foreach ($defs as $cd) {
- $column = [];
- $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'] = [];
- }
- $table['primary key'][] = $cd->name;
- } elseif ($cd->key === 'MUL') {
-
-
- $idx = "{$tableName}_{$cd->name}_idx";
- $table['indexes'][$idx] = [$cd->name];
- } elseif ($cd->key === 'UNI') {
-
-
- $idx = "{$tableName}_{$cd->name}_idx";
- $table['unique keys'][$idx] = [$cd->name];
- }
- }
- return $table;
- }
-
- public function filterDef(array $tableDef)
- {
- return $tableDef;
- }
-
- public 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;
- }
- public function isNumericType($type)
- {
- $type = strtolower($type);
- $known = ['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 = [];
- $row = [];
- while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) {
- $out[] = $row;
- }
- $res->free();
- return $out;
- }
- public function renameTable(string $old_name, string $new_name) : bool
- {
- try {
- $this->getTableDef($old_name);
- try {
- $this->getTableDef($new_name);
-
- throw new ServerException("Both table {$old_name} and {$new_name} exist. You're on your own.");
- } catch (SchemaTableMissingException $e) {
-
- }
- } catch (SchemaTableMissingException $e) {
-
- return true;
- }
- return $this->runSqlSet([
- 'ALTER TABLE ' . $this->quoteIdentifier($old_name) .
- ' RENAME TO ' . $this->quoteIdentifier($new_name) . ';',
- ]);
- }
- }
- class SchemaTableMissingException extends Exception
- {
-
- }
|