On Feb 24, 2011, at 5:13 PM, Tim-Erwin wrote:
> Hi,
>
> I'm trying to implement a versioning mechanism similar to this recipe:
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap
>
> The idea: as in the example I'd like to have (lets say) a Config of
> which the data ist stored in vertical tables. The difference to the
> example: I'd like to have different tables for different types
> (instead of stuffing all kinds of values into one table). It should
> have the same versioning mechanism.
>
> So my tables would be:
>
> config
> config_string_assoc
> string_values
> config_daterange_assoc
> daterange_values
> ...
>
> And how I'd like the code to be:
>
> config.data["username"] = "Joe"
> config.data["duration"] = ("11.11.2009", "12.11.2009")
> etc
>
> Or maybe even better (not sure which one is easier to achieve):
>
> config.data["username"] == {"type": "string", "value": "Joe"}
> config.data["duration"] == {"type": "daterange", "value1":
> "11.11.2009",
> "value2": "12.11.2009"}
>
> type, value, value1, and value2 would be column names. Does that
> relate to column_mapped_collection?
>
> I'd be thankful for any hints on which mechanisms of SQLA would help
> or which classes to use.
so the key moving part is when you have Config and you iterate through
"elements", what SQL you would want emitted. There's lots of ways to do it,
though as I go through these the tables you have laid out are trickier to use
than some other ways of approaching this issue.
Based on your tables, using concrete inheritance for different "assoc" types
with a UNION on the base would look like:
select * from config where <criterion>
select * from (
select * from config_string_assoc
union all
select * from config_daterange_assoc
) where config_id=<config_id>
using joined inheritance for different "assoc" types, adding a "config_assoc"
as a common base table, would look like:
select * from config where <criterion>
select * from config_assoc where config_id=<config_id>
select * from config_string_assoc where id= <some assoc_id we got
previously>
select * from config_daterange_assoc where id= <some assoc_id we got
previously>
or alternatively, if the mapper had a "polymorphic on", it might emit OUTER
JOIN to load those three tables at once:
select * from config where <criterion>
select * from config_assoc left outer join config_string_assoc on
<onclause>
left outer join config_daterange_assoc on <onclause>
where config_id=<config_id>
or, if two distinct relationship() objects to config_string_assoc and
config_daterange_assoc were created, a single descriptor on Config might pull
them both into memory and return a dictionary interface on top of both (either
by copying into a new dict or by proxing __getitem__()) - that would then allow
Config to load the two relationships at once using joined load, or fairly
efficiently using lazyload or subquery load - then the SQL looks like:
select * from config where <criterion>
select * from config_string_assoc where config_id=<config_id>
select * from config_daterange_assoc where config_id=<config_id>
To add even more possibilities, all three ideas above could also occur at the
level of config_association->value instead of config->config_association.
Now for alternative tables. If you were amenable to NULLs on
config_association, you could put several foreign keys on config_association
and do it like this:
config->config_association->string_value
config->config_association->daterange_value
ConfigAssociation would have multiple many-to-one relationships configured, one
to each type of "value". When you say ConfigAssociation.value, it looks up
the sub-value relationship that is not NULL. This is really simple, scales
well, maintains the same versioning idea of value objects owned by many
parents. But it has NULLs.
If OTOH you want to change the central concept of the versionioning, i.e. that
the "value" row is shared among many parent config_assoc objects, that allows
some straightforward possibliities. You might have:
config->config_value->string_value
config->config_value->daterange_value
ConfigValue relates to StringValue and DaterangeValue most simply via joined
table inheritance. Then, when a new Config version is made, you copy the full
contents of the collection, and each ConfigValue remains associated with one
and only one Config. This simplifies the model but would copy values upon
new versions. If your "value" tables hold large textual or binary fields, this
might be wasteful. So it sort of depends on your preferences and what kind
of data you're storing, how often the versioning takes place.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
