sqlite_automatic_undo_redo.dart

import 'package:mustache_template/mustache_template.dart';
import 'package:signals/signals_core.dart';
import 'package:sqlite3/sqlite3.dart';

class UndoRedoManager {
  final Database _db;
  UndoRedoManager(this._db);

  bool _active = false;
  List<List<int>> _undoStack = [];
  List<List<int>> _redoStack = [];
  int _firstLog = 1;
  int _freeze = -1;
  int? _pending;

  void activate(Iterable<String> tables) {
    if (_active) return;
    _createTriggers(_db, tables);
    _undoStack = [];
    _redoStack = [];
    _active = true;
    _freeze = -1;
    _startInterval();
  }

  void deactivate() {
    if (!_active) return;
    _dropTriggers(_db);
    _undoStack = [];
    _redoStack = [];
    _active = false;
    _freeze = -1;
  }

  void freeze() {
    if (_freeze >= 0) {
      throw Exception("Recursive call to freeze");
    }
    _freeze = _getMaxSeq();
  }

  void unfreeze() {
    if (_freeze < 0) {
      throw Exception("Called unfreeze while not frozen");
    }
    _db.execute("DELETE FROM undolog WHERE seq>$_freeze");
    _freeze = -1;
  }

  void event() {
    if (_pending == null) {
      barrier();
    }
  }

  void barrier() {
    _pending = null;
    if (!_active) {
      refresh();
      return;
    }
    int end = _getMaxSeq();
    if (_freeze >= 0 && end > _freeze) {
      end = _freeze;
    }
    int begin = _firstLog;
    _startInterval();
    if (begin == _firstLog) {
      refresh();
      return;
    }
    _undoStack.add([begin, end]);
    _redoStack = [];
    refresh();
  }

  void undo() {
    _step(_undoStack, _redoStack);
  }

  void redo() {
    _step(_redoStack, _undoStack);
  }

  void refresh() {
    // Implement your refresh logic here.  This will likely involve
    // calling methods in other parts of your application to update
    // the UI based on the current undo/redo state.
    // Example:
    // myUiComponent.updateUndoStatus(_undoStack.isNotEmpty);
    // myUiComponent.updateRedoStatus(_redoStack.isNotEmpty);
    _canUndo.value = _undoStack.isNotEmpty;
    _canRedo.value = _redoStack.isNotEmpty;
  }

  void reloadAll() {
    // Implement your reload logic here.  This will likely involve
    // calling methods in other parts of your application to completely
    // redraw the UI based on the current database state.
    _canUndo.value = _undoStack.isNotEmpty;
    _canRedo.value = _redoStack.isNotEmpty;
  }

  int _getMaxSeq() {
    return (_db.select('SELECT MAX(seq) FROM undolog')).firstOrNull?['MAX(seq)']
            as int? ??
        0;
  }

  void _createTriggers(Database db, Iterable<String> tables) {
    db.execute("DROP TABLE IF EXISTS undolog");
    db.execute("CREATE TEMP TABLE undolog(seq INTEGER PRIMARY KEY, sql TEXT)");

    for (String tbl in tables) {
      List<Map<String, dynamic>> collist = db.select("PRAGMA table_info($tbl)");
      final args = (
        name: tbl,
        columns: collist.map((col) => col['name'] as String).toList(),
      );
      final sql = args.render();
      db.execute(sql);
    }
  }

  void _dropTriggers(Database db) {
    List<Map<String, dynamic>> tlist = db.select(
      "SELECT name FROM sqlite_temp_schema WHERE type='trigger'",
    );
    for (var triggerMap in tlist) {
      String trigger = triggerMap['name'] as String;
      if (!RegExp(r"_.*_(i|u|d)t$").hasMatch(trigger)) continue;
      db.execute("DROP TRIGGER $trigger;");
    }
    db.execute("DROP TABLE IF EXISTS undolog");
  }

  void _startInterval() {
    _firstLog = _getMaxSeq() + 1;
  }

  void _step(List<List<int>> v1, List<List<int>> v2) {
    if (v1.isEmpty) return; // Handle empty stack case.
    List<int> op = v1.removeLast();
    int begin = op[0];
    int end = op[1];

    _db.transaction((txn) {
      // Use a transaction
      txn.execute("BEGIN");
      List<Map<String, dynamic>> sqllist = txn.select(
        "SELECT sql FROM undolog WHERE seq>=$begin AND seq<=$end ORDER BY seq DESC",
      );
      txn.execute("DELETE FROM undolog WHERE seq>=$begin AND seq<=$end");
      _firstLog = _getMaxSeq() + 1;

      for (var sqlMap in sqllist) {
        String sql = sqlMap['sql'] as String;
        txn.execute(sql);
      }
      txn.execute("COMMIT");
    });

    int newEnd = _getMaxSeq();
    int newBegin = _firstLog;
    v2.add([newBegin, newEnd]);
    _startInterval();
    refresh();
  }

  late final _canUndo = signal(_undoStack.isNotEmpty);
  late final canUndo = _canUndo.readonly();

  late final _canRedo = signal(_redoStack.isNotEmpty);
  late final canRedo = _canRedo.readonly();
}

extension on Database {
  void transaction(void Function(Database db) action) {
    // execute('BEGIN');
    try {
      action(this);
      // execute('COMMIT');
    } catch (e) {
      execute('ROLLBACK');
      rethrow;
    }
  }
}

const _undoRedoTemplate = r'''
CREATE TEMP TRIGGER {{name}}_it AFTER INSERT ON {{name}} BEGIN
  INSERT INTO undolog VALUES(NULL,'DELETE FROM {{name}} WHERE rowid='||new.rowid);
END;
CREATE TEMP TRIGGER {{name}}_ut AFTER UPDATE ON {{name}} BEGIN
  INSERT INTO undolog VALUES(NULL,'UPDATE {{name}}
     SET {{#columns}}{{name}}='||quote(old.{{name}})||'{{^last}}, {{/last}}{{/columns}}
   WHERE rowid='||old.rowid);
END;
CREATE TEMP TRIGGER {{name}}_dt BEFORE DELETE ON {{name}} BEGIN
  INSERT INTO undolog VALUES(NULL,'INSERT INTO {{name}}(rowid, {{#columns}}{{name}}{{^last}}, {{/last}}{{/columns}}) VALUES('||old.rowid||{{#columns}}','||quote(old.{{name}})||{{/columns}}')');
END;
''';

typedef UndoRedoTemplate = ({
  String name,
  List<String> columns,
});

extension on UndoRedoTemplate {
  String render() {
    final templ = Template(
      _undoRedoTemplate,
      htmlEscapeValues: false,
    );
    return templ.renderString({
      'name': name,
      'columns': [
        for (var i = 0; i < columns.length; i++)
          {
            'name': columns[i],
            'last': i == columns.length - 1,
          },
      ],
    });
  }
}