def getGenomeIdsFromGenomeListIds(self, genome_list_ids):
'''
Function: getGenomeIdsFromGenomeListIds
Given a list of ids, return all the ids of the genomes contained
:param genome_list_ids: A list of genome list ids whose contents needs to be retrieved.
Returns:
A list of all the genome ids contained within the specified genome
list(s), False on failure.
'''
try:
temp_table_name = generateTempTableName()
if genome_list_ids:
try:
self.cur.execute("CREATE TEMP TABLE %s (id integer)" %
(temp_table_name, ))
query = "INSERT INTO {0} (id) VALUES (%s)".format(
temp_table_name)
self.cur.executemany(
query, [(genome_list_id, )
for genome_list_id in genome_list_ids])
except:
raise GenomeDatabaseError(
"Unable to process genome list ID: %s" %
str(genome_list_id))
else:
raise GenomeDatabaseError(
"No genome lists given. Cannot retrieve IDs")
# Find any ids that don't have genome lists
query = ("SELECT id FROM {0} " + "WHERE id NOT IN ( " +
"SELECT id " +
"FROM genome_lists)").format(temp_table_name)
self.cur.execute(query)
missing_list_ids = []
for (list_id, ) in self.cur:
missing_list_ids.append(list_id)
if missing_list_ids:
raise GenomeDatabaseError(
"Unknown genome list id(s) given. %s" %
str(missing_list_ids))
# Find any genome list ids that we dont have permission to view
self.cur.execute(
"SELECT genome_id " + "FROM genome_list_contents " +
"WHERE list_id in %s", (tuple(genome_list_ids), ))
except GenomeDatabaseError as e:
raise e
return [genome_id for (genome_id, ) in self.cur.fetchall()]
def getGenomeIdsFromGenomeListIds(self, genome_list_ids):
'''
Function: getGenomeIdsFromGenomeListIds
Given a list of ids, return all the ids of the genomes contained
:param genome_list_ids: A list of genome list ids whose contents needs to be retrieved.
Returns:
A list of all the genome ids contained within the specified genome
list(s), False on failure.
'''
try:
temp_table_name = generateTempTableName()
if genome_list_ids:
try:
self.cur.execute("CREATE TEMP TABLE %s (id integer)" %
(temp_table_name,))
query = "INSERT INTO {0} (id) VALUES (%s)".format(
temp_table_name)
self.cur.executemany(query, [(genome_list_id,) for genome_list_id in genome_list_ids])
except:
raise GenomeDatabaseError("Unable to process genome list ID: %s" % str(genome_list_id))
else:
raise GenomeDatabaseError(
"No genome lists given. Cannot retrieve IDs")
# Find any ids that don't have genome lists
query = ("SELECT id FROM {0} " +
"WHERE id NOT IN ( " +
"SELECT id " +
"FROM genome_lists)").format(temp_table_name)
self.cur.execute(query)
missing_list_ids = []
for (list_id,) in self.cur:
missing_list_ids.append(list_id)
if missing_list_ids:
raise GenomeDatabaseError(
"Unknown genome list id(s) given. %s" % str(missing_list_ids))
# Find any genome list ids that we dont have permission to view
self.cur.execute("SELECT genome_id " +
"FROM genome_list_contents " +
"WHERE list_id in %s", (tuple(genome_list_ids),))
except GenomeDatabaseError as e:
raise e
return [genome_id for (genome_id,) in self.cur.fetchall()]
def editGenomeList(self,
genome_list_id,
genome_ids=None,
operation=None,
name=None,
description=None,
private=None):
"""Edit an existing genome list in the database.
Parameters
----------
genome_list_id : int
Identifier of genome list in database.
genome_ids : list
A list of genome ids to be modified.
operation : str
Operation to perform on genome list (add or remove).
name : str
Name of the newly created list.
description : str
Description of the newly created list.
private : bool
Denotes whether this list is public or private.
Returns
-------
bool
True if successful, else False
"""
try:
edit_permission = self.permissionToModify(genome_list_id)
if edit_permission is None:
raise GenomeDatabaseError(
"Unable to retrieve genome list id for editing. Offending list id: %s"
% genome_list_id)
if edit_permission is False:
raise GenomeDatabaseError(
"Insufficient permissions to edit this genome list. Offending list id: %s"
% genome_list_id)
update_query = []
params = []
if name is not None:
update_query.append("name = %s")
params.append(name)
if description is not None:
update_query.append("description = %s")
params.append(description)
if private is not None:
update_query.append("private = %s")
params.append(private)
if params:
self.cur.execute(
"UPDATE genome_lists SET " + ",".join(update_query) +
" WHERE id = %s", params + [genome_list_id])
temp_table_name = generateTempTableName()
if operation is not None:
if len(genome_ids) == 0:
raise GenomeDatabaseError(
"No genome ids given to perform '%s' operation." %
operation)
self.cur.execute("CREATE TEMP TABLE %s (id integer)" %
(temp_table_name, ))
query = "INSERT INTO {0} (id) VALUES (%s)".format(
temp_table_name)
self.cur.executemany(query, [(x, ) for x in genome_ids])
if operation == 'add':
query = (
"INSERT INTO genome_list_contents (list_id, genome_id) "
+ "SELECT %s, id FROM {0} " + "WHERE id NOT IN ( " +
"SELECT genome_id " + "FROM genome_list_contents " +
"WHERE list_id = %s)").format(temp_table_name)
self.cur.execute(query, (genome_list_id, genome_list_id))
elif operation == 'remove':
query = ("DELETE FROM genome_list_contents " +
"WHERE list_id = %s " + "AND genome_id IN ( " +
"SELECT id " +
"FROM {0})").format(temp_table_name)
self.cur.execute(query, [genome_list_id])
query_is_empty = (
"SELECT count(glc.genome_id) from genome_lists as gl "
+
"LEFT JOIN genome_list_contents as glc on glc.list_id = gl.id "
+ "WHERE gl.id = {0} " +
"GROUP BY gl.id").format(genome_list_id)
self.cur.execute(query_is_empty)
count = self.cur.fetchone()
if count[0] == 0:
# We delete the list because it's empty
query_del_list = (
"DELETE FROM genome_lists WHERE id = {0} "
).format(genome_list_id)
self.cur.execute(query_del_list)
else:
raise GenomeDatabaseError(
"Unknown genome set edit operation: %s" % operation)
except GenomeDatabaseError as e:
raise e
return True
def editGenomeList(self, genome_list_id, genome_ids=None, operation=None, name=None, description=None, private=None):
"""Edit an existing genome list in the database.
Parameters
----------
genome_list_id : int
Identifier of genome list in database.
genome_ids : list
A list of genome ids to be modified.
operation : str
Operation to perform on genome list (add or remove).
name : str
Name of the newly created list.
description : str
Description of the newly created list.
private : bool
Denotes whether this list is public or private.
Returns
-------
bool
True if successful, else False
"""
try:
edit_permission = self.permissionToModify(genome_list_id)
if edit_permission is None:
raise GenomeDatabaseError(
"Unable to retrieve genome list id for editing. Offending list id: %s" % genome_list_id)
if edit_permission is False:
raise GenomeDatabaseError(
"Insufficient permissions to edit this genome list. Offending list id: %s" % genome_list_id)
update_query = []
params = []
if name is not None:
update_query.append("name = %s")
params.append(name)
if description is not None:
update_query.append("description = %s")
params.append(description)
if private is not None:
update_query.append("private = %s")
params.append(private)
if params:
self.cur.execute("UPDATE genome_lists SET " + ",".join(update_query) +
" WHERE id = %s", params + [genome_list_id])
temp_table_name = generateTempTableName()
if operation is not None:
if len(genome_ids) == 0:
raise GenomeDatabaseError(
"No genome ids given to perform '%s' operation." % operation)
self.cur.execute("CREATE TEMP TABLE %s (id integer)" %
(temp_table_name,))
query = "INSERT INTO {0} (id) VALUES (%s)".format(
temp_table_name)
self.cur.executemany(query, [(x,) for x in genome_ids])
if operation == 'add':
query = ("INSERT INTO genome_list_contents (list_id, genome_id) " +
"SELECT %s, id FROM {0} " +
"WHERE id NOT IN ( " +
"SELECT genome_id " +
"FROM genome_list_contents " +
"WHERE list_id = %s)").format(temp_table_name)
self.cur.execute(query, (genome_list_id, genome_list_id))
elif operation == 'remove':
query = ("DELETE FROM genome_list_contents " +
"WHERE list_id = %s " +
"AND genome_id IN ( " +
"SELECT id " +
"FROM {0})").format(temp_table_name)
self.cur.execute(query, [genome_list_id])
query_is_empty = ("SELECT count(glc.genome_id) from genome_lists as gl " +
"LEFT JOIN genome_list_contents as glc on glc.list_id = gl.id " +
"WHERE gl.id = {0} " +
"GROUP BY gl.id").format(genome_list_id)
self.cur.execute(query_is_empty)
count = self.cur.fetchone()
if count[0] == 0:
# We delete the list because it's empty
query_del_list = ("DELETE FROM genome_lists WHERE id = {0} ").format(
genome_list_id)
self.cur.execute(query_del_list)
else:
raise GenomeDatabaseError(
"Unknown genome set edit operation: %s" % operation)
except GenomeDatabaseError as e:
raise e
return True