[mythtv] [PATCH] mythfilldatabase errors

[Ben Bucksch]

I got SQL errors when running mythfilldatabase from CVS.

1.

Duplicate entry '1045-20030421070000' for key 1
INSERT INTO program 
(chanid,starttime,endtime,title,subtitle,description,category,airdate,stars,previouslyshown) 
VALUES(1045,"20030421082000","20030421082500","Kleine Geschichten von 
wilden Tieren","Der Elch","Dachzeile: KINDERPROGRAMM
","Kinderserie","0","0","0");

I get 1000s of lines from these on stdout. IIRC, I previously had 
similar problems with the indexes on title.
I removed the primary keys / indexes from the program and record tables 
and that problem is gone. Not sure what the right fix would be, but a 
unique chan/starttime seems to conflict with the fact that there are 
conflicting programmes (not sure why), which some special Myth code 
explicitly checks for and removes ("removing conflicting program"), and 
I get lots of those.

2. It's nice to see that you added credits data to the DB. But the names 
can include quotes:

You have an error in your SQL syntax near 'M")")' at line 1
SELECT person FROM people WHERE name = "Bernard Lee ("M")";

A patch for that is attached. It also changes an .ascii() to 
utf8().data(), which sounds better to me (or was there a reason that you 
used ascii()?).

BTW: Maybe change the role column to an int to speed things up (1=actor, 
2=presenter) and change its name to "type" or something else - I'd think 
that the character name is in field "role".
-------------- next part --------------
Index: programs/mythfilldatabase/filldata.cpp
===================================================================
RCS file: /var/lib/cvs/MC/programs/mythfilldatabase/filldata.cpp,v
retrieving revision 1.55
diff -u -r1.55 filldata.cpp
--- programs/mythfilldatabase/filldata.cpp	20 Apr 2003 01:13:51 -0000	1.55
+++ programs/mythfilldatabase/filldata.cpp	22 Apr 2003 14:53:09 -0000
@@ -1029,6 +1034,9 @@
             QValueList<ProgCredit>::iterator k = (*i).credits.begin();
             for (; k != (*i).credits.end(); k++)
             {
+                (*k).name.replace(QRegExp("\""), QString("\\\""));
+                (*k).role.replace(QRegExp("\""), QString("\\\""));
+
                 querystr.sprintf("SELECT person FROM people WHERE "
                                  "name = \"%s\";", (*k).name.utf8().data());
                 if (!query.exec(querystr.utf8().data()))
@@ -1071,7 +1079,7 @@
                                  "(%d, \"%s\", \"%s\", %d);",
                                  chanid,
                                  (*i).start.toString("yyyyMMddhhmmss").ascii(),
-                                 (*k).role.ascii(),
+                                 (*k).role.utf8().data(),
                                  personid);
 
                 if (!query.exec(querystr.utf8().data()))