3 * This file contains all the SQL commands issued by the Director
5 * Kern Sibbald, July MMII
11 Copyright (C) 2002-2004 Kern Sibbald and John Walker
13 This program is free software; you can redistribute it and/or
14 modify it under the terms of the GNU General Public License as
15 published by the Free Software Foundation; either version 2 of
16 the License, or (at your option) any later version.
18 This program is distributed in the hope that it will be useful,
19 but WITHOUT ANY WARRANTY; without even the implied warranty of
20 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
21 General Public License for more details.
23 You should have received a copy of the GNU General Public
24 License along with this program; if not, write to the Free
25 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
34 const char *list_pool = "SELECT * FROM Pool WHERE PoolId=%u";
36 /* For ua_dotcmds.c */
37 const char *client_backups =
38 "SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,"
39 "JobFiles,JobBytes,VolumeName"
40 " FROM Client,Job,JobMedia,Media"
41 " WHERE Client.Name='%s'"
42 " AND Client.ClientId=Job.ClientId"
44 " AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId"
45 " ORDER BY Job.StartTime";
48 /* ====== ua_prune.c */
50 const char *cnt_File = "SELECT count(*) FROM File WHERE JobId=%u";
51 const char *del_File = "DELETE FROM File WHERE JobId=%u";
52 const char *upd_Purged = "UPDATE Job Set PurgedFiles=1 WHERE JobId=%u";
53 const char *cnt_DelCand = "SELECT count(*) FROM DelCandidates";
54 const char *del_Job = "DELETE FROM Job WHERE JobId=%u";
55 const char *del_JobMedia = "DELETE FROM JobMedia WHERE JobId=%u";
56 const char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%u";
57 const char *sel_JobMedia = "SELECT JobId FROM JobMedia WHERE MediaId=%u";
59 /* Select JobIds for File deletion. */
60 const char *select_job =
61 "SELECT JobId from Job "
66 /* Delete temp tables and indexes */
67 const char *drop_deltabs[] = {
68 "DROP TABLE DelCandidates",
73 /* List of SQL commands to create temp table and indicies */
74 const char *create_deltabs[] = {
75 "CREATE TABLE DelCandidates ("
77 "JobId INTEGER UNSIGNED NOT NULL, "
78 "PurgedFiles TINYINT, "
79 "FileSetId INTEGER UNSIGNED, "
80 "JobFiles INTEGER UNSIGNED, "
81 "JobStatus BINARY(1))",
83 #ifdef HAVE_POSTGRESQL
84 "JobId INTEGER NOT NULL, "
85 "PurgedFiles SMALLINT, "
90 "JobId INTEGER UNSIGNED NOT NULL, "
91 "PurgedFiles TINYINT, "
92 "FileSetId INTEGER UNSIGNED, "
93 "JobFiles INTEGER UNSIGNED, "
97 "CREATE INDEX DelInx1 ON DelCandidates (JobId)",
100 /* Fill candidates table with all Jobs subject to being deleted.
101 * This is used for pruning Jobs (first the files, then the Jobs).
103 const char *insert_delcand =
104 "INSERT INTO DelCandidates "
105 "SELECT JobId,PurgedFiles,FileSetId,JobFiles,JobStatus FROM Job "
110 /* Select Jobs from the DelCandidates table that have a
111 * more recent backup -- i.e. are not the only backup.
112 * This is the list of Jobs to delete for a Backup Job.
113 * At the same time, we select "orphanned" jobs
114 * (i.e. no files, ...) for deletion.
116 const char *select_backup_del =
117 "SELECT DISTINCT DelCandidates.JobId "
118 "FROM Job,DelCandidates "
119 "WHERE (Job.JobTDate<%s AND ((DelCandidates.JobFiles=0) OR "
120 "(DelCandidates.JobStatus!='T'))) OR "
122 "AND Job.ClientId=%u "
125 "AND Job.JobStatus='T' "
126 "AND Job.FileSetId=DelCandidates.FileSetId)";
128 /* Select Jobs from the DelCandidates table that have a
129 * more recent InitCatalog -- i.e. are not the only InitCatalog
130 * This is the list of Jobs to delete for a Verify Job.
132 const char *select_verify_del =
133 "SELECT DISTINCT DelCandidates.JobId "
134 "FROM Job,DelCandidates "
135 "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR "
137 "AND Job.ClientId=%u "
140 "AND Job.JobStatus='T' "
141 "AND Job.FileSetId=DelCandidates.FileSetId)";
144 /* Select Jobs from the DelCandidates table.
145 * This is the list of Jobs to delete for a Restore Job.
147 const char *select_restore_del =
148 "SELECT DISTINCT DelCandidates.JobId "
149 "FROM Job,DelCandidates "
150 "WHERE (Job.JobTdate<%s AND delCandidates.JobStatus!='T') OR "
152 "AND Job.ClientId=%u "
155 /* Select Jobs from the DelCandidates table.
156 * This is the list of Jobs to delete for an Admin Job.
158 const char *select_admin_del =
159 "SELECT DISTINCT DelCandidates.JobId "
160 "FROM Job,DelCandidates "
161 "WHERE (Job.JobTdate<%s AND delCandidates.JobStatus!='T') OR "
163 "AND Job.ClientId=%u "
167 /* ======= ua_restore.c */
168 const char *uar_count_files =
169 "SELECT JobFiles FROM Job WHERE JobId=%u";
171 /* List last 20 Jobs */
172 const char *uar_list_jobs =
173 "SELECT JobId,Client.Name as Client,StartTime,Level as "
174 "JobLevel,JobFiles,JobBytes "
175 "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus='T' "
176 "AND Type='B' ORDER BY StartTime DESC LIMIT 20";
179 /* MYSQL IS NOT STANDARD SQL !!!!! */
180 /* List Jobs where a particular file is saved */
181 const char *uar_file =
182 "SELECT Job.JobId as JobId, Client.Name as Client, "
183 "CONCAT(Path.Path,Filename.Name) as Name, "
184 "StartTime,Type as JobType,JobFiles,JobBytes "
185 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
186 "AND JobStatus='T' AND Job.JobId=File.JobId "
187 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
188 "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20";
190 /* List Jobs where a particular file is saved */
191 const char *uar_file =
192 "SELECT Job.JobId as JobId, Client.Name as Client, "
193 "Path.Path||Filename.Name as Name, "
194 "StartTime,Type as JobType,JobFiles,JobBytes "
195 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
196 "AND JobStatus='T' AND Job.JobId=File.JobId "
197 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
198 "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20";
203 * Find all files for a particular JobId and insert them into
204 * the tree during a restore.
206 const char *uar_sel_files =
207 "SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat "
208 "FROM File,Filename,Path "
209 "WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId "
210 "AND Path.PathId=File.PathId";
212 const char *uar_del_temp = "DROP TABLE temp";
213 const char *uar_del_temp1 = "DROP TABLE temp1";
215 const char *uar_create_temp =
216 "CREATE TABLE temp ("
217 #ifdef HAVE_POSTGRESQL
218 "JobId INTEGER NOT NULL,"
226 "VolSessionId INTEGER,"
227 "VolSessionTime INTEGER)";
229 "JobId INTEGER UNSIGNED NOT NULL,"
230 "JobTDate BIGINT UNSIGNED,"
231 "ClientId INTEGER UNSIGNED,"
233 "JobFiles INTEGER UNSIGNED,"
236 "StartFile INTEGER UNSIGNED,"
237 "VolSessionId INTEGER UNSIGNED,"
238 "VolSessionTime INTEGER UNSIGNED)";
241 const char *uar_create_temp1 =
242 "CREATE TABLE temp1 ("
243 #ifdef HAVE_POSTGRESQL
244 "JobId INTEGER NOT NULL,"
247 "JobId INTEGER UNSIGNED NOT NULL,"
248 "JobTDate BIGINT UNSIGNED)";
251 const char *uar_last_full =
252 "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
253 "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%u "
254 "AND Job.ClientId=%u "
255 "AND Job.StartTime<'%s' "
256 "AND Level='F' AND JobStatus='T' "
257 "AND JobMedia.JobId=Job.JobId "
258 "AND JobMedia.MediaId=Media.MediaId "
259 "AND Job.FileSetId=FileSet.FileSetId "
260 "AND FileSet.FileSet='%s' "
262 "ORDER BY Job.JobTDate DESC LIMIT 1";
264 const char *uar_full =
265 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
266 " Job.ClientId,Job.Level,Job.JobFiles,"
267 " StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
268 "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
269 "AND Level='F' AND JobStatus='T' "
270 "AND JobMedia.JobId=Job.JobId "
271 "AND JobMedia.MediaId=Media.MediaId";
273 const char *uar_dif =
274 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
275 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
276 "Job.VolSessionId,Job.VolSessionTime "
277 "FROM Job,JobMedia,Media,FileSet "
278 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
279 "AND Job.ClientId=%u "
280 "AND JobMedia.JobId=Job.JobId "
281 "AND JobMedia.MediaId=Media.MediaId "
282 "AND Job.Level='D' AND JobStatus='T' "
283 "AND Job.FileSetId=FileSet.FileSetId "
284 "AND FileSet.FileSet='%s' "
286 "ORDER BY Job.JobTDate DESC LIMIT 1";
288 const char *uar_inc =
289 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
290 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
291 "Job.VolSessionId,Job.VolSessionTime "
292 "FROM Job,JobMedia,Media,FileSet "
293 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
294 "AND Job.ClientId=%u "
295 "AND JobMedia.JobId=Job.JobId "
296 "AND JobMedia.MediaId=Media.MediaId "
297 "AND Job.Level='I' AND JobStatus='T' "
298 "AND Job.FileSetId=FileSet.FileSetId "
299 "AND FileSet.FileSet='%s' "
302 const char *uar_list_temp =
303 "SELECT JobId,Level,JobFiles,StartTime,VolumeName,StartFile,"
304 "VolSessionId,VolSessionTime FROM temp "
305 "ORDER BY StartTime ASC";
308 const char *uar_sel_jobid_temp = "SELECT JobId FROM temp ORDER BY StartTime ASC";
310 const char *uar_sel_all_temp1 = "SELECT * FROM temp1";
312 const char *uar_sel_all_temp = "SELECT * FROM temp";
316 /* Select FileSet names for this Client */
317 const char *uar_sel_fileset =
318 "SELECT DISTINCT FileSet.FileSet FROM Job,"
319 "Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId "
320 "AND Job.ClientId=%u AND Client.ClientId=%u "
321 "ORDER BY FileSet.FileSet";
323 /* Find MediaType used by this Job */
324 const char *uar_mediatype =
325 "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%u "
326 "AND JobMedia.MediaId=Media.MediaId";
329 * Find JobId, FileIndex for a given path/file and date
330 * for use when inserting individual files into the tree.
332 const char *uar_jobid_fileindex =
333 "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
334 "WHERE Job.JobId=File.JobId "
335 "AND Job.StartTime<'%s' "
336 "AND Path.Path='%s' "
337 "AND Filename.Name='%s' "
338 "AND Client.Name='%s' "
339 "AND Job.ClientId=Client.ClientId "
340 "AND Path.PathId=File.PathId "
341 "AND Filename.FilenameId=File.FilenameId "
342 "ORDER BY Job.StartTime DESC LIMIT 1";
344 const char *uar_jobids_fileindex =
345 "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
346 "WHERE Job.JobId IN (%s) "
347 "AND Job.JobId=File.JobId "
348 "AND Job.StartTime<'%s' "
349 "AND Path.Path='%s' "
350 "AND Filename.Name='%s' "
351 "AND Client.Name='%s' "
352 "AND Job.ClientId=Client.ClientId "
353 "AND Path.PathId=File.PathId "
354 "AND Filename.FilenameId=File.FilenameId "
355 "ORDER BY Job.StartTime DESC LIMIT 1";