jonathan ellis pointed us all to a great series of articles
concerning primary keys:
http://spyced.blogspot.com/2006/07/single-column-primary-keys-should-
be.html
the gist of the articles is that *any* concept you are trying to
store in a database usually has some kind of unique identifying key.
in your case, the combination of "uid" and "timestamp" is the primary
key. SQLAlchemy is fine with this. even if you dont have these
columns explicitly defined as part of a primary key in your table
schema (which you probably should), you can still tell a mapper what
columns compose the primary key like this:
mapper(someclass, sometable, primary_key=[sometable.c.uid,
sometable.c.timestamp])
now for the next thing. SA, like every ORM I have ever seen, maps
object instances on a one-to-one basis against this primary key
identity, and in memory stores the instances in a map called an
"identity map". Which means, if you have an object instance A with
primary key "3", and then you change its primary key to "4", then
object instance A is essentially a different instance; the old number
"3" still remains. If you did that, SA would get pretty confused
and function in an undefined manner; since it still has that instance
mapped to the identity of "3". So the best you can do, assuming you
want the mappers to save these instances, is to have a distinct
object instance represented for each unique primary key value. If you
want a single object to change its "identity" under the scenes in a
transparent manner, you can construct a proxying object that talks to
the underlying mapped object, or use a similar architectural pattern.
Alternatively, you can simply map to the exact select statement you
have below, using the aggregate MAX function, etc. That way you
would just have a single object instance that corresponds to the
tabular product of that exact statement. However, while this mapper
will be able to query instances, it will not have all the information
it needs to save changes to those instances (or new instances) to the
database.
you could also do a combination of both approaches; SQLAlchemy
supports a few patterns to allow multiple mappers to exist for a
single class (or you could use multiple classes).
In either approach, you also have a lot of ways you could drop down
to raw SQL if needed.
so the gist is, SA is probably open ended enough to allow your
patterns to be successfully used; however its going to require some
creativity and slightly more in-depth knowledge about the session's
behavior since the ORM is designed around an immutable primary key
assumption.
On Aug 4, 2006, at 10:57 AM, Bill Noon wrote:
> I am at a loss on how to use sqlAlchemy Data Mappers to map a set of
> sql tables that don't have a unique primary key.
>
> Lets say I have these tables:
> CREATE TABLE Customer (
> uid integer,
> timestamp numeric,
> ts_flag text,
> name text,
> mail_addr integer,
> bill_addr integer
> );
> CREATE TABLE Address (
> uid integer,
> timestamp numeric,
> ts_flag text,
> person_uid integer,
> addr text
> );
> CREATE TABLE Order (
> uid integer,
> timestamp numeric,
> ts_flag text,
> person_uid integer,
> );
>
> Each time the data is updated, the timestamp is updated. The ts_flag
> indicates 'latest','historical', or 'deleted' and is used to sync to
> other databases.
>
> The combination of uid and timestamp _is_ unique but one customer
> would
> have a series of changes. The desired record depends on the use,
> either latest timestamp (not deleted), latest timestamp before a date,
> etc.
>
> Looking through the mail archives, there were several suggestions to
> split the current data from historical changes, but that complicates a
> standard query where you need the info current at a particular time
> (i.e. when an order was filled).
>
> To select the right set of records for an order, SA would need to
> generate a select clause similar to the following:
> select distinct Customer.uid, max(Customer.timestamp), mail_addr,
> bill_addr
> from Order, Customer
> where Order.uid = ? and Customer.uid = Order.person_uid and
> Customer.timestamp <= Order.timestamp;
>
> Subselects for the mailing and billing addresses would be similar.
>
> To get the most current records for a customer, the timestamp would be
> just the most recent. There would be other constraints for non-
> deleted
> customers.
>
> Is there a way to define a Mapper to handle these cases?
>
> Thanks --Bill
>
>
>
>
> ----------------------------------------------------------------------
> ---
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to
> share your
> opinions on IT & business topics through brief surveys -- and earn
> cash
> http://www.techsay.com/default.php?
> page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Sqlalchemy-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
