/**
* 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);
}
}
}
}
}
}
}
protected function buildTableRelations(TableSchema $table, $constraints)
{
$schema = !empty($table->schemaName) ? $table->schemaName : $this->getDefaultSchema();
$defaultSchema = $this->getDefaultSchema();
$constraints2 = $constraints;
foreach ($constraints as $key => $constraint) {
$constraint = array_change_key_case($constraint, CASE_LOWER);
$ts = $constraint['table_schema'];
$tn = $constraint['table_name'];
$cn = $constraint['column_name'];
$rts = $constraint['referenced_table_schema'];
$rtn = $constraint['referenced_table_name'];
$rcn = $constraint['referenced_column_name'];
if (0 == strcasecmp($tn, $table->tableName) && 0 == strcasecmp($ts, $schema)) {
$name = $rts == $defaultSchema ? $rtn : $rts . '.' . $rtn;
$cnk = strtolower($cn);
$table->foreignKeys[$cnk] = [$name, $rcn];
if (isset($table->columns[$cnk])) {
$table->columns[$cnk]->isForeignKey = true;
$table->columns[$cnk]->refTable = $name;
$table->columns[$cnk]->refFields = $rcn;
if (ColumnSchema::TYPE_INTEGER === $table->columns[$cnk]->type) {
$table->columns[$cnk]->type = ColumnSchema::TYPE_REF;
}
}
// Add it to our foreign references as well
$relation = new RelationSchema(['type' => RelationSchema::BELONGS_TO, 'ref_table' => $name, 'ref_fields' => $rcn, 'field' => $cn]);
$table->addRelation($relation);
} elseif (0 == strcasecmp($rtn, $table->tableName) && 0 == strcasecmp($rts, $schema)) {
$name = $ts == $defaultSchema ? $tn : $ts . '.' . $tn;
$relation = new RelationSchema(['type' => RelationSchema::HAS_MANY, 'ref_table' => $name, 'ref_fields' => $cn, 'field' => $rcn]);
$table->addRelation($relation);
// if other has foreign keys to other tables, we can say these are related as well
foreach ($constraints2 as $key2 => $constraint2) {
if (0 != strcasecmp($key, $key2)) {
$constraint2 = array_change_key_case($constraint2, CASE_LOWER);
$ts2 = $constraint2['table_schema'];
$tn2 = $constraint2['table_name'];
$cn2 = $constraint2['column_name'];
if (0 == strcasecmp($ts2, $ts) && 0 == strcasecmp($tn2, $tn)) {
$rts2 = $constraint2['referenced_table_schema'];
$rtn2 = $constraint2['referenced_table_name'];
$rcn2 = $constraint2['referenced_column_name'];
if (0 != strcasecmp($rts2, $schema) || 0 != strcasecmp($rtn2, $table->tableName)) {
$name2 = $rts2 == $schema ? $rtn2 : $rts2 . '.' . $rtn2;
// not same as parent, i.e. via reference back to self
// not the same key
$relation = new RelationSchema(['type' => RelationSchema::MANY_MANY, 'ref_table' => $name2, 'ref_fields' => $rcn2, 'field' => $rcn, 'junction_table' => $name, 'junction_field' => $cn, 'junction_ref_field' => $cn2]);
$table->addRelation($relation);
}
}
}
}
}
}
}
/**
* 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)
{
$schema = !empty($table->schemaName) ? $table->schemaName : $this->getDefaultSchema();
$defaultSchema = $this->getDefaultSchema();
$sql = <<<EOD
SELECT indextype,colnames FROM SYS.SYSINDEXES WHERE creator = :schema AND tname = :table
EOD;
$params = [':schema' => $schema, ':table' => $table->name];
$columns = $this->connection->createCommand($sql)->queryAll(true, $params);
foreach ($columns as $key => $column) {
$type = $column['indextype'];
$colnames = $column['colnames'];
switch ($type) {
case 'Primary Key':
$colnames = explode(',', $colnames);
switch (count($colnames)) {
case 0:
// No primary key on table
$table->primaryKey = null;
break;
case 1:
// Only 1 primary key
$primary = strstr($colnames[0], ' ', true);
$key = strtolower($primary);
if (isset($table->columns[$key])) {
$table->columns[$key]->isPrimaryKey = true;
if ('integer' === $table->columns[$key]->type && $table->columns[$key]->autoIncrement) {
$table->columns[$key]->type = 'id';
}
}
$table->primaryKey = $primary;
break;
default:
if (is_array($colnames)) {
$primary = '';
foreach ($colnames as $key) {
$key = strstr($key, ' ', true);
$primary = empty($key) ? $key : ',' . $key;
}
$table->primaryKey = $primary;
}
break;
}
break;
case 'Unique Constraint':
$field = strtolower(strstr($colnames, ' ', true));
if (isset($table->columns[$field])) {
$table->columns[$field]->IsUnique = true;
}
break;
case 'Non-unique':
$colnames = explode(',', $colnames);
switch (count($colnames)) {
case 1:
// Only 1 key
$field = strtolower(strstr($colnames[0], ' ', true));
if (isset($table->columns[$field])) {
$table->columns[$field]->isIndex = true;
}
break;
default:
if (is_array($colnames)) {
foreach ($colnames as $key) {
$field = strtolower(strstr($key, ' ', true));
if (isset($table->columns[$field])) {
$table->columns[$field]->isIndex = true;
}
}
}
break;
}
break;
}
}
$sql = <<<EOD
SELECT * FROM SYS.SYSFOREIGNKEYS WHERE foreign_creator NOT IN ('SYS','dbo')
EOD;
$columns = $columns2 = $this->connection->createCommand($sql)->queryAll();
foreach ($columns as $key => $column) {
list($cn, $rcn) = explode(' IS ', $column['columns']);
$ts = $column['foreign_creator'];
$tn = $column['foreign_tname'];
$rts = $column['primary_creator'];
$rtn = $column['primary_tname'];
if (0 == strcasecmp($tn, $table->name) && 0 == strcasecmp($ts, $schema)) {
$name = $rts == $defaultSchema ? $rtn : $rts . '.' . $rtn;
$cnk = strtolower($cn);
$table->foreignKeys[$cnk] = [$name, $rcn];
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, $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['foreign_creator'];
$tn2 = $column2['foreign_tname'];
list($cn2, $rcn2) = explode(' IS ', $column2['columns']);
if (0 == strcasecmp($ts2, $ts) && 0 == strcasecmp($tn2, $tn)) {
$rts2 = $column2['primary_creator'];
$rtn2 = $column2['primary_tname'];
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 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 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']})");
}
}
}
}
}
}
}
