I've seen lots of posts regarding the decoding of Lstat data. I'm currently
using a few functions to decoded the lstat data and make it human readable.
These functions have been tested and are known to work with mysql 5.0.70
delimiter |
DROP FUNCTION IF EXISTS MYBASE64_DECODE |
CREATE FUNCTION MYBASE64_DECODE (input BLOB)
RETURNS BIGINT
CONTAINS SQL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
IF input IS NULL THEN
RETURN NULL;
END IF;
BEGIN
DECLARE base10 BIGINT DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE length BIGINT DEFAULT 0;
DECLARE tempval BIGINT UNSIGNED;
DECLARE b64 VARCHAR(65) DEFAULT
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvfwxyz0123456789+/';
SET length = CHAR_LENGTH(input);
WHILE i < length + 1 DO
SET base10 = base10 + ((INSTR(b64, substring(input, i, 1)) -
1) * POWER(64, (length - i)));
SET i = i + 1;
END WHILE;
RETURN base10;
END;
END |
-- in the query pass it in the substring of the blob that you want
delimiter |
DROP FUNCTION IF EXISTS HUMAN_READABLE |
CREATE FUNCTION HUMAN_READABLE (bytes FLOAT(20, 2))
RETURNS VARCHAR(50)
CONTAINS SQL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE frmtd VARCHAR(50);
DECLARE tmpfloat FLOAT(20, 2);
IF bytes < 1024 THEN
SET tmpfloat = bytes;
SET frmtd = CONCAT(tmpfloat, ' B');
ELSEIF bytes < 1048576 THEN
SET tmpfloat = bytes / 1024;
SET frmtd = CONCAT(tmpfloat, ' KB');
ELSEIF bytes < 1073741824 THEN
SET tmpfloat = bytes / 1048576;
SET frmtd = CONCAT(tmpfloat, ' MB');
ELSE
SET tmpfloat = bytes / 1073741824;
SET frmtd = CONCAT(tmpfloat, ' GB');
END IF;
RETURN frmtd;
END ;
Here is an example. I have many Windows clients and I have a need to know
what file types a particular users Full backup consists. In this particular
situation it's too bad the MIME type for each file isn't stored in the
catalog.
# 36
:Full backup counts and sizes per Extension Type
*Enter Client Name:
select if (char_length(substring_index(Filename.Name, '.', -1)) in (3,4),
substring_index(Filename.Name, '.', -1), 'Invalid Extension') as Extension,
count(*) as Count,
human_readable(sum(mybase64_decode(substring_index(substring_index(File.LSta
t, ' ', 8), ' ', -1)))) as Size
from (((select Job.JobId
from Job join Client on Job.ClientId = Client.ClientId
where Job.EndTime > (select Job.StartTime
from Job join Client on Job.ClientId =
Client.ClientId
where Client.Name = '%1'
and Job.Level = 'F'
and Job.JobStatus = 'T'
order by Job.StartTime desc
limit 1)
and Client.Name = '%1') f1 join
File on f1.JobId = File.JobId) join
Filename on File.FilenameId = Filename.FilenameId)
join
Path on File.PathId = Path.PathId
group by Extension order by
sum(mybase64_decode(substring_index(substring_index(File.LStat, ' ', 8), '
', -1))) desc;
Brian Kelly
smime.p7s
Description: S/MIME cryptographic signature
------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com _______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|