/**
* {@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);
}
