BbkTools/bbk_table_schema.oracle - view

File: [BaBar] / BbkTools / bbk_table_schema.oracle
Revision 1.42: download - view: text, annotated - select for diffs
Tue Dec 2 00:45:03 2008 UTC (11 months, 3 weeks ago) by adye
Branches: MAIN
CVS tags: tja090929a, tja090928b, tja090928a, tja090924a, tja090923a, tja090918b, tja090918a, tja090917c, tja090917b, tja090917a, tja090916a, tja090914a, tja090911a, tja090910a, tja090909a, tja090819a, tja090817a, tja090731a, tja090724a, tja090710a, tja090610a, tja090305c, tja090305b, tja090305a, tja081223a, tja081201a, V00-01-54, V00-01-53, V00-01-52, V00-01-51, V00-01-50, V00-01-49, V00-01-48, V00-01-47, HEAD
Diff to previous 1.41: preferred, colored
Changes since revision 1.41: +3 -1 lines

Tim Adye  1-Dec-2008 tja081201a
1. Add unique key constraint on bbk_dataset.name in MySQL schema.
   The Oracle schema had this change with das20070321a, so that should
   have enforced the constraint on mirrored databases. It's probably not
   necessary to apply this change to existing databases.
2. Add commented foreign key definitions for dse_skim_cycle joins.

