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 = {'slice_senslab': slice_table}
26 SlabBase = declarative_base()
28 class SliceSenslab (SlabBase):
29 """ SQL alchemy class to manipulate slice_senslab table in
33 __tablename__ = 'slice_senslab'
34 #record_id_user = Column(Integer, primary_key=True)
36 slice_hrn = Column(String, primary_key=True)
37 peer_authority = Column(String, nullable = True)
38 record_id_slice = Column(Integer)
39 record_id_user = Column(Integer)
41 #oar_job_id = Column( Integer,default = -1)
42 #node_list = Column(postgresql.ARRAY(String), nullable =True)
44 def __init__ (self, slice_hrn =None, record_id_slice=None, \
45 record_id_user= None,peer_authority=None):
47 Defines a row of the slice_senslab table
50 self.record_id_slice = record_id_slice
52 self.slice_hrn = slice_hrn
54 self.record_id_user = record_id_user
56 self.peer_authority = peer_authority
60 """Prints the SQLAlchemy record to the format defined
63 result = "<Record id user =%s, slice hrn=%s, Record id slice =%s , \
64 peer_authority =%s"% (self.record_id_user, self.slice_hrn, \
65 self.record_id_slice, self.peer_authority)
69 def dump_sqlalchemyobj_to_dict(self):
70 """Transforms a SQLalchemy record object to a python dictionary.
71 Returns the dictionary.
74 dump_dict = {'slice_hrn':self.slice_hrn,
75 'peer_authority':self.peer_authority,
76 'record_id':self.record_id_slice,
77 'record_id_user':self.record_id_user,
78 'record_id_slice':self.record_id_slice, }
83 """ SQL Alchemy connection class.
86 def __init__(self, config, debug = False):
87 self.sl_base = SlabBase
96 self.slab_session = None
97 # the former PostgreSQL.py used the psycopg2 directly and was doing
98 #self.connection.set_client_encoding("UNICODE")
99 # it's unclear how to achieve this in sqlalchemy, nor if it's needed
101 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
102 # we indeed have /var/lib/pgsql/data/postgresql.conf where
103 # this setting is unset, it might be an angle to tweak that if need be
104 # try a unix socket first - omitting the hostname does the trick
105 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"% \
106 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, \
107 config.SFA_DB_PORT, dbname)
109 # the TCP fallback method
110 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"% \
111 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, config.SFA_DB_HOST, \
112 config.SFA_DB_PORT, dbname)
113 for url in [ unix_url, tcp_url ] :
115 self.slab_engine = create_engine (url, echo_pool = \
116 l_echo_pool, echo = l_echo)
122 self.slab_engine = None
123 raise Exception, "Could not connect to database"
128 """ Cehck if a table exists by trying a selection
132 self.slab_engine.execute ("select 1").scalar()
137 Creates a SQLalchemy session. Once the session object is created
138 it should be used throughout the code for all the operations on
139 tables for this given database.
142 if self.slab_session is None:
143 Session = sessionmaker()
144 self.slab_session = Session(bind = self.slab_engine)
145 return self.slab_session
147 def close_session(self):
149 Closes connection to database.
152 if self.slab_session is None: return
153 self.slab_session.close()
154 self.slab_session = None
157 def exists(self, tablename):
159 Checks if the table specified as tablename exists.
164 metadata = MetaData (bind=self.slab_engine)
165 table = Table (tablename, metadata, autoload=True)
168 except NoSuchTableError:
169 logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
174 def createtable(self):
176 Creates all the table sof the engine.
177 Uses the global dictionnary holding the tablenames and the table schema.
181 logger.debug("SLABPOSTGRES createtable SlabBase.metadata.sorted_tables \
182 %s \r\n engine %s" %(SlabBase.metadata.sorted_tables , slab_engine))
183 SlabBase.metadata.create_all(slab_engine)
188 slab_alchemy = SlabDB(Config())
189 slab_engine = slab_alchemy.slab_engine
190 slab_dbsession = slab_alchemy.session()