ViewVC Help
View File | Revision Log | Show Annotations | Root Listing
root/cvsroot/COMP/DBS/Schema/RefDBLegacy/OracleApps.sql
Revision: 1.6
Committed: Thu Oct 13 07:08:23 2005 UTC (19 years, 6 months ago) by lat
Content type: application/sql
Branch: MAIN
Changes since 1.5: +2 -1 lines
Log Message: 
Move run-related stuff to separate file.
Move parameter set on app_config, make it a string for now,
and make it part of the key on app_config.

File Contents

# Content
1 -- ======================================================================
2 create sequence seq_collection_type;
3 create sequence seq_app_family;
4 create sequence seq_application;
5 create sequence seq_app_config;
6
7 -- ======================================================================
8 create table t_collection_type
9 (id integer not null,
10 name varchar (1000) not null,
11 created_at float not null,
12 created_by integer not null,
13 modified_at float,
14 modified_by integer);
15
16 create table t_app_family
17 (id integer not null,
18 name varchar (1000) not null,
19 created_at float not null,
20 created_by integer not null,
21 modified_at float,
22 modified_by integer);
23
24 create table t_application
25 (id integer not null,
26 executable varchar (1000) not null,
27 app_version varchar (1000) not null,
28 app_family integer not null,
29 input_type integer not null,
30 output_type integer not null,
31 created_at float not null,
32 created_by integer not null,
33 modified_at float,
34 modified_by integer);
35
36 create table t_app_config
37 (id integer not null,
38 application integer not null,
39 parameter_set varchar (1000) not null, -- FIXME!
40 conditions_version varchar (1000) not null,
41 created_at float not null,
42 created_by integer not null,
43 modified_at float,
44 modified_by integer);
45
46 -- ======================================================================
47 alter table t_collection_type
48 add constraint pk_collection_type
49 primary key (id)
50 using index tablespace CMS_DBS_INDX01;
51
52 alter table t_collection_type
53 add constraint uq_collection_type_name
54 unique (name);
55
56 alter table t_collection_type
57 add constraint fk_collection_type_creatby
58 foreign key (created_by) references t_person (id);
59
60 alter table t_collection_type
61 add constraint fk_collection_type_modifby
62 foreign key (modified_by) references t_person (id);
63
64 --
65 alter table t_app_family
66 add constraint pk_app_family
67 primary key (id)
68 using index tablespace CMS_DBS_INDX01;
69
70 alter table t_app_family
71 add constraint uq_app_family_name
72 unique (name);
73
74 alter table t_app_family
75 add constraint fk_app_family_creatby
76 foreign key (created_by) references t_person (id);
77
78 alter table t_app_family
79 add constraint fk_app_family_modifby
80 foreign key (modified_by) references t_person (id);
81
82 --
83 alter table t_application
84 add constraint pk_application
85 primary key (id)
86 using index tablespace CMS_DBS_INDX01;
87
88 alter table t_application
89 add constraint uq_application_key
90 unique (executable, app_version, app_family);
91
92 alter table t_application
93 add constraint fk_app_family
94 foreign key (app_family) references t_app_family (id);
95
96 alter table t_application
97 add constraint fk_application_intype
98 foreign key (input_type) references t_collection_type (id);
99
100 alter table t_application
101 add constraint fk_application_outtype
102 foreign key (output_type) references t_collection_type (id);
103
104 alter table t_application
105 add constraint fk_application_creatby
106 foreign key (created_by) references t_person (id);
107
108 alter table t_application
109 add constraint fk_application_modifby
110 foreign key (modified_by) references t_person (id);
111
112 --
113 alter table t_app_config
114 add constraint pk_app_config
115 primary key (id)
116 using index tablespace CMS_DBS_INDX01;
117
118 alter table t_app_config
119 add constraint uq_app_config
120 unique (application, parameter_set, conditions_version);
121
122 alter table t_app_config
123 add constraint fk_app_config_app
124 foreign key (application) references t_application (id);
125
126 alter table t_app_config
127 add constraint fk_app_config_creatby
128 foreign key (created_by) references t_person (id);
129
130 alter table t_app_config
131 add constraint fk_app_config_modifby
132 foreign key (modified_by) references t_person (id);
133
134 -- ======================================================================
135 create index ix_collection_type_creatby
136 on t_collection_type (created_by)
137 tablespace CMS_DBS_INDX01;
138
139 create index ix_collection_type_modifby
140 on t_collection_type (modified_by)
141 tablespace CMS_DBS_INDX01;
142
143 --
144 create index ix_app_family_creatby
145 on t_app_family (created_by)
146 tablespace CMS_DBS_INDX01;
147
148 create index ix_app_family_modifby
149 on t_app_family (modified_by)
150 tablespace CMS_DBS_INDX01;
151
152 --
153 create index ix_application_intype
154 on t_application (input_type)
155 tablespace CMS_DBS_INDX01;
156
157 create index ix_application_outtype
158 on t_application (output_type)
159 tablespace CMS_DBS_INDX01;
160
161 create index ix_application_creatby
162 on t_application (created_by)
163 tablespace CMS_DBS_INDX01;
164
165 create index ix_application_modifby
166 on t_application (modified_by)
167 tablespace CMS_DBS_INDX01;
168
169 --
170 create index ix_app_config_creatby
171 on t_app_config (created_by)
172 tablespace CMS_DBS_INDX01;
173
174 create index ix_app_config_modifby
175 on t_app_config (modified_by)
176 tablespace CMS_DBS_INDX01;