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