sfa/storage/alchemy.py

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