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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.