toSqliteRebuildSql method

String toSqliteRebuildSql(TableDefinition targetTable)

Returns SQL for a full table rebuild when this migration requires it (column type/nullability changes or FK add/drop).

Implements SQLite's "Making Other Kinds Of Table Schema Changes" procedure. https://www.sqlite.org/lang_altertable.html#makingotherkindsofchanges

Implementation

String toSqliteRebuildSql(TableDefinition targetTable) {
  const newTablePrefix = 'new_';
  final newTableName = newTablePrefix + name;

  var out = '';

  // 1. Disable foreign key constraints (handled by the migration runner).
  // 2. Ensure the migration runs in a transaction (handled by the migration runner).
  // 3. No need to store indexes/triggers/views since we have the target definition.
  // 4. Create the new_<table_name> table in the target table format.
  out += targetTable.tableCreationToSql(
    tableNameOverride: newTableName,
    skipIndexes: true,
  );

  // 5. Transfer content from <table_name> into new_<table_name>.
  // Copy every target column that already existed on the old table: new
  // columns from this migration are omitted so SQLite fills them via DEFAULT
  // (or NULL) per INSERT rules.
  final addColumnNames = addColumns.map((c) => c.name).toSet();
  final copyColumns = targetTable.columns
      .where((c) => !addColumnNames.contains(c.name))
      .toList();
  if (copyColumns.isNotEmpty) {
    final colListNew = copyColumns.map((c) => '"${c.name}"').join(', ');
    final selectList = copyColumns
        .map((c) {
          final columnMigration = _getColumnMigrationByName(c.name);
          final sourceName = columnMigration?.columnName ?? c.name;

          return switch (columnMigration?.newType) {
            ColumnType.jsonb => 'jsonb("$sourceName")',
            ColumnType.json => 'json("$sourceName")',
            _ => '"$sourceName"',
          };
        })
        .join(', ');
    out +=
        'INSERT INTO "$newTableName" ($colListNew) '
        'SELECT $selectList FROM "$name";\n';
  }

  // 6. Drop the old table.
  out += 'DROP TABLE "$name";\n';

  // 7. Rename new_<table_name> to <table_name>.
  out += 'ALTER TABLE "$newTableName" RENAME TO "$name";\n';

  // 8. Recreate indexes from the target table definition. Triggers and views
  // are user-defined and must be handled by the user manually.
  for (var index in targetTable.indexes) {
    out += '\n';
    out += '-- Indexes\n';
    out += index.toSql(tableName: name);
  }

  // 9. Serverpod does not manager views, so no need to update related views.
  // 10. Verify foreign key constraints (handled by the migration runner).
  // 11. Commit the transaction (handled by the migration runner).
  // 12. Re-enable foreign keys. (handled by the migration runner)
  out += '\n';

  return out;
}