docs/upgrade_v0.4.2_to_v0.4.3.py

   1 """
   2 This renames avg_lib_size to gel_cut_size and adds an insert length field
   3 to samples_library.
   4 """
   5
   6 import shutil
   7 import sqlite3
   8 import sys
   9
  10 def main(cmdline=None):
  11     if len(cmdline) == 1:
  12        dest='/tmp/fctracker.db'
  13     else:
  14       dest = cmdline[1]
  15     shutil.copy(cmdline[0], dest)
  16     conn = sqlite3.connect(dest)
  17     c = conn.cursor()
  18
  19
  20     c.execute("""alter table samples_library rename to samples_library_temp;""")
  21     c.execute("""CREATE TABLE "samples_library" (
  22     "id" varchar(10) NOT NULL PRIMARY KEY,
  23     "library_name" varchar(100) NOT NULL UNIQUE,
  24     "library_species_id" integer NOT NULL REFERENCES "samples_species" ("id"),
  25     "hidden" bool NOT NULL,
  26     "account_number" varchar(100) NULL,
  27     "cell_line_id" integer NULL REFERENCES "samples_cellline" ("id"),
  28     "condition_id" integer NULL REFERENCES "samples_condition" ("id"),
  29     "antibody_id" integer NULL REFERENCES "samples_antibody" ("id"),
  30     "replicate" smallint unsigned NOT NULL,
  31     "experiment_type_id" integer NOT NULL REFERENCES "samples_experimenttype" ("id"),
  32     "library_type_id" integer NULL REFERENCES "samples_librarytype" ("id"),
  33     "creation_date" date NULL,
  34     "made_for" varchar(50) NOT NULL,
  35     "made_by" varchar(50) NOT NULL,
  36     "stopping_point" varchar(25) NOT NULL,
  37     "amplified_from_sample_id" varchar(10) NULL,
  38     "undiluted_concentration" decimal NULL,
  39     "successful_pM" decimal NULL,
  40     "ten_nM_dilution" bool NOT NULL,
  41     "gel_cut_size" integer NULL,
  42     "insert_size" integer NULL,
  43     "notes" text NOT NULL
  44 );""")
  45     c.execute("""INSERT INTO samples_library
  46 ( id, library_name, library_species_id, hidden, account_number,
  47   cell_line_id,  condition_id, antibody_id, replicate,
  48   experiment_type_id, library_type_id, creation_date, made_for,
  49   made_by, stopping_point, amplified_from_sample_id,
  50   undiluted_concentration, successful_pM, ten_nM_dilution,
  51   gel_cut_size, notes )
  52 SELECT
  53   id, library_name, library_species_id, hidden, account_number,
  54   cell_line_id,  condition_id, antibody_id, replicate,
  55   experiment_type_id, library_type_id, creation_date, made_for,
  56   made_by, stopping_point, amplified_from_sample_id,
  57   undiluted_concentration, successful_pM, ten_nM_dilution,
  58   avg_lib_size, notes
  59 FROM samples_library_temp;
  60 """)
  61     c.execute('DROP TABLE samples_library_temp;')
  62
  63
  64     # modify experiments_lane
  65     c.execute("""alter table experiments_lane rename to experiments_lane_temp;""")
  66     c.execute('''
  67 CREATE TABLE "experiments_lane" (
  68     "id" integer NOT NULL PRIMARY KEY,
  69     "flowcell_id" integer NOT NULL REFERENCES "experiments_flowcell" ("id"),
  70     "lane_number" integer NOT NULL,
  71     "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("id"),
  72     "pM" decimal NOT NULL,
  73     "cluster_estimate" integer,
  74     "status" integer,
  75     "comment" text
  76 );''')
  77
  78     c.execute('''
  79 INSERT INTO experiments_lane
  80 ( id,  flowcell_id, lane_number, library_id, pM, cluster_estimate, comment)
  81 SELECT
  82 id, flowcell_id, lane_number, library_id, pM, cluster_estimate, comment
  83 FROM experiments_lane_temp;
  84 ''')
  85     c.execute('DROP TABLE experiments_lane_temp;')
  86     conn.commit()
  87
  88 if __name__ == "__main__":
  89     main(sys.argv[1:])