Final Class Yiisoft\Db\Pgsql\Schema

                protected function findColumns(TableSchemaInterface $table): bool
{
    $orIdentity = '';
    if (version_compare($this->db->getServerInfo()->getVersion(), '12.0', '>=')) {
        $orIdentity = 'OR a.attidentity != \'\'';
    }
    $sql = <<<SQL
    SELECT
        a.attname AS column_name,
        COALESCE(td.typname, tb.typname, t.typname) AS data_type,
        COALESCE(td.typtype, tb.typtype, t.typtype) AS type_type,
        (SELECT nspname FROM pg_namespace WHERE oid = COALESCE(td.typnamespace, tb.typnamespace, t.typnamespace)) AS type_scheme,
        a.attlen AS character_maximum_length,
        pg_catalog.col_description(c.oid, a.attnum) AS column_comment,
        NOT (a.attnotnull OR t.typnotnull) AS is_nullable,
        COALESCE(t.typdefault, pg_get_expr(ad.adbin, ad.adrelid)) AS column_default,
        COALESCE(pg_get_expr(ad.adbin, ad.adrelid) ~ 'nextval', false) $orIdentity AS is_autoinc,
        pg_get_serial_sequence(quote_ident(d.nspname) || '.' || quote_ident(c.relname), a.attname)
        AS sequence_name,
        CASE WHEN COALESCE(td.typtype, tb.typtype, t.typtype) = 'e'::char
            THEN array_to_string(
                (
                    SELECT array_agg(enumlabel)
                    FROM pg_enum
                    WHERE enumtypid = COALESCE(td.oid, tb.oid, a.atttypid)
                )::varchar[],
            ',')
            ELSE NULL
        END AS values,
        COALESCE(
            information_schema._pg_char_max_length(
                COALESCE(td.oid, tb.oid, a.atttypid),
                a.atttypmod
            ),
            information_schema._pg_datetime_precision(
                COALESCE(td.oid, tb.oid, a.atttypid),
                a.atttypmod
            ),
            CASE a.atttypmod
                WHEN -1 THEN null
                ELSE ((a.atttypmod - 4) >> 16) & 65535
            END
        ) AS size,
        information_schema._pg_numeric_scale(
            COALESCE(td.oid, tb.oid, a.atttypid),
            a.atttypmod
        ) AS scale,
        ct.contype,
        pg_get_constraintdef(chk.oid) AS "check",
        COALESCE(NULLIF(a.attndims, 0), NULLIF(t.typndims, 0), (t.typcategory='A')::int) AS dimension,
        co.collname AS collation,
        nco.nspname AS collation_schema
    FROM
        pg_class c
        LEFT JOIN pg_attribute a ON a.attrelid = c.oid
        LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
        LEFT JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_type tb ON (a.attndims > 0 OR t.typcategory='A') AND t.typelem > 0 AND t.typelem = tb.oid
            OR t.typbasetype > 0 AND t.typbasetype = tb.oid
        LEFT JOIN pg_type td ON t.typndims > 0 AND t.typbasetype > 0 AND tb.typelem = td.oid
        LEFT JOIN pg_namespace d ON d.oid = c.relnamespace
        LEFT JOIN pg_rewrite rw ON c.relkind = 'v' AND rw.ev_class = c.oid AND rw.rulename = '_RETURN'
        LEFT JOIN pg_constraint ct ON (ct.contype = 'p' OR ct.contype = 'u' AND cardinality(ct.conkey) = 1)
            AND (
                ct.conrelid = c.oid AND a.attnum = ANY (ct.conkey)
                OR rw.ev_action IS NOT NULL AND ct.conrelid != 0
                AND strpos(rw.ev_action, ':resorigtbl ' || ct.conrelid || ' ') > 0
                AND rw.ev_action ~ ('.* :resno ' || a.attnum || ' :resname \S+ :ressortgroupref \d+ :resorigtbl '
                    || ct.conrelid || ' :resorigcol (?:'
                    || replace(substr(ct.conkey::text, 2, length(ct.conkey::text) - 2), ',', '|') || ') .*')
            )
        LEFT JOIN pg_constraint chk ON (chk.contype = 'c' AND cardinality(chk.conkey) = 1)
            AND (
                chk.conrelid = c.oid AND a.attnum = ANY (chk.conkey)
                OR rw.ev_action IS NOT NULL AND chk.conrelid != 0
                AND strpos(rw.ev_action, ':resorigtbl ' || chk.conrelid || ' ') > 0
                AND rw.ev_action ~ ('.* :resno ' || a.attnum || ' :resname \S+ :ressortgroupref \d+ :resorigtbl '
                    || chk.conrelid || ' :resorigcol (?:'
                    || replace(substr(chk.conkey::text, 2, length(chk.conkey::text) - 2), ',', '|') || ') .*')
            )
        LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid)
            ON a.attcollation = co.oid AND (nco.nspname != 'pg_catalog' OR co.collname != 'default')
    WHERE
        a.attnum > 0 AND t.typname != '' AND NOT a.attisdropped
        AND c.relname = :tableName
        AND d.nspname = :schemaName
    ORDER BY
        a.attnum;
    SQL;
    $schemaName = $table->getSchemaName() ?: $this->defaultSchema;
    $tableName = $table->getName();
    $columns = $this->db->createCommand($sql, [
        ':schemaName' => $schemaName,
        ':tableName' => $tableName,
    ])->queryAll();
    if (empty($columns)) {
        return false;
    }
    /** @psalm-var ColumnArray $info */
    foreach ($columns as $info) {
        $info = array_change_key_case($info);
        $info['schema'] = $schemaName;
        $info['table'] = $tableName;
        /** @psalm-var ColumnArray $info */
        $column = $this->loadColumn($info);
        $table->column($info['column_name'], $column);
        if ($column instanceof SequenceColumnInterface
            && $column->isPrimaryKey()
            && $table->getSequenceName() === null
        ) {
            $table->sequenceName($column->getSequenceName());
        }
    }
    return true;
}

            

                protected function findConstraints(TableSchemaInterface $table): void
{
    $tableName = $this->resolveFullName($table->getName(), $table->getSchemaName());
    $table->checks(...$this->getTableMetadata($tableName, SchemaInterface::CHECKS));
    $table->foreignKeys(...$this->getTableMetadata($tableName, SchemaInterface::FOREIGN_KEYS));
    $table->indexes(...$this->getTableMetadata($tableName, SchemaInterface::INDEXES));
}

            

                protected function findSchemaNames(): array
{
    $sql = <<<SQL
    SELECT "ns"."nspname"
    FROM "pg_namespace" AS "ns"
    WHERE "ns"."nspname" != 'information_schema' AND "ns"."nspname" NOT LIKE 'pg_%'
    ORDER BY "ns"."nspname" ASC
    SQL;
    /** @var string[] */
    return $this->db->createCommand($sql)->queryColumn();
}

            

                protected function findTableComment(TableSchemaInterface $tableSchema): void
{
    $sql = <<<SQL
    SELECT obj_description(pc.oid, 'pg_class')
    FROM pg_catalog.pg_class pc
    INNER JOIN pg_namespace pn ON pc.relnamespace = pn.oid
    WHERE
    pc.relname=:tableName AND
    pn.nspname=:schemaName
    SQL;
    $comment = $this->db->createCommand($sql, [
        ':schemaName' => $tableSchema->getSchemaName() ?: $this->defaultSchema,
        ':tableName' => $tableSchema->getName(),
    ])->queryScalar();
    $tableSchema->comment(is_string($comment) ? $comment : null);
}

            

                protected function findTableNames(string $schema = ''): array
{
    if ($schema === '') {
        $schema = $this->defaultSchema;
    }
    $sql = <<<SQL
    SELECT c.relname AS table_name
    FROM pg_class c
    INNER JOIN pg_namespace ns ON ns.oid = c.relnamespace
    WHERE ns.nspname = :schemaName AND c.relkind IN ('r','v','m','f', 'p')
    ORDER BY c.relname
    SQL;
    /** @var string[] */
    return $this->db->createCommand($sql, [':schemaName' => $schema])->queryColumn();
}

            

                protected function findViewNames(string $schema = ''): array
{
    if ($schema === '') {
        $schema = $this->defaultSchema;
    }
    $sql = <<<SQL
    SELECT c.relname AS table_name
    FROM pg_class c
    INNER JOIN pg_namespace ns ON ns.oid = c.relnamespace
    WHERE ns.nspname = :schemaName AND (c.relkind = 'v' OR c.relkind = 'm')
    ORDER BY c.relname
    SQL;
    /** @var string[] */
    return $this->db->createCommand($sql, [':schemaName' => $schema])->queryColumn();
}

            

                protected function loadResultColumn(array $metadata): ?ColumnInterface
{
    if (empty($metadata['native_type'])) {
        return null;
    }
    $dbType = $metadata['native_type'];
    $columnInfo = ['source' => ColumnInfoSource::QUERY_RESULT];
    if (!empty($metadata['table'])) {
        $columnInfo['table'] = $metadata['table'];
        $columnInfo['name'] = $metadata['name'];
    } elseif (!empty($metadata['name'])) {
        $columnInfo['name'] = $metadata['name'];
    }
    if ($metadata['precision'] !== -1) {
        $columnInfo['size'] = match ($dbType) {
            'varchar', 'bpchar' => $metadata['precision'] - 4,
            'numeric' => (($metadata['precision'] - 4) >> 16) & 0xFFFF,
            'interval' => ($metadata['precision'] & 0xFFFF) === 0xFFFF ? 6 : $metadata['precision'] & 0xFFFF,
            default => $metadata['precision'],
        };
        if ($dbType === 'numeric') {
            $columnInfo['scale'] = ($metadata['precision'] - 4) & 0xFFFF;
        }
    }
    $isArray = $dbType[0] === '_';
    if ($isArray) {
        $dbType = substr($dbType, 1);
    }
    if ($metadata['pgsql:oid'] > 16000) {
        /** @var string[] $typeInfo */
        $typeInfo = $this->db->createCommand(
            <<<SQL
            SELECT
                ns.nspname AS schema,
                COALESCE(t2.typname, t.typname) AS typname,
                COALESCE(t2.typtype, t.typtype) AS typtype,
                CASE WHEN COALESCE(t2.typtype, t.typtype) = 'e'::char
                    THEN array_to_string(
                        (
                            SELECT array_agg(enumlabel)
                            FROM pg_enum
                            WHERE enumtypid = COALESCE(t2.oid, t.oid)
                        )::varchar[],
                    ',')
                    ELSE NULL
                END AS values
            FROM pg_type AS t
            LEFT JOIN pg_type AS t2 ON t.typcategory='A' AND t2.oid = t.typelem OR t.typbasetype > 0 AND t2.oid = t.typbasetype
            LEFT JOIN pg_namespace AS ns ON ns.oid = COALESCE(t2.typnamespace, t.typnamespace)
            WHERE t.oid = :oid
            SQL,
            [':oid' => $metadata['pgsql:oid']],
        )->queryOne();
        $dbType = $this->resolveFullName($typeInfo['typname'], $typeInfo['schema']);
        if ($typeInfo['typtype'] === 'c') {
            $structured = new TableSchema($typeInfo['typname'], $typeInfo['schema']);
            if ($this->findColumns($structured)) {
                $columnInfo['columns'] = $structured->getColumns();
            }
            $columnInfo['type'] = ColumnType::STRUCTURED;
        } elseif (!empty($typeInfo['values'])) {
            $columnInfo['values'] = explode(',', str_replace(["''"], ["'"], $typeInfo['values']));
        }
    }
    $columnFactory = $this->db->getColumnFactory();
    $column = $columnFactory->fromDbType($dbType, $columnInfo);
    if ($isArray) {
        $columnInfo['dbType'] = $dbType;
        $columnInfo['column'] = $column;
        return $columnFactory->fromType(ColumnType::ARRAY, $columnInfo);
    }
    return $column;
}

            

                protected function loadTableChecks(string $tableName): array
{
    /** @var Check[] */
    return $this->loadTableConstraints($tableName, self::CHECKS);
}

            

                protected function loadTableDefaultValues(string $tableName): array
{
    throw new NotSupportedException(__METHOD__ . ' is not supported by PostgreSQL.');
}

            

                protected function loadTableForeignKeys(string $tableName): array
{
    /** @var ForeignKey[] */
    return $this->loadTableConstraints($tableName, self::FOREIGN_KEYS);
}

            

                protected function loadTableIndexes(string $tableName): array
{
    $sql = <<<SQL
    SELECT
        "ic"."relname" AS "name",
        "ia"."attname" AS "column_name",
        "i"."indisunique" AS "is_unique",
        "i"."indisprimary" AS "is_primary_key"
    FROM "pg_class" AS "tc"
    INNER JOIN "pg_namespace" AS "tcns"
        ON "tcns"."oid" = "tc"."relnamespace"
    LEFT JOIN "pg_rewrite" AS "rw"
        ON "tc"."relkind" = 'v' AND "rw"."ev_class" = "tc"."oid" AND "rw"."rulename" = '_RETURN'
    INNER JOIN "pg_index" AS "i"
        ON "i"."indrelid" = "tc"."oid"
            OR "rw"."ev_action" IS NOT NULL
            AND strpos("rw"."ev_action", ':resorigtbl ' || "i"."indrelid" || ' :resorigcol ' || "i"."indkey"[0] || ' ') > 0
    INNER JOIN "pg_class" AS "ic"
        ON "ic"."oid" = "i"."indexrelid"
    INNER JOIN "pg_attribute" AS "ia"
        ON "ia"."attrelid" = "i"."indexrelid" AND "ia"."attnum" <= cardinality("i"."indoption")
    WHERE "tcns"."nspname" = :schemaName AND "tc"."relname" = :tableName
    ORDER BY "i"."indkey", "ia"."attnum" ASC
    SQL;
    $nameParts = $this->db->getQuoter()->getTableNameParts($tableName);
    $indexes = $this->db->createCommand($sql, [
        ':schemaName' => $nameParts['schemaName'] ?? $this->defaultSchema,
        ':tableName' => $nameParts['name'],
    ])->queryAll();
    /** @psalm-var list<array<string,mixed>> $indexes */
    $indexes = array_map(array_change_key_case(...), $indexes);
    $indexes = DbArrayHelper::arrange($indexes, ['name']);
    $result = [];
    /**
     * @var string $name
     * @psalm-var list<
     *   array{
     *     name: string,
     *     column_name: string,
     *     is_unique: bool,
     *     is_primary_key: bool
     *   }
     * > $index
     */
    foreach ($indexes as $name => $index) {
        $result[$name] = new Index(
            $name,
            array_column($index, 'column_name'),
            $index[0]['is_unique'],
            $index[0]['is_primary_key'],
        );
    }
    return $result;
}

            

                protected function loadTableSchema(string $name): ?TableSchemaInterface
{
    $table = new TableSchema(...$this->db->getQuoter()->getTableNameParts($name));
    if ($this->findColumns($table)) {
        $this->findTableComment($table);
        $this->findConstraints($table);
        return $table;
    }
    return null;
}

            

                protected function resolveFullName(string $name, string $schemaName = ''): string
{
    $quoter = $this->db->getQuoter();
    $rawName = $quoter->getRawTableName($name);
    return match ($schemaName) {
        '', 'pg_catalog', $this->defaultSchema => $rawName,
        default => $quoter->getRawTableName($schemaName) . ".$rawName",
    };
}