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 Copy INTEGER UNSIGNED NOT NULL DEFAULT 0,
130 Stripe INTEGER UNSIGNED NOT NULL DEFAULT 0,
131 PRIMARY KEY(JobMediaId),
132 INDEX (JobId, MediaId)
137 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
138 VolumeName TINYBLOB NOT NULL,
139 Slot INTEGER NOT NULL DEFAULT 0,
140 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
141 MediaType TINYBLOB NOT NULL,
142 LabelType TINYINT NOT NULL DEFAULT 0,
143 FirstWritten DATETIME NOT NULL,
144 LastWritten DATETIME NOT NULL,
145 LabelDate DATETIME NOT NULL,
146 VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
147 VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
148 VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
149 VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
150 VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
151 VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0,
152 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
153 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
154 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
155 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
156 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
157 Recycle TINYINT NOT NULL DEFAULT 0,
158 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
159 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
160 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
161 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
162 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
163 InChanger TINYINT NOT NULL DEFAULT 0,
164 StorageId INTEGER UNSIGNED NOT NULL REFERENCES Storage,
165 MediaAddressing TINYINT NOT NULL DEFAULT 0,
166 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
167 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
168 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
169 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
170 PRIMARY KEY(MediaId),
174 CREATE INDEX inx8 ON Media (PoolId);
179 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
180 Name TINYBLOB NOT NULL,
181 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
182 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
183 UseOnce TINYINT NOT NULL,
184 UseCatalog TINYINT NOT NULL,
185 AcceptAnyVolume TINYINT DEFAULT 0,
186 VolRetention BIGINT UNSIGNED NOT NULL,
187 VolUseDuration BIGINT UNSIGNED NOT NULL,
188 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
189 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
190 MaxVolBytes BIGINT UNSIGNED NOT NULL,
191 AutoPrune TINYINT DEFAULT 0,
192 Recycle TINYINT DEFAULT 0,
193 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
194 LabelType TINYINT NOT NULL DEFAULT 0,
195 LabelFormat TINYBLOB,
196 Enabled TINYINT DEFAULT 1,
197 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
198 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
199 NextPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
200 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
201 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
202 MigrationTime BIGINT UNSIGNED DEFAULT 0,
208 CREATE TABLE Client (
209 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
210 Name TINYBLOB NOT NULL,
211 Uname TINYBLOB NOT NULL, /* full uname -a of client */
212 AutoPrune TINYINT DEFAULT 0,
213 FileRetention BIGINT UNSIGNED NOT NULL,
214 JobRetention BIGINT UNSIGNED NOT NULL,
216 PRIMARY KEY(ClientId)
219 CREATE TABLE BaseFiles (
220 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
221 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
222 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
223 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
224 FileIndex INTEGER UNSIGNED,
228 CREATE TABLE UnsavedFiles (
229 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
230 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
231 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
232 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
233 PRIMARY KEY (UnsavedId)
238 CREATE TABLE Counters (
239 Counter TINYBLOB NOT NULL,
242 CurrentValue INTEGER,
243 WrapCounter TINYBLOB NOT NULL,
244 PRIMARY KEY (Counter(128))
247 CREATE TABLE CDImages (
248 MediaId INTEGER UNSIGNED NOT NULL,
249 LastBurn DATETIME NOT NULL,
250 PRIMARY KEY (MediaId)
253 CREATE TABLE Version (
254 VersionId INTEGER UNSIGNED NOT NULL
257 -- Initialize Version
258 INSERT INTO Version (VersionId) VALUES (9);
262 echo "Creation of Bacula MySQL tables succeeded."
264 echo "Creation of Bacula MySQL tables failed."