1
2CREATE TABLE filename
3(
4    filenameid        serial      not null,
5    name              text        not null,
6    primary key (filenameid)
7);
8
9ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
10CREATE UNIQUE INDEX filename_name_idx on filename (name);
11
12CREATE TABLE path
13(
14    pathid            serial      not null,
15    path              text        not null,
16    primary key (pathid)
17);
18
19ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
20CREATE UNIQUE INDEX path_name_idx on path (path);
21
22CREATE TABLE file
23(
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,
30    lstat             text        not null,
31    md5               text        not null,
32    primary key (fileid)
33);
34
35CREATE INDEX file_jobid_idx on file (jobid);
36CREATE INDEX file_fp_idx on file (filenameid, pathid);
37
38--
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;
42
43--
44-- Possibly add one or more of the following indexes
45--  if your Verifies are too slow.
46--
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);
50
51CREATE TABLE job
52(
53    jobid             serial      not null,
54    job               text        not null,
55    name              text        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,
77    primary key (jobid)
78);
79
80CREATE INDEX job_name_idx on job (name);
81
82-- Create a table like Job for long term statistics
83CREATE TABLE JobHisto (LIKE Job);
84CREATE INDEX jobhisto_idx ON jobhisto ( starttime );
85
86
87CREATE TABLE Location (
88   LocationId         serial      not null,
89   Location           text        not null,
90   Cost               integer     default 0,
91   Enabled            smallint,
92   primary key (LocationId)
93);
94
95
96CREATE TABLE fileset
97(
98    filesetid         serial      not null,
99    fileset           text        not null,
100    md5               text        not null,
101    createtime        timestamp without time zone not null,
102    primary key (filesetid)
103);
104
105CREATE INDEX fileset_name_idx on fileset (fileset);
106
107CREATE TABLE jobmedia
108(
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)
121);
122
123CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
124
125CREATE TABLE media
126(
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,
171    comment           text,
172    primary key (mediaid)
173);
174
175create unique index media_volumename_id on media (volumename);
176
177
178CREATE TABLE MediaType (
179   MediaTypeId SERIAL,
180   MediaType TEXT NOT NULL,
181   ReadOnly INTEGER DEFAULT 0,
182   PRIMARY KEY(MediaTypeId)
183   );
184
185CREATE TABLE Storage (
186   StorageId SERIAL,
187   Name TEXT NOT NULL,
188   AutoChanger INTEGER DEFAULT 0,
189   PRIMARY KEY(StorageId)
190   );
191
192CREATE TABLE Device (
193   DeviceId SERIAL,
194   Name TEXT NOT NULL,
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)
209   );
210
211
212CREATE TABLE pool
213(
214    poolid            serial      not null,
215    name              text        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,
229    pooltype          text
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,
240    primary key (poolid)
241);
242
243CREATE INDEX pool_name_idx on pool (name);
244
245CREATE TABLE client
246(
247    clientid          serial      not null,
248    name              text        not null,
249    uname             text        not null,
250    autoprune         smallint    default 0,
251    fileretention     bigint      default 0,
252    jobretention      bigint      default 0,
253    primary key (clientid)
254);
255
256create unique index client_name_idx on client (name);
257
258CREATE TABLE Log
259(
260    LogId             serial      not null,
261    JobId             integer     not null,
262    Time              timestamp   without time zone,
263    LogText           text        not null,
264    primary key (LogId)
265);
266create index log_name_idx on Log (JobId);
267
268CREATE 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')),
278   newenabled smallint,
279   PRIMARY KEY(LocLogId)
280);
281
282
283
284CREATE TABLE counters
285(
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)
292);
293
294
295
296CREATE TABLE basefiles
297(
298    baseid            serial                not null,
299    jobid             integer               not null,
300    fileid            bigint                not null,
301    fileindex         integer                       ,
302    basejobid         integer                       ,
303    primary key (baseid)
304);
305
306CREATE TABLE unsavedfiles
307(
308    UnsavedId         integer               not null,
309    jobid             integer               not null,
310    pathid            integer               not null,
311    filenameid        integer               not null,
312    primary key (UnsavedId)
313);
314
315CREATE TABLE CDImages
316(
317   MediaId integer not null,
318   LastBurn timestamp without time zone not null,
319   primary key (MediaId)
320);
321
322
323CREATE TABLE version
324(
325    versionid         integer               not null
326);
327
328CREATE TABLE Status (
329   JobStatus CHAR(1) NOT NULL,
330   JobStatusLong TEXT,
331   PRIMARY KEY (JobStatus)
332   );
333
334INSERT INTO Status (JobStatus,JobStatusLong) VALUES
335   ('C', 'Created, not yet running');
336INSERT INTO Status (JobStatus,JobStatusLong) VALUES
337   ('R', 'Running');
338INSERT INTO Status (JobStatus,JobStatusLong) VALUES
339   ('B', 'Blocked');
340INSERT INTO Status (JobStatus,JobStatusLong) VALUES
341   ('T', 'Completed successfully');
342INSERT INTO Status (JobStatus,JobStatusLong) VALUES
343   ('E', 'Terminated with errors');
344INSERT INTO Status (JobStatus,JobStatusLong) VALUES
345   ('e', 'Non-fatal error');
346INSERT INTO Status (JobStatus,JobStatusLong) VALUES
347   ('f', 'Fatal error');
348INSERT INTO Status (JobStatus,JobStatusLong) VALUES
349   ('D', 'Verify found differences');
350INSERT INTO Status (JobStatus,JobStatusLong) VALUES
351   ('A', 'Canceled by user');
352INSERT INTO Status (JobStatus,JobStatusLong) VALUES
353   ('F', 'Waiting for Client');
354INSERT INTO Status (JobStatus,JobStatusLong) VALUES
355   ('S', 'Waiting for Storage daemon');
356INSERT INTO Status (JobStatus,JobStatusLong) VALUES
357   ('m', 'Waiting for new media');
358INSERT INTO Status (JobStatus,JobStatusLong) VALUES
359   ('M', 'Waiting for media mount');
360INSERT INTO Status (JobStatus,JobStatusLong) VALUES
361   ('s', 'Waiting for storage resource');
362INSERT INTO Status (JobStatus,JobStatusLong) VALUES
363   ('j', 'Waiting for job resource');
364INSERT INTO Status (JobStatus,JobStatusLong) VALUES
365   ('c', 'Waiting for client resource');
366INSERT INTO Status (JobStatus,JobStatusLong) VALUES
367   ('d', 'Waiting on maximum jobs');
368INSERT INTO Status (JobStatus,JobStatusLong) VALUES
369   ('t', 'Waiting on start time');
370INSERT INTO Status (JobStatus,JobStatusLong) VALUES
371   ('p', 'Waiting on higher priority jobs');
372INSERT INTO Status (JobStatus,JobStatusLong) VALUES
373   ('a', 'SD despooling attributes');
374INSERT INTO Status (JobStatus,JobStatusLong) VALUES
375   ('i', 'Doing batch insert file records');
376
377INSERT INTO Version (VersionId) VALUES (11);
378
379-- Make sure we have appropriate permissions
380
381