On 28.03.2010, at 17:58, Michael Bayer wrote:
On Mar 28, 2010, at 11:38 AM, paul wrote:
Hi,
Sorry for the last mail, which i sent in error, before finishing it.
If you(Michael) still have it in the filter, please just reject it.
I am working on a notification framework, which saves the dirty, new
and deleted states of objects in sessions to an Eventlog at commit
time.
The target is to have asynchronous agents listening to table changes
and a sqla extension to notify them when data is written to db.
The notification sender core code is written as a SessionExtension at
the moment.
The design uses the postgres NOTIFY / listen statements, and a table
(event_log) for carrying the per commit information of primary id's
which have added/deleted/modified. I know that NOTIFY is not sql
standard, but many dbs have some sort of notification capability.
So i
think a general case is abstractable from this db agnostic proposal
(=pg's NOTIFY).
It is even conceivable to send the notification event through OS
services, while still keep the notification data in the db.
I have already implemented this simple protocol:
before_commit(self, session):
event_data=# get all pks for session.['dirty', 'new', 'deleted']
for each x of [dirty', 'new', 'deleted']
session.add(Event( data=event_data[x]))
session.add(Event(data=event_data)) # gener
for each object type:
for each notification type: #= one of (dirty', 'new', 'deleted')
self.pending_notifications = [self.get_table_name(class_)]
^^^^
First Question: How does SQLAlchemy map the call to
session.query(MappedObject) to a MetaData.tables[x]. Is there any way
to do this without using a custom mapper fuction ? I would prefer
using the same mechanism as SQLAlchemy
self.pending_notifications += General Event for table
after_commit:
send Notifications on separate db agnostic connection (in pg i need
to set a different isolation level)
My second concern is about what session.execute("UPDATE table_x set
pop = ''newval") actually does.
Is the sql statement parsed for table names? My interest would be in
marking the objects which are changed by this update as dirty. So
what
i am really looking for is
a method to generate
Note that i use pk=Primary Key
'SELECT pk1, pk2 ...., change_field from tablename where prop=x'
from an update:
'UPDATE tablename set change_field=... where prop=x'
This would enable me to generate a dirty pk list from any (text) sql
statement accepted by sqla.
Please let me know if you feel that this design is not the 'sqla way
to do things'. I want to OS the code, but i wanted to get an short
feedback if this is of any use for anyone out there. So please
comment :)
some details here are unclear, but it appears you are attempting to
save Event objects to the database in response to flushed changes,
and also sending out notifications. A SessionExtension is an
appropriate place to do that. If I were writing a "generic change
event" system, I would probably use rabbitmq instead of NOTIFY. If
I wanted to also have a log of changes in the database, I'd probably
forego the usage of Session.add() and just do a series of INSERT
statements using executemany() syntax, as it would have the minimal
performance/complexity impact.
Thanks for the pointer to rabbitmq!
To get a Table from a mapped class you can use
class_mapper(MappedClass).mapped_table. A Session.execute() given
a literal string-based statement does not parse the string - usually
its a given that high-level ORM enabled features, like notifying
listeners of newly changes objects, aren't going to work if the user
drops down to raw SQL.
That last point leads to naturally towards, if you really want to
NOTIFY when any changes whatsoever occur on a table, then you should
be using triggers.
Yes, but i plan to introduce these at table creation time, and only to
write them to db when the lib user adds a specific ddl extention. DDLs
would have to be written for all dialects available, so this is a bit
far fetched.
At the moment I just try to stick with sqla for change management, and
just abstract the NOTIFY concept. The event log can also be omitted,
so basically there are 3 Levels (in increasing order of intrusiveness
into the data model)
1) pure change notification = here each listener must maintain it's
own table state management, no modification to the db model
whatsoever. Just a 'wake up' event is sent
2) change notification + event_log = the changeset is published
through an additional table, but the overall datamodel is not affected
3) trigger based change notification = the changeset is maintained by
triggers, and their definition for each DB is handled by sqla ddl
extensions on creation time
Postgresql also allows stored procedures to be written in many
languages including Python so even using rabbitmq for notification
is quite doable entirely with triggers.
Well, i was thinking about a DB table notification lib which can be
used for rapid prototyping of listening agents around db tables, using
sqla. I think about sending events to rabbitmq/other event dispatchers
not as primary goal. The design based on intrusion level aims at
attaching those agents to 'foreign' dbs without interference with the
db model. sqla is ideal for me because of the portable(= many dialects
for) reflection.
Thanks for your fast reply, and ... great lib!
paul
paul
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com
.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com
.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
