public function testGetPrimaryKeyDDLCompositeKeyWithAutoIncrement()
{
$table = new Table('foo');
$table->setIdMethod(IdMethod::NATIVE);
$column1 = new Column('bar');
$column1->setPrimaryKey(true);
$table->addColumn($column1);
$column2 = new Column('baz');
$column2->setPrimaryKey(true);
$column2->setAutoIncrement(true);
$table->addColumn($column2);
$expected = '';
$this->assertEquals($expected, $this->getPlatform()->getPrimaryKeyDDL($table));
}
public function testGetColumnDDLAutoIncrement()
{
$database = new Database();
$database->setPlatform($this->getPlatform());
$table = new Table('foo_table');
$table->setIdMethod(IdMethod::NATIVE);
$database->addTable($table);
$column = new Column('foo');
$column->getDomain()->copy($this->getPlatform()->getDomainForType(PropelTypes::BIGINT));
$column->setAutoIncrement(true);
$table->addColumn($column);
$expected = 'foo bigserial';
$this->assertEquals($expected, $this->getPlatform()->getColumnDDL($column));
}
/**
* 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);
}
}
/**
* 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);
}
}
$column32->setNotNull();
$column41 = new Column('id', 'integer', 7);
$column41->setAutoIncrement();
$column41->setNotNull();
$column41->setPrimaryKey();
$column42 = new Column('name', 'varchar', 40);
$column42->setNotNull();
$column51 = new Column('post_id', 'integer', 7);
$column51->setNotNull();
$column51->setPrimaryKey();
$column52 = new Column('tag_id', 'integer', 7);
$column52->setNotNull();
$column52->setPrimaryKey();
$column61 = new Column('id', 'integer', 5);
$column61->setNotNull();
$column61->setAutoIncrement();
$column61->setPrimaryKey();
$column62 = new Column('title', 'varchar', 150);
$column62->setNotNull();
$column63 = new Column('content', 'clob');
$column63->addVendorInfo(new VendorInfo('mysql', ['Charset' => 'latin1', 'Collate' => 'latin1_general_ci']));
$column64 = new Column('is_published', 'boolean');
$column64->setNotNull();
$column64->setDefaultValue('false');
/* Foreign Keys */
$fkAuthorPost = new ForeignKey('fk_post_has_author');
$fkAuthorPost->addReference('author_id', 'id');
$fkAuthorPost->setForeignTableCommonName('blog_author');
$fkAuthorPost->setRefPhpName('Posts');
$fkAuthorPost->setPhpName('Author');
$fkAuthorPost->setDefaultJoin('Criteria::LEFT_JOIN');
/**
* 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);
}
}
/**
* 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 testSetAutoIncrement()
{
$column = new Column();
$column->setAutoIncrement(true);
$this->assertTrue($column->isAutoIncrement());
}
/**
* 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 testCompareAutoincrement()
{
$c1 = new Column();
$c1->setAutoIncrement(true);
$c2 = new Column();
$c2->setAutoIncrement(false);
$expectedChangedProperties = array('autoIncrement' => array(true, false));
$this->assertEquals($expectedChangedProperties, ColumnComparator::compareColumns($c1, $c2));
}