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)); } }