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 -- When using batch insert make sure you adhere to the following
14 -- minimum Ingres version:
16 -- Ingres 9.2 or higher with the fix for bug 123652 and bug 117256
17 -- For Solaris x86 this is Ingres 9.2. + patch 13785 or higher
18 -- For Linux x86_64 this is Ingres 9.2 + patch 13790 or higher
22 -- As it seems the upper limit for a VARCHAR/VARBYTE column is 32000 bytes
23 -- We could have used a BLOB as storage type which can go up to 2 Gb but
24 -- you cannot create indexes based on a BLOB (and we don't seem to be able
25 -- to limit the part of the field which should be used for the index to lets
26 -- say 255 chars like we can in MySQL) and the storage for a BLOB is also far
27 -- from optimal so for now we decided that the upper limit for File and Path
28 -- fields is the 32000 bytes.
30 -- To use the big VARBYTE colums we need the bigger page sizes enabled in
31 -- ingres (which at this time supports 2K, 4K, 8K, 16K, 32K, 64K)
32 -- Make sure you have the following settings in your
33 -- ${II_SYSTEM}/ingres/files/config.dat
35 -- ii.<nodename>.dbms.private.*.cache.p8k_status: ON
36 -- ii.<nodename>.dbms.private.*.cache.p16k_status: ON
37 -- ii.<nodename>.dbms.private.*.cache.p32k_status: ON
38 -- ii.<nodename>.dbms.private.*.cache.p64k_status: ON
39 -- ii.<nodename>.rcp.dmf_cache_size8k: 200
40 -- ii.<nodename>.rcp.dmf_cache_size16k: 200
41 -- ii.<nodename>.rcp.dmf_cache_size32k: 200
42 -- ii.<nodename>.rcp.dmf_cache_size64k: 200
47 CREATE SEQUENCE Filename_Seq;
50 FilenameId INTEGER NOT NULL DEFAULT Filename_Seq.nextval,
51 Name VARBYTE(32000) NOT NULL,
52 PRIMARY KEY (FilenameId)
55 CREATE UNIQUE INDEX (filename_name_idx ON filename (Name) WITH STRUCTURE=HASH,PAGE_SIZE=32768);
57 CREATE SEQUENCE Path_Seq;
60 Pathid INTEGER NOT NULL DEFAULT Path_Seq.nextval,
61 Path VARBYTE(32000) NOT NULL,
65 CREATE UNIQUE INDEX (path_name_idx ON path (Path) WITH STRUCTURE=HASH,PAGE_SIZE=32768);
67 CREATE SEQUENCE File_Seq;
70 FileId BIGINT NOT NULL DEFAULT File_Seq.nextval,
71 FileIndex INTEGER NOT NULL DEFAULT 0,
72 JobId INTEGER NOT NULL,
73 PathId INTEGER NOT NULL,
74 FilenameId INTEGER NOT NULL,
75 DeltaSeq INTEGER NOT NULL DEFAULT 0,
76 MarkId INTEGER NOT NULL DEFAULT 0,
77 LStat VARBYTE(255) NOT NULL,
78 Md5 VARBYTE(255) NOT NULL,
82 CREATE INDEX file_jpfid_idx ON File (jobid, pathid, filenameid);
84 -- If you need performances, you can remove this index
85 -- the database engine is able to use the composite index
86 -- to find all records with a given JobId
87 CREATE INDEX file_jobid_idx ON File(jobid);
89 CREATE SEQUENCE RestoreObject_Seq;
90 CREATE TABLE RestoreObject (
91 RestoreObjectId INTEGER NOT NULL DEFAULT RestoreObject_Seq.nextval,
92 ObjectName VARBYTE(128) NOT NULL,
93 RestoreObject BLOB NOT NULL,
94 PluginName VARBYTE(128) NOT NULL,
95 ObjectLength INTEGER DEFAULT 0,
96 ObjectFullLength INTEGER DEFAULT 0,
97 ObjectIndex INTEGER DEFAULT 0,
98 ObjectType INTEGER DEFAULT 0,
99 FileIndex INTEGER DEFAULT 0,
101 ObjectCompression INTEGER DEFAULT 0,
102 PRIMARY KEY (RestoreObjectId)
105 CREATE INDEX restore_jobid_idx on RestoreObject(JobId);
107 CREATE SEQUENCE Job_Seq;
110 JobId INTEGER NOT NULL DEFAULT Job_Seq.nextval,
111 Job VARBYTE(128) NOT NULL,
112 Name VARBYTE(128) NOT NULL,
113 Type CHAR(1) NOT NULL,
114 Level CHAR(1) NOT NULL,
115 ClientId INTEGER DEFAULT 0,
116 JobStatus CHAR(1) NOT NULL,
117 SchedTime TIMESTAMP WITHOUT TIME ZONE,
118 StartTime TIMESTAMP WITHOUT TIME ZONE,
119 EndTime TIMESTAMP WITHOUT TIME ZONE,
120 RealEndTime TIMESTAMP WITHOUT TIME ZONE,
121 JobTDate BIGINT DEFAULT 0,
122 VolSessionId INTEGER DEFAULT 0,
123 volSessionTime INTEGER DEFAULT 0,
124 JobFiles INTEGER DEFAULT 0,
125 JobBytes BIGINT DEFAULT 0,
126 ReadBytes BIGINT DEFAULT 0,
127 JobErrors INTEGER DEFAULT 0,
128 JobMissingFiles INTEGER DEFAULT 0,
129 PoolId INTEGER DEFAULT 0,
130 FilesetId INTEGER DEFAULT 0,
131 PriorJobid INTEGER DEFAULT 0,
132 PurgedFiles SMALLINT DEFAULT 0,
133 HasBase SMALLINT DEFAULT 0,
134 HasCache SMALLINT DEFAULT 0,
135 Reviewed SMALLINT DEFAULT 0,
136 Comment VARBYTE(4096),
140 CREATE INDEX job_name_idx ON Job (Name);
142 -- Create a table like Job for long term statistics
143 CREATE SEQUENCE JobHisto_Seq;
144 CREATE TABLE JobHisto
146 JobId INTEGER NOT NULL DEFAULT JobHisto_Seq.nextval,
147 Job VARBYTE(128) NOT NULL,
148 Name VARBYTE(128) NOT NULL,
149 Type CHAR(1) NOT NULL,
150 Level CHAR(1) NOT NULL,
151 ClientId INTEGER DEFAULT 0,
152 JobStatus CHAR(1) NOT NULL,
153 SchedTime TIMESTAMP WITHOUT TIME ZONE,
154 StartTime TIMESTAMP WITHOUT TIME ZONE,
155 EndTime TIMESTAMP WITHOUT TIME ZONE,
156 RealEndTime TIMESTAMP WITHOUT TIME ZONE,
157 JobTDate BIGINT DEFAULT 0,
158 VolSessionId INTEGER DEFAULT 0,
159 volSessionTime INTEGER DEFAULT 0,
160 JobFiles INTEGER DEFAULT 0,
161 JobBytes BIGINT DEFAULT 0,
162 ReadBytes BIGINT DEFAULT 0,
163 JobErrors INTEGER DEFAULT 0,
164 JobMissingFiles INTEGER DEFAULT 0,
165 PoolId INTEGER DEFAULT 0,
166 FilesetId INTEGER DEFAULT 0,
167 PriorJobid INTEGER DEFAULT 0,
168 PurgedFiles SMALLINT DEFAULT 0,
169 HasBase SMALLINT DEFAULT 0,
170 HasCache SMALLINT DEFAULT 0,
171 Reviewed SMALLINT DEFAULT 0,
172 Comment VARBYTE(4096),
176 CREATE INDEX jobhisto_idx ON JobHisto (StartTime);
178 CREATE SEQUENCE Location_Seq;
179 CREATE TABLE Location (
180 LocationId INTEGER NOT NULL DEFAULT Location_Seq.nextval,
181 Location VARBYTE(128) NOT NULL,
182 Cost INTEGER DEFAULT 0,
184 PRIMARY KEY (LocationId)
187 CREATE SEQUENCE Fileset_Seq;
190 FilesetId INTEGER NOT NULL DEFAULT Fileset_Seq.nextval,
191 Fileset VARBYTE(128) NOT NULL,
192 Md5 VARBYTE(128) NOT NULL,
193 Createtime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
194 PRIMARY KEY (filesetid)
197 CREATE INDEX fileset_name_idx ON Fileset (fileset);
199 CREATE SEQUENCE JobMedia_Seq;
200 CREATE TABLE JobMedia
202 JobMediaId INTEGER NOT NULL DEFAULT JobMedia_Seq.nextval,
203 JobId INTEGER NOT NULL,
204 MediaId INTEGER NOT NULL,
205 FirstIndex INTEGER DEFAULT 0,
206 LastIndex INTEGER DEFAULT 0,
207 StartFile INTEGER DEFAULT 0,
208 EndFile INTEGER DEFAULT 0,
209 StartBlock BIGINT DEFAULT 0,
210 EndBlock BIGINT DEFAULT 0,
211 VolIndex INTEGER DEFAULT 0,
212 PRIMARY KEY (jobmediaid)
215 CREATE INDEX job_media_job_id_media_id_idx ON JobMedia (jobid, mediaid);
217 CREATE SEQUENCE Media_Seq;
220 MediaId INTEGER NOT NULL DEFAULT Media_Seq.nextval,
221 VolumeName VARBYTE(128) NOT NULL,
222 Slot INTEGER DEFAULT 0,
223 PoolId INTEGER DEFAULT 0,
224 MediaType VARBYTE(128) NOT NULL,
225 MediaTypeId INTEGER DEFAULT 0,
226 LabelType INTEGER DEFAULT 0,
227 FirstWritten TIMESTAMP WITHOUT TIME ZONE,
228 LastWritten TIMESTAMP WITHOUT TIME ZONE,
229 LabelDate TIMESTAMP WITHOUT TIME ZONE,
230 VolJobs INTEGER DEFAULT 0,
231 VolFiles INTEGER DEFAULT 0,
232 VolBlocks INTEGER DEFAULT 0,
233 VolMounts INTEGER DEFAULT 0,
234 VolBytes BIGINT DEFAULT 0,
235 VolParts INTEGER DEFAULT 0,
236 VolErrors INTEGER DEFAULT 0,
237 VolWrites INTEGER DEFAULT 0,
238 VolCapacitybytes BIGINT DEFAULT 0,
239 VolStatus VARBYTE(128) NOT NULL
240 CHECK (volstatus in ('Full','Archive','Append',
241 'Recycle','Purged','Read-Only','Disabled',
242 'Error','Busy','Used','Cleaning','Scratch')),
243 Enabled SMALLINT DEFAULT 1,
244 Recycle SMALLINT DEFAULT 0,
245 ActionOnPurge SMALLINT DEFAULT 0,
246 VolRetention BIGINT DEFAULT 0,
247 VolUseDuration BIGINT DEFAULT 0,
248 MaxVolJobs INTEGER DEFAULT 0,
249 MaxVolFiles INTEGER DEFAULT 0,
250 MaxVolBytes BIGINT DEFAULT 0,
251 InChanger SMALLINT DEFAULT 0,
252 StorageId BIGINT DEFAULT 0,
253 DeviceId INTEGER DEFAULT 0,
254 MediaAddressing SMALLINT DEFAULT 0,
255 VolReadTime BIGINT DEFAULT 0,
256 VolWriteTime BIGINT DEFAULT 0,
257 EndFile INTEGER DEFAULT 0,
258 EndBlock BIGINT DEFAULT 0,
259 LocationId INTEGER DEFAULT 0,
260 RecycleCount INTEGER DEFAULT 0,
261 InitialWrite TIMESTAMP WITHOUT TIME ZONE,
262 ScratchPoolId INTEGER DEFAULT 0,
263 RecyclePoolId INTEGER DEFAULT 0,
264 Comment VARBYTE(4096),
265 PRIMARY KEY (mediaid)
268 CREATE UNIQUE INDEX media_volumename_id ON Media (VolumeName);
270 CREATE SEQUENCE MediaType_Seq;
271 CREATE TABLE MediaType (
272 MediaTypeId INTEGER NOT NULL DEFAULT MediaType_Seq.nextval,
273 MediaType VARBYTE(128) NOT NULL,
274 ReadOnly INTEGER NOT NULL DEFAULT 0,
275 PRIMARY KEY (MediaTypeId)
278 CREATE SEQUENCE Storage_Seq;
279 CREATE TABLE Storage (
280 StorageId INTEGER NOT NULL DEFAULT Storage_Seq.nextval,
281 Name VARBYTE(128) NOT NULL,
282 AutoChanger INTEGER NOT NULL DEFAULT 0,
283 PRIMARY KEY (StorageId)
286 CREATE SEQUENCE Device_Seq;
287 CREATE TABLE Device (
288 DeviceId INTEGER NOT NULL DEFAULT Device_Seq.nextval,
289 Name VARBYTE(128) NOT NULL,
290 MediaTypeId INTEGER NOT NULL,
291 StorageId INTEGER NOT NULL,
292 DevMounts INTEGER NOT NULL DEFAULT 0,
293 DevReadBytes BIGINT NOT NULL DEFAULT 0,
294 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
295 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
296 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
297 DevReadTime BIGINT NOT NULL DEFAULT 0,
298 DevWriteTime BIGINT NOT NULL DEFAULT 0,
299 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
300 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
301 CleaningDate TIMESTAMP WITHOUT TIME ZONE,
302 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
303 PRIMARY KEY (DeviceId)
306 CREATE SEQUENCE Pool_Seq;
309 PoolId INTEGER NOT NULL DEFAULT pool_Seq.nextval,
310 Name VARBYTE(128) NOT NULL,
311 NumVols INTEGER DEFAULT 0,
312 MaxVols INTEGER DEFAULT 0,
313 UseOnce SMALLINT DEFAULT 0,
314 UseCatalog SMALLINT DEFAULT 0,
315 AcceptAnyVolume SMALLINT DEFAULT 0,
316 VolRetention BIGINT DEFAULT 0,
317 VolUseDuration BIGINT DEFAULT 0,
318 MaxVolJobs INTEGER DEFAULT 0,
319 MaxVolFiles INTEGER DEFAULT 0,
320 MaxVolBytes BIGINT DEFAULT 0,
321 AutoPrune SMALLINT DEFAULT 0,
322 Recycle SMALLINT DEFAULT 0,
323 ActionOnPurge SMALLINT DEFAULT 0,
325 CHECK (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
326 LabelType INTEGER DEFAULT 0,
327 LabelFormat VARBYTE(128) NOT NULL,
328 Enabled SMALLINT DEFAULT 1,
329 ScratchPoolId INTEGER DEFAULT 0,
330 RecyclePoolId INTEGER DEFAULT 0,
331 NextPoolId INTEGER DEFAULT 0,
332 MigrationHighBytes BIGINT DEFAULT 0,
333 MigrationLowBytes BIGINT DEFAULT 0,
334 MigrationTime BIGINT DEFAULT 0,
338 CREATE INDEX pool_name_idx ON Pool (name);
340 CREATE SEQUENCE Client_Seq;
343 ClientId INTEGER NOT NULL DEFAULT Client_Seq.nextval,
344 Name VARBYTE(128) NOT NULL,
345 Uname VARBYTE(256) NOT NULL,
346 AutoPrune SMALLINT DEFAULT 0,
347 FileRetention BIGINT DEFAULT 0,
348 JobRetention BIGINT DEFAULT 0,
349 PRIMARY KEY (clientid)
352 CREATE UNIQUE INDEX client_name_idx ON Client (Name);
354 CREATE SEQUENCE Log_Seq;
357 LogId INTEGER NOT NULL DEFAULT Log_Seq.nextval,
358 JobId INTEGER NOT NULL,
359 Time TIMESTAMP WITHOUT TIME ZONE,
360 LogText VARBYTE(4096) NOT NULL,
363 CREATE INDEX log_name_idx ON Log (JobId);
365 CREATE SEQUENCE LocationLog_Seq;
366 CREATE TABLE LocationLog (
367 LocLogId INTEGER NOT NULL DEFAULT LocationLog_Seq.nextval,
368 Date TIMESTAMP WITHOUT TIME ZONE,
369 Comment VARBYTE(4096) NOT NULL,
370 MediaId INTEGER DEFAULT 0,
371 LocationId INTEGER DEFAULT 0,
372 NewVolStatus VARBYTE(32) NOT NULL
373 CHECK (newvolstatus in ('Full','Archive','Append',
374 'Recycle','Purged','Read-Only','Disabled',
375 'Error','Busy','Used','Cleaning','Scratch')),
377 PRIMARY KEY (LocLogId)
380 CREATE TABLE Counters
382 Counter VARBYTE(128) NOT NULL,
383 MinValue INTEGER DEFAULT 0,
384 MaxValue INTEGER DEFAULT 0,
385 CurrentValue INTEGER DEFAULT 0,
386 WrapCounter VARBYTE(128) NOT NULL,
387 PRIMARY KEY (counter)
390 CREATE SEQUENCE BaseFiles_Seq;
391 CREATE TABLE BaseFiles
393 BaseId INTEGER NOT NULL DEFAULT BaseFiles_Seq.nextval,
394 JobId INTEGER NOT NULL,
395 FileId BIGINT NOT NULL,
401 CREATE INDEX basefiles_jobid_idx ON BaseFiles (JobId);
403 CREATE TABLE unsavedfiles
405 UnsavedId INTEGER NOT NULL,
406 JobId INTEGER NOT NULL,
407 PathId INTEGER NOT NULL,
408 FilenameId INTEGER NOT NULL,
409 PRIMARY KEY (UnsavedId)
412 CREATE TABLE CDImages
414 MediaId INTEGER NOT NULL,
415 LastBurn TIMESTAMP WITHOUT TIME ZONE NOT NULL,
416 PRIMARY KEY (MediaId)
419 CREATE TABLE PathHierarchy
421 PathId INTEGER NOT NULL,
422 PPathId INTEGER NOT NULL,
423 CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
426 CREATE INDEX pathhierarchy_ppathid ON PathHierarchy (PPathId);
428 CREATE TABLE PathVisibility
430 PathId INTEGER NOT NULL,
431 JobId INTEGER NOT NULL,
432 Size BIGINT DEFAULT 0,
433 Files INTEGER DEFAULT 0,
434 CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
436 CREATE INDEX pathvisibility_jobid ON PathVisibility (JobId);
440 versionid INTEGER NOT NULL
443 CREATE TABLE Status (
444 JobStatus CHAR(1) NOT NULL,
445 JobStatusLong VARBYTE(128),
447 PRIMARY KEY (JobStatus)
452 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
453 ('C', 'Created, not yet running',15);
454 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
456 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
458 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
459 ('T', 'Completed successfully', 10);
460 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
461 ('E', 'Terminated with errors', 25);
462 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
463 ('e', 'Non-fatal error',20);
464 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
465 ('f', 'Fatal error',100);
466 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
467 ('D', 'Verify found differences',15);
468 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
469 ('A', 'Canceled by user',90);
470 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
471 ('F', 'Waiting for Client',15);
472 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
473 ('S', 'Waiting for Storage daemon',15);
474 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
475 ('m', 'Waiting for new media');
476 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
477 ('M', 'Waiting for media mount',15);
478 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
479 ('s', 'Waiting for storage resource',15);
480 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
481 ('j', 'Waiting for job resource',15);
482 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
483 ('c', 'Waiting for client resource',15);
484 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
485 ('d', 'Waiting on maximum jobs',15);
486 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
487 ('t', 'Waiting on start time',15);
488 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
489 ('p', 'Waiting on higher priority jobs',15);
490 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
491 ('a', 'SD despooling attributes',15);
492 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
493 ('i', 'Doing batch insert file records',15);
495 INSERT INTO Version (VersionId) VALUES (@BDB_VERSION@);
497 -- Make sure we have appropriate permissions
504 echo "Creation of Bacula Ingres tables succeeded."
506 echo "Creation of Bacula Ingres tables failed."