+from types import StringTypes
+
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
-from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
-from sqlalchemy.orm import relationship, backref
from sqlalchemy import ForeignKey
from sfa.util.sfalogging import logger
-Base=declarative_base()
+# this module is designed to be loaded when the configured db server is reachable
+# OTOH model can be loaded from anywhere including the client-side
class Alchemy:
def __init__ (self, config):
- dbname="sfa"
+ dbname = "sfa"
# will be created lazily on-demand
self._session = None
# the former PostgreSQL.py used the psycopg2 directly and was doing
# we indeed have /var/lib/pgsql/data/postgresql.conf where
# this setting is unset, it might be an angle to tweak that if need be
# try a unix socket first - omitting the hostname does the trick
- unix_desc = "postgresql+psycopg2://%s:%s@:%s/%s"%\
+ unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"%\
(config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_PORT,dbname)
# the TCP fallback method
- tcp_desc = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
+ tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
(config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_HOST,config.SFA_DB_PORT,dbname)
- for engine_desc in [ unix_desc, tcp_desc ] :
+ for url in [ unix_url, tcp_url ] :
try:
- self.engine = create_engine (engine_desc)
+ logger.debug("Trying db URL %s"%url)
+ self.engine = create_engine (url)
self.check()
+ self.url=url
return
except:
pass
self.engine=None
- raise Exception,"Could not connect to database"
-
+ raise Exception,"Could not connect to database %s as %s with psycopg2"%(dbname,config.SFA_DB_USER)
+
# expects boolean True: debug is ON or False: debug is OFF
def debug (self, echo):
def check (self):
self.engine.execute ("select 1").scalar()
- # create schema
- # warning: need to have all Base subclass loaded for this to work
- def create_schema (self):
- return Base.metadata.create_all(self.engine)
-
- # does a complete wipe of the schema, use with care
- def drop_schema (self):
- return Base.metadata.drop_all(self.engine)
-
- def session (self):
+ def global_session (self):
if self._session is None:
Session=sessionmaker ()
self._session=Session(bind=self.engine)
+ logger.debug('alchemy.global_session created session %s'%self._session)
return self._session
- def close_session (self):
+ def close_global_session (self):
if self._session is None: return
+ logger.debug('alchemy.close_global_session %s'%self._session)
self._session.close()
self._session=None
- def commit (self):
- self.session().commit()
-
- def insert (self, stuff, commit=False):
- if isinstance (stuff,list):
- self.session().add_all(stuff)
- else:
- self.session().add(obj)
-
- # for compat with the previous PostgreSQL stuff
- def update (self, record):
- self.commit()
-
- def remove (self, record):
- del record
- self.commit()
+ # create a dbsession to be managed separately
+ def session (self):
+ Session=sessionmaker()
+ session=Session (bind=self.engine)
+ logger.debug('alchemy.session created session %s'%session)
+ return session
-####################
-# dicts vs objects
-####################
-# historically the front end to the db dealt with dicts, so the code was only dealing with dicts
-# sqlalchemy however offers an object interface, meaning that you write obj.id instead of obj['id']
-# which is admittedly much nicer
-# however we still need to deal with dictionaries if only for the xmlrpc layer
-#
-# here are a few utilities for this
-#
-# (*) first off, when an old pieve of code needs to be used as-is, if only temporarily, the simplest trick
-# is to use obj.__dict__
-# this behaves exactly like required, i.e. obj.__dict__['field']='new value' does change obj.field
-# however this depends on sqlalchemy's implementation so it should be avoided
-#
-# (*) second, when an object needs to be exposed to the xmlrpc layer, we need to convert it into a dict
-# remember though that writing the resulting dictionary won't change the object
-# essentially obj.__dict__ would be fine too, except that we want to discard alchemy private keys starting with '_'
-# 2 ways are provided for that:
-# . dict(obj)
-# . obj.todict()
-# the former dict(obj) relies on __iter__() and next() below, and does not rely on the fields names
-# although it seems to work fine, I've found cases where it issues a weird python error that I could not get right
-# so the latter obj.todict() seems more reliable but more hacky as is relies on the form of fields, so this can probably be improved
-#
-# (*) finally for converting a dictionary into an sqlalchemy object, we provide
-# obj.set_from_dict(dict)
-
-from sqlalchemy.orm import object_mapper
-class AlchemyObj:
- def __iter__(self):
- self._i = iter(object_mapper(self).columns)
- return self
- def next(self):
- n = self._i.next().name
- return n, getattr(self, n)
- def todict (self):
- d=self.__dict__
- keys=[k for k in d.keys() if not k.startswith('_')]
- return dict ( [ (k,d[k]) for k in keys ] )
- def set_from_dict (self, d):
- for (k,v) in d.iteritems():
- setattr(self,k,v)
+ def close_session (self, session):
+ logger.debug('alchemy.close_session closed session %s'%session)
+ session.close()
####################
from sfa.util.config import Config
alchemy=Alchemy (Config())
engine=alchemy.engine
-dbsession=alchemy.session()
+global_dbsession=alchemy.global_session()