Gets the SchemaManager that can be used to inspect or change the
database schema through the connection.
| public getSchemaManager ( ) : Doctrine\DBAL\Schema\AbstractSchemaManager | ||
| return | Doctrine\DBAL\Schema\AbstractSchemaManager | |
/**
* Returns a database metadata object that can be used to retrieve table
* meta data from the database.
*
* @return PHPUnit_Extensions_Database_DB_IMetaData
*/
public function getMetaData()
{
if (null === $this->_metadata) {
$this->_metadata = new DoctrineMetadata($this->_conn->getSchemaManager(), $this->_conn->getDatabase());
}
return $this->_metadata;
}
/**
* @param Connection $connection
* @param AppKernel $kernel
*
* @throws \Claroline\MigrationBundle\Migrator\InvalidDirectionException
* @throws \Claroline\MigrationBundle\Migrator\InvalidVersionException
* @throws \Doctrine\DBAL\Migrations\MigrationException
*/
protected function migrateBadgeTables(Connection $connection, AppKernel $kernel)
{
$portfolioBundle = $this->container->get('claroline.persistence.object_manager')->getRepository('ClarolineCoreBundle:Plugin')->findBy(array('vendorName' => 'Icap', 'bundleName' => 'PortfolioBundle'));
$portfolioBundle = count($portfolioBundle) === 1 ? true : false;
if (!$portfolioBundle && $connection->getSchemaManager()->tablesExist(['icap__portfolio_widget_badges'])) {
$this->log('Deleting portfolios badges tables...');
$connection->getSchemaManager()->dropTable('icap__portfolio_widget_badges_badge');
$connection->getSchemaManager()->dropTable('icap__portfolio_widget_badges');
$this->log('Portfolios badges tables deleted.');
}
if ($portfolioBundle && !$connection->getSchemaManager()->tablesExist(['icap__portfolio_widget_badges'])) {
$badgeBundle = $kernel->getBundle('IcapBadgeBundle');
$this->log('Executing migrations for portfolio interaction');
$migrationsDir = "{$badgeBundle->getPath()}/Installation/Migrations";
$migrationsName = "{$badgeBundle->getName()} migration";
$migrationsNamespace = "{$badgeBundle->getNamespace()}\\Installation\\Migrations";
$migrationsTableName = 'doctrine_' . strtolower($badgeBundle->getName()) . '_versions';
$config = new Configuration($connection);
$config->setName($migrationsName);
$config->setMigrationsDirectory($migrationsDir);
$config->setMigrationsNamespace($migrationsNamespace);
$config->setMigrationsTableName($migrationsTableName);
$config->registerMigrationsFromDirectory($migrationsDir);
$migration = new Migration($config);
$executedQueriesNumber = $migration->migrate('20150929141509');
$this->log(sprintf('%d queries executed', $executedQueriesNumber));
}
}
/**
* @param Connection $connection
* @param AppKernel $kernel
*/
public function preUpdate(Connection $connection, AppKernel $kernel)
{
/** @var \Symfony\Component\HttpKernel\Bundle\Bundle[] $bundles */
$bundles = $kernel->getBundles();
$isPortfolioBundleInstalled = false;
foreach ($bundles as $bundle) {
if ('IcapPortfolioBundle' === $bundle->getName()) {
$isPortfolioBundleInstalled = true;
}
}
if ($connection->getSchemaManager()->tablesExist(['icap__portfolio_widget_badges'])) {
if ($isPortfolioBundleInstalled) {
$this->log('Found existing database schema: skipping install migration...');
$config = new Configuration($connection);
$config->setMigrationsTableName('doctrine_icapbadgebundle_versions');
$config->setMigrationsNamespace('claro_badge');
// required but useless
$config->setMigrationsDirectory('claro_badge');
// idem
try {
$version = new Version($config, '20150929141509', 'stdClass');
$version->markMigrated();
} catch (\Exception $e) {
$this->log('Already migrated');
}
} else {
$this->log('Deleting badges tables for portfolio...');
$connection->getSchemaManager()->dropTable('icap__portfolio_widget_badges_badge');
$connection->getSchemaManager()->dropTable('icap__portfolio_widget_badges');
$this->log('badges tables for portfolio deleted.');
}
}
}
/**
* ->processQueueCallback(function (\Dja\Db\Model\Metadata $metadata, \Doctrine\DBAL\Schema\Table $table, array $sql, \Doctrine\DBAL\Connection $db) {})
* @param callable|\Closure $callBack
*/
public function processQueueCallback(\Closure $callBack)
{
$callbackQueue = [];
while (count($this->generateQueue)) {
$modelName = array_shift($this->generateQueue);
try {
/** @var Metadata $metadata */
$metadata = $modelName::metadata();
$tblName = $metadata->getDbTableName();
if ($this->db->getSchemaManager()->tablesExist($tblName)) {
continue;
}
if (isset($this->generated[$tblName])) {
continue;
}
$table = $this->metadataToTable($metadata);
$this->generated[$tblName] = 1;
$sql = $this->dp->getCreateTableSQL($table, AbstractPlatform::CREATE_INDEXES);
array_unshift($callbackQueue, [$metadata, $table, $sql]);
$fks = $table->getForeignKeys();
if (count($fks)) {
$sql = [];
foreach ($fks as $fk) {
$sql[] = $this->dp->getCreateForeignKeySQL($fk, $table);
}
array_push($callbackQueue, [$metadata, $table, $sql]);
}
} catch (\Exception $e) {
pr($e->__toString());
}
}
foreach ($callbackQueue as $args) {
$callBack($args[0], $args[1], $args[2], $this->db);
}
}
public function restorePortfolioTitle()
{
$totalPortfolioProcessed = 0;
$nbPortfolioProcessed = 0;
if ($this->connection->getSchemaManager()->tablesExist(array('icap__portfolio_widget_title'))) {
$this->log('Restoring portfolio titles...');
$rowPortfolioTitles = $this->connection->query('SELECT * FROM icap__portfolio_widget_title');
$sql = 'SELECT aw.id, aw.user_id FROM icap__portfolio_abstract_widget aw WHERE id = :id';
$stmt = $this->connection->prepare($sql);
foreach ($rowPortfolioTitles as $rowPortfolioTitle) {
$stmt->bindValue('id', $rowPortfolioTitle['id']);
$stmt->execute();
foreach ($stmt->fetchAll() as $rowAbstractWidget) {
$this->connection->update('icap__portfolio', ['title' => $rowPortfolioTitle['title'], 'slug' => $rowPortfolioTitle['slug']], ['id' => $rowAbstractWidget['user_id']]);
}
$this->connection->delete('icap__portfolio_abstract_widget', ['id' => $rowPortfolioTitle['id']]);
++$nbPortfolioProcessed;
if ($nbPortfolioProcessed >= 10) {
$totalPortfolioProcessed += $nbPortfolioProcessed;
$nbPortfolioProcessed = 0;
$this->log(' processing portfolio...');
}
}
$this->log(sprintf(' %d portfolio processed', $totalPortfolioProcessed + $nbPortfolioProcessed));
$this->connection->delete('icap__portfolio_widget_type', ['name' => 'title']);
$this->connection->getSchemaManager()->dropTable('icap__portfolio_widget_title');
}
}
/**
* Default constructor
*
* @param $config array Database connection setting
*/
public function __construct($config)
{
$this->config = $config;
$this->conn = \Doctrine\DBAL\DriverManager::getConnection($this->config);
$this->conn->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'enum');
$this->conn->getDatabasePlatform()->registerDoctrineTypeMapping('point', 'point');
$this->sm = $this->conn->getSchemaManager();
}
public function __construct(Connection $conn, StorageKeyGeneratorInterface $storageKeyGenerator)
{
$this->conn = $conn;
$this->storageKeyGenerator = $storageKeyGenerator;
$this->schemaManager = $this->conn->getSchemaManager();
$this->keyColumn = $this->conn->quoteIdentifier(self::KEY_COLUMN);
$this->valueColumn = $this->conn->quoteIdentifier(self::VALUE_COLUMN);
}
public function postUpdate()
{
$em = $this->container->get('doctrine.orm.entity_manager');
$process = false;
if (in_array('claro_forum_subject_temp', $this->conn->getSchemaManager()->listTableNames())) {
$columns = $this->conn->getSchemaManager()->listTableColumns('claro_forum_subject_temp');
foreach ($columns as $column) {
if ($column->getName() === 'forum_id') {
$process = true;
break;
}
}
}
if ($process) {
$this->log('restoring the subjects...');
$forums = $em->getRepository('ClarolineForumBundle:Forum')->findAll();
$sql = 'SELECT * FROM claro_forum_subject_temp WHERE forum_id = :forumId';
$stmt = $this->conn->prepare($sql);
foreach ($forums as $forum) {
$category = new Category();
$category->setName($forum->getResourceNode()->getName());
$category->setForum($forum);
$em->persist($category);
$em->flush();
$stmt->bindValue('forumId', $forum->getId());
$stmt->execute();
foreach ($stmt->fetchAll() as $rowsSubject) {
$this->conn->query("INSERT INTO claro_forum_subject VALUES (\n {$rowsSubject['id']},\n {$category->getId()},\n {$rowsSubject['user_id']},\n {$this->conn->quote($rowsSubject['title'])},\n '{$rowsSubject['created']}',\n '{$rowsSubject['updated']}',\n false\n )");
}
}
$this->log('restoring the messages...');
$this->conn->query('INSERT IGNORE INTO claro_forum_message SELECT * FROM claro_forum_message_temp');
$this->conn->query('DROP TABLE claro_forum_message_temp');
$this->conn->query('DROP TABLE claro_forum_subject_temp');
$this->conn->query('DROP TABLE claro_forum_options');
} else {
$this->log('categories already added');
}
$widget = $em->getRepository('ClarolineCoreBundle:Widget\\Widget')->findBy(array('name' => 'claroline_forum_widget'));
if (!$widget) {
$this->log('adding the forum widget...');
$plugin = $em->getRepository('ClarolineCoreBundle:Plugin')->findOneBy(array('vendorName' => 'Claroline', 'bundleName' => 'ForumBundle'));
$widget = new Widget();
$widget->setName('claroline_forum_widget');
$widget->setDisplayableInDesktop(true);
$widget->setDisplayableInWorkspace(true);
$widget->setConfigurable(false);
$widget->setExportable(false);
$widget->setIcon('none');
$widget->setPlugin($plugin);
$em->persist($widget);
$plugin->setHasOptions(true);
$em->persist($widget);
$em->flush();
} else {
$this->log('forum widget already added');
}
}
public function __construct(Version $version)
{
$config = $version->getConfiguration();
$this->version = $version;
$this->connection = $config->getConnection();
$this->sm = $this->connection->getSchemaManager();
$this->platform = $this->connection->getDatabasePlatform();
$this->outputWriter = $config->getOutputWriter();
}
/**
* {@inheritdoc}
*/
public function shouldBeRun()
{
$schemaManager = $this->connection->getSchemaManager();
if (!$schemaManager->tablesExist('tl_layout')) {
return false;
}
$columns = $schemaManager->listTableColumns('tl_layout');
return !isset($columns['viewport']);
}
public function testSkipMigrateUp()
{
$version = new \Doctrine\DBAL\Migrations\Version($this->config, 1, 'Doctrine\\DBAL\\Migrations\\Tests\\Functional\\MigrationSkipMigration');
$this->assertFalse($this->config->hasVersionMigrated($version));
$version->execute('up');
$schema = $this->connection->getSchemaManager()->createSchema();
$this->assertFalse($schema->hasTable('foo'));
$this->assertTrue($this->config->hasVersionMigrated($version));
}
/**
* Check if the given table exists in a database
*
* @param string $tableName
* @return bool TRUE if a table exists; otherwise, FALSE
*/
public function isTableExist($tableName)
{
$result = false;
try {
$this->ensureConnected();
$result = $this->connection->isConnected() && (bool) array_intersect([$tableName], $this->connection->getSchemaManager()->listTableNames());
} catch (\PDOException $e) {
}
return $result;
}
/**
* {@inheritdoc}
*/
public function shouldBeRun()
{
$schemaManager = $this->connection->getSchemaManager();
if (!$schemaManager->tablesExist('tl_member')) {
return false;
}
$sql = $this->connection->getDatabasePlatform()->getListTableIndexesSQL('tl_member', $this->connection->getDatabase());
$index = $this->connection->fetchAssoc($sql . " AND INDEX_NAME = 'username'");
return '0' !== $index['Non_Unique'];
}
public function setUp()
{
if (!extension_loaded('pdo_sqlite')) {
$this->markTestSkipped('The Redis extension is not available.');
}
$schema = new TableEventStoreSchema();
$tableSchema = $schema->getTableSchema();
$this->conn = DriverManager::getConnection(['driver' => 'pdo_sqlite', 'memory' => true]);
$this->conn->getSchemaManager()->createTable($tableSchema);
$this->tableEventStore = new TableEventStore(new SomeSerializer(), $this->conn, $tableSchema->getName());
}
protected function createQueryTestTable()
{
$table = new \Doctrine\DBAL\Schema\Table('query_test');
$table->addColumn('id', 'integer');
$table->addColumn('val1', 'string');
$table->addColumn('val2', 'integer');
try {
$this->connection->getSchemaManager()->createTable($table);
} catch (DBALException $e) {
}
}
/**
* Check if the given table exists in a database
*
* @param string $tableName
* @return bool TRUE if a table exists; otherwise, FALSE
*/
public function isTableExist($tableName)
{
if (!empty($tableName)) {
try {
$this->connection->connect();
return $this->connection->getSchemaManager()->tablesExist($tableName);
} catch (\PDOException $e) {
} catch (DBALException $e) {
}
}
return false;
}
/**
* Returns an array with all available tables that should be dumped. Those tables are pre-sorted according to
* their dependencies to other tables. Furthermore, the identifiers of the tables are already quoted.
*
* @param DumperConfigurationInterface $config
*
* @return \Doctrine\DBAL\Schema\Table[]
*/
public function findTablesToDump(DumperConfigurationInterface $config)
{
$schemaManager = $this->connection->getSchemaManager();
$tables = $schemaManager->listTables();
$this->createMissingTableConfigs($config, $tables);
$tables = $this->tableDependencyResolver->sortTablesByDependencies($tables);
$this->tableDependencyResolver->createDependentFilters($tables, $config);
$filter = new TableFilter($config);
$tables = $filter->filterWhiteListTables($tables);
$tables = $filter->filterIgnoredTables($tables);
// Quote all identifiers, as Doctrine DBAL only quotes reserved keywords by default
$tables = $this->identifierQuoter->quoteTables($tables);
return $tables;
}
/**
* constructor
* @param string $table_name
* @param Connection $conn
*/
public function __construct($table_name, \Doctrine\DBAL\Connection $conn)
{
$this->conn = $conn;
$this->table_name = $table_name;
$this->quoted_table_name = $this->conn->quoteIdentifier($this->table_name);
$this->sm = $this->conn->getSchemaManager();
if (!$this->sm->tablesExist([$table_name])) {
throw Schema\SchemaException::tableDoesNotExist($table_name);
}
foreach ($this->sm->listTableColumns($this->table_name) as $colum) {
$this->columns[$colum->getName()] = $colum;
$this->column_types[$colum->getName()] = $colum->getType()->getName();
}
}
/**
* Revert data to the previous version.
*/
public function sqlDownData()
{
// Map columns to custom fields.
$colmap = array();
foreach ($this->fieldmap as $field => $col) {
$colmap[$col] = $field;
}
// Test that the v2 columns actually exist.
$existingCols = $this->conn->getSchemaManager()->listTableColumns($this->usersTable);
$existingColnames = array_map(function ($col) {
return $col->getName();
}, $existingCols);
foreach ($this->fieldmap as $col) {
if (!in_array($col, $existingColnames)) {
throw new \RuntimeException('Cannot migrate down because current schema is not v2. (Missing column "' . $this->usersTable . '.' . $col . '").');
}
}
// Get user columns to revert back to custom fields.
$userData = $this->conn->fetchAll('SELECT id AS user_id, ' . implode(', ', $this->fieldmap) . ' FROM ' . $this->conn->quoteIdentifier($this->usersTable));
$queries = array();
foreach ($userData as $row) {
foreach ($this->fieldmap as $col) {
if ($row[$col] !== null) {
$queries[] = 'INSERT INTO ' . $this->conn->quoteIdentifier($this->userCustomFieldsTable) . ' (user_id, attribute, value) VALUES' . ' (' . $this->conn->quote($row['user_id'], Type::INTEGER) . ', ' . $this->conn->quote($colmap[$col], Type::STRING) . ', ' . $this->conn->quote($row[$col], Type::STRING) . ')';
}
}
}
return $queries;
}
/**
* Convert the tables in the current database to use given character set and collation.
*
* @param string $characterSet Character set to convert to
* @param string $collation Collation to set, must be compatible with the character set
* @param string $outputPathAndFilename
* @param boolean $verbose
* @throws ConnectionException
* @throws DBALException
*/
protected function convertToCharacterSetAndCollation($characterSet = 'utf8', $collation = 'utf8_unicode_ci', $outputPathAndFilename = null, $verbose = false)
{
$statements = ['SET foreign_key_checks = 0'];
$statements[] = 'ALTER DATABASE ' . $this->connection->quoteIdentifier($this->persistenceSettings['backendOptions']['dbname']) . ' CHARACTER SET ' . $characterSet . ' COLLATE ' . $collation;
$tableNames = $this->connection->getSchemaManager()->listTableNames();
foreach ($tableNames as $tableName) {
$statements[] = 'ALTER TABLE ' . $this->connection->quoteIdentifier($tableName) . ' DEFAULT CHARACTER SET ' . $characterSet . ' COLLATE ' . $collation;
$statements[] = 'ALTER TABLE ' . $this->connection->quoteIdentifier($tableName) . ' CONVERT TO CHARACTER SET ' . $characterSet . ' COLLATE ' . $collation;
}
$statements[] = 'SET foreign_key_checks = 1';
if ($outputPathAndFilename === null) {
try {
$this->connection->beginTransaction();
foreach ($statements as $statement) {
if ($verbose) {
$this->outputLine($statement);
}
$this->connection->exec($statement);
}
$this->connection->commit();
} catch (\Exception $exception) {
$this->connection->rollBack();
$this->outputLine($exception->getMessage());
$this->outputLine('[ERROR] The transaction was rolled back.');
}
} else {
file_put_contents($outputPathAndFilename, implode(';' . PHP_EOL, $statements) . ';');
}
}
/**
* Ensure that all tables in the schema file can be created
*/
public function testSchemaFile()
{
$tables = (require $this->schemaFile);
$this->assertTrue(is_array($tables));
$this->assertTrue(count($tables) > 0);
$schemaManager = $this->dbConnection->getSchemaManager();
$madeTables = [];
foreach ($tables as $tableName => $table) {
$schemaManager->createTable($table);
$madeTables[] = $tableName;
}
$tablesPresent = $schemaManager->listTableNames();
sort($tablesPresent);
sort($madeTables);
$this->assertEquals($tablesPresent, $madeTables);
}
public function test_drop()
{
$this->storage->create($this->schema);
$this->storage->drop($this->schema);
$result = $this->connection->getSchemaManager()->tablesExist(['metadata_test_foo', 'metadata_test_bar']);
$this->assertFalse($result);
}
/**
* @inheritdoc
*/
public function performInitialSetup()
{
$manager = $this->connection->getSchemaManager();
$from = $manager->createSchema();
$to = clone $from;
$table = $to->createTable($this->getQueueTableName());
$to->createSequence('job_seq');
$table->addColumn($this->columns[JobReflector::PROPERTY_ID], 'integer', ['autoincrement' => true]);
$table->addColumn($this->columns[JobReflector::PROPERTY_QUEUE], 'string');
$table->addColumn($this->columns[JobReflector::PROPERTY_CREATED], 'datetime');
$table->addColumn($this->columns[JobReflector::PROPERTY_SCHEDULE], 'datetime');
$table->addColumn($this->columns[JobReflector::PROPERTY_FAILED], 'boolean', ['notnull' => true, 'default' => false]);
$table->addColumn($this->columns[JobReflector::PROPERTY_FINISHED], 'datetime', ['notnull' => false, 'default' => null]);
$table->addColumn($this->columns[JobReflector::PROPERTY_RESULT], 'text', ['notnull' => false, 'default' => null]);
$table->addColumn($this->columns[JobReflector::PROPERTY_PROGRESS], 'decimal', ['notnull' => false, 'default' => null, 'precision' => 5, 'scale' => 2]);
$table->addColumn($this->columns[JobReflector::PROPERTY_LAST_ATTEMPT], 'datetime', ['notnull' => false, 'default' => null]);
$table->addColumn($this->columns[JobReflector::PROPERTY_TIMEOUT], 'datetime', ['notnull' => false, 'default' => null]);
$table->addColumn($this->columns[JobReflector::PROPERTY_RETRY_COUNT], 'integer');
$table->addColumn($this->columns[JobReflector::PROPERTY_RETRY], 'boolean', ['notnull' => true, 'default' => false]);
$table->addColumn($this->columns[JobReflector::PROPERTY_PARAMETERS], 'text', ['notnull' => false, 'default' => null]);
$table->addColumn($this->columns[JobReflector::PROPERTY_VERSION], 'integer');
$table->addColumn($this->columns['__CLASS__'], 'string');
$table->setPrimaryKey(array($this->columns[JobReflector::PROPERTY_ID]));
$sql = $from->getMigrateToSql($to, $this->connection->getDatabasePlatform());
$this->connection->beginTransaction();
foreach ($sql as $query) {
$this->connection->exec($query);
}
$this->connection->commit();
return;
}
/**
* Gets the schema manager
*
* @return \Doctrine\DBAL\Schema\AbstractSchemaManager|null
*/
public function getSchemaManager()
{
if (method_exists($this->connection, 'getSchemaManager')) {
return $this->connection->getSchemaManager();
}
return null;
}
/**
* @param Connection $db
* @param string $prefix
* @param ColumnSchemaHelper $columnHelper
*/
public function __construct(Connection $db, $prefix, ColumnSchemaHelper $columnHelper)
{
$this->db = $db;
$this->sm = $db->getSchemaManager();
$this->prefix = $prefix;
$this->columnHelper = $columnHelper;
$this->schema = new \Doctrine\DBAL\Schema\Schema(array(), array(), $this->sm->createSchemaConfig());
}
/**
* Create or update a schema.
*
* @param Schema $schema
* @param string $tablePrefix Only tables with this prefix will be updated.
*
* @throws \Doctrine\DBAL\DBALException
*/
public function createOrUpdateSchema(Schema $schema, $tablePrefix)
{
$manager = $this->conn->getSchemaManager();
$platform = $this->conn->getDatabasePlatform();
$origSchema = $manager->createSchema();
$tables = array();
foreach ($origSchema->getTables() as $table) {
if (0 === strpos($table->getName(), $tablePrefix)) {
$tables[] = $table;
}
}
$migrateSchema = new Schema($tables);
$queries = $migrateSchema->getMigrateToSql($schema, $platform);
foreach ($queries as $query) {
$this->conn->executeQuery($query);
}
}
/**
* Tests that the database schema is initialised correctly.
*/
public function testDatabaseSchema()
{
$sm = $this->db->getSchemaManager();
$this->assertTrue($sm->tablesExist('seen'));
$t = $sm->listTableDetails('seen');
$this->assertSame(array_keys($t->getColumns()), ['time', 'server', 'channel', 'nick', 'type', 'text']);
$this->assertTrue($t->hasIndex('seen_primary'));
}
public function createTable()
{
if ($this->tableExist) {
return;
}
$schemaM = $this->connection->getSchemaManager();
$schema = $schemaM->createSchema();
if (!$schemaM->tablesExist($this->migrationTableName)) {
$myTable = $schema->createTable($this->migrationTableName);
$myTable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement' => true]);
$myTable->addColumn('name', 'string', ['length' => 60]);
$myTable->addColumn('applied', 'datetime', ['default' => 'CURRENT_TIMESTAMP']);
$myTable->setPrimaryKey(['id']);
$schemaM->createTable($myTable);
$this->tableExist = true;
}
}
public static function createSchema(Connection $conn)
{
$sm = $conn->getSchemaManager();
$schema = static::getSchema();
foreach ($schema->getTables() as $table) {
$sm->createTable($table);
}
}
/**
* @param Connection $db
* @param $prefix
* @param ColumnSchemaHelper $columnHelper
*/
public function __construct(Connection $db, $prefix, ColumnSchemaHelper $columnHelper)
{
$this->db = $db;
$this->sm = $db->getSchemaManager();
$this->prefix = $prefix;
$this->columnHelper = $columnHelper;
$this->schema = new Schema([], [], $this->sm->createSchemaConfig());
}
