PHP Table::addUniqueIndex方法代码示例

 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!';
 }

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