db.sql

/*----------------facility------------------------------*/
drop table if exists t_module_status;

drop table if exists t_observation;

drop table if exists t_detector;

drop table if exists t_detector_status;

drop table if exists t_facility_status;

drop table if exists t_guiding;

drop table if exists t_level0_data;

drop table if exists t_level0_header;

drop table if exists t_level0_prc;

drop table if exists t_cal2level0;

drop table if exists t_cal_header;

drop table if exists t_cal_merge;

/*----------------msc------------------------------*/


drop table if exists msc_level1_data;

drop table if exists msc_level1_header;

drop table if exists msc_level1_prc;

drop table if exists msc_level1_ref;

drop table if exists msc_level2_data;

drop table if exists msc_level2_header;

drop table if exists msc_level2_catalog;
/*----------------ifs------------------------------*/

drop table if exists ifs_level1_data;

drop table if exists ifs_level1_header;

drop table if exists ifs_level1_prc;

drop table if exists ifs_level1_ref;
/*----------------mci------------------------------*/

drop table if exists mci_level1_data;

drop table if exists mci_level1_header;

drop table if exists mci_level1_prc;

drop table if exists mci_level1_ref;
/*----------------cpic------------------------------*/

drop table if exists cpic_level1_data;

drop table if exists cpic_level1_header;

drop table if exists cpic_level1_prc;

drop table if exists cpic_level1_ref;
/*----------------sls------------------------------*/

drop table if exists sls_level1_data;

drop table if exists sls_level1_header;

drop table if exists sls_level1_prc;

drop table if exists sls_level1_ref;

drop table if exists sls_level2_spectra;

/*===========================facility===================================*/
create table t_detector
(
   no                   varchar(10) not null,
   detector_name        varchar(256) not null,
   module_id            varchar(20),
   filter_id            varchar(20),
   create_time          datetime,
   update_time          datetime,
   primary key (no)
);
/*==============================================================*/
/* Table: t_detector_status                                     */
/*==============================================================*/
create table t_detector_status
(
   id                   integer PRIMARY KEY autoincrement,
   detector_no          varchar(10) not null,
   status               varchar(256) not null,
   status_time          datetime,
   create_time          datetime
);

/*==============================================================*/
/* Table: t_facility_status                                     */
/*==============================================================*/
create table t_facility_status
(
   id                   integer PRIMARY KEY autoincrement,
   status               varchar(256) not null,
   status_time          datetime,
   create_time          datetime
);

/*==============================================================*/
/* Table: t_guiding                                             */
/*==============================================================*/
create table t_guiding
(
   id                   integer PRIMARY KEY autoincrement,
   filename             varbinary(128),
   guiding_file_path    varchar(256) not null,
   guiding_no           varchar(256),
   create_time          datetime
);
/*==============================================================*/
/* Table: t_module_status                                       */
/*==============================================================*/
create table t_module_status
(
   id                   integer PRIMARY KEY autoincrement,
   module_id            varbinary(20),
   status               varchar(256) not null,
   status_time          datetime,
   create_time          datetime
);

/*==============================================================*/
/* Table: t_observation                                         */
/*==============================================================*/
create table t_observation
(
   id                   integer PRIMARY KEY autoincrement,
   obs_id               varchar(10),
   obs_time             datetime,
   exp_time             float,
   module_id            varchar(20),
   obs_type             varchar(16),
   facility_status_id   int(20),
   module_status_id     int(20),
   qc0_status           tinyint(1),
   qc0_time             datetime,
   prc_status           tinyint(1),
   prc_time             datetime,
   create_time          datetime,
   import_status        tinyint(1)
);

create table t_level0_data
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   obs_id               varchar(10) not null,
   detector_no          varchar(10) not null,
   obs_type             varchar(16),
   obs_time             datetime,
   exp_time             float,
   detector_status_id   int(20),
   filename             varchar(128),
   file_path            varchar(256),
   qc0_status           tinyint(1),
   qc0_time             datetime,
   prc_status           tinyint(1),
   prc_time             datetime,
   create_time          datetime
);

