/*******************************************************************/
/* 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 must already exist on your server         */
/*******************************************************************/

use bibcodes
go

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

sp_unbindrule "bibcodes_main.bib_status"
go
drop rule bib_status_rule
go
create rule bib_status_rule as @status in 
("early release", "bibcode changed")
go

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

/**************************************************************/
/* bibcodes:  This table contains the links between a bicodes */
/*            and datasets referenced in papers.              */
/**************************************************************/

drop table bibcodes
go

create table bibcodes (
center varchar(10) default 'CXC' NOT NULL, 
obsid int NOT NULL,
bibcode varchar(32) NOT NULL,
pub_date datetime,
prop_paper varchar(1),
bib_id int,
comment varchar(100))
go
sp_primarykey bibcodes, obsid,bibcode
go

/**************************************************************/
/* bib_def:  This table gives the definitions of the          */
/*           categories assigned to the papers.  It is used   */
/*           by the search form                               */
/**************************************************************/

drop table bib_def
go
create table bib_def (
ref_id int NOT NULL,
description varchar(255) NOT NULL)
go

/**************************************************************/
/* bibcodes_main: This table stores most of the information   */
/*                related to the observatory related paper    */
/**************************************************************/

drop table bibcodes_main
go
create table bibcodes_main (
center varchar(10) default 'CXC' NOT NULL,
bibcode varchar(32) NOT NULL,
ref_id int NOT NULL,
pub_date datetime,
cite_cnt int,
comment varchar(100),
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,
prop_paper varchar(1),
pub_type varchar(1),
refereed varchar(1),
bib_id int,
pub_form varchar(10),
related_bib int,
cxc_set varchar(1) default "N",
ads_db varchar(4),
pub_url varchar(1) default "N",
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,
bib_status varchar(20))
go
drop index bibcodes_main.ind_bibcodes_main
go
create index ind_bibcodes_main on bibcodes_main (bibcode)
go
sp_bindrule flag_rule, "bibcodes_main.acis"
go
sp_bindrule flag_rule, "bibcodes_main.hrc"
go
sp_bindrule flag_rule, "bibcodes_main.hetg"
go
sp_bindrule flag_rule, "bibcodes_main.letg"
go
sp_bindrule flag_rule, "bibcodes_main.hrma"
go
sp_bindrule flag_rule, "bibcodes_main.pcad"
go
sp_bindrule flag_rule, "bibcodes_main.ephin"
go
sp_bindrule flag_rule, "bibcodes_main.operations"
go
sp_bindrule flag_rule, "bibcodes_main.software"
go
sp_bindrule flag_rule, "bibcodes_main.multiwave"
go
sp_bindrule flag_rule, "bibcodes_main.followup"
go
sp_bindrule flag_rule, "bibcodes_main.theory"
go
sp_bindrule bib_status_rule, "bibcodes_main.bib_status"
go

/**************************************************************/
/* pub_url: The table stores the url for papers that we can   */
/*          link directly to.                                 */
/**************************************************************/

drop table pub_url
go
create table pub_url (
bib_id int,
pub_url text,
pub_id int)
go
sp_primarykey pub_url, pub_id
go
create index ind_pub_url on pub_url (bib_id)
go

/**************************************************************/
/* bib2set: This links a bibcode to a defined dataset         */
/**************************************************************/

drop table bib2set
go
create table bib2set (
bib_id int,
set_id int)
go
sp_primarykey bib2set, bib_id,set_id
go

/**************************************************************/
/* datasets: links the dataset id with the dataset name       */
/**************************************************************/

drop table datasets
go
create table datasets (
set_id int,
dataset varchar(25),
data_url text)
go
sp_primarykey datasets, dataset
go

/**************************************************************/
/* set2obsid: links the observations in a dataset with the    */
/*            dataset id                                      */
/**************************************************************/
drop table set2obsid
go
create table set2obsid (
set_id int,
obsid int)
go

/**************************************************************/
/* bib2keyword: links a bibcode with the keywords associated  */
/*              by ADS with that bibcode                      */
/**************************************************************/

drop table bib2keyword
go
create table bib2keyword (
bib_id int,
keyword varchar(255),
kwd_str varchar(255),
std_id int)
go
sp_primarykey bib2keyword, bib_id,keyword
go
create index keyword_index on bib2keyword (std_id)
go

/**************************************************************/
/* std_keywords: these are the keywords that can be selected  */
/*               for keyword searches in the search pages     */
/**************************************************************/

drop table std_keywords
go
create table std_keywords (
std_id int,
cat_id int,
keyword varchar(255))
go
sp_primarykey std_keywords, std_id
go

/**************************************************************/
/* std_categories: these are the keyword categories that are  */
/*                 presented for keyword searches of the      */
/*                 database                                   */
/**************************************************************/
drop table std_categories
go
create table std_categories (
cat_id int,
category varchar(255))
go

/**************************************************************/
/* stdkwd_links: this links the standard keywords to standard */
/*               categories                                   */
/**************************************************************/
drop table stdkwd_links
go
create table stdkwd_links (
std_id int,
cat_id int,
keyword varchar(255))
go
sp_primarykey stdkwd_links, keyword
go

