#
# mSQL calibdb creation script  (requires mSQL-1.0.6 or better)
#
# Database: calibDB
#--------------------------------------------------------


#
# Table structure for table 'system'
#
DROP TABLE system \g
CREATE TABLE system (
  key_name CHAR(32) NOT NULL,
  key_val CHAR(64) NOT NULL
) \g
INSERT INTO system VALUES ('CURRENT_MOMENT','NONE')\g

#
# Table structure for table 'real_component'
#
DROP TABLE real_component \g
CREATE TABLE real_component (
  comp_id CHAR(16) NOT NULL,
  ins_id CHAR(16) NOT NULL,
  name CHAR(128) NOT NULL,
  type CHAR(16) NOT NULL
) \g
CREATE UNIQUE  INDEX compid ON real_component (comp_id) \g


#
# Table structure for table 'component'
#
DROP TABLE component \g
CREATE TABLE component (
  comp_id CHAR(16) NOT NULL,
  orig_file_name CHAR(128),
  cdb_catg CHAR(128),
  name CHAR(128) NOT NULL,
  type CHAR(16),
  location CHAR(255),
  valid_from CHAR(32),
  valid_to CHAR(32),
  certif_lev CHAR(1),
  class CHAR(8)
) \g
CREATE UNIQUE  INDEX compid ON component (comp_id) \g
CREATE  INDEX validfrom ON component (valid_from)\g
CREATE  INDEX validto ON component (valid_to)\g



#
# Table structure for table 'data_product'
#
DROP TABLE data_product \g
CREATE TABLE data_product (
  dp_id CHAR(16) NOT NULL,
  orig_file_name CHAR(128),
  cdb_catg CHAR(128),
  type CHAR(8),
  class CHAR(8),
  comp_id CHAR(16),
  valid_from CHAR(32),
  valid_to CHAR(32),
  certif_lev CHAR(1)
) \g
CREATE UNIQUE  INDEX dpid ON data_product (dp_id) \g
CREATE  INDEX compid ON data_product (comp_id)\g
CREATE  INDEX validfrom ON data_product (valid_from)\g
CREATE  INDEX validto ON data_product (valid_to)\g


#
# Table structure for table 'has_comp'
#
DROP TABLE has_comp \g
CREATE TABLE has_comp (
  comp_id_1 CHAR(16) NOT NULL,
  comp_id_2 CHAR(16) NOT NULL
) \g
CREATE INDEX compid1 ON has_comp (comp_id_1)\g
CREATE INDEX compid2 ON has_comp (comp_id_2)\g


#
# Table structure for table 'has_dpr'
#
DROP TABLE has_dpr \g
CREATE TABLE has_dpr (
  comp_id CHAR(16) NOT NULL,
  dpr_id CHAR(16) NOT NULL
) \g
CREATE INDEX dprid ON has_dpr (dpr_id)\g
CREATE INDEX compid ON has_dpr (comp_id)\g


#
# Table structure for table 'instrument'
#
DROP TABLE instrument \g
CREATE TABLE instrument (
  ins_id CHAR(16) NOT NULL,
  telescope CHAR(8),
  mode CHAR(8),
  lightpath CHAR(256)
) \g
CREATE   INDEX insid ON instrument (ins_id) \g


#
# Table structure for table 'path_map'
#
DROP TABLE path_map \g
CREATE TABLE path_map (
  short CHAR(255),
  long CHAR(255)
) \g
INSERT INTO path_map VALUES ('/cal','ins')\g
