3 # shell script to create Bacula Ingres tables
7 #db_name=${db_name:-@db_name@}
8 #db_user=${db_user:-@db_user@}
12 sql $* -u${db_user} ${db_name} <<END-OF-DATA
16 CREATE SEQUENCE filename_seq;
19 filenameid integer not null default filename_seq.nextval,
20 name varchar(256) not null,
21 primary key (filenameid)
24 -- ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
25 CREATE UNIQUE INDEX filename_name_idx on filename (name);
27 CREATE SEQUENCE path_seq;
30 pathid integer not null default path_seq.nextval,
31 path varchar(256) not null,
35 -- ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
36 CREATE UNIQUE INDEX path_name_idx on path (path);
38 CREATE SEQUENCE file_seq;
41 fileid integer8 not null default file_seq.nextval,
42 fileindex integer not null default 0,
43 jobid integer not null,
44 pathid integer not null,
45 filenameid integer not null,
46 markid integer not null default 0,
47 lstat varchar(256) not null,
48 md5 varchar(256) not null,
52 CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
54 -- If you need performances, you can remove this index
55 -- the database engine is able to use the composite index
56 -- to find all records with a given JobId
57 CREATE INDEX file_jobid_idx on file(jobid);
60 -- Add this if you have a good number of job
61 -- that run at the same time
62 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
65 -- Possibly add one or more of the following indexes
66 -- if your Verifies are too slow.
68 -- CREATE INDEX file_pathid_idx on file(pathid);
69 -- CREATE INDEX file_filenameid_idx on file(filenameid);
71 CREATE SEQUENCE Job_seq;
74 JobId integer not null default Job_seq.nextval,
75 Job varchar(256) not null,
76 Name varchar(256) not null,
77 Type char(1) not null,
78 Level char(1) not null,
79 ClientId integer default 0,
80 JobStatus char(1) not null,
81 SchedTime timestamp without time zone,
82 StartTime timestamp without time zone,
83 EndTime timestamp without time zone,
84 RealEndTime timestamp without time zone,
85 JobTDate bigint default 0,
86 VolSessionId integer default 0,
87 volSessionTime integer default 0,
88 JobFiles integer default 0,
89 JobBytes bigint default 0,
90 ReadBytes bigint default 0,
91 JobErrors integer default 0,
92 JobMissingFiles integer default 0,
93 PoolId integer default 0,
94 FilesetId integer default 0,
95 PriorJobid integer default 0,
96 PurgedFiles smallint default 0,
97 HasBase smallint default 0,
98 HasCache smallint default 0,
99 Reviewed smallint default 0,
100 Comment varchar(256),
104 CREATE INDEX job_name_idx on job (name);
106 -- Create a table like Job for long term statistics
107 CREATE SEQUENCE JobHisto_seq;
108 CREATE TABLE JobHisto
110 JobId integer not null default JobHisto_seq.nextval,
111 Job varchar(256) not null,
112 Name varchar(256) not null,
113 Type char(1) not null,
114 Level char(1) not null,
115 ClientId integer default 0,
116 JobStatus char(1) not null,
117 SchedTime timestamp without time zone,
118 StartTime timestamp without time zone,
119 EndTime timestamp without time zone,
120 RealEndTime timestamp without time zone,
121 JobTDate bigint default 0,
122 VolSessionId integer default 0,
123 volSessionTime integer default 0,
124 JobFiles integer default 0,
125 JobBytes bigint default 0,
126 ReadBytes bigint default 0,
127 JobErrors integer default 0,
128 JobMissingFiles integer default 0,
129 PoolId integer default 0,
130 FilesetId integer default 0,
131 PriorJobid integer default 0,
132 PurgedFiles smallint default 0,
133 HasBase smallint default 0,
134 HasCache smallint default 0,
135 Reviewed smallint default 0,
136 Comment varchar(256),
140 CREATE INDEX jobhisto_idx on JobHisto ( StartTime );
143 CREATE SEQUENCE Location_seq;
144 CREATE TABLE Location (
145 LocationId integer not null default Location_seq.nextval,
146 Location varchar(256) not null,
147 Cost integer default 0,
149 primary key (LocationId)
152 CREATE SEQUENCE fileset_seq;
155 filesetid integer not null default fileset_seq.nextval,
156 fileset varchar(256) not null,
157 md5 varchar(256) not null,
158 createtime timestamp without time zone not null,
159 primary key (filesetid)
162 CREATE INDEX fileset_name_idx on fileset (fileset);
164 CREATE SEQUENCE jobmedia_seq;
165 CREATE TABLE jobmedia
167 jobmediaid integer not null default jobmedia_seq.nextval,
168 jobid integer not null,
169 mediaid integer not null,
170 firstindex integer default 0,
171 lastindex integer default 0,
172 startfile integer default 0,
173 endfile integer default 0,
174 startblock bigint default 0,
175 endblock bigint default 0,
176 volindex integer default 0,
177 primary key (jobmediaid)
180 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
182 CREATE SEQUENCE media_seq;
185 mediaid integer not null default media_seq.nextval,
186 volumename varchar(256) not null,
187 slot integer default 0,
188 poolid integer default 0,
189 mediatype varchar(256) not null,
190 mediatypeid integer default 0,
191 labeltype integer default 0,
192 firstwritten timestamp without time zone,
193 lastwritten timestamp without time zone,
194 labeldate timestamp without time zone,
195 voljobs integer default 0,
196 volfiles integer default 0,
197 volblocks integer default 0,
198 volmounts integer default 0,
199 volbytes bigint default 0,
200 volparts integer default 0,
201 volerrors integer default 0,
202 volwrites integer default 0,
203 volcapacitybytes bigint default 0,
204 volstatus varchar(256) not null
205 check (volstatus in ('Full','Archive','Append',
206 'Recycle','Purged','Read-Only','Disabled',
207 'Error','Busy','Used','Cleaning','Scratch')),
208 enabled smallint default 1,
209 recycle smallint default 0,
210 ActionOnPurge smallint default 0,
211 volretention bigint default 0,
212 voluseduration bigint default 0,
213 maxvoljobs integer default 0,
214 maxvolfiles integer default 0,
215 maxvolbytes bigint default 0,
216 inchanger smallint default 0,
217 StorageId integer default 0,
218 DeviceId integer default 0,
219 mediaaddressing smallint default 0,
220 volreadtime bigint default 0,
221 volwritetime bigint default 0,
222 endfile integer default 0,
223 endblock bigint default 0,
224 LocationId integer default 0,
225 recyclecount integer default 0,
226 initialwrite timestamp without time zone,
227 scratchpoolid integer default 0,
228 recyclepoolid integer default 0,
229 comment varchar(256),
230 primary key (mediaid)
233 create unique index media_volumename_id on media (volumename);
235 CREATE SEQUENCE MediaType_seq;
236 CREATE TABLE MediaType (
237 MediaTypeId INTEGER NOT NULL DEFAULT MediaType_seq.nextval,
238 MediaType varchar(256) NOT NULL,
239 ReadOnly INTEGER NOT NULL DEFAULT 0,
240 PRIMARY KEY(MediaTypeId)
243 CREATE SEQUENCE Storage_seq;
244 CREATE TABLE Storage (
245 StorageId INTEGER NOT NULL DEFAULT Storage_seq.nextval,
246 Name varchar(256) NOT NULL,
247 AutoChanger INTEGER NOT NULL DEFAULT 0,
248 PRIMARY KEY(StorageId)
251 CREATE SEQUENCE Device_seq;
252 CREATE TABLE Device (
253 DeviceId INTEGER NOT NULL DEFAULT Device_seq.nextval,
254 Name varchar(256) NOT NULL,
255 MediaTypeId INTEGER NOT NULL,
256 StorageId INTEGER NOT NULL,
257 DevMounts INTEGER NOT NULL DEFAULT 0,
258 DevReadBytes BIGINT NOT NULL DEFAULT 0,
259 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
260 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
261 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
262 DevReadTime BIGINT NOT NULL DEFAULT 0,
263 DevWriteTime BIGINT NOT NULL DEFAULT 0,
264 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
265 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
266 CleaningDate timestamp without time zone,
267 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
268 PRIMARY KEY(DeviceId)
272 CREATE SEQUENCE pool_seq;
275 poolid integer not null default pool_seq.nextval,
276 name varchar(256) not null,
277 numvols integer default 0,
278 maxvols integer default 0,
279 useonce smallint default 0,
280 usecatalog smallint default 0,
281 acceptanyvolume smallint default 0,
282 volretention bigint default 0,
283 voluseduration bigint default 0,
284 maxvoljobs integer default 0,
285 maxvolfiles integer default 0,
286 maxvolbytes bigint default 0,
287 autoprune smallint default 0,
288 recycle smallint default 0,
289 ActionOnPurge smallint default 0,
290 pooltype varchar(256)
291 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
292 labeltype integer default 0,
293 labelformat varchar(256) not null,
294 enabled smallint default 1,
295 scratchpoolid integer default 0,
296 recyclepoolid integer default 0,
297 NextPoolId integer default 0,
298 MigrationHighBytes BIGINT DEFAULT 0,
299 MigrationLowBytes BIGINT DEFAULT 0,
300 MigrationTime BIGINT DEFAULT 0,
304 CREATE INDEX pool_name_idx on pool (name);
306 CREATE SEQUENCE client_seq;
309 clientid integer not null default client_seq.nextval,
310 name varchar(256) not null,
311 uname varchar(256) not null,
312 autoprune smallint default 0,
313 fileretention bigint default 0,
314 jobretention bigint default 0,
315 primary key (clientid)
318 create unique index client_name_idx on client (name);
320 CREATE SEQUENCE Log_seq;
323 LogId integer not null default Log_seq.nextval,
324 JobId integer not null,
325 Time timestamp without time zone,
326 LogText varchar(256) not null,
329 create index log_name_idx on Log (JobId);
331 CREATE SEQUENCE LocationLog_seq;
332 CREATE TABLE LocationLog (
333 LocLogId INTEGER NOT NULL DEFAULT LocationLog_seq.nextval,
334 Date timestamp without time zone,
335 Comment varchar(256) NOT NULL,
336 MediaId INTEGER DEFAULT 0,
337 LocationId INTEGER DEFAULT 0,
338 newvolstatus varchar(256) not null
339 check (newvolstatus in ('Full','Archive','Append',
340 'Recycle','Purged','Read-Only','Disabled',
341 'Error','Busy','Used','Cleaning','Scratch')),
343 PRIMARY KEY(LocLogId)
348 CREATE TABLE counters
350 counter varchar(256) not null,
351 minvalue integer default 0,
352 maxvalue integer default 0,
353 currentvalue integer default 0,
354 wrapcounter varchar(256) not null,
355 primary key (counter)
359 CREATE SEQUENCE basefiles_seq;
360 CREATE TABLE basefiles
362 baseid integer not null default basefiles_seq.nextval,
363 jobid integer not null,
364 fileid bigint not null,
370 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
372 CREATE TABLE unsavedfiles
374 UnsavedId integer not null,
375 jobid integer not null,
376 pathid integer not null,
377 filenameid integer not null,
378 primary key (UnsavedId)
381 CREATE TABLE CDImages
383 MediaId integer not null,
384 LastBurn timestamp without time zone not null,
385 primary key (MediaId)
389 CREATE TABLE PathHierarchy
391 PathId integer NOT NULL,
392 PPathId integer NOT NULL,
393 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
396 CREATE INDEX pathhierarchy_ppathid
397 ON PathHierarchy (PPathId);
399 CREATE TABLE PathVisibility
401 PathId integer NOT NULL,
402 JobId integer NOT NULL,
404 Files int4 DEFAULT 0,
405 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
407 CREATE INDEX pathvisibility_jobid
408 ON PathVisibility (JobId);
412 versionid integer not null
415 CREATE TABLE Status (
416 JobStatus CHAR(1) NOT NULL,
417 JobStatusLong varchar(256),
419 PRIMARY KEY (JobStatus)
424 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
425 ('C', 'Created, not yet running',15);
426 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
428 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
430 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
431 ('T', 'Completed successfully', 10);
432 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
433 ('E', 'Terminated with errors', 25);
434 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
435 ('e', 'Non-fatal error',20);
436 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
437 ('f', 'Fatal error',100);
438 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
439 ('D', 'Verify found differences',15);
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
441 ('A', 'Canceled by user',90);
442 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
443 ('F', 'Waiting for Client',15);
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 ('S', 'Waiting for Storage daemon',15);
446 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
447 ('m', 'Waiting for new media');
448 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
449 ('M', 'Waiting for media mount',15);
450 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
451 ('s', 'Waiting for storage resource',15);
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('j', 'Waiting for job resource',15);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
455 ('c', 'Waiting for client resource',15);
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
457 ('d', 'Waiting on maximum jobs',15);
458 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
459 ('t', 'Waiting on start time',15);
460 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
461 ('p', 'Waiting on higher priority jobs',15);
462 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
463 ('a', 'SD despooling attributes',15);
464 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
465 ('i', 'Doing batch insert file records',15);
467 INSERT INTO Version (VersionId) VALUES (11);
469 -- Make sure we have appropriate permissions
476 echo "Creation of Bacula Ingres tables succeeded."
478 echo "Creation of Bacula Ingres tables failed."