On Friday 2015-07-31 15:13:50 Josh Fisher wrote:
> On 7/31/2015 10:46 AM, Josip Deanovic wrote:
> > On Friday 2015-07-31 10:31:01 Bill Arlofski wrote:
> >> On 07/30/2015 07:01 PM, Josip Deanovic wrote:
> >>> I have played with the  "SelectionType = PoolUncopiedJobs" option
> >>> some
> >>> time ago and have found it problematic because the first time you
> >>> use
> >>> it it will copy every job existing in the database which is not yet
> >>> copied.
> >> 
> >> Hi Josip,
> >> 
> >> The way it should work per the documentation is that only un-copied
> >> jobs in the specified _pool_ will be copied, not all un-copied jobs
> >> in the catalog _database_.
> >> 
> >> Perhaps when you tried that option you made the same mistake as I did
> >> when copy jobs were first introduced: On my first attempts, I
> >> mistakingly tried to use that directive on a pool which I had been
> >> using for years. Of course, there were many jobs in the pool, and of
> >> course the first attempts to use the simple "PoolUncopiedJobs"
> >> selection triggered thousands of copy jobs - much to my chagrin. :)
> > 
> > Hi,
> > 
> > Sorry, I wasn't careful enough when I chose to say: "every job
> > existing
> > in the database".
> > 
> > What I wanted to say is that first time you are using it on a pool it
> > will copy every job related to that pool, existing in the database
> > which is not yet copied.
> > 
> > I wanted to match and copy only the latest full with its differential
> > and incremental jobs and the only way to do it was through the use of
> > SQL query.
> 
> Could you post your SQL?

Here you are:

Selection Type = SQLQuery
Selection Pattern = "SELECT DISTINCT JobId,StartTime,Type,Level,Name,PriorJobId 
FROM Job WHERE Name = 'jobname' AND JobBytes > 0 AND ((Level='F' AND JobStatus 
IN ('T', 'W') AND StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 
'jobname' AND Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC 
LIMIT 1)) OR (Level='D' AND JobStatus IN ('T', 'W') AND StartTime >= (SELECT 
DISTINCT StartTime FROM Job WHERE Name = 'jobname' AND Level='F' AND JobStatus 
IN ('T', 'W') ORDER BY StartTime DESC LIMIT 1)) OR (Level='I' AND JobStatus IN 
('T', 'W') AND StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 
'jobname' AND Level='D' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC 
LIMIT 1) OR StartTime >= (SELECT DISTINCT StartTime FROM Job WHERE Name = 
'jobname' AND Level='F' AND JobStatus IN ('T', 'W') ORDER BY StartTime DESC 
LIMIT 1))) AND NOT JobId = ANY (select PriorJobId FROM Job WHERE Name = 
'jobname' AND PriorJobId <> 0) AND Type<>'C' ORDER BY JobId;"


I am using it for more than a year and it selects only the latest full
job along with its differential and all the incremental jobs that
haven't been copied yet.
If someone finds a way to improve it, it would be nice to hear about it.

The string "jobname" should be replaced with the actual job name. The
string appears six times in the query.

I recommend tests directly in the database before using the query in
production.
I am using it with MySQL database.

-- 
Josip Deanovic

------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users