1 from sqlalchemy import create_engine
2 from sqlalchemy.orm import sessionmaker
4 from sfa.util.config import Config
5 from sfa.util.sfalogging import logger
7 from sqlalchemy import Column, Integer, String
8 from sqlalchemy import Table, MetaData
9 from sqlalchemy.ext.declarative import declarative_base
11 from sqlalchemy.dialects import postgresql
13 from sqlalchemy.exc import NoSuchTableError
16 #Dict holding the columns names of the table as keys
17 #and their type, used for creation of the table
18 slice_table = {'record_id_user': 'integer PRIMARY KEY references X ON DELETE \
19 CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1', \
20 'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}
22 #Dict with all the specific senslab tables
23 tablenames_dict = {'slab_xp': slice_table}
26 SlabBase = declarative_base()
30 class SenslabXP (SlabBase):
31 """ SQL alchemy class to manipulate slice_senslab table in
35 __tablename__ = 'slab_xp'
38 slice_hrn = Column(String)
39 job_id = Column(Integer, primary_key = True)
40 end_time = Column(Integer, nullable = False)
43 #oar_job_id = Column( Integer,default = -1)
44 #node_list = Column(postgresql.ARRAY(String), nullable =True)
46 def __init__ (self, slice_hrn =None, job_id=None, end_time=None):
48 Defines a row of the slice_senslab table
51 self.slice_hrn = slice_hrn
55 self.end_time = end_time
59 """Prints the SQLAlchemy record to the format defined
62 result = "<slab_xp : slice_hrn = %s , job_id %s end_time = %s" \
63 %(self.slice_hrn, self.job_id, self.end_time)
70 """ SQL Alchemy connection class.
73 def __init__(self, config, debug = False):
74 self.sl_base = SlabBase
83 self.slab_session = None
84 # the former PostgreSQL.py used the psycopg2 directly and was doing
85 #self.connection.set_client_encoding("UNICODE")
86 # it's unclear how to achieve this in sqlalchemy, nor if it's needed
88 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
89 # we indeed have /var/lib/pgsql/data/postgresql.conf where
90 # this setting is unset, it might be an angle to tweak that if need be
91 # try a unix socket first - omitting the hostname does the trick
92 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"% \
93 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, \
94 config.SFA_DB_PORT, dbname)
96 # the TCP fallback method
97 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"% \
98 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, config.SFA_DB_HOST, \
99 config.SFA_DB_PORT, dbname)
100 for url in [ unix_url, tcp_url ] :
102 self.slab_engine = create_engine (url, echo_pool = \
103 l_echo_pool, echo = l_echo)
109 self.slab_engine = None
110 raise Exception, "Could not connect to database"
115 """ Cehck if a table exists by trying a selection
119 self.slab_engine.execute ("select 1").scalar()
124 Creates a SQLalchemy session. Once the session object is created
125 it should be used throughout the code for all the operations on
126 tables for this given database.
129 if self.slab_session is None:
130 Session = sessionmaker()
131 self.slab_session = Session(bind = self.slab_engine)
132 return self.slab_session
134 def close_session(self):
136 Closes connection to database.
139 if self.slab_session is None: return
140 self.slab_session.close()
141 self.slab_session = None
144 def exists(self, tablename):
146 Checks if the table specified as tablename exists.
151 metadata = MetaData (bind=self.slab_engine)
152 table = Table (tablename, metadata, autoload=True)
155 except NoSuchTableError:
156 logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
161 def createtable(self):
163 Creates all the table sof the engine.
164 Uses the global dictionnary holding the tablenames and the table schema.
168 logger.debug("SLABPOSTGRES createtable SlabBase.metadata.sorted_tables \
169 %s \r\n engine %s" %(SlabBase.metadata.sorted_tables , slab_engine))
170 SlabBase.metadata.create_all(slab_engine)
175 slab_alchemy = SlabDB(Config())
176 slab_engine = slab_alchemy.slab_engine
177 slab_dbsession = slab_alchemy.session()