Schema/RefDBLegacy/OracleCMS.sql

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

-- ======================================================================
create table t_person
  (id                           integer         not null,
   name                         varchar (80)    not null,
   distinguished_name           varchar (255)   not null,
   contact_info                 varchar (255)   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 (80)    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_creat
  foreign key (created_by) references t_person (id);

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

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

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

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

create index ix_physicsgroup_mod
  on t_physics_group (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_person;
3			create sequence seq_physics_group;
4
5			-- ======================================================================
6			create table t_person
7			(id integer not null,
8			name varchar (80) not null,
9			distinguished_name varchar (255) not null,
10			contact_info varchar (255) 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 (80) 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_creat
41			foreign key (created_by) references t_person (id);
42
43			alter table t_person
44			add constraint fk_person_mod
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_creat
63			foreign key (created_by) references t_person (id);
64
65			alter table t_physics_group
66			add constraint fk_physicsgroup_mod
67			foreign key (modified_by) references t_person (id);
68
69			-- ======================================================================
70			create index ix_person_creat
71			on t_person (created_by)
72			tablespace CMS_DBS_INDX01;
73
74			create index ix_person_mod
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_creat
84			on t_physics_group (created_by)
85			tablespace CMS_DBS_INDX01;
86
87			create index ix_physicsgroup_mod
88			on t_physics_group (modified_by)
89			tablespace CMS_DBS_INDX01;