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);

create table t_app_family
  (id                           integer         not null,
   name                         varchar (1000)  not null);

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 */);

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 */);

-- ======================================================================
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_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_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_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);

-- ======================================================================
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;
Revision:	1.7
Committed:	Wed Oct 19 12:54:56 2005 UTC (19 years, 6 months ago) by lat
Content type:	application/sql
Branch:	MAIN
CVS Tags:	before_message_removal
Changes since 1.6:	+5 -88 lines
Log Message:	Store object history in separate table. Make not-understood columns nullable, and don't fill them. Add key for event collection parentage type.
#	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
12	create table t_app_family
13	(id integer not null,
14	name varchar (1000) not null);
15
16	create table t_application
17	(id integer not null,
18	executable varchar (1000) not null,
19	app_version varchar (1000) not null,
20	app_family integer not null,
21	input_type integer /* not null */,
22	output_type integer /* not null */);
23
24	create table t_app_config
25	(id integer not null,
26	application integer not null,
27	parameter_set varchar (1000) not null, -- FIXME!
28	conditions_version varchar (1000) /* not null */);
29
30	-- ======================================================================
31	alter table t_collection_type
32	add constraint pk_collection_type
33	primary key (id)
34	using index tablespace CMS_DBS_INDX01;
35
36	alter table t_collection_type
37	add constraint uq_collection_type_name
38	unique (name);
39
40	--
41	alter table t_app_family
42	add constraint pk_app_family
43	primary key (id)
44	using index tablespace CMS_DBS_INDX01;
45
46	alter table t_app_family
47	add constraint uq_app_family_name
48	unique (name);
49
50	--
51	alter table t_application
52	add constraint pk_application
53	primary key (id)
54	using index tablespace CMS_DBS_INDX01;
55
56	alter table t_application
57	add constraint uq_application_key
58	unique (executable, app_version, app_family);
59
60	alter table t_application
61	add constraint fk_app_family
62	foreign key (app_family) references t_app_family (id);
63
64	alter table t_application
65	add constraint fk_application_intype
66	foreign key (input_type) references t_collection_type (id);
67
68	alter table t_application
69	add constraint fk_application_outtype
70	foreign key (output_type) references t_collection_type (id);
71
72	--
73	alter table t_app_config
74	add constraint pk_app_config
75	primary key (id)
76	using index tablespace CMS_DBS_INDX01;
77
78	alter table t_app_config
79	add constraint uq_app_config
80	unique (application, parameter_set, conditions_version);
81
82	alter table t_app_config
83	add constraint fk_app_config_app
84	foreign key (application) references t_application (id);
85
86	-- ======================================================================
87	create index ix_application_intype
88	on t_application (input_type)
89	tablespace CMS_DBS_INDX01;
90
91	create index ix_application_outtype
92	on t_application (output_type)
93	tablespace CMS_DBS_INDX01;