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); }