/**
* @param \Cake\Database\Schema\Table $table Table schema
* @return \Cake\Database\Schema\Table
*/
protected function _initializeSchema(Schema $table)
{
$table->columnType('payload', 'serialize');
$table->columnType('options', 'serialize');
$table->columnType('history', 'json');
return parent::_initializeSchema($table);
}
/**
* Tell CakePHP to modify the data structure of the entity data types
* @param Schema $schema this table's schema
* @return Schema the adjusted schema definition
*/
protected function _initializeSchema(Schema $schema)
{
$schema->columnType('sent_to', 'json');
$schema->columnType('sent_from', 'json');
$schema->columnType('email_data', 'json');
return $schema;
}
/**
* {@inheritDoc}
*/
public function truncate(ConnectionInterface $db)
{
$sql = $this->_schema->truncateSql($db);
foreach ($sql as $stmt) {
$db->execute($stmt)->closeCursor();
}
return true;
}
protected function _ensureTable($archiveTable)
{
try {
$archiveTable->schema();
} catch (Exception $e) {
$djSchema = TableRegistry::get('DelayedJobs.DelayedJobs')->schema();
$djColumns = $djSchema->columns();
$columns = [];
foreach ($djColumns as $djColumn) {
$columns[$djColumn] = $djSchema->column($djColumn);
}
$columns['payload']['type'] = 'binary';
$columns['options']['type'] = 'binary';
$archiveTableSchema = new Table($archiveTable->table(), $columns);
$archiveTableSchema->addConstraint('primary', $djSchema->constraint('primary'));
$createSql = $archiveTableSchema->createSql($archiveTable->connection());
foreach ($createSql as $createSqlQuery) {
$archiveTable->connection()->query($createSqlQuery);
}
}
}
protected function _initializeSchema(Schema $table)
{
$table->columnType('email', 'crypted');
$table->columnType('username', 'crypted');
$table->columnType('last_ip', 'crypted');
$table->columnType('token', 'crypted');
return $table;
}
/**
* Initialize schema method
*
* @param \Cake\Database\Schema\Table $schema The schema of the Table.
*
* @return \Cake\Database\Schema\Table
*/
protected function _initializeSchema(Schema $schema)
{
$schema->columnType('secret', 'encryptedsecurity');
$schema->columnType('username', 'encryptedsecurity');
$schema->columnType('session', 'encryptedsecurity');
$schema->columnType('recovery_code', 'encryptedsecurity');
return $schema;
}
/**
* Does individual field validation handling.
*
* @param \Cake\Database\Schema\Table $schema The table schema for the current field.
* @param string $fieldName Name of field to be validated.
* @param array $metaData metadata for field
* @param string $primaryKey The primary key field
* @return array Array of validation for the field.
*/
public function fieldValidation($schema, $fieldName, array $metaData, $primaryKey)
{
$ignoreFields = ['created', 'modified', 'updated'];
if (in_array($fieldName, $ignoreFields)) {
return false;
}
$rule = false;
if ($fieldName === 'email') {
$rule = 'email';
} elseif ($metaData['type'] === 'uuid') {
$rule = 'uuid';
} elseif ($metaData['type'] === 'integer') {
$rule = 'numeric';
} elseif ($metaData['type'] === 'float') {
$rule = 'numeric';
} elseif ($metaData['type'] === 'decimal') {
$rule = 'decimal';
} elseif ($metaData['type'] === 'boolean') {
$rule = 'boolean';
} elseif ($metaData['type'] === 'date') {
$rule = 'date';
} elseif ($metaData['type'] === 'time') {
$rule = 'time';
} elseif ($metaData['type'] === 'datetime') {
$rule = 'datetime';
} elseif ($metaData['type'] === 'inet') {
$rule = 'ip';
}
$allowEmpty = false;
if (in_array($fieldName, $primaryKey)) {
$allowEmpty = 'create';
} elseif ($metaData['null'] === true) {
$allowEmpty = true;
}
$validation = ['valid' => ['rule' => $rule, 'allowEmpty' => $allowEmpty]];
foreach ($schema->constraints() as $constraint) {
$constraint = $schema->constraint($constraint);
if (!in_array($fieldName, $constraint['columns']) || count($constraint['columns']) > 1) {
continue;
}
if ($constraint['type'] === SchemaTable::CONSTRAINT_UNIQUE) {
$validation['unique'] = ['rule' => 'validateUnique', 'provider' => 'table'];
}
}
return $validation;
}
/**
* Test that if a table already exists in the test database, it will dropped
* before being recreated
*
* @return void
*/
public function testResetDbIfTableExists()
{
$db = ConnectionManager::get('test');
$restore = $db->logQueries();
$db->logQueries(true);
$this->manager->setDebug(true);
$buffer = new ConsoleOutput();
Log::config('testQueryLogger', ['className' => 'Console', 'stream' => $buffer]);
$table = new Table('articles', ['id' => ['type' => 'integer', 'unsigned' => true], 'title' => ['type' => 'string', 'length' => 255]]);
$table->addConstraint('primary', ['type' => 'primary', 'columns' => ['id']]);
$sql = $table->createSql($db);
foreach ($sql as $stmt) {
$db->execute($stmt);
}
$test = $this->getMockBuilder('Cake\\TestSuite\\TestCase')->getMock();
$test->fixtures = ['core.articles'];
$this->manager->fixturize($test);
$this->manager->load($test);
$db->logQueries($restore);
$this->assertContains('DROP TABLE', implode('', $buffer->messages()));
}
/**
* {@inheritDoc}
*/
public function indexSql(Table $table, $name)
{
$data = $table->index($name);
if ($data['type'] === Table::INDEX_INDEX) {
$out = 'KEY ';
}
if ($data['type'] === Table::INDEX_FULLTEXT) {
$out = 'FULLTEXT KEY ';
}
$out .= $this->_driver->quoteIdentifier($name);
return $this->_keySql($out, $data);
}
/**
* Returns the schema table object describing this table's properties.
*
* If an \Cake\Database\Schema\Table is passed, it will be used for this table
* instead of the default one.
*
* If an array is passed, a new \Cake\Database\Schema\Table will be constructed
* out of it and used as the schema for this table.
*
* @param array|\Cake\Database\Schema\Table|null $schema New schema to be used for this table
* @return \Cake\Database\Schema\Table
*/
public function schema($schema = null)
{
if ($schema === null) {
if ($this->_schema === null) {
$this->_schema = $this->_initializeSchema($this->connection()->schemaCollection()->describe($this->table()));
}
return $this->_schema;
}
if (is_array($schema)) {
$constraints = [];
if (isset($schema['_constraints'])) {
$constraints = $schema['_constraints'];
unset($schema['_constraints']);
}
$schema = new Schema($this->table(), $schema);
foreach ($constraints as $name => $value) {
$schema->addConstraint($name, $value);
}
}
return $this->_schema = $schema;
}
/**
* Test truncateSql()
*
* @return void
*/
public function testTruncateSql()
{
$driver = $this->_getMockedDriver();
$connection = $this->getMockBuilder('Cake\\Database\\Connection')->disableOriginalConstructor()->getMock();
$connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
$table = new Table('articles');
$result = $table->truncateSql($connection);
$this->assertCount(1, $result);
$this->assertEquals('TRUNCATE TABLE `articles`', $result[0]);
}
/**
* Test truncateSql() with no sequences
*
* @return void
*/
public function testTruncateSqlNoSequences()
{
$driver = $this->_getMockedDriver();
$connection = $this->getMock('Cake\\Database\\Connection', [], [], '', false);
$connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
$statement = $this->getMock('\\PDOStatement', ['execute', 'rowCount', 'closeCursor', 'fetch']);
$driver->connection()->expects($this->once())->method('prepare')->with('SELECT 1 FROM sqlite_master WHERE name = "sqlite_sequence"')->will($this->returnValue($statement));
$statement->expects($this->once())->method('fetch')->will($this->returnValue(false));
$table = new Table('articles');
$result = $table->truncateSql($connection);
$this->assertCount(1, $result);
$this->assertEquals('DELETE FROM "articles"', $result[0]);
}
/**
* {@inheritDoc}
*/
public function columnSql(Table $table, $name)
{
$data = $table->column($name);
$out = $this->_driver->quoteIdentifier($name);
$typeMap = ['integer' => ' INTEGER', 'biginteger' => ' BIGINT', 'boolean' => ' BOOLEAN', 'binary' => ' LONGBLOB', 'float' => ' FLOAT', 'decimal' => ' DECIMAL', 'text' => ' TEXT', 'date' => ' DATE', 'time' => ' TIME', 'datetime' => ' DATETIME', 'timestamp' => ' TIMESTAMP', 'uuid' => ' CHAR(36)'];
$specialMap = ['string' => true];
if (isset($typeMap[$data['type']])) {
$out .= $typeMap[$data['type']];
}
if (isset($specialMap[$data['type']])) {
switch ($data['type']) {
case 'string':
$out .= !empty($data['fixed']) ? ' CHAR' : ' VARCHAR';
if (!isset($data['length'])) {
$data['length'] = 255;
}
break;
}
}
$hasLength = ['integer', 'string'];
if (in_array($data['type'], $hasLength, true) && isset($data['length'])) {
$out .= '(' . (int) $data['length'] . ')';
}
$hasPrecision = ['float', 'decimal'];
if (in_array($data['type'], $hasPrecision, true) && (isset($data['length']) || isset($data['precision']))) {
$out .= '(' . (int) $data['length'] . ',' . (int) $data['precision'] . ')';
}
$hasUnsigned = ['float', 'decimal', 'integer', 'biginteger'];
if (in_array($data['type'], $hasUnsigned, true) && isset($data['unsigned']) && $data['unsigned'] === true) {
$out .= ' UNSIGNED';
}
if (isset($data['null']) && $data['null'] === false) {
$out .= ' NOT NULL';
}
$addAutoIncrement = [$name] == (array) $table->primaryKey() && !$table->hasAutoIncrement();
if (in_array($data['type'], ['integer', 'biginteger']) && ($data['autoIncrement'] === true || $addAutoIncrement)) {
$out .= ' AUTO_INCREMENT';
}
if (isset($data['null']) && $data['null'] === true) {
$out .= $data['type'] === 'timestamp' ? ' NULL' : ' DEFAULT NULL';
unset($data['default']);
}
if (isset($data['default']) && !in_array($data['type'], ['timestamp', 'datetime'])) {
$out .= ' DEFAULT ' . $this->_driver->schemaValue($data['default']);
unset($data['default']);
}
if (isset($data['default']) && in_array($data['type'], ['timestamp', 'datetime']) && strtolower($data['default']) === 'current_timestamp') {
$out .= ' DEFAULT CURRENT_TIMESTAMP';
unset($data['default']);
}
if (isset($data['comment']) && $data['comment'] !== '') {
$out .= ' COMMENT ' . $this->_driver->schemaValue($data['comment']);
}
return $out;
}
/**
* Initialize Schema
*
* @param Schema $schema
* @return Schema
*/
protected function _initializeSchema(Schema $schema)
{
$schema->columnType('data', 'json');
$schema->columnType('stats', 'json');
return $schema;
}
/**
* Tell CakePHP to modify the data structure of the entity data types
* @param Schema $schema this table's schema
* @return Schema the adjusted schema definition
*/
protected function _initializeSchema(Schema $schema)
{
$schema->columnType('viewVars', 'json');
return $schema;
}
/**
* Add/update a constraint into the schema object.
*
* @param \Cake\Database\Schema\Table $table The table to update.
* @param string $name The index name.
* @param string $type The index type.
* @param array $row The metadata record to update with.
* @return void
*/
protected function _convertConstraint($table, $name, $type, $row)
{
$constraint = $table->constraint($name);
if (!$constraint) {
$constraint = ['type' => $type, 'columns' => []];
}
$constraint['columns'][] = $row['attname'];
$table->addConstraint($name, $constraint);
}
/**
* Returns an array of column data for a single column
*
* @param \Cake\Database\Schema\Table $tableSchema Name of the table to retrieve columns for
* @param string $column A column to retrieve data for
* @return array
*/
public function column($tableSchema, $column)
{
return ['columnType' => $tableSchema->columnType($column), 'options' => $this->attributes($tableSchema->name(), $column)];
}
<?php
/**
* Queued Tasks schema file
*
* @author David Yell <*****@*****.**>
* @author MGriesbach@gmail.com
*/
use Cake\Database\Schema\Table;
$t = new Table('queued_tasks');
$t->addColumn('id', ['type' => 'integer', 'length' => 10, 'null' => false, 'default' => null]);
$t->addColumn('job_type', ['type' => 'string', 'null' => false, 'length' => 45]);
$t->addColumn('data', ['type' => 'text', 'null' => true, 'default' => null]);
$t->addColumn('job_group', ['type' => 'string', 'length' => 255, 'null' => true, 'default' => null]);
$t->addColumn('reference', ['type' => 'string', 'length' => 255, 'null' => true, 'default' => null]);
$t->addColumn('created', ['type' => 'datetime', 'null' => true, 'default' => null]);
$t->addColumn('notbefore', ['type' => 'datetime', 'null' => true, 'default' => null]);
$t->addColumn('fetched', ['type' => 'datetime', 'null' => true, 'default' => null]);
$t->addColumn('progress', ['type' => 'float', 'length' => '3,2', 'null' => true, 'default' => null]);
$t->addColumn('status', ['type' => 'string', 'length' => 255, 'null' => true, 'default' => null]);
$t->addColumn('completed', ['type' => 'datetime', 'null' => true, 'default' => null]);
$t->addColumn('failed', ['type' => 'integer', 'null' => false, 'default' => '0', 'length' => 3]);
$t->addColumn('failure_message', ['type' => 'text', 'null' => true, 'default' => null]);
$t->addColumn('workerkey', ['type' => 'string', 'null' => true, 'length' => 45]);
$t->addConstraint('primary', ['type' => 'primary', 'columns' => ['id']]);
$t->options(['collate' => 'utf8_unicode_ci']);
/**
* _initializeSchema
*/
protected function _initializeSchema(\Cake\Database\Schema\Table $table)
{
$table->columnType('image', 'proffer.file');
return $table;
}
/**
* test schema reflection without $import or $fields will reflect the schema
*
* @return void
*/
public function testInitNoImportNoFields()
{
$db = ConnectionManager::get('test');
$collection = $db->schemaCollection();
if (!in_array('letters', $collection->listTables())) {
$table = new Table('letters', ['id' => ['type' => 'integer'], 'letter' => ['type' => 'string', 'length' => 1]]);
$table->addConstraint('primary', ['type' => 'primary', 'columns' => ['id']]);
$sql = $table->createSql($db);
foreach ($sql as $stmt) {
$db->execute($stmt);
}
}
$fixture = new LettersFixture();
$fixture->init();
$this->assertEquals(['id', 'letter'], $fixture->schema()->columns());
$db = $this->getMockBuilder('Cake\\Database\\Connection')->setMethods(['prepare', 'execute'])->disableOriginalConstructor()->getMock();
$db->expects($this->never())->method('prepare');
$db->expects($this->never())->method('execute');
$this->assertTrue($fixture->create($db));
$this->assertTrue($fixture->drop($db));
}
/**
* @param \Cake\Database\Schema\Table $schema
*
* @return \Cake\Database\Schema\Table
*/
protected function _initializeSchema(Schema $schema)
{
$schema->columnType('year_of_birth', 'year');
return $schema;
}
/**
* @param \Cake\Database\Schema\Table $table
*
* @return \Cake\Database\Schema\Table
*/
protected function _initializeSchema(Schema $table)
{
$table->columnType('image', 'image');
return $table;
}
/**
* Generate the SQL to drop a table.
*
* @param \Cake\Database\Schema\Table $table Table instance
* @return array SQL statements to drop a table.
*/
public function dropTableSql(Table $table)
{
$sql = sprintf('DROP TABLE %s CASCADE', $this->_driver->quoteIdentifier($table->name()));
return [$sql];
}
/**
* Alter the schema used by this table.
*
* @param \Cake\Database\Schema\Table $table The table definition fetched from database
* @return \Cake\Database\Schema\Table the altered schema
*/
protected function _initializeSchema(Schema $table)
{
$table->columnType('data', 'serialized');
return $table;
}
/**
* {@inheritDoc}
*/
public function truncateTableSql(Table $table)
{
$name = $table->name();
$sql = [];
if ($this->hasSequences()) {
$sql[] = sprintf('DELETE FROM sqlite_sequence WHERE name="%s"', $name);
}
$sql[] = sprintf('DELETE FROM "%s"', $name);
return $sql;
}
/**
* {@inheritDoc}
*/
public function truncateTableSql(Table $table)
{
$name = $this->_driver->quoteIdentifier($table->name());
return [sprintf('TRUNCATE TABLE %s', $name)];
}
/**
* Generate String representation of Records
*
* @param \Cake\Database\Schema\Table $table Table schema array
* @param int $recordCount The number of records to generate.
* @return array Array of records to use in the fixture.
*/
protected function _generateRecords(Table $table, $recordCount = 1)
{
$records = [];
for ($i = 0; $i < $recordCount; $i++) {
$record = [];
foreach ($table->columns() as $field) {
$fieldInfo = $table->column($field);
$insert = '';
switch ($fieldInfo['type']) {
case 'decimal':
$insert = $i + 1.5;
break;
case 'biginteger':
case 'integer':
case 'float':
$insert = $i + 1;
break;
case 'string':
case 'binary':
$isPrimary = in_array($field, $table->primaryKey());
if ($isPrimary) {
$insert = Text::uuid();
} else {
$insert = "Lorem ipsum dolor sit amet";
if (!empty($fieldInfo['length'])) {
$insert = substr($insert, 0, (int) $fieldInfo['length'] - 2);
}
}
break;
case 'timestamp':
$insert = time();
break;
case 'datetime':
$insert = date('Y-m-d H:i:s');
break;
case 'date':
$insert = date('Y-m-d');
break;
case 'time':
$insert = date('H:i:s');
break;
case 'boolean':
$insert = 1;
break;
case 'text':
$insert = "Lorem ipsum dolor sit amet, aliquet feugiat.";
$insert .= " Convallis morbi fringilla gravida,";
$insert .= " phasellus feugiat dapibus velit nunc, pulvinar eget sollicitudin";
$insert .= " venenatis cum nullam, vivamus ut a sed, mollitia lectus. Nulla";
$insert .= " vestibulum massa neque ut et, id hendrerit sit,";
$insert .= " feugiat in taciti enim proin nibh, tempor dignissim, rhoncus";
$insert .= " duis vestibulum nunc mattis convallis.";
break;
case 'uuid':
$insert = Text::uuid();
break;
}
$record[$field] = $insert;
}
$records[] = $record;
}
return $records;
}
/**
* Test truncateSql()
*
* @return void
*/
public function testTruncateSql()
{
$driver = $this->_getMockedDriver();
$connection = $this->getMockBuilder('Cake\\Database\\Connection')->disableOriginalConstructor()->getMock();
$connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
$table = new Table('schema_articles');
$table->addColumn('id', 'integer')->addConstraint('primary', ['type' => 'primary', 'columns' => ['id']]);
$result = $table->truncateSql($connection);
$this->assertCount(1, $result);
$this->assertEquals('TRUNCATE "schema_articles" RESTART IDENTITY CASCADE', $result[0]);
}
public function truncateTableSql(Table $table)
{
$tableName = $table->name();
if ($this->_driver->autoQuoting()) {
$tableName = $this->_driver->quoteIdentifier($tableName);
}
return [sprintf("TRUNCATE TABLE %s", $tableName)];
}
protected function _initializeSchema(Schema $schema)
{
$schema->columnType('gender', 'gender');
return $schema;
}
