public function reverseTableSchema($table) { $tableDef = $this->parseTableSql($table); $schema = new DeclareSchema(); $schema->columnNames = $schema->columns = array(); $schema->table($table); foreach ($tableDef->columns as $columnDef) { $name = $columnDef->name; $column = $schema->column($name); if (!isset($columnDef->type)) { throw new LogicException("Missing column type definition on column {$table}.{$name}."); } $type = $columnDef->type; $typeInfo = TypeInfoParser::parseTypeInfo($type, $this->driver); $column->type($type); if (isset($columnDef->length)) { $column->length($columnDef->length); } if (isset($columnDef->decimals)) { $column->decimals($columnDef->decimals); } $isa = $this->typenameToIsa($type); $column->isa($isa); if (isset($columnDef->notNull) && $columnDef->notNull !== null) { if ($columnDef->notNull) { $column->notNull(); } else { $column->null(); } } if (isset($columnDef->primary)) { $column->primary(true); $schema->primaryKey = $name; if (isset($columnDef->autoIncrement)) { $column->autoIncrement(true); } } else { if (isset($columnDef->unique)) { $column->unique(true); } } if (isset($columnDef->default)) { $default = $columnDef->default; if (is_scalar($default)) { $column->default($default); } else { if ($default instanceof Token && $default->type == 'literal') { $column->default(new Raw($default->val)); } else { throw new Exception('Incorrect literal token'); } } } } return $schema; }
public function testBasicComparison() { $before = new DeclareSchema(); $before->column('same'); $before->column('changed')->varchar(20); $before->column('removed')->boolean(); $after = new DeclareSchema(); $after->column('same'); $after->column('changed')->varchar(30); $after->column('added')->varchar(10); $comparator = new Comparator(); $diffs = $comparator->compare($before, $after); foreach ($diffs as $diff) { $this->assertInstanceOf('LazyRecord\\Schema\\Comparator\\ColumnDiff', $diff); } $firstDiff = $diffs[0]; $this->assertEquals('changed', $firstDiff->name); $this->assertEquals('M', $firstDiff->flag); $secondDiff = $diffs[1]; $this->assertEquals('removed', $secondDiff->name); $this->assertEquals('D', $secondDiff->flag); $thirdDiff = $diffs[2]; $this->assertEquals('added', $thirdDiff->name); $this->assertEquals('A', $thirdDiff->flag); /** * this can't work with posix (color output) */ # $this->expectOutputRegex('/^= same/sm'); # $this->expectOutputRegex('/^= changed/sm'); # $this->expectOutputRegex('/^- removed/sm'); /* ob_start(); $printer->output(); $content = ob_get_contents(); ob_clean(); like('#removed#',$content); like('#added#',$content); */ return $diffs; }
public function reverseTableSchema($table, $referenceSchema = null) { $stm = $this->connection->query("SHOW COLUMNS FROM {$table}"); $schema = new DeclareSchema(); $schema->columnNames = $schema->columns = array(); $schema->table($table); $rows = $stm->fetchAll(); foreach ($rows as $row) { $type = $row['Type']; $typeInfo = TypeInfoParser::parseTypeInfo($type, $this->driver); $isa = $typeInfo->isa; $column = $schema->column($row['Field']); $column->type($typeInfo->type); if ($typeInfo->length) { $column->length($typeInfo->length); } if ($typeInfo->precision) { $column->decimals($typeInfo->precision); } if ($typeInfo->isa) { $column->isa($typeInfo->isa); } if ($typeInfo->unsigned) { $column->unsigned(); } if ($typeInfo->enum) { $column->enum($typeInfo->enum); } elseif ($typeInfo->set) { $column->set($typeInfo->set); } switch ($row['Null']) { case 'NO': // timestamp is set to Null=No by default. // However, it's possible that user didn't set notNull in the schema, // we should skip the check in comparator. if ($referenceSchema && isset($row['Field']) && $referenceSchema->getColumn($row['Field']) && !$referenceSchema->getColumn($row['Field'])->notNull && (strtolower($typeInfo->type) === 'timestamp' || isset($row['Key']) && $row['Key'] === 'PRI')) { } else { $column->notNull(true); } break; case 'YES': $column->null(); break; } switch ($row['Key']) { case 'PRI': $column->primary(true); $schema->primaryKey = $row['Field']; break; // If Key is MUL, multiple occurrences of a given value are // permitted within the column. The column is the first // column of a nonunique index or a unique-valued index // that can contain NULL values. // If Key is MUL, multiple occurrences of a given value are // permitted within the column. The column is the first // column of a nonunique index or a unique-valued index // that can contain NULL values. case 'MUL': break; case 'UNI': $column->unique(true); break; } // Parse information from the Extra field // @see https://dev.mysql.com/doc/refman/5.7/en/show-columns.html $extraAttributes = []; if (strtolower($row['Extra']) == 'auto_increment') { $column->autoIncrement(); } elseif (preg_match('/ON UPDATE (\\w+)/i', $row['Extra'], $matches)) { /* To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP(). */ $extraAttributes['OnUpdate' . Inflector::getInstance()->camelize(strtolower($matches[1]))] = true; } elseif (preg_match('/VIRTUAL GENERATED/i', $row['Extra'])) { $extraAttributes['VirtualGenerated'] = true; } elseif (preg_match('/VIRTUAL STORED/i', $row['Extra'])) { $extraAttributes['VirtualStored'] = true; } // The default value returned from MySQL is string, we need the // type information to cast them to PHP Scalar or other // corresponding type if (null !== $row['Default']) { $default = $row['Default']; if ($typeInfo->type == 'boolean') { if ($default == '1') { $column->default(true); } elseif ($default == '0') { $column->default(false); } } elseif ($typeInfo->isa == 'int') { $column->default(intval($default)); } elseif ($typeInfo->isa == 'double') { $column->default(doubleval($default)); } elseif ($typeInfo->isa == 'float') { $column->default(floatval($default)); } elseif ($typeInfo->isa == 'str') { $column->default($default); } elseif ($typeInfo->type == 'timestamp') { // for mysql, timestamp fields' default value is // 'current_timestamp' and 'on update current_timestamp' // when the two conditions are matched, we need to elimante // the default value just as what we've defined in schema. if (isset($extraAttributes['OnUpdateCurrentTimestamp']) && strtolower($default) == 'current_timestamp') { // Don't set default value } elseif (strtolower($default) == 'current_timestamp') { $column->default(new Raw($default)); } elseif (is_numeric($default)) { $column->default(intval($default)); } } elseif ($typeInfo->type == 'datetime') { // basically, CURRENT_TIMESTAMP, transaction_timestamp() // and now() do exactly the same. CURRENT_TIMESTAMP is a // syntactical oddity for a function, having no trailing // pair of parentheses. That's according to the SQL // standard. // // @see http://dba.stackexchange.com/questions/63548/difference-between-now-and-current-timestamp if (strtolower($default) == 'current_timestamp') { // XXX: NOW() will be converted into current_timestamp $column->default(new Raw($default)); } } } } return $schema; }
public function reverseTableSchema($table) { $stm = $this->connection->query("SHOW COLUMNS FROM {$table}"); $schema = new DeclareSchema(); $schema->columnNames = $schema->columns = array(); $schema->table($table); $rows = $stm->fetchAll(); foreach ($rows as $row) { $type = $row['Type']; $typeInfo = TypeInfoParser::parseTypeInfo($type, $this->driver); $isa = $typeInfo->isa; $column = $schema->column($row['Field']); $column->type($typeInfo->type); if ($typeInfo->length) { $column->length($typeInfo->length); } if ($typeInfo->precision) { $column->decimals($typeInfo->precision); } if ($typeInfo->isa) { $column->isa($typeInfo->isa); } if ($typeInfo->unsigned) { $column->unsigned(); } if ($typeInfo->enum) { $column->enum($typeInfo->enum); } else { if ($typeInfo->set) { $column->set($typeInfo->set); } } if ($row['Null'] == 'NO') { $column->notNull(); } else { $column->null(); } switch ($row['Key']) { case 'PRI': $column->primary(true); $schema->primaryKey = $row['Field']; break; // If Key is MUL, multiple occurrences of a given value are // permitted within the column. The column is the first // column of a nonunique index or a unique-valued index // that can contain NULL values. // If Key is MUL, multiple occurrences of a given value are // permitted within the column. The column is the first // column of a nonunique index or a unique-valued index // that can contain NULL values. case 'MUL': break; case 'UNI': $column->unique(true); break; } // Parse information from the Extra field // @see https://dev.mysql.com/doc/refman/5.7/en/show-columns.html $extraAttributes = []; if (strtolower($row['Extra']) == 'auto_increment') { $column->autoIncrement(); } else { if (preg_match('/ON UPDATE CURRENT_TIMESTAMP/i', $row['Extra'])) { $extraAttributes['OnUpdateCurrentTimestamp'] = true; } else { if (preg_match('/VIRTUAL GENERATED/i', $row['Extra'])) { $extraAttributes['VirtualGenerated'] = true; } else { if (preg_match('/VIRTUAL STORED/i', $row['Extra'])) { $extraAttributes['VirtualStored'] = true; } } } } // The default value returned from MySQL is string, we need the // type information to cast them to PHP Scalar or other // corresponding type if (NULL !== $row['Default']) { $default = $row['Default']; if ($typeInfo->type == 'boolean') { if ($default == '1') { $column->default(true); } else { if ($default == '0') { $column->default(false); } } } else { if ($typeInfo->isa == 'int') { $column->default(intval($default)); } else { if ($typeInfo->isa == 'double') { $column->default(doubleval($default)); } else { if ($typeInfo->isa == 'float') { $column->default(floatval($default)); } else { if ($typeInfo->isa == 'str') { $column->default($default); } else { if ($typeInfo->type == 'timestamp') { // for mysql, timestamp fields' default value is // 'current_timestamp' and 'on update current_timestamp' // when the two conditions are matched, we need to elimante // the default value just as what we've defined in schema. if (isset($extraAttributes['OnUpdateCurrentTimestamp']) && strtolower($default) == 'current_timestamp') { // Do nothing } else { if (strtolower($default) == 'current_timestamp') { $column->default(new Raw($default)); } else { if (is_numeric($default)) { $column->default(intval($default)); } } } } } } } } } } } return $schema; }
public function reverseTableSchema($table, $referenceSchema = null) { /* * postgresql information schema column descriptions * * @see http://www.postgresql.org/docs/8.1/static/infoschema-columns.html */ $sql = "SELECT * FROM information_schema.columns WHERE table_name = '{$table}';"; $stm = $this->connection->query($sql); $schema = new DeclareSchema(); $schema->columnNames = $schema->columns = array(); $rows = $stm->fetchAll(PDO::FETCH_OBJ); /* * more detailed attributes * * > select * from pg_attribute, pg_type where typname = 'addresses'; * > select * from pg_attribute, pg_type where typname = 'addresses' and attname not in ('cmin','cmax','ctid','oid','tableoid','xmin','xmax'); * * > SELECT * a.attname as "Column", * pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype" * FROM * pg_catalog.pg_attribute a * WHERE * a.attnum > 0 * AND NOT a.attisdropped * AND a.attrelid = ( * SELECT c.oid * FROM pg_catalog.pg_class c * LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace * WHERE c.relname ~ '^(books)$' * AND pg_catalog.pg_table_is_visible(c.oid) * ) * ; * * @see http://notfaq.wordpress.com/2006/07/29/sql-postgresql-get-tables-and-columns/ */ foreach ($rows as $row) { $column = $schema->column($row->column_name); if ($row->is_nullable === 'YES') { $column->null(); } else { $column->notNull(); } $type = $row->data_type; $typeInfo = TypeInfoParser::parseTypeInfo($type); if ($typeInfo->type === 'varchar') { $type = 'varchar(' . $row->character_maximum_length . ')'; } $column->type($type); $isa = null; if (preg_match('/^(text|varchar|character)/i', $type)) { $isa = 'str'; } elseif (preg_match('/^(int|bigint|smallint|integer)/i', $type)) { $isa = 'int'; } elseif (preg_match('/^(timestamp|date)/i', $type)) { $isa = 'DateTime'; } elseif ($type === 'boolean') { $isa = 'bool'; } if ($isa) { $column->isa($isa); } if ($typeInfo->length) { $column->length($typeInfo->length); } if ($typeInfo->precision) { $column->decimals($typeInfo->precision); } // $row->ordinal_position // $row->data_type // $row->column_default // $row->character_maximum_length } return $schema; }