Re: [Sqlalchemy-users] Compound Keys: determining for create, using compound foreign keys

Thu, 21 Sep 2006 14:22:56 -0700

if the versions are truly just 1-N based, why even call to the database to get the number ?  if you set up a backreference on Version to get its Project, you can just do:

class VersionKeyMapper(MapperExtension):
    def before_insert(self, mapper, connection, instance):
   instance.id = instance.project.versions.index(instance) + 1

or you can just modify Project to assign numbers as versions are appended, that way you have the numbers before you even have to flush():

class Project(object):
def add_version(self, version):
version.id = len(self.versions) + 1
self.versions.append(version)

or if you want the totally clean API, do the "custom list class" thing:

class VersionList(list):
def append(self, item):
if getattr(item, 'id', None) is None:
item.id = len(self) + 1
list.append(self, item)

class Project(object):
versions = VersionList

i was thinking you could also do it via a column default combined with a thread local, but id have to modify the API a little bit to get the current Connection in there.

the mapper's save_obj is written the way it is to support batching.   which it doesnt do at the moment because it makes the "concurrency" check impossible with DBAPI's current behavior for executemany(), and also theres other stuff regarding defauls that wouldnt work right now (but could work with a little tweaking).  but if we flip it inside out then theres no chance of ever supporting that, or we'd have to have two totally different versions of save_obj...although if you have some insight on how to do it in a reasonably readable fashion im open to that (since i suppose this sort of thing is going to be needed by others as well).


On Sep 21, 2006, at 3:45 PM, Hogarty, David A. wrote:

Looking at the code, the problem seems to arise in sqlalchemy/orm/mapper.py, in the save_obj function
 
The basic structure is:
 
run before_insert/before_update for all objects
 
save all the mapper attributes to a table at a time for all objects
 
run after_insert/after_update for all objects
 
The problem here is if there are sequential dependencies between object saves, as there are in the case of versioning, the above model doesn't work. The goal of the above structuring was to "bundle inserts/updates on the same table together...". However, this obviously doesn't work in all cases. There are two options I see:
 
-Always save complete objects before the next object, reordering the central loop of save_obj to loop first on objects, then on tables
-Have some sort of 'sequential dependency' flag that determines whether or not to loop on tables for efficiency if false or on objects for correctness if true
 
-Dave H
 
"

From: [EMAIL PROTECTED] on behalf of Hogarty, David A.
Sent: Thu 9/21/2006 1:38 PM
To: Sqlalchemy-users@lists.sourceforge.net
Subject: [Sqlalchemy-users] Compound Keys: determining for create,using compound foreign keys

I'm having a little difficulty figuring out how to work with compound primary keys within the following context: I have Projects, Versions, and for examples sake, 'Things' inside of both of these, as follows:
 
CREATE TABLE projects (
        id INTEGER NOT NULL,
        name VARCHAR(40),
        PRIMARY KEY (id)
)
 
CREATE TABLE versions (
        project_id INTEGER NOT NULL,
        id INTEGER NOT NULL,
        date TIMESTAMP,
        comment VARCHAR(300),
        PRIMARY KEY (project_id, id),
         FOREIGN KEY(project_id) REFERENCES projects (id)
)
 
CREATE TABLE things (
        project_id INTEGER NOT NULL,
        thing_id INTEGER NOT NULL,
        start_version INTEGER NOT NULL,
        end_version INTEGER NOT NULL,
        PRIMARY KEY(thing_id),
        FOREIGN KEY(project_id) REFERENCES projects (id),
        FOREIGN KEY(project_id, start_version) REFERENCES versions (project_id, id),
        FOREIGN KEY(project_id, end_version) REFERENCES versions (project_id, id)
)
 
For now, I'll start with my first problem: properly sequencing the version ids. I need the version ids to start at 1 and increase by 1 for each project, so for example project 1 might have versions 1-52, project 2 might have 1-25, etc. Versions will not be deleted. My current approach to this is the following MapperExtension:
 
versions_table = Table('versions', metadata,
    Column('project_id', Integer, ForeignKey('projects.id'), primary_key=True),
    Column('id', Integer, primary_key=True),
    Column('date', DateTime),
    Column('comment', String(300)))
 
class VersionKeyMapper(MapperExtension):
    def before_insert(self, mapper, connection, instance):
        versions_t = mapper.select_table
        s = select(
            [func.max(versions_t.c.id)+1],
            versions_t.c.project_id==instance.project_id
        )
        r = s.execute()
        new_id = r.fetchone()[0]
        instance.id = new_id or 1
 
mapper(Version,versions_table, extension=VersionKeyMapper())
 
However, using this mapping, if I insert multiple versions before flushing the session, as in:
 
p = Project('test')
p.versions.append(Version('first version'))
session.save(p)
p.versions.append(Version('second version'))
session.flush()
 
I get an error because the selects in the MapperExtension happen before the inserts, so multiple versions are all assigned the same version number:
 
[2006-09-21 13:21:24,030] [engine]: BEGIN
[2006-09-21 13:21:24,032] [engine]: INSERT INTO projects (name) VALUES (?)
[2006-09-21 13:21:24,032] [engine]: ['test']
[2006-09-21 13:21:24,036] [engine]: SELECT max(versions.id) + ?
FROM versions
WHERE versions.project_id = ?
[2006-09-21 13:21:24,038] [engine]: [1, 1]
[2006-09-21 13:21:24,046] [engine]: SELECT max(versions.id) + ?
FROM versions
WHERE versions.project_id = ?
[2006-09-21 13:21:24,048] [engine]: [1, 1]
[2006-09-21 13:21:24,051] [engine]: INSERT INTO versions (project_id, id, date,
comment) VALUES (?, ?, ?, ?)
[2006-09-21 13:21:24,051] [engine]: [1, 1, '2006-09-21 13:21:24', 'first version
']
[2006-09-21 13:21:24,054] [engine]: INSERT INTO versions (project_id, id, date,
comment) VALUES (?, ?, ?, ?)
[2006-09-21 13:21:24,055] [engine]: [1, 1, '2006-09-21 13:21:24', 'second versio
n']
 
Any suggestions?
 
-Dave Hogarty
-------------------------------------------------------------------------
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
Sqlalchemy-users mailing list

-------------------------------------------------------------------------
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

Reply via email to