1#!/bin/sh
2#
3# Author: Eric Bollengier, 2009
4# Copyright (C) 2000-2015 Kern Sibbald
5# License: BSD 2-Clause; see file LICENSE-FOSS
6#
7# shell script to update SQLite from version 1.38 to 2.0
8#
9echo " "
10echo "This script will update a Bacula SQLite database from version 9 to 10"
11echo " which is needed to convert from Bacula version 1.38.x to 2.0.x or higher"
12echo "Depending on the size of your database,"
13echo "this script may take several minutes to run."
14echo " "
15
16bindir=@SQLITE_BINDIR@
17PATH="$bindir:$PATH"
18cd @working_dir@
19db_name=@db_name@
20
21sqlite3 $* ${db_name}.db <<END-OF-DATA
22BEGIN TRANSACTION;
23
24CREATE TEMPORARY TABLE Media_backup (
25   MediaId INTEGER UNSIGNED AUTOINCREMENT,
26   VolumeName VARCHAR(128) NOT NULL,
27   Slot INTEGER DEFAULT 0,
28   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
29   MediaType VARCHAR(128) NOT NULL,
30   MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
31   LabelType TINYINT DEFAULT 0,
32   FirstWritten DATETIME DEFAULT 0,
33   LastWritten DATETIME DEFAULT 0,
34   LabelDate DATETIME DEFAULT 0,
35   VolJobs INTEGER UNSIGNED DEFAULT 0,
36   VolFiles INTEGER UNSIGNED DEFAULT 0,
37   VolBlocks INTEGER UNSIGNED DEFAULT 0,
38   VolMounts INTEGER UNSIGNED DEFAULT 0,
39   VolBytes BIGINT UNSIGNED DEFAULT 0,
40   VolParts INTEGER UNSIGNED DEFAULT 0,
41   VolErrors INTEGER UNSIGNED DEFAULT 0,
42   VolWrites INTEGER UNSIGNED DEFAULT 0,
43   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
44   VolStatus VARCHAR(20) NOT NULL,
45   Enabled TINYINT DEFAULT 1,
46   Recycle TINYINT DEFAULT 0,
47   VolRetention BIGINT UNSIGNED DEFAULT 0,
48   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
49   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
50   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
51   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
52   InChanger TINYINT DEFAULT 0,
53   StorageId INTEGER UNSIGNED REFERENCES Storage,
54   DeviceId INTEGER UNSIGNED REFERENCES Device,
55   MediaAddressing TINYINT DEFAULT 0,
56   VolReadTime BIGINT UNSIGNED DEFAULT 0,
57   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
58   EndFile INTEGER UNSIGNED DEFAULT 0,
59   EndBlock INTEGER UNSIGNED DEFAULT 0,
60   LocationId INTEGER UNSIGNED REFERENCES Location,
61   RecycleCount INTEGER UNSIGNED DEFAULT 0,
62   InitialWrite DATETIME DEFAULT 0,
63   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
64   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
65   PRIMARY KEY(MediaId)
66   );
67
68INSERT INTO Media_backup SELECT 
69   MediaId, VolumeName, Slot, PoolId,
70   MediaType, LabelType, 0, FirstWritten, LastWritten,
71   LabelDate, VolJobs, VolFiles, VolBlocks,
72   VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
73   VolCapacityBytes, VolStatus, 1, Recycle,
74   VolRetention, VolUseDuration, MaxVolJobs,
75   MaxVolFiles, MaxVolBytes, InChanger, 
76   StorageId, 0, MediaAddressing,
77   VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
78   FROM Media;
79
80
81DROP TABLE Media;
82
83CREATE TABLE Media (
84   MediaId INTEGER,
85   VolumeName VARCHAR(128) NOT NULL,
86   Slot INTEGER DEFAULT 0,
87   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
88   MediaType VARCHAR(128) NOT NULL,
89   MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
90   LabelType TINYINT DEFAULT 0,
91   FirstWritten DATETIME DEFAULT 0,
92   LastWritten DATETIME DEFAULT 0,
93   LabelDate DATETIME DEFAULT 0,
94   VolJobs INTEGER UNSIGNED DEFAULT 0,
95   VolFiles INTEGER UNSIGNED DEFAULT 0,
96   VolBlocks INTEGER UNSIGNED DEFAULT 0,
97   VolMounts INTEGER UNSIGNED DEFAULT 0,
98   VolBytes BIGINT UNSIGNED DEFAULT 0,
99   VolParts INTEGER UNSIGNED DEFAULT 0,
100   VolErrors INTEGER UNSIGNED DEFAULT 0,
101   VolWrites INTEGER UNSIGNED DEFAULT 0,
102   VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
103   VolStatus VARCHAR(20) NOT NULL,
104   Enabled TINYINT DEFAULT 1,
105   Recycle TINYINT DEFAULT 0,
106   VolRetention BIGINT UNSIGNED DEFAULT 0,
107   VolUseDuration BIGINT UNSIGNED DEFAULT 0,
108   MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
109   MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
110   MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
111   InChanger TINYINT DEFAULT 0,
112   StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
113   DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
114   MediaAddressing TINYINT DEFAULT 0,
115   VolReadTime BIGINT UNSIGNED DEFAULT 0,
116   VolWriteTime BIGINT UNSIGNED DEFAULT 0,
117   EndFile INTEGER UNSIGNED DEFAULT 0,
118   EndBlock INTEGER UNSIGNED DEFAULT 0,
119   LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
120   RecycleCount INTEGER UNSIGNED DEFAULT 0,
121   InitialWrite DATETIME DEFAULT 0,
122   ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
123   RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
124   Comment TEXT,
125   PRIMARY KEY(MediaId)
126   );
127
128INSERT INTO Media (
129   MediaId, VolumeName, Slot, PoolId,
130   MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
131   LabelDate, VolJobs, VolFiles, VolBlocks,
132   VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
133   VolCapacityBytes, VolStatus, Enabled, Recycle,
134   VolRetention, VolUseDuration, MaxVolJobs,
135   MaxVolFiles, MaxVolBytes,
136   InChanger, StorageId, DeviceId, MediaAddressing,
137   VolReadTime, VolWriteTime,	    
138   EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,	  
139   ScratchPoolId, RecyclePoolId)
140   SELECT * FROM Media_backup;
141
142
143DROP TABLE Media_backup;
144CREATE INDEX inx8 ON Media (PoolId);
145
146CREATE TEMPORARY TABLE job_backup
147(
148   JobId INTEGER,
149   Job VARCHAR(128) NOT NULL,
150   Name VARCHAR(128) NOT NULL,
151   Type CHAR NOT NULL,
152   Level CHAR NOT NULL,
153   ClientId INTEGER REFERENCES Client DEFAULT 0,
154   JobStatus CHAR NOT NULL,
155   SchedTime DATETIME NOT NULL,
156   StartTime DATETIME DEFAULT 0,
157   EndTime DATETIME DEFAULT 0,
158   RealEndTime DATETIME DEFAULT 0,
159   JobTDate BIGINT UNSIGNED DEFAULT 0,
160   VolSessionId INTEGER UNSIGNED DEFAULT 0,
161   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
162   JobFiles INTEGER UNSIGNED DEFAULT 0,
163   JobBytes BIGINT UNSIGNED DEFAULT 0,
164   JobErrors INTEGER UNSIGNED DEFAULT 0,
165   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
166   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
167   FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
168   PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
169   PurgedFiles TINYINT DEFAULT 0,
170   HasBase TINYINT DEFAULT 0 
171   );
172
173INSERT INTO Job_backup SELECT 
174   JobId, Job, Name, Type, Level, ClientId, JobStatus,
175   SchedTime, StartTime, EndTime, 0,
176   JobTDate, VolSessionId, VolSessionTime, 
177   JobFiles, JobBytes, JobErrors, JobMissingFiles, 
178   PoolId, FileSetId, 0, PurgedFiles, HasBase
179   FROM Job;
180
181DROP TABLE Job;
182
183CREATE TABLE Job
184(
185   JobId INTEGER,
186   Job VARCHAR(128) NOT NULL,
187   Name VARCHAR(128) NOT NULL,
188   Type CHAR NOT NULL,
189   Level CHAR NOT NULL,
190   ClientId INTEGER REFERENCES Client DEFAULT 0,
191   JobStatus CHAR NOT NULL,
192   SchedTime DATETIME NOT NULL,
193   StartTime DATETIME DEFAULT 0,
194   EndTime DATETIME DEFAULT 0,
195   RealEndTime DATETIME DEFAULT 0,
196   JobTDate BIGINT UNSIGNED DEFAULT 0,
197   VolSessionId INTEGER UNSIGNED DEFAULT 0,
198   VolSessionTime INTEGER UNSIGNED DEFAULT 0,
199   JobFiles INTEGER UNSIGNED DEFAULT 0,
200   JobBytes BIGINT UNSIGNED DEFAULT 0,
201   JobErrors INTEGER UNSIGNED DEFAULT 0,
202   JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
203   PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
204   FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
205   PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
206   PurgedFiles TINYINT DEFAULT 0,
207   HasBase TINYINT DEFAULT 0,
208   PRIMARY KEY(JobId)
209   );
210CREATE INDEX inx6 ON Job (Name);
211
212INSERT INTO Job (
213   JobId, Job, Name, Type, Level, ClientId, JobStatus,
214   SchedTime, StartTime, EndTime, RealEndTime,
215   JobTDate, VolSessionId, VolSessionTime, 
216   JobFiles, JobBytes, JobErrors, JobMissingFiles, 
217   PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase)
218   SELECT * FROM Job_backup;
219
220DROP TABLE Job_backup;
221
222CREATE TABLE LocationLog (
223   LocLogId INTEGER,
224   Date DATETIME NOT NULL,
225   Comment TEXT NOT NULL,
226   MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
227   LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
228   NewVolStatus VARCHAR(20) NOT NULL,
229   NewEnabled TINYINT NOT NULL,
230   PRIMARY KEY(LocLogId)
231);
232
233CREATE TABLE Log (
234   LogId INTEGER,
235   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
236   Time DATETIME NOT NULL,
237   LogText TEXT NOT NULL,
238   PRIMARY KEY(LogId) 
239   );
240CREATE INDEX LogInx1 ON File (JobId);
241
242CREATE TABLE Location (
243   LocationId INTEGER,
244   Location TEXT NOT NULL,
245   Cost INTEGER DEFAULT 0,
246   Enabled TINYINT,
247   PRIMARY KEY(LocationId)
248   );
249 
250
251DELETE FROM Version;
252INSERT INTO Version (VersionId) VALUES (10);
253
254COMMIT;
255
256END-OF-DATA
257