Updated DDL for SQL backend

[Phil Longstaff]

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