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 char *list_pool = "SELECT * FROM Pool WHERE PoolId=%u";
36 /* ====== ua_prune.c */
38 char *cnt_File = "SELECT count(*) FROM File WHERE JobId=%u";
39 char *del_File = "DELETE FROM File WHERE JobId=%u";
40 char *upd_Purged = "UPDATE Job Set PurgedFiles=1 WHERE JobId=%u";
41 char *cnt_DelCand = "SELECT count(*) FROM DelCandidates";
42 char *del_Job = "DELETE FROM Job WHERE JobId=%u";
43 char *del_JobMedia = "DELETE FROM JobMedia WHERE JobId=%u";
44 char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%u";
45 char *sel_JobMedia = "SELECT JobId FROM JobMedia WHERE MediaId=%u";
47 /* Select JobIds for File deletion. */
49 "SELECT JobId from Job "
54 /* Delete temp tables and indexes */
55 char *drop_deltabs[] = {
56 "DROP TABLE DelCandidates",
61 /* List of SQL commands to create temp table and indicies */
62 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 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 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 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 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 */
148 /* List last 20 Jobs */
149 char *uar_list_jobs =
150 "SELECT JobId,Client.Name as Client,StartTime,Level as "
151 "JobLevel,JobFiles,JobBytes "
152 "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus='T' "
153 "AND Type='B' ORDER BY StartTime DESC LIMIT 20";
156 /* MYSQL IS NOT STANDARD SQL !!!!! */
157 /* List Jobs where a particular file is saved */
159 "SELECT Job.JobId as JobId, Client.Name as Client, "
160 "CONCAT(Path.Path,Filename.Name) as Name, "
161 "StartTime,Type as JobType,JobFiles,JobBytes "
162 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
163 "AND JobStatus='T' AND Job.JobId=File.JobId "
164 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
165 "AND Filename.Name='%s' LIMIT 20";
167 /* List Jobs where a particular file is saved */
169 "SELECT Job.JobId as JobId, Client.Name as Client, "
170 "Path.Path||Filename.Name as Name, "
171 "StartTime,Type as JobType,JobFiles,JobBytes "
172 "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
173 "AND JobStatus='T' AND Job.JobId=File.JobId "
174 "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
175 "AND Filename.Name='%s' LIMIT 20";
179 char *uar_sel_files =
180 "SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat "
181 "FROM File,Filename,Path "
182 "WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId "
183 "AND Path.PathId=File.PathId";
185 char *uar_del_temp = "DROP TABLE temp";
186 char *uar_del_temp1 = "DROP TABLE temp1";
188 char *uar_create_temp =
189 "CREATE TABLE temp ("
190 #ifdef HAVE_POSTGRESQL
191 "JobId INTEGER NOT NULL,"
199 "VolSessionId INTEGER,"
200 "VolSessionTime INTEGER)";
202 "JobId INTEGER UNSIGNED NOT NULL,"
203 "JobTDate BIGINT UNSIGNED,"
204 "ClientId INTEGER UNSIGNED,"
206 "JobFiles INTEGER UNSIGNED,"
209 "StartFile INTEGER UNSIGNED,"
210 "VolSessionId INTEGER UNSIGNED,"
211 "VolSessionTime INTEGER UNSIGNED)";
214 char *uar_create_temp1 =
215 "CREATE TABLE temp1 ("
216 #ifdef HAVE_POSTGRESQL
217 "JobId INTEGER NOT NULL,"
220 "JobId INTEGER UNSIGNED NOT NULL,"
221 "JobTDate BIGINT UNSIGNED)";
224 char *uar_last_full =
225 "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
226 "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%u "
227 "AND Job.ClientId=%u "
228 "AND Job.StartTime<'%s' "
229 "AND Level='F' AND JobStatus='T' "
230 "AND JobMedia.JobId=Job.JobId "
231 "AND JobMedia.MediaId=Media.MediaId "
232 "AND Job.FileSetId=FileSet.FileSetId "
233 "AND FileSet.FileSet='%s' "
235 "ORDER BY Job.JobTDate DESC LIMIT 1";
238 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
239 " Job.ClientId,Job.Level,Job.JobFiles,"
240 " StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
241 "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
242 "AND Level='F' AND JobStatus='T' "
243 "AND JobMedia.JobId=Job.JobId "
244 "AND JobMedia.MediaId=Media.MediaId";
247 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
248 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
249 "Job.VolSessionId,Job.VolSessionTime "
250 "FROM Job,JobMedia,Media,FileSet "
251 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
252 "AND Job.ClientId=%u "
253 "AND JobMedia.JobId=Job.JobId "
254 "AND JobMedia.MediaId=Media.MediaId "
255 "AND Job.Level='D' AND JobStatus='T' "
256 "AND Job.FileSetId=FileSet.FileSetId "
257 "AND FileSet.FileSet='%s' "
259 "ORDER BY Job.JobTDate DESC LIMIT 1";
262 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
263 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
264 "Job.VolSessionId,Job.VolSessionTime "
265 "FROM Job,JobMedia,Media,FileSet "
266 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
267 "AND Job.ClientId=%u "
268 "AND JobMedia.JobId=Job.JobId "
269 "AND JobMedia.MediaId=Media.MediaId "
270 "AND Job.Level='I' AND JobStatus='T' "
271 "AND Job.FileSetId=FileSet.FileSetId "
272 "AND FileSet.FileSet='%s' "
275 char *uar_list_temp =
276 "SELECT JobId,Level,JobFiles,StartTime,VolumeName,StartFile,"
277 "VolSessionId,VolSessionTime FROM temp "
278 "ORDER BY StartTime ASC";
281 char *uar_sel_jobid_temp = "SELECT JobId FROM temp";
283 char *uar_sel_all_temp1 = "SELECT * FROM temp1";
285 char *uar_sel_all_temp = "SELECT * FROM temp";
289 /* Select FileSet names for this Client */
290 char *uar_sel_fileset =
291 "SELECT DISTINCT FileSet.FileSet FROM Job,"
292 "Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId "
293 "AND Job.ClientId=%u AND Client.ClientId=%u "
294 "ORDER BY FileSet.FileSet";
296 /* Find MediaType used by this Job */
297 char *uar_mediatype =
298 "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%u "
299 "AND JobMedia.MediaId=Media.MediaId";
301 /* Find JobId, FileIndex for a given path/file and date */
302 char *uar_jobid_fileindex =
303 "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
304 "WHERE Job.JobId=File.JobId "
305 "AND Job.StartTime<'%s' "
306 "AND Path.Path='%s' "
307 "AND Filename.Name='%s' "
308 "AND Client.Name='%s' "
309 "AND Path.PathId=File.PathId "
310 "AND Filename.FilenameId=File.FilenameId "
311 "ORDER BY Job.StartTime DESC LIMIT 1";