createIndex method
Build a new secondary index name over column columnName. Walks
every existing row, populates the index, commits everything, and
rewrites meta.json so the index survives reopen. Throws when
name is already taken or columnName is unknown.
Index names must match ^[A-Za-z0-9_]+$ so they can become a
safe filename suffix (<base>.idx_<name>).
Create a secondary index named name on columnNames (one or
more). The new index is built by backfilling every existing row.
Rejects: invalid name, duplicate, unknown column, the PK column,
or an empty/duplicate column list. NULLs in any indexed component
cause that row to be omitted from the index (SQL-ish semantics).
When unique is true, two rows with the same indexed-column
tuple are rejected (the build fails with a StateError and the
half-built index file is removed). NULL-containing tuples never
participate in the uniqueness check — matching SQLite semantics.
Implementation
Future<void> createIndex(String name, List<String> columnNames,
{bool unique = false}) async {
if (!RegExp(r'^[A-Za-z0-9_]+$').hasMatch(name)) {
throw ArgumentError.value(
name, 'name', 'index name must match [A-Za-z0-9_]+');
}
if (_secondary.containsKey(name)) {
throw StateError('PagedTable: index $name already exists');
}
if (columnNames.isEmpty) {
throw ArgumentError('PagedTable.createIndex: no columns');
}
final seen = <String>{};
final cols = <PagedColumn>[];
for (final cn in columnNames) {
final lower = cn.toLowerCase();
if (!seen.add(lower)) {
throw ArgumentError(
'PagedTable.createIndex: duplicate column $cn in index $name');
}
final col = columns.firstWhere(
(c) => c.name == cn,
orElse: () =>
throw ArgumentError.value(cn, 'columnName', 'no such column'),
);
if (cn == primaryKey.name) {
throw ArgumentError(
'PagedTable: column $cn is already the primary key');
}
cols.add(col);
}
final f = await PagedFile.open(
'$basePath.idx_$name',
pageSize: _heapFile.pageSize,
cacheCapacity: 8,
);
final b = await PagedBTree.open(f);
final si = _SecondaryIndex(
name: name,
columns: [for (final c in cols) c.name],
columnTypes: [for (final c in cols) c.type],
file: f,
btree: b,
unique: unique,
);
// Backfill: walk every existing row through the primary index and
// populate the new tree. Tuples containing any NULL are skipped.
// For UNIQUE indexes we additionally track the prefix bytes we've
// already inserted and reject a second occurrence.
final seenPrefixes = unique ? <String>{} : null;
try {
await for (final entry in _index.scan()) {
final bytes = await _heap.get(entry.value);
if (bytes == null) continue;
final row = _decodeRow(bytes);
final key = _encodeSecondaryKey(si, row, entry.key);
if (key == null) continue;
if (seenPrefixes != null) {
final prefix = _encodeSecondaryPrefix(si, row)!;
final tag = base64Encode(prefix);
if (!seenPrefixes.add(tag)) {
throw StateError(
'CREATE UNIQUE INDEX $name on ${cols.map((c) => c.name).join(", ")}: '
'duplicate value in existing rows');
}
}
await b.put(key, entry.value);
}
} catch (e) {
// Tear down the half-built file so the next open doesn't see a
// ghost index — meta.json hasn't been updated yet, so the file
// is "orphan" in the same sense as any failed createIndex.
await f.close();
for (final ext in const ['', '.journal']) {
final junk = File('$basePath.idx_$name$ext');
if (await junk.exists()) {
try {
await junk.delete();
} catch (_) {/* best-effort */}
}
}
rethrow;
}
await b.commit();
_secondary[name] = si;
// Persist the schema change *after* the backfilled index is on disk;
// a crash before this point leaves an orphan file the next open will
// ignore (since meta.json doesn't list it).
await _writeMeta(basePath, columns, primaryKeyIndex, _indexDescriptors(),
pageSize: _heapFile.pageSize);
}