1#!/bin/sh
2#
3# Copyright (C) 2000-2020 Kern Sibbald
4# License: BSD 2-Clause; see file LICENSE-FOSS
5#
6# Shell script to update PostgreSQL tables from version 1.38 to 2.0.0 or higher
7#
8echo " "
9echo "This script will update a Bacula PostgreSQL database from version 9 to 10"
10echo " which is needed to convert from Bacula version 1.38.x to 2.0.0 or higher"
11echo "Depending on the size of your database,"
12echo "this script may take several minutes to run."
13echo " "
14
15bindir=@POSTGRESQL_BINDIR@
16PATH="$bindir:$PATH"
17db_name=${db_name:-@db_name@}
18
19if $bindir/psql -f - -d ${db_name} $* <<END-OF-DATA
20ALTER TABLE media ADD COLUMN DeviceId integer;
21UPDATE media SET DeviceId=0;
22ALTER TABLE media ADD COLUMN MediaTypeId integer;
23UPDATE media SET MediaTypeId=0;
24ALTER TABLE media ADD COLUMN LocationId integer;
25UPDATE media SET LocationId=0;
26ALTER TABLE media ADD COLUMN RecycleCount integer;
27UPDATE media SET RecycleCount=0;
28ALTER TABLE media ADD COLUMN InitialWrite timestamp without time zone;
29ALTER TABLE media ADD COLUMN scratchpoolid integer;
30UPDATE media SET scratchpoolid=0;
31ALTER TABLE media ADD COLUMN recyclepoolid integer;
32UPDATE media SET recyclepoolid=0;
33ALTER TABLE media ADD COLUMN enabled integer;
34UPDATE media SET enabled=1;
35ALTER TABLE media ADD COLUMN Comment TEXT;
36
37ALTER TABLE job ADD COLUMN RealEndTime timestamp without time zone;
38ALTER TABLE job ADD COLUMN PriorJobId integer;
39UPDATE job SET PriorJobId=0;
40
41ALTER TABLE jobmedia DROP COLUMN Stripe;
42
43CREATE TABLE Location (
44   LocationId SERIAL NOT NULL,
45   Location TEXT NOT NULL,
46   Cost integer default 0,
47   Enabled integer,
48   PRIMARY KEY (LocationId)
49);
50
51CREATE TABLE LocationLog (
52   LocLogId SERIAL NOT NULL,
53   Date timestamp   without time zone,
54   Comment TEXT NOT NULL,
55   MediaId INTEGER DEFAULT 0,
56   LocationId INTEGER DEFAULT 0,
57   newvolstatus text not null
58	check (newvolstatus in ('Full','Archive','Append',
59	      'Recycle','Purged','Read-Only','Disabled',
60	      'Error','Busy','Used','Cleaning','Scratch')),
61   newenabled smallint,
62   PRIMARY KEY(LocLogId)
63);
64
65
66CREATE TABLE Log
67(
68    LogId	      serial	  not null,
69    JobId	      integer	  not null,
70    Time	      timestamp   without time zone,
71    LogText	      text	  not null,
72    primary key (LogId)
73);
74create index log_name_idx on Log (JobId);
75
76
77DELETE FROM version;
78INSERT INTO version (versionId) VALUES (10);
79
80vacuum;
81
82END-OF-DATA
83then
84   echo "Update of Bacula PostgreSQL tables succeeded."
85else
86   echo "Update of Bacula PostgreSQL tables failed."
87fi
88exit 0
89