[prev in list] [next in list] [prev in thread] [next in thread]
List: bacula-commits
Subject: [Bacula-commits] git: Bacula branch, master,
From: "Kern Sibbald" <kerns () users ! sourceforge ! net>
Date: 2009-11-14 21:40:08
Message-ID: E1N9QLw-00072r-Ad () cj8yhf1 ! ch3 ! sourceforge ! com
[Download RAW message or body]
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "Bacula".
The branch, master has been updated
via 7cd66d2541ca69efe7f32e021fe93623f96a59a5 (commit)
via aa88bb65780c389f9a624b16afabad0d6301726a (commit)
from c1d6eb6f82f6897705f423aaccc4a498b8e3eeb9 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 7cd66d2541ca69efe7f32e021fe93623f96a59a5
Author: Kern Sibbald <kern@sibbald.com>
Date: Sat Nov 14 22:41:01 2009 +0100
Update technotes
commit aa88bb65780c389f9a624b16afabad0d6301726a
Author: Kern Sibbald <kern@sibbald.com>
Date: Sat Nov 14 22:39:29 2009 +0100
Fix bug #1367 by creating an empty query.sql file
-----------------------------------------------------------------------
Summary of changes:
diff --git a/bacula/examples/sample-query.sql b/bacula/examples/sample-query.sql
new file mode 100644
index 0000000..69ab59d
--- /dev/null
+++ b/bacula/examples/sample-query.sql
@@ -0,0 +1,195 @@
+#
+# This file contains sample queries that you can possibly use in
+# your query.sql file. However, please note that these examples
+# are not supported. They may or may not work -- in fact, they may
+# work with one SQL engine and not another.
+#
+
+# 1
+:List up to 20 places where a File is saved regardless of the directory
+*Enter Filename (no path):
+SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
+ Path.Path,Filename.Name,StartTime,Level,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='%1'
+ ORDER BY Job.StartTime LIMIT 20;
+# 2
+:List where the most recent copies of a file are saved
+*Enter path with trailing slash:
+*Enter filename:
+*Enter Client name:
+SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
+ FROM Job,File,Path,Filename,Media,JobMedia,Client
+ WHERE File.JobId=Job.JobId
+ AND Path.Path='%1'
+ AND Filename.Name='%2'
+ AND Client.Name='%3'
+ AND Path.PathId=File.PathId
+ AND Filename.FilenameId=File.FilenameId
+ AND JobMedia.JobId=Job.JobId
+ AND JobMedia.MediaId=Media.MediaId
+ AND Client.ClientId=Job.ClientId
+ ORDER BY Job.StartTime DESC LIMIT 5;
+# 3
+:List last 20 Full Backups for a Client
+*Enter Client name:
+SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
+ JobMedia.StartFile as VolFile,VolumeName
+ FROM Client,Job,JobMedia,Media
+ WHERE Client.Name='%1'
+ AND Client.ClientId=Job.ClientId
+ AND Level='F' AND JobStatus='T'
+ AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
+ ORDER BY Job.StartTime DESC LIMIT 20;
+# 4
+:List all backups for a Client after a specified time
+*Enter Client Name:
+*Enter time in YYYY-MM-DD HH:MM:SS format:
+SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
+ FROM Client,Job,JobMedia,Media
+ WHERE Client.Name='%1'
+ AND Client.ClientId=Job.ClientId
+ AND JobStatus='T'
+ AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
+ AND Job.StartTime >= '%2'
+ ORDER BY Job.StartTime;
+# 5
+:List all backups for a Client
+*Enter Client Name:
+SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
+ FileSet.FileSet AS FileSet,Level,StartTime,
+ JobFiles,JobBytes,VolumeName
+ FROM Client,Job,JobMedia,Media,FileSet
+ WHERE Client.Name='%1'
+ AND Client.ClientId=Job.ClientId AND Job.Type='B'
+ AND Job.JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
+ AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
+ ORDER BY Job.StartTime;
+# 6
+:List Volume Attributes for a selected Volume
+*Enter Volume name:
+SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
+ VolUseDuration,MaxVolJobs,MaxVolFiles
+ FROM Media
+ WHERE VolumeName='%1';
+# 7
+:List Volumes used by selected JobId
+*Enter JobId:
+SELECT DISTINCT Job.JobId,VolumeName
+ FROM Job,JobMedia,Media
+ WHERE Job.JobId=%1
+ AND Job.JobId=JobMedia.JobId
+ AND JobMedia.MediaId=Media.MediaId;
+# 8
+:List Volumes to Restore All Files
+*Enter Client Name:
+!DROP TABLE temp;
+!DROP TABLE temp2;
+CREATE TABLE temp (JobId BIGINT NOT NULL,
+ JobTDate BIGINT,
+ ClientId BIGINT,
+ Level CHAR,
+ StartTime TEXT,
+ VolumeName TEXT,
+ StartFile BIGINT,
+ VolSessionId BIGINT,
+ VolSessionTime BIGINT );
+CREATE TABLE temp2 (JobId BIGINT NOT NULL,
+ StartTime TEXT,
+ VolumeName TEXT,
+ Level CHAR,
+ StartFile BIGINT,
+ VolSessionId BIGINT,
+ VolSessionTime BIGINT);
+# Select last Full save
+INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
+ StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
+ FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
+ 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;
+# Copy into temp 2 getting all volumes of Full save
+INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
+ JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
+ FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
+ AND Job.Level='F' AND Job.JobStatus='T'
+ AND JobMedia.JobId=Job.JobId
+ AND JobMedia.MediaId=Media.MediaId;
+# Now add subsequent incrementals
+INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
+ Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
+ FROM Job,temp,JobMedia,Media
+ WHERE Job.JobTDate>temp.JobTDate
+ AND Job.ClientId=temp.ClientId
+ AND Job.Level IN ('I','D') AND JobStatus='T'
+ AND JobMedia.JobId=Job.JobId
+ AND JobMedia.MediaId=Media.MediaId;
+# list results
+SELECT DISTINCT VolumeName from temp2;
+!DROP TABLE temp;
+!DROP TABLE temp2;
+# 9
+:List Pool Attributes for a selected Pool
+*Enter Pool name:
+SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
+ FROM Pool
+ WHERE Name='%1';
+# 10
+:List total files/bytes by Job
+SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
+ FROM Job GROUP by Name;
+# 11
+:List total files/bytes by Volume
+SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName
+ FROM Job,JobMedia,Media
+ WHERE JobMedia.JobId=Job.JobId
+ AND JobMedia.MediaId=Media.MediaId
+ GROUP by VolumeName;
+# 12
+:List Files for a selected JobId
+*Enter JobId:
+SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
+ AND Filename.FilenameId=File.FilenameId
+ AND Path.PathId=File.PathId ORDER BY
+ Path.Path,Filename.Name;
+# 13
+:List Jobs stored on a selected MediaId
+*Enter MediaId:
+SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
+ Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
+ FROM JobMedia,Job
+ WHERE JobMedia.JobId=Job.JobId
+ AND JobMedia.MediaId=%1
+ ORDER by Job.StartTime;
+# 14
+:List Jobs stored for a given Volume name
+*Enter Volume name:
+SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
+ Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
+ Job.JobBytes as Bytes,Job.JobStatus as Status
+ FROM Media,JobMedia,Job
+ WHERE Media.VolumeName='%1'
+ AND Media.MediaId=JobMedia.MediaId
+ AND JobMedia.JobId=Job.JobId
+ ORDER by Job.StartTime;
+# 15
+:List Volumes Bacula thinks are in changer
+SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
+ AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
+ FROM Media,Pool,Storage
+ WHERE Media.PoolId=Pool.PoolId
+ AND Slot>0 AND InChanger=1
+ AND Media.StorageId=Storage.StorageId
+ ORDER BY MediaType ASC, Slot ASC;
+# 16
+:List Volumes likely to need replacement from age or errors
+SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
+ VolWrites AS Writes,VolStatus AS Status
+ FROM Media
+ WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
+ (VolStatus='Disabled') OR (VolWrites>3999999)
+ ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;
diff --git a/bacula/src/dird/query.sql b/bacula/src/dird/query.sql
index b18f10f..aa0600e 100644
--- a/bacula/src/dird/query.sql
+++ b/bacula/src/dird/query.sql
@@ -1,188 +1,4 @@
-# 1
-:List up to 20 places where a File is saved regardless of the directory
-*Enter Filename (no path):
-SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
- Path.Path,Filename.Name,StartTime,Level,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='%1'
- ORDER BY Job.StartTime LIMIT 20;
-# 2
-:List where the most recent copies of a file are saved
-*Enter path with trailing slash:
-*Enter filename:
-*Enter Client name:
-SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
- FROM Job,File,Path,Filename,Media,JobMedia,Client
- WHERE File.JobId=Job.JobId
- AND Path.Path='%1'
- AND Filename.Name='%2'
- AND Client.Name='%3'
- AND Path.PathId=File.PathId
- AND Filename.FilenameId=File.FilenameId
- AND JobMedia.JobId=Job.JobId
- AND JobMedia.MediaId=Media.MediaId
- AND Client.ClientId=Job.ClientId
- ORDER BY Job.StartTime DESC LIMIT 5;
-# 3
-:List last 20 Full Backups for a Client
-*Enter Client name:
-SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
- JobMedia.StartFile as VolFile,VolumeName
- FROM Client,Job,JobMedia,Media
- WHERE Client.Name='%1'
- AND Client.ClientId=Job.ClientId
- AND Level='F' AND JobStatus='T'
- AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
- ORDER BY Job.StartTime DESC LIMIT 20;
-# 4
-:List all backups for a Client after a specified time
-*Enter Client Name:
-*Enter time in YYYY-MM-DD HH:MM:SS format:
-SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
- FROM Client,Job,JobMedia,Media
- WHERE Client.Name='%1'
- AND Client.ClientId=Job.ClientId
- AND JobStatus='T'
- AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
- AND Job.StartTime >= '%2'
- ORDER BY Job.StartTime;
-# 5
-:List all backups for a Client
-*Enter Client Name:
-SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
- FileSet.FileSet AS FileSet,Level,StartTime,
- JobFiles,JobBytes,VolumeName
- FROM Client,Job,JobMedia,Media,FileSet
- WHERE Client.Name='%1'
- AND Client.ClientId=Job.ClientId AND Job.Type='B'
- AND Job.JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
- AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
- ORDER BY Job.StartTime;
-# 6
-:List Volume Attributes for a selected Volume
-*Enter Volume name:
-SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
- VolUseDuration,MaxVolJobs,MaxVolFiles
- FROM Media
- WHERE VolumeName='%1';
-# 7
-:List Volumes used by selected JobId
-*Enter JobId:
-SELECT DISTINCT Job.JobId,VolumeName
- FROM Job,JobMedia,Media
- WHERE Job.JobId=%1
- AND Job.JobId=JobMedia.JobId
- AND JobMedia.MediaId=Media.MediaId;
-# 8
-:List Volumes to Restore All Files
-*Enter Client Name:
-!DROP TABLE temp;
-!DROP TABLE temp2;
-CREATE TABLE temp (JobId BIGINT NOT NULL,
- JobTDate BIGINT,
- ClientId BIGINT,
- Level CHAR,
- StartTime TEXT,
- VolumeName TEXT,
- StartFile BIGINT,
- VolSessionId BIGINT,
- VolSessionTime BIGINT );
-CREATE TABLE temp2 (JobId BIGINT NOT NULL,
- StartTime TEXT,
- VolumeName TEXT,
- Level CHAR,
- StartFile BIGINT,
- VolSessionId BIGINT,
- VolSessionTime BIGINT);
-# Select last Full save
-INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
- StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
- FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
- 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;
-# Copy into temp 2 getting all volumes of Full save
-INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
- JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
- FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
- AND Job.Level='F' AND Job.JobStatus='T'
- AND JobMedia.JobId=Job.JobId
- AND JobMedia.MediaId=Media.MediaId;
-# Now add subsequent incrementals
-INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
- Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
- FROM Job,temp,JobMedia,Media
- WHERE Job.JobTDate>temp.JobTDate
- AND Job.ClientId=temp.ClientId
- AND Job.Level IN ('I','D') AND JobStatus='T'
- AND JobMedia.JobId=Job.JobId
- AND JobMedia.MediaId=Media.MediaId;
-# list results
-SELECT DISTINCT VolumeName from temp2;
-!DROP TABLE temp;
-!DROP TABLE temp2;
-# 9
-:List Pool Attributes for a selected Pool
-*Enter Pool name:
-SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
- FROM Pool
- WHERE Name='%1';
-# 10
-:List total files/bytes by Job
-SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
- FROM Job GROUP by Name;
-# 11
-:List total files/bytes by Volume
-SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName
- FROM Job,JobMedia,Media
- WHERE JobMedia.JobId=Job.JobId
- AND JobMedia.MediaId=Media.MediaId
- GROUP by VolumeName;
-# 12
-:List Files for a selected JobId
-*Enter JobId:
-SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
- AND Filename.FilenameId=File.FilenameId
- AND Path.PathId=File.PathId ORDER BY
- Path.Path,Filename.Name;
-# 13
-:List Jobs stored on a selected MediaId
-*Enter MediaId:
-SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
- Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
- FROM JobMedia,Job
- WHERE JobMedia.JobId=Job.JobId
- AND JobMedia.MediaId=%1
- ORDER by Job.StartTime;
-# 14
-:List Jobs stored for a given Volume name
-*Enter Volume name:
-SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
- Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
- Job.JobBytes as Bytes,Job.JobStatus as Status
- FROM Media,JobMedia,Job
- WHERE Media.VolumeName='%1'
- AND Media.MediaId=JobMedia.MediaId
- AND JobMedia.JobId=Job.JobId
- ORDER by Job.StartTime;
-# 15
-:List Volumes Bacula thinks are in changer
-SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
- AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
- FROM Media,Pool,Storage
- WHERE Media.PoolId=Pool.PoolId
- AND Slot>0 AND InChanger=1
- AND Media.StorageId=Storage.StorageId
- ORDER BY MediaType ASC, Slot ASC;
-# 16
-:List Volumes likely to need replacement from age or errors
-SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
- VolWrites AS Writes,VolStatus AS Status
- FROM Media
- WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
- (VolStatus='Disabled') OR (VolWrites>3999999)
- ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;
+#
+# See the file <bacula-source>/examples/sample-query.sql
+# for some sample queries.
+#
diff --git a/bacula/technotes b/bacula/technotes
index 94dd239..85814a0 100644
--- a/bacula/technotes
+++ b/bacula/technotes
@@ -2,6 +2,9 @@
General:
+14Nov09
+kes Fix bug #1367 buy creating an empty query.sql file. The old query.sql
+ file is now in exmaples/sample-query.sql, but is unsupported.
11Nov09
ebl Fix basejob code for Mysql
ebl Fix segfault in basejob code
hooks/post-receive
--
Bacula
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Bacula-commits mailing list
Bacula-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-commits
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic