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.
#	User	Rev	Content
1	lat	1.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	lat	1.2	name varchar (1000) not null,
9			distinguished_name varchar (1000) not null,
10			contact_info 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_physics_group
17			(id integer not null,
18	lat	1.2	name varchar (1000) not null,
19	lat	1.1	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	lat	1.3	add constraint fk_person_creatby
41	lat	1.1	foreign key (created_by) references t_person (id);
42
43			alter table t_person
44	lat	1.3	add constraint fk_person_modifby
45	lat	1.1	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	lat	1.3	add constraint fk_physicsgroup_creatby
63	lat	1.1	foreign key (created_by) references t_person (id);
64
65			alter table t_physics_group
66	lat	1.3	add constraint fk_physicsgroup_modifby
67	lat	1.1	foreign key (modified_by) references t_person (id);
68
69			-- ======================================================================
70	lat	1.3	create index ix_person_creatby
71	lat	1.1	on t_person (created_by)
72			tablespace CMS_DBS_INDX01;
73
74	lat	1.3	create index ix_person_modifby
75	lat	1.1	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	lat	1.3	create index ix_physicsgroup_creatby
84	lat	1.1	on t_physics_group (created_by)
85			tablespace CMS_DBS_INDX01;
86
87	lat	1.3	create index ix_physicsgroup_modifby
88	lat	1.1	on t_physics_group (modified_by)
89			tablespace CMS_DBS_INDX01;