sfa/storage/alchemy.py

   1 from types import StringTypes
   2
   3 from sqlalchemy import create_engine
   4 from sqlalchemy.orm import sessionmaker
   5
   6 from sqlalchemy import Column, Integer, String
   7 from sqlalchemy.orm import relationship, backref
   8 from sqlalchemy import ForeignKey
   9
  10 from sfa.util.sfalogging import logger
  11
  12 # this module is designed to be loaded when the configured db server is reachable
  13 # OTOH persistentobjs can be loaded from anywhere including the client-side
  14
  15 class Alchemy:
  16
  17     def __init__ (self, config):
  18         dbname="sfa"
  19         # will be created lazily on-demand
  20         self._session = None
  21         # the former PostgreSQL.py used the psycopg2 directly and was doing
  22         #self.connection.set_client_encoding("UNICODE")
  23         # it's unclear how to achieve this in sqlalchemy, nor if it's needed at all
  24         # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
  25         # we indeed have /var/lib/pgsql/data/postgresql.conf where
  26         # this setting is unset, it might be an angle to tweak that if need be
  27         # try a unix socket first - omitting the hostname does the trick
  28         unix_desc = "postgresql+psycopg2://%s:%s@:%s/%s"%\
  29             (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_PORT,dbname)
  30         # the TCP fallback method
  31         tcp_desc = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
  32             (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_HOST,config.SFA_DB_PORT,dbname)
  33         for engine_desc in [ unix_desc, tcp_desc ] :
  34             try:
  35                 self.engine = create_engine (engine_desc)
  36                 self.check()
  37                 return
  38             except:
  39                 pass
  40         self.engine=None
  41         raise Exception,"Could not connect to database"
  42
  43
  44     # expects boolean True: debug is ON or False: debug is OFF
  45     def debug (self, echo):
  46         self.engine.echo=echo
  47
  48     def check (self):
  49         self.engine.execute ("select 1").scalar()
  50
  51     def session (self):
  52         if self._session is None:
  53             Session=sessionmaker ()
  54             self._session=Session(bind=self.engine)
  55         return self._session
  56
  57     def close_session (self):
  58         if self._session is None: return
  59         self._session.close()
  60         self._session=None
  61
  62     def commit (self):
  63         self.session().commit()
  64
  65     def insert (self, stuff, commit=False):
  66         if isinstance (stuff,list):
  67             self.session().add_all(stuff)
  68         else:
  69             self.session().add(obj)
  70
  71     # for compat with the previous PostgreSQL stuff
  72     def update (self, record):
  73         self.commit()
  74
  75     def remove (self, record):
  76         self.delete(record)
  77         self.commit()
  78
  79 ####################
  80 from sfa.util.config import Config
  81
  82 alchemy=Alchemy (Config())
  83 engine=alchemy.engine
  84 dbsession=alchemy.session()
  85