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 /* ====== ua_prune.c */
38 const char *cnt_File = "SELECT count(*) FROM File WHERE JobId=%u";
39 const char *del_File = "DELETE FROM File WHERE JobId=%u";
40 const char *upd_Purged = "UPDATE Job Set PurgedFiles=1 WHERE JobId=%u";
41 const char *cnt_DelCand = "SELECT count(*) FROM DelCandidates";
42 const char *del_Job = "DELETE FROM Job WHERE JobId=%u";
43 const char *del_JobMedia = "DELETE FROM JobMedia WHERE JobId=%u";
44 const char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%u";
45 const char *sel_JobMedia = "SELECT JobId FROM JobMedia WHERE MediaId=%u";
47 /* Select JobIds for File deletion. */
48 const char *select_job =
49 "SELECT JobId from Job "
54 /* Delete temp tables and indexes */
55 const char *drop_deltabs[] = {
56 "DROP TABLE DelCandidates",
61 /* List of SQL commands to create temp table and indicies */
62 const char *create_deltabs[] = {
63 "CREATE TABLE DelCandidates ("
65 "JobId INTEGER UNSIGNED NOT NULL, "
66 "PurgedFiles TINYINT, "
67 "FileSetId INTEGER UNSIGNED, "
68 "JobFiles INTEGER UNSIGNED, "
69 "JobStatus BINARY(1))",
71 #ifdef HAVE_POSTGRESQL
72 "JobId INTEGER NOT NULL, "
73 "PurgedFiles SMALLINT, "
78 "JobId INTEGER UNSIGNED NOT NULL, "
79 "PurgedFiles TINYINT, "
80 "FileSetId INTEGER UNSIGNED, "
81 "JobFiles INTEGER UNSIGNED, "
85 "CREATE INDEX DelInx1 ON DelCandidates (JobId)",
88 /* Fill candidates table with all Jobs subject to being deleted.
89 * This is used for pruning Jobs (first the files, then the Jobs).
91 const char *insert_delcand =
92 "INSERT INTO DelCandidates "
93 "SELECT JobId,PurgedFiles,FileSetId,JobFiles,JobStatus FROM Job "
98 /* Select Jobs from the DelCandidates table that have a
99 * more recent backup -- i.e. are not the only backup.
100 * This is the list of Jobs to delete for a Backup Job.
101 * At the same time, we select "orphanned" jobs
102 * (i.e. no files, ...) for deletion.
104 const char *select_backup_del =
105 "SELECT DelCandidates.JobId "
106 "FROM Job,DelCandidates "
107 "WHERE (JobTDate<%s AND ((DelCandidates.JobFiles=0) OR "
108 "(DelCandidates.JobStatus!='T'))) OR "
110 "AND Job.ClientId=%u "
113 "AND Job.JobStatus='T' "
114 "AND Job.FileSetId=DelCandidates.FileSetId) "
115 "GROUP BY DelCandidates.JobId";
117 /* Select Jobs from the DelCandidates table that have a
118 * more recent InitCatalog -- i.e. are not the only InitCatalog
119 * This is the list of Jobs to delete for a Verify Job.
121 const char *select_verify_del =
122 "SELECT DelCandidates.JobId "
123 "FROM Job,DelCandidates "
124 "WHERE Job.JobTDate>%s "
125 "AND Job.ClientId=%u "
128 "AND Job.JobStatus='T' "
129 "AND Job.FileSetId=DelCandidates.FileSetId "
130 "GROUP BY DelCandidates.JobId";
133 /* Select Jobs from the DelCandidates table.
134 * This is the list of Jobs to delete for a Restore Job.
136 const char *select_restore_del =
137 "SELECT DelCandidates.JobId "
138 "FROM Job,DelCandidates "
139 "WHERE Job.JobTDate>%s "
140 "AND Job.ClientId=%u "
142 "GROUP BY DelCandidates.JobId";
146 /* ======= ua_restore.c */
147 const char *uar_count_files =
148 "SELECT JobFiles FROM Job WHERE JobId=%u";
150 /* List last 20 Jobs */
151 const char *uar_list_jobs =
152 "SELECT JobId,Client.Name as Client,StartTime,Level as "
153 "JobLevel,JobFiles,JobBytes "
154 "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus='T' "
155 "AND Type='B' ORDER BY StartTime DESC LIMIT 20";
158 /* MYSQL IS NOT STANDARD SQL !!!!! */
159 /* List Jobs where a particular file is saved */
160 const char *uar_file =
161 "SELECT Job.JobId as JobId, Client.Name as Client, "
162 "CONCAT(Path.Path,Filename.Name) as Name, "
163 "StartTime,Type as JobType,JobFiles,JobBytes "
164 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
165 "AND JobStatus='T' AND Job.JobId=File.JobId "
166 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
167 "AND Filename.Name='%s' LIMIT 20";
169 /* List Jobs where a particular file is saved */
170 const char *uar_file =
171 "SELECT Job.JobId as JobId, Client.Name as Client, "
172 "Path.Path||Filename.Name as Name, "
173 "StartTime,Type as JobType,JobFiles,JobBytes "
174 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
175 "AND JobStatus='T' AND Job.JobId=File.JobId "
176 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
177 "AND Filename.Name='%s' LIMIT 20";
182 * Find all files for a particular JobId and insert them into
183 * the tree during a restore.
185 const char *uar_sel_files =
186 "SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat "
187 "FROM File,Filename,Path "
188 "WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId "
189 "AND Path.PathId=File.PathId";
191 const char *uar_del_temp = "DROP TABLE temp";
192 const char *uar_del_temp1 = "DROP TABLE temp1";
194 const char *uar_create_temp =
195 "CREATE TABLE temp ("
196 #ifdef HAVE_POSTGRESQL
197 "JobId INTEGER NOT NULL,"
205 "VolSessionId INTEGER,"
206 "VolSessionTime INTEGER)";
208 "JobId INTEGER UNSIGNED NOT NULL,"
209 "JobTDate BIGINT UNSIGNED,"
210 "ClientId INTEGER UNSIGNED,"
212 "JobFiles INTEGER UNSIGNED,"
215 "StartFile INTEGER UNSIGNED,"
216 "VolSessionId INTEGER UNSIGNED,"
217 "VolSessionTime INTEGER UNSIGNED)";
220 const char *uar_create_temp1 =
221 "CREATE TABLE temp1 ("
222 #ifdef HAVE_POSTGRESQL
223 "JobId INTEGER NOT NULL,"
226 "JobId INTEGER UNSIGNED NOT NULL,"
227 "JobTDate BIGINT UNSIGNED)";
230 const char *uar_last_full =
231 "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
232 "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%u "
233 "AND Job.ClientId=%u "
234 "AND Job.StartTime<'%s' "
235 "AND Level='F' AND JobStatus='T' "
236 "AND JobMedia.JobId=Job.JobId "
237 "AND JobMedia.MediaId=Media.MediaId "
238 "AND Job.FileSetId=FileSet.FileSetId "
239 "AND FileSet.FileSet='%s' "
241 "ORDER BY Job.JobTDate DESC LIMIT 1";
243 const char *uar_full =
244 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
245 " Job.ClientId,Job.Level,Job.JobFiles,"
246 " StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
247 "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
248 "AND Level='F' AND JobStatus='T' "
249 "AND JobMedia.JobId=Job.JobId "
250 "AND JobMedia.MediaId=Media.MediaId";
252 const char *uar_dif =
253 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
254 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
255 "Job.VolSessionId,Job.VolSessionTime "
256 "FROM Job,JobMedia,Media,FileSet "
257 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
258 "AND Job.ClientId=%u "
259 "AND JobMedia.JobId=Job.JobId "
260 "AND JobMedia.MediaId=Media.MediaId "
261 "AND Job.Level='D' AND JobStatus='T' "
262 "AND Job.FileSetId=FileSet.FileSetId "
263 "AND FileSet.FileSet='%s' "
265 "ORDER BY Job.JobTDate DESC LIMIT 1";
267 const char *uar_inc =
268 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
269 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
270 "Job.VolSessionId,Job.VolSessionTime "
271 "FROM Job,JobMedia,Media,FileSet "
272 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
273 "AND Job.ClientId=%u "
274 "AND JobMedia.JobId=Job.JobId "
275 "AND JobMedia.MediaId=Media.MediaId "
276 "AND Job.Level='I' AND JobStatus='T' "
277 "AND Job.FileSetId=FileSet.FileSetId "
278 "AND FileSet.FileSet='%s' "
281 const char *uar_list_temp =
282 "SELECT JobId,Level,JobFiles,StartTime,VolumeName,StartFile,"
283 "VolSessionId,VolSessionTime FROM temp "
284 "ORDER BY StartTime ASC";
287 const char *uar_sel_jobid_temp = "SELECT JobId FROM temp";
289 const char *uar_sel_all_temp1 = "SELECT * FROM temp1";
291 const char *uar_sel_all_temp = "SELECT * FROM temp";
295 /* Select FileSet names for this Client */
296 const char *uar_sel_fileset =
297 "SELECT DISTINCT FileSet.FileSet FROM Job,"
298 "Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId "
299 "AND Job.ClientId=%u AND Client.ClientId=%u "
300 "ORDER BY FileSet.FileSet";
302 /* Find MediaType used by this Job */
303 const char *uar_mediatype =
304 "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%u "
305 "AND JobMedia.MediaId=Media.MediaId";
308 * Find JobId, FileIndex for a given path/file and date
309 * for use when inserting individual files into the tree.
311 const char *uar_jobid_fileindex =
312 "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
313 "WHERE Job.JobId=File.JobId "
314 "AND Job.StartTime<'%s' "
315 "AND Path.Path='%s' "
316 "AND Filename.Name='%s' "
317 "AND Client.Name='%s' "
318 "AND Path.PathId=File.PathId "
319 "AND Filename.FilenameId=File.FilenameId "
320 "ORDER BY Job.StartTime DESC LIMIT 1";