Schema/RefDBLegacy/OracleRun.sql

-- ======================================================================
create sequence seq_run_quality;
create sequence seq_run;
create sequence seq_evcoll_run;

-- ======================================================================
create table t_run_quality
  (id                           integer         not null,
   name                         varchar (1000)  not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_run
  (id                           integer         not null,
   run_number                   integer         not null,
   run_quality                  integer         not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_evcoll_run
  (event_collection             integer         not null,
   run                          integer         not null);

-- ======================================================================
alter table t_run_quality
  add constraint pk_run_quality
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_run_quality
  add constraint uq_run_quality_name
  unique (name);

alter table t_run_quality
  add constraint fk_run_quality_creatby
  foreign key (created_by) references t_person (id);

alter table t_run_quality
  add constraint fk_run_quality_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_run
  add constraint pk_run
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_run
  add constraint uq_run_number
  unique (run_number);

alter table t_run
  add constraint fk_run_quality
  foreign key (run_quality) references t_run_quality (id);

alter table t_run
  add constraint fk_run_creatby
  foreign key (created_by) references t_person (id);

alter table t_run
  add constraint fk_run_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_evcoll_run
  add constraint pk_evcoll_run
  primary key (event_collection, run)
  using index tablespace CMS_DBS_INDX01;

alter table t_evcoll_run
  add constraint fk_evcoll_run_evcoll
  foreign key (event_collection) references t_event_collection (id);

alter table t_evcoll_run
  add constraint fk_evcoll_run_run
  foreign key (run) references t_run (id);

-- ======================================================================
create index ix_run_quality_creatby
  on t_run_quality (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_run_quality_modifby
  on t_run_quality (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_run_creatby
  on t_run (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_run_modifby
  on t_run (modified_by)
  tablespace CMS_DBS_INDX01;
Revision:	1.1
Committed:	Thu Oct 13 07:08:23 2005 UTC (19 years, 6 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Log Message:	Move run-related stuff to separate file. Move parameter set on app_config, make it a string for now, and make it part of the key on app_config.
#	Content
1	-- ======================================================================
2	create sequence seq_run_quality;
3	create sequence seq_run;
4	create sequence seq_evcoll_run;
5
6	-- ======================================================================
7	create table t_run_quality
8	(id integer not null,
9	name varchar (1000) not null,
10	created_at float not null,
11	created_by integer not null,
12	modified_at float,
13	modified_by integer);
14
15	create table t_run
16	(id integer not null,
17	run_number integer not null,
18	run_quality integer not null,
19	created_at float not null,
20	created_by integer not null,
21	modified_at float,
22	modified_by integer);
23
24	create table t_evcoll_run
25	(event_collection integer not null,
26	run integer not null);
27
28	-- ======================================================================
29	alter table t_run_quality
30	add constraint pk_run_quality
31	primary key (id)
32	using index tablespace CMS_DBS_INDX01;
33
34	alter table t_run_quality
35	add constraint uq_run_quality_name
36	unique (name);
37
38	alter table t_run_quality
39	add constraint fk_run_quality_creatby
40	foreign key (created_by) references t_person (id);
41
42	alter table t_run_quality
43	add constraint fk_run_quality_modifby
44	foreign key (modified_by) references t_person (id);
45
46	--
47	alter table t_run
48	add constraint pk_run
49	primary key (id)
50	using index tablespace CMS_DBS_INDX01;
51
52	alter table t_run
53	add constraint uq_run_number
54	unique (run_number);
55
56	alter table t_run
57	add constraint fk_run_quality
58	foreign key (run_quality) references t_run_quality (id);
59
60	alter table t_run
61	add constraint fk_run_creatby
62	foreign key (created_by) references t_person (id);
63
64	alter table t_run
65	add constraint fk_run_modifby
66	foreign key (modified_by) references t_person (id);
67
68	--
69	alter table t_evcoll_run
70	add constraint pk_evcoll_run
71	primary key (event_collection, run)
72	using index tablespace CMS_DBS_INDX01;
73
74	alter table t_evcoll_run
75	add constraint fk_evcoll_run_evcoll
76	foreign key (event_collection) references t_event_collection (id);
77
78	alter table t_evcoll_run
79	add constraint fk_evcoll_run_run
80	foreign key (run) references t_run (id);
81
82	-- ======================================================================
83	create index ix_run_quality_creatby
84	on t_run_quality (created_by)
85	tablespace CMS_DBS_INDX01;
86
87	create index ix_run_quality_modifby
88	on t_run_quality (modified_by)
89	tablespace CMS_DBS_INDX01;
90
91	--
92	create index ix_run_creatby
93	on t_run (created_by)
94	tablespace CMS_DBS_INDX01;
95
96	create index ix_run_modifby
97	on t_run (modified_by)
98	tablespace CMS_DBS_INDX01;