/*******************************************************************/
/* The tables created in ddl_working_tables are the tables used by */
/* the bicat gui for categorizing all possible observatory related */
/* papers. The tables created in ddl_final_tables are the tables   */
/* for the papers that have been categorized as being observatory  */
/* related.                                                        */
/*                                                                 */
/* The two sets of tables can exist in the same database.  We had  */
/* split ours across two servers, and hence two databases, for     */
/* for policy reasons.  If you create all tables in one database,  */
/* be sure to correct the "use" statement in the two script to     */
/* point to the same database and to create duplicated rules only  */
/* once and to link them with all related tables.                  */
/*******************************************************************/

/*******************************************************************/
/* The database bibcodes_working must already exist on your server */
/*******************************************************************/

use bibcodes_working
go

/*********/
/* Rules */
/*********/
sp_unbindrule "bibcodes_work.acis"
go
sp_unbindrule "bibcodes_work.hrc"
go
sp_unbindrule "bibcodes_work.hetg"
go
sp_unbindrule "bibcodes_work.letg"
go
sp_unbindrule "bibcodes_work.hrma"
go
sp_unbindrule "bibcodes_work.pcad"
go
sp_unbindrule "bibcodes_work.ephin"
go
sp_unbindrule "bibcodes_work.operations"
go
sp_unbindrule "bibcodes_work.software"
go
sp_unbindrule "bibcodes_work.multiwave"
go
sp_unbindrule "bibcodes_work.followup"
go
sp_unbindrule "bibcodes_work.theory"
go
drop rule flag_rule
go
create rule flag_rule as @flag in ("Y", "N")
go

sp_unbindrule 'bibcodes_work.source'
go
drop rule bib_source_rule
go
create rule bib_source_rule as @source in ('AST', 'INST', 'PHY', 'OTHER')
go

sp_unbindrule 'bibcodes_work.status'
go
drop rule bib_status_rule 
go
create rule bib_status_rule as @status in (
'U', 'OK', 'R', 'P', 'J', 'NA', 'Q', 'H', 'W')
go

/**********/
/* tables */
/**********/

/**************************************************************/
/* bibcodes_work:  This table is used to keep track of the    */
/*                 classifying of papers                      */
/**************************************************************/

drop table bibcodes_work
go
create table bibcodes_work (
bibcode   varchar(32)             NOT NULL,
source    varchar(5)              NOT NULL,
loc_date  datetime                NOT NULL,
read_flag varchar(1)                      ,
status    varchar(2)  default "U" NOT NULL,
comment   varchar(100),
ref_id    int,
obs_list  bit default 0 not null,
acis bit default 0 not null,
hrc bit default 0 not null,
hetg bit default 0 not null,
letg bit default 0 not null,
hrma bit default 0 not null,
pcad bit default 0 not null,
ephin bit default 0 not null,
year int,
month int,
pub_type varchar(1),
pub_form varchar(10),
refereed varchar(1),
bib_id int,
related_bib int,
cxc_set varchar(15),
operations bit default 0 not null,
software bit default 0 not null,
multiwave char(1) default "N" not null,
followup char(1) default "N" not null,
theory char(1) default "N" not null)
go
create unique index bibcodes_index on bibcodes_work (bibcode)
go
sp_bindrule flag_rule, "bibcodes_work.acis"
go
sp_bindrule flag_rule, "bibcodes_work.hrc"
go
sp_bindrule flag_rule, "bibcodes_work.hetg"
go
sp_bindrule flag_rule, "bibcodes_work.letg"
go
sp_bindrule flag_rule, "bibcodes_work.hrma"
go
sp_bindrule flag_rule, "bibcodes_work.pcad"
go
sp_bindrule flag_rule, "bibcodes_work.ephin"
go
sp_bindrule flag_rule, "bibcodes_work.operations"
go
sp_bindrule flag_rule, "bibcodes_work.software"
go
sp_bindrule flag_rule, "bibcodes_work.multiwave"
go
sp_bindrule flag_rule, "bibcodes_work.followup"
go
sp_bindrule flag_rule, "bibcodes_work.theory"
go
sp_bindrule bib_source_rule, "bibcodes_work.source"
go
sp_bindrule bib_status_rule, "bibcodes_work.status"
go 

/**************************************************************/
/* bib2pub: links a bib_string to the characteristics of the  */
/*          journal                                           */
/**************************************************************/
drop table bib2pub
go
create table bib2pub (
bib_string varchar(10),
pub_type varchar(1),
pub_form varchar(5),
refereed varchar(1),
publication varchar(100))
go
drop index bib2pub.ind_bib2pub
go
create index ind_bib2pub on bib2pub (bib_string)
go

/**************************************************************/
/* obs_list: links observations to papers                     */
/**************************************************************/
drop table obs_list
go
create table obs_list (
bibcode varchar(32),
obsid int)
go
create unique index obs_list_index on  obs_list (bibcode, obsid)
go

/**************************************************************/
/* bib2set_work: links bibcodes with datasets                 */
/**************************************************************/
drop table bib2set_work
go
create table bib2set_work (
bib_id int,
bibcode varchar(32))
go

/**************************************************************/
/* bib_author_title: stores the author list and title link to */
/*                   a bibcode                                */
/**************************************************************/
drop table bib_author_title
go
create table bib_author_title (
bibcode varchar(32),
first_author varchar(255),
title text)
go
drop index bib_author_title.ind_bib_author_title
go
create unique index ind_bib_author_title on bib_author_title (bibcode)
go
