BbkTools/bbk_table_schema.mysql - view

File: [BaBar] / BbkTools / bbk_table_schema.mysql
Revision 1.37: download - view: text, annotated - select for diffs
Tue Dec 2 00:45:01 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.36: preferred, colored
Changes since revision 1.36: +5 -2 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: A.Forti -- Created: 11/06/03 -- Maintained by D. Smith and T. Adye -- drop all tables drop table if exists bbk_aliases; drop table if exists bbk_dataset; drop table if exists bbk_dstodse; drop table if exists bbk_dsentities; drop table if exists bbk_dsetodse; drop table if exists bbk_dsetorun; drop table if exists bbk_runs; drop table if exists bbk_releases; drop table if exists bbk_components; drop table if exists bbk_sitecontent; drop table if exists bbk_sites; drop table if exists bbk_streams; drop table if exists bbk_dsereject; drop table if exists bbk_dseskim; drop table if exists bbk_files; drop table if exists bbk_conditions; create table bbk_aliases( id int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, name varchar(255) binary NOT NULL, ds_id int(10) unsigned NOT NULL, tagdate bigint(14) default '0' NOT NULL, tag varchar(80), PRIMARY KEY (id), UNIQUE KEY (name) ); -- ALTER TABLE bbk_aliases ADD CONSTRAINT bbk_aliases_fk FOREIGN KEY (ds_id) REFERENCES bbk_dataset(id); create table bbk_dataset( id int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, maintainer varchar(8) binary default '' NOT NULL, name varchar(255) binary NOT NULL, input_ds blob, description blob NOT NULL, tot_lumi float(12,4), tot_nev int(10) default '0' NOT NULL, dse_type varchar(10), stream_id smallint(5) unsigned, on_peak tinyint(1), run_cycle varchar(5), skim_cycle varchar(10), skim_state tinyint(1) unsigned, modenum int(8), ignore_ds tinyint(1) default '0' NOT NULL, autoupdate tinyint(1) default '1' NOT NULL, awg varchar(80), PRIMARY KEY (id), UNIQUE KEY (name) ); -- ALTER TABLE bbk_dataset ADD CONSTRAINT bbk_dataset_fk_optional FOREIGN KEY (stream_id) REFERENCES bbk_streams(id); create table bbk_dstodse( id int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, ds_id int(10) unsigned NOT NULL, link_status tinyint(1) unsigned default '0' NOT NULL, dse_id int(10) unsigned default '0' NOT NULL, PRIMARY KEY (id) ); alter table bbk_dstodse add index bbk_dstodse_dse_idx (dse_id); alter table bbk_dstodse add index bbk_dstodse_ds_idx (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 int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, dse_type varchar(10) default '' NOT NULL, name varchar(255) binary NOT NULL, input_nev int(10) unsigned default '0' NOT NULL, output_nev int(10) unsigned default '0' NOT NULL, dse_status tinyint(1) default '1' NOT NULL, is_local tinyint(1) default '0' NOT NULL, rel_id smallint(5) unsigned NOT NULL, stream_id smallint(5) unsigned default '0' NOT NULL, run_sel varchar(255), on_peak tinyint(1) default '1' NOT NULL, run_cycle varchar(5), modenum int(8), lumi_sum float(12,4), num_runs int(8), PRIMARY KEY (id), UNIQUE KEY (name) ); -- 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 int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, dse_id int(10) unsigned NOT NULL, input_id int(10) unsigned NOT NULL, dse_nev int(10), dse_lumi float(12,4), rel_id smallint(5) unsigned, PRIMARY KEY (id), UNIQUE KEY (dse_id,input_id) ); alter table bbk_dsetodse add index bbk_dtd_dse_idx (dse_id); alter table bbk_dsetodse add index bbk_dtd_input_idx (input_id); -- 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 int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, dse_id int(10) unsigned NOT NULL, run_status tinyint(1) unsigned default '0' NOT NULL, pr_lumi float(12,4) unsigned, run_id int(10) unsigned NOT NULL, run_nev int(10), PRIMARY KEY (id), UNIQUE KEY (dse_id,run_id) ); alter table bbk_dsetorun add index bbk_dtr_dse_idx (dse_id); alter table bbk_dsetorun add index bbk_dtr_run_idx (run_id); -- 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 int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, run_cycle varchar(5) default '' NOT NULL, run int(10) default '0' NOT NULL, on_peak tinyint(1) unsigned default '1' NOT NULL, run_nev int(10) unsigned default '0' NOT NULL, l3_lumi float(12,4) unsigned, condalias int(8) unsigned default '0' NOT NULL, bkg tinyint(1) unsigned default '0' NOT NULL, modenum int(8) default '0' NOT NULL, PRIMARY KEY (id), UNIQUE KEY run (run) ); -- ALTER TABLE bbk_runs ADD CONSTRAINT bbk_runs_fk FOREIGN KEY (condalias) REFERENCES bbk_conditions(condalias); create table bbk_releases( id smallint(5) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, name varchar(15) binary NOT NULL, precedence bigint(12) default '0' NOT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ); create table bbk_components( id int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, name varchar(40) binary NOT NULL, description blob NOT NULL, PRIMARY KEY (id) ); create table bbk_sitecontent( id int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, ds_id int(10) unsigned NOT NULL, comp_id int(10) unsigned NOT NULL, is_local tinyint(1) unsigned default '0' NOT NULL, site_id tinyint(4) unsigned default '0' NOT NULL, PRIMARY KEY (id) ); -- 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 tinyint(4) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, name varchar(15) binary NOT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ); create table bbk_streams( id smallint(5) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, name varchar(80) binary NOT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ); create table bbk_dsereject ( id int(10) unsigned auto_increment NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, dse_id int(10) unsigned NOT NULL, reject_status tinyint(1) unsigned default '1' NOT NULL, reject_type varchar(10) default 'Run' NOT NULL, reject_value int(10) unsigned NOT NULL, reject_nev int(10) default '0' NOT NULL, reject_lumi float(12,4) unsigned, PRIMARY KEY (id) ); alter table bbk_dsereject add index bbk_dsr_dse_idx (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 int(10) unsigned auto_increment NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, dse_id int(10) unsigned NOT NULL, skim_state tinyint(1) unsigned default '0' NOT NULL, skim_cycle varchar(10), PRIMARY KEY (id), UNIQUE KEY dse (dse_id,skim_cycle) ); -- 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 int(10) unsigned auto_increment NOT NULL, pub_id tinyint(1) unsigned default '0' NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, dse_id int(10) unsigned default '0' NOT NULL, filenum smallint(5) unsigned default '1' NOT NULL, components varchar(10) default '', suffix varchar(13) default '.01', bytes int(10) unsigned default '0' NOT NULL, uuid varchar(255), checksum int(10) unsigned default 0 NOT NULL, PRIMARY KEY (id), UNIQUE KEY (dse_id,filenum), UNIQUE KEY (dse_id,suffix) ); -- ALTER TABLE bbk_files ADD CONSTRAINT bbk_files_fk FOREIGN KEY (dse_id) REFERENCES bbk_dsentities(id); create table bbk_conditions ( id int(10) unsigned auto_increment NOT NULL, created bigint(14) default '0' NOT NULL, modified bigint(14) default '0' NOT NULL, condalias int(8) unsigned NOT NULL, on_peak tinyint(1) default '1' NOT NULL, condstring varchar(20), run_cycle varchar(20), sub_cycle varchar(20), cond_lumi float(12,4), PRIMARY KEY ( id ), UNIQUE KEY ( condalias, on_peak ) );