Ejemplo n.º 1
0
    /**
     * 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;
    }
Ejemplo n.º 2
0
    /**
     * 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;
    }
Ejemplo n.º 3
0
 /**
  * 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);
                         }
                     }
                 }
             }
         }
     }
 }
Ejemplo n.º 4
0
 /**
  * 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;
 }
Ejemplo n.º 5
0
 /**
  * @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];
 }
Ejemplo n.º 6
0
 /**
  * @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];
 }
Ejemplo n.º 7
0
 /**
  * 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) . ')';
 }
Ejemplo n.º 8
0
    /**
     * 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;
    }
Ejemplo n.º 9
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})");
                            }
                        }
                    }
                }
            }
        }
    }
Ejemplo n.º 10
0
    /**
     * 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})");
                            }
                        }
                    }
                }
            }
        }
    }
Ejemplo n.º 11
0
    /**
     * 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;
    }
Ejemplo n.º 12
0
 /**
  * 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']})");
                         }
                     }
                 }
             }
         }
     }
 }