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 DEFAULT 0,
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,
54 RecycleCount INTEGER UNSIGNED DEFAULT 0,
55 InitialWrite DATETIME DEFAULT 0,
56 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
57 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
61 INSERT INTO Media_backup SELECT
62 MediaId, VolumeName, Slot, PoolId,
63 MediaType, LabelType, 0, FirstWritten, LastWritten,
64 LabelDate, VolJobs, VolFiles, VolBlocks,
65 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
66 VolCapacityBytes, VolStatus, Recycle,
67 VolRetention, VolUseDuration, MaxVolJobs,
68 MaxVolFiles, MaxVolBytes, InChanger,
69 StorageId, 0, MediaAddressing,
70 VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
77 MediaId INTEGER UNSIGNED AUTOINCREMENT,
78 VolumeName VARCHAR(128) NOT NULL,
79 Slot INTEGER DEFAULT 0,
80 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
81 MediaType VARCHAR(128) NOT NULL,
82 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
83 LabelType TINYINT DEFAULT 0,
84 FirstWritten DATETIME DEFAULT 0,
85 LastWritten DATETIME DEFAULT 0,
86 LabelDate DATETIME DEFAULT 0,
87 VolJobs INTEGER UNSIGNED DEFAULT 0,
88 VolFiles INTEGER UNSIGNED DEFAULT 0,
89 VolBlocks INTEGER UNSIGNED DEFAULT 0,
90 VolMounts INTEGER UNSIGNED DEFAULT 0,
91 VolBytes BIGINT UNSIGNED DEFAULT 0,
92 VolParts INTEGER UNSIGNED DEFAULT 0,
93 VolErrors INTEGER UNSIGNED DEFAULT 0,
94 VolWrites INTEGER UNSIGNED DEFAULT 0,
95 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
96 VolStatus VARCHAR(20) NOT NULL,
97 Recycle TINYINT DEFAULT 0,
98 VolRetention BIGINT UNSIGNED DEFAULT 0,
99 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
100 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
101 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
102 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
103 InChanger TINYINT DEFAULT 0,
104 StorageId INTEGER UNSIGNED REFERENCES Storage,
105 DeviceId INTEGER UNSIGNED REFERENCES Device,
106 MediaAddressing TINYINT DEFAULT 0,
107 VolReadTime BIGINT UNSIGNED DEFAULT 0,
108 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
109 EndFile INTEGER UNSIGNED DEFAULT 0,
110 EndBlock INTEGER UNSIGNED DEFAULT 0,
111 LocationId INTEGER UNSIGNED REFERENCES Location,
112 RecycleCount INTEGER UNSIGNED DEFAULT 0,
113 InitialWrite DATETIME DEFAULT 0,
114 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
115 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
120 MediaId, VolumeName, Slot, PoolId,
121 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
122 LabelDate, VolJobs, VolFiles, VolBlocks,
123 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
124 VolCapacityBytes, VolStatus, Recycle,
125 VolRetention, VolUseDuration, MaxVolJobs,
126 MaxVolFiles, MaxVolBytes,
127 InChanger, StorageId, DeviceId, MediaAddressing,
128 VolReadTime, VolWriteTime,
129 EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,
130 ScratchPoolId, RecyclePoolId)
131 SELECT * FROM Media_backup;
134 DROP TABLE Media_backup;
135 CREATE INDEX inx8 ON Media (PoolId);
140 OriginalJobId INTEGER,
141 JobType CHAR NOT NULL,
142 JobLevel CHAR NOT NULL,
143 SchedTime DATETIME NOT NULL,
144 StartTime DATETIME DEFAULT 0,
145 EndTime DATETIME DEFAULT 0,
146 JobTDate BIGINT UNSIGNED DEFAULT 0,
150 CREATE TABLE Location (
152 Location VARCHAR(128) NOT NULL,
153 PRIMARY KEY(LocationId)
158 INSERT INTO Version (VersionId) VALUES (9);