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