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)
connection.execute(upd, status = 0)
instance.document.status = 0
return EXT_STOP
versioned_document_ext = VersionedDocumentMapperExtension()
#versioned_organisation_document_ext =
VersionedOrganisationDocumentMapperExtension()
mapper(User, USERS_TABLE, properties={
'documents':relation(Document, backref="creator"),
'organisation_document_versions':relation(OrganisationDocumentVersion,
backref="updator"),
}
)
mapper(Organisation, ORGANISATIONS_TABLE, properties={
'document_versions':relation(OrganisationDocumentVersion,
backref="organisation"),
}
)
mapper(Language, LANGUAGES_TABLE, properties={
'document_versions':relation(DocumentVersion, backref="language"),
}
)
mapper(Document, DOCUMENTS_TABLE, allow_column_override=True,
properties={
'versions':relation(DocumentVersion, backref="document"),
}
)
mapper(DocumentVersion, DOCUMENT_VERSIONS_TABLE,
extension=versioned_document_ext, properties={})
mapper(OrganisationDocumentVersion, ORGANISATION_DOCUMENT_VERSIONS,
allow_column_override=True, properties={})
################################################################################
# Example
################################################################################
from dbsessions import db_sess
from models import *
from mappers import *
user = db_sess.query(User).filter_by(id=1).first()
user_document = user.documents[0]
#user_document.versions
#user_document.first()
document_version = user_document.latest()
document_version.name = "new name"
document_version.language = language
db_sess.save(document_version)
db_sess.commit()
db_sess.refresh(user_document) # refreshing only user object does not
work
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
