Ticket #11914: programdata.cpp

File programdata.cpp, 30.9 KB (added by klaas.de.waal@…, 13 years ago)

"newer is better" implementation for EIT/EPG

Line 
1// -*- Mode: c++ -*-
2
3// Simplified implementation that does only the following:
4// - update existing program if new program has the same starttime and endtime
5// - delete all programs that otherwise overlap with the new program
6// - insert new program if there is no match for update
7// Tested with DVB-C from Ziggo.
8
9#include <limits.h>
10
11// C++ includes
12#include <algorithm>
13using namespace std;
14
15// MythTV headers
16#include "programdata.h"
17#include "channelutil.h"
18#include "mythdb.h"
19#include "mythlogging.h"
20#include "dvbdescriptors.h"
21
22#define LOC QString("ProgramData: ")
23
24static const char *roles[] =
25{
26 "",
27 "actor", "director", "producer", "executive_producer",
28 "writer", "guest_star", "host", "adapter",
29 "presenter", "commentator", "guest",
30};
31
32static QString denullify(const QString &str)
33{
34 return str.isNull() ? "" : str;
35}
36
37DBPerson::DBPerson(const DBPerson &other) :
38 role(other.role), name(other.name)
39{
40 name.squeeze();
41}
42
43DBPerson::DBPerson(Role _role, const QString &_name) :
44 role(_role), name(_name)
45{
46 name.squeeze();
47}
48
49DBPerson::DBPerson(const QString &_role, const QString &_name) :
50 role(kUnknown), name(_name)
51{
52 if (!_role.isEmpty())
53 {
54 for (uint i = 0; i < sizeof(roles) / sizeof(char *); i++)
55 {
56 if (_role == QString(roles[i]))
57 role = (Role) i;
58 }
59 }
60 name.squeeze();
61}
62
63QString DBPerson::GetRole(void) const
64{
65 if ((role < kActor) || (role > kGuest))
66 return "guest";
67 return roles[role];
68}
69
70uint DBPerson::InsertDB(MSqlQuery &query, uint chanid,
71 const QDateTime &starttime) const
72{
73 uint personid = GetPersonDB(query);
74 if (!personid && InsertPersonDB(query))
75 personid = GetPersonDB(query);
76
77 return InsertCreditsDB(query, personid, chanid, starttime);
78}
79
80uint DBPerson::GetPersonDB(MSqlQuery &query) const
81{
82 query.prepare(
83 "SELECT person "
84 "FROM people "
85 "WHERE name = :NAME");
86 query.bindValue(":NAME", name);
87
88 if (!query.exec())
89 MythDB::DBError("get_person", query);
90 else if (query.next())
91 return query.value(0).toUInt();
92
93 return 0;
94}
95
96uint DBPerson::InsertPersonDB(MSqlQuery &query) const
97{
98 query.prepare(
99 "INSERT IGNORE INTO people (name) "
100 "VALUES (:NAME);");
101 query.bindValue(":NAME", name);
102
103 if (query.exec())
104 return 1;
105
106 MythDB::DBError("insert_person", query);
107 return 0;
108}
109
110uint DBPerson::InsertCreditsDB(MSqlQuery &query, uint personid, uint chanid,
111 const QDateTime &starttime) const
112{
113 if (!personid)
114 return 0;
115
116 query.prepare(
117 "REPLACE INTO credits "
118 " ( person, chanid, starttime, role) "
119 "VALUES (:PERSON, :CHANID, :STARTTIME, :ROLE) ");
120 query.bindValue(":PERSON", personid);
121 query.bindValue(":CHANID", chanid);
122 query.bindValue(":STARTTIME", starttime);
123 query.bindValue(":ROLE", GetRole());
124
125 if (query.exec())
126 return 1;
127
128 MythDB::DBError("insert_credits", query);
129 return 0;
130}
131
132DBEvent &DBEvent::operator=(const DBEvent &other)
133{
134 if (this == &other)
135 return *this;
136
137 title = other.title;
138 subtitle = other.subtitle;
139 description = other.description;
140 category = other.category;
141 starttime = other.starttime;
142 endtime = other.endtime;
143 airdate = other.airdate;
144 originalairdate = other.originalairdate;
145
146 if (credits != other.credits)
147 {
148 if (credits)
149 {
150 delete credits;
151 credits = NULL;
152 }
153
154 if (other.credits)
155 {
156 credits = new DBCredits;
157 credits->insert(credits->end(),
158 other.credits->begin(),
159 other.credits->end());
160 }
161 }
162
163 partnumber = other.partnumber;
164 parttotal = other.parttotal;
165 syndicatedepisodenumber = other.syndicatedepisodenumber;
166 subtitleType = other.subtitleType;
167 audioProps = other.audioProps;
168 videoProps = other.videoProps;
169 stars = other.stars;
170 categoryType = other.categoryType;
171 seriesId = other.seriesId;
172 programId = other.programId;
173 previouslyshown = other.previouslyshown;
174 ratings = other.ratings;
175 listingsource = other.listingsource;
176
177 Squeeze();
178
179 return *this;
180}
181
182void DBEvent::Squeeze(void)
183{
184 title.squeeze();
185 subtitle.squeeze();
186 description.squeeze();
187 category.squeeze();
188 syndicatedepisodenumber.squeeze();
189 seriesId.squeeze();
190 programId.squeeze();
191}
192
193void DBEvent::AddPerson(DBPerson::Role role, const QString &name)
194{
195 if (!credits)
196 credits = new DBCredits;
197
198 credits->push_back(DBPerson(role, name));
199}
200
201void DBEvent::AddPerson(const QString &role, const QString &name)
202{
203 if (!credits)
204 credits = new DBCredits;
205
206 credits->push_back(DBPerson(role, name));
207}
208
209bool DBEvent::HasTimeConflict(const DBEvent &o) const
210{
211 return ((starttime <= o.starttime && o.starttime < endtime) ||
212 (o.endtime <= endtime && starttime < o.endtime));
213}
214
215uint DBEvent::GetOverlappingPrograms(
216 MSqlQuery &query, uint chanid, vector<DBEvent> &programs) const
217{
218 uint count = 0;
219 query.prepare(
220 "SELECT title, subtitle, description, "
221 " category, category_type, "
222 " starttime, endtime, "
223 " subtitletypes+0,audioprop+0, videoprop+0, "
224 " seriesid, programid, "
225 " partnumber, parttotal, "
226 " syndicatedepisodenumber, "
227 " airdate, originalairdate, "
228 " previouslyshown,listingsource, "
229 " stars+0 "
230 "FROM program "
231 "WHERE chanid = :CHANID AND "
232 " manualid = 0 AND "
233 " ( ( starttime >= :STIME1 AND starttime < :ETIME1 ) OR "
234 " ( endtime > :STIME2 AND endtime <= :ETIME2 ) OR "
235 " ( starttime < :STIME3 AND endtime > :ETIME3 ) )");
236 query.bindValue(":CHANID", chanid);
237 query.bindValue(":STIME1", starttime);
238 query.bindValue(":ETIME1", endtime);
239 query.bindValue(":STIME2", starttime);
240 query.bindValue(":ETIME2", endtime);
241 query.bindValue(":STIME3", starttime);
242 query.bindValue(":ETIME3", endtime);
243 // Test STIME3/ETIME3 for new program inside old program
244
245 if (!query.exec())
246 {
247 MythDB::DBError("GetOverlappingPrograms 1", query);
248 return 0;
249 }
250
251 while (query.next())
252 {
253 ProgramInfo::CategoryType category_type =
254 string_to_myth_category_type(query.value(4).toString());
255
256 DBEvent prog(
257 query.value(0).toString(),
258 query.value(1).toString(),
259 query.value(2).toString(),
260 query.value(3).toString(),
261 category_type,
262 MythDate::as_utc(query.value(5).toDateTime()),
263 MythDate::as_utc(query.value(6).toDateTime()),
264 query.value(7).toUInt(),
265 query.value(8).toUInt(),
266 query.value(9).toUInt(),
267 query.value(19).toDouble(),
268 query.value(10).toString(),
269 query.value(11).toString(),
270 query.value(18).toUInt());
271
272 prog.partnumber = query.value(12).toUInt();
273 prog.parttotal = query.value(13).toUInt();
274 prog.syndicatedepisodenumber = query.value(14).toString();
275 prog.airdate = query.value(15).toUInt();
276 prog.originalairdate = query.value(16).toDate();
277 prog.previouslyshown = query.value(17).toBool();
278 ;
279
280 programs.push_back(prog);
281 count++;
282 }
283
284 return count;
285}
286
287static bool delete_program(MSqlQuery &query, uint chanid, const QDateTime &st)
288{
289 query.prepare(
290 "DELETE from program "
291 "WHERE chanid = :CHANID AND "
292 " starttime = :STARTTIME");
293
294 query.bindValue(":CHANID", chanid);
295 query.bindValue(":STARTTIME", st);
296
297 if (!query.exec())
298 {
299 MythDB::DBError("delete_program", query);
300 return false;
301 }
302
303 query.prepare(
304 "DELETE from credits "
305 "WHERE chanid = :CHANID AND "
306 " starttime = :STARTTIME");
307
308 query.bindValue(":CHANID", chanid);
309 query.bindValue(":STARTTIME", st);
310
311 if (!query.exec())
312 {
313 MythDB::DBError("delete_credits", query);
314 return false;
315 }
316
317 return true;
318}
319
320uint DBEvent::UpdateDB(
321 MSqlQuery &query, uint chanid, int match_threshold) const
322{
323 vector<DBEvent> programs;
324 uint count = GetOverlappingPrograms(query, chanid, programs);
325 bool update = false;
326 uint status = 0;
327
328 // Update if we have an overlapping program with
329 // exactly the same start- and endtime.
330 // Delete all other overlapping programs.
331 for (uint i=0; i<count; i++)
332 {
333 if (starttime == programs[i].starttime &&
334 endtime == programs[i].endtime)
335 {
336 LOG(VB_EIT, LOG_DEBUG,
337 QString("EIT: update chanid %1 %2 '%3'")
338 .arg(chanid)
339 .arg(starttime.toString(Qt::ISODate))
340 .arg(title));
341 status = InsertDB(query, chanid);
342 update = true;
343 }
344 else
345 {
346 LOG(VB_EIT, LOG_DEBUG,
347 QString("EIT: delete chanid %1 %2 '%3'")
348 .arg(chanid)
349 .arg(programs[i].starttime.toString(Qt::ISODate))
350 .arg(programs[i].title));
351 delete_program(query, chanid, programs[i].starttime);
352 }
353 }
354
355 // If we have not done an update then we will insert a new program.
356 if (!update)
357 {
358 LOG(VB_EIT, LOG_DEBUG,
359 QString("EIT: insert chanid %1 %2 '%3'")
360 .arg(chanid)
361 .arg(starttime.toString(Qt::ISODate))
362 .arg(title));
363 status = InsertDB(query, chanid);
364 }
365
366 return status;
367}
368
369uint DBEvent::InsertDB(MSqlQuery &query, uint chanid) const
370{
371 query.prepare(
372 "REPLACE INTO program ("
373 " chanid, title, subtitle, description, "
374 " category, category_type, "
375 " starttime, endtime, "
376 " closecaptioned, stereo, hdtv, subtitled, "
377 " subtitletypes, audioprop, videoprop, "
378 " stars, partnumber, parttotal, "
379 " syndicatedepisodenumber, "
380 " airdate, originalairdate,listingsource, "
381 " seriesid, programid, previouslyshown ) "
382 "VALUES ("
383 " :CHANID, :TITLE, :SUBTITLE, :DESCRIPTION, "
384 " :CATEGORY, :CATTYPE, "
385 " :STARTTIME, :ENDTIME, "
386 " :CC, :STEREO, :HDTV, :HASSUBTITLES, "
387 " :SUBTYPES, :AUDIOPROP, :VIDEOPROP, "
388 " :STARS, :PARTNUMBER, :PARTTOTAL, "
389 " :SYNDICATENO, "
390 " :AIRDATE, :ORIGAIRDATE, :LSOURCE, "
391 " :SERIESID, :PROGRAMID, :PREVSHOWN) ");
392
393 QString cattype = myth_category_type_to_string(categoryType);
394 QString empty("");
395 query.bindValue(":CHANID", chanid);
396 query.bindValue(":TITLE", denullify(title));
397 query.bindValue(":SUBTITLE", denullify(subtitle));
398 query.bindValue(":DESCRIPTION", denullify(description));
399 query.bindValue(":CATEGORY", denullify(category));
400 query.bindValue(":CATTYPE", cattype);
401 query.bindValue(":STARTTIME", starttime);
402 query.bindValue(":ENDTIME", endtime);
403 query.bindValue(":CC", subtitleType & SUB_HARDHEAR ? true : false);
404 query.bindValue(":STEREO", audioProps & AUD_STEREO ? true : false);
405 query.bindValue(":HDTV", videoProps & VID_HDTV ? true : false);
406 query.bindValue(":HASSUBTITLES",subtitleType & SUB_NORMAL ? true : false);
407 query.bindValue(":SUBTYPES", subtitleType);
408 query.bindValue(":AUDIOPROP", audioProps);
409 query.bindValue(":VIDEOPROP", videoProps);
410 query.bindValue(":STARS", stars);
411 query.bindValue(":PARTNUMBER", partnumber);
412 query.bindValue(":PARTTOTAL", parttotal);
413 query.bindValue(":SYNDICATENO", denullify(syndicatedepisodenumber));
414 query.bindValue(":AIRDATE", airdate ? QString::number(airdate):"0000");
415 query.bindValue(":ORIGAIRDATE", originalairdate);
416 query.bindValue(":LSOURCE", listingsource);
417 query.bindValue(":SERIESID", denullify(seriesId));
418 query.bindValue(":PROGRAMID", denullify(programId));
419 query.bindValue(":PREVSHOWN", previouslyshown);
420
421 if (!query.exec())
422 {
423 MythDB::DBError("InsertDB", query);
424 return 0;
425 }
426
427 if (credits)
428 {
429 for (uint i = 0; i < credits->size(); i++)
430 (*credits)[i].InsertDB(query, chanid, starttime);
431 }
432
433 return 1;
434}
435
436ProgInfo::ProgInfo(const ProgInfo &other) :
437 DBEvent(other.listingsource)
438{
439 *this = other;
440}
441
442ProgInfo &ProgInfo::operator=(const ProgInfo &other)
443{
444 if (this == &other)
445 return *this;
446
447 DBEvent::operator=(other);
448
449 channel = other.channel;
450 startts = other.startts;
451 endts = other.endts;
452 stars = other.stars;
453 title_pronounce = other.title_pronounce;
454 showtype = other.showtype;
455 colorcode = other.colorcode;
456 clumpidx = other.clumpidx;
457 clumpmax = other.clumpmax;
458
459 channel.squeeze();
460 startts.squeeze();
461 endts.squeeze();
462 stars.squeeze();
463 title_pronounce.squeeze();
464 showtype.squeeze();
465 colorcode.squeeze();
466 clumpidx.squeeze();
467 clumpmax.squeeze();
468
469 return *this;
470}
471
472void ProgInfo::Squeeze(void)
473{
474 DBEvent::Squeeze();
475 channel.squeeze();
476 startts.squeeze();
477 endts.squeeze();
478 stars.squeeze();
479 title_pronounce.squeeze();
480 showtype.squeeze();
481 colorcode.squeeze();
482 clumpidx.squeeze();
483 clumpmax.squeeze();
484}
485
486uint ProgInfo::InsertDB(MSqlQuery &query, uint chanid) const
487{
488 LOG(VB_XMLTV, LOG_INFO,
489 QString("Inserting new program : %1 - %2 %3 %4")
490 .arg(starttime.toString(Qt::ISODate))
491 .arg(endtime.toString(Qt::ISODate))
492 .arg(channel)
493 .arg(title));
494
495 query.prepare(
496 "REPLACE INTO program ("
497 " chanid, title, subtitle, description, "
498 " category, category_type, "
499 " starttime, endtime, "
500 " closecaptioned, stereo, hdtv, subtitled, "
501 " subtitletypes, audioprop, videoprop, "
502 " partnumber, parttotal, "
503 " syndicatedepisodenumber, "
504 " airdate, originalairdate,listingsource, "
505 " seriesid, programid, previouslyshown, "
506 " stars, showtype, title_pronounce, colorcode ) "
507
508 "VALUES("
509 " :CHANID, :TITLE, :SUBTITLE, :DESCRIPTION, "
510 " :CATEGORY, :CATTYPE, "
511 " :STARTTIME, :ENDTIME, "
512 " :CC, :STEREO, :HDTV, :HASSUBTITLES, "
513 " :SUBTYPES, :AUDIOPROP, :VIDEOPROP, "
514 " :PARTNUMBER, :PARTTOTAL, "
515 " :SYNDICATENO, "
516 " :AIRDATE, :ORIGAIRDATE, :LSOURCE, "
517 " :SERIESID, :PROGRAMID, :PREVSHOWN, "
518 " :STARS, :SHOWTYPE, :TITLEPRON, :COLORCODE)");
519
520 QString cattype = myth_category_type_to_string(categoryType);
521
522 query.bindValue(":CHANID", chanid);
523 query.bindValue(":TITLE", denullify(title));
524 query.bindValue(":SUBTITLE", denullify(subtitle));
525 query.bindValue(":DESCRIPTION", denullify(description));
526 query.bindValue(":CATEGORY", denullify(category));
527 query.bindValue(":CATTYPE", cattype);
528 query.bindValue(":STARTTIME", starttime);
529 query.bindValue(":ENDTIME", endtime);
530 query.bindValue(":CC",
531 subtitleType & SUB_HARDHEAR ? true : false);
532 query.bindValue(":STEREO",
533 audioProps & AUD_STEREO ? true : false);
534 query.bindValue(":HDTV",
535 videoProps & VID_HDTV ? true : false);
536 query.bindValue(":HASSUBTITLES",
537 subtitleType & SUB_NORMAL ? true : false);
538 query.bindValue(":SUBTYPES", subtitleType);
539 query.bindValue(":AUDIOPROP", audioProps);
540 query.bindValue(":VIDEOPROP", videoProps);
541 query.bindValue(":PARTNUMBER", partnumber);
542 query.bindValue(":PARTTOTAL", parttotal);
543 query.bindValue(":SYNDICATENO", denullify(syndicatedepisodenumber));
544 query.bindValue(":AIRDATE", airdate ? QString::number(airdate):"0000");
545 query.bindValue(":ORIGAIRDATE", originalairdate);
546 query.bindValue(":LSOURCE", listingsource);
547 query.bindValue(":SERIESID", denullify(seriesId));
548 query.bindValue(":PROGRAMID", denullify(programId));
549 query.bindValue(":PREVSHOWN", previouslyshown);
550 query.bindValue(":STARS", stars);
551 query.bindValue(":SHOWTYPE", showtype);
552 query.bindValue(":TITLEPRON", title_pronounce);
553 query.bindValue(":COLORCODE", colorcode);
554
555 if (!query.exec())
556 {
557 MythDB::DBError("program insert", query);
558 return 0;
559 }
560
561 QList<EventRating>::const_iterator j = ratings.begin();
562 for (; j != ratings.end(); ++j)
563 {
564 query.prepare(
565 "INSERT INTO programrating "
566 " ( chanid, starttime, system, rating) "
567 "VALUES (:CHANID, :START, :SYS, :RATING)");
568 query.bindValue(":CHANID", chanid);
569 query.bindValue(":START", starttime);
570 query.bindValue(":SYS", (*j).system);
571 query.bindValue(":RATING", (*j).rating);
572
573 if (!query.exec())
574 MythDB::DBError("programrating insert", query);
575 }
576
577 if (credits)
578 {
579 for (uint i = 0; i < credits->size(); ++i)
580 (*credits)[i].InsertDB(query, chanid, starttime);
581 }
582
583 return 1;
584}
585
586bool ProgramData::ClearDataByChannel(
587 uint chanid, const QDateTime &from, const QDateTime &to,
588 bool use_channel_time_offset)
589{
590 int secs = 0;
591 if (use_channel_time_offset)
592 secs = ChannelUtil::GetTimeOffset(chanid) * 60;
593
594 QDateTime newFrom = from.addSecs(secs);
595 QDateTime newTo = to.addSecs(secs);
596
597 MSqlQuery query(MSqlQuery::InitCon());
598 query.prepare("DELETE FROM program "
599 "WHERE starttime >= :FROM AND starttime < :TO "
600 "AND chanid = :CHANID ;");
601 query.bindValue(":FROM", newFrom);
602 query.bindValue(":TO", newTo);
603 query.bindValue(":CHANID", chanid);
604 bool ok = query.exec();
605
606 query.prepare("DELETE FROM programrating "
607 "WHERE starttime >= :FROM AND starttime < :TO "
608 "AND chanid = :CHANID ;");
609 query.bindValue(":FROM", newFrom);
610 query.bindValue(":TO", newTo);
611 query.bindValue(":CHANID", chanid);
612 ok &= query.exec();
613
614 query.prepare("DELETE FROM credits "
615 "WHERE starttime >= :FROM AND starttime < :TO "
616 "AND chanid = :CHANID ;");
617 query.bindValue(":FROM", newFrom);
618 query.bindValue(":TO", newTo);
619 query.bindValue(":CHANID", chanid);
620 ok &= query.exec();
621
622 query.prepare("DELETE FROM programgenres "
623 "WHERE starttime >= :FROM AND starttime < :TO "
624 "AND chanid = :CHANID ;");
625 query.bindValue(":FROM", newFrom);
626 query.bindValue(":TO", newTo);
627 query.bindValue(":CHANID", chanid);
628 ok &= query.exec();
629
630 return ok;
631}
632
633bool ProgramData::ClearDataBySource(
634 uint sourceid, const QDateTime &from, const QDateTime &to,
635 bool use_channel_time_offset)
636{
637 vector<uint> chanids = ChannelUtil::GetChanIDs(sourceid);
638
639 bool ok = true;
640 for (uint i = 0; i < chanids.size(); i++)
641 ok &= ClearDataByChannel(chanids[i], from, to, use_channel_time_offset);
642
643 return ok;
644}
645
646static bool start_time_less_than(const DBEvent *a, const DBEvent *b)
647{
648 return (a->starttime < b->starttime);
649}
650
651void ProgramData::FixProgramList(QList<ProgInfo*> &fixlist)
652{
653 qStableSort(fixlist.begin(), fixlist.end(), start_time_less_than);
654
655 QList<ProgInfo*>::iterator it = fixlist.begin();
656 while (1)
657 {
658 QList<ProgInfo*>::iterator cur = it;
659 ++it;
660
661 // fill in miss stop times
662 if ((*cur)->endts.isEmpty() || (*cur)->startts > (*cur)->endts)
663 {
664 if (it != fixlist.end())
665 {
666 (*cur)->endts = (*it)->startts;
667 (*cur)->endtime = (*it)->starttime;
668 }
669 else
670 {
671 (*cur)->endtime = (*cur)->starttime;
672 if ((*cur)->endtime < QDateTime(
673 (*cur)->endtime.date(), QTime(6, 0), Qt::UTC))
674 {
675 (*cur)->endtime = QDateTime(
676 (*cur)->endtime.date(), QTime(6, 0), Qt::UTC);
677 }
678 else
679 {
680 (*cur)->endtime = QDateTime(
681 (*cur)->endtime.date().addDays(1),
682 QTime(0, 0), Qt::UTC);
683 }
684
685 (*cur)->endts =
686 MythDate::toString((*cur)->endtime, MythDate::kFilename);
687 }
688 }
689
690 if (it == fixlist.end())
691 break;
692
693 // remove overlapping programs
694 if ((*cur)->HasTimeConflict(**it))
695 {
696 QList<ProgInfo*>::iterator tokeep, todelete;
697
698 if ((*cur)->endtime <= (*cur)->starttime)
699 tokeep = it, todelete = cur;
700 else if ((*it)->endtime <= (*it)->starttime)
701 tokeep = cur, todelete = it;
702 else if (!(*cur)->subtitle.isEmpty() &&
703 (*it)->subtitle.isEmpty())
704 tokeep = cur, todelete = it;
705 else if (!(*it)->subtitle.isEmpty() &&
706 (*cur)->subtitle.isEmpty())
707 tokeep = it, todelete = cur;
708 else if (!(*cur)->description.isEmpty() &&
709 (*it)->description.isEmpty())
710 tokeep = cur, todelete = it;
711 else
712 tokeep = it, todelete = cur;
713
714
715 LOG(VB_XMLTV, LOG_INFO,
716 QString("Removing conflicting program: %1 - %2 %3 %4")
717 .arg((*todelete)->starttime.toString(Qt::ISODate))
718 .arg((*todelete)->endtime.toString(Qt::ISODate))
719 .arg((*todelete)->channel)
720 .arg((*todelete)->title));
721
722 LOG(VB_XMLTV, LOG_INFO,
723 QString("Conflicted with : %1 - %2 %3 %4")
724 .arg((*tokeep)->starttime.toString(Qt::ISODate))
725 .arg((*tokeep)->endtime.toString(Qt::ISODate))
726 .arg((*tokeep)->channel)
727 .arg((*tokeep)->title));
728
729 bool step_back = todelete == it;
730 it = fixlist.erase(todelete);
731 if (step_back)
732 --it;
733 }
734 }
735}
736
737void ProgramData::HandlePrograms(
738 uint sourceid, QMap<QString, QList<ProgInfo> > &proglist)
739{
740 uint unchanged = 0, updated = 0;
741
742 MSqlQuery query(MSqlQuery::InitCon());
743
744 QMap<QString, QList<ProgInfo> >::const_iterator mapiter;
745 for (mapiter = proglist.begin(); mapiter != proglist.end(); ++mapiter)
746 {
747 if (mapiter.key().isEmpty())
748 continue;
749
750 query.prepare(
751 "SELECT chanid "
752 "FROM channel "
753 "WHERE sourceid = :ID AND "
754 " xmltvid = :XMLTVID");
755 query.bindValue(":ID", sourceid);
756 query.bindValue(":XMLTVID", mapiter.key());
757
758 if (!query.exec())
759 {
760 MythDB::DBError("ProgramData::HandlePrograms", query);
761 continue;
762 }
763
764 vector<uint> chanids;
765 while (query.next())
766 chanids.push_back(query.value(0).toUInt());
767
768 if (chanids.empty())
769 {
770 LOG(VB_GENERAL, LOG_NOTICE,
771 QString("Unknown xmltv channel identifier: %1"
772 " - Skipping channel.").arg(mapiter.key()));
773 continue;
774 }
775
776 QList<ProgInfo> &list = proglist[mapiter.key()];
777 QList<ProgInfo*> sortlist;
778 QList<ProgInfo>::iterator it = list.begin();
779 for (; it != list.end(); ++it)
780 sortlist.push_back(&(*it));
781
782 FixProgramList(sortlist);
783
784 for (uint i = 0; i < chanids.size(); ++i)
785 {
786 HandlePrograms(query, chanids[i], sortlist, unchanged, updated);
787 }
788 }
789
790 LOG(VB_GENERAL, LOG_INFO,
791 QString("Updated programs: %1 Unchanged programs: %2")
792 .arg(updated) .arg(unchanged));
793}
794
795void ProgramData::HandlePrograms(MSqlQuery &query,
796 uint chanid,
797 const QList<ProgInfo*> &sortlist,
798 uint &unchanged,
799 uint &updated)
800{
801 QList<ProgInfo*>::const_iterator it = sortlist.begin();
802 for (; it != sortlist.end(); ++it)
803 {
804 if (IsUnchanged(query, chanid, **it))
805 {
806 unchanged++;
807 continue;
808 }
809
810 if (!DeleteOverlaps(query, chanid, **it))
811 continue;
812
813 updated += (*it)->InsertDB(query, chanid);
814 }
815}
816
817int ProgramData::fix_end_times(void)
818{
819 int count = 0;
820 QString chanid, starttime, endtime, querystr;
821 MSqlQuery query1(MSqlQuery::InitCon()), query2(MSqlQuery::InitCon());
822
823 querystr = "SELECT chanid, starttime, endtime FROM program "
824 "WHERE (DATE_FORMAT(endtime,'%H%i') = '0000') "
825 "ORDER BY chanid, starttime;";
826
827 if (!query1.exec(querystr))
828 {
829 LOG(VB_GENERAL, LOG_ERR,
830 QString("fix_end_times query failed: %1").arg(querystr));
831 return -1;
832 }
833
834 while (query1.next())
835 {
836 starttime = query1.value(1).toString();
837 chanid = query1.value(0).toString();
838 endtime = query1.value(2).toString();
839
840 querystr = QString("SELECT chanid, starttime, endtime FROM program "
841 "WHERE starttime BETWEEN '%1 00:00:00'"
842 "AND '%2 23:59:59' AND chanid = '%3' "
843 "ORDER BY starttime LIMIT 1;")
844 .arg(endtime.left(10))
845 .arg(endtime.left(10))
846 .arg(chanid);
847
848 if (!query2.exec(querystr))
849 {
850 LOG(VB_GENERAL, LOG_ERR,
851 QString("fix_end_times query failed: %1").arg(querystr));
852 return -1;
853 }
854
855 if (query2.next() && (endtime != query2.value(1).toString()))
856 {
857 count++;
858 endtime = query2.value(1).toString();
859 querystr = QString("UPDATE program SET starttime = '%1', "
860 "endtime = '%2' WHERE (chanid = '%3' AND "
861 "starttime = '%4');")
862 .arg(starttime)
863 .arg(endtime)
864 .arg(chanid)
865 .arg(starttime);
866
867 if (!query2.exec(querystr))
868 {
869 LOG(VB_GENERAL, LOG_ERR,
870 QString("fix_end_times query failed: %1").arg(querystr));
871 return -1;
872 }
873 }
874 }
875
876 return count;
877}
878
879bool ProgramData::IsUnchanged(
880 MSqlQuery &query, uint chanid, const ProgInfo &pi)
881{
882 query.prepare(
883 "SELECT count(*) "
884 "FROM program "
885 "WHERE chanid = :CHANID AND "
886 " starttime = :START AND "
887 " endtime = :END AND "
888 " title = :TITLE AND "
889 " subtitle = :SUBTITLE AND "
890 " description = :DESC AND "
891 " category = :CATEGORY AND "
892 " category_type = :CATEGORY_TYPE AND "
893 " airdate = :AIRDATE AND "
894 " stars >= (:STARS1 - 0.001) AND "
895 " stars <= (:STARS2 + 0.001) AND "
896 " previouslyshown = :PREVIOUSLYSHOWN AND "
897 " title_pronounce = :TITLE_PRONOUNCE AND "
898 " audioprop = :AUDIOPROP AND "
899 " videoprop = :VIDEOPROP AND "
900 " subtitletypes = :SUBTYPES AND "
901 " partnumber = :PARTNUMBER AND "
902 " parttotal = :PARTTOTAL AND "
903 " seriesid = :SERIESID AND "
904 " showtype = :SHOWTYPE AND "
905 " colorcode = :COLORCODE AND "
906 " syndicatedepisodenumber = :SYNDICATEDEPISODENUMBER AND "
907 " programid = :PROGRAMID");
908
909 QString cattype = myth_category_type_to_string(pi.categoryType);
910
911 query.bindValue(":CHANID", chanid);
912 query.bindValue(":START", pi.starttime);
913 query.bindValue(":END", pi.endtime);
914 query.bindValue(":TITLE", denullify(pi.title));
915 query.bindValue(":SUBTITLE", denullify(pi.subtitle));
916 query.bindValue(":DESC", denullify(pi.description));
917 query.bindValue(":CATEGORY", denullify(pi.category));
918 query.bindValue(":CATEGORY_TYPE", cattype);
919 query.bindValue(":AIRDATE", pi.airdate);
920 query.bindValue(":STARS1", pi.stars);
921 query.bindValue(":STARS2", pi.stars);
922 query.bindValue(":PREVIOUSLYSHOWN", pi.previouslyshown);
923 query.bindValue(":TITLE_PRONOUNCE", pi.title_pronounce);
924 query.bindValue(":AUDIOPROP", pi.audioProps);
925 query.bindValue(":VIDEOPROP", pi.videoProps);
926 query.bindValue(":SUBTYPES", pi.subtitleType);
927 query.bindValue(":PARTNUMBER", pi.partnumber);
928 query.bindValue(":PARTTOTAL", pi.parttotal);
929 query.bindValue(":SERIESID", denullify(pi.seriesId));
930 query.bindValue(":SHOWTYPE", pi.showtype);
931 query.bindValue(":COLORCODE", pi.colorcode);
932 query.bindValue(":SYNDICATEDEPISODENUMBER",
933 denullify(pi.syndicatedepisodenumber));
934 query.bindValue(":PROGRAMID", denullify(pi.programId));
935
936 if (query.exec() && query.next())
937 return query.value(0).toUInt() > 0;
938
939 return false;
940}
941
942bool ProgramData::DeleteOverlaps(
943 MSqlQuery &query, uint chanid, const ProgInfo &pi)
944{
945 if (VERBOSE_LEVEL_CHECK(VB_XMLTV, LOG_INFO))
946 {
947 // Get overlaps..
948 query.prepare(
949 "SELECT title,starttime,endtime "
950 "FROM program "
951 "WHERE chanid = :CHANID AND "
952 " starttime >= :START AND "
953 " starttime < :END;");
954 query.bindValue(":CHANID", chanid);
955 query.bindValue(":START", pi.starttime);
956 query.bindValue(":END", pi.endtime);
957
958 if (!query.exec())
959 return false;
960
961 if (!query.next())
962 return true;
963
964 do
965 {
966 LOG(VB_XMLTV, LOG_INFO,
967 QString("Removing existing program: %1 - %2 %3 %4")
968 .arg(MythDate::as_utc(query.value(1).toDateTime()).toString(Qt::ISODate))
969 .arg(MythDate::as_utc(query.value(2).toDateTime()).toString(Qt::ISODate))
970 .arg(pi.channel)
971 .arg(query.value(0).toString()));
972 } while (query.next());
973 }
974
975 if (!ClearDataByChannel(chanid, pi.starttime, pi.endtime, false))
976 {
977 LOG(VB_XMLTV, LOG_ERR,
978 QString("Program delete failed : %1 - %2 %3 %4")
979 .arg(pi.starttime.toString(Qt::ISODate))
980 .arg(pi.endtime.toString(Qt::ISODate))
981 .arg(pi.channel)
982 .arg(pi.title));
983 return false;
984 }
985
986 return true;
987}