Returns an array with fields referenced by a Foreign key

@param string $database
@param string $tblName
@param string|array $oCol Only column(s) considered
@return array
168282
private function getForeignKeysToArray($database, $tblName, $oCol = '')
    {
        if (!isset($this->advCache['tableFKs'][$database][$tblName])) {
            $this->setTableForeignKeyCache($database, $this->fixTableSource($tblName));
        }
        $aRt = [];
        if (isset($this->advCache['tableFKs'][$database][$tblName])) {
            $cnm = ['COLUMN_NAME', 'full_array_key_numbered', 'REFERENCED_TABLE_SCHEMA', 'REFERENCED_TABLE_NAME'];
            foreach ($this->advCache['tableFKs'][$database][$tblName] as $val) {
                if ($val[$cnm[0]] == $oCol) {
                    $vlQ        = array_merge($val, ['LIMIT' => 2]);
                    $tFd        = $this->setMySQLquery2Server($this->getForeignKeysQuery($vlQ), $cnm[1])['result'];
                    $tgtFld     = '`' . ($tFd[0][$cnm[0]] == $val[$cnm[0]] ? $tFd[1][$cnm[0]] : $tFd[0][$cnm[0]]) . '`';
                    $aRt[$oCol] = [$this->glueDbTb($val[$cnm[2]], $val[$cnm[3]]), $val[$cnm[2]], $tgtFld];
                }
            }
        }
        return $aRt;
    }
793726
protected function getTableFields( $_table ) {

        $_sql = "SHOW COLUMNS FROM {$_table}";
        $_ret = $this->execute( $_sql );
        $_fields = array();
        if ( $_ret != false ) {
            while ( ($_rows = $_ret->fetch()) != false ) {
                $_fields[] = $_rows[0];
            }
        }
        return $_fields;
    }
1192346
protected function getForeignKeys($tableName)
    {
        $foreignKeys = [];
        $rows = $this->fetchAll(sprintf(
            "SELECT
              CONSTRAINT_NAME,
              TABLE_NAME,
              COLUMN_NAME,
              REFERENCED_TABLE_NAME,
              REFERENCED_COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE REFERENCED_TABLE_SCHEMA = DATABASE()
              AND REFERENCED_TABLE_NAME IS NOT NULL
              AND TABLE_NAME = '%s'
            ORDER BY POSITION_IN_UNIQUE_CONSTRAINT",
            $tableName
        ));
        foreach ($rows as $row) {
            $foreignKeys[$row['CONSTRAINT_NAME']]['table'] = $row['TABLE_NAME'];
            $foreignKeys[$row['CONSTRAINT_NAME']]['columns'][] = $row['COLUMN_NAME'];
            $foreignKeys[$row['CONSTRAINT_NAME']]['referenced_table'] = $row['REFERENCED_TABLE_NAME'];
            $foreignKeys[$row['CONSTRAINT_NAME']]['referenced_columns'][] = $row['REFERENCED_COLUMN_NAME'];
        }

        return $foreignKeys;
    }
265786
public static function getFieldsWithAllData($tbl): array
    {
        if (isset(self::$_cached_tbl_fields[$tbl])) {
            return self::$_cached_tbl_fields[$tbl];
        }

        return self::$_cached_tbl_fields = self::q_assoc_id("SHOW FIELDS FROM `$tbl`");
    }
524223
public function getFieldMappingByDbFieldName(string $dbFieldName) : array
    {
        foreach ($this->fieldMappings as $mapping) {
            if ($mapping['name'] === $dbFieldName) {
                return $mapping;
            }
        }

        throw MappingException::mappingNotFoundByDbName($this->name, $dbFieldName);
    }
