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 (
140 MediaTypeId INTERGER,
141 MediaType VARCHAR(128) NOT NULL,
142 ReadOnly TINYINT DEFAULT 0,
143 PRIMARY KEY(MediaTypeId)
146 CREATE TABLE Device (
148 Name VARCHAR(128) NOT NULL,
149 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
150 StorageId INTEGER UNSIGNED REFERENCES Storage,
151 DevMounts INTEGER UNSIGNED DEFAULT 0,
152 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
153 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
154 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
155 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
156 DevReadTime BIGINT UNSIGNED DEFAULT 0,
157 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
158 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
159 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
160 CleaningDate DATETIME DEFAULT 0,
161 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
162 PRIMARY KEY(DeviceId)
165 CREATE TABLE Storage (
167 Name VARCHAR(128) NOT NULL,
168 AutoChanger TINYINT DEFAULT 0,
169 PRIMARY KEY(StorageId)
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,
199 CREATE TABLE Client (
201 Name VARCHAR(128) NOT NULL,
202 Uname VARCHAR(255) NOT NULL, -- uname -a field
203 AutoPrune TINYINT DEFAULT 0,
204 FileRetention BIGINT UNSIGNED DEFAULT 0,
205 JobRetention BIGINT UNSIGNED DEFAULT 0,
207 PRIMARY KEY(ClientId)
210 CREATE TABLE BaseFiles (
212 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
213 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
214 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
215 FileIndex INTEGER UNSIGNED,
219 CREATE TABLE UnsavedFiles (
221 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
222 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
223 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
224 PRIMARY KEY (UnsavedId)
228 CREATE TABLE NextId (
229 id INTEGER UNSIGNED DEFAULT 0,
230 TableName TEXT NOT NULL,
231 PRIMARY KEY (TableName)
234 -- Initialize JobId to start at 1
235 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
237 CREATE TABLE Version (
238 VersionId INTEGER UNSIGNED NOT NULL
241 -- Initialize Version
242 INSERT INTO Version (VersionId) VALUES (8);
244 CREATE TABLE Counters (
245 Counter TEXT NOT NULL,
246 MinValue INTEGER DEFAULT 0,
247 MaxValue INTEGER DEFAULT 0,
248 CurrentValue INTEGER DEFAULT 0,
249 WrapCounter TEXT NOT NULL,
250 PRIMARY KEY (Counter)
253 CREATE TABLE CDImages (
254 MediaId INTEGER UNSIGNED NOT NULL,
255 LastBurn DATETIME NOT NULL,
256 PRIMARY KEY (MediaId)
260 PRAGMA default_synchronous = OFF;
261 PRAGMA default_cache_size = 10000;