-- Author: D. Smith -- Created: 22/07/2003 -- Maintained by D. Smith -- drop all tables: drop table bbk_aliases; drop table bbk_dataset; drop table bbk_dstodse; drop table bbk_dsentities; drop table bbk_dsetodse; drop table bbk_dsetorun; drop table bbk_runs; drop table bbk_releases; drop table bbk_components; drop table bbk_sitecontent; drop table bbk_sites; drop table bbk_streams; drop table bbk_dsereject; drop table bbk_dseskim; drop table bbk_files; drop table bbk_conditions; -- drop sequences drop sequence bbk_aliases_seq; drop sequence bbk_dataset_seq; drop sequence bbk_dstodse_seq; drop sequence bbk_dsentities_seq; drop sequence bbk_dsetodse_seq; drop sequence bbk_dsetorun_seq; drop sequence bbk_runs_seq; drop sequence bbk_releases_seq; drop sequence bbk_components_seq; drop sequence bbk_sitecontent_seq; drop sequence bbk_sites_seq; drop sequence bbk_streams_seq; drop sequence bbk_dsereject_seq; drop sequence bbk_dseskim_seq; drop sequence bbk_files_seq; drop sequence bbk_conditions_seq; -- create tables and sequences: create table bbk_aliases ( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, name varchar2(255) NOT NULL, ds_id number(10) NOT NULL, tagdate number(14) default '0' NOT NULL, tag varchar2(80) ); alter table bbk_aliases add constraint bbk_aliases_pk primary key (id); alter table bbk_aliases add constraint bbk_aliases_uk unique (name); create sequence bbk_aliases_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_aliases ADD CONSTRAINT bbk_aliases_fk FOREIGN KEY (ds_id) REFERENCES bbk_dataset(id); create table bbk_dataset( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, maintainer varchar2(8) default '' NOT NULL, name varchar2(255) NOT NULL, input_ds varchar2(2000), description varchar2(2000) NOT NULL, tot_lumi number(12,4), tot_nev number(10) default '0' NOT NULL, dse_type varchar2(10), stream_id number(4), on_peak number(1), run_cycle varchar2(5), skim_cycle varchar2(10), skim_state number(1), modenum number(8), ignore_ds number(1) default '0' NOT NULL, autoupdate number(1) default '1' NOT NULL, awg varchar2(80) ); alter table bbk_dataset add constraint bbk_dataset_pk primary key (id); alter table bbk_dataset add constraint bbk_dataset_uk unique (name); create sequence bbk_dataset_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_dataset ADD CONSTRAINT bbk_dataset_fk_optional FOREIGN KEY (stream_id) REFERENCES bbk_streams(id); create table bbk_dstodse( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, ds_id number(10) NOT NULL, link_status number(1) default '0' NOT NULL, dse_id number(10) NOT NULL ); alter table bbk_dstodse add constraint bbk_dstodse_pk primary key (id); create sequence bbk_dstodse_seq increment by 1 start with 1 nocache; create index bbk_dstodse_dse_idx on bbk_dstodse (dse_id); create index bbk_dstodse_ds_idx on bbk_dstodse (ds_id); -- ALTER TABLE bbk_dstodse ADD CONSTRAINT bbk_dstodse_fk1 FOREIGN KEY (ds_id) REFERENCES bbk_dataset(id); -- ALTER TABLE bbk_dstodse ADD CONSTRAINT bbk_dstodse_fk2 FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); create table bbk_dsentities( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, dse_type varchar2(10) default '' NOT NULL, name varchar2(255) NOT NULL, input_nev number(10) default '0' NOT NULL, output_nev number(10) default '0' NOT NULL, dse_status number(1) default '0' NOT NULL, is_local number(1) default '0' NOT NULL, rel_id number(5) NOT NULL, stream_id number(4) default '0' NOT NULL, run_sel varchar2(255), on_peak number(1) default '1' NOT NULL, run_cycle varchar2(5), modenum number(8), lumi_sum number(12,4), num_runs number(8) ); alter table bbk_dsentities add constraint bbk_dsentities_pk primary key (id); alter table bbk_dsentities add constraint bbk_dsentities_uk unique (name); create sequence bbk_dsentities_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_dsentities ADD CONSTRAINT bbk_dsentities_fk1 FOREIGN KEY (rel_id) REFERENCES bbk_releases(id); -- ALTER TABLE bbk_dsentities ADD CONSTRAINT bbk_dsentities_fk2 FOREIGN KEY (stream_id) REFERENCES bbk_streams(id); -- ALTER TABLE bbk_dsentities ADD CONSTRAINT bbk_dsentities_fk3_optional FOREIGN KEY (id) REFERENCES bbk_dsentities(id); create table bbk_dsetodse( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, dse_id number(10) NOT NULL, input_id number(10) NOT NULL, dse_nev number(10), dse_lumi number(12,4), rel_id number(5) ); alter table bbk_dsetodse add constraint bbk_dsetodse_pk primary key (id); alter table bbk_dsetodse add constraint bbk_dsetodse_uk unique (dse_id,input_id); create index bbk_dtd_dse_idx on bbk_dsetodse (dse_id); create index bbk_dtd_input_idx on bbk_dsetodse (input_id); create sequence bbk_dsetodse_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_dsetodse ADD CONSTRAINT bbk_dsetodse_fk1 FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); -- ALTER TABLE bbk_dsetodse ADD CONSTRAINT bbk_dsetodse_fk2_optional FOREIGN KEY (input_id) REFERENCES bbk_dsentities(id); -- ALTER TABLE bbk_dsetodse ADD CONSTRAINT bbk_dsetodse_fk3_optional FOREIGN KEY (rel_id) REFERENCES bbk_releases(id); create table bbk_dsetorun( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, dse_id number(10) NOT NULL, run_status number(1) default '0' NOT NULL, pr_lumi number(12,4), run_id number(9) NOT NULL, run_nev number(10) ); alter table bbk_dsetorun add constraint bbk_dsetorun_pk primary key (id); alter table bbk_dsetorun add constraint bbk_dsetorun_uk unique (dse_id,run_id); create sequence bbk_dsetorun_seq increment by 1 start with 1 nocache; create index bbk_dtr_dse_idx on bbk_dsetorun (dse_id); create index bbk_dtr_run_idx on bbk_dsetorun (run_id); create index bbk_dsetorun_modified_idx on bbk_dsetorun(modified); -- ALTER TABLE bbk_dsetorun ADD CONSTRAINT bbk_dsetorun_fk1 FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); -- ALTER TABLE bbk_dsetorun ADD CONSTRAINT bbk_dsetorun_fk2 FOREIGN KEY (run_id) REFERENCES bbk_runs(id); create table bbk_runs( id number(9) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, run_cycle varchar2(5) default '' NOT NULL, run number(10) default '0' NOT NULL, on_peak number(1) default '1' NOT NULL, run_nev number(10) default '0' NOT NULL, l3_lumi number(12,4), condalias number(8) default '0' NOT NULL, bkg number(1) default '0' NOT NULL, modenum number(8) default '0' NOT NULL ); alter table bbk_runs add constraint bbk_runs_pk primary key (id); alter table bbk_runs add constraint bbk_runs_uk unique (run); create sequence bbk_runs_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_runs ADD CONSTRAINT bbk_runs_fk FOREIGN KEY (condalias) REFERENCES bbk_conditions(condalias); create table bbk_releases( id number(5) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, name varchar2(15) NOT NULL, precedence number(12) default '0' NOT NULL ); alter table bbk_releases add constraint bbk_releases_pk primary key (id); alter table bbk_releases add constraint bbk_releases_uk unique (name); create sequence bbk_releases_seq increment by 1 start with 1 nocache; create table bbk_components( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, name varchar2(40) NOT NULL, description long NOT NULL ); alter table bbk_components add constraint bbk_components_pk primary key (id); create sequence bbk_components_seq increment by 1 start with 1 nocache; create table bbk_sitecontent( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, ds_id number(10) NOT NULL, comp_id number(10) NOT NULL, is_local number(1) default '0' NOT NULL, site_id number(4) default '0' NOT NULL ); alter table bbk_sitecontent add constraint bbk_sitecontent_pk primary key (id); create sequence bbk_sitecontent_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_sitecontent ADD CONSTRAINT bbk_sitecontent_fk1 FOREIGN KEY (ds_id) REFERENCES bbk_dataset(id); -- ALTER TABLE bbk_sitecontent ADD CONSTRAINT bbk_sitecontent_fk2 FOREIGN KEY (comp_id) REFERENCES bbk_components(id); -- ALTER TABLE bbk_sitecontent ADD CONSTRAINT bbk_sitecontent_fk3 FOREIGN KEY (site_id) REFERENCES bbk_sites(id); create table bbk_sites( id number(4) NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, name varchar2(15) NOT NULL ); alter table bbk_sites add constraint bbk_sites_pk primary key (id); alter table bbk_sites add constraint bbk_sites_uk unique (name); create sequence bbk_sites_seq increment by 1 start with 1 nocache; create table bbk_streams( id number(4) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, name varchar2(80) NOT NULL ); alter table bbk_streams add constraint bbk_streams_pk primary key (id); alter table bbk_streams add constraint bbk_streams_uk unique (name); create sequence bbk_streams_seq increment by 1 start with 1 nocache; create table bbk_dsereject( id number(10) NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, dse_id number(10) NOT NULL, reject_status number(1) default '1' NOT NULL, reject_type varchar2(10) default 'Run' NOT NULL, reject_value number(9) NOT NULL, reject_nev number(10) default '0' NOT NULL, reject_lumi number(12,4) ); alter table bbk_dsereject add constraint bbk_dsereject_pk primary key (id); create sequence bbk_dsereject_seq increment by 1 start with 1 nocache; create index bbk_dsr_dse_idx on bbk_dsereject (dse_id); -- ALTER TABLE bbk_dsereject ADD CONSTRAINT bbk_dsereject_fk1 FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); -- ALTER TABLE bbk_dsereject ADD CONSTRAINT bbk_dsereject_fk2_optional FOREIGN KEY (reject_value) REFERENCES bbk_runs(run); create table bbk_dseskim( id number(10) NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, dse_id number(10) NOT NULL, skim_state number(1) default '0' NOT NULL, skim_cycle varchar2(10) ); alter table bbk_dseskim add constraint bbk_dseskim_pk primary key (id); alter table bbk_dseskim add constraint bbk_dseskim_uk unique (dse_id, skim_cycle); create sequence bbk_dseskim_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_dseskim ADD CONSTRAINT bbk_dseskim_fk1 FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); -- ALTER TABLE bbk_dseskim ADD CONSTRAINT bbk_dseskim_fk2_optional FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); create table bbk_files( id number(10) NOT NULL, pub_id number(1) default '0' NOT NULL, created number(14) DEFAULT '0' NOT NULL, modified number(14) DEFAULT '0' NOT NULL, dse_id number(10) DEFAULT '0' NOT NULL, filenum number(5) DEFAULT '1' NOT NULL, components varchar2(10) DEFAULT '', suffix varchar2(13) DEFAULT '.01', bytes number(10) DEFAULT '0' NOT NULL, uuid varchar2(255), checksum number(10) DEFAULT '0' NOT NULL ); alter table bbk_files add constraint bbk_files_pk primary key (id); alter table bbk_files add constraint bbk_files1_uk unique (dse_id,filenum); alter table bbk_files add constraint bbk_files2_uk unique (dse_id,suffix); create sequence bbk_files_seq increment by 1 start with 1 nocache; -- ALTER TABLE bbk_files ADD CONSTRAINT bbk_files_fk FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); create table bbk_conditions ( id number(10) NOT NULL, created number(14) default '0' NOT NULL, modified number(14) default '0' NOT NULL, condalias number(8) default '0' NOT NULL, on_peak number(1) default '1' NOT NULL, condstring varchar2(20), run_cycle varchar2(20), sub_cycle varchar2(20), cond_lumi number(12,4) ); alter table bbk_conditions add constraint bbk_conditions_pk primary key ( id ); alter table bbk_conditions add constraint bbk_conditions_uk unique ( condalias, on_peak ); create sequence bbk_conditions_seq increment by 1 start with 1 nocache; -- grant select priviledge on table to bbkanyuser grant select on bbk_aliases to bbkanyuser; grant select on bbk_dataset to bbkanyuser; grant select on bbk_dstodse to bbkanyuser; grant select on bbk_dsentities to bbkanyuser; grant select on bbk_dsetodse to bbkanyuser; grant select on bbk_dsetorun to bbkanyuser; grant select on bbk_runs to bbkanyuser; grant select on bbk_releases to bbkanyuser; grant select on bbk_components to bbkanyuser; grant select on bbk_sitecontent to bbkanyuser; grant select on bbk_sites to bbkanyuser; grant select on bbk_streams to bbkanyuser; grant select on bbk_dsereject to bbkanyuser; grant select on bbk_dseskim to bbkanyuser; grant select on bbk_files to bbkanyuser; grant select on bbk_conditions to bbkanyuser;