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