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";
180 * Find all files for a particular JobId and insert them into
181 * the tree during a restore.
183 char *uar_sel_files =
184 "SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat "
185 "FROM File,Filename,Path "
186 "WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId "
187 "AND Path.PathId=File.PathId";
189 char *uar_del_temp = "DROP TABLE temp";
190 char *uar_del_temp1 = "DROP TABLE temp1";
192 char *uar_create_temp =
193 "CREATE TABLE temp ("
194 #ifdef HAVE_POSTGRESQL
195 "JobId INTEGER NOT NULL,"
203 "VolSessionId INTEGER,"
204 "VolSessionTime INTEGER)";
206 "JobId INTEGER UNSIGNED NOT NULL,"
207 "JobTDate BIGINT UNSIGNED,"
208 "ClientId INTEGER UNSIGNED,"
210 "JobFiles INTEGER UNSIGNED,"
213 "StartFile INTEGER UNSIGNED,"
214 "VolSessionId INTEGER UNSIGNED,"
215 "VolSessionTime INTEGER UNSIGNED)";
218 char *uar_create_temp1 =
219 "CREATE TABLE temp1 ("
220 #ifdef HAVE_POSTGRESQL
221 "JobId INTEGER NOT NULL,"
224 "JobId INTEGER UNSIGNED NOT NULL,"
225 "JobTDate BIGINT UNSIGNED)";
228 char *uar_last_full =
229 "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
230 "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%u "
231 "AND Job.ClientId=%u "
232 "AND Job.StartTime<'%s' "
233 "AND Level='F' AND JobStatus='T' "
234 "AND JobMedia.JobId=Job.JobId "
235 "AND JobMedia.MediaId=Media.MediaId "
236 "AND Job.FileSetId=FileSet.FileSetId "
237 "AND FileSet.FileSet='%s' "
239 "ORDER BY Job.JobTDate DESC LIMIT 1";
242 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
243 " Job.ClientId,Job.Level,Job.JobFiles,"
244 " StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
245 "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
246 "AND Level='F' AND JobStatus='T' "
247 "AND JobMedia.JobId=Job.JobId "
248 "AND JobMedia.MediaId=Media.MediaId";
251 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
252 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
253 "Job.VolSessionId,Job.VolSessionTime "
254 "FROM Job,JobMedia,Media,FileSet "
255 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
256 "AND Job.ClientId=%u "
257 "AND JobMedia.JobId=Job.JobId "
258 "AND JobMedia.MediaId=Media.MediaId "
259 "AND Job.Level='D' AND JobStatus='T' "
260 "AND Job.FileSetId=FileSet.FileSetId "
261 "AND FileSet.FileSet='%s' "
263 "ORDER BY Job.JobTDate DESC LIMIT 1";
266 "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId,"
267 "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile,"
268 "Job.VolSessionId,Job.VolSessionTime "
269 "FROM Job,JobMedia,Media,FileSet "
270 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' "
271 "AND Job.ClientId=%u "
272 "AND JobMedia.JobId=Job.JobId "
273 "AND JobMedia.MediaId=Media.MediaId "
274 "AND Job.Level='I' AND JobStatus='T' "
275 "AND Job.FileSetId=FileSet.FileSetId "
276 "AND FileSet.FileSet='%s' "
279 char *uar_list_temp =
280 "SELECT JobId,Level,JobFiles,StartTime,VolumeName,StartFile,"
281 "VolSessionId,VolSessionTime FROM temp "
282 "ORDER BY StartTime ASC";
285 char *uar_sel_jobid_temp = "SELECT JobId FROM temp";
287 char *uar_sel_all_temp1 = "SELECT * FROM temp1";
289 char *uar_sel_all_temp = "SELECT * FROM temp";
293 /* Select FileSet names for this Client */
294 char *uar_sel_fileset =
295 "SELECT DISTINCT FileSet.FileSet FROM Job,"
296 "Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId "
297 "AND Job.ClientId=%u AND Client.ClientId=%u "
298 "ORDER BY FileSet.FileSet";
300 /* Find MediaType used by this Job */
301 char *uar_mediatype =
302 "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%u "
303 "AND JobMedia.MediaId=Media.MediaId";
306 * Find JobId, FileIndex for a given path/file and date
307 * for use when inserting individual files into the tree.
309 char *uar_jobid_fileindex =
310 "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client "
311 "WHERE Job.JobId=File.JobId "
312 "AND Job.StartTime<'%s' "
313 "AND Path.Path='%s' "
314 "AND Filename.Name='%s' "
315 "AND Client.Name='%s' "
316 "AND Path.PathId=File.PathId "
317 "AND Filename.FilenameId=File.FilenameId "
318 "ORDER BY Job.StartTime DESC LIMIT 1";