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.1 by lat, Tue Oct 4 21:46:23 2005 UTC vs.
Revision 1.4 by lat, Tue Oct 11 17:39:45 2005 UTC

# Line 1 | Line 1
1   -- ======================================================================
2 + create sequence seq_block_status;
3 + create sequence seq_block;
4   create sequence seq_file_status;
5   create sequence seq_file_type;
6   create sequence seq_file;
7   create sequence seq_evcoll_file;
8  
9   -- ======================================================================
10 + create table t_block_status
11 +  (id                           integer         not null,
12 +   name                         varchar (1000)  not null,
13 +   created_at                   float           not null,
14 +   created_by                   integer         not null,
15 +   modified_at                  float,
16 +   modified_by                  integer);
17 +
18 + create table t_block
19 +  (id                           integer         not null,
20 +   processed_dataset            integer         not null,
21 +   status                       integer         not null,
22 +   files                        integer         not null,
23 +   bytes                        integer         not null,
24 +   created_at                   float           not null,
25 +   created_by                   integer         not null,
26 +   modified_at                  float,
27 +   modified_by                  integer);
28 +
29   create table t_file_status
30    (id                           integer         not null,
31 <   name                         varchar (80)    not null,
31 >   name                         varchar (1000)  not null,
32     created_at                   float           not null,
33     created_by                   integer         not null,
34 <   modified_at                  float           not null,
35 <   modified_by                  integer         not null);
34 >   modified_at                  float,
35 >   modified_by                  integer);
36  
37   create table t_file_type
38    (id                           integer         not null,
39 <   name                         varchar (80)    not null,
39 >   name                         varchar (1000)  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);
42 >   modified_at                  float,
43 >   modified_by                  integer);
44  
45   create table t_file
46    (id                           integer         not null,
47 <   logical_name                 varchar (255)   not null,
48 <   checksum                     varchar (255)   not null,
49 <   filesize                     varchar (255)   not null,
47 >   guid                         varchar (1000)  /* not null */,
48 >   logical_name                 varchar (1000)  not null,
49 >   checksum                     varchar (1000)  not null,
50 >   filesize                     integer         not null,
51     status                       integer         not null,
52     type                         integer         not null,
53 +   inblock                      integer         not null,
54     created_at                   float           not null,
55     created_by                   integer         not null,
56 <   modified_at                  float           not null,
57 <   modified_by                  integer         not null);
56 >   modified_at                  float,
57 >   modified_by                  integer);
58  
59   create table t_evcoll_file
60    (id                           integer         not null,
# Line 39 | Line 62 | create table t_evcoll_file
62     fileid                       integer         not null,
63     created_at                   float           not null,
64     created_by                   integer         not null,
65 <   modified_at                  float           not null,
66 <   modified_by                  integer         not null);
65 >   modified_at                  float,
66 >   modified_by                  integer);
67  
68   -- ======================================================================
69 + alter table t_block_status
70 +  add constraint pk_block_status
71 +  primary key (id)
72 +  using index tablespace CMS_DBS_INDX01;
73 +
74 + alter table t_block_status
75 +  add constraint uq_block_status_key
76 +  unique (name);
77 +
78 + alter table t_block_status
79 +  add constraint fk_block_status_creatby
80 +  foreign key (created_by) references t_person (id);
81 +
82 + alter table t_block_status
83 +  add constraint fk_block_status_modifby
84 +  foreign key (modified_by) references t_person (id);
85 +
86 + --
87 + alter table t_block
88 +  add constraint pk_block
89 +  primary key (id)
90 +  using index tablespace CMS_DBS_INDX01;
91 +
92 + alter table t_block
93 +  add constraint fk_block_status
94 +  foreign key (status) references t_block_status (id);
95 +
96 + alter table t_block
97 +  add constraint fk_block_creatby
98 +  foreign key (created_by) references t_person (id);
99 +
100 + alter table t_block
101 +  add constraint fk_block_modifby
102 +  foreign key (modified_by) references t_person (id);
103 +
104 + --
105   alter table t_file_status
106    add constraint pk_file_status
107    primary key (id)
# Line 53 | Line 112 | alter table t_file_status
112    unique (name);
113  
114   alter table t_file_status
115 <  add constraint fk_file_status_creat
115 >  add constraint fk_file_status_creatby
116    foreign key (created_by) references t_person (id);
117  
118   alter table t_file_status
119 <  add constraint fk_file_status_mod
119 >  add constraint fk_file_status_modifby
120    foreign key (modified_by) references t_person (id);
121  
122   --
# Line 71 | Line 130 | alter table t_file_type
130    unique (name);
131  
132   alter table t_file_type
133 <  add constraint fk_file_type_creat
133 >  add constraint fk_file_type_creatby
134    foreign key (created_by) references t_person (id);
135  
136   alter table t_file_type
137 <  add constraint fk_file_type_mod
137 >  add constraint fk_file_type_modifby
138    foreign key (modified_by) references t_person (id);
139  
140   --
# Line 97 | Line 156 | alter table t_file
156    foreign key (type) references t_file_type (id);
157  
158   alter table t_file
159 <  add constraint fk_file_creat
159 >  add constraint fk_file_inblock
160 >  foreign key (status) references t_block (id);
161 >
162 > alter table t_file
163 >  add constraint fk_file_creatby
164    foreign key (created_by) references t_person (id);
165  
166   alter table t_file
167 <  add constraint fk_file_mod
167 >  add constraint fk_file_modifby
168    foreign key (modified_by) references t_person (id);
169  
170   --
# Line 117 | Line 180 | alter table t_evcoll_file
180   alter table t_evcoll_file
181    add constraint fk_evcoll_file_evcoll
182    foreign key (evcoll) references t_event_collection (id)
183 <  /* on upfloat cascade */ on delete cascade;
183 >  /* on update cascade */ on delete cascade;
184  
185   alter table t_evcoll_file
186    add constraint fk_evcoll_file_fileid
187    foreign key (fileid) references t_file (id);
188  
189   alter table t_evcoll_file
190 <  add constraint fk_evcoll_file_creat
190 >  add constraint fk_evcoll_file_creatby
191    foreign key (created_by) references t_person (id);
192  
193   alter table t_evcoll_file
194 <  add constraint fk_evcoll_file_mod
194 >  add constraint fk_evcoll_file_modifby
195    foreign key (modified_by) references t_person (id);
196  
197   -- ======================================================================
198 < create index ix_file_status_creat
198 > create index ix_block_status_creatby
199 >  on t_block_status (created_by)
200 >  tablespace CMS_DBS_INDX01;
201 >
202 > create index ix_block_status_modifby
203 >  on t_block_status (modified_by)
204 >  tablespace CMS_DBS_INDX01;
205 >
206 > --
207 > create index ix_block_status
208 >  on t_block (status)
209 >  tablespace CMS_DBS_INDX01;
210 >
211 > create index ix_block_creatby
212 >  on t_block (created_by)
213 >  tablespace CMS_DBS_INDX01;
214 >
215 > create index ix_block_modifby
216 >  on t_block (modified_by)
217 >  tablespace CMS_DBS_INDX01;
218 >
219 > --
220 > create index ix_file_status_creatby
221    on t_file_status (created_by)
222    tablespace CMS_DBS_INDX01;
223  
224 < create index ix_file_status_mod
224 > create index ix_file_status_modifby
225    on t_file_status (modified_by)
226    tablespace CMS_DBS_INDX01;
227  
228   --
229 < create index ix_file_type_creat
229 > create index ix_file_type_creatby
230    on t_file_type (created_by)
231    tablespace CMS_DBS_INDX01;
232  
233 < create index ix_file_type_mod
233 > create index ix_file_type_modifby
234    on t_file_type (modified_by)
235    tablespace CMS_DBS_INDX01;
236  
# Line 158 | Line 243 | create index ix_file_type
243    on t_file (type)
244    tablespace CMS_DBS_INDX01;
245  
246 < create index ix_file_creat
246 > create index ix_file_inblock
247 >  on t_file (inblock)
248 >  tablespace CMS_DBS_INDX01;
249 >
250 > create index ix_file_creatby
251    on t_file (created_by)
252    tablespace CMS_DBS_INDX01;
253  
254 < create index ix_file_mod
254 > create index ix_file_modifby
255    on t_file (modified_by)
256    tablespace CMS_DBS_INDX01;
257  
258   --
259 < create index ix_evcoll_file_creat
259 > create index ix_evcoll_file_creatby
260    on t_evcoll_file (created_by)
261    tablespace CMS_DBS_INDX01;
262  
263 < create index ix_evcoll_file_mod
263 > create index ix_evcoll_file_modifby
264    on t_evcoll_file (modified_by)
265    tablespace CMS_DBS_INDX01;

Diff Legend

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