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 (FilenameId, PathId)
39 # Possibly add one or more of the following indexes
40 # to the above File table if your Verifies are
46 # INDEX (JobId, PathId, FilenameId)
50 JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
51 Job TINYBLOB NOT NULL,
52 Name TINYBLOB NOT NULL,
53 Type BINARY(1) NOT NULL,
54 Level BINARY(1) NOT NULL,
55 ClientId INTEGER NOT NULL REFERENCES Client,
56 JobStatus BINARY(1) NOT NULL,
57 SchedTime DATETIME NOT NULL,
58 StartTime DATETIME NOT NULL,
59 EndTime DATETIME NOT NULL,
60 JobTDate BIGINT UNSIGNED NOT NULL,
61 VolSessionId INTEGER UNSIGNED NOT NULL DEFAULT 0,
62 VolSessionTime INTEGER UNSIGNED NOT NULL DEFAULT 0,
63 JobFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
64 JobBytes BIGINT UNSIGNED NOT NULL,
65 JobErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
66 JobMissingFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
67 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
68 FileSetId INTEGER UNSIGNED NOT NULL REFERENCES FileSet,
69 PurgedFiles TINYINT NOT NULL DEFAULT 0,
70 HasBase TINYINT NOT NULL DEFAULT 0,
76 CREATE TABLE FileSet (
77 FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
78 FileSet TINYBLOB NOT NULL,
79 MD5 TINYBLOB NOT NULL,
80 CreateTime DATETIME NOT NULL,
81 PRIMARY KEY(FileSetId)
84 CREATE TABLE JobMedia (
85 JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
86 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
87 MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
88 FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
89 LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
90 StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
91 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
92 StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
93 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
94 VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
95 PRIMARY KEY(JobMediaId),
96 INDEX (JobId, MediaId)
101 MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
102 VolumeName TINYBLOB NOT NULL,
103 Slot INTEGER NOT NULL DEFAULT 0,
104 PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
105 MediaType TINYBLOB NOT NULL,
106 FirstWritten DATETIME NOT NULL,
107 LastWritten DATETIME NOT NULL,
108 LabelDate DATETIME NOT NULL,
109 VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
110 VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
111 VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
112 VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
113 VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
114 VolParts INTEGER UNSIGNED NOT NULL DEFAULT 0,
115 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
116 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
117 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
118 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
119 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
120 Recycle TINYINT NOT NULL DEFAULT 0,
121 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
122 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
123 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
124 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
125 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
126 InChanger TINYINT NOT NULL DEFAULT 0,
127 MediaAddressing TINYINT NOT NULL DEFAULT 0,
128 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
129 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
130 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
131 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
132 PRIMARY KEY(MediaId),
137 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
138 Name TINYBLOB NOT NULL,
139 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
140 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
141 UseOnce TINYINT NOT NULL,
142 UseCatalog TINYINT NOT NULL,
143 AcceptAnyVolume TINYINT DEFAULT 0,
144 VolRetention BIGINT UNSIGNED NOT NULL,
145 VolUseDuration BIGINT UNSIGNED NOT NULL,
146 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
147 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
148 MaxVolBytes BIGINT UNSIGNED NOT NULL,
149 AutoPrune TINYINT DEFAULT 0,
150 Recycle TINYINT DEFAULT 0,
151 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
152 LabelFormat TINYBLOB,
153 Enabled TINYINT DEFAULT 1,
154 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
155 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
161 CREATE TABLE Client (
162 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
163 Name TINYBLOB NOT NULL,
164 Uname TINYBLOB NOT NULL, /* full uname -a of client */
165 AutoPrune TINYINT DEFAULT 0,
166 FileRetention BIGINT UNSIGNED NOT NULL,
167 JobRetention BIGINT UNSIGNED NOT NULL,
169 PRIMARY KEY(ClientId)
172 CREATE TABLE BaseFiles (
173 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
174 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
175 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
176 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
177 FileIndex INTEGER UNSIGNED,
181 CREATE TABLE UnsavedFiles (
182 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
183 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
184 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
185 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
186 PRIMARY KEY (UnsavedId)
190 CREATE TABLE Version (
191 VersionId INTEGER UNSIGNED NOT NULL
194 -- Initialize Version
195 INSERT INTO Version (VersionId) VALUES (8);
197 CREATE TABLE Counters (
198 Counter TINYBLOB NOT NULL,
201 CurrentValue INTEGER,
202 WrapCounter TINYBLOB NOT NULL,
203 PRIMARY KEY (Counter(128))
206 CREATE TABLE CDImages (
207 MediaId INTEGER UNSIGNED NOT NULL,
208 LastBurn DATETIME NOT NULL,
209 PRIMARY KEY (MediaId)
215 echo "Creation of Bacula MySQL tables succeeded."
217 echo "Creation of Bacula MySQL tables failed."