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_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.3
Committed:	Thu Oct 6 21:41:37 2005 UTC (19 years, 7 months ago) by lat
Content type:	application/sql
Branch:	MAIN
Changes since 1.2:	+5 -5 lines
Log Message:	Change all varchars to length 1000
#	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	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 (1000) 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 (1000) not null,
27	app_version varchar (1000) 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 (1000) 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;