From f84b3d5717d3fcbd608e4e5c0699056fb6963525 Mon Sep 17 00:00:00 2001 From: Marco van Wieringen Date: Tue, 13 Apr 2010 19:48:46 +0200 Subject: [PATCH] Ingres doesn't understand a query string with ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId and we must rewrite it to ORDER BY IFNULL(LastWritten, '') DESC,MediaId which we now do by using an array with the correct order statement per backend in sql_cmds just as some of the other database specific queries. --- bacula/src/cats/sql_cmds.c | 19 ++++++++++++++++--- bacula/src/cats/sql_cmds.h | 1 + bacula/src/cats/sql_find.c | 2 +- 3 files changed, 18 insertions(+), 4 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index aa23c50143..18a9f5b32f 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -723,7 +723,7 @@ const char *uar_file[5] = { "AND Job.JobId=File.JobId AND File.FileIndex > 0 " "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " "AND Filename.Name='%s' ORDER BY StartTime DESC FETCH FIRST 20 ROWS ONLY" - }; +}; const char *uar_create_temp[5] = { /* Mysql */ @@ -792,7 +792,7 @@ const char *uar_create_temp[5] = { "VolSessionId INTEGER," "VolSessionTime INTEGER)" "ON COMMIT PRESERVE ROWS WITH NORECOVERY" - }; +}; const char *uar_create_temp1[5] = { /* Mysql */ @@ -874,7 +874,20 @@ const char *uar_jobid_fileindex_from_dir[5] = { "AND Job.ClientId=Client.ClientId " "AND Path.PathId=File.Pathid " "AND Filename.FilenameId=File.FilenameId" - }; +}; + +const char *sql_media_order_most_recently_written[5] { + /* Mysql */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* Postgresql */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* SQLite */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* SQLite3 */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* Ingres */ + "ORDER BY IFNULL(LastWritten, '') DESC,MediaId" +}; const char *sql_get_max_connections[5] = { /* Mysql */ diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h index d17fdbd453..69cd8e3e71 100644 --- a/bacula/src/cats/sql_cmds.h +++ b/bacula/src/cats/sql_cmds.h @@ -81,5 +81,6 @@ extern const char CATS_IMP_EXP *uar_file[5]; extern const char CATS_IMP_EXP *uar_create_temp[5]; extern const char CATS_IMP_EXP *uar_create_temp1[5]; extern const char CATS_IMP_EXP *uar_jobid_fileindex_from_dir[5]; +extern const char CATS_IMP_EXP *sql_media_order_by_leastrecently_used[5]; extern const char CATS_IMP_EXP *sql_get_max_connections[5]; extern const uint32_t CATS_IMP_EXP sql_get_max_connections_index[5]; diff --git a/bacula/src/cats/sql_find.c b/bacula/src/cats/sql_find.c index 3920798a0b..d5b42e0b6d 100644 --- a/bacula/src/cats/sql_find.c +++ b/bacula/src/cats/sql_find.c @@ -346,7 +346,7 @@ db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr strcmp(mr->VolStatus, "Purged") == 0) { order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */ } else { - order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */ + order = sql_media_order_most_recently_written[db_type]; /* take most recently written */ } Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks," "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes," -- 2.39.5