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 VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
115 VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
116 VolCapacityBytes BIGINT UNSIGNED NOT NULL,
117 VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
118 'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
119 Recycle TINYINT NOT NULL DEFAULT 0,
120 VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
121 VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
122 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
123 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
124 MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
125 InChanger TINYINT NOT NULL DEFAULT 0,
126 MediaAddressing TINYINT NOT NULL DEFAULT 0,
127 VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
128 VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
129 EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
130 EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
131 PRIMARY KEY(MediaId),
136 PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
137 Name TINYBLOB NOT NULL,
138 NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
139 MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
140 UseOnce TINYINT NOT NULL,
141 UseCatalog TINYINT NOT NULL,
142 AcceptAnyVolume TINYINT DEFAULT 0,
143 VolRetention BIGINT UNSIGNED NOT NULL,
144 VolUseDuration BIGINT UNSIGNED NOT NULL,
145 MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
146 MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
147 MaxVolBytes BIGINT UNSIGNED NOT NULL,
148 AutoPrune TINYINT DEFAULT 0,
149 Recycle TINYINT DEFAULT 0,
150 PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
151 LabelFormat TINYBLOB,
152 Enabled TINYINT DEFAULT 1,
153 ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
154 RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
160 CREATE TABLE Client (
161 ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
162 Name TINYBLOB NOT NULL,
163 Uname TINYBLOB NOT NULL, /* full uname -a of client */
164 AutoPrune TINYINT DEFAULT 0,
165 FileRetention BIGINT UNSIGNED NOT NULL,
166 JobRetention BIGINT UNSIGNED NOT NULL,
168 PRIMARY KEY(ClientId)
171 CREATE TABLE BaseFiles (
172 BaseId INTEGER UNSIGNED AUTO_INCREMENT,
173 BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
174 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
175 FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
176 FileIndex INTEGER UNSIGNED,
180 CREATE TABLE UnsavedFiles (
181 UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
182 JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
183 PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
184 FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
185 PRIMARY KEY (UnsavedId)
189 CREATE TABLE Version (
190 VersionId INTEGER UNSIGNED NOT NULL
193 -- Initialize Version
194 INSERT INTO Version (VersionId) VALUES (8);
196 CREATE TABLE Counters (
197 Counter TINYBLOB NOT NULL,
200 CurrentValue INTEGER,
201 WrapCounter TINYBLOB NOT NULL,
202 PRIMARY KEY (Counter(128))
205 CREATE TABLE CDImages (
206 MediaId INTEGER UNSIGNED NOT NULL,
207 LastBurn DATETIME NOT NULL,
208 PRIMARY KEY (MediaId)
214 echo "Creation of Bacula MySQL tables succeeded."
216 echo "Creation of Bacula MySQL tables failed."