286981
public function getColumnAttributes($record, $column)
    {
        if (!$this->columnDispatch) {
            $this->buildColumnDispatch();
        }

        if (!empty($this->columnDispatch[$column])) {
            $attributes = array();

            foreach ($this->columnDispatch[$column] as $handler) {
                /**
                 * @var GridField_ColumnProvider $handler
                 */
                $columnAttributes = $handler->getColumnAttributes($this, $record, $column);

                if (is_array($columnAttributes)) {
                    $attributes = array_merge($attributes, $columnAttributes);
                    continue;
                }

                throw new LogicException(sprintf(
                    'Non-array response from %s::getColumnAttributes().',
                    get_class($handler)
                ));
            }

            return $attributes;
        }

        throw new InvalidArgumentException(sprintf(
            'Bad column "%s"',
            $column
        ));
    }
160215
public function getForeignPrimaryKeys()
    {
        $lfmap = $this->getLocalForeignMapping();
        $foreignTable = $this->getForeignTable();

        $foreignPKCols = [];
        foreach ($foreignTable->getPrimaryKey() as $fPKCol) {
            $foreignPKCols[$fPKCol->getName()] = true;
        }

        $foreignCols = [];
        foreach ($this->getLocalColumn() as $colName) {
            if ($foreignPKCols[$lfmap[$colName]]) {
                $foreignCols[] = $foreignTable->getColumn($lfmap[$colName]);
            }
        }

        return $foreignCols;
    }
963010
public function loadColumns($tbl)
    {
        $this->db->sql('SHOW FIELDS FROM `' . $tbl . '`');
        $result = $this->db->all();
        
        $columns = [];
        
        foreach ($result as $row) {
            
            $name = $row['Field'];
            $null = $row['Null'] != 'YES' ? false : true;
            $default = isset($row['Default']) ? $row['Default'] : null;
            $unsigned = false;
            
            // Is column auto increment?
            $auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false;
            
            // Size of field?
            if (preg_match('~(.+?)\s*\((\d+)\)(?:(?:\s*)?(unsigned))?~i', $row['Type'], $matches) === 1) {
                
                $type = $matches[1];
                $size = $matches[2];
                
                if (!empty($matches[3]) && $matches[3] == 'unsigned') {
                    $unsigned = true;
                }
            }
            else {
                $type = $row['Type'];
                $size = null;
            }
            
            $this->addColumn($name, $null, $default, $type, $size, $auto, $unsigned);
        }
    }
342548
public function getInverseForeignKeys($tableName): array {
		
		if (!isset($this->definitions[$tableName]['inverseForeignKeys'])) {
			
			// old-style join because of speedness
			$query =
				'SELECT k.CONSTRAINT_NAME, k.REFERENCED_COLUMN_NAME, k.TABLE_NAME, ' .
				' k.COLUMN_NAME, r.UPDATE_RULE, r.DELETE_RULE' .
				' FROM information_schema.`KEY_COLUMN_USAGE` AS k' .
				' JOIN information_schema.`REFERENTIAL_CONSTRAINTS` AS r' .
				' WHERE k.CONSTRAINT_NAME != "PRIMARY"' .
				' AND k.TABLE_SCHEMA = ? AND k.REFERENCED_TABLE_NAME = ?' .
				' AND r.CONSTRAINT_SCHEMA = ? AND r.REFERENCED_TABLE_NAME = ?' .
				' AND k.CONSTRAINT_NAME = r.CONSTRAINT_NAME';

			$this->definitions[$tableName]['inverseForeignKeys'] = self::load($query, [DB_NAME, $tableName, DB_NAME, $tableName], PAIR_DB_OBJECT_LIST);
			
		}
		
		return $this->definitions[$tableName]['inverseForeignKeys'];

	}
1350877
protected function getForeignKeyAttributesFromNonLoadedRelation(string $relation): array
    {
        $keys = $this->entityMap->getLocalKeys($relation);

        // We'll treat single and composite keys (polymorphic) the same way.
        if (!is_array($keys)) {
            $keys = [$keys];
        }

        $foreignKey = [];

        foreach ($keys as $key) {
            $foreignKey[$key] = $this->getEntityAttribute($key);
        }

        return $foreignKey;
    }
