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