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.9 by afaq, Mon Feb 6 23:18:54 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;
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 +
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);
20 +
21   create table t_file_status
22    (id                           integer         not null,
23 <   name                         varchar (80)    not null,
11 <   created_at                   float           not null,
12 <   created_by                   integer         not null,
13 <   modified_at                  float           not null,
14 <   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 (80)    not null,
19 <   created_at                   float           not null,
20 <   created_by                   integer         not null,
21 <   modified_at                  float           not null,
22 <   modified_by                  integer         not null);
27 >   name                         varchar (1000)  not null);
28  
29   create table t_file
30    (id                           integer         not null,
31 <   logical_name                 varchar (255)   not null,
32 <   checksum                     varchar (255)   not null,
33 <   filesize                     varchar (255)   not null,
34 <   status                       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 */,
36     type                         integer         not null,
37 <   created_at                   float           not null,
32 <   created_by                   integer         not null,
33 <   modified_at                  float           not null,
34 <   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,
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 >   fileid                       integer         not null);
43  
44   -- ======================================================================
45 < alter table t_file_status
46 <  add constraint pk_file_status
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_file_status
51 <  add constraint uq_file_status_key
50 > alter table t_block_status
51 >  add constraint uq_block_status_key
52    unique (name);
53  
54 + --
55 + alter table t_block
56 +  add constraint pk_block
57 +  primary key (id)
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_processed_dataset
66 +   foreign key (processed_dataset) references t_processed_dataset (id);
67 +
68 + --
69   alter table t_file_status
70 <  add constraint fk_file_status_creat
71 <  foreign key (created_by) references t_person (id);
70 >  add constraint pk_file_status
71 >  primary key (id)
72 >  using index tablespace INDX01;
73  
74   alter table t_file_status
75 <  add constraint fk_file_status_mod
76 <  foreign key (modified_by) references t_person (id);
75 >  add constraint uq_file_status_key
76 >  unique (name);
77  
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  
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 –
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 97 | Line 104 | alter table t_file
104    foreign key (type) references t_file_type (id);
105  
106   alter table t_file
107 <  add constraint fk_file_creat
108 <  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);
107 >  add constraint fk_file_inblock
108 >  foreign key (status) 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 117 | Line 120 | alter table t_evcoll_file
120   alter table t_evcoll_file
121    add constraint fk_evcoll_file_evcoll
122    foreign key (evcoll) references t_event_collection (id)
123 <  /* on upfloat cascade */ on delete cascade;
123 >  /* on update cascade */ on delete cascade;
124  
125   alter table t_evcoll_file
126    add constraint fk_evcoll_file_fileid
127    foreign key (fileid) references t_file (id);
128  
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 –
129   -- ======================================================================
130 < create index ix_file_status_creat
131 <  on t_file_status (created_by)
132 <  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;
130 > create index ix_block_status
131 >  on t_block (status)
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;
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;
141 >  tablespace INDX01;
142  
143 < create index ix_evcoll_file_mod
144 <  on t_evcoll_file (modified_by)
145 <  tablespace CMS_DBS_INDX01;
143 > create index ix_file_inblock
144 >  on t_file (inblock)
145 >  tablespace INDX01;

Diff Legend

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