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.
#	User	Rev	Content
1	lat	1.1	-- ======================================================================
2	lat	1.3	create sequence seq_block;
3	lat	1.1	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	lat	1.3	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	lat	1.1	create table t_file_status
29			(id integer not null,
30	lat	1.2	name varchar (1000) not null,
31	lat	1.1	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	lat	1.2	name varchar (1000) not null,
39	lat	1.1	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	lat	1.3	guid varchar (1000) /* not null */,
47	lat	1.2	logical_name varchar (1000) not null,
48			checksum varchar (1000) not null,
49	lat	1.3	filesize integer not null,
50	lat	1.1	status integer not null,
51			type integer not null,
52	lat	1.3	inblock integer not null,
53	lat	1.1	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	lat	1.3	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	lat	1.1	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	lat	1.3	add constraint fk_file_status_creatby
115	lat	1.1	foreign key (created_by) references t_person (id);
116
117			alter table t_file_status
118	lat	1.3	add constraint fk_file_status_modifby
119	lat	1.1	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	lat	1.3	add constraint fk_file_type_creatby
133	lat	1.1	foreign key (created_by) references t_person (id);
134
135			alter table t_file_type
136	lat	1.3	add constraint fk_file_type_modifby
137	lat	1.1	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	lat	1.3	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	lat	1.1	foreign key (created_by) references t_person (id);
164
165			alter table t_file
166	lat	1.3	add constraint fk_file_modifby
167	lat	1.1	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	lat	1.3	/* on update cascade */ on delete cascade;
183	lat	1.1
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	lat	1.3	add constraint fk_evcoll_file_creatby
190	lat	1.1	foreign key (created_by) references t_person (id);
191
192			alter table t_evcoll_file
193	lat	1.3	add constraint fk_evcoll_file_modifby
194	lat	1.1	foreign key (modified_by) references t_person (id);
195
196			-- ======================================================================
197	lat	1.3	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	lat	1.1	on t_file_status (created_by)
221			tablespace CMS_DBS_INDX01;
222
223	lat	1.3	create index ix_file_status_modifby
224	lat	1.1	on t_file_status (modified_by)
225			tablespace CMS_DBS_INDX01;
226
227			--
228	lat	1.3	create index ix_file_type_creatby
229	lat	1.1	on t_file_type (created_by)
230			tablespace CMS_DBS_INDX01;
231
232	lat	1.3	create index ix_file_type_modifby
233	lat	1.1	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	lat	1.3	create index ix_file_inblock
246			on t_file (inblock)
247			tablespace CMS_DBS_INDX01;
248
249			create index ix_file_creatby
250	lat	1.1	on t_file (created_by)
251			tablespace CMS_DBS_INDX01;
252
253	lat	1.3	create index ix_file_modifby
254	lat	1.1	on t_file (modified_by)
255			tablespace CMS_DBS_INDX01;
256
257			--
258	lat	1.3	create index ix_evcoll_file_creatby
259	lat	1.1	on t_evcoll_file (created_by)
260			tablespace CMS_DBS_INDX01;
261
262	lat	1.3	create index ix_evcoll_file_modifby
263	lat	1.1	on t_evcoll_file (modified_by)
264			tablespace CMS_DBS_INDX01;