[sqlalchemy] Re: Separate version table
Thanks Arnar, that was interesting to read. I learned a lot with these
codes. Here i share current point of my progress. I managed to do
mapper extension and document - documentversion objects seems to work
ok, but im not so sure, if my solution was very elegant. So if anyone
sees this and wants to give further addvises, i'll be glad to hear. My
next step is to extend versioned documents from user point to the
organisation wide shared documents.
# Models
from string import join
from datetime import datetime
class User(object):
def __init__(self, name):
self.name = name
class Organisation(object):
def __init__(self, name):
self.name = name
class Language(object):
def __init__(self, alpha2, name):
self.alpha2 = alpha2
self.name = name
class Document(object):
def first(self):
return self.versions[0]
def latest(self):
return self.versions[len(self.versions)-1]
class DocumentVersion(object):
def __init__(self, name, content, language):
self.name = name
self.content = content
self.language = language
class OrganisationDocumentVersion(object):
def __init__(self, document_version, user):
self.document_version = document_version
self.updator = user
# Table definitions
from connections import engine
from sqlalchemy import ForeignKey, MetaData, Table, Column, String,
Integer, Unicode, DateTime, Date, Time, Boolean
from datetime import datetime
METADATA = MetaData()
USERS_TABLE = Table(users, METADATA,
Column(id, Integer, primary_key=True),
Column(name, Unicode(100), nullable=False),
)
ORGANISATIONS_TABLE = Table(organisations, METADATA,
Column(id, Integer, primary_key=True),
Column(name, Unicode(100), nullable=False),
)
LANGUAGES_TABLE = Table(languages, METADATA,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2), unique=True, nullable=False),
Column(name, Unicode(100), unique=True, nullable=False),
)
DOCUMENTS_TABLE = Table(documents, METADATA,
Column(id, Integer, primary_key=True),
Column(user_id, Integer, ForeignKey('users.id'),
nullable=False),
Column(status, Integer, default=1, nullable=False), # 0 =
deleted, 1 = active
Column(created, DateTime, default=datetime.now()),
)
DOCUMENT_VERSIONS_TABLE = Table(document_versions, METADATA,
Column(id, Integer, primary_key=True),
Column(document_id, Integer, ForeignKey('documents.id'),
nullable=False),
Column(language_id, Integer, ForeignKey('languages.id'),
nullable=False),
Column(name, Unicode(64), nullable=False),
Column(content, Unicode),
Column(version, Integer, default=1, nullable=False),
Column(updated, DateTime, default=datetime.now()),
Column(status, Integer, default=1, nullable=False), # 0 =
deleted, 1 = active
)
ORGANISATION_DOCUMENT_VERSIONS =
Table(organisation_document_versions, METADATA,
Column(organisation_id, Integer, ForeignKey('organisations.id'),
primary_key=True),
Column(document_version_id, Integer,
ForeignKey('document_versions.id'), primary_key=True),
Column(user_id, Integer, ForeignKey('users.id'),
nullable=False),
)
METADATA.create_all(engine)
# Mappers
from models import *
from tables import *
from sqlalchemy.orm import mapper, relation, MapperExtension,
EXT_PASS, EXT_STOP
from datetime import datetime
class VersionedDocumentMapperExtension(MapperExtension):
def before_update(self, mapper, connection, instance):
colvalues = dict([(key, getattr(instance, key)) for key in
instance.c.keys()])
del colvalues['id']
colvalues['version'] = instance.version + 1
colvalues['updated'] = datetime.now()
# create a new version insert
ins = DOCUMENT_VERSIONS_TABLE.insert(colvalues)
connection.execute(ins)
# get old values select, revert old values to current instance
# this prevents making an update for the current instance
sel =
DOCUMENT_VERSIONS_TABLE.select(DOCUMENT_VERSIONS_TABLE.c.id ==
instance.id)
oldvalues = connection.execute(sel).fetchone()
instance.name = oldvalues[name]
instance.content = oldvalues[content]
instance.language_id = oldvalues[language_id]
instance.status = oldvalues[status]
return EXT_STOP
def before_delete(self, mapper, connection, instance):
TODO! Hwo to halt actualy deleting the row?
upd = DOCUMENT_TABLE.update(DOCUMENT_TABLE.c.id ==
instance.document.id)
[sqlalchemy] Re: Separate version table
Thats handy. Where could i get the utils module you're using for Enum datatype? On 28 loka, 23:52, Arnar Birgisson [EMAIL PROTECTED] wrote: On 10/28/07, mmstud [EMAIL PROTECTED] wrote: Thanks there were some good ideas to try. Btw. what does the first def constructor(fun)? It is a decorator I use on mapped classes constructors. It allows me to give keyword arguments to constructors with initial values for any field in class.c (i.e. any mapped columns). cheers, Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Separate version table
On 29 loka, 09:08, [EMAIL PROTECTED] wrote: I dont need history tracking, just revert documents to older ones. that is history, just not timed history. Most optimal would it be, if i can make rows with updated fields only, not to copy whole row... but im afraid setting unchanged field to None would be problematic when retrieving versions. I should retrieve several rows and collect the latest not None fields... just pondering u'll have documents, and then for each document a bunch of versions. Once get it working on simple form, then perhaps trying optimicing and feeding only field, that has changed. Version field automatic update was on my mind lately. Could it be something like this: Column(version, Integer, default=1, nullable=False, onupdate=document_versions.select(document_versions.document_id=c.d ocument_id, order_by=version, order=DESC, limit=1) no idea, what this onupdate is expected to do here? It was meant to be oninsert, not onupdate, but on the other hand, if making system with mapper extension, i think i need to do before_update and increase version number there as well as making a new insert and halt doing update. afaik these things are done with a mapper extension, but i'm not sure its the only way. I'm going to try with mapper extension what would be version field? something that counts the inserts for that particular document_id? Yes, just a counter for every insert to document_id. I could use len(Versions) or get latest DocumentVersion.version+1 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Separate version table
Found enumeration implementations: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Enum http://cheeseshop.python.org/pypi/enum/0.4.3 On 29 loka, 09:04, mmstud [EMAIL PROTECTED] wrote: Thats handy. Where could i get the utils module you're using for Enum datatype? On 28 loka, 23:52, Arnar Birgisson [EMAIL PROTECTED] wrote: On 10/28/07, mmstud [EMAIL PROTECTED] wrote: Thanks there were some good ideas to try. Btw. what does the first def constructor(fun)? It is a decorator I use on mapped classes constructors. It allows me to give keyword arguments to constructors with initial values for any field in class.c (i.e. any mapped columns). cheers, Arnar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: associative table with extra field
That made difference, i think i got it working. I made a little
example for study purposes. From test part you see i can fetch
meaningful information with Country, CountryLanguage and Language
objects. Country has many CountryLanguages (having Language object and
is_primary field), CountryLanguage has only one Country but Language
has many countries. Thank you Barry and Michael.
PS.
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association
might have an update time due to this. I couldnt get association table
work without primary key field definitions, but on tutorial, its not
mentioned.
##
# Tables
##
metadata = MetaData()
countries_table = Table(countries, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(alpha3, String(3)),
Column(name, Unicode(100)),
)
countries_languages = Table(countries_languages, metadata,
# either separate primary key or both link fields as primary_keys
#Column(id, Integer, primary_key=True),
Column(country_id, Integer, ForeignKey('countries.id'),
primary_key=True),
Column(language_id, Integer, ForeignKey('languages.id'),
primary_key=True),
Column(is_primary, Boolean, default=False), # only one can be
default at time
)
languages_table = Table(languages, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(name, Unicode(100)),
)
metadata.create_all(engine)
##
# Models
##
class Country(object):
def __init__(self, alpha2, alpha3, name):
self.alpha2 = alpha2
self.alpha3 = alpha3
self.name = name
def __repr__(self):
return %s(%s, %s, %s) % (self.__class__, self.name,
self.alpha2, self.alpha3)
class Language(object):
def __init__(self, alpha2, name):
self.alpha2 = alpha2
self.name = name
def __repr__(self):
return %s(%s, %s) % (self.__class__, self.name,
self.alpha2)
class CountryLanguage(object):
def __init__(self, language, is_primary = False):
self.language = language
self.is_primary = is_primary
def __repr__(self):
return %s(%s, %s) % (self.__class__, self.language,
self.is_primary)
##
# Mappers
##
mapper(Country, countries_table, properties={
'languages':relation(CountryLanguage, backref='country'), # many
to many
}
)
mapper(CountryLanguage, countries_languages, properties={
'language':relation(Language, backref=countries)
}
)
mapper(Language, languages_table)
##
# Fixtures
##
language_fi = Language('fi', 'Finnish')
language_sv = Language('sv', 'Swedish')
language_es = Language('es', 'Spain')
language_en = Language('en', 'English')
country_fi = Country('fi', 'fin', 'Finland')
country_se = Country('se', 'swe', 'Sweden')
clanguage_fi = CountryLanguage(language_fi, True) # making this mother
language
clanguage_sv = CountryLanguage(language_sv)
country_fi.languages.append(clanguage_fi)
country_fi.languages.append(clanguage_sv)
# more straight form
country_se.languages.append(CountryLanguage(language_sv, True))
# db_sess = Session()
db_sess.save(country_fi)
db_sess.save(country_se)
db_sess.commit()
##
# Test
##
language = db_sess.query(Language).filter_by(name=Finnish).first()
country = db_sess.query(Country).filter_by(alpha2=se).first()
print language
print country
# get all Country and CountryLanguage
countries = db_sess.query(Country)
for country in countries:
for language in country.languages:
print language
# get all Language and Country
languages = db_sess.query(Language)
for language in languages:
for country in language.countries:
print country
# get all CountryLanguage and Country
clanguages = db_sess.query(CountryLanguage)
for clanguage in clanguages:
print clanguage.language
print clanguage.country
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Separate version table
Next design problem for me is version table. I have Document model
with DocumentVersion model, but i dont know how to:
- get the latest version of document
- set creator and updator, automatic behavior for this
- update version number
- fetch thru Document(s) and DocumentVersion(s)
Lets see the code sample i have prepared (raw sketch):
##
# Tables
##
users_table = Table(users, metadata,
Column(id, Integer, primary_key=True),
)
languages_table = Table(languages, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(name, Unicode(100)),
)
documents_table = Table(documents, metadata,
Column(id, Integer, primary_key=True),
# how to get latest version
# Column(latest_version, Integer,
ForeignKey('document_versions.version'), default=1),
Column(created, DateTime, default=datetime.now),
# how to set creator and updator?
Column(creator, Integer, ForeignKey('users.id'),
default=uid_users_id),
)
document_versions_table = Table(document_versions, metadata,
Column(id, Integer, primary_key=True),
Column(document_id, Integer, ForeignKey('documents.id')),
Column(language_id, Integer, ForeignKey('language.id')),
Column(name, Unicode(64)),
Column(content, Unicode),
# how to update DocumentVersion.version ???
Column(version, Integer, default=1,
onupdate=DocumentVersion.version+1),
Column(updated, DateTime, default=datetime.now,
onupdate=datetime.now),
# how to set creator and updator?
Column(updator, Integer, ForeignKey('users.id'),
onupdate=uid_users_id),
)
##
# Models
##
class BaseObject(object):
def __init__(self):
self._repr_ = []
def __repr__(self):
from string import join
str = ', '.join('%s' % (self.__dict__[v]) for v in
self._repr_)
return %s(%s) % (self.__class__, str)
class NameObject(BaseObject):
def __init__(self, name):
BaseObject.__init__(self)
self.name = name
def __repr__(self):
self._repr_ = [name]
return BaseObject.__repr__(self)
class User(BaseObject):
def __init__(self):
BaseObject.__init__(self)
class Language(NameObject):
def __init__(self, alpha2, name):
NameObject.__init__(self, name)
self.alpha2 = alpha2
def __repr__(self):
self._repr_ = [alpha2, name]
return BaseObject.__repr__(self)
class Document(BaseObject):
def __init__(self):
BaseObject.__init__(self)
def __repr__(self):
self._repr_ = [created, creator]
return BaseObject.__repr__(self)
class DocumentVersion(NameObject):
def __init__(self, name, content = , language = Null):
NameObject.__init__(self, name)
self.document = Document()
self.content = content
self.language = language
def __repr__(self):
self._repr_ = [name, document, version]
return BaseObject.__repr__(self)
class UserDocument(object):
pass
class OrganisationDocument(object):
pass
##
# Mappers
##
mapper(User, users_table)
mapper(Document, documents_table, properties={
'user':relation(User, backref=documentversions),
}
)
mapper(DocumentVersion, document_versions_table, properties={
'document':relation(Document, backref=documentversions),
'language':relation(Language, backref=documentversions),
'user':relation(User, backref=documentversions),
}
)
mapper(Language, languages_table)
mapper(UserDocument, user_documents, properties={
'document':relation(DocumentVersion, backref=users),
}
)
mapper(OrganisationDocument, organisation_documents, properties={
'document':relation(DocumentVersion, backref=organisations),
}
)
##
# Fixtures
##
language_fi = Language('fi', 'Finnish')
language_en = Language('en', 'English')
dv = DocumentVersion(My first document, some content...,
language_en)
db_sess.save(dv)
# accidental english
dv = DocumentVersion(Toinen dokumentti, hiukan tavaraa...,
language_en)
db_sess.save(dv)
##
# Tests and examples
##
dv = db_sess.query(DocumentVersion).filter_by(name=Toinen
dokumentti).first()
dv.language = language_fi
dv.save()
print dv # should have version 2
# how to fetch thru Document(s) and their DocumentVersion(s)
# how to set up UserDocument(s)
--~--~-~--~~~---~--~~
You
[sqlalchemy] Re: Separate version table
Thanks there were some good ideas to try. Btw. what does the first def
constructor(fun)?
On 28 loka, 18:00, Arnar Birgisson [EMAIL PROTECTED] wrote:
Hi there,
On 10/28/07, mmstud [EMAIL PROTECTED] wrote:
Next design problem for me is version table. I have Document model
with DocumentVersion model, but i dont know how to:
- get the latest version of document
- set creator and updator, automatic behavior for this
- update version number
- fetch thru Document(s) and DocumentVersion(s)
I didn't read your code thoroughly, but I have a model with some
similarities. Perhaps it will provide some insight. Basically, I'm
dealing with Pages and PageVersions. PageVersions refers to it's
parent Page, but Page also keeps the version number of the latest
version.
Arnar
# encoding: utf-8
import os
from datetime import datetime
from sqlalchemy import *
from softproof import utils
from softproof.json import jsonify_saobject
__meta__ = metadata
def constructor(fun):
def decorated(self, *args, **kw):
assert hasattr(self, 'c')
for key,value in kw.items():
if hasattr(self.c, key):
setattr(self, key, value)
del kw[key]
fun(self, *args, **kw)
return decorated
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):
@constructor
def __init__(self, jobno=None):
if jobno:
self.jobno = jobno
def sortedpages(self):
listcopy = self.pages[:]
listcopy.sort(key=Page.sort_key)
return listcopy
def get_page_by_name(self, pagename):
Finnur síðu með nafnið pagename og skilar henni. Skilar
None ef engin síða hefur
viðkomandi nafn.
Ef pagename er _firstpage_ er skilað viðeigandi síðu (t.d.
kápu ef hún er til)
if len(self.pages) == 0:
return None
if '_firstpage_' == pagename:
for p in self.pages:
if 'KAP' in p.pagename:
return p
return self.pages[0]
for p in self.pages:
if p.pagename == pagename:
return p
return None
def create_page(self, pagename, *args, **kwargs):
p = Page(job=self, pagename=pagename, *args, **kwargs)
return p
def get_path(self):
if self.jobno.startswith('P'):
pg1, pg2, pg3 = self.jobno.split('.')
return os.path.join(pg1, pg1+'.'+pg2, self.jobno)
else:
return os.path.join(self.jobno[:-3]+'000',
self.jobno[:-2]+'00', self.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(md5sum, String(32)),
Column(width, Integer, nullable=False, default=0),
Column(height, Integer, nullable=False, default=0),
ForeignKeyConstraint([jobno,
pagename],[pages.jobno, pages.pagename]))
class PageVersion(object):
@constructor
def __init__(self, page=None, version=None):
if page:
self.page = page
if version:
self.version = version
@property
def filename(self):
if self.version == 1:
return self.page.pagename + '.jpg'
else:
return %s.v%02d.jpg % (self.page.pagename, self.version)
mapper(PageVersion, pageversions)
PageStates = utils.Enum('new', 'approved', 'rejected')
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(current_version, Integer),
Column(status, PageStates, nullable=False,
default=PageStates.new))
class Page(object):
@constructor
def __init__(self, job=None, pagename=None):
if job:
self.job = job
if pagename:
self.pagename = pagename
self.currentversion = PageVersion(self, 1)
self.status = PageStates.new
def add_version(self):
self.currentversion = PageVersion(self, self.currentversion.version+1)
self.status = PageStates.new
comment = self.add_comment()
comment.closeable = False
[sqlalchemy] associative table with extra field
I'm trying to implement associative table with extra field on join
table, but faced problems... what is it, that im doing wrong here,
because i get python error:
.
.
.
2007-10-27 14:58:26,816 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2007-10-27 14:58:26,816 INFO sqlalchemy.engine.base.Engine.0x..d0
COMMIT
ERROR: Internal Python error in the inspect module.
Below is the traceback from this internal error.
Traceback (most recent call last):
File /usr/lib/python2.4/site-packages/IPython/ultraTB.py, line
462, in text
records = _fixed_getinnerframes(etb, context,self.tb_offset)
File /usr/lib/python2.4/site-packages/IPython/ultraTB.py, line
118, in _fixed_getinnerframes
records = inspect.getinnerframes(etb, context)
File inspect.py, line 804, in getinnerframes
framelist.append((tb.tb_frame,) + getframeinfo(tb, context))
File inspect.py, line 768, in getframeinfo
lines, lnum = findsource(frame)
File inspect.py, line 437, in findsource
if pat.match(lines[lnum]): break
IndexError: list index out of range
Unfortunately, your original traceback can not be constructed.
*
countries_table = Table(countries, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(alpha3, String(3)),
Column(name, Unicode(100)),
)
# association table with additional field
countries_languages = Table(countries_languages, metadata,
Column(country_id, Integer, ForeignKey('countries.id')),
Column(language_id, Integer, ForeignKey('languages.id')),
Column(is_primary, Boolean, default=False), # only one can be
default at time
)
languages_table = Table(languages, metadata,
Column(id, Integer, primary_key=True),
Column(alpha2, String(2)),
Column(name, Unicode(100)),
)
class BaseObject(object):
def __init__(self):
self._repr_ = []
def __repr__(self):
from string import join
str = ', '.join('%s' % (self.__dict__[v]) for v in
self._repr_)
return %s(%s) % (self.__class__, str)
class NameObject(BaseObject):
def __init__(self, name):
self.name = name
BaseObject.__init__(self)
def __repr__(self):
self._repr_ = [name]
return BaseObject.__repr__(self)
class Country(NameObject):
def __init__(self, alpha2, alpha3, name):
NameObject.__init__(self, name)
self.alpha2 = alpha2
self.alpha3 = alpha3
class CountryLanguage(object):
def __init__(self, is_primary = False):
self.is_primary = is_primary
class Language(NameObject):
def __init__(self, alpha2, name):
NameObject.__init__(self, name)
self.alpha2 = alpha2
mapper(Country, countries_table, properties={
'post_offices':relation(PostOffice, backref='country'), # one to
many
'languages':relation(CountryLanguage, backref='country'), # many
to many
}
)
mapper(CountryLanguage, countries_languages, properties={
'language':relation(Language, backref=countries)
}
)
mapper(Language, languages_table)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: associative table with extra field
That was a good tip. Now i get:
2007-10-27 22:49:25,454 INFO sqlalchemy.engine.base.Engine.0x..90 {}
2007-10-27 22:49:25,455 INFO sqlalchemy.engine.base.Engine.0x..90
COMMIT
Traceback (most recent call last):
File stdin, line 1, in ?
File fixtures.py, line 4, in ?
from mappers import *
File mappers.py, line 39, in ?
mapper(CountryLanguage, countries_languages, properties={
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/__init__.py, line
516, in mapper
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line
152, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line
414, in _compile_tables
sqlalchemy.exceptions.ArgumentError: Could not assemble any primary
key columns for mapped table 'countries_languages'
I'll provide more full example tomorrow...
Thanks.
On Oct 27, 6:44 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Oct 27, 2007, at 8:02 AM, mmstud wrote:
I'm trying to implement associative table with extra field on join
table, but faced problems... what is it, that im doing wrong here,
because i get python error:
try not using IPython. shells like IDLE etc. always seem to corrupt
things. at the very least, youll get a real stack trace.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
