Executes a query using $params for interpolating values and $types as a hint for each
those params.
| public execute ( string $query, array $params = [], array $types = [] ) : Cake\Database\StatementInterface | ||
| $query | string | SQL to be executed and interpolated with $params |
| $params | array | list or associative array of params to be interpolated in $query as values |
| $types | array | list or associative array of types to be used for casting values in query |
| return | Cake\Database\StatementInterface | executed statement |
/**
* 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);
}
}
/**
* 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);
}
/**
* 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);
}
}
/**
* setup method
*
* @return void
*/
public function setUp()
{
parent::setUp();
$this->Connection = ConnectionManager::get('test');
$connectionConfig = $this->Connection->config();
$this->Connection->execute('DROP TABLE IF EXISTS phinxlog');
$this->config = new Config(['paths' => ['migrations' => __FILE__], 'environments' => ['default_migration_table' => 'phinxlog', 'default_database' => 'cakephp_test', 'default' => ['adapter' => getenv('DB'), 'host' => '127.0.0.1', 'name' => !empty($connectionConfig['database']) ? $connectionConfig['database'] : '', 'user' => !empty($connectionConfig['username']) ? $connectionConfig['username'] : '', 'pass' => !empty($connectionConfig['password']) ? $connectionConfig['password'] : '']]]);
$application = new MigrationsDispatcher('testing');
$output = new StreamOutput(fopen('php://memory', 'a', false));
$this->command = $application->find('mark_migrated');
$Environment = new Environment('default', $this->config['environments']['default']);
$Manager = $this->getMock('\\Phinx\\Migration\\Manager', [], [$this->config, $output]);
$Manager->expects($this->any())->method('getEnvironment')->will($this->returnValue($Environment));
$this->command->setManager($Manager);
}
/**
* 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")');
}
/**
* {@inheritdoc}
*/
public function findMask(RequesterInterface $requester, ResourceInterface $resource)
{
if (false === ($result = $this->connection->execute('SELECT mask FROM ' . $this->getAclSchema()->getPermissionsTableName() . ' WHERE requester = :requester AND resource = :resource', ['requester' => $requester->getAclRequesterIdentifier(), 'resource' => $resource->getAclResourceIdentifier()], ['requester' => 'string', 'resource' => 'string'])->fetch('assoc'))) {
throw new MaskNotFoundException();
}
return (int) $result['mask'];
}
/**
* 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();
}
/**
* 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);
}
