def delete_permission(user_id, object_id): with TuRODSSession() as session: sql = "delete from R_OBJT_ACCESS WHERE object_id = {} and user_id = {}".format( object_id, 24746) alias = 'del_permissions' query = SpecificQuery(session, sql, alias) # register specific query in iCAT _ = query.register() exception = None try: query.execute() query.get_results() except Exception as e: exception = e # delete specific query _ = query.remove() if exception is not None: log_and_raise_exception('Error in delete_permission', exception)
def update_permissions(user, access, recursive, name): access_type_id = get_access_type_id(access) object_id = get_collection_id(name) users_dict = get_user_dict() user_id = list(users_dict.keys())[list(users_dict.values()).index(user)] with TuRODSSession() as session: sql = "update R_OBJT_ACCESS set access_type_id = {} where object_id = {} and user_id = {}" \ .format(access_type_id, object_id, user_id) alias = 'update_permissions2' query = SpecificQuery(session, sql, alias) # register specific query in iCAT _ = query.register() exception = None try: query.execute() except CAT_NO_ROWS_FOUND: pass except Exception as e: exception = e _ = query.remove() if exception is not None: log_and_raise_exception('Error in changing the permissions access_type_id = {} object_id = {} and user_id = {}' \ .format(access_type_id, object_id, user_id), exception)
def test_query_data_name_and_id(self): # make a test collection larger than MAX_SQL_ROWS (number of files) test_collection_size = 3*MAX_SQL_ROWS test_collection_path = '/{0}/home/{1}/test_collection'.format(self.session.zone, self.session.username) self.test_collection = helpers.make_test_collection( self.session, test_collection_path, obj_count=test_collection_size) # make specific query sql = "select DATA_NAME, DATA_ID from R_DATA_MAIN join R_COLL_MAIN using (COLL_ID) where COLL_NAME = '{test_collection_path}'".format(**locals()) alias = 'list_data_name_id' columns = [DataObject.name, DataObject.id] query = SpecificQuery(self.session, sql, alias, columns) # register query in iCAT query.register() # run query and check results for i, result in enumerate(query): self.assertIn('test', result[DataObject.name]) self.assertIsNotNone(result[DataObject.id]) self.assertEqual(i, test_collection_size - 1) # unregister query query.remove() # remove test collection self.test_collection.remove(recurse=True, force=True)
def get_permissions_dict(): permissions_dict = {} with TuRODSSession() as session: sql = "select token_id, token_name from R_TOKN_MAIN" columns = [CollectionAccess.user_id, CollectionAccess.name] alias = 'get_permissions_codes' query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: for result in query.get_results(): token_id = result[CollectionAccess.user_id] name = result[CollectionAccess.name] permissions_dict[token_id] = name except Exception as e: exception = e _ = query.remove() if exception is not None: log_and_raise_exception('Error in get_permissions_dict', exception) return permissions_dict
def get_thumbnail_list(sql, thumbs_ids_collection): with TuRODSSession() as session: columns = [DataObject.id] alias = 'get_thumbnail_list' query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: results = query.get_results() for result in results: object_id = result[DataObject.id] thumbs_ids_collection.append(object_id) except CAT_NO_ROWS_FOUND: pass except Exception as e: exception = e # delete specific query _ = query.remove() if exception is not None: log_and_raise_exception("Error in get_thumbnail_list", exception) return thumbs_ids_collection
def determine_object_type( session, attribute, irods_user_id): all_ints = True all_dates = True no_rows = False all_times = True sql = "select r_data_meta_main.meta_attr_value from R_DATA_MAIN " \ "JOIN R_OBJT_METAMAP r_data_metamap ON R_DATA_MAIN.data_id = r_data_metamap.object_id " \ "JOIN R_META_MAIN r_data_meta_main ON r_data_metamap.meta_id = r_data_meta_main.meta_id " \ "JOIN R_OBJT_ACCESS OA ON OA.object_id = R_DATA_MAIN.data_id " \ "where user_id in ( select group_user_id from R_user_group where user_id = {0}) and r_data_meta_main.meta_attr_name = '{1}'".format(irods_user_id, attribute) alias = 'determine_object_type' columns = [DataObjectMeta.value] query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: for result in query.get_results(): value = result[DataObjectMeta.value] if all_ints: all_ints = is_float(value) if all_times: all_times = is_time(value) if all_dates: all_dates = is_date(value) if not all_ints and not all_dates and not all_times: break except CAT_NO_ROWS_FOUND: no_rows = True except Exception as e: exception = e # delete specific query _ = query.remove() if exception is not None: log_and_raise_exception("Error in determine_object_type", exception) if no_rows: return None if all_ints: return 'De' if all_dates: return 'Da' if all_times: return 'Ti' return 'St'
def test_query_data_name_and_id_no_columns(self): '''Same test as above, but without providing query columns to parse results. Result columns are retrieved by index 0..n ''' # make a test collection larger than MAX_SQL_ROWS (number of files) test_collection_size = 3*MAX_SQL_ROWS test_collection_path = '/{0}/home/{1}/test_collection'.format(self.session.zone, self.session.username) self.test_collection = helpers.make_test_collection( self.session, test_collection_path, obj_count=test_collection_size) # make specific query sql = "select DATA_NAME, DATA_ID from R_DATA_MAIN join R_COLL_MAIN using (COLL_ID) where COLL_NAME = '{test_collection_path}'".format(**locals()) alias = 'list_data_name_id' query = SpecificQuery(self.session, sql, alias) # register query in iCAT query.register() # run query and check results for i, result in enumerate(query): self.assertIn('test', result[0]) self.assertIsNotNone(result[1]) self.assertEqual(i, test_collection_size - 1) # unregister query query.remove() # remove test collection self.test_collection.remove(recurse=True, force=True)
def remove_none(attribute_name): with TuRODSSession() as session: sql = "select meta_attr_value from R_META_MAIN where meta_attr_name = '{}'".format(attribute_name) alias = 'remove_none' columns = [CollectionMeta.value] query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: for result in query.get_results(): print("Result: '{}'".format(result[CollectionMeta.value])) except Exception as e: exception = e # delete specific query _ = query.remove() if exception is not None: log_and_raise_exception('Error in remove_none', exception)
def has_objects(clauses, user, starting_folder): coll_ids = {} irods_user_id = get_irods_user_id(user.profile.irods_user) with TuRODSSession() as session: base_sql = "select distinct R_DATA_MAIN.coll_id, R_COLL_MAIN.coll_name from R_DATA_MAIN " \ "JOIN R_COLL_MAIN ON R_DATA_MAIN.coll_id = R_COLL_MAIN.coll_id " \ "JOIN R_OBJT_METAMAP r_data_metamap ON R_DATA_MAIN.data_id = r_data_metamap.object_id " \ "JOIN R_META_MAIN r_data_meta_main ON r_data_metamap.meta_id = r_data_meta_main.meta_id " \ "JOIN R_OBJT_ACCESS OA ON OA.object_id = R_DATA_MAIN.data_id " \ "WHERE user_id in ( select group_user_id from R_user_group where user_id = {0})".format(irods_user_id) nb_of_filters = len(clauses) columns = [DataObject.collection_id, DataObject.path] if nb_of_filters == 1: sql = base_sql + clauses[0] else: sql = '' n = 0 for clause in clauses: if n == 0: sql = base_sql + clause n = n + 1 else: sql = sql + ' intersect ' + base_sql + clause alias = 'list_data_name_idyy' query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: for result in query.get_results(): collid = result[DataObject.collection_id] path = result[DataObject.path] for folder in starting_folder.split(';'): if path.startswith(folder): coll_ids[path] = collid break except CAT_NO_ROWS_FOUND: pass except Exception as e: exception = e _ = query.remove() if exception is not None: log_and_raise_exception('Error in has_objects',exception) return coll_ids
def permissions(folder, name): users_dict = get_user_dict() show_groups(users_dict) with TuRODSSession() as session: coll = session.collections.get(name) permission = [] user_with_permissions = {} with TuRODSSession() as session: sql = "select user_id, access_type_id from R_OBJT_ACCESS WHERE object_id = {}".format( coll.id) columns = [CollectionAccess.user_id, CollectionAccess.access_id] alias = 'get_permissions' query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: for result in query.get_results(): usr_id = result[CollectionAccess.user_id] access_id = result[CollectionAccess.access_id] permission.append({ 'user': users_dict[usr_id], 'access': permissions_dict[access_id], 'hasRights': True }) user_with_permissions[usr_id] = usr_id print(usr_id, users_dict[usr_id]) except CAT_NO_ROWS_FOUND: pass except Exception as e: exception = e _ = query.remove() if exception is not None: log_and_raise_exception('Error fetching permissions', exception) for usr_id in users_dict: if usr_id not in user_with_permissions: permission.append({ 'user': users_dict[usr_id], 'access': '', 'hasRights': False }) return permission
def get_distinct_object_metadata_attr_per_user(usr, irods_user_id): with TuRODSSession() as session: sql = "select distinct r_data_meta_main.meta_attr_name from R_DATA_MAIN " \ "JOIN R_OBJT_METAMAP r_data_metamap ON R_DATA_MAIN.data_id = r_data_metamap.object_id " \ "JOIN R_META_MAIN r_data_meta_main ON r_data_metamap.meta_id = r_data_meta_main.meta_id " \ "JOIN R_OBJT_ACCESS OA ON OA.object_id = R_DATA_MAIN.data_id " \ "where user_id in ( select group_user_id from R_user_group where user_id = {0}) order by r_data_meta_main.meta_attr_name".format(irods_user_id) alias = 'list_data_name_id118' columns = [DataObjectMeta.name] query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None new_att_list = [] try: for result in query.get_results(): name = result[DataObjectMeta.name] name_unique = name.replace(' ', '%20') # if we have two attribute 'ColorMode' and 'ColorMode ' update_or_create does not see the difference @%$$% uima, created = UserIrodsMetaDataAttribute.objects.update_or_create( name_unique__exact=name_unique, object_or_collection=True, user=usr) if created: uima.name = name uima.name_unique = name_unique uima.save() new_att_list.append(uima) except CAT_NO_ROWS_FOUND: pass except Exception as e: exception = e # delete specific query _ = query.remove() if exception is not None: log_and_raise_exception('Error in get_distinct_object_metadata_attr_per_user', exception) #for uima in UserIrodsMetaDataAttribute.objects.filter(user=usr, object_or_collection=True): for uima in new_att_list: uima.type = determine_object_type(session, uima.name, irods_user_id) if uima.type is None: uima.delete() else: uima.save()
def test_register_query_twice(self): query = SpecificQuery(self.session, sql='select DATA_NAME from R_DATA_MAIN', alias='list_data_names') # register query query.register() # register same query again with self.assertRaises(CAT_INVALID_ARGUMENT) as ex: query.register() # check the error message self.assertEqual(str(ex.exception), 'Alias is not unique') # remove query query.remove()
def irods_specificQuery(term): username = "******" passw="alicepass" try: env_file = os.environ['IRODS_ENVIRONMENT_FILE'] except KeyError: env_file = os.path.expanduser('~/.irods/irods_environment.json') with iRODSSession(irods_env_file=env_file) as session: sql = "select data_name, data_id from r_data_main join r_coll_main using (coll_id) where coll_name = '/tempZone/home/alice'" alias = 'list_data_name_id' query = SpecificQuery(session, sql,alias) query.register() for result in query: print('{} {}'.format(result[DataObject.name], alias,result[DataObject.id])) _ = query.remove()
def testje(path, user_id): with TuRODSSession() as session: sql = "select object_id from R_OBJT_ACCESS where user_id = {} and object_id in" \ "(select R_COLL_MAIN.coll_id from R_COLL_MAIN WHERE R_COLL_MAIN.coll_name like '{}%' )". \ format(user_id, path) sql = "select R_COLL_MAIN.coll_id from R_COLL_MAIN JOIN R_OBJT_ACCESS ON R_COLL_MAIN.coll_id = R_OBJT_ACCESS.object_id " \ "WHERE R_COLL_MAIN.coll_name like '{}% and R_OBJT_ACCESS.user_id = {}".format(path, user_id) sql = "select R_COLL_MAIN.coll_id from R_COLL_MAIN " \ "JOIN R_OBJT_ACCESS OA ON OA.object_id = R_COLL_MAIN.coll_id " \ "WHERE user_id = " + str(user_id) #sql = "select R_COLL_MAIN.coll_id from R_COLL_MAIN WHERE R_COLL_MAIN.coll_name like '{}%' ".format(path) print(sql) alias = 'blepper14' columns = [Collection.id] query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None n = 0 try: query.execute() for result in query.get_results(): n = n + 1 print(n, result[Collection.id]) print(get_collection_name(result[Collection.id])) except CAT_NO_ROWS_FOUND: print('no exception') except Exception as e: exception = e _ = query.remove() if exception is not None: log_and_raise_exception('Error', exception)
def show_groups(users_dict): with TuRODSSession() as session: query = session.query(UserGroup.id, UserGroup.name) for result in query.get_results(): print(result[UserGroup.id], result[UserGroup.name]) sql = "select group_user_id, user_id from R_user_group" alias = 'blepper15' columns = [CollectionAccess.user_id, CollectionAccess.access_id] query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None n = 0 try: query.execute() for result in query.get_results(): n = n + 1 user = users_dict[result[CollectionAccess.access_id]] #print(n,result[CollectionAccess.user_id], user) print(result[CollectionAccess.user_id], result[CollectionAccess.access_id]) except CAT_NO_ROWS_FOUND: print('no exception') except Exception as e: exception = e _ = query.remove() if exception is not None: log_and_raise_exception('Error', exception)
def test_list_specific_queries_with_unknown_alias(self): query = SpecificQuery(self.session, alias='foo') with self.assertRaises(CAT_UNKNOWN_SPECIFIC_QUERY): res = query.get_results() next(res)
def test_list_specific_queries_with_arguments(self): query = SpecificQuery(self.session, alias='lsl', args=['%OFFSET%']) for result in query: self.assertIsNotNone(result[0]) # query alias self.assertIn('SELECT', result[1].upper()) # query string
def test_list_specific_queries(self): query = SpecificQuery(self.session, alias='ls') for result in query: self.assertIsNotNone(result[0]) # query alias self.assertIn('SELECT', result[1].upper()) # query string
def search_objects(coll, parent, filters, irods_user_id): folder = coll.path clauses = [] thumbs_ids_collection = [] with TuRODSSession() as session: base_sql = "select R_DATA_MAIN.data_name, R_DATA_MAIN.data_id, R_DATA_MAIN.data_size from R_DATA_MAIN " \ "JOIN R_OBJT_METAMAP r_data_metamap ON R_DATA_MAIN.data_id = r_data_metamap.object_id " \ "JOIN R_META_MAIN r_data_meta_main ON r_data_metamap.meta_id = r_data_meta_main.meta_id " \ "JOIN R_OBJT_ACCESS OA ON OA.object_id = R_DATA_MAIN.data_id " \ "where R_DATA_MAIN.coll_id = {0} and user_id in (select group_user_id from R_user_group where user_id = {1})".format(coll.id, irods_user_id) if filters is not None: for key in filters.keys(): clause = create_obj_filter(key, filters[key]) if clause != None: clauses.append(clause) nb_of_filters = len(clauses) columns = [DataObject.name, DataObject.id, DataObject.size] if nb_of_filters == 0: sql = "select R_DATA_MAIN.data_name, R_DATA_MAIN.data_id, R_DATA_MAIN.data_size from R_DATA_MAIN " \ "JOIN R_OBJT_ACCESS OA ON OA.object_id = R_DATA_MAIN.data_id " \ "where R_DATA_MAIN.coll_id = {0} and user_id in (select group_user_id from R_user_group where user_id = {1})".format(coll.id, irods_user_id) elif nb_of_filters == 1: sql = base_sql + clauses[0] else: sql = '' n = 0 for clause in clauses: if n == 0: sql = base_sql + clause n = n + 1 else: sql = sql + ' intersect ' + base_sql + clause alias = 'xx_list_data_name_id11' query = SpecificQuery(session, sql, alias, columns) # register specific query in iCAT _ = query.register() exception = None try: thumbs = [] results = query.get_results() for result in results: name = result[DataObject.name] path = folder + "/" + name id = result[DataObject.id] size = result[DataObject.size] filename, extension = os.path.splitext(name) if is_thumbnail(extension): thumbs.append(thumbs) #th = Thumbnail.objects.filter(id=id).first() #if th is not None: thumbs_ids_collection.append(id) Node(name, parent=parent, id=id, path=path, leaf=True, size=size) except CAT_NO_ROWS_FOUND: pass except Exception as e: exception = e # delete specific query _ = query.remove() if exception is not None: log_and_raise_exception("Error in search_objects", exception) return thumbs_ids_collection