/** * {@inheritdoc} */ public function purge() { foreach ($this->tables as $table) { $sql = 'DELETE FROM ' . $table; $this->connection->exec($sql); } }
public function _before() { $this->connection->exec('DELETE FROM stations'); $loader = new YamlLoader(__DIR__ . '/../../data/fixtures/stations.yml'); $persister = new ConnectionPersister($this->connection); $persister->persist($loader->load()); }
public function testGetSqlStack() { $sql = 'select * from user'; static::$conn->exec($sql); $sqlStack = static::$mf->getSqlStack(); $this->assertCount(1, $sqlStack); $this->assertEquals($sql, $sqlStack[1]['sql']); }
private function backupDatabase(Connection $connection, $newDatabaseName, $databaseName) { $connection->exec('CREATE DATABASE `' . $newDatabaseName . '` COLLATE `utf8_general_ci`'); $statement = 'SELECT ' . 'concat("RENAME TABLE `' . $databaseName . '`.", table_name," TO `' . $newDatabaseName . '`.", table_name, ";") as query ' . 'FROM information_schema.TABLES WHERE table_schema= "' . $databaseName . '"'; $renameTableSql = $connection->fetchAll($statement); foreach ($renameTableSql as $sql) { $connection->exec($sql['query']); } }
public function setUp() { $this->connection = \OC_DB::getConnection(); if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\SqlitePlatform) { $this->markTestSkipped("Test only relevant on Sqlite"); } $dbPrefix = \OC::$server->getConfig()->getSystemValue("dbtableprefix"); $this->tableName = uniqid($dbPrefix . "_enum_bit_test"); $this->connection->exec("CREATE TABLE {$this->tableName}(t0 tinyint unsigned, t1 tinyint)"); }
protected function runQueriesFromFile($file) { $queries = array_filter(preg_split('(;\\s*$)m', file_get_contents($file))); if (!$this->output->isQuiet()) { $this->output->writeln(sprintf("Executing %d queries from %s on database %s", count($queries), $file, $this->db->getDatabase())); } foreach ($queries as $query) { $this->db->exec($query); } }
public function setUp() { $this->connection = \OC_DB::getConnection(); if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\MySqlPlatform) { $this->markTestSkipped("Test only relevant on MySql"); } $dbPrefix = \OC::$server->getConfig()->getSystemValue("dbtableprefix"); $this->tableName = uniqid($dbPrefix . "_enum_bit_test"); $this->connection->exec("CREATE TABLE {$this->tableName}(b BIT, e ENUM('1','2','3','4'))"); }
/** * @param string $articleId * @param array $categories * @throws DBALException */ public function write($articleId, $categories) { if (!$categories) { return; } $values = $this->prepareValues($categories, $articleId); $sql = "\n INSERT INTO s_articles_categories (articleID, categoryID)\n VALUES {$values}\n ON DUPLICATE KEY UPDATE categoryID=VALUES(categoryID), articleID=VALUES(articleID)\n "; $this->connection->exec($sql); $this->updateArticlesCategoriesRO($articleId); }
public function createTable() { $sql = <<<EOS CREATE TABLE IF NOT EXISTS `{$this->getTableName()}` ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, status VARCHAR(255) NOT NULL, PRIMARY KEY (id) ) EOS; return $this->connection->exec($sql); }
public function setUp() { $this->connection = \OC_DB::getConnection(); if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\MySqlPlatform) { $this->markTestSkipped("Test only relevant on MySql"); } $dbPrefix = \OC::$server->getConfig()->getSystemValue("dbtableprefix"); $this->tableName = uniqid($dbPrefix . "_innodb_test"); $this->connection->exec("CREATE TABLE {$this->tableName}(id INT) ENGINE MyISAM"); $this->repair = new \OC\Repair\InnoDB(); }
public function setUp() { $this->connection = \OC_DB::getConnection(); $this->config = \OC::$server->getConfig(); if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\MySqlPlatform) { $this->markTestSkipped("Test only relevant on MySql"); } $dbPrefix = $this->config->getSystemValue("dbtableprefix"); $this->tableName = uniqid($dbPrefix . "_collation_test"); $this->connection->exec("CREATE TABLE {$this->tableName}(text VARCHAR(16)) COLLATE utf8_unicode_ci"); $this->repair = new TestCollationRepair($this->config, $this->connection); }
protected function setUp() { parent::setUp(); $this->connection = \OC::$server->getDatabaseConnection(); $this->config = \OC::$server->getConfig(); if (!$this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\SqlitePlatform) { $this->markTestSkipped("Test only relevant on Sqlite"); } $dbPrefix = $this->config->getSystemValue('dbtableprefix', 'oc_'); $this->tableName = $this->getUniqueID($dbPrefix . 'autoinc_test'); $this->connection->exec('CREATE TABLE ' . $this->tableName . '("someid" INTEGER NOT NULL, "text" VARCHAR(16), PRIMARY KEY("someid"))'); $this->repair = new \OC\Repair\SqliteAutoincrement($this->connection); }
protected function setUp() { if (!class_exists('PDO') || !in_array('sqlite', \PDO::getAvailableDrivers())) { self::markTestSkipped('This test requires SQLite support in your environment'); } $this->con = DriverManager::getConnection(['driver' => 'pdo_sqlite', 'memory' => true]); // import the schema $schema = new Schema($this->getOptions()); foreach ($schema->toSql($this->con->getDatabasePlatform()) as $sql) { $this->con->exec($sql); } $this->sid = UserSecurityIdentity::fromAccount(new User('jimmy', 'jimmypass')); $this->aclProvider = $this->getProvider(); }
/** * @throws \Doctrine\DBAL\DBALException */ public function synchronize() { $categories = $this->pluginService->getCategories(); $this->connection->exec("DELETE FROM s_core_plugin_categories"); $statement = $this->connection->prepare("INSERT INTO s_core_plugin_categories (id, locale, parent_id, name)\n VALUES (:id, :locale, :parent_id, :name)"); $pseudo = $this->getPseudoCategories(); foreach ($pseudo as $category) { $statement->execute($category); } foreach ($categories as $category) { foreach ($category->getName() as $locale => $name) { $statement->execute([':id' => $category->getId(), ':name' => $name, ':locale' => $locale, ':parent_id' => $category->getParentId()]); } } }
/** * @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; }
/** * Fill database with default data and run all not applied upgrades * * @param Connection $connection * * @return boolean */ public function fillNewscoopDatabase($connection) { // import database from sql file $sqlFile = __DIR__ . '/../../../../install/Resources/sql/campsite_core.sql'; try { $connection->exec(file_get_contents($sqlFile)); } catch (\Exception $e) { return false; } $dbVersions = array_map('basename', glob(__DIR__ . '/../../../../install/Resources/sql/upgrade/[2-9].[0-9]*')); if (!empty($dbVersions)) { usort($dbVersions, array($this, 'versionCompare')); $dbLastVersion = array_pop($dbVersions); $dbLastVersionDir = __DIR__ . '/../../../../install/Resources/sql/upgrade/' . $dbLastVersion . '/'; $dbLastRoll = ''; $dbRolls = $this->searchDbRolls($dbLastVersionDir, ''); if (!empty($dbRolls)) { $dbLastRollInfo = array_slice($dbRolls, -1, 1, true); $dbLastRollInfoKeys = array_keys($dbLastRollInfo); $dbLastRoll = $dbLastRollInfoKeys[0]; } $this->saveDatabaseVersion($connection, $dbLastVersion, $dbLastRoll); $this->logger->addInfo('Last db version:"' . $dbLastVersion . '", last db roll: "' . $dbLastRoll . '"'); } return true; }
protected function tearDown() { $paths = array(ROOT_TESTS . '/data/files', ROOT_TESTS . '/data/publisher/public'); foreach ($paths as $path) { $diter = new RecursiveDirectoryIterator($path); $riter = new RecursiveIteratorIterator($diter, RecursiveIteratorIterator::CHILD_FIRST); foreach ($riter as $item) { if (($item->isFile() || $item->isLink()) && $item->getFilename() !== '.gitignore') { @unlink($item->getPathName()); } } foreach ($riter as $item) { if ($item->isDir() && !in_array($item->getPathName(), array('.', '..'))) { @rmdir($item->getPathName()); } } } $this->conn->exec("DELETE FROM xi_filelib_file"); $this->conn->exec("DELETE FROM xi_filelib_resource"); $this->conn->exec("DELETE FROM xi_filelib_folder"); /* $this->mongo->selectCollection('resources')->drop(); $this->mongo->selectCollection('folders')->drop(); $this->mongo->selectCollection('files')->drop(); */ }
protected function setUpPostgreSql() { $this->conn->exec('CREATE EXTENSION postgis'); $this->setupCommonTypes(); $this->setupCommonFunctions(); $this->setupCommonEntities(); }
/** * Apply a migration status with unapplied changes to the database. * * @param MigrationStatus $status */ public function apply(MigrationStatus $status) { foreach ($status->getApplyMigrations() as $revision => $data) { $this->connection->exec($data['sql']); $this->connection->insert('changelog', array('change_number' => $data['change_number'], 'description' => $data['description'], 'applied_by' => $data['applied_by'])); } }
private function executeRegisteredSql($dryRun = false, $timeAllQueries = false) { if (!$dryRun) { if (!empty($this->sql)) { foreach ($this->sql as $key => $query) { $queryStart = microtime(true); if (!isset($this->params[$key])) { $this->outputWriter->write(' <comment>-></comment> ' . $query); $this->connection->exec($query); } else { $this->outputWriter->write(sprintf(' <comment>-</comment> %s (with parameters)', $query)); $this->connection->executeQuery($query, $this->params[$key], $this->types[$key]); } $this->outputQueryTime($queryStart, $timeAllQueries); } } else { $this->outputWriter->write(sprintf('<error>Migration %s was executed but did not result in any SQL statements.</error>', $this->version)); } } else { foreach ($this->sql as $query) { $this->outputWriter->write(' <comment>-></comment> ' . $query); } } $this->resetRegisteredSql(); }
/** * 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) . ';'); } }
protected function createDB() { $schema = new Schema($this->tableNames); foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) { $this->connection->exec($sql); } }
/** * Execute a query against the database * * @param string $query */ public function exec($query) { try { $this->connection->exec($query); } catch (DBALException $e) { throw new QueryException($e->getMessage(), $e->getCode(), $e); } }
/** * Inserts new relations. * * @param $relations * @param $articleId * @throws \Doctrine\DBAL\DBALException */ private function insertRelations($relations, $articleId) { $values = implode(', ', array_map(function ($relation) use($articleId) { return "({$articleId}, {$relation[$this->idKey]})"; }, $relations)); $insert = "INSERT INTO {$this->table} (articleID, relatedarticle) VALUES {$values}"; $this->connection->exec($insert); }
public function down(Connection $db, Schema $sc) { $builder = new SchemaBuilder(); $this->getTagTable($builder); $queries = $builder->toDropSql($db->getDatabasePlatform()); foreach ($queries as $query) { $db->exec($query); } }
protected function loadFixtures(Connection $conn) { $options = array('disable_fks' => $conn->getDatabasePlatform() instanceof SqlitePlatform); $schema = new RepositorySchema($options, $conn); $tables = $schema->getTables(); foreach ($tables as $table) { $conn->exec('DELETE FROM ' . $table->getName()); } }
/** * @test */ public function shouldBeEncodedUTF8() { $this->conn->exec("SET CHARSET utf8"); $string = "ac"; $create_query = "CREATE TABLE test (test CHAR(12)) CHARACTER SET utf8 COLLATE utf8_bin;"; $insert_query = "INSERT INTO test VALUES('" . $string . "');"; $event = $this->createAndInsertValue($create_query, $insert_query); self::assertEquals($string, $event->getValues()[0]['test']); }
/** * @param MediaPosition $mediaPosition * @throws \Doctrine\DBAL\DBALException */ private function handleTable(MediaPosition $mediaPosition) { $sql = sprintf('INSERT INTO s_media_used SELECT DISTINCT NULL, m.id FROM s_media m INNER JOIN %1$s ON %1$s.%2$s = m.%3$s', $mediaPosition->getSourceTable(), $mediaPosition->getSourceColumn(), $mediaPosition->getMediaColumn()); $this->connection->exec($sql); }
public function run($sourceFile, Connection $dbConn) { $driverType = $dbConn->getDriver()->getName(); $sqlite = preg_match('/sqlite/i', $driverType); $objPHPExcel = \PHPExcel_IOFactory::load($sourceFile); $dbConn->exec($sqlite ? 'DELETE FROM songs' : 'TRUNCATE TABLE songs'); // empty table - reset autoincrement if it has one $iterator = $objPHPExcel->getSheet()->getRowIterator($this->startRow); $i = 1; $codeStored = []; foreach ($iterator as $row) { $raw = []; /** @var \PHPExcel_Worksheet_Row $row */ // $rowIdx = $row->getRowIndex(); $cells = $row->getCellIterator(); foreach ($cells as $cell) { /** @var \PHPExcel_Cell $cell */ $column = $cell->getColumn(); $content = $cell->getFormattedValue(); $targetField = isset($this->fileFields[$column]) ? $this->fileFields[$column] : null; if ($targetField) { $raw[$targetField] = trim($content); } } //map row $storable = $this->rowToStorable($raw); // print_r($storable); if (strlen(join($storable, ''))) { if ($sqlite && !isset($storable['id'])) { $storable['id'] = $i; } if (!isset($storable['codeNumber'])) { $storable['codeNumber'] = (string) $this->makeCodeNumberFromArray($storable); } if (isset($codeStored[$storable['codeNumber']])) { print "\nDuplicate: " . $storable[self::INPUT_FIELD_ARTIST] . ': ' . $storable[self::INPUT_FIELD_TITLE] . "\n"; } else { $dbConn->insert('songs', $storable); if (!($i % 100)) { echo $i; } else { if (!($i % 10)) { echo '.'; } } if (!($i % 1000)) { echo "\n"; } $i++; $codeStored[$storable['codeNumber']] = true; } } } $total = $i - 1; echo "\nImported {$total} songs\n"; }
public static function dropTable(Connection $connection, $table = 'cache', array $fields = array()) { $sm = $connection->getSchemaManager(); $schema = $sm->createSchema(); $sqls = $schema->toDropSql($connection->getDatabasePlatform()); foreach ($sqls as $sql) { $connection->exec($sql); } return count($sqls); }