3 from sqlalchemy import create_engine, and_
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
12 from sfa.storage.model import RegSlice
13 from sqlalchemy.ext.declarative import declarative_base
14 from sqlalchemy.orm import relationship, backref
17 from sqlalchemy.dialects import postgresql
19 from sqlalchemy import MetaData, Table
20 from sqlalchemy.exc import NoSuchTableError
22 from sqlalchemy import String
23 from sfa.storage.alchemy import dbsession
25 #Dict holding the columns names of the table as keys
26 #and their type, used for creation of the table
27 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'}
29 #Dict with all the specific senslab tables
30 tablenames_dict = {'slice_senslab': slice_table}
32 ##############################
36 SlabBase = declarative_base()
41 class SliceSenslab (SlabBase):
42 __tablename__ = 'slice_senslab'
43 #record_id_user = Column(Integer, primary_key=True)
45 slice_hrn = Column(String,primary_key=True)
46 peer_authority = Column( String,nullable = True)
47 record_id_slice = Column(Integer)
48 record_id_user = Column(Integer)
50 #oar_job_id = Column( Integer,default = -1)
51 #node_list = Column(postgresql.ARRAY(String), nullable =True)
53 def __init__ (self, slice_hrn =None, record_id_slice=None, record_id_user= None,peer_authority=None):
55 self.record_id_slice = record_id_slice
57 self.slice_hrn = slice_hrn
59 self.record_id_user= record_id_user
61 self.peer_authority = peer_authority
65 result="<Record id user =%s, slice hrn=%s, Record id slice =%s ,peer_authority =%s"% \
66 (self.record_id_user, self.slice_hrn, self.record_id_slice, self.peer_authority)
70 def dump_sqlalchemyobj_to_dict(self):
71 dict = {'slice_hrn':self.slice_hrn,
72 'peer_authority':self.peer_authority,
73 'record_id':self.record_id_slice,
74 'record_id_user':self.record_id_user,
75 'record_id_slice':self.record_id_slice, }
80 #class PeerSenslab(SlabBase):
81 #__tablename__ = 'peer_senslab'
82 #peername = Column(String, nullable = False)
83 #peerid = Column( Integer,primary_key=True)
85 #def __init__ (self,peername = None ):
87 #self.peername = peername
91 #result="<Peer id =%s, Peer name =%s" % (self.peerid, self.peername)
96 def __init__(self,config, debug = False):
97 self.sl_base = SlabBase
105 # will be created lazily on-demand
106 self.slab_session = None
107 # the former PostgreSQL.py used the psycopg2 directly and was doing
108 #self.connection.set_client_encoding("UNICODE")
109 # it's unclear how to achieve this in sqlalchemy, nor if it's needed at all
110 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
111 # we indeed have /var/lib/pgsql/data/postgresql.conf where
112 # this setting is unset, it might be an angle to tweak that if need be
113 # try a unix socket first - omitting the hostname does the trick
114 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"%\
115 (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_PORT,dbname)
116 print >>sys.stderr, " \r\n \r\n SLAPOSTGRES INIT unix_url %s" %(unix_url)
117 # the TCP fallback method
118 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
119 (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_HOST,config.SFA_DB_PORT,dbname)
120 for url in [ unix_url, tcp_url ] :
122 self.slab_engine = create_engine (url,echo_pool = l_echo_pool, echo = l_echo)
128 self.slab_engine=None
129 raise Exception,"Could not connect to database"
134 self.slab_engine.execute ("select 1").scalar()
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 #Close connection to database
149 if self.connection is not None:
150 self.connection.close()
151 self.connection = None
156 def exists(self, tablename):
158 Checks if the table specified as tablename exists.
163 metadata = MetaData (bind=self.slab_engine)
164 table=Table (tablename, metadata, autoload=True)
167 except NoSuchTableError:
168 print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES EXISTS NOPE! tablename %s " %(tablename)
172 def createtable(self, tablename ):
174 Creates the specifed table. Uses the global dictionnary holding the tablenames and
179 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)
180 SlabBase.metadata.create_all(slab_engine)
188 from sfa.util.config import Config
190 slab_alchemy= SlabDB(Config())
191 slab_engine=slab_alchemy.slab_engine
192 slab_dbsession=slab_alchemy.session()