/** * Get a table definition * @param string $table the table to analyze * @param bool $detectRelations should relations be extracted - defaults to `true` * @param bool $lowerCase should the table fields be converted to lowercase - defaults to `true` * @return the newly added definition */ public function definition(string $table, bool $detectRelations = true, bool $lowerCase = true) : Table { if (isset($this->tables[$table])) { return $this->tables[$table]; } $definition = new Table($table); $columns = []; $primary = []; $comment = ''; switch ($this->driver()) { case 'mysql': case 'mysqli': foreach ($this->all("SHOW FULL COLUMNS FROM {$table}", null, null, false, 'assoc') as $data) { $columns[$data['Field']] = $data; if ($data['Key'] == 'PRI') { $primary[] = $data['Field']; } } $comment = (string) $this->one("SELECT table_comment FROM information_schema.tables WHERE table_schema = ? AND table_name = ?", [$this->name(), $table]); break; case 'postgre': $columns = $this->all("SELECT * FROM information_schema.columns WHERE table_schema = ? AND table_name = ?", [$this->name(), $table], 'column_name', false, 'assoc_lc'); $pkname = $this->one("SELECT constraint_name FROM information_schema.table_constraints\n WHERE table_schema = ? AND table_name = ? AND constraint_type = ?", [$this->name(), $table, 'PRIMARY KEY']); if ($pkname) { $primary = $this->all("SELECT column_name FROM information_schema.key_column_usage\n WHERE table_schema = ? AND table_name = ? AND constraint_name = ?", [$this->name(), $table, $pkname]); } break; case 'oracle': $columns = $this->all("SELECT * FROM all_tab_cols WHERE table_name = ? AND owner = ?", [strtoupper($table), $this->name()], 'COLUMN_NAME', false, 'assoc_uc'); $owner = $this->name(); // current($columns)['OWNER']; $pkname = $this->one("SELECT constraint_name FROM all_constraints\n WHERE table_name = ? AND constraint_type = ? AND owner = ?", [strtoupper($table), 'P', $owner]); if ($pkname) { $primary = $this->all("SELECT column_name FROM all_cons_columns\n WHERE table_name = ? AND constraint_name = ? AND owner = ?", [strtoupper($table), $pkname, $owner]); } break; //case 'ibase': // $columns = $this->all( // "SELECT * FROM rdb$relation_fields WHERE rdb$relation_name = ? ORDER BY rdb$field_position", // [ strtoupper($table) ], // 'FIELD_NAME', // false, // 'assoc_uc' // ); // break; //case 'mssql': // break; //case 'sqlite': // break; //case 'ibase': // $columns = $this->all( // "SELECT * FROM rdb$relation_fields WHERE rdb$relation_name = ? ORDER BY rdb$field_position", // [ strtoupper($table) ], // 'FIELD_NAME', // false, // 'assoc_uc' // ); // break; //case 'mssql': // break; //case 'sqlite': // break; default: throw new DatabaseException('Driver is not supported: ' . $this->driver(), 500); } if (!count($columns)) { throw new DatabaseException('Table not found by name'); } $definition->addColumns($columns)->setPrimaryKey($primary)->setComment($comment); $this->tables[$table] = $definition; if ($detectRelations) { switch ($this->driver()) { case 'mysql': case 'mysqli': // relations where the current table is referenced // assuming current table is on the "one" end having "many" records in the referencing table // resulting in a "hasMany" or "manyToMany" relationship (if a pivot table is detected) $relations = []; foreach ($this->all("SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME\n FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n WHERE TABLE_SCHEMA = ? AND REFERENCED_TABLE_SCHEMA = ? AND REFERENCED_TABLE_NAME = ?", [$this->name(), $this->name(), $table], null, false, 'assoc_uc') as $relation) { $relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['TABLE_NAME']; $relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['REFERENCED_COLUMN_NAME']] = $relation['COLUMN_NAME']; } foreach ($relations as $data) { $rtable = $this->definition($data['table'], true, $lowerCase); // ?? $this->addTableByName($data['table'], false); $columns = []; foreach ($rtable->getColumns() as $column) { if (!in_array($column, $data['keymap'])) { $columns[] = $column; } } $foreign = []; $usedcol = []; if (count($columns)) { foreach ($this->all("SELECT\n TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, \n REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME\n FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n WHERE\n TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME IN (??) AND\n REFERENCED_TABLE_NAME IS NOT NULL", [$this->name(), $data['table'], $columns], null, false, 'assoc_uc') as $relation) { $foreign[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_NAME']; $foreign[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] = $relation['REFERENCED_COLUMN_NAME']; $usedcol[] = $relation['COLUMN_NAME']; } } if (count($foreign) === 1 && !count(array_diff($columns, $usedcol))) { $foreign = current($foreign); $relname = $foreign['table']; $cntr = 1; while ($definition->hasRelation($relname) || $definition->getName() == $relname) { $relname = $foreign['table'] . '_' . ++$cntr; } $definition->addRelation($relname, ['name' => $relname, 'table' => $this->definition($foreign['table'], true, $lowerCase), 'keymap' => $data['keymap'], 'many' => true, 'pivot' => $rtable, 'pivot_keymap' => $foreign['keymap'], 'sql' => null, 'par' => []]); } else { $relname = $data['table']; $cntr = 1; while ($definition->hasRelation($relname) || $definition->getName() == $relname) { $relname = $data['table'] . '_' . ++$cntr; } $definition->addRelation($relname, ['name' => $relname, 'table' => $this->definition($data['table'], true, $lowerCase), 'keymap' => $data['keymap'], 'many' => true, 'pivot' => null, 'pivot_keymap' => [], 'sql' => null, 'par' => []]); } } // relations where the current table references another table // assuming current table is linked to "one" record in the referenced table // resulting in a "belongsTo" relationship $relations = []; foreach ($this->all("SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME\n FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND REFERENCED_TABLE_NAME IS NOT NULL", [$this->name(), $table], null, false, 'assoc_uc') as $relation) { $relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_NAME']; $relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] = $relation['REFERENCED_COLUMN_NAME']; } foreach ($relations as $name => $data) { $relname = $data['table']; $cntr = 1; while ($definition->hasRelation($relname) || $definition->getName() == $relname) { $relname = $data['table'] . '_' . ++$cntr; } $definition->addRelation($relname, ['name' => $relname, 'table' => $this->definition($data['table'], true, $lowerCase), 'keymap' => $data['keymap'], 'many' => false, 'pivot' => null, 'pivot_keymap' => [], 'sql' => null, 'par' => []]); } break; case 'oracle': // relations where the current table is referenced // assuming current table is on the "one" end having "many" records in the referencing table // resulting in a "hasMany" or "manyToMany" relationship (if a pivot table is detected) $relations = []; foreach ($this->all("SELECT ac.TABLE_NAME, ac.CONSTRAINT_NAME, cc.COLUMN_NAME, cc.POSITION\n FROM all_constraints ac\n LEFT JOIN all_cons_columns cc ON cc.OWNER = ac.OWNER AND cc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME\n WHERE ac.OWNER = ? AND ac.R_OWNER = ? AND ac.R_CONSTRAINT_NAME = ? AND ac.CONSTRAINT_TYPE = ?\n ORDER BY cc.POSITION", [$owner, $owner, $pkname, 'R'], null, false, 'assoc_uc') as $k => $relation) { $relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['TABLE_NAME']; $relations[$relation['CONSTRAINT_NAME']]['keymap'][$primary[(int) $relation['POSITION'] - 1]] = $relation['COLUMN_NAME']; } foreach ($relations as $data) { $rtable = $this->definition($data['table'], true, $lowerCase); // ?? $this->addTableByName($data['table'], false); $columns = []; foreach ($rtable->getColumns() as $column) { if (!in_array($column, $data['keymap'])) { $columns[] = $column; } } $foreign = []; $usedcol = []; if (count($columns)) { foreach ($this->all("SELECT\n cc.COLUMN_NAME, ac.CONSTRAINT_NAME, rc.TABLE_NAME AS REFERENCED_TABLE_NAME, ac.R_CONSTRAINT_NAME\n FROM all_constraints ac\n JOIN all_constraints rc ON rc.CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME AND rc.OWNER = ac.OWNER\n LEFT JOIN all_cons_columns cc ON cc.OWNER = ac.OWNER AND cc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME\n WHERE\n ac.OWNER = ? AND ac.R_OWNER = ? AND ac.TABLE_NAME = ? AND ac.CONSTRAINT_TYPE = ? AND \n cc.COLUMN_NAME IN (??)\n ORDER BY POSITION", [$owner, $owner, $data['table'], 'R', $columns], null, false, 'assoc_uc') as $k => $relation) { $foreign[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_NAME']; $foreign[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] = $relation['R_CONSTRAINT_NAME']; $usedcol[] = $relation['COLUMN_NAME']; } } if (count($foreign) === 1 && !count(array_diff($columns, $usedcol))) { $foreign = current($foreign); $rcolumns = $this->all("SELECT COLUMN_NAME FROM all_cons_columns WHERE OWNER = ? AND CONSTRAINT_NAME = ? ORDER BY POSITION", [$owner, current($foreign['keymap'])], null, false, 'assoc_uc'); foreach ($foreign['keymap'] as $column => $related) { $foreign['keymap'][$column] = array_shift($rcolumns); } $relname = $foreign['table']; $cntr = 1; while ($definition->hasRelation($relname) || $definition->getName() == $relname) { $relname = $foreign['table'] . '_' . ++$cntr; } $definition->addRelation($relname, ['name' => $relname, 'table' => $this->definition($foreign['table'], true, $lowerCase), 'keymap' => $data['keymap'], 'many' => true, 'pivot' => $rtable, 'pivot_keymap' => $foreign['keymap'], 'sql' => null, 'par' => []]); } else { $relname = $data['table']; $cntr = 1; while ($definition->hasRelation($relname) || $definition->getName() == $relname) { $relname = $data['table'] . '_' . ++$cntr; } $definition->addRelation($relname, ['name' => $relname, 'table' => $this->definition($data['table'], true, $lowerCase), 'keymap' => $data['keymap'], 'many' => true, 'pivot' => null, 'pivot_keymap' => [], 'sql' => null, 'par' => []]); } } // relations where the current table references another table // assuming current table is linked to "one" record in the referenced table // resulting in a "belongsTo" relationship $relations = []; foreach ($this->all("SELECT ac.CONSTRAINT_NAME, cc.COLUMN_NAME, rc.TABLE_NAME AS REFERENCED_TABLE_NAME, ac.R_CONSTRAINT_NAME\n FROM all_constraints ac\n JOIN all_constraints rc ON rc.CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME AND rc.OWNER = ac.OWNER\n LEFT JOIN all_cons_columns cc ON cc.OWNER = ac.OWNER AND cc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME\n WHERE ac.OWNER = ? AND ac.R_OWNER = ? AND ac.TABLE_NAME = ? AND ac.CONSTRAINT_TYPE = ?\n ORDER BY cc.POSITION", [$owner, $owner, strtoupper($table), 'R'], null, false, 'assoc_uc') as $relation) { $relations[$relation['CONSTRAINT_NAME']]['table'] = $relation['REFERENCED_TABLE_NAME']; $relations[$relation['CONSTRAINT_NAME']]['keymap'][$relation['COLUMN_NAME']] = $relation['R_CONSTRAINT_NAME']; } foreach ($relations as $name => $data) { $rcolumns = $this->all("SELECT COLUMN_NAME FROM all_cons_columns WHERE OWNER = ? AND CONSTRAINT_NAME = ? ORDER BY POSITION", [$owner, current($data['keymap'])], null, false, 'assoc_uc'); foreach ($data['keymap'] as $column => $related) { $data['keymap'][$column] = array_shift($rcolumns); } $relname = $data['table']; $cntr = 1; while ($definition->hasRelation($relname) || $definition->getName() == $relname) { $relname = $data['table'] . '_' . ++$cntr; } $definition->addRelation($relname, ['name' => $relname, 'table' => $this->definition($data['table'], true, $lowerCase), 'keymap' => $data['keymap'], 'many' => false, 'pivot' => null, 'pivot_keymap' => [], 'sql' => null, 'par' => []]); } break; default: // throw new DatabaseException('Relations discovery is not supported: '.$this->driver(), 500); break; } } if ($lowerCase) { $definition->toLowerCase(); } return $definition; }