3 # shell script to create Bacula SQLite tables
8 $bindir/sqlite $* bacula.db <<END-OF-DATA
9 CREATE TABLE Filename (
10 FilenameId INTEGER UNSIGNED AUTOINCREMENT,
12 PRIMARY KEY(FilenameId)
15 CREATE INDEX inx1 ON Filename (Name);
18 PathId INTEGER UNSIGNED AUTOINCREMENT,
23 CREATE INDEX inx2 ON Path (Path);
27 FileId INTEGER UNSIGNED AUTOINCREMENT,
28 FileIndex INTEGER UNSIGNED NOT NULL,
29 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
30 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
31 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
32 MarkId INTEGER UNSIGNED DEFAULT 0,
33 LStat VARCHAR(255) NOT NULL,
34 MD5 VARCHAR(255) NOT NULL,
38 CREATE INDEX inx3 ON File (JobId);
39 CREATE INDEX inx4 ON File (FilenameId, PathId);
41 -- Possibly add one or more of the following indexes
42 -- if your Verifies are too slow.
44 -- CREATE INDEX inx4 ON File (PathId);
45 -- CREATE INDEX inx5 ON File (FileNameId);
46 -- CREATE INDEX inx9 ON File (JobId, PathId, FilenameId);
49 JobId INTEGER UNSIGNED NOT NULL,
50 Job VARCHAR(128) NOT NULL,
51 Name VARCHAR(128) NOT NULL,
54 ClientId INTEGER REFERENCES Client DEFAULT 0,
55 JobStatus CHAR NOT NULL,
56 SchedTime DATETIME NOT NULL,
57 StartTime DATETIME DEFAULT 0,
58 EndTime DATETIME DEFAULT 0,
59 JobTDate BIGINT UNSIGNED DEFAULT 0,
60 VolSessionId INTEGER UNSIGNED DEFAULT 0,
61 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
62 JobFiles INTEGER UNSIGNED DEFAULT 0,
63 JobBytes BIGINT UNSIGNED DEFAULT 0,
64 JobErrors INTEGER UNSIGNED DEFAULT 0,
65 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
66 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
67 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
68 PurgedFiles TINYINT DEFAULT 0,
69 HasBase TINYINT DEFAULT 0,
73 CREATE INDEX inx6 ON Job (Name);
75 CREATE TABLE FileSet (
76 FileSetId INTEGER UNSIGNED AUTOINCREMENT,
77 FileSet VARCHAR(128) NOT NULL,
78 MD5 VARCHAR(25) NOT NULL,
79 CreateTime DATETIME DEFAULT 0,
80 PRIMARY KEY(FileSetId)
83 CREATE TABLE JobMedia (
84 JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
85 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
86 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
87 FirstIndex INTEGER UNSIGNED NOT NULL,
88 LastIndex INTEGER UNSIGNED NOT NULL,
89 StartFile INTEGER UNSIGNED DEFAULT 0,
90 EndFile INTEGER UNSIGNED DEFAULT 0,
91 StartBlock INTEGER UNSIGNED DEFAULT 0,
92 EndBlock INTEGER UNSIGNED DEFAULT 0,
93 VolIndex INTEGER UNSIGNED DEFAULT 0,
94 PRIMARY KEY(JobMediaId)
97 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
101 MediaId INTEGER UNSIGNED AUTOINCREMENT,
102 VolumeName VARCHAR(128) NOT NULL,
103 Slot INTEGER DEFAULT 0,
104 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
105 MediaType VARCHAR(128) NOT NULL,
106 FirstWritten DATETIME DEFAULT 0,
107 LastWritten DATETIME DEFAULT 0,
108 LabelDate DATETIME DEFAULT 0,
109 VolJobs INTEGER UNSIGNED DEFAULT 0,
110 VolFiles INTEGER UNSIGNED DEFAULT 0,
111 VolBlocks INTEGER UNSIGNED DEFAULT 0,
112 VolMounts INTEGER UNSIGNED DEFAULT 0,
113 VolBytes BIGINT UNSIGNED DEFAULT 0,
114 VolErrors INTEGER UNSIGNED DEFAULT 0,
115 VolWrites INTEGER UNSIGNED DEFAULT 0,
116 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
117 VolStatus VARCHAR(20) NOT NULL,
118 Recycle TINYINT DEFAULT 0,
119 VolRetention BIGINT UNSIGNED DEFAULT 0,
120 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
121 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
122 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
123 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
124 InChanger TINYINT DEFAULT 0,
125 MediaAddressing TINYINT DEFAULT 0,
126 VolReadTime BIGINT UNSIGNED DEFAULT 0,
127 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
128 EndFile INTEGER UNSIGNED DEFAULT 0,
129 EndBlock INTEGER UNSIGNED DEFAULT 0,
133 CREATE INDEX inx8 ON Media (PoolId);
136 PoolId INTEGER UNSIGNED AUTOINCREMENT,
137 Name VARCHAR(128) NOT NULL,
138 NumVols INTEGER UNSIGNED DEFAULT 0,
139 MaxVols INTEGER UNSIGNED DEFAULT 0,
140 UseOnce TINYINT DEFAULT 0,
141 UseCatalog TINYINT DEFAULT 1,
142 AcceptAnyVolume TINYINT DEFAULT 0,
143 VolRetention BIGINT UNSIGNED DEFAULT 0,
144 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
145 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
146 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
147 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
148 AutoPrune TINYINT DEFAULT 0,
149 Recycle TINYINT DEFAULT 0,
150 PoolType VARCHAR(20) NOT NULL,
151 LabelFormat VARCHAR(128) NOT NULL,
152 Enabled TINYINT DEFAULT 1,
153 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
154 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
160 CREATE TABLE Client (
161 ClientId INTEGER UNSIGNED AUTOINCREMENT,
162 Name VARCHAR(128) NOT NULL,
163 Uname VARCHAR(255) NOT NULL, -- uname -a field
164 AutoPrune TINYINT DEFAULT 0,
165 FileRetention BIGINT UNSIGNED DEFAULT 0,
166 JobRetention BIGINT UNSIGNED DEFAULT 0,
168 PRIMARY KEY(ClientId)
171 CREATE TABLE BaseFiles (
172 BaseId INTEGER UNSIGNED AUTOINCREMENT,
173 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
174 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
175 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
176 FileIndex INTEGER UNSIGNED,
180 CREATE TABLE UnsavedFiles (
181 UnsavedId INTEGER UNSIGNED AUTOINCREMENT,
182 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
183 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
184 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
185 PRIMARY KEY (UnsavedId)
189 CREATE TABLE NextId (
190 id INTEGER UNSIGNED DEFAULT 0,
191 TableName TEXT NOT NULL,
192 PRIMARY KEY (TableName)
195 -- Initialize JobId to start at 1
196 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
198 CREATE TABLE Version (
199 VersionId INTEGER UNSIGNED NOT NULL
202 -- Initialize Version
203 INSERT INTO Version (VersionId) VALUES (8);
205 CREATE TABLE Counters (
206 Counter TEXT NOT NULL,
207 MinValue INTEGER DEFAULT 0,
208 MaxValue INTEGER DEFAULT 0,
209 CurrentValue INTEGER DEFAULT 0,
210 WrapCounter TEXT NOT NULL,
211 PRIMARY KEY (Counter)
214 CREATE TABLE CDImages (
215 MediaId INTEGER UNSIGNED NOT NULL,
216 LastBurn DATETIME NOT NULL,
217 PRIMARY KEY (MediaId)
221 PRAGMA default_synchronous = OFF;
222 PRAGMA default_cache_size = 10000;