-- This file is contributed by Allen Day and is specific to his own work.
-- It is included for illustrative purposes, these tables and views
-- are not part of the mage module.  It gives an example of how the
-- generic element and elementresult tables in the mage module can be 
-- used to represent different types of data.  Another way to do this is
-- with PostgreSQL table subclasses. There is a performance gain for
-- typical usage in splitting data into subclass tables rather than
-- using views to filter.

CREATE TABLE affymetrixprobeset (
  element_id serial not null,
  primary key (element_id),
  feature_id int null,
  foreign key (feature_id) references feature (feature_id) on delete set null INITIALLY DEFERRED,
  arraydesign_id int not null,
  foreign key (arraydesign_id) references arraydesign (arraydesign_id) on delete cascade INITIALLY DEFERRED,
  type_id int null,
  foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
  dbxref_id int null,
  foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
  name varchar(255) NULL
);
--ALTER TABLE affymetrixprobeset ADD CONSTRAINT affymetrixprobeset_c2 FOREIGN KEY (dbxref_id)        REFERENCES dbxref        (dbxref_id);
--ALTER TABLE affymetrixprobeset ADD CONSTRAINT affymetrixprobeset_c3 FOREIGN KEY (arraydesign_id)   REFERENCES arraydesign   (arraydesign_id);
--ALTER TABLE affymetrixprobeset ADD CONSTRAINT affymetrixprobeset_c4 FOREIGN KEY (type_id)          REFERENCES cvterm        (cvterm_id);
--ALTER TABLE affymetrixprobeset ADD CONSTRAINT affymetrixprobeset_c5 FOREIGN KEY (feature_id)       REFERENCES feature       (feature_id);
CREATE INDEX affymetrixprobeset_idx1 ON affymetrixprobeset (name);
CREATE INDEX affymetrixprobeset_idx2 ON affymetrixprobeset (feature_id);
CREATE INDEX affymetrixprobeset_idx3 ON affymetrixprobeset (dbxref_id);
CREATE INDEX affymetrixprobeset_idx4 ON affymetrixprobeset (arraydesign_id);
CREATE INDEX affymetrixprobeset_idx5 ON affymetrixprobeset (type_id);
CREATE UNIQUE INDEX affymetrixprobeset_idx6 ON affymetrixprobeset (name,arraydesign_id);
CREATE UNIQUE INDEX affymetrixprobeset_idx7 ON affymetrixprobeset (feature_id,arraydesign_id);

CREATE TABLE affymetrixprobe (
  element_id serial not null,
  primary key (element_id),
  feature_id int null,
  foreign key (feature_id) references feature (feature_id) on delete set null INITIALLY DEFERRED,
  arraydesign_id int not null,
  foreign key (arraydesign_id) references arraydesign (arraydesign_id) on delete cascade INITIALLY DEFERRED,
  type_id int null,
  foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
  dbxref_id int null,
  foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
  name varchar(255) NULL,
  affymetrixprobeset_id int NULL,
  row int NOT NULL,
  col int NOT NULL
);
--ALTER TABLE affymetrixprobe ADD CONSTRAINT affymetrixprobe_c1 FOREIGN KEY (feature_id)            REFERENCES feature       (feature_id);
--ALTER TABLE affymetrixprobe ADD CONSTRAINT affymetrixprobe_c2 FOREIGN KEY (dbxref_id)             REFERENCES dbxref        (dbxref_id);
--ALTER TABLE affymetrixprobe ADD CONSTRAINT affymetrixprobe_c3 FOREIGN KEY (arraydesign_id)        REFERENCES arraydesign   (arraydesign_id);
--ALTER TABLE affymetrixprobe ADD CONSTRAINT affymetrixprobe_c4 FOREIGN KEY (type_id)               REFERENCES cvterm        (cvterm_id);
ALTER TABLE affymetrixprobe ADD CONSTRAINT affymetrixprobe_c5 FOREIGN KEY (affymetrixprobeset_id) REFERENCES affymetrixprobeset (element_id);
CREATE INDEX affymetrixprobe_idx1 ON affymetrixprobe (affymetrixprobeset_id);
CREATE INDEX affymetrixprobe_idx2 ON affymetrixprobe (name);
CREATE INDEX affymetrixprobe_idx3 ON affymetrixprobe (feature_id);
CREATE INDEX affymetrixprobe_idx4 ON affymetrixprobe (dbxref_id);
CREATE INDEX affymetrixprobe_idx5 ON affymetrixprobe (arraydesign_id);
CREATE INDEX affymetrixprobe_idx6 ON affymetrixprobe (type_id);

