From 72c412240602b536917b3c6df9edde3f0b9ba6cf Mon Sep 17 00:00:00 2001 From: Sandrine Avakian Date: Wed, 7 Mar 2012 09:59:12 +0100 Subject: [PATCH] Thrash commit to use SQLalchemy and create senslab_slice table. NOT WORKING. Didi it for merging updated master branch. --- sfa/importer/slabimporter.py | 373 ++++++++++++++++++----------------- sfa/senslab/slabpostgres.py | 145 ++++++++++---- 2 files changed, 290 insertions(+), 228 deletions(-) diff --git a/sfa/importer/slabimporter.py b/sfa/importer/slabimporter.py index 7d2cfabf..0c4740ee 100644 --- a/sfa/importer/slabimporter.py +++ b/sfa/importer/slabimporter.py @@ -16,6 +16,8 @@ from sfa.trust.gid import create_uuid from sfa.storage.alchemy import dbsession from sfa.storage.model import RegRecord, RegAuthority, RegSlice, RegNode, RegUser, RegKey +from sfa.storage.dbschema import DBSchema + def _get_site_hrn(site): hrn = site['login_base'] @@ -55,6 +57,7 @@ class SlabImporter: #Create special slice table for senslab db = SlabDB() + if not db.exists('slice_senslab'): db.createtable('slice_senslab') ######## retrieve all existing SFA objects @@ -65,209 +68,209 @@ class SlabImporter: self.records_by_type_hrn = \ dict ( [ ( (record.type, record.hrn) , record ) for record in all_records ] ) - # initialize record.stale to True by default, then mark stale=False on the ones that are in use - for record in all_records: - record.stale=True + ## initialize record.stale to True by default, then mark stale=False on the ones that are in use + #for record in all_records: + #record.stale=True - nodes_listdict = slabdriver.GetNodes() - nodes_by_id = dict([(node['node_id'],node) for node in nodes_listdict]) - sites_listdict = slabdriver.GetSites() + #nodes_listdict = slabdriver.GetNodes() + #nodes_by_id = dict([(node['node_id'],node) for node in nodes_listdict]) + #sites_listdict = slabdriver.GetSites() - ldap_person_listdict = slabdriver.GetPersons() - slices_listdict = slabdriver.GetSlices() - for site in sites_listdict: - site_hrn = _get_site_hrn(site) - site_record = self.find_record_by_type_hrn ('authority', site_hrn) - if not site_record: - try: - urn = hrn_to_urn(site_hrn, 'authority') - if not self.auth_hierarchy.auth_exists(urn): - self.auth_hierarchy.create_auth(urn) - auth_info = self.auth_hierarchy.get_auth_info(urn) - site_record = RegAuthority(hrn=site_hrn, gid=auth_info.get_gid_object(), - pointer='-1', - authority=get_authority(site_hrn)) - site_record.just_created() - dbsession.add(site_record) - dbsession.commit() - self.logger.info("SlabImporter: imported authority (site) : %s" % site_record) - self.update_just_added_records_dict (site_record) - except: - # if the site import fails then there is no point in trying to import the - # site's child records (node, slices, persons), so skip them. - self.logger.log_exc("SlabImporter: failed to import site. Skipping child records") - continue - else: - # xxx update the record ... - pass - site_record.stale=False + #ldap_person_listdict = slabdriver.GetPersons() + #slices_listdict = slabdriver.GetSlices() + #for site in sites_listdict: + #site_hrn = _get_site_hrn(site) + #site_record = self.find_record_by_type_hrn ('authority', site_hrn) + #if not site_record: + #try: + #urn = hrn_to_urn(site_hrn, 'authority') + #if not self.auth_hierarchy.auth_exists(urn): + #self.auth_hierarchy.create_auth(urn) + #auth_info = self.auth_hierarchy.get_auth_info(urn) + #site_record = RegAuthority(hrn=site_hrn, gid=auth_info.get_gid_object(), + #pointer='-1', + #authority=get_authority(site_hrn)) + #site_record.just_created() + #dbsession.add(site_record) + #dbsession.commit() + #self.logger.info("SlabImporter: imported authority (site) : %s" % site_record) + #self.update_just_added_records_dict(site_record) + #except: + ## if the site import fails then there is no point in trying to import the + ## site's child records (node, slices, persons), so skip them. + #self.logger.log_exc("SlabImporter: failed to import site. Skipping child records") + #continue + #else: + ## xxx update the record ... + #pass + #site_record.stale=False - # import node records in site - for node_id in site['node_ids']: - try: - node = nodes_by_id[node_id] - except: - self.logger.warning ("SlabImporter: cannot find node_id %s - ignored"%node_id) - continue - site_auth = get_authority(site_hrn) - site_name = site['login_base'] - hrn = self.hostname_to_hrn(root_auth, site_name, node['hostname']) - # xxx this sounds suspicious - if len(hrn) > 64: hrn = hrn[:64] - node_record = self.find_record_by_type_hrn( 'node', hrn ) - print >>sys.stderr, " \r\n \r\n SLAB IMPORTER node_record %s " %(node_record) - if not node_record: - try: - pkey = Keypair(create=True) - urn = hrn_to_urn(hrn, 'node') - print>>sys.stderr, "\r\n \r\n SLAB IMPORTER NODE IMPORT urn %s hrn %s" %(urn, hrn) - node_gid = self.auth_hierarchy.create_gid(urn, create_uuid(), pkey) - node_record = RegNode (hrn=hrn, gid=node_gid, - pointer =node['node_id'], - authority=get_authority(hrn)) - node_record.just_created() - dbsession.add(node_record) - dbsession.commit() - self.logger.info("SlabImporter: imported node: %s" % node_record) - print>>sys.stderr, "\r\n \t\t\t SLAB IMPORTER NODE IMPORT NOTnode_record %s " %(node_record) - self.update_just_added_records_dict(node_record) - except: - self.logger.log_exc("SlabImporter: failed to import node") - else: - # xxx update the record ... - pass - node_record.stale=False + ## import node records in site + #for node_id in site['node_ids']: + #try: + #node = nodes_by_id[node_id] + #except: + #self.logger.warning ("SlabImporter: cannot find node_id %s - ignored"%node_id) + #continue + #site_auth = get_authority(site_hrn) + #site_name = site['login_base'] + #hrn = self.hostname_to_hrn(root_auth, site_name, node['hostname']) + ## xxx this sounds suspicious + #if len(hrn) > 64: hrn = hrn[:64] + #node_record = self.find_record_by_type_hrn( 'node', hrn ) + #print >>sys.stderr, " \r\n \r\n SLAB IMPORTER node_record %s " %(node_record) + #if not node_record: + #try: + #pkey = Keypair(create=True) + #urn = hrn_to_urn(hrn, 'node') + #print>>sys.stderr, "\r\n \r\n SLAB IMPORTER NODE IMPORT urn %s hrn %s" %(urn, hrn) + #node_gid = self.auth_hierarchy.create_gid(urn, create_uuid(), pkey) + #node_record = RegNode (hrn=hrn, gid=node_gid, + #pointer =node['node_id'], + #authority=get_authority(hrn)) + #node_record.just_created() + #dbsession.add(node_record) + #dbsession.commit() + #self.logger.info("SlabImporter: imported node: %s" % node_record) + #print>>sys.stderr, "\r\n \t\t\t SLAB IMPORTER NODE IMPORT NOTnode_record %s " %(node_record) + #self.update_just_added_records_dict(node_record) + #except: + #self.logger.log_exc("SlabImporter: failed to import node") + #else: + ## xxx update the record ... + #pass + #node_record.stale=False - # import persons - for person in ldap_person_listdict : + ## import persons + #for person in ldap_person_listdict : - person_hrn = person['hrn'] - slice_hrn = self.slicename_to_hrn(person['hrn'],'_slice') + #person_hrn = person['hrn'] + #slice_hrn = self.slicename_to_hrn(person['hrn'],'_slice') - # xxx suspicious again - if len(person_hrn) > 64: person_hrn = person_hrn[:64] - person_urn = hrn_to_urn(person_hrn, 'user') + ## xxx suspicious again + #if len(person_hrn) > 64: person_hrn = person_hrn[:64] + #person_urn = hrn_to_urn(person_hrn, 'user') - user_record = self.find_record_by_type_hrn( 'user', person_hrn) - slice_record = self.find_record_by_type_hrn ('slice', slice_hrn) - print>>sys.stderr, "\r\n \r\n SLAB IMPORTER PERSON IMPORT user_record %s " %(user_record) + #user_record = self.find_record_by_type_hrn( 'user', person_hrn) + #slice_record = self.find_record_by_type_hrn ('slice', slice_hrn) + #print>>sys.stderr, "\r\n \r\n SLAB IMPORTER PERSON IMPORT user_record %s " %(user_record) - # return a tuple pubkey (a plc key object) and pkey (a Keypair object) - def init_person_key (person, slab_key): - pubkey=None - if person['pkey']: - # randomly pick first key in set - pubkey = slab_key - try: - pkey = convert_public_key(pubkey) - except: - self.logger.warn('SlabImporter: unable to convert public key for %s' % person_hrn) - pkey = Keypair(create=True) - else: - # the user has no keys. Creating a random keypair for the user's gid - self.logger.warn("SlabImporter: person %s does not have a PL public key"%person_hrn) - pkey = Keypair(create=True) - return (pubkey, pkey) + ## return a tuple pubkey (a plc key object) and pkey (a Keypair object) + #def init_person_key (person, slab_key): + #pubkey=None + #if person['pkey']: + ## randomly pick first key in set + #pubkey = slab_key + #try: + #pkey = convert_public_key(pubkey) + #except: + #self.logger.warn('SlabImporter: unable to convert public key for %s' % person_hrn) + #pkey = Keypair(create=True) + #else: + ## the user has no keys. Creating a random keypair for the user's gid + #self.logger.warn("SlabImporter: person %s does not have a PL public key"%person_hrn) + #pkey = Keypair(create=True) + #return (pubkey, pkey) - try: - slab_key = person['pkey'] - # new person - if not user_record: - (pubkey,pkey) = init_person_key (person, slab_key ) - person_gid = self.auth_hierarchy.create_gid(person_urn, create_uuid(), pkey) - person_gid.set_email(person['email']) - user_record = RegUser (hrn=person_hrn, gid=person_gid, - pointer='-1', - authority=get_authority(person_hrn), - email=person['email']) - if pubkey: - user_record.reg_keys=[RegKey (pubkey)] - else: - self.logger.warning("No key found for user %s"%user_record) - user_record.just_created() - dbsession.add (user_record) - dbsession.commit() - self.logger.info("SlabImporter: imported person: %s" % user_record) - print>>sys.stderr, "\r\n \r\n SLAB IMPORTER PERSON IMPORT NOTuser_record %s " %(user_record) - self.update_just_added_records_dict( user_record ) - else: - # update the record ? - # if user's primary key has changed then we need to update the - # users gid by forcing an update here - sfa_keys = user_record.reg_keys - #def key_in_list (key,sfa_keys): - #for reg_key in sfa_keys: - #if reg_key.key==key['key']: return True - #return False - # is there a new key in myplc ? - new_keys=False - if key is not sfa_keys : - new_keys = True - if new_keys: - (pubkey,pkey) = init_person_key (person, slab_key) - person_gid = self.auth_hierarchy.create_gid(person_urn, create_uuid(), pkey) - if not pubkey: - user_record.reg_keys=[] - else: - user_record.reg_keys=[ RegKey (pubkey)] - self.logger.info("SlabImporter: updated person: %s" % user_record) - user_record.email = person['email'] - dbsession.commit() - user_record.stale=False - except: - self.logger.log_exc("SlabImporter: failed to import person %s"%(person) ) + #try: + #slab_key = person['pkey'] + ## new person + #if not user_record: + #(pubkey,pkey) = init_person_key (person, slab_key ) + #person_gid = self.auth_hierarchy.create_gid(person_urn, create_uuid(), pkey) + #person_gid.set_email(person['email']) + #user_record = RegUser (hrn=person_hrn, gid=person_gid, + #pointer='-1', + #authority=get_authority(person_hrn), + #email=person['email']) + #if pubkey: + #user_record.reg_keys=[RegKey (pubkey)] + #else: + #self.logger.warning("No key found for user %s"%user_record) + #user_record.just_created() + #dbsession.add (user_record) + #dbsession.commit() + #self.logger.info("SlabImporter: imported person: %s" % user_record) + #print>>sys.stderr, "\r\n \r\n SLAB IMPORTER PERSON IMPORT NOTuser_record %s " %(user_record) + #self.update_just_added_records_dict( user_record ) + #else: + ## update the record ? + ## if user's primary key has changed then we need to update the + ## users gid by forcing an update here + #sfa_keys = user_record.reg_keys + ##def key_in_list (key,sfa_keys): + ##for reg_key in sfa_keys: + ##if reg_key.key==key['key']: return True + ##return False + ## is there a new key in myplc ? + #new_keys=False + #if key is not sfa_keys : + #new_keys = True + #if new_keys: + #(pubkey,pkey) = init_person_key (person, slab_key) + #person_gid = self.auth_hierarchy.create_gid(person_urn, create_uuid(), pkey) + #if not pubkey: + #user_record.reg_keys=[] + #else: + #user_record.reg_keys=[ RegKey (pubkey)] + #self.logger.info("SlabImporter: updated person: %s" % user_record) + #user_record.email = person['email'] + #dbsession.commit() + #user_record.stale=False + #except: + #self.logger.log_exc("SlabImporter: failed to import person %s"%(person) ) - if not slice_record: - try: - pkey = Keypair(create=True) - urn = hrn_to_urn(slice_hrn, 'slice') - slice_gid = self.auth_hierarchy.create_gid(urn, create_uuid(), pkey) - slice_record = RegSlice (hrn=slice_hrn, gid=slice_gid, - pointer='-1', - authority=get_authority(slice_hrn)) - print>>sys.stderr, "\r\n \r\n SLAB IMPORTER SLICE IMPORT NOTslice_record%s " %(slice_record) - slice_record.just_created() - dbsession.add(slice_record) - dbsession.commit() - self.logger.info("SlabImporter: imported slice: %s" % slice_record) - self.remember_record ( slice_record ) - except: - self.logger.log_exc("SlabImporter: failed to import slice") - else: - # xxx update the record ... - self.logger.warning ("Slice update not yet implemented") - pass - # record current users affiliated with the slice - slice_record.reg_researchers = \ - [ self.locate_by_type_pointer ('user',user_id) for user_id in slice['person_ids'] ] - dbsession.commit() - slice_record.stale=False + #if not slice_record: + #try: + #pkey = Keypair(create=True) + #urn = hrn_to_urn(slice_hrn, 'slice') + #slice_gid = self.auth_hierarchy.create_gid(urn, create_uuid(), pkey) + #slice_record = RegSlice (hrn=slice_hrn, gid=slice_gid, + #pointer='-1', + #authority=get_authority(slice_hrn)) + #print>>sys.stderr, "\r\n \r\n SLAB IMPORTER SLICE IMPORT NOTslice_record%s " %(slice_record) + #slice_record.just_created() + #dbsession.add(slice_record) + #dbsession.commit() + #self.logger.info("SlabImporter: imported slice: %s" % slice_record) + #self.remember_record ( slice_record ) + #except: + #self.logger.log_exc("SlabImporter: failed to import slice") + #else: + ## xxx update the record ... + #self.logger.warning ("Slice update not yet implemented") + #pass + ## record current users affiliated with the slice + #slice_record.reg_researchers = \ + #[ self.locate_by_type_pointer ('user',user_id) for user_id in slice['person_ids'] ] + #dbsession.commit() + #slice_record.stale=False - ### remove stale records - # special records must be preserved - system_hrns = [interface_hrn, root_auth, interface_hrn + '.slicemanager'] - for record in all_records: - if record.hrn in system_hrns: - record.stale=False - if record.peer_authority: - record.stale=False + #### remove stale records + ## special records must be preserved + #system_hrns = [interface_hrn, root_auth, interface_hrn + '.slicemanager'] + #for record in all_records: + #if record.hrn in system_hrns: + #record.stale=False + #if record.peer_authority: + #record.stale=False - for record in all_records: - try: - stale=record.stale - except: - stale=True - self.logger.warning("stale not found with %s"%record) - if stale: - self.logger.info("SlabImporter: deleting stale record: %s" % record) - dbsession.delete(record) - dbsession.commit() + #for record in all_records: + #try: + #stale=record.stale + #except: + #stale=True + #self.logger.warning("stale not found with %s"%record) + #if stale: + #self.logger.info("SlabImporter: deleting stale record: %s" % record) + #dbsession.delete(record) + #dbsession.commit() \ No newline at end of file diff --git a/sfa/senslab/slabpostgres.py b/sfa/senslab/slabpostgres.py index 84c1a770..b589f2bf 100644 --- a/sfa/senslab/slabpostgres.py +++ b/sfa/senslab/slabpostgres.py @@ -11,13 +11,55 @@ import sys try: import pgdb except: print >> sys.stderr, "WARNING, could not import pgdb" + +from sqlalchemy import Column, Integer, String, DateTime +from sqlalchemy import Table, Column, MetaData, join, ForeignKey +from sfa.storage.model import Base +from sqlalchemy.ext.declarative import declarative_base +from sqlalchemy.orm import relationship, backref + +from sfa.storage.alchemy import dbsession, engine +from sqlalchemy import MetaData, Table +from sqlalchemy.exc import NoSuchTableError + #Dict holding the columns names of the table as keys #and their type, used for creation of the table 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'} #Dict with all the specific senslab tables -tablenames_dict = {'slice': slice_table} +tablenames_dict = {'slice_senslab': slice_table} + +############################## + + + +SlabBase = declarative_base(metadata= Base.metadata, bind=engine) + + + + +class SlabSliceDB (SlabBase): + __tablename__ = 'slice_senslab' + record_id_user = Column(Integer, ForeignKey("records.record_id"), primary_key=True) + oar_job_id = Column( Integer,default = -1) + record_id_slice = Column(Integer) + slice_hrn = Column(String,nullable = False) + + def __init__ (self, slice_hrn =None, oar_job_id=None, record_id_slice=None, record_id_user= None): + if record_id_slice: + self.record_id_slice = record_id_slice + if slice_hrn: + self.slice_hrn = slice_hrn + if oar_job_id: + self.oar_job_id = oar_job_id + if slice_hrn: + self.slice_hrn = slice_hrn + if record_id_user: + self.record_id_user= record_id_user + + + class SlabDB: def __init__(self): self.config = Config() @@ -96,18 +138,28 @@ class SlabDB: Checks if the table specified as tablename exists. """ - #mark = self.cursor() - sql = "SELECT * from pg_tables" - #mark.execute(sql) - #rows = mark.fetchall() - #mark.close() - #labels = [column[0] for column in mark.description] - #rows = [dict(zip(labels, row)) for row in rows] - rows = self.selectall(sql) - rows = filter(lambda row: row['tablename'].startswith(tablename), rows) - if rows: + + + try: + metadata = MetaData (bind=engine) + table=Table (tablename, metadata, autoload=True) return True - return False + except NoSuchTableError: + print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES EXISTS NOPE! tablename %s " %(tablename) + return False + ##mark = self.cursor() + #sql = "SELECT * from pg_tables" + ##mark.execute(sql) + ##rows = mark.fetchall() + ##mark.close() + ##labels = [column[0] for column in mark.description] + ##rows = [dict(zip(labels, row)) for row in rows] + #rows = self.selectall(sql) + #rows = filter(lambda row: row['tablename'].startswith(tablename), rows) + #if rows: + #return True + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES EXISTS NOPE ! tablename %s " %(tablename) + #return False def createtable(self, tablename ): """ @@ -115,40 +167,47 @@ class SlabDB: the table schema. """ - mark = self.cursor() - tablelist =[] - if tablename not in tablenames_dict: - logger.error("Tablename unknown - creation failed") - return + print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES CREATETABLE " + SlabBase.metadata.create_all(engine) + print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES CREATETABLE YAAAAAAAAAAY" + #mark = self.cursor() + #tablelist =[] + #if tablename not in tablenames_dict: + #logger.error("Tablename unknown - creation failed") + #return - T = tablenames_dict[tablename] - - for k in T.keys(): - tmp = str(k) +' ' + T[k] - tablelist.append(tmp) + #T = tablenames_dict[tablename] + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES.PY T %s" %(T) + #for k in T.keys(): + #tmp = str(k) +' ' + T[k] + #tablelist.append(tmp) - end_of_statement = ",".join(tablelist) + #end_of_statement = ",".join(tablelist) - statement = "CREATE TABLE " + tablename + " ("+ end_of_statement +");" - - #template = "CREATE INDEX %s_%s_idx ON %s (%s);" - #indexes = [template % ( self.tablename, field, self.tablename, field) \ - #for field in ['hrn', 'type', 'authority', 'peer_authority', 'pointer']] - # IF EXISTS doenst exist in postgres < 8.2 - try: - mark.execute('DROP TABLE IF EXISTS ' + tablename +';') - except: - try: - mark.execute('DROP TABLE' + tablename +';') - except: - pass + #statement = "CREATE TABLE " + tablename + " ("+ end_of_statement +");" + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES.PY statement %s" %(statement) + ##template = "CREATE INDEX %s_%s_idx ON %s (%s);" + ##indexes = [template % ( self.tablename, field, self.tablename, field) \ + ##for field in ['hrn', 'type', 'authority', 'peer_authority', 'pointer']] + ##IF EXISTS doenst exist in postgres < 8.2 + #try: + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES.PY AVANT LE DROP IF EXISTS" + #mark.execute('DROP TABLE IF EXISTS ' + tablename +';') - mark.execute(statement) - #for index in indexes: - #self.db.do(index) - self.connection.commit() - mark.close() - self.close() + #except: + #try: + #mark.execute('DROP TABLE' + tablename +';') + #except: + #pass + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES.PY AVANT EXECUTE statement ",statement + #mark.execute(statement) + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES.PY OUEEEEEEEEEEEEEEEEEEEEEE " + ##for index in indexes: + ##self.db.do(index) + #self.connection.commit() + #print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES.PY COMMIT DE OUFGUEDIN " + #mark.close() + #self.close() return @@ -193,7 +252,7 @@ class SlabDB: oar_dflt_jobid = -1 values = [ str(oar_dflt_jobid), ' \''+ str(slicerec['hrn']) + '\'', str(userrecord['record_id']), str( slicerec['record_id'])] - self.insert('slice', keys, values) + self.insert('slice_senslab', keys, values) else : logger.error("Trying to import a not senslab slice") return -- 2.43.0