Hi Hayden,
On 14.07.2015 03:43, Hayden Livingston wrote:
> Is there a concept of a schema-less JSON SQLite DB?
>
> My reason is simple: versioning. We have lot of business metrics that
> get updated let's say once a month, and we need to be agile to get
> them. Right now, we just put the version in the SQLite file, and then
> make sure no queries cross the boundaries.
>
> Secondly, we have requirements for slightly hierarchal data, i.e.
> mostly row form, but then some guy wants to put an object.
>
> What's the SQLite community heading towards if at all?
Given the number of the threads in the list, it seems a
lot of people want hierarchical data in SQLite :-)
>
> Do others have experiences and requirements similar to this?
My experience:
Few years ago, during small project about translating set of
procedures from one to another SQL dialect, we initially
tried to use XML database for the task, but finally become
to solution to manipulate the parsed procedures in SQLite
using a schema with following simplified core:
doc(d, name)
node(dn, d, x, y, type, value)
attribute(dn, type, value)
reference(dn, type, ref_dn)
[Where (x, y) are equal to (row, column) when one dump the
tree to text file with indent = 1]
This schema is a variation of the BaseX encoding schema:
http://docs.basex.org/wiki/Node_Storage
where:
- we used y (the depth of the node) instead of DIS
(distance to the parent)
- we omitted SIZ (size of the subtree) - it is easily
calculable (next(on same y).x - x).
- we used single integer .type as replacement of NS, KIND, Tag
name + expected scalar type
- we split the scalar attributes (XML attributes) in
separate table - attribute and moved the first (only
in our case) text() value to node.value.
- we added reference (links between nodes)
Actually there were other modifications like e.g.
node_range_offset(dn_from, dn_to, offset) table, which we
applied for faking insertions and deletions of whole subtrees
(logical node.x was function of that table and stored
node.x).
Sometime next year I am planning to revive that experiment,
this time for storing/generating configuration data (for
Linux configuration parsed with the augeas tool). Now with the
presence of the powerful CTE feature in SQLite the things
may be a way more easier.
Meantime (given that you have shared with the list that your
project is a business oriented one), I would like to propose
to you, to initiate a sponsorship and a team including
interested list members for digging the subject further.
The team could consolidate the different approach ideas,
existing open source components and discuss what will be
most appropriate and light as implementation resources for
use cases like yours + eventually report to the the SQLite
core team which additional APIs would make the implementation
more efficient.
Kind regards,
Alek
