Enhance Matrix SDK Database with queries

Preflight Checklist

[X] I could not find a solution in the existing issues, docs, nor discussions

Describe your problem

The current implementation is a drop-in-replacement for Hive. But it could need some refactoring and improvements.

Describe your ideal solution

With IndexedDB and SQFlite we can implement easy queries to improve performance at some places. Then we might be able to eliminate the cache in the key/value box implementations so that at the end we could have code which is simpler to read, more performant and reduce the code base at all.

For queries we could create indezes for indexedDB in the openbox method:

  Box<V> openBox<V>(String name, {Set<String>? indexes}) {
    if (!boxNames.contains(name)) {
      throw ('Box with name $name is not in the known box names of this collection.');
    }
    if (indexes != null) {
      if (V is! Map) {
        throw ('Indexes can only be used for Boxes which store JSON');
      }
      final txn = _db.transaction(name, 'readwrite');
      final store = txn.objectStore(name);
      for (final index in indexes) {
        store.createIndex(index, index, unique: false);
      }
    }
    return Box<V>(name, this);
  }

And then implement a query method like this (could also need direction parameter):

  Future<Map<String, V>> query(String indexName, Object indexValue,
      [int? limit, Transaction? txn]) async {
    txn ??= boxCollection._db.transaction(name, 'readonly');
    final store = txn.objectStore(name);

    final index = store.index(indexName);
    final resultStream = index.openCursor();

    final resultMap = <String, V>{};

    await for (final result in resultStream) {
      if (result.value != indexValue) continue;
      resultMap[result.key as String] = result.value as V;
      if (limit != null && resultMap.length >= limit) break;
    }
    return resultMap;
  }

For the sqflite backend we could use JSON functions to implement something similar: https://www.sqlite.org/json1.html

Then we may can get rid of the timeline box which stores only event IDs in a row and just rely on the sort order in the database.

Version

No response

Security requirements

No response

Additional Context

No response

famedly / matrix-dart-sdk