/**
* Generates the expression for selecting rows with specified composite key values.
*
* @param TableSchema $table the table schema
* @param array $values list of primary key values to be selected within
* @param string $prefix column prefix (ended with dot)
*
* @return string the expression for selection
*/
protected function createCompositeInCondition($table, $values, $prefix)
{
$vs = [];
foreach ($values as $value) {
$c = [];
foreach ($value as $k => $v) {
$c[] = $prefix . $table->getColumn($k)->rawName . '=' . $v;
}
$vs[] = '(' . implode(' AND ', $c) . ')';
}
return '(' . implode(' OR ', $vs) . ')';
}
/**
* @param string $table_name
* @param array $fields
* @param null|TableSchema $oldSchema
* @param bool $allow_update
* @param bool $allow_delete
*
* @throws \Exception
* @return string
*/
public function buildTableFields($table_name, $fields, $oldSchema = null, $allow_update = false, $allow_delete = false)
{
if (!is_array($fields) || empty($fields)) {
throw new \Exception('There are no fields in the requested schema.');
}
if (!isset($fields[0])) {
// single record possibly passed in without wrapper array
$fields = [$fields];
}
$columns = [];
$alterColumns = [];
$dropColumns = [];
$references = [];
$indexes = [];
$extras = [];
$dropExtras = [];
$commands = [];
$newFields = [];
foreach ($fields as $field) {
$newFields[strtolower($field['name'])] = array_change_key_case($field, CASE_LOWER);
}
if ($allow_delete && isset($oldSchema, $oldSchema->columns)) {
// check for columns to drop
/** @type ColumnSchema $oldField */
foreach ($oldSchema->columns as $ndx => $oldField) {
if (!isset($newFields[$ndx])) {
if (ColumnSchema::TYPE_VIRTUAL === $oldField->type) {
$dropExtras[$table_name][] = $oldField->name;
} else {
$dropColumns[] = $oldField->name;
}
}
}
}
foreach ($newFields as $ndx => $field) {
$name = $field['name'];
if (empty($name)) {
throw new \Exception("Invalid schema detected - no name element.");
}
/** @type ColumnSchema $oldField */
$oldField = isset($oldSchema) ? $oldSchema->getColumn($ndx) : null;
$isAlter = null !== $oldField;
if ($isAlter && !$allow_update) {
throw new \Exception("Field '{$name}' already exists in table '{$table_name}'.");
}
$oldForeignKey = isset($oldField) ? $oldField->isForeignKey : false;
$picklist = isset($field['picklist']) ? $field['picklist'] : [];
if (!empty($picklist) && !is_array($picklist)) {
// accept comma delimited from client side
$picklist = array_map('trim', explode(',', trim($picklist, ',')));
}
// extras
$extraTags = ['alias', 'label', 'description', 'picklist', 'validation', 'client_info', 'db_function', 'is_virtual_foreign_key', 'is_foreign_ref_service', 'ref_service', 'ref_service_id'];
$virtualFK = isset($field['is_virtual_foreign_key']) && boolval($field['is_virtual_foreign_key']);
if ($virtualFK) {
$extraTags = array_merge($extraTags, ['ref_table', 'ref_fields', 'ref_on_update', 'ref_on_delete']);
// cleanup possible overkill from API
$field['is_foreign_key'] = null;
if (!empty($field['type']) && ColumnSchema::TYPE_REF == $field['type']) {
$field['type'] = ColumnSchema::TYPE_INTEGER;
}
} else {
// don't set this in the database extras
$field['ref_service'] = null;
$field['ref_service_id'] = null;
}
$extraNew = array_only($field, $extraTags);
if ($oldField) {
$extraOld = array_only($oldField->toArray(), $extraTags);
$noDiff = ['picklist', 'validation', 'db_function'];
$extraNew = array_diff_assoc(array_except($extraNew, $noDiff), array_except($extraOld, $noDiff));
$oldPicklist = is_array($oldField->picklist) ? $oldField->picklist : [];
if (count($picklist) !== count($oldPicklist) || !empty(array_diff($picklist, $oldPicklist)) || !empty(array_diff($oldPicklist, $picklist))) {
$extraNew['picklist'] = $picklist;
}
$validation = isset($field['validation']) ? $field['validation'] : [];
$oldValidation = is_array($oldField->validation) ? $oldField->validation : [];
if (json_encode($validation) !== json_encode($oldValidation)) {
$extraNew['validation'] = $validation;
}
$dbFunction = isset($field['db_function']) ? $field['db_function'] : [];
$oldFunction = is_array($oldField->dbFunction) ? $oldField->dbFunction : [];
if (json_encode($dbFunction) !== json_encode($oldFunction)) {
$extraNew['db_function'] = $dbFunction;
}
}
// if same as old, don't bother
if ($virtualFK) {
// clean out extras
$field = array_except($field, $extraTags);
}
if ($oldField) {
$extraTags[] = 'default';
$settingsNew = array_except($field, $extraTags);
$settingsOld = array_except($oldField->toArray(), $extraTags);
$settingsNew = array_diff_assoc($settingsNew, $settingsOld);
// may be an array due to expressions
$default = isset($field['default']) ? $field['default'] : null;
if ($default !== $oldField->defaultValue) {
$settingsNew['default'] = $default;
}
// if empty, nothing to do here, check extras
if (empty($settingsNew)) {
if (!empty($extraNew)) {
$extraNew['table'] = $table_name;
$extraNew['field'] = $name;
$extras[] = $extraNew;
}
continue;
}
}
$type = isset($field['type']) ? strtolower($field['type']) : '';
switch ($type) {
case ColumnSchema::TYPE_USER_ID:
case ColumnSchema::TYPE_USER_ID_ON_CREATE:
case ColumnSchema::TYPE_USER_ID_ON_UPDATE:
case ColumnSchema::TYPE_TIMESTAMP_ON_CREATE:
case ColumnSchema::TYPE_TIMESTAMP_ON_UPDATE:
$extraNew['extra_type'] = $type;
break;
case ColumnSchema::TYPE_ID:
case 'pk':
$pkExtras = $this->getPrimaryKeyCommands($table_name, $name);
$commands = array_merge($commands, $pkExtras);
break;
case ColumnSchema::TYPE_VIRTUAL:
$extraNew['extra_type'] = $type;
$extraNew['table'] = $table_name;
$extraNew['field'] = $name;
$extras[] = $extraNew;
continue 2;
break;
}
$isForeignKey = isset($field['is_foreign_key']) ? boolval($field['is_foreign_key']) : false;
if (ColumnSchema::TYPE_REF == $type || $isForeignKey) {
// special case for references because the table referenced may not be created yet
$refTable = isset($field['ref_table']) ? $field['ref_table'] : null;
if (empty($refTable)) {
throw new \Exception("Invalid schema detected - no table element for reference type of {$name}.");
}
$refColumns = isset($field['ref_fields']) ? $field['ref_fields'] : 'id';
$refOnDelete = isset($field['ref_on_delete']) ? $field['ref_on_delete'] : null;
$refOnUpdate = isset($field['ref_on_update']) ? $field['ref_on_update'] : null;
if ($this->allowsSeparateForeignConstraint()) {
// will get to it later, $refTable may not be there
$keyName = $this->makeConstraintName('fk', $table_name, $name);
if (!$isAlter || !$oldForeignKey) {
$references[] = ['name' => $keyName, 'table' => $table_name, 'column' => $name, 'ref_table' => $refTable, 'ref_fields' => $refColumns, 'delete' => $refOnDelete, 'update' => $refOnUpdate];
}
}
}
// regardless of type
if (isset($field['is_unique']) && boolval($field['is_unique'])) {
if ($this->requiresCreateIndex(true, !$isAlter)) {
// will get to it later, create after table built
$keyName = $this->makeConstraintName('undx', $table_name, $name);
$indexes[] = ['name' => $keyName, 'table' => $table_name, 'column' => $name, 'unique' => true, 'drop' => $isAlter];
}
} elseif (isset($field['is_index']) && boolval($field['is_index'])) {
if ($this->requiresCreateIndex(false, !$isAlter)) {
// will get to it later, create after table built
$keyName = $this->makeConstraintName('ndx', $table_name, $name);
$indexes[] = ['name' => $keyName, 'table' => $table_name, 'column' => $name, 'drop' => $isAlter];
}
}
if ($isAlter) {
$alterColumns[$name] = $field;
} else {
$columns[$name] = $field;
}
if (!empty($extraNew)) {
$extraNew['table'] = $table_name;
$extraNew['field'] = $name;
$extras[] = $extraNew;
}
}
return ['columns' => $columns, 'alter_columns' => $alterColumns, 'drop_columns' => $dropColumns, 'references' => $references, 'indexes' => $indexes, 'extras' => $extras, 'drop_extras' => $dropExtras, 'commands' => $commands];
}
/**
* @param string $table_name
* @param array $fields
* @param null|TableSchema $oldSchema
* @param bool $allow_update
* @param bool $allow_delete
*
* @throws \Exception
* @return string
*/
public function buildTableFields($table_name, $fields, $oldSchema = null, $allow_update = false, $allow_delete = false)
{
if (!is_array($fields) || empty($fields)) {
throw new \Exception('There are no fields in the requested schema.');
}
if (!isset($fields[0])) {
// single record possibly passed in without wrapper array
$fields = [$fields];
}
$columns = [];
$alterColumns = [];
$references = [];
$indexes = [];
$labels = [];
$dropColumns = [];
$extraCommands = [];
$newFields = [];
foreach ($fields as $field) {
$newFields[$field['name']] = array_change_key_case($field, CASE_LOWER);
}
if ($allow_delete && isset($oldSchema, $oldSchema->columns)) {
// check for columns to drop
foreach ($oldSchema->columns as $oldName => $oldField) {
if (!isset($newFields[$oldName])) {
$dropColumns[] = $oldName;
}
}
}
foreach ($newFields as $name => $field) {
if (empty($name)) {
throw new \Exception("Invalid schema detected - no name element.");
}
/** @type ColumnSchema $oldField */
$oldField = isset($oldSchema) ? $oldSchema->getColumn($name) : null;
$isAlter = null !== $oldField;
if ($isAlter && !$allow_update) {
throw new \Exception("Field '{$name}' already exists in table '{$table_name}'.");
}
$oldForeignKey = isset($oldField) ? $oldField->isForeignKey : false;
$picklist = isset($field['picklist']) ? $field['picklist'] : [];
if (!empty($picklist) && !is_array($picklist)) {
// accept comma delimited from client side
$field['picklist'] = array_map('trim', explode(',', trim($picklist, ',')));
}
// extras
$extraTags = ['alias', 'label', 'description', 'picklist', 'validation', 'client_info'];
$extraNew = array_only($field, $extraTags);
if ($oldField) {
$extraOld = array_only($oldField->toArray(), $extraTags);
$extraNew = array_diff_assoc($extraNew, $extraOld);
}
// if (!empty($picklist)) {
// $oldPicklist = (isset($oldField)) ? $oldField->picklist : [];
// if ((count($picklist) !== count($oldPicklist)) ||
// empty(array_diff($picklist, $oldPicklist))
// ) {
// $temp['picklist'] = $picklist;
// }
// }
// if same as old, don't bother
if (!empty($oldField)) {
$settingsNew = array_except($field, $extraTags);
$settingsOld = array_except($oldField->toArray(), $extraTags);
$settingsNew = array_diff_assoc($settingsNew, $settingsOld);
// if empty, nothing to do here, check extras
if (empty($settingsNew)) {
if (!empty($extraNew)) {
$extraNew['table'] = $table_name;
$extraNew['field'] = $name;
$labels[] = $extraNew;
}
continue;
}
}
$type = isset($field['type']) ? strtolower($field['type']) : '';
switch ($type) {
case 'user_id':
$extraNew['extra_type'] = 'user_id';
break;
case 'user_id_on_create':
$extraNew['extra_type'] = 'user_id_on_create';
break;
case 'user_id_on_update':
$extraNew['extra_type'] = 'user_id_on_update';
break;
case 'timestamp_on_create':
$extraNew['extra_type'] = 'timestamp_on_create';
break;
case 'timestamp_on_update':
$extraNew['extra_type'] = 'timestamp_on_update';
break;
case 'id':
case 'pk':
$pkExtras = $this->getPrimaryKeyCommands($table_name, $name);
$extraCommands = array_merge($extraCommands, $pkExtras);
break;
}
$isForeignKey = isset($field['is_foreign_key']) ? boolval($field['is_foreign_key']) : false;
if ('reference' == $type || $isForeignKey) {
// special case for references because the table referenced may not be created yet
$refTable = isset($field['ref_table']) ? $field['ref_table'] : null;
if (empty($refTable)) {
throw new \Exception("Invalid schema detected - no table element for reference type of {$name}.");
}
$refColumns = isset($field['ref_fields']) ? $field['ref_fields'] : 'id';
$refOnDelete = isset($field['ref_on_delete']) ? $field['ref_on_delete'] : null;
$refOnUpdate = isset($field['ref_on_update']) ? $field['ref_on_update'] : null;
if ($this->allowsSeparateForeignConstraint()) {
// will get to it later, $refTable may not be there
$keyName = $this->makeConstraintName('fk', $table_name, $name);
if (!$isAlter || !$oldForeignKey) {
$references[] = ['name' => $keyName, 'table' => $table_name, 'column' => $name, 'ref_table' => $refTable, 'ref_fields' => $refColumns, 'delete' => $refOnDelete, 'update' => $refOnUpdate];
}
}
}
// regardless of type
if (isset($field['is_unique']) && boolval($field['is_unique'])) {
if ($this->requiresCreateIndex(true, !$isAlter)) {
// will get to it later, create after table built
$keyName = $this->makeConstraintName('undx', $table_name, $name);
$indexes[] = ['name' => $keyName, 'table' => $table_name, 'column' => $name, 'unique' => true, 'drop' => $isAlter];
}
} elseif (isset($field['is_index']) && boolval($field['is_index'])) {
if ($this->requiresCreateIndex(false, !$isAlter)) {
// will get to it later, create after table built
$keyName = $this->makeConstraintName('ndx', $table_name, $name);
$indexes[] = ['name' => $keyName, 'table' => $table_name, 'column' => $name, 'drop' => $isAlter];
}
}
if ($isAlter) {
$alterColumns[$name] = $field;
} else {
$columns[$name] = $field;
}
if (!empty($extraNew)) {
$extraNew['table'] = $table_name;
$extraNew['field'] = $name;
$labels[] = $extraNew;
}
}
return ['columns' => $columns, 'alter_columns' => $alterColumns, 'drop_columns' => $dropColumns, 'references' => $references, 'indexes' => $indexes, 'labels' => $labels, 'extras' => $extraCommands];
}