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
13 -- As it seems the upper limit for a VARCHAR/VARBYTE column is 32000 bytes
14 -- We could have used a BLOB as storage type which can go up to 2 Gb but
15 -- you cannot create indexes based on a BLOB (and we don't seem to be able
16 -- to limit the part of the field which should be used for the index to lets
17 -- say 255 chars like we can in MySQL) and the storage for a BLOB is also far
18 -- from optimal so for now we decided that the upper limit for File and Path
19 -- fields is the 32000 bytes.
21 -- To use the big VARBYTE colums we need the bigger page sizes enabled in
22 -- ingres (which at this time supports 2K, 4K, 8K, 16K, 32K, 64K)
23 -- Make sure you have the following settings in your
24 -- ${II_SYSTEM}/ingres/files/config.dat
26 -- ii.<nodename>.dbms.private.*.cache.p8k_status: ON
27 -- ii.<nodename>.dbms.private.*.cache.p16k_status: ON
28 -- ii.<nodename>.dbms.private.*.cache.p32k_status: ON
29 -- ii.<nodename>.dbms.private.*.cache.p64k_status: ON
30 -- ii.<nodename>.rcp.dmf_cache_size8k: 200
31 -- ii.<nodename>.rcp.dmf_cache_size16k: 200
32 -- ii.<nodename>.rcp.dmf_cache_size32k: 200
33 -- ii.<nodename>.rcp.dmf_cache_size64k: 200
38 CREATE SEQUENCE Filename_Seq;
41 FilenameId INTEGER NOT NULL DEFAULT Filename_Seq.nextval,
42 Name VARBYTE(32000) NOT NULL,
43 PRIMARY KEY (FilenameId)
46 CREATE UNIQUE INDEX (filename_name_idx ON filename (Name) WITH STRUCTURE=HASH,PAGE_SIZE=32768);
48 CREATE SEQUENCE Path_Seq;
51 Pathid INTEGER NOT NULL DEFAULT Path_Seq.nextval,
52 Path VARBYTE(32000) NOT NULL,
56 CREATE UNIQUE INDEX (path_name_idx ON path (Path) WITH STRUCTURE=HASH,PAGE_SIZE=32768);
58 CREATE SEQUENCE File_Seq;
61 FileId BIGINT NOT NULL DEFAULT File_Seq.nextval,
62 FileIndex INTEGER NOT NULL DEFAULT 0,
63 JobId INTEGER NOT NULL,
64 PathId INTEGER NOT NULL,
65 FilenameId INTEGER NOT NULL,
66 MarkId INTEGER NOT NULL DEFAULT 0,
67 LStat VARBYTE(255) NOT NULL,
68 Md5 VARBYTE(255) NOT NULL,
72 CREATE INDEX file_jpfid_idx ON File (jobid, pathid, filenameid);
74 -- If you need performances, you can remove this index
75 -- the database engine is able to use the composite index
76 -- to find all records with a given JobId
77 CREATE INDEX file_jobid_idx ON File(jobid);
79 CREATE SEQUENCE RestoreObject_Seq;
80 CREATE TABLE RestoreObject (
81 RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_Seq.nextval,
82 RestoreObject BLOB NOT NULL,
83 PluginName VARBYTE(128) NOT NULL,
84 ObjectLength INTEGER DEFAULT 0,
85 ObjectFullLength INTEGER DEFAULT 0,
86 ObjectIndex INTEGER DEFAULT 0,
87 ObjectType INTEGER DEFAULT 0,
88 FileIndex INTEGER DEFAULT 0,
90 ObjectCompression INTEGER DEFAULT 0,
91 PRIMARY KEY (RestoreObjectId)
94 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
96 CREATE SEQUENCE Job_Seq;
99 JobId INTEGER NOT NULL DEFAULT Job_Seq.nextval,
100 Job VARBYTE(128) NOT NULL,
101 Name VARBYTE(128) NOT NULL,
102 Type CHAR(1) NOT NULL,
103 Level CHAR(1) NOT NULL,
104 ClientId INTEGER DEFAULT 0,
105 JobStatus CHAR(1) NOT NULL,
106 SchedTime TIMESTAMP WITHOUT TIME ZONE,
107 StartTime TIMESTAMP WITHOUT TIME ZONE,
108 EndTime TIMESTAMP WITHOUT TIME ZONE,
109 RealEndTime TIMESTAMP WITHOUT TIME ZONE,
110 JobTDate BIGINT DEFAULT 0,
111 VolSessionId INTEGER DEFAULT 0,
112 volSessionTime INTEGER DEFAULT 0,
113 JobFiles INTEGER DEFAULT 0,
114 JobBytes BIGINT DEFAULT 0,
115 ReadBytes BIGINT DEFAULT 0,
116 JobErrors INTEGER DEFAULT 0,
117 JobMissingFiles INTEGER DEFAULT 0,
118 PoolId INTEGER DEFAULT 0,
119 FilesetId INTEGER DEFAULT 0,
120 PriorJobid INTEGER DEFAULT 0,
121 PurgedFiles SMALLINT DEFAULT 0,
122 HasBase SMALLINT DEFAULT 0,
123 HasCache SMALLINT DEFAULT 0,
124 Reviewed SMALLINT DEFAULT 0,
125 Comment VARBYTE(4096),
129 CREATE INDEX job_name_idx ON Job (Name);
131 -- Create a table like Job for long term statistics
132 CREATE SEQUENCE JobHisto_Seq;
133 CREATE TABLE JobHisto
135 JobId INTEGER NOT NULL DEFAULT JobHisto_Seq.nextval,
136 Job VARBYTE(128) NOT NULL,
137 Name VARBYTE(128) NOT NULL,
138 Type CHAR(1) NOT NULL,
139 Level CHAR(1) NOT NULL,
140 ClientId INTEGER DEFAULT 0,
141 JobStatus CHAR(1) NOT NULL,
142 SchedTime TIMESTAMP WITHOUT TIME ZONE,
143 StartTime TIMESTAMP WITHOUT TIME ZONE,
144 EndTime TIMESTAMP WITHOUT TIME ZONE,
145 RealEndTime TIMESTAMP WITHOUT TIME ZONE,
146 JobTDate BIGINT DEFAULT 0,
147 VolSessionId INTEGER DEFAULT 0,
148 volSessionTime INTEGER DEFAULT 0,
149 JobFiles INTEGER DEFAULT 0,
150 JobBytes BIGINT DEFAULT 0,
151 ReadBytes BIGINT DEFAULT 0,
152 JobErrors INTEGER DEFAULT 0,
153 JobMissingFiles INTEGER DEFAULT 0,
154 PoolId INTEGER DEFAULT 0,
155 FilesetId INTEGER DEFAULT 0,
156 PriorJobid INTEGER DEFAULT 0,
157 PurgedFiles SMALLINT DEFAULT 0,
158 HasBase SMALLINT DEFAULT 0,
159 HasCache SMALLINT DEFAULT 0,
160 Reviewed SMALLINT DEFAULT 0,
161 Comment VARBYTE(4096),
165 CREATE INDEX jobhisto_idx ON JobHisto (StartTime);
167 CREATE SEQUENCE Location_Seq;
168 CREATE TABLE Location (
169 LocationId INTEGER NOT NULL DEFAULT Location_Seq.nextval,
170 Location VARBYTE(128) NOT NULL,
171 Cost INTEGER DEFAULT 0,
173 PRIMARY KEY (LocationId)
176 CREATE SEQUENCE Fileset_Seq;
179 FilesetId INTEGER NOT NULL DEFAULT Fileset_Seq.nextval,
180 Fileset VARBYTE(128) NOT NULL,
181 Md5 VARBYTE(128) NOT NULL,
182 Createtime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
183 PRIMARY KEY (filesetid)
186 CREATE INDEX fileset_name_idx ON Fileset (fileset);
188 CREATE SEQUENCE JobMedia_Seq;
189 CREATE TABLE JobMedia
191 JobMediaId INTEGER NOT NULL DEFAULT JobMedia_Seq.nextval,
192 JobId INTEGER NOT NULL,
193 MediaId INTEGER NOT NULL,
194 FirstIndex INTEGER DEFAULT 0,
195 LastIndex INTEGER DEFAULT 0,
196 StartFile INTEGER DEFAULT 0,
197 EndFile INTEGER DEFAULT 0,
198 StartBlock BIGINT DEFAULT 0,
199 EndBlock BIGINT DEFAULT 0,
200 VolIndex INTEGER DEFAULT 0,
201 PRIMARY KEY (jobmediaid)
204 CREATE INDEX job_media_job_id_media_id_idx ON JobMedia (jobid, mediaid);
206 CREATE SEQUENCE Media_Seq;
209 MediaId INTEGER NOT NULL DEFAULT Media_Seq.nextval,
210 VolumeName VARBYTE(128) NOT NULL,
211 Slot INTEGER DEFAULT 0,
212 PoolId INTEGER DEFAULT 0,
213 MediaType VARBYTE(128) NOT NULL,
214 MediaTypeId INTEGER DEFAULT 0,
215 LabelType INTEGER DEFAULT 0,
216 FirstWritten TIMESTAMP WITHOUT TIME ZONE,
217 LastWritten TIMESTAMP WITHOUT TIME ZONE,
218 LabelDate TIMESTAMP WITHOUT TIME ZONE,
219 VolJobs INTEGER DEFAULT 0,
220 VolFiles INTEGER DEFAULT 0,
221 VolBlocks INTEGER DEFAULT 0,
222 VolMounts INTEGER DEFAULT 0,
223 VolBytes BIGINT DEFAULT 0,
224 VolParts INTEGER DEFAULT 0,
225 VolErrors INTEGER DEFAULT 0,
226 VolWrites INTEGER DEFAULT 0,
227 VolCapacitybytes BIGINT DEFAULT 0,
228 VolStatus VARBYTE(128) NOT NULL
229 CHECK (volstatus in ('Full','Archive','Append',
230 'Recycle','Purged','Read-Only','Disabled',
231 'Error','Busy','Used','Cleaning','Scratch')),
232 Enabled SMALLINT DEFAULT 1,
233 Recycle SMALLINT DEFAULT 0,
234 ActionOnPurge SMALLINT DEFAULT 0,
235 VolRetention BIGINT DEFAULT 0,
236 VolUseDuration BIGINT DEFAULT 0,
237 MaxVolJobs INTEGER DEFAULT 0,
238 MaxVolFiles INTEGER DEFAULT 0,
239 MaxVolBytes BIGINT DEFAULT 0,
240 InChanger SMALLINT DEFAULT 0,
241 StorageId BIGINT DEFAULT 0,
242 DeviceId INTEGER DEFAULT 0,
243 MediaAddressing SMALLINT DEFAULT 0,
244 VolReadTime BIGINT DEFAULT 0,
245 VolWriteTime BIGINT DEFAULT 0,
246 EndFile INTEGER DEFAULT 0,
247 EndBlock BIGINT DEFAULT 0,
248 LocationId INTEGER DEFAULT 0,
249 RecycleCount INTEGER DEFAULT 0,
250 InitialWrite TIMESTAMP WITHOUT TIME ZONE,
251 ScratchPoolId INTEGER DEFAULT 0,
252 RecyclePoolId INTEGER DEFAULT 0,
253 Comment VARBYTE(4096),
254 PRIMARY KEY (mediaid)
257 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName);
259 CREATE SEQUENCE MediaType_Seq;
260 CREATE TABLE MediaType (
261 MediaTypeId INTEGER NOT NULL DEFAULT MediaType_Seq.nextval,
262 MediaType VARBYTE(128) NOT NULL,
263 ReadOnly INTEGER NOT NULL DEFAULT 0,
264 PRIMARY KEY (MediaTypeId)
267 CREATE SEQUENCE Storage_Seq;
268 CREATE TABLE Storage (
269 StorageId INTEGER NOT NULL DEFAULT Storage_Seq.nextval,
270 Name VARBYTE(128) NOT NULL,
271 AutoChanger INTEGER NOT NULL DEFAULT 0,
272 PRIMARY KEY (StorageId)
275 CREATE SEQUENCE Device_Seq;
276 CREATE TABLE Device (
277 DeviceId INTEGER NOT NULL DEFAULT Device_Seq.nextval,
278 Name VARBYTE(128) NOT NULL,
279 MediaTypeId INTEGER NOT NULL,
280 StorageId INTEGER NOT NULL,
281 DevMounts INTEGER NOT NULL DEFAULT 0,
282 DevReadBytes BIGINT NOT NULL DEFAULT 0,
283 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
284 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
285 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
286 DevReadTime BIGINT NOT NULL DEFAULT 0,
287 DevWriteTime BIGINT NOT NULL DEFAULT 0,
288 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
289 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
290 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
291 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
292 PRIMARY KEY (DeviceId)
295 CREATE SEQUENCE Pool_Seq;
298 PoolId INTEGER NOT NULL DEFAULT pool_Seq.nextval,
299 Name VARBYTE(128) NOT NULL,
300 NumVols INTEGER DEFAULT 0,
301 MaxVols INTEGER DEFAULT 0,
302 UseOnce SMALLINT DEFAULT 0,
303 UseCatalog SMALLINT DEFAULT 0,
304 AcceptAnyVolume SMALLINT DEFAULT 0,
305 VolRetention BIGINT DEFAULT 0,
306 VolUseDuration BIGINT DEFAULT 0,
307 MaxVolJobs INTEGER DEFAULT 0,
308 MaxVolFiles INTEGER DEFAULT 0,
309 MaxVolBytes BIGINT DEFAULT 0,
310 AutoPrune SMALLINT DEFAULT 0,
311 Recycle SMALLINT DEFAULT 0,
312 ActionOnPurge SMALLINT DEFAULT 0,
314 CHECK (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
315 LabelType INTEGER DEFAULT 0,
316 LabelFormat VARBYTE(128) NOT NULL,
317 Enabled SMALLINT DEFAULT 1,
318 ScratchPoolId INTEGER DEFAULT 0,
319 RecyclePoolId INTEGER DEFAULT 0,
320 NextPoolId INTEGER DEFAULT 0,
321 MigrationHighBytes BIGINT DEFAULT 0,
322 MigrationLowBytes BIGINT DEFAULT 0,
323 MigrationTime BIGINT DEFAULT 0,
327 CREATE INDEX pool_name_idx ON Pool (name);
329 CREATE SEQUENCE Client_Seq;
332 ClientId INTEGER NOT NULL DEFAULT Client_Seq.nextval,
333 Name VARBYTE(128) NOT NULL,
334 Uname VARBYTE(256) NOT NULL,
335 AutoPrune SMALLINT DEFAULT 0,
336 FileRetention BIGINT DEFAULT 0,
337 JobRetention BIGINT DEFAULT 0,
338 PRIMARY KEY (clientid)
341 CREATE UNIQUE INDEX client_name_idx ON Client (Name);
343 CREATE SEQUENCE Log_Seq;
346 LogId INTEGER NOT NULL DEFAULT Log_Seq.nextval,
347 JobId INTEGER NOT NULL,
348 Time TIMESTAMP WITHOUT TIME ZONE,
349 LogText VARBYTE(4096) NOT NULL,
352 CREATE INDEX log_name_idx ON Log (JobId);
354 CREATE SEQUENCE LocationLog_Seq;
355 CREATE TABLE LocationLog (
356 LocLogId INTEGER NOT NULL DEFAULT LocationLog_Seq.nextval,
357 Date TIMESTAMP WITHOUT TIME ZONE,
358 Comment VARBYTE(4096) NOT NULL,
359 MediaId INTEGER DEFAULT 0,
360 LocationId INTEGER DEFAULT 0,
361 NewVolStatus VARBYTE(32) NOT NULL
362 CHECK (newvolstatus in ('Full','Archive','Append',
363 'Recycle','Purged','Read-Only','Disabled',
364 'Error','Busy','Used','Cleaning','Scratch')),
366 PRIMARY KEY (LocLogId)
369 CREATE TABLE Counters
371 Counter VARBYTE(128) NOT NULL,
372 MinValue INTEGER DEFAULT 0,
373 MaxValue INTEGER DEFAULT 0,
374 CurrentValue INTEGER DEFAULT 0,
375 WrapCounter VARBYTE(128) NOT NULL,
376 PRIMARY KEY (counter)
379 CREATE SEQUENCE BaseFiles_Seq;
380 CREATE TABLE BaseFiles
382 BaseId INTEGER NOT NULL DEFAULT BaseFiles_Seq.nextval,
383 JobId INTEGER NOT NULL,
384 FileId BIGINT NOT NULL,
390 CREATE INDEX basefiles_jobid_idx ON BaseFiles (JobId);
392 CREATE TABLE unsavedfiles
394 UnsavedId INTEGER NOT NULL,
395 JobId INTEGER NOT NULL,
396 PathId INTEGER NOT NULL,
397 FilenameId INTEGER NOT NULL,
398 PRIMARY KEY (UnsavedId)
401 CREATE TABLE CDImages
403 MediaId INTEGER NOT NULL,
404 LastBurn TIMESTAMP WITHOUT TIME ZONE NOT NULL,
405 PRIMARY KEY (MediaId)
408 CREATE TABLE PathHierarchy
410 PathId INTEGER NOT NULL,
411 PPathId INTEGER NOT NULL,
412 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
415 CREATE INDEX pathhierarchy_ppathid ON PathHierarchy (PPathId);
417 CREATE TABLE PathVisibility
419 PathId INTEGER NOT NULL,
420 JobId INTEGER NOT NULL,
421 Size BIGINT DEFAULT 0,
422 Files INTEGER DEFAULT 0,
423 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
425 CREATE INDEX pathvisibility_jobid ON PathVisibility (JobId);
429 versionid INTEGER NOT NULL
432 CREATE TABLE Status (
433 JobStatus CHAR(1) NOT NULL,
434 JobStatusLong VARBYTE(128),
436 PRIMARY KEY (JobStatus)
441 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
442 ('C', 'Created, not yet running',15);
443 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
445 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
447 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
448 ('T', 'Completed successfully', 10);
449 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
450 ('E', 'Terminated with errors', 25);
451 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
452 ('e', 'Non-fatal error',20);
453 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
454 ('f', 'Fatal error',100);
455 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
456 ('D', 'Verify found differences',15);
457 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
458 ('A', 'Canceled by user',90);
459 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
460 ('F', 'Waiting for Client',15);
461 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
462 ('S', 'Waiting for Storage daemon',15);
463 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
464 ('m', 'Waiting for new media');
465 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
466 ('M', 'Waiting for media mount',15);
467 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
468 ('s', 'Waiting for storage resource',15);
469 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
470 ('j', 'Waiting for job resource',15);
471 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
472 ('c', 'Waiting for client resource',15);
473 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
474 ('d', 'Waiting on maximum jobs',15);
475 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
476 ('t', 'Waiting on start time',15);
477 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
478 ('p', 'Waiting on higher priority jobs',15);
479 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
480 ('a', 'SD despooling attributes',15);
481 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
482 ('i', 'Doing batch insert file records',15);
484 INSERT INTO Version (VersionId) VALUES (12);
486 -- Make sure we have appropriate permissions
493 echo "Creation of Bacula Ingres tables succeeded."
495 echo "Creation of Bacula Ingres tables failed."