SQLite3 Undo/Redo

undo.dart

import 'dart:typed_data';

import 'package:sqlite3/sqlite3.dart';

const _schema = '''
CREATE TABLE IF NOT EXISTS undo_stack(
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  changeset BLOB NOT NULL
);
CREATE TABLE IF NOT EXISTS redo_stack(
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  changeset BLOB NOT NULL
);
''';

class UndoRedo {
  final Database db;
  final Database target;
  final List<String> tables;

  UndoRedo(this.db, this.target, this.tables) {
    db.execute(_schema);
  }

  void exec(void Function() cb) {
    final session = Session(target);
    for (final tbl in tables) {
      session.attach(tbl);
    }
    cb();
    final changeset = session.changeset();
    final bytes = changeset.bytes;
    session.delete();

    if (bytes.isEmpty) return;

    db.execute('INSERT INTO undo_stack (changeset) VALUES (?)', [bytes]);
    // Clear the redo stack on new action
    db.execute('DELETE FROM redo_stack');
  }

  void undo() {
    final result = db.select('SELECT id, changeset FROM undo_stack ORDER BY id DESC LIMIT 1');
    if (result.isEmpty) return;

    final row = result.first;
    final id = row['id'] as int;
    final changesetBytes = row['changeset'] as List<int>;

    final changeset = Changeset.fromBytes(Uint8List.fromList(changesetBytes), sqlite3);
    changeset.invert().applyTo(target);

    // Move the undone changeset to the redo stack
    db.execute('INSERT INTO redo_stack (changeset) VALUES (?)', [changesetBytes]);
    db.execute('DELETE FROM undo_stack WHERE id = ?', [id]);
  }

  void redo() {
    final result = db.select('SELECT id, changeset FROM redo_stack ORDER BY id DESC LIMIT 1');
    if (result.isEmpty) return;

    final row = result.first;
    final id = row['id'] as int;
    final changesetBytes = row['changeset'] as List<int>;

    final changeset = Changeset.fromBytes(Uint8List.fromList(changesetBytes), sqlite3);
    changeset.applyTo(target);

    // Move the redone changeset back to the undo stack
    db.execute('INSERT INTO undo_stack (changeset) VALUES (?)', [changesetBytes]);
    db.execute('DELETE FROM redo_stack WHERE id = ?', [id]);
  }

  bool get canUndo => db.select('SELECT id FROM undo_stack').isNotEmpty;
  bool get canRedo => db.select('SELECT id FROM redo_stack').isNotEmpty;
}

extension on Changeset {
  Changeset invert() => -this;
}

undo_example.dart

import 'package:flutter/material.dart';
import 'package:sqlite3/sqlite3.dart' as sqlite;

import 'undo.dart';

const schema = '''
CREATE TABLE todos(
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0
);
''';

class UndoExample extends StatelessWidget {
  const UndoExample({super.key});

  @override
  Widget build(BuildContext context) {
    return const MyHomePage(title: 'Undo/Redo Demo');
  }
}

class MyHomePage extends StatefulWidget {
  const MyHomePage({super.key, required this.title});

  final String title;

  @override
  State<MyHomePage> createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  late final sqlite.Database db;
  late final UndoRedo undoRedo;

  sqlite.ResultSet _testData = sqlite.ResultSet([], [], []);

  @override
  void initState() {
    super.initState();
    final undoDb = sqlite.sqlite3.openInMemory();
    db = sqlite.sqlite3.openInMemory();
    db.execute(schema);
    undoRedo = UndoRedo(undoDb, db, ['todos']);
    _refreshState();
  }

  @override
  void dispose() {
    db.dispose();
    undoRedo.db.dispose();
    super.dispose();
  }

  void _refreshState() {
    setState(() {
      _testData = db.select('SELECT * FROM todos');
    });
  }

  void _addTodo() {
    final textController = TextEditingController();
    showDialog(
      context: context,
      builder:
          (context) => AlertDialog(
            title: const Text('Add new todo'),
            content: TextField(
              controller: textController,
              decoration: const InputDecoration(hintText: "Enter todo description"),
            ),
            actions: [
              TextButton(onPressed: () => Navigator.pop(context), child: const Text('Cancel')),
              TextButton(
                onPressed: () {
                  final content = textController.text;
                  if (content.isNotEmpty) {
                    undoRedo.exec(() {
                      db.execute('INSERT INTO todos (content) VALUES (?)', [content]);
                    });
                    _refreshState();
                  }
                  Navigator.pop(context);
                },
                child: const Text('Add'),
              ),
            ],
          ),
    );
  }

  void _toggleTodo(int id, bool completed) {
    undoRedo.exec(() {
      db.execute('UPDATE todos SET completed = ? WHERE id = ?', [completed ? 1 : 0, id]);
    });
    _refreshState();
  }

