This text file includes step by step SQL commands to convert your DB into our schema...

diff --git a/htswfrontend/htswfrontend/Conv_CaltechDB_Aug142008.txt b/htswfrontend/htswfrontend/Conv_CaltechDB_Aug142008.txt
new file mode 100644 (file)
index 0000000..b26de07
--- /dev/null
+++ b/htswfrontend/htswfrontend/Conv_CaltechDB_Aug142008.txt
@@ -0,0 +1,52 @@
+Conversion SQLs for Caltech DB (schema of 2008Aug08 @ 5:06 PM) 
+______________________________________________________________
+
+
+Step by step do the following:
+
+''' Note: Instead of '?' you can put one of your machine names.
+sqlite> ALTER TABLE fctracker_flowcell ADD cluster_mac_id varchar(50) NOT NULL DEFAULT '?';
+sqlite> ALTER TABLE fctracker_flowcell ADD seq_mac_id varchar(50) NOT NULL DEFAULT '?';
+sqlite> ALTER TABLE fctracker_library RENAME TO PREV_fctracker_library;
+
+Now, do a syncdb. The output should look like this (assuming you have migrated to the new models.py):
+sh-3.2# pym syncdb
+Creating table fctracker_cellline
+Creating table fctracker_library
+Creating table fctracker_primer
+Creating table fctracker_antibody
+Creating table fctracker_condition
+Creating table exp_track_datarun
+Creating table exp_track_flowcell
+Creating table analys_track_project
+Creating table analys_track_task
+Creating table htsw_reports_progressreport
+Installing index for fctracker.Library model
+Failed to install index for fctracker.Library model: index fctracker_library_library_species_id already existsInstalling index for fctracker.Primer model
+Installing index for fctracker.Antibody model
+Installing index for exp_track.DataRun model
+Installing index for exp_track.FlowCell model
+Installing index for analys_track.Task model
+Installing index for htsw_reports.ProgressReport model
+sh-3.2# 
+
+''' Copy all records from "fctracker_flowcell" to "exp_track_flowcell" table. (Why? Because, Flowcell table moves now from the "fctracker" to the "exp_track" component). 
+sqlite> insert into exp_track_flowcell select * from fctracker_flowcell;
+
+''' Now to fctracker_library, a bit more complex case
+
+'''Back to the sqlite prompt..
+sqlite> insert into fctracker_cellline (cellline_name,notes) values('Unknown','Unknown');
+sqlite> insert into fctracker_condition (condition_name,notes) values('Unknown','Unknown');
+''' Now we can put 1 in these fields for the Library insert.
+''' Note: avg_lib_size field is missing in Caltech DB (although it's in the models.py Trac), so I put default value 225.
+
+''' Now the actual migration to the new fctracker_library table
+sqlite> INSERT INTO fctracker_library (id,library_id,library_name,library_species_id,experiment_type,cell_line_id,condition_id,made_by,creation_date,made_for,stopping_point,amplified_from_sample_id,undiluted_concentration,ten_nM_dilution,successful_pM,avg_lib_size,notes) select library_id as id,library_id,library_name,library_species_id,'unknown',1,1,made_by,creation_date,made_for,stopping_point,amplified_from_sample_id,undiluted_concentration,ten_nM_dilution,successful_pM,0,notes from PREV_fctracker_library; 
+
+''' Set the right values for "experiment_type"
+sqlite> update fctracker_library set experiment_type = 'RNA-seq' where id in (select library_id from PREV_fctracker_library where RNAseq = 1);
+''' YOU CAN ADD SIMILAR SQL CMD TO SET THE VALUE FOR "avg_lib_size" FIELD (WHICH IS NOW SET TO 0) ...  
+
+----------------------------------------------------------------------------------------
+THAT SHOULD BE IT --- NOW YOUR WEB SITE SHOULD SUCESSFULY LOAD THE NEW DB WITH YOUR DATA.
\ No newline at end of file