create table t_level0_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj              float,
   crpix1               float,
   crpix2               float,
   crval1               float,
   crval2               float,
   ctype1               varchar(10),
   ctype2               varchar(10),
   cd1_1                float,
   cd1_2                float,
   cd2_1                float,
   cd2_2                float,
   object_name          varchar(64),
   version              varchar(64),   
   create_time          datetime,
   primary key (id)
);

create table t_level0_prc
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   pipeline_id          varchar(64) not null,
   prc_module           varchar(32) not null,
   params_file_path     varchar(256),
   prc_status           int(2),
   prc_time             datetime,
   result_file_path            varchar(256)
);

create table t_cal2level0
(
   merge_id             int(20) not null,
   level0_id            varchar(20) not null,
   primary key (merge_id, level0_id)
);

create table t_cal_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);

create table t_cal_merge
(
   id                   integer PRIMARY KEY autoincrement,
   cal_id               varchar(20) not null,
   detector_no          varchar(10) not null,
   ref_type             varchar(16),
   obs_time             datetime,
   exp_time             float,
   filename             varchar(128),
   file_path            varchar(256),
   qc1_status           tinyint(1),
   qc1_time             datetime,
   prc_status           tinyint(1),
   prc_time             datetime,
   create_time          datetime
);
/*========================msc======================================*/
create table msc_level1_data
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   data_type            varchar(64) not null,
   cor_sci_id           int(20),
   prc_params           varchar(1024),
   filename             varchar(128),
   file_path            varchar(256),
   prc_status           tinyint(1),
   prc_time             datetime,
   qc1_status           tinyint(1),
   qc1_time             datetime,
   create_time          datetime,
   pipeline_id          varchar(60)
);

create table msc_level1_ref (
  level1_id int(20) not null,
  ref_type varchar(64) not null,
  cal_id int(20) not null,
  primary key (level1_id, ref_type)
);

create table msc_level1_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);

create table msc_level1_prc
(
   id                   integer PRIMARY KEY autoincrement,
   level1_id            int(20) not null,
   pipeline_id          varchar(64) not null,
   prc_module           varchar(32) not null,
   params_file_path     varchar(256),
   prc_status           int(2),
   prc_time             datetime,
   result_file_path            varchar(256)
);

create table msc_level2_data (
   id integer PRIMARY KEY autoincrement,
   level0_id            VARCHAR(20)          null,
   level1_id            int(20)          not null,
   data_type            VARCHAR(64)          not null,
   filename             VARCHAR(128)         null,
   file_path            VARCHAR(256)         null,
   obs_time             datetime                 null,
   prc_status           tinyint(1),
   prc_time             datetime                 null,
   qc2_status           tinyint(1),
   qc2_time             datetime                 null,
   create_time          datetime                 null,
   import_status        tinyint(1)  default 0,
   pipeline_id            VARCHAR(64)         null
);

create table msc_level2_header (
   id                   BIGINT                 not null,
   ra_obj               float8               null,
   dec_obj               float8               null,
   constraint PK_MSC_LEVEL2_HEADER primary key (id)
);

create table msc_level2co_data (
   id integer PRIMARY KEY autoincrement,
   data_type            VARCHAR(64)          not null,
   filename             VARCHAR(128)         null,
   file_path            VARCHAR(256)         null,
   prc_status           tinyint(1),
   prc_time             datetime             null,
   qc2_status           tinyint(1),
   qc2_time             datetime             null,
   create_time          datetime             null,
   import_status        tinyint(1)           default 0,
   pipeline_id            VARCHAR(64)         null
);

create table msc_level2co_header (
   id                   BIGINT                 not null,
   ra_obj               float8               null,
   dec_obj               float8               null,
   constraint PK_MSC_LEVEL2CO_HEADER primary key (id)
);
/*===========================ifs===================================*/
create table ifs_level1_data
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   data_type            varchar(64) not null,
   cor_sci_id           int(20),
   prc_params           varchar(1024),
   filename             varchar(128),
   file_path            varchar(256),
   prc_status           tinyint(1),
   prc_time             datetime,
   qc1_status           tinyint(1),
   qc1_time             datetime,
   create_time          datetime,
   pipeline_id          varchar(60)
);

