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 (80)    not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float           not null,
   modified_by                  integer         not null);

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

create table t_application
  (id                           integer         not null,
   executable                   varchar (80)    not null,
   app_version                  varchar (80)    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           not null,
   modified_by                  integer         not null);

create table t_app_config
  (id                           integer         not null,
   application                  integer         not null,
   conditions_version           varchar (80)    not null,
   created_at                   float           not null,
   created_by                   integer         not null,
   modified_at                  float           not null,
   modified_by                  integer         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_collection_type
  add constraint fk_collection_type_creat
  foreign key (created_by) references t_person (id);

alter table t_collection_type
  add constraint fk_collection_type_mod
  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_creat
  foreign key (created_by) references t_person (id);

alter table t_app_family
  add constraint fk_app_family_mod
  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_creat
  foreign key (created_by) references t_person (id);

alter table t_application
  add constraint fk_application_mod
  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, 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_creat
  foreign key (created_by) references t_person (id);

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

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

create index ix_collection_type_mod
  on t_collection_type (modified_by)
  tablespace CMS_DBS_INDX01;

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

create index ix_app_family_mod
  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_creat
  on t_application (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_application_mod
  on t_application (modified_by)
  tablespace CMS_DBS_INDX01;

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

create index ix_app_config_mod
  on t_app_config (modified_by)
  tablespace CMS_DBS_INDX01;
Revision:	1.1
Committed:	Tue Oct 4 21:46:23 2005 UTC (19 years, 7 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Log Message:	Schema converted to Oracle, various other changes
#	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			name varchar (80) not null,
11			created_at float not null,
12			created_by integer not null,
13			modified_at float not null,
14			modified_by integer not null);
15
16			create table t_app_family
17			(id integer not null,
18			name varchar (80) not null,
19			created_at float not null,
20			created_by integer not null,
21			modified_at float not null,
22			modified_by integer not null);
23
24			create table t_application
25			(id integer not null,
26			executable varchar (80) not null,
27			app_version varchar (80) 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 not null,
34			modified_by integer not null);
35
36			create table t_app_config
37			(id integer not null,
38			application integer not null,
39			conditions_version varchar (80) not null,
40			created_at float not null,
41			created_by integer not null,
42			modified_at float not null,
43			modified_by integer not null);
44
45			-- ======================================================================
46			alter table t_collection_type
47			add constraint pk_collection_type
48			primary key (id)
49			using index tablespace CMS_DBS_INDX01;
50
51			alter table t_collection_type
52			add constraint uq_collection_type_name
53			unique (name);
54
55			alter table t_collection_type
56			add constraint fk_collection_type_creat
57			foreign key (created_by) references t_person (id);
58
59			alter table t_collection_type
60			add constraint fk_collection_type_mod
61			foreign key (modified_by) references t_person (id);
62
63			--
64			alter table t_app_family
65			add constraint pk_app_family
66			primary key (id)
67			using index tablespace CMS_DBS_INDX01;
68
69			alter table t_app_family
70			add constraint uq_app_family_name
71			unique (name);
72
73			alter table t_app_family
74			add constraint fk_app_family_creat
75			foreign key (created_by) references t_person (id);
76
77			alter table t_app_family
78			add constraint fk_app_family_mod
79			foreign key (modified_by) references t_person (id);
80
81			--
82			alter table t_application
83			add constraint pk_application
84			primary key (id)
85			using index tablespace CMS_DBS_INDX01;
86
87			alter table t_application
88			add constraint uq_application_key
89			unique (executable, app_version, app_family);
90
91			alter table t_application
92			add constraint fk_app_family
93			foreign key (app_family) references t_app_family (id);
94
95			alter table t_application
96			add constraint fk_application_intype
97			foreign key (input_type) references t_collection_type (id);
98
99			alter table t_application
100			add constraint fk_application_outtype
101			foreign key (output_type) references t_collection_type (id);
102
103			alter table t_application
104			add constraint fk_application_creat
105			foreign key (created_by) references t_person (id);
106
107			alter table t_application
108			add constraint fk_application_mod
109			foreign key (modified_by) references t_person (id);
110
111			--
112			alter table t_app_config
113			add constraint pk_app_config
114			primary key (id)
115			using index tablespace CMS_DBS_INDX01;
116
117			alter table t_app_config
118			add constraint uq_app_config
119			unique (application, conditions_version);
120
121			alter table t_app_config
122			add constraint fk_app_config_app
123			foreign key (application) references t_application (id);
124
125			alter table t_app_config
126			add constraint fk_app_config_creat
127			foreign key (created_by) references t_person (id);
128
129			alter table t_app_config
130			add constraint fk_app_config_mod
131			foreign key (modified_by) references t_person (id);
132
133			-- ======================================================================
134			create index ix_collection_type_creat
135			on t_collection_type (created_by)
136			tablespace CMS_DBS_INDX01;
137
138			create index ix_collection_type_mod
139			on t_collection_type (modified_by)
140			tablespace CMS_DBS_INDX01;
141
142			--
143			create index ix_app_family_creat
144			on t_app_family (created_by)
145			tablespace CMS_DBS_INDX01;
146
147			create index ix_app_family_mod
148			on t_app_family (modified_by)
149			tablespace CMS_DBS_INDX01;
150
151			--
152			create index ix_application_intype
153			on t_application (input_type)
154			tablespace CMS_DBS_INDX01;
155
156			create index ix_application_outtype
157			on t_application (output_type)
158			tablespace CMS_DBS_INDX01;
159
160			create index ix_application_creat
161			on t_application (created_by)
162			tablespace CMS_DBS_INDX01;
163
164			create index ix_application_mod
165			on t_application (modified_by)
166			tablespace CMS_DBS_INDX01;
167
168			--
169			create index ix_app_config_creat
170			on t_app_config (created_by)
171			tablespace CMS_DBS_INDX01;
172
173			create index ix_app_config_mod
174			on t_app_config (modified_by)
175			tablespace CMS_DBS_INDX01;