[Bucardo-general] Status on Multi-Column Primary Key Support

[Greg Sabino Mullane]

As promised, an estimate of multi-column primary keys in Bucardo. I've
been working on the problem of smoothly adding and removing target
databases and keeping bucardo_delta clean at the same time, but have
spent a little thought on the key problem.

Basically, we can either add new columns to goat, or overload the
existing pkey and pkeytype columns to hold multiple fields, such as
"abc|def". Then we create new versions of bucardo_delta, for each number
of primary keys. So bucardo_delta2 would get created as needed, etc. In
reality, keys with more than 2 primary keys are unlikely, so I'm not
convinced that we need to be so flexible as to hold any number of keys.
Perhaps three would be sufficient? The other big thing that would need
changing would be the main join against the bucardo_delta table from
within the kid. I've got some early prototypes, but I'm still undecided
on which of these to do:

1. goat.pkey = 'id1' goat.pkeytype = 'int'; goat.pkey2 = 'id2'
goat.pkeytype2 = 'text';

2. goat.pkey = 'id1|id2'; goat.pkeytype = 'int|text';

3. goat.pkey = fk to a separate table.

Number three is just my instinctual desire to normalize things, but it
not needed as the data is not going to change often, and we'd have to
parse it apart anyway. Number two is nice because we don't have to
change the schema at all, it supports any number, and a quick split gets
you the needed information. Number one prevents any changes to existing
goat tables, keeps things very simple, but hardcodes some more columns
into the goat table, which is not pretty. Now that I've written it out,
I'm leaning towards number two strongly, but any input is welcome. I'd
estimate a working prototype could arrive in about a week from now,
based on how much time I'm able to give it between now and then.

-- 
Greg Sabino Mullane greg at endpoint.com
End Point Corporation 610-983-9073

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 155 bytes
Desc: This is a digitally signed message part
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20071026/84fb2774/attachment.bin