/**
* 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);
}
public function makeTableRepresentation(DBAL\Schema\Table $table)
{
$table->addColumn('id', 'string', ['length' => 100]);
$table->addColumn('content', 'string', ['length' => $this->length]);
$table->addColumn('last_indexed', 'integer', ['length' => 50]);
$table->addIndex(['id', 'content']);
}
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 !');
}
}
public function makeTableRepresentation(DBAL\Schema\Table $table)
{
$table->addColumn('id', 'string', ['length' => 100]);
$table->addColumn('datetime', 'datetime');
$table->addColumn('last_indexed', 'integer', ['length' => 50]);
$table->addIndex(['id', 'datetime']);
}
/**
* Exports create table SQL.
*
* @return string
*/
protected function exportCreateTable()
{
$table = new Table(self::TABLE_NAME, array(), array(), array(), false, array());
$table->addColumn('id', 'string', array('length' => 2, 'notnull' => true));
$table->setPrimaryKey(array('id'));
$table->addColumn('value', 'string', array('length' => 64));
return array_pop($this->getConnection()->getDatabasePlatform()->getCreateTableSQL($table, AbstractPlatform::CREATE_INDEXES)) . ';' . PHP_EOL;
}
public function doIt(Table $table, Schema $schema = null)
{
$table->addColumn("title", "string", ['notnull' => false]);
$table->addColumn("sort", "integer", ['notnull' => false]);
$table->addColumn("path", "string", ['notnull' => false]);
$table->addColumn("level", "integer", ['notnull' => false]);
$table->addColumn("parent_id", "integer", ['notnull' => false]);
$table->addForeignKeyConstraint($table, array('parent_id'), array("id"), array("onUpdate" => "CASCADE"));
}
/**
* @group DBAL-255
*/
public function testDropColumnConstraints()
{
$table = new Table('sqlsrv_drop_column');
$table->addColumn('id', 'integer');
$table->addColumn('todrop', 'decimal', array('default' => 10.2));
$this->_sm->createTable($table);
$diff = new TableDiff('sqlsrv_drop_column', array(), array(), array(new Column('todrop', Type::getType('decimal'))));
$this->_sm->alterTable($diff);
$columns = $this->_sm->listTableColumns('sqlsrv_drop_column');
$this->assertEquals(1, count($columns));
}
public function testListTableColumnsWithFixedStringTypeColumn()
{
$table = new Table('list_table_columns_char');
$table->addColumn('id', 'integer', array('notnull' => true));
$table->addColumn('test', 'string', array('fixed' => true));
$table->setPrimaryKey(array('id'));
$this->_sm->dropAndCreateTable($table);
$columns = $this->_sm->listTableColumns('list_table_columns_char');
$this->assertArrayHasKey('test', $columns);
$this->assertTrue($columns['test']->getFixed());
}
/**
* @group DDC-1737
*/
public function testClobNoAlterTable()
{
$tableOld = new Table("test");
$tableOld->addColumn('id', 'integer');
$tableOld->addColumn('description', 'string', array('length' => 65536));
$tableNew = clone $tableOld;
$tableNew->setPrimaryKey(array('id'));
$diff = $this->comparator->diffTable($tableOld, $tableNew);
$sql = $this->platform->getAlterTableSQL($diff);
$this->assertEquals(array('ALTER TABLE test ADD PRIMARY KEY (id)'), $sql);
}
public function createTable()
{
if ($this->isExistTable()) {
return;
}
$table = new Table(self::TABLE_NAME);
$table->addColumn('version', Type::STRING, array('length' => 255, 'notnull' => true, 'default' => ''));
$table->addColumn('apply_at', Type::DATETIME, array('notnull' => false, 'default' => null));
$table->setPrimaryKey(array('version'));
$this->doctrine->getSchemaManager()->createTable($table);
}
private function createTable(string $tableName) : Table
{
$table = new Table($tableName);
$table->addColumn('aggregate_id', 'guid', ['notnull' => true]);
$table->addColumn('occurred_on', 'datetime', ['notnull' => true]);
$table->addColumn('name', 'string', ['notnull' => true]);
$table->addColumn('event_class', 'string', ['notnull' => true]);
$table->addColumn('event', 'text');
$table->addIndex(['aggregate_id']);
$table->addIndex(['occurred_on']);
return $table;
}
public static function getTableMetaData()
{
if (self::$schemaDetails === null) {
$table = new Table('mock_temporal');
$table->addColumn('slug_name', "string", array("length" => 150));
$table->addColumn('enabled_from', "date", array());
$table->addColumn('enabled_to', "date", array());
$table->addColumn('posting_date', "date", array());
self::$schemaDetails = $table;
}
return self::$schemaDetails;
}
public function testSwitchPrimaryKeyColumns()
{
$tableOld = new Table("switch_primary_key_columns");
$tableOld->addColumn('foo_id', 'integer');
$tableOld->addColumn('bar_id', 'integer');
$tableNew = clone $tableOld;
$this->_sm->createTable($tableOld);
$tableFetched = $this->_sm->listTableDetails("switch_primary_key_columns");
$tableNew = clone $tableFetched;
$tableNew->setPrimaryKey(array('bar_id', 'foo_id'));
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$this->_sm->alterTable($comparator->diffTable($tableFetched, $tableNew));
}
public function testColumnCollation()
{
$table = new Schema\Table('test_collation');
$table->addColumn('id', 'integer');
$table->addColumn('text', 'text');
$table->addColumn('foo', 'text')->setPlatformOption('collation', 'BINARY');
$table->addColumn('bar', 'text')->setPlatformOption('collation', 'NOCASE');
$this->_sm->dropAndCreateTable($table);
$columns = $this->_sm->listTableColumns('test_collation');
$this->assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
$this->assertEquals('BINARY', $columns['text']->getPlatformOption('collation'));
$this->assertEquals('BINARY', $columns['foo']->getPlatformOption('collation'));
$this->assertEquals('NOCASE', $columns['bar']->getPlatformOption('collation'));
}
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);
}
public function testColumnCollation()
{
$table = new Table('test_collation');
$table->addOption('collate', $collation = 'utf8_unicode_ci');
$table->addColumn('id', 'integer');
$table->addColumn('text', 'text');
$table->addColumn('foo', 'text')->setPlatformOption('collation', 'utf8_swedish_ci');
$table->addColumn('bar', 'text')->setPlatformOption('collation', 'utf8_general_ci');
$this->_sm->dropAndCreateTable($table);
$columns = $this->_sm->listTableColumns('test_collation');
$this->assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
$this->assertEquals('utf8_unicode_ci', $columns['text']->getPlatformOption('collation'));
$this->assertEquals('utf8_swedish_ci', $columns['foo']->getPlatformOption('collation'));
$this->assertEquals('utf8_general_ci', $columns['bar']->getPlatformOption('collation'));
}
/**
* @group DDC-1337
* @return void
*/
public function testCreateTemporaryTableNotAutoCommitTransaction()
{
if ($this->_conn->getDatabasePlatform()->getName() == 'sqlanywhere' || $this->_conn->getDatabasePlatform()->getName() == 'oracle') {
$this->markTestSkipped("Test does not work on Oracle and SQL Anywhere.");
}
$platform = $this->_conn->getDatabasePlatform();
$columnDefinitions = array("id" => array("type" => Type::getType("integer"), "notnull" => true));
$tempTable = $platform->getTemporaryTableName("my_temporary");
$createTempTableSQL = $platform->getCreateTemporaryTableSnippetSQL() . ' ' . $tempTable . ' (' . $platform->getColumnDeclarationListSQL($columnDefinitions) . ')';
$table = new Table("nontemporary");
$table->addColumn("id", "integer");
$table->setPrimaryKey(array('id'));
foreach ($platform->getCreateTableSQL($table) as $sql) {
$this->_conn->executeQuery($sql);
}
$this->_conn->beginTransaction();
$this->_conn->insert("nontemporary", array("id" => 1));
$this->_conn->exec($createTempTableSQL);
$this->_conn->insert("nontemporary", array("id" => 2));
$this->_conn->rollback();
try {
$this->_conn->exec($platform->getDropTemporaryTableSQL($tempTable));
} catch (\Exception $e) {
}
$rows = $this->_conn->fetchAll('SELECT * FROM nontemporary');
$this->assertEquals(array(), $rows, "In an event of an error this result has one row, because of an implicit commit.");
}
/**
* @param string $schema
* @param Table $table
* @param string $name
* @param FieldDefinition $definition
*/
public function map(string $schema, Table $table, string $name, FieldDefinition $definition)
{
$table->addColumn($name, 'float', ['notnull' => !$definition->isNullable(), 'default' => $definition->defaultValue(), 'unique' => $definition->options()['unique'] ?? false]);
if ($definition->options()['index'] ?? false) {
$table->addIndex([$name]);
}
}
public function testReservedColumnName()
{
$table = new Table("TABLE");
$column = $table->addColumn('table', 'string');
$this->validator->acceptColumn($table, $column);
$this->assertEquals(array('Table TABLE column table keyword violations: MySQL'), $this->validator->getViolations());
}
public function addColumns(\Doctrine\DBAL\Schema\Table $table, $columns)
{
foreach ($columns as $column) {
$table->addColumn($column['name'], $column['type'], $column['options']);
}
return $table;
}
private function createTable($name)
{
$table = new Schema\Table($this->tableName($name));
$table->addColumn('id', Type::INTEGER, ['autoincrement' => true, 'unsigned' => true]);
$table->setPrimaryKey(['id']);
return $table;
}
/**
* @group DBAL-400
*/
public function testAlterTableAddPrimaryKey()
{
$table = new Table('alter_table_add_pk');
$table->addColumn('id', 'integer');
$table->addColumn('foo', 'integer');
$table->addIndex(array('id'), 'idx_id');
$this->_sm->createTable($table);
$comparator = new Comparator();
$diffTable = clone $table;
$diffTable->dropIndex('idx_id');
$diffTable->setPrimaryKey(array('id'));
$this->_sm->alterTable($comparator->diffTable($table, $diffTable));
$table = $this->_sm->listTableDetails("alter_table_add_pk");
$this->assertFalse($table->hasIndex('idx_id'));
$this->assertTrue($table->hasPrimaryKey());
}
/**
* 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 testGenerateTableWithAutoincrement()
{
$table = new \Doctrine\DBAL\Schema\Table('autoinc_table');
$column = $table->addColumn('id', 'integer');
$column->setAutoincrement(true);
$this->assertEquals(array('CREATE TABLE autoinc_table (id SERIAL NOT NULL)'), $this->_platform->getCreateTableSQL($table));
}
/**
* @param string $schema
* @param Table $table
* @param string $name
* @param FieldDefinition $definition
*
* @throws DoctrineStorageException
*/
public function map(string $schema, Table $table, string $name, FieldDefinition $definition)
{
if (!$definition instanceof AssociationFieldDefinition) {
throw DoctrineStorageException::invalidDefinition(AssociationFieldDefinition::class, $definition);
}
$table->addColumn($name, 'guid', ['notnull' => !$definition->isNullable(), 'default' => $definition->defaultValue(), 'length' => $definition->options()['length'] ?? null, 'unique' => $definition->options()['unique'] ?? false]);
$table->addForeignKeyConstraint($this->tableName($schema, $definition->typeSchema()->name()), [$name], ['id']);
}
/**
* @param DBALTable $KeyTarget Foreign Key (Column: KeySource Name)
* @param DBALTable $KeySource Foreign Data (Column: Id)
*/
public function addForeignKey(DBALTable &$KeyTarget, DBALTable $KeySource)
{
if (!$this->Database->hasColumn($KeyTarget->getName(), $KeySource->getName())) {
$KeyTarget->addColumn($KeySource->getName(), 'bigint');
if ($this->Database->getPlatform()->supportsForeignKeyConstraints()) {
$KeyTarget->addForeignKeyConstraint($KeySource, array($KeySource->getName()), array('Id'));
}
}
}
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!';
}
/**
* Adds the missing columns to the table
*
* @param \Doctrine\DBAL\Schema\Table $table Table object
* @return \Doctrine\DBAL\Schema\Table Updated table object
*/
protected function addColumns(\Doctrine\DBAL\Schema\Table $table)
{
$columns = array('company' => array('string', array('length' => 100)), 'vatid' => array('string', array('length' => 32)), 'salutation' => array('string', array('length' => 8)), 'title' => array('string', array('length' => 64)), 'firstname' => array('string', array('length' => 64)), 'lastname' => array('string', array('length' => 64)), 'address1' => array('string', array('length' => 255)), 'address2' => array('string', array('length' => 255)), 'address3' => array('string', array('length' => 255)), 'postal' => array('string', array('length' => 16)), 'city' => array('string', array('length' => 255)), 'state' => array('string', array('length' => 255)), 'langid' => array('string', array('length' => 5, 'notnull' => false)), 'countryid' => array('string', array('length' => 2, 'notnull' => false, 'fixed' => true)), 'telephone' => array('string', array('length' => 32)), 'telefax' => array('string', array('length' => 32)), 'website' => array('string', array('length' => 255)), 'birthday' => array('date', array('notnull' => false)), 'vdate' => array('date', array('notnull' => false)), 'status' => array('smallint', array()), 'mtime' => array('datetime', array()), 'ctime' => array('datetime', array()), 'editor' => array('string', array('length' => 255)));
foreach ($columns as $name => $def) {
if ($table->hasColumn($name) === false) {
$table->addColumn($name, $def[0], $def[1]);
}
}
return $table;
}
/**
* Adds the missing columns to the table
*
* @param \Doctrine\DBAL\Schema\Table $table Table object
* @return \Doctrine\DBAL\Schema\Table Updated table object
*/
protected function addColumns(\Doctrine\DBAL\Schema\Table $table)
{
$columns = array('cdate' => array('string', array('fixed' => 10)), 'cmonth' => array('string', array('fixed' => 7)), 'cweek' => array('string', array('fixed' => 7)), 'chour' => array('string', array('fixed' => 2)));
foreach ($columns as $name => $def) {
if ($table->hasColumn($name) === false) {
$table->addColumn($name, $def[0], $def[1]);
}
}
return $table;
}
