2 import psycopg2.extensions
3 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
4 # UNICODEARRAY not exported yet
5 psycopg2.extensions.register_type(psycopg2._psycopg.UNICODEARRAY)
6 from sfa.util.config import Config
7 from sfa.util.table import SfaTable
8 from sfa.util.sfalogging import logger
9 # allow to run sfa2wsdl if this is missing (for mac)
12 except: print >> sys.stderr, "WARNING, could not import pgdb"
14 #Dict holding the columns names of the table as keys
15 #and their type, used for creation of the table
16 slice_table = {'record_id_user':'integer PRIMARY KEY references sfa ON DELETE CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1', 'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}
18 #Dict with all the specific senslab tables
19 tablenames_dict = {'slice': slice_table}
23 self.config = Config()
24 self.connection = None
27 if self.connection is None:
28 # (Re)initialize database connection
31 # Try UNIX socket first
32 self.connection = psycopg2.connect(user = 'sfa',
35 #self.connection = psycopg2.connect(user = self.config.SFA_PLC_DB_USER,
36 #password = self.config.SFA_PLC_DB_PASSWORD,
37 #database = self.config.SFA_PLC_DB_NAME)
38 except psycopg2.OperationalError:
40 self.connection = psycopg2.connect(user = self.config.SFA_PLC_DB_USER,
41 password = self.config.SFA_PLC_DB_PASSWORD,
42 database = self.config.SFA_PLC_DB_NAME,
43 host = self.config.SFA_PLC_DB_HOST,
44 port = self.config.SFA_PLC_DB_PORT)
45 self.connection.set_client_encoding("UNICODE")
47 self.connection = pgdb.connect(user = self.config.SFA_PLC_DB_USER,
48 password = self.config.SFA_PLC_DB_PASSWORD,
49 host = "%s:%d" % (self.config.SFA_PLC_DB_HOST, self.config.SFA_PLC_DB_PORT),
50 database = self.config.SFA_PLC_DB_NAME)
52 (self.rowcount, self.description, self.lastrowid) = \
55 return self.connection.cursor()
57 #Close connection to database
59 if self.connection is not None:
60 self.connection.close()
61 self.connection = None
63 def exists(self, tablename):
65 Checks if the table specified as tablename exists.
69 sql = "SELECT * from pg_tables"
71 rows = mark.fetchall()
73 labels = [column[0] for column in mark.description]
74 rows = [dict(zip(labels, row)) for row in rows]
76 rows = filter(lambda row: row['tablename'].startswith(tablename), rows)
81 def createtable(self, tablename ):
83 Creates the specifed table. Uses the global dictionnary holding the tablenames and
89 if tablename not in tablenames_dict:
90 logger.error("Tablename unknown - creation failed")
93 T = tablenames_dict[tablename]
96 tmp = str(k) +' ' + T[k]
99 end_of_statement = ",".join(tablelist)
101 statement = "CREATE TABLE " + tablename + " ("+ end_of_statement +");"
103 #template = "CREATE INDEX %s_%s_idx ON %s (%s);"
104 #indexes = [template % ( self.tablename, field, self.tablename, field) \
105 #for field in ['hrn', 'type', 'authority', 'peer_authority', 'pointer']]
106 # IF EXISTS doenst exist in postgres < 8.2
108 mark.execute('DROP TABLE IF EXISTS ' + tablename +';')
111 mark.execute('DROP TABLE' + tablename +';')
115 mark.execute(statement)
116 #for index in indexes:
118 self.connection.commit()
126 def insert(self, table, columns,values):
128 Inserts data (values) into the columns of the specified table.
132 statement = "INSERT INTO " + table + \
133 "(" + ",".join(columns) + ") " + \
134 "VALUES(" + ", ".join(values) + ");"
136 mark.execute(statement)
137 self.connection.commit()
142 def insert_slab_slice(self, person_rec):
144 Inserts information about a user and his slice into the slice table.
147 sfatable = SfaTable()
148 keys = slice_table.keys()
150 #returns a list of records from the sfa table (dicts)
151 #the filters specified will return only one matching record, into a list of dicts
152 #Finds the slice associated with the user (Senslabs slices hrns contains the user hrn)
154 userrecord = sfatable.find({'hrn': person_rec['hrn'], 'type':'user'})
155 slicerec = sfatable.find({'hrn': person_rec['hrn']+'_slice', 'type':'slice'})
157 if (isinstance (userrecord, list)):
158 userrecord = userrecord[0]
159 if (isinstance (slicerec, list)):
160 slicerec = slicerec[0]
163 values = [ str(oar_dflt_jobid), ' \''+ str(slicerec['hrn']) + '\'', str(userrecord['record_id']), str( slicerec['record_id'])]
165 self.insert('slice', keys, values)
167 logger.error("Trying to import a not senslab slice")
171 def update(self, table, column_names, values, whereclause, valueclause):
173 Updates a record in a given table.
176 #Creates the values string for the update SQL command
177 if len(column_names) is not len(values):
181 valuesdict = dict(zip(column_names,values))
182 for k in valuesdict.keys():
183 valuesdict[k] = str(valuesdict[k])
184 v = ' \''+ str(k) + '\''+ '='+' \''+ valuesdict[k]+'\''
187 statement = "UPDATE %s SET %s WHERE %s = %s" % \
188 (table, ", ".join(valueslist), whereclause, valueclause)
191 mark.execute(statement)
192 self.connection.commit()
198 def update_senslab_slice(self, slice_rec):
199 sfatable = SfaTable()
200 userhrn = slice_rec['hrn'].strip('_slice')
201 userrecord = sfatable.find({'hrn': userhrn, 'type':'user'})
202 if (isinstance (userrecord, list)):
203 userrecord = userrecord[0]
204 columns = [ 'record_user_id', 'oar_job_id']
205 values = [slice_rec['record_user_id'],slice_rec['oar_job_id']]
206 self.update('slice',columns, values,'record_slice_id', slice_rec['record_slice_id'])