ViewVC Help
View File | Revision Log | Show Annotations | Root Listing
root/cvsroot/COMP/DBS/Schema/RefDBLegacy/OracleFiles.sql
Revision: 1.10
Committed: Tue Feb 7 20:32:13 2006 UTC (19 years, 2 months ago) by sekhri
Content type: application/sql
Branch: MAIN
CVS Tags: DBS_1_0_0_pre6, DBS_1_0_0_pre5, pre5_v01_00_00, pre4_v01_00_00, pre3_v01_00_00, pre2_v01_00_00, pre_v01_00_00, v00_00_14, v00_00_13, v00_00_12, v00_00_11, v00_00_10, v00_00_09, v00_00_08, v00_00_07, v00_00_06, v00_00_05, v00_00_004, DBS_0_0_3a, DBS_0_0_3, DBS_0_0_2, DBS_0_0_1, pre_DBS_0_0_1, post_dict_type_checking_merge, post_MiniPythonAPI_merged_to_trunk, pre_MiniPythonAPI_merge_to_trunk, DBS_0_0_0, vs20060320, AfterJan2006SchemaChanges_v01_00_01, AfterJan2006SchemaChanges_v01_00_00, AfterJan2006SchemaChanges, HEAD
Branch point for: BranchForCPPWebServiceTesting
Changes since 1.9: +1 -1 lines
Error occurred while calculating annotation data.
Log Message: 
Incorporated the new schema into the DBS. Fixed some bugs in the schema

File Contents

# Content
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 (1000) not null);
24
25 create table t_file_type
26 (id 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 */,
36 type 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);
43
44 -- ======================================================================
45 alter table t_block_status
46 add constraint pk_block_status
47 primary key (id)
48 using index tablespace INDX01;
49
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 pk_file_status
71 primary key (id)
72 using index tablespace INDX01;
73
74 alter table t_file_status
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 INDX01;
83
84 alter table t_file_type
85 add constraint uq_file_type
86 unique (name);
87
88 --
89 alter table t_file
90 add constraint pk_file
91 primary key (id)
92 using index tablespace INDX01;
93
94 alter table t_file
95 add constraint uq_file_lfn
96 unique (logical_name);
97
98 alter table t_file
99 add constraint fk_file_status
100 foreign key (status) references t_file_status (id);
101
102 alter table t_file
103 add constraint fk_file_type
104 foreign key (type) references t_file_type (id);
105
106 alter table t_file
107 add constraint fk_file_inblock
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 INDX01;
115
116 alter table t_evcoll_file
117 add constraint uq_evcoll_file_key
118 unique (evcoll, fileid);
119
120 alter table t_evcoll_file
121 add constraint fk_evcoll_file_evcoll
122 foreign key (evcoll) references t_event_collection (id)
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
129 -- ======================================================================
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 INDX01;
138
139 create index ix_file_type
140 on t_file (type)
141 tablespace INDX01;
142
143 create index ix_file_inblock
144 on t_file (inblock)
145 tablespace INDX01;