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 (name);
47 filesetid serial not null,
50 createtime timestamp without time zone not null,
51 primary key (filesetid)
54 create index fileset_name_idx on fileset (name);
58 poolid serial not null,
60 numvols integer not null,
61 maxvols integer not null,
62 useonce smallint not null,
63 usecatalog smallint not null,
64 acceptanyvolume smallint
66 volretention bigint not null,
67 voluseduration bigint not null,
68 maxvoljobs integer not null,
69 maxvolfiles integer not null,
70 maxvolbytes bigint not null,
71 autoprune smallint not null
76 check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
77 labelformat text not null,
78 enabled smallint not null
80 scratchpoolid integer ,
81 recyclepoolid integer ,
85 create index pool_name_idx on pool (name);
89 clientid serial not null,
94 fileretention bigint not null,
95 jobretention bigint not null,
96 primary key (clientid)
99 create unique index client_name_idx on client (name);
103 mediaid serial not null,
104 volumename text not null,
105 slot integer not null
107 poolid integer not null,
108 mediatype text not null,
109 firstwritten timestamp without time zone not null,
110 lastwritten timestamp without time zone not null,
111 labeldate timestamp without time zone not null,
112 voljobs integer not null,
113 volfiles integer not null,
114 volblocks integer not null,
115 volmounts integer not null,
116 volbytes bigint not null,
117 volerrors integer not null,
118 volwrites integer not null,
119 volcapacitybytes bigint not null,
120 volstatus text not null
121 check (volstatus in ('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy',
123 recycle smallint not null,
124 volretention bigint not null,
125 voluseduration bigint not null,
126 maxvoljobs integer not null,
127 maxvolfiles integer not null,
128 maxvolbytes bigint not null,
129 drive integer not null
131 inchanger smallint not null
133 mediaaddressing smallint not null
135 primary key (mediaid)
140 jobid serial not null,
143 type char(1) not null,
144 level char(1) not null,
145 clientid integer not null,
146 jobstatus char(1) not null,
147 schedtime timestamp without time zone not null,
148 starttime timestamp without time zone not null,
149 endtime timestamp without time zone not null,
150 jobtdate bigint not null,
151 volsessionid integer not null,
152 volsessiontime integer not null,
153 jobfiles integer not null,
154 jobbytes bigint not null,
155 joberrors integer not null,
156 jobmissingfiles integer not null,
157 poolid integer not null,
158 filesetid integer not null,
159 purgedfiles smallint not null
161 hasbase smallint not null
166 create index job_name_idx on job (name);
170 fileid serial not null,
171 fileindex integer not null,
172 jobid integer not null,
173 pathid integer not null,
174 filenameid bigint not null,
175 markid integer not null
182 create table jobmedia
184 jobmediaid serial not null,
185 jobid integer not null,
186 mediaid integer not null,
187 firstindex integer not null,
188 lastindex integer not null,
189 startfile integer not null,
190 endfile integer not null,
191 startblock integer not null,
192 endblock integer not null,
193 volindex integer not null,
194 primary key (jobmediaid)
197 create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
199 create table basefiles
201 baseid serial not null,
202 jobid integer not null,
203 fileid integer not null,
209 create table unsavedfiles
211 UnsavedId integer not null,
212 jobid integer not null,
213 pathid integer not null,
214 filenameid integer not null,
215 primary key (UnsavedId)
219 add foreign key (scratchpoolid)
220 references pool (poolid) on update cascade on delete cascade;
223 add foreign key (recyclepoolid)
224 references pool (poolid) on update cascade on delete cascade;
227 add foreign key (poolid)
228 references pool (poolid) on update cascade on delete cascade;
231 add foreign key (poolid)
232 references pool (poolid) on update cascade on delete cascade;
235 add foreign key (filesetid)
236 references fileset (filesetid) on update cascade on delete cascade;
239 add foreign key (clientid)
240 references client (clientid) on update cascade on delete cascade;
243 add foreign key (jobid)
244 references job (jobid) on update cascade on delete cascade;
247 add foreign key (pathid)
248 references path (pathid) on update cascade on delete restrict;
251 add foreign key (filenameid)
252 references filename (filenameid) on update cascade on delete cascade;
255 add foreign key (jobid)
256 references job (jobid) on update cascade on delete cascade;
259 add foreign key (mediaid)
260 references media (mediaid) on update cascade on delete cascade;
262 alter table basefiles
263 add foreign key (jobid)
264 references job (jobid) on update cascade on delete cascade;
266 alter table basefiles
267 add foreign key (fileid)
268 references file (fileid) on update cascade on delete cascade;
270 alter table basefiles
271 add foreign key (basejobid)
272 references job (jobid) on update cascade on delete cascade;
274 alter table unsavedfiles
275 add foreign key (jobid)
276 references job (jobid) on update restrict on delete restrict;
278 alter table unsavedfiles
279 add foreign key (pathid)
280 references path (pathid) on update restrict on delete restrict;
282 alter table unsavedfiles
283 add foreign key (filenameid)
284 references filename (filenameid) on update restrict on delete restrict;
291 echo "Creation of Bacula PostgreSQL tables succeeded."
293 echo "Creation of Bacula PostgreSQL tables failed."