Schema/RefDBLegacy/OracleApps.sql

-- ======================================================================
create sequence seq_collection_type;
create sequence seq_app_family;
create sequence seq_application;
create sequence seq_app_config;

-- ======================================================================
create table t_collection_type
  (id                           integer         not null,
   name                         varchar (1000)  not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_app_family
  (id                           integer         not null,
   name                         varchar (1000)  not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_application
  (id                           integer         not null,
   executable                   varchar (1000)  not null,
   app_version                  varchar (1000)  not null,
   app_family                   integer         not null,
   input_type                   integer         not null,
   output_type                  integer         not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

create table t_app_config
  (id                           integer         not null,
   application                  integer         not null,
   parameter_set                varchar (1000)  not null,  -- FIXME!
   conditions_version           varchar (1000)  not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float,
   modified_by                  integer);

-- ======================================================================
alter table t_collection_type
  add constraint pk_collection_type
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_collection_type
  add constraint uq_collection_type_name
  unique (name);

alter table t_collection_type
  add constraint fk_collection_type_creatby
  foreign key (created_by) references t_person (id);

alter table t_collection_type
  add constraint fk_collection_type_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_app_family
  add constraint pk_app_family
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_app_family
  add constraint uq_app_family_name
  unique (name);

alter table t_app_family
  add constraint fk_app_family_creatby
  foreign key (created_by) references t_person (id);

alter table t_app_family
  add constraint fk_app_family_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_application
  add constraint pk_application
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_application
  add constraint uq_application_key
  unique (executable, app_version, app_family);

alter table t_application
  add constraint fk_app_family
  foreign key (app_family) references t_app_family (id);

alter table t_application
  add constraint fk_application_intype
  foreign key (input_type) references t_collection_type (id);

alter table t_application
  add constraint fk_application_outtype
  foreign key (output_type) references t_collection_type (id);

alter table t_application
  add constraint fk_application_creatby
  foreign key (created_by) references t_person (id);

alter table t_application
  add constraint fk_application_modifby
  foreign key (modified_by) references t_person (id);

--
alter table t_app_config
  add constraint pk_app_config
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_app_config
  add constraint uq_app_config
  unique (application, parameter_set, conditions_version);

alter table t_app_config
  add constraint fk_app_config_app
  foreign key (application) references t_application (id);

alter table t_app_config
  add constraint fk_app_config_creatby
  foreign key (created_by) references t_person (id);

alter table t_app_config
  add constraint fk_app_config_modifby
  foreign key (modified_by) references t_person (id);

-- ======================================================================
create index ix_collection_type_creatby
  on t_collection_type (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_collection_type_modifby
  on t_collection_type (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_app_family_creatby
  on t_app_family (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_app_family_modifby
  on t_app_family (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_application_intype
  on t_application (input_type)
  tablespace CMS_DBS_INDX01;

create index ix_application_outtype
  on t_application (output_type)
  tablespace CMS_DBS_INDX01;

create index ix_application_creatby
  on t_application (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_application_modifby
  on t_application (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_app_config_creatby
  on t_app_config (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_app_config_modifby
  on t_app_config (modified_by)
  tablespace CMS_DBS_INDX01;
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.
#	User	Rev	Content
1	lat	1.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	lat	1.3	name varchar (1000) not null,
11	lat	1.1	created_at float not null,
12			created_by integer not null,
13	lat	1.5	modified_at float,
14			modified_by integer);
15	lat	1.1
16			create table t_app_family
17			(id integer not null,
18	lat	1.3	name varchar (1000) not null,
19	lat	1.1	created_at float not null,
20			created_by integer not null,
21	lat	1.5	modified_at float,
22			modified_by integer);
23	lat	1.1
24			create table t_application
25			(id integer not null,
26	lat	1.3	executable varchar (1000) not null,
27			app_version varchar (1000) not null,
28	lat	1.1	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	lat	1.5	modified_at float,
34			modified_by integer);
35	lat	1.1
36			create table t_app_config
37			(id integer not null,
38			application integer not null,
39	lat	1.6	parameter_set varchar (1000) not null, -- FIXME!
40	lat	1.3	conditions_version varchar (1000) not null,
41	lat	1.1	created_at float not null,
42			created_by integer not null,
43	lat	1.5	modified_at float,
44			modified_by integer);
45	lat	1.1
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	lat	1.4	add constraint fk_collection_type_creatby
58	lat	1.1	foreign key (created_by) references t_person (id);
59
60			alter table t_collection_type
61	lat	1.4	add constraint fk_collection_type_modifby
62	lat	1.1	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	lat	1.4	add constraint fk_app_family_creatby
76	lat	1.1	foreign key (created_by) references t_person (id);
77
78			alter table t_app_family
79	lat	1.4	add constraint fk_app_family_modifby
80	lat	1.1	foreign key (modified_by) references t_person (id);
81	lat	1.2
82	lat	1.1	--
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	lat	1.4	add constraint fk_application_creatby
106	lat	1.1	foreign key (created_by) references t_person (id);
107
108			alter table t_application
109	lat	1.4	add constraint fk_application_modifby
110	lat	1.1	foreign key (modified_by) references t_person (id);
111	lat	1.2
112	lat	1.1	--
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	lat	1.6	unique (application, parameter_set, conditions_version);
121	lat	1.1
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	lat	1.4	add constraint fk_app_config_creatby
128	lat	1.1	foreign key (created_by) references t_person (id);
129
130			alter table t_app_config
131	lat	1.4	add constraint fk_app_config_modifby
132	lat	1.1	foreign key (modified_by) references t_person (id);
133
134			-- ======================================================================
135	lat	1.4	create index ix_collection_type_creatby
136	lat	1.1	on t_collection_type (created_by)
137			tablespace CMS_DBS_INDX01;
138
139	lat	1.4	create index ix_collection_type_modifby
140	lat	1.1	on t_collection_type (modified_by)
141			tablespace CMS_DBS_INDX01;
142
143			--
144	lat	1.4	create index ix_app_family_creatby
145	lat	1.1	on t_app_family (created_by)
146			tablespace CMS_DBS_INDX01;
147
148	lat	1.4	create index ix_app_family_modifby
149	lat	1.1	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	lat	1.4	create index ix_application_creatby
162	lat	1.1	on t_application (created_by)
163			tablespace CMS_DBS_INDX01;
164
165	lat	1.4	create index ix_application_modifby
166	lat	1.1	on t_application (modified_by)
167			tablespace CMS_DBS_INDX01;
168
169			--
170	lat	1.4	create index ix_app_config_creatby
171	lat	1.1	on t_app_config (created_by)
172			tablespace CMS_DBS_INDX01;
173
174	lat	1.4	create index ix_app_config_modifby
175	lat	1.1	on t_app_config (modified_by)
176			tablespace CMS_DBS_INDX01;