Support multi-column indexes in prose-store

Given a record

struct DeviceRecord {
  account: UserId, 
  id: DeviceId,
  name: String
}

where you'd want to perform the equivalent of a SQL query

SELECT `name` from `device_record` WHERE `account` = '?' AND `id` = '?'

you could currently only use account as an index then iterate over each row and manually check if it's id column matches.

Instead we should support compound indexes so that we could perform equivalent queries with our Store

<!DOCTYPE html>
<html>
<body>
<script>
let request = window.indexedDB.open('MyDatabase', 1);

request.onupgradeneeded = function(e) {
  let db = e.target.result;
  let objectStore = db.createObjectStore('device_record', { keyPath: 'name' });
  objectStore.createIndex('accountAndId', ['account', 'id'], { unique: false });

  for (let i = 1; i <= 3; i++) {
    for (let j = 1; j <= 10; j++) {
      for (let k = 1; k <= 5; k++) {
        objectStore.add({ account: `user${i}@prose.org`, id: j, name: `device ${i}-${j}-${k}` });
      }
    }
  }
};

request.onsuccess = function(e){
  let db = e.target.result;
  let tx = db.transaction('device_record', 'readonly');
  let objectStore = tx.objectStore('device_record');
  let accountIndex = objectStore.index('accountAndId');

  accountIndex.getAll(['user3@prose.org', 1]).onsuccess = function(event){
    console.log('> query 1');
    for (const device of event.target.result) {
      console.log(device.name);
    }
    console.log('< query 1');
  };

  accountIndex.getAll(IDBKeyRange.bound(['user2@prose.org', 2], ['user2@prose.org', 4])).onsuccess = function(event){
    console.log('> query 2');
    for (const device of event.target.result) {
      console.log(device.name);
    }
    console.log('< query 2');
  };
};
</script>
</body>
</html>

prose-im / prose-core-client

Support multi-column indexes in prose-store #77