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