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(); }
/** * @param \Doctrine\DBAL\Schema\Schema|\Doctrine\DBAL\Schema\SchemaDiff $schema * @return bool */ private function executeSchemaChange($schema) { $this->conn->beginTransaction(); foreach ($schema->toSql($this->conn->getDatabasePlatform()) as $sql) { $this->conn->query($sql); } $this->conn->commit(); if ($this->conn->getDatabasePlatform() instanceof SqlitePlatform) { $this->conn->close(); $this->conn->connect(); } return true; }
/** * @author "Lionel Lecaque, <*****@*****.**>" * @param \Doctrine\DBAL\Schema\Schema $schema */ public function schemaToSql($schema) { return $schema->toSql($this->dbalPlatform); }
/** * createSchema * * @param array $streams * @return bool */ public function createSchema(array $streams) { $schema = new Schema(); $snapshotTable = $schema->createTable('snapshot'); $snapshotTable->addColumn('id', 'integer', array('autoincrement' => true)); $snapshotTable->addColumn('sourceType', 'text'); $snapshotTable->addColumn('sourceId', 'string'); $snapshotTable->addColumn('snapshotVersion', 'integer'); $snapshotTable->setPrimaryKey(array("id")); foreach ($streams as $stream) { $streamTable = $schema->createTable($this->getTable($stream)); $streamTable->addColumn('eventId', 'string', array("length" => 128)); $streamTable->addColumn('sourceId', 'string'); $streamTable->addColumn('sourceVersion', 'integer'); $streamTable->addColumn('eventClass', 'text'); $streamTable->addColumn('payload', 'text'); $streamTable->addColumn('eventVersion', 'decimal'); $streamTable->addColumn('timestamp', 'integer'); $streamTable->setPrimaryKey(array("eventId")); } $queries = $schema->toSql($this->conn->getDatabasePlatform()); foreach ($queries as $query) { $this->getConnection()->exec($query); } return true; }
public function getConnection() { $params = array('driver' => 'pdo_sqlite', 'memory' => true); $connection = DriverManager::getConnection($params, new Configuration()); $schema = new Schema(); $table = $schema->createTable('groups'); $table->addColumn('id', 'integer'); $table->addColumn('name', 'string', array('length' => 45)); $table->setPrimaryKey(array('id')); $myTable = $schema->createTable('user'); $myTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true)); $myTable->addColumn('username', 'string', array('length' => 32)); $myTable->addColumn('group_id', 'integer'); $myTable->setPrimaryKey(array('id')); $myTable->addUniqueIndex(array('username')); $myTable->addForeignKeyConstraint($table, array('group_id'), array('id')); foreach ($schema->toSql(new SqlitePlatform()) as $query) { $connection->query($query); } return $connection; }
/** * @param \Doctrine\DBAL\Schema\Schema $schema * @return string */ public function generateChangeScript($schema) { $script = ''; $sqls = $schema->toSql($this->conn->getDatabasePlatform()); foreach ($sqls as $sql) { $script .= $sql . ';'; $script .= PHP_EOL; } return $script; }
public function create(\Closure $logger = null) { $queries = $this->schema->toSql($this->conn->getDatabasePlatform()); $this->executeQueries($queries, $logger); }
$retrieved->setPrimaryKey(array('id')); $scrobbles = $schema->createTable($app['scrobbles']->table); $scrobbles->addColumn('id', 'integer', array('unsigned' => true)); $scrobbles->addColumn('user', 'string', array('length' => 255)); $scrobbles->addColumn('artist', 'string', array('length' => 255)); $scrobbles->addColumn('title', 'string', array('length' => 255)); $scrobbles->addColumn('start', 'integer', array('unsigned' => true)); $scrobbles->addColumn('sent', 'integer', array('unsigned' => true)); $scrobbles->setPrimaryKey(array('id')); $ignores = $schema->createTable($app['ignores']->table); $ignores->addColumn('id', 'integer', array('unsigned' => true)); $ignores->addColumn('user', 'string', array('length' => 255)); $ignores->addColumn('artist', 'string', array('length' => 255)); $ignores->addColumn('title', 'string', array('length' => 255, 'notnull' => false)); $ignores->setPrimaryKey(array('id')); // do the actual work try { $platform = $app['db']->getDatabasePlatform(); $sql = $schema->toSql($platform); $i = 0; $app['db']->beginTransaction(); foreach ($sql as $query) { $app['db']->executeQuery($query); $i += 1; } $app['db']->commit(); } catch (\Exception $e) { $app['db']->rollback(); throw $e; } print "Executed {$i} queries...\n";
/** * Creates the table to store cache items which can be called once for setup. * * Cache ID are saved in a column of maximum length 255. Cache data is * saved in a BLOB. * * @throws \PDOException When the table already exists * @throws DBALException When the table already exists * @throws \DomainException When an unsupported PDO driver is used */ public function createTable() { // connect if we are not yet $conn = $this->getConnection(); if ($conn instanceof Connection) { $schema = new Schema(); $table = $schema->createTable($this->table); $table->addColumn($this->idCol, 'blob', array('length' => 255)); $table->addColumn($this->dataCol, 'blob', array('length' => 16777215)); $table->addColumn($this->lifetimeCol, 'integer', array('unsigned' => true, 'notnull' => false)); $table->addColumn($this->timeCol, 'integer', array('unsigned' => true, 'foo' => 'bar')); $table->setPrimaryKey(array($this->idCol)); foreach ($schema->toSql($conn->getDatabasePlatform()) as $sql) { $conn->exec($sql); } return; } switch ($this->driver) { case 'mysql': // We use varbinary for the ID column because it prevents unwanted conversions: // - character set conversions between server and client // - trailing space removal // - case-insensitivity // - language processing like é == e $sql = "CREATE TABLE {$this->table} ({$this->idCol} VARBINARY(255) NOT NULL PRIMARY KEY, {$this->dataCol} MEDIUMBLOB NOT NULL, {$this->lifetimeCol} INTEGER UNSIGNED, {$this->timeCol} INTEGER UNSIGNED NOT NULL) COLLATE utf8_bin, ENGINE = InnoDB"; break; case 'sqlite': $sql = "CREATE TABLE {$this->table} ({$this->idCol} TEXT NOT NULL PRIMARY KEY, {$this->dataCol} BLOB NOT NULL, {$this->lifetimeCol} INTEGER, {$this->timeCol} INTEGER NOT NULL)"; break; case 'pgsql': $sql = "CREATE TABLE {$this->table} ({$this->idCol} VARCHAR(255) NOT NULL PRIMARY KEY, {$this->dataCol} BYTEA NOT NULL, {$this->lifetimeCol} INTEGER, {$this->timeCol} INTEGER NOT NULL)"; break; case 'oci': $sql = "CREATE TABLE {$this->table} ({$this->idCol} VARCHAR2(255) NOT NULL PRIMARY KEY, {$this->dataCol} BLOB NOT NULL, {$this->lifetimeCol} INTEGER, {$this->timeCol} INTEGER NOT NULL)"; break; case 'sqlsrv': $sql = "CREATE TABLE {$this->table} ({$this->idCol} VARCHAR(255) NOT NULL PRIMARY KEY, {$this->dataCol} VARBINARY(MAX) NOT NULL, {$this->lifetimeCol} INTEGER, {$this->timeCol} INTEGER NOT NULL)"; break; default: throw new \DomainException(sprintf('Creating the cache table is currently not implemented for PDO driver "%s".', $this->driver)); } $conn->exec($sql); }
/** * Creates database table for custom URL alias backup. */ protected function createGlobalUrlAliasBackupTable() { $schema = new Schema(); $table = $schema->createTable(static::GLOBAL_ALIAS_BACKUP_TABLE); $table->addColumn('id', 'integer', ['autoincrement' => true]); $table->addColumn('resource', 'text'); $table->addColumn('path', 'text'); $table->addColumn('language_code', 'string'); $table->addColumn('always_available', 'integer'); $table->addColumn('forwarding', 'integer'); $table->setPrimaryKey(['id']); $queries = $schema->toSql($this->connection->getDatabasePlatform()); foreach ($queries as $query) { $this->connection->exec($query); } }
/** * {@inheritdoc} */ public function getCreateSchema(Schema $createSchema) { return $createSchema->toSql($this->platform); }
$sessionScopesTable->setPrimaryKey(array('id')); $sessionScopesTable->addUniqueIndex(array('session_id', 'scope')); $sessionScopesTable->addForeignKeyConstraint($sessionsTable, ['session_id'], ['id'], ['onDelete' => 'CASCADE']); $sessionScopesTable->addForeignKeyConstraint($scopesTable, ['scope'], ['id'], ['onDelete' => 'CASCADE']); $accessTokenScopesTable = $schema->createTable('oauth_access_token_scopes'); $accessTokenScopesTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true)); $accessTokenScopesTable->addColumn('access_token', 'string', array('Notnull' => true)); $accessTokenScopesTable->addColumn('scope', 'string', array('Notnull' => true)); $accessTokenScopesTable->setPrimaryKey(array('id')); $accessTokenScopesTable->addUniqueIndex(array('access_token', 'scope')); $accessTokenScopesTable->addForeignKeyConstraint($accessTokensTable, ['access_token'], ['access_token'], ['onDelete' => 'CASCADE']); $accessTokenScopesTable->addForeignKeyConstraint($scopesTable, ['scope'], ['id'], ['onDelete' => 'CASCADE']); $authCodeScopesTable = $schema->createTable('oauth_auth_code_scopes'); $authCodeScopesTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true)); $authCodeScopesTable->addColumn('auth_code', 'string', array('Notnull' => true)); $authCodeScopesTable->addColumn('scope', 'string', array('Notnull' => true)); $authCodeScopesTable->setPrimaryKey(array('id')); $authCodeScopesTable->addUniqueIndex(array('auth_code', 'scope')); $authCodeScopesTable->addForeignKeyConstraint($authCodesTable, ['auth_code'], ['auth_code'], ['onDelete' => 'CASCADE']); $authCodeScopesTable->addForeignKeyConstraint($scopesTable, ['scope'], ['id'], ['onDelete' => 'CASCADE']); $refreshTokensTable = $schema->createTable('oauth_refresh_tokens'); $refreshTokensTable->addColumn('refresh_token', 'string'); $refreshTokensTable->addColumn('access_token', 'string', array('Notnull' => true)); $refreshTokensTable->addColumn('expire_time', 'integer', array('unsigned' => true)); $refreshTokensTable->setPrimaryKey(array('refresh_token')); $refreshTokensTable->addForeignKeyConstraint($accessTokensTable, ['access_token'], ['access_token'], ['onDelete' => 'CASCADE']); // Create the database tables as defined above $queries = $schema->toSql($connection->getDatabasePlatform()); foreach ($queries as $query) { $connection->exec($query); }
private function createSchema(Connection $connection, Schema $schema) { foreach ($schema->toSql($connection->getDatabasePlatform()) as $sql) { $connection->exec($sql); } }
/** * Create a table * * @param string $table table name * @param Closure $config configuration callback */ public function createTable($table, Closure $config) { $schema = new DoctrineSchema(); $table = new Schema\Table($schema->createTable($table), $schema); $config($table); $commands = (array) $schema->toSql($this->getPlatform()); $this->runCommands($commands); }