From fdb97158da9f1f5a3ed27c8ea7f8ca4bc8529c7d Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@baculasystems.com>
Date: Wed, 1 Jun 2022 17:04:55 +0200
Subject: [PATCH] Use regular LIKE for MySQL and SQLite, use ILIKE for
 PostgreSQL in .search command

The lower() trick doesn't work on MySQL because we use BLOB to store text.
So, we revert to LIKE and ILIKE for PostgreSQL
---
 bacula/src/cats/sql_cmds.c | 12 +++++++++++-
 bacula/src/cats/sql_cmds.h |  1 +
 bacula/src/cats/sql_list.c | 10 ++++++----
 3 files changed, 18 insertions(+), 5 deletions(-)

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index d8a799e03..82d5a54cb 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -925,7 +925,17 @@ const char *match_query[] =
    /* SQLite */
    "LIKE"                       /* MATCH doesn't seems to work anymore... */
 }; 
- 
+
+const char *sql_like[] =
+{
+   /* MySQL */
+   "LIKE",
+   /* PostgreSQL */
+   "ILIKE",
+   /* SQLite */
+   "LIKE"
+};
+
 static const char *insert_counter_values_default =
    "INSERT INTO Counters (Counter, MinValue, "
    "MaxValue, CurrentValue, WrapCounter) "
diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h
index 634697fd1..3b589cc47 100644
--- a/bacula/src/cats/sql_cmds.h
+++ b/bacula/src/cats/sql_cmds.h
@@ -115,3 +115,4 @@ extern const char CATS_IMP_EXP *get_volume_size;
 extern const char CATS_IMP_EXP *escape_char_value[];
 extern const char CATS_IMP_EXP *regexp_value[];
 extern const char CATS_IMP_EXP *copy_object[];
+extern const char CATS_IMP_EXP *sql_like[];
diff --git a/bacula/src/cats/sql_list.c b/bacula/src/cats/sql_list.c
index a47f906d1..e7d6bad70 100644
--- a/bacula/src/cats/sql_list.c
+++ b/bacula/src/cats/sql_list.c
@@ -111,7 +111,7 @@ bool BDB::bdb_search_job_records(JCR *jcr, JOB_DBR *jr,
    Mmsg(cmd,
         "SELECT Job "
         "FROM Job "
-        " %s WHERE lower(Job.Job) LIKE lower('%%%s%%') %s", join, esc, where_tmp);
+        " %s WHERE Job.Job %s '%%%s%%' %s", join, sql_like[bdb_get_type_index()], esc, where_tmp);
 
    if (jr->limit > 0) {
       char ed1[50];
@@ -140,7 +140,8 @@ bool BDB::bdb_search_client_records(JCR *jcr, CLIENT_DBR *rec, DB_RESULT_HANDLER
    where_tmp = get_acls(DB_ACL_BIT(DB_ACL_CLIENT), 0);
 
    Mmsg(cmd, "SELECT Name "
-        "FROM Client WHERE lower(Name) LIKE lower('%%%s%%') %s",
+        "FROM Client WHERE Name %s '%%%s%%' %s",
+        sql_like[bdb_get_type_index()],
         esc, where_tmp);
 
    if (rec->limit > 0) {
@@ -443,8 +444,9 @@ bool BDB::bdb_search_media_records(JCR *jcr, MEDIA_DBR *mdbr,
       mdbr->limit = 50;
    }
 
-   Mmsg(cmd, "SELECT VolumeName FROM Media %s WHERE lower(Media.VolumeName) LIKE lower('%%%s%%') %s LIMIT %u",
+   Mmsg(cmd, "SELECT VolumeName FROM Media %s WHERE Media.VolumeName %s '%%%s%%' %s LIMIT %u",
         join,
+        sql_like[bdb_get_type_index()],
         esc,
         where,
         mdbr->limit);
@@ -811,7 +813,7 @@ void BDB::bdb_list_joblog_records(JCR *jcr, uint32_t JobId, const char *pattern,
       POOL_MEM esc;
       esc.check_size(strlen(pattern) * 2 + 1);
       bdb_escape_string(jcr, esc.c_str(), pattern, strlen(pattern));
-      Mmsg(tmp, "lower(Log.LogText) LIKE lower('%%%s%%') ", esc.c_str());
+      Mmsg(tmp, "Log.LogText %s '%%%s%%' ", sql_like[bdb_get_type_index()], esc.c_str());
       append_filter(where2.handle(), tmp.c_str());
    }
 
-- 
2.47.3