Example #1
0
 /**
  * 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;
 }