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 (PathId);
40 CREATE INDEX inx5 ON File (FileNameId);
43 JobId INTEGER UNSIGNED NOT NULL,
44 Job VARCHAR(128) NOT NULL,
45 Name VARCHAR(128) NOT NULL,
48 ClientId INTEGER REFERENCES Client DEFAULT 0,
49 JobStatus CHAR NOT NULL,
50 SchedTime DATETIME NOT NULL,
51 StartTime DATETIME DEFAULT 0,
52 EndTime DATETIME DEFAULT 0,
53 JobTDate BIGINT UNSIGNED DEFAULT 0,
54 VolSessionId INTEGER UNSIGNED DEFAULT 0,
55 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
56 JobFiles INTEGER UNSIGNED DEFAULT 0,
57 JobBytes BIGINT UNSIGNED DEFAULT 0,
58 JobErrors INTEGER UNSIGNED DEFAULT 0,
59 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
60 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
61 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
62 PurgedFiles TINYINT DEFAULT 0,
63 HasBase TINYINT DEFAULT 0,
67 CREATE INDEX inx6 ON Job (Name);
69 CREATE TABLE FileSet (
70 FileSetId INTEGER UNSIGNED AUTOINCREMENT,
71 FileSet VARCHAR(128) NOT NULL,
72 MD5 VARCHAR(25) NOT NULL,
73 CreateTime DATETIME DEFAULT 0,
74 PRIMARY KEY(FileSetId)
77 CREATE TABLE JobMedia (
78 JobMediaId INTEGER UNSIGNED AUTOINCREMENT,
79 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
80 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
81 FirstIndex INTEGER UNSIGNED NOT NULL,
82 LastIndex INTEGER UNSIGNED NOT NULL,
83 StartFile INTEGER UNSIGNED DEFAULT 0,
84 EndFile INTEGER UNSIGNED DEFAULT 0,
85 StartBlock INTEGER UNSIGNED DEFAULT 0,
86 EndBlock INTEGER UNSIGNED DEFAULT 0,
87 VolIndex INTEGER UNSIGNED DEFAULT 0,
88 PRIMARY KEY(JobMediaId)
91 CREATE INDEX inx7 ON JobMedia (JobId, MediaId);
95 MediaId INTEGER UNSIGNED AUTOINCREMENT,
96 VolumeName VARCHAR(128) NOT NULL,
97 Slot INTEGER DEFAULT 0,
98 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
99 MediaType VARCHAR(128) NOT NULL,
100 FirstWritten DATETIME DEFAULT 0,
101 LastWritten DATETIME DEFAULT 0,
102 LabelDate DATETIME DEFAULT 0,
103 VolJobs INTEGER UNSIGNED DEFAULT 0,
104 VolFiles INTEGER UNSIGNED DEFAULT 0,
105 VolBlocks INTEGER UNSIGNED DEFAULT 0,
106 VolMounts INTEGER UNSIGNED DEFAULT 0,
107 VolBytes BIGINT UNSIGNED DEFAULT 0,
108 VolErrors INTEGER UNSIGNED DEFAULT 0,
109 VolWrites INTEGER UNSIGNED DEFAULT 0,
110 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
111 VolStatus VARCHAR(20) NOT NULL,
112 Recycle TINYINT DEFAULT 0,
113 VolRetention BIGINT UNSIGNED DEFAULT 0,
114 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
115 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
116 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
117 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
118 InChanger TINYINT DEFAULT 0,
119 MediaAddressing TINYINT DEFAULT 0,
120 VolReadTime BIGINT UNSIGNED DEFAULT 0,
121 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
122 EndFile INTEGER UNSIGNED DEFAULT 0,
123 EndBlock INTEGER UNSIGNED DEFAULT 0,
127 CREATE INDEX inx8 ON Media (PoolId);
130 PoolId INTEGER UNSIGNED AUTOINCREMENT,
131 Name VARCHAR(128) NOT NULL,
132 NumVols INTEGER UNSIGNED DEFAULT 0,
133 MaxVols INTEGER UNSIGNED DEFAULT 0,
134 UseOnce TINYINT DEFAULT 0,
135 UseCatalog TINYINT DEFAULT 1,
136 AcceptAnyVolume TINYINT DEFAULT 0,
137 VolRetention BIGINT UNSIGNED DEFAULT 0,
138 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
139 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
140 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
141 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
142 AutoPrune TINYINT DEFAULT 0,
143 Recycle TINYINT DEFAULT 0,
144 PoolType VARCHAR(20) NOT NULL,
145 LabelFormat VARCHAR(128) NOT NULL,
146 Enabled TINYINT DEFAULT 1,
147 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
148 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
154 CREATE TABLE Client (
155 ClientId INTEGER UNSIGNED AUTOINCREMENT,
156 Name VARCHAR(128) NOT NULL,
157 Uname VARCHAR(255) NOT NULL, -- uname -a field
158 AutoPrune TINYINT DEFAULT 0,
159 FileRetention BIGINT UNSIGNED DEFAULT 0,
160 JobRetention BIGINT UNSIGNED DEFAULT 0,
162 PRIMARY KEY(ClientId)
165 CREATE TABLE BaseFiles (
166 BaseId INTEGER UNSIGNED AUTOINCREMENT,
167 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
168 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
169 FileId INTEGER UNSIGNED REFERENCES File NOT NULL,
170 FileIndex INTEGER UNSIGNED,
174 CREATE TABLE UnsavedFiles (
175 UnsavedId INTEGER UNSIGNED AUTOINCREMENT,
176 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
177 PathId INTEGER UNSIGNED REFERENCES Path NOT NULL,
178 FilenameId INTEGER UNSIGNED REFERENCES Filename NOT NULL,
179 PRIMARY KEY (UnsavedId)
183 CREATE TABLE NextId (
184 id INTEGER UNSIGNED DEFAULT 0,
185 TableName TEXT NOT NULL,
186 PRIMARY KEY (TableName)
189 -- Initialize JobId to start at 1
190 INSERT INTO NextId (id, TableName) VALUES (1, "Job");
192 CREATE TABLE Version (
193 VersionId INTEGER UNSIGNED NOT NULL
196 -- Initialize Version
197 INSERT INTO Version (VersionId) VALUES (8);
199 CREATE TABLE Counters (
200 Counter TEXT NOT NULL,
201 MinValue INTEGER DEFAULT 0,
202 MaxValue INTEGER DEFAULT 0,
203 CurrentValue INTEGER DEFAULT 0,
204 WrapCounter TEXT NOT NULL,
205 PRIMARY KEY (Counter)
208 PRAGMA default_synchronous = OFF;
209 PRAGMA default_cache_size = 10000;