123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525 |
- <?php
- defined('GNUSOCIAL') || die();
- class MysqlSchema extends Schema
- {
- public static $_single = null;
-
- public static function get($conn = null, $_ = 'mysql')
- {
- if (empty(self::$_single)) {
- self::$_single = new Schema($conn, 'mysql');
- }
- return self::$_single;
- }
-
- public function getTableDef($table)
- {
- $def = [];
- $hasKeys = false;
-
- $columns = $this->fetchMetaInfo($table, 'COLUMNS', 'ORDINAL_POSITION');
- if (count($columns) == 0) {
- throw new SchemaTableMissingException("No such table: $table");
- }
- foreach ($columns as $row) {
- $name = $row['COLUMN_NAME'];
- $field = [];
-
-
- $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 == 'decimal') {
-
-
- 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) {
-
- if ($row['COLUMN_DEFAULT'] === 'current_timestamp()') {
-
- if ($type !== 'timestamp') {
- $field['default'] = 'CURRENT_TIMESTAMP';
- }
- } elseif ($this->isNumericType($type)) {
- $field['default'] = intval($row['COLUMN_DEFAULT']);
- } else {
- $field['default'] = $row['COLUMN_DEFAULT'];
- }
- }
- if ($row['COLUMN_KEY'] !== null) {
-
- $hasKeys = true;
- }
- if ($row['COLUMN_COMMENT'] !== null && $row['COLUMN_COMMENT'] != '') {
- $field['description'] = $row['COLUMN_COMMENT'];
- }
- $extra = $row['EXTRA'];
- if ($extra) {
- if (preg_match('/(^|\s)auto_increment(\s|$)/i', $extra)) {
- $field['auto_increment'] = true;
- }
-
-
- }
-
- $def['fields'][$name] = $field;
- }
- if ($hasKeys) {
-
-
-
- $keyColumns = $this->fetchMetaInfo($table, 'KEY_COLUMN_USAGE', 'CONSTRAINT_NAME, ORDINAL_POSITION');
- $keys = [];
- $fkeys = [];
- foreach ($keyColumns as $row) {
- $keyName = $row['CONSTRAINT_NAME'];
- $keyCol = $row['COLUMN_NAME'];
- if (!isset($keys[$keyName])) {
- $keys[$keyName] = [];
- }
- $keys[$keyName][] = $keyCol;
- if (!is_null($row['REFERENCED_TABLE_NAME'])) {
- $fkeys[] = $keyName;
- }
- }
- foreach ($keys as $keyName => $cols) {
- if ($keyName === 'PRIMARY') {
- $def['primary key'] = $cols;
- } elseif (in_array($keyName, $fkeys)) {
- $fkey = $this->fetchForeignKeyInfo($table, $keyName);
- $colMap = array_combine($cols, $fkey['cols']);
- $def['foreign keys'][$keyName] = [$fkey['table_name'], $colMap];
- } else {
- $def['unique keys'][$keyName] = $cols;
- }
- }
- $indexInfo = $this->fetchIndexInfo($table);
- foreach ($indexInfo as $row) {
- $keyName = $row['key_name'];
- $cols = $row['cols'];
- if ($row['key_type'] === 'FULLTEXT') {
- $def['fulltext indexes'][$keyName] = $cols;
- } else {
- $def['indexes'][$keyName] = $cols;
- }
- }
- }
- return $def;
- }
-
- public function getTableProperties($table, $props)
- {
- $data = $this->fetchMetaInfo($table, 'TABLES');
- if ($data) {
- return $data[0];
- } else {
- throw new SchemaTableMissingException("No such table: $table");
- }
- }
-
- public function fetchMetaInfo($table, $infoTable, $orderBy = null)
- {
- $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 $this->fetchQueryData($sql);
- }
-
- public function fetchIndexInfo(string $table): array
- {
- $query = 'SELECT INDEX_NAME AS `key_name`, INDEX_TYPE AS `key_type`, COLUMN_NAME AS `col` ' .
- 'FROM INFORMATION_SCHEMA.STATISTICS ' .
- 'WHERE TABLE_SCHEMA = \'%s\' AND TABLE_NAME = \'%s\' AND NON_UNIQUE = TRUE ' .
- 'AND INDEX_NAME NOT IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL) ' .
- 'ORDER BY SEQ_IN_INDEX';
- $schema = $this->conn->dsn['database'];
- $sql = sprintf($query, $schema, $table);
- $data = $this->fetchQueryData($sql);
- $rows = [];
- foreach ($data as $row) {
- $name = $row['key_name'];
- if (isset($rows[$name])) {
- $rows[$name]['cols'][] = $row['col'];
- } else {
- $row['cols'] = [$row['col']];
- unset($row['col']);
- $rows[$name] = $row;
- }
- }
- return array_values($rows);
- }
-
- public function fetchForeignKeyInfo(string $table, string $constraint_name): array
- {
- $query = 'SELECT REFERENCED_TABLE_NAME AS `table_name`, REFERENCED_COLUMN_NAME AS `col` ' .
- 'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ' .
- 'WHERE TABLE_SCHEMA = \'%s\' AND TABLE_NAME = \'%s\' AND CONSTRAINT_NAME = \'%s\' ' .
- 'AND REFERENCED_TABLE_SCHEMA IS NOT NULL ' .
- 'ORDER BY POSITION_IN_UNIQUE_CONSTRAINT';
- $schema = $this->conn->dsn['database'];
- $sql = sprintf($query, $schema, $table, $constraint_name);
- $data = $this->fetchQueryData($sql);
- if (count($data) < 1) {
- throw new Exception('Could not find foreign key ' . $constraint_name . ' on table ' . $table);
- }
- $info = [
- 'table_name' => $data[0]['table_name'],
- 'cols' => [],
- ];
- foreach ($data as $row) {
- $info['cols'][] = $row['col'];
- }
- return $info;
- }
-
- public function appendCreateFulltextIndex(array &$statements, $table, $name, array $def)
- {
- $statements[] = "CREATE FULLTEXT INDEX $name ON $table " . $this->buildIndexList($def);
- }
-
- public function endCreateTable($name, array $def)
- {
- $engine = $this->preferredEngine($def);
- return ") ENGINE=$engine CHARACTER SET utf8mb4 COLLATE utf8mb4_bin";
- }
- public function preferredEngine($def)
- {
-
- return 'InnoDB';
- }
-
- public function _uniqueKey($tableName, $columnName)
- {
- return $this->_key($tableName, $columnName);
- }
-
- public function _key($tableName, $columnName)
- {
- return "{$tableName}_{$columnName}_idx";
- }
-
- public function appendAlterDropPrimary(array &$phrase, string $tableName)
- {
- $phrase[] = 'DROP PRIMARY KEY';
- }
-
- public function appendAlterDropUnique(array &$phrase, $keyName)
- {
- $phrase[] = 'DROP INDEX ' . $keyName;
- }
-
- public function appendAlterExtras(array &$phrase, $tableName, array $def)
- {
-
-
-
- $oldProps = $this->getTableProperties($tableName, ['ENGINE', 'TABLE_COLLATION']);
- $engine = $this->preferredEngine($def);
- if (strtolower($oldProps['ENGINE']) != strtolower($engine)) {
- $phrase[] = "ENGINE=$engine";
- }
- 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';
- }
- }
-
- private function _isString(array $cd)
- {
- $strings = ['char', 'varchar', 'text'];
- return in_array(strtolower($cd['type']), $strings);
- }
-
- public function columnSql(string $name, array $cd)
- {
- $line = [];
- $line[] = parent::columnSql($name, $cd);
-
- if (!empty($cd['auto_increment'])) {
- $line[] = 'auto_increment';
- }
- if (!empty($cd['description'])) {
- $line[] = 'comment';
- $line[] = $this->quoteValue($cd['description']);
- }
- return implode(' ', $line);
- }
- public function mapType($column)
- {
- $map = [
- 'integer' => 'int',
- 'bool' => 'tinyint',
- 'numeric' => 'decimal',
- ];
- $type = $column['type'];
- if (isset($map[$type])) {
- $type = $map[$type];
- }
- if (!empty($column['size'])) {
- $size = $column['size'];
- if ($type == 'int' &&
- in_array($size, ['tiny', 'small', 'medium', 'big'])) {
- $type = $size . $type;
- } elseif ($type == 'float' && $size == 'big') {
- $type = 'double';
- } elseif (in_array($type, ['blob', 'text']) &&
- in_array($size, ['tiny', 'medium', 'long'])) {
- $type = $size . $type;
- }
- }
- return $type;
- }
- public function typeAndSize(string $name, array $column)
- {
- if ($column['type'] === 'enum') {
- foreach ($column['enum'] as &$val) {
- $vals[] = "'" . $val . "'";
- }
- return 'enum(' . implode(',', $vals) . ')';
- } elseif ($this->_isString($column)) {
- $col = parent::typeAndSize($name, $column);
- if (!empty($column['charset'])) {
- $col .= ' CHARSET ' . $column['charset'];
- }
- if (!empty($column['collate'])) {
- $col .= ' COLLATE ' . $column['collate'];
- }
- return $col;
- } else {
- return parent::typeAndSize($name, $column);
- }
- }
-
- public function filterDef(array $tableDef)
- {
- $version = $this->conn->getVersion();
- foreach ($tableDef['fields'] as $name => &$col) {
- if ($col['type'] == 'serial') {
- $col['type'] = 'int';
- $col['auto_increment'] = true;
- }
- $col['type'] = $this->mapType($col);
- unset($col['size']);
- }
- if (!common_config('db', 'mysql_foreign_keys')) {
- unset($tableDef['foreign keys']);
- }
- return $tableDef;
- }
- }
|