/** * Attribute keys should be filtered and have defaults set. * * @return void */ public function testAddColumnFiltersAttributes() { $table = new Table('articles'); $table->addColumn('title', ['type' => 'string']); $result = $table->column('title'); $expected = ['type' => 'string', 'length' => null, 'precision' => null, 'default' => null, 'null' => null, 'fixed' => null, 'comment' => null]; $this->assertEquals($expected, $result); $table->addColumn('author_id', ['type' => 'integer']); $result = $table->column('author_id'); $expected = ['type' => 'integer', 'length' => null, 'precision' => null, 'default' => null, 'null' => null, 'unsigned' => null, 'comment' => null, 'autoIncrement' => null]; $this->assertEquals($expected, $result); $table->addColumn('amount', ['type' => 'decimal']); $result = $table->column('amount'); $expected = ['type' => 'decimal', 'length' => null, 'precision' => null, 'default' => null, 'null' => null, 'unsigned' => null, 'comment' => null]; $this->assertEquals($expected, $result); }
/** * Converts the internal records into data used to generate a query. * * @return array */ protected function _getRecords() { $fields = $values = $types = []; foreach ($this->records as $record) { $fields = array_merge($fields, array_keys(array_intersect_key($record, $this->fields))); } $fields = array_values(array_unique($fields)); foreach ($fields as $field) { $types[] = $this->_schema->column($field)['type']; } $default = array_fill_keys($fields, null); foreach ($this->records as $record) { $values[] = array_merge($default, $record); } return [$fields, $values, $types]; }
public function createTableSql(Table $table, $columns, $constraints, $indexes) { $content = array_merge($columns, $constraints); $content = implode(",\n", array_filter($content)); $tableName = $table->name(); if ($this->_driver->autoQuoting()) { $tableName = $this->_driver->quoteIdentifier($tableName); } $out = [sprintf("CREATE TABLE %s (\n%s\n)", $tableName, $content)]; foreach ($indexes as $index) { $out[] = $index; } foreach ($table->columns() as $column) { $columnData = $table->column($column); if ($this->_driver->autoQuoting()) { $column = $this->_driver->quoteIdentifier($column); } if (isset($columnData['comment'])) { $out[] = sprintf('COMMENT ON COLUMN %s.%s IS %s', $tableName, $column, $this->_driver->schemaValue($columnData['comment'])); } } return $out; }
/** * find the table, if any, actually referenced by the passed key field. * Search tables in db for keyField; if found search key constraints * for the table to which it refers. * * @param \Cake\Database\Schema\Table $schema The table schema to find a constraint for. * @param string $keyField The field to check for a constraint. * @return string|null Either the referenced table or null if the field has no constraints. */ public function findTableReferencedBy($schema, $keyField) { if (!$schema->column($keyField)) { return null; } foreach ($schema->constraints() as $constraint) { $constraintInfo = $schema->constraint($constraint); if (in_array($keyField, $constraintInfo['columns'])) { if (!isset($constraintInfo['references'])) { continue; } return $constraintInfo['references'][0]; } } return null; }
/** * 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; }
/** * {@inheritDoc} * * Note integer primary keys will return ''. This is intentional as Sqlite requires * that integer primary keys be defined in the column definition. * */ public function constraintSql(Table $table, $name) { $data = $table->constraint($name); if ($data['type'] === Table::CONSTRAINT_PRIMARY && count($data['columns']) === 1 && $table->column($data['columns'][0])['type'] === 'integer') { return ''; } $clause = ''; if ($data['type'] === Table::CONSTRAINT_PRIMARY) { $type = 'PRIMARY KEY'; } if ($data['type'] === Table::CONSTRAINT_UNIQUE) { $type = 'UNIQUE'; } if ($data['type'] === Table::CONSTRAINT_FOREIGN) { $type = 'FOREIGN KEY'; $clause = sprintf(' REFERENCES %s (%s) ON UPDATE %s ON DELETE %s', $this->_driver->quoteIdentifier($data['references'][0]), $this->_driver->quoteIdentifier($data['references'][1]), $this->_foreignOnClause($data['update']), $this->_foreignOnClause($data['delete'])); } $columns = array_map([$this->_driver, 'quoteIdentifier'], $data['columns']); return sprintf('CONSTRAINT %s %s (%s)%s', $this->_driver->quoteIdentifier($name), $type, implode(', ', $columns), $clause); }
/** * {@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'; } if (in_array($data['type'], ['integer', 'biginteger']) && ([$name] == (array) $table->primaryKey() || $data['autoIncrement'] === true)) { $out .= ' AUTO_INCREMENT'; } if (isset($data['null']) && $data['null'] === true) { $out .= $data['type'] === 'timestamp' ? ' NULL' : ' DEFAULT NULL'; unset($data['default']); } if (isset($data['default']) && $data['type'] !== 'timestamp') { $out .= ' DEFAULT ' . $this->_driver->schemaValue($data['default']); } if (isset($data['default']) && $data['type'] === 'timestamp' && strtolower($data['default']) === 'current_timestamp') { $out .= ' DEFAULT CURRENT_TIMESTAMP'; } if (isset($data['comment']) && $data['comment'] !== '') { $out .= ' COMMENT ' . $this->_driver->schemaValue($data['comment']); } return $out; }
/** * {@inheritDoc} */ public function columnSql(Table $table, $name) { $data = $table->column($name); $out = $this->_driver->quoteIdentifier($name); $typeMap = ['integer' => ' INTEGER', 'biginteger' => ' BIGINT', 'boolean' => ' BIT', 'binary' => ' BINARY', 'float' => ' FLOAT', 'decimal' => ' DECIMAL', 'text' => ' NVARCHAR(MAX)', 'date' => ' DATE', 'time' => ' TIME', 'datetime' => ' DATETIME', 'timestamp' => ' DATETIME', 'uuid' => ' UNIQUEIDENTIFIER']; if (isset($typeMap[$data['type']])) { $out .= $typeMap[$data['type']]; } if ($data['type'] === 'integer' || $data['type'] === 'biginteger') { if ([$name] === $table->primaryKey() || $data['autoIncrement'] === true) { unset($data['null'], $data['default']); $out .= ' IDENTITY(1, 1)'; } } if ($data['type'] === 'string') { $type = ' NVARCHAR'; if (!empty($data['fixed'])) { $type = ' NCHAR'; } if (!isset($data['length'])) { $data['length'] = 255; } $out .= sprintf('%s(%d)', $type, $data['length']); } if ($data['type'] === 'float' && isset($data['precision'])) { $out .= '(' . (int) $data['precision'] . ')'; } if ($data['type'] === 'decimal' && (isset($data['length']) || isset($data['precision']))) { $out .= '(' . (int) $data['length'] . ',' . (int) $data['precision'] . ')'; } if (isset($data['null']) && $data['null'] === false) { $out .= ' NOT NULL'; } if (isset($data['null']) && $data['null'] === true) { $out .= ' DEFAULT NULL'; unset($data['default']); } if (isset($data['default']) && $data['type'] !== 'datetime') { $default = is_bool($data['default']) ? (int) $data['default'] : $this->_driver->schemaValue($data['default']); $out .= ' DEFAULT ' . $default; } return $out; }
/** * {@inheritDoc} */ public function truncateTableSql(Table $table) { $name = $this->_driver->quoteIdentifier($table->name()); $queries = [sprintf('DELETE FROM %s', $name)]; // Restart identity sequences $pk = $table->primaryKey(); if (count($pk) === 1) { $column = $table->column($pk[0]); if (in_array($column['type'], ['integer', 'biginteger'])) { $queries[] = sprintf('DBCC CHECKIDENT(%s, RESEED, 0)', $name); } } return $queries; }
/** * Checks if table primary key has single column. * * @param Table $table Table schema object. * @param array $constraints Constraints list. * @return bool */ protected function _isSingleKey(Table $table, $constraints) { if (count($constraints) !== 1) { return false; } $constraint = $constraints[0]; $columns = $constraint['columns']; if (count($columns) !== 1) { return false; } $column = $table->column($columns[0]); return $column['type'] === 'integer' && $constraint['type'] === Table::CONSTRAINT_PRIMARY; }
/** * Converts the given array of records into data used to generate a query. * * @param array $records Records to be imported * @param \Cake\Database\Schema\Table $schema Table schema for which records will * be imported * @return array */ protected function _getRecords(array $records, TableSchema $schema) { $fields = $values = $types = []; $columns = $schema->columns(); foreach ($records as $record) { $fields = array_merge($fields, array_intersect(array_keys($record), $columns)); } $fields = array_values(array_unique($fields)); foreach ($fields as $field) { $types[$field] = $schema->column($field)['type']; } $default = array_fill_keys($fields, null); foreach ($records as $record) { $values[] = array_merge($default, $record); } return [$fields, $values, $types]; }
/** * Generates a string representation of a schema. * * @param \Cake\Database\Schema\Table $table Table schema. * @return string fields definitions */ protected function _generateSchema(Table $table) { $cols = $indexes = $constraints = []; foreach ($table->columns() as $field) { $fieldData = $table->column($field); $properties = implode(', ', $this->_values($fieldData)); $cols[] = " '{$field}' => [{$properties}],"; } foreach ($table->indexes() as $index) { $fieldData = $table->index($index); $properties = implode(', ', $this->_values($fieldData)); $indexes[] = " '{$index}' => [{$properties}],"; } foreach ($table->constraints() as $index) { $fieldData = $table->constraint($index); $properties = implode(', ', $this->_values($fieldData)); $constraints[] = " '{$index}' => [{$properties}],"; } $options = $this->_values($table->options()); $content = implode("\n", $cols) . "\n"; if (!empty($indexes)) { $content .= " '_indexes' => [\n" . implode("\n", $indexes) . "\n ],\n"; } if (!empty($constraints)) { $content .= " '_constraints' => [\n" . implode("\n", $constraints) . "\n ],\n"; } if (!empty($options)) { $content .= " '_options' => [\n" . implode(', ', $options) . "\n ],\n"; } return "[\n{$content} ]"; }
/** * {@inheritDoc} */ public function columnSql(Table $table, $name) { $data = $table->column($name); $out = $this->_driver->quoteIdentifier($name); $nativeJson = $this->_driver->supportsNativeJson(); $typeMap = ['integer' => ' INTEGER', 'biginteger' => ' BIGINT', 'boolean' => ' BOOLEAN', 'float' => ' FLOAT', 'decimal' => ' DECIMAL', 'date' => ' DATE', 'time' => ' TIME', 'datetime' => ' DATETIME', 'timestamp' => ' TIMESTAMP', 'uuid' => ' CHAR(36)', 'json' => $nativeJson ? ' JSON' : ' LONGTEXT']; $specialMap = ['string' => true, 'text' => true, 'binary' => 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; case 'text': $isKnownLength = in_array($data['length'], Table::$columnLengths); if (empty($data['length']) || !$isKnownLength) { $out .= ' TEXT'; break; } if ($isKnownLength) { $length = array_search($data['length'], Table::$columnLengths); $out .= ' ' . strtoupper($length) . 'TEXT'; } break; case 'binary': $isKnownLength = in_array($data['length'], Table::$columnLengths); if (empty($data['length']) || !$isKnownLength) { $out .= ' BLOB'; break; } if ($isKnownLength) { $length = array_search($data['length'], Table::$columnLengths); $out .= ' ' . strtoupper($length) . 'BLOB'; } 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'; } $hasCollate = ['text', 'string']; if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') { $out .= ' COLLATE ' . $data['collate']; } 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 && $data['type'] === 'timestamp') { $out .= ' NULL'; 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['default'])) { $out .= ' DEFAULT ' . $this->_driver->schemaValue($data['default']); unset($data['default']); } if (isset($data['comment']) && $data['comment'] !== '') { $out .= ' COMMENT ' . $this->_driver->schemaValue($data['comment']); } return $out; }