Add ability to hydrate refs when getting from DataTypes

[RangerMauve]

Description

A lot of datatypes have refs to other objects consisting of core id + version id. This is needed to get a doc from another collection (basically a join). Right now consumers of core need to do these joins at the application level which can be complex when there's several fields to manage accross queries. We should provide a way to hydrate these refs at the getMany or getById level.

e.g. dream API:

project.observation.getByDocId(docId, { include: { track: true })

Tasks

(TODO: Bikeshed approach)

• Settle on API for consumers (I think a list of fields to hydrate would be useful
• Add metadata to schema to define ref table names
• Add first class notion of "datatype registry" to look up other tables to do joins on.
• Figure out TS shenanigans to add de-ref'd fields for each ref'd field to datatype returns.
• Nested deref
• in-memory cache for getMany to reduce queries
• real SQL joins? https://orm.drizzle.team/docs/joins (probs not)

[RangerMauve]

So far lookin at something like this:

const observations = new DataType({
  dataStore,
  table: observationTable,
  db,
  hydratedFields: [{
    field: observationTable.presetRef,
    table: presetTable
  }]
})

const obs = observations
  .getDocById(docId, {hydrate: true})

assert.deepEqual(obs.preset, thePresetData)

[RangerMauve]

@gmaclennan Thoughts on how to join on the JSON inside presetRef to actually get the docId out in a way the SQL can run?

[RangerMauve]

Here's how to extract the docId from ref fields for future reference:

comapeo-core/src/translation-api.js

Line 85 in f7b8bc2

sql`json_extract(docRef, '$.docId') = ${value.docRef.docId}`,

 sql`json_extract(refField, '$.docId') = ${docId}`

[RangerMauve]

A bit stuck figuring out how to extract values out of an array of object and do a join on them

[RangerMauve]

Got it!

[RangerMauve]

Issue, track.observationRefs and preset.fieldRefs are arrays. SQLite JOIN only pulls one value at a time and results in duplicate entries for either one of the documents.

Gonna see if I can get this to work with json_group_array. I worry the typescript correctness will keep getting harder

[RangerMauve]

DataType registry proposal

After fussing with drizzle-orm relations and raw Sqlite I've come up with a proposal for how to implement this feature.

Ee will make DataTypes aware of each other so that the datatype.get* methods can get the DocID from the Ref field and get the relevant DataType to fetch the doc by ID.

This would be done by introducing a new "registry" object based on the existing MapeoProject.#dataTypes map. We'll need to either define it as an empty map and add datatypes one by one so we can pass it into the DataType constructor in or have a getTypeByName() function passed into the DataType constructor that could call this.#dataTypes[name] inside the mapeo project (have a default function that throws an error).

From there we'll look at the table columns in the constructor and track which ones have Ref in the name to get a set of hydration targets. The prefix before Ref will be used to look up the name of the datatype since it seems we've got a consistant convetion around this so far. We may also want to pass in the list of ref fields and their relevant datatype name manually which is what I started with in my pr

We'll then add another step to DataType.#mutatingAddDerivedFields() which will check if the hydrate flag is true, and will go through the ref fields, look up the target doc by ID (also with hydrate: true so it's recursive)

DataType will also get a new getManyByDocId() which will take a list of doc IDs and do a select where docID is inArray of the list.

The docs fetched for the ref field will be placed in a field with the name derived from the ref field name. e.g. observationRefs -> observations, presetRef -> preset

For the getMany case we might want to have some sort of cache to pull from in case there's several observations for the same preset.

Why?

While learning the drizzle relations feature it felt like our existing datamodel is not suitable to how Drizzle expects things to work. This would mean adding more complexity to make our datamodel work for drizzle, or potentially make a bunch of changes to our data model (like rethinking refs, generating junction tables and converting between arrays and junction tables).

This approach will keep most of the code as is and have a small layer added for skipping doing the joins necessary on the consumer side of the API.

Alternatives

The downside is that this will result in more individual SQL queries which may add up vs keeping more logic in single bigger queries.

Another approach we could consider is ditching .select style queries (maybe just in DataType) and migrate to db.queries. This would require building junction tables for any array based ref fields. I'm also not certain that virtual junction tables would work with the relations API out of the box.

Gregor mentioned we could use virtual columns that extract the DocID, and virtual junction tables that extract the doc IDs from arrays of refs.

const presetsTable = sqliteTable("presets", {
  //...
  iconDocId: generatedAlwaysAs(
    (): SQL => sql`(json_extract(${presetsTable.iconRef}, '$.docId'))`
    { mode: 'VIRTUAL' }
  )
}

We could modify the jsonSchemaToDrizzleColumns helper to automatically detect ref fields and add this.

We'd likely need a second helper to generate the virtual junction tables.

Here's the proposed SQL

CREATE VIEW preset_field_junction AS
SELECT 
    preset.id AS preset_id,
    json_extract(ref.value, '$.docId') AS field_doc_id,
    json_extract(ref.value, '$.versionId') AS field_version_id
FROM 
    preset,
    json_each(preset.fieldRefs) fieldRef;

We'd need to build the junction table, then define relations on the two datatypes that link to the table based on the many-to-many example. I think we'd then need to query against the junction table when hydrating inside datatype.

Recursive hydration will require queries to be aware of sub-refs.

const users = await db.query.observation.findMany({
	with: {
		preset: {
			with: {
				fields: true,
                                icon: true,
			},
		},
	},
});

We'd also likely want to add another step to populate / translate fields which would require the "datatype" registry anyway.

[RangerMauve]

Note re deletions from @gmaclennan:

I think that maybe it's better not to delete references in order to reduce the chance of conflicts (e.g. let's say device A edits a track, and device B deletes an observation it references, and deletes the reference on the track, resulting in conflicting edits, which could be resolved with the track from device A, without the deleted reference, or vice-versa), and regardless we would still not be able to guarantee that references do not reference deleted observations (because of the nature of a p2p system), so we need to implement filtering of references by deleted property anyway. Ultimately this should be a backend solution, as part of the work of expanding references before returning data, but the frontend should also be aware of it, because we would probably want to keep that reference around when editing a track, so it will still be exposed. @Mauve (they/it) could you add a note to the issue about expanding refs to consider deletions - how do you think we should handle it on the backend? Only return the refs if includeDeleted=true? Do we remove the ref before returning the data? I think it might end up feeling too "magic" and the least confusing solution might be to always return all expanded refs, whether deleted or not, and just rely on the frontend to filter.