create table ifs_level1_ref (
  level1_id int(20) not null,
  ref_type varchar(64) not null,
  cal_id int(20) not null,
  primary key (level1_id, ref_type)
);

create table ifs_level1_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);
create table ifs_level1_prc
(
   id                   integer PRIMARY KEY autoincrement,
   level1_id            int(20) not null,
   pipeline_id          varchar(64) not null,
   prc_module           varchar(32) not null,
   params_file_path     varchar(256),
   prc_status           int(2),
   prc_time             datetime,
   result_file_path            varchar(256)
);
/*===========================mci===================================*/

create table mci_level1_data
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   data_type            varchar(64) not null,
   cor_sci_id           int(20),
   prc_params           varchar(1024),
   filename             varchar(128),
   file_path            varchar(256),
   prc_status           tinyint(1),
   prc_time             datetime,
   qc1_status           tinyint(1),
   qc1_time             datetime,
   create_time          datetime,
   pipeline_id          varchar(60)
);

create table mci_level1_ref (
  level1_id int(20) not null,
  ref_type varchar(64) not null,
  cal_id int(20) not null,
  primary key (level1_id, ref_type)
);

create table mci_level1_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);
create table mci_level1_prc
(
   id                   integer PRIMARY KEY autoincrement,
   level1_id            int(20) not null,
   pipeline_id          varchar(64) not null,
   prc_module           varchar(32) not null,
   params_file_path     varchar(256),
   prc_status           int(2),
   prc_time             datetime,
   result_file_path            varchar(256)
);
/*===========================sls===================================*/
create table sls_level1_data
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   data_type            varchar(64) not null,
   prc_params           varchar(1024),
   filename             varchar(128),
   file_path            varchar(256),
   prc_status           tinyint(1),
   prc_time             datetime,
   qc1_status           tinyint(1),
   qc1_time             datetime,
   create_time          datetime,
   pipeline_id          varchar(60)
);

create table sls_level1_ref (
  level1_id int(20) not null,
  ref_type varchar(64) not null,
  cal_id int(20) not null,
  primary key (level1_id, ref_type)
);

create table sls_level1_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);

create table sls_level1_prc
(
   id                   integer PRIMARY KEY autoincrement,
   level1_id            int(20) not null,
   pipeline_id          varchar(64) not null,
   prc_module           varchar(32) not null,
   params_file_path     varchar(256),
   prc_status           int(2),
   prc_time             datetime,
   result_file_path     varchar(256)
);

create table sls_level2_spectra
(
   id                   integer PRIMARY KEY autoincrement,
   spectra_id           varchar(40),
   level0_id            VARCHAR(20)          null,
   level1_id            int(20) not null,
   region               varchar(128),
   filename             varchar(128),
   file_path            varchar(256),
   prc_status           tinyint(1),
   prc_time             datetime,
   qc1_status           tinyint(1),
   qc1_time             datetime,
   create_time          datetime,
   pipeline_id          varchar(60)
);
create table sls_level2_spectra_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);
/*===========================cpic===================================*/

create table cpic_level1_data
(
   id                   integer PRIMARY KEY autoincrement,
   level0_id            varchar(20) not null,
   data_type            varchar(64) not null,
   prc_params           varchar(1024),
   filename             varchar(128),
   file_path            varchar(256),
   prc_status           tinyint(1),
   prc_time             datetime,
   qc1_status           tinyint(1),
   qc1_time             datetime,
   create_time          datetime,
   pipeline_id          varchar(60)
);

create table cpic_level1_ref (
  level1_id int(20) not null,
  ref_type varchar(64) not null,
  cal_id int(20) not null,
  primary key (level1_id, ref_type)
);

create table cpic_level1_header
(
   id                   int(20) not null,
   ra_obj               float,
   dec_obj               float,
   create_time          datetime,
   primary key (id)
);

create table cpic_level1_prc
(
   id                   integer PRIMARY KEY autoincrement,
   level1_id            int(20) not null,
   pipeline_id          varchar(64) not null,
   prc_module           varchar(32) not null,
   params_file_path     varchar(256),
   prc_status           int(2),
   prc_time             datetime,
   result_file_path     varchar(256)
);