597615
protected function getForeignKeySqlDefinition(ForeignKey $foreignKey)
    {
        $def = '';
        if ($foreignKey->getConstraint()) {
            $def .= ' CONSTRAINT ' . $this->quoteColumnName($foreignKey->getConstraint());
        } else {
            $columnNames = [];
            foreach ($foreignKey->getColumns() as $column) {
                $columnNames[] = $this->quoteColumnName($column);
            }
            $def .= ' FOREIGN KEY (' . implode(',', $columnNames) . ')';
            $refColumnNames = [];
            foreach ($foreignKey->getReferencedColumns() as $column) {
                $refColumnNames[] = $this->quoteColumnName($column);
            }
            $def .= ' REFERENCES ' . $this->quoteTableName($foreignKey->getReferencedTable()->getName()) . ' (' . implode(',', $refColumnNames) . ')';
            if ($foreignKey->getOnDelete()) {
                $def .= ' ON DELETE ' . $foreignKey->getOnDelete();
            }
            if ($foreignKey->getOnUpdate()) {
                $def .= ' ON UPDATE ' . $foreignKey->getOnUpdate();
            }
        }

        return $def;
    }
1211436
function GetFields($table)
    {
        $fields = array();
        $sql = new Sql\Builder($this);
        $tbl = $sql->Table('information_schema.columns', array('TABLE_SCHEMA', 'TABLE_NAME', 'COLUMN_NAME'));
        $cond = $sql->Equals($tbl->Field('TABLE_SCHEMA'), $sql->Value($this->dbName))
                ->And_($sql->Equals($tbl->Field('TABLE_NAME'), $sql->Value($table)));
        $what = $sql->SelectList($tbl->Field('COLUMN_NAME'));
        $select = $sql->Select(true, $what, $tbl, $cond);
        $reader = $this->ExecuteQuery((string) $select);
        while ($reader->Read())
        {
            $fields[] = $reader->ByIndex(0);
        }
        return $fields;
    }
154641
public function getForeignKeysReferencingTable($tablename)
    {
        $matches = array();
        $keys = $this->getForeignKeys();
        foreach ($keys as $fk) {
            if ($fk->getForeignTableName() === $tablename) {
                $matches[] = $fk;
            }
        }

        return $matches;
    }
