maybe expand the swingstore API to take better advantage of SQLite features

What is the Problem Being Solved?

Now that the kvStore is kept in a SQLite DB (#3087, PR #6561), we could take advantage of SQL to improve or simplify the way the kernel uses the DB. This would expand the swingstore API to add new calls, not just key/value get/set/delete.

This is an exploratory ticket, to brainstorm about things we could do better. cc @FUDCo

On the kernel side, some things we might do include:

query the vat c-list for just the non-durable exports, to be abandoned during vat upgrade
- o+dNN is durable, o+NN is non-durable, but the table has an extra column with a isDurable boolean, for indexing
query the vat c-list for all the exports of a single vat, to break them during vat termination
vat c-lists are using two keys per entry, could be combined into one table with two CREATE INDEX
cleanupAfterTerminatedVat does a linear search of vat.dynamicIDs to remove the ID being deleted, could be faster
getImporters(koid) scans every vat to find which ones import a given object (used by retireExports): #3223
kernel promise table holds a list of queued messages, could be stored in its own table
- table with (kpid, seqnum, msg) columns
- would enable deletion of everything with a single SQL command
- could also put the messages in a second table, indexed by a unique msgid
- then moving messages from one queue to another would be cheaper: retain the msgid, change the pointer
unresolved kernel promises hold a set of subscribers, currently held as a serialized list: replace this with one-row-per-subscription

On the vat side (e.g. the vatstore), we might:

store virtual object state in one "vobj" table (baseref, stateCapdataBody, stateCapdataSlots), and the reference-graph edges in a second "edges" table (fromBaseref, toBaseref, refCount), and then use SQL queries to figure out which objects might be released when the edges are deleted
- where each slot in stateCapdataSlots is mapped to a baseref and then appears as toBaseref in an edge
- multiple outbound references to different vrefs that share a baseref cause the refCount to be greater than one

The "rank order" key encoding we use in virtual collections could be replaced with more sophisticated schemes (e.g. one column for the data type, and if the datatype is bigint, then the second column is digit length, and the third is the actual decimal-encoded value, and we have an INDEX that cites all three columns). Relatedly, we might be able to compile the "pattern" constraint into a more-direct SQL query. OTOH, the speedup is limited to the indices we add to the table, and everything eventually bottoms out into some linear search of a subset of the table. So we'd be treading on the territory of DBAs and their optimization magic, while obscuring the scheme with other layers on top.

Agoric / agoric-sdk

maybe expand the swingstore API to take better advantage of SQLite features #6677

What is the Problem Being Solved?

Description of the Design

Security Considerations

Test Plan