/** * Refresh the protected foreign keys variable. * All foreign keys are removed from the original constraints. * * @return void */ protected function _extractForeignKeys(Connection $connection) { $dialect = $connection->driver()->schemaDialect(); foreach ($this->_constraints as $name => $attrs) { if ($attrs['type'] === static::CONSTRAINT_FOREIGN) { $this->_foreignKeys[$name] = $attrs; $this->_foreignKeysSql[$name] = $dialect->constraintSql($this, $name); unset($this->_constraints[$name]); } } }
/** * Executes list of quries in one transaction. * * @param \Cake\Database\Connection $db Connection to run the SQL queries on. * @param array $queries List of SQL statements. * @return void */ protected function _execute($db, $queries = null) { $logQueries = $db->logQueries(); if ($logQueries) { $db->logQueries(false); } $db->transactional(function ($db) use($queries) { $db->disableForeignKeys(); foreach ($queries as $query) { $this->_io->out('.', 0); $db->execute($query)->closeCursor(); } $db->enableForeignKeys(); }); if ($logQueries) { $db->logQueries(true); } }
/** * @param object $command * @param callable $next * * @return mixed * @throws \Exception when Transaction fails to commit. */ public function execute($command, callable $next) { $this->connection->begin(); try { $returnValue = $next($command); $isCommitted = $this->connection->commit(); } catch (\Exception $e) { $this->connection->rollback(); throw $e; } if (!$isCommitted) { throw new \Exception('Failed to commit the transaction.'); } return $returnValue; }
/** * Creates tables for testing listTables/describe() * * @param \Cake\Database\Connection $connection * @return void */ protected function _createTables($connection) { $this->_needsConnection(); $schema = new SchemaCollection($connection); $result = $schema->listTables(); if (in_array('schema_articles', $result) && in_array('schema_authors', $result)) { return; } $table = <<<SQL CREATE TABLE schema_authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50), bio TEXT, created DATETIME ) SQL; $connection->execute($table); $table = <<<SQL CREATE TABLE schema_articles ( id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(20) DEFAULT 'Let ''em eat cake', body TEXT, author_id INT(11) NOT NULL, published BOOLEAN DEFAULT 0, created DATETIME, field1 VARCHAR(10) DEFAULT NULL, field2 VARCHAR(10) DEFAULT 'NULL', CONSTRAINT "title_idx" UNIQUE ("title", "body") CONSTRAINT "author_idx" FOREIGN KEY ("author_id") REFERENCES "schema_authors" ("id") ON UPDATE CASCADE ON DELETE RESTRICT ); SQL; $connection->execute($table); $connection->execute('CREATE INDEX "created_idx" ON "schema_articles" ("created")'); $sql = <<<SQL CREATE TABLE schema_composite ( "id" INTEGER NOT NULL, "site_id" INTEGER NOT NULL, "name" VARCHAR(255), PRIMARY KEY("id", "site_id") ); SQL; $connection->execute($sql); }
/** * Set all sequence's current value to the lowest available field value. * * @return bool */ public function sequences() { $this->out(sprintf('%s - %s', date('H:i:s'), 'Set all sequence\'s current values')); $success = $this->connection->begin() !== false; $schema = Hash::get($this->connection->config(), 'schema') ?: 'public'; $conditions = ["table_schema = '{$schema}'"]; foreach ($this->connection->driver()->sequences($conditions) as $sequence) { $sequence['sequence'] = preg_replace('/^nextval\\(\'(.*)\'.*\\)$/', '\\1', $sequence['sequence']); $sql = "SELECT setval('{$sequence['sequence']}', COALESCE(MAX({$sequence['column']}),0)+1, false) FROM {$sequence['table']};"; $success = $success && $this->connection->query($sql)->fetchAll('assoc') !== false; } if ($success) { $success = $this->connection->commit() !== false && $success; } else { $success = $this->connection->rollback() !== false && $success; } if ($this->command === __FUNCTION__) { $this->_stop($success ? self::SUCCESS : self::ERROR); } return $success; }
/** * Test executing "mark_migration" * * @return void */ public function testExecute() { $commandTester = new CommandTester($this->command); $commandTester->execute(['command' => $this->command->getName(), 'version' => '20150416223600', '--connection' => 'test']); $this->assertContains('Migration successfully marked migrated !', $commandTester->getDisplay()); $result = $this->Connection->newQuery()->select(['*'])->from('phinxlog')->execute()->fetch('assoc'); $this->assertEquals('20150416223600', $result['version']); }
/** * Helper method to run queries and convert Exceptions to the correct types. * * @param string $sql The sql to run. * @param array $params Parameters for the statement. * @return \Cake\Database\StatementInterface Prepared statement * @throws \Cake\Database\Exception on query failure. */ protected function _executeSql($sql, $params) { try { return $this->_connection->execute($sql, $params); } catch (\PDOException $e) { throw new Exception($e->getMessage(), 500, $e); } }
/** * Runs the drop and create commands on the fixtures if necessary. * * @param \Cake\TestSuite\Fixture\TestFixture $fixture the fixture object to create * @param Connection $db the datasource instance to use * @param array $sources The existing tables in the datasource. * @param bool $drop whether drop the fixture if it is already created or not * @return void */ protected function _setupTable(TestFixture $fixture, Connection $db, array $sources, $drop = true) { if (!empty($fixture->created) && in_array($db->configName(), $fixture->created)) { return; } $table = $fixture->table; $exists = in_array($table, $sources); if ($drop && $exists) { $fixture->drop($db); $fixture->create($db); } elseif (!$exists) { $fixture->create($db); } else { $fixture->created[] = $db->configName(); $fixture->truncate($db); } }
/** * Helper method for testing methods. * * @param \Cake\Database\Connection $connection * @return void */ protected function _createTables($connection) { $this->_needsConnection(); $connection->execute('DROP TABLE IF EXISTS schema_articles'); $connection->execute('DROP TABLE IF EXISTS schema_authors'); $table = <<<SQL CREATE TABLE schema_authors ( id SERIAL, name VARCHAR(50) DEFAULT 'bob', bio DATE, position INT DEFAULT 1, created TIMESTAMP, PRIMARY KEY (id), CONSTRAINT "unique_position" UNIQUE ("position") ) SQL; $connection->execute($table); $table = <<<SQL CREATE TABLE schema_articles ( id BIGINT PRIMARY KEY, title VARCHAR(20), body TEXT, author_id INTEGER NOT NULL, published BOOLEAN DEFAULT false, views SMALLINT DEFAULT 0, created TIMESTAMP, CONSTRAINT "content_idx" UNIQUE ("title", "body"), CONSTRAINT "author_idx" FOREIGN KEY ("author_id") REFERENCES "schema_authors" ("id") ON DELETE RESTRICT ON UPDATE CASCADE ) SQL; $connection->execute($table); $connection->execute('COMMENT ON COLUMN "schema_articles"."title" IS \'a title\''); $connection->execute('CREATE INDEX "author_idx" ON "schema_articles" ("author_id")'); }
/** * Tests identifier quoting * * @return void */ public function testQuoteIdentifier() { $driver = $this->getMock('Cake\\Database\\Driver\\Sqlite', ['enabled']); $driver->expects($this->once())->method('enabled')->will($this->returnValue(true)); $connection = new Connection(['driver' => $driver]); $result = $connection->quoteIdentifier('name'); $expected = '"name"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Model.*'); $expected = '"Model".*'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('MTD()'); $expected = 'MTD()'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('(sm)'); $expected = '(sm)'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('name AS x'); $expected = '"name" AS "x"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Model.name AS x'); $expected = '"Model"."name" AS "x"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Function(Something.foo)'); $expected = 'Function("Something"."foo")'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Function(SubFunction(Something.foo))'); $expected = 'Function(SubFunction("Something"."foo"))'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Function(Something.foo) AS x'); $expected = 'Function("Something"."foo") AS "x"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('name-with-minus'); $expected = '"name-with-minus"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('my-name'); $expected = '"my-name"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Foo-Model.*'); $expected = '"Foo-Model".*'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Team.P%'); $expected = '"Team"."P%"'; $this->assertEquals($expected, $result); $result = $connection->quoteIdentifier('Team.G/G'); $expected = '"Team"."G/G"'; $result = $connection->quoteIdentifier('Model.name as y'); $expected = '"Model"."name" AS "y"'; $this->assertEquals($expected, $result); }
/** * Runs the drop and create commands on the fixtures if necessary. * * @param \Cake\TestSuite\Fixture\TestFixture $fixture the fixture object to create * @param Connection $db the datasource instance to use * @param array $sources The existing tables in the datasource. * @param bool $drop whether drop the fixture if it is already created or not * @return void */ protected function _setupTable($fixture, $db, array $sources, $drop = true) { $configName = $db->configName(); if ($this->isFixtureSetup($configName, $fixture)) { return; } $table = $fixture->sourceName(); $exists = in_array($table, $sources); if ($drop && $exists) { $fixture->drop($db); $fixture->create($db); } elseif (!$exists) { $fixture->create($db); } else { $this->_insertionMap[$configName][] = $fixture; $fixture->truncate($db); } }
/** * Runs the drop and create commands on the fixtures if necessary. * * @param \Cake\TestSuite\Fixture\TestFixture $fixture the fixture object to create * @param \Cake\Database\Connection $db The Connection object instance to use * @param array $sources The existing tables in the datasource. * @param bool $drop whether drop the fixture if it is already created or not * @return void */ protected function _setupTable($fixture, $db, array $sources, $drop = true) { $configName = $db->configName(); $isFixtureSetup = $this->isFixtureSetup($configName, $fixture); if ($isFixtureSetup) { return; } $table = $fixture->sourceName(); $exists = in_array($table, $sources); if ($drop && $exists || $exists && !$isFixtureSetup && $fixture instanceof TableSchemaInterface && $fixture->schema() instanceof Table) { $fixture->drop($db); $fixture->create($db); } elseif (!$exists) { $fixture->create($db); } else { $fixture->truncate($db); } $this->_insertionMap[$configName][] = $fixture; }
/** * Helper method for running each step of the reflection process. * * @param string $stage The stage name. * @param string $name The table name. * @param array $config The config data. * @param \Cake\Database\Schema\Table $table The table instance * @return void * @throws \Cake\Database\Exception on query failure. */ protected function _reflect($stage, $name, $config, $table) { $describeMethod = "describe{$stage}Sql"; $convertMethod = "convert{$stage}Description"; list($sql, $params) = $this->_dialect->{$describeMethod}($name, $config); if (empty($sql)) { return; } try { $statement = $this->_connection->execute($sql, $params); } catch (PDOException $e) { throw new Exception($e->getMessage(), 500, $e); } foreach ($statement->fetchAll('assoc') as $row) { $this->_dialect->{$convertMethod}($table, $row); } $statement->closeCursor(); }
/** * Compiles the SQL representation of this query and executes it using the * configured connection object. Returns the resulting statement object. * * Executing a query internally executes several steps, the first one is * letting the connection transform this object to fit its particular dialect, * this might result in generating a different Query object that will be the one * to actually be executed. Immediately after, literal values are passed to the * connection so they are bound to the query in a safe way. Finally, the resulting * statement is decorated with custom objects to execute callbacks for each row * retrieved if necessary. * * Resulting statement is traversable, so it can be used in any loop as you would * with an array. * * This method can be overridden in query subclasses to decorate behavior * around query execution. * * @return \Cake\Database\StatementInterface */ public function execute() { $statement = $this->_connection->run($this); return $this->_iterator = $this->_decorateStatement($statement); }
/** * Insert data into tables. * * @param \Cake\Database\Connection $db Connection to run the SQL queries on. * @param array $data List tables and rows. * @return void */ protected function _insert($db, $data = null) { $this->_io->out('Seeding ', 0); $operation = function ($db) use($data) { $db->disableForeignKeys(); foreach ($data as $table => $rows) { $this->_io->out('.', 0); $this->_beforeTableInsert($db, $table); $this->_insertTable($db, $table, $rows); $this->_afterTableInsert($db, $table); } $db->enableForeignKeys(); }; $this->_runOperation($db, $operation); $this->_io->out(); // New line }
/** * Imports all records of the given fixture. * * @param string $fixtureClassName Fixture class name * @param \Cake\Database\Schema\Table $schema Table schema for which records * will be imported * @param \Cake\Database\Connection $connection Database connection to use * @return bool True on success */ protected function _importRecords($fixtureClassName, TableSchema $schema, Connection $connection) { $fixture = new $fixtureClassName(); if (!isset($fixture->records) || empty($fixture->records)) { return true; } $fixture->records = (array) $fixture->records; if (count($fixture->records) > 100) { $chunk = array_chunk($fixture->records, 100); } else { $chunk = [0 => $fixture->records]; } foreach ($chunk as $records) { list($fields, $values, $types) = $this->_getRecords($records, $schema); $query = $connection->newQuery()->insert($fields, $types)->into($schema->name()); foreach ($values as $row) { $query->values($row); } try { $statement = $query->execute(); $statement->closeCursor(); } catch (\Exception $ex) { $this->error(__d('installer', 'Error while importing data for table "{0}". Details: {1}', $schema->name(), $ex->getMessage())); return false; } } return true; }
/** * Rollback a transaction. * * @return void */ public function rollbackTransaction() { $this->connection->rollback(); }
/** * {@inheritdoc} */ public function insertPermission(PermissionInterface $permission) { $this->connection->insert($this->getAclSchema()->getPermissionsTableName(), ['requester' => $permission->getRequester()->getAclRequesterIdentifier(), 'resource' => $permission->getResource()->getAclResourceIdentifier(), 'mask' => $permission->getMask()], ['mask' => 'integer', 'requester' => 'string', 'resource' => 'string']); }
/** * Helper method for testing methods. * * @param \Cake\Database\Connection $connection * @return void */ protected function _createTables($connection) { $this->_needsConnection(); $connection->execute('DROP TABLE IF EXISTS schema_articles'); $connection->execute('DROP TABLE IF EXISTS schema_authors'); $table = <<<SQL CREATE TABLE schema_authors ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), bio TEXT, created DATETIME )ENGINE=InnoDB SQL; $connection->execute($table); $table = <<<SQL CREATE TABLE schema_articles ( id BIGINT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20) COMMENT 'A title', body TEXT, author_id INT(11) NOT NULL, published BOOLEAN DEFAULT 0, allow_comments TINYINT(1) DEFAULT 0, created DATETIME, KEY `author_idx` (`author_id`), UNIQUE KEY `length_idx` (`title`(4)), FOREIGN KEY `author_idx` (`author_id`) REFERENCES `schema_authors`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB COLLATE=utf8_general_ci SQL; $connection->execute($table); }
/** * @expectedException \Exception * @expectedExceptionMessage Failed to commit the transaction. */ public function testCommandSucceedsButTransactionFailsReturningFalse() { $this->connection->shouldReceive('begin')->once(); $this->connection->shouldReceive('commit')->once()->andReturn(false); $this->connection->shouldReceive('rollback')->once(); $next = function () { // no-op }; $this->middleware->execute(new \stdClass(), $next); }
/** * Generate the SQL statements to truncate a table * * @param Connection $connection The connection to generate SQL for. * @return array SQL to drop a table. */ public function truncateSql(Connection $connection) { $dialect = $connection->driver()->schemaDialect(); return $dialect->truncateTableSql($this); }
/** * Run before each tests is executed, should return a set of SQL statements to insert records for the table * of this fixture could be executed successfully. * * @param Connection $db An instance of the database into which the records will be inserted * @return bool on success or if there are no records to insert, or false on failure */ public function insert(Connection $db) { if (isset($this->records) && !empty($this->records)) { list($fields, $values, $types) = $this->_getRecords(); $query = $db->newQuery()->insert($fields, $types)->into($this->table); foreach ($values as $row) { $query->values($row); } $statement = $query->execute(); $statement->closeCursor(); return $statement; } return true; }
/** * @inheritDoc */ public function cacheMetadata($cache) { $this->_schemaMethodsCollection = null; parent::cacheMetadata($cache); }