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