[Bacula-users] Re: [Bacula] Some SQL problems

[Kern S. <ke...@si...>]

Hello Jari,

I'm copying this to the list because I think some
of the other users might want to know how to write
their own SQL or to sort the output. I hope you don't
mind. :-)

All the "list" commands are hard coded SQL, which was
easy to program in the beginning, but is not very
flexible and adaptable for users. So I have stopped
developing the list commands, and instead am trying
to concentrate on "query" commands. These commands
are read from the query.sql file that you define
in your Director's conf file.  

The nice thing about query.sql commands is that you
can change them on the fly with any ASCII editor.
Then you can immediately re-run the command and
see the results.

I encourage you to make your own set of query commands,
adapted to your own needs with any kinds of sorting
you would like.  I also recommend that when you do so,
you put then in a different file (e.g. my_query.sql)
so that your file will not be overwritten by the next
Bacula upgrade.

The SQL for the list files is the following:

SELECT Path.Path,Filename.Name FROM File,
 Filename,Path WHERE File.JobId=%u 
 AND Filename.FilenameId=File.FilenameId 
 AND Path.PathId=File.PathId
where the %u is replaced by the JobId you specified.

If you wanted to create your own "query" command, you
could add the following:

#
: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;

To the end of the query.sql file, and then by
playing with the command, you can get it to
order anyway you want. For example, if you
made it:

#
: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;

It would probably do what you want.  You could
even try using a concatenation operator (different
in MySQL and SQLite) to print the path and filename
together rather than in two fields.  Unfortunately,
MySQL's concatenation is not standard SQL :-(

One more example (for MySQL ONLY):
#
:List Files for a selected JobId:
*Enter JobId:
SELECT CONCAT(Path.Path,Filename.Name) AS Fname FROM File,
 Filename,Path WHERE File.JobId=%1 
 AND Filename.FilenameId=File.FilenameId 
 AND Path.PathId=File.PathId ORDER BY Fname;

Best regards,

Kern

PS: I'm sorry about your XP backup problems. I'm
    pretty sure that if you do not have Cygwin loaded
    on your XP, that the problem is not related to
    Bacula.  If you do, you probably have a cygwin dll
    or mount point conflict.

PPS: I'd recommend taking great care when modifying the
     database directly. It could cause problems with Bacula.

PPPS: I see that I have not documented the format of the query.sql
     file, so here it is in a nut shell.  
     - A # in column 1 is a comment
     - Blank lines are ignored
     - The first line that starts with a : defines the prompt
       string that will appear when you enter query.
     - Any line that starts with a * is a query prompt, and
       the first prompt (line with a * ) will be issued when a
       %1 is found in the SQL command, and the %1 will be replaced
       with what you type in.  You can have up to 9 such lines
     - Any following lines will form a Query command and each line
       will be concatenated to the preceding line until either a
       blank line is found, or a semicolon is found.  That command
       with the %1 - %9 substitutions will then be feed to the SQL
       query engine and the results will be printed.

I'll add this to the doc.

Best regards,

Kern
       


On Tue, 2002-11-26 at 13:07, Jari Fredriksson wrote:
> Hi;)
> 
> Well, not problems, but still..
> 
> SQL requires always ORDER BY in the queries, if the result is wanted in
> some preferred order. The result may seem to come on some good order, but
> it is really random by definition.
> 
> I have currently very random results from "list files" and such calls..
> Because I have manually manipulated the data in the MySQL database.
> (Deleted some records, which are then reused by later backups..)
> 
> It would be nice to get this minor thing fixed in some version..
> Meanwhile, I'll learn more about bacula by trying to fix it by myself;)
> (Just made one query change, but it didn't help.. must have been a wrong
> one for "list files"... hehe).
> 
> I love this backup software! Yes!
> 
> Regards, Jarif
> 
> --
> 
> Btw, I'm the one having strange reboots while backing up my WinXP box.
> Still having that problem, but to my current understanding it's not caused
> directly by bacula.. Some strange problem in the XP instead, reboots
> itself every now and then, when doing lots of network traffic..)
> 
> Rgds, Same
> 
-- 
Kern Sibbald <ke...@si...>