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.3 by lat, Mon Oct 10 20:19:48 2005 UTC vs.
Revision 1.10 by sekhri, Tue Feb 7 20:32:13 2006 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;
# Line 8 | Line 9 | create sequence seq_evcoll_file;
9   -- ======================================================================
10   create table t_block_status
11    (id                           integer         not null,
12 <   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);
12 >   name                         varchar (1000)  not null);
13  
14   create table t_block
15    (id                           integer         not null,
16     processed_dataset            integer         not null,
17     status                       integer         not null,
18     files                        integer         not null,
19 <   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);
19 >   bytes                        integer         not null);
20  
21   create table t_file_status
22    (id                           integer         not null,
23 <   name                         varchar (1000)  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);
23 >   name                         varchar (1000)  not null);
24  
25   create table t_file_type
26    (id                           integer         not null,
27 <   name                         varchar (1000)  not null,
39 <   created_at                   float           not null,
40 <   created_by                   integer         not null,
41 <   modified_at                  float           not null,
42 <   modified_by                  integer         not null);
27 >   name                         varchar (1000)  not null);
28  
29   create table t_file
30    (id                           integer         not null,
31     guid                         varchar (1000)  /* not null */,
32     logical_name                 varchar (1000)  not null,
33 <   checksum                     varchar (1000)  not null,
34 <   filesize                     integer         not null,
35 <   status                       integer         not null,
33 >   checksum                     varchar (1000)  /* not null */,
34 >   filesize                     integer         /* not null */,
35 >   status                       integer         /* not null */,
36     type                         integer         not null,
37 <   inblock                      integer         not null,
53 <   created_at                   float           not null,
54 <   created_by                   integer         not null,
55 <   modified_at                  float           not null,
56 <   modified_by                  integer         not null);
37 >   inblock                      integer         not null);
38  
39   create table t_evcoll_file
40    (id                           integer         not null,
41     evcoll                       integer         not null,
42 <   fileid                       integer         not null,
62 <   created_at                   float           not null,
63 <   created_by                   integer         not null,
64 <   modified_at                  float           not null,
65 <   modified_by                  integer         not null);
42 >   fileid                       integer         not null);
43  
44   -- ======================================================================
45   alter table t_block_status
46    add constraint pk_block_status
47    primary key (id)
48 <  using index tablespace CMS_DBS_INDX01;
48 >  using index tablespace INDX01;
49  
50   alter table t_block_status
51    add constraint uq_block_status_key
52    unique (name);
53  
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 –
54   --
55   alter table t_block
56    add constraint pk_block
57    primary key (id)
58 <  using index tablespace CMS_DBS_INDX01;
58 >  using index tablespace INDX01;
59  
60   alter table t_block
61    add constraint fk_block_status
62    foreign key (status) references t_block_status (id);
63  
64   alter table t_block
65 <  add constraint fk_block_creatby
66 <  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);
65 >   add constraint fk_processed_dataset
66 >   foreign key (processed_dataset) references t_processed_dataset (id);
67  
68   --
69   alter table t_file_status
70    add constraint pk_file_status
71    primary key (id)
72 <  using index tablespace CMS_DBS_INDX01;
72 >  using index tablespace INDX01;
73  
74   alter table t_file_status
75    add constraint uq_file_status_key
76    unique (name);
77  
113 – alter table t_file_status
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_modifby
119 –  foreign key (modified_by) references t_person (id);
120 –
78   --
79   alter table t_file_type
80    add constraint pk_file_type
81    primary key (id)
82 <  using index tablespace CMS_DBS_INDX01;
82 >  using index tablespace INDX01;
83  
84   alter table t_file_type
85    add constraint uq_file_type
86    unique (name);
87  
131 – alter table t_file_type
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_modifby
137 –  foreign key (modified_by) references t_person (id);
138 –
88   --
89   alter table t_file
90    add constraint pk_file
91    primary key (id)
92 <  using index tablespace CMS_DBS_INDX01;
92 >  using index tablespace INDX01;
93  
94   alter table t_file
95    add constraint uq_file_lfn
# Line 156 | Line 105 | alter table t_file
105  
106   alter table t_file
107    add constraint fk_file_inblock
108 <  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_modifby
167 <  foreign key (modified_by) references t_person (id);
108 >  foreign key (inblock) references t_block (id);
109  
110   --
111   alter table t_evcoll_file
112    add constraint pk_evcoll_file
113    primary key (id)
114 <  using index tablespace CMS_DBS_INDX01;
114 >  using index tablespace INDX01;
115  
116   alter table t_evcoll_file
117    add constraint uq_evcoll_file_key
# Line 185 | Line 126 | alter table t_evcoll_file
126    add constraint fk_evcoll_file_fileid
127    foreign key (fileid) references t_file (id);
128  
188 – alter table t_evcoll_file
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_modifby
194 –  foreign key (modified_by) references t_person (id);
195 –
129   -- ======================================================================
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 – --
130   create index ix_block_status
131    on t_block (status)
132 <  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_modifby
224 <  on t_file_status (modified_by)
225 <  tablespace CMS_DBS_INDX01;
226 <
227 < --
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_modifby
233 <  on t_file_type (modified_by)
234 <  tablespace CMS_DBS_INDX01;
132 >  tablespace INDX01;
133  
134   --
135   create index ix_file_status
136    on t_file (status)
137 <  tablespace CMS_DBS_INDX01;
137 >  tablespace INDX01;
138  
139   create index ix_file_type
140    on t_file (type)
141 <  tablespace CMS_DBS_INDX01;
141 >  tablespace INDX01;
142  
143   create index ix_file_inblock
144    on t_file (inblock)
145 <  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_modifby
254 <  on t_file (modified_by)
255 <  tablespace CMS_DBS_INDX01;
256 <
257 < --
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_modifby
263 <  on t_evcoll_file (modified_by)
264 <  tablespace CMS_DBS_INDX01;
145 >  tablespace INDX01;

Diff Legend

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