inspectColumns function

Future<List<ColumnInfo>> inspectColumns(SqlDatabase<Backend> db, String table, { String? namespace, })

Reads storage types, nullability, defaults, and computed-column metadata.

Implementation

Future<List<ColumnInfo>> inspectColumns(
  SqlDatabase<Backend> db,
  String table, {
  String? namespace,
}) async {
  if (namespace != null && db.dialect != SqlDialect.postgres) {
    throw const OrmException(
      'SCHEMA.NAMESPACE',
      'Database schemas require PostgreSQL.',
    );
  }
  if (isMysqlFamily(db.dialect)) return mysqlColumns(db, table);
  if (db.dialect == SqlDialect.sqlite) {
    final rows = await db.execute(
      SqlCommand('PRAGMA table_xinfo(${quoteIdentifier(table)})'),
    );
    final indexes = await db.execute(
      SqlCommand('PRAGMA index_list(${quoteIdentifier(table)})'),
    );
    final rowidPrimaryKey = !indexes.rows.any((r) => r[3] == 'pk');
    final ddl = await db.execute(
      SqlCommand(
        "SELECT sql FROM main.sqlite_schema WHERE type = 'table' AND name = ?1",
        [table],
      ),
    );
    final sql = ddl.rows.firstOrNull?.first as String? ?? '';
    final checks = sqliteChecks(sql);
    final computed = sqliteComputedColumns(sql);
    final collations = {
      for (final c in sqliteColumnCollations(sql))
        sqliteName(c.column): c.collation,
    };
    final columns = <ColumnInfo>[];
    for (final row in rows.rows) {
      final name = row[1] as String;
      final type = (row[2] as String).toUpperCase();
      final collation = collations[sqliteName(name)] ?? 'BINARY';
      final digits =
          type == 'TEXT' && collation.toLowerCase() == 'orm_decimal_v1'
          ? sqliteDecimalDigits(name, checks)
          : null;
      columns.add(
        ColumnInfo(
          name: name,
          storageType: type,
          collation: collation,
          decimalPrecision: digits?.$1,
          decimalScale: digits?.$2,
          temporalPrecision:
              type == 'TEXT' && temporalCollationKind(collation) != null
              ? sqliteTemporalPrecision(
                  name,
                  temporalCollationKind(collation)!,
                  checks,
                )
              : null,
          nullable: row[3] == 0 && !(row[5] != 0 && rowidPrimaryKey),
          defaultSql: row[4] as String?,
          generated: (row[6] as int) > 0,
          computed: computed[sqliteName(name)],
          integerBits: type == 'INTEGER'
              ? sqliteIntegerBits(name, checks)
              : null,
        ),
      );
    }
    return columns;
  }

  final result = await db.execute(
    SqlCommand(
      '''
SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod),
       NOT a.attnotnull, pg_catalog.pg_get_expr(d.adbin, d.adrelid),
       a.attidentity <> '' OR a.attgenerated <> '', a.attgenerated::text
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE c.relname = \$1 AND n.nspname = coalesce(\$2::text, pg_catalog.current_schema()) AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum''',
      [table, namespace],
    ),
  );
  return [
    for (final row in result.rows)
      ColumnInfo(
        name: row[0] as String,
        storageType: _postgresStorageName(row[1] as String),
        temporalPrecision: _postgresTemporalPrecision(row[1] as String),
        nullable: row[2] as bool,
        defaultSql: row[5] == '' ? row[3] as String? : null,
        generated: row[4] as bool,
        computed: row[5] == ''
            ? null
            : ComputedColumn(
                row[3] as String,
                storage: row[5] == 's'
                    ? ComputedStorage.stored
                    : ComputedStorage.virtual,
              ),
        decimalPrecision: _postgresDecimalDigits(row[1] as String)?.$1,
        decimalScale: _postgresDecimalDigits(row[1] as String)?.$2,
        integerBits: switch ((row[1] as String).toUpperCase()) {
          'SMALLINT' => 16,
          'INTEGER' => 32,
          'BIGINT' => 64,
          _ => null,
        },
      ),
  ];
}