ViewVC Help
View File | Revision Log | Show Annotations | Root Listing
root/cvsroot/COMP/DBS/Schema/RefDBLegacy/OracleFiles.sql
(Generate patch)

Comparing COMP/DBS/Schema/RefDBLegacy/OracleFiles.sql (file contents):
Revision 1.2 by lat, Thu Oct 6 21:41:38 2005 UTC vs.
Revision 1.3 by lat, Mon Oct 10 20:19:48 2005 UTC

# Line 1 | Line 1
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,
# Line 23 | Line 43 | create table t_file_type
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                     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,
# Line 43 | Line 65 | create table t_evcoll_file
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)
# Line 53 | Line 111 | alter table t_file_status
111    unique (name);
112  
113   alter table t_file_status
114 <  add constraint fk_file_status_creat
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_mod
118 >  add constraint fk_file_status_modifby
119    foreign key (modified_by) references t_person (id);
120  
121   --
# Line 71 | Line 129 | alter table t_file_type
129    unique (name);
130  
131   alter table t_file_type
132 <  add constraint fk_file_type_creat
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_mod
136 >  add constraint fk_file_type_modifby
137    foreign key (modified_by) references t_person (id);
138  
139   --
# Line 97 | Line 155 | alter table t_file
155    foreign key (type) references t_file_type (id);
156  
157   alter table t_file
158 <  add constraint fk_file_creat
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_mod
166 >  add constraint fk_file_modifby
167    foreign key (modified_by) references t_person (id);
168  
169   --
# Line 117 | Line 179 | alter table t_evcoll_file
179   alter table t_evcoll_file
180    add constraint fk_evcoll_file_evcoll
181    foreign key (evcoll) references t_event_collection (id)
182 <  /* on upfloat cascade */ on delete cascade;
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_creat
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_mod
193 >  add constraint fk_evcoll_file_modifby
194    foreign key (modified_by) references t_person (id);
195  
196   -- ======================================================================
197 < create index ix_file_status_creat
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_mod
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_creat
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_mod
232 > create index ix_file_type_modifby
233    on t_file_type (modified_by)
234    tablespace CMS_DBS_INDX01;
235  
# Line 158 | Line 242 | create index ix_file_type
242    on t_file (type)
243    tablespace CMS_DBS_INDX01;
244  
245 < create index ix_file_creat
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_mod
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_creat
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_mod
262 > create index ix_evcoll_file_modifby
263    on t_evcoll_file (modified_by)
264    tablespace CMS_DBS_INDX01;

Diff Legend

– Removed lines
+ Added lines
< Changed lines
> Changed lines