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

[Hogarty, David A.]

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

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