inspectTable function
Reads one physical table without changing its schema or data.
Implementation
Future<TableInfo> inspectTable(SqlDatabase<Backend> db, String table) async {
if (isMysqlFamily(db.dialect)) return mysqlTable(db, table);
final columns = await inspectColumns(db, table);
final primary = <String>[],
unique = <List<String>>[],
indexes = <IndexSchema>[];
final foreign = <ForeignKey>[], unmanaged = <CatalogObject>[];
final checks = <CheckInfo>[];
if (db.dialect == SqlDialect.sqlite) {
final info = await db.execute(
SqlCommand('PRAGMA table_xinfo(${quoteIdentifier(table)})'),
);
final primaryRows = info.rows.where((r) => (r[5] as int) > 0).toList()
..sort((a, b) => (a[5] as int).compareTo(b[5] as int));
primary.addAll(primaryRows.map((r) => r[1] as String));
final list = await db.execute(
SqlCommand('PRAGMA index_list(${quoteIdentifier(table)})'),
);
for (final row in list.rows) {
final name = row[1] as String;
final parts = await db.execute(
SqlCommand('PRAGMA index_xinfo(${quoteIdentifier(name)})'),
);
final keys = parts.rows.where((r) => r[5] == 1).toList();
final sql = await db.execute(
SqlCommand(
'SELECT sql FROM sqlite_schema WHERE type = \'index\' AND name = ?1',
[name],
),
);
final definition = sql.rows.firstOrNull?.first as String? ?? '';
if (row[4] == 1 ||
keys.any(
(r) =>
r[2] == null ||
r[3] != 0 ||
(r[4] as String).toLowerCase() !=
(columns.firstWhere((c) => c.name == r[2]).collation ??
'BINARY')
.toLowerCase(),
)) {
unmanaged.add(CatalogObject('index', name, definition));
continue;
}
final names = keys.map((r) => r[2] as String).toList();
if (row[3] == 'u') {
unique.add(names);
} else if (row[3] != 'pk') {
indexes.add(IndexSchema(name, names, unique: row[2] == 1));
}
}
final keys = await db.execute(
SqlCommand('PRAGMA foreign_key_list(${quoteIdentifier(table)})'),
);
final grouped = <int, List<List<Object?>>>{};
for (final row in keys.rows) {
(grouped[row[0] as int] ??= []).add(row);
}
for (final rows in grouped.values) {
rows.sort((a, b) => (a[1] as int).compareTo(b[1] as int));
if (rows.any((r) => r[4] == null) ||
rows.first[5] != 'NO ACTION' ||
rows.first[7] != 'NONE') {
unmanaged.add(
CatalogObject(
'foreign key',
'$table#${rows.first[0]}',
rows.toString(),
),
);
continue;
}
foreign.add(
ForeignKey(
rows.map((r) => r[3] as String).toList(),
rows.first[2] as String,
rows.map((r) => r[4] as String).toList(),
onDelete: rows.first[6] as String,
),
);
}
final objects = await db.execute(
SqlCommand(
"SELECT type, name, sql FROM sqlite_schema WHERE (tbl_name = ?1 AND type = 'trigger') OR type = 'view'",
[table],
),
);
for (final row in objects.rows) {
unmanaged.add(
CatalogObject(row[0] as String, row[1] as String, row[2] as String),
);
}
final ddl = await db.execute(
SqlCommand(
"SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = ?1",
[table],
),
);
final sql = ddl.rows.firstOrNull?.first as String?;
var withoutChecks = sql ?? '';
if (sql != null) {
// Storage checks retain their existing column-width/precision meaning.
final remaining = withoutIntegerChecks(sql, columns);
final parsed = sqliteChecks(remaining);
checks.addAll(parsed.map((c) => CheckInfo(c.name, c.expression)));
final text = StringBuffer();
var start = 0;
for (final check in parsed) {
text.write(remaining.substring(start, check.start));
start = check.end;
}
withoutChecks = (text..write(remaining.substring(start))).toString();
}
if (sql != null &&
sqlWords(
withoutStorageCollations(withoutComputed(withoutChecks), columns),
).any(
{
'CHECK',
'DEFERRABLE',
'COLLATE',
'STRICT',
'WITHOUT',
'AUTOINCREMENT',
'CONFLICT',
}.contains,
)) {
unmanaged.add(CatalogObject('table options', table, sql));
}
} else {
final constraints = await db.execute(
SqlCommand(
r'''
SELECT c.conname, c.contype::text,
ARRAY(SELECT a.attname::text FROM unnest(c.conkey) WITH ORDINALITY k(num, ord)
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.num ORDER BY k.ord),
t.relname,
ARRAY(SELECT a.attname::text FROM unnest(c.confkey) WITH ORDINALITY k(num, ord)
JOIN pg_attribute a ON a.attrelid = c.confrelid AND a.attnum = k.num ORDER BY k.ord),
c.confdeltype::text, c.confupdtype::text, c.confmatchtype::text,
c.condeferrable, c.convalidated, pg_get_constraintdef(c.oid), tn.nspname,
coalesce((to_jsonb(i)->>'indnullsnotdistinct')::boolean, false),
pg_get_expr(c.conbin, c.conrelid), c.connoinherit, c.conislocal, c.coninhcount,
coalesce((to_jsonb(c)->>'conenforced')::boolean, true)
FROM pg_constraint c JOIN pg_class r ON r.oid = c.conrelid
JOIN pg_namespace n ON n.oid = r.relnamespace
LEFT JOIN pg_class t ON t.oid = c.confrelid
LEFT JOIN pg_namespace tn ON tn.oid = t.relnamespace
LEFT JOIN pg_index i ON i.indexrelid = c.conindid
WHERE n.nspname = current_schema() AND r.relname = $1''',
[table],
),
);
final schemaName = (await db.execute(SqlCommand('SELECT current_schema()')))
.rows
.single
.single;
for (final row in constraints.rows) {
final kind = row[1] as String,
keys = (row[2] as List<Object?>).cast<String>();
if (kind == 'p') {
primary.addAll(keys);
if (row[8] == true || row[9] != true) {
unmanaged.add(
CatalogObject('constraint', row[0] as String, row[10] as String),
);
}
} else if (kind == 'u' &&
row[8] == false &&
row[9] == true &&
row[12] == false) {
unique.add(keys);
} else if (kind == 'f' &&
row[6] == 'a' &&
row[7] == 's' &&
row[8] == false &&
row[9] == true &&
row[11] == schemaName) {
foreign.add(
ForeignKey(
keys,
row[3] as String,
(row[4] as List<Object?>).cast<String>(),
onDelete: switch (row[5]) {
'a' => 'NO ACTION',
'r' => 'RESTRICT',
'c' => 'CASCADE',
'n' => 'SET NULL',
'd' => 'SET DEFAULT',
_ => throw StateError('Unknown FK action'),
},
),
);
} else if (kind == 'c' &&
row[9] == true &&
row[14] == false &&
row[15] == true &&
row[16] == 0 &&
row[17] == true) {
checks.add(CheckInfo(row[0] as String, row[13] as String));
} else if (kind != 'n') {
unmanaged.add(
CatalogObject('constraint', row[0] as String, row[10] as String),
);
}
}
final list = await db.execute(
SqlCommand(
r'''
SELECT ic.relname, i.indisunique, i.indisvalid AND i.indisready AND i.indislive,
ARRAY(SELECT a.attname::text FROM unnest(i.indkey) WITH ORDINALITY k(num, ord)
LEFT JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = k.num ORDER BY k.ord),
pg_get_indexdef(i.indexrelid), i.indexprs IS NULL AND i.indpred IS NULL
AND i.indnatts = i.indnkeyatts AND am.amname = 'btree'
AND ic.reloptions IS NULL
AND NOT coalesce((to_jsonb(i)->>'indnullsnotdistinct')::boolean, false)
AND NOT EXISTS(SELECT 1 FROM unnest(i.indoption) v WHERE v <> 0)
AND NOT EXISTS(SELECT 1 FROM unnest(i.indclass) v JOIN pg_opclass o ON o.oid = v WHERE NOT o.opcdefault)
AND NOT EXISTS(SELECT 1 FROM unnest(i.indkey, i.indcollation) k(num, collation_oid)
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.num WHERE k.collation_oid <> a.attcollation)
FROM pg_index i JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am am ON am.oid = ic.relam
WHERE n.nspname = current_schema() AND t.relname = $1
AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = i.indexrelid AND c.contype IN ('p', 'u', 'x'))''',
[table],
),
);
for (final row in list.rows) {
final keys = row[3] as List<Object?>;
if (row[2] != true || row[5] != true || keys.contains(null)) {
unmanaged.add(
CatalogObject('index', row[0] as String, row[4] as String),
);
} else {
indexes.add(
IndexSchema(
row[0] as String,
keys.cast<String>(),
unique: row[1] as bool,
),
);
}
}
final extras = await db.execute(
SqlCommand(
r'''
SELECT 'trigger', t.tgname, pg_get_triggerdef(t.oid)
FROM pg_trigger t JOIN pg_class c ON c.oid = t.tgrelid JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = $1 AND n.nspname = current_schema() AND NOT t.tgisinternal
UNION ALL SELECT 'policy', policyname,
jsonb_build_object('permissive', permissive, 'roles', roles, 'command', cmd,
'using', qual, 'withCheck', with_check)::text
FROM pg_policies WHERE schemaname = current_schema() AND tablename = $1
UNION ALL SELECT 'row_security', c.relname,
jsonb_build_object('enabled', c.relrowsecurity, 'forced', c.relforcerowsecurity)::text
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = $1 AND n.nspname = current_schema()
AND (c.relrowsecurity OR c.relforcerowsecurity
OR EXISTS (SELECT 1 FROM pg_policy p WHERE p.polrelid = c.oid))''',
[table],
),
);
for (final row in extras.rows) {
unmanaged.add(
CatalogObject(row[0] as String, row[1] as String, row[2] as String),
);
}
}
return TableInfo(
name: table,
columns: columns,
primaryKey: primary,
uniqueKeys: unique,
foreignKeys: foreign,
indexes: indexes,
unmanaged: unmanaged,
checks: List.unmodifiable(checks),
);
}