3 # shell script to update SQLite from version 1.38 to 1.39
6 echo "This script will update a Bacula SQLite database from version 9 to 9"
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
15 ${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
18 CREATE TEMPORARY TABLE Media_backup (
19 MediaId INTEGER UNSIGNED AUTOINCREMENT,
20 VolumeName VARCHAR(128) NOT NULL,
21 Slot INTEGER DEFAULT 0,
22 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
23 MediaType VARCHAR(128) NOT NULL,
24 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
25 LabelType TINYINT DEFAULT 0,
26 FirstWritten DATETIME DEFAULT 0,
27 LastWritten DATETIME DEFAULT 0,
28 LabelDate DATETIME DEFAULT 0,
29 VolJobs INTEGER UNSIGNED DEFAULT 0,
30 VolFiles INTEGER UNSIGNED DEFAULT 0,
31 VolBlocks INTEGER UNSIGNED DEFAULT 0,
32 VolMounts INTEGER UNSIGNED DEFAULT 0,
33 VolBytes BIGINT UNSIGNED DEFAULT 0,
34 VolParts INTEGER UNSIGNED DEFAULT 0,
35 VolErrors INTEGER UNSIGNED DEFAULT 0,
36 VolWrites INTEGER UNSIGNED DEFAULT 0,
37 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
38 VolStatus VARCHAR(20) NOT NULL,
39 Recycle TINYINT DEFAULT 0,
40 VolRetention BIGINT UNSIGNED DEFAULT 0,
41 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
42 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
43 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
44 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
45 InChanger TINYINT DEFAULT 0,
46 StorageId INTEGER UNSIGNED REFERENCES Storage,
47 DeviceId INTEGER UNSIGNED REFERENCES Device,
48 MediaAddressing TINYINT DEFAULT 0,
49 VolReadTime BIGINT UNSIGNED DEFAULT 0,
50 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
51 EndFile INTEGER UNSIGNED DEFAULT 0,
52 EndBlock INTEGER UNSIGNED DEFAULT 0,
53 LocationId INTEGER UNSIGNED REFERENCES Location,
57 INSERT INTO Media_backup SELECT
58 MediaId, VolumeName, Slot, PoolId,
59 MediaType, LabelType, 0, FirstWritten, LastWritten,
60 LabelDate, VolJobs, VolFiles, VolBlocks,
61 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
62 VolCapacityBytes, VolStatus, Recycle,
63 VolRetention, VolUseDuration, MaxVolJobs,
64 MaxVolFiles, MaxVolBytes, InChanger,
65 StorageId, 0, MediaAddressing,
66 VolReadTime, VolWriteTime, EndFile, EndBlock, 0
73 MediaId INTEGER UNSIGNED AUTOINCREMENT,
74 VolumeName VARCHAR(128) NOT NULL,
75 Slot INTEGER DEFAULT 0,
76 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
77 MediaType VARCHAR(128) NOT NULL,
78 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
79 LabelType TINYINT DEFAULT 0,
80 FirstWritten DATETIME DEFAULT 0,
81 LastWritten DATETIME DEFAULT 0,
82 LabelDate DATETIME DEFAULT 0,
83 VolJobs INTEGER UNSIGNED DEFAULT 0,
84 VolFiles INTEGER UNSIGNED DEFAULT 0,
85 VolBlocks INTEGER UNSIGNED DEFAULT 0,
86 VolMounts INTEGER UNSIGNED DEFAULT 0,
87 VolBytes BIGINT UNSIGNED DEFAULT 0,
88 VolParts INTEGER UNSIGNED DEFAULT 0,
89 VolErrors INTEGER UNSIGNED DEFAULT 0,
90 VolWrites INTEGER UNSIGNED DEFAULT 0,
91 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
92 VolStatus VARCHAR(20) NOT NULL,
93 Recycle TINYINT DEFAULT 0,
94 VolRetention BIGINT UNSIGNED DEFAULT 0,
95 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
96 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
97 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
98 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
99 InChanger TINYINT DEFAULT 0,
100 StorageId INTEGER UNSIGNED REFERENCES Storage,
101 DeviceId INTEGER UNSIGNED REFERENCES Device,
102 MediaAddressing TINYINT DEFAULT 0,
103 VolReadTime BIGINT UNSIGNED DEFAULT 0,
104 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
105 EndFile INTEGER UNSIGNED DEFAULT 0,
106 EndBlock INTEGER UNSIGNED DEFAULT 0,
107 LocationId INTEGER UNSIGNED REFERENCES Location,
112 MediaId, VolumeName, Slot, PoolId,
113 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
114 LabelDate, VolJobs, VolFiles, VolBlocks,
115 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
116 VolCapacityBytes, VolStatus, Recycle,
117 VolRetention, VolUseDuration, MaxVolJobs,
118 MaxVolFiles, MaxVolBytes,
119 InChanger, StorageId, DeviceId, MediaAddressing,
120 VolReadTime, VolWriteTime,
121 EndFile, EndBlock, LocationId)
122 SELECT * FROM Media_backup;
124 DROP TABLE Media_backup;
125 CREATE INDEX inx8 ON Media (PoolId);
130 OriginalJobId INTEGER,
131 JobType CHAR NOT NULL,
132 JobLevel CHAR NOT NULL,
133 SchedTime DATETIME NOT NULL,
134 StartTime DATETIME DEFAULT 0,
135 EndTime DATETIME DEFAULT 0,
136 JobTDate BIGINT UNSIGNED DEFAULT 0,
140 CREATE TABLE Location (
142 Location VARCHAR(128) NOT NULL,
143 PRIMARY KEY(LocationId)
148 INSERT INTO Version (VersionId) VALUES (9);