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.
#	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.7	name varchar (1000) not null);
11	lat	1.1
12			create table t_app_family
13			(id integer not null,
14	lat	1.7	name varchar (1000) not null);
15	lat	1.1
16			create table t_application
17			(id integer not null,
18	lat	1.3	executable varchar (1000) not null,
19			app_version varchar (1000) not null,
20	lat	1.1	app_family integer not null,
21	lat	1.7	input_type integer /* not null */,
22			output_type integer /* not null */);
23	lat	1.1
24			create table t_app_config
25			(id integer not null,
26			application integer not null,
27	lat	1.6	parameter_set varchar (1000) not null, -- FIXME!
28	lat	1.7	conditions_version varchar (1000) /* not null */);
29	lat	1.1
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	lat	1.6	unique (application, parameter_set, conditions_version);
81	lat	1.1
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;