[Bacula-users] Re: File attributes
James Harper wrote: 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 Thanks for the example. I'll try to work out something similar and then try to match the results with some actual file dates. 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
RE: [Bacula-users] Re: File attributes
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_tst_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_tst_atime; /* time of last access */
> > time_tst_mtime; /* time of last modification */
> > time_tst_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
Re: [Bacula-users] Re: File attributes
Hi, There was thread a couple of weeks back about the fact that bacula uses a slightly different algorithm to the "standard" base64 one. Would that apply in this case and would it account for the confusing output received by Mark? Just a thought - could be completely off the mark. Troy. James Harper wrote: James Harper wrote: Do you want to deal with it in the query (harder), or in the code that processes the query (easier)? Are you asking what Base64 is, or do you know already and would just like some help doing something useful with it from within the SQL language? My hope was to write a custom query to put in the query.sql file so I could run it from bconsole, but if I have to I guess I could script something independently to deal with the conversion. The way to do that might depend on the backend... hmmm... something to think about. I've tried a number of online decoders (including the one you suggest) and none of them returns anything that makes sense when I give it a value like BEHwQV. As you say, I am expecting a big integer, but I get back nonsense or an error message. That is what made me question whether I was going about it completely wrong. At this point I am stuck on the conversion. It does give you a big integer if that's how you want to interpret it, when I type in 'BEHwQV' in the 'BASE64' window and then click 'Decode', I get ' 04 41 f0 41' in the hex window. I might have my endian-ness around the wrong way here, but if I shuffle that around to '41f04104', and convert it to decimal, I get 1106264324. This should be somewhere early 2005 if interpreted as number of seconds since Jan 1 1970. James --- 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=k&kid0709&bid&3057&dat1642 ___ 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&kid=120709&bid=263057&dat=121642 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
RE: [Bacula-users] Re: File attributes
> > James Harper wrote: > > Do you want to deal with it in the query (harder), or in the code that > > processes the query (easier)? Are you asking what Base64 is, or do you > > know already and would just like some help doing something useful with > > it from within the SQL language? > > My hope was to write a custom query to put in the query.sql file so I > could run it from bconsole, but if I have to I guess I could script > something independently to deal with the conversion. The way to do that might depend on the backend... hmmm... something to think about. > > I've tried a number of online decoders (including the one you suggest) > and none of them returns anything that makes sense when I give it a > value like BEHwQV. As you say, I am expecting a big integer, but I get > back nonsense or an error message. That is what made me question > whether I was going about it completely wrong. At this point I am stuck > on the conversion. It does give you a big integer if that's how you want to interpret it, when I type in 'BEHwQV' in the 'BASE64' window and then click 'Decode', I get ' 04 41 f0 41' in the hex window. I might have my endian-ness around the wrong way here, but if I shuffle that around to '41f04104', and convert it to decimal, I get 1106264324. This should be somewhere early 2005 if interpreted as number of seconds since Jan 1 1970. James --- 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
[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_tst_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_tst_atime; /* time of last access */
time_tst_mtime; /* time of last modification */
time_tst_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
[Bacula-users] Re: File attributes
James Harper wrote:
Do you want to deal with it in the query (harder), or in the code that
processes the query (easier)? Are you asking what Base64 is, or do you
know already and would just like some help doing something useful with
it from within the SQL language?
My hope was to write a custom query to put in the query.sql file so I
could run it from bconsole, but if I have to I guess I could script
something independently to deal with the conversion.
Just in case you don't know already, the date fields in the text you
have given below are "BEU0tK BEHwQV BEHwQV", and you need to decode them
using Base64, where each letter represents 1 of 64 possible values (eg
our numbering system is Base10 and the digits are 0123456789, base64 is
the same but with all the numbers (10), all the lowercase letters (26),
all the uppercase letters (26), and another 2 characters ('+' and '/' I
think, but you'd need to check), giving you your 64 'digit'
combinations.
Thanks, I had the basic idea but that is a better explanation.
Once you have decoded these values you should have a number that
represents the number of seconds since midnight on Jan 1 1970 UTC.
The following URL is to a really cool page that allows you to convert
between all sorts of encoding methods:
http://www.paulschou.com/tools/xlate/
I've tried a number of online decoders (including the one you suggest)
and none of them returns anything that makes sense when I give it a
value like BEHwQV. As you say, I am expecting a big integer, but I get
back nonsense or an error message. That is what made me question
whether I was going about it completely wrong. At this point I am stuck
on the conversion.
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
Re: [Bacula-users] Re: File attributes
Mark Nienberg 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
Thanks,
Mark
Anyone?
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_tst_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_tst_atime; /* time of last access */
time_tst_mtime; /* time of last modification */
time_tst_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.
- Cedric
---
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
RE: [Bacula-users] Re: File attributes
Do you want to deal with it in the query (harder), or in the code that
processes the query (easier)? Are you asking what Base64 is, or do you
know already and would just like some help doing something useful with
it from within the SQL language?
Just in case you don't know already, the date fields in the text you
have given below are "BEU0tK BEHwQV BEHwQV", and you need to decode them
using Base64, where each letter represents 1 of 64 possible values (eg
our numbering system is Base10 and the digits are 0123456789, base64 is
the same but with all the numbers (10), all the lowercase letters (26),
all the uppercase letters (26), and another 2 characters ('+' and '/' I
think, but you'd need to check), giving you your 64 'digit'
combinations.
Once you have decoded these values you should have a number that
represents the number of seconds since midnight on Jan 1 1970 UTC.
The following URL is to a really cool page that allows you to convert
between all sorts of encoding methods:
http://www.paulschou.com/tools/xlate/
'man stat' should tell you about the 'struct stat' where all these
fields are.
HTH
James
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:bacula-users-
> [EMAIL PROTECTED] On Behalf Of Mark Nienberg
> Sent: Thursday, 11 May 2006 08:35
> To: bacula-users@lists.sourceforge.net
> Subject: [Bacula-users] Re: File attributes
>
> 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
> >
> > Thanks,
> > Mark
>
> Anyone?
>
>
>
> ---
> 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
[Bacula-users] Re: File attributes
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 Thanks, Mark Anyone? --- 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
