protected function doExecute()
{
$this->step('Creating schema');
$configuration = new \Firenote\Configuration\Yaml($this->rootPath . 'config');
$app = new \Firenote\Application($configuration);
$this->createDatabase($configuration);
$schema = $app['db']->getSchemaManager();
if (!$schema instanceof \Doctrine\DBAL\Schema\AbstractSchemaManager) {
throw new \Exception();
}
if (!$schema->tablesExist('users')) {
$this->writeln('<info>Creating table users ...</info>');
$users = new Table('users');
$users->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$users->setPrimaryKey(array('id'));
$users->addColumn('username', 'string', array('length' => 32));
$users->addUniqueIndex(array('username'));
$users->addColumn('password', 'string', array('length' => 255));
$users->addColumn('roles', 'string', array('length' => 255));
$users->addColumn('avatar', 'string', array('length' => 512));
$schema->createTable($users);
$this->writeln('<info>Adding admin user (admin/foo) ...</info>');
$this->writeln('<comment>Please change this dummy password !</comment>');
$app['db']->insert('users', array('username' => 'admin', 'password' => '5FZ2Z8QIkA7UTZ4BYkoC+GsReLf569mSKDsfods6LYQ8t+a8EW9oaircfMpmaLbPBh4FOBiiFyLfuZmTSUwzZg==', 'roles' => 'ROLE_ADMIN', 'avatar' => '/assets/firenote/avatars/avatar2.png'));
} else {
$this->writeln('Nothing to do !');
}
}
/**
* Drops the tables and rebuilds them
*/
public function rebuildSchema()
{
$schemaManager = $this->conn->getSchemaManager();
$productTable = new Table('product');
$productTable->addColumn("id", "integer", array("unsigned" => true));
$productTable->addColumn("name", "string", array("length" => 255));
$productTable->addColumn('author_id', 'integer', array('notNull' => false));
$productTable->addColumn("description", "text", array('notNull' => false));
$productTable->addColumn("price", "decimal", array('scale' => 2, 'notNull' => false));
$productTable->addColumn("is_published", "boolean");
$productTable->addColumn('created_at', 'datetime');
$productTable->setPrimaryKey(array("id"));
$schemaManager->dropAndCreateTable($productTable);
$userTable = new Table('user');
$userTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$userTable->setPrimaryKey(array('id'));
$userTable->addColumn('username', 'string', array('length' => 32));
$userTable->addUniqueIndex(array('username'));
$userTable->addColumn('password', 'string', array('length' => 255));
$userTable->addColumn('roles', 'string', array('length' => 255));
$userTable->addColumn('created_at', 'datetime');
// add an author_id to product
$productTable->addForeignKeyConstraint($userTable, array('author_id'), array('id'));
$schemaManager->dropAndCreateTable($userTable);
}
/**
* Defines the table at the specified version
*
* @param Table $table Table
* @param string $version Version
*/
public function define(Table $table, $version)
{
switch (true) {
// Version 0.1.0
case version_compare($version, "0.1.0", '>='):
$table->addColumn('id', 'integer')->setUnsigned(true)->setNotNull(true)->setAutoIncrement(true)->setComment('Country ID (local db)');
$table->addColumn('code', 'string')->setLength(2)->setNotNull(true)->setComment("ISO code (2 char)");
$table->addColumn('name', 'string')->setLength(50)->setNotNull(true)->setComment("Internationally recognised name");
$table->addColumn('domain', 'string')->setLength(2)->setNotNull(false)->setComment("Top level domain suffix");
$table->addColumn('postal_code_format', 'string')->setLength(60)->setNotNull(false)->setComment("Postal code format");
$table->addColumn('postal_code_regex', 'string')->setLength(180)->setNotNull(false)->setComment("Postal code regex");
$table->addColumn('phone_prefix', 'string')->setLength(20)->setNotNull(true)->setComment("Phone number prefix");
// Primary key
$table->setPrimaryKey(['id'], 'PK_GeoCountry_id');
// Unique Keys
$table->addUniqueIndex(['code'], 'UK_GeoCountry_code');
$table->addUniqueIndex(['name'], 'UK_GeoCountry_name');
}
}
/**
* Builds table structure.
*
* @param Table $table
*/
protected function buildTable(Table $table)
{
$table->addColumn('id', 'bigint')->setUnsigned(true)->setAutoincrement(true);
$table->addColumn('type', 'string')->setLength(1)->setComment('C-CREATE(INSERT),U-UPDATE,D-DELETE');
$table->addColumn('document_type', 'string')->setLength(32);
$table->addColumn('document_id', 'string')->setLength(32);
$table->addColumn('timestamp', 'datetime');
$table->addColumn('status', 'boolean', ['default' => self::STATUS_NEW])->setComment('0-new,1-inProgress,2-error');
$table->setPrimaryKey(['id']);
$table->addUniqueIndex(['type', 'document_type', 'document_id', 'status']);
}
public function testEncodedUniqueIndexNameIsTheSameAsDoctrineDefault()
{
$tableName = 'tbl123456789012345';
$columnName = 'clmn1234567890';
$table = new Table($tableName, [new Column($columnName, Type::getType('string'))]);
$table->addUniqueIndex([$columnName]);
$indices = $table->getIndexes();
$doctrineResult = array_pop($indices)->getName();
$generator = new DbIdentifierNameGenerator();
$result = $generator->generateIndexName($tableName, [$columnName], true);
$this->assertEquals($doctrineResult, $result);
}
public function testUniquePrimaryKey()
{
$keyTable = new Table("foo");
$keyTable->addColumn("bar", "integer");
$keyTable->addColumn("baz", "string");
$keyTable->setPrimaryKey(array("bar"));
$keyTable->addUniqueIndex(array("baz"));
$oldTable = new Table("foo");
$oldTable->addColumn("bar", "integer");
$oldTable->addColumn("baz", "string");
$c = new \Doctrine\DBAL\Schema\Comparator();
$diff = $c->diffTable($oldTable, $keyTable);
$sql = $this->_platform->getAlterTableSQL($diff);
$this->assertEquals(array("ALTER TABLE foo ADD PRIMARY KEY (bar)", "CREATE UNIQUE INDEX UNIQ_8C73652178240498 ON foo (baz)"), $sql);
}
/**
* Defines the table at the specified version
*
* @param Table $table Table
* @param string $version Version
*/
public function define(Table $table, $version)
{
switch (true) {
// Version 0.1.0
case version_compare($version, "0.1.0", '>='):
$table->addColumn('id', 'integer')->setUnsigned(true)->setNotNull(true)->setAutoIncrement(true)->setComment('Timezone ID (local db)');
$table->addColumn('country_id', 'integer')->setUnsigned(true)->setNotNull(true)->setComment('Country (=> ' . CountryTableDefinition::NAME . '.id)');
$table->addColumn('code', 'string')->setLength(50)->setNotNull(false)->setComment("Timezone code");
// Primary key
$table->setPrimaryKey(['id'], 'PK_GeoTimezone_id');
// Foriegn keys
$table->addNamedForeignKeyConstraint('FK_GeoTimezone_country', CountryTableDefinition::NAME, ['country_id'], ['id']);
// Unique Keys
$table->addUniqueIndex(['code'], 'UK_GeoTimezone_code');
}
}
public function __construct(Application $app)
{
$this->app = $app;
$this->db = $app['db'];
$schema = $this->db->getSchemaManager();
if (!$schema->tablesExist('users')) {
$users = new Table('users');
$users->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$users->setPrimaryKey(array('id'));
$users->addColumn('username', 'string', array('length' => 32));
$users->addUniqueIndex(array('username'));
$users->addColumn('password', 'string', array('length' => 255));
$users->addColumn('roles', 'string', array('length' => 255));
$schema->createTable($users);
$app['db']->insert('users', array('username' => 'fabien', 'password' => '5FZ2Z8QIkA7UTZ4BYkoC+GsReLf569mSKDsfods6LYQ8t+a8EW9oaircfMpmaLbPBh4FOBiiFyLfuZmTSUwzZg==', 'roles' => 'ROLE_USER'));
$app['db']->insert('users', array('username' => 'admin', 'password' => '5FZ2Z8QIkA7UTZ4BYkoC+GsReLf569mSKDsfods6LYQ8t+a8EW9oaircfMpmaLbPBh4FOBiiFyLfuZmTSUwzZg==', 'roles' => 'ROLE_ADMIN'));
}
}
/**
* Создание таблицы пользователей
*
* @param App $app
* @return string
*/
public function createAction(App $app)
{
/** @var $schema MySqlSchemaManager */
$schema = $app['db']->getSchemaManager();
if (!$schema->tablesExist('users')) {
$users = new Table('users');
$users->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$users->setPrimaryKey(array('id'));
$users->addColumn('username', 'string', array('length' => 32));
$users->addUniqueIndex(array('username'));
$users->addColumn('password', 'string', array('length' => 255));
$users->addColumn('roles', 'string', array('length' => 255));
$schema->createTable($users);
$app['db']->insert('users', array('username' => 'user', 'password' => (new MessageDigestPasswordEncoder())->encodePassword('user', ''), 'roles' => 'ROLE_USER'));
$app['db']->insert('users', array('username' => 'admin', 'password' => (new MessageDigestPasswordEncoder())->encodePassword('admin', ''), 'roles' => 'ROLE_ADMIN'));
}
return 'Done!';
}
/**
* @param \TYPO3\CMS\Core\Database\Schema\Parser\AST\CreateColumnDefinitionItem $item
* @return \Doctrine\DBAL\Schema\Column
* @throws \Doctrine\DBAL\Schema\SchemaException
* @throws \RuntimeException
*/
protected function addColumn(CreateColumnDefinitionItem $item) : Column
{
$column = $this->table->addColumn($item->columnName->getQuotedName(), $this->getDoctrineColumnTypeName($item->dataType));
$column->setNotnull(!$item->allowNull);
$column->setAutoincrement((bool) $item->autoIncrement);
$column->setComment($item->comment);
// Set default value (unless it's an auto increment column)
if ($item->hasDefaultValue && !$column->getAutoincrement()) {
$column->setDefault($item->defaultValue);
}
if ($item->dataType->getLength()) {
$column->setLength($item->dataType->getLength());
}
if ($item->dataType->getPrecision() >= 0) {
$column->setPrecision($item->dataType->getPrecision());
}
if ($item->dataType->getScale() >= 0) {
$column->setScale($item->dataType->getScale());
}
if ($item->dataType->isUnsigned()) {
$column->setUnsigned(true);
}
// Select CHAR/VARCHAR or BINARY/VARBINARY
if ($item->dataType->isFixed()) {
$column->setFixed(true);
}
if ($item->dataType instanceof DataType\EnumDataType || $item->dataType instanceof DataType\SetDataType) {
$column->setPlatformOption('unquotedValues', $item->dataType->getValues());
}
if ($item->index) {
$this->table->addIndex([$item->columnName->getQuotedName()]);
}
if ($item->unique) {
$this->table->addUniqueIndex([$item->columnName->getQuotedName()]);
}
if ($item->primary) {
$this->table->setPrimaryKey([$item->columnName->getQuotedName()]);
}
if ($item->reference !== null) {
$this->addForeignKeyConstraint([$item->columnName->getQuotedName()], $item->reference);
}
return $column;
}
public function initialize(array $options = [])
{
$tableName = $this->options[self::OPTION_TABLENAME];
if (!method_exists($this->connection, 'getSchemaManager')) {
throw new \RuntimeException('The provided connection does not support query building, please choose a different connection type ' . 'that does');
}
if ($this->connection->getSchemaManager()->tablesExist([$tableName])) {
return;
}
$table = new Table($tableName);
$table->addColumn('id', Type::STRING, ['length' => '36']);
$table->addColumn('stream_id', Type::STRING, ['length' => '36']);
$table->addColumn('sequence', Type::BIGINT);
$table->addColumn('payload', Type::TEXT);
$table->addColumn('emitted_at', Type::DATETIME);
$table->setPrimaryKey(['id']);
$table->addIndex(['stream_id']);
$table->addUniqueIndex(['stream_id', 'sequence']);
$this->connection->getSchemaManager()->createTable($table);
}
/**
* Defines the locality columns and constraints
*
* @param string $locality Locality type name
* @param Table $table Database table
* @param string $version Bundle version
*/
protected function defineLocality($locality, Table $table, $version)
{
switch (true) {
// Version 0.1.0
case version_compare($version, "0.1.0", '>='):
$table->addColumn('id', 'integer')->setUnsigned(true)->setNotNull(true)->setAutoIncrement(true)->setComment("{$locality} ID");
$table->addColumn('geoname_id', 'integer')->setUnsigned(true)->setNotNull(false)->setComment('GeoNames.org ID');
$table->addColumn('country_id', 'integer')->setUnsigned(true)->setNotNull(true)->setComment('Country (=> ' . CountryTableDefinition::NAME . '.id)');
$table->addColumn('name_utf8', 'string')->setLength(200)->setNotNull(true)->setComment("Name (UTF-8 encoding)");
$table->addColumn('name_ascii', 'string')->setLength(200)->setNotNull(false)->setComment("Name (ASCII encoding)");
$table->addColumn('latitude', 'decimal')->setPrecision(9)->setScale(6)->setNotNull(false)->setComment("Latitude coordinate");
$table->addColumn('longitude', 'decimal')->setPrecision(9)->setScale(6)->setNotNull(false)->setComment("Longitude coordinate");
$table->addColumn('timezone_id', 'integer')->setUnsigned(true)->setNotNull(false)->setComment("Timezone");
$table->addColumn('creation_date', 'datetime')->setNotNull(true)->setComment("Database creation date");
$table->addColumn('modification_date', 'datetime')->setNotNull(false)->setComment("Database modification date");
// Primary key
$table->setPrimaryKey(['id'], "PK_Geo{$locality}_id");
// Unique Keys
$table->addUniqueIndex(['geoname_id'], 'UK_Geo{$locality}_geoname');
// Foriegn keys
$table->addNamedForeignKeyConstraint("FK_Geo{$locality}_country", CountryTableDefinition::NAME, ['country_id'], ['id']);
$table->addNamedForeignKeyConstraint("FK_Geo{$locality}_timezone", TimezoneTableDefinition::NAME, ['timezone_id'], ['id']);
}
}
public function resetDatabase()
{
$dbPath = $this->app['sqlite_path'];
$dbDir = dirname($dbPath);
$filesystem = new Filesystem();
$filesystem->mkdir($dbDir);
$filesystem->chmod($dbDir, 0777, 00, true);
if (!is_writable($dbDir)) {
throw new \Exception('Unable to write to ' . $dbPath);
}
$schemaManager = $this->getConnection()->getSchemaManager();
$userTable = new Table('user');
$userTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$userTable->setPrimaryKey(array('id'));
$userTable->addColumn('name', 'string', array('length' => 255));
$userTable->addUniqueIndex(array('name'));
$userTable->addColumn('age', 'integer');
$schemaManager->dropAndCreateTable($userTable);
$bookTable = new Table('book');
$bookTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$bookTable->setPrimaryKey(array('id'));
$bookTable->addColumn('name', 'string', array('length' => 255));
$bookTable->addUniqueIndex(array('name'));
$bookTable->addColumn('userID', 'integer');
$bookTable->addColumn('ISBN', 'integer');
$bookTable->addForeignKeyConstraint($userTable, array('userID'), array('id'));
$schemaManager->dropAndCreateTable($bookTable);
$bannedTable = new Table('banned');
$bannedTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$bannedTable->setPrimaryKey(array('id'));
$bannedTable->addColumn('name', 'string', array('length' => 255));
$bannedTable->addUniqueIndex(array('name'));
$bannedTable->addColumn('userID', 'integer');
$bannedTable->addColumn('ISBN', 'integer');
$schemaManager->dropAndCreateTable($bannedTable);
}
/**
* @group DBAL-50
*/
public function testOverruleIndex()
{
$table = new Table("bar");
$table->addColumn('baz', 'integer', array());
$table->addIndex(array('baz'));
$this->assertEquals(1, count($table->getIndexes()));
$this->assertTrue($table->hasIndex('bar_baz_idx'));
$table->addUniqueIndex(array('baz'));
$this->assertEquals(1, count($table->getIndexes()));
$this->assertFalse($table->hasIndex('bar_baz_idx'));
$this->assertTrue($table->hasIndex('bar_baz_uniq'));
}
/**
* @param Metadata $metadata
* @return Table
*/
public function metadataToTable(Metadata $metadata)
{
$tblName = $metadata->getDbTableName();
if (isset($this->md2tableCache[$tblName])) {
return $this->md2tableCache[$tblName];
}
$cols = [];
foreach ($metadata->getLocalFields() as $fieldObj) {
$col = $fieldObj->getDoctrineColumn();
$col->setLength($fieldObj->max_length);
$col->setNotnull(!$fieldObj->null);
$col->setComment($fieldObj->help_text);
$col->setAutoincrement($fieldObj->auto_increment);
$cols[] = $col;
}
$table = new Table($tblName, $cols);
$this->md2tableCache[$tblName] = $table;
foreach ($metadata->getLocalFields() as $fieldObj) {
if ($fieldObj->unique) {
$table->addUniqueIndex([$fieldObj->db_column]);
} elseif ($fieldObj->db_index) {
$table->addIndex([$fieldObj->db_column]);
}
if ($fieldObj->primary_key) {
$table->setPrimaryKey([$fieldObj->db_column]);
}
if ($this->followRelations === true && $fieldObj instanceof ForeignKey) {
$relationClass = $fieldObj->relationClass;
$relationTable = $this->metadataToTable($relationClass::metadata());
$table->addForeignKeyConstraint($relationTable, [$fieldObj->db_column], [$fieldObj->to_field]);
$this->generateQueue[] = $relationClass;
}
}
if ($this->followRelations === true) {
foreach ($metadata->getRelationFields() as $fieldObj) {
if ($fieldObj instanceof ManyToMany) {
if ($fieldObj->throughClass) {
$throughClass = $fieldObj->throughClass;
//$this->metadataToTable($throughClass::metadata());
$this->generateQueue[] = $throughClass;
}
}
}
}
return $table;
}
/**
* Gathers columns and fk constraints that are required for one part of relationship.
*
* @param array $joinColumns
* @param Table $theJoinTable
* @param ClassMetadata $class
* @param array $mapping
* @param array $primaryKeyColumns
* @param array $addedFks
* @param array $blacklistedFks
*
* @return void
*
* @throws \Doctrine\ORM\ORMException
*/
private function gatherRelationJoinColumns($joinColumns, $theJoinTable, $class, $mapping, &$primaryKeyColumns, &$addedFks, &$blacklistedFks)
{
$localColumns = array();
$foreignColumns = array();
$fkOptions = array();
$foreignTableName = $this->quoteStrategy->getTableName($class, $this->platform);
$uniqueConstraints = array();
foreach ($joinColumns as $joinColumn) {
list($definingClass, $referencedFieldName) = $this->getDefiningClass($class, $joinColumn['referencedColumnName']);
if (!$definingClass) {
throw new \Doctrine\ORM\ORMException("Column name `" . $joinColumn['referencedColumnName'] . "` referenced for relation from " . $mapping['sourceEntity'] . " towards " . $mapping['targetEntity'] . " does not exist.");
}
$quotedColumnName = $this->quoteStrategy->getJoinColumnName($joinColumn, $class, $this->platform);
$quotedRefColumnName = $this->quoteStrategy->getReferencedJoinColumnName($joinColumn, $class, $this->platform);
$primaryKeyColumns[] = $quotedColumnName;
$localColumns[] = $quotedColumnName;
$foreignColumns[] = $quotedRefColumnName;
if (!$theJoinTable->hasColumn($quotedColumnName)) {
// Only add the column to the table if it does not exist already.
// It might exist already if the foreign key is mapped into a regular
// property as well.
$fieldMapping = $definingClass->getFieldMapping($referencedFieldName);
$columnDef = null;
if (isset($joinColumn['columnDefinition'])) {
$columnDef = $joinColumn['columnDefinition'];
} elseif (isset($fieldMapping['columnDefinition'])) {
$columnDef = $fieldMapping['columnDefinition'];
}
$columnOptions = array('notnull' => false, 'columnDefinition' => $columnDef);
if (isset($joinColumn['nullable'])) {
$columnOptions['notnull'] = !$joinColumn['nullable'];
}
if (isset($fieldMapping['options'])) {
$columnOptions['options'] = $fieldMapping['options'];
}
if ($fieldMapping['type'] == "string" && isset($fieldMapping['length'])) {
$columnOptions['length'] = $fieldMapping['length'];
} elseif ($fieldMapping['type'] == "decimal") {
$columnOptions['scale'] = $fieldMapping['scale'];
$columnOptions['precision'] = $fieldMapping['precision'];
}
$theJoinTable->addColumn($quotedColumnName, $fieldMapping['type'], $columnOptions);
}
if (isset($joinColumn['unique']) && $joinColumn['unique'] == true) {
$uniqueConstraints[] = array('columns' => array($quotedColumnName));
}
if (isset($joinColumn['onDelete'])) {
$fkOptions['onDelete'] = $joinColumn['onDelete'];
}
}
// Prefer unique constraints over implicit simple indexes created for foreign keys.
// Also avoids index duplication.
foreach ($uniqueConstraints as $indexName => $unique) {
$theJoinTable->addUniqueIndex($unique['columns'], is_numeric($indexName) ? null : $indexName);
}
$compositeName = $theJoinTable->getName() . '.' . implode('', $localColumns);
if (isset($addedFks[$compositeName]) && ($foreignTableName != $addedFks[$compositeName]['foreignTableName'] || 0 < count(array_diff($foreignColumns, $addedFks[$compositeName]['foreignColumns'])))) {
foreach ($theJoinTable->getForeignKeys() as $fkName => $key) {
if (0 === count(array_diff($key->getLocalColumns(), $localColumns)) && ($key->getForeignTableName() != $foreignTableName || 0 < count(array_diff($key->getForeignColumns(), $foreignColumns)))) {
$theJoinTable->removeForeignKey($fkName);
break;
}
}
$blacklistedFks[$compositeName] = true;
} elseif (!isset($blacklistedFks[$compositeName])) {
$addedFks[$compositeName] = array('foreignTableName' => $foreignTableName, 'foreignColumns' => $foreignColumns);
$theJoinTable->addUnnamedForeignKeyConstraint($foreignTableName, $localColumns, $foreignColumns, $fkOptions);
}
}
/**
* @param \Doctrine\DBAL\Schema\Table $table
* @param \SimpleXMLElement $xml
* @throws \DomainException
*/
private function loadIndex($table, $xml)
{
$name = null;
$fields = array();
foreach ($xml->children() as $child) {
/**
* @var \SimpleXMLElement $child
*/
switch ($child->getName()) {
case 'name':
$name = (string) $child;
break;
case 'primary':
$primary = $this->asBool($child);
break;
case 'unique':
$unique = $this->asBool($child);
break;
case 'field':
foreach ($child->children() as $field) {
/**
* @var \SimpleXMLElement $field
*/
switch ($field->getName()) {
case 'name':
$field_name = (string) $field;
$field_name = $this->platform->quoteIdentifier($field_name);
$fields[] = $field_name;
break;
case 'sorting':
break;
default:
throw new \DomainException('Unknown element: ' . $field->getName());
}
}
break;
default:
throw new \DomainException('Unknown element: ' . $child->getName());
}
}
if (!empty($fields)) {
if (isset($primary) && $primary) {
$table->setPrimaryKey($fields, $name);
} else {
if (isset($unique) && $unique) {
$table->addUniqueIndex($fields, $name);
} else {
$table->addIndex($fields, $name);
}
}
} else {
throw new \DomainException('Empty index definition: ' . $name . ' options:' . print_r($fields, true));
}
}
/**
* Specify a unique index for the table.
*
* @param string|array $columns
* @param string $name
* @param array $options
*
* @return Blueprint
*/
public function unique($columns, $name = null, $options = [])
{
$columns = is_array($columns) ? $columns : [$columns];
return $this->table->addUniqueIndex($columns, $name, $options);
}
/**
* Adds a unique index
*
* @param string $fields fields
* @param string $name index name
* @return $this
*/
public function unique($fields, $name)
{
$this->table->addUniqueIndex((array) $fields, $name);
return $this;
}
/**
* @group DBAL-234
*/
public function testRenameIndex()
{
$table = new Table("test");
$table->addColumn('id', 'integer');
$table->addColumn('foo', 'integer');
$table->addColumn('bar', 'integer');
$table->addColumn('baz', 'integer');
$table->setPrimaryKey(array('id'), 'pk');
$table->addIndex(array('foo'), 'idx', array('flag'));
$table->addUniqueIndex(array('bar', 'baz'), 'uniq');
// Rename to custom name.
$this->assertSame($table, $table->renameIndex('pk', 'pk_new'));
$this->assertSame($table, $table->renameIndex('idx', 'idx_new'));
$this->assertSame($table, $table->renameIndex('uniq', 'uniq_new'));
$this->assertTrue($table->hasPrimaryKey());
$this->assertTrue($table->hasIndex('pk_new'));
$this->assertTrue($table->hasIndex('idx_new'));
$this->assertTrue($table->hasIndex('uniq_new'));
$this->assertFalse($table->hasIndex('pk'));
$this->assertFalse($table->hasIndex('idx'));
$this->assertFalse($table->hasIndex('uniq'));
$this->assertEquals(new Index('pk_new', array('id'), true, true), $table->getPrimaryKey());
$this->assertEquals(new Index('pk_new', array('id'), true, true), $table->getIndex('pk_new'));
$this->assertEquals(new Index('idx_new', array('foo'), false, false, array('flag')), $table->getIndex('idx_new'));
$this->assertEquals(new Index('uniq_new', array('bar', 'baz'), true), $table->getIndex('uniq_new'));
// Rename to auto-generated name.
$this->assertSame($table, $table->renameIndex('pk_new', null));
$this->assertSame($table, $table->renameIndex('idx_new', null));
$this->assertSame($table, $table->renameIndex('uniq_new', null));
$this->assertTrue($table->hasPrimaryKey());
$this->assertTrue($table->hasIndex('primary'));
$this->assertTrue($table->hasIndex('IDX_D87F7E0C8C736521'));
$this->assertTrue($table->hasIndex('UNIQ_D87F7E0C76FF8CAA78240498'));
$this->assertFalse($table->hasIndex('pk_new'));
$this->assertFalse($table->hasIndex('idx_new'));
$this->assertFalse($table->hasIndex('uniq_new'));
$this->assertEquals(new Index('primary', array('id'), true, true), $table->getPrimaryKey());
$this->assertEquals(new Index('primary', array('id'), true, true), $table->getIndex('primary'));
$this->assertEquals(new Index('IDX_D87F7E0C8C736521', array('foo'), false, false, array('flag')), $table->getIndex('IDX_D87F7E0C8C736521'));
$this->assertEquals(new Index('UNIQ_D87F7E0C76FF8CAA78240498', array('bar', 'baz'), true), $table->getIndex('UNIQ_D87F7E0C76FF8CAA78240498'));
// Rename to same name (changed case).
$this->assertSame($table, $table->renameIndex('primary', 'PRIMARY'));
$this->assertSame($table, $table->renameIndex('IDX_D87F7E0C8C736521', 'idx_D87F7E0C8C736521'));
$this->assertSame($table, $table->renameIndex('UNIQ_D87F7E0C76FF8CAA78240498', 'uniq_D87F7E0C76FF8CAA78240498'));
$this->assertTrue($table->hasPrimaryKey());
$this->assertTrue($table->hasIndex('primary'));
$this->assertTrue($table->hasIndex('IDX_D87F7E0C8C736521'));
$this->assertTrue($table->hasIndex('UNIQ_D87F7E0C76FF8CAA78240498'));
}
public function testGenerateTableWithMultiColumnUniqueIndex()
{
$table = new Table('test');
$table->addColumn('foo', 'string', array('notnull' => false, 'length' => 255));
$table->addColumn('bar', 'string', array('notnull' => false, 'length' => 255));
$table->addUniqueIndex(array("foo", "bar"));
$sql = $this->_platform->getCreateTableSQL($table);
$this->assertEquals($this->getGenerateTableWithMultiColumnUniqueIndexSql(), $sql);
}
* ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*
*
* CREATE TABLE `user_custom_fields` (
* user_id INT(11) UNSIGNED NOT NULL,
* attribute VARCHAR(50) NOT NULL DEFAULT '',
* value VARCHAR(255) DEFAULT NULL,
* PRIMARY KEY (user_id, attribute)
* ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
$users = new Table('users');
$users->addColumn('id', Type::INTEGER, ['unsigned' => true, 'autoincrement' => true]);
$users->setPrimaryKey(['id']);
$users->addColumn('email', Type::STRING)->setLength(100)->setNotnull(true);
// login key
$users->addUniqueIndex(['email']);
$users->addColumn('password', Type::STRING)->setLength(255)->setNotnull(true);
$users->addColumn('salt', Type::STRING)->setLength(255)->setNotnull(true);
$users->addColumn('roles', Type::STRING)->setLength(255)->setNotnull(true);
$users->addColumn('name', Type::STRING)->setLength(100)->setNotnull(true);
// display name
$users->addColumn('time_created', Type::INTEGER, ['unsigned' => true]);
// epoch time?
$tables['users'] = $users;
$userCustomFields = new Table('user_custom_fields');
$userCustomFields->addColumn('user_id', Type::INTEGER, ['unsigned' => true]);
$userCustomFields->addColumn('attribute', Type::STRING)->setLength(50)->setNotnull(true)->setDefault('');
$userCustomFields->setPrimaryKey(['user_id', 'attribute']);
$userCustomFields->addColumn('value', Type::STRING)->setLength(255);
$tables['user_custom_fields'] = $userCustomFields;
return $tables;
/**
* @group DBAL-1033
*/
public function testPartialIndexes()
{
$offlineTable = new Schema\Table('person');
$offlineTable->addColumn('id', 'integer');
$offlineTable->addColumn('name', 'string');
$offlineTable->addColumn('email', 'string');
$offlineTable->addUniqueIndex(array('id', 'name'), 'simple_partial_index', array('where' => '(id IS NULL)'));
$offlineTable->addIndex(array('id', 'name'), 'complex_partial_index', array(), array('where' => '(((id IS NOT NULL) AND (name IS NULL)) AND (email IS NULL))'));
$this->_sm->dropAndCreateTable($offlineTable);
$onlineTable = $this->_sm->listTableDetails('person');
$comparator = new Schema\Comparator();
$this->assertFalse($comparator->diffTable($offlineTable, $onlineTable));
$this->assertTrue($onlineTable->hasIndex('simple_partial_index'));
$this->assertTrue($onlineTable->hasIndex('complex_partial_index'));
$this->assertTrue($onlineTable->getIndex('simple_partial_index')->hasOption('where'));
$this->assertTrue($onlineTable->getIndex('complex_partial_index')->hasOption('where'));
$this->assertSame('(id IS NULL)', $onlineTable->getIndex('simple_partial_index')->getOption('where'));
$this->assertSame('(((id IS NOT NULL) AND (name IS NULL)) AND (email IS NULL))', $onlineTable->getIndex('complex_partial_index')->getOption('where'));
}
public function createSchema()
{
$schema = $this->conn->getSchemaManager();
$table = new Table('bundles');
$table->addColumn('id', 'integer', array('autoincrement' => true));
$table->addColumn('bundlename', 'string', array('length' => 100));
$table->addColumn('autoload', 'string', array('length' => 384));
$table->addColumn('bundleclass', 'string', array('length' => 100));
$table->addColumn('bundletype', 'string', array('length' => 2));
$table->addColumn('bundlestate', 'integer', array('length' => 1));
$table->setPrimaryKey(array('id'));
$table->addUniqueIndex(array('bundlename'));
$schema->createTable($table);
}
<?php
use Doctrine\DBAL\Schema\Table;
$schema = $app['db']->getSchemaManager();
if (!$schema->tablesExist('users')) {
$users = new Table('users');
$users->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$users->setPrimaryKey(array('id'));
$users->addColumn('username', 'string', array('length' => 32));
$users->addUniqueIndex(array('username'));
$users->addColumn('password', 'string', array('length' => 255));
$users->addColumn('roles', 'string', array('length' => 255));
$schema->createTable($users);
$app['db']->insert('users', array('username' => 'giorgionetg', 'password' => '5FZ2Z8QIkA7UTZ4BYkoC+GsReLf569mSKDsfods6LYQ8t+a8EW9oaircfMpmaLbPBh4FOBiiFyLfuZmTSUwzZg==', 'roles' => 'ROLE_USER'));
$app['db']->insert('users', array('username' => 'admin', 'password' => '5FZ2Z8QIkA7UTZ4BYkoC+GsReLf569mSKDsfods6LYQ8t+a8EW9oaircfMpmaLbPBh4FOBiiFyLfuZmTSUwzZg==', 'roles' => 'ROLE_ADMIN'));
}
/**
* Parses the index definition and adds it to the schema table.
*
* @param Table $table
* @param string $keyName
* @param string $sql
*/
private function parseIndexSql(Table $table, $keyName, $sql)
{
if ('PRIMARY' === $keyName) {
if (!preg_match_all('/`([^`]+)`/', $sql, $matches)) {
throw new \RuntimeException(sprintf('Primary key definition "%s" could not be parsed.', $sql));
}
$table->setPrimaryKey($matches[1]);
return;
}
if (!preg_match('/(.*) `([^`]+)` \\((.*)\\)/', $sql, $matches)) {
throw new \RuntimeException(sprintf('Key definition "%s" could not be parsed.', $sql));
}
$columns = [];
$flags = [];
foreach (explode(',', $matches[3]) as $column) {
preg_match('/`([^`]+)`(\\((\\d+)\\))?/', $column, $cm);
$column = $cm[1];
if (isset($cm[3])) {
$column .= '(' . $cm[3] . ')';
}
$columns[$cm[1]] = $column;
}
if (false !== strpos($matches[1], 'unique')) {
$table->addUniqueIndex($columns, $matches[2]);
} else {
if (false !== strpos($matches[1], 'fulltext')) {
$flags[] = 'fulltext';
}
$table->addIndex($columns, $matches[2], $flags);
}
}
public function testBuilderAddUniqueIndex()
{
$table = new Table("foo");
$table->addColumn("bar", 'integer');
$table->addUniqueIndex(array("bar"), "my_idx");
$this->assertTrue($table->hasIndex("my_idx"));
$this->assertTrue($table->getIndex("my_idx")->isUnique());
$this->assertFalse($table->getIndex("my_idx")->isPrimary());
}
protected static function parseIndex(Schema $schema, Table $table, SimpleXMLElement $xIndex, AbstractPlatform $platform)
{
$s = (string) $xIndex['name'];
$indexName = $s === '' ? null : $s;
$fieldNames = array();
foreach ($xIndex->col as $col) {
$fieldNames[] = (string) $col;
}
if (isset($xIndex->unique)) {
$table->addUniqueIndex($fieldNames, $indexName);
} else {
$flags = array();
if (isset($xIndex->fulltext)) {
$flags[] = 'FULLTEXT';
}
$table->addIndex($fieldNames, $indexName, $flags);
}
}
public function testPrimaryKeyOverrulesUniqueIndex()
{
$table = new Table("bar");
$table->addColumn('baz', 'integer', array());
$table->addUniqueIndex(array('baz'));
$table->setPrimaryKey(array('baz'));
$indexes = $table->getIndexes();
$this->assertEquals(1, count($indexes), "Table should only contain the primary key table index, not the unique one anymore, because it was overruled.");
$index = current($indexes);
$this->assertTrue($index->isPrimary());
}
/**
* Creates a column definition as required by the DBAL from an ORM field mapping definition.
*
* @param ClassMetadata $class The class that owns the field mapping.
* @param array $mapping The field mapping.
* @param Table $table
*
* @return array The portable column definition as required by the DBAL.
*/
private function gatherColumn($class, array $mapping, Table $table)
{
$columnName = $this->quoteStrategy->getColumnName($mapping['fieldName'], $class, $this->platform);
$columnType = $mapping['type'];
$options = array();
$options['length'] = isset($mapping['length']) ? $mapping['length'] : null;
$options['notnull'] = isset($mapping['nullable']) ? !$mapping['nullable'] : true;
if ($class->isInheritanceTypeSingleTable() && count($class->parentClasses) > 0) {
$options['notnull'] = false;
}
$options['platformOptions'] = array();
$options['platformOptions']['version'] = $class->isVersioned && $class->versionField == $mapping['fieldName'] ? true : false;
if (strtolower($columnType) == 'string' && $options['length'] === null) {
$options['length'] = 255;
}
if (isset($mapping['precision'])) {
$options['precision'] = $mapping['precision'];
}
if (isset($mapping['scale'])) {
$options['scale'] = $mapping['scale'];
}
if (isset($mapping['default'])) {
$options['default'] = $mapping['default'];
}
if (isset($mapping['columnDefinition'])) {
$options['columnDefinition'] = $mapping['columnDefinition'];
}
if (isset($mapping['options'])) {
$knownOptions = array('comment', 'unsigned', 'fixed', 'default');
foreach ($knownOptions as $knownOption) {
if (isset($mapping['options'][$knownOption])) {
$options[$knownOption] = $mapping['options'][$knownOption];
unset($mapping['options'][$knownOption]);
}
}
$options['customSchemaOptions'] = $mapping['options'];
}
if ($class->isIdGeneratorIdentity() && $class->getIdentifierFieldNames() == array($mapping['fieldName'])) {
$options['autoincrement'] = true;
}
if ($class->isInheritanceTypeJoined() && $class->name != $class->rootEntityName) {
$options['autoincrement'] = false;
}
if ($table->hasColumn($columnName)) {
// required in some inheritance scenarios
$table->changeColumn($columnName, $options);
} else {
$table->addColumn($columnName, $columnType, $options);
}
$isUnique = isset($mapping['unique']) ? $mapping['unique'] : false;
if ($isUnique) {
$table->addUniqueIndex(array($columnName));
}
}
