Schema/RefDBLegacy/OracleFiles.sql

-- ======================================================================
create sequence seq_block_status;
create sequence seq_block;
create sequence seq_file_status;
create sequence seq_file_type;
create sequence seq_file;
create sequence seq_evcoll_file;

-- ======================================================================
create table t_block_status
  (id                           integer         not null,
   name                         varchar (1000)  not null);

create table t_block
  (id                           integer         not null,
   processed_dataset            integer         not null,
   status                       integer         not null,
   files                        integer         not null,
   bytes                        integer         not null);

create table t_file_status
  (id                           integer         not null,
   name                         varchar (1000)  not null);

create table t_file_type
  (id                           integer         not null,
   name                         varchar (1000)  not null);

create table t_file
  (id                           integer         not null,
   guid                         varchar (1000)  /* not null */,
   logical_name                 varchar (1000)  not null,
   checksum                     varchar (1000)  /* not null */,
   filesize                     integer         /* not null */,
   status                       integer         /* not null */,
   type                         integer         not null,
   inblock                      integer         not null);

create table t_evcoll_file
  (id                           integer         not null,
   evcoll                       integer         not null,
   fileid                       integer         not null);

-- ======================================================================
alter table t_block_status
  add constraint pk_block_status
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_block_status
  add constraint uq_block_status_key
  unique (name);

--
alter table t_block
  add constraint pk_block
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_block
  add constraint fk_block_status
  foreign key (status) references t_block_status (id);

--
alter table t_file_status
  add constraint pk_file_status
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_file_status
  add constraint uq_file_status_key
  unique (name);

--
alter table t_file_type
  add constraint pk_file_type
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_file_type
  add constraint uq_file_type
  unique (name);

--
alter table t_file
  add constraint pk_file
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_file
  add constraint uq_file_lfn
  unique (logical_name);

alter table t_file
  add constraint fk_file_status
  foreign key (status) references t_file_status (id);

alter table t_file
  add constraint fk_file_type
  foreign key (type) references t_file_type (id);

alter table t_file
  add constraint fk_file_inblock
  foreign key (status) references t_block (id);

--
alter table t_evcoll_file
  add constraint pk_evcoll_file
  primary key (id)
  using index tablespace CMS_DBS_INDX01;

alter table t_evcoll_file
  add constraint uq_evcoll_file_key
  unique (evcoll, fileid);

alter table t_evcoll_file
  add constraint fk_evcoll_file_evcoll
  foreign key (evcoll) references t_event_collection (id)
  /* on update cascade */ on delete cascade;

alter table t_evcoll_file
  add constraint fk_evcoll_file_fileid
  foreign key (fileid) references t_file (id);

-- ======================================================================
create index ix_block_status
  on t_block (status)
  tablespace CMS_DBS_INDX01;

--
create index ix_file_status
  on t_file (status)
  tablespace CMS_DBS_INDX01;

create index ix_file_type
  on t_file (type)
  tablespace CMS_DBS_INDX01;

create index ix_file_inblock
  on t_file (inblock)
  tablespace CMS_DBS_INDX01;
