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 DelCandidates.JobId "
118 "FROM Job,DelCandidates "
119 "WHERE (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) "
127 "GROUP BY DelCandidates.JobId";
129 /* Select Jobs from the DelCandidates table that have a
130 * more recent InitCatalog -- i.e. are not the only InitCatalog
131 * This is the list of Jobs to delete for a Verify Job.
133 const char *select_verify_del =
134 "SELECT DelCandidates.JobId "
135 "FROM Job,DelCandidates "
136 "WHERE Job.JobTDate>%s "
137 "AND Job.ClientId=%u "
140 "AND Job.JobStatus='T' "
141 "AND Job.FileSetId=DelCandidates.FileSetId "
142 "GROUP BY DelCandidates.JobId";
145 /* Select Jobs from the DelCandidates table.
146 * This is the list of Jobs to delete for a Restore Job.
148 const char *select_restore_del =
149 "SELECT DelCandidates.JobId "
150 "FROM Job,DelCandidates "
151 "WHERE Job.JobTDate>%s "
152 "AND Job.ClientId=%u "
154 "GROUP BY DelCandidates.JobId";
158 /* ======= ua_restore.c */
159 const char *uar_count_files =
160 "SELECT JobFiles FROM Job WHERE JobId=%u";
162 /* List last 20 Jobs */
163 const char *uar_list_jobs =
164 "SELECT JobId,Client.Name as Client,StartTime,Level as "
165 "JobLevel,JobFiles,JobBytes "
166 "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus='T' "
167 "AND Type='B' ORDER BY StartTime DESC LIMIT 20";
170 /* MYSQL IS NOT STANDARD SQL !!!!! */
171 /* List Jobs where a particular file is saved */
172 const char *uar_file =
173 "SELECT Job.JobId as JobId, Client.Name as Client, "
174 "CONCAT(Path.Path,Filename.Name) as Name, "
175 "StartTime,Type as JobType,JobFiles,JobBytes "
176 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
177 "AND JobStatus='T' AND Job.JobId=File.JobId "
178 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
179 "AND Filename.Name='%s' LIMIT 20";
181 /* List Jobs where a particular file is saved */
182 const char *uar_file =
183 "SELECT Job.JobId as JobId, Client.Name as Client, "
184 "Path.Path||Filename.Name as Name, "
185 "StartTime,Type as JobType,JobFiles,JobBytes "
186 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
187 "AND JobStatus='T' AND Job.JobId=File.JobId "
188 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
189 "AND Filename.Name='%s' LIMIT 20";
194 * Find all files for a particular JobId and insert them into
195 * the tree during a restore.
197 const char *uar_sel_files =
198 "SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat "
199 "FROM File,Filename,Path "
200 "WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId "
201 "AND Path.PathId=File.PathId";
203 const char *uar_del_temp = "DROP TABLE temp";
204 const char *uar_del_temp1 = "DROP TABLE temp1";
206 const char *uar_create_temp =
207 "CREATE TABLE temp ("
208 #ifdef HAVE_POSTGRESQL
209 "JobId INTEGER NOT NULL,"
217 "VolSessionId INTEGER,"
218 "VolSessionTime INTEGER)";
220 "JobId INTEGER UNSIGNED NOT NULL,"
221 "JobTDate BIGINT UNSIGNED,"
222 "ClientId INTEGER UNSIGNED,"
224 "JobFiles INTEGER UNSIGNED,"
227 "StartFile INTEGER UNSIGNED,"
228 "VolSessionId INTEGER UNSIGNED,"
229 "VolSessionTime INTEGER UNSIGNED)";
232 const char *uar_create_temp1 =
233 "CREATE TABLE temp1 ("
234 #ifdef HAVE_POSTGRESQL
235 "JobId INTEGER NOT NULL,"
238 "JobId INTEGER UNSIGNED NOT NULL,"
239 "JobTDate BIGINT UNSIGNED)";
242 const char *uar_last_full =
243 "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
244 "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%u "
245 "AND Job.ClientId=%u "
246 "AND Job.StartTime<'%s' "
247 "AND Level='F' AND JobStatus='T' "
248 "AND JobMedia.JobId=Job.JobId "
249 "AND JobMedia.MediaId=Media.MediaId "
250 "AND Job.FileSetId=FileSet.FileSetId "
251 "AND FileSet.FileSet='%s' "
253 "ORDER BY Job.JobTDate DESC LIMIT 1";
255 const char *uar_full =
256 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
257 " Job.ClientId,Job.Level,Job.JobFiles,"
258 " StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
259 "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
260 "AND Level='F' AND JobStatus='T' "
261 "AND JobMedia.JobId=Job.JobId "
262 "AND JobMedia.MediaId=Media.MediaId";
264 const char *uar_dif =
265 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
266 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
267 "Job.VolSessionId,Job.VolSessionTime "
268 "FROM Job,JobMedia,Media,FileSet "
269 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
270 "AND Job.ClientId=%u "
271 "AND JobMedia.JobId=Job.JobId "
272 "AND JobMedia.MediaId=Media.MediaId "
273 "AND Job.Level='D' AND JobStatus='T' "
274 "AND Job.FileSetId=FileSet.FileSetId "
275 "AND FileSet.FileSet='%s' "
277 "ORDER BY Job.JobTDate DESC LIMIT 1";
279 const char *uar_inc =
280 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
281 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
282 "Job.VolSessionId,Job.VolSessionTime "
283 "FROM Job,JobMedia,Media,FileSet "
284 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
285 "AND Job.ClientId=%u "
286 "AND JobMedia.JobId=Job.JobId "
287 "AND JobMedia.MediaId=Media.MediaId "
288 "AND Job.Level='I' AND JobStatus='T' "
289 "AND Job.FileSetId=FileSet.FileSetId "
290 "AND FileSet.FileSet='%s' "
293 const char *uar_list_temp =
294 "SELECT JobId,Level,JobFiles,StartTime,VolumeName,StartFile,"
295 "VolSessionId,VolSessionTime FROM temp "
296 "ORDER BY StartTime ASC";
299 const char *uar_sel_jobid_temp = "SELECT JobId FROM temp ORDER BY StartTime ASC";
301 const char *uar_sel_all_temp1 = "SELECT * FROM temp1";
303 const char *uar_sel_all_temp = "SELECT * FROM temp";
307 /* Select FileSet names for this Client */
308 const char *uar_sel_fileset =
309 "SELECT DISTINCT FileSet.FileSet FROM Job,"
310 "Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId "
311 "AND Job.ClientId=%u AND Client.ClientId=%u "
312 "ORDER BY FileSet.FileSet";
314 /* Find MediaType used by this Job */
315 const char *uar_mediatype =
316 "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%u "
317 "AND JobMedia.MediaId=Media.MediaId";
320 * Find JobId, FileIndex for a given path/file and date
321 * for use when inserting individual files into the tree.
323 const char *uar_jobid_fileindex =
324 "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
325 "WHERE Job.JobId=File.JobId "
326 "AND Job.StartTime<'%s' "
327 "AND Path.Path='%s' "
328 "AND Filename.Name='%s' "
329 "AND Client.Name='%s' "
330 "AND Job.ClientId=Client.ClientId "
331 "AND Path.PathId=File.PathId "
332 "AND Filename.FilenameId=File.FilenameId "
333 "ORDER BY Job.StartTime DESC LIMIT 1";