Hello everybody,
I am currently pulling my hair out because I have a solution that I think
should work on the database side (albeit I don't like it), but I can't
figure out how to do this with sqlalchemy.
My current goal is to manage (long-lived) locks on copy-on-write
hierarchical data that is stored in Oracle RDBMS.
Of course the code must guaranteee uniqueness. Funny thing is that I can
have multiple locks on the same object as it can be shared due to the
shallow copy nature of the application.
The weird schema is something like this:
CREATE TABLE item_locks (
lock_id VARCHAR(36) NOT NULL,
context_id INTEGER NOT NULL,
target_path VARCHAR(2048) NOT NULL,
target_id INTEGER,
expiration_time DATETIME,
owner_id INTEGER NOT NULL,
PRIMARY KEY (lock_id),
CONSTRAINT item_locks_unique UNIQUE (target_path),
FOREIGN KEY(id) REFERENCES taggable (id) ON DELETE CASCADE,
FOREIGN KEY(target_id) REFERENCES taggable (id) ON DELETE CASCADE,
FOREIGN KEY(owner_id) REFERENCES users (id) DEFERRABLE INITIALLY
deferred
);
CREATE INDEX ix_taggable_locks_target_id ON taggable_locks (target_id);
Yes I know.
My code is basically working fine but I ran into one limitation:
target_path is actually a comma separated list of integers like "5,7,8"
which identify the locked object (id 8, same as target_id) as seen from the
context object (id 5, same as context_id).
Given the copy on write nature I have to propagate the locks to the actual
writable object though. context_id is always writable but the children are
reused across different contexts. A COW operation will create a new
instance that is also writable, so the lock may move from "5,7,8" to "9, 8"
with id 9 being the new copy of the object with id 7. (For completeness:
when the object is actually written by the lock holder, the lock would
transform to something like "10", with context_id = target_id = 10 as well).
What I forgot though: Usually the copy on write created objects are created
within the ORM session which means that I actually got target_path =
"None,8" when actually using the code in the intended setting. That's one
thing I did not have in mind... :-(
I can hack around this by code like this:
from sqlalchemy import create_engine, Column, Integer, String, event
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class Person(Base):
__tablename__ = "persons"
id = Column(Integer, primary_key=True)
name = Column(String)
class Bundle(Base):
__tablename__ = "bundle"
id = Column(Integer, primary_key=True)
member_ids = Column(String, unique=True)
def __init__(self, members):
self.members = members
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
@event.listens_for(Session, "after_flush")
def receive_after_flush(session, flush_context):
items = set(session.dirty) | set(session.new)
for item in items:
if isinstance(item, Bundle):
session.execute(Bundle.__table__
.update()
.values(member_ids=",".join(str(x.id) for x in item.members
))
.where(Bundle.id == item.id))
session = Session()
persons = [Person(name=name) for name in ["Martin", "Michael", "Fabian"]]
for person in persons:
session.add(person)
session.commit()
bundle = Bundle(persons[1:])
session.add(bundle)
session.commit()
This comes with its share of new problems (like updating bundle.members
after the initial commit does not yield an update).
This frustrates me because SQLAlchemy internally has all the knowledge for
topological sorting of operations, but I can not figure out how to use the
goodness to implement the schema that I would like to have.
I have a faint hope that Mike will come up with a recipe how I can build my
own relationship like property so that the code can just be changed to
class Bundle(Base):
# ...
members = csv_relationship(Person, "id")
BTW: For extra fun the lock propagation work on the target_path column in
before_flush.
Any hints how to mount this with SQLAlchemy greatly appreciated!
Greetings, Torsten
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
