Thank you again, Michael - a very interesting suggestion. I'm going to start
experimenting with your previous suggestion of a linked list. This is simple
and intuitive, and I can see it working very well. In fact it's a similar
principle to that used by Audacity, the open source audio editor, which
segments the original audio into a linked list of temporary 'block' files,
enabling fast cut/copy/paste manipulations (in contrast to many editors, which
rely on literal copying and insertion into a single file).
I will be storing my data as blobs between 1024 and 2048 bytes, rather than
individual samples. Of course it is extremely unlikely that any cut or copy
selection will fall on the boundary of a blob - most of the time it will slice
through it. So any blob thus affected will have to be rebuilt, or its data
redistributed to neighbouring rows. Given the average size of a blob this
shouldn't be an issue, however. And even for a large file experiment indicates
that the corresponding reduction hierarchy will be unlikely to exceed a few MB.
So now I am going to actually perform some tests...
Many thanks again,
Christopher
> From: michael.bla...@ngc.com
> To: sqlite-users@sqlite.org
> Date: Tue, 12 Jul 2011 11:38:13 +0000
> Subject: Re: [sqlite] Storing/editing hierarchical data sets
>
> I thought of another way to do your copy/cut/paste...
>
>
>
> Assuming you keep the original audio around and use the levels I showed
> before.
>
>
>
> create table sequence(level int,parent int,start int,end end);
>
> insert into seqeunce values(1,0,0,-1); // note that -1 means "until end of
> data".
>
>
>
> See where I'm going? You keep a sequence table that is much like your btree.
> It's just a collection of clips that when strung together can make your
> audio clip. By default you have one sequence per level.
>
>
>
> Cut 1000-1999 from level=1
>
> select * from sequence where level=1;
>
> delete from sequence where level=1;
>
> insert into sequence values(1,0,0,999);
>
> insert into sequence values(2,1,2000,-1);
>
>
>
> Insert some data:
>
> 1st you find where it fits
>
> select * from sequence where level=1;
>
> bytes1=0;
>
> while moredata
>
> bytes2+=end-start;
>
> if (insertpoint >=bytes1 and insertpoint <=bytes2)
>
> update sequence set id=id+1,parent=parent+1 where id>=currentid;
>
> break;
>
> end
>
>
>
> Cuts are just splitting one record in 2, or adjusting 2 records and deleting
> records in between.
>
> I'll leave that as an exercise for you.
>
>
>
> This would
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> ________________________________
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Christopher Melen [relativef...@hotmail.co.uk]
> Sent: Sunday, July 10, 2011 12:52 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Storing/editing hierarchical data sets
>
>
> Hi,
>
>
> I am developing an application which analyses audio data, and I have recently
> been looking into Sqlite as a possible file format. The result of an analysis
> in my application is a hierarchical data set, where each level in the
> hierarchy represents a summary of the level below, taking the max of each
> pair in the sub-level, in the following way:
>
>
> 251 214
>
>
> 251 54 201 214
>
>
> 251 91 17 54 31 201
> 214 66
>
>
> 251 18 5 91 11 17 54 16 9 31 201 148 173 214 43 66
>
>
> Such a structure essentially represents the same data set at different levels
> of resolution ('zoom levels', if you like). My first experiments involved a
> btree-like structure (actually something closer to an enfilade* or counted
> btree**), where the data stored in each node is simply a summary of its child
> nodes. Edits to any node at the leaf level propagate up the tree, whilst
> large edits simply entail unlinking pointers to subtrees, thus making edits
> on any scale generally log-like in nature. This works fine as an in-memory
> structure, but since my data sets might potentially grow fairly large (a few
> hundred MB at least) I need a disk-based solution. I naively assumed that I
> might be able to utilize Sqlite's btree layer in order to implement this more
> effectively; this doesn't seem possible, however, given that the btree layer
> isn't directly exposed, and in any case it doesn't map onto the user
> interface in any way that seems helpful for this task.
>
>
> I am aware of some of the ways in which hierarchical or tree-like structures
> can be represented in a database (adjacency lists, nested sets, materialized
> paths, etc.), but none of these seems to offer a good solution. What I'm
> experimenting with at present is the idea of entering each node of the
> hierarchy into the database as a blob (of say, 1024 bytes), while maintaining
> a separate in-memory tree which then maps on to this flat database of nodes
> (each node in the tree maintains a pointer to a node in the database).
>
>
> I would be very interested in
> thoughts/observations
> on this problem - or even better a solution!
>
>
>
> Many thanks in advance,
> Christopher
>
>
> * http://en.wikipedia.org/wiki/Enfilade_(Xanadu)
> ** http://www.chiark.greenend.org.uk/~sgtatham/algorithms/cbtree.html
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
