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, DateTime
10 from sqlalchemy import Table, Column, MetaData, join, ForeignKey
11 import sfa.storage.model as model
13 from sqlalchemy.ext.declarative import declarative_base
14 from sqlalchemy.orm import relationship, backref
17 from sqlalchemy import MetaData, Table
18 from sqlalchemy.exc import NoSuchTableError
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 CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1', 'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}
24 #Dict with all the specific senslab tables
25 tablenames_dict = {'slice_senslab': slice_table}
27 ##############################
31 SlabBase = declarative_base()
36 class SlabSliceDB (SlabBase):
37 __tablename__ = 'slice_senslab'
38 record_id_user = Column(Integer, primary_key=True)
39 oar_job_id = Column( Integer,default = -1)
40 record_id_slice = Column(Integer)
41 slice_hrn = Column(String,nullable = False)
43 def __init__ (self, slice_hrn =None, oar_job_id=None, record_id_slice=None, record_id_user= None):
45 self.record_id_slice = record_id_slice
47 self.slice_hrn = slice_hrn
49 self.oar_job_id = oar_job_id
51 self.slice_hrn = slice_hrn
53 self.record_id_user= record_id_user
56 result="<Record id user =%s, slice hrn=%s, oar_job id=%s,Record id slice =%s" % \
57 (self.record_id_user, self.slice_hrn, self.oar_job_id, self.record_id_slice)
65 def __init__(self,config):
66 self.sl_base = SlabBase
69 # will be created lazily on-demand
70 self.slab_session = None
71 # the former PostgreSQL.py used the psycopg2 directly and was doing
72 #self.connection.set_client_encoding("UNICODE")
73 # it's unclear how to achieve this in sqlalchemy, nor if it's needed at all
74 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
75 # we indeed have /var/lib/pgsql/data/postgresql.conf where
76 # this setting is unset, it might be an angle to tweak that if need be
77 # try a unix socket first - omitting the hostname does the trick
78 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"%\
79 (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_PORT,dbname)
80 print >>sys.stderr, " \r\n \r\n SLAPOSTGRES INIT unix_url %s" %(unix_url)
81 # the TCP fallback method
82 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
83 (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_HOST,config.SFA_DB_PORT,dbname)
84 for url in [ unix_url, tcp_url ] :
86 self.slab_engine = create_engine (url,echo_pool=True,echo=True)
93 raise Exception,"Could not connect to database"
96 self.slab_engine.execute ("select 1").scalar()
100 if self.slab_session is None:
101 Session=sessionmaker ()
102 self.slab_session=Session(bind=self.slab_engine)
103 return self.slab_session
108 #Close connection to database
110 if self.connection is not None:
111 self.connection.close()
112 self.connection = None
117 def exists(self, tablename):
119 Checks if the table specified as tablename exists.
124 metadata = MetaData (bind=self.slab_engine)
125 table=Table (tablename, metadata, autoload=True)
128 except NoSuchTableError:
129 print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES EXISTS NOPE! tablename %s " %(tablename)
133 def createtable(self, tablename ):
135 Creates the specifed table. Uses the global dictionnary holding the tablenames and
140 print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES createtable SlabBase.metadata.sorted_tables %s \r\n engine %s" %(SlabBase.metadata.sorted_tables , slab_engine)
141 SlabBase.metadata.create_all(slab_engine)
145 def find (self, name = None, filter_dict = None):
147 filter_statement = "and_(SlabSliceDB."
148 for k in filter_dict:
149 filter_statement += str(k)+ "==" + str(filter_dict[l])
150 filter_statement +=')'
151 print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES find filter_statement %s"%(filter_statement)
152 slab_dbsession.query(SlabSliceDB).filter(filter_statement)
157 from sfa.util.config import Config
159 slab_alchemy= SlabDB(Config())
160 slab_engine=slab_alchemy.slab_engine
161 slab_dbsession=slab_alchemy.session()