[mythtv] Re: [mythtv-commits] Re: Ticket #602: Add ability to

[Chris Pinkham]

> Wendy Seltzer wrote:
> 
> > Duplicate column name 'recordid'
> 
> mysqld --version
> 
> --  bjm

I see the same here.  I think that section of code needs to be
modified no matter what the MySQL version because we're
duplicating the recordmatch schema when we shouldn't be (here
and in dbquery).  My MySQL version is
"mysql  Ver 11.18 Distrib 3.23.58".   My MySQL also barfed
on the "drop temporary table" command, it doesn't like the word
temporary in there.

Here's what I did to get the code working on my system and it's
futureproof if we ever add fields to recordmatch.  If you don't
see anything wrong, feel free to commit or let me know and I will.
Once we create out temporary table recordmatch, it overshadows
the real recordmatch, so we can create and drop our temp table
without specifying temporary.  I verified and my real recordmatch
table does exist after this code completes, so the drop is dropping
the temp version.  I'm also trying to figure out why this screen
doesn't work in the Titivillus theme, but didn't notice anything,
but seeing the time maybe it's that I'm too sleepy to debug but
not too sleepy to code (hence my reason for posting this patch
for review). :)

Index: programs/mythbackend/scheduler.cpp
===================================================================
--- programs/mythbackend/scheduler.cpp  (revision 8008)
+++ programs/mythbackend/scheduler.cpp  (working copy)
@@ -297,19 +297,15 @@

     MSqlQuery query(dbConn);
     QString thequery;
+    QString where = "";
+
+    // This will cause our temp copy of recordmatch to be empty
     if (recordid == -1)
-    {
-            thequery = "CREATE TEMPORARY TABLE recordmatch "
-                  "(recordid int unsigned, chanid int unsigned, "
-                  " starttime datetime, manualid int unsigned, "
-                  " INDEX (recordid));";
-    } else {
-            thequery = "CREATE TEMPORARY TABLE recordmatch "
-                  "(recordid int unsigned, chanid int unsigned, "
-                  " starttime datetime, manualid int unsigned, "
-                  " INDEX (recordid)) SELECT * from recordmatch;";
-    }
+        where = "WHERE starttime = 19000101000000";

+    thequery = QString("CREATE TEMPORARY TABLE recordmatch ") +
+                           "SELECT * FROM recordmatch " + where + "; ";
+
     query.prepare(thequery);
     recordmatchLock.lock();
     query.exec();
@@ -320,11 +316,20 @@
         return;
     }

+    thequery = "ALTER TABLE recordmatch ADD INDEX (recordid);";
+    query.prepare(thequery);
+    query.exec();
+    if (!query.isActive())
+    {
+        MythContext::DBError("FillRecordListFromDB", query);
+        return;
+    }
+
     UpdateMatches(recordid);
     FillRecordList();

     MSqlQuery queryDrop(dbConn);
-    queryDrop.prepare("DROP TEMPORARY TABLE recordmatch;");
+    queryDrop.prepare("DROP TABLE recordmatch;");
     queryDrop.exec();
     if (!queryDrop.isActive())
     {



-- 
Chris