Schema/RefDBLegacy/OracleFiles.sql

-- ======================================================================
create sequence seq_block_status;
create sequence seq_block;
create sequence seq_file_status;
create sequence seq_file_type;
create sequence seq_file;
create sequence seq_evcoll_file;

-- ======================================================================
create table t_block_status
  (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_block
  (id                           integer         not null,
   processed_dataset            integer         not null,
   status                       integer         not null,
   files                        integer         not null,
   bytes                        integer         not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_file_status
  (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_file_type
  (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_file
  (id                           integer         not null,
   guid                         varchar (1000)  /* not null */,
   logical_name                 varchar (1000)  not null,
   checksum                     varchar (1000)  not null,
   filesize                     integer         not null,
   status                       integer         not null,
   type                         integer         not null,
   inblock                      integer         not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_evcoll_file
  (id                           integer         not null,
   evcoll                       integer         not null,
   fileid                       integer         not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

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

alter table t_block_status
  add constraint uq_block_status_key
  unique (name);

alter table t_block_status
  add constraint fk_block_status_creatby
  foreign key (created_by) references t_person (id);

alter table t_block_status
  add constraint fk_block_status_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_block
  add constraint pk_block
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_block
  add constraint fk_block_status
  foreign key (status) references t_block_status (id);

alter table t_block
  add constraint fk_block_creatby
  foreign key (created_by) references t_person (id);

alter table t_block
  add constraint fk_block_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_file_status
  add constraint pk_file_status
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_file_status
  add constraint uq_file_status_key
  unique (name);

alter table t_file_status
  add constraint fk_file_status_creatby
  foreign key (created_by) references t_person (id);

alter table t_file_status
  add constraint fk_file_status_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_file_type
  add constraint pk_file_type
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_file_type
  add constraint uq_file_type
  unique (name);

alter table t_file_type
  add constraint fk_file_type_creatby
  foreign key (created_by) references t_person (id);

alter table t_file_type
  add constraint fk_file_type_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_file
  add constraint pk_file
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_file
  add constraint uq_file_lfn
  unique (logical_name);

alter table t_file
  add constraint fk_file_status
  foreign key (status) references t_file_status (id);

alter table t_file
  add constraint fk_file_type
  foreign key (type) references t_file_type (id);

alter table t_file
  add constraint fk_file_inblock
  foreign key (status) references t_block (id);

alter table t_file
  add constraint fk_file_creatby
  foreign key (created_by) references t_person (id);

alter table t_file
  add constraint fk_file_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_evcoll_file
  add constraint pk_evcoll_file
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_evcoll_file
  add constraint uq_evcoll_file_key
  unique (evcoll, fileid);

alter table t_evcoll_file
  add constraint fk_evcoll_file_evcoll
  foreign key (evcoll) references t_event_collection (id)
  /* on update cascade */ on delete cascade;

alter table t_evcoll_file
  add constraint fk_evcoll_file_fileid
  foreign key (fileid) references t_file (id);

alter table t_evcoll_file
  add constraint fk_evcoll_file_creatby
  foreign key (created_by) references t_person (id);

alter table t_evcoll_file
  add constraint fk_evcoll_file_modifby
  foreign key (modified_by) references t_person (id);

-- ======================================================================
create index ix_block_status_creatby
  on t_block_status (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_block_status_modifby
  on t_block_status (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_block_status
  on t_block (status)
  tablespace CMS_DBS_INDX01;

create index ix_block_creatby
  on t_block (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_block_modifby
  on t_block (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_file_status_creatby
  on t_file_status (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_file_status_modifby
  on t_file_status (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_file_type_creatby
  on t_file_type (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_file_type_modifby
  on t_file_type (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_file_status
  on t_file (status)
  tablespace CMS_DBS_INDX01;

create index ix_file_type
  on t_file (type)
  tablespace CMS_DBS_INDX01;

create index ix_file_inblock
  on t_file (inblock)
  tablespace CMS_DBS_INDX01;

create index ix_file_creatby
  on t_file (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_file_modifby
  on t_file (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_evcoll_file_creatby
  on t_evcoll_file (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_evcoll_file_modifby
  on t_evcoll_file (modified_by)
  tablespace CMS_DBS_INDX01;
Revision:	1.4
Committed:	Tue Oct 11 17:39:45 2005 UTC (19 years, 6 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Changes since 1.3:	+13 -12 lines
Log Message:	Make modified_* columns optional
#	Content
1	-- ======================================================================
2	create sequence seq_block_status;
3	create sequence seq_block;
4	create sequence seq_file_status;
5	create sequence seq_file_type;
6	create sequence seq_file;
7	create sequence seq_evcoll_file;
8
9	-- ======================================================================
10	create table t_block_status
11	(id integer not null,
12	name varchar (1000) not null,
13	created_at float not null,
14	created_by integer not null,
15	modified_at float,
16	modified_by integer);
17
18	create table t_block
19	(id integer not null,
20	processed_dataset integer not null,
21	status integer not null,
22	files integer not null,
23	bytes integer not null,
24	created_at float not null,
25	created_by integer not null,
26	modified_at float,
27	modified_by integer);
28
29	create table t_file_status
30	(id integer not null,
31	name varchar (1000) not null,
32	created_at float not null,
33	created_by integer not null,
34	modified_at float,
35	modified_by integer);
36
37	create table t_file_type
38	(id integer not null,
39	name varchar (1000) not null,
40	created_at float not null,
41	created_by integer not null,
42	modified_at float,
43	modified_by integer);
44
45	create table t_file
46	(id integer not null,
47	guid varchar (1000) /* not null */,
48	logical_name varchar (1000) not null,
49	checksum varchar (1000) not null,
50	filesize integer not null,
51	status integer not null,
52	type integer not null,
53	inblock integer not null,
54	created_at float not null,
55	created_by integer not null,
56	modified_at float,
57	modified_by integer);
58
59	create table t_evcoll_file
60	(id integer not null,
61	evcoll integer not null,
62	fileid integer not null,
63	created_at float not null,
64	created_by integer not null,
65	modified_at float,
66	modified_by integer);
67
68	-- ======================================================================
69	alter table t_block_status
70	add constraint pk_block_status
71	primary key (id)
72	using index tablespace CMS_DBS_INDX01;
73
74	alter table t_block_status
75	add constraint uq_block_status_key
76	unique (name);
77
78	alter table t_block_status
79	add constraint fk_block_status_creatby
80	foreign key (created_by) references t_person (id);
81
82	alter table t_block_status
83	add constraint fk_block_status_modifby
84	foreign key (modified_by) references t_person (id);
85
86	--
87	alter table t_block
88	add constraint pk_block
89	primary key (id)
90	using index tablespace CMS_DBS_INDX01;
91
92	alter table t_block
93	add constraint fk_block_status
94	foreign key (status) references t_block_status (id);
95
96	alter table t_block
97	add constraint fk_block_creatby
98	foreign key (created_by) references t_person (id);
99
100	alter table t_block
101	add constraint fk_block_modifby
102	foreign key (modified_by) references t_person (id);
103
104	--
105	alter table t_file_status
106	add constraint pk_file_status
107	primary key (id)
108	using index tablespace CMS_DBS_INDX01;
109
110	alter table t_file_status
111	add constraint uq_file_status_key
112	unique (name);
113
114	alter table t_file_status
115	add constraint fk_file_status_creatby
116	foreign key (created_by) references t_person (id);
117
118	alter table t_file_status
119	add constraint fk_file_status_modifby
120	foreign key (modified_by) references t_person (id);
121
122	--
123	alter table t_file_type
124	add constraint pk_file_type
125	primary key (id)
126	using index tablespace CMS_DBS_INDX01;
127
128	alter table t_file_type
129	add constraint uq_file_type
130	unique (name);
131
132	alter table t_file_type
133	add constraint fk_file_type_creatby
134	foreign key (created_by) references t_person (id);
135
136	alter table t_file_type
137	add constraint fk_file_type_modifby
138	foreign key (modified_by) references t_person (id);
139
140	--
141	alter table t_file
142	add constraint pk_file
143	primary key (id)
144	using index tablespace CMS_DBS_INDX01;
145
146	alter table t_file
147	add constraint uq_file_lfn
148	unique (logical_name);
149
150	alter table t_file
151	add constraint fk_file_status
152	foreign key (status) references t_file_status (id);
153
154	alter table t_file
155	add constraint fk_file_type
156	foreign key (type) references t_file_type (id);
157
158	alter table t_file
159	add constraint fk_file_inblock
160	foreign key (status) references t_block (id);
161
162	alter table t_file
163	add constraint fk_file_creatby
164	foreign key (created_by) references t_person (id);
165
166	alter table t_file
167	add constraint fk_file_modifby
168	foreign key (modified_by) references t_person (id);
169
170	--
171	alter table t_evcoll_file
172	add constraint pk_evcoll_file
173	primary key (id)
174	using index tablespace CMS_DBS_INDX01;
175
176	alter table t_evcoll_file
177	add constraint uq_evcoll_file_key
178	unique (evcoll, fileid);
179
180	alter table t_evcoll_file
181	add constraint fk_evcoll_file_evcoll
182	foreign key (evcoll) references t_event_collection (id)
183	/* on update cascade */ on delete cascade;
184
185	alter table t_evcoll_file
186	add constraint fk_evcoll_file_fileid
187	foreign key (fileid) references t_file (id);
188
189	alter table t_evcoll_file
190	add constraint fk_evcoll_file_creatby
191	foreign key (created_by) references t_person (id);
192
193	alter table t_evcoll_file
194	add constraint fk_evcoll_file_modifby
195	foreign key (modified_by) references t_person (id);
196
197	-- ======================================================================
198	create index ix_block_status_creatby
199	on t_block_status (created_by)
200	tablespace CMS_DBS_INDX01;
201
202	create index ix_block_status_modifby
203	on t_block_status (modified_by)
204	tablespace CMS_DBS_INDX01;
205
206	--
207	create index ix_block_status
208	on t_block (status)
209	tablespace CMS_DBS_INDX01;
210
211	create index ix_block_creatby
212	on t_block (created_by)
213	tablespace CMS_DBS_INDX01;
214
215	create index ix_block_modifby
216	on t_block (modified_by)
217	tablespace CMS_DBS_INDX01;
218
219	--
220	create index ix_file_status_creatby
221	on t_file_status (created_by)
222	tablespace CMS_DBS_INDX01;
223
224	create index ix_file_status_modifby
225	on t_file_status (modified_by)
226	tablespace CMS_DBS_INDX01;
227
228	--
229	create index ix_file_type_creatby
230	on t_file_type (created_by)
231	tablespace CMS_DBS_INDX01;
232
233	create index ix_file_type_modifby
234	on t_file_type (modified_by)
235	tablespace CMS_DBS_INDX01;
236
237	--
238	create index ix_file_status
239	on t_file (status)
240	tablespace CMS_DBS_INDX01;
241
242	create index ix_file_type
243	on t_file (type)
244	tablespace CMS_DBS_INDX01;
245
246	create index ix_file_inblock
247	on t_file (inblock)
248	tablespace CMS_DBS_INDX01;
249
250	create index ix_file_creatby
251	on t_file (created_by)
252	tablespace CMS_DBS_INDX01;
253
254	create index ix_file_modifby
255	on t_file (modified_by)
256	tablespace CMS_DBS_INDX01;
257
258	--
259	create index ix_evcoll_file_creatby
260	on t_evcoll_file (created_by)
261	tablespace CMS_DBS_INDX01;
262
263	create index ix_evcoll_file_modifby
264	on t_evcoll_file (modified_by)
265	tablespace CMS_DBS_INDX01;