/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = <<<EOD
SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef,
\tpg_catalog.col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attnum > 0 AND NOT a.attisdropped
\tAND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
\t\tAND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema))
ORDER BY a.attnum
EOD;
$command = $this->connection->createCommand($sql);
$command->bindValue(':table', $table->name);
$command->bindValue(':schema', $table->schemaName);
if (($columns = $command->queryAll()) === []) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if (stripos($column['adsrc'], 'nextval') === 0 && preg_match('/nextval\\([^\']*\'([^\']+)\'[^\\)]*\\)/i', $column['adsrc'], $matches)) {
if (strpos($matches[1], '.') !== false || $table->schemaName === self::DEFAULT_SCHEMA) {
$this->sequences[$table->rawName . '.' . $c->name] = $matches[1];
} else {
$this->sequences[$table->rawName . '.' . $c->name] = $table->schemaName . '.' . $matches[1];
}
$c->autoIncrement = true;
}
}
return true;
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = <<<SQL
SELECT * FROM sys.syscolumns WHERE creator = '{$table->schemaName}' AND tname = '{$table->name}'
SQL;
try {
$columns = $this->connection->createCommand($sql)->queryAll();
if (empty($columns)) {
return false;
}
} catch (\Exception $e) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if ($c->autoIncrement && $table->sequenceName === null) {
$table->sequenceName = $table->name;
}
}
return true;
}
/**
* Collects the foreign key column details for the given table.
*
* @param TableSchema $table the table metadata
*/
protected function findConstraints($table)
{
$keys = [];
/** @type TableNameSchema $each */
foreach ($this->getTableNames() as $each) {
$sql = "PRAGMA foreign_key_list({$each->name})";
$fks = $this->connection->createCommand($sql)->queryAll();
if ($each->name === $table->name) {
foreach ($fks as $key) {
$column = $table->columns[strtolower($key['from'])];
$column->isForeignKey = true;
$column->refTable = $key['table'];
$column->refFields = $key['to'];
if (ColumnSchema::TYPE_INTEGER === $column->type) {
$column->type = ColumnSchema::TYPE_REF;
}
$table->foreignKeys[$key['from']] = [$key['table'], $key['to']];
// Add it to our foreign references as well
$relation = new RelationSchema(RelationSchema::BELONGS_TO, ['ref_table' => $key['table'], 'ref_fields' => $key['to'], 'field' => $key['from']]);
$table->addRelation($relation);
}
} else {
$keys[$each->name] = $fks;
foreach ($fks as $key => $fk) {
if ($fk['table'] === $table->name) {
$relation = new RelationSchema(RelationSchema::HAS_MANY, ['ref_table' => $each->name, 'ref_fields' => $fk['from'], 'field' => $fk['to']]);
$table->addRelation($relation);
$fks2 = $fks;
// if other has foreign keys to other tables, we can say these are related as well
foreach ($fks2 as $key2 => $fk2) {
if ($key !== $key2 && $fk2['table'] !== $table->name) {
// not same as parent, i.e. via reference back to self
// not the same key
$relation = new RelationSchema(RelationSchema::MANY_MANY, ['ref_table' => $fk2['table'], 'ref_fields' => $fk['to'], 'field' => $fk2['to'], 'junction_table' => $each->name, 'junction_field' => $fk['from'], 'junction_ref_field' => $fk2['from']]);
$table->addRelation($relation);
}
}
}
}
}
}
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$sql = 'SHOW FULL COLUMNS FROM ' . $table->rawName;
try {
$columns = $this->connection->createCommand($sql)->queryAll();
} catch (\Exception $e) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
if ($c->isPrimaryKey) {
if ($table->primaryKey === null) {
$table->primaryKey = $c->name;
} elseif (is_string($table->primaryKey)) {
$table->primaryKey = [$table->primaryKey, $c->name];
} else {
$table->primaryKey[] = $c->name;
}
if ($c->autoIncrement) {
$table->sequenceName = '';
if (ColumnSchema::TYPE_INTEGER === $c->type) {
$c->type = ColumnSchema::TYPE_ID;
}
}
}
$table->addColumn($c);
}
return true;
}
/**
* @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];
}
/**
* 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) . ')';
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$schema = !empty($table->schemaName) ? $table->schemaName : $this->getDefaultSchema();
if ($this->isISeries()) {
$sql = <<<SQL
SELECT column_name AS colname,
ordinal_position AS colno,
data_type AS typename,
CAST(column_default AS VARCHAR(254)) AS default,
is_nullable AS nulls,
length AS length,
numeric_scale AS scale,
is_identity AS identity
FROM qsys2.syscolumns
WHERE table_name = :table AND table_schema = :schema
ORDER BY ordinal_position
SQL;
} else {
$sql = <<<SQL
SELECT colname AS colname,
colno,
typename,
CAST(default AS VARCHAR(254)) AS default,
nulls,
length,
scale,
identity
FROM syscat.columns
WHERE syscat.columns.tabname = :table AND syscat.columns.tabschema = :schema
ORDER BY colno
SQL;
}
$command = $this->connection->createCommand($sql);
$command->bindValue(':table', $table->tableName);
$command->bindValue(':schema', $schema);
if (($columns = $command->queryAll()) === []) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
}
return count($table->columns) > 0;
}
/**
* Collects the primary and foreign key column details for the given table.
*
* @param TableSchema $table the table metadata
*/
protected function findConstraints($table)
{
$defaultSchema = static::getDefaultSchema();
$schema = !empty($table->schemaName) ? $table->schemaName : $defaultSchema;
$sql = <<<EOD
\t\tSELECT D.constraint_type, C.position, D.r_constraint_name,
C.owner as table_schema,
C.table_name as table_name,
\t\t C.column_name as column_name,
E.owner as referenced_table_schema,
E.table_name as referenced_table_name,
F.column_name as referenced_column_name
FROM ALL_CONS_COLUMNS C
inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = C.position
WHERE D.constraint_type = 'R'
ORDER BY D.constraint_name, C.position
EOD;
$columns = $columns2 = $command = $this->connection->createCommand($sql)->queryAll();
foreach ($columns as $key => $column) {
$ts = $column['TABLE_SCHEMA'];
$tn = $column['TABLE_NAME'];
$cn = $column['COLUMN_NAME'];
$rts = $column['REFERENCED_TABLE_SCHEMA'];
$rtn = $column['REFERENCED_TABLE_NAME'];
$rcn = $column['REFERENCED_COLUMN_NAME'];
if (0 == strcasecmp($tn, $table->name) && 0 == strcasecmp($ts, $schema)) {
$name = $rts == $defaultSchema ? $rtn : $rts . '.' . $rtn;
$table->foreignKeys[$cn] = [$name, $rcn];
if (isset($table->columns[$cn])) {
$table->columns[$cn]->isForeignKey = true;
$table->columns[$cn]->refTable = $name;
$table->columns[$cn]->refFields = $rcn;
if ('integer' === $table->columns[$cn]->type) {
$table->columns[$cn]->type = 'reference';
}
}
// Add it to our foreign references as well
$table->addRelation('belongs_to', $name, $rcn, $cn);
} elseif (0 == strcasecmp($rtn, $table->name) && 0 == strcasecmp($rts, $schema)) {
$name = $ts == $defaultSchema ? $tn : $ts . '.' . $tn;
$table->addRelation('has_many', $name, $cn, $rcn);
// if other has foreign keys to other tables, we can say these are related as well
foreach ($columns2 as $key2 => $column2) {
if (0 != strcasecmp($key, $key2)) {
$ts2 = $column2['TABLE_SCHEMA'];
$tn2 = $column2['TABLE_NAME'];
$cn2 = $column2['COLUMN_NAME'];
if (0 == strcasecmp($ts2, $ts) && 0 == strcasecmp($tn2, $tn)) {
$rts2 = $column2['REFERENCED_TABLE_SCHEMA'];
$rtn2 = $column2['REFERENCED_TABLE_NAME'];
$rcn2 = $column2['REFERENCED_COLUMN_NAME'];
if (0 != strcasecmp($rts2, $schema) || 0 != strcasecmp($rtn2, $table->name)) {
$name2 = $rts2 == $defaultSchema ? $rtn2 : $rts2 . '.' . $rtn2;
// not same as parent, i.e. via reference back to self
// not the same key
$table->addRelation('many_many', $name2, $rcn2, $rcn, "{$name}({$cn},{$cn2})");
}
}
}
}
}
}
}
/**
* Collects the foreign key column details for the given table.
* Also, collects the foreign tables and columns that reference the given table.
*
* @param TableSchema $table the table metadata
*/
protected function findConstraints($table)
{
$defaultSchema = $this->getDefaultSchema();
$tableSchema = !empty($table->schemaName) ? $table->schemaName : $this->getDefaultSchema();
$columns = [];
foreach ($this->getSchemaNames() as $schema) {
$sql = <<<MYSQL
SELECT table_schema, table_name, column_name, referenced_table_schema, referenced_table_name, referenced_column_name
FROM information_schema.KEY_COLUMN_USAGE WHERE referenced_table_name IS NOT NULL AND table_schema = '{$schema}';
MYSQL;
$columns = array_merge($columns, $this->connection->createCommand($sql)->queryAll());
}
$columns2 = $columns;
foreach ($columns as $key => $column) {
$ts = $column['table_schema'];
$tn = $column['table_name'];
$cn = $column['column_name'];
$rts = $column['referenced_table_schema'];
$rtn = $column['referenced_table_name'];
$rcn = $column['referenced_column_name'];
if (0 == strcasecmp($tn, $table->name) && 0 == strcasecmp($ts, $tableSchema)) {
$name = $rts == $defaultSchema ? $rtn : $rts . '.' . $rtn;
$table->foreignKeys[$cn] = [$name, $rcn];
$cnk = strtolower($cn);
if (isset($table->columns[$cnk])) {
$table->columns[$cnk]->isForeignKey = true;
$table->columns[$cnk]->refTable = $name;
$table->columns[$cnk]->refFields = $rcn;
if ('integer' === $table->columns[$cnk]->type) {
$table->columns[$cnk]->type = 'reference';
}
}
// Add it to our foreign references as well
$table->addRelation('belongs_to', $name, $rcn, $cn);
} elseif (0 == strcasecmp($rtn, $table->name) && 0 == strcasecmp($rts, $tableSchema)) {
$name = $ts == $defaultSchema ? $tn : $ts . '.' . $tn;
$table->addRelation('has_many', $name, $cn, $rcn);
// if other has foreign keys to other tables, we can say these are related as well
foreach ($columns2 as $key2 => $column2) {
if (0 != strcasecmp($key, $key2)) {
$ts2 = $column2['table_schema'];
$tn2 = $column2['table_name'];
$cn2 = $column2['column_name'];
if (0 == strcasecmp($ts2, $ts) && 0 == strcasecmp($tn2, $tn)) {
$rts2 = $column2['referenced_table_schema'];
$rtn2 = $column2['referenced_table_name'];
$rcn2 = $column2['referenced_column_name'];
if (0 != strcasecmp($rts2, $tableSchema) || 0 != strcasecmp($rtn2, $table->name)) {
$name2 = $rts2 == $defaultSchema ? $rtn2 : $rts2 . '.' . $rtn2;
// not same as parent, i.e. via reference back to self
// not the same key
$table->addRelation('many_many', $name2, $rcn2, $rcn, "{$name}({$cn},{$cn2})");
}
}
}
}
}
}
}
/**
* Collects the table column metadata.
*
* @param TableSchema $table the table metadata
*
* @return boolean whether the table exists in the database
*/
protected function findColumns($table)
{
$schemaName = $table->schemaName;
$tableName = $table->name;
$sql = <<<EOD
SELECT a.column_name, a.data_type ||
case
when data_precision is not null
then '(' || a.data_precision ||
case when a.data_scale > 0 then ',' || a.data_scale else '' end
|| ')'
when data_type = 'DATE' then ''
when data_type = 'NUMBER' then ''
else '(' || to_char(a.data_length) || ')'
end as data_type,
a.nullable, a.data_default,
( SELECT D.constraint_type
FROM ALL_CONS_COLUMNS C
inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
WHERE C.OWNER = B.OWNER
and C.table_name = B.object_name
and C.column_name = A.column_name
and D.constraint_type = 'P') as Key,
com.comments as column_comment
FROM ALL_TAB_COLUMNS A
inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)
LEFT JOIN user_col_comments com ON (A.table_name = com.table_name AND A.column_name = com.column_name)
WHERE
a.owner = '{$schemaName}'
\tand (b.object_type = 'TABLE' or b.object_type = 'VIEW')
\tand b.object_name = '{$tableName}'
ORDER by a.column_id
EOD;
$command = $this->connection->createCommand($sql);
if (($columns = $command->queryAll()) === []) {
return false;
}
foreach ($columns as $column) {
$c = $this->createColumn($column);
$table->addColumn($c);
if ($c->isPrimaryKey) {
if ($table->primaryKey === null) {
$table->primaryKey = $c->name;
} elseif (is_string($table->primaryKey)) {
$table->primaryKey = [$table->primaryKey, $c->name];
} else {
$table->primaryKey[] = $c->name;
}
// set defaults
$c->autoIncrement = false;
$table->sequenceName = '';
$sql = <<<EOD
SELECT trigger_body FROM ALL_TRIGGERS
WHERE table_owner = '{$schemaName}' and table_name = '{$tableName}'
and triggering_event = 'INSERT' and status = 'ENABLED' and trigger_type = 'BEFORE EACH ROW'
EOD;
$trig = $command = $this->connection->createCommand($sql)->queryScalar();
if (!empty($trig)) {
$c->autoIncrement = true;
$seq = stristr($trig, '.nextval', true);
$seq = substr($seq, strrpos($seq, ' ') + 1);
$table->sequenceName = $seq;
}
}
}
return true;
}
/**
* Collects the foreign key column details for the given table.
*
* @param TableSchema $table the table metadata
*/
protected function findConstraints($table)
{
$keys = [];
/** @type TableNameSchema $each */
foreach ($this->getTableNames() as $each) {
$sql = "PRAGMA foreign_key_list({$each->name})";
$fks = $this->connection->createCommand($sql)->queryAll();
if ($each->name === $table->name) {
foreach ($fks as $key) {
$column = $table->columns[$key['from']];
$column->isForeignKey = true;
$column->refTable = $key['table'];
$column->refFields = $key['to'];
if ('integer' === $column->type) {
$column->type = 'reference';
}
$table->foreignKeys[$key['from']] = [$key['table'], $key['to']];
// Add it to our foreign references as well
$table->addRelation('belongs_to', $key['table'], $key['to'], $key['from']);
}
} else {
$keys[$each->name] = $fks;
foreach ($fks as $key => $fk) {
if ($fk['table'] === $table->name) {
$table->addRelation('has_many', $each->name, $fk['from'], $fk['to']);
$fks2 = $fks;
// if other has foreign keys to other tables, we can say these are related as well
foreach ($fks2 as $key2 => $fk2) {
if ($key !== $key2 && $fk2['table'] !== $table->name) {
// not same as parent, i.e. via reference back to self
// not the same key
$table->addRelation('many_many', $fk2['table'], $fk['to'], $fk2['to'], "{$each->name}({$fk['from']},{$fk2['from']})");
}
}
}
}
}
}
}
