docs/upgrade_v0.2.6_to_v0.3.py

   1 import shutil
   2 import sqlite3
   3 import sys
   4
   5 def main(cmdline=None):
   6     if len(cmdline) == 1:
   7        dest='/tmp/fctracker.db'
   8     else:
   9       dest = cmdline[1]
  10     shutil.copy(cmdline[0], dest)
  11     conn = sqlite3.connect(dest)
  12     c = conn.cursor()
  13
  14     #Copy user id's from auth User to new HTSUser
  15     c.execute('INSERT INTO samples_htsuser (user_ptr_id) SELECT id FROM auth_user;')
  16
  17     c.execute("""CREATE TEMPORARY TABLE experiments_flowcell_temp (
  18     "id" integer NOT NULL PRIMARY KEY,
  19     "flowcell_id" varchar(20) NOT NULL UNIQUE,
  20     "run_date" datetime NOT NULL,
  21     "advanced_run" bool NOT NULL,
  22     "paired_end" bool NOT NULL,
  23     "read_length" integer NOT NULL,
  24     "lane_1_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  25     "lane_2_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  26     "lane_3_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  27     "lane_4_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  28     "lane_5_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  29     "lane_6_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  30     "lane_7_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  31     "lane_8_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  32     "lane_1_pM" decimal NOT NULL,
  33     "lane_2_pM" decimal NOT NULL,
  34     "lane_3_pM" decimal NOT NULL,
  35     "lane_4_pM" decimal NOT NULL,
  36     "lane_5_pM" decimal NOT NULL,
  37     "lane_6_pM" decimal NOT NULL,
  38     "lane_7_pM" decimal NOT NULL,
  39     "lane_8_pM" decimal NOT NULL,
  40     "lane_1_cluster_estimate" integer NULL,
  41     "lane_2_cluster_estimate" integer NULL,
  42     "lane_3_cluster_estimate" integer NULL,
  43     "lane_4_cluster_estimate" integer NULL,
  44     "lane_5_cluster_estimate" integer NULL,
  45     "lane_6_cluster_estimate" integer NULL,
  46     "lane_7_cluster_estimate" integer NULL,
  47     "lane_8_cluster_estimate" integer NULL,
  48     "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
  49     "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
  50     "notes" text NOT NULL
  51 );""")
  52     c.execute("""INSERT INTO experiments_flowcell_temp SELECT id,flowcell_id,run_date,advanced_run,paired_end,read_length,lane_1_library_id,lane_2_library_id,lane_3_library_id,lane_4_library_id,lane_5_library_id,lane_6_library_id,lane_7_library_id,lane_8_library_id,lane_1_pM,lane_2_pM,lane_3_pM,lane_4_pM,lane_5_pM,lane_6_pM,lane_7_pM,lane_8_pM,lane_1_cluster_estimate,lane_2_cluster_estimate,lane_3_cluster_estimate,lane_4_cluster_estimate,lane_5_cluster_estimate,lane_6_cluster_estimate,lane_7_cluster_estimate,lane_8_cluster_estimate,cluster_station_id,sequencer_id,notes FROM experiments_flowcell;
  53 """)
  54     c.execute('DROP TABLE experiments_flowcell;')
  55     c.execute("""CREATE TABLE experiments_flowcell (
  56     "id" integer NOT NULL PRIMARY KEY,
  57     "flowcell_id" varchar(20) NOT NULL UNIQUE,
  58     "run_date" datetime NOT NULL,
  59     "advanced_run" bool NOT NULL,
  60     "paired_end" bool NOT NULL,
  61     "read_length" integer NOT NULL,
  62     "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
  63     "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
  64     "notes" text NOT NULL
  65 );
  66 """)
  67     c.execute("""INSERT INTO experiments_flowcell SELECT id,flowcell_id,run_date,advanced_run,paired_end,read_length,cluster_station_id,sequencer_id,notes FROM experiments_flowcell_temp;
  68 """)
  69     c.execute("""select id from experiments_flowcell;""")
  70     flowcell_pks = [ r[0] for r in c ]
  71
  72     lane_insert = """INSERT INTO experiments_lane (
  73   flowcell_id,
  74   lane_number,
  75   library_id,
  76   pM,
  77   cluster_estimate )
  78 SELECT id, %(lane)d, lane_%(lane)d_library_id, lane_%(lane)d_pM,
  79        lane_%(lane)d_cluster_estimate
  80 FROM experiments_flowcell_temp
  81 WHERE id=%(id)d;"""
  82
  83     for pk in flowcell_pks:
  84         for lane in (1,2,3,4,5,6,7,8):
  85             c.execute( lane_insert % {'lane': int(lane), 'id': int(pk)} )
  86
  87     c.execute('DROP TABLE experiments_flowcell_temp;')
  88
  89     #Add control_lane column
  90     c.execute('ALTER TABLE experiments_flowcell ADD COLUMN "control_lane" integer NULL;')
  91
  92     conn.commit()
  93
  94 if __name__ == "__main__":
  95     main(sys.argv[1:])