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
#	User	Rev	Content
1	lat	1.1	-- ======================================================================
2	lat	1.4	create sequence seq_block_status;
3	lat	1.3	create sequence seq_block;
4	lat	1.1	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	lat	1.3	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	lat	1.4	modified_at float,
16			modified_by integer);
17	lat	1.3
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	lat	1.4	modified_at float,
27			modified_by integer);
28	lat	1.3
29	lat	1.1	create table t_file_status
30			(id integer not null,
31	lat	1.2	name varchar (1000) not null,
32	lat	1.1	created_at float not null,
33			created_by integer not null,
34	lat	1.4	modified_at float,
35			modified_by integer);
36	lat	1.1
37			create table t_file_type
38			(id integer not null,
39	lat	1.2	name varchar (1000) not null,
40	lat	1.1	created_at float not null,
41			created_by integer not null,
42	lat	1.4	modified_at float,
43			modified_by integer);
44	lat	1.1
45			create table t_file
46			(id integer not null,
47	lat	1.3	guid varchar (1000) /* not null */,
48	lat	1.2	logical_name varchar (1000) not null,
49			checksum varchar (1000) not null,
50	lat	1.3	filesize integer not null,
51	lat	1.1	status integer not null,
52			type integer not null,
53	lat	1.3	inblock integer not null,
54	lat	1.1	created_at float not null,
55			created_by integer not null,
56	lat	1.4	modified_at float,
57			modified_by integer);
58	lat	1.1
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	lat	1.4	modified_at float,
66			modified_by integer);
67	lat	1.1
68			-- ======================================================================
69	lat	1.3	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	lat	1.1	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	lat	1.3	add constraint fk_file_status_creatby
116	lat	1.1	foreign key (created_by) references t_person (id);
117
118			alter table t_file_status
119	lat	1.3	add constraint fk_file_status_modifby
120	lat	1.1	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	lat	1.3	add constraint fk_file_type_creatby
134	lat	1.1	foreign key (created_by) references t_person (id);
135
136			alter table t_file_type
137	lat	1.3	add constraint fk_file_type_modifby
138	lat	1.1	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	lat	1.3	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	lat	1.1	foreign key (created_by) references t_person (id);
165
166			alter table t_file
167	lat	1.3	add constraint fk_file_modifby
168	lat	1.1	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	lat	1.3	/* on update cascade */ on delete cascade;
184	lat	1.1
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	lat	1.3	add constraint fk_evcoll_file_creatby
191	lat	1.1	foreign key (created_by) references t_person (id);
192
193			alter table t_evcoll_file
194	lat	1.3	add constraint fk_evcoll_file_modifby
195	lat	1.1	foreign key (modified_by) references t_person (id);
196
197			-- ======================================================================
198	lat	1.3	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	lat	1.1	on t_file_status (created_by)
222			tablespace CMS_DBS_INDX01;
223
224	lat	1.3	create index ix_file_status_modifby
225	lat	1.1	on t_file_status (modified_by)
226			tablespace CMS_DBS_INDX01;
227
228			--
229	lat	1.3	create index ix_file_type_creatby
230	lat	1.1	on t_file_type (created_by)
231			tablespace CMS_DBS_INDX01;
232
233	lat	1.3	create index ix_file_type_modifby
234	lat	1.1	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	lat	1.3	create index ix_file_inblock
247			on t_file (inblock)
248			tablespace CMS_DBS_INDX01;
249
250			create index ix_file_creatby
251	lat	1.1	on t_file (created_by)
252			tablespace CMS_DBS_INDX01;
253
254	lat	1.3	create index ix_file_modifby
255	lat	1.1	on t_file (modified_by)
256			tablespace CMS_DBS_INDX01;
257
258			--
259	lat	1.3	create index ix_evcoll_file_creatby
260	lat	1.1	on t_evcoll_file (created_by)
261			tablespace CMS_DBS_INDX01;
262
263	lat	1.3	create index ix_evcoll_file_modifby
264	lat	1.1	on t_evcoll_file (modified_by)
265			tablespace CMS_DBS_INDX01;