scripts/drop_lanes_from_flowcell.sql

   1 BEGIN TRANSACTION;
   2 CREATE TEMPORARY TABLE experiments_flowcell_temp (
   3     "id" integer NOT NULL PRIMARY KEY,
   4     "flowcell_id" varchar(20) NOT NULL UNIQUE,
   5     "run_date" datetime NOT NULL,
   6     "advanced_run" bool NOT NULL,
   7     "paired_end" bool NOT NULL,
   8     "read_length" integer NOT NULL,
   9     "lane_1_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  10     "lane_2_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  11     "lane_3_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  12     "lane_4_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  13     "lane_5_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  14     "lane_6_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  15     "lane_7_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  16     "lane_8_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
  17     "lane_1_pM" decimal NOT NULL,
  18     "lane_2_pM" decimal NOT NULL,
  19     "lane_3_pM" decimal NOT NULL,
  20     "lane_4_pM" decimal NOT NULL,
  21     "lane_5_pM" decimal NOT NULL,
  22     "lane_6_pM" decimal NOT NULL,
  23     "lane_7_pM" decimal NOT NULL,
  24     "lane_8_pM" decimal NOT NULL,
  25     "lane_1_cluster_estimate" integer NULL,
  26     "lane_2_cluster_estimate" integer NULL,
  27     "lane_3_cluster_estimate" integer NULL,
  28     "lane_4_cluster_estimate" integer NULL,
  29     "lane_5_cluster_estimate" integer NULL,
  30     "lane_6_cluster_estimate" integer NULL,
  31     "lane_7_cluster_estimate" integer NULL,
  32     "lane_8_cluster_estimate" integer NULL,
  33     "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
  34     "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
  35     "notes" text NOT NULL
  36 );
  37 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;
  38 DROP TABLE experiments_flowcell;
  39 CREATE TABLE experiments_flowcell (
  40     "id" integer NOT NULL PRIMARY KEY,
  41     "flowcell_id" varchar(20) NOT NULL UNIQUE,
  42     "run_date" datetime NOT NULL,
  43     "advanced_run" bool NOT NULL,
  44     "paired_end" bool NOT NULL,
  45     "read_length" integer NOT NULL,
  46     "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
  47     "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
  48     "notes" text NOT NULL
  49 );
  50 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;
  51 DROP TABLE experiments_flowcell_temp;
  52 COMMIT;