Updated DDL for SQL backend

Phil Longstaff plongstaff at rogers.com
Sat Oct 28 13:25:37 EDT 2006


On Sat, 2006-28-10 at 12:55 -0400, Derek Atkins wrote:
> Quoting Phil Longstaff <plongstaff at rogers.com>:
> 
> > Re slot table: even if lists aren't used, there is still the hierarchy
> > based on sub-frames.  A row in the slot table may still need a link to
> > its parent, and this can't be the guid which identifies the whole slot.
> > We will need some sort of secondary key, then, to represent the links
> > within the hierarchy of one complete slot.  Each row in a complete slot
> > would have the same guid, so that a complete slot could be deleted using
> > "delete from slots where guid='...'", the top level slot would have
> > parent = null and secondary_key = 0, and other rows in the slot would
> > have parent = secondary_key_of_parent.
> >
> > Of course, if there are no lists, is there really any reason to bury a
> > single slot value in a hierarchy?  If lists are gone, sub-frames may be
> > gone too.
> 
> Nah...  Think of a KVP slot like a filesystem directory tree.  Each node
> can either be a File or a Directory.  The Slot Path walks down the
> tree (through KVP_FRAMEs) until you get down to a "file" node, a KVP_VALUE
> of some data-type other that KVP_FRAME.  Just because you don't have
> a KVP_VALUE of type "list" doesn't mean you can't (or shouldn't) have
> KVP_VALUE of type KVP_FRAME.

Yes, but without lists, the analogy is each node being either a file or
a directory which can contain at most 1 file or directory in it.  I
question the usefulness of all of the directory levels.

> However...  You CAN model it as a flat table!
> Take a look at the existing PG Backend KVP implementation.   You can
> model it as:
> 
>    text(32) object_id NOT NULL,
>    text slot_path NOT NULL,
>    enum(kvp_value_types) value_type,
>    ... values
> 
> You can construct (and deconstruct) a full kvp tree this way.  No
> need to reference parents or anything like that.  The slot_path would
> be a string like "/A/B/C/D".  Each "/" implies a KVP_FRAME with "/"
> denoting the "root node".

Fine, but without lists, isn't this equivalent to adding '/' to the list
of chars which can be put into a KVP node name?  Without lists, "/A/B/C"
and "/A/B/D" are separate independent nodes.  With lists, C and D are
children in the list "/A/B".

> The only question with this approach is how you model an empty KVP_FRAME
> as a leaf-node in the tree.

Yes, this works.  By adding 'empty' to the KVP type enum (at least in
the DB), you can model empty KVP frames.

Phil



More information about the gnucash-devel mailing list