/** * 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]; }