983989
protected function addForeignKeys(Table $table, $oid)
    {
        $database = $table->getDatabase();
        $stmt = $this->dbh->prepare("SELECT
            conname,
            confupdtype,
            confdeltype,
            CASE nl.nspname WHEN 'public' THEN cl.relname ELSE nl.nspname||'.'||cl.relname END as fktab,
                array_agg(DISTINCT a2.attname) AS fkcols,
                CASE nr.nspname WHEN 'public' THEN cr.relname ELSE nr.nspname||'.'||cr.relname END as reftab,
                    array_agg(DISTINCT a1.attname) AS refcols
                    FROM pg_constraint ct
                    JOIN pg_class cl ON cl.oid=conrelid
                    JOIN pg_class cr ON cr.oid=confrelid
                    JOIN pg_namespace nl ON nl.oid = cl.relnamespace
                    JOIN pg_namespace nr ON nr.oid = cr.relnamespace
                    LEFT JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = ct.confrelid
                    LEFT JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = ct.conrelid
                    WHERE
                    contype='f'
                    AND conrelid = ?
                    AND a2.attnum = ANY (ct.conkey)
                    AND a1.attnum = ANY (ct.confkey)
                    GROUP BY conname, confupdtype, confdeltype, fktab, reftab
                    ORDER BY conname");
        $stmt->bindValue(1, $oid);
        $stmt->execute();

        $foreignKeys = [];
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {

            $name = $row['conname'];
            $localTable = $row['fktab'];
            $localColumns = explode(',', trim($row['fkcols'], '{}'));
            $foreignTableName = $row['reftab'];
            $foreignColumns = explode(',', trim($row['refcols'], '{}'));

            // On Update
            switch ($row['confupdtype']) {
                case 'c':
                    $onupdate = ForeignKey::CASCADE;
                    break;
                case 'd':
                    $onupdate = ForeignKey::SETDEFAULT;
                    break;
                case 'n':
                    $onupdate = ForeignKey::SETNULL;
                    break;
                case 'r':
                    $onupdate = ForeignKey::RESTRICT;
                    break;
                default:
                case 'a':
                    // NOACTION is the postgresql default
                    $onupdate = ForeignKey::NONE;
                    break;
            }
            // On Delete
            switch ($row['confdeltype']) {
                case 'c':
                    $ondelete = ForeignKey::CASCADE;
                    break;
                case 'd':
                    $ondelete = ForeignKey::SETDEFAULT;
                    break;
                case 'n':
                    $ondelete = ForeignKey::SETNULL;
                    break;
                case 'r':
                    $ondelete = ForeignKey::RESTRICT;
                    break;
                default:
                case 'a':
                    // NOACTION is the postgresql default
                    $ondelete = ForeignKey::NONE;
                    break;
            }

            $foreignTable = $database->getTable($foreignTableName);
            $localTable   = $database->getTable($localTable);

            if (!$foreignTable) {
                continue;
            }

            if (!isset($foreignKeys[$name])) {
                $fk = new ForeignKey($name);
                $fk->setForeignTableCommonName($foreignTable->getCommonName());
                if ($table->guessSchemaName() != $foreignTable->guessSchemaName()) {
                    $fk->setForeignSchemaName($foreignTable->getSchema());
                }
                $fk->setOnDelete($ondelete);
                $fk->setOnUpdate($onupdate);
                $table->addForeignKey($fk);
                $foreignKeys[$name] = $fk;
            }

            $max = count($localColumns);
            for ($i = 0; $i < $max; $i++) {
                $foreignKeys[$name]->addReference(
                    $localTable->getColumn($localColumns[$i]),
                    $foreignTable->getColumn($foreignColumns[$i])
                );
            }
        }
    }
104910
protected function getIndexes($tableName)
    {
        $parts = $this->getSchemaName($tableName);

        $indexes = [];
        $sql = sprintf(
            "SELECT
                i.relname AS index_name,
                a.attname AS column_name
            FROM
                pg_class t,
                pg_class i,
                pg_index ix,
                pg_attribute a,
                pg_namespace nsp
            WHERE
                t.oid = ix.indrelid
                AND i.oid = ix.indexrelid
                AND a.attrelid = t.oid
                AND a.attnum = ANY(ix.indkey)
                AND t.relnamespace = nsp.oid
                AND nsp.nspname = %s
                AND t.relkind = 'r'
                AND t.relname = %s
            ORDER BY
                t.relname,
                i.relname",
            $this->getConnection()->quote($parts['schema']),
            $this->getConnection()->quote($parts['table'])
        );
        $rows = $this->fetchAll($sql);
        foreach ($rows as $row) {
            if (!isset($indexes[$row['index_name']])) {
                $indexes[$row['index_name']] = ['columns' => []];
            }
            $indexes[$row['index_name']]['columns'][] = $row['column_name'];
        }

        return $indexes;
    }
467804
public static function tableForeignKeys($tableName)
    {
        $result = ['inner' => [], 'outer' => []];
        foreach (Yii::$app->db->schema->tableSchemas as $table) {
            $foreignKeys = static::findConstraints($table);
            /** @var TableSchema $table */
            if ($table->name == $tableName) {
                $result['inner'] = $foreignKeys;
                continue;
            }
            foreach ($foreignKeys as $foreignKey) {
                if ($foreignKey['ftable'] == $tableName) {
                    $result['outer'][] = $foreignKey;
                }
            }
        }
        return $result;
    }
567158
public static function getFromDB( )
    {
        $result = array();
        $objDB = \Database::getInstance( );

        $tables = $objDB->listTables( null, true );
        if( empty($tables) ) {
            return $result;
        }

        foreach( $tables as $table ) {
            $keys = array();
            $fields = $objDB->listFields($table, true);

            foreach( $fields as $field ) {
                $name = $field['name'];
                $field['name'] = '`' . $field['name'] . '`';

                if( in_array(strtolower($field['type']), array('text', 'tinytext', 'mediumtext', 'longtext')) && isset($fields[$name]) ) {
                    $keys[$name] = 'FULLTEXT ';
                }

                //--- Tabellenspalten definieren ---
                if( $field['type'] != 'index' ) {
                    unset($field['index']);

                    // Field type
                    if( isset($field['length']) && ($field['length'] != '') ) {
                        $field['type'] .= '(' . $field['length'] . ((isset($field['precision']) && $field['precision'] != '') ? ',' . $field['precision'] : '') . ')';

                        unset( $field['length'] );
                        unset( $field['precision'] );
                    }

                    // Variant collation
                    if( ($field['collation'] != '') && ($field['collation'] != $GLOBALS['TL_CONFIG']['dbCollation']) ) {
                        $field['collation'] = 'COLLATE ' . $field['collation'];
                    }
                    else {
                        unset( $field['collation'] );
                    }

                    // Default values
                    if( in_array(strtolower($field['type']), array('text', 'tinytext', 'mediumtext', 'longtext', 'blob', 'tinyblob', 'mediumblob', 'longblob')) || stristr($field['extra'], 'auto_increment') || ($field['default'] === null) || (strtolower($field['null']) == 'null') ) {
                        unset( $field['default'] );
                    }
                    // Date/time constants (see #5089)
                    else if( in_array(strtolower($field['default']), array('current_date', 'current_time', 'current_timestamp')) ) {
                        $field['default'] = "default " . $field['default'];
                    }
                    // Everything else
                    else {
                        $field['default'] = "default '" . $field['default'] . "'";
                    }

                    unset( $field['origtype'] );
                    $result[$table]['TABLE_FIELDS'][$name] = trim( implode( ' ', $field ) );
                }

                //--- Index-Einträge ---
                if( isset($field['index']) && !empty($field['index_fields']) ) {
                    $index_fields = '`' . implode( '`, `', $field['index_fields'] ) . '`';
                    $index_fields = str_replace(array('(', ')`'), array('`(', ')'), $index_fields );

                    switch( $field['index'] ) {
                        case 'UNIQUE':  if( $name == 'PRIMARY' ) {
                                            $result[$table]['TABLE_CREATE_DEFINITIONS'][$name] = 'PRIMARY KEY  ('.$index_fields.')';
                                        }
                                        else {
                                            $result[$table]['TABLE_CREATE_DEFINITIONS'][$name] = 'UNIQUE KEY `'.$name.'` ('.$index_fields.')';
                                        }
                                        break;

                        default:        $result[$table]['TABLE_CREATE_DEFINITIONS'][$name] = (isset($keys[$name]) ? $keys[$name] : '') . 'KEY `'.$name.'` ('.$index_fields.')';
                                        break;
                    }
                    unset( $field['index_fields'] );
                    unset( $field['index'] );
                }
            }
        }

        // Table status
        $objStatus = $objDB->execute( 'SHOW TABLE STATUS' );
        while( $zeile = $objStatus->fetchAssoc() ) {
            $result[$zeile['Name']]['TABLE_OPTIONS'] = ' ENGINE=' . $zeile['Engine'] . ' DEFAULT CHARSET=' . substr($zeile['Collation'], 0, strpos($zeile['Collation'],"_"));

            if( $zeile['Auto_increment'] != '' ) {
                $result[$zeile['Name']]['TABLE_OPTIONS'] .= ' AUTO_INCREMENT=' . $zeile['Auto_increment'];
            }
        }

        return $result;
    }