3 # shell script to create Bacula PostgreSQL tables
7 if $bindir/psql $* -f - <<END-OF-DATA
12 versionid integer not null
15 INSERT INTO Version (VersionId) VALUES (7);
19 counter text not null,
22 currentvalue integer ,
23 wrapcounter text not null,
29 filenameid serial not null,
31 primary key (filenameid)
34 create index filename_name_idx on filename (name);
38 pathid serial not null,
43 create index path_name_idx on path (path);
47 filesetid serial not null,
48 fileset text not null,
50 createtime timestamp without time zone not null,
51 primary key (filesetid)
54 create index fileset_name_idx on fileset (fileset);
58 poolid serial not null,
60 numvols integer not null
62 maxvols integer not null
64 useonce smallint not null,
65 usecatalog smallint not null,
66 acceptanyvolume smallint
68 volretention bigint not null,
69 voluseduration bigint not null,
70 maxvoljobs integer not null
72 maxvolfiles integer not null
74 maxvolbytes bigint not null,
75 autoprune smallint not null
80 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
81 labelformat text not null,
82 enabled smallint not null
84 scratchpoolid integer ,
85 recyclepoolid integer ,
89 create index pool_name_idx on pool (name);
93 clientid serial not null,
98 fileretention bigint not null,
99 jobretention bigint not null,
100 primary key (clientid)
103 create unique index client_name_idx on client (name);
107 mediaid serial not null,
108 volumename text not null,
109 slot integer not null
111 poolid integer not null,
112 mediatype text not null,
113 firstwritten timestamp without time zone,
114 lastwritten timestamp without time zone,
115 labeldate timestamp without time zone,
116 voljobs integer not null
118 volfiles integer not null
120 volblocks integer not null
122 volmounts integer not null
124 volbytes bigint not null
126 volerrors integer not null
128 volwrites integer not null
130 volcapacitybytes bigint not null,
131 volstatus text not null
132 check (volstatus in ('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy',
134 recycle smallint not null
136 volretention bigint not null
138 voluseduration bigint not null
140 maxvoljobs integer not null
142 maxvolfiles integer not null
144 maxvolbytes bigint not null
146 inchanger smallint not null
148 mediaaddressing smallint not null
150 volreadtime bigint not null
152 volwritetime bigint not null
154 primary key (mediaid)
157 create unique index media_volumename_id on media (volumename);
161 jobid serial not null,
164 type char(1) not null,
165 level char(1) not null,
167 jobstatus char(1) not null,
168 schedtime timestamp without time zone not null,
169 starttime timestamp without time zone ,
170 endtime timestamp without time zone ,
171 jobtdate bigint not null,
172 volsessionid integer not null
174 volsessiontime integer not null
176 jobfiles integer not null
178 jobbytes bigint not null
180 joberrors integer not null
182 jobmissingfiles integer not null
186 purgedfiles smallint not null
188 hasbase smallint not null
193 create index job_name_idx on job (name);
197 fileid serial not null,
198 fileindex integer not null
200 jobid integer not null,
201 pathid integer not null,
202 filenameid bigint not null,
203 markid integer not null
210 create table jobmedia
212 jobmediaid serial not null,
213 jobid integer not null,
214 mediaid integer not null,
215 firstindex integer not null
217 lastindex integer not null
219 startfile integer not null
221 endfile integer not null
223 startblock integer not null
225 endblock integer not null
227 volindex integer not null
229 primary key (jobmediaid)
232 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
234 create table basefiles
236 baseid serial not null,
237 jobid integer not null,
238 fileid integer not null,
244 create table unsavedfiles
246 UnsavedId integer not null,
247 jobid integer not null,
248 pathid integer not null,
249 filenameid integer not null,
250 primary key (UnsavedId)
254 add foreign key (scratchpoolid)
255 references pool (poolid) on update cascade on delete cascade;
258 add foreign key (recyclepoolid)
259 references pool (poolid) on update cascade on delete cascade;
262 add foreign key (poolid)
263 references pool (poolid) on update cascade on delete cascade;
266 add foreign key (poolid)
267 references pool (poolid) on update cascade on delete cascade;
270 add foreign key (filesetid)
271 references fileset (filesetid) on update cascade on delete cascade;
274 add foreign key (clientid)
275 references client (clientid) on update cascade on delete cascade;
278 add foreign key (jobid)
279 references job (jobid) on update cascade on delete cascade;
282 add foreign key (pathid)
283 references path (pathid) on update cascade on delete restrict;
286 add foreign key (filenameid)
287 references filename (filenameid) on update cascade on delete cascade;
290 add foreign key (jobid)
291 references job (jobid) on update cascade on delete cascade;
294 add foreign key (mediaid)
295 references media (mediaid) on update cascade on delete cascade;
297 alter table basefiles
298 add foreign key (jobid)
299 references job (jobid) on update cascade on delete cascade;
301 alter table basefiles
302 add foreign key (fileid)
303 references file (fileid) on update cascade on delete cascade;
305 alter table basefiles
306 add foreign key (basejobid)
307 references job (jobid) on update cascade on delete cascade;
309 alter table unsavedfiles
310 add foreign key (jobid)
311 references job (jobid) on update restrict on delete restrict;
313 alter table unsavedfiles
314 add foreign key (pathid)
315 references path (pathid) on update restrict on delete restrict;
317 alter table unsavedfiles
318 add foreign key (filenameid)
319 references filename (filenameid) on update restrict on delete restrict;
325 echo "Creation of Bacula PostgreSQL tables succeeded."
327 echo "Creation of Bacula PostgreSQL tables failed."