Revision:	1.8
Committed:	Fri Jan 20 16:55:06 2006 UTC (19 years, 3 months ago) by lat
Content type:	application/sql
Branch:	MAIN
CVS Tags:	BeforeJan2006SchemaChanges
Changes since 1.7:	+10 -10 lines
Log Message:	Default to production tablespace names.
#	User	Rev	Content
1	lat	1.1	-- ======================================================================
2	lat	1.4	create sequence seq_block_status;
3	lat	1.3	create sequence seq_block;
4	lat	1.1	create sequence seq_file_status;
5			create sequence seq_file_type;
6			create sequence seq_file;
7			create sequence seq_evcoll_file;
8
9			-- ======================================================================
10	lat	1.3	create table t_block_status
11			(id integer not null,
12	lat	1.5	name varchar (1000) not null);
13	lat	1.3
14			create table t_block
15			(id integer not null,
16			processed_dataset integer not null,
17			status integer not null,
18			files integer not null,
19	lat	1.5	bytes integer not null);
20	lat	1.3
21	lat	1.1	create table t_file_status
22			(id integer not null,
23	lat	1.5	name varchar (1000) not null);
24	lat	1.1
25			create table t_file_type
26			(id integer not null,
27	lat	1.5	name varchar (1000) not null);
28	lat	1.1
29			create table t_file
30			(id integer not null,
31	lat	1.3	guid varchar (1000) /* not null */,
32	lat	1.2	logical_name varchar (1000) not null,
33	lat	1.6	checksum varchar (1000) /* not null */,
34			filesize integer /* not null */,
35	lat	1.5	status integer /* not null */,
36	lat	1.1	type integer not null,
37	lat	1.5	inblock integer not null);
38	lat	1.1
39			create table t_evcoll_file
40			(id integer not null,
41			evcoll integer not null,
42	lat	1.5	fileid integer not null);
43	lat	1.1
44			-- ======================================================================
45	lat	1.3	alter table t_block_status
46			add constraint pk_block_status
47			primary key (id)
48	lat	1.8	using index tablespace CMS_DBS_INDX01;
49	lat	1.3
50			alter table t_block_status
51			add constraint uq_block_status_key
52			unique (name);
53
54			--
55			alter table t_block
56			add constraint pk_block
57			primary key (id)
58	lat	1.8	using index tablespace CMS_DBS_INDX01;
59	lat	1.3
60			alter table t_block
61			add constraint fk_block_status
62			foreign key (status) references t_block_status (id);
63
64			--
65	lat	1.1	alter table t_file_status
66			add constraint pk_file_status
67			primary key (id)
68	lat	1.8	using index tablespace CMS_DBS_INDX01;
69	lat	1.1
70			alter table t_file_status
71			add constraint uq_file_status_key
72			unique (name);
73
74			--
75			alter table t_file_type
76			add constraint pk_file_type
77			primary key (id)
78	lat	1.8	using index tablespace CMS_DBS_INDX01;
79	lat	1.1
80			alter table t_file_type
81			add constraint uq_file_type
82			unique (name);
83
84			--
85			alter table t_file
86			add constraint pk_file
87			primary key (id)
88	lat	1.8	using index tablespace CMS_DBS_INDX01;
89	lat	1.1
90			alter table t_file
91			add constraint uq_file_lfn
92			unique (logical_name);
93
94			alter table t_file
95			add constraint fk_file_status
96			foreign key (status) references t_file_status (id);
97
98			alter table t_file
99			add constraint fk_file_type
100			foreign key (type) references t_file_type (id);
101
102			alter table t_file
103	lat	1.3	add constraint fk_file_inblock
104			foreign key (status) references t_block (id);
105
106	lat	1.1	--
107			alter table t_evcoll_file
108			add constraint pk_evcoll_file
109			primary key (id)
110	lat	1.8	using index tablespace CMS_DBS_INDX01;
111	lat	1.1
112			alter table t_evcoll_file
113			add constraint uq_evcoll_file_key
114			unique (evcoll, fileid);
115
116			alter table t_evcoll_file
117			add constraint fk_evcoll_file_evcoll
118			foreign key (evcoll) references t_event_collection (id)
119	lat	1.3	/* on update cascade */ on delete cascade;
120	lat	1.1
121			alter table t_evcoll_file
122			add constraint fk_evcoll_file_fileid
123			foreign key (fileid) references t_file (id);
124
125			-- ======================================================================
126	lat	1.3	create index ix_block_status
127			on t_block (status)
128	lat	1.8	tablespace CMS_DBS_INDX01;
129	lat	1.3
130	lat	1.1	--
131			create index ix_file_status
132			on t_file (status)
133	lat	1.8	tablespace CMS_DBS_INDX01;
134	lat	1.1
135			create index ix_file_type
136			on t_file (type)
137	lat	1.8	tablespace CMS_DBS_INDX01;
138	lat	1.1
139	lat	1.3	create index ix_file_inblock
140			on t_file (inblock)
141	lat	1.8	tablespace CMS_DBS_INDX01;