PHP Schema::toSql方法代码示例

 public function createRepository(CategoryInterface $category)
 {
     $schema = new Schema();
     if ($this->isValid($category)) {
         /**
          * @var $category StandardSearchIndexerInterface
          */
         if (!$this->connection->tableExists($category->getIndexedSearchTable())) {
             $table = $schema->createTable($category->getIndexedSearchTable());
             $details = $category->getSearchIndexFieldDefinition();
             if (isset($details['columns'])) {
                 foreach ($details['columns'] as $column) {
                     $table->addColumn($column['name'], $column['type'], $column['options']);
                 }
             }
             if (isset($details['primary'])) {
                 $table->setPrimaryKey($details['primary']);
             }
             $queries = $schema->toSql($this->connection->getDatabasePlatform());
             foreach ($queries as $query) {
                 $this->connection->query($query);
             }
         }
     }
 }

 protected function assertSchemaSql(Schema $schema, array $expectedSql)
 {
     $sql = $schema->toSql(new MySqlPlatform());
     foreach ($sql as &$el) {
         $el = str_replace(' DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB', '', $el);
     }
     $this->assertEquals($expectedSql, $sql);
 }

 /**
  * Creates task view model table in the database
  */
 public function createTable()
 {
     $schema = new Schema();
     $table = $schema->createTable($this->tableName);
     $table->addColumn('id', 'string', array('length' => 64, 'nullable' => false, 'unique' => true));
     $table->addColumn('content', 'string', array('length' => 255, 'nullable' => false));
     $table->setPrimaryKey(array('id'));
     foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) {
         $this->connection->exec($sql);
     }
 }

 public static function create()
 {
     $conn = ConnectionManager::getConnection();
     $schema = new DbSchema();
     $users = $schema->createTable("users");
     $users->addColumn("id", "integer", ["unsigned" => true]);
     $users->addColumn("username", "string", ["length" => 32]);
     $users->setPrimaryKey(["id"]);
     $users->addUniqueIndex(["username"]);
     foreach ($schema->toSql($conn->getDatabasePlatform()) as $query) {
         $conn->query($query);
     }
 }

 /**
  * @group DBAL-204
  */
 public function testCleanupForeignKeysDifferentOrder()
 {
     $config = new SchemaConfig();
     $config->setName("test");
     $schema = new Schema(array(), array(), $config);
     $testTable = $schema->createTable("test.test");
     $testTable->addColumn('id', 'integer');
     $fooTable = $schema->createTable("foo.bar");
     $fooTable->addColumn('id', 'integer');
     $testTable->addForeignKeyConstraint("foo.bar", array("id"), array("id"));
     $schema->visit(new RemoveNamespacedAssets());
     $sql = $schema->toSql(new MySqlPlatform());
     $this->assertEquals(1, count($sql), "Just one CREATE TABLE statement, no foreign key and table to foo.bar");
 }

 /**
  * Execute the console command.
  *
  * @return void
  */
 public function fire()
 {
     global $app;
     /** @var EntityManager $em */
     $em = $app->make('em');
     MessagesSchema::create($schema = new Schema());
     // setup Doctrine DBAL
     $connection = $em->getConnection();
     $sql = $schema->toSql($connection->getDatabasePlatform());
     foreach ($sql as $query) {
         $connection->exec($query);
     }
     $this->info('Create Doctrine schema successfully.');
 }

 /**
  * Executes the changes.
  */
 public function executeChanges()
 {
     $platform = $this->db->getDatabasePlatform();
     if (!empty($this->dropTables)) {
         foreach ($this->dropTables as $t) {
             $this->sm->dropTable($this->prefix . $t);
         }
     }
     $sql = $this->schema->toSql($platform);
     if (!empty($sql)) {
         foreach ($sql as $s) {
             $this->db->executeUpdate($s);
         }
     }
     //reset schema
     $this->schema = new Schema([], [], $this->sm->createSchemaConfig());
     $this->dropTables = $this->addTables = [];
 }

 public function setUp()
 {
     parent::setUp();
     $schema = new Schema();
     $posts = $schema->createTable('posts');
     $posts->addColumn('id', Type::INTEGER);
     $posts->setPrimaryKey(['id']);
     $posts->addColumn('status', Type::STRING);
     $this->connection->exec('DROP TABLE IF EXISTS posts');
     foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) {
         $this->connection->exec($sql);
     }
     $i = 1;
     while ($i <= 10) {
         $this->posts[$i] = new PostObject($i);
         $this->connection->insert('posts', ['id' => $this->posts[$i]->getId(), 'status' => $this->posts[$i]->getStatus()]);
         $i++;
     }
 }

 private function createSchema($conn)
 {
     $schema = new Schema();
     $posts = $schema->createTable('posts');
     $posts->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
     $posts->addColumn('username', 'string', array('length' => 32));
     $posts->addColumn('post_content', 'text');
     $posts->setPrimaryKey(array('id'));
     $comments = $schema->createTable('comments');
     $comments->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
     $comments->addColumn('post_id', 'integer', array('unsigned' => true));
     $comments->addColumn('username', 'string', array('length' => 32));
     $comments->addColumn('content', 'text');
     $comments->setPrimaryKey(array('id'));
     $queries = $schema->toSql($conn->getDatabasePlatform());
     // get queries to create this schema.
     foreach ($queries as $sql) {
         $conn->executeQuery($sql);
     }
 }

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     /** @var Connection $conn */
     $conn = $this->getHelper('db')->getConnection();
     $table_name = $input->getArgument('table_name');
     $schema = new Schema();
     $table = $schema->createTable($table_name);
     $table->addColumn('id', 'integer');
     $table->addColumn('expires_on', 'integer');
     $table->addColumn('parent_id', 'integer', array('notnull' => false));
     $table->addColumn('runner_class', 'string');
     $table->addColumn('schedule', 'string');
     $table->addColumn('command', 'string');
     $table->setPrimaryKey(array('id'));
     $table->addForeignKeyConstraint($table, array('parent_id'), array('id'), array('onDelete' => 'CASCADE'));
     $sqls = $schema->toSql($conn->getDatabasePlatform());
     foreach ($sqls as $sql) {
         $output->writeln('Executing: ' . $sql);
         $conn->exec($sql);
     }
     $output->writeln('Done!');
 }

 public function install()
 {
     // create schema
     $schema = new Schema();
     $table = $schema->createTable($this->tableName);
     $table->addColumn('id', Type::INTEGER, array('autoincrement' => true));
     $table->addColumn('token', Type::STRING, array('length' => 128, 'unique' => true));
     $table->addColumn('userId', Type::INTEGER);
     $table->addColumn('expires', Type::BIGINT, array('unsigned' => true));
     $table->addIndex(array('token'));
     $table->addIndex(array('expires'));
     $table->setPrimaryKey(array('id'));
     // drop existing table
     if (in_array($this->tableName, $this->connection->getSchemaManager()->listTableNames())) {
         foreach ($schema->toDropSql($this->connection->getDatabasePlatform()) as $sql) {
             $this->connection->exec($sql);
         }
     }
     // create table
     foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) {
         $this->connection->exec($sql);
     }
 }

 /**
  * Create Schema
  *
  * @return DBAL
  */
 public function createSchema()
 {
     $schema = new \Doctrine\DBAL\Schema\Schema();
     $table = $schema->createTable($this->tableName);
     $table->addColumn("version", "string", array("length" => 255));
     $queries = $schema->toSql($this->connection->getDatabasePlatform());
     foreach ($queries as $sql) {
         $this->connection->query($sql);
     }
     return $this;
 }

 /**
  * Create database table.
  */
 public static function createTable(Connection $connection, $tableName = self::DEFAULT_TABLE_NAME)
 {
     $schema = new Schema();
     $myTable = $schema->createTable($tableName);
     $myTable->addColumn("machine_id", Type::INTEGER, array("unsigned" => true));
     $myTable->addColumn("last_access", Type::BIGINT, array("unsigned" => true));
     $myTable->setPrimaryKey(array("machine_id"));
     $sql = $schema->toSql($connection->getDatabasePlatform());
     foreach ($sql as $statement) {
         $connection->exec($statement);
     }
 }

 public function createMigrationsTable()
 {
     /** @var \Doctrine\DBAL\Schema\AbstractSchemaManager $sm */
     $sm = $this->dbHandler->getConnection()->getSchemaManager();
     $dbPlatform = $sm->getDatabasePlatform();
     $schema = new Schema();
     $t = $schema->createTable($this->migrationsTableName);
     $t->addColumn('migration', 'string', array('length' => 255));
     $t->addColumn('path', 'string', array('length' => 4000));
     $t->addColumn('md5', 'string', array('length' => 32));
     $t->addColumn('execution_date', 'integer', array('notnull' => false));
     $t->addColumn('status', 'integer', array('default ' => Migration::STATUS_TODO));
     $t->addColumn('execution_error', 'string', array('length' => 4000, 'notnull' => false));
     $t->setPrimaryKey(array('migration'));
     // in case users want to look up migrations by their full path
     // NB: disabled for the moment, as it causes problems on some versions of mysql which limit index length to 767 bytes,
     // and 767 bytes can be either 255 chars or 191 chars depending on charset utf8 or utf8mb4...
     //$t->addIndex(array('path'));
     foreach ($schema->toSql($dbPlatform) as $sql) {
         $this->dbHandler->exec($sql);
     }
 }

 /**
  * @throws \Doctrine\DBAL\DBALException
  */
 private function migrationVersion1()
 {
     $platform = $this->connection->getDatabasePlatform();
     // Version table
     $versionTable = $this->schema->createTable('vbee_version');
     $versionTable->addColumn('version', 'integer');
     $versionTable->setPrimaryKey(['version']);
     // Person table
     $personTable = $this->schema->createTable('vbee_person');
     $personTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
     $personTable->addColumn('birthDate', 'date');
     $personTable->addColumn('description', 'string');
     $personTable->addColumn('email', 'string');
     $personTable->addColumn('familyName', 'string');
     $personTable->addColumn('gender', 'string');
     $personTable->addColumn('givenName', 'string');
     $personTable->addColumn('image', 'string');
     $personTable->addColumn('jobTitle', 'string');
     $personTable->addColumn('nationality', 'string');
     $personTable->addColumn('telephone', 'string');
     $personTable->addUniqueIndex(['id']);
     $personTable->setPrimaryKey(['id']);
     // Organization table
     $organizationTable = $this->schema->createTable('vbee_organization');
     $organizationTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
     $organizationTable->addColumn('address', 'string');
     $organizationTable->addColumn('description', 'string');
     $organizationTable->addColumn('email', 'string');
     $organizationTable->addColumn('location', 'string');
     $organizationTable->addColumn('logo', 'string');
     $organizationTable->addColumn('name', 'string');
     $organizationTable->addColumn('numberOfEmployees', 'string');
     $organizationTable->addColumn('telephone', 'string');
     $organizationTable->addUniqueIndex(['id']);
     $organizationTable->setPrimaryKey(['id']);
     // Experience table
     $experienceTable = $this->schema->createTable('vbee_experience');
     $experienceTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
     $experienceTable->addColumn('type', 'string');
     $experienceTable->addColumn('startDate', 'date');
     $experienceTable->addColumn('endDate', 'date');
     $experienceTable->addColumn('personId', 'integer', ['unsigned' => true]);
     $experienceTable->addColumn('organizationId', 'integer', ['unsigned' => true]);
     $experienceTable->addForeignKeyConstraint($personTable, ["personId"], ["id"]);
     $experienceTable->addForeignKeyConstraint($organizationTable, ["organizationId"], ["id"]);
     $experienceTable->addUniqueIndex(['id']);
     $experienceTable->setPrimaryKey(['id']);
     // Mission table
     $missionTable = $this->schema->createTable('vbee_mission');
     $missionTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
     $missionTable->addColumn('description', 'string');
     $missionTable->addColumn('title', 'string');
     $missionTable->addColumn('startDate', 'date');
     $missionTable->addColumn('endDate', 'date');
     $missionTable->addColumn('experienceId', 'integer', ['unsigned' => true]);
     $missionTable->addForeignKeyConstraint($experienceTable, ["experienceId"], ["id"]);
     $missionTable->addUniqueIndex(['id']);
     $missionTable->setPrimaryKey(['id']);
     $skillTable = $this->schema->createTable('vbee_skill');
     $skillTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
     $skillTable->addColumn('level', 'integer');
     $skillTable->addColumn('name', 'string');
     $skillTable->addColumn('personId', 'integer', ['unsigned' => true]);
     $skillTable->addForeignKeyConstraint($personTable, ["personId"], ["id"]);
     $skillTable->addUniqueIndex(['id']);
     $skillTable->setPrimaryKey(['id']);
     $studyTable = $this->schema->createTable('vbee_study');
     $studyTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
     $studyTable->addColumn('description', 'string');
     $studyTable->addColumn('graduation', 'string');
     $studyTable->addColumn('name', 'string');
     $studyTable->addColumn('startDate', 'date');
     $studyTable->addColumn('endDate', 'date');
     $studyTable->addColumn('personId', 'integer', ['unsigned' => true]);
     $studyTable->addForeignKeyConstraint($personTable, ["personId"], ["id"]);
     $studyTable->addUniqueIndex(['id']);
     $studyTable->setPrimaryKey(['id']);
     // Execute schema migration
     foreach ($this->schema->toSql($platform) as $query) {
         $statement = $this->connection->prepare($query);
         $statement->execute();
     }
     // Create the version row
     $this->connection->createQueryBuilder()->insert('vbee_version')->values(['version' => '0'])->execute();
 }