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