alterTable method

@experimental Future<void> alterTable(TableMigration migration)

Experimental utility method to alter columns of an existing table.

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

@experimental
Future<void> alterTable(TableMigration migration) async {
  final foreignKeysEnabled =
      (await _db.customSelect('PRAGMA foreign_keys').getSingle())
          .readBool('foreign_keys');

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

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

  await _db.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 _db.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.readString('type');
      final sql = row.read<String?>('sql');
      final name = row.readString('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();
    final expressionsForSelect = <Expression>[];

    context.buffer.write('INSERT INTO $temporaryName (');
    var first = true;
    for (final column in table.$columns) {
      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.escapedName);
        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 ${escapeIfNeeded(tableName)};');
    await _issueCustomQuery(context.sql, context.introducedVariables);

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

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

    // 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 _db.customStatement('PRAGMA foreign_keys = ON;');
  }
}