On Mon, Jan 16, 2006 at 10:27:17AM +0100, Arno Lehmann wrote:
> Ok, here is what I added to my query.sql file. Note that it works with
> MySQL only - the only PostgreSQL installation I have available uses file
> based backups, so there never was much need to make these queries
> portable. And doing this by simply writing portable SQL is,
> unfortunately, beyond my SQL capabilities :-)
These queries are more portable than you're giving yourself credit
for. I ran all of these queries against a Postgres database and they
work as expected except for #17, which gives the notice and error
message shown below. Even in this case, the fix is simple, albeit
non-obvious: just change "FROM Media,Storage" to "FROM Storage,Media",
and all is well.
> # 17: List which media bacula thinks is in changer:
> SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
> AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
> FROM Media,Storage
> LEFT JOIN Pool ON Media.PoolId=Pool.PoolId
> WHERE (Slot > 0) AND (InChanger=1)
> AND Media.StorageId=Storage.StorageId
> ORDER BY MediaType ASC, Slot ASC;
NOTICE: adding missing FROM-clause entry for table "media"
ERROR: JOIN/ON clause refers to "media", which is not part of JOIN
bacula=> SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024)
bacula-> AS GB,Storage.Name
bacula-> AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
bacula-> FROM Storage,Media
bacula-> LEFT JOIN Pool ON Media.PoolId=Pool.PoolId
bacula-> WHERE (Slot > 0) AND (InChanger=1)
bacula-> AND Media.StorageId=Storage.StorageId
bacula-> ORDER BY MediaType ASC, Slot ASC;
mediaid | volumename | gb | storage | slot | pool | mediatype |
volstatus
---------+------------+-----+---------+------+--------------+-----------+-----------
9 | A00001L3 | 755 | Tape | 1 | LTO3-backups | LTO-3 | Full
... snip ...
58 | A00050L3 | 0 | tape | 50 | LTO3-backups | LTO-3 | Append
(49 rows)
Thanks for the contribution Arno. I can see these queries coming in
handy.
-- John Kodis.
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
