protected function addClosureColumn($name, Table $ct_table, Column $column) { $table = $this->getTable(); $id_fieldname = $column->getName(); $domain = $column->getDomain(); if (!$ct_table->hasColumn($name)) { $column = new Column($name); $column->setDomain($domain); $column->setPrimaryKey(true); $ct_table->addColumn($column); } else { $column = $ct_table->getColumn($name); } $ct_tablename_normalized = str_replace('_', '', $ct_table->getName()); $fk_name = $ct_tablename_normalized . '_' . $name . '_fk'; if (!$ct_table->getColumnForeignKeys($name)) { $column_fk = new ForeignKey($fk_name); $column_fk->addReference($name, $table->getColumn($id_fieldname)->getName()); $column_fk->setForeignTableCommonName($table->getName()); $column_fk->setOnUpdate('cascade'); $column_fk->setOnDelete('restrict'); $ct_table->addForeignKey($column_fk); } $column_idx_name = $fk_name . '_idx'; if (!$ct_table->hasIndex($column_idx_name)) { $column_idx = new Index($column_idx_name); $column_idx->addColumn(['name' => $column->getName()]); $ct_table->addIndex($column_idx); } }
public static function compareColumns(Column $fromColumn, Column $toColumn) { $changedProperties = []; // compare column types $fromDomain = $fromColumn->getDomain(); $toDomain = $toColumn->getDomain(); if ($fromDomain->getScale() !== $toDomain->getScale()) { $changedProperties['scale'] = [$fromDomain->getScale(), $toDomain->getScale()]; } if ($fromDomain->getSize() !== $toDomain->getSize()) { $changedProperties['size'] = [$fromDomain->getSize(), $toDomain->getSize()]; } if (strtoupper($fromDomain->getSqlType()) !== strtoupper($toDomain->getSqlType())) { if ($fromDomain->getOriginSqlType()) { if (strtoupper($fromDomain->getOriginSqlType()) !== strtoupper($toDomain->getSqlType())) { if ($fromDomain->getType() !== $toDomain->getType()) { $changedProperties['type'] = [$fromDomain->getType(), $toDomain->getType()]; } $changedProperties['sqlType'] = [$fromDomain->getSqlType(), $toDomain->getSqlType()]; } } else { $changedProperties['sqlType'] = [$fromDomain->getSqlType(), $toDomain->getSqlType()]; if ($fromDomain->getType() !== $toDomain->getType()) { $changedProperties['type'] = [$fromDomain->getType(), $toDomain->getType()]; } } } if ($fromColumn->isNotNull() !== $toColumn->isNotNull()) { $changedProperties['notNull'] = [$fromColumn->isNotNull(), $toColumn->isNotNull()]; } // compare column default value $fromDefaultValue = $fromColumn->getDefaultValue(); $toDefaultValue = $toColumn->getDefaultValue(); if ($fromDefaultValue && !$toDefaultValue) { $changedProperties['defaultValueType'] = [$fromDefaultValue->getType(), null]; $changedProperties['defaultValueValue'] = [$fromDefaultValue->getValue(), null]; } elseif (!$fromDefaultValue && $toDefaultValue) { $changedProperties['defaultValueType'] = [null, $toDefaultValue->getType()]; $changedProperties['defaultValueValue'] = [null, $toDefaultValue->getValue()]; } elseif ($fromDefaultValue && $toDefaultValue) { if (!$fromDefaultValue->equals($toDefaultValue)) { if ($fromDefaultValue->getType() !== $toDefaultValue->getType()) { $changedProperties['defaultValueType'] = [$fromDefaultValue->getType(), $toDefaultValue->getType()]; } if ($fromDefaultValue->getValue() !== $toDefaultValue->getValue()) { $changedProperties['defaultValueValue'] = [$fromDefaultValue->getValue(), $toDefaultValue->getValue()]; } } } if ($fromColumn->isAutoIncrement() !== $toColumn->isAutoIncrement()) { $changedProperties['autoIncrement'] = [$fromColumn->isAutoIncrement(), $toColumn->isAutoIncrement()]; } return $changedProperties; }
public function getColumnDDL(Column $col) { $domain = $col->getDomain(); $sqlType = $domain->getSqlType(); $notNullString = $this->getNullString($col->isNotNull()); $defaultSetting = $this->getColumnDefaultValueDDL($col); // Special handling of TIMESTAMP/DATETIME types ... // See: http://propel.phpdb.org/trac/ticket/538 if ($sqlType == 'DATETIME') { $def = $domain->getDefaultValue(); if ($def && $def->isExpression()) { // DATETIME values can only have constant expressions $sqlType = 'TIMESTAMP'; } } elseif ($sqlType == 'DATE') { $def = $domain->getDefaultValue(); if ($def && $def->isExpression()) { throw new EngineException('DATE columns cannot have default *expressions* in MySQL.'); } } elseif ($sqlType == 'TEXT' || $sqlType == 'BLOB') { if ($domain->getDefaultValue()) { throw new EngineException('BLOB and TEXT columns cannot have DEFAULT values. in MySQL.'); } } $ddl = array($this->quoteIdentifier($col->getName())); if ($this->hasSize($sqlType) && $col->isDefaultSqlType($this)) { $ddl[] = $sqlType . $col->getSizeDefinition(); } else { $ddl[] = $sqlType; } $colinfo = $col->getVendorInfoForType($this->getDatabaseType()); if ($colinfo->hasParameter('Charset')) { $ddl[] = 'CHARACTER SET ' . $this->quote($colinfo->getParameter('Charset')); } if ($colinfo->hasParameter('Collation')) { $ddl[] = 'COLLATE ' . $this->quote($colinfo->getParameter('Collation')); } elseif ($colinfo->hasParameter('Collate')) { $ddl[] = 'COLLATE ' . $this->quote($colinfo->getParameter('Collate')); } if ($sqlType == 'TIMESTAMP') { if ($notNullString == '') { $notNullString = 'NULL'; } if ($defaultSetting == '' && $notNullString == 'NOT NULL') { $defaultSetting = 'DEFAULT CURRENT_TIMESTAMP'; } if ($notNullString) { $ddl[] = $notNullString; } if ($defaultSetting) { $ddl[] = $defaultSetting; } } else { if ($defaultSetting) { $ddl[] = $defaultSetting; } if ($notNullString) { $ddl[] = $notNullString; } } if ($autoIncrement = $col->getAutoIncrementString()) { $ddl[] = $autoIncrement; } if ($col->getDescription()) { $ddl[] = 'COMMENT ' . $this->quote($col->getDescription()); } return implode(' ', $ddl); }
public function testCompareSeveralRenamedSameColumns() { $t1 = new Table(); $c1 = new Column('col1'); $c1->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c1->getDomain()->replaceSize(255); $t1->addColumn($c1); $c2 = new Column('col2'); $c2->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c2->getDomain()->replaceSize(255); $t1->addColumn($c2); $c3 = new Column('col3'); $c3->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c3->getDomain()->replaceSize(255); $t1->addColumn($c3); $t2 = new Table(); $c4 = new Column('col4'); $c4->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c4->getDomain()->replaceSize(255); $t2->addColumn($c4); $c5 = new Column('col5'); $c5->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c5->getDomain()->replaceSize(255); $t2->addColumn($c5); $c6 = new Column('col3'); $c6->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c6->getDomain()->replaceSize(255); $t2->addColumn($c6); // col1 and col2 were renamed $tc = new TableComparator(); $tc->setFromTable($t1); $tc->setToTable($t2); $nbDiffs = $tc->compareColumns(); $tableDiff = $tc->getTableDiff(); $this->assertEquals(2, $nbDiffs); $this->assertEquals([[$c1, $c4], [$c2, $c5]], $tableDiff->getRenamedColumns()); $this->assertEquals([], $tableDiff->getAddedColumns()); $this->assertEquals([], $tableDiff->getRemovedColumns()); $this->assertEquals([], $tableDiff->getModifiedColumns()); }
/** * Adds Columns to the specified table. * * @param Table $table The Table model class to add columns to. */ protected function addColumns(Table $table) { $stmt = $this->dbh->query("PRAGMA table_info('" . $table->getName() . "')"); while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $name = $row['name']; $fulltype = $row['type']; $size = null; $precision = null; $scale = null; if (preg_match('/^([^\\(]+)\\(\\s*(\\d+)\\s*,\\s*(\\d+)\\s*\\)$/', $fulltype, $matches)) { $type = $matches[1]; $precision = $matches[2]; $scale = $matches[3]; // aka precision } elseif (preg_match('/^([^\\(]+)\\(\\s*(\\d+)\\s*\\)$/', $fulltype, $matches)) { $type = $matches[1]; $size = $matches[2]; } else { $type = $fulltype; } // If column is primary key and of type INTEGER, it is auto increment // See: http://sqlite.org/faq.html#q1 $autoincrement = 1 == $row['pk'] && 'integer' === strtolower($type); $notNull = $row['notnull']; $default = $row['dflt_value']; $propelType = $this->getMappedPropelType(strtolower($type)); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $this->warn('Column [' . $table->getName() . '.' . $name . '] has a column type (' . $type . ') that Propel does not support.'); } $column = new Column($name); $column->setTable($table); $column->setDomainForType($propelType); // We may want to provide an option to include this: // $column->getDomain()->replaceSqlType($type); $column->getDomain()->replaceSize($size); $column->getDomain()->replaceScale($scale); if (null !== $default) { $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, ColumnDefaultValue::TYPE_VALUE)); } $column->setAutoIncrement($autoincrement); $column->setNotNull($notNull); if (1 == $row['pk'] || 'integer' === strtolower($type)) { $column->setPrimaryKey(true); } $table->addColumn($column); } }
/** * Adds Columns to the specified table. * * @param Table $table The Table model class to add columns to. */ protected function addColumns(Table $table) { $dataFetcher = $this->dbh->query("sp_columns '" . $table->getName() . "'"); foreach ($dataFetcher as $row) { $name = $this->cleanDelimitedIdentifiers($row['COLUMN_NAME']); $type = $row['TYPE_NAME']; $size = $row['LENGTH']; $isNullable = $row['NULLABLE']; $default = $row['COLUMN_DEF']; $scale = $row['SCALE']; $autoincrement = false; if (strtolower($type) == 'int identity') { $autoincrement = true; } $propelType = $this->getMappedPropelType($type); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $this->warn(sprintf('Column [%s.%s] has a column type (%s) that Propel does not support.', $table->getName(), $name, $type)); } $column = new Column($name); $column->setTable($table); $column->setDomainForType($propelType); $column->getDomain()->setOriginSqlType($type); // We may want to provide an option to include this: // $column->getDomain()->replaceSqlType($type); $column->getDomain()->replaceSize($size); $column->getDomain()->replaceScale($scale); if ($default !== null) { $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, ColumnDefaultValue::TYPE_VALUE)); } $column->setAutoIncrement($autoincrement); $column->setNotNull(!$isNullable); $table->addColumn($column); } }
public function providerForTestGetModifyColumnRemoveDefaultValueDDL() { $t1 = new Table('test'); $c1 = new Column(); $c1->setName('test'); $c1->getDomain()->setType('INTEGER'); $c1->setDefaultValue(0); $t1->addColumn($c1); $t2 = new Table('test'); $c2 = new Column(); $c2->setName('test'); $c2->getDomain()->setType('INTEGER'); $t2->addColumn($c2); return array(array(PropelColumnComparator::computeDiff($c1, $c2))); }
public function testGetIndexDDLFulltext() { $table = new Table('foo'); $table->setIdentifierQuoting(true); $column1 = new Column('bar1'); $column1->getDomain()->copy($this->getPlatform()->getDomainForType('LONGVARCHAR')); $table->addColumn($column1); $index = new Index('bar_index'); $index->addColumn($column1); $vendor = new VendorInfo('mysql'); $vendor->setParameter('Index_type', 'FULLTEXT'); $index->addVendorInfo($vendor); $table->addIndex($index); $expected = 'FULLTEXT INDEX `bar_index` (`bar1`)'; $this->assertEquals($expected, $this->getPlatform()->getIndexDDL($index)); }
public function providerForTestGetForeignKeysDDL() { $db = new Database(); $db->setIdentifierQuoting(true); $table1 = new Table('foo'); $db->addTable($table1); $column1 = new Column('bar_id'); $column1->getDomain()->copy(new Domain('FOOTYPE')); $table1->addColumn($column1); $table2 = new Table('bar'); $db->addTable($table2); $column2 = new Column('id'); $column2->getDomain()->copy(new Domain('BARTYPE')); $table2->addColumn($column2); $fk = new ForeignKey('foo_bar_fk'); $fk->setForeignTableCommonName('bar'); $fk->addReference($column1, $column2); $fk->setOnDelete('CASCADE'); $table1->addForeignKey($fk); $column3 = new Column('baz_id'); $column3->getDomain()->copy(new Domain('BAZTYPE')); $table1->addColumn($column3); $table3 = new Table('baz'); $db->addTable($table3); $column4 = new Column('id'); $column4->getDomain()->copy(new Domain('BAZTYPE')); $table3->addColumn($column4); $fk = new ForeignKey('foo_baz_fk'); $fk->setForeignTableCommonName('baz'); $fk->addReference($column3, $column4); $fk->setOnDelete('SETNULL'); $table1->addForeignKey($fk); return array(array($table1)); }
public function getUsingCast(Column $fromColumn, Column $toColumn) { $fromSqlType = strtoupper($fromColumn->getDomain()->getSqlType()); $toSqlType = strtoupper($toColumn->getDomain()->getSqlType()); $name = $fromColumn->getName(); if ($this->isNumber($fromSqlType) && $this->isString($toSqlType)) { //cast from int to string return ' '; } if ($this->isString($fromSqlType) && $this->isNumber($toSqlType)) { //cast from string to int return "\n USING CASE WHEN trim({$name}) SIMILAR TO '[0-9]+'\n THEN CAST(trim({$name}) AS integer)\n ELSE NULL END"; } if ($this->isNumber($fromSqlType) && 'BYTEA' === $toSqlType) { return " USING decode(CAST({$name} as text), 'escape')"; } if ('DATE' === $fromSqlType && 'TIME' === $toSqlType) { return " USING NULL"; } if ($this->isNumber($fromSqlType) && $this->isNumber($toSqlType)) { return ''; } if ($this->isString($fromSqlType) && $this->isString($toSqlType)) { return ''; } return " USING NULL"; }
/** * Factory method creating a Column object * based on a row from the 'show columns from ' MySQL query result. * * @param array $row An associative array with the following keys: * Field, Type, Null, Key, Default, Extra. * @return Column */ public function getColumnFromRow($row, Table $table) { $name = $row['Field']; $is_nullable = $row['Null'] == 'YES'; $autoincrement = strpos($row['Extra'], 'auto_increment') !== false; $size = null; $precision = null; $scale = null; $sqlType = false; $regexp = '/^ (\\w+) # column type [1] [\\(] # ( ?([\\d,]*) # size or size, precision [2] [\\)] # ) ?\\s* # whitespace (\\w*) # extra description (UNSIGNED, CHARACTER SET, ...) [3] $/x'; if (preg_match($regexp, $row['Type'], $matches)) { $nativeType = $matches[1]; if ($matches[2]) { if (($cpos = strpos($matches[2], ',')) !== false) { $size = (int) substr($matches[2], 0, $cpos); $precision = $size; $scale = (int) substr($matches[2], $cpos + 1); } else { $size = (int) $matches[2]; } } if ($matches[3]) { $sqlType = $row['Type']; } foreach (self::$defaultTypeSizes as $type => $defaultSize) { if ($nativeType == $type && $size == $defaultSize) { $size = null; continue; } } } elseif (preg_match('/^(\\w+)\\(/', $row['Type'], $matches)) { $nativeType = $matches[1]; if ($nativeType == 'enum') { $sqlType = $row['Type']; } } else { $nativeType = $row['Type']; } //BLOBs can't have any default values in MySQL $default = preg_match('~blob|text~', $nativeType) ? null : $row['Default']; $propelType = $this->getMappedPropelType($nativeType); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $sqlType = $row['Type']; $this->warn("Column [" . $table->getName() . "." . $name . "] has a column type (" . $nativeType . ") that Propel does not support."); } // Special case for TINYINT(1) which is a BOOLEAN if (PropelTypes::TINYINT === $propelType && 1 === $size) { $propelType = PropelTypes::BOOLEAN; } $column = new Column($name); $column->setTable($table); $column->setDomainForType($propelType); if ($sqlType) { $column->getDomain()->replaceSqlType($sqlType); } $column->getDomain()->replaceSize($size); $column->getDomain()->replaceScale($scale); if ($default !== null) { if ($propelType == PropelTypes::BOOLEAN) { if ($default == '1') { $default = 'true'; } if ($default == '0') { $default = 'false'; } } if (in_array($default, array('CURRENT_TIMESTAMP'))) { $type = ColumnDefaultValue::TYPE_EXPR; } else { $type = ColumnDefaultValue::TYPE_VALUE; } $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, $type)); } $column->setAutoIncrement($autoincrement); $column->setNotNull(!$is_nullable); if ($this->addVendorInfo) { $vi = $this->getNewVendorInfoObject($row); $column->addVendorInfo($vi); } return $column; }
/** * Adds Columns to the specified table. * * @param Table $table The Table model class to add columns to. * @param int $oid The table OID * @param string $version The database version. */ protected function addColumns(Table $table, $oid, $version) { // Get the columns, types, etc. // Based on code from pgAdmin3 (http://www.pgadmin.org/) $stmt = $this->dbh->prepare("SELECT\n att.attname,\n att.atttypmod,\n att.atthasdef,\n att.attnotnull,\n def.adsrc,\n CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,\n CASE\n WHEN ty.typname = 'bpchar'\n THEN 'char'\n WHEN ty.typname = '_bpchar'\n THEN '_char'\n ELSE\n ty.typname\n END AS typname,\n ty.typtype\n FROM pg_attribute att\n JOIN pg_type ty ON ty.oid=att.atttypid\n LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum\n WHERE att.attrelid = ? AND att.attnum > 0\n AND att.attisdropped IS FALSE\n ORDER BY att.attnum"); $stmt->bindValue(1, $oid, PDO::PARAM_INT); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $size = null; $precision = null; $scale = null; // Check to ensure that this column isn't an array data type if ((int) $row['isarray'] === 1) { throw new EngineException(sprintf("Array datatypes are not currently supported [%s.%s]", $this->name, $row['attname'])); } // if (((int) $row['isarray']) === 1) $name = $row['attname']; // If they type is a domain, Process it if (strtolower($row['typtype']) == 'd') { $arrDomain = $this->processDomain($row['typname']); $type = $arrDomain['type']; $size = $arrDomain['length']; $precision = $size; $scale = $arrDomain['scale']; $boolHasDefault = strlen(trim($row['atthasdef'])) > 0 ? $row['atthasdef'] : $arrDomain['hasdefault']; $default = strlen(trim($row['adsrc'])) > 0 ? $row['adsrc'] : $arrDomain['default']; $isNullable = strlen(trim($row['attnotnull'])) > 0 ? $row['attnotnull'] : $arrDomain['notnull']; $isNullable = $isNullable == 't' ? false : true; } else { $type = $row['typname']; $arrLengthPrecision = $this->processLengthScale($row['atttypmod'], $type); $size = $arrLengthPrecision['length']; $precision = $size; $scale = $arrLengthPrecision['scale']; $boolHasDefault = $row['atthasdef']; $default = $row['adsrc']; $isNullable = $row['attnotnull'] == 't' ? false : true; } // else (strtolower ($row['typtype']) == 'd') $autoincrement = null; // if column has a default if ($boolHasDefault == 't' && strlen(trim($default)) > 0) { if (!preg_match('/^nextval\\(/', $default)) { $strDefault = preg_replace('/::[\\W\\D]*/', '', $default); $default = preg_replace('/(\'?)\'/', '${1}', $strDefault); } else { $autoincrement = true; $default = null; } } else { $default = null; } $propelType = $this->getMappedPropelType($type); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $this->warn("Column [" . $table->getName() . "." . $name . "] has a column type (" . $type . ") that Propel does not support."); } $column = new Column($name); $column->setTable($table); $column->setDomainForType($propelType); // We may want to provide an option to include this: // $column->getDomain()->replaceSqlType($type); $column->getDomain()->replaceSize($size); $column->getDomain()->replaceScale($scale); if ($default !== null) { if (in_array($default, array('now()'))) { $type = ColumnDefaultValue::TYPE_EXPR; } else { $type = ColumnDefaultValue::TYPE_VALUE; } $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, $type)); } $column->setAutoIncrement($autoincrement); $column->setNotNull(!$isNullable); $table->addColumn($column); } }
public function testCompareSeveralRenamedSamePrimaryKeys() { $t1 = new Table(); $c1 = new Column('col1'); $c1->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c1->setNotNull(true); $c1->setPrimaryKey(true); $t1->addColumn($c1); $c2 = new Column('col2'); $c2->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c2->setNotNull(true); $c2->setPrimaryKey(true); $t1->addColumn($c2); $c3 = new Column('col3'); $c3->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c3->setNotNull(true); $c3->setPrimaryKey(true); $t1->addColumn($c3); $t2 = new Table(); $c4 = new Column('col4'); $c4->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c4->setNotNull(true); $c4->setPrimaryKey(true); $t2->addColumn($c4); $c5 = new Column('col5'); $c5->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c5->setNotNull(true); $c5->setPrimaryKey(true); $t2->addColumn($c5); $c6 = new Column('col3'); $c6->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c6->setNotNull(true); $c6->setPrimaryKey(true); $t2->addColumn($c6); // col1 and col2 were renamed $tc = new TableComparator(); $tc->setFromTable($t1); $tc->setToTable($t2); $nbDiffs = $tc->comparePrimaryKeys(); $tableDiff = $tc->getTableDiff(); $this->assertEquals(2, $nbDiffs); $this->assertEquals([[$c1, $c4], [$c2, $c5]], $tableDiff->getRenamedPkColumns()); $this->assertEquals([], $tableDiff->getAddedPkColumns()); $this->assertEquals([], $tableDiff->getRemovedPkColumns()); }
public function testCompareMultipleDifferences() { $c1 = new Column(); $c1->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c1->setNotNull(false); $c2 = new Column(); $c2->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $c2->getDomain()->replaceScale(2); $c2->getDomain()->replaceSize(3); $c2->setNotNull(true); $c2->getDomain()->setDefaultValue(new ColumnDefaultValue(123, ColumnDefaultValue::TYPE_VALUE)); $expectedChangedProperties = array('type' => array('INTEGER', 'DOUBLE'), 'sqlType' => array('INTEGER', 'DOUBLE'), 'scale' => array(NULL, 2), 'size' => array(NULL, 3), 'notNull' => array(false, true), 'defaultValueType' => array(NULL, ColumnDefaultValue::TYPE_VALUE), 'defaultValueValue' => array(NULL, 123)); $this->assertEquals($expectedChangedProperties, ColumnComparator::compareColumns($c1, $c2)); }
/** * Builds the DDL SQL for a Column object. * @return string */ public function getColumnDDL(Column $col) { $domain = $col->getDomain(); $ddl = array($this->quoteIdentifier($col->getName())); $sqlType = $domain->getSqlType(); if ($this->hasSize($sqlType) && $col->isDefaultSqlType($this)) { $ddl[] = $sqlType . $col->getSizeDefinition(); } else { $ddl[] = $sqlType; } if ($default = $this->getColumnDefaultValueDDL($col)) { $ddl[] = $default; } if ($notNull = $this->getNullString($col->isNotNull())) { $ddl[] = $notNull; } if ($autoIncrement = $col->getAutoIncrementString()) { $ddl[] = $autoIncrement; } return implode(' ', $ddl); }
public function testCompareModifiedIndices() { $t1 = new Table(); $c1 = new Column('Foo'); $c1->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c1->getDomain()->replaceSize(255); $c1->setNotNull(false); $t1->addColumn($c1); $i1 = new Index('Foo_Index'); $i1->addColumn($c1); $t1->addIndex($i1); $t2 = new Table(); $c2 = new Column('Foo'); $c2->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $c2->getDomain()->replaceScale(2); $c2->getDomain()->replaceSize(3); $c2->setNotNull(true); $c2->getDomain()->setDefaultValue(new ColumnDefaultValue(123, ColumnDefaultValue::TYPE_VALUE)); $t2->addColumn($c2); $i2 = new Unique('Foo_Index'); $i2->addColumn($c2); $t2->addIndex($i2); $tc = new PropelTableComparator(); $tc->setFromTable($t1); $tc->setToTable($t2); $nbDiffs = $tc->compareIndices(); $tableDiff = $tc->getTableDiff(); $this->assertEquals(1, $nbDiffs); $this->assertEquals(1, count($tableDiff->getModifiedIndices())); $this->assertEquals(array('Foo_Index' => array($i1, $i2)), $tableDiff->getModifiedIndices()); }
public function testGetModifyColumnDDLWithVarcharWithoutSizeAndPlatform() { $t1 = new Table('foo'); $c1 = new Column('bar'); $c1->setTable($t1); $c1->getDomain()->copy($this->getPlatform()->getDomainForType('VARCHAR')); $c1->getDomain()->replaceSize(null); $c1->getDomain()->replaceScale(null); $t1->addColumn($c1); $schema = <<<EOF <database name="test"> <table name="foo"> <column name="id" primaryKey="true" type="INTEGER" autoIncrement="true" /> <column name="bar"/> </table> </database> EOF; $xtad = new XmlToAppData(null); $appData = $xtad->parseString($schema); $db = $appData->getDatabase(); $table = $db->getTable('foo'); $c2 = $table->getColumn('bar'); $columnDiff = ColumnComparator::computeDiff($c1, $c2); $expected = false; $this->assertSame($expected, $columnDiff); }
public function testSetDomain() { $column = new Column(); $column->setDomain($this->getDomainMock()); $this->assertInstanceOf('Propel\\Generator\\Model\\Domain', $column->getDomain()); }
public function getColumnDDL(Column $col) { $domain = $col->getDomain(); $ddl = array($this->quoteIdentifier($col->getName())); $sqlType = $domain->getSqlType(); $table = $col->getTable(); if ($col->isAutoIncrement() && $table && $table->getIdMethodParameters() == null) { $sqlType = $col->getType() === PropelTypes::BIGINT ? 'bigserial' : 'serial'; } if ($this->hasSize($sqlType) && $col->isDefaultSqlType($this)) { $ddl[] = $sqlType . $domain->printSize(); } else { $ddl[] = $sqlType; } if ($default = $this->getColumnDefaultValueDDL($col)) { $ddl[] = $default; } if ($notNull = $this->getNullString($col->isNotNull())) { $ddl[] = $notNull; } if ($autoIncrement = $col->getAutoIncrementString()) { $ddl[] = $autoIncrement; } return implode(' ', $ddl); }
/** * Appends the generated <column> XML node to its parent node. * * @param Column $column The Column model instance * @param \DOMNode $parentNode The parent DOMNode object */ private function appendColumnNode(Column $column, \DOMNode $parentNode) { $columnNode = $parentNode->appendChild($this->document->createElement('column')); $columnNode->setAttribute('name', $column->getName()); if ($phpName = $column->getPhpName()) { $columnNode->setAttribute('phpName', $phpName); } $columnNode->setAttribute('type', $column->getType()); $domain = $column->getDomain(); if ($size = $domain->getSize()) { $columnNode->setAttribute('size', $size); } if (null !== ($scale = $domain->getScale())) { $columnNode->setAttribute('scale', $scale); } $platform = $column->getPlatform(); if ($platform && !$column->isDefaultSqlType($platform)) { $columnNode->setAttribute('sqlType', $domain->getSqlType()); } if ($description = $column->getDescription()) { $columnNode->setAttribute('description', $description); } if ($column->isPrimaryKey()) { $columnNode->setAttribute('primaryKey', 'true'); } if ($column->isAutoIncrement()) { $columnNode->setAttribute('autoIncrement', 'true'); } if ($column->isNotNull()) { $columnNode->setAttribute('required', 'true'); } $defaultValue = $domain->getDefaultValue(); if ($defaultValue) { $type = $defaultValue->isExpression() ? 'defaultExpr' : 'defaultValue'; $columnNode->setAttribute($type, $defaultValue->getValue()); } if ($column->isInheritance()) { $columnNode->setAttribute('inheritance', $column->getInheritanceType()); foreach ($column->getInheritanceList() as $inheritance) { $this->appendInheritanceNode($inheritance, $columnNode); } } if ($column->isNodeKey()) { $columnNode->setAttribute('nodeKey', 'true'); if ($nodeKeySeparator = $column->getNodeKeySep()) { $columnNode->setAttribute('nodeKeySep', $nodeKeySeparator); } } foreach ($column->getVendorInformation() as $vendorInformation) { $this->appendVendorInformationNode($vendorInformation, $columnNode); } }
/** * Adds Columns to the specified table. * * @param Table $table The Table model class to add columns to. */ protected function addColumns(Table $table) { $stmt = $this->dbh->query("SELECT COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, DATA_DEFAULT FROM USER_TAB_COLS WHERE TABLE_NAME = '" . $table->getName() . "'"); while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { if (false !== strpos($row['COLUMN_NAME'], '$')) { // this is an Oracle internal column - prune continue; } $size = $row['DATA_PRECISION'] ? $row['DATA_PRECISION'] : $row['DATA_LENGTH']; $scale = $row['DATA_SCALE']; $default = $row['DATA_DEFAULT']; $type = $row['DATA_TYPE']; $isNullable = 'Y' === $row['NULLABLE']; if ($type === 'NUMBER' && $row['DATA_SCALE'] > 0) { $type = 'DECIMAL'; } if ($type === 'NUMBER' && $size > 9) { $type = 'BIGINT'; } if ($type === 'FLOAT' && $row['DATA_PRECISION'] == 126) { $type = 'DOUBLE'; } if (false !== strpos($type, 'TIMESTAMP(')) { $type = substr($type, 0, strpos($type, '(')); $default = '0000-00-00 00:00:00'; $size = null; $scale = null; } if ('DATE' === $type) { $default = '0000-00-00'; $size = null; $scale = null; } $propelType = $this->getMappedPropelType($type); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $this->warn('Column [' . $table->getName() . '.' . $row['COLUMN_NAME'] . '] has a column type (' . $row['DATA_TYPE'] . ') that Propel does not support.'); } $column = new Column($row['COLUMN_NAME']); $column->setPhpName(); // Prevent problems with strange col names $column->setTable($table); $column->setDomainForType($propelType); $column->getDomain()->setOriginSqlType($type); $column->getDomain()->replaceSize($size); $column->getDomain()->replaceScale($scale); if ($default !== null) { $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, ColumnDefaultValue::TYPE_VALUE)); } $column->setAutoIncrement(false); // This flag sets in self::parse() $column->setNotNull(!$isNullable); $table->addColumn($column); } }
public function createMigrationTable($datasource) { $platform = $this->getPlatform($datasource); // modelize the table $database = new Database($datasource); $database->setPlatform($platform); $table = new Table($this->getMigrationTable()); $database->addTable($table); $column = new Column('version'); $column->getDomain()->copy($platform->getDomainForType('INTEGER')); $column->setDefaultValue(0); $table->addColumn($column); // insert the table into the database $statements = $platform->getAddTableDDL($table); $conn = $this->getAdapterConnection($datasource); $res = SqlParser::executeString($statements, $conn); if (!$res) { throw new \Exception(sprintf('Unable to create migration table in datasource "%s"', $datasource)); } }
/** * Adds Columns to the specified table. * * @param Table $table The Table model class to add columns to. */ protected function addColumns(Table $table) { $tableName = $table->getName(); // var_dump("PRAGMA table_info('$tableName') //"); $stmt = $this->dbh->query("PRAGMA table_info('{$tableName}')"); while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $name = $row['name']; $fulltype = $row['type']; $size = null; $scale = null; if (preg_match('/^([^\\(]+)\\(\\s*(\\d+)\\s*,\\s*(\\d+)\\s*\\)$/', $fulltype, $matches)) { $type = $matches[1]; $size = $matches[2]; $scale = $matches[3]; } elseif (preg_match('/^([^\\(]+)\\(\\s*(\\d+)\\s*\\)$/', $fulltype, $matches)) { $type = $matches[1]; $size = $matches[2]; } else { $type = $fulltype; } $notNull = $row['notnull']; $default = $row['dflt_value']; $propelType = $this->getMappedPropelType(strtolower($type)); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $this->warn('Column [' . $table->getName() . '.' . $name . '] has a column type (' . $type . ') that Propel does not support.'); } $column = new Column($name); $column->setTable($table); $column->setDomainForType($propelType); // We may want to provide an option to include this: // $column->getDomain()->replaceSqlType($type); $column->getDomain()->replaceSize($size); $column->getDomain()->replaceScale($scale); if (null !== $default) { if ("'" !== substr($default, 0, 1) && strpos($default, '(')) { $defaultType = ColumnDefaultValue::TYPE_EXPR; if ('datetime(CURRENT_TIMESTAMP, \'localtime\')' === $default) { $default = 'CURRENT_TIMESTAMP'; } } else { $defaultType = ColumnDefaultValue::TYPE_VALUE; $default = str_replace("'", '', $default); } $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, $defaultType)); } $column->setNotNull($notNull); if (0 < $row['pk'] + 0) { $column->setPrimaryKey(true); } if ($column->isPrimaryKey()) { // check if autoIncrement $autoIncrementStmt = $this->dbh->prepare(' SELECT tbl_name FROM sqlite_master WHERE tbl_name = ? AND sql LIKE "%AUTOINCREMENT%" '); $autoIncrementStmt->execute([$table->getName()]); $autoincrementRow = $autoIncrementStmt->fetch(\PDO::FETCH_ASSOC); if ($autoincrementRow && $autoincrementRow['tbl_name'] == $table->getName()) { $column->setAutoIncrement(true); } } $table->addColumn($column); } }
public function testNormalizeTable() { $column = new Column('price', 'DECIMAL'); $column->getDomain()->copy($this->getPlatform()->getDomainForType('DECIMAL')); $column->setSize(10); $column->setScale(3); $table = new Table('prices'); $table->addColumns([$column]); $this->getPlatform()->normalizeTable($table); $this->assertEquals("`price` DECIMAL(10,3)", $this->getPlatform()->getColumnDDL($column)); }
public function testCompareSeveralColumnDifferences() { $t1 = new Table(); $c1 = new Column('col1'); $c1->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c1->getDomain()->replaceSize(255); $c1->setNotNull(false); $t1->addColumn($c1); $c2 = new Column('col2'); $c2->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c2->setNotNull(true); $t1->addColumn($c2); $c3 = new Column('col3'); $c3->getDomain()->copy($this->platform->getDomainForType('VARCHAR')); $c3->getDomain()->replaceSize(255); $t1->addColumn($c3); $t2 = new Table(); $c4 = new Column('col1'); $c4->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $c4->getDomain()->replaceScale(2); $c4->getDomain()->replaceSize(3); $c4->setNotNull(true); $c4->getDomain()->setDefaultValue(new ColumnDefaultValue(123, ColumnDefaultValue::TYPE_VALUE)); $t2->addColumn($c4); $c5 = new Column('col22'); $c5->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $c5->setNotNull(true); $t2->addColumn($c5); $c6 = new Column('col4'); $c6->getDomain()->copy($this->platform->getDomainForType('LONGVARCHAR')); $c6->getDomain()->setDefaultValue(new ColumnDefaultValue('123', ColumnDefaultValue::TYPE_VALUE)); $t2->addColumn($c6); // col1 was modified, col2 was renamed, col3 was removed, col4 was added $tc = new PropelTableComparator(); $tc->setFromTable($t1); $tc->setToTable($t2); $nbDiffs = $tc->compareColumns(); $tableDiff = $tc->getTableDiff(); $this->assertEquals(4, $nbDiffs); $this->assertEquals(array(array($c2, $c5)), $tableDiff->getRenamedColumns()); $this->assertEquals(array('col4' => $c6), $tableDiff->getAddedColumns()); $this->assertEquals(array('col3' => $c3), $tableDiff->getRemovedColumns()); $columnDiff = PropelColumnComparator::computeDiff($c1, $c4); $this->assertEquals(array('col1' => $columnDiff), $tableDiff->getModifiedColumns()); }
/** * Adds Columns to the specified table. * * @param Table $table The Table model class to add columns to. * @param int $oid The table OID */ protected function addColumns(Table $table, $oid) { // Get the columns, types, etc. // Based on code from pgAdmin3 (http://www.pgadmin.org/) $searchPath = '?'; $params = [$table->getDatabase()->getSchema()]; if ($schema = $table->getSchema()) { $searchPath = '?'; $params = [$schema]; } else { if (!$table->getDatabase()->getSchema()) { $stmt = $this->dbh->query('SHOW search_path'); $searchPathString = $stmt->fetchColumn(); $params = []; $searchPath = explode(',', $searchPathString); foreach ($searchPath as &$path) { $params[] = $path; $path = '?'; } $searchPath = implode(', ', $searchPath); } } $stmt = $this->dbh->prepare("\n SELECT\n column_name,\n data_type,\n column_default,\n is_nullable,\n numeric_precision,\n numeric_scale,\n character_maximum_length\n FROM information_schema.columns\n WHERE\n table_schema IN ({$searchPath}) AND table_name = ?\n "); $params[] = $table->getCommonName(); $stmt->execute($params); while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $size = $row['character_maximum_length']; if (!$size) { $size = $row['numeric_precision']; } $scale = $row['numeric_scale']; $name = $row['column_name']; $type = $row['data_type']; $default = $row['column_default']; $isNullable = true === $row['is_nullable'] || 'YES' === strtoupper($row['is_nullable']); // Check to ensure that this column isn't an array data type if ('ARRAY' === $type) { $this->warn(sprintf('Array datatypes are not currently supported [%s.%s]', $table->getName(), $name)); continue; } $autoincrement = null; // if column has a default if (strlen(trim($default)) > 0) { if (!preg_match('/^nextval\\(/', $default)) { $strDefault = preg_replace('/::[\\W\\D]*/', '', $default); } else { $autoincrement = true; $default = null; } } else { $default = null; } $propelType = $this->getMappedPropelType($type); if (!$propelType) { $propelType = Column::DEFAULT_TYPE; $this->warn('Column [' . $table->getName() . '.' . $name . '] has a column type (' . $type . ') that Propel does not support.'); } if (isset(static::$defaultTypeSizes[$type]) && $size == static::$defaultTypeSizes[$type]) { $size = null; } if ('SERIAL' === substr(strtoupper($type), 0, 6)) { $autoincrement = true; $default = null; } $column = new Column($name); $column->setTable($table); $column->setDomainForType($propelType); $column->getDomain()->replaceSize($size); if ($scale) { $column->getDomain()->replaceScale($scale); } if (null !== $default) { if ("'" !== substr($default, 0, 1) && strpos($default, '(')) { $defaultType = ColumnDefaultValue::TYPE_EXPR; } else { $defaultType = ColumnDefaultValue::TYPE_VALUE; $default = str_replace("'", '', $strDefault); } $column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, $defaultType)); } $column->setAutoIncrement($autoincrement); $column->setNotNull(!$isNullable); $table->addColumn($column); } }
public function testGetColumnDDLCustomSqlType() { $column = new Column('foo'); $column->getDomain()->copy($this->getPlatform()->getDomainForType('DOUBLE')); $column->getDomain()->replaceScale(2); $column->getDomain()->replaceSize(3); $column->setNotNull(true); $column->getDomain()->setDefaultValue(new ColumnDefaultValue(123, ColumnDefaultValue::TYPE_VALUE)); $column->getDomain()->replaceSqlType('DECIMAL(5,6)'); $expected = '[foo] DECIMAL(5,6) DEFAULT 123 NOT NULL'; $this->assertEquals($expected, $this->getPlatform()->getColumnDDL($column)); }
public function testCompareSeveralTableDifferences() { $d1 = new Database(); $t1 = new Table('Foo_Table'); $c1 = new Column('Foo'); $c1->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $c1->getDomain()->replaceScale(2); $c1->getDomain()->replaceSize(3); $c1->setNotNull(true); $c1->getDomain()->setDefaultValue(new ColumnDefaultValue(123, ColumnDefaultValue::TYPE_VALUE)); $t1->addColumn($c1); $d1->addTable($t1); $t2 = new Table('Bar'); $c2 = new Column('Bar_Column'); $c2->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $t2->addColumn($c2); $d1->addTable($t2); $t11 = new Table('Baz'); $d1->addTable($t11); $d2 = new Database(); $t3 = new Table('Foo_Table'); $c3 = new Column('Foo1'); $c3->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $c3->getDomain()->replaceScale(2); $c3->getDomain()->replaceSize(3); $c3->setNotNull(true); $c3->getDomain()->setDefaultValue(new ColumnDefaultValue(123, ColumnDefaultValue::TYPE_VALUE)); $t3->addColumn($c3); $d2->addTable($t3); $t4 = new Table('Bar2'); $c4 = new Column('Bar_Column'); $c4->getDomain()->copy($this->platform->getDomainForType('DOUBLE')); $t4->addColumn($c4); $d2->addTable($t4); $t5 = new Table('Biz'); $c5 = new Column('Biz_Column'); $c5->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t5->addColumn($c5); $d2->addTable($t5); // Foo_Table was modified, Bar was renamed, Baz was removed, Biz was added $dc = new DatabaseComparator(); $dc->setFromDatabase($d1); $dc->setToDatabase($d2); $nbDiffs = $dc->compareTables(); $databaseDiff = $dc->getDatabaseDiff(); $this->assertEquals(4, $nbDiffs); $this->assertEquals(array('Bar' => 'Bar2'), $databaseDiff->getRenamedTables()); $this->assertEquals(array('Biz' => $t5), $databaseDiff->getAddedTables()); $this->assertEquals(array('Baz' => $t11), $databaseDiff->getRemovedTables()); $tableDiff = TableComparator::computeDiff($t1, $t3); $this->assertEquals(array('Foo_Table' => $tableDiff), $databaseDiff->getModifiedTables()); }
public function providerForTestGetModifyColumnRemoveDefaultValueDDL() { $t1 = new Table('test'); $t1->setIdentifierQuoting(true); $c1 = new Column(); $c1->setName('test'); $c1->getDomain()->setType('INTEGER'); $c1->setDefaultValue(0); $t1->addColumn($c1); $t2 = new Table('test'); $t2->setIdentifierQuoting(true); $c2 = new Column(); $c2->setName('test'); $c2->getDomain()->setType('INTEGER'); $t2->addColumn($c2); return [[ColumnComparator::computeDiff($c1, $c2)]]; }
public function testCompareSeveralRenamedSameTables() { $d1 = new Database(); $t1 = new Table('table1'); $c1 = new Column('col1'); $c1->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t1->addColumn($c1); $d1->addTable($t1); $t2 = new Table('table2'); $c2 = new Column('col1'); $c2->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t2->addColumn($c2); $d1->addTable($t2); $t3 = new Table('table3'); $c3 = new Column('col1'); $c3->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t3->addColumn($c3); $d1->addTable($t3); $d2 = new Database(); $t4 = new Table('table4'); $c4 = new Column('col1'); $c4->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t4->addColumn($c4); $d2->addTable($t4); $t5 = new Table('table5'); $c5 = new Column('col1'); $c5->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t5->addColumn($c5); $d2->addTable($t5); $t6 = new Table('table3'); $c6 = new Column('col1'); $c6->getDomain()->copy($this->platform->getDomainForType('INTEGER')); $t6->addColumn($c6); $d2->addTable($t6); // table1 and table2 were removed and table4, table5 added with same columns (does not always mean its a rename, hence we // can not guarantee it) $dc = new DatabaseComparator(); $dc->setFromDatabase($d1); $dc->setToDatabase($d2); $nbDiffs = $dc->compareTables(); $databaseDiff = $dc->getDatabaseDiff(); $this->assertEquals(4, $nbDiffs); $this->assertEquals(0, count($databaseDiff->getRenamedTables())); $this->assertEquals(array('table4', 'table5'), array_keys($databaseDiff->getAddedTables())); $this->assertEquals(array('table1', 'table2'), array_keys($databaseDiff->getRemovedTables())); }