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
#	User	Rev	Content
1	lat	1.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;