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 INDEX restore_jobid_idx ON File(JobId);
81 CREATE SEQUENCE RestoreObject_Seq;
82 CREATE TABLE RestoreObject (
83 RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_Seq.nextval,
84 Fname VARBYTE(32000) NOT NULL,
85 Path VARBYTE(32000) NOT NULL,
86 RestoreObject VARBYTE(32000) NOT NULL,
87 PluginName VARBYTE(128) NOT NULL,
88 ObjectIndex INTEGER DEFAULT 0,
89 ObjectType INTEGER DEFAULT 0,
90 FileIndex INTEGER DEFAULT 0,
92 PRIMARY KEY(RestoreObjectId)
95 CREATE SEQUENCE Job_Seq;
98 JobId INTEGER NOT NULL DEFAULT Job_Seq.nextval,
99 Job VARBYTE(128) NOT NULL,
100 Name VARBYTE(128) NOT NULL,
101 Type CHAR(1) NOT NULL,
102 Level CHAR(1) NOT NULL,
103 ClientId INTEGER DEFAULT 0,
104 JobStatus CHAR(1) NOT NULL,
105 SchedTime TIMESTAMP WITHOUT TIME ZONE,
106 StartTime TIMESTAMP WITHOUT TIME ZONE,
107 EndTime TIMESTAMP WITHOUT TIME ZONE,
108 RealEndTime TIMESTAMP WITHOUT TIME ZONE,
109 JobTDate BIGINT DEFAULT 0,
110 VolSessionId INTEGER DEFAULT 0,
111 volSessionTime INTEGER DEFAULT 0,
112 JobFiles INTEGER DEFAULT 0,
113 JobBytes BIGINT DEFAULT 0,
114 ReadBytes BIGINT DEFAULT 0,
115 JobErrors INTEGER DEFAULT 0,
116 JobMissingFiles INTEGER DEFAULT 0,
117 PoolId INTEGER DEFAULT 0,
118 FilesetId INTEGER DEFAULT 0,
119 PriorJobid INTEGER DEFAULT 0,
120 PurgedFiles SMALLINT DEFAULT 0,
121 HasBase SMALLINT DEFAULT 0,
122 HasCache SMALLINT DEFAULT 0,
123 Reviewed SMALLINT DEFAULT 0,
124 Comment VARBYTE(4096),
128 CREATE INDEX job_name_idx ON Job (Name);
130 -- Create a table like Job for long term statistics
131 CREATE SEQUENCE JobHisto_Seq;
132 CREATE TABLE JobHisto
134 JobId INTEGER NOT NULL DEFAULT JobHisto_Seq.nextval,
135 Job VARBYTE(128) NOT NULL,
136 Name VARBYTE(128) NOT NULL,
137 Type CHAR(1) NOT NULL,
138 Level CHAR(1) NOT NULL,
139 ClientId INTEGER DEFAULT 0,
140 JobStatus CHAR(1) NOT NULL,
141 SchedTime TIMESTAMP WITHOUT TIME ZONE,
142 StartTime TIMESTAMP WITHOUT TIME ZONE,
143 EndTime TIMESTAMP WITHOUT TIME ZONE,
144 RealEndTime TIMESTAMP WITHOUT TIME ZONE,
145 JobTDate BIGINT DEFAULT 0,
146 VolSessionId INTEGER DEFAULT 0,
147 volSessionTime INTEGER DEFAULT 0,
148 JobFiles INTEGER DEFAULT 0,
149 JobBytes BIGINT DEFAULT 0,
150 ReadBytes BIGINT DEFAULT 0,
151 JobErrors INTEGER DEFAULT 0,
152 JobMissingFiles INTEGER DEFAULT 0,
153 PoolId INTEGER DEFAULT 0,
154 FilesetId INTEGER DEFAULT 0,
155 PriorJobid INTEGER DEFAULT 0,
156 PurgedFiles SMALLINT DEFAULT 0,
157 HasBase SMALLINT DEFAULT 0,
158 HasCache SMALLINT DEFAULT 0,
159 Reviewed SMALLINT DEFAULT 0,
160 Comment VARBYTE(4096),
164 CREATE INDEX jobhisto_idx ON JobHisto (StartTime);
166 CREATE SEQUENCE Location_Seq;
167 CREATE TABLE Location (
168 LocationId INTEGER NOT NULL DEFAULT Location_Seq.nextval,
169 Location VARBYTE(128) NOT NULL,
170 Cost INTEGER DEFAULT 0,
172 PRIMARY KEY (LocationId)
175 CREATE SEQUENCE Fileset_Seq;
178 FilesetId INTEGER NOT NULL DEFAULT Fileset_Seq.nextval,
179 Fileset VARBYTE(128) NOT NULL,
180 Md5 VARBYTE(128) NOT NULL,
181 Createtime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
182 PRIMARY KEY (filesetid)
185 CREATE INDEX fileset_name_idx ON Fileset (fileset);
187 CREATE SEQUENCE JobMedia_Seq;
188 CREATE TABLE JobMedia
190 JobMediaId INTEGER NOT NULL DEFAULT JobMedia_Seq.nextval,
191 JobId INTEGER NOT NULL,
192 MediaId INTEGER NOT NULL,
193 FirstIndex INTEGER DEFAULT 0,
194 LastIndex INTEGER DEFAULT 0,
195 StartFile INTEGER DEFAULT 0,
196 EndFile INTEGER DEFAULT 0,
197 StartBlock BIGINT DEFAULT 0,
198 EndBlock BIGINT DEFAULT 0,
199 VolIndex INTEGER DEFAULT 0,
200 PRIMARY KEY (jobmediaid)
203 CREATE INDEX job_media_job_id_media_id_idx ON JobMedia (jobid, mediaid);
205 CREATE SEQUENCE Media_Seq;
208 MediaId INTEGER NOT NULL DEFAULT Media_Seq.nextval,
209 VolumeName VARBYTE(128) NOT NULL,
210 Slot INTEGER DEFAULT 0,
211 PoolId INTEGER DEFAULT 0,
212 MediaType VARBYTE(128) NOT NULL,
213 MediaTypeId INTEGER DEFAULT 0,
214 LabelType INTEGER DEFAULT 0,
215 FirstWritten TIMESTAMP WITHOUT TIME ZONE,
216 LastWritten TIMESTAMP WITHOUT TIME ZONE,
217 LabelDate TIMESTAMP WITHOUT TIME ZONE,
218 VolJobs INTEGER DEFAULT 0,
219 VolFiles INTEGER DEFAULT 0,
220 VolBlocks INTEGER DEFAULT 0,
221 VolMounts INTEGER DEFAULT 0,
222 VolBytes BIGINT DEFAULT 0,
223 VolParts INTEGER DEFAULT 0,
224 VolErrors INTEGER DEFAULT 0,
225 VolWrites INTEGER DEFAULT 0,
226 VolCapacitybytes BIGINT DEFAULT 0,
227 VolStatus VARBYTE(128) NOT NULL
228 CHECK (volstatus in ('Full','Archive','Append',
229 'Recycle','Purged','Read-Only','Disabled',
230 'Error','Busy','Used','Cleaning','Scratch')),
231 Enabled SMALLINT DEFAULT 1,
232 Recycle SMALLINT DEFAULT 0,
233 ActionOnPurge SMALLINT DEFAULT 0,
234 VolRetention BIGINT DEFAULT 0,
235 VolUseDuration BIGINT DEFAULT 0,
236 MaxVolJobs INTEGER DEFAULT 0,
237 MaxVolFiles INTEGER DEFAULT 0,
238 MaxVolBytes BIGINT DEFAULT 0,
239 InChanger SMALLINT DEFAULT 0,
240 StorageId BIGINT DEFAULT 0,
241 DeviceId INTEGER DEFAULT 0,
242 MediaAddressing SMALLINT DEFAULT 0,
243 VolReadTime BIGINT DEFAULT 0,
244 VolWriteTime BIGINT DEFAULT 0,
245 EndFile INTEGER DEFAULT 0,
246 EndBlock BIGINT DEFAULT 0,
247 LocationId INTEGER DEFAULT 0,
248 RecycleCount INTEGER DEFAULT 0,
249 InitialWrite TIMESTAMP WITHOUT TIME ZONE,
250 ScratchPoolId INTEGER DEFAULT 0,
251 RecyclePoolId INTEGER DEFAULT 0,
252 Comment VARBYTE(4096),
253 PRIMARY KEY (mediaid)
256 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName);
258 CREATE SEQUENCE MediaType_Seq;
259 CREATE TABLE MediaType (
260 MediaTypeId INTEGER NOT NULL DEFAULT MediaType_Seq.nextval,
261 MediaType VARBYTE(128) NOT NULL,
262 ReadOnly INTEGER NOT NULL DEFAULT 0,
263 PRIMARY KEY(MediaTypeId)
266 CREATE SEQUENCE Storage_Seq;
267 CREATE TABLE Storage (
268 StorageId INTEGER NOT NULL DEFAULT Storage_Seq.nextval,
269 Name VARBYTE(128) NOT NULL,
270 AutoChanger INTEGER NOT NULL DEFAULT 0,
271 PRIMARY KEY(StorageId)
274 CREATE SEQUENCE Device_Seq;
275 CREATE TABLE Device (
276 DeviceId INTEGER NOT NULL DEFAULT Device_Seq.nextval,
277 Name VARBYTE(128) NOT NULL,
278 MediaTypeId INTEGER NOT NULL,
279 StorageId INTEGER NOT NULL,
280 DevMounts INTEGER NOT NULL DEFAULT 0,
281 DevReadBytes BIGINT NOT NULL DEFAULT 0,
282 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
283 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
284 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
285 DevReadTime BIGINT NOT NULL DEFAULT 0,
286 DevWriteTime BIGINT NOT NULL DEFAULT 0,
287 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
288 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
289 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
290 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
291 PRIMARY KEY(DeviceId)
294 CREATE SEQUENCE Pool_Seq;
297 PoolId INTEGER NOT NULL DEFAULT pool_Seq.nextval,
298 Name VARBYTE(128) NOT NULL,
299 NumVols INTEGER DEFAULT 0,
300 MaxVols INTEGER DEFAULT 0,
301 UseOnce SMALLINT DEFAULT 0,
302 UseCatalog SMALLINT DEFAULT 0,
303 AcceptAnyVolume SMALLINT DEFAULT 0,
304 VolRetention BIGINT DEFAULT 0,
305 VolUseDuration BIGINT DEFAULT 0,
306 MaxVolJobs INTEGER DEFAULT 0,
307 MaxVolFiles INTEGER DEFAULT 0,
308 MaxVolBytes BIGINT DEFAULT 0,
309 AutoPrune SMALLINT DEFAULT 0,
310 Recycle SMALLINT DEFAULT 0,
311 ActionOnPurge SMALLINT DEFAULT 0,
313 CHECK (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
314 LabelType INTEGER DEFAULT 0,
315 LabelFormat VARBYTE(128) NOT NULL,
316 Enabled SMALLINT DEFAULT 1,
317 ScratchPoolId INTEGER DEFAULT 0,
318 RecyclePoolId INTEGER DEFAULT 0,
319 NextPoolId INTEGER DEFAULT 0,
320 MigrationHighBytes BIGINT DEFAULT 0,
321 MigrationLowBytes BIGINT DEFAULT 0,
322 MigrationTime BIGINT DEFAULT 0,
326 CREATE INDEX pool_name_idx ON Pool (name);
328 CREATE SEQUENCE Client_Seq;
331 ClientId INTEGER NOT NULL DEFAULT Client_Seq.nextval,
332 Name VARBYTE(128) NOT NULL,
333 Uname VARBYTE(256) NOT NULL,
334 AutoPrune SMALLINT DEFAULT 0,
335 FileRetention BIGINT DEFAULT 0,
336 JobRetention BIGINT DEFAULT 0,
337 PRIMARY KEY (clientid)
340 CREATE UNIQUE INDEX client_name_idx ON Client (Name);
342 CREATE SEQUENCE Log_Seq;
345 LogId INTEGER NOT NULL DEFAULT Log_Seq.nextval,
346 JobId INTEGER NOT NULL,
347 Time TIMESTAMP WITHOUT TIME ZONE,
348 LogText VARBYTE(4096) NOT NULL,
351 CREATE INDEX log_name_idx ON Log (JobId);
353 CREATE SEQUENCE LocationLog_Seq;
354 CREATE TABLE LocationLog (
355 LocLogId INTEGER NOT NULL DEFAULT LocationLog_Seq.nextval,
356 Date TIMESTAMP WITHOUT TIME ZONE,
357 Comment VARBYTE(4096) NOT NULL,
358 MediaId INTEGER DEFAULT 0,
359 LocationId INTEGER DEFAULT 0,
360 NewVolStatus VARBYTE(32) NOT NULL
361 CHECK (newvolstatus in ('Full','Archive','Append',
362 'Recycle','Purged','Read-Only','Disabled',
363 'Error','Busy','Used','Cleaning','Scratch')),
365 PRIMARY KEY(LocLogId)
368 CREATE TABLE Counters
370 Counter VARBYTE(128) NOT NULL,
371 MinValue INTEGER DEFAULT 0,
372 MaxValue INTEGER DEFAULT 0,
373 CurrentValue INTEGER DEFAULT 0,
374 WrapCounter VARBYTE(128) NOT NULL,
375 PRIMARY KEY (counter)
378 CREATE SEQUENCE BaseFiles_Seq;
379 CREATE TABLE BaseFiles
381 BaseId INTEGER NOT NULL DEFAULT BaseFiles_Seq.nextval,
382 JobId INTEGER NOT NULL,
383 FileId BIGINT NOT NULL,
389 CREATE INDEX basefiles_jobid_idx ON BaseFiles (JobId);
391 CREATE TABLE unsavedfiles
393 UnsavedId INTEGER NOT NULL,
394 JobId INTEGER NOT NULL,
395 PathId INTEGER NOT NULL,
396 FilenameId INTEGER NOT NULL,
397 PRIMARY KEY (UnsavedId)
400 CREATE TABLE CDImages
402 MediaId INTEGER NOT NULL,
403 LastBurn TIMESTAMP WITHOUT TIME ZONE NOT NULL,
404 PRIMARY KEY (MediaId)
407 CREATE TABLE PathHierarchy
409 PathId INTEGER NOT NULL,
410 PPathId INTEGER NOT NULL,
411 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
414 CREATE INDEX pathhierarchy_ppathid ON PathHierarchy (PPathId);
416 CREATE TABLE PathVisibility
418 PathId INTEGER NOT NULL,
419 JobId INTEGER NOT NULL,
420 Size BIGINT DEFAULT 0,
421 Files INTEGER DEFAULT 0,
422 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
424 CREATE INDEX pathvisibility_jobid ON PathVisibility (JobId);
428 versionid INTEGER NOT NULL
431 CREATE TABLE Status (
432 JobStatus CHAR(1) NOT NULL,
433 JobStatusLong VARBYTE(128),
435 PRIMARY KEY (JobStatus)
440 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
441 ('C', 'Created, not yet running',15);
442 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
444 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
446 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
447 ('T', 'Completed successfully', 10);
448 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
449 ('E', 'Terminated with errors', 25);
450 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
451 ('e', 'Non-fatal error',20);
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('f', 'Fatal error',100);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
455 ('D', 'Verify found differences',15);
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
457 ('A', 'Canceled by user',90);
458 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
459 ('F', 'Waiting for Client',15);
460 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
461 ('S', 'Waiting for Storage daemon',15);
462 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
463 ('m', 'Waiting for new media');
464 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
465 ('M', 'Waiting for media mount',15);
466 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
467 ('s', 'Waiting for storage resource',15);
468 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
469 ('j', 'Waiting for job resource',15);
470 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
471 ('c', 'Waiting for client resource',15);
472 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
473 ('d', 'Waiting on maximum jobs',15);
474 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
475 ('t', 'Waiting on start time',15);
476 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
477 ('p', 'Waiting on higher priority jobs',15);
478 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
479 ('a', 'SD despooling attributes',15);
480 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
481 ('i', 'Doing batch insert file records',15);
483 INSERT INTO Version (VersionId) VALUES (12);
485 -- Make sure we have appropriate permissions
492 echo "Creation of Bacula Ingres tables succeeded."
494 echo "Creation of Bacula Ingres tables failed."