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 VolParts INTEGER UNSIGNED DEFAULT 0,
115 VolErrors INTEGER UNSIGNED DEFAULT 0,
116 VolWrites INTEGER UNSIGNED DEFAULT 0,
117 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
118 VolStatus VARCHAR(20) NOT NULL,
119 Recycle TINYINT DEFAULT 0,
120 VolRetention BIGINT UNSIGNED DEFAULT 0,
121 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
122 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
123 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
124 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
125 InChanger TINYINT DEFAULT 0,
126 MediaAddressing TINYINT DEFAULT 0,
127 VolReadTime BIGINT UNSIGNED DEFAULT 0,
128 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
129 EndFile INTEGER UNSIGNED DEFAULT 0,
130 EndBlock INTEGER UNSIGNED DEFAULT 0,
134 CREATE INDEX inx8 ON Media (PoolId);
137 PoolId INTEGER UNSIGNED AUTOINCREMENT,
138 Name VARCHAR(128) NOT NULL,
139 NumVols INTEGER UNSIGNED DEFAULT 0,
140 MaxVols INTEGER UNSIGNED DEFAULT 0,
141 UseOnce TINYINT DEFAULT 0,
142 UseCatalog TINYINT DEFAULT 1,
143 AcceptAnyVolume TINYINT DEFAULT 0,
144 VolRetention BIGINT UNSIGNED DEFAULT 0,
145 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
146 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
147 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
148 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
149 AutoPrune TINYINT DEFAULT 0,
150 Recycle TINYINT DEFAULT 0,
151 PoolType VARCHAR(20) NOT NULL,
152 LabelFormat VARCHAR(128) NOT NULL,
153 Enabled TINYINT DEFAULT 1,
154 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
155 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
161 CREATE TABLE Client (
162 ClientId INTEGER UNSIGNED AUTOINCREMENT,
163 Name VARCHAR(128) NOT NULL,
164 Uname VARCHAR(255) NOT NULL, -- uname -a field
165 AutoPrune TINYINT DEFAULT 0,
166 FileRetention BIGINT UNSIGNED DEFAULT 0,
167 JobRetention BIGINT UNSIGNED DEFAULT 0,
169 PRIMARY KEY(ClientId)
172 CREATE TABLE BaseFiles (
173 BaseId INTEGER UNSIGNED AUTOINCREMENT,
174 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
175 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
176 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
177 FileIndex INTEGER UNSIGNED,
181 CREATE TABLE UnsavedFiles (
182 UnsavedId INTEGER UNSIGNED AUTOINCREMENT,
183 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
184 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
185 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
186 PRIMARY KEY (UnsavedId)
190 CREATE TABLE NextId (
191 id INTEGER UNSIGNED DEFAULT 0,
192 TableName TEXT NOT NULL,
193 PRIMARY KEY (TableName)
196 -- Initialize JobId to start at 1
197 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
199 CREATE TABLE Version (
200 VersionId INTEGER UNSIGNED NOT NULL
203 -- Initialize Version
204 INSERT INTO Version (VersionId) VALUES (8);
206 CREATE TABLE Counters (
207 Counter TEXT NOT NULL,
208 MinValue INTEGER DEFAULT 0,
209 MaxValue INTEGER DEFAULT 0,
210 CurrentValue INTEGER DEFAULT 0,
211 WrapCounter TEXT NOT NULL,
212 PRIMARY KEY (Counter)
215 CREATE TABLE CDImages (
216 MediaId INTEGER UNSIGNED NOT NULL,
217 LastBurn DATETIME NOT NULL,
218 PRIMARY KEY (MediaId)
222 PRAGMA default_synchronous = OFF;
223 PRAGMA default_cache_size = 10000;