On Wed, Jul 15, 2015 at 2:14 AM, Alek Paunov <alex at declera.com> wrote:
> 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:
>
>
I have an option map stored hierarchical. I would tackle storing something
like XML similarly I suppose where I'd have...
node( n, d, parent_node, node_order, name )
value( n, value )
(for XML... doc( d, name, n ) )
element( n, parent_element, node_order, name )
attributes( n, name, value )
(where d, n are primary keys)
I didn't have separate maps (like separate xml docs), I have a 'root' which
is ID 0 (GUID 0), and for each value under that I grab where parent is the
node and the name is what I'm looking for... or enumerating all is order by
node_order and parent_id. For document support, the document would store
the root node ID to prevent replicating the doc ID in all nodes. The
treeview only queries on the branches you expand so it's not a large query
even with lots of options. Other developers in Access tried to use it but
ended up sucking in the entire tree and reparsing it with normalized names
that were the full path instead of treating it as a hierarchy.
The other place I used a similar structure was saving a neural network
(really a wiring diagram of nodes and connections)
I don't know much about RECURSE operator that's available... like to select
'option, program, display, render_interface' which is a 4 deep query...
But I did find that reversing the direction of the pointer made it work
pretty well... just not with SQL stored procedures, instead relying on code
support external to the database... (reversing being that normally you
have node->node->node ... where the storage is actually more like
node<-node<-node )
> 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
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
