4 filenameid serial not null,
6 primary key (filenameid)
9 ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
10 CREATE UNIQUE INDEX filename_name_idx on filename (name);
14 pathid serial not null,
19 ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
20 CREATE UNIQUE INDEX path_name_idx on path (path);
24 fileid bigserial not null,
25 fileindex integer not null default 0,
26 jobid integer not null,
27 pathid integer not null,
28 filenameid integer not null,
29 markid integer not null default 0,
35 CREATE INDEX file_jobid_idx on file (jobid);
36 CREATE INDEX file_fp_idx on file (filenameid, pathid);
39 -- Add this if you have a good number of job
40 -- that run at the same time
41 -- ALTER SEQUENCE file_fileid_seq CACHE 1000;
44 -- Possibly add one or more of the following indexes
45 -- if your Verifies are too slow.
47 -- CREATE INDEX file_pathid_idx on file(pathid);
48 -- CREATE INDEX file_filenameid_idx on file(filenameid);
49 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
53 jobid serial not null,
56 type char(1) not null,
57 level char(1) not null,
58 clientid integer default 0,
59 jobstatus char(1) not null,
60 schedtime timestamp without time zone,
61 starttime timestamp without time zone,
62 endtime timestamp without time zone,
63 realendtime timestamp without time zone,
64 jobtdate bigint default 0,
65 volsessionid integer default 0,
66 volsessiontime integer default 0,
67 jobfiles integer default 0,
68 jobbytes bigint default 0,
69 readbytes bigint default 0,
70 joberrors integer default 0,
71 jobmissingfiles integer default 0,
72 poolid integer default 0,
73 filesetid integer default 0,
74 purgedfiles smallint default 0,
75 hasbase smallint default 0,
76 priorjobid integer default 0,
80 CREATE INDEX job_name_idx on job (name);
82 -- Create a table like Job for long term statistics
83 CREATE TABLE JobHisto (LIKE Job);
84 CREATE INDEX jobhisto_idx ON jobhisto ( starttime );
87 CREATE TABLE Location (
88 LocationId serial not null,
89 Location text not null,
90 Cost integer default 0,
92 primary key (LocationId)
98 filesetid serial not null,
99 fileset text not null,
101 createtime timestamp without time zone not null,
102 primary key (filesetid)
105 CREATE INDEX fileset_name_idx on fileset (fileset);
107 CREATE TABLE jobmedia
109 jobmediaid serial not null,
110 jobid integer not null,
111 mediaid integer not null,
112 firstindex integer default 0,
113 lastindex integer default 0,
114 startfile integer default 0,
115 endfile integer default 0,
116 startblock bigint default 0,
117 endblock bigint default 0,
118 volindex integer default 0,
119 copy integer default 0,
120 primary key (jobmediaid)
123 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
127 mediaid serial not null,
128 volumename text not null,
129 slot integer default 0,
130 poolid integer default 0,
131 mediatype text not null,
132 mediatypeid integer default 0,
133 labeltype integer default 0,
134 firstwritten timestamp without time zone,
135 lastwritten timestamp without time zone,
136 labeldate timestamp without time zone,
137 voljobs integer default 0,
138 volfiles integer default 0,
139 volblocks integer default 0,
140 volmounts integer default 0,
141 volbytes bigint default 0,
142 volparts integer default 0,
143 volerrors integer default 0,
144 volwrites integer default 0,
145 volcapacitybytes bigint default 0,
146 volstatus text not null
147 check (volstatus in ('Full','Archive','Append',
148 'Recycle','Purged','Read-Only','Disabled',
149 'Error','Busy','Used','Cleaning','Scratch')),
150 enabled smallint default 1,
151 recycle smallint default 0,
152 ActionOnPurge smallint default 0,
153 volretention bigint default 0,
154 voluseduration bigint default 0,
155 maxvoljobs integer default 0,
156 maxvolfiles integer default 0,
157 maxvolbytes bigint default 0,
158 inchanger smallint default 0,
159 StorageId integer default 0,
160 DeviceId integer default 0,
161 mediaaddressing smallint default 0,
162 volreadtime bigint default 0,
163 volwritetime bigint default 0,
164 endfile integer default 0,
165 endblock bigint default 0,
166 LocationId integer default 0,
167 recyclecount integer default 0,
168 initialwrite timestamp without time zone,
169 scratchpoolid integer default 0,
170 recyclepoolid integer default 0,
172 primary key (mediaid)
175 create unique index media_volumename_id on media (volumename);
178 CREATE TABLE MediaType (
180 MediaType TEXT NOT NULL,
181 ReadOnly INTEGER DEFAULT 0,
182 PRIMARY KEY(MediaTypeId)
185 CREATE TABLE Storage (
188 AutoChanger INTEGER DEFAULT 0,
189 PRIMARY KEY(StorageId)
192 CREATE TABLE Device (
195 MediaTypeId INTEGER NOT NULL,
196 StorageId INTEGER NOT NULL,
197 DevMounts INTEGER NOT NULL DEFAULT 0,
198 DevReadBytes BIGINT NOT NULL DEFAULT 0,
199 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
200 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
201 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
202 DevReadTime BIGINT NOT NULL DEFAULT 0,
203 DevWriteTime BIGINT NOT NULL DEFAULT 0,
204 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
205 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
206 CleaningDate timestamp without time zone,
207 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
208 PRIMARY KEY(DeviceId)
214 poolid serial not null,
216 numvols integer default 0,
217 maxvols integer default 0,
218 useonce smallint default 0,
219 usecatalog smallint default 0,
220 acceptanyvolume smallint default 0,
221 volretention bigint default 0,
222 voluseduration bigint default 0,
223 maxvoljobs integer default 0,
224 maxvolfiles integer default 0,
225 maxvolbytes bigint default 0,
226 autoprune smallint default 0,
227 recycle smallint default 0,
228 ActionOnPurge smallint default 0,
230 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
231 labeltype integer default 0,
232 labelformat text not null,
233 enabled smallint default 1,
234 scratchpoolid integer default 0,
235 recyclepoolid integer default 0,
236 NextPoolId integer default 0,
237 MigrationHighBytes BIGINT DEFAULT 0,
238 MigrationLowBytes BIGINT DEFAULT 0,
239 MigrationTime BIGINT DEFAULT 0,
243 CREATE INDEX pool_name_idx on pool (name);
247 clientid serial not null,
250 autoprune smallint default 0,
251 fileretention bigint default 0,
252 jobretention bigint default 0,
253 primary key (clientid)
256 create unique index client_name_idx on client (name);
260 LogId serial not null,
261 JobId integer not null,
262 Time timestamp without time zone,
263 LogText text not null,
266 create index log_name_idx on Log (JobId);
268 CREATE TABLE LocationLog (
269 LocLogId SERIAL NOT NULL,
270 Date timestamp without time zone,
271 Comment TEXT NOT NULL,
272 MediaId INTEGER DEFAULT 0,
273 LocationId INTEGER DEFAULT 0,
274 newvolstatus text not null
275 check (newvolstatus in ('Full','Archive','Append',
276 'Recycle','Purged','Read-Only','Disabled',
277 'Error','Busy','Used','Cleaning','Scratch')),
279 PRIMARY KEY(LocLogId)
284 CREATE TABLE counters
286 counter text not null,
287 minvalue integer default 0,
288 maxvalue integer default 0,
289 currentvalue integer default 0,
290 wrapcounter text not null,
291 primary key (counter)
296 CREATE TABLE basefiles
298 baseid serial not null,
299 jobid integer not null,
300 fileid bigint not null,
306 CREATE TABLE unsavedfiles
308 UnsavedId integer not null,
309 jobid integer not null,
310 pathid integer not null,
311 filenameid integer not null,
312 primary key (UnsavedId)
315 CREATE TABLE CDImages
317 MediaId integer not null,
318 LastBurn timestamp without time zone not null,
319 primary key (MediaId)
325 versionid integer not null
328 CREATE TABLE Status (
329 JobStatus CHAR(1) NOT NULL,
331 PRIMARY KEY (JobStatus)
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335 ('C', 'Created, not yet running');
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
340 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341 ('T', 'Completed successfully');
342 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343 ('E', 'Terminated with errors');
344 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345 ('e', 'Non-fatal error');
346 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347 ('f', 'Fatal error');
348 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
349 ('D', 'Verify found differences');
350 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
351 ('A', 'Canceled by user');
352 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
353 ('F', 'Waiting for Client');
354 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
355 ('S', 'Waiting for Storage daemon');
356 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
357 ('m', 'Waiting for new media');
358 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
359 ('M', 'Waiting for media mount');
360 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
361 ('s', 'Waiting for storage resource');
362 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
363 ('j', 'Waiting for job resource');
364 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
365 ('c', 'Waiting for client resource');
366 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
367 ('d', 'Waiting on maximum jobs');
368 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
369 ('t', 'Waiting on start time');
370 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
371 ('p', 'Waiting on higher priority jobs');
372 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
373 ('a', 'SD despooling attributes');
374 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
375 ('i', 'Doing batch insert file records');
377 INSERT INTO Version (VersionId) VALUES (11);
379 -- Make sure we have appropriate permissions