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
Log Message: 
Incorporated the new schema into the DBS. Fixed some bugs in the schema

File Contents

# User Rev Content
1 lat 1.1 -- ======================================================================
2 lat 1.4 create sequence seq_block_status;
3 lat 1.3 create sequence seq_block;
4 lat 1.1 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 lat 1.3 create table t_block_status
11     (id integer not null,
12 lat 1.5 name varchar (1000) not null);
13 lat 1.3
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 lat 1.5 bytes integer not null);
20 lat 1.3
21 lat 1.1 create table t_file_status
22     (id integer not null,
23 lat 1.5 name varchar (1000) not null);
24 lat 1.1
25     create table t_file_type
26     (id integer not null,
27 lat 1.5 name varchar (1000) not null);
28 lat 1.1
29     create table t_file
30     (id integer not null,
31 lat 1.3 guid varchar (1000) /* not null */,
32 lat 1.2 logical_name varchar (1000) not null,
33 lat 1.6 checksum varchar (1000) /* not null */,
34     filesize integer /* not null */,
35 lat 1.5 status integer /* not null */,
36 lat 1.1 type integer not null,
37 lat 1.5 inblock integer not null);
38 lat 1.1
39     create table t_evcoll_file
40     (id integer not null,
41     evcoll integer not null,
42 lat 1.5 fileid integer not null);
43 lat 1.1
44     -- ======================================================================
45 lat 1.3 alter table t_block_status
46     add constraint pk_block_status
47     primary key (id)
48 afaq 1.9 using index tablespace INDX01;
49 lat 1.3
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 afaq 1.9 using index tablespace INDX01;
59 lat 1.3
60     alter table t_block
61     add constraint fk_block_status
62     foreign key (status) references t_block_status (id);
63    
64 afaq 1.9 alter table t_block
65     add constraint fk_processed_dataset
66     foreign key (processed_dataset) references t_processed_dataset (id);
67    
68 lat 1.3 --
69 lat 1.1 alter table t_file_status
70     add constraint pk_file_status
71     primary key (id)
72 afaq 1.9 using index tablespace INDX01;
73 lat 1.1
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 afaq 1.9 using index tablespace INDX01;
83 lat 1.1
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 afaq 1.9 using index tablespace INDX01;
93 lat 1.1
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 lat 1.3 add constraint fk_file_inblock
108 sekhri 1.10 foreign key (inblock) references t_block (id);
109 lat 1.3
110 lat 1.1 --
111     alter table t_evcoll_file
112     add constraint pk_evcoll_file
113     primary key (id)
114 afaq 1.9 using index tablespace INDX01;
115 lat 1.1
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 lat 1.3 /* on update cascade */ on delete cascade;
124 lat 1.1
125     alter table t_evcoll_file
126     add constraint fk_evcoll_file_fileid
127     foreign key (fileid) references t_file (id);
128    
129     -- ======================================================================
130 lat 1.3 create index ix_block_status
131     on t_block (status)
132 afaq 1.9 tablespace INDX01;
133 lat 1.3
134 lat 1.1 --
135     create index ix_file_status
136     on t_file (status)
137 afaq 1.9 tablespace INDX01;
138 lat 1.1
139     create index ix_file_type
140     on t_file (type)
141 afaq 1.9 tablespace INDX01;
142 lat 1.1
143 lat 1.3 create index ix_file_inblock
144     on t_file (inblock)
145 afaq 1.9 tablespace INDX01;