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.7 by lat, Fri Jan 20 11:53:17 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  
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 –
64   --
65   alter table t_file_status
66    add constraint pk_file_status
67    primary key (id)
68 <  using index tablespace CMS_DBS_INDX01;
68 >  using index tablespace INDX01;
69  
70   alter table t_file_status
71    add constraint uq_file_status_key
72    unique (name);
73  
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 –
74   --
75   alter table t_file_type
76    add constraint pk_file_type
77    primary key (id)
78 <  using index tablespace CMS_DBS_INDX01;
78 >  using index tablespace INDX01;
79  
80   alter table t_file_type
81    add constraint uq_file_type
82    unique (name);
83  
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 –
84   --
85   alter table t_file
86    add constraint pk_file
87    primary key (id)
88 <  using index tablespace CMS_DBS_INDX01;
88 >  using index tablespace INDX01;
89  
90   alter table t_file
91    add constraint uq_file_lfn
# Line 158 | Line 103 | alter table t_file
103    add constraint fk_file_inblock
104    foreign key (status) references t_block (id);
105  
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);
168 –
106   --
107   alter table t_evcoll_file
108    add constraint pk_evcoll_file
109    primary key (id)
110 <  using index tablespace CMS_DBS_INDX01;
110 >  using index tablespace INDX01;
111  
112   alter table t_evcoll_file
113    add constraint uq_evcoll_file_key
# Line 185 | Line 122 | alter table t_evcoll_file
122    add constraint fk_evcoll_file_fileid
123    foreign key (fileid) references t_file (id);
124  
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 –
125   -- ======================================================================
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 – --
126   create index ix_block_status
127    on t_block (status)
128 <  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;
128 >  tablespace INDX01;
129  
130   --
131   create index ix_file_status
132    on t_file (status)
133 <  tablespace CMS_DBS_INDX01;
133 >  tablespace INDX01;
134  
135   create index ix_file_type
136    on t_file (type)
137 <  tablespace CMS_DBS_INDX01;
137 >  tablespace INDX01;
138  
139   create index ix_file_inblock
140    on t_file (inblock)
141 <  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;
141 >  tablespace INDX01;

Diff Legend

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