alterTable method

Future<void> alterTable(TableMigration migration)

Alter columns of an existing tabe.

Since sqlite does not provide a way to alter the type or constraint of an individual column, one needs to write a fairly complex migration procedure for this. alterTable will run the 12 step procedure recommended by sqlite.

The migration to run describes the transformation to apply to the table. The individual fields of the TableMigration class contain more information on the transformations supported at the moment. Drifts's documentation also contains more details and examples for common migrations that can be run with alterTable.

When deleting columns from a table, make sure to migrate tables that have a foreign key constraint on those columns first.

While this function will re-create affected indexes and triggers, it does not reliably handle views at the moment.

Implementation

Future<void> alterTable(TableMigration migration) async {
  final foreignKeysEnabled =
      (await database.customSelect('PRAGMA foreign_keys').getSingle())
          .read<bool>('foreign_keys');
  final legacyAlterTable =
      (await database.customSelect('PRAGMA legacy_alter_table').getSingle())
          .read<bool>('legacy_alter_table');

  if (foreignKeysEnabled) {
    await database.customStatement('PRAGMA foreign_keys = OFF;');
  }

  final table = migration.affectedTable;
  final tableName = table.actualTableName;

  await database.transaction(() async {
    // We will drop the original table later, which will also delete
    // associated triggers, indices and and views. We query sqlite_schema to
    // re-create those later.
    // We use the legacy sqlite_master table since the _schema rename happened
    // in a very recent version (3.33.0)
    final schemaQuery = await database.customSelect(
      'SELECT type, name, sql FROM sqlite_master WHERE tbl_name = ?;',
      variables: [Variable<String>(tableName)],
    ).get();

    final createAffected = <String>[];

    for (final row in schemaQuery) {
      final type = row.read<String>('type');
      final sql = row.readNullable<String>('sql');
      final name = row.read<String>('name');

      if (sql == null) {
        // These indexes are created by sqlite to enforce different kinds of
        // special constraints.
        // They do not have any SQL create statement as they are created
        // automatically by the constraints on the table.
        // They can not be re-created and need to be skipped.
        assert(name.startsWith('sqlite_autoindex'));
        continue;
      }

      switch (type) {
        case 'trigger':
        case 'view':
        case 'index':
          createAffected.add(sql);
          break;
      }
    }

    // Step 4: Create the new table in the desired format
    final temporaryName = 'tmp_for_copy_$tableName';
    final temporaryTable = table.createAlias(temporaryName);
    await createTable(temporaryTable);

    // Step 5: Transfer old content into the new table
    final context = _createContext(supportsVariables: true);
    final expressionsForSelect = <Expression>[];

    context.buffer.write('INSERT INTO $temporaryName (');
    var first = true;
    for (final column in table.$columns) {
      if (column.generatedAs != null) continue;

      final transformer = migration.columnTransformer[column];

      if (transformer != null || !migration.newColumns.contains(column)) {
        // New columns without a transformer have a default value, so we don't
        // include them in the column list of the insert.
        // Otherwise, we prefer to use the column transformer if set. If there
        // isn't a transformer, just copy the column from the old table,
        // without any transformation.
        final expression = migration.columnTransformer[column] ?? column;
        expressionsForSelect.add(expression);

        if (!first) context.buffer.write(', ');
        context.buffer.write(column.escapedNameFor(context.dialect));
        first = false;
      }
    }

    context.buffer.write(') SELECT ');
    first = true;
    for (final expr in expressionsForSelect) {
      if (!first) context.buffer.write(', ');
      expr.writeInto(context);
      first = false;
    }
    context.buffer.write(' FROM ${context.identifier(tableName)};');
    await _issueCustomQuery(context.sql, context.boundVariables);

    // Step 6: Drop the old table
    await _issueCustomQuery('DROP TABLE ${context.identifier(tableName)}');

    // This step is not mentioned in the documentation, but: If we use `ALTER`
    // on an inconsistent schema (and it is inconsistent right now because
    // we've just dropped the original table), we need to enable the legacy
    // option which skips the integrity check.
    // See also: https://sqlite.org/forum/forumpost/0e2390093fbb8fd6
    if (!legacyAlterTable) {
      await _issueCustomQuery('pragma legacy_alter_table = 1;');
    }

    // Step 7: Rename the new table to the old name
    await _issueCustomQuery(
        'ALTER TABLE ${context.identifier(temporaryName)} '
        'RENAME TO ${context.identifier(tableName)}');

    if (!legacyAlterTable) {
      await _issueCustomQuery('pragma legacy_alter_table = 0;');
    }

    // Step 8: Re-create associated indexes, triggers and views
    for (final stmt in createAffected) {
      await _issueCustomQuery(stmt);
    }

    // We don't currently check step 9 and 10, step 11 happens implicitly.
  });

  // Finally, re-enable foreign keys if they were enabled originally.
  if (foreignKeysEnabled) {
    await database.customStatement('PRAGMA foreign_keys = ON;');
  }
}