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 (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,
   logical_name                 varchar (1000)  not null,
   checksum                     varchar (1000)  not null,
   filesize                     varchar (1000)  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.2
Committed:	Thu Oct 6 21:41:38 2005 UTC (19 years, 7 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Changes since 1.1:	+5 -5 lines
Log Message:	Change all varchars to length 1000
#	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	lat	1.2	name varchar (1000) not null,
11	lat	1.1	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	lat	1.2	name varchar (1000) not null,
19	lat	1.1	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	lat	1.2	logical_name varchar (1000) not null,
27			checksum varchar (1000) not null,
28			filesize varchar (1000) not null,
29	lat	1.1	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;