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

[Hogarty, David A.]

Title: Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?

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