Schema/RefDBLegacy/OracleCMS.sql

-- ======================================================================
create sequence seq_person;
create sequence seq_physics_group;

-- ======================================================================
create table t_person
  (id                           integer         not null,
   name                         varchar (1000)  not null,
   distinguished_name           varchar (1000)  not null,
   contact_info                 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_physics_group
  (id                           integer         not null,
   name                         varchar (1000)  not null,
   convenor                     integer         /* 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_person
  add constraint pk_person
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_person
  add constraint uq_person_name
  unique (name);

alter table t_person
  add constraint uq_person_distinguished
  unique (distinguished_name);

alter table t_person
  add constraint fk_person_creatby
  foreign key (created_by) references t_person (id);

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

--
alter table t_physics_group
  add constraint pk_physicsgroup
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_physics_group
  add constraint uq_physicsgroup_name
  unique (name);

alter table t_physics_group
  add constraint fk_physicsgroup_convener
  foreign key (convenor) references t_person (id);

alter table t_physics_group
  add constraint fk_physicsgroup_creatby
  foreign key (created_by) references t_person (id);

alter table t_physics_group
  add constraint fk_physicsgroup_modifby
  foreign key (modified_by) references t_person (id);

-- ======================================================================
create index ix_person_creatby
  on t_person (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_person_modifby
  on t_person (modified_by)
  tablespace CMS_DBS_INDX01;

--
create index ix_physicsgroup_convener
  on t_physics_group (convenor)
  tablespace CMS_DBS_INDX01;

create index ix_physicsgroup_creatby
  on t_physics_group (created_by)
  tablespace CMS_DBS_INDX01;

create index ix_physicsgroup_modifby
  on t_physics_group (modified_by)
  tablespace CMS_DBS_INDX01;
Revision:	1.3
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.2:	+8 -8 lines
Log Message:	First schema for import tests. Reduce constraint names a bit less, more readable. Add blocks and block status. Add parentage type.
#	Content
1	-- ======================================================================
2	create sequence seq_person;
3	create sequence seq_physics_group;
4
5	-- ======================================================================
6	create table t_person
7	(id integer not null,
8	name varchar (1000) not null,
9	distinguished_name varchar (1000) not null,
10	contact_info 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_physics_group
17	(id integer not null,
18	name varchar (1000) not null,
19	convenor integer /* not null? */,
20	created_at float not null,
21	created_by integer not null,
22	modified_at float not null,
23	modified_by integer not null);
24
25	-- ======================================================================
26	alter table t_person
27	add constraint pk_person
28	primary key (id)
29	using index tablespace CMS_DBS_INDX01;
30
31	alter table t_person
32	add constraint uq_person_name
33	unique (name);
34
35	alter table t_person
36	add constraint uq_person_distinguished
37	unique (distinguished_name);
38
39	alter table t_person
40	add constraint fk_person_creatby
41	foreign key (created_by) references t_person (id);
42
43	alter table t_person
44	add constraint fk_person_modifby
45	foreign key (modified_by) references t_person (id);
46
47	--
48	alter table t_physics_group
49	add constraint pk_physicsgroup
50	primary key (id)
51	using index tablespace CMS_DBS_INDX01;
52
53	alter table t_physics_group
54	add constraint uq_physicsgroup_name
55	unique (name);
56
57	alter table t_physics_group
58	add constraint fk_physicsgroup_convener
59	foreign key (convenor) references t_person (id);
60
61	alter table t_physics_group
62	add constraint fk_physicsgroup_creatby
63	foreign key (created_by) references t_person (id);
64
65	alter table t_physics_group
66	add constraint fk_physicsgroup_modifby
67	foreign key (modified_by) references t_person (id);
68
69	-- ======================================================================
70	create index ix_person_creatby
71	on t_person (created_by)
72	tablespace CMS_DBS_INDX01;
73
74	create index ix_person_modifby
75	on t_person (modified_by)
76	tablespace CMS_DBS_INDX01;
77
78	--
79	create index ix_physicsgroup_convener
80	on t_physics_group (convenor)
81	tablespace CMS_DBS_INDX01;
82
83	create index ix_physicsgroup_creatby
84	on t_physics_group (created_by)
85	tablespace CMS_DBS_INDX01;
86
87	create index ix_physicsgroup_modifby
88	on t_physics_group (modified_by)
89	tablespace CMS_DBS_INDX01;