3 # shell script to create Bacula MySQL tables
7 if $bindir/mysql $* -f <<END-OF-DATA
9 CREATE TABLE Filename (
10 FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
12 PRIMARY KEY(FilenameId),
17 PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
24 # ****FIXME**** make FileId BIGINT someday when MySQL works *****
26 FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
27 FileIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
28 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
29 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
30 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
31 MarkId INTEGER UNSIGNED NOT NULL DEFAULT 0,
32 LStat TINYBLOB NOT NULL,
33 MD5 TINYBLOB NOT NULL,
35 INDEX (JobId, PathId, FilenameId)
39 # Possibly add one or more of the following indexes
40 # to the above File table if your Verifies are
45 # INDEX (FilenameId, PathId)
49 CREATE TABLE MediaType (
50 MediaTypeId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
51 MediaType VARCHAR(128) NOT NULL,
52 ReadOnly TINYINT DEFAULT 0,
53 PRIMARY KEY(MediaTypeId)
56 CREATE TABLE Storage (
57 StorageId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
58 Name VARCHAR(128) NOT NULL,
59 AutoChanger TINYINT DEFAULT 0,
60 PRIMARY KEY(StorageId)
64 DeviceId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
65 Name VARCHAR(128) NOT NULL,
66 MediaTypeId INTEGER UNSIGNED NOT NULL REFERENCES MediaType,
67 StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage,
68 DevMounts INTEGER UNSIGNED DEFAULT 0,
69 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
70 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
71 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
72 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
73 DevReadTime BIGINT UNSIGNED DEFAULT 0,
74 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
75 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
76 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
77 CleaningDate DATETIME DEFAULT 0,
78 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
84 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
85 Job TINYBLOB NOT NULL,
86 Name TINYBLOB NOT NULL,
87 Type BINARY(1) NOT NULL,
88 Level BINARY(1) NOT NULL,
89 ClientId INTEGER NOT NULL REFERENCES Client,
90 JobStatus BINARY(1) NOT NULL,
91 SchedTime DATETIME NOT NULL,
92 StartTime DATETIME NOT NULL,
93 EndTime DATETIME NOT NULL,
94 JobTDate BIGINT UNSIGNED NOT NULL,
95 VolSessionId INTEGER UNSIGNED NOT NULL DEFAULT 0,
96 VolSessionTime INTEGER UNSIGNED NOT NULL DEFAULT 0,
97 JobFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
98 JobBytes BIGINT UNSIGNED NOT NULL,
99 JobErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
100 JobMissingFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
101 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
102 FileSetId INTEGER UNSIGNED NOT NULL REFERENCES FileSet,
103 PurgedFiles TINYINT NOT NULL DEFAULT 0,
104 HasBase TINYINT NOT NULL DEFAULT 0,
110 CREATE TABLE FileSet (
111 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
112 FileSet TINYBLOB NOT NULL,
113 MD5 TINYBLOB NOT NULL,
114 CreateTime DATETIME NOT NULL,
115 PRIMARY KEY(FileSetId)
118 CREATE TABLE JobMedia (
119 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
120 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
121 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
122 FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
123 LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
124 StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
125 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
126 StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
127 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
128 VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
129 PRIMARY KEY(JobMediaId),
130 INDEX (JobId, MediaId)
135 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
136 VolumeName TINYBLOB NOT NULL,
137 Slot INTEGER NOT NULL DEFAULT 0,
138 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
139 MediaType TINYBLOB NOT NULL,
140 LabelType TINYINT NOT NULL DEFAULT 0,
141 FirstWritten DATETIME NOT NULL,
142 LastWritten DATETIME NOT NULL,
143 LabelDate DATETIME NOT NULL,
144 VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
145 VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
146 VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
147 VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
148 VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
149 VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0,
150 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
151 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
152 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
153 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
154 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
155 Recycle TINYINT NOT NULL DEFAULT 0,
156 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
157 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
158 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
159 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
160 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
161 InChanger TINYINT NOT NULL DEFAULT 0,
162 StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage,
163 MediaAddressing TINYINT NOT NULL DEFAULT 0,
164 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
165 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
166 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
167 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
168 PRIMARY KEY(MediaId),
172 CREATE INDEX inx8 ON Media (PoolId);
177 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
178 Name TINYBLOB NOT NULL,
179 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
180 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
181 UseOnce TINYINT NOT NULL,
182 UseCatalog TINYINT NOT NULL,
183 AcceptAnyVolume TINYINT DEFAULT 0,
184 VolRetention BIGINT UNSIGNED NOT NULL,
185 VolUseDuration BIGINT UNSIGNED NOT NULL,
186 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
187 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
188 MaxVolBytes BIGINT UNSIGNED NOT NULL,
189 AutoPrune TINYINT DEFAULT 0,
190 Recycle TINYINT DEFAULT 0,
191 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
192 LabelType TINYINT NOT NULL DEFAULT 0,
193 LabelFormat TINYBLOB,
194 Enabled TINYINT DEFAULT 1,
195 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
196 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
197 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
198 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
199 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
200 MigrationTime BIGINT UNSIGNED DEFAULT 0,
206 CREATE TABLE Client (
207 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
208 Name TINYBLOB NOT NULL,
209 Uname TINYBLOB NOT NULL, /* full uname -a of client */
210 AutoPrune TINYINT DEFAULT 0,
211 FileRetention BIGINT UNSIGNED NOT NULL,
212 JobRetention BIGINT UNSIGNED NOT NULL,
214 PRIMARY KEY(ClientId)
217 CREATE TABLE BaseFiles (
218 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
219 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
220 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
221 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
222 FileIndex INTEGER UNSIGNED,
226 CREATE TABLE UnsavedFiles (
227 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
228 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
229 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
230 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
231 PRIMARY KEY (UnsavedId)
235 CREATE TABLE Version (
236 VersionId INTEGER UNSIGNED NOT NULL
239 -- Initialize Version
240 INSERT INTO Version (VersionId) VALUES (8);
242 CREATE TABLE Counters (
243 Counter TINYBLOB NOT NULL,
246 CurrentValue INTEGER,
247 WrapCounter TINYBLOB NOT NULL,
248 PRIMARY KEY (Counter(128))
251 CREATE TABLE CDImages (
252 MediaId INTEGER UNSIGNED NOT NULL,
253 LastBurn DATETIME NOT NULL,
254 PRIMARY KEY (MediaId)
260 echo "Creation of Bacula MySQL tables succeeded."
262 echo "Creation of Bacula MySQL tables failed."