I just threw together some MS SQL (because that's what I've got in front of me) code that sort of does what you want, but not in a way that could be used in a query (you'd have to put it in a user defined function, which is easy enough to do), and being MSSQL it won't work as given under any of the databases bacula uses. You can probably do the same thing in Postgres, but I'm not at all sure it can be done in the others...
Note that the COLLATE thing is only there so that it knows that a 'W' and a 'w' are different. Shouldn't be an issue on non-MS databases. Hope it helps in some way (beware of line wrapping :) James DECLARE @b64 char(64) DECLARE @string varchar(6) DECLARE @val decimal(38,0) DECLARE @pos int SET @b64 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/' SET @string = 'BEU0tK' SET @val = 0 SET @pos = 1 WHILE @pos <= LEN(@string) BEGIN SET @val = @val * 64 + CHARINDEX(SUBSTRING(@string, @pos, 1), @b64 COLLATE Latin1_General_CS_AS) - 1 SET @pos = @pos + 1 END PRINT @val > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:bacula-users- > [EMAIL PROTECTED] On Behalf Of Mark Nienberg > Sent: Thursday, 11 May 2006 15:12 > To: bacula-users@lists.sourceforge.net > Subject: [Bacula-users] Re: File attributes > > Cedric Tefft wrote: > > Mark Nienberg wrote: > > > >>> I'd like to write an SQL query to extract some information from the > >>> bacula catalog including file dates. I see from the bacula > >>> developers guide that the "File" table has a field named "LStat" that > >>> is defined as "File attributes in base64 encoding". I assume my file > >>> dates are in there, but how do I deal with the base64 bit? > >>> > >>> A typical entry looks like this: > >>> P0A HL4KO IHk B A A A BdD BAA Y BEU0tK BEHwQV BEHwQV A A C > > > I've got about half an answer for 'ya. > > > > The database entry appears to be a series of base-64 encoded values > > corresponding to the fields of a UNIX stat structure (plus a couple > > extra bits), which 'man lstat' says is: > > > > struct stat { > > dev_t st_dev; /* ID of device containing file */ > > ino_t st_ino; /* inode number */ > > mode_t st_mode; /* protection */ > > nlink_t st_nlink; /* number of hard links */ > > uid_t st_uid; /* user ID of owner */ > > gid_t st_gid; /* group ID of owner */ > > dev_t st_rdev; /* device ID (if special file) */ > > off_t st_size; /* total size, in bytes */ > > blksize_t st_blksize; /* blocksize for filesystem I/O */ > > blkcnt_t st_blocks; /* number of blocks allocated */ > > time_t st_atime; /* time of last access */ > > time_t st_mtime; /* time of last modification */ > > time_t st_ctime; /* time of last status change */ > > }; > > > > Therefore, from your example, "P0A" is the base64-encoded value of > > st_dev, "HL4KO" is the base64-encoded value of st_ino, etc. > > > > So, if you're after the modification time (st_mtime) of your file, you > > should theoretically just have to base64 decode the string "BEHwQV" to > > get back a standard UNIX timestamp value (seconds since Jan 1, 1970). > > Exactly how you go about doing a base64 decode is, of course, the other > > half of the answer which I'm far too lazy to go into here, but Google > > should be able to take you the rest of the way, and if you're a C > > programmer, the bacula source code itself (in particular > > src/lib/base64.c) should be helpful. > > Thanks. That is an excellent half answer! Now I understand which > values I am trying to decode. I am just having trouble decoding them. > See my response to James Harper and thanks for your help. > Mark > > > > ------------------------------------------------------- > Using Tomcat but need to do more? Need to support web services, security? > Get stuff done quickly with pre-integrated technology to make your job > easier > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 > _______________________________________________ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users ------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid0709&bid&3057&dat1642 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
