importSqlite method
Replace the contents of this database with the tables found in the
SQLite file at path. The file's CREATE TABLE statements are
re-executed against this engine, then rows are bulk-inserted. Indexes
stored in the file are recreated by re-executing their CREATE INDEX
statements (any indexes the local parser doesn't accept are skipped
with a warning row in the returned message).
Returns a human-readable summary string describing what was loaded.
Implementation
Future<String> importSqlite(String path) async {
final bytes = await File(path).readAsBytes();
// If the database is in WAL (or WAL2) mode, a `<path>-wal` and/or
// `<path>-wal2` companion may hold newer page versions. Pick the
// freshest one and overlay it transparently.
final walBytes = await _pickFreshWalBytes(path);
final f = walBytes != null
? SqliteFile.fromBytesWithWal(bytes, walBytes)
: SqliteFile.fromBytes(bytes);
_tables.clear();
_views.clear();
final schema = f.readSchema();
final tableSchemas = schema.where((s) => s.type == 'table').toList();
final indexSchemas = schema.where((s) => s.type == 'index').toList();
var tablesLoaded = 0;
var rowsLoaded = 0;
var indexesLoaded = 0;
final skipped = <String>[];
for (final ts in tableSchemas) {
if (ts.sql == null) continue;
// SQLite's internal sqlite_sequence is handled below; never try to
// re-execute its CREATE TABLE (the parser rejects sqlite_*).
if (ts.name == 'sqlite_sequence') continue;
// sqlite_stat1 is similarly synthesized below from raw rows.
if (ts.name == 'sqlite_stat1') continue;
// SQLite includes auto-created indexes for INTEGER PRIMARY KEY etc.
// with names like `sqlite_autoindex_*`; their entries live with the
// table B-tree, no separate root.
try {
await execute(ts.sql!);
} catch (_) {
skipped.add('table ${ts.name}');
continue;
}
final t = _tables[ts.name];
if (t == null) {
skipped.add('table ${ts.name}');
continue;
}
tablesLoaded++;
final pkIdx = t.columns.indexWhere(
(c) => c.primaryKey && c.type == DataType.integer,
);
// For WITHOUT ROWID tables, SQLite physically stores PK columns
// first in the record, then the remaining columns in declared
// order. Build a mapping from on-disk position back to declared
// position so we can restore the user's column order.
final isWor = f.isWithoutRowid(ts.name);
List<int>? onDiskToDeclared;
if (isWor) {
final pkCols = <int>[];
for (var i = 0; i < t.columns.length; i++) {
if (t.columns[i].primaryKey) pkCols.add(i);
}
// Table-level PRIMARY KEY constraint, if any (preserves order).
if (pkCols.isEmpty) {
for (final con in t.constraints) {
if (con is PrimaryKeyConstraint) {
for (final n in con.columns) {
final idx = t.columns.indexWhere(
(c) => c.name.toLowerCase() == n.toLowerCase(),
);
if (idx >= 0) pkCols.add(idx);
}
break;
}
}
}
// On-disk order = [pkCols..., other declared columns in order].
final pkSet = pkCols.toSet();
final onDisk = <int>[
...pkCols,
for (var i = 0; i < t.columns.length; i++)
if (!pkSet.contains(i)) i,
];
// Map: onDisk[k] = declared index. We want, for each declared
// index d, the on-disk index k such that onDisk[k] == d.
onDiskToDeclared = onDisk;
}
for (final row in f.readTable(ts.name)) {
var src = row.values;
if (onDiskToDeclared != null && src.length == t.columns.length) {
final remapped = List<Object?>.filled(t.columns.length, null);
for (var k = 0; k < src.length; k++) {
remapped[onDiskToDeclared[k]] = src[k];
}
src = remapped;
}
// Pad/truncate to column count and store values as-is.
final values = List<Object?>.from(src);
while (values.length < t.columns.length) {
values.add(null);
}
if (values.length > t.columns.length) {
values.removeRange(t.columns.length, values.length);
}
// SQLite's INTEGER PRIMARY KEY column is stored as NULL in the
// record (the rowid IS the value). Repair that on read.
// (WITHOUT ROWID tables don't have this trick — values are real.)
if (!isWor && pkIdx >= 0 && values[pkIdx] == null) {
values[pkIdx] = row.rowid;
}
t.rows.add(values);
rowsLoaded++;
}
_rebuildIndexes(t);
}
for (final ixs in indexSchemas) {
if (ixs.sql == null) continue;
if (ixs.name.startsWith('sqlite_autoindex_')) continue;
try {
await execute(ixs.sql!);
indexesLoaded++;
} catch (_) {
skipped.add('index ${ixs.name}');
}
}
// Restore AUTOINCREMENT counters from sqlite_sequence, if present.
final hasSeq = tableSchemas.any((s) => s.name == 'sqlite_sequence');
if (hasSeq) {
try {
for (final row in f.readTable('sqlite_sequence')) {
final vals = row.values;
if (vals.length < 2) continue;
final tname = vals[0]?.toString();
final seq = vals[1];
if (tname == null || seq is! int) continue;
final tt = _tables[tname];
if (tt == null) continue;
for (final c in tt.columns) {
if (c.autoIncrement) tt.autoInc[c.name] = seq;
}
}
} catch (_) {
// Non-fatal: leave counters at default.
}
}
// Restore ANALYZE planner stats from sqlite_stat1, if present.
final hasStat = tableSchemas.any((s) => s.name == 'sqlite_stat1');
if (hasStat) {
final stat = Table('sqlite_stat1', const [
ColumnDef('tbl', DataType.text),
ColumnDef('idx', DataType.text),
ColumnDef('stat', DataType.text),
]);
try {
for (final row in f.readTable('sqlite_stat1')) {
final vals = List<Object?>.from(row.values);
while (vals.length < 3) {
vals.add(null);
}
stat.rows.add(vals.sublist(0, 3));
}
} catch (_) {
// Non-fatal.
}
_tables['sqlite_stat1'] = stat;
// Repopulate the planner's _stats map from the loaded rows. SQLite
// emits per-index rows where `stat` is `<rowCount> <avgRowsPerKey>
// ...`; the first integer doubles as the table row count, and the
// second (when present) lets us recover the indexed column's
// distinct cardinality.
final tableCounts = <String, int>{};
for (final r in stat.rows) {
final tname = r[0]?.toString();
final statStr = r[2]?.toString();
if (tname == null || statStr == null) continue;
final n = int.tryParse(statStr.split(' ').first);
if (n == null) continue;
// Prefer the largest count seen across rows for this table.
final cur = tableCounts[tname] ?? 0;
if (n > cur) tableCounts[tname] = n;
}
tableCounts.forEach((tname, n) {
if (_tables[tname] != null) {
_stats[tname] = _TableStats(n, <String, int>{});
}
});
// Second pass: extract per-index distinct counts from the
// `<n> <avgRowsPerKey>` form on index-stat rows.
for (final r in stat.rows) {
final tname = r[0]?.toString();
final idxName = r[1]?.toString();
final statStr = r[2]?.toString();
if (tname == null || idxName == null || statStr == null) continue;
final parts = statStr.split(' ');
if (parts.length < 2) continue;
final n = int.tryParse(parts[0]);
final avg = int.tryParse(parts[1]);
if (n == null || avg == null || avg <= 0) continue;
final tbl = _tables[tname];
if (tbl == null) continue;
final def = tbl.indexDefs[idxName];
if (def == null) continue;
final distinct = (n / avg).ceil();
final ts = _stats.putIfAbsent(
tname,
() => _TableStats(n, <String, int>{}),
);
ts.distinctByColumn[def.column.toLowerCase()] = distinct;
}
}
final msg = StringBuffer(
'Loaded $tablesLoaded table(s), '
'$rowsLoaded row(s), $indexesLoaded index(es) from $path',
);
if (skipped.isNotEmpty) {
msg.write(' (skipped: ${skipped.join(", ")})');
}
return msg.toString();
}