-- createdb cachegraph_name_species_revision

BEGIN;

CREATE TABLE Track (
  track_name varchar NOT NULL, -- ex. CpG_Islands
  comment varchar, -- ex. "CpG Islands"
  description_url varchar, -- ex. http://gene.org/
  color_red integer, -- ex. 0
  color_green integer, -- ex. 128
  color_blue integer, -- ex. 255
  species varchar NOT NULL, -- ex. medaka
  revision varchar NOT NULL, -- ex. 200406
  species_url varchar, -- ex. http://gene.org/species.txt
  use_minmax integer NOT NULL,
  max double precision,
  min double precision,
  blend char(8), -- ex. average
  date char(8), -- ex. 20040707
  optattr_str varchar, -- ex. "fromSpecies/select[medaka,fugu,zebrafish](disp,color)"
  memo varchar -- ex. "# comment"
);

CREATE TABLE OptattrSelectName (
  select_name_id serial PRIMARY KEY,
  select_name_str varchar NOT NULL, -- ex. fromspecies
  UNIQUE(select_name_str)
);

CREATE TABLE OptattrRealName (
  real_name_id serial PRIMARY KEY,
  real_name_str varchar NOT NULL, -- ex. matchRatio
  UNIQUE(real_name_str)
);

CREATE TABLE Data (
  data_id serial PRIMARY KEY,
  target varchar NOT NULL, -- ex. scaffold1
  range_start bigint NOT NULL, -- ex. 100
  range_end bigint NOT NULL, -- ex. 300
  url varchar, -- ex. http://www.ncbi.nlm.nih.gov/entrez/viewer.fcgi?val=BJ014617
  color_red integer, -- ex. 0
  color_green integer, -- ex. 128
  color_blue integer, -- ex. 255
  memo varchar, -- ex. "# comment"
  str varchar
  -- UNIQUE(target, range_start, range_end)
);
CREATE INDEX Data_target_index ON Data(target);
CREATE INDEX Data_range_start_index ON Data(range_start);
CREATE INDEX Data_range_end_index ON Data(range_end);

CREATE TABLE OptattrDataSelect (
  data_id integer NOT NULL REFERENCES Data(data_id),
  select_name_id integer NOT NULL REFERENCES OptattrSelectName(select_name_id),
  select_name varchar,
  PRIMARY KEY(data_id, select_name_id)
);
CREATE INDEX OptattrDataSelect_select_name_index ON OptattrDataSelect(select_name);

CREATE TABLE OptattrDataReal (
  data_id integer NOT NULL REFERENCES Data(data_id),
  real_name_id integer NOT NULL REFERENCES OptattrRealName(real_name_id),
  real_value double precision,
  PRIMARY KEY(data_id, real_name_id)
);
CREATE INDEX OptattrDataReal_real_value_index ON OptattrDataReal(real_value);

-- CREATE USER browser;
-- REVOKE ALL ON DATABASE cachegraph FROM browser;
-- GRANT ALL ON DATABASE cachegraph TO browser;
GRANT ALL ON Track,OptattrSelectName,OptattrRealName,Data,OptattrDataSelect,OptattrDataReal TO browser;

COMMIT;

---

