3 # Copyright (C) 2000-2015 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
6 # Shell script to update SQLite tables from Bacula Community version
10 echo "This script will update a Bacula SQLite database from version 12-14 to 15"
12 echo "Depending on the current version of your catalog, you may have to run this script"
13 echo " multiple times"
16 bindir=@SQLITE_BINDIR@
21 DBVERSION=`sqlite3 ${db_name}.db <<END
22 SELECT VersionId FROM Version LIMIT 1;
25 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 14 ] ; then
27 echo "The existing database is version $DBVERSION !!"
28 echo "This script can only update an existing version 12, 13, 14 or 1014 database to version 1015."
29 echo "Error. Cannot upgrade this database."
34 if [ "$DBVERSION" = 12 ] ; then
35 sqlite3 $* ${db_name}.db <<END-OF-DATA
38 CREATE TABLE RestoreObject (
39 RestoreObjectId INTEGER,
40 ObjectName TEXT DEFAULT '',
41 RestoreObject TEXT DEFAULT '',
42 PluginName TEXT DEFAULT '',
43 ObjectLength INTEGER DEFAULT 0,
44 ObjectFullLength INTEGER DEFAULT 0,
45 ObjectIndex INTEGER DEFAULT 0,
46 ObjectType INTEGER DEFAULT 0,
47 FileIndex INTEGER UNSIGNED DEFAULT 0,
48 ObjectCompression INTEGER DEFAULT 0,
49 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
50 PRIMARY KEY(RestoreObjectId)
52 CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
54 UPDATE Version SET VersionId=13;
61 if [ "$DBVERSION" = 13 ] ; then
63 sqlite3 $* ${db_name}.db <<END-OF-DATA
66 ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
67 UPDATE Version SET VersionId=14;
77 if [ "$DBVERSION" = 14 ] ; then
78 sqlite3 $* ${db_name}.db <<END-OF-DATA
81 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
82 ('I', 'Incomplete Job',25);
84 CREATE TABLE new_Media (
86 VolumeName VARCHAR(128) NOT NULL,
87 Slot INTEGER DEFAULT 0,
88 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
89 MediaType VARCHAR(128) NOT NULL,
90 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
91 LabelType TINYINT DEFAULT 0,
92 FirstWritten DATETIME DEFAULT 0,
93 LastWritten DATETIME DEFAULT 0,
94 LabelDate DATETIME DEFAULT 0,
95 VolJobs INTEGER UNSIGNED DEFAULT 0,
96 VolFiles INTEGER UNSIGNED DEFAULT 0,
97 VolBlocks INTEGER UNSIGNED DEFAULT 0,
98 VolMounts INTEGER UNSIGNED DEFAULT 0,
99 VolBytes BIGINT UNSIGNED DEFAULT 0,
100 VolParts INTEGER UNSIGNED DEFAULT 0,
101 VolErrors INTEGER UNSIGNED DEFAULT 0,
102 VolWrites BIGINT UNSIGNED DEFAULT 0,
103 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
104 VolStatus VARCHAR(20) NOT NULL,
105 Enabled TINYINT DEFAULT 1,
106 Recycle TINYINT DEFAULT 0,
107 ActionOnPurge TINYINT DEFAULT 0,
108 VolRetention BIGINT UNSIGNED DEFAULT 0,
109 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
110 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
111 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
112 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
113 InChanger TINYINT DEFAULT 0,
114 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
115 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
116 MediaAddressing TINYINT DEFAULT 0,
117 VolReadTime BIGINT UNSIGNED DEFAULT 0,
118 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
119 EndFile INTEGER UNSIGNED DEFAULT 0,
120 EndBlock INTEGER UNSIGNED DEFAULT 0,
121 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
122 RecycleCount INTEGER UNSIGNED DEFAULT 0,
123 InitialWrite DATETIME DEFAULT 0,
124 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
125 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
127 VolABytes BIGINT UNSIGNED DEFAULT 0,
128 VolAPadding BIGINT UNSIGNED DEFAULT 0,
129 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
130 VolHoles INTEGER UNSIGNED DEFAULT 0,
133 INSERT INTO new_Media (MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment) SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment FROM Media;
135 ALTER TABLE new_Media RENAME TO Media;
136 CREATE INDEX inx8 ON Media (PoolId);
138 CREATE TABLE Snapshot (
139 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT,
140 Name TINYBLOB NOT NULL,
141 JobId INTEGER UNSIGNED DEFAULT 0,
142 FileSetId INTEGER UNSIGNED DEFAULT 0,
143 CreateTDate BIGINT NOT NULL,
144 CreateDate DATETIME NOT NULL,
145 ClientId INTEGER DEFAULT 0,
146 Volume TINYBLOB NOT NULL,
147 Device TINYBLOB NOT NULL,
148 Type TINYBLOB NOT NULL,
149 Retention INTEGER DEFAULT 0,
151 primary key (SnapshotId)
154 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name);
156 CREATE INDEX jobtdate_idx on JobHisto (JobTDate);
158 UPDATE Version SET VersionId=15;