Add new LoadFromProgram method to reduce the likelihood of DB Errors.

Services API Guide::GetProgramGuide started failing after 37a293f. Caused by the out-of-sequence addition of GROUP BY in a query that already had an ORDER BY in it. Callers must supply all WHERE/GROUP BY/ORDER BY values. No defaults are added. See: Guide::GetProgramGuide for a sample of its use. Spotted by Volker on the -users list when the EPG in WebFrontend didn't display any programs. Concept by gigem.
MythTV · Feb 16, 2016 · 606597a · 606597a
1 parent 96d4686
commit 606597a
Show file tree

Hide file tree

Showing 3 changed files with 48 additions and 10 deletions.
diff --git a/mythtv/libs/libmyth/programinfo.cpp b/mythtv/libs/libmyth/programinfo.cpp
@@ -5495,6 +5495,28 @@ static bool FromProgramQuery(const QString &sql, const MSqlBindings &bindings,
     return true;
 }
 
+bool LoadFromProgram(ProgramList &destination, const QString &where,
+                     const QString &groupBy, const QString &orderBy,
+                     const MSqlBindings &bindings, const ProgramList &schedList)
+{
+    uint count = 0;
+
+    QString queryStr = "";
+
+    if (!where.isEmpty())
+        queryStr.append(QString("WHERE %1 ").arg(where));
+
+    if (!groupBy.isEmpty())
+        queryStr.append(QString("GROUP BY %1 ").arg(groupBy));
+
+    if (!orderBy.isEmpty())
+        queryStr.append(QString("ORDER BY %1 ").arg(orderBy));
+
+    // ------------------------------------------------------------------------
+
+    return LoadFromProgram(destination, queryStr, bindings, schedList, 0, 0, count);
+}
+
 bool LoadFromProgram(ProgramList &destination,
                      const QString &sql, const MSqlBindings &bindings,
                      const ProgramList &schedList)
@@ -5510,6 +5532,11 @@ bool LoadFromProgram(ProgramList &destination,
     //        the caller isn't getting what they asked for and to fix that they
     //        are forced to include a GROUP BY, ORDER BY or WHERE that they
     //        do not want
+    //
+    // See the new version of this method above. The plan is to convert existing
+    // users of this method and have them supply all of their own data for the
+    // queries (no defaults.)
+
     if (!queryStr.contains("WHERE"))
         queryStr += " WHERE visible != 0 ";
 

diff --git a/mythtv/libs/libmyth/programinfo.h b/mythtv/libs/libmyth/programinfo.h
@@ -786,6 +786,14 @@ class MPUBLIC ProgramInfo
     static bool usingProgIDAuth;
 };
 
+MPUBLIC bool LoadFromProgram(
+    ProgramList        &destination,
+    const QString      &where,
+    const QString      &groupBy,
+    const QString      &orderBy,
+    const MSqlBindings &bindings,
+    const ProgramList  &schedList);
+
 MPUBLIC bool LoadFromProgram(
     ProgramList        &destination,
     const QString      &sql,

diff --git a/mythtv/programs/mythbackend/services/guide.cpp b/mythtv/programs/mythbackend/services/guide.cpp
@@ -89,15 +89,17 @@ DTC::ProgramGuide *Guide::GetProgramGuide( const QDateTime &rawStartTime ,
     ProgramList  schedList;
     MSqlBindings bindings;
 
-    // lpad is to allow natural sorting of numbers
-    QString      sSQL = "WHERE ";
-
-    sSQL +=     "program.chanid = :CHANID "
-                "AND program.endtime >= :STARTDATE "
-                "AND program.starttime < :ENDDATE "
-                "AND program.starttime >= :STARTDATELIMIT "
-                "AND program.manualid = 0 " // Exclude programmes created purely for 'manual' recording schedules
-                "ORDER BY program.starttime ";
+    QString sWhere   = "program.chanid = :CHANID "
+                       "AND program.endtime >= :STARTDATE "
+                       "AND program.starttime < :ENDDATE "
+                       "AND program.starttime >= :STARTDATELIMIT "
+                       "AND program.manualid = 0"; // Omit 'manual' recordings scheds
+
+    QString sGroupBy = "program.starttime, channel.channum,"
+                       "channel.callsign, program.title";
+
+    QString sOrderBy = "program.starttime";
+
     bindings[":STARTDATE"     ] = dtStartTime;
     bindings[":STARTDATELIMIT"] = dtStartTime.addDays(-1);
     bindings[":ENDDATE"       ] = dtEndTime;
@@ -129,7 +131,8 @@ DTC::ProgramGuide *Guide::GetProgramGuide( const QDateTime &rawStartTime ,
         // Load the list of programmes for this channel
         ProgramList  progList;
         bindings[":CHANID"] = (*chan_it).chanid;
-        LoadFromProgram( progList, sSQL, bindings, schedList );
+        LoadFromProgram( progList, sWhere, sOrderBy, sOrderBy, bindings,
+                         schedList );
 
         // Create Program objects and add them to the channel object
         ProgramList::iterator progIt;