1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
Description: Fix sqlite3 update script
Author: Carsten Leonhardt <leo@debian.org>
Bug: https://bugs.bacula.org/view.php?id=2422
Last-Update: 2017-09-10
--- a/src/cats/update_sqlite3_tables.in
+++ b/src/cats/update_sqlite3_tables.in
@@ -167,19 +167,22 @@
sqlite3 $* ${db_name}.db <<END-OF-DATA
BEGIN;
-CREATE TABLE basefiles_temp (
+CREATE TABLE new_BaseFiles (
BaseId BIGINT,
BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
- FileIndex INTEGER UNSIGNED,
+ FileIndex INTEGER DEFAULT 0,
PRIMARY KEY(BaseId)
);
-INSERT INTO basefiles_temp (BaseId, BaseJobId, JobId,
- FileId, FileIndex)
+INSERT INTO new_BaseFiles (BaseId, BaseJobId, JobId, FileId, FileIndex)
SELECT BaseId, BaseJobId, JobId, FileId, FileIndex
- FROM BaseFiles;
+ FROM BaseFiles;
+
+DROP TABLE BaseFiles;
+ALTER TABLE new_BaseFiles RENAME TO BaseFiles;
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
CREATE TABLE new_Media (
MediaId INTEGER,
@@ -235,23 +238,68 @@
PRIMARY KEY(MediaId)
);
-INSERT INTO new_Media (MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolType, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment) SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment FROM Media;
+INSERT INTO new_Media (MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolType, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment)
+ SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment
+ FROM Media;
+
DROP TABLE Media;
ALTER TABLE new_Media RENAME TO Media;
CREATE INDEX inx8 ON Media (PoolId);
-
+CREATE INDEX inx9 ON Media (StorageId);
CREATE UNIQUE INDEX Media_Volumename_Id ON Media (VolumeName);
+CREATE TABLE new_RestoreObject (
+ RestoreObjectId INTEGER,
+ ObjectName TEXT DEFAULT '',
+ RestoreObject TEXT DEFAULT '',
+ PluginName TEXT DEFAULT '',
+ ObjectLength INTEGER DEFAULT 0,
+ ObjectFullLength INTEGER DEFAULT 0,
+ ObjectIndex INTEGER DEFAULT 0,
+ ObjectType INTEGER DEFAULT 0,
+ FileIndex INTEGER DEFAULT 0,
+ ObjectCompression INTEGER DEFAULT 0,
+ JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
+ PRIMARY KEY(RestoreObjectId)
+ );
+
+INSERT INTO new_RestoreObject (RestoreObjectId, ObjectName, RestoreObject, PluginName, ObjectLength, ObjectFullLength, ObjectIndex, ObjectType, FileIndex, ObjectCompression, JobId)
+ SELECT RestoreObjectId, ObjectName, RestoreObject, PluginName, ObjectLength, ObjectFullLength, ObjectIndex, ObjectType, FileIndex, ObjectCompression, JobId
+ FROM RestoreObject;
+
+DROP TABLE RestoreObject;
+ALTER TABLE new_RestoreObject RENAME TO RestoreObject;
+CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
+
+CREATE TABLE new_File (
+ FileId INTEGER,
+ FileIndex INTEGER DEFAULT 0,
+ JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
+ PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
+ FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
+ DeltaSeq SMALLINT UNSIGNED DEFAULT 0,
+ MarkId INTEGER UNSIGNED DEFAULT 0,
+ LStat VARCHAR(255) NOT NULL,
+ MD5 VARCHAR(255) NOT NULL,
+ PRIMARY KEY(FileId)
+ );
-DROP TABLE BaseFiles;
-ALTER TABLE basefiles_temp RENAME TO BaseFiles;
+INSERT INTO new_File (FileId, FileIndex, JobId, PathId, FilenameId, DeltaSeq, MarkId, LStat, MD5)
+ SELECT FileId, FileIndex, JobId, PathId, FilenameId, DeltaSeq, MarkId, LStat, MD5
+ FROM File;
+
+DROP TABLE File;
+ALTER TABLE new_File RENAME TO File;
+CREATE INDEX inx3 ON File (JobId);
+CREATE INDEX file_jpf_idx ON File (JobId, PathId, FilenameId);
CREATE INDEX job_jobtdate_inx ON job (JobTDate);
-ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT DEFAULT 0;
-ALTER TABLE File MODIFY FileIndex INTEGER DEFAULT 0;
-ALTER TABLE RestoreObject MODIFY FileIndex INTEGER DEFAULT 0;
-ALTER TABLE BaseFiles MODIFY FileIndex INTEGER DEFAULT 0;
+ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT UNSIGNED DEFAULT 0;
+DROP TABLE IF EXISTS NextId;
+CREATE INDEX fileset_name_idx on FileSet (FileSet);
+CREATE INDEX pool_name_idx on Pool (Name);
+CREATE UNIQUE INDEX client_name_idx on Client (Name);
UPDATE Version SET VersionId=16;
COMMIT;
|
