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);
70 -- ***FIXME*** this needs to be corrected and turned on
71 --CREATE TABLE RestoreObject (
72 -- RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_seq.nextval,
73 -- Fname TEXT NOT NULL,
74 -- Path TEXT NOT NULL,
75 -- RestoreObject TEXT NOT NULL,
76 -- PluginName TEXT NOT NULL,
77 -- ObjectIndex INTEGER DEFAULT 0,
78 -- ObjectType INTEGER DEFAULT 0,
79 -- FileIndex INTEGER UNSIGNED DEFAULT 0,
80 -- JobId INTEGER UNSIGNED,
81 -- PRIMARY KEY(RestoreObjectId),
83 --CREATE INDEX restore_jobid_idx on file(JobId);
87 CREATE SEQUENCE Job_seq;
90 JobId integer not null default Job_seq.nextval,
91 Job varchar(256) not null,
92 Name varchar(256) not null,
93 Type char(1) not null,
94 Level char(1) not null,
95 ClientId integer default 0,
96 JobStatus char(1) not null,
97 SchedTime timestamp without time zone,
98 StartTime timestamp without time zone,
99 EndTime timestamp without time zone,
100 RealEndTime timestamp without time zone,
101 JobTDate bigint default 0,
102 VolSessionId integer default 0,
103 volSessionTime integer default 0,
104 JobFiles integer default 0,
105 JobBytes bigint default 0,
106 ReadBytes bigint default 0,
107 JobErrors integer default 0,
108 JobMissingFiles integer default 0,
109 PoolId integer default 0,
110 FilesetId integer default 0,
111 PriorJobid integer default 0,
112 PurgedFiles smallint default 0,
113 HasBase smallint default 0,
114 HasCache smallint default 0,
115 Reviewed smallint default 0,
116 Comment varchar(256),
120 CREATE INDEX job_name_idx on job (name);
122 -- Create a table like Job for long term statistics
123 CREATE SEQUENCE JobHisto_seq;
124 CREATE TABLE JobHisto
126 JobId integer not null default JobHisto_seq.nextval,
127 Job varchar(256) not null,
128 Name varchar(256) not null,
129 Type char(1) not null,
130 Level char(1) not null,
131 ClientId integer default 0,
132 JobStatus char(1) not null,
133 SchedTime timestamp without time zone,
134 StartTime timestamp without time zone,
135 EndTime timestamp without time zone,
136 RealEndTime timestamp without time zone,
137 JobTDate bigint default 0,
138 VolSessionId integer default 0,
139 volSessionTime integer default 0,
140 JobFiles integer default 0,
141 JobBytes bigint default 0,
142 ReadBytes bigint default 0,
143 JobErrors integer default 0,
144 JobMissingFiles integer default 0,
145 PoolId integer default 0,
146 FilesetId integer default 0,
147 PriorJobid integer default 0,
148 PurgedFiles smallint default 0,
149 HasBase smallint default 0,
150 HasCache smallint default 0,
151 Reviewed smallint default 0,
152 Comment varchar(256),
156 CREATE INDEX jobhisto_idx on JobHisto ( StartTime );
159 CREATE SEQUENCE Location_seq;
160 CREATE TABLE Location (
161 LocationId integer not null default Location_seq.nextval,
162 Location varchar(256) not null,
163 Cost integer default 0,
165 primary key (LocationId)
168 CREATE SEQUENCE fileset_seq;
171 filesetid integer not null default fileset_seq.nextval,
172 fileset varchar(256) not null,
173 md5 varchar(256) not null,
174 createtime timestamp without time zone not null,
175 primary key (filesetid)
178 CREATE INDEX fileset_name_idx on fileset (fileset);
180 CREATE SEQUENCE jobmedia_seq;
181 CREATE TABLE jobmedia
183 jobmediaid integer not null default jobmedia_seq.nextval,
184 jobid integer not null,
185 mediaid integer not null,
186 firstindex integer default 0,
187 lastindex integer default 0,
188 startfile integer default 0,
189 endfile integer default 0,
190 startblock bigint default 0,
191 endblock bigint default 0,
192 volindex integer default 0,
193 primary key (jobmediaid)
196 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
198 CREATE SEQUENCE media_seq;
201 mediaid integer not null default media_seq.nextval,
202 volumename varchar(256) not null,
203 slot integer default 0,
204 poolid integer default 0,
205 mediatype varchar(256) not null,
206 mediatypeid integer default 0,
207 labeltype integer default 0,
208 firstwritten timestamp without time zone,
209 lastwritten timestamp without time zone,
210 labeldate timestamp without time zone,
211 voljobs integer default 0,
212 volfiles integer default 0,
213 volblocks integer default 0,
214 volmounts integer default 0,
215 volbytes bigint default 0,
216 volparts integer default 0,
217 volerrors integer default 0,
218 volwrites integer default 0,
219 volcapacitybytes bigint default 0,
220 volstatus varchar(256) not null
221 check (volstatus in ('Full','Archive','Append',
222 'Recycle','Purged','Read-Only','Disabled',
223 'Error','Busy','Used','Cleaning','Scratch')),
224 enabled smallint default 1,
225 recycle smallint default 0,
226 ActionOnPurge smallint default 0,
227 volretention bigint default 0,
228 voluseduration bigint default 0,
229 maxvoljobs integer default 0,
230 maxvolfiles integer default 0,
231 maxvolbytes bigint default 0,
232 inchanger smallint default 0,
233 StorageId bigint default 0,
234 DeviceId integer default 0,
235 mediaaddressing smallint default 0,
236 volreadtime bigint default 0,
237 volwritetime bigint default 0,
238 endfile integer default 0,
239 endblock bigint default 0,
240 LocationId integer default 0,
241 recyclecount integer default 0,
242 initialwrite timestamp without time zone,
243 scratchpoolid integer default 0,
244 recyclepoolid integer default 0,
245 comment varchar(256),
246 primary key (mediaid)
249 create unique index media_volumename_id on media (volumename);
251 CREATE SEQUENCE MediaType_seq;
252 CREATE TABLE MediaType (
253 MediaTypeId INTEGER NOT NULL DEFAULT MediaType_seq.nextval,
254 MediaType varchar(256) NOT NULL,
255 ReadOnly INTEGER NOT NULL DEFAULT 0,
256 PRIMARY KEY(MediaTypeId)
259 CREATE SEQUENCE Storage_seq;
260 CREATE TABLE Storage (
261 StorageId INTEGER NOT NULL DEFAULT Storage_seq.nextval,
262 Name varchar(256) NOT NULL,
263 AutoChanger INTEGER NOT NULL DEFAULT 0,
264 PRIMARY KEY(StorageId)
267 CREATE SEQUENCE Device_seq;
268 CREATE TABLE Device (
269 DeviceId INTEGER NOT NULL DEFAULT Device_seq.nextval,
270 Name varchar(256) NOT NULL,
271 MediaTypeId INTEGER NOT NULL,
272 StorageId INTEGER NOT NULL,
273 DevMounts INTEGER NOT NULL DEFAULT 0,
274 DevReadBytes BIGINT NOT NULL DEFAULT 0,
275 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
276 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
277 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
278 DevReadTime BIGINT NOT NULL DEFAULT 0,
279 DevWriteTime BIGINT NOT NULL DEFAULT 0,
280 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
281 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
282 CleaningDate timestamp without time zone,
283 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
284 PRIMARY KEY(DeviceId)
287 CREATE SEQUENCE pool_seq;
290 poolid integer not null default pool_seq.nextval,
291 name varchar(256) not null,
292 numvols integer default 0,
293 maxvols integer default 0,
294 useonce smallint default 0,
295 usecatalog smallint default 0,
296 acceptanyvolume smallint default 0,
297 volretention bigint default 0,
298 voluseduration bigint default 0,
299 maxvoljobs integer default 0,
300 maxvolfiles integer default 0,
301 maxvolbytes bigint default 0,
302 autoprune smallint default 0,
303 recycle smallint default 0,
304 ActionOnPurge smallint default 0,
305 pooltype varchar(256)
306 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
307 labeltype integer default 0,
308 labelformat varchar(256) not null,
309 enabled smallint default 1,
310 scratchpoolid integer default 0,
311 recyclepoolid integer default 0,
312 NextPoolId integer default 0,
313 MigrationHighBytes BIGINT DEFAULT 0,
314 MigrationLowBytes BIGINT DEFAULT 0,
315 MigrationTime BIGINT DEFAULT 0,
319 CREATE INDEX pool_name_idx on pool (name);
321 CREATE SEQUENCE client_seq;
324 clientid integer not null default client_seq.nextval,
325 name varchar(256) not null,
326 uname varchar(256) not null,
327 autoprune smallint default 0,
328 fileretention bigint default 0,
329 jobretention bigint default 0,
330 primary key (clientid)
333 create unique index client_name_idx on client (name);
335 CREATE SEQUENCE Log_seq;
338 LogId integer not null default Log_seq.nextval,
339 JobId integer not null,
340 Time timestamp without time zone,
341 LogText varchar(256) not null,
344 create index log_name_idx on Log (JobId);
346 CREATE SEQUENCE LocationLog_seq;
347 CREATE TABLE LocationLog (
348 LocLogId INTEGER NOT NULL DEFAULT LocationLog_seq.nextval,
349 Date timestamp without time zone,
350 Comment varchar(256) NOT NULL,
351 MediaId INTEGER DEFAULT 0,
352 LocationId INTEGER DEFAULT 0,
353 newvolstatus varchar(256) not null
354 check (newvolstatus in ('Full','Archive','Append',
355 'Recycle','Purged','Read-Only','Disabled',
356 'Error','Busy','Used','Cleaning','Scratch')),
358 PRIMARY KEY(LocLogId)
361 CREATE TABLE counters
363 counter varchar(256) not null,
364 minvalue integer default 0,
365 maxvalue integer default 0,
366 currentvalue integer default 0,
367 wrapcounter varchar(256) not null,
368 primary key (counter)
371 CREATE SEQUENCE basefiles_seq;
372 CREATE TABLE basefiles
374 baseid integer not null default basefiles_seq.nextval,
375 jobid integer not null,
376 fileid bigint not null,
382 CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
384 CREATE TABLE unsavedfiles
386 UnsavedId integer not null,
387 jobid integer not null,
388 pathid integer not null,
389 filenameid integer not null,
390 primary key (UnsavedId)
393 CREATE TABLE CDImages
395 MediaId integer not null,
396 LastBurn timestamp without time zone not null,
397 primary key (MediaId)
401 CREATE TABLE PathHierarchy
403 PathId integer NOT NULL,
404 PPathId integer NOT NULL,
405 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
408 CREATE INDEX pathhierarchy_ppathid
409 ON PathHierarchy (PPathId);
411 CREATE TABLE PathVisibility
413 PathId integer NOT NULL,
414 JobId integer NOT NULL,
416 Files int4 DEFAULT 0,
417 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
419 CREATE INDEX pathvisibility_jobid
420 ON PathVisibility (JobId);
424 versionid integer not null
427 CREATE TABLE Status (
428 JobStatus CHAR(1) NOT NULL,
429 JobStatusLong varchar(256),
431 PRIMARY KEY (JobStatus)
436 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
437 ('C', 'Created, not yet running',15);
438 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
443 ('T', 'Completed successfully', 10);
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 ('E', 'Terminated with errors', 25);
446 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
447 ('e', 'Non-fatal error',20);
448 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
449 ('f', 'Fatal error',100);
450 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
451 ('D', 'Verify found differences',15);
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('A', 'Canceled by user',90);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
455 ('F', 'Waiting for Client',15);
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
457 ('S', 'Waiting for Storage daemon',15);
458 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
459 ('m', 'Waiting for new media');
460 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
461 ('M', 'Waiting for media mount',15);
462 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
463 ('s', 'Waiting for storage resource',15);
464 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
465 ('j', 'Waiting for job resource',15);
466 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
467 ('c', 'Waiting for client resource',15);
468 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
469 ('d', 'Waiting on maximum jobs',15);
470 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
471 ('t', 'Waiting on start time',15);
472 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
473 ('p', 'Waiting on higher priority jobs',15);
474 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
475 ('a', 'SD despooling attributes',15);
476 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
477 ('i', 'Doing batch insert file records',15);
479 INSERT INTO Version (VersionId) VALUES (12);
481 -- Make sure we have appropriate permissions
488 echo "Creation of Bacula Ingres tables succeeded."
490 echo "Creation of Bacula Ingres tables failed."