Schema/RefDBLegacy/OracleFiles.sql

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

-- ======================================================================
create table t_file_status
  (id                           integer         not null,
   name                         varchar (80)    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 (80)    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,
   logical_name                 varchar (255)   not null,
   checksum                     varchar (255)   not null,
   filesize                     varchar (255)   not null,
   status                       integer         not null,
   type                         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_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_creat
  foreign key (created_by) references t_person (id);

alter table t_file_status
  add constraint fk_file_status_mod
  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_creat
  foreign key (created_by) references t_person (id);

alter table t_file_type
  add constraint fk_file_type_mod
  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_creat
  foreign key (created_by) references t_person (id);

alter table t_file
  add constraint fk_file_mod
  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 upfloat 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_creat
  foreign key (created_by) references t_person (id);

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

-- ======================================================================
create index ix_file_status_creat
  on t_file_status (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_file_status_mod
  on t_file_status (modified_by)
  tablespace CMS_DBS_INDX01;

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

create index ix_file_type_mod
  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_creat
  on t_file (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_file_mod
  on t_file (modified_by)
  tablespace CMS_DBS_INDX01;

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

create index ix_evcoll_file_mod
  on t_evcoll_file (modified_by)
  tablespace CMS_DBS_INDX01;
Revision:	1.1
Committed:	Tue Oct 4 21:46:23 2005 UTC (19 years, 7 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Log Message:	Schema converted to Oracle, various other changes
#	Content
1	-- ======================================================================
2	create sequence seq_file_status;
3	create sequence seq_file_type;
4	create sequence seq_file;
5	create sequence seq_evcoll_file;
6
7	-- ======================================================================
8	create table t_file_status
9	(id integer not null,
10	name varchar (80) not null,
11	created_at float not null,
12	created_by integer not null,
13	modified_at float not null,
14	modified_by integer not null);
15
16	create table t_file_type
17	(id integer not null,
18	name varchar (80) not null,
19	created_at float not null,
20	created_by integer not null,
21	modified_at float not null,
22	modified_by integer not null);
23
24	create table t_file
25	(id integer not null,
26	logical_name varchar (255) not null,
27	checksum varchar (255) not null,
28	filesize varchar (255) not null,
29	status integer not null,
30	type integer 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_evcoll_file
37	(id integer not null,
38	evcoll integer not null,
39	fileid integer not null,
40	created_at float not null,
41	created_by integer not null,
42	modified_at float not null,
43	modified_by integer not null);
44
45	-- ======================================================================
46	alter table t_file_status
47	add constraint pk_file_status
48	primary key (id)
49	using index tablespace CMS_DBS_INDX01;
50
51	alter table t_file_status
52	add constraint uq_file_status_key
53	unique (name);
54
55	alter table t_file_status
56	add constraint fk_file_status_creat
57	foreign key (created_by) references t_person (id);
58
59	alter table t_file_status
60	add constraint fk_file_status_mod
61	foreign key (modified_by) references t_person (id);
62
63	--
64	alter table t_file_type
65	add constraint pk_file_type
66	primary key (id)
67	using index tablespace CMS_DBS_INDX01;
68
69	alter table t_file_type
70	add constraint uq_file_type
71	unique (name);
72
73	alter table t_file_type
74	add constraint fk_file_type_creat
75	foreign key (created_by) references t_person (id);
76
77	alter table t_file_type
78	add constraint fk_file_type_mod
79	foreign key (modified_by) references t_person (id);
80
81	--
82	alter table t_file
83	add constraint pk_file
84	primary key (id)
85	using index tablespace CMS_DBS_INDX01;
86
87	alter table t_file
88	add constraint uq_file_lfn
89	unique (logical_name);
90
91	alter table t_file
92	add constraint fk_file_status
93	foreign key (status) references t_file_status (id);
94
95	alter table t_file
96	add constraint fk_file_type
97	foreign key (type) references t_file_type (id);
98
99	alter table t_file
100	add constraint fk_file_creat
101	foreign key (created_by) references t_person (id);
102
103	alter table t_file
104	add constraint fk_file_mod
105	foreign key (modified_by) references t_person (id);
106
107	--
108	alter table t_evcoll_file
109	add constraint pk_evcoll_file
110	primary key (id)
111	using index tablespace CMS_DBS_INDX01;
112
113	alter table t_evcoll_file
114	add constraint uq_evcoll_file_key
115	unique (evcoll, fileid);
116
117	alter table t_evcoll_file
118	add constraint fk_evcoll_file_evcoll
119	foreign key (evcoll) references t_event_collection (id)
120	/* on upfloat cascade */ on delete cascade;
121
122	alter table t_evcoll_file
123	add constraint fk_evcoll_file_fileid
124	foreign key (fileid) references t_file (id);
125
126	alter table t_evcoll_file
127	add constraint fk_evcoll_file_creat
128	foreign key (created_by) references t_person (id);
129
130	alter table t_evcoll_file
131	add constraint fk_evcoll_file_mod
132	foreign key (modified_by) references t_person (id);
133
134	-- ======================================================================
135	create index ix_file_status_creat
136	on t_file_status (created_by)
137	tablespace CMS_DBS_INDX01;
138
139	create index ix_file_status_mod
140	on t_file_status (modified_by)
141	tablespace CMS_DBS_INDX01;
142
143	--
144	create index ix_file_type_creat
145	on t_file_type (created_by)
146	tablespace CMS_DBS_INDX01;
147
148	create index ix_file_type_mod
149	on t_file_type (modified_by)
150	tablespace CMS_DBS_INDX01;
151
152	--
153	create index ix_file_status
154	on t_file (status)
155	tablespace CMS_DBS_INDX01;
156
157	create index ix_file_type
158	on t_file (type)
159	tablespace CMS_DBS_INDX01;
160
161	create index ix_file_creat
162	on t_file (created_by)
163	tablespace CMS_DBS_INDX01;
164
165	create index ix_file_mod
166	on t_file (modified_by)
167	tablespace CMS_DBS_INDX01;
168
169	--
170	create index ix_evcoll_file_creat
171	on t_evcoll_file (created_by)
172	tablespace CMS_DBS_INDX01;
173
174	create index ix_evcoll_file_mod
175	on t_evcoll_file (modified_by)
176	tablespace CMS_DBS_INDX01;