CREATE TABLE affymetrixcel (
  mean float NOT NULL,
  sd float NOT NULL,
  pixels int NOT NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixcel ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixcel ADD CONSTRAINT affymetrixcel_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobe (element_id);
ALTER TABLE affymetrixcel ADD CONSTRAINT affymetrixcel_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixcel_idx1 ON affymetrixcel (mean);
CREATE INDEX affymetrixcel_idx2 ON affymetrixcel (sd);
CREATE INDEX affymetrixcel_idx3 ON affymetrixcel (pixels);
CREATE INDEX affymetrixcel_idx4 ON affymetrixcel (element_id);
CREATE INDEX affymetrixcel_idx5 ON affymetrixcel (quantification_id);

CREATE TABLE affymetrixsnp (
  call smallint NULL,
  call_p float NULL,
  _signal _float4 NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixsnp ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixsnp ADD CONSTRAINT affymetrixsnp_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixsnp ADD CONSTRAINT affymetrixsnp_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixsnp_idx1 ON affymetrixsnp (call);
CREATE INDEX affymetrixsnp_idx2 ON affymetrixsnp (signal);
CREATE INDEX affymetrixsnp_idx4 ON affymetrixsnp (element_id);
CREATE INDEX affymetrixsnp_idx5 ON affymetrixsnp (quantification_id);
CREATE INDEX affymetrixsnp_idx6 ON affymetrixsnp (call_p);

CREATE TABLE affymetrixpsi (
  q1pas INT NULL,
  q1pbs INT NULL,
  q1mas INT NULL,
  q1mbs INT NULL,
  q1paa INT NULL,
  q1pba INT NULL,
  q1maa INT NULL,
  q1mba INT NULL,

  q2pas INT NULL,
  q2pbs INT NULL,
  q2mas INT NULL,
  q2mbs INT NULL,
  q2paa INT NULL,
  q2pba INT NULL,
  q2maa INT NULL,
  q2mba INT NULL,

  q3pas INT NULL,
  q3pbs INT NULL,
  q3mas INT NULL,
  q3mbs INT NULL,
  q3paa INT NULL,
  q3pba INT NULL,
  q3maa INT NULL,
  q3mba INT NULL,

  q4pas INT NULL,
  q4pbs INT NULL,
  q4mas INT NULL,
  q4mbs INT NULL,
  q4paa INT NULL,
  q4pba INT NULL,
  q4maa INT NULL,
  q4mba INT NULL,

  q5pas INT NULL,
  q5pbs INT NULL,
  q5mas INT NULL,
  q5mbs INT NULL,
  q5paa INT NULL,
  q5pba INT NULL,
  q5maa INT NULL,
  q5mba INT NULL,

  q6pas INT NULL,
  q6pbs INT NULL,
  q6mas INT NULL,
  q6mbs INT NULL,
  q6paa INT NULL,
  q6pba INT NULL,
  q6maa INT NULL,
  q6mba INT NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixpsi ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixpsi ADD CONSTRAINT affymetrixpsi_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixpsi ADD CONSTRAINT affymetrixpsi_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixpsi_idx4 ON affymetrixpsi (element_id);
CREATE INDEX affymetrixpsi_idx5 ON affymetrixpsi (quantification_id);


CREATE TABLE affymetrixmas5 (
  call char(1) NOT NULL,
  call_p float NOT NULL,
  statpairs int NOT NULL,
  statpairsused int NOT NULL,
  z float NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixmas5 ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixmas5 ADD CONSTRAINT affymetrixmas5_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixmas5 ADD CONSTRAINT affymetrixmas5_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixmas5_idx1 ON affymetrixmas5 (signal);
CREATE INDEX affymetrixmas5_idx2 ON affymetrixmas5 (call);
CREATE INDEX affymetrixmas5_idx3 ON affymetrixmas5 (call_p);
CREATE INDEX affymetrixmas5_idx4 ON affymetrixmas5 (element_id);
CREATE INDEX affymetrixmas5_idx5 ON affymetrixmas5 (quantification_id);
CREATE INDEX affymetrixmas5_idx6 ON affymetrixmas5 (z);

CREATE TABLE affymetrixdchip (
  z float NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixdchip ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixdchip ADD CONSTRAINT affymetrixdchip_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixdchip ADD CONSTRAINT affymetrixdchip_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixdchip_idx1 ON affymetrixdchip (element_id);
CREATE INDEX affymetrixdchip_idx2 ON affymetrixdchip (quantification_id);
CREATE INDEX affymetrixdchip_idx3 ON affymetrixdchip (signal);
CREATE INDEX affymetrixdchip_idx6 ON affymetrixdchip (z);

CREATE TABLE affymetrixvsn (
  z float NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixvsn ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixvsn ADD CONSTRAINT affymetrixvsn_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixvsn ADD CONSTRAINT affymetrixvsn_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixvsn_idx1 ON affymetrixvsn (element_id);
CREATE INDEX affymetrixvsn_idx2 ON affymetrixvsn (quantification_id);
CREATE INDEX affymetrixvsn_idx3 ON affymetrixvsn (signal);
CREATE INDEX affymetrixvsn_idx6 ON affymetrixvsn (z);

CREATE TABLE affymetrixsea (
) INHERITS ( elementresult );
ALTER TABLE affymetrixsea ADD PRIMARY KEY (elementresult_id);
--ALTER TABLE affymetrixsea ADD CONSTRAINT affymetrixsea_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixsea ADD CONSTRAINT affymetrixsea_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixsea_idx1 ON affymetrixsea (element_id);
CREATE INDEX affymetrixsea_idx2 ON affymetrixsea (quantification_id);
CREATE INDEX affymetrixsea_idx3 ON affymetrixsea (signal);

CREATE TABLE affymetrixplier (
) INHERITS ( elementresult );
ALTER TABLE affymetrixplier ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixplier ADD CONSTRAINT affymetrixplier_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixplier ADD CONSTRAINT affymetrixplier_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixplier_idx1 ON affymetrixplier (element_id);
CREATE INDEX affymetrixplier_idx2 ON affymetrixplier (quantification_id);
CREATE INDEX affymetrixplier_idx3 ON affymetrixplier (signal);

CREATE TABLE affymetrixdabg (
  call_p float NOT NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixdabg ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixdabg ADD CONSTRAINT affymetrixdabg_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixdabg ADD CONSTRAINT affymetrixdabg_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixdabg_idx1 ON affymetrixdabg (element_id);
CREATE INDEX affymetrixdabg_idx2 ON affymetrixdabg (quantification_id);
CREATE INDEX affymetrixdabg_idx3 ON affymetrixdabg (call_p);

CREATE TABLE affymetrixrma (
  z float NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixrma ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixrma ADD CONSTRAINT affymetrixrma_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixrma ADD CONSTRAINT affymetrixrma_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixrma_idx1 ON affymetrixrma (element_id);
CREATE INDEX affymetrixrma_idx2 ON affymetrixrma (quantification_id);
CREATE INDEX affymetrixrma_idx3 ON affymetrixrma (signal);
CREATE INDEX affymetrixrma_idx6 ON affymetrixrma (z);

CREATE TABLE affymetrixgcrma (
  z float NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixgcrma ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixgcrma ADD CONSTRAINT affymetrixgcrma_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixgcrma ADD CONSTRAINT affymetrixgcrma_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixgcrma_idx1 ON affymetrixgcrma (element_id);
CREATE INDEX affymetrixgcrma_idx2 ON affymetrixgcrma (quantification_id);
CREATE INDEX affymetrixgcrma_idx3 ON affymetrixgcrma (signal);
CREATE INDEX affymetrixgcrma_idx6 ON affymetrixgcrma (z);

CREATE TABLE affymetrixprobesetstat (
  mean float NOT NULL,
  median float NOT NULL,
  quartile1 float NOT NULL,
  quartile3 float NOT NULL,
  sd float NOT NULL,
  n int NOT NULL
) INHERITS ( elementresult );
ALTER TABLE affymetrixprobesetstat ADD PRIMARY KEY (elementresult_id);
ALTER TABLE affymetrixprobesetstat ADD CONSTRAINT affymetrixprobesetstat_c1 FOREIGN KEY (element_id)        REFERENCES affymetrixprobeset (element_id);
ALTER TABLE affymetrixprobesetstat ADD CONSTRAINT affymetrixprobesetstat_c2 FOREIGN KEY (quantification_id) REFERENCES quantification  (quantification_id);
CREATE INDEX affymetrixprobesetstat_idx1 ON affymetrixprobesetstat (element_id);
CREATE INDEX affymetrixprobesetstat_idx2 ON affymetrixprobesetstat (quantification_id);
CREATE INDEX affymetrixprobesetstat_idx3 ON affymetrixprobesetstat (mean);
CREATE INDEX affymetrixprobesetstat_idx4 ON affymetrixprobesetstat (median);
CREATE INDEX affymetrixprobesetstat_idx5 ON affymetrixprobesetstat (quartile1);
CREATE INDEX affymetrixprobesetstat_idx6 ON affymetrixprobesetstat (quartile3);
CREATE INDEX affymetrixprobesetstat_idx7 ON affymetrixprobesetstat (sd);
CREATE INDEX affymetrixprobesetstat_idx8 ON affymetrixprobesetstat (n);