/**
* Helper method for testing methods.
*
* @param \Cake\Datasource\ConnectionInterface $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);
}
/**
* 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);
$this->_iterator = $this->_decorateStatement($statement);
$this->_dirty = false;
return $this->_iterator;
}
/**
* Helper method for testing methods.
*
* @param \Cake\Datasource\ConnectionInterface $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,
readingtime TIME,
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")');
}
/**
* 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);
$driver = $this->_connection->driver();
$typeMap = $this->selectTypeMap();
if ($typeMap->toArray()) {
$this->decorateResults(new FieldTypeConverter($typeMap, $driver));
}
$this->_iterator = $this->_decorateStatement($statement);
$this->_dirty = false;
return $this->_iterator;
}
/**
* Helper method for running each step of the reflection process.
*
* @param \CakeDC\OracleDriver\Database\Schema\Method $method Object with method metadata.
* @param string $name The table name.
* @param array $config The config data.
* @return void
* @throws \Cake\Database\Exception on query failure.
*/
protected function _reflect($method, $name, $config)
{
list($sql, $params) = $this->_dialect->describeParametersSql($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->convertParametersDescription($method, $row);
}
$statement->closeCursor();
}
/**
* 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();
}
/**
* {@inheritDoc}
*/
public function insert(ConnectionInterface $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;
}
/**
* Truncate the fixture type.
*
* @param \Cake\Datasource\ConnectionInterface $db The Elasticsearch connection
* @return void
*/
public function truncate(ConnectionInterface $db)
{
$query = new MatchAll();
$index = $db->getIndex();
$type = $index->getType($this->table);
$type->deleteByQuery($query);
$index->refresh();
}
/**
* Constructor.
*
* @param \Cake\Datasource\ConnectionInterface $connection The connection instance.
*/
public function __construct(ConnectionInterface $connection)
{
$this->_connection = $connection;
$this->_dialect = $connection->driver()->schemaDialect();
}
/**
* Generate the SQL statements to drop the constraints to the table
*
* @param \Cake\Datasource\ConnectionInterface $connection The connection to generate SQL for.
* @return array SQL to drop a table.
*/
public function dropConstraintSql(ConnectionInterface $connection)
{
$dialect = $connection->driver()->schemaDialect();
return $dialect->dropConstraintSql($this);
}
/**
* Get the table hash from MySQL for a specific table
*
* @param ConnectionInterface $db
* @return string
*/
protected function _hash(ConnectionInterface $db)
{
$sth = $db->execute("CHECKSUM TABLE " . $this->table);
$result = $sth->fetch('assoc');
$checksum = $result['Checksum'];
return $checksum;
}
/**
* {@inheritDoc}
*/
public function drop(ConnectionInterface $db)
{
try {
$sql = [];
if ($this->drop !== null) {
$sql = (array) $this->drop;
}
foreach ($sql as $query) {
$stmt = $db->prepare($query);
$stmt->execute();
$stmt->closeCursor();
}
} catch (Exception $e) {
return false;
}
return true;
}
/**
* Generate the SQL statements to truncate a table
*
* @param \Cake\Datasource\ConnectionInterface $connection The connection to generate SQL for.
* @return array SQL to truncate a table.
*/
public function truncateSql(ConnectionInterface $connection)
{
$dialect = $connection->driver()->schemaDialect();
return $dialect->truncateTableSql($this);
}
