Debian Codegraph @

updatedb/update_sqlite_tables_5_to_6

raw
1 #!/bin/sh
2 #
3 # shell script to update SQLite from version 1.29 to 1.30
4 #
5 echo " "
6 echo "This script will update a bacula database from version 5 to 6."
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
9 echo " "
10
11 # The location of the sqlite program
12 bindir=/usr/lib/sqlite
13 # The location of your bacula working directory
14 cd /var/bacula
15
16 if [ -s bacula.db ];then
17 DB_VER=`echo "select * from Version;" | $bindir/sqlite bacula.db | tail -n 1 2>/dev/null`
18 if [ -n "$DB_VER" ] && [ "$DB_VER" -ne "5" ]; then
19 echo "Sorry, this script is designed to update a version 5 database"
20 echo "and you have a version $DB_VER database."
21 exit 1
22 fi
23 else
24 echo "Sorry, I can't seem to locate a bacula database."
25 exit 1
26 fi
27
28 $bindir/sqlite $* bacula.db <<END-OF-DATA
29
30 BEGIN TRANSACTION;
31 CREATE TEMPORARY TABLE FileSet_backup (
32 FileSetId INTEGER UNSIGNED AUTOINCREMENT,
33 FileSet VARCHAR(128) NOT NULL,
34 MD5 VARCHAR(25) NOT NULL,
35 PRIMARY KEY(FileSetId)
36 );
37 INSERT INTO FileSet_backup SELECT * From FileSet;
38
39 DROP TABLE FileSet;
40 CREATE TABLE FileSet (
41 FileSetId INTEGER UNSIGNED AUTOINCREMENT,
42 FileSet VARCHAR(128) NOT NULL,
43 MD5 VARCHAR(25) NOT NULL,
44 CreateTime DATETIME DEFAULT 0,
45 PRIMARY KEY(FileSetId)
46 );
47 INSERT INTO FileSet (
48 FileSetId, FileSet, MD5, CreateTime)
49 SELECT FileSet_backup.FileSetId,FileSet,MD5,StartTime FROM FileSet_backup,Job
50 WHERE FileSet_backup.FileSetId=Job.FileSetId GROUP by FileSet_backup.FileSetId
51 ORDER BY Job.FileSetId;
52
53 DROP TABLE FileSet_backup;
54 COMMIT;
55
56
57 BEGIN TRANSACTION;
58 CREATE TEMPORARY TABLE Job_backup (
59 JobId INTEGER UNSIGNED NOT NULL,
60 Job VARCHAR(128) NOT NULL,
61 Name VARCHAR(128) NOT NULL,
62 PurgedFiles TINYINT DEFAULT 0,
63 Type CHAR NOT NULL,
64 Level CHAR NOT NULL,
65 ClientId INTEGER REFERENCES Client DEFAULT 0,
66 JobStatus CHAR NOT NULL,
67 SchedTime DATETIME NOT NULL,
68 StartTime DATETIME DEFAULT 0,
69 EndTime DATETIME DEFAULT 0,
70 JobTDate BIGINT UNSIGNED DEFAULT 0,
71 VolSessionId INTEGER UNSIGNED DEFAULT 0,
72 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
73 JobFiles INTEGER UNSIGNED DEFAULT 0,
74 JobBytes BIGINT UNSIGNED DEFAULT 0,
75 JobErrors INTEGER UNSIGNED DEFAULT 0,
76 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
77 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
78 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0
79 );
80 INSERT INTO Job_backup SELECT * From Job;
81
82 DROP TABLE Job;
83 CREATE TABLE Job (
84 JobId INTEGER UNSIGNED NOT NULL,
85 Job VARCHAR(128) NOT NULL,
86 Name VARCHAR(128) NOT NULL,
87 PurgedFiles TINYINT DEFAULT 0,
88 Type CHAR NOT NULL,
89 Level CHAR NOT NULL,
90 ClientId INTEGER REFERENCES Client DEFAULT 0,
91 JobStatus CHAR NOT NULL,
92 SchedTime DATETIME NOT NULL,
93 StartTime DATETIME DEFAULT 0,
94 EndTime DATETIME DEFAULT 0,
95 JobTDate BIGINT UNSIGNED DEFAULT 0,
96 VolSessionId INTEGER UNSIGNED DEFAULT 0,
97 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
98 JobFiles INTEGER UNSIGNED DEFAULT 0,
99 JobBytes BIGINT UNSIGNED DEFAULT 0,
100 JobErrors INTEGER UNSIGNED DEFAULT 0,
101 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
102 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
103 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
104 HasBase TINYINT DEFAULT 0,
105 PRIMARY KEY(JobId)
106 );
107 INSERT INTO Job (JobId,Job,Name,PurgedFiles,
108 Type,Level,ClientId,JobStatus,SchedTime,
109 StartTime,EndTime,JobTDate,VolSessionId,
110 VolSessionTime,JobFiles,JobBytes,JobErrors,
111 JobMissingFiles,PoolId,FileSetId)
112 SELECT * FROM Job_backup;
113 DROP TABLE Job_backup;
114 COMMIT;
115
116
117 BEGIN TRANSACTION;
118 CREATE TEMPORARY TABLE JobMedia_backup (
119 JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
120 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
121 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
122 FirstIndex INTEGER UNSIGNED NOT NULL,
123 LastIndex INTEGER UNSIGNED NOT NULL,
124 StartFile INTEGER UNSIGNED DEFAULT 0,
125 EndFile INTEGER UNSIGNED DEFAULT 0,
126 StartBlock INTEGER UNSIGNED DEFAULT 0,
127 EndBlock INTEGER UNSIGNED DEFAULT 0,
128 PRIMARY KEY(JobMediaId)
129 );
130 INSERT INTO JobMedia_backup SELECT * From JobMedia;
131
132 DROP TABLE JobMedia;
133 CREATE TABLE JobMedia (
134 JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
135 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
136 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
137 FirstIndex INTEGER UNSIGNED NOT NULL,
138 LastIndex INTEGER UNSIGNED NOT NULL,
139 StartFile INTEGER UNSIGNED DEFAULT 0,
140 EndFile INTEGER UNSIGNED DEFAULT 0,
141 StartBlock INTEGER UNSIGNED DEFAULT 0,
142 EndBlock INTEGER UNSIGNED DEFAULT 0,
143 VolIndex INTEGER UNSIGNED DEFAULT 0,
144 PRIMARY KEY(JobMediaId)
145 );
146 INSERT INTO JobMedia (
147 JobMediaId,JobId,MediaId,FirstIndex,
148 LastIndex,StartFile,EndFile,StartBlock,
149 EndBlock)
150 SELECT * FROM JobMedia_backup;
151 DROP TABLE JobMedia_backup;
152 COMMIT;
153
154 CREATE TABLE BaseFiles (
155 BaseId INTEGER UNSIGNED AUTOINCREMENT,
156 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
157 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
158 FileIndex INTEGER UNSIGNED,
159 PRIMARY KEY(BaseId)
160 );
161
162 DROP TABLE Counters;
163
164 CREATE TABLE Counters (
165 Counter TEXT NOT NULL,
166 MinValue INTEGER DEFAULT 0,
167 MaxValue INTEGER DEFAULT 0,
168 CurrentValue INTEGER DEFAULT 0,
169 WrapCounter TEXT NOT NULL,
170 PRIMARY KEY (Counter)
171 );
172
173 UPDATE Version SET VersionId=6;
174
175 END-OF-DATA