On 6/15/06, Arnar Birgisson <[EMAIL PROTECTED]> wrote:
> Sounds good. I haven't tested it yet, but somehow the join conditions
> above seem insufficient. The key on Pages is (job, pagename) and the
> key on PageVersions is (job, pagename, version). Anyways - I'll give
> it a try and look at the generated SQL.
Ok, tried things out. Seems I need to specify the join condition fully.
Saving:
In [1]: from sqlalchemy import *
In [2]: from softproof.model.jobs import *
In [3]: engine = create_engine('sqlite://./test.db', echo=True)
In [4]: metadata.create_all(engine)
[2006-06-15 09:42:36,809] [engine]: PRAGMA table_info(jobs)
[2006-06-15 09:42:36,809] [engine]: {}
[2006-06-15 09:42:36,816] [engine]:
CREATE TABLE jobs(
jobno VARCHAR(15) NOT NULL PRIMARY KEY,
created TIMESTAMP NOT NULL,
deleted BOOLEAN NOT NULL
)
[2006-06-15 09:42:36,817] [engine]: None
[2006-06-15 09:42:36,843] [engine]: COMMIT
[2006-06-15 09:42:36,843] [engine]: PRAGMA table_info(pages)
[2006-06-15 09:42:36,843] [engine]: {}
[2006-06-15 09:42:36,844] [engine]:
CREATE TABLE pages(
jobno VARCHAR(15) NOT NULL REFERENCES jobs(jobno),
pagename VARCHAR(30) NOT NULL,
created TIMESTAMP NOT NULL,
deleted BOOLEAN NOT NULL,
currentversion INTEGER,
UNIQUE (jobno, pagename)
)
[2006-06-15 09:42:36,844] [engine]: None
[2006-06-15 09:42:36,873] [engine]: COMMIT
[2006-06-15 09:42:36,873] [engine]: PRAGMA table_info(pageversions)
[2006-06-15 09:42:36,873] [engine]: {}
[2006-06-15 09:42:36,874] [engine]:
CREATE TABLE pageversions(
jobno VARCHAR(15) NOT NULL REFERENCES pages(jobno),
pagename VARCHAR(30) NOT NULL REFERENCES pages(pagename),
version INTEGER NOT NULL,
created TIMESTAMP NOT NULL,
active BOOLEAN NOT NULL,
UNIQUE (jobno, pagename, version)
)
[2006-06-15 09:42:36,874] [engine]: None
[2006-06-15 09:42:36,922] [engine]: COMMIT
In [5]: s = create_session(engine)
In [6]: j = Job('Job1')
In [7]: p = Page(j, 'Page1')
In [8]: for v in range(3):
...: p.versions.append(PageVersion(p, v+1))
...:
In [9]: p.versions[2].version
Out[9]: 3
In [10]: p.current_version = p.versions[2]
In [11]: s.save(j)
In [12]: s.flush()
[2006-06-15 09:44:02,378] [engine]: BEGIN
[2006-06-15 09:44:02,380] [engine]: INSERT INTO jobs (jobno, created,
deleted) VALUES (?, ?, ?)
[2006-06-15 09:44:02,381] [engine]: ['Job1', '2006-06-15
09:44:02.380696', False]
[2006-06-15 09:44:02,389] [engine]: INSERT INTO pages (jobno,
pagename, created, deleted, currentversion) VALUES (?, ?, ?, ?, ?)
[2006-06-15 09:44:02,389] [engine]: ['Job1', 'Page1', '2006-06-15
09:44:02.389557', False, None]
[2006-06-15 09:44:02,395] [engine]: INSERT INTO pageversions (jobno,
pagename, version, created, active) VALUES (?, ?, ?, ?, ?)
[2006-06-15 09:44:02,396] [engine]: ['Job1', 'Page1', 1, '2006-06-15
09:44:02.395765', True]
[2006-06-15 09:44:02,398] [engine]: INSERT INTO pageversions (jobno,
pagename, version, created, active) VALUES (?, ?, ?, ?, ?)
[2006-06-15 09:44:02,398] [engine]: ['Job1', 'Page1', 2, '2006-06-15
09:44:02.398153', True]
[2006-06-15 09:44:02,400] [engine]: INSERT INTO pageversions (jobno,
pagename, version, created, active) VALUES (?, ?, ?, ?, ?)
[2006-06-15 09:44:02,400] [engine]: ['Job1', 'Page1', 3, '2006-06-15
09:44:02.400543', True]
[2006-06-15 09:44:02,404] [engine]: UPDATE pages SET created=?,
deleted=?, currentversion=? WHERE pages.jobno = ? AND pages.pagename =
?
[2006-06-15 09:44:02,404] [engine]: ['2006-06-15 09:44:02.389557',
False, 3, 'Job1', 'Page1']
[2006-06-15 09:44:02,405] [engine]: COMMIT
Ok, that worked fine. Now, loading:
In [1]: from sqlalchemy import *
In [2]: from softproof.model.jobs import *
In [3]: engine = create_engine('sqlite://./test.db', echo=True)
In [4]: s = create_session(engine)
In [5]: jobs = s.query(Job).select()
[2006-06-15 09:47:34,633] [engine]: SELECT jobs.deleted AS
jobs_deleted, jobs.jobno AS jobs_jobno, jobs.created AS jobs_created
FROM jobs ORDER BY jobs.oid
[2006-06-15 09:47:34,633] [engine]: []
In [6]: j = jobs[0]
In [7]: j.jobno
Out[7]: u'Job1'
In [8]: j.pages
[2006-06-15 09:47:46,457] [engine]: SELECT pages.created AS
pages_created, pages.deleted AS pages_deleted, pages.currentversion AS
pages_currentversion, pages.pagename AS pages_pagename, pages.jobno AS
pages_jobno
FROM pages
WHERE ? = pages.jobno ORDER BY pages.oid
[2006-06-15 09:47:46,457] [engine]: [u'Job1']
Out[8]: [<softproof.model.jobs.Page object at 0xb76ee58c>]
In [9]: p = j.pages[0]
In [10]: p.pagename
Out[10]: u'Page1'
In [11]: p.cur
p.current_version p.currentversion
In [11]: p.current_version
[2006-06-15 09:48:04,476] [engine]: SELECT pageversions.created AS
pageversions_created, pageversions.active AS pageversions_active,
pageversions.version AS pageversions_version, pageversions.pagename AS
pageversions_pagename, pageversions.jobno AS pageversions_jobno
FROM pageversions
WHERE ? = pageversions.version ORDER BY pageversions.oid
[2006-06-15 09:48:04,476] [engine]: [3]
Out[11]: <softproof.model.jobs.PageVersion object at 0xb76ee9ec>
oops.. that last where clause would load all pageversions with version=3..
Anyways, I think I can figure out the rest. Many thanks!
Arnar
My code for the above:
from datetime import datetime
from sqlalchemy import *
__meta__ = metadata = DynamicMetaData()
jobs = Table("jobs", __meta__,
Column("jobno", Unicode(15), primary_key=True),
Column("created", DateTime, nullable=False,
default=datetime.now),
Column("deleted", Boolean, nullable=False, default=False))
class Job(object):
def __init__(self, jobno=None):
if jobno:
self.jobno = jobno
mapper(Job, jobs)
pageversions = Table("pageversions", __meta__,
Column("jobno", Unicode(15),
ForeignKey("pages.jobno"), primary_key=True),
Column("pagename", Unicode(30),
ForeignKey("pages.pagename"), primary_key=True),
Column("version", Integer, primary_key=True, default=1),
Column("created", DateTime, nullable=False,
default=datetime.now),
Column("active", Boolean, nullable=False, default=True))
class PageVersion(object):
def __init__(self, page=None, version=None):
if page:
self.page = page
if version:
self.version = version
mapper(PageVersion, pageversions)
pages = Table("pages", __meta__,
Column("jobno", Unicode(15), ForeignKey("jobs.jobno"),
primary_key=True),
Column("pagename", Unicode(30), primary_key=True),
Column("created", DateTime, nullable=False,
default=datetime.now),
Column("deleted", Boolean, nullable=False, default=False),
Column("currentversion", Integer))
class Page(object):
def __init__(self, job=None, pagename=None):
if job:
self.job = job
if pagename:
self.pagename = pagename
mapper(Page, pages, properties={
'job': relation(Job, backref=backref('pages', cascade="all,
delete-orphan")),
'current_version': relation(PageVersion,
primaryjoin=pages.c.currentversion==pageversions.c.version,
post_update=True),
'versions': relation(PageVersion, cascade="all, delete-orphan",
order_by=pageversions.c.version, backref='page')
})
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
