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.6
Committed:	Thu Oct 27 16:57:38 2005 UTC (19 years, 6 months ago) by lat
Content type:	application/sql
Branch:	MAIN
CVS Tags:	before_message_removal
Changes since 1.5:	+2 -2 lines
Log Message:	Make file size, checksum optional
#	Content
1	-- ======================================================================
2	create sequence seq_block_status;
3	create sequence seq_block;
4	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	create table t_block_status
11	(id integer not null,
12	name varchar (1000) not null);
13
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	bytes integer not null);
20
21	create table t_file_status
22	(id integer not null,
23	name varchar (1000) not null);
24
25	create table t_file_type
26	(id integer not null,
27	name varchar (1000) not null);
28
29	create table t_file
30	(id integer not null,
31	guid varchar (1000) /* not null */,
32	logical_name varchar (1000) not null,
33	checksum varchar (1000) /* not null */,
34	filesize integer /* not null */,
35	status integer /* not null */,
36	type integer not null,
37	inblock integer not null);
38
39	create table t_evcoll_file
40	(id integer not null,
41	evcoll integer not null,
42	fileid integer not null);
43
44	-- ======================================================================
45	alter table t_block_status
46	add constraint pk_block_status
47	primary key (id)
48	using index tablespace CMS_DBS_INDX01;
49
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	using index tablespace CMS_DBS_INDX01;
59
60	alter table t_block
61	add constraint fk_block_status
62	foreign key (status) references t_block_status (id);
63
64	--
65	alter table t_file_status
66	add constraint pk_file_status
67	primary key (id)
68	using index tablespace CMS_DBS_INDX01;
69
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	using index tablespace CMS_DBS_INDX01;
79
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	using index tablespace CMS_DBS_INDX01;
89
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	add constraint fk_file_inblock
104	foreign key (status) references t_block (id);
105
106	--
107	alter table t_evcoll_file
108	add constraint pk_evcoll_file
109	primary key (id)
110	using index tablespace CMS_DBS_INDX01;
111
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	/* on update cascade */ on delete cascade;
120
121	alter table t_evcoll_file
122	add constraint fk_evcoll_file_fileid
123	foreign key (fileid) references t_file (id);
124
125	-- ======================================================================
126	create index ix_block_status
127	on t_block (status)
128	tablespace CMS_DBS_INDX01;
129
130	--
131	create index ix_file_status
132	on t_file (status)
133	tablespace CMS_DBS_INDX01;
134
135	create index ix_file_type
136	on t_file (type)
137	tablespace CMS_DBS_INDX01;
138
139	create index ix_file_inblock
140	on t_file (inblock)
141	tablespace CMS_DBS_INDX01;