1 CREATE TABLE filename
\r
3 filenameid serial not null,
\r
5 primary key (filenameid)
\r
8 CREATE INDEX filename_name_idx on filename (name);
\r
12 pathid serial not null,
\r
14 primary key (pathid)
\r
17 CREATE INDEX path_name_idx on path (path);
\r
21 fileid serial not null,
\r
22 fileindex integer not null default 0,
\r
23 jobid integer not null,
\r
24 pathid integer not null,
\r
25 filenameid integer not null,
\r
26 markid integer not null default 0,
\r
27 lstat text not null,
\r
29 primary key (fileid)
\r
32 CREATE INDEX file_jobid_idx on file (jobid);
\r
33 CREATE INDEX file_fp_idx on file (filenameid, pathid);
\r
36 -- Possibly add one or more of the following indexes
\r
37 -- if your Verifies are too slow.
\r
39 -- CREATE INDEX file_pathid_idx on file(pathid);
\r
40 -- CREATE INDEX file_filenameid_idx on file(filenameid);
\r
41 -- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
\r
45 jobid serial not null,
\r
48 type char(1) not null,
\r
49 level char(1) not null,
\r
50 clientid integer default 0,
\r
51 jobstatus char(1) not null,
\r
52 schedtime timestamp without time zone,
\r
53 starttime timestamp without time zone,
\r
54 endtime timestamp without time zone,
\r
55 realendtime timestamp without time zone,
\r
56 jobtdate bigint default 0,
\r
57 volsessionid integer default 0,
\r
58 volsessiontime integer default 0,
\r
59 jobfiles integer default 0,
\r
60 jobbytes bigint default 0,
\r
61 joberrors integer default 0,
\r
62 jobmissingfiles integer default 0,
\r
63 poolid integer default 0,
\r
64 filesetid integer default 0,
\r
65 purgedfiles smallint default 0,
\r
66 hasbase smallint default 0,
\r
67 priorjobid integer default 0,
\r
71 CREATE INDEX job_name_idx on job (name);
\r
73 CREATE TABLE Location (
\r
74 LocationId serial not null,
\r
75 Location text not null,
\r
76 Cost integer default 0,
\r
78 primary key (LocationId)
\r
82 CREATE TABLE fileset
\r
84 filesetid serial not null,
\r
85 fileset text not null,
\r
87 createtime timestamp without time zone not null,
\r
88 primary key (filesetid)
\r
91 CREATE INDEX fileset_name_idx on fileset (fileset);
\r
93 CREATE TABLE jobmedia
\r
95 jobmediaid serial not null,
\r
96 jobid integer not null,
\r
97 mediaid integer not null,
\r
98 firstindex integer default 0,
\r
99 lastindex integer default 0,
\r
100 startfile integer default 0,
\r
101 endfile integer default 0,
\r
102 startblock bigint default 0,
\r
103 endblock bigint default 0,
\r
104 volindex integer default 0,
\r
105 copy integer default 0,
\r
106 primary key (jobmediaid)
\r
109 CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
\r
113 mediaid serial not null,
\r
114 volumename text not null,
\r
115 slot integer default 0,
\r
116 poolid integer default 0,
\r
117 mediatype text not null,
\r
118 mediatypeid integer default 0,
\r
119 labeltype integer default 0,
\r
120 firstwritten timestamp without time zone,
\r
121 lastwritten timestamp without time zone,
\r
122 labeldate timestamp without time zone,
\r
123 voljobs integer default 0,
\r
124 volfiles integer default 0,
\r
125 volblocks integer default 0,
\r
126 volmounts integer default 0,
\r
127 volbytes bigint default 0,
\r
128 volparts integer default 0,
\r
129 volerrors integer default 0,
\r
130 volwrites integer default 0,
\r
131 volcapacitybytes bigint default 0,
\r
132 volstatus text not null
\r
133 check (volstatus in ('Full','Archive','Append',
\r
134 'Recycle','Purged','Read-Only','Disabled',
\r
135 'Error','Busy','Used','Cleaning','Scratch')),
\r
136 enabled smallint default 1,
\r
137 recycle smallint default 0,
\r
138 volretention bigint default 0,
\r
139 voluseduration bigint default 0,
\r
140 maxvoljobs integer default 0,
\r
141 maxvolfiles integer default 0,
\r
142 maxvolbytes bigint default 0,
\r
143 inchanger smallint default 0,
\r
144 StorageId integer default 0,
\r
145 DeviceId integer default 0,
\r
146 mediaaddressing smallint default 0,
\r
147 volreadtime bigint default 0,
\r
148 volwritetime bigint default 0,
\r
149 endfile integer default 0,
\r
150 endblock bigint default 0,
\r
151 LocationId integer default 0,
\r
152 recyclecount integer default 0,
\r
153 initialwrite timestamp without time zone,
\r
154 scratchpoolid integer default 0,
\r
155 recyclepoolid integer default 0,
\r
157 primary key (mediaid)
\r
160 create unique index media_volumename_id on media (volumename);
\r
163 CREATE TABLE MediaType (
\r
164 MediaTypeId SERIAL,
\r
165 MediaType TEXT NOT NULL,
\r
166 ReadOnly INTEGER DEFAULT 0,
\r
167 PRIMARY KEY(MediaTypeId)
\r
170 CREATE TABLE Storage (
\r
172 Name TEXT NOT NULL,
\r
173 AutoChanger INTEGER DEFAULT 0,
\r
174 PRIMARY KEY(StorageId)
\r
177 CREATE TABLE Device (
\r
179 Name TEXT NOT NULL,
\r
180 MediaTypeId INTEGER NOT NULL,
\r
181 StorageId INTEGER NOT NULL,
\r
182 DevMounts INTEGER NOT NULL DEFAULT 0,
\r
183 DevReadBytes BIGINT NOT NULL DEFAULT 0,
\r
184 DevWriteBytes BIGINT NOT NULL DEFAULT 0,
\r
185 DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
\r
186 DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
\r
187 DevReadTime BIGINT NOT NULL DEFAULT 0,
\r
188 DevWriteTime BIGINT NOT NULL DEFAULT 0,
\r
189 DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
\r
190 DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
\r
191 CleaningDate timestamp without time zone,
\r
192 CleaningPeriod BIGINT NOT NULL DEFAULT 0,
\r
193 PRIMARY KEY(DeviceId)
\r
199 poolid serial not null,
\r
200 name text not null,
\r
201 numvols integer default 0,
\r
202 maxvols integer default 0,
\r
203 useonce smallint default 0,
\r
204 usecatalog smallint default 0,
\r
205 acceptanyvolume smallint default 0,
\r
206 volretention bigint default 0,
\r
207 voluseduration bigint default 0,
\r
208 maxvoljobs integer default 0,
\r
209 maxvolfiles integer default 0,
\r
210 maxvolbytes bigint default 0,
\r
211 autoprune smallint default 0,
\r
212 recycle smallint default 0,
\r
214 check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
\r
215 labeltype integer default 0,
\r
216 labelformat text not null,
\r
217 enabled smallint default 1,
\r
218 scratchpoolid integer default 0,
\r
219 recyclepoolid integer default 0,
\r
220 NextPoolId integer default 0,
\r
221 MigrationHighBytes BIGINT DEFAULT 0,
\r
222 MigrationLowBytes BIGINT DEFAULT 0,
\r
223 MigrationTime BIGINT DEFAULT 0,
\r
224 primary key (poolid)
\r
227 CREATE INDEX pool_name_idx on pool (name);
\r
229 CREATE TABLE client
\r
231 clientid serial not null,
\r
232 name text not null,
\r
233 uname text not null,
\r
234 autoprune smallint default 0,
\r
235 fileretention bigint default 0,
\r
236 jobretention bigint default 0,
\r
237 primary key (clientid)
\r
240 create unique index client_name_idx on client (name);
\r
244 LogId serial not null,
\r
245 JobId integer not null,
\r
246 Time timestamp without time zone,
\r
247 LogText text not null,
\r
248 primary key (LogId)
\r
250 create index log_name_idx on Log (JobId);
\r
252 CREATE TABLE LocationLog (
\r
253 LocLogId SERIAL NOT NULL,
\r
254 Date timestamp without time zone,
\r
255 Comment TEXT NOT NULL,
\r
256 MediaId INTEGER DEFAULT 0,
\r
257 LocationId INTEGER DEFAULT 0,
\r
258 newvolstatus text not null
\r
259 check (newvolstatus in ('Full','Archive','Append',
\r
260 'Recycle','Purged','Read-Only','Disabled',
\r
261 'Error','Busy','Used','Cleaning','Scratch')),
\r
262 newenabled smallint,
\r
263 PRIMARY KEY(LocLogId)
\r
268 CREATE TABLE counters
\r
270 counter text not null,
\r
271 minvalue integer default 0,
\r
272 maxvalue integer default 0,
\r
273 currentvalue integer default 0,
\r
274 wrapcounter text not null,
\r
275 primary key (counter)
\r
280 CREATE TABLE basefiles
\r
282 baseid serial not null,
\r
283 jobid integer not null,
\r
284 fileid integer not null,
\r
285 fileindex integer ,
\r
286 basejobid integer ,
\r
287 primary key (baseid)
\r
290 CREATE TABLE unsavedfiles
\r
292 UnsavedId integer not null,
\r
293 jobid integer not null,
\r
294 pathid integer not null,
\r
295 filenameid integer not null,
\r
296 primary key (UnsavedId)
\r
299 CREATE TABLE CDImages
\r
301 MediaId integer not null,
\r
302 LastBurn timestamp without time zone not null,
\r
303 primary key (MediaId)
\r
307 CREATE TABLE version
\r
309 versionid integer not null
\r
312 CREATE TABLE Status (
\r
313 JobStatus CHAR(1) NOT NULL,
\r
314 JobStatusLong TEXT,
\r
315 PRIMARY KEY (JobStatus)
\r
318 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
319 ('C', 'Created, not yet running');
\r
320 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
322 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
324 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
325 ('T', 'Completed successfully');
\r
326 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
327 ('E', 'Terminated with errors');
\r
328 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
329 ('e', 'Non-fatal error');
\r
330 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
331 ('f', 'Fatal error');
\r
332 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
333 ('D', 'Verify found differences');
\r
334 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
335 ('A', 'Canceled by user');
\r
336 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
337 ('F', 'Waiting for Client');
\r
338 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
339 ('S', 'Waiting for Storage daemon');
\r
340 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
341 ('m', 'Waiting for new media');
\r
342 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
343 ('M', 'Waiting for media mount');
\r
344 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
345 ('s', 'Waiting for storage resource');
\r
346 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
347 ('j', 'Waiting for job resource');
\r
348 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
349 ('c', 'Waiting for client resource');
\r
350 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
351 ('d', 'Waiting on maximum jobs');
\r
352 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
353 ('t', 'Waiting on start time');
\r
354 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
\r
355 ('p', 'Waiting on higher priority jobs');
\r
358 INSERT INTO Version (VersionId) VALUES (10);
\r
360 -- Make sure we have appropriate permissions
\r