I've seen more than a few users asking how to access information such
as file size and other things stored in the File.lstat field.  I
thought it would be nice to get at this directly via an SQL query
rather than through external scripts that have been posted.

Below is a quick hack I came up with.  I did this because I wanted to
see what files were taking up the most space for my daily incrementals
that I have to transfer off site over a relatively slow connection.  I
hope someone else finds it useful.

Added to the query.sql file:

# 21
:List the largest 20 files for a JobId:
*Enter JobId:
SELECT (SELECT st_size FROM decode_lstat(File.lstat)) AS Size,
 Path.Name, FileName.Name
 FROM File,FileName,Path
 WHERE File.JobId = '%1'
 AND Filename.FileNameId = File.FileNameid
 AND File.PathId = Path.PathId
 ORDER by Size DESC
 LIMIT 20;


Enter the following with the psql command on your Bacula database.
You will probably also have to run createlang plpgsql or use the
CREATE LANGUAGE statement as the PostgreSQL superuser:


-- Decode the File.lstat field and return it as a row result.
-- John H. Pierce <john AT killTERM DOT com>
-- NO WARRANTY IS EXPRESSED OR IMPLIED. Free for use for any reason.
CREATE FUNCTION bacula_base64(field text) RETURNS INTEGER AS $$
       DECLARE
               i INTEGER DEFAULT 1;
               res INTEGER DEFAULT 0;
               base64 TEXT DEFAULT
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
       BEGIN
               WHILE i <= char_length(field) LOOP
                       res := res << 6;
                       res := res + position(substring(field from i
for 1) in base64) - 1;
                       i := i + 1;
               END LOOP;
               RETURN res;
       END
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- This table never contains any data, it's just a definition for the
-- row we'll return.
CREATE TABLE lstat (
       st_dev INTEGER,
       st_ino INTEGER,
       st_mode INTEGER,
       st_nlink INTEGER,
       st_uid INTEGER,
       st_gid INTEGER,
       st_rdev INTEGER,
       st_size INTEGER,
       st_blksize INTEGER,
       st_blocks INTEGER,
       st_atime INTEGER,
       st_mtime INTEGER,
       st_ctime INTEGER,
       LinkFI  INTEGER,
       st_flags INTEGER,
       data INTEGER
);

CREATE FUNCTION decode_lstat(lst text) RETURNS lstat AS $$
       DECLARE
               st lstat%ROWTYPE;
               ret record;
               fields text[];
       BEGIN
               fields = regexp_split_to_array(lst, ' ');
               st.st_dev := bacula_base64(fields[1]);
               st.st_ino := bacula_base64(fields[2]);
               st.st_mode := bacula_base64(fields[3]);
               st.st_nlink := bacula_base64(fields[4]);
               st.st_uid := bacula_base64(fields[5]);
               st.st_gid := bacula_base64(fields[6]);
               st.st_rdev := bacula_base64(fields[7]);
               st.st_size := bacula_base64(fields[8]);
               st.st_blksize := bacula_base64(fields[9]);
               st.st_blocks := bacula_base64(fields[10]);
               st.st_atime := bacula_base64(fields[11]);
               st.st_mtime := bacula_base64(fields[12]);
               st.st_ctime := bacula_base64(fields[13]);
               st.LinkFI := bacula_base64(fields[14]);
               st.st_flags := bacula_base64(fields[15]);
               st.data := bacula_base64(fields[16]);
               RETURN st;
       END
$$ LANGUAGE 'plpgsql' IMMUTABLE;


--
John H. Pierce <john AT killTERM DOT com>

------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users