Schema/RefDBLegacy/OracleFiles.sql

-- ======================================================================
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           not null,
   modified_by                  integer         not null);

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           not null,
   modified_by                  integer         not null);

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           not null,
   modified_by                  integer         not null);

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           not null,
   modified_by                  integer         not null);

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           not null,
   modified_by                  integer         not null);

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           not null,
   modified_by                  integer         not null);

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