X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fsql_list.c;h=aa903a0ca0090621ff388a4569c4f97b90dba20e;hb=f5984fe3b5fe567e1609078ed5966f4d0428f0a7;hp=7a1d17731165e795cb36e579552d89442a570b82;hpb=7232c33915addb5a96a729e9dd1d09cb22127c60;p=bacula%2Fbacula diff --git a/bacula/src/cats/sql_list.c b/bacula/src/cats/sql_list.c index 7a1d177311..aa903a0ca0 100644 --- a/bacula/src/cats/sql_list.c +++ b/bacula/src/cats/sql_list.c @@ -1,40 +1,50 @@ /* - * Bacula Catalog Database List records interface routines - * - * Kern Sibbald, March 2000 - * - * Version $Id$ - */ + Bacula® - The Network Backup Solution -/* - Copyright (C) 2000-2005 Kern Sibbald + Copyright (C) 2000-2009 Free Software Foundation Europe e.V. - 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. + The main author of Bacula is Kern Sibbald, with contributions from + many others, a complete list can be found in the file AUTHORS. + This program is Free Software; you can redistribute it and/or + modify it under the terms of version two of the GNU General Public + License as published by the Free Software Foundation and included + in the file LICENSE. - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of + 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. + 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., 51 Franklin Street, Fifth Floor, Boston, MA + 02110-1301, USA. + Bacula® is a registered trademark of Kern Sibbald. + The licensor of Bacula is the Free Software Foundation Europe + (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich, + Switzerland, email:ftf@fsfeurope.org. +*/ +/* + * Bacula Catalog Database List records interface routines + * + * Kern Sibbald, March 2000 + * + * Version $Id: sql_list.c 8508 2009-03-07 20:59:46Z kerns $ */ + /* The following is necessary so that we do not include * the dummy external definition of DB. */ -#define __SQL_C /* indicate that this is sql.c */ +#define __SQL_C /* indicate that this is sql.c */ #include "bacula.h" #include "cats.h" -#if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL +extern int db_type; + +#if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI /* ----------------------------------------------------------------------- * @@ -43,22 +53,17 @@ * ----------------------------------------------------------------------- */ -/* Imported subroutines */ -extern void list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type); -extern int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd); - - /* * Submit general SQL query */ -int db_list_sql_query(JCR *jcr, B_DB *mdb, char *query, DB_LIST_HANDLER *sendit, - void *ctx, int verbose, e_list_type type) +int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit, + void *ctx, int verbose, e_list_type type) { db_lock(mdb); if (sql_query(mdb, query) != 0) { Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb)); if (verbose) { - sendit(ctx, mdb->errmsg); + sendit(ctx, mdb->errmsg); } db_unlock(mdb); return 0; @@ -75,18 +80,32 @@ int db_list_sql_query(JCR *jcr, B_DB *mdb, char *query, DB_LIST_HANDLER *sendit, } void -db_list_pool_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) +db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr, + DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) { db_lock(mdb); if (type == VERT_LIST) { - Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog," - "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes," - "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId," - "RecyclePoolId,LabelType " - " FROM Pool ORDER BY PoolId"); + if (pdbr->Name[0] != 0) { + Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog," + "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes," + "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId," + "RecyclePoolId,LabelType " + " FROM Pool WHERE Name='%s'", pdbr->Name); + } else { + Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog," + "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes," + "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId," + "RecyclePoolId,LabelType " + " FROM Pool ORDER BY PoolId"); + } } else { - Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat " - "FROM Pool ORDER BY PoolId"); + if (pdbr->Name[0] != 0) { + Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat " + "FROM Pool WHERE Name='%s'", pdbr->Name); + } else { + Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat " + "FROM Pool ORDER BY PoolId"); + } } if (!QUERY_DB(jcr, mdb, mdb->cmd)) { @@ -106,7 +125,7 @@ db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, db_lock(mdb); if (type == VERT_LIST) { Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention," - "FileRetention,JobRetention " + "JobRetention " "FROM Client ORDER BY ClientId"); } else { Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention " @@ -131,7 +150,7 @@ db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, */ void db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr, - DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) + DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) { char ed1[50]; db_lock(mdb); @@ -140,30 +159,34 @@ db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr, Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId," "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs," "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites," - "VolCapacityBytes,VolStatus,Recycle,VolRetention," + "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention," "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger," - "EndFile,EndBlock,VolParts,LabelType" + "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId," + "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, " + "Comment" " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName); } else { Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId," "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs," "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites," - "VolCapacityBytes,VolStatus,Recycle,VolRetention," + "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention," "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger," - "EndFile,EndBlock,VolParts,LabelType" - " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", - edit_int64(mdbr->PoolId, ed1)); + "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId," + "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, " + "Comment" + " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", + edit_int64(mdbr->PoolId, ed1)); } } else { if (mdbr->VolumeName[0] != 0) { - Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus," + Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled," "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten " "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName); } else { - Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus," + Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled," "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten " - "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", - edit_int64(mdbr->PoolId, ed1)); + "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", + edit_int64(mdbr->PoolId, ed1)); } } @@ -179,12 +202,12 @@ db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr, } void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId, - DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) + DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) { char ed1[50]; db_lock(mdb); if (type == VERT_LIST) { - if (JobId > 0) { /* do by JobId */ + if (JobId > 0) { /* do by JobId */ Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName," "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock," "JobMedia.EndBlock " @@ -198,7 +221,7 @@ void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId, } } else { - if (JobId > 0) { /* do by JobId */ + if (JobId > 0) { /* do by JobId */ Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex " "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId " "AND JobMedia.JobId=%s", edit_int64(JobId, ed1)); @@ -219,6 +242,79 @@ void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId, } +void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds, + DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) +{ + POOL_MEM str_limit(PM_MESSAGE); + POOL_MEM str_jobids(PM_MESSAGE); + + if (limit > 0) { + Mmsg(str_limit, " LIMIT %d", limit); + } + + if (JobIds && JobIds[0]) { + Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ", + JobIds, JobIds); + } + + db_lock(mdb); + Mmsg(mdb->cmd, + "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, " + "Job.JobId AS CopyJobId, Media.MediaType " + "FROM Job " + "JOIN JobMedia USING (JobId) " + "JOIN Media USING (MediaId) " + "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s", + (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str()); + + if (!QUERY_DB(jcr, mdb, mdb->cmd)) { + goto bail_out; + } + + if (mdb->result && sql_num_rows(mdb)) { + if (JobIds && JobIds[0]) { + sendit(ctx, _("These JobIds have copies as follows:\n")); + } else { + sendit(ctx, _("The catalog contains copies as follows:\n")); + } + + list_result(jcr, mdb, sendit, ctx, type); + } + + sql_free_result(mdb); + +bail_out: + db_unlock(mdb); +} + +void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId, + DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) +{ + char ed1[50]; + + if (JobId <= 0) { + return; + } + db_lock(mdb); + if (type == VERT_LIST) { + Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log " + "WHERE Log.JobId=%s", edit_int64(JobId, ed1)); + } else { + Mmsg(mdb->cmd, "SELECT LogText FROM Log " + "WHERE Log.JobId=%s", edit_int64(JobId, ed1)); + } + if (!QUERY_DB(jcr, mdb, mdb->cmd)) { + goto bail_out; + } + + list_result(jcr, mdb, sendit, ctx, type); + + sql_free_result(mdb); + +bail_out: + db_unlock(mdb); +} + /* * List Job record(s) that match JOB_DBR @@ -228,42 +324,58 @@ void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId, */ void db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, - void *ctx, e_list_type type) + void *ctx, e_list_type type) { char ed1[50]; + char limit[100]; db_lock(mdb); + if (jr->limit > 0) { + snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit); + } else { + limit[0] = 0; + } if (type == VERT_LIST) { if (jr->JobId == 0 && jr->Job[0] == 0) { - Mmsg(mdb->cmd, + Mmsg(mdb->cmd, "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level," - "Job.ClientId,Client.Name,JobStatus,SchedTime," - "StartTime,EndTime,JobTDate," + "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime," + "StartTime,EndTime,RealEndTime,JobTDate," "VolSessionId,VolSessionTime,JobFiles,JobErrors," - "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet " + "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId," + "Job.FileSetId,FileSet.FileSet " "FROM Job,Client,Pool,FileSet WHERE " "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId " - "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime"); - } else { /* single record */ - Mmsg(mdb->cmd, + "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit); + } else { /* single record */ + Mmsg(mdb->cmd, "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level," "Job.ClientId,Client.Name,JobStatus,SchedTime," - "StartTime,EndTime,JobTDate," + "StartTime,EndTime,RealEndTime,JobTDate," "VolSessionId,VolSessionTime,JobFiles,JobErrors," - "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet " + "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId," + "Job.FileSetId,FileSet.FileSet " "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND " "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId " - "AND FileSet.FileSetId=Job.FileSetId", - edit_int64(jr->JobId, ed1)); + "AND FileSet.FileSetId=Job.FileSetId", + edit_int64(jr->JobId, ed1)); } } else { - if (jr->JobId == 0 && jr->Job[0] == 0) { - Mmsg(mdb->cmd, + if (jr->Name[0] != 0) { + Mmsg(mdb->cmd, + "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus " + "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name); + } else if (jr->Job[0] != 0) { + Mmsg(mdb->cmd, + "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus " + "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job); + } else if (jr->JobId != 0) { + Mmsg(mdb->cmd, + "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus " + "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1)); + } else { /* all records */ + Mmsg(mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus " - "FROM Job ORDER BY StartTime"); - } else { /* single record */ - Mmsg(mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level," - "JobFiles,JobBytes,JobStatus FROM Job WHERE JobId=%s", - edit_int64(jr->JobId, ed1)); + "FROM Job ORDER BY StartTime,JobId ASC%s", limit); } } if (!QUERY_DB(jcr, mdb, mdb->cmd)) { @@ -300,7 +412,7 @@ db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, vo /* Do Grand Total */ Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) " -"AS Files,sum(JobBytes) As Bytes FROM Job"); + "AS Files,sum(JobBytes) As Bytes FROM Job"); if (!QUERY_DB(jcr, mdb, mdb->cmd)) { db_unlock(mdb); @@ -313,25 +425,78 @@ db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, vo db_unlock(mdb); } -/* - * Stupid MySQL is NON-STANDARD ! - */ -#ifdef HAVE_MYSQL -#define FN "CONCAT(Path.Path,Filename.Name)" -#else -#define FN "Path.Path||Filename.Name" -#endif - void db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx) { char ed1[50]; db_lock(mdb); - Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File," -"Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId " -"AND Path.PathId=File.PathId", - edit_int64(jobid, ed1)); + /* + * Stupid MySQL is NON-STANDARD ! + */ + if (db_type == SQL_TYPE_MYSQL) { + Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename " + "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s " + "UNION ALL " + "SELECT PathId, FilenameId " + "FROM BaseFiles JOIN File " + "ON (BaseFiles.FileId = File.FileId) " + "WHERE BaseFiles.JobId = %s" + ") AS F, Filename,Path " + "WHERE Filename.FilenameId=F.FilenameId " + "AND Path.PathId=F.PathId", + edit_int64(jobid, ed1), ed1); + } else { + Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename " + "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s " + "UNION ALL " + "SELECT PathId, FilenameId " + "FROM BaseFiles JOIN File " + "ON (BaseFiles.FileId = File.FileId) " + "WHERE BaseFiles.JobId = %s" + ") AS F, Filename,Path " + "WHERE Filename.FilenameId=F.FilenameId " + "AND Path.PathId=F.PathId", + edit_int64(jobid, ed1), ed1); + } + + if (!QUERY_DB(jcr, mdb, mdb->cmd)) { + db_unlock(mdb); + return; + } + + list_result(jcr, mdb, sendit, ctx, HORZ_LIST); + + sql_free_result(mdb); + db_unlock(mdb); +} + +void +db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx) +{ + char ed1[50]; + db_lock(mdb); + + /* + * Stupid MySQL is NON-STANDARD ! + */ + if (db_type == SQL_TYPE_MYSQL) { + Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename " + "FROM BaseFiles, File, Filename, Path " + "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId " + "AND BaseFiles.FileId = File.FileId " + "AND Filename.FilenameId=File.FilenameId " + "AND Path.PathId=File.PathId", + edit_int64(jobid, ed1)); + } else { + Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename " + "FROM BaseFiles, File, Filename, Path " + "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId " + "AND BaseFiles.FileId = File.FileId " + "AND Filename.FilenameId=File.FilenameId " + "AND Path.PathId=File.PathId", + edit_int64(jobid, ed1)); + } if (!QUERY_DB(jcr, mdb, mdb->cmd)) { db_unlock(mdb); @@ -345,4 +510,4 @@ db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendi } -#endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/ +#endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES */