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           not null,
   modified_by                  integer         not null);

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           not null,
   modified_by                  integer         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,
   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 (1000)  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_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, 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.4
Committed:	Mon Oct 10 20:19:48 2005 UTC (19 years, 6 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Changes since 1.3:	+16 -16 lines
Log Message:	First schema for import tests. Reduce constraint names a bit less, more readable. Add blocks and block status. Add 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.3	name varchar (1000) not null,
11	lat	1.1	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	lat	1.3	name varchar (1000) not null,
19	lat	1.1	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	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			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	lat	1.3	conditions_version varchar (1000) not null,
40	lat	1.1	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	lat	1.4	add constraint fk_collection_type_creatby
57	lat	1.1	foreign key (created_by) references t_person (id);
58
59			alter table t_collection_type
60	lat	1.4	add constraint fk_collection_type_modifby
61	lat	1.1	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	lat	1.4	add constraint fk_app_family_creatby
75	lat	1.1	foreign key (created_by) references t_person (id);
76
77			alter table t_app_family
78	lat	1.4	add constraint fk_app_family_modifby
79	lat	1.1	foreign key (modified_by) references t_person (id);
80	lat	1.2
81	lat	1.1	--
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	lat	1.4	add constraint fk_application_creatby
105	lat	1.1	foreign key (created_by) references t_person (id);
106
107			alter table t_application
108	lat	1.4	add constraint fk_application_modifby
109	lat	1.1	foreign key (modified_by) references t_person (id);
110	lat	1.2
111	lat	1.1	--
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	lat	1.4	add constraint fk_app_config_creatby
127	lat	1.1	foreign key (created_by) references t_person (id);
128
129			alter table t_app_config
130	lat	1.4	add constraint fk_app_config_modifby
131	lat	1.1	foreign key (modified_by) references t_person (id);
132
133			-- ======================================================================
134	lat	1.4	create index ix_collection_type_creatby
135	lat	1.1	on t_collection_type (created_by)
136			tablespace CMS_DBS_INDX01;
137
138	lat	1.4	create index ix_collection_type_modifby
139	lat	1.1	on t_collection_type (modified_by)
140			tablespace CMS_DBS_INDX01;
141
142			--
143	lat	1.4	create index ix_app_family_creatby
144	lat	1.1	on t_app_family (created_by)
145			tablespace CMS_DBS_INDX01;
146
147	lat	1.4	create index ix_app_family_modifby
148	lat	1.1	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	lat	1.4	create index ix_application_creatby
161	lat	1.1	on t_application (created_by)
162			tablespace CMS_DBS_INDX01;
163
164	lat	1.4	create index ix_application_modifby
165	lat	1.1	on t_application (modified_by)
166			tablespace CMS_DBS_INDX01;
167
168			--
169	lat	1.4	create index ix_app_config_creatby
170	lat	1.1	on t_app_config (created_by)
171			tablespace CMS_DBS_INDX01;
172
173	lat	1.4	create index ix_app_config_modifby
174	lat	1.1	on t_app_config (modified_by)
175			tablespace CMS_DBS_INDX01;