From ba79141c66d05a967e5c9daf819035882993cd43 Mon Sep 17 00:00:00 2001 From: Kern Sibbald Date: Tue, 6 Aug 2002 10:22:52 +0000 Subject: [PATCH] add new file git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@78 91ce42f0-d328-0410-95d8-f526ca767f89 --- bacula/src/dird/sql_cmds.c | 192 +++++++++++++++++++++++++++++++++++++ 1 file changed, 192 insertions(+) create mode 100644 bacula/src/dird/sql_cmds.c diff --git a/bacula/src/dird/sql_cmds.c b/bacula/src/dird/sql_cmds.c new file mode 100644 index 0000000000..090229ab88 --- /dev/null +++ b/bacula/src/dird/sql_cmds.c @@ -0,0 +1,192 @@ +/* + * + * This file contains all the SQL commands issued by the Director + * + * Kern Sibbald, July MMII + * + * Version $Id$ + */ + +/* + Copyright (C) 2002 Kern Sibbald and John Walker + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU General Public License as + published by the Free Software Foundation; either version 2 of + the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + General Public License for more details. + + You should have received a copy of the GNU General Public + License along with this program; if not, write to the Free + Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, + MA 02111-1307, USA. + + */ + +#include "bacula.h" +#include "dird.h" + +/* ====== ua_prune.c */ + +char *cnt_File = "SELECT count(*) FROM File WHERE JobId=%u"; +char *del_File = "DELETE FROM File WHERE JobId=%u"; +char *upd_Purged = "UPDATE Job Set PurgedFiles=1 WHERE JobId=%u"; +char *cnt_DelCand = "SELECT count(*) FROM DelCandidates"; +char *del_Job = "DELETE FROM Job WHERE JobId=%u"; +char *del_JobMedia = "DELETE FROM JobMedia WHERE JobId=%u"; +char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%u"; +char *sel_JobMedia = "SELECT JobId FROM JobMedia WHERE MediaId=%u"; + +/* Select JobIds for File deletion. */ +char *select_job = + "SELECT JobId from Job " + "WHERE JobTDate < %s " + "AND ClientId=%d " + "AND PurgedFiles=0"; + +/* Delete temp tables and indexes */ +char *drop_deltabs[] = { + "DROP TABLE DelCandidates", + "DROP INDEX DelInx1", + NULL}; + +/* List of SQL commands to create temp table and indicies */ +char *create_deltabs[] = { + "CREATE TABLE DelCandidates (" + "JobId INTEGER UNSIGNED NOT NULL, " + "PurgedFiles TINYINT, " + "FileSetId INTEGER UNSIGNED)", + "CREATE INDEX DelInx1 ON DelCandidates (JobId)", + NULL}; + +/* Fill candidates table with all Files subject to being deleted. + * This is used for pruning Jobs (first the files, then the Jobs). + */ +char *insert_delcand = + "INSERT INTO DelCandidates " + "SELECT JobId, PurgedFiles, FileSetId FROM Job " + "WHERE JobTDate < %s " + "AND ClientId=%d"; + +/* Select files from the DelCandidates table that have a + * more recent backup -- i.e. are not the only backup. + * This is the list of files to delete for a Backup Job. + */ +char *select_backup_del = + "SELECT DelCandidates.JobId " + "FROM Job,DelCandidates " + "WHERE Job.JobTDate >= %s " + "AND Job.ClientId=%d " + "AND Job.JobType='B' " + "AND Job.Level='F' " + "AND Job.JobStatus='T' " + "AND Job.FileSetId=DelCandidates.FileSetId"; + +/* Select files from the DelCandidates table that have a + * more recent InitCatalog -- i.e. are not the only InitCatalog + * This is the list of files to delete for a Verify Job. + */ +char *select_verify_del = + "SELECT DelCandidates.JobId " + "FROM Job,DelCandidates " + "WHERE Job.JobTDate >= %s " + "AND Job.ClientId=%d " + "AND Job.JobType='V' " + "AND Job.Level='V' " + "AND Job.JobStatus='T' " + "AND Job.FileSetId=DelCandidates.FileSetId"; + + +/* Select files from the DelCandidates table. + * This is the list of files to delete for a Restore Job. + */ +char *select_restore_del = + "SELECT DelCandidates.JobId " + "FROM Job,DelCandidates " + "WHERE Job.JobTDate >= %s " + "AND Job.ClientId=%d " + "AND Job.JobType='R'"; + + + +/* ======= ua_restore.c */ + +char *uar_list_jobs = + "SELECT JobId,Client.Name as Client,StartTime,Type as " + "JobType,JobFiles,JobBytes " + "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus='T' " + "LIMIT 20"; + +char *uar_file = + "SELECT Job.JobId as JobId, Client.Name as Client, " + "CONCAT(Path.Path,Filename.Name) as Name, " + "StartTime,Type as JobType,JobFiles,JobBytes " + "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId " + "AND JobStatus='T' AND Job.JobId=File.JobId " + "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " + "AND Filename.Name='%s' LIMIT 20"; + +char *uar_sel_files = + "SELECT Path.Path,Filename.Name,FileIndex,JobId " + "FROM File,Filename,Path " + "WHERE File.JobId=%d AND Filename.FilenameId=File.FilenameId " + "AND Path.PathId=File.PathId"; + +char *uar_del_temp = "DROP TABLE temp"; +char *uar_del_temp1 = "DROP TABLE temp1"; + +char *uar_create_temp = + "CREATE TABLE temp (JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED," + "ClientId INTEGER UNSIGNED," + "Level CHAR," + "JobFiles INTEGER UNSIGNED," + "StartTime TEXT," + "VolumeName TEXT," + "StartFile INTEGER UNSIGNED," + "VolSessionId INTEGER UNSIGNED," + "VolSessionTime INTEGER UNSIGNED)"; + +char *uar_create_temp1 = + "CREATE TABLE temp1 (JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED," + "ClientId INTEGER UNSIGNED)"; + +char *uar_last_full = + "INSERT INTO temp1 SELECT Job.JobId,JobTdate,Job.ClientId " + "FROM Client,Job,JobMedia,Media WHERE Client.Name='%s' " + "AND Client.ClientId=Job.ClientId " + "AND Level='F' AND JobStatus='T' " + "AND JobMedia.JobId=Job.JobId " + "AND JobMedia.MediaId=Media.MediaId " + "ORDER BY Job.JobTDate DESC LIMIT 1"; + +char *uar_full = + "INSERT INTO temp SELECT Job.JobId,Job.JobTDate," + " Job.ClientId,Job.Level,Job.JobFiles," + " StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime " + "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId " + "AND Level='F' AND JobStatus='T' " + "AND JobMedia.JobId=Job.JobId " + "AND JobMedia.MediaId=Media.MediaId"; + +char *uar_inc = + "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId," + "Job.Level,Job.JobFiles,Job.StartTime,Media.VolumeName,JobMedia.StartFile," + "Job.VolSessionId,Job.VolSessionTime " + "FROM Job,JobMedia,Media " + "WHERE Job.JobTDate>%d AND Job.ClientId=%u " + "AND JobMedia.JobId=Job.JobId " + "AND JobMedia.MediaId=Media.MediaId " + "AND Job.Level='I' AND JobStatus='T' " + "GROUP BY Job.JobId"; + +char *uar_list_temp = + "SELECT JobId,Level,JobFiles,StartTime,VolumeName,StartFile," + "VolSessionId,VolSessionTime FROM temp"; + +char *uar_sel_jobid_temp = "SELECT JobId from temp"; -- 2.39.5