From: Diane Trout Date: Tue, 27 Oct 2009 22:07:44 +0000 (+0000) Subject: Turn the library_id back into the primary key for samples_library (SCHEMA CHANGE!) X-Git-Tag: 0.4.0~40 X-Git-Url: http://woldlab.caltech.edu/gitweb/?p=htsworkflow.git;a=commitdiff_plain;h=80978a451d08921d67f0aed8b916c6228f36c818 Turn the library_id back into the primary key for samples_library (SCHEMA CHANGE!) Trying to make it possible to enter the 'library_id' instead of the arbitrary auto-incrementing key when creating a flowcell was turning out to be far too time consuming. It was vastly easier to decide that the 'library id' was a sufficiently unique short value that it could be used directly as the primary key. Its now a char 10 field, unlike the integer primary key to support stanford style library IDs like SL123. Its possible to convert the previous database version to one compatible with this code by running docs/conv_library_id_to_pk_v0.3.1.py --- diff --git a/docs/conv_library_id_to_pk_v0.3.1.py b/docs/conv_library_id_to_pk_v0.3.1.py new file mode 100644 index 0000000..125e7b4 --- /dev/null +++ b/docs/conv_library_id_to_pk_v0.3.1.py @@ -0,0 +1,194 @@ +import sqlite3 + +def map_library_ids(c): + lib_ids = {} + c.execute("""select id, library_id from samples_library""") + for row in c: + surrogate_id = unicode(row[0]) # auto key + artificial_id = unicode(row[1]) # the id printed on the library tubes + lib_ids[surrogate_id] = artificial_id + return lib_ids + +def convert_experiments_lane(c, lib_ids): + """ + Change Library ID in experiments_lane table + """ + c.execute('alter table experiments_lane rename to old_experiments_lane') + c.execute("""CREATE TABLE "experiments_lane" ( + "id" integer NOT NULL PRIMARY KEY, + "flowcell_id" integer NOT NULL REFERENCES "experiments_flowcell" ("id"), + "lane_number" integer NOT NULL, + "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("library_id"), + "pM" decimal NOT NULL, + "cluster_estimate" integer, + "comment" text);""") + + c.execute("""select id, flowcell_id, lane_number, library_id, pM, cluster_estimate, comment + from old_experiments_lane;""") + + new_rows = [] + for row in c: + new_rows.append({'id':row[0], 'flowcell_id':row[1], 'lane_number':row[2], + 'library_id':lib_ids[unicode(row[3])], 'pM':row[4], + 'cluster_estimate':row[5], + 'comment':row[6]}) + + sql = '''insert into experiments_lane + (id, flowcell_id, lane_number, library_id, pM, cluster_estimate, comment) + values + (:id, :flowcell_id, :lane_number, :library_id, :pM, :cluster_estimate, :comment)''' + c.executemany(sql, new_rows) + + c.execute('drop table old_experiments_lane') + +def convert_samples_library_affiliations(c, lib_ids): + """ + Change Library ID in experiments_lane table + """ + c.execute('alter table samples_library_affiliations rename to old_samples_library_affiliations') + c.execute('''CREATE TABLE "samples_library_affiliations" ( + "id" integer NOT NULL PRIMARY KEY, + "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("id"), + "affiliation_id" integer NOT NULL REFERENCES "samples_affiliation" ("id"), + UNIQUE ("library_id", "affiliation_id") +);''') + + c.execute("""select id, library_id, affiliation_id + from old_samples_library_affiliations;""") + + new_rows = [] + for row in c: + new_rows.append({'id':row[0], 'library_id': lib_ids[unicode(row[1])], 'affiliation_id':row[2],}) + + sql = '''insert into samples_library_affiliations + (id, library_id, affiliation_id) + values + (:id, :library_id, :affiliation_id)''' + c.executemany(sql, new_rows) + + c.execute('drop table old_samples_library_affiliations;') + +def convert_samples_library_tags(c, lib_ids): + """ + Change Library ID in samples_library_tags table + """ + c.execute('alter table samples_library_tags rename to old_samples_library_tags') + c.execute('''CREATE TABLE "samples_library_tags" ( + "id" integer NOT NULL PRIMARY KEY, + "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("id"), + "tag_id" integer NOT NULL REFERENCES "samples_tag" ("id"), + UNIQUE ("library_id", "tag_id") +);''') + + c.execute("""select id, library_id, tag_id + from old_samples_library_tags;""") + + new_rows = [] + for row in c: + new_rows.append({'id':row[0], 'library_id': lib_ids[unicode(row[1])], 'tag_id':row[2]}) + + sql = '''insert into samples_library_tags + (id, library_id, tag_id) + values + (:id, :library_id, :tag_id)''' + c.executemany(sql, new_rows) + + c.execute('drop table old_samples_library_tags;') + + +def convert_samples_library(c, lib_ids): + """ + Change Library ID in samples_library_tags table + """ + c.execute('alter table samples_library rename to old_samples_library') + c.execute('''CREATE TABLE "samples_library" ( + "id" varchar(10) NOT NULL PRIMARY KEY, + "library_name" varchar(100) NOT NULL UNIQUE, + "library_species_id" integer NOT NULL REFERENCES "samples_species" ("id"), + "hidden" bool NOT NULL, + "account_number" varchar(100), + "cell_line_id" integer REFERENCES "samples_cellline" ("id"), + "condition_id" integer REFERENCES "samples_condition" ("id"), + "antibody_id" integer REFERENCES "samples_antibody" ("id"), + "replicate" smallint unsigned NOT NULL, + "experiment_type_id" integer NOT NULL REFERENCES "samples_experimenttype" ("id"), + "library_type_id" integer REFERENCES "samples_librarytype" ("id"), + "creation_date" date, + "made_for" varchar(50) NOT NULL, + "made_by" varchar(50) NOT NULL, + "stopping_point" varchar(25) NOT NULL, + "amplified_from_sample_id" varchar(10), + "undiluted_concentration" decimal, + "successful_pM" decimal, + "ten_nM_dilution" bool NOT NULL, + "avg_lib_size" integer, + "notes" text NOT NULL +);''') + + c.execute(""" + select library_id, library_name, library_species_id, hidden, account_number, cell_line_id, + condition_id, antibody_id, replicate, experiment_type_id, library_type_id, + creation_date, made_for, made_by, stopping_point, amplified_from_sample_id, + undiluted_concentration, successful_pM, ten_nM_dilution, avg_lib_size, notes + from old_samples_library;""") + + new_rows = [] + for row in c: + new_rows.append({ + 'id': row[0], + 'library_name': row[1], + 'library_species_id': row[2], + 'hidden': row[3], + 'account_number': row[4], + 'cell_line_id': row[5], + 'condition_id': row[6], + 'antibody_id': row[7], + 'replicate': row[8], + 'experiment_type_id': row[9], + 'library_type_id': row[10], + 'creation_date': row[11], + 'made_for': row[12], + 'made_by': row[13], + 'stopping_point': row[14], + 'amplified_from_sample_id': row[15], + 'undiluted_concentration': row[16], + 'successful_pM': row[17], + 'ten_nM_dilution': row[18], + 'avg_lib_size': row[19], + 'notes': row[20], + }) + + sql = '''insert into samples_library + (id, library_name, library_species_id, hidden, account_number, cell_line_id, + condition_id, antibody_id, replicate, experiment_type_id, library_type_id, + creation_date, made_for, made_by, stopping_point, amplified_from_sample_id, + undiluted_concentration, successful_pM, ten_nM_dilution, avg_lib_size, notes) + values + (:id, :library_name, :library_species_id, :hidden, :account_number, :cell_line_id, + :condition_id, :antibody_id, :replicate, :experiment_type_id, :library_type_id, + :creation_date, :made_for, :made_by, :stopping_point, :amplified_from_sample_id, + :undiluted_concentration, :successful_pM, :ten_nM_dilution, :avg_lib_size, :notes); + ''' + c.executemany(sql, new_rows) + + c.execute('drop table old_samples_library;') + +def convert_library_id(db_path): + conn = sqlite3.connect(db_path) + c = conn.cursor() + + lib_ids = map_library_ids(c) + + convert_experiments_lane(c, lib_ids) + convert_samples_library_affiliations(c, lib_ids) + convert_samples_library_tags(c, lib_ids) + convert_samples_library(c, lib_ids) + + conn.commit() + +def main(): + db_path = '/Users/diane/proj/solexa/gaworkflow/svn/woldlab.db' + convert_library_id(db_path) + +if __name__ == "__main__": + main() diff --git a/htsworkflow/frontend/analysis/admin.py b/htsworkflow/frontend/analysis/admin.py index d3f903e..87a8a81 100644 --- a/htsworkflow/frontend/analysis/admin.py +++ b/htsworkflow/frontend/analysis/admin.py @@ -5,7 +5,7 @@ from django.utils.translation import ugettext_lazy as _ class ProjectOptions(admin.ModelAdmin): list_display = ('ProjTitle','ProjectTasks') list_filter = () - search_fieldsets = ['project_name','=tasks__subject1__library_id','=tasks__subject2__library_id','tasks__subject1__library_name','tasks__subject2__library_name','project_notes'] + search_fieldsets = ['project_name','=tasks__subject1__id','=tasks__subject2__id','tasks__subject1__library_name','tasks__subject2__library_name','project_notes'] fieldsets = ( (None, { 'fields': (('project_name'),('tasks'),('project_notes'))}), @@ -15,7 +15,7 @@ class ProjectOptions(admin.ModelAdmin): class TaskOptions(admin.ModelAdmin): list_display = ('task_name','apply_calc','subject1','subject2','task_params','InProjects','submitted_on','task_status') list_filter = ('apply_calc',) - search_fieldsets = ['task_name','id','=subject1__library_id','=subject2__library_id'] + search_fieldsets = ['task_name','id','=subject1__id','=subject2__id'] fieldsets = ( (None, { 'fields': (('task_name'),('apply_calc'),('subject1'),('subject2'),('task_params')) diff --git a/htsworkflow/frontend/analysis/main.py b/htsworkflow/frontend/analysis/main.py index b5217dc..b57ad20 100644 --- a/htsworkflow/frontend/analysis/main.py +++ b/htsworkflow/frontend/analysis/main.py @@ -67,9 +67,9 @@ def getProjects(request): if (t.apply_calc == 'QuEST' or t.apply_calc == 'WingPeaks' or t.apply_calc == 'MACS'): outputfile += '\n' if t.subject1: - outputfile += '\n' + outputfile += '\n' if t.subject2: - outputfile += '\n' + outputfile += '\n' else: outputfile += '\nBackground Library Missing' else: @@ -80,9 +80,9 @@ def getProjects(request): if (t.apply_calc == 'Methylseq'): outputfile += '\n' if t.subject1: - outputfile += '\n' + outputfile += '\n' if t.subject2: - outputfile += '\n' + outputfile += '\n' else: outputfile += '\nMsp1 Library Missing' else: @@ -91,16 +91,16 @@ def getProjects(request): outputfile += '\n' if (t.apply_calc == 'ProfileReads' or t.apply_calc == 'qPCR'): - outputfile += '\n<'+t.apply_calc+' TaskId="'+t.id.__str__()+'" Name="'+t.task_name+'" Genome="'+t.subject1.library_species.use_genome_build+'" Library="'+t.subject1.library_id+'"/>' + outputfile += '\n<'+t.apply_calc+' TaskId="'+t.id.__str__()+'" Name="'+t.task_name+'" Genome="'+t.subject1.library_species.use_genome_build+'" Library="'+t.subject1.id+'"/>' if (t.apply_calc == 'CompareLibs'): outputfile += '\n' if t.subject1: - outputfile += '\n' + outputfile += '\n' else: outputfile += '\nLibrary Missing' if t.subject2: - outputfile += '\n' + outputfile += '\n' else: outputfile += '\nLibrary Missing' outputfile += '\n'+t.task_params.__str__()+'' diff --git a/htsworkflow/frontend/analysis/models.py b/htsworkflow/frontend/analysis/models.py index e2ddff4..41ecf42 100644 --- a/htsworkflow/frontend/analysis/models.py +++ b/htsworkflow/frontend/analysis/models.py @@ -67,9 +67,9 @@ class Project(models.Model): tstr += '' isregistered = False for t in ptasks: - taskdesc = t.task_name+'
Details: '+t.apply_calc+' on '+t.subject1.library_id + taskdesc = t.task_name+'
Details: '+t.apply_calc+' on '+t.subject1.id if t.subject2 is not None: - taskdesc += ' and '+t.subject2.library_id + taskdesc += ' and '+t.subject2.id taskdesc += ' (TaskId:'+t.id.__str__()+')' tstr += '
' % (taskdesc,replace(t.task_status,'Complete','Complete')) if t.task_status != 'defined': isregistered = True diff --git a/htsworkflow/frontend/eland_config/views.py b/htsworkflow/frontend/eland_config/views.py index 994a410..6169f8a 100644 --- a/htsworkflow/frontend/eland_config/views.py +++ b/htsworkflow/frontend/eland_config/views.py @@ -273,7 +273,7 @@ def getElandConfig(flowcell, regenerate=False): #Convert all newline conventions to unix style for lane in fcObj.lane_set.all(): data.append("# Lane%d: %s | %s" % \ - (lane.lane_number, unicode(lane.library.library_id), lane.library.library_name.replace('%', '%%'))) + (lane.lane_number, unicode(lane.library.id), lane.library.library_name.replace('%', '%%'))) #data.append("GENOME_DIR %s" % (BASE_DIR)) #data.append("CONTAM_DIR %s" % (BASE_DIR)) diff --git a/htsworkflow/frontend/experiments/admin.py b/htsworkflow/frontend/experiments/admin.py index ca3f730..0f00d0e 100644 --- a/htsworkflow/frontend/experiments/admin.py +++ b/htsworkflow/frontend/experiments/admin.py @@ -11,14 +11,14 @@ class DataRunOptions(admin.ModelAdmin): 'run_folder', 'run_note', 'config_params', - '=fcid__lane_1_library__library_id', - '=fcid__lane_2_library__library_id', - '=fcid__lane_3_library__library_id', - '=fcid__lane_4_library__library_id', - '=fcid__lane_5_library__library_id', - '=fcid__lane_6_library__library_id', - '=fcid__lane_7_library__library_id', - '=fcid__lane_8_library__library_id' + '=fcid__lane_1_library__id', + '=fcid__lane_2_library__id', + '=fcid__lane_3_library__id', + '=fcid__lane_4_library__id', + '=fcid__lane_5_library__id', + '=fcid__lane_6_library__id', + '=fcid__lane_7_library__id', + '=fcid__lane_8_library__id' 'fcid__lane_1_library__library_name', 'fcid__lane_2_library__library_name', 'fcid__lane_3_library__library_name', @@ -85,7 +85,7 @@ class FlowCellOptions(admin.ModelAdmin): search_fields = ('flowcell_id', 'sequencer__name', 'cluster_station__name', - '=lane__library__library_id', + '=lane__library__id', 'lane__library__library_name') list_display = ('flowcell_id','run_date','Lanes') list_filter = ('sequencer','cluster_station') @@ -94,7 +94,7 @@ class FlowCellOptions(admin.ModelAdmin): 'fields': ('run_date', ('flowcell_id','cluster_station','sequencer'), ('read_length', 'control_lane', 'paired_end'),) }), #('Lanes:', { - # 'fields' : (('lane__library__library_id', 'lane__pM', 'lane__cluster_estimate'),) + # 'fields' : (('lane__library__id', 'lane__pM', 'lane__cluster_estimate'),) #}), ('Notes:', { 'fields': ('notes',),}), ) diff --git a/htsworkflow/frontend/experiments/experiments.py b/htsworkflow/frontend/experiments/experiments.py index e9a7ab7..7e6c734 100755 --- a/htsworkflow/frontend/experiments/experiments.py +++ b/htsworkflow/frontend/experiments/experiments.py @@ -37,7 +37,7 @@ def flowcell_information(flowcell_id): 'flowcell': lane.flowcell.flowcell_id, 'lane_number': int(lane.lane_number), 'library_name': lane.library.library_name, - 'library_id': lane.library.library_id, + 'library_id': lane.library.id, 'library_species': lane.library.library_species.scientific_name, 'pM': float(lane.pM), 'read_length': fc.read_length @@ -225,14 +225,14 @@ def getLaneLibs(req): mydate = year+month+day outputfile = '' outputfile += '\n' - outputfile += '\n' - outputfile += '\n' - outputfile += '\n' - outputfile += '\n' - outputfile += '\n' - outputfile += '\n' - outputfile += '\n' - outputfile += '\n' + outputfile += '\n' + outputfile += '\n' + outputfile += '\n' + outputfile += '\n' + outputfile += '\n' + outputfile += '\n' + outputfile += '\n' + outputfile += '\n' outputfile += '\n' except ObjectDoesNotExist: outputfile = 'Flowcell entry not found for: '+fcid diff --git a/htsworkflow/frontend/experiments/fixtures/test_flowcells.json b/htsworkflow/frontend/experiments/fixtures/test_flowcells.json index 698813f..41c59f5 100644 --- a/htsworkflow/frontend/experiments/fixtures/test_flowcells.json +++ b/htsworkflow/frontend/experiments/fixtures/test_flowcells.json @@ -109,16 +109,15 @@ {"pk": 1193, "model": "experiments.lane", "fields": { "comment": "No change in cluster numbers, despite slight increase in pM", - "library": 10984, + "library": "10981", "cluster_estimate": 129000, "flowcell": 153, "lane_number": 1, "pM": "8" } }, - {"pk": 10984, "model": "samples.library", + {"pk": "10981", "model": "samples.library", "fields": { - "library_id": "10981", "ten_nM_dilution": false, "avg_lib_size": 400, "library_name": "Paired End Multiplexed Sp-BAC", @@ -147,7 +146,7 @@ {"pk": 1194, "model": "experiments.lane", "fields": { "comment": "", - "library": 11019, + "library": "11016", "cluster_estimate": 152000, "flowcell": 153, "lane_number": 2, @@ -155,10 +154,9 @@ } }, { - "pk": 11019, + "pk": "11016", "model": "samples.library", "fields": { - "library_id": "11016", "ten_nM_dilution": false, "avg_lib_size": 325, "library_name": "Paired End Pfl #3 MP 7/24/9 a", @@ -187,7 +185,7 @@ {"pk": 1195, "model": "experiments.lane", "fields": { "comment": "", - "library": 11042, + "library": "SL039", "cluster_estimate": 162000, "flowcell": 153, "lane_number": 3, @@ -195,10 +193,9 @@ } }, { - "pk": 11042, + "pk": "SL039", "model": "samples.library", "fields": { - "library_id": "SL039", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 99 GM12892", @@ -227,7 +224,7 @@ {"pk": 1196, "model": "experiments.lane", "fields": { "comment": "This lane's library had the second lowest concentration of all the libraries built at the same time (2.05ng/ul)", - "library": 11063, + "library": "11060", "cluster_estimate": 24000, "flowcell": 153, "lane_number": 4, @@ -235,10 +232,9 @@ } }, { - "pk": 11063, + "pk": "11060", "model": "samples.library", "fields": { - "library_id": "11060", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 100 VC_CN_4_M_MBB1185_s1", @@ -267,7 +263,7 @@ {"pk": 1197, "model": "experiments.lane", "fields": { "comment": "", - "library": 11064, + "library": "11061", "cluster_estimate": 140000, "flowcell": 153, "lane_number": 5, @@ -275,10 +271,9 @@ } }, { - "pk": 11064, + "pk": "11061", "model": "samples.library", "fields": { - "library_id": "11061", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 101 VC_CN_4_M_MBB1185_s2", @@ -307,7 +302,7 @@ {"pk": 1198, "model": "experiments.lane", "fields": { "comment": "This lane's library had the lowest concentration of all the libraries built at the same time (1.2ng/ul)", - "library": 11065, + "library": "11062", "cluster_estimate": 2000, "flowcell": 153, "lane_number": 6, @@ -315,10 +310,9 @@ } }, { - "pk": 11065, + "pk": "11062", "model": "samples.library", "fields": { - "library_id": "11062", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 102 VC_AU_8_M_MBB4721_s1", @@ -347,7 +341,7 @@ {"pk": 1199, "model": "experiments.lane", "fields": { "comment": "", - "library": 11066, + "library": "11063", "cluster_estimate": 120000, "flowcell": 153, "lane_number": 7, @@ -355,10 +349,9 @@ } }, { - "pk": 11066, + "pk": "11063", "model": "samples.library", "fields": { - "library_id": "11063", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 103 VC_AU_8_M_MBB4721_s2", @@ -387,7 +380,7 @@ {"pk": 1200, "model": "experiments.lane", "fields": { "comment": "This lane's library had the third lowest concentration of all the libraries built at the same time (5.21ng/ul), but gave perfectly normal cluster numbers", - "library": 11067, + "library": "11064", "cluster_estimate": 157000, "flowcell": 153, "lane_number": 8, @@ -395,10 +388,9 @@ } }, { - "pk": 11067, + "pk": "11064", "model": "samples.library", "fields": { - "library_id": "11064", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 104 VC_CN_7_M_MBB4898_s1", @@ -440,7 +432,7 @@ {"pk": 1185, "model": "experiments.lane", "fields": { "comment": "", - "library": 11038, + "library": "11035", "cluster_estimate": 174000, "flowcell": 152, "lane_number": 1, @@ -448,10 +440,9 @@ } }, { - "pk": 11038, + "pk": "11035", "model": "samples.library", "fields": { - "library_id": "11035", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 95 Gilberto_d3_control_LTA", @@ -480,7 +471,7 @@ {"pk": 1186, "model": "experiments.lane", "fields": { "comment": "", - "library": 11040, + "library": "11037", "cluster_estimate": 173000, "flowcell": 152, "lane_number": 2, @@ -488,10 +479,9 @@ } }, { - "pk": 11040, + "pk": "11037", "model": "samples.library", "fields": { - "library_id": "11037", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 97 Kuntz_PDHT", @@ -520,7 +510,7 @@ {"pk": 1187, "model": "experiments.lane", "fields": { "comment": "", - "library": 11048, + "library": "11045", "cluster_estimate": 198000, "flowcell": 152, "lane_number": 3, @@ -528,10 +518,9 @@ } }, { - "pk": 11048, + "pk": "11045", "model": "samples.library", "fields": { - "library_id": "11045", "ten_nM_dilution": false, "avg_lib_size": 250, "library_name": "FLDN1 8/3/9 anti-AcH3 chip B6 a", @@ -560,17 +549,16 @@ {"pk": 1188, "model": "experiments.lane", "fields": { "comment": "", - "library": 11049, + "library": "11046", "cluster_estimate": 212000, "flowcell": 152, "lane_number": 4, "pM": "7"} }, { - "pk": 11049, + "pk": "11046", "model": "samples.library", "fields": { - "library_id": "11046", "ten_nM_dilution": false, "avg_lib_size": 250, "library_name": "FLDN1 7/8/9 anti-DiMeH3K4 chip B6 a", @@ -599,7 +587,7 @@ {"pk": 1189, "model": "experiments.lane", "fields": { "comment": "", - "library": 11057, + "library": "11054", "cluster_estimate": 49000, "flowcell": 152, "lane_number": 5, @@ -607,10 +595,9 @@ } }, { - "pk": 11057, + "pk": "11054", "model": "samples.library", "fields": { - "library_id": "11054", "ten_nM_dilution": false, "avg_lib_size": 225, "library_name": "HNDHT HLH hnd-1 strain HT115 fed anti-hlh-1 2% fix plate a", @@ -639,7 +626,7 @@ {"pk": 1190, "model": "experiments.lane", "fields": { "comment": "", - "library": 11059, + "library": "11056", "cluster_estimate": 48000, "flowcell": 152, "lane_number": 6, @@ -647,10 +634,9 @@ } }, { - "pk": 11059, + "pk": "11056", "model": "samples.library", "fields": { - "library_id": "11056", "ten_nM_dilution": false, "avg_lib_size": 225, "library_name": "HNDM3 HLH hnd-1 strain mex-3 fed anti-hlh-1 2% fix plate a", @@ -679,7 +665,7 @@ {"pk": 1191, "model": "experiments.lane", "fields": { "comment": "", - "library": 11060, + "library": "11057", "cluster_estimate": 4000, "flowcell": 152, "lane_number": 7, @@ -687,10 +673,9 @@ } }, { - "pk": 11060, + "pk": "11057", "model": "samples.library", "fields": { - "library_id": "11057", "ten_nM_dilution": false, "avg_lib_size": 225, "library_name": "HNDM3 4H8 hnd-1 strain mex-3 fed 4H8 2% fix plate a", @@ -719,7 +704,7 @@ {"pk": 1192, "model": "experiments.lane", "fields": { "comment": "", - "library": 11068, + "library": "11065", "cluster_estimate": 5000, "flowcell": 152, "lane_number": 8, @@ -727,10 +712,9 @@ } }, { - "pk": 11068, + "pk": "11065", "model": "samples.library", "fields": { - "library_id": "11065", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 105 Kuntz PDM3", @@ -772,7 +756,7 @@ {"pk": 1177, "model": "experiments.lane", "fields": { "comment": "", - "library": 11037, + "library": "11034", "cluster_estimate": 177000, "flowcell": 151, "lane_number": 1, @@ -782,7 +766,7 @@ {"pk": 1178, "model": "experiments.lane", "fields": { "comment": "", - "library": 11039, + "library": "11036", "cluster_estimate": 169000, "flowcell": 151, "lane_number": 2, @@ -790,10 +774,9 @@ } }, { - "pk": 11039, + "pk": "11036", "model": "samples.library", "fields": { - "library_id": "11036", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 96 Kuntz_PDE1", @@ -820,10 +803,9 @@ } }, { - "pk": 11037, + "pk": "11034", "model": "samples.library", "fields": { - "library_id": "11034", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 94 Gilberto_d3_denerv_LTA", @@ -850,10 +832,9 @@ } }, { - "pk": 11047, + "pk": "11044", "model": "samples.library", "fields": { - "library_id": "11044", "ten_nM_dilution": false, "avg_lib_size": 225, "library_name": "p300 60h C2 FA KF 12/22/8 a", @@ -883,7 +864,7 @@ "model": "experiments.lane", "fields": { "comment": "", - "library": 11047, + "library": "11044", "cluster_estimate": 196000, "flowcell": 151, "lane_number": 3, @@ -891,10 +872,9 @@ } }, { - "pk": 11047, + "pk": "11044", "model": "samples.library", "fields": { - "library_id": "11044", "ten_nM_dilution": false, "avg_lib_size": 225, "library_name": "p300 60h C2 FA KF 12/22/8 a", @@ -923,7 +903,7 @@ {"pk": 1180, "model": "experiments.lane", "fields": { "comment": "", - "library": 11050, + "library": "11047", "cluster_estimate": 200000, "flowcell": 151, "lane_number": 4, @@ -931,10 +911,9 @@ } }, { - "pk": 11050, + "pk": "11047", "model": "samples.library", "fields": { - "library_id": "11047", "ten_nM_dilution": false, "avg_lib_size": 250, "library_name": "FLDN1 7/8/9 anti-TriMeH3K27 chip B6 a", @@ -963,7 +942,7 @@ {"pk": 1181, "model": "experiments.lane", "fields": { "comment": "", - "library": 11058, + "library": "11055", "cluster_estimate": 104000, "flowcell": 151, "lane_number": 5, @@ -971,10 +950,9 @@ } }, { - "pk": 11058, + "pk": "11055", "model": "samples.library", "fields": { - "library_id": "11055", "ten_nM_dilution": false, "avg_lib_size": 225, "library_name": "HNDHT 4H8 hnd-1 strain HT115 fed 4H8 2% fix plate a", @@ -1003,7 +981,7 @@ {"pk": 1182, "model": "experiments.lane", "fields": { "comment": "", - "library": 11070, + "library": "11067", "cluster_estimate": 168000, "flowcell": 151, "lane_number": 6, @@ -1011,10 +989,9 @@ } }, { - "pk": 11070, + "pk": "11067", "model": "samples.library", "fields": { - "library_id": "11067", "ten_nM_dilution": false, "avg_lib_size": 325, "library_name": "Paired End SP-BAC Barcoding test 250-300 bp", @@ -1043,7 +1020,7 @@ {"pk": 1183, "model": "experiments.lane", "fields": { "comment": "", - "library": 11072, + "library": "11069", "cluster_estimate": 184000, "flowcell": 151, "lane_number": 7, @@ -1051,10 +1028,9 @@ } }, { - "pk": 11072, + "pk": "11069", "model": "samples.library", "fields": { - "library_id": "11069", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired End AG-3d-1 AG domain of floral meristem day 3, rep 1", @@ -1083,7 +1059,7 @@ {"pk": 1184, "model": "experiments.lane", "fields": { "comment": "", - "library": 11073, + "library": "11070", "cluster_estimate": 182000, "flowcell": 151, "lane_number": 8, @@ -1091,10 +1067,9 @@ } }, { - "pk": 11073, + "pk": "11070", "model": "samples.library", "fields": { - "library_id": "11070", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired End AG-5d-1 AG domain of floral meristem day 5, rep 1", diff --git a/htsworkflow/frontend/experiments/tests.py b/htsworkflow/frontend/experiments/tests.py index f9b7527..a27023c 100644 --- a/htsworkflow/frontend/experiments/tests.py +++ b/htsworkflow/frontend/experiments/tests.py @@ -1,4 +1,5 @@ import re +from BeautifulSoup import BeautifulSoup try: import json except ImportError, e: @@ -11,6 +12,8 @@ from htsworkflow.frontend.experiments import models from htsworkflow.frontend.experiments import experiments from htsworkflow.frontend.auth import apidata +LANE_SET = range(1,9) + class ExperimentsTestCases(TestCase): fixtures = ['test_flowcells.json'] @@ -38,7 +41,7 @@ class ExperimentsTestCases(TestCase): self.failUnlessEqual(lane_dict['flowcell'], lane.flowcell.flowcell_id) self.failUnlessEqual(lane_dict['lane_number'], lane.lane_number) self.failUnlessEqual(lane_dict['library_name'], lane.library.library_name) - self.failUnlessEqual(lane_dict['library_id'], lane.library.library_id) + self.failUnlessEqual(lane_dict['library_id'], lane.library.id) self.failUnlessAlmostEqual(lane_dict['pM'], float(lane.pM)) self.failUnlessEqual(lane_dict['library_species'], lane.library.library_species.scientific_name) @@ -60,7 +63,7 @@ class ExperimentsTestCases(TestCase): self.failUnlessEqual(lane_dict['flowcell'], lane.flowcell.flowcell_id) self.failUnlessEqual(lane_dict['lane_number'], lane.lane_number) self.failUnlessEqual(lane_dict['library_name'], lane.library.library_name) - self.failUnlessEqual(lane_dict['library_id'], lane.library.library_id) + self.failUnlessEqual(lane_dict['library_id'], lane.library.id) self.failUnlessAlmostEqual(lane_dict['pM'], float(lane.pM)) self.failUnlessEqual(lane_dict['library_species'], lane.library.library_species.scientific_name) @@ -95,6 +98,32 @@ class ExperimentsTestCases(TestCase): self.failUnlessEqual(library_sl039['library_id'], 'SL039') + def test_raw_id_field(self): + """ + Test ticket:147 + + Library's have IDs, libraries also have primary keys, + we eventually had enough libraries that the drop down combo box was too + hard to filter through, unfortnately we want a field that uses our library + id and not the internal primary key, and raw_id_field uses primary keys. + + This tests to make sure that the value entered in the raw library id field matches + the library id looked up. + """ + expected_ids = [u'10981',u'11016',u'SL039',u'11060', + u'11061',u'11062',u'11063',u'11064'] + self.client.login(username='supertest', password='BJOKL5kAj6aFZ6A5') + response = self.client.get('/admin/experiments/flowcell/153/') + soup = BeautifulSoup(response.content) + for i in range(0,8): + input_field = soup.find(id='id_lane_set-%d-library' % (i,)) + library_field = input_field.findNext('strong') + library_id, library_name = library_field.string.split(':') + # strip leading '#' sign from name + library_id = library_id[1:] + self.failUnlessEqual(library_id, expected_ids[i]) + self.failUnlessEqual(input_field['value'], library_id) + class TestEmailNotify(TestCase): fixtures = ['test_flowcells.json'] diff --git a/htsworkflow/frontend/samples/admin.py b/htsworkflow/frontend/samples/admin.py index 074ad16..ef815a2 100644 --- a/htsworkflow/frontend/samples/admin.py +++ b/htsworkflow/frontend/samples/admin.py @@ -95,14 +95,14 @@ class LibraryOptions(admin.ModelAdmin): save_as = True save_on_top = True search_fields = ( - 'library_id', + 'id', 'library_name', 'cell_line__cellline_name', 'library_species__scientific_name', 'library_species__common_name', ) list_display = ( - 'library_id', + 'id', #'aligned_reads', #'DataRun', 'library_name', @@ -133,11 +133,11 @@ class LibraryOptions(admin.ModelAdmin): 'condition', 'stopping_point', 'hidden') - list_display_links = ('library_id', 'library_name',) + list_display_links = ('id', 'library_name',) fieldsets = ( (None, { 'fields': ( - ('library_id','library_name','hidden'), + ('id','library_name','hidden'), ('library_species'), ('library_type', 'experiment_type', 'replicate'), ('cell_line','condition','antibody'),) diff --git a/htsworkflow/frontend/samples/fixtures/test_samples.json b/htsworkflow/frontend/samples/fixtures/test_samples.json index ae99872..edb76e1 100644 --- a/htsworkflow/frontend/samples/fixtures/test_samples.json +++ b/htsworkflow/frontend/samples/fixtures/test_samples.json @@ -15,9 +15,8 @@ "date_joined": "2009-01-01 00:01:01" } }, - {"pk": 10984, "model": "samples.library", + {"pk": "10981", "model": "samples.library", "fields": { - "library_id": "10981", "ten_nM_dilution": false, "avg_lib_size": 400, "library_name": "Paired End Multiplexed Sp-BAC", @@ -44,10 +43,9 @@ } }, { - "pk": 11019, + "pk": "11016", "model": "samples.library", "fields": { - "library_id": "11016", "ten_nM_dilution": false, "avg_lib_size": 325, "library_name": "Paired End Pfl #3 MP 7/24/9 a", @@ -74,10 +72,9 @@ } }, { - "pk": 11042, + "pk": "11039", "model": "samples.library", "fields": { - "library_id": "11039", "ten_nM_dilution": false, "avg_lib_size": 300, "library_name": "Paired ends 99 GM12892", @@ -106,10 +103,9 @@ } }, { - "pk": 11006, + "pk": "11003", "model": "samples.library", "fields": { - "library_id": "11003", "ten_nM_dilution": false, "avg_lib_size": 325, "library_name": "Paired End Pfl #3 MP 7/24/9", diff --git a/htsworkflow/frontend/samples/models.py b/htsworkflow/frontend/samples/models.py index 57c7888..a5b3327 100644 --- a/htsworkflow/frontend/samples/models.py +++ b/htsworkflow/frontend/samples/models.py @@ -128,8 +128,7 @@ class LibraryType(models.Model): return unicode(self.name) class Library(models.Model): - id = models.AutoField(primary_key=True) - library_id = models.CharField(max_length=30, db_index=True, unique=True) + id = models.CharField(max_length=10, primary_key=True) library_name = models.CharField(max_length=100, unique=True) library_species = models.ForeignKey(Species) # new field 2008 Mar 5, alter table samples_library add column "hidden" NOT NULL default 0; @@ -178,12 +177,12 @@ class Library(models.Model): notes = models.TextField(blank=True) def __unicode__(self): - return u'#%s: %s' % (self.library_id, self.library_name) + return u'#%s: %s' % (self.id, self.library_name) class Meta: verbose_name_plural = "libraries" #ordering = ["-creation_date"] - ordering = ["-library_id"] + ordering = ["-id"] def antibody_name(self): str =''+self.antibody.nickname+'' @@ -218,15 +217,15 @@ class Library(models.Model): return u'%s' % ( ", ".join(ar)) def DataRun(self): - str ='Data Run' + str ='Data Run' return str DataRun.allow_tags = True def aligned_m_reads(self): - return getLibReads(self.library_id) + return getLibReads(self.id) def aligned_reads(self): - res = getLibReads(self.library_id) + res = getLibReads(self.id) # Check data sanity if res[2] != "OK": @@ -263,7 +262,7 @@ class Library(models.Model): @models.permalink def get_absolute_url(self): - return ('htsworkflow.frontend.samples.views.library_to_flowcells', [str(self.library_id)]) + return ('htsworkflow.frontend.samples.views.library_to_flowcells', [str(self.id)]) class HTSUser(User): """ diff --git a/htsworkflow/frontend/samples/tests.py b/htsworkflow/frontend/samples/tests.py index d85a466..f8eaf0f 100644 --- a/htsworkflow/frontend/samples/tests.py +++ b/htsworkflow/frontend/samples/tests.py @@ -71,7 +71,7 @@ def create_db(obj): Library.objects.all().delete() obj.library_10001 = Library( - library_id = 10001, + id = "10001", library_name = 'C2C12 named poorly', library_species = obj.species_human, experiment_type = obj.experiment_rna_seq, @@ -83,7 +83,7 @@ def create_db(obj): ) obj.library_10001.save() obj.library_10002 = Library( - library_id = 10002, + id = "10002", library_name = 'Worm named poorly', library_species = obj.species_human, experiment_type = obj.experiment_rna_seq, @@ -124,15 +124,15 @@ class SampleWebTestCase(TestCase): def test_library_info(self): for lib in Library.objects.all(): - lib_dict = library_dict(lib.library_id) - url = '/samples/library/%s/json' % (lib.library_id,) + lib_dict = library_dict(lib.id) + url = '/samples/library/%s/json' % (lib.id,) lib_response = self.client.get(url, apidata) self.failUnlessEqual(lib_response.status_code, 200) lib_json = json.loads(lib_response.content) for d in [lib_dict, lib_json]: # amplified_from_sample is a link to the library table, - # I want to use the "library_id" for the data lookups not + # I want to use the "id" for the data lookups not # the embedded primary key. # It gets slightly confusing on how to implement sending the right id # since amplified_from_sample can be null @@ -144,7 +144,6 @@ class SampleWebTestCase(TestCase): self.failUnlessEqual(d['experiment_type'], lib.experiment_type.name) self.failUnlessEqual(d['experiment_type_id'], lib.experiment_type_id) self.failUnlessEqual(d['id'], lib.id) - self.failUnlessEqual(d['library_id'], lib.library_id) self.failUnlessEqual(d['library_name'], lib.library_name) self.failUnlessEqual(d['library_species'], lib.library_species.scientific_name) self.failUnlessEqual(d['library_species_id'], lib.library_species_id) diff --git a/htsworkflow/frontend/samples/views.py b/htsworkflow/frontend/samples/views.py index b0a61f4..20c3d98 100644 --- a/htsworkflow/frontend/samples/views.py +++ b/htsworkflow/frontend/samples/views.py @@ -41,11 +41,11 @@ def create_library_context(cl): #for lib in library_items.object_list: for lib in cl.result_list: summary = {} - summary['library_id'] = lib.library_id + summary['library_id'] = lib.id summary['library_name'] = lib.library_name summary['species_name' ] = lib.library_species.scientific_name if lib.amplified_from_sample is not None: - summary['amplified_from'] = lib.amplified_from_sample.library_id + summary['amplified_from'] = lib.amplified_from_sample.id else: summary['amplified_from'] = '' lanes_run = 0 @@ -63,7 +63,7 @@ def library(request): # build changelist fcl = ChangeList(request, Library, list_filter=['affiliations', 'library_species'], - search_fields=['library_id', 'library_name', 'amplified_from_sample__library_id'], + search_fields=['id', 'library_name', 'amplified_from_sample__id'], list_per_page=200, queryset=Library.objects.filter(hidden__exact=0) ) @@ -90,7 +90,7 @@ def library_to_flowcells(request, lib_id): """ try: - lib = Library.objects.get(library_id=lib_id) + lib = Library.objects.get(id=lib_id) except: return HttpResponse("Library %s does not exist" % (lib_id)) @@ -446,7 +446,7 @@ def _files(flowcell_id, lane): return '(' + '|'.join(output) + ')' def library_id_to_admin_url(request, lib_id): - lib = Library.objects.get(library_id=lib_id) + lib = Library.objects.get(id=lib_id) return HttpResponseRedirect('/admin/samples/library/%s' % (lib.id,)) def library_dict(library_id): @@ -455,7 +455,7 @@ def library_dict(library_id): return None if nothing was found """ try: - lib = Library.objects.get(library_id = library_id) + lib = Library.objects.get(id = library_id) except Library.DoesNotExist, e: return None @@ -472,7 +472,7 @@ def library_dict(library_id): 'experiment_type': lib.experiment_type.name, 'experiment_type_id': lib.experiment_type_id, 'id': lib.id, - 'library_id': lib.library_id, + 'library_id': lib.id, 'library_name': lib.library_name, 'library_species': lib.library_species.scientific_name, 'library_species_id': lib.library_species_id, diff --git a/htsworkflow/frontend/templates/experiments/flowcellSheet.html b/htsworkflow/frontend/templates/experiments/flowcellSheet.html index f639ff6..fb43753 100644 --- a/htsworkflow/frontend/templates/experiments/flowcellSheet.html +++ b/htsworkflow/frontend/templates/experiments/flowcellSheet.html @@ -35,7 +35,7 @@ TD {% for lane in fc.lane_set.all %} - + {% endfor %} diff --git a/htsworkflow/frontend/templates/experiments/started_email.html b/htsworkflow/frontend/templates/experiments/started_email.html index 9434c93..d75e4f1 100644 --- a/htsworkflow/frontend/templates/experiments/started_email.html +++ b/htsworkflow/frontend/templates/experiments/started_email.html @@ -4,8 +4,8 @@ which is a {{ flowcell.read_length }} base pair {% if flowcell.paired_end %}pair

{% for lane in lanes %} Lane #{{ lane.lane_number }} : - -{{ lane.library.library_id }} + +{{ lane.library.id }} {{ lane.library.library_name }}
{% endfor %}

diff --git a/htsworkflow/frontend/templates/experiments/started_email.txt b/htsworkflow/frontend/templates/experiments/started_email.txt index 5a2c1ca..9b11a74 100644 --- a/htsworkflow/frontend/templates/experiments/started_email.txt +++ b/htsworkflow/frontend/templates/experiments/started_email.txt @@ -3,8 +3,8 @@ which is a {{ flowcell.read_length }} base pair {% if flowcell.paired_end %}pair Lane : (Library Id) Library Name (Cluster Estimate) {% for lane in lanes %} -Lane #{{ lane.lane_number }} : ({{ lane.library.library_id }}) {{ lane.library.library_name }} ({{ lane.cluster_estimate }}) - http://jumpgate.caltech.edu/library/{{ lane.library.library_id }} +Lane #{{ lane.lane_number }} : ({{ lane.library.id }}) {{ lane.library.library_name }} ({{ lane.cluster_estimate }}) + http://jumpgate.caltech.edu/library/{{ lane.library.id }} {% endfor %} The raw data should be available at the following link when diff --git a/htsworkflow/frontend/templates/samples/library_detail.html b/htsworkflow/frontend/templates/samples/library_detail.html index c457bda..947691c 100644 --- a/htsworkflow/frontend/templates/samples/library_detail.html +++ b/htsworkflow/frontend/templates/samples/library_detail.html @@ -30,7 +30,7 @@ {% block content %}

About this library

- Library ID: {{ lib.library_id }}
+ Library ID: {{ lib.id }}
Name: {{ lib.library_name }}
Species: {{ lib.library_species.scientific_name }}
Affiliations:
TasksJob Status
%s%s
Solexa Library Number{{ lane.library.library_id }}{{ lane.library.id }}