From 5055a0f639857df5de2afae8d120cb54e395d18c Mon Sep 17 00:00:00 2001 From: Stefan Reddig Date: Thu, 1 Apr 2010 18:04:58 +0200 Subject: [PATCH] fixed syntax LIMIT/NULL behavior/improved tests --- bacula/src/cats/make_ingres_tables.in | 2 +- bacula/src/cats/sql_cmds.c | 79 ++++++++++++- bacula/src/cats/sql_find.c | 91 ++++++++++++++- bacula/src/cats/sql_get.c | 73 ++++++++++++ bacula/src/cats/sql_list.c | 8 ++ bacula/src/tools/ing_test.c | 155 ++++++++++++++++---------- regress/prototype.conf | 2 - 7 files changed, 343 insertions(+), 67 deletions(-) diff --git a/bacula/src/cats/make_ingres_tables.in b/bacula/src/cats/make_ingres_tables.in index a4cb559abc..e2151fe1f6 100755 --- a/bacula/src/cats/make_ingres_tables.in +++ b/bacula/src/cats/make_ingres_tables.in @@ -214,7 +214,7 @@ CREATE TABLE media maxvolfiles integer default 0, maxvolbytes bigint default 0, inchanger smallint default 0, - StorageId integer default 0, + StorageId bigint default 0, DeviceId integer default 0, mediaaddressing smallint default 0, volreadtime bigint default 0, diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 84e2ddf019..02772721ee 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -287,11 +287,19 @@ const char *uar_count_files = "SELECT JobFiles FROM Job WHERE JobId=%s"; /* List last 20 Jobs */ +#if HAVE_INGRES +const char *uar_list_jobs = + "SELECT JobId,Client.Name as Client,StartTime,Level as " + "JobLevel,JobFiles,JobBytes " + "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus IN ('T','W') " + "AND Type='B' ORDER BY StartTime DESC FETCH FIRST 20 ROWS ONLY"; +#else const char *uar_list_jobs = "SELECT JobId,Client.Name as Client,StartTime,Level as " "JobLevel,JobFiles,JobBytes " "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus IN ('T','W') " "AND Type='B' ORDER BY StartTime DESC LIMIT 20"; +#endif const char *uar_print_jobs = "SELECT DISTINCT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName" @@ -312,6 +320,21 @@ const char *uar_sel_files = const char *uar_del_temp = "DROP TABLE temp"; const char *uar_del_temp1 = "DROP TABLE temp1"; +#if HAVE_INGRES +const char *uar_last_full = + "INSERT INTO temp1 SELECT Job.JobId,JobTdate " + "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%s " + "AND Job.ClientId=%s " + "AND Job.StartTime < '%s' " + "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " + "AND JobMedia.JobId=Job.JobId " + "AND Media.Enabled=1 " + "AND JobMedia.MediaId=Media.MediaId " + "AND Job.FileSetId=FileSet.FileSetId " + "AND FileSet.FileSet='%s' " + "%s" + "ORDER BY Job.JobTDate DESC FETCH FIRST 1 ROW ONLY"; +#else const char *uar_last_full = "INSERT INTO temp1 SELECT Job.JobId,JobTdate " "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%s " @@ -325,6 +348,7 @@ const char *uar_last_full = "AND FileSet.FileSet='%s' " "%s" "ORDER BY Job.JobTDate DESC LIMIT 1"; +#endif const char *uar_full = "INSERT INTO temp SELECT Job.JobId,Job.JobTDate," @@ -336,6 +360,24 @@ const char *uar_full = "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId"; +#if HAVE_INGRES +const char *uar_dif = + "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId," + "Job.Level,Job.JobFiles,Job.JobBytes," + "Job.StartTime,Media.VolumeName,JobMedia.StartFile," + "Job.VolSessionId,Job.VolSessionTime " + "FROM Job,JobMedia,Media,FileSet " + "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' " + "AND Job.ClientId=%s " + "AND JobMedia.JobId=Job.JobId " + "AND Media.Enabled=1 " + "AND JobMedia.MediaId=Media.MediaId " + "AND Job.Level='D' AND JobStatus IN ('T','W') AND Type='B' " + "AND Job.FileSetId=FileSet.FileSetId " + "AND FileSet.FileSet='%s' " + "%s" + "ORDER BY Job.JobTDate DESC FETCH FIRST 1 ROW ONLY"; +#else const char *uar_dif = "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId," "Job.Level,Job.JobFiles,Job.JobBytes," @@ -352,6 +394,7 @@ const char *uar_dif = "AND FileSet.FileSet='%s' " "%s" "ORDER BY Job.JobTDate DESC LIMIT 1"; +#endif const char *uar_inc = "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,Job.ClientId," @@ -400,6 +443,20 @@ const char *uar_mediatype = * Find JobId, FileIndex for a given path/file and date * for use when inserting individual files into the tree. */ +#if HAVE_INGRES +const char *uar_jobid_fileindex = + "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " + "WHERE Job.JobId=File.JobId " + "AND Job.StartTime<='%s' " + "AND Path.Path='%s' " + "AND Filename.Name='%s' " + "AND Client.Name='%s' " + "AND Job.ClientId=Client.ClientId " + "AND Path.PathId=File.PathId " + "AND Filename.FilenameId=File.FilenameId " + "AND JobStatus IN ('T','W') AND Type='B' " + "ORDER BY Job.StartTime DESC FETCH FIRST 1 ROW ONLY"; +#else const char *uar_jobid_fileindex = "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId=File.JobId " @@ -412,7 +469,22 @@ const char *uar_jobid_fileindex = "AND Filename.FilenameId=File.FilenameId " "AND JobStatus IN ('T','W') AND Type='B' " "ORDER BY Job.StartTime DESC LIMIT 1"; +#endif +#if HAVE_INGRES +const char *uar_jobids_fileindex = + "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " + "WHERE Job.JobId IN (%s) " + "AND Job.JobId=File.JobId " + "AND Job.StartTime<='%s' " + "AND Path.Path='%s' " + "AND Filename.Name='%s' " + "AND Client.Name='%s' " + "AND Job.ClientId=Client.ClientId " + "AND Path.PathId=File.PathId " + "AND Filename.FilenameId=File.FilenameId " + "ORDER BY Job.StartTime DESC FETCH FIRST 1 ROW ONLY"; +#else const char *uar_jobids_fileindex = "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " @@ -425,6 +497,7 @@ const char *uar_jobids_fileindex = "AND Path.PathId=File.PathId " "AND Filename.FilenameId=File.FilenameId " "ORDER BY Job.StartTime DESC LIMIT 1"; +#endif /* Query to get list of files from table -- presuably built by an external program */ const char *uar_jobid_fileindex_from_table = @@ -722,7 +795,7 @@ const char *uar_file[5] = { "AND Client.ClientId=Job.ClientId " "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 LIMIT 20" + "AND Filename.Name='%s' ORDER BY StartTime DESC FETCH FIRST 20 ROWS ONLY" }; const char *uar_create_temp[5] = { @@ -786,8 +859,8 @@ const char *uar_create_temp[5] = { "Level CHAR," "JobFiles INTEGER," "JobBytes BIGINT," - "StartTime TEXT," - "VolumeName TEXT," + "StartTime VARCHAR(256)," + "VolumeName VARCHAR(256)," "StartFile INTEGER," "VolSessionId INTEGER," "VolSessionTime INTEGER)" diff --git a/bacula/src/cats/sql_find.c b/bacula/src/cats/sql_find.c index fb77dee9d5..6247e10ce0 100644 --- a/bacula/src/cats/sql_find.c +++ b/bacula/src/cats/sql_find.c @@ -75,12 +75,21 @@ db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime) /* If no Id given, we must find corresponding job */ if (jr->JobId == 0) { /* Differential is since last Full backup */ +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND " +"Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s " +"ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + jr->JobType, L_FULL, jr->Name, + edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2)); +#else Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND " "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s " "ORDER BY StartTime DESC LIMIT 1", jr->JobType, L_FULL, jr->Name, edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2)); +#endif if (jr->JobLevel == L_DIFFERENTIAL) { /* SQL cmd for Differential backup already edited above */ @@ -105,12 +114,21 @@ db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime) } sql_free_result(mdb); /* Now edit SQL command for Incremental Job */ +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND " +"Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s " +"AND FileSetId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name, + edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2)); +#else Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND " "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s " "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1", jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name, edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2)); +#endif } else { Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel); goto bail_out; @@ -166,12 +184,21 @@ db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, i pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */ +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND " +"Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s " +"ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + jr->JobType, JobLevel, jr->Name, + edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2)); +#else Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND " "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s " "ORDER BY StartTime DESC LIMIT 1", jr->JobType, JobLevel, jr->Name, edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2)); +#endif if (!QUERY_DB(jcr, mdb, mdb->cmd)) { Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"), sql_strerror(mdb), mdb->cmd); @@ -209,6 +236,16 @@ db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int & db_lock(mdb); /* Differential is since last Full backup */ +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND " +"Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s " +"AND FileSetId=%s AND StartTime>'%s' " +"ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name, + edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2), + stime); +#else Mmsg(mdb->cmd, "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND " "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s " @@ -217,7 +254,7 @@ db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int & jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name, edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2), stime); - +#endif if (!QUERY_DB(jcr, mdb, mdb->cmd)) { db_unlock(mdb); return false; @@ -254,24 +291,46 @@ db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr) db_lock(mdb); Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType); if (jr->JobLevel == L_VERIFY_CATALOG) { +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND " +" JobStatus IN ('T','W') AND Name='%s' AND " +"ClientId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + L_VERIFY_INIT, jr->Name, + edit_int64(jr->ClientId, ed1)); +#else Mmsg(mdb->cmd, "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND " " JobStatus IN ('T','W') AND Name='%s' AND " "ClientId=%s ORDER BY StartTime DESC LIMIT 1", L_VERIFY_INIT, jr->Name, edit_int64(jr->ClientId, ed1)); +#endif } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG || jr->JobLevel == L_VERIFY_DISK_TO_CATALOG || jr->JobType == JT_BACKUP) { if (Name) { +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND " +"Name='%s' ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", Name); +#else Mmsg(mdb->cmd, "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND " "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name); +#endif } else { +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND " +"ClientId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + edit_int64(jr->ClientId, ed1)); +#else Mmsg(mdb->cmd, "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND " "ClientId=%s ORDER BY StartTime DESC LIMIT 1", edit_int64(jr->ClientId, ed1)); +#endif } } else { Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel); @@ -324,6 +383,19 @@ db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr db_lock(mdb); if (item == -1) { /* find oldest volume */ /* Find oldest volume */ +#if HAVE_INGRES + Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks," + "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes," + "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs," + "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger," + "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId," + "Enabled,LocationId,RecycleCount,InitialWrite," + "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge " + "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full'," + "'Recycle','Purged','Used','Append') AND Enabled=1 " + "ORDER BY LastWritten FETCH FIRST 1 ROW ONLY", + edit_int64(mr->PoolId, ed1), mr->MediaType); +#else Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks," "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes," "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs," @@ -335,6 +407,7 @@ db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr "'Recycle','Purged','Used','Append') AND Enabled=1 " "ORDER BY LastWritten LIMIT 1", edit_int64(mr->PoolId, ed1), mr->MediaType); +#endif item = 1; } else { POOL_MEM changer(PM_FNAME); @@ -349,6 +422,21 @@ db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr } else { order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */ } +#if HAVE_INGRES + Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks," + "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes," + "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs," + "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger," + "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId," + "Enabled,LocationId,RecycleCount,InitialWrite," + "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge " + "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 " + "AND VolStatus='%s' " + "%s " + "%s FETCH FIRST %d ROWS ONLY", + edit_int64(mr->PoolId, ed1), mr->MediaType, + mr->VolStatus, changer.c_str(), order, item); +#else Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks," "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes," "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs," @@ -362,6 +450,7 @@ db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr "%s LIMIT %d", edit_int64(mr->PoolId, ed1), mr->MediaType, mr->VolStatus, changer.c_str(), order, item); +#endif } Dmsg1(050, "fnextvol=%s\n", mdb->cmd); if (!QUERY_DB(jcr, mdb, mdb->cmd)) { diff --git a/bacula/src/cats/sql_get.c b/bacula/src/cats/sql_get.c index 540921ef08..2bb689697b 100644 --- a/bacula/src/cats/sql_get.c +++ b/bacula/src/cats/sql_get.c @@ -117,6 +117,16 @@ int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr) char ed1[50], ed2[50], ed3[50]; if (jcr->getJobLevel() == L_VERIFY_DISK_TO_CATALOG) { +#if HAVE_INGRES + Mmsg(mdb->cmd, +"SELECT FileId, LStat, MD5 FROM File,Job WHERE " +"File.JobId=Job.JobId AND File.PathId=%s AND " +"File.FilenameId=%s AND Job.Type='B' AND Job.JobStatus IN ('T','W') AND " +"ClientId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", + edit_int64(fdbr->PathId, ed1), + edit_int64(fdbr->FilenameId, ed2), + edit_int64(jr->ClientId,ed3)); +#else Mmsg(mdb->cmd, "SELECT FileId, LStat, MD5 FROM File,Job WHERE " "File.JobId=Job.JobId AND File.PathId=%s AND " @@ -125,6 +135,7 @@ int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr) edit_int64(fdbr->PathId, ed1), edit_int64(fdbr->FilenameId, ed2), edit_int64(jr->ClientId,ed3)); +#endif } else { Mmsg(mdb->cmd, "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND " @@ -796,9 +807,15 @@ int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr) "WHERE FileSetId=%s", edit_int64(fsr->FileSetId, ed1)); } else { /* find by name */ +#if HAVE_INGRES + Mmsg(mdb->cmd, + "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet " + "WHERE FileSet='%s' ORDER BY CreateTime DESC FETCH FIRST 1 ROW ONLY", fsr->FileSet); +#else Mmsg(mdb->cmd, "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet " "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet); +#endif } if (QUERY_DB(jcr, mdb, mdb->cmd)) { @@ -1171,6 +1188,21 @@ bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb, jobids->count = 0; /* First, find the last good Full backup for this job/client/fileset */ +#if HAVE_INGRES + Mmsg(query, +"CREATE TABLE btemp3%s AS " + "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " + "FROM Job JOIN FileSet USING (FileSetId) " + "WHERE ClientId = %s " + "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " + "AND StartTime<'%s' " + "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) " + "ORDER BY Job.JobTDate DESC FETCH FIRST 1 ROW ONLY", + edit_uint64(jcr->JobId, jobid), + edit_uint64(jr->ClientId, clientid), + date, + edit_uint64(jr->FileSetId, filesetid)); +#else Mmsg(query, "CREATE TABLE btemp3%s AS " "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " @@ -1184,6 +1216,7 @@ bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb, edit_uint64(jr->ClientId, clientid), date, edit_uint64(jr->FileSetId, filesetid)); +#endif if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) { goto bail_out; @@ -1191,6 +1224,23 @@ bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb, if (jr->JobLevel == L_INCREMENTAL || jr->JobLevel == L_VIRTUAL_FULL) { /* Now, find the last differential backup after the last full */ +#if HAVE_INGRES + Mmsg(query, +"INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) " + "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " + "FROM Job JOIN FileSet USING (FileSetId) " + "WHERE ClientId = %s " + "AND Level='D' AND JobStatus IN ('T','W') AND Type='B' " + "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) " + "AND StartTime < '%s' " + "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) " + "ORDER BY Job.JobTDate DESC FETCH FIRST 1 ROW ONLY ", + jobid, + clientid, + jobid, + date, + filesetid); +#else Mmsg(query, "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) " "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " @@ -1206,12 +1256,30 @@ bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb, jobid, date, filesetid); +#endif if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) { goto bail_out; } /* We just have to take all incremental after the last Full/Diff */ +#if HAVE_INGRES + Mmsg(query, +"INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) " + "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " + "FROM Job JOIN FileSet USING (FileSetId) " + "WHERE ClientId = %s " + "AND Level='I' AND JobStatus IN ('T','W') AND Type='B' " + "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC FETCH FIRST 1 ROW ONLY) " + "AND StartTime < '%s' " + "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) " + "ORDER BY Job.JobTDate DESC ", + jobid, + clientid, + jobid, + date, + filesetid); +#else Mmsg(query, "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) " "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " @@ -1227,6 +1295,7 @@ bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb, jobid, date, filesetid); +#endif if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) { goto bail_out; } @@ -1283,6 +1352,10 @@ bool db_get_base_jobid(JCR *jcr, B_DB *mdb, JOB_DBR *jr, JobId_t *jobid) // "AND FileSet.FileSet= '%s' " // "AND Client.Name = '%s' " "AND StartTime<'%s' " +#if HAVE_INGRES +#else + "ORDER BY Job.JobTDate DESC FETCH FIRST 1 ROW ONLY", +#endif "ORDER BY Job.JobTDate DESC LIMIT 1", jr->Name, // edit_uint64(jr->ClientId, clientid), diff --git a/bacula/src/cats/sql_list.c b/bacula/src/cats/sql_list.c index aa903a0ca0..4942ca7940 100644 --- a/bacula/src/cats/sql_list.c +++ b/bacula/src/cats/sql_list.c @@ -249,7 +249,11 @@ void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds, POOL_MEM str_jobids(PM_MESSAGE); if (limit > 0) { +#if HAVE_INGRES + Mmsg(str_limit, " FETCH FIRST %d ROWS ONLY", limit); +#else Mmsg(str_limit, " LIMIT %d", limit); +#endif } if (JobIds && JobIds[0]) { @@ -330,7 +334,11 @@ db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, char limit[100]; db_lock(mdb); if (jr->limit > 0) { +#if HAVE_INGRES + snprintf(limit, sizeof(limit), " FETCH FIRST %d ROWS ONLY", jr->limit); +#else snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit); +#endif } else { limit[0] = 0; } diff --git a/bacula/src/tools/ing_test.c b/bacula/src/tools/ing_test.c index d8b0505749..9bf5400075 100644 --- a/bacula/src/tools/ing_test.c +++ b/bacula/src/tools/ing_test.c @@ -77,7 +77,7 @@ PROG_COPYRIGHT */ static int test_handler(void *ctx, int num_fields, char **row) { - Dmsg2(200, " Values are %d, %s\n", str_to_int64(row[0]), row[1]); + Pmsg2(0, " Values are %d, %s\n", str_to_int64(row[0]), row[1]); return 0; } @@ -86,7 +86,7 @@ static int test_handler(void *ctx, int num_fields, char **row) */ static int string_handler(void *ctx, int num_fields, char **row) { - Dmsg1(200, " Value is >>%s<<\n", row[0]); + Pmsg1(0, " Value is >>%s<<\n", row[0]); return 0; } @@ -199,75 +199,110 @@ int main (int argc, char *argv[]) if (verbose) { Pmsg2(000, _("Using Database: %s, User: %s\n"), db_name, db_user); } - - - /* simple CRUD test including create/drop table */ + /* + * simple CRUD test including create/drop table + */ Pmsg0(0, "\nsimple CRUD test...\n\n"); - - Dmsg0(200, "DB-Statement: CREATE TABLE t1 ( c1 integer, c2 varchar(29))\n"); - if (!db_sql_query(db, "CREATE TABLE t1 ( c1 integer, c2 varchar(29))", NULL, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("CREATE-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: INSERT INTO t1 VALUES (1, 'foo')\n"); - if (!db_sql_query(db, "INSERT INTO t1 VALUES (1, 'foo')", NULL, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("INSERT-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: SELECT c1,c2 FROM t1 (should be 1, foo)\n"); - if (!db_sql_query(db, "SELECT c1,c2 FROM t1", test_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: UPDATE t1 SET c2='bar' WHERE c1=1\n"); - if (!db_sql_query(db, "UPDATE t1 SET c2='bar' WHERE c1=1", NULL, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("UPDATE-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: SELECT * FROM t1 (should be 1, bar)\n"); - if (!db_sql_query(db, "SELECT * FROM t1", test_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: DELETE FROM t1 WHERE c2 LIKE '\%r'\n"); - if (!db_sql_query(db, "DELETE FROM t1 WHERE c2 LIKE '%r'", NULL, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("DELETE-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: SELECT * FROM t1 (should be 0 rows)\n"); - if (!db_sql_query(db, "SELECT * FROM t1", test_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); + const char *stmt1[8] = { + "CREATE TABLE t1 ( c1 integer, c2 varchar(29))", + "INSERT INTO t1 VALUES (1, 'foo')", + "SELECT c1,c2 FROM t1", + "UPDATE t1 SET c2='bar' WHERE c1=1", + "SELECT * FROM t1", + "DELETE FROM t1 WHERE c2 LIKE '\%r'", + "SELECT * FROM t1", + "DROP TABLE t1" + }; + int (*hndl1[8])(void*,int,char**) = { + NULL, + NULL, + test_handler, + NULL, + test_handler, + NULL, + test_handler, + NULL + }; + + for (int i=0; i<8; ++i) { + Pmsg1(0, "DB-Statement: %s\n",stmt1[i]); + if (!db_sql_query(db, stmt1[i], hndl1[i], NULL)) { + Emsg0(M_ERROR_TERM, 0, _("Stmt went wrong\n")); + } } - Dmsg0(200, "DB-Statement: DROP TABLE t1\n"); - if (!db_sql_query(db, "DROP TABLE t1", NULL, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("DROP-Stmt went wrong\n")); - } /* - * simple SELECT test without tables + * simple SELECT tests without tables */ - - Dmsg0(200, "DB-Statement: SELECT 'Test of simple SELECT!'\n"); - if (!db_sql_query(db, "SELECT 'Test of simple SELECT!'", string_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); - } - - Dmsg0(200, "DB-Statement: SELECT 'Test of simple SELECT!' as Text\n"); - if (!db_sql_query(db, "SELECT 'Test of simple SELECT!' as Text", string_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); + Pmsg0(0, "\nsimple SELECT tests without tables...\n\n"); + const char *stmt2[8] = { + "SELECT 'Test of simple SELECT!'", + "SELECT 'Test of simple SELECT!' as Text", + "SELECT VARCHAR(LENGTH('Test of simple SELECT!'))", + "SELECT DBMSINFO('_version')", + "SELECT 'This is a ''quoting'' test with single quotes'", + "SELECT 'This is a \"quoting\" test with double quotes'", + "SELECT null", + "SELECT ''" + }; + int (*hndl2[8])(void*,int,char**) = { + string_handler, + string_handler, + string_handler, + string_handler, + string_handler, + string_handler, + string_handler, + string_handler + }; + + for (int i=0; i<8; ++i) { + Pmsg1(0, "DB-Statement: %s\n",stmt2[i]); + if (!db_sql_query(db, stmt2[i], hndl2[i], NULL)) { + Emsg0(M_ERROR_TERM, 0, _("Stmt went wrong\n")); + } } - Dmsg0(200, "DB-Statement: SELECT VARCHAR(LENGTH('Test of simple SELECT!'))\n"); - if (!db_sql_query(db, "SELECT VARCHAR(LENGTH('Test of simple SELECT!'))", string_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); + /* + * testing aggregates like avg, max, sum + */ + Pmsg0(0, "\ntesting aggregates...\n\n"); + const char *stmt[11] = { + "CREATE TABLE t1 (c1 integer, c2 varchar(29))", + "INSERT INTO t1 VALUES (1,'foo')", + "INSERT INTO t1 VALUES (2,'bar')", + "INSERT INTO t1 VALUES (3,'fun')", + "INSERT INTO t1 VALUES (4,'egg')", + "SELECT max(c1) from t1", + "SELECT sum(c1) from t1", + "INSERT INTO t1 VALUES (5,NULL)", + "SELECT count(*) from t1", + "SELECT count(c2) from t1", + "DROP TABLE t1" + }; + int (*hndl[11])(void*,int,char**) = { + NULL, + NULL, + NULL, + NULL, + NULL, + string_handler, + string_handler, + NULL, + string_handler, + string_handler, + NULL + }; + + for (int i=0; i<11; ++i) { + Pmsg1(0, "DB-Statement: %s\n",stmt[i]); + if (!db_sql_query(db, stmt[i], hndl[i], NULL)) { + Emsg0(M_ERROR_TERM, 0, _("Stmt went wrong\n")); + } } - Dmsg0(200, "DB-Statement: SELECT DBMSINFO('_version')\n"); - if (!db_sql_query(db, "SELECT DBMSINFO('_version')", string_handler, NULL)) { - Emsg0(M_ERROR_TERM, 0, _("SELECT-Stmt went wrong\n")); - } /* * datatypes test diff --git a/regress/prototype.conf b/regress/prototype.conf index ace6b55fba..814f2c06a7 100644 --- a/regress/prototype.conf +++ b/regress/prototype.conf @@ -108,5 +108,3 @@ WIN32_FILE="c:/users/xxxx" WIN32_PORT=9102 # Win32 Client password WIN32_PASSWORD="zzzzz" -# Where the win32 client will have to contact the SD -WIN32_STORE_ADDR="zzzz" -- 2.39.5