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