Re: [Bacula-users] Filename searching and case insensitivity

On 6 April 2011 02:31, Kernel Panic <kpnemesis AT gmail DOT com> wrote:
> Hello everyone,
>
> Whilst trying to find a way of doing a case-insensitive search for
> file, I found previous posts on the mailing lists that instructed me
> to use the sqlquery function in bacula. As a test I wanted to search
> for files with zfs in their name and then with ZFS in the name. After
> starting up bconsole and entering sqlquery mode I did the following:
>
> USE bacula;
> SELECT * FROM Filename WHERE name LIKE '%ZFS%';
> SELECT * FROM Filename WHERE name LIKE '%zfs%';
>
> Although the commands worked, they only returned case-sensitive
> matches, despite MySQL's documentation stating that pattern matching
> is case-insensitive by default:
> http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html
>
> Can anyone help me?
>
> Thanks.
>

Looking at the code in the make_mysql_tables script shows the following:

-- Note, we use BLOB rather than TEXT because in MySQL,
--  BLOBs are identical to TEXT except that BLOB is case
--  sensitive in sorts, which is what we want, and TEXT
--  is case insensitive.
--
CREATE TABLE Filename (
  FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name BLOB NOT NULL,
  PRIMARY KEY(FilenameId),
  INDEX (Name(255))
  );

I admit I know next to nothing about SQL, but since they are using
BLOB when creating the 'Filename' table does this mean a
case-insensitive search is not possible?

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users