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
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
