3 # shell script to create Bacula SQLite tables
9 ${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
10 CREATE TABLE Filename (
13 PRIMARY KEY(FilenameId)
16 CREATE INDEX inx1 ON Filename (Name);
24 CREATE INDEX inx2 ON Path (Path);
29 FileIndex INTEGER UNSIGNED NOT NULL,
30 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
31 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
32 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
33 MarkId INTEGER UNSIGNED DEFAULT 0,
34 LStat VARCHAR(255) NOT NULL,
35 MD5 VARCHAR(255) NOT NULL,
39 CREATE INDEX inx3 ON File (JobId);
40 CREATE INDEX inx4 ON File (FilenameId, PathId);
42 -- Possibly add one or more of the following indexes
43 -- if your Verifies are too slow.
45 -- CREATE INDEX inx4 ON File (PathId);
46 -- CREATE INDEX inx5 ON File (FileNameId);
47 -- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
51 Job VARCHAR(128) NOT NULL,
52 Name VARCHAR(128) NOT NULL,
55 ClientId INTEGER REFERENCES Client DEFAULT 0,
56 JobStatus CHAR NOT NULL,
57 SchedTime DATETIME NOT NULL,
58 StartTime DATETIME DEFAULT 0,
59 EndTime DATETIME DEFAULT 0,
60 JobTDate BIGINT UNSIGNED DEFAULT 0,
61 VolSessionId INTEGER UNSIGNED DEFAULT 0,
62 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
63 JobFiles INTEGER UNSIGNED DEFAULT 0,
64 JobBytes BIGINT UNSIGNED DEFAULT 0,
65 JobErrors INTEGER UNSIGNED DEFAULT 0,
66 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
67 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
68 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
69 PurgedFiles TINYINT DEFAULT 0,
70 HasBase TINYINT DEFAULT 0,
74 CREATE INDEX inx6 ON Job (Name);
76 CREATE TABLE FileSet (
78 FileSet VARCHAR(128) NOT NULL,
79 MD5 VARCHAR(25) NOT NULL,
80 CreateTime DATETIME DEFAULT 0,
81 PRIMARY KEY(FileSetId)
84 CREATE TABLE JobMedia (
86 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
87 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
88 FirstIndex INTEGER UNSIGNED NOT NULL,
89 LastIndex INTEGER UNSIGNED NOT NULL,
90 StartFile INTEGER UNSIGNED DEFAULT 0,
91 EndFile INTEGER UNSIGNED DEFAULT 0,
92 StartBlock INTEGER UNSIGNED DEFAULT 0,
93 EndBlock INTEGER UNSIGNED DEFAULT 0,
94 VolIndex INTEGER UNSIGNED DEFAULT 0,
95 PRIMARY KEY(JobMediaId)
98 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
103 VolumeName VARCHAR(128) NOT NULL,
104 Slot INTEGER DEFAULT 0,
105 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
106 MediaType VARCHAR(128) NOT NULL,
107 LabelType TINYINT DEFAULT 0,
108 FirstWritten DATETIME DEFAULT 0,
109 LastWritten DATETIME DEFAULT 0,
110 LabelDate DATETIME DEFAULT 0,
111 VolJobs INTEGER UNSIGNED DEFAULT 0,
112 VolFiles INTEGER UNSIGNED DEFAULT 0,
113 VolBlocks INTEGER UNSIGNED DEFAULT 0,
114 VolMounts INTEGER UNSIGNED DEFAULT 0,
115 VolBytes BIGINT UNSIGNED DEFAULT 0,
116 VolParts INTEGER UNSIGNED DEFAULT 0,
117 VolErrors INTEGER UNSIGNED DEFAULT 0,
118 VolWrites INTEGER UNSIGNED DEFAULT 0,
119 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
120 VolStatus VARCHAR(20) NOT NULL,
121 Recycle TINYINT DEFAULT 0,
122 VolRetention BIGINT UNSIGNED DEFAULT 0,
123 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
124 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
125 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
126 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
127 InChanger TINYINT DEFAULT 0,
128 StorageId INTEGER UNSIGNED REFERENCES Storage,
129 MediaAddressing TINYINT DEFAULT 0,
130 VolReadTime BIGINT UNSIGNED DEFAULT 0,
131 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
132 EndFile INTEGER UNSIGNED DEFAULT 0,
133 EndBlock INTEGER UNSIGNED DEFAULT 0,
137 CREATE INDEX inx8 ON Media (PoolId);
139 CREATE TABLE MediaType (
141 MediaType VARCHAR(128) NOT NULL,
142 ReadOnly TINYINT DEFAULT 0,
143 PRIMARY KEY(MediaTypeId)
146 CREATE TABLE Storage (
148 Name VARCHAR(128) NOT NULL,
149 AutoChanger TINYINT DEFAULT 0,
150 PRIMARY KEY(StorageId)
153 CREATE TABLE Device (
155 Name VARCHAR(128) NOT NULL,
156 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
157 StorageId INTEGER UNSIGNED REFERENCES Storage,
158 DevMounts INTEGER UNSIGNED DEFAULT 0,
159 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
160 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
161 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
162 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
163 DevReadTime BIGINT UNSIGNED DEFAULT 0,
164 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
165 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
166 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
167 CleaningDate DATETIME DEFAULT 0,
168 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
169 PRIMARY KEY(DeviceId)
175 Name VARCHAR(128) NOT NULL,
176 NumVols INTEGER UNSIGNED DEFAULT 0,
177 MaxVols INTEGER UNSIGNED DEFAULT 0,
178 UseOnce TINYINT DEFAULT 0,
179 UseCatalog TINYINT DEFAULT 1,
180 AcceptAnyVolume TINYINT DEFAULT 0,
181 VolRetention BIGINT UNSIGNED DEFAULT 0,
182 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
183 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
184 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
185 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
186 AutoPrune TINYINT DEFAULT 0,
187 Recycle TINYINT DEFAULT 0,
188 PoolType VARCHAR(20) NOT NULL,
189 LabelType TINYINT DEFAULT 0,
190 LabelFormat VARCHAR(128) NOT NULL,
191 Enabled TINYINT DEFAULT 1,
192 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
193 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
194 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
195 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
196 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
197 MigrationTime BIGINT UNSIGNED DEFAULT 0,
203 CREATE TABLE Client (
205 Name VARCHAR(128) NOT NULL,
206 Uname VARCHAR(255) NOT NULL, -- uname -a field
207 AutoPrune TINYINT DEFAULT 0,
208 FileRetention BIGINT UNSIGNED DEFAULT 0,
209 JobRetention BIGINT UNSIGNED DEFAULT 0,
211 PRIMARY KEY(ClientId)
214 CREATE TABLE BaseFiles (
216 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
217 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
218 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
219 FileIndex INTEGER UNSIGNED,
223 CREATE TABLE UnsavedFiles (
225 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
226 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
227 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
228 PRIMARY KEY (UnsavedId)
232 CREATE TABLE NextId (
233 id INTEGER UNSIGNED DEFAULT 0,
234 TableName TEXT NOT NULL,
235 PRIMARY KEY (TableName)
238 -- Initialize JobId to start at 1
239 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
241 CREATE TABLE Version (
242 VersionId INTEGER UNSIGNED NOT NULL
245 -- Initialize Version
246 INSERT INTO Version (VersionId) VALUES (8);
248 CREATE TABLE Counters (
249 Counter TEXT NOT NULL,
250 MinValue INTEGER DEFAULT 0,
251 MaxValue INTEGER DEFAULT 0,
252 CurrentValue INTEGER DEFAULT 0,
253 WrapCounter TEXT NOT NULL,
254 PRIMARY KEY (Counter)
257 CREATE TABLE CDImages (
258 MediaId INTEGER UNSIGNED NOT NULL,
259 LastBurn DATETIME NOT NULL,
260 PRIMARY KEY (MediaId)
264 PRAGMA default_synchronous = OFF;
265 PRAGMA default_cache_size = 10000;