  void _deleteTodo(int id) {
    undoRedo.exec(() {
      db.execute('DELETE FROM todos WHERE id = ?', [id]);
    });
    _refreshState();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        backgroundColor: Theme.of(context).colorScheme.inversePrimary,
        title: Text(widget.title),
        actions: [
          IconButton(
            icon: const Icon(Icons.undo),
            onPressed: undoRedo.canUndo ? () {
              undoRedo.undo();
              _refreshState();
            } : null,
          ),
          IconButton(
            icon: const Icon(Icons.redo),
            onPressed: undoRedo.canRedo ? () {
              undoRedo.redo();
              _refreshState();
            } : null,
          ),
        ],
      ),
      body: _testData.isEmpty
          ? const Center(child: Text('No todos yet!'))
          : ListView.builder(
              itemCount: _testData.length,
              itemBuilder: (context, index) {
                final row = _testData[index];
                final id = row['id'] as int;
                final content = row['content'] as String;
                final completed = row['completed'] == 1;
                return ListTile(
                  leading: Checkbox(
                    value: completed,
                    onChanged: (value) {
                      if (value != null) {
                        _toggleTodo(id, value);
                      }
                    },
                  ),
                  title: Text(
                    content,
                    style: TextStyle(
                      decoration: completed ? TextDecoration.lineThrough : null,
                    ),
                  ),
                  trailing: IconButton(
                    icon: const Icon(Icons.delete),
                    onPressed: () => _deleteTodo(id),
                  ),
                );
              },
            ),
      floatingActionButton: FloatingActionButton(
        onPressed: _addTodo,
        tooltip: 'Add Todo',
        child: const Icon(Icons.add),
      ),
    );
  }
}

undo_test.dart

import 'package:flutter_test/flutter_test.dart';
import 'package:sqlite3/sqlite3.dart';

import 'undo.dart';

const schema = '''
CREATE TABLE todos(
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0
);
''';

void main() {
  group('UndoRedo with Todos', () {
    late Database db;
    late Database target;
    late UndoRedo undoRedo;

    setUp(() {
      db = sqlite3.openInMemory();
      target = sqlite3.openInMemory();
      target.execute(schema);
      undoRedo = UndoRedo(db, target, ['todos']);
    });

    tearDown(() {
      db.dispose();
      target.dispose();
    });

    test('initial state', () {
      expect(undoRedo.canUndo, isFalse);
      expect(undoRedo.canRedo, isFalse);
      final data = target.select('SELECT * FROM todos');
      expect(data.isEmpty, isTrue);
    });

    test('add todo and undo/redo', () {
      // Add a todo
      undoRedo.exec(() {
        target.execute("INSERT INTO todos (content) VALUES ('buy milk')");
      });
      var data = target.select('SELECT * FROM todos');
      expect(data.length, 1);
      expect(data.first['content'], 'buy milk');
      expect(undoRedo.canUndo, isTrue);

      // Undo adding the todo
      undoRedo.undo();
      data = target.select('SELECT * FROM todos');
      expect(data.isEmpty, isTrue);
      expect(undoRedo.canUndo, isFalse);
      expect(undoRedo.canRedo, isTrue);

      // Redo adding the todo
      undoRedo.redo();
      data = target.select('SELECT * FROM todos');
      expect(data.length, 1);
      expect(data.first['content'], 'buy milk');
      expect(undoRedo.canUndo, isTrue);
      expect(undoRedo.canRedo, isFalse);
    });

    test('toggle todo and undo/redo', () {
      // Add a todo
      target.execute("INSERT INTO todos (id, content) VALUES (1, 'buy milk')");

      // Toggle todo to completed
      undoRedo.exec(() {
        target.execute('UPDATE todos SET completed = 1 WHERE id = 1');
      });
      var data = target.select('SELECT * FROM todos');
      expect(data.first['completed'], 1);

      // Undo toggle
      undoRedo.undo();
      data = target.select('SELECT * FROM todos');
      expect(data.first['completed'], 0);

      // Redo toggle
      undoRedo.redo();
      data = target.select('SELECT * FROM todos');
      expect(data.first['completed'], 1);
    });

    test('delete todo and undo/redo', () {
      // Add a todo
      target.execute("INSERT INTO todos (id, content) VALUES (1, 'buy milk')");

      // Delete the todo
      undoRedo.exec(() {
        target.execute('DELETE FROM todos WHERE id = 1');
      });
      var data = target.select('SELECT * FROM todos');
      expect(data.isEmpty, isTrue);

      // Undo delete
      undoRedo.undo();
      data = target.select('SELECT * FROM todos');
      expect(data.length, 1);

      // Redo delete
      undoRedo.redo();
      data = target.select('SELECT * FROM todos');
      expect(data.isEmpty, isTrue);
    });

    test('new action clears redo stack', () {
      // Add a todo
      undoRedo.exec(() {
        target.execute("INSERT INTO todos (content) VALUES ('action 1')");
      });
      
      // Undo the action
      undoRedo.undo();
      expect(undoRedo.canRedo, isTrue);

      // Perform a new action
      undoRedo.exec(() {
        target.execute("INSERT INTO todos (content) VALUES ('action 2')");
      });

      // Verify redo stack is cleared
      expect(undoRedo.canRedo, isFalse);
      final data = target.select('SELECT * FROM todos');
      expect(data.length, 1);
      expect(data.first['content'], 'action 2');
    });
  });
}