On Mon, Mar 3, 2008 at 9:46 AM, Jonathan Vanasco <[EMAIL PROTECTED]> wrote: > > the app that I'm working on has some constraints that are implemented > under mod_perl , php & python. > > i'm trying to figure out how to do this in pylons/sa -- hoping some > people can point me in the right direction > > basically, I use 5 database connections -- each with different > permissions / roles > - config > readonly from main db on startup, populates a class with db > constants... the connection is not needed after initial loading. > - readonly > only reads from main db, active for every request > - write > writeable to main db , active on certain requests > - logger > does quick begin->log->end on visit metrics > - session > read/write on session-db for user persistance > > sometimes these have the same db credentials... but i always abstract > stuff like this at the beginning so i can scale & not worry about > permissions > > i usually accomplish this via a db factory class that returns a > connection, and has a request-start + request-cleanup hook for > transactional issues. > > is anyone else doing something similar? if so, how have you laid this > all out? i'm trying to wrap my head around the layout of pylons.
You'll need a different engine for each situation. "Using SQLAlchemy with Pylons" shows how to define multiple engines in the config file and pass them to the model. http://wiki.pylonshq.com/display/pylonsdocs/Using+SQLAlchemy+with+Pylons An engine is a pool of connections that's managed for you. You can use engine options to specify how many idle connections should be left open for better performance. That number should be higher for engines used during every request, and lower for engines used only occasionally. You can define all tables in a single metadata unless you're doing something really wacky. Now you have a choice. 1) Don't bind any engines to the session or metadata. Instead, pass the appropriate engine to every database-accessing method using the bind= argument. This is simple and non-magical, but it requires you to be verbose about engines. 2) Bind the primary (readonly) engine to the session. All operations that use a different engine will need the bind= argument. 3) If the "readonly", "logger", and "session" engines access non-overlapping tables, you can use the binds= argument in the sessionmaker to bind each table to its primary engine. Then you'll have to pass the engine to db-access methods only when contradicting this scheme; e.g., when using the "write" engine. 4) At any time you can use Session.reconfigure() or Session().bind (?) to rebind tables to a different engine; e.g., when using the 'write' engine. This is local to the current web request. Your "configure" engine is a special case because you use it only at startup. Your model.init_model() function can use it to populate some Python object, then let it go out of scope and it will be closed. You can put this object in a module global, under pylons.g, or someplace like that. (Don't use a module global if you have multiple instances of the same Pylons app in one OS process, and the constants are different between the two instances. This is a rare situation, however.) You could have problems if you access the same database records in the same session via different engines. For instance, if you load an ORM record into one variable via 'readonly', then load it into a different variable via 'write' and modify it, SQLAlchemy may not realize they're the same record. (Because for all it knows, they're in different databases.) You'd have to ask on the sqlalchemy list about what exactly is safe to do and what isn't. -- Mike Orr <[EMAIL PROTECTED]> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
