Ticket #13057: programdata.cpp.fixed_with_debug

File programdata.cpp.fixed_with_debug, 59.4 KB (added by klaas@…, 3 years ago)

libs/libmythtv/programdata.cpp with fix and with lots of debug code

Line 
1// -*- Mode: c++ -*-
2
3#include <limits.h>
4
5// C++ includes
6#include <algorithm>
7using namespace std;
8
9// Qt includes
10#include <QtCore> // for qAbs
11
12// MythTV headers
13#include "programdata.h"
14#include "channelutil.h"
15#include "mythdb.h"
16#include "mythlogging.h"
17#include "dvbdescriptors.h"
18
19#define LOC      QString("ProgramData: ")
20
21static const char *roles[] =
22{
23    "",
24    "actor",     "director",    "producer", "executive_producer",
25    "writer",    "guest_star",  "host",     "adapter",
26    "presenter", "commentator", "guest",
27};
28
29static QString denullify(const QString &str)
30{
31    return str.isNull() ? "" : str;
32}
33
34static QVariant denullify(const QDateTime &dt)
35{
36    return dt.isNull() ? QVariant("0000-00-00 00:00:00") : QVariant(dt);
37}
38
39static void add_genres(MSqlQuery &query, const QStringList &genres,
40                uint chanid, const QDateTime &starttime)
41{
42    QString relevance = QStringLiteral("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ");
43    QStringList::const_iterator it = genres.constBegin();
44    for (; (it != genres.end()) &&
45             ((it - genres.constBegin()) < relevance.size()); ++it)
46    {
47        query.prepare(
48           "INSERT INTO programgenres "
49           "       ( chanid,  starttime, genre,  relevance) "
50           "VALUES (:CHANID, :START,    :genre, :relevance)");
51        query.bindValue(":CHANID",    chanid);
52        query.bindValue(":START",     starttime);
53        query.bindValue(":genre",     *it);
54        query.bindValue(":relevance", relevance.at(it - genres.constBegin()));
55
56        if (!query.exec())
57            MythDB::DBError("programgenres insert", query);
58    }
59}
60
61DBPerson::DBPerson(const DBPerson &other) :
62    role(other.role), name(other.name)
63{
64    name.squeeze();
65}
66
67DBPerson::DBPerson(Role _role, const QString &_name) :
68    role(_role), name(_name)
69{
70    name.squeeze();
71}
72
73DBPerson::DBPerson(const QString &_role, const QString &_name) :
74    role(kUnknown), name(_name)
75{
76    if (!_role.isEmpty())
77    {
78        for (uint i = 0; i < sizeof(roles) / sizeof(char *); i++)
79        {
80            if (_role == QString(roles[i]))
81                role = (Role) i;
82        }
83    }
84    name.squeeze();
85}
86
87QString DBPerson::GetRole(void) const
88{
89    if ((role < kActor) || (role > kGuest))
90        return "guest";
91    return roles[role];
92}
93
94uint DBPerson::InsertDB(MSqlQuery &query, uint chanid,
95                        const QDateTime &starttime) const
96{
97    uint personid = GetPersonDB(query);
98    if (!personid && InsertPersonDB(query))
99        personid = GetPersonDB(query);
100
101    return InsertCreditsDB(query, personid, chanid, starttime);
102}
103
104uint DBPerson::GetPersonDB(MSqlQuery &query) const
105{
106    query.prepare(
107        "SELECT person "
108        "FROM people "
109        "WHERE name = :NAME");
110    query.bindValue(":NAME", name);
111
112    if (!query.exec())
113        MythDB::DBError("get_person", query);
114    else if (query.next())
115        return query.value(0).toUInt();
116
117    return 0;
118}
119
120uint DBPerson::InsertPersonDB(MSqlQuery &query) const
121{
122    query.prepare(
123        "INSERT IGNORE INTO people (name) "
124        "VALUES (:NAME);");
125    query.bindValue(":NAME", name);
126
127    if (query.exec())
128        return 1;
129
130    MythDB::DBError("insert_person", query);
131    return 0;
132}
133
134uint DBPerson::InsertCreditsDB(MSqlQuery &query, uint personid, uint chanid,
135                               const QDateTime &starttime) const
136{
137    if (!personid)
138        return 0;
139
140    query.prepare(
141        "REPLACE INTO credits "
142        "       ( person,  chanid,  starttime,  role) "
143        "VALUES (:PERSON, :CHANID, :STARTTIME, :ROLE) ");
144    query.bindValue(":PERSON",    personid);
145    query.bindValue(":CHANID",    chanid);
146    query.bindValue(":STARTTIME", starttime);
147    query.bindValue(":ROLE",      GetRole());
148
149    if (query.exec())
150        return 1;
151
152    MythDB::DBError("insert_credits", query);
153    return 0;
154}
155
156DBEvent &DBEvent::operator=(const DBEvent &other)
157{
158    if (this == &other)
159        return *this;
160
161    title           = other.title;
162    subtitle        = other.subtitle;
163    description     = other.description;
164    category        = other.category;
165    starttime       = other.starttime;
166    endtime         = other.endtime;
167    airdate         = other.airdate;
168    originalairdate = other.originalairdate;
169
170    if (credits != other.credits)
171    {
172        if (credits)
173        {
174            delete credits;
175            credits = NULL;
176        }
177
178        if (other.credits)
179        {
180            credits = new DBCredits;
181            credits->insert(credits->end(),
182                            other.credits->begin(),
183                            other.credits->end());
184        }
185    }
186
187    partnumber      = other.partnumber;
188    parttotal       = other.parttotal;
189    syndicatedepisodenumber = other.syndicatedepisodenumber;
190    subtitleType    = other.subtitleType;
191    audioProps      = other.audioProps;
192    videoProps      = other.videoProps;
193    stars           = other.stars;
194    categoryType    = other.categoryType;
195    seriesId        = other.seriesId;
196    programId       = other.programId;
197    inetref         = other.inetref;
198    previouslyshown = other.previouslyshown;
199    ratings         = other.ratings;
200    listingsource   = other.listingsource;
201    season          = other.season;
202    episode         = other.episode;
203    totalepisodes   = other.totalepisodes;
204    genres          = other.genres;
205
206    Squeeze();
207
208    return *this;
209}
210
211void DBEvent::Squeeze(void)
212{
213    title.squeeze();
214    subtitle.squeeze();
215    description.squeeze();
216    category.squeeze();
217    syndicatedepisodenumber.squeeze();
218    seriesId.squeeze();
219    programId.squeeze();
220    inetref.squeeze();
221}
222
223void DBEvent::AddPerson(DBPerson::Role role, const QString &name)
224{
225    if (!credits)
226        credits = new DBCredits;
227
228    credits->push_back(DBPerson(role, name.simplified()));
229}
230
231void DBEvent::AddPerson(const QString &role, const QString &name)
232{
233    if (!credits)
234        credits = new DBCredits;
235
236    credits->push_back(DBPerson(role, name.simplified()));
237}
238
239bool DBEvent::HasTimeConflict(const DBEvent &o) const
240{
241    return ((starttime <= o.starttime && o.starttime < endtime) ||
242            (o.endtime <= endtime     && starttime   < o.endtime));
243}
244
245// Processing new EIT entry starts here
246uint DBEvent::UpdateDB(
247    MSqlQuery &query, uint chanid, int match_threshold) const
248{
249    // List the program that we are going to add
250    LOG(VB_EIT, LOG_DEBUG,
251        QString("EIT: new program: %1 %2 '%3' chanid %4")
252                .arg(starttime.toString(Qt::ISODate))
253                .arg(endtime.toString(Qt::ISODate))
254                .arg(title.left(35))
255                .arg(chanid));
256
257    // Do not insert or update when the program is in the past
258    QDateTime now = QDateTime::currentDateTimeUtc();
259    if (endtime < now)
260    {
261        LOG(VB_EIT, LOG_DEBUG,
262            QString("EIT: skip '%1' endtime is in the past")
263                    .arg(title.left(35)));
264        return 0;
265    }
266
267    // Get all programs already in the database that overlap
268    // with our new program.
269    vector<DBEvent> programs;
270    uint count = GetOverlappingPrograms(query, chanid, programs);
271    int  match = INT_MIN;
272    int  i     = -1;
273
274    // If there are no programs already in the database that overlap
275    // with our new program then we can simply insert it in the database.
276    if (!count)
277        return InsertDB(query, chanid);
278
279    // List all overlapping programs with start- and endtime.
280    for (uint j=0; j<count; j++)
281    {
282        LOG(VB_EIT, LOG_DEBUG,
283            QString("EIT: overlap[%1] : %2 %3 '%4'")
284                .arg(j)
285                .arg(programs[j].starttime.toString(Qt::ISODate))
286                .arg(programs[j].endtime.toString(Qt::ISODate))
287                .arg(programs[j].title.left(35)));
288    }
289
290    // Determine which of the overlapping programs is a match with
291    // our new program; if we have a match then our new program is considered
292    // to be an update of the matching program.
293    // The 2nd parameter "i" is the index of the best matching program.
294    match = GetMatch(programs, i);
295
296    // Update an existing program or insert a new program.
297    if (match >= match_threshold)
298    {
299        // We have a good match; update program[i] in the database
300        // with the new program data and move the overlapping programs
301        // out of the way.
302        LOG(VB_EIT, LOG_DEBUG,
303            QString("EIT: accept match[%1]: %2 '%3' vs. '%4'")
304                .arg(i).arg(match).arg(title.left(35))
305                .arg(programs[i].title.left(35)));
306        return UpdateDB(query, chanid, programs, i);
307    }
308    else
309    {
310        // If we are here then either we have a match but the match is
311        // not good enough (the "i >= 0" case) or we did not find
312        // a match at all.
313        if (i >= 0)
314        {
315            LOG(VB_EIT, LOG_DEBUG,
316                QString("EIT: reject match[%1]: %2 '%3' vs. '%4'")
317                    .arg(i).arg(match).arg(title.left(35))
318                    .arg(programs[i].title.left(35)));
319        }
320
321        // Move the overlapping programs out of the way and
322        // insert the new program.
323        return UpdateDB(query, chanid, programs, -1);
324    }
325}
326
327// Get all programs in the database that overlap with our new program.
328// We check for three ways in which we can have an overlap:
329// (1)   Start of old program is inside our new program:
330//           old program starts at or after our program AND
331//           old program starts before end of our program;
332//       e.g. new program  s-------------e
333//            old program      s-------------e
334//         or old program      s-----e
335//       This is the STIME1/ETIME1 comparison.
336// (2)   End of old program is inside our new program:
337//           old program ends after our program starts AND
338//           old program ends before end of our program
339//       e.g. new program      s-------------e
340//            old program  s-------------e
341//         or old program          s-----e
342//       This is the STIME2/ETIME2 comparison.
343// (3)   We can have a new program is "inside" the old program:
344//           old program starts before our program AND
345//          old program ends after end of our program
346//       e.g. new program      s---------e
347//            old program  s-----------------e
348//       This is the STIME3/ETIME3 comparison.
349//
350uint DBEvent::GetOverlappingPrograms(
351    MSqlQuery &query, uint chanid, vector<DBEvent> &programs) const
352{
353    uint count = 0;
354    query.prepare(
355        "SELECT title,          subtitle,      description, "
356        "       category,       category_type, "
357        "       starttime,      endtime, "
358        "       subtitletypes+0,audioprop+0,   videoprop+0, "
359        "       seriesid,       programid, "
360        "       partnumber,     parttotal, "
361        "       syndicatedepisodenumber, "
362        "       airdate,        originalairdate, "
363        "       previouslyshown,listingsource, "
364        "       stars+0, "
365        "       season,         episode,       totalepisodes, "
366        "       inetref "
367        "FROM program "
368        "WHERE chanid   = :CHANID AND "
369        "      manualid = 0       AND "
370        "      ( ( starttime >= :STIME1 AND starttime <  :ETIME1 ) OR "
371        "        ( endtime   >  :STIME2 AND endtime   <= :ETIME2 ) OR "
372        "        ( starttime <  :STIME3 AND endtime   >  :ETIME3 ) )");
373    query.bindValue(":CHANID", chanid);
374    query.bindValue(":STIME1", starttime);
375    query.bindValue(":ETIME1", endtime);
376    query.bindValue(":STIME2", starttime);
377    query.bindValue(":ETIME2", endtime);
378    query.bindValue(":STIME3", starttime);
379    query.bindValue(":ETIME3", endtime);
380
381    if (!query.exec())
382    {
383        MythDB::DBError("GetOverlappingPrograms 1", query);
384        return 0;
385    }
386
387    while (query.next())
388    {
389        ProgramInfo::CategoryType category_type =
390            string_to_myth_category_type(query.value(4).toString());
391
392        DBEvent prog(
393            query.value(0).toString(),
394            query.value(1).toString(),
395            query.value(2).toString(),
396            query.value(3).toString(),
397            category_type,
398            MythDate::as_utc(query.value(5).toDateTime()),
399            MythDate::as_utc(query.value(6).toDateTime()),
400            query.value(7).toUInt(),
401            query.value(8).toUInt(),
402            query.value(9).toUInt(),
403            query.value(19).toDouble(),
404            query.value(10).toString(),
405            query.value(11).toString(),
406            query.value(18).toUInt(),
407            query.value(20).toUInt(),  // Season
408            query.value(21).toUInt(),  // Episode
409            query.value(22).toUInt()); // Total Episodes
410
411        prog.inetref    = query.value(23).toString();
412        prog.partnumber = query.value(12).toUInt();
413        prog.parttotal  = query.value(13).toUInt();
414        prog.syndicatedepisodenumber = query.value(14).toString();
415        prog.airdate    = query.value(15).toUInt();
416        prog.originalairdate  = query.value(16).toDate();
417        prog.previouslyshown  = query.value(17).toBool();
418
419        programs.push_back(prog);
420        count++;
421    }
422
423    return count;
424}
425
426
427static int score_words(const QStringList &al, const QStringList &bl)
428{
429    QStringList::const_iterator ait = al.begin();
430    QStringList::const_iterator bit = bl.begin();
431    int score = 0;
432    for (; (ait != al.end()) && (bit != bl.end()); ++ait)
433    {
434        QStringList::const_iterator bit2 = bit;
435        int dist = 0;
436        int bscore = 0;
437        for (; bit2 != bl.end(); ++bit2)
438        {
439            if (*ait == *bit)
440            {
441                bscore = max(1000, 2000 - (dist * 500));
442                // lower score for short words
443                if (ait->length() < 5)
444                    bscore /= 5 - ait->length();
445                break;
446            }
447            dist++;
448        }
449        if (bscore && dist < 3)
450        {
451            for (int i = 0; (i < dist) && bit != bl.end(); i++)
452                ++bit;
453        }
454        score += bscore;
455    }
456
457    return score / al.size();
458}
459
460static int score_match(const QString &a, const QString &b)
461{
462    if (a.isEmpty() || b.isEmpty())
463        return 0;
464    else if (a == b)
465        return 1000;
466
467    QString A = a.simplified().toUpper();
468    QString B = b.simplified().toUpper();
469    if (A == B)
470        return 1000;
471
472    QStringList al, bl;
473    al = A.split(" ", QString::SkipEmptyParts);
474    if (al.isEmpty())
475        return 0;
476
477    bl = B.split(" ", QString::SkipEmptyParts);
478    if (bl.isEmpty())
479        return 0;
480
481    // score words symmetrically
482    int score = (score_words(al, bl) + score_words(bl, al)) / 2;
483
484    return min(900, score);
485}
486
487int DBEvent::GetMatch(const vector<DBEvent> &programs, int &bestmatch) const
488{
489    bestmatch = -1;
490    int match_val = INT_MIN;
491    int overlap = 0;
492    int duration = starttime.secsTo(endtime);
493
494    for (uint i = 0; i < programs.size(); i++)
495    {
496        int mv = 0;
497        int duration_loop = programs[i].starttime.secsTo(programs[i].endtime);
498
499        mv -= qAbs(starttime.secsTo(programs[i].starttime));
500        mv -= qAbs(endtime.secsTo(programs[i].endtime));
501        mv -= qAbs(duration - duration_loop);
502        mv += score_match(title, programs[i].title) * 10;
503        mv += score_match(subtitle, programs[i].subtitle);
504        mv += score_match(description, programs[i].description);
505
506        /* determine overlap of both programs
507         * we don't know which one starts first */
508        if (starttime < programs[i].starttime)
509            overlap = programs[i].starttime.secsTo(endtime);
510        else if (starttime > programs[i].starttime)
511            overlap = starttime.secsTo(programs[i].endtime);
512        else
513        {
514            if (endtime <= programs[i].endtime)
515                overlap = starttime.secsTo(endtime);
516            else
517                overlap = starttime.secsTo(programs[i].endtime);
518        }
519
520        /* scale the score depending on the overlap length
521         * full score is preserved if the overlap is at least 1/2 of the length
522         * of the shorter program */
523        if (overlap > 0)
524        {
525            /* crappy providers apparently have events without duration
526             * ensure that the minimal duration is 2 second to avoid
527             * multiplying and more importantly dividing by zero */
528            int min_dur = max(2, min(duration, duration_loop));
529            overlap = min(overlap, min_dur/2);
530            mv *= overlap * 2;
531            mv /= min_dur;
532        }
533        else
534        {
535            LOG(VB_GENERAL, LOG_ERR,
536                QString("Unexpected result: shows don't "
537                        "overlap\n\t%1: %2 - %3\n\t%4: %5 - %6")
538                    .arg(title.left(35))
539                    .arg(starttime.toString(Qt::ISODate))
540                    .arg(endtime.toString(Qt::ISODate))
541                    .arg(programs[i].title.left(35), 35)
542                    .arg(programs[i].starttime.toString(Qt::ISODate))
543                    .arg(programs[i].endtime.toString(Qt::ISODate))
544                );
545        }
546
547        if (mv > match_val)
548        {
549            LOG(VB_EIT, LOG_DEBUG,
550                QString("GM : '%1' new best match '%2' with score %3")
551                    .arg(title.left(35))
552                    .arg(programs[i].title.left(35)).arg(mv));
553            bestmatch = i;
554            match_val = mv;
555        }
556    }
557
558    return match_val;
559}
560
561uint DBEvent::UpdateDB(
562    MSqlQuery &q, uint chanid, const vector<DBEvent> &p, int match) const
563{
564    // Adjust/delete overlaps;
565    bool ok = true;
566    for (uint i = 0; i < p.size(); i++)
567    {
568        if (i != (uint)match)
569            ok &= MoveOutOfTheWayDB(q, chanid, p[i]);
570    }
571
572    // If we failed to move programs out of the way, don't insert new ones..
573    if (!ok)
574    {
575        LOG(VB_EIT, LOG_DEBUG,
576            QString("EIT: cannot insert '%1' MoveOutOfTheWayDB failed")
577                    .arg(title.left(35)));
578        return 0;
579    }
580
581    // No match, insert current item
582    if ((match < 0) || ((uint)match >= p.size()))
583    {
584        LOG(VB_EIT, LOG_DEBUG,
585            QString("EIT: insert '%1'")
586                    .arg(title.left(35)));
587        return InsertDB(q, chanid);
588    }
589
590    // Changing a starttime of a program that is being recorded can
591    // start another recording of the same program.
592    // Therefore we skip updates that change a starttime in the past
593    // unless the endtime is later.
594    if (starttime != p[match].starttime)
595    {
596        QDateTime now = QDateTime::currentDateTimeUtc();
597        if (starttime < now && endtime <= p[match].endtime)
598        {
599            LOG(VB_EIT, LOG_DEBUG,
600                QString("EIT:  skip '%1' starttime is in the past")
601                        .arg(title.left(35)));
602            return 0;
603        }
604    }
605
606    // Update matched item with current data
607    LOG(VB_EIT, LOG_DEBUG,
608         QString("EIT: update '%1' with '%2'")
609                 .arg(p[match].title.left(35))
610                 .arg(title.left(35)));
611    return UpdateDB(q, chanid, p[match]);
612}
613
614// Update matched item with current data.
615//
616uint DBEvent::UpdateDB(
617    MSqlQuery &query, uint chanid, const DBEvent &match)  const
618{
619    QString  ltitle     = title;
620    QString  lsubtitle  = subtitle;
621    QString  ldesc      = description;
622    QString  lcategory  = category;
623    uint16_t lairdate   = airdate;
624    QString  lprogramId = programId;
625    QString  lseriesId  = seriesId;
626    QString  linetref   = inetref;
627    QDate loriginalairdate = originalairdate;
628
629// #define UPDATEDB_DEBUG
630// #define UPDATEDB_ORIGINAL
631#define UPDATEDB_FIXED
632
633#ifdef UPDATEDB_DEBUG
634//
635// Give a debug output for the case where we would NOT have done the update
636// because the old value was seemed better than the new one.
637// Such as the old title, subtitle and description longer than the new one
638// or the old data item already valid.
639//
640    LOG(VB_EIT, LOG_DEBUG,
641        QString("EIT: TEST update title old '%1' new '%2'")
642            .arg(match.title.left(35))
643            .arg(ltitle.left(35)));
644
645    if (match.title.length() > ltitle.length())
646    {
647        LOG(VB_EIT, LOG_DEBUG,
648            QString("EIT: SKIP update title old '%1' new '%2'")
649                .arg(match.title.left(35))
650                .arg(ltitle.left(35)));
651//      ltitle = match.title;
652    }
653
654    if (match.subtitle.length() > lsubtitle.length())
655    {
656        LOG(VB_EIT, LOG_DEBUG,
657            QString("EIT: SKIP update subtitle old '%1' new '%2'")
658                .arg(match.subtitle.left(35))
659                .arg(lsubtitle.left(35)));
660//      lsubtitle = match.subtitle;
661    }
662
663    if (match.description.length() > ldesc.length())
664    {
665        LOG(VB_EIT, LOG_DEBUG,
666            QString("EIT: SKIP update description old '%1' new '%2'")
667                .arg(match.description.left(35))
668                .arg(ldesc.left(35)));
669//      ldesc = match.description;
670    }
671
672    if (lcategory.isEmpty() && !match.category.isEmpty())
673    {
674        LOG(VB_EIT, LOG_DEBUG,
675            QString("EIT: SKIP update category old '%1' new '%2'")
676                .arg(match.category)
677                .arg(lcategory));
678//      lcategory = match.category;
679    }
680
681    if (!lairdate && match.airdate)     // KdW condition changed!!
682    {
683        LOG(VB_EIT, LOG_DEBUG,
684            QString("EIT: SKIP update airdate old '%1' new '%2'")
685                .arg(match.airdate)
686                .arg(lairdate));
687//      lairdate = match.airdate;
688    }
689
690    if (!loriginalairdate.isValid() && match.originalairdate.isValid())
691    {
692        LOG(VB_EIT, LOG_DEBUG,
693            QString("EIT: SKIP update originalairdate isValid old '%1' new '%2'")
694                .arg(match.originalairdate.isValid())
695                .arg(loriginalairdate.isValid()));
696//      loriginalairdate = match.originalairdate;
697    }
698
699    if (lprogramId.isEmpty() && !match.programId.isEmpty())
700    {
701        LOG(VB_EIT, LOG_DEBUG,
702            QString("EIT: SKIP update programId old '%1' new '%2'")
703                .arg(match.programId)
704                .arg(lprogramId));
705//      lprogramId = match.programId;
706    }
707
708    if (lseriesId.isEmpty() && !match.seriesId.isEmpty())
709    {
710        LOG(VB_EIT, LOG_DEBUG,
711            QString("EIT: SKIP update seriesId old '%1' new '%2'")
712                .arg(match.seriesId)
713                .arg(lseriesId));
714//      lseriesId = match.seriesId;
715    }
716
717    if (linetref.isEmpty() && !match.inetref.isEmpty())
718    {
719        LOG(VB_EIT, LOG_DEBUG,
720            QString("EIT: SKIP update inetref old '%1' new '%2'")
721                .arg(match.inetref)
722                .arg(linetref));
723//      linetref= match.inetref;
724    }
725
726    ProgramInfo::CategoryType tmp = categoryType;
727    if (!categoryType && match.categoryType)
728    {
729        LOG(VB_EIT, LOG_DEBUG,
730            QString("EIT: SKIP update categoryType old '%1' new '%2'")
731                .arg(match.categoryType)
732                .arg(tmp));
733//      tmp = match.categoryType;
734    }
735    QString lcattype = myth_category_type_to_string(tmp);
736
737    unsigned char lsubtype = subtitleType;
738    unsigned char laudio   = audioProps;
739    unsigned char lvideo   = videoProps;
740
741    uint lseason = match.season;
742    uint lepisode = match.episode;
743    uint lepisodeTotal = match.totalepisodes;
744
745    if (season || episode || totalepisodes)
746    {
747        lseason = season;
748        lepisode = episode;
749        lepisodeTotal = totalepisodes;
750    }
751    else
752    {
753        if (match.season)
754        {
755            LOG(VB_EIT, LOG_DEBUG,
756                    QString("EIT: SKIP update season old '%1' new '%2'")
757                        .arg(match.season)
758                        .arg(season));
759        }
760        if (match.episode)
761        {
762            LOG(VB_EIT, LOG_DEBUG,
763                    QString("EIT: SKIP update episode old '%1' new '%2'")
764                        .arg(match.episode)
765                        .arg(episode));
766        }
767        if (match.totalepisodes)
768        {
769            LOG(VB_EIT, LOG_DEBUG,
770                    QString("EIT: SKIP update totalepisodes old '%1' new '%2'")
771                        .arg(match.totalepisodes)
772                        .arg(totalepisodes));
773        }
774    }
775
776    uint lpartnumber = match.partnumber;
777    uint lparttotal = match.parttotal;
778
779    if (partnumber || parttotal)
780    {
781        lpartnumber = partnumber;
782        lparttotal  = parttotal;
783    }
784    else
785    {
786        if (match.partnumber)
787        {
788            LOG(VB_EIT, LOG_DEBUG,
789                    QString("EIT: SKIP update partnumber old '%1' new '%2'")
790                        .arg(match.partnumber)
791                        .arg(partnumber));
792        }
793        if (match.parttotal)
794        {
795            LOG(VB_EIT, LOG_DEBUG,
796                    QString("EIT: SKIP update parttotal old '%1' new '%2'")
797                        .arg(match.parttotal)
798                        .arg(parttotal));
799        }
800    }
801
802    bool lpreviouslyshown = previouslyshown;
803
804    uint32_t llistingsource = listingsource;
805
806    QString lsyndicatedepisodenumber = syndicatedepisodenumber;
807    if (lsyndicatedepisodenumber.isEmpty() &&
808        !match.syndicatedepisodenumber.isEmpty())
809    {
810        LOG(VB_EIT, LOG_DEBUG,
811            QString("EIT: SKIP update syndicatedepisodenumber old '%1' new '%2'")
812                .arg(match.syndicatedepisodenumber)
813                .arg(syndicatedepisodenumber));
814//      lsyndicatedepisodenumber = match.syndicatedepisodenumber;
815    }
816
817#endif  // UPDATEDB_DEBUG
818
819#ifdef UPDATEDB_ORIGINAL
820    if (match.title.length() >= ltitle.length())
821        ltitle = match.title;
822
823    if (match.subtitle.length() >= lsubtitle.length())
824        lsubtitle = match.subtitle;
825
826    if (match.description.length() >= ldesc.length())
827        ldesc = match.description;
828
829    if (lcategory.isEmpty() && !match.category.isEmpty())
830        lcategory = match.category;
831
832    if (!lairdate && !match.airdate)
833        lairdate = match.airdate;
834
835    if (!loriginalairdate.isValid() && match.originalairdate.isValid())
836        loriginalairdate = match.originalairdate;
837
838    if (lprogramId.isEmpty() && !match.programId.isEmpty())
839        lprogramId = match.programId;
840
841    if (lseriesId.isEmpty() && !match.seriesId.isEmpty())
842        lseriesId = match.seriesId;
843
844    if (linetref.isEmpty() && !match.inetref.isEmpty())
845        linetref= match.inetref;
846
847    ProgramInfo::CategoryType tmp = categoryType;
848    if (!categoryType && match.categoryType)
849        tmp = match.categoryType;
850
851    QString lcattype = myth_category_type_to_string(tmp);
852
853    unsigned char lsubtype = subtitleType | match.subtitleType;
854    unsigned char laudio   = audioProps   | match.audioProps;
855    unsigned char lvideo   = videoProps   | match.videoProps;
856
857    uint lseason = match.season;
858    uint lepisode = match.episode;
859    uint lepisodeTotal = match.totalepisodes;
860
861    if (season || episode || totalepisodes)
862    {
863        lseason = season;
864        lepisode = episode;
865        lepisodeTotal = totalepisodes;
866    }
867
868    uint lpartnumber = match.partnumber;
869    uint lparttotal = match.parttotal;
870
871    if (partnumber || parttotal)
872    {
873        lpartnumber = partnumber;
874        lparttotal  = parttotal;
875    }
876
877    bool lpreviouslyshown = previouslyshown | match.previouslyshown;
878
879    uint32_t llistingsource = listingsource | match.listingsource;
880
881    QString lsyndicatedepisodenumber = syndicatedepisodenumber;
882    if (lsyndicatedepisodenumber.isEmpty() &&
883        !match.syndicatedepisodenumber.isEmpty())
884        lsyndicatedepisodenumber = match.syndicatedepisodenumber;
885
886#endif  // UPDATEDB_ORIGINAL
887
888#ifdef UPDATEDB_FIXED
889
890    ProgramInfo::CategoryType tmp = categoryType;
891    QString lcattype = myth_category_type_to_string(tmp);
892
893    unsigned char lsubtype = subtitleType;
894    unsigned char laudio   = audioProps;
895    unsigned char lvideo   = videoProps;
896
897    uint lseason = season;
898    uint lepisode = episode;
899    uint lepisodeTotal = totalepisodes;
900
901    uint lpartnumber = partnumber;
902    uint lparttotal  = parttotal;
903
904    bool lpreviouslyshown = previouslyshown;
905
906    uint32_t llistingsource = listingsource;
907
908    QString lsyndicatedepisodenumber = syndicatedepisodenumber;
909
910#endif  // UPDATEDB_FIXED
911
912    query.prepare(
913        "UPDATE program "
914        "SET title          = :TITLE,     subtitle      = :SUBTITLE, "
915        "    description    = :DESC, "
916        "    category       = :CATEGORY,  category_type = :CATTYPE, "
917        "    starttime      = :STARTTIME, endtime       = :ENDTIME, "
918        "    closecaptioned = :CC,        subtitled     = :HASSUBTITLES, "
919        "    stereo         = :STEREO,    hdtv          = :HDTV, "
920        "    subtitletypes  = :SUBTYPE, "
921        "    audioprop      = :AUDIOPROP, videoprop     = :VIDEOPROP, "
922        "    season         = :SEASON,  "
923        "    episode        = :EPISODE,   totalepisodes = :TOTALEPS, "
924        "    partnumber     = :PARTNO,    parttotal     = :PARTTOTAL, "
925        "    syndicatedepisodenumber = :SYNDICATENO, "
926        "    airdate        = :AIRDATE,   originalairdate=:ORIGAIRDATE, "
927        "    listingsource  = :LSOURCE, "
928        "    seriesid       = :SERIESID,  programid     = :PROGRAMID, "
929        "    previouslyshown = :PREVSHOWN, inetref      = :INETREF "
930        "WHERE chanid    = :CHANID AND "
931        "      starttime = :OLDSTART ");
932
933    query.bindValue(":CHANID",      chanid);
934    query.bindValue(":OLDSTART",    match.starttime);
935    query.bindValue(":TITLE",       denullify(ltitle));
936    query.bindValue(":SUBTITLE",    denullify(lsubtitle));
937    query.bindValue(":DESC",        denullify(ldesc));
938    query.bindValue(":CATEGORY",    denullify(lcategory));
939    query.bindValue(":CATTYPE",     lcattype);
940    query.bindValue(":STARTTIME",   starttime);
941    query.bindValue(":ENDTIME",     endtime);
942    query.bindValue(":CC",          (lsubtype & SUB_HARDHEAR) ? true : false);
943    query.bindValue(":HASSUBTITLES",(lsubtype & SUB_NORMAL)   ? true : false);
944    query.bindValue(":STEREO",      (laudio   & AUD_STEREO)   ? true : false);
945    query.bindValue(":HDTV",        (lvideo   & VID_HDTV)     ? true : false);
946    query.bindValue(":SUBTYPE",     lsubtype);
947    query.bindValue(":AUDIOPROP",   laudio);
948    query.bindValue(":VIDEOPROP",   lvideo);
949    query.bindValue(":SEASON",      lseason);
950    query.bindValue(":EPISODE",     lepisode);
951    query.bindValue(":TOTALEPS",    lepisodeTotal);
952    query.bindValue(":PARTNO",      lpartnumber);
953    query.bindValue(":PARTTOTAL",   lparttotal);
954    query.bindValue(":SYNDICATENO", denullify(lsyndicatedepisodenumber));
955    query.bindValue(":AIRDATE",     lairdate ? QString::number(lairdate) : "0000");
956    query.bindValue(":ORIGAIRDATE", loriginalairdate);
957    query.bindValue(":LSOURCE",     llistingsource);
958    query.bindValue(":SERIESID",    denullify(lseriesId));
959    query.bindValue(":PROGRAMID",   denullify(lprogramId));
960    query.bindValue(":PREVSHOWN",   lpreviouslyshown);
961    query.bindValue(":INETREF",     linetref);
962
963    if (!query.exec())
964    {
965        MythDB::DBError("InsertDB", query);
966        return 0;
967    }
968
969    if (credits)
970    {
971        for (uint i = 0; i < credits->size(); i++)
972            (*credits)[i].InsertDB(query, chanid, starttime);
973    }
974
975    QList<EventRating>::const_iterator j = ratings.begin();
976    for (; j != ratings.end(); ++j)
977    {
978        query.prepare(
979            "INSERT IGNORE INTO programrating "
980            "       ( chanid, starttime, system, rating) "
981            "VALUES (:CHANID, :START,    :SYS,  :RATING)");
982        query.bindValue(":CHANID", chanid);
983        query.bindValue(":START",  starttime);
984        query.bindValue(":SYS",    (*j).system);
985        query.bindValue(":RATING", (*j).rating);
986
987        if (!query.exec())
988            MythDB::DBError("programrating insert", query);
989    }
990
991    add_genres(query, genres, chanid, starttime);
992
993    return 1;
994}
995
996static bool delete_program(MSqlQuery &query, uint chanid, const QDateTime &st)
997{
998    query.prepare(
999        "DELETE from program "
1000        "WHERE chanid    = :CHANID AND "
1001        "      starttime = :STARTTIME");
1002
1003    query.bindValue(":CHANID",    chanid);
1004    query.bindValue(":STARTTIME", st);
1005
1006    if (!query.exec())
1007    {
1008        MythDB::DBError("delete_program", query);
1009        return false;
1010    }
1011
1012    query.prepare(
1013        "DELETE from credits "
1014        "WHERE chanid    = :CHANID AND "
1015        "      starttime = :STARTTIME");
1016
1017    query.bindValue(":CHANID",    chanid);
1018    query.bindValue(":STARTTIME", st);
1019
1020    if (!query.exec())
1021    {
1022        MythDB::DBError("delete_credits", query);
1023        return false;
1024    }
1025
1026    query.prepare(
1027        "DELETE from programrating "
1028        "WHERE chanid    = :CHANID AND "
1029        "      starttime = :STARTTIME");
1030
1031    query.bindValue(":CHANID",    chanid);
1032    query.bindValue(":STARTTIME", st);
1033
1034    if (!query.exec())
1035    {
1036        MythDB::DBError("delete_rating", query);
1037        return false;
1038    }
1039
1040    query.prepare(
1041        "DELETE from programgenres "
1042        "WHERE chanid    = :CHANID AND "
1043        "      starttime = :STARTTIME");
1044
1045    query.bindValue(":CHANID",    chanid);
1046    query.bindValue(":STARTTIME", st);
1047
1048    if (!query.exec())
1049    {
1050        MythDB::DBError("delete_genres", query);
1051        return false;
1052    }
1053
1054    return true;
1055}
1056
1057static bool program_exists(MSqlQuery &query, uint chanid, const QDateTime &st)
1058{
1059    query.prepare(
1060        "SELECT title FROM program "
1061        "WHERE chanid    = :CHANID AND "
1062        "      starttime = :OLDSTART");
1063    query.bindValue(":CHANID",   chanid);
1064    query.bindValue(":OLDSTART", st);
1065    if (!query.exec())
1066    {
1067        MythDB::DBError("program_exists", query);
1068    }
1069    if (query.next())
1070    {
1071        return true;
1072    }
1073    return false;
1074}
1075
1076static bool change_program(MSqlQuery &query, uint chanid, const QDateTime &st,
1077                           const QDateTime &new_st, const QDateTime &new_end)
1078{
1079    query.prepare(
1080        "UPDATE program "
1081        "SET starttime = :NEWSTART, "
1082        "    endtime   = :NEWEND "
1083        "WHERE chanid    = :CHANID AND "
1084        "      starttime = :OLDSTART");
1085
1086    query.bindValue(":CHANID",   chanid);
1087    query.bindValue(":OLDSTART", st);
1088    query.bindValue(":NEWSTART", new_st);
1089    query.bindValue(":NEWEND",   new_end);
1090
1091    if (!query.exec())
1092    {
1093        MythDB::DBError("change_program", query);
1094        return false;
1095    }
1096
1097    query.prepare(
1098        "UPDATE credits "
1099        "SET starttime = :NEWSTART "
1100        "WHERE chanid    = :CHANID AND "
1101        "      starttime = :OLDSTART");
1102
1103    query.bindValue(":CHANID",   chanid);
1104    query.bindValue(":OLDSTART", st);
1105    query.bindValue(":NEWSTART", new_st);
1106
1107    if (!query.exec())
1108    {
1109        MythDB::DBError("change_credits", query);
1110        return false;
1111    }
1112
1113    query.prepare(
1114        "UPDATE programrating "
1115        "SET starttime = :NEWSTART "
1116        "WHERE chanid    = :CHANID AND "
1117        "      starttime = :OLDSTART");
1118
1119    query.bindValue(":CHANID",   chanid);
1120    query.bindValue(":OLDSTART", st);
1121    query.bindValue(":NEWSTART", new_st);
1122
1123    if (!query.exec())
1124    {
1125        MythDB::DBError("change_rating", query);
1126        return false;
1127    }
1128
1129    query.prepare(
1130        "UPDATE programgenres "
1131        "SET starttime = :NEWSTART "
1132        "WHERE chanid    = :CHANID AND "
1133        "      starttime = :OLDSTART");
1134
1135    query.bindValue(":CHANID",   chanid);
1136    query.bindValue(":OLDSTART", st);
1137    query.bindValue(":NEWSTART", new_st);
1138
1139    if (!query.exec())
1140    {
1141        MythDB::DBError("change_genres", query);
1142        return false;
1143    }
1144
1145    return true;
1146}
1147
1148// Move the program "prog" (3rd parameter) out of the way
1149// because it overlaps with our new program.
1150bool DBEvent::MoveOutOfTheWayDB(
1151    MSqlQuery &query, uint chanid, const DBEvent &prog) const
1152{
1153    if (prog.starttime >= starttime && prog.endtime <= endtime)
1154    {
1155        // Old program completely inside our new program.
1156        // Delete the old program completely.
1157        LOG(VB_EIT, LOG_DEBUG,
1158            QString("EIT: delete '%1' %2 - %3")
1159                    .arg(prog.title.left(35))
1160                    .arg(prog.starttime.toString(Qt::ISODate))
1161                    .arg(prog.endtime.toString(Qt::ISODate)));
1162        return delete_program(query, chanid, prog.starttime);
1163    }
1164    else if (prog.starttime < starttime && prog.endtime > starttime)
1165    {
1166        // Old program starts before, but ends during or after our new program.
1167        // Adjust the end time of the old program to the start time
1168        // of our new program.
1169        // This will leave a hole after our new program when the end time of
1170        // the old program was after the end time of the new program!!
1171        LOG(VB_EIT, LOG_DEBUG,
1172            QString("EIT: change '%1' endtime to %2")
1173                    .arg(prog.title.left(35))
1174                    .arg(starttime.toString(Qt::ISODate)));
1175        return change_program(query, chanid, prog.starttime,
1176                              prog.starttime, // Keep the start time
1177                              starttime);     // New end time is our start time
1178    }
1179    else if (prog.starttime < endtime && prog.endtime > endtime)
1180    {
1181        // Old program starts during, but ends after our new program.
1182        // Adjust the starttime of the old program to the end time
1183        // of our new program.
1184        // If there is already a program starting just when our
1185        // new program ends we cannot move the old program
1186        // so then we have to delete the old program.
1187        if (program_exists(query, chanid, endtime))
1188        {
1189            LOG(VB_EIT, LOG_DEBUG,
1190                QString("EIT: delete '%1' %2 - %3")
1191                        .arg(prog.title.left(35))
1192                        .arg(prog.starttime.toString(Qt::ISODate))
1193                        .arg(prog.endtime.toString(Qt::ISODate)));
1194            return delete_program(query, chanid, prog.starttime);
1195        }
1196        LOG(VB_EIT, LOG_DEBUG,
1197            QString("EIT: change '%1' starttime to %2")
1198                    .arg(prog.title.left(35))
1199                    .arg(endtime.toString(Qt::ISODate)));
1200
1201        return change_program(query, chanid, prog.starttime,
1202                              endtime,        // New start time is our endtime
1203                              prog.endtime);  // Keep the end time
1204    }
1205    // must be non-conflicting...
1206    return true;
1207}
1208
1209uint DBEvent::InsertDB(MSqlQuery &query, uint chanid) const
1210{
1211    query.prepare(
1212        "REPLACE INTO program ("
1213        "  chanid,         title,          subtitle,        description, "
1214        "  category,       category_type, "
1215        "  starttime,      endtime, "
1216        "  closecaptioned, stereo,         hdtv,            subtitled, "
1217        "  subtitletypes,  audioprop,      videoprop, "
1218        "  stars,          partnumber,     parttotal, "
1219        "  syndicatedepisodenumber, "
1220        "  airdate,        originalairdate,listingsource, "
1221        "  seriesid,       programid,      previouslyshown, "
1222        "  season,         episode,        totalepisodes, "
1223        "  inetref ) "
1224        "VALUES ("
1225        " :CHANID,        :TITLE,         :SUBTITLE,       :DESCRIPTION, "
1226        " :CATEGORY,      :CATTYPE, "
1227        " :STARTTIME,     :ENDTIME, "
1228        " :CC,            :STEREO,        :HDTV,           :HASSUBTITLES, "
1229        " :SUBTYPES,      :AUDIOPROP,     :VIDEOPROP, "
1230        " :STARS,         :PARTNUMBER,    :PARTTOTAL, "
1231        " :SYNDICATENO, "
1232        " :AIRDATE,       :ORIGAIRDATE,   :LSOURCE, "
1233        " :SERIESID,      :PROGRAMID,     :PREVSHOWN, "
1234        " :SEASON,        :EPISODE,       :TOTALEPISODES, "
1235        " :INETREF ) ");
1236
1237    QString cattype = myth_category_type_to_string(categoryType);
1238    QString empty("");
1239    query.bindValue(":CHANID",      chanid);
1240    query.bindValue(":TITLE",       denullify(title));
1241    query.bindValue(":SUBTITLE",    denullify(subtitle));
1242    query.bindValue(":DESCRIPTION", denullify(description));
1243    query.bindValue(":CATEGORY",    denullify(category));
1244    query.bindValue(":CATTYPE",     cattype);
1245    query.bindValue(":STARTTIME",   starttime);
1246    query.bindValue(":ENDTIME",     endtime);
1247    query.bindValue(":CC",          (subtitleType & SUB_HARDHEAR) ? true : false);
1248    query.bindValue(":STEREO",      (audioProps   & AUD_STEREO)   ? true : false);
1249    query.bindValue(":HDTV",        (videoProps   & VID_HDTV)     ? true : false);
1250    query.bindValue(":HASSUBTITLES",(subtitleType & SUB_NORMAL)   ? true : false);
1251    query.bindValue(":SUBTYPES",    subtitleType);
1252    query.bindValue(":AUDIOPROP",   audioProps);
1253    query.bindValue(":VIDEOPROP",   videoProps);
1254    query.bindValue(":STARS",       stars);
1255    query.bindValue(":PARTNUMBER",  partnumber);
1256    query.bindValue(":PARTTOTAL",   parttotal);
1257    query.bindValue(":SYNDICATENO", denullify(syndicatedepisodenumber));
1258    query.bindValue(":AIRDATE",     airdate ? QString::number(airdate) : "0000");
1259    query.bindValue(":ORIGAIRDATE", originalairdate);
1260    query.bindValue(":LSOURCE",     listingsource);
1261    query.bindValue(":SERIESID",    denullify(seriesId));
1262    query.bindValue(":PROGRAMID",   denullify(programId));
1263    query.bindValue(":PREVSHOWN",   previouslyshown);
1264    query.bindValue(":SEASON",      season);
1265    query.bindValue(":EPISODE",     episode);
1266    query.bindValue(":TOTALEPISODES", totalepisodes);
1267    query.bindValue(":INETREF",     inetref);
1268
1269    if (!query.exec())
1270    {
1271        MythDB::DBError("InsertDB", query);
1272        return 0;
1273    }
1274
1275    QList<EventRating>::const_iterator j = ratings.begin();
1276    for (; j != ratings.end(); ++j)
1277    {
1278        query.prepare(
1279            "INSERT IGNORE INTO programrating "
1280            "       ( chanid, starttime, system, rating) "
1281            "VALUES (:CHANID, :START,    :SYS,  :RATING)");
1282        query.bindValue(":CHANID", chanid);
1283        query.bindValue(":START",  starttime);
1284        query.bindValue(":SYS",    (*j).system);
1285        query.bindValue(":RATING", (*j).rating);
1286
1287        if (!query.exec())
1288            MythDB::DBError("programrating insert", query);
1289    }
1290
1291    if (credits)
1292    {
1293        for (uint i = 0; i < credits->size(); i++)
1294            (*credits)[i].InsertDB(query, chanid, starttime);
1295    }
1296
1297    add_genres(query, genres, chanid, starttime);
1298
1299    return 1;
1300}
1301
1302ProgInfo::ProgInfo(const ProgInfo &other) :
1303    DBEvent(other.listingsource)
1304{
1305    *this = other;
1306}
1307
1308ProgInfo &ProgInfo::operator=(const ProgInfo &other)
1309{
1310    if (this == &other)
1311        return *this;
1312
1313    DBEvent::operator=(other);
1314
1315    channel         = other.channel;
1316    startts         = other.startts;
1317    endts           = other.endts;
1318    title_pronounce = other.title_pronounce;
1319    showtype        = other.showtype;
1320    colorcode       = other.colorcode;
1321    clumpidx        = other.clumpidx;
1322    clumpmax        = other.clumpmax;
1323
1324    channel.squeeze();
1325    startts.squeeze();
1326    endts.squeeze();
1327    title_pronounce.squeeze();
1328    showtype.squeeze();
1329    colorcode.squeeze();
1330    clumpidx.squeeze();
1331    clumpmax.squeeze();
1332
1333    return *this;
1334}
1335
1336void ProgInfo::Squeeze(void)
1337{
1338    DBEvent::Squeeze();
1339    channel.squeeze();
1340    startts.squeeze();
1341    endts.squeeze();
1342    title_pronounce.squeeze();
1343    showtype.squeeze();
1344    colorcode.squeeze();
1345    clumpidx.squeeze();
1346    clumpmax.squeeze();
1347}
1348
1349uint ProgInfo::InsertDB(MSqlQuery &query, uint chanid) const
1350{
1351    LOG(VB_XMLTV, LOG_INFO,
1352        QString("Inserting new program    : %1 - %2 %3 %4")
1353            .arg(starttime.toString(Qt::ISODate))
1354            .arg(endtime.toString(Qt::ISODate))
1355            .arg(channel)
1356            .arg(title));
1357
1358    query.prepare(
1359        "REPLACE INTO program ("
1360        "  chanid,         title,          subtitle,        description, "
1361        "  category,       category_type,  "
1362        "  starttime,      endtime, "
1363        "  closecaptioned, stereo,         hdtv,            subtitled, "
1364        "  subtitletypes,  audioprop,      videoprop, "
1365        "  partnumber,     parttotal, "
1366        "  syndicatedepisodenumber, "
1367        "  airdate,        originalairdate,listingsource, "
1368        "  seriesid,       programid,      previouslyshown, "
1369        "  stars,          showtype,       title_pronounce, colorcode, "
1370        "  season,         episode,        totalepisodes, "
1371        "  inetref ) "
1372
1373        "VALUES("
1374        " :CHANID,        :TITLE,         :SUBTITLE,       :DESCRIPTION, "
1375        " :CATEGORY,      :CATTYPE,       "
1376        " :STARTTIME,     :ENDTIME, "
1377        " :CC,            :STEREO,        :HDTV,           :HASSUBTITLES, "
1378        " :SUBTYPES,      :AUDIOPROP,     :VIDEOPROP, "
1379        " :PARTNUMBER,    :PARTTOTAL, "
1380        " :SYNDICATENO, "
1381        " :AIRDATE,       :ORIGAIRDATE,   :LSOURCE, "
1382        " :SERIESID,      :PROGRAMID,     :PREVSHOWN, "
1383        " :STARS,         :SHOWTYPE,      :TITLEPRON,      :COLORCODE, "
1384        " :SEASON,        :EPISODE,       :TOTALEPISODES, "
1385        " :INETREF )");
1386
1387    QString cattype = myth_category_type_to_string(categoryType);
1388
1389    query.bindValue(":CHANID",      chanid);
1390    query.bindValue(":TITLE",       denullify(title));
1391    query.bindValue(":SUBTITLE",    denullify(subtitle));
1392    query.bindValue(":DESCRIPTION", denullify(description));
1393    query.bindValue(":CATEGORY",    denullify(category));
1394    query.bindValue(":CATTYPE",     cattype);
1395    query.bindValue(":STARTTIME",   starttime);
1396    query.bindValue(":ENDTIME",     denullify(endtime));
1397    query.bindValue(":CC",
1398                    (subtitleType & SUB_HARDHEAR) ? true : false);
1399    query.bindValue(":STEREO",
1400                    (audioProps   & AUD_STEREO)   ? true : false);
1401    query.bindValue(":HDTV",
1402                    (videoProps   & VID_HDTV)     ? true : false);
1403    query.bindValue(":HASSUBTITLES",
1404                    (subtitleType & SUB_NORMAL)   ? true : false);
1405    query.bindValue(":SUBTYPES",    subtitleType);
1406    query.bindValue(":AUDIOPROP",   audioProps);
1407    query.bindValue(":VIDEOPROP",   videoProps);
1408    query.bindValue(":PARTNUMBER",  partnumber);
1409    query.bindValue(":PARTTOTAL",   parttotal);
1410    query.bindValue(":SYNDICATENO", denullify(syndicatedepisodenumber));
1411    query.bindValue(":AIRDATE",     airdate ? QString::number(airdate):"0000");
1412    query.bindValue(":ORIGAIRDATE", originalairdate);
1413    query.bindValue(":LSOURCE",     listingsource);
1414    query.bindValue(":SERIESID",    denullify(seriesId));
1415    query.bindValue(":PROGRAMID",   denullify(programId));
1416    query.bindValue(":PREVSHOWN",   previouslyshown);
1417    query.bindValue(":STARS",       stars);
1418    query.bindValue(":SHOWTYPE",    showtype);
1419    query.bindValue(":TITLEPRON",   title_pronounce);
1420    query.bindValue(":COLORCODE",   colorcode);
1421    query.bindValue(":SEASON",      season);
1422    query.bindValue(":EPISODE",     episode);
1423    query.bindValue(":TOTALEPISODES", totalepisodes);
1424    query.bindValue(":INETREF",     inetref);
1425
1426    if (!query.exec())
1427    {
1428        MythDB::DBError("program insert", query);
1429        return 0;
1430    }
1431
1432    QList<EventRating>::const_iterator j = ratings.begin();
1433    for (; j != ratings.end(); ++j)
1434    {
1435        query.prepare(
1436            "INSERT IGNORE INTO programrating "
1437            "       ( chanid, starttime, system, rating) "
1438            "VALUES (:CHANID, :START,    :SYS,  :RATING)");
1439        query.bindValue(":CHANID", chanid);
1440        query.bindValue(":START",  starttime);
1441        query.bindValue(":SYS",    (*j).system);
1442        query.bindValue(":RATING", (*j).rating);
1443
1444        if (!query.exec())
1445            MythDB::DBError("programrating insert", query);
1446    }
1447
1448    if (credits)
1449    {
1450        for (uint i = 0; i < credits->size(); ++i)
1451            (*credits)[i].InsertDB(query, chanid, starttime);
1452    }
1453
1454    add_genres(query, genres, chanid, starttime);
1455
1456    return 1;
1457}
1458
1459bool ProgramData::ClearDataByChannel(
1460    uint chanid, const QDateTime &from, const QDateTime &to,
1461    bool use_channel_time_offset)
1462{
1463    int secs = 0;
1464    if (use_channel_time_offset)
1465        secs = ChannelUtil::GetTimeOffset(chanid) * 60;
1466
1467    QDateTime newFrom = from.addSecs(secs);
1468    QDateTime newTo   = to.addSecs(secs);
1469
1470    MSqlQuery query(MSqlQuery::InitCon());
1471    query.prepare("DELETE FROM program "
1472                  "WHERE starttime >= :FROM AND starttime < :TO "
1473                  "AND chanid = :CHANID ;");
1474    query.bindValue(":FROM", newFrom);
1475    query.bindValue(":TO", newTo);
1476    query.bindValue(":CHANID", chanid);
1477    bool ok = query.exec();
1478
1479    query.prepare("DELETE FROM programrating "
1480                  "WHERE starttime >= :FROM AND starttime < :TO "
1481                  "AND chanid = :CHANID ;");
1482    query.bindValue(":FROM", newFrom);
1483    query.bindValue(":TO", newTo);
1484    query.bindValue(":CHANID", chanid);
1485    ok &= query.exec();
1486
1487    query.prepare("DELETE FROM credits "
1488                  "WHERE starttime >= :FROM AND starttime < :TO "
1489                  "AND chanid = :CHANID ;");
1490    query.bindValue(":FROM", newFrom);
1491    query.bindValue(":TO", newTo);
1492    query.bindValue(":CHANID", chanid);
1493    ok &= query.exec();
1494
1495    query.prepare("DELETE FROM programgenres "
1496                  "WHERE starttime >= :FROM AND starttime < :TO "
1497                  "AND chanid = :CHANID ;");
1498    query.bindValue(":FROM", newFrom);
1499    query.bindValue(":TO", newTo);
1500    query.bindValue(":CHANID", chanid);
1501    ok &= query.exec();
1502
1503    return ok;
1504}
1505
1506bool ProgramData::ClearDataBySource(
1507    uint sourceid, const QDateTime &from, const QDateTime &to,
1508    bool use_channel_time_offset)
1509{
1510    vector<uint> chanids = ChannelUtil::GetChanIDs(sourceid);
1511
1512    bool ok = true;
1513    for (uint i = 0; i < chanids.size(); i++)
1514        ok &= ClearDataByChannel(chanids[i], from, to, use_channel_time_offset);
1515
1516    return ok;
1517}
1518
1519static bool start_time_less_than(const DBEvent *a, const DBEvent *b)
1520{
1521    return (a->starttime < b->starttime);
1522}
1523
1524void ProgramData::FixProgramList(QList<ProgInfo*> &fixlist)
1525{
1526    qStableSort(fixlist.begin(), fixlist.end(), start_time_less_than);
1527
1528    QList<ProgInfo*>::iterator it = fixlist.begin();
1529    while (1)
1530    {
1531        QList<ProgInfo*>::iterator cur = it;
1532        ++it;
1533
1534        // fill in miss stop times
1535        if ((*cur)->endts.isEmpty() || (*cur)->startts > (*cur)->endts)
1536        {
1537            if (it != fixlist.end())
1538            {
1539                (*cur)->endts   = (*it)->startts;
1540                (*cur)->endtime = (*it)->starttime;
1541            }
1542            /* if its the last programme in the file then leave its
1543               endtime as 0000-00-00 00:00:00 so we can find it easily in
1544               fix_end_times() */
1545        }
1546
1547        if (it == fixlist.end())
1548            break;
1549
1550        // remove overlapping programs
1551        if ((*cur)->HasTimeConflict(**it))
1552        {
1553            QList<ProgInfo*>::iterator tokeep, todelete;
1554
1555            if ((*cur)->endtime <= (*cur)->starttime)
1556                tokeep = it, todelete = cur;
1557            else if ((*it)->endtime <= (*it)->starttime)
1558                tokeep = cur, todelete = it;
1559            else if (!(*cur)->subtitle.isEmpty() &&
1560                     (*it)->subtitle.isEmpty())
1561                tokeep = cur, todelete = it;
1562            else if (!(*it)->subtitle.isEmpty()  &&
1563                     (*cur)->subtitle.isEmpty())
1564                tokeep = it, todelete = cur;
1565            else if (!(*cur)->description.isEmpty() &&
1566                     (*it)->description.isEmpty())
1567                tokeep = cur, todelete = it;
1568            else
1569                tokeep = it, todelete = cur;
1570
1571
1572            LOG(VB_XMLTV, LOG_INFO,
1573                QString("Removing conflicting program: %1 - %2 %3 %4")
1574                    .arg((*todelete)->starttime.toString(Qt::ISODate))
1575                    .arg((*todelete)->endtime.toString(Qt::ISODate))
1576                    .arg((*todelete)->channel)
1577                    .arg((*todelete)->title));
1578
1579            LOG(VB_XMLTV, LOG_INFO,
1580                QString("Conflicted with            : %1 - %2 %3 %4")
1581                    .arg((*tokeep)->starttime.toString(Qt::ISODate))
1582                    .arg((*tokeep)->endtime.toString(Qt::ISODate))
1583                    .arg((*tokeep)->channel)
1584                    .arg((*tokeep)->title));
1585
1586            bool step_back = todelete == it;
1587            it = fixlist.erase(todelete);
1588            if (step_back)
1589                --it;
1590        }
1591    }
1592}
1593
1594void ProgramData::HandlePrograms(
1595    uint sourceid, QMap<QString, QList<ProgInfo> > &proglist)
1596{
1597    uint unchanged = 0, updated = 0;
1598
1599    MSqlQuery query(MSqlQuery::InitCon());
1600
1601    QMap<QString, QList<ProgInfo> >::const_iterator mapiter;
1602    for (mapiter = proglist.begin(); mapiter != proglist.end(); ++mapiter)
1603    {
1604        if (mapiter.key().isEmpty())
1605            continue;
1606
1607        query.prepare(
1608            "SELECT chanid "
1609            "FROM channel "
1610            "WHERE sourceid = :ID AND "
1611            "      xmltvid  = :XMLTVID");
1612        query.bindValue(":ID",      sourceid);
1613        query.bindValue(":XMLTVID", mapiter.key());
1614
1615        if (!query.exec())
1616        {
1617            MythDB::DBError("ProgramData::HandlePrograms", query);
1618            continue;
1619        }
1620
1621        vector<uint> chanids;
1622        while (query.next())
1623            chanids.push_back(query.value(0).toUInt());
1624
1625        if (chanids.empty())
1626        {
1627            LOG(VB_GENERAL, LOG_NOTICE,
1628                QString("Unknown xmltv channel identifier: %1"
1629                        " - Skipping channel.").arg(mapiter.key()));
1630            continue;
1631        }
1632
1633        QList<ProgInfo> &list = proglist[mapiter.key()];
1634        QList<ProgInfo*> sortlist;
1635        QList<ProgInfo>::iterator it = list.begin();
1636        for (; it != list.end(); ++it)
1637            sortlist.push_back(&(*it));
1638
1639        FixProgramList(sortlist);
1640
1641        for (uint i = 0; i < chanids.size(); ++i)
1642        {
1643            HandlePrograms(query, chanids[i], sortlist, unchanged, updated);
1644        }
1645    }
1646
1647    LOG(VB_GENERAL, LOG_INFO,
1648        QString("Updated programs: %1 Unchanged programs: %2")
1649                .arg(updated) .arg(unchanged));
1650}
1651
1652void ProgramData::HandlePrograms(MSqlQuery             &query,
1653                                 uint                   chanid,
1654                                 const QList<ProgInfo*> &sortlist,
1655                                 uint &unchanged,
1656                                 uint &updated)
1657{
1658    QList<ProgInfo*>::const_iterator it = sortlist.begin();
1659    for (; it != sortlist.end(); ++it)
1660    {
1661        if (IsUnchanged(query, chanid, **it))
1662        {
1663            unchanged++;
1664            continue;
1665        }
1666
1667        if (!DeleteOverlaps(query, chanid, **it))
1668            continue;
1669
1670        updated += (*it)->InsertDB(query, chanid);
1671    }
1672}
1673
1674int ProgramData::fix_end_times(void)
1675{
1676    int count = 0;
1677    QString chanid, starttime, endtime, querystr;
1678    MSqlQuery query1(MSqlQuery::InitCon()), query2(MSqlQuery::InitCon());
1679
1680    querystr = "SELECT chanid, starttime, endtime FROM program "
1681               "WHERE endtime = '0000-00-00 00:00:00' "
1682               "ORDER BY chanid, starttime;";
1683
1684    if (!query1.exec(querystr))
1685    {
1686        LOG(VB_GENERAL, LOG_ERR,
1687            QString("fix_end_times query failed: %1").arg(querystr));
1688        return -1;
1689    }
1690
1691    while (query1.next())
1692    {
1693        starttime = query1.value(1).toString();
1694        chanid = query1.value(0).toString();
1695        endtime = query1.value(2).toString();
1696
1697        querystr = QString("SELECT chanid, starttime, endtime FROM program "
1698                           "WHERE starttime > '%1' "
1699                           "AND chanid = '%2' "
1700                           "ORDER BY starttime LIMIT 1;")
1701                           .arg(starttime)
1702                           .arg(chanid);
1703
1704        if (!query2.exec(querystr))
1705        {
1706            LOG(VB_GENERAL, LOG_ERR,
1707                QString("fix_end_times query failed: %1").arg(querystr));
1708            return -1;
1709        }
1710
1711        if (query2.next() && (endtime != query2.value(1).toString()))
1712        {
1713            count++;
1714            endtime = query2.value(1).toString();
1715            querystr = QString("UPDATE program SET "
1716                               "endtime = '%2' WHERE (chanid = '%3' AND "
1717                               "starttime = '%4');")
1718                               .arg(endtime)
1719                               .arg(chanid)
1720                               .arg(starttime);
1721
1722            if (!query2.exec(querystr))
1723            {
1724                LOG(VB_GENERAL, LOG_ERR,
1725                    QString("fix_end_times query failed: %1").arg(querystr));
1726                return -1;
1727            }
1728        }
1729    }
1730
1731    return count;
1732}
1733
1734bool ProgramData::IsUnchanged(
1735    MSqlQuery &query, uint chanid, const ProgInfo &pi)
1736{
1737    query.prepare(
1738        "SELECT count(*) "
1739        "FROM program "
1740        "WHERE chanid          = :CHANID     AND "
1741        "      starttime       = :START      AND "
1742        "      endtime         = :END        AND "
1743        "      title           = :TITLE      AND "
1744        "      subtitle        = :SUBTITLE   AND "
1745        "      description     = :DESC       AND "
1746        "      category        = :CATEGORY   AND "
1747        "      category_type   = :CATEGORY_TYPE AND "
1748        "      airdate         = :AIRDATE    AND "
1749        "      stars >= (:STARS1 - 0.001)    AND "
1750        "      stars <= (:STARS2 + 0.001)    AND "
1751        "      previouslyshown = :PREVIOUSLYSHOWN AND "
1752        "      title_pronounce = :TITLE_PRONOUNCE AND "
1753        "      audioprop       = :AUDIOPROP  AND "
1754        "      videoprop       = :VIDEOPROP  AND "
1755        "      subtitletypes   = :SUBTYPES   AND "
1756        "      partnumber      = :PARTNUMBER AND "
1757        "      parttotal       = :PARTTOTAL  AND "
1758        "      seriesid        = :SERIESID   AND "
1759        "      showtype        = :SHOWTYPE   AND "
1760        "      colorcode       = :COLORCODE  AND "
1761        "      syndicatedepisodenumber = :SYNDICATEDEPISODENUMBER AND "
1762        "      programid       = :PROGRAMID  AND "
1763        "      inetref         = :INETREF");
1764
1765    QString cattype = myth_category_type_to_string(pi.categoryType);
1766
1767    query.bindValue(":CHANID",     chanid);
1768    query.bindValue(":START",      pi.starttime);
1769    query.bindValue(":END",        pi.endtime);
1770    query.bindValue(":TITLE",      denullify(pi.title));
1771    query.bindValue(":SUBTITLE",   denullify(pi.subtitle));
1772    query.bindValue(":DESC",       denullify(pi.description));
1773    query.bindValue(":CATEGORY",   denullify(pi.category));
1774    query.bindValue(":CATEGORY_TYPE", cattype);
1775    query.bindValue(":AIRDATE",    pi.airdate);
1776    query.bindValue(":STARS1",     pi.stars);
1777    query.bindValue(":STARS2",     pi.stars);
1778    query.bindValue(":PREVIOUSLYSHOWN", pi.previouslyshown);
1779    query.bindValue(":TITLE_PRONOUNCE", pi.title_pronounce);
1780    query.bindValue(":AUDIOPROP",  pi.audioProps);
1781    query.bindValue(":VIDEOPROP",  pi.videoProps);
1782    query.bindValue(":SUBTYPES",   pi.subtitleType);
1783    query.bindValue(":PARTNUMBER", pi.partnumber);
1784    query.bindValue(":PARTTOTAL",  pi.parttotal);
1785    query.bindValue(":SERIESID",   denullify(pi.seriesId));
1786    query.bindValue(":SHOWTYPE",   pi.showtype);
1787    query.bindValue(":COLORCODE",  pi.colorcode);
1788    query.bindValue(":SYNDICATEDEPISODENUMBER",
1789                    denullify(pi.syndicatedepisodenumber));
1790    query.bindValue(":PROGRAMID",  denullify(pi.programId));
1791    query.bindValue(":INETREF",    pi.inetref);
1792
1793    if (query.exec() && query.next())
1794        return query.value(0).toUInt() > 0;
1795
1796    return false;
1797}
1798
1799bool ProgramData::DeleteOverlaps(
1800    MSqlQuery &query, uint chanid, const ProgInfo &pi)
1801{
1802    if (VERBOSE_LEVEL_CHECK(VB_XMLTV, LOG_INFO))
1803    {
1804        // Get overlaps..
1805        query.prepare(
1806            "SELECT title,starttime,endtime "
1807            "FROM program "
1808            "WHERE chanid     = :CHANID AND "
1809            "      starttime >= :START AND "
1810            "      starttime <  :END;");
1811        query.bindValue(":CHANID", chanid);
1812        query.bindValue(":START",  pi.starttime);
1813        query.bindValue(":END",    pi.endtime);
1814
1815        if (!query.exec())
1816            return false;
1817
1818        if (!query.next())
1819            return true;
1820
1821        do
1822        {
1823            LOG(VB_XMLTV, LOG_INFO,
1824                QString("Removing existing program: %1 - %2 %3 %4")
1825                .arg(MythDate::as_utc(query.value(1).toDateTime()).toString(Qt::ISODate))
1826                .arg(MythDate::as_utc(query.value(2).toDateTime()).toString(Qt::ISODate))
1827                .arg(pi.channel)
1828                .arg(query.value(0).toString()));
1829        } while (query.next());
1830    }
1831
1832    if (!ClearDataByChannel(chanid, pi.starttime, pi.endtime, false))
1833    {
1834        LOG(VB_XMLTV, LOG_ERR,
1835            QString("Program delete failed    : %1 - %2 %3 %4")
1836                .arg(pi.starttime.toString(Qt::ISODate))
1837                .arg(pi.endtime.toString(Qt::ISODate))
1838                .arg(pi.channel)
1839                .arg(pi.title));
1840        return false;
1841    }
1842
1843    return true;
1844}