Wondering if anyone has advice on modelling this data structure in Ash resources:
"name": "Rule - Lionel Messi sighting",
- id: uuid
- name: string
- conditions: MultiCondition (nested dynamic map)
- action: string
- type: one of [all, any]
- list of SimpleConditions and possible nested MultiConditions
- path: string
- operator: one of [equals, contains, greater_than, less_than, like]
- value: string or int
- A Rule must have only one top level MultiCondition
- A MultiCondition is allowed to have any number of SimpleConditions and nested MultiConditions
Dynamic nested data (like MultiCondition) doesn’t work well with SQL (a known issue). I am targeting SQLite for persistence layer.
a.) model it all as one ‘Rule’ resource and everything else as attributes: id, name, condtions, action. Then the ‘conditions’ attribute stays as json string in DB for easy dynamic nesting.
b.) break them all out into individual resources but then manage complex relationships between them all? Not sure how the MultiCondition would work with nesting.
I was going to suggest jsonb and some database check constraints at the DB level as the first step, but you’re using sqlite which has poor Json support (it’s basically text).
Ash provides the ability to add any validations you like, a custom validator can enforce your rules.
You might also want to look at embedded resources Embedded Resources — ash v2.15.7 and unions Ash.Type.Union — ash v2.15.7
I don’t know much about the differences between postgres jsonb and sqlite json but the sqlite functions look alright. For the app idea I’m expecting most DB operations to be around searching, viewing, and fetching the rules. The only other DB worth considering for this is maybe CubDB after seeing they launched Ash support recently. But then I don’t get the other niceties of sqlite and ecto etc.
And thanks I’ll take a look at the embedded resources!
Also just found this Sqlite discussion:
The cool thing for working with json is to store each json document as is in one column, then make virtual columns that store some specific information you want to query, using some combination of json_extract, then index those columns.
This makes for super-fast search, and the best part is you don’t have to choose what to index at insert time; you can always make more virtual columns when you need them.
(You can still also search non-indexed, raw json, although it may take a long time for large collections).
I just want to point out that Ash doesn’t have a working SQLite datalayer. @zachdaniel started on one but it’s basically just a copy pasta of
ash_postgres and needs a ton of work before it’s usable.
Thanks for mentioning, I later realized this . No worries appreciate all the hard work you guys are doing. I’ll probably be switching to postgres for POC anyway at this point.