3 from sqlalchemy import create_engine
4 from sqlalchemy.orm import sessionmaker
6 from sfa.util.config import Config
7 from sfa.util.sfalogging import logger
9 from sqlalchemy import Column, Integer, String
10 from sqlalchemy import Table, Column, MetaData
11 from sqlalchemy.ext.declarative import declarative_base
13 from sqlalchemy.dialects import postgresql
15 from sqlalchemy import MetaData, Table
16 from sqlalchemy.exc import NoSuchTableError
18 from sqlalchemy import String
20 #Dict holding the columns names of the table as keys
21 #and their type, used for creation of the table
22 slice_table = {'record_id_user':'integer PRIMARY KEY references X ON DELETE \
23 CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1', \
24 'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}
26 #Dict with all the specific senslab tables
27 tablenames_dict = {'slice_senslab': slice_table}
29 ##############################
33 SlabBase = declarative_base()
35 class SliceSenslab (SlabBase):
36 __tablename__ = 'slice_senslab'
37 #record_id_user = Column(Integer, primary_key=True)
39 slice_hrn = Column(String, primary_key=True)
40 peer_authority = Column(String, nullable = True)
41 record_id_slice = Column(Integer)
42 record_id_user = Column(Integer)
44 #oar_job_id = Column( Integer,default = -1)
45 #node_list = Column(postgresql.ARRAY(String), nullable =True)
47 def __init__ (self, slice_hrn =None, record_id_slice=None, \
48 record_id_user= None,peer_authority=None):
50 Defines a row of the slice_senslab table
53 self.record_id_slice = record_id_slice
55 self.slice_hrn = slice_hrn
57 self.record_id_user= record_id_user
59 self.peer_authority = peer_authority
63 """Prints the SQLAlchemy record to the format defined
66 result="<Record id user =%s, slice hrn=%s, Record id slice =%s , \
67 peer_authority =%s"% (self.record_id_user, self.slice_hrn, \
68 self.record_id_slice, self.peer_authority)
72 def dump_sqlalchemyobj_to_dict(self):
73 """Transforms a SQLalchemy record object to a python dictionary.
74 Returns the dictionary.
77 dict = {'slice_hrn':self.slice_hrn,
78 'peer_authority':self.peer_authority,
79 'record_id':self.record_id_slice,
80 'record_id_user':self.record_id_user,
81 'record_id_slice':self.record_id_slice, }
86 def __init__(self,config, debug = False):
87 self.sl_base = SlabBase
95 # will be created lazily on-demand
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 self.slab_engine.execute ("select 1").scalar()
134 Creates a SQLalchemy session. Once the session object is created
135 it should be used throughout the code for all the operations on
136 tables for this given database.
139 if self.slab_session is None:
140 Session = sessionmaker()
141 self.slab_session = Session(bind = self.slab_engine)
142 return self.slab_session
147 Closes connection to database.
150 if self.connection is not None:
151 self.connection.close()
152 self.connection = None
155 def exists(self, tablename):
157 Checks if the table specified as tablename exists.
162 metadata = MetaData (bind=self.slab_engine)
163 table=Table (tablename, metadata, autoload=True)
166 except NoSuchTableError:
167 logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
172 def createtable(self, tablename ):
174 Creates the specifed table. Uses the global dictionnary holding the
175 tablenames and the table schema.
179 logger.debug("SLABPOSTGRES createtable SlabBase.metadata.sorted_tables \
180 %s \r\n engine %s" %(SlabBase.metadata.sorted_tables , slab_engine))
181 SlabBase.metadata.create_all(slab_engine)
185 from sfa.util.config import Config
187 slab_alchemy= SlabDB(Config())
188 slab_engine=slab_alchemy.slab_engine
189 slab_dbsession=slab_alchemy.session()