ebl Add database update scripts to updatedb dir

[bacula/bacula] / bacula / updatedb / update_postgresql_tables_9_to_10.in

diff --git a/bacula/updatedb/update_postgresql_tables_9_to_10.in b/bacula/updatedb/update_postgresql_tables_9_to_10.in
new file mode 100644 (file)
index 0000000..97ecee2
--- /dev/null
+++ b/bacula/updatedb/update_postgresql_tables_9_to_10.in
@@ -0,0 +1,84 @@
+#!/bin/sh
+#
+# Shell script to update PostgreSQL tables from version 1.38 to 2.0.0 or higher
+#
+echo " "
+echo "This script will update a Bacula PostgreSQL database from version 9 to 10"
+echo " which is needed to convert from Bacula version 1.38.x to 2.0.0 or higher"
+echo "Depending on the size of your database,"
+echo "this script may take several minutes to run."
+echo " "
+bindir=@SQL_BINDIR@
+db_name=@db_name@
+
+if $bindir/psql -f - -d ${db_name} $* <<END-OF-DATA
+
+ALTER TABLE media ADD COLUMN DeviceId integer;
+UPDATE media SET DeviceId=0;
+ALTER TABLE media ADD COLUMN MediaTypeId integer;
+UPDATE media SET MediaTypeId=0;
+ALTER TABLE media ADD COLUMN LocationId integer;
+UPDATE media SET LocationId=0;
+ALTER TABLE media ADD COLUMN RecycleCount integer;
+UPDATE media SET RecycleCount=0;
+ALTER TABLE media ADD COLUMN InitialWrite timestamp without time zone;
+ALTER TABLE media ADD COLUMN scratchpoolid integer;
+UPDATE media SET scratchpoolid=0;
+ALTER TABLE media ADD COLUMN recyclepoolid integer;
+UPDATE media SET recyclepoolid=0;
+ALTER TABLE media ADD COLUMN enabled integer;
+UPDATE media SET enabled=1;
+ALTER TABLE media ADD COLUMN Comment TEXT;
+
+ALTER TABLE job ADD COLUMN RealEndTime timestamp without time zone;
+ALTER TABLE job ADD COLUMN PriorJobId integer;
+UPDATE job SET PriorJobId=0;
+
+ALTER TABLE jobmedia DROP COLUMN Stripe;
+
+CREATE TABLE Location (
+   LocationId SERIAL NOT NULL,
+   Location TEXT NOT NULL,
+   Cost integer default 0,
+   Enabled integer,
+   PRIMARY KEY (LocationId)
+);
+
+CREATE TABLE LocationLog (
+   LocLogId SERIAL NOT NULL,
+   Date timestamp   without time zone,
+   Comment TEXT NOT NULL,
+   MediaId INTEGER DEFAULT 0,
+   LocationId INTEGER DEFAULT 0,
+   newvolstatus text not null
+       check (newvolstatus in ('Full','Archive','Append',
+             'Recycle','Purged','Read-Only','Disabled',
+             'Error','Busy','Used','Cleaning','Scratch')),
+   newenabled smallint,
+   PRIMARY KEY(LocLogId)
+);
+
+
+CREATE TABLE Log
+(
+    LogId            serial      not null,
+    JobId            integer     not null,
+    Time             timestamp   without time zone,
+    LogText          text        not null,
+    primary key (LogId)
+);
+create index log_name_idx on Log (JobId);
+
+
+DELETE FROM version;
+INSERT INTO version (versionId) VALUES (10);
+
+vacuum;
+
+END-OF-DATA
+then
+   echo "Update of Bacula PostgreSQL tables succeeded."
+else
+   echo "Update of Bacula PostgreSQL tables failed."
+fi
+exit 0