exec() public method

Executes an SQL statement and return the number of affected rows.
public exec ( string $statement ) : integer
$statement string
return integer The number of affected rows.
Example #1
0
 /**
  * {@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());
 }
Example #3
0
 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']);
     }
 }
Example #5
0
 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);
     }
 }
Example #7
0
 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);
    }
Example #10
0
 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();
 }
Example #11
0
 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);
 }
Example #12
0
 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()]);
         }
     }
 }
Example #15
0
 /**
  * @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;
 }
Example #16
0
 /**
  * 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;
 }
Example #17
0
 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();
     */
 }
Example #18
0
 protected function setUpPostgreSql()
 {
     $this->conn->exec('CREATE EXTENSION postgis');
     $this->setupCommonTypes();
     $this->setupCommonFunctions();
     $this->setupCommonEntities();
 }
Example #19
0
 /**
  * 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']));
     }
 }
Example #20
0
 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);
 }
Example #25
0
 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']);
 }
Example #28
0
 /**
  * @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);
 }
Example #29
0
 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);
 }