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 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 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 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 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 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 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 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.get_results()):
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 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.get_results()):
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 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_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 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 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
def test_list_specific_queries(self):
query = SpecificQuery(self.session, alias='ls')
for result in query.get_results():
self.assertIsNotNone(result[0]) # query alias
self.assertIn('SELECT', result[1].upper()) # query string
def test_list_specific_queries_with_wrong_alias(self):
query = SpecificQuery(self.session, alias='foo')
with self.assertRaises(CAT_UNKNOWN_SPECIFIC_QUERY):
query.get_results().next()