hello all :)
from the past years, i've been working on solutions to the "problem"
described by Jean (we are co-workers, and we use twisted and sqlalchemy,
A LOT), and as everybody may already know, it's a very complicated
combination, since we have to do a lot of code around to have a
consistent application.
ok, that's not bad, but ... since we are a team of several developers,
from 'grey haired pythonistas' to 'fishes in a barrel', it's hard to
mantain a quality standard, so we decided to take ALL our codebase based
on twisted and sqlalchemy to give it a try on creating a better
integration between them, specially if you are more acquainted to
asynchronous programming.
*RESULTS*
1. we were able to create an "asynchronous" sqlalchemy, but it relies
on deep object copy, so every promise (or deferred, on twisted's
language) generates an overhead that is not welcome in our standards
(or every programmer with a brain, lol);
2. without deep copy, furthermore we were able to have again a fully
promise version of sqlalchemy, but we had to left aside all the ORM
codebase and work only with low level sqlalchemy. that's a good
result, but again, we'll have a problem with the fishes not using
the ORM.
*THE PROBLEM*
SO, after reading a lot about the internals of sqlalchemy AND tons of
solutions out there (sAsync, etc, etc) we always ended up with the same
problem: thread safety. the orm design of sqlalchemy, specially the
session states, are all designed to be thread safe (Mike, correct me if
i'm wrong), so from there the work might be huge, but we are willing to
work on a solution - specially if our main rdbms, postgres, have one (if
not the best) of dialects implementations in sa. this solution we expect
even to publish for others to use :)
*
**THREAD SAFETY*
ok, as Jean already stated (those are actually my words) that 99,9% of
the programmers who uses sqlalchemy are quite happy with the solutions
it already provides, which are in fact very, very usable. we have no
problem with that.
but, what if we want to go further on this? i mean, we can already
control a lot of things in sqlalchemy. i mean, a lot, really. it is very
flexible in almost all aspects. but ... why it isn't when the subject is
the session state? or it is and we are missing something?
*FINAL THOUGHTS*
the bottom line is not about twisted, just to be clear, but to implement
a non thread safe session state system that can work with async
frameworks (gevent, twisted, tornado) and so on. is that really possible?
my best regards,
richard.
On 09/05/2014 11:23 AM, Jean Marcel Duvoisin Shmidt wrote:
Hi everyone!
I have a more complex and architectural question to make,
it will be a bit long, but I want to make myself clear as I already
have done some research ;D
in our company we have some *really* cool stuff made out of SQLA, we
wrote over its ORM an abstraction to allow us to
build the same schema on the same database but in different 'database
level schema' (from Postgresql), allowing us to
use the same codebase, same database, same architecture and separate
clients content easily. We also managed to build
a EAV (entity, attribute, value) database in top of that, that allow
us to change the database schema any time, any way,
and get it running it without touching the DB... aaannnddd in top of
that we got a Schematics to represent the database
schema and allow us to work as a ORM over the SQLAORM.
Yeh its crazy, but it works, and its really *fast*. We handle
thousands of client in that way, and we are happy with it, not
counting how flexible is the code and the database now.
But here comes the problem. We want to scale it up (not just out), and
we made some tests on the past with SQLA + Twisted
using, Gevent, Twisted, Psycopg. First we managed successfully to
integrate the SQLA-Core + SQLA-ORM + Our EAV-ORM
with twisted using twisted.deferToThread, with works nicely but it is
not exactly what we wanted. This takes out all the purpose
of using twisted in the first place, as we end up with a threaded
model, where queries are running in threads, and we have a
main thread managing all of that. What we really wanted is to make the
app *assyncronous* on top of the ORM.
Than we managed to use assync features of Psycopg with twisted, and in
a similar mode that is done with Alchimia. We where
able to make it work with SQLA-Core. But we found out that the ORM is
completely designed with the synchronous paradigm,
for logical reasons of course - as 99.9% of the users will use it
synchronously, and we though that the best way to make it
work is overwrite the Session, Query, SessionQuery and other classes
to make it work with the deferred concept
(collection, CollectionAdapter, Attributes, and so on).
As an app developer there is no problem to create a session and all
the ambient to handle every request on SQLA.
With provides isolation avoiding any concurrent problems, this can be
done as deferred concept uses concurrent points.
But it is a nightmare to change all SQLA-ORM to handle future
promisses and deferreds.
So, the central point in my question is, does any one had tried it
before? What is the best path to follow to make
the SQLA-ORM work with promises? Any ideas? Is there any work in
progress in that direction? I'll just paste a
really small segment of the code I've been working on, just to you all
get the idea:
class AssyncSessionTransaction(SessionTransaction):
def _connection_for_bind(self, bind):
self._assert_active()
if bind in self._connections:
defrr = defer.Deferred()
defrr.callback(self._connections[bind][0])
return defrr
if self._parent:
defrr = self._parent._connection_for_bind(bind)
if not self.nested:
return defrr
else:
if isinstance(bind, engine.Connection):
if bind.engine in self._connections:
raise sa_exc.InvalidRequestError(
"Session already has a Connection associated
for the "
"given Connection's Engine")
defrr = defer.Deferred()
defrr.callback(bind)
else:
defrr = bind.contextual_connect()
def start_transaction(conn):
if self.session.twophase and self._parent is None:
transaction = conn.begin_twophase()
elif self.nested:
transaction = conn.begin_nested()
else:
transaction = conn.begin()
self._connections[conn] = self._connections[conn.engine] = \
(conn, transaction, conn is not bind)
self.session.dispatch.after_begin(self.session, self, conn)
return conn
defrr.addCallback(start_transaction)
return defrr
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
