MythTV  0.27pre
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Groups Pages
programdata.cpp
Go to the documentation of this file.
1 // -*- Mode: c++ -*-
2 
3 #include <limits.h>
4 
5 // C++ includes
6 #include <algorithm>
7 using namespace std;
8 
9 // MythTV headers
10 #include "programdata.h"
11 #include "channelutil.h"
12 #include "mythdb.h"
13 #include "mythlogging.h"
14 #include "dvbdescriptors.h"
15 
16 #define LOC QString("ProgramData: ")
17 
18 static const char *roles[] =
19 {
20  "",
21  "actor", "director", "producer", "executive_producer",
22  "writer", "guest_star", "host", "adapter",
23  "presenter", "commentator", "guest",
24 };
25 
26 static QString denullify(const QString &str)
27 {
28  return str.isNull() ? "" : str;
29 }
30 
31 DBPerson::DBPerson(const DBPerson &other) :
32  role(other.role), name(other.name)
33 {
34  name.squeeze();
35 }
36 
37 DBPerson::DBPerson(Role _role, const QString &_name) :
38  role(_role), name(_name)
39 {
40  name.squeeze();
41 }
42 
43 DBPerson::DBPerson(const QString &_role, const QString &_name) :
44  role(kUnknown), name(_name)
45 {
46  if (!_role.isEmpty())
47  {
48  for (uint i = 0; i < sizeof(roles) / sizeof(char *); i++)
49  {
50  if (_role == QString(roles[i]))
51  role = (Role) i;
52  }
53  }
54  name.squeeze();
55 }
56 
57 QString DBPerson::GetRole(void) const
58 {
59  if ((role < kActor) || (role > kGuest))
60  return "guest";
61  return roles[role];
62 }
63 
64 uint DBPerson::InsertDB(MSqlQuery &query, uint chanid,
65  const QDateTime &starttime) const
66 {
67  uint personid = GetPersonDB(query);
68  if (!personid && InsertPersonDB(query))
69  personid = GetPersonDB(query);
70 
71  return InsertCreditsDB(query, personid, chanid, starttime);
72 }
73 
74 uint DBPerson::GetPersonDB(MSqlQuery &query) const
75 {
76  query.prepare(
77  "SELECT person "
78  "FROM people "
79  "WHERE name = :NAME");
80  query.bindValue(":NAME", name);
81 
82  if (!query.exec())
83  MythDB::DBError("get_person", query);
84  else if (query.next())
85  return query.value(0).toUInt();
86 
87  return 0;
88 }
89 
90 uint DBPerson::InsertPersonDB(MSqlQuery &query) const
91 {
92  query.prepare(
93  "INSERT IGNORE INTO people (name) "
94  "VALUES (:NAME);");
95  query.bindValue(":NAME", name);
96 
97  if (query.exec())
98  return 1;
99 
100  MythDB::DBError("insert_person", query);
101  return 0;
102 }
103 
104 uint DBPerson::InsertCreditsDB(MSqlQuery &query, uint personid, uint chanid,
105  const QDateTime &starttime) const
106 {
107  if (!personid)
108  return 0;
109 
110  query.prepare(
111  "REPLACE INTO credits "
112  " ( person, chanid, starttime, role) "
113  "VALUES (:PERSON, :CHANID, :STARTTIME, :ROLE) ");
114  query.bindValue(":PERSON", personid);
115  query.bindValue(":CHANID", chanid);
116  query.bindValue(":STARTTIME", starttime);
117  query.bindValue(":ROLE", GetRole());
118 
119  if (query.exec())
120  return 1;
121 
122  MythDB::DBError("insert_credits", query);
123  return 0;
124 }
125 
126 DBEvent &DBEvent::operator=(const DBEvent &other)
127 {
128  if (this == &other)
129  return *this;
130 
131  title = other.title;
132  subtitle = other.subtitle;
133  description = other.description;
134  category = other.category;
135  starttime = other.starttime;
136  endtime = other.endtime;
137  airdate = other.airdate;
138  originalairdate = other.originalairdate;
139 
140  if (credits != other.credits)
141  {
142  if (credits)
143  {
144  delete credits;
145  credits = NULL;
146  }
147 
148  if (other.credits)
149  {
150  credits = new DBCredits;
151  credits->insert(credits->end(),
152  other.credits->begin(),
153  other.credits->end());
154  }
155  }
156 
157  partnumber = other.partnumber;
158  parttotal = other.parttotal;
159  syndicatedepisodenumber = other.syndicatedepisodenumber;
160  subtitleType = other.subtitleType;
161  audioProps = other.audioProps;
162  videoProps = other.videoProps;
163  stars = other.stars;
164  categoryType = other.categoryType;
165  seriesId = other.seriesId;
166  programId = other.programId;
167  previouslyshown = other.previouslyshown;
168  ratings = other.ratings;
169  listingsource = other.listingsource;
170 
171  Squeeze();
172 
173  return *this;
174 }
175 
176 void DBEvent::Squeeze(void)
177 {
178  title.squeeze();
179  subtitle.squeeze();
180  description.squeeze();
181  category.squeeze();
182  syndicatedepisodenumber.squeeze();
183  seriesId.squeeze();
184  programId.squeeze();
185 }
186 
187 void DBEvent::AddPerson(DBPerson::Role role, const QString &name)
188 {
189  if (!credits)
190  credits = new DBCredits;
191 
192  credits->push_back(DBPerson(role, name));
193 }
194 
195 void DBEvent::AddPerson(const QString &role, const QString &name)
196 {
197  if (!credits)
198  credits = new DBCredits;
199 
200  credits->push_back(DBPerson(role, name));
201 }
202 
203 bool DBEvent::HasTimeConflict(const DBEvent &o) const
204 {
205  return ((starttime <= o.starttime && o.starttime < endtime) ||
206  (o.endtime <= endtime && starttime < o.endtime));
207 }
208 
209 uint DBEvent::UpdateDB(
210  MSqlQuery &query, uint chanid, int match_threshold) const
211 {
212  vector<DBEvent> programs;
213  uint count = GetOverlappingPrograms(query, chanid, programs);
214  int match = INT_MIN;
215  int i = -1;
216 
217  if (!count)
218  return InsertDB(query, chanid);
219 
220  // move overlapping programs out of the way and update existing if possible
221  match = GetMatch(programs, i);
222 
223  if (match >= match_threshold)
224  {
225  LOG(VB_EIT, LOG_DEBUG,
226  QString("EIT: accept match[%1]: %2 '%3' vs. '%4'")
227  .arg(i).arg(match).arg(title).arg(programs[i].title));
228  return UpdateDB(query, chanid, programs, i);
229  }
230  else
231  {
232  if (i >= 0)
233  {
234  LOG(VB_EIT, LOG_DEBUG,
235  QString("EIT: reject match[%1]: %2 '%3' vs. '%4'")
236  .arg(i).arg(match).arg(title).arg(programs[i].title));
237  }
238  return UpdateDB(query, chanid, programs, -1);
239  }
240 }
241 
242 uint DBEvent::GetOverlappingPrograms(
243  MSqlQuery &query, uint chanid, vector<DBEvent> &programs) const
244 {
245  uint count = 0;
246  query.prepare(
247  "SELECT title, subtitle, description, "
248  " category, category_type, "
249  " starttime, endtime, "
250  " subtitletypes+0,audioprop+0, videoprop+0, "
251  " seriesid, programid, "
252  " partnumber, parttotal, "
253  " syndicatedepisodenumber, "
254  " airdate, originalairdate, "
255  " previouslyshown,listingsource, "
256  " stars+0 "
257  "FROM program "
258  "WHERE chanid = :CHANID AND "
259  " manualid = 0 AND "
260  " ( ( starttime >= :STIME1 AND starttime < :ETIME1 ) OR "
261  " ( endtime > :STIME2 AND endtime <= :ETIME2 ) )");
262  query.bindValue(":CHANID", chanid);
263  query.bindValue(":STIME1", starttime);
264  query.bindValue(":ETIME1", endtime);
265  query.bindValue(":STIME2", starttime);
266  query.bindValue(":ETIME2", endtime);
267 
268  if (!query.exec())
269  {
270  MythDB::DBError("GetOverlappingPrograms 1", query);
271  return 0;
272  }
273 
274  while (query.next())
275  {
276  ProgramInfo::CategoryType category_type =
277  string_to_myth_category_type(query.value(4).toString());
278 
279  DBEvent prog(
280  query.value(0).toString(),
281  query.value(1).toString(),
282  query.value(2).toString(),
283  query.value(3).toString(),
284  category_type,
285  MythDate::as_utc(query.value(5).toDateTime()),
286  MythDate::as_utc(query.value(6).toDateTime()),
287  query.value(7).toUInt(),
288  query.value(8).toUInt(),
289  query.value(9).toUInt(),
290  query.value(19).toDouble(),
291  query.value(10).toString(),
292  query.value(11).toString(),
293  query.value(18).toUInt());
294 
295  prog.partnumber = query.value(12).toUInt();
296  prog.parttotal = query.value(13).toUInt();
297  prog.syndicatedepisodenumber = query.value(14).toString();
298  prog.airdate = query.value(15).toUInt();
299  prog.originalairdate = query.value(16).toDate();
300  prog.previouslyshown = query.value(17).toBool();
301  ;
302 
303  programs.push_back(prog);
304  count++;
305  }
306 
307  return count;
308 }
309 
310 
311 static int score_words(const QStringList &al, const QStringList &bl)
312 {
313  QStringList::const_iterator ait = al.begin();
314  QStringList::const_iterator bit = bl.begin();
315  int score = 0;
316  for (; (ait != al.end()) && (bit != bl.end()); ++ait)
317  {
318  QStringList::const_iterator bit2 = bit;
319  int dist = 0;
320  int bscore = 0;
321  for (; bit2 != bl.end(); ++bit2)
322  {
323  if (*ait == *bit)
324  {
325  bscore = max(1000, 2000 - (dist * 500));
326  // lower score for short words
327  if (ait->length() < 5)
328  bscore /= 5 - ait->length();
329  break;
330  }
331  dist++;
332  }
333  if (bscore && dist < 3)
334  {
335  for (int i = 0; (i < dist) && bit != bl.end(); i++)
336  ++bit;
337  }
338  score += bscore;
339  }
340 
341  return score / al.size();
342 }
343 
344 static int score_match(const QString &a, const QString &b)
345 {
346  if (a.isEmpty() || b.isEmpty())
347  return 0;
348  else if (a == b)
349  return 1000;
350 
351  QString A = a.simplified().toUpper();
352  QString B = b.simplified().toUpper();
353  if (A == B)
354  return 1000;
355 
356  QStringList al, bl;
357  al = A.split(" ", QString::SkipEmptyParts);
358  if (al.isEmpty())
359  return 0;
360 
361  bl = B.split(" ", QString::SkipEmptyParts);
362  if (bl.isEmpty())
363  return 0;
364 
365  // score words symmetrically
366  int score = (score_words(al, bl) + score_words(bl, al)) / 2;
367 
368  return min(900, score);
369 }
370 
371 int DBEvent::GetMatch(const vector<DBEvent> &programs, int &bestmatch) const
372 {
373  bestmatch = -1;
374  int match_val = INT_MIN;
375  int overlap = 0;
376  int duration = starttime.secsTo(endtime);
377 
378  for (uint i = 0; i < programs.size(); i++)
379  {
380  int mv = 0;
381  int duration_loop = programs[i].starttime.secsTo(programs[i].endtime);
382 
383  mv -= abs(starttime.secsTo(programs[i].starttime));
384  mv -= abs(endtime.secsTo(programs[i].endtime));
385  mv -= abs(duration - duration_loop);
386  mv += score_match(title, programs[i].title) * 10;
387  mv += score_match(subtitle, programs[i].subtitle);
388  mv += score_match(description, programs[i].description);
389 
390  /* determine overlap of both programs
391  * we don't know which one starts first */
392  if (starttime < programs[i].starttime)
393  overlap = programs[i].starttime.secsTo(endtime);
394  else if (starttime > programs[i].starttime)
395  overlap = starttime.secsTo(programs[i].endtime);
396  else
397  {
398  if (endtime <= programs[i].endtime)
399  overlap = starttime.secsTo(endtime);
400  else
401  overlap = starttime.secsTo(programs[i].endtime);
402  }
403 
404  /* scale the score depending on the overlap length
405  * full score is preserved if the overlap is at least 1/2 of the length
406  * of the shorter program */
407  if (overlap > 0)
408  {
409  /* crappy providers apparently have events without duration
410  * ensure that the minimal duration is 2 second to avoid
411  * muliplying and more importantly dividing by zero */
412  int min_dur = max(2, min(duration, duration_loop));
413  overlap = min(overlap, min_dur/2);
414  mv *= overlap * 2;
415  mv /= min_dur;
416  }
417  else
418  {
419  LOG(VB_GENERAL, LOG_ERR,
420  QString("Unexpected result: shows don't "
421  "overlap\n\t%1: %2 - %3\n\t%4: %5 - %6")
422  .arg(title.left(30), 30)
423  .arg(starttime.toString(Qt::ISODate))
424  .arg(endtime.toString(Qt::ISODate))
425  .arg(programs[i].title.left(30), 30)
426  .arg(programs[i].starttime.toString(Qt::ISODate))
427  .arg(programs[i].endtime.toString(Qt::ISODate))
428  );
429  }
430 
431  if (mv > match_val)
432  {
433  LOG(VB_EIT, LOG_DEBUG,
434  QString("GM : %1 new best match %2 with score %3")
435  .arg(title.left(25))
436  .arg(programs[i].title.left(25)).arg(mv));
437  bestmatch = i;
438  match_val = mv;
439  }
440  }
441 
442  return match_val;
443 }
444 
445 uint DBEvent::UpdateDB(
446  MSqlQuery &q, uint chanid, const vector<DBEvent> &p, int match) const
447 {
448  // adjust/delete overlaps;
449  bool ok = true;
450  for (uint i = 0; i < p.size(); i++)
451  {
452  if (i != (uint)match)
453  ok &= MoveOutOfTheWayDB(q, chanid, p[i]);
454  }
455 
456  // if we failed to move programs out of the way, don't insert new ones..
457  if (!ok)
458  return 0;
459 
460  // if no match, insert current item
461  if ((match < 0) || ((uint)match >= p.size()))
462  return InsertDB(q, chanid);
463 
464  // update matched item with current data
465  return UpdateDB(q, chanid, p[match]);
466 }
467 
468 uint DBEvent::UpdateDB(
469  MSqlQuery &query, uint chanid, const DBEvent &match) const
470 {
471  QString ltitle = title;
472  QString lsubtitle = subtitle;
473  QString ldesc = description;
474  QString lcategory = category;
475  uint16_t lairdate = airdate;
476  QString lprogramId = programId;
477  QString lseriesId = seriesId;
478  QDate loriginalairdate = originalairdate;
479 
480  if (match.title.length() >= ltitle.length())
481  ltitle = match.title;
482 
483  if (match.subtitle.length() >= lsubtitle.length())
484  lsubtitle = match.subtitle;
485 
486  if (match.description.length() >= ldesc.length())
487  ldesc = match.description;
488 
489  if (lcategory.isEmpty() && !match.category.isEmpty())
490  lcategory = match.category;
491 
492  if (!lairdate && !match.airdate)
493  lairdate = match.airdate;
494 
495  if (!loriginalairdate.isValid() && match.originalairdate.isValid())
496  loriginalairdate = match.originalairdate;
497 
498  if (lprogramId.isEmpty() && !match.programId.isEmpty())
499  lprogramId = match.programId;
500 
501  if (lseriesId.isEmpty() && !match.seriesId.isEmpty())
502  lseriesId = match.seriesId;
503 
504  ProgramInfo::CategoryType tmp = categoryType;
505  if (!categoryType && match.categoryType)
506  tmp = match.categoryType;
507 
508  QString lcattype = myth_category_type_to_string(tmp);
509 
510  unsigned char lsubtype = subtitleType | match.subtitleType;
511  unsigned char laudio = audioProps | match.audioProps;
512  unsigned char lvideo = videoProps | match.videoProps;
513 
514  uint lpartnumber =
515  (!partnumber && match.partnumber) ? match.partnumber : partnumber;
516  uint lparttotal =
517  (!parttotal && match.parttotal ) ? match.parttotal : parttotal;
518 
519  bool lpreviouslyshown = previouslyshown | match.previouslyshown;
520 
521  uint32_t llistingsource = listingsource | match.listingsource;
522 
523  QString lsyndicatedepisodenumber = syndicatedepisodenumber;
524  if (lsyndicatedepisodenumber.isEmpty() &&
525  !match.syndicatedepisodenumber.isEmpty())
526  lsyndicatedepisodenumber = match.syndicatedepisodenumber;
527 
528  query.prepare(
529  "UPDATE program "
530  "SET title = :TITLE, subtitle = :SUBTITLE, "
531  " description = :DESC, "
532  " category = :CATEGORY, category_type = :CATTYPE, "
533  " starttime = :STARTTIME, endtime = :ENDTIME, "
534  " closecaptioned = :CC, subtitled = :HASSUBTITLES, "
535  " stereo = :STEREO, hdtv = :HDTV, "
536  " subtitletypes = :SUBTYPE, "
537  " audioprop = :AUDIOPROP, videoprop = :VIDEOPROP, "
538  " partnumber = :PARTNO, parttotal = :PARTTOTAL, "
539  " syndicatedepisodenumber = :SYNDICATENO, "
540  " airdate = :AIRDATE, originalairdate=:ORIGAIRDATE, "
541  " listingsource = :LSOURCE, "
542  " seriesid = :SERIESID, programid = :PROGRAMID, "
543  " previouslyshown = :PREVSHOWN "
544  "WHERE chanid = :CHANID AND "
545  " starttime = :OLDSTART ");
546 
547  query.bindValue(":CHANID", chanid);
548  query.bindValue(":OLDSTART", match.starttime);
549  query.bindValue(":TITLE", denullify(ltitle));
550  query.bindValue(":SUBTITLE", denullify(lsubtitle));
551  query.bindValue(":DESC", denullify(ldesc));
552  query.bindValue(":CATEGORY", denullify(lcategory));
553  query.bindValue(":CATTYPE", lcattype);
554  query.bindValue(":STARTTIME", starttime);
555  query.bindValue(":ENDTIME", endtime);
556  query.bindValue(":CC", lsubtype & SUB_HARDHEAR ? true : false);
557  query.bindValue(":HASSUBTITLES",lsubtype & SUB_NORMAL ? true : false);
558  query.bindValue(":STEREO", laudio & AUD_STEREO ? true : false);
559  query.bindValue(":HDTV", lvideo & VID_HDTV ? true : false);
560  query.bindValue(":SUBTYPE", lsubtype);
561  query.bindValue(":AUDIOPROP", laudio);
562  query.bindValue(":VIDEOPROP", lvideo);
563  query.bindValue(":PARTNO", lpartnumber);
564  query.bindValue(":PARTTOTAL", lparttotal);
565  query.bindValue(":SYNDICATENO", denullify(lsyndicatedepisodenumber));
566  query.bindValue(":AIRDATE", lairdate?QString::number(lairdate):"0000");
567  query.bindValue(":ORIGAIRDATE", loriginalairdate);
568  query.bindValue(":LSOURCE", llistingsource);
569  query.bindValue(":SERIESID", denullify(lseriesId));
570  query.bindValue(":PROGRAMID", denullify(lprogramId));
571  query.bindValue(":PREVSHOWN", lpreviouslyshown);
572 
573  if (!query.exec())
574  {
575  MythDB::DBError("InsertDB", query);
576  return 0;
577  }
578 
579  if (credits)
580  {
581  for (uint i = 0; i < credits->size(); i++)
582  (*credits)[i].InsertDB(query, chanid, starttime);
583  }
584 
585  QList<EventRating>::const_iterator j = ratings.begin();
586  for (; j != ratings.end(); ++j)
587  {
588  query.prepare(
589  "INSERT INTO programrating "
590  " ( chanid, starttime, system, rating) "
591  "VALUES (:CHANID, :START, :SYS, :RATING)");
592  query.bindValue(":CHANID", chanid);
593  query.bindValue(":START", starttime);
594  query.bindValue(":SYS", (*j).system);
595  query.bindValue(":RATING", (*j).rating);
596 
597  if (!query.exec())
598  MythDB::DBError("programrating insert", query);
599  }
600 
601  return 1;
602 }
603 
604 static bool delete_program(MSqlQuery &query, uint chanid, const QDateTime &st)
605 {
606  query.prepare(
607  "DELETE from program "
608  "WHERE chanid = :CHANID AND "
609  " starttime = :STARTTIME");
610 
611  query.bindValue(":CHANID", chanid);
612  query.bindValue(":STARTTIME", st);
613 
614  if (!query.exec())
615  {
616  MythDB::DBError("delete_program", query);
617  return false;
618  }
619 
620  query.prepare(
621  "DELETE from credits "
622  "WHERE chanid = :CHANID AND "
623  " starttime = :STARTTIME");
624 
625  query.bindValue(":CHANID", chanid);
626  query.bindValue(":STARTTIME", st);
627 
628  if (!query.exec())
629  {
630  MythDB::DBError("delete_credits", query);
631  return false;
632  }
633 
634  return true;
635 }
636 
637 static bool change_program(MSqlQuery &query, uint chanid, const QDateTime &st,
638  const QDateTime &new_st, const QDateTime &new_end)
639 {
640  query.prepare(
641  "UPDATE program "
642  "SET starttime = :NEWSTART, "
643  " endtime = :NEWEND "
644  "WHERE chanid = :CHANID AND "
645  " starttime = :OLDSTART");
646 
647  query.bindValue(":CHANID", chanid);
648  query.bindValue(":OLDSTART", st);
649  query.bindValue(":NEWSTART", new_st);
650  query.bindValue(":NEWEND", new_end);
651 
652  if (!query.exec())
653  {
654  MythDB::DBError("change_program", query);
655  return false;
656  }
657 
658  query.prepare(
659  "UPDATE credits "
660  "SET starttime = :NEWSTART "
661  "WHERE chanid = :CHANID AND "
662  " starttime = :OLDSTART");
663 
664  query.bindValue(":CHANID", chanid);
665  query.bindValue(":OLDSTART", st);
666  query.bindValue(":NEWSTART", new_st);
667 
668  if (!query.exec())
669  {
670  MythDB::DBError("change_credits", query);
671  return false;
672  }
673 
674  return true;
675 }
676 
677 bool DBEvent::MoveOutOfTheWayDB(
678  MSqlQuery &query, uint chanid, const DBEvent &prog) const
679 {
680  if (prog.starttime >= starttime && prog.endtime <= endtime)
681  {
682  // inside current program
683  return delete_program(query, chanid, prog.starttime);
684  }
685  else if (prog.starttime < starttime && prog.endtime > starttime)
686  {
687  // starts before, but ends during our program
688  return change_program(query, chanid, prog.starttime,
689  prog.starttime, starttime);
690  }
691  else if (prog.starttime < endtime && prog.endtime > endtime)
692  {
693  // starts during, but ends after our program
694  return change_program(query, chanid, prog.starttime,
695  endtime, prog.endtime);
696  }
697  // must be non-conflicting...
698  return true;
699 }
700 
701 uint DBEvent::InsertDB(MSqlQuery &query, uint chanid) const
702 {
703  query.prepare(
704  "REPLACE INTO program ("
705  " chanid, title, subtitle, description, "
706  " category, category_type, "
707  " starttime, endtime, "
708  " closecaptioned, stereo, hdtv, subtitled, "
709  " subtitletypes, audioprop, videoprop, "
710  " stars, partnumber, parttotal, "
711  " syndicatedepisodenumber, "
712  " airdate, originalairdate,listingsource, "
713  " seriesid, programid, previouslyshown ) "
714  "VALUES ("
715  " :CHANID, :TITLE, :SUBTITLE, :DESCRIPTION, "
716  " :CATEGORY, :CATTYPE, "
717  " :STARTTIME, :ENDTIME, "
718  " :CC, :STEREO, :HDTV, :HASSUBTITLES, "
719  " :SUBTYPES, :AUDIOPROP, :VIDEOPROP, "
720  " :STARS, :PARTNUMBER, :PARTTOTAL, "
721  " :SYNDICATENO, "
722  " :AIRDATE, :ORIGAIRDATE, :LSOURCE, "
723  " :SERIESID, :PROGRAMID, :PREVSHOWN) ");
724 
725  QString cattype = myth_category_type_to_string(categoryType);
726  QString empty("");
727  query.bindValue(":CHANID", chanid);
728  query.bindValue(":TITLE", denullify(title));
729  query.bindValue(":SUBTITLE", denullify(subtitle));
730  query.bindValue(":DESCRIPTION", denullify(description));
731  query.bindValue(":CATEGORY", denullify(category));
732  query.bindValue(":CATTYPE", cattype);
733  query.bindValue(":STARTTIME", starttime);
734  query.bindValue(":ENDTIME", endtime);
735  query.bindValue(":CC", subtitleType & SUB_HARDHEAR ? true : false);
736  query.bindValue(":STEREO", audioProps & AUD_STEREO ? true : false);
737  query.bindValue(":HDTV", videoProps & VID_HDTV ? true : false);
738  query.bindValue(":HASSUBTITLES",subtitleType & SUB_NORMAL ? true : false);
739  query.bindValue(":SUBTYPES", subtitleType);
740  query.bindValue(":AUDIOPROP", audioProps);
741  query.bindValue(":VIDEOPROP", videoProps);
742  query.bindValue(":STARS", stars);
743  query.bindValue(":PARTNUMBER", partnumber);
744  query.bindValue(":PARTTOTAL", parttotal);
745  query.bindValue(":SYNDICATENO", denullify(syndicatedepisodenumber));
746  query.bindValue(":AIRDATE", airdate ? QString::number(airdate):"0000");
747  query.bindValue(":ORIGAIRDATE", originalairdate);
748  query.bindValue(":LSOURCE", listingsource);
749  query.bindValue(":SERIESID", denullify(seriesId));
750  query.bindValue(":PROGRAMID", denullify(programId));
751  query.bindValue(":PREVSHOWN", previouslyshown);
752 
753  if (!query.exec())
754  {
755  MythDB::DBError("InsertDB", query);
756  return 0;
757  }
758 
759  if (credits)
760  {
761  for (uint i = 0; i < credits->size(); i++)
762  (*credits)[i].InsertDB(query, chanid, starttime);
763  }
764 
765  return 1;
766 }
767 
768 ProgInfo::ProgInfo(const ProgInfo &other) :
769  DBEvent(other.listingsource)
770 {
771  *this = other;
772 }
773 
774 ProgInfo &ProgInfo::operator=(const ProgInfo &other)
775 {
776  if (this == &other)
777  return *this;
778 
779  DBEvent::operator=(other);
780 
781  channel = other.channel;
782  startts = other.startts;
783  endts = other.endts;
784  stars = other.stars;
785  title_pronounce = other.title_pronounce;
786  showtype = other.showtype;
787  colorcode = other.colorcode;
788  clumpidx = other.clumpidx;
789  clumpmax = other.clumpmax;
790 
791  channel.squeeze();
792  startts.squeeze();
793  endts.squeeze();
794  stars.squeeze();
795  title_pronounce.squeeze();
796  showtype.squeeze();
797  colorcode.squeeze();
798  clumpidx.squeeze();
799  clumpmax.squeeze();
800 
801  return *this;
802 }
803 
804 void ProgInfo::Squeeze(void)
805 {
806  DBEvent::Squeeze();
807  channel.squeeze();
808  startts.squeeze();
809  endts.squeeze();
810  stars.squeeze();
811  title_pronounce.squeeze();
812  showtype.squeeze();
813  colorcode.squeeze();
814  clumpidx.squeeze();
815  clumpmax.squeeze();
816 }
817 
818 uint ProgInfo::InsertDB(MSqlQuery &query, uint chanid) const
819 {
820  LOG(VB_XMLTV, LOG_INFO,
821  QString("Inserting new program : %1 - %2 %3 %4")
822  .arg(starttime.toString(Qt::ISODate))
823  .arg(endtime.toString(Qt::ISODate))
824  .arg(channel)
825  .arg(title));
826 
827  query.prepare(
828  "REPLACE INTO program ("
829  " chanid, title, subtitle, description, "
830  " category, category_type, "
831  " starttime, endtime, "
832  " closecaptioned, stereo, hdtv, subtitled, "
833  " subtitletypes, audioprop, videoprop, "
834  " partnumber, parttotal, "
835  " syndicatedepisodenumber, "
836  " airdate, originalairdate,listingsource, "
837  " seriesid, programid, previouslyshown, "
838  " stars, showtype, title_pronounce, colorcode ) "
839 
840  "VALUES("
841  " :CHANID, :TITLE, :SUBTITLE, :DESCRIPTION, "
842  " :CATEGORY, :CATTYPE, "
843  " :STARTTIME, :ENDTIME, "
844  " :CC, :STEREO, :HDTV, :HASSUBTITLES, "
845  " :SUBTYPES, :AUDIOPROP, :VIDEOPROP, "
846  " :PARTNUMBER, :PARTTOTAL, "
847  " :SYNDICATENO, "
848  " :AIRDATE, :ORIGAIRDATE, :LSOURCE, "
849  " :SERIESID, :PROGRAMID, :PREVSHOWN, "
850  " :STARS, :SHOWTYPE, :TITLEPRON, :COLORCODE)");
851 
852  QString cattype = myth_category_type_to_string(categoryType);
853 
854  query.bindValue(":CHANID", chanid);
855  query.bindValue(":TITLE", denullify(title));
856  query.bindValue(":SUBTITLE", denullify(subtitle));
857  query.bindValue(":DESCRIPTION", denullify(description));
858  query.bindValue(":CATEGORY", denullify(category));
859  query.bindValue(":CATTYPE", cattype);
860  query.bindValue(":STARTTIME", starttime);
861  query.bindValue(":ENDTIME", endtime);
862  query.bindValue(":CC",
863  subtitleType & SUB_HARDHEAR ? true : false);
864  query.bindValue(":STEREO",
865  audioProps & AUD_STEREO ? true : false);
866  query.bindValue(":HDTV",
867  videoProps & VID_HDTV ? true : false);
868  query.bindValue(":HASSUBTITLES",
869  subtitleType & SUB_NORMAL ? true : false);
870  query.bindValue(":SUBTYPES", subtitleType);
871  query.bindValue(":AUDIOPROP", audioProps);
872  query.bindValue(":VIDEOPROP", videoProps);
873  query.bindValue(":PARTNUMBER", partnumber);
874  query.bindValue(":PARTTOTAL", parttotal);
875  query.bindValue(":SYNDICATENO", denullify(syndicatedepisodenumber));
876  query.bindValue(":AIRDATE", airdate ? QString::number(airdate):"0000");
877  query.bindValue(":ORIGAIRDATE", originalairdate);
878  query.bindValue(":LSOURCE", listingsource);
879  query.bindValue(":SERIESID", denullify(seriesId));
880  query.bindValue(":PROGRAMID", denullify(programId));
881  query.bindValue(":PREVSHOWN", previouslyshown);
882  query.bindValue(":STARS", stars);
883  query.bindValue(":SHOWTYPE", showtype);
884  query.bindValue(":TITLEPRON", title_pronounce);
885  query.bindValue(":COLORCODE", colorcode);
886 
887  if (!query.exec())
888  {
889  MythDB::DBError("program insert", query);
890  return 0;
891  }
892 
893  QList<EventRating>::const_iterator j = ratings.begin();
894  for (; j != ratings.end(); ++j)
895  {
896  query.prepare(
897  "INSERT INTO programrating "
898  " ( chanid, starttime, system, rating) "
899  "VALUES (:CHANID, :START, :SYS, :RATING)");
900  query.bindValue(":CHANID", chanid);
901  query.bindValue(":START", starttime);
902  query.bindValue(":SYS", (*j).system);
903  query.bindValue(":RATING", (*j).rating);
904 
905  if (!query.exec())
906  MythDB::DBError("programrating insert", query);
907  }
908 
909  if (credits)
910  {
911  for (uint i = 0; i < credits->size(); ++i)
912  (*credits)[i].InsertDB(query, chanid, starttime);
913  }
914 
915  return 1;
916 }
917 
918 bool ProgramData::ClearDataByChannel(
919  uint chanid, const QDateTime &from, const QDateTime &to,
920  bool use_channel_time_offset)
921 {
922  int secs = 0;
923  if (use_channel_time_offset)
924  secs = ChannelUtil::GetTimeOffset(chanid) * 60;
925 
926  QDateTime newFrom = from.addSecs(secs);
927  QDateTime newTo = to.addSecs(secs);
928 
929  MSqlQuery query(MSqlQuery::InitCon());
930  query.prepare("DELETE FROM program "
931  "WHERE starttime >= :FROM AND starttime < :TO "
932  "AND chanid = :CHANID ;");
933  query.bindValue(":FROM", newFrom);
934  query.bindValue(":TO", newTo);
935  query.bindValue(":CHANID", chanid);
936  bool ok = query.exec();
937 
938  query.prepare("DELETE FROM programrating "
939  "WHERE starttime >= :FROM AND starttime < :TO "
940  "AND chanid = :CHANID ;");
941  query.bindValue(":FROM", newFrom);
942  query.bindValue(":TO", newTo);
943  query.bindValue(":CHANID", chanid);
944  ok &= query.exec();
945 
946  query.prepare("DELETE FROM credits "
947  "WHERE starttime >= :FROM AND starttime < :TO "
948  "AND chanid = :CHANID ;");
949  query.bindValue(":FROM", newFrom);
950  query.bindValue(":TO", newTo);
951  query.bindValue(":CHANID", chanid);
952  ok &= query.exec();
953 
954  query.prepare("DELETE FROM programgenres "
955  "WHERE starttime >= :FROM AND starttime < :TO "
956  "AND chanid = :CHANID ;");
957  query.bindValue(":FROM", newFrom);
958  query.bindValue(":TO", newTo);
959  query.bindValue(":CHANID", chanid);
960  ok &= query.exec();
961 
962  return ok;
963 }
964 
965 bool ProgramData::ClearDataBySource(
966  uint sourceid, const QDateTime &from, const QDateTime &to,
967  bool use_channel_time_offset)
968 {
969  vector<uint> chanids = ChannelUtil::GetChanIDs(sourceid);
970 
971  bool ok = true;
972  for (uint i = 0; i < chanids.size(); i++)
973  ok &= ClearDataByChannel(chanids[i], from, to, use_channel_time_offset);
974 
975  return ok;
976 }
977 
978 static bool start_time_less_than(const DBEvent *a, const DBEvent *b)
979 {
980  return (a->starttime < b->starttime);
981 }
982 
983 void ProgramData::FixProgramList(QList<ProgInfo*> &fixlist)
984 {
985  qStableSort(fixlist.begin(), fixlist.end(), start_time_less_than);
986 
987  QList<ProgInfo*>::iterator it = fixlist.begin();
988  while (1)
989  {
990  QList<ProgInfo*>::iterator cur = it;
991  ++it;
992 
993  // fill in miss stop times
994  if ((*cur)->endts.isEmpty() || (*cur)->startts > (*cur)->endts)
995  {
996  if (it != fixlist.end())
997  {
998  (*cur)->endts = (*it)->startts;
999  (*cur)->endtime = (*it)->starttime;
1000  }
1001  else
1002  {
1003  (*cur)->endtime = (*cur)->starttime;
1004  if ((*cur)->endtime < QDateTime(
1005  (*cur)->endtime.date(), QTime(6, 0), Qt::UTC))
1006  {
1007  (*cur)->endtime = QDateTime(
1008  (*cur)->endtime.date(), QTime(6, 0), Qt::UTC);
1009  }
1010  else
1011  {
1012  (*cur)->endtime = QDateTime(
1013  (*cur)->endtime.date().addDays(1),
1014  QTime(0, 0), Qt::UTC);
1015  }
1016 
1017  (*cur)->endts =
1018  MythDate::toString((*cur)->endtime, MythDate::kFilename);
1019  }
1020  }
1021 
1022  if (it == fixlist.end())
1023  break;
1024 
1025  // remove overlapping programs
1026  if ((*cur)->HasTimeConflict(**it))
1027  {
1028  QList<ProgInfo*>::iterator tokeep, todelete;
1029 
1030  if ((*cur)->endtime <= (*cur)->starttime)
1031  tokeep = it, todelete = cur;
1032  else if ((*it)->endtime <= (*it)->starttime)
1033  tokeep = cur, todelete = it;
1034  else if (!(*cur)->subtitle.isEmpty() &&
1035  (*it)->subtitle.isEmpty())
1036  tokeep = cur, todelete = it;
1037  else if (!(*it)->subtitle.isEmpty() &&
1038  (*cur)->subtitle.isEmpty())
1039  tokeep = it, todelete = cur;
1040  else if (!(*cur)->description.isEmpty() &&
1041  (*it)->description.isEmpty())
1042  tokeep = cur, todelete = it;
1043  else
1044  tokeep = it, todelete = cur;
1045 
1046 
1047  LOG(VB_XMLTV, LOG_INFO,
1048  QString("Removing conflicting program: %1 - %2 %3 %4")
1049  .arg((*todelete)->starttime.toString(Qt::ISODate))
1050  .arg((*todelete)->endtime.toString(Qt::ISODate))
1051  .arg((*todelete)->channel)
1052  .arg((*todelete)->title));
1053 
1054  LOG(VB_XMLTV, LOG_INFO,
1055  QString("Conflicted with : %1 - %2 %3 %4")
1056  .arg((*tokeep)->starttime.toString(Qt::ISODate))
1057  .arg((*tokeep)->endtime.toString(Qt::ISODate))
1058  .arg((*tokeep)->channel)
1059  .arg((*tokeep)->title));
1060 
1061  bool step_back = todelete == it;
1062  it = fixlist.erase(todelete);
1063  if (step_back)
1064  --it;
1065  }
1066  }
1067 }
1068 
1069 void ProgramData::HandlePrograms(
1070  uint sourceid, QMap<QString, QList<ProgInfo> > &proglist)
1071 {
1072  uint unchanged = 0, updated = 0;
1073 
1074  MSqlQuery query(MSqlQuery::InitCon());
1075 
1076  QMap<QString, QList<ProgInfo> >::const_iterator mapiter;
1077  for (mapiter = proglist.begin(); mapiter != proglist.end(); ++mapiter)
1078  {
1079  if (mapiter.key().isEmpty())
1080  continue;
1081 
1082  query.prepare(
1083  "SELECT chanid "
1084  "FROM channel "
1085  "WHERE sourceid = :ID AND "
1086  " xmltvid = :XMLTVID");
1087  query.bindValue(":ID", sourceid);
1088  query.bindValue(":XMLTVID", mapiter.key());
1089 
1090  if (!query.exec())
1091  {
1092  MythDB::DBError("ProgramData::HandlePrograms", query);
1093  continue;
1094  }
1095 
1096  vector<uint> chanids;
1097  while (query.next())
1098  chanids.push_back(query.value(0).toUInt());
1099 
1100  if (chanids.empty())
1101  {
1102  LOG(VB_GENERAL, LOG_NOTICE,
1103  QString("Unknown xmltv channel identifier: %1"
1104  " - Skipping channel.").arg(mapiter.key()));
1105  continue;
1106  }
1107 
1108  QList<ProgInfo> &list = proglist[mapiter.key()];
1109  QList<ProgInfo*> sortlist;
1110  QList<ProgInfo>::iterator it = list.begin();
1111  for (; it != list.end(); ++it)
1112  sortlist.push_back(&(*it));
1113 
1114  FixProgramList(sortlist);
1115 
1116  for (uint i = 0; i < chanids.size(); ++i)
1117  {
1118  HandlePrograms(query, chanids[i], sortlist, unchanged, updated);
1119  }
1120  }
1121 
1122  LOG(VB_GENERAL, LOG_INFO,
1123  QString("Updated programs: %1 Unchanged programs: %2")
1124  .arg(updated) .arg(unchanged));
1125 }
1126 
1127 void ProgramData::HandlePrograms(MSqlQuery &query,
1128  uint chanid,
1129  const QList<ProgInfo*> &sortlist,
1130  uint &unchanged,
1131  uint &updated)
1132 {
1133  QList<ProgInfo*>::const_iterator it = sortlist.begin();
1134  for (; it != sortlist.end(); ++it)
1135  {
1136  if (IsUnchanged(query, chanid, **it))
1137  {
1138  unchanged++;
1139  continue;
1140  }
1141 
1142  if (!DeleteOverlaps(query, chanid, **it))
1143  continue;
1144 
1145  updated += (*it)->InsertDB(query, chanid);
1146  }
1147 }
1148 
1149 int ProgramData::fix_end_times(void)
1150 {
1151  int count = 0;
1152  QString chanid, starttime, endtime, querystr;
1153  MSqlQuery query1(MSqlQuery::InitCon()), query2(MSqlQuery::InitCon());
1154 
1155  querystr = "SELECT chanid, starttime, endtime FROM program "
1156  "WHERE (DATE_FORMAT(endtime,'%H%i') = '0000') "
1157  "ORDER BY chanid, starttime;";
1158 
1159  if (!query1.exec(querystr))
1160  {
1161  LOG(VB_GENERAL, LOG_ERR,
1162  QString("fix_end_times query failed: %1").arg(querystr));
1163  return -1;
1164  }
1165 
1166  while (query1.next())
1167  {
1168  starttime = query1.value(1).toString();
1169  chanid = query1.value(0).toString();
1170  endtime = query1.value(2).toString();
1171 
1172  querystr = QString("SELECT chanid, starttime, endtime FROM program "
1173  "WHERE starttime BETWEEN '%1 00:00:00'"
1174  "AND '%2 23:59:59' AND chanid = '%3' "
1175  "ORDER BY starttime LIMIT 1;")
1176  .arg(endtime.left(10))
1177  .arg(endtime.left(10))
1178  .arg(chanid);
1179 
1180  if (!query2.exec(querystr))
1181  {
1182  LOG(VB_GENERAL, LOG_ERR,
1183  QString("fix_end_times query failed: %1").arg(querystr));
1184  return -1;
1185  }
1186 
1187  if (query2.next() && (endtime != query2.value(1).toString()))
1188  {
1189  count++;
1190  endtime = query2.value(1).toString();
1191  querystr = QString("UPDATE program SET starttime = '%1', "
1192  "endtime = '%2' WHERE (chanid = '%3' AND "
1193  "starttime = '%4');")
1194  .arg(starttime)
1195  .arg(endtime)
1196  .arg(chanid)
1197  .arg(starttime);
1198 
1199  if (!query2.exec(querystr))
1200  {
1201  LOG(VB_GENERAL, LOG_ERR,
1202  QString("fix_end_times query failed: %1").arg(querystr));
1203  return -1;
1204  }
1205  }
1206  }
1207 
1208  return count;
1209 }
1210 
1211 bool ProgramData::IsUnchanged(
1212  MSqlQuery &query, uint chanid, const ProgInfo &pi)
1213 {
1214  query.prepare(
1215  "SELECT count(*) "
1216  "FROM program "
1217  "WHERE chanid = :CHANID AND "
1218  " starttime = :START AND "
1219  " endtime = :END AND "
1220  " title = :TITLE AND "
1221  " subtitle = :SUBTITLE AND "
1222  " description = :DESC AND "
1223  " category = :CATEGORY AND "
1224  " category_type = :CATEGORY_TYPE AND "
1225  " airdate = :AIRDATE AND "
1226  " stars >= (:STARS1 - 0.001) AND "
1227  " stars <= (:STARS2 + 0.001) AND "
1228  " previouslyshown = :PREVIOUSLYSHOWN AND "
1229  " title_pronounce = :TITLE_PRONOUNCE AND "
1230  " audioprop = :AUDIOPROP AND "
1231  " videoprop = :VIDEOPROP AND "
1232  " subtitletypes = :SUBTYPES AND "
1233  " partnumber = :PARTNUMBER AND "
1234  " parttotal = :PARTTOTAL AND "
1235  " seriesid = :SERIESID AND "
1236  " showtype = :SHOWTYPE AND "
1237  " colorcode = :COLORCODE AND "
1238  " syndicatedepisodenumber = :SYNDICATEDEPISODENUMBER AND "
1239  " programid = :PROGRAMID");
1240 
1241  QString cattype = myth_category_type_to_string(pi.categoryType);
1242 
1243  query.bindValue(":CHANID", chanid);
1244  query.bindValue(":START", pi.starttime);
1245  query.bindValue(":END", pi.endtime);
1246  query.bindValue(":TITLE", denullify(pi.title));
1247  query.bindValue(":SUBTITLE", denullify(pi.subtitle));
1248  query.bindValue(":DESC", denullify(pi.description));
1249  query.bindValue(":CATEGORY", denullify(pi.category));
1250  query.bindValue(":CATEGORY_TYPE", cattype);
1251  query.bindValue(":AIRDATE", pi.airdate);
1252  query.bindValue(":STARS1", pi.stars);
1253  query.bindValue(":STARS2", pi.stars);
1254  query.bindValue(":PREVIOUSLYSHOWN", pi.previouslyshown);
1255  query.bindValue(":TITLE_PRONOUNCE", pi.title_pronounce);
1256  query.bindValue(":AUDIOPROP", pi.audioProps);
1257  query.bindValue(":VIDEOPROP", pi.videoProps);
1258  query.bindValue(":SUBTYPES", pi.subtitleType);
1259  query.bindValue(":PARTNUMBER", pi.partnumber);
1260  query.bindValue(":PARTTOTAL", pi.parttotal);
1261  query.bindValue(":SERIESID", denullify(pi.seriesId));
1262  query.bindValue(":SHOWTYPE", pi.showtype);
1263  query.bindValue(":COLORCODE", pi.colorcode);
1264  query.bindValue(":SYNDICATEDEPISODENUMBER",
1265  denullify(pi.syndicatedepisodenumber));
1266  query.bindValue(":PROGRAMID", denullify(pi.programId));
1267 
1268  if (query.exec() && query.next())
1269  return query.value(0).toUInt() > 0;
1270 
1271  return false;
1272 }
1273 
1274 bool ProgramData::DeleteOverlaps(
1275  MSqlQuery &query, uint chanid, const ProgInfo &pi)
1276 {
1277  if (VERBOSE_LEVEL_CHECK(VB_XMLTV, LOG_INFO))
1278  {
1279  // Get overlaps..
1280  query.prepare(
1281  "SELECT title,starttime,endtime "
1282  "FROM program "
1283  "WHERE chanid = :CHANID AND "
1284  " starttime >= :START AND "
1285  " starttime < :END;");
1286  query.bindValue(":CHANID", chanid);
1287  query.bindValue(":START", pi.starttime);
1288  query.bindValue(":END", pi.endtime);
1289 
1290  if (!query.exec())
1291  return false;
1292 
1293  if (!query.next())
1294  return true;
1295 
1296  do
1297  {
1298  LOG(VB_XMLTV, LOG_INFO,
1299  QString("Removing existing program: %1 - %2 %3 %4")
1300  .arg(MythDate::as_utc(query.value(1).toDateTime()).toString(Qt::ISODate))
1301  .arg(MythDate::as_utc(query.value(2).toDateTime()).toString(Qt::ISODate))
1302  .arg(pi.channel)
1303  .arg(query.value(0).toString()));
1304  } while (query.next());
1305  }
1306 
1307  if (!ClearDataByChannel(chanid, pi.starttime, pi.endtime, false))
1308  {
1309  LOG(VB_XMLTV, LOG_ERR,
1310  QString("Program delete failed : %1 - %2 %3 %4")
1311  .arg(pi.starttime.toString(Qt::ISODate))
1312  .arg(pi.endtime.toString(Qt::ISODate))
1313  .arg(pi.channel)
1314  .arg(pi.title));
1315  return false;
1316  }
1317 
1318  return true;
1319 }
Generated on Sun May 26 2013 01:00:10 for MythTV by   doxygen 1.8.1.2