[mythtv] patch: airdate/stars/ratings
Andy Davidoff
dert at pobox.com
Sun Mar 2 20:36:12 EST 2003
This patch causes mythfilldatabase to parse and insert the
original airdate (for episodic programs) or release year
(for movies), the "star rating" (for movies), and the
content ratings. Currently, the content rating schema is
very flexible. It works for MPAA and VCHIP ratings in my
NTSC/cable programming. Let me know if it doesn't work for
other rating systems -- it will only accept one rating per
rating system.
The "stars" are averaged from (any) multiple rating sources,
though I've never seen more than one. To calculate the
number of stars, you will therefore need to supply the
maximum value on your scale. The XML from the grabber is
ambiguous on whether multiple star ratings may exist and
whether they may use different scales.
Here's an example query that demonstrates getting data for
a movie on a "5 star" scale:
mysql> select title,round(stars*5) as 'stars',airdate, \
programrating.system,programrating.rating from program \
left join programrating using (chanid,starttime) where \
program.chanid = 1366 and program.starttime = 20030303150000;
+---------------------+-------+---------+--------+--------+
| title | stars | airdate | system | rating |
+---------------------+-------+---------+--------+--------+
| Doctor in the House | 4 | 1954 | MPAA | NR |
| Doctor in the House | 4 | 1954 | VCHIP | PG |
+---------------------+-------+---------+--------+--------+
2 rows in set (0.00 sec)
-------------- next part --------------
Index: MC/programs/mythfilldatabase/filldata.cpp
===================================================================
RCS file: /var/lib/cvs/MC/programs/mythfilldatabase/filldata.cpp,v
retrieving revision 1.43
diff -d -u -w -r1.43 filldata.cpp
--- MC/programs/mythfilldatabase/filldata.cpp 23 Feb 2003 15:13:34 -0000 1.43
+++ MC/programs/mythfilldatabase/filldata.cpp 3 Mar 2003 01:19:44 -0000
@@ -50,6 +50,12 @@
QString finetune;
};
+struct ProgRating
+{
+ QString system;
+ QString rating;
+};
+
class ProgInfo
{
public:
@@ -63,6 +69,9 @@
category = other.category;
start = other.start;
end = other.end;
+ airdate = other.airdate;
+ stars = other.stars;
+ ratings = other.ratings;
}
QString startts;
@@ -72,6 +81,9 @@
QString subtitle;
QString desc;
QString category;
+ QString airdate;
+ QString stars;
+ QValueList<ProgRating> ratings;
QDateTime start;
QDateTime end;
@@ -301,6 +313,50 @@
{
pginfo->category = getFirstText(info);
}
+ else if (info.tagName() == "date" && pginfo->airdate == "")
+ {
+ pginfo->airdate = getFirstText(info);
+
+ if (4 != pginfo->airdate.length())
+ pginfo->airdate = "";
+ }
+ else if (info.tagName() == "star-rating")
+ {
+ QDomNodeList values = info.elementsByTagName("value");
+ QDomElement item;
+ QString stars, num, den;
+ float avg = 0.0;
+ // not sure why the XML suggests multiple ratings,
+ // but the following will average them anyway.
+ for (unsigned int i = 0; i < values.length(); i++)
+ {
+ item = values.item(i).toElement();
+ if (item.isNull())
+ continue;
+ stars = getFirstText(item);
+ num = stars.section('/', 0, 0);
+ den = stars.section('/', 1, 1);
+ if (0.0 >= den.toFloat())
+ continue;
+ avg *= i/(i+1);
+ avg += (num.toFloat()/den.toFloat()) / (i+1);
+ }
+ pginfo->stars.setNum(avg);
+ }
+ else if (info.tagName() == "rating")
+ {
+ // again, the structure of ratings seems poorly represented
+ // in the XML. no idea what we'd do with multiple values.
+ QDomNodeList values = info.elementsByTagName("value");
+ QDomElement item = values.item(0).toElement();
+ if (item.isNull())
+ continue;
+ ProgRating rating;
+ rating.system = info.attribute("system", "");
+ rating.rating = getFirstText(item);
+ if ("" != rating.system)
+ pginfo->ratings.append(rating);
+ }
}
}
@@ -713,14 +769,19 @@
nextoffset = 10;
}
+ QSqlQuery query;
QString querystr;
+
querystr.sprintf("DELETE FROM program WHERE starttime >= "
"DATE_ADD(CURRENT_DATE, INTERVAL %d DAY) "
"AND starttime < DATE_ADD(CURRENT_DATE, INTERVAL "
"%d DAY) AND chanid = %d;", offset, nextoffset, chanid);
+ query.exec(querystr);
- QSqlQuery query;
-
+ querystr.sprintf("DELETE FROM programrating WHERE starttime >= "
+ "DATE_ADD(CURRENT_DATE, INTERVAL %d DAY) "
+ "AND starttime < DATE_ADD(CURRENT_DATE, INTERVAL "
+ "%d DAY) AND chanid = %d;", offset, nextoffset, chanid);
query.exec(querystr);
}
@@ -769,23 +830,45 @@
(*i).title.replace(QRegExp("\""), QString("\\\""));
(*i).subtitle.replace(QRegExp("\""), QString("\\\""));
(*i).desc.replace(QRegExp("\""), QString("\\\""));
+ if ("" == (*i).airdate)
+ (*i).airdate = "0";
+ if ("" == (*i).stars)
+ (*i).stars = "0";
querystr.sprintf("INSERT INTO program (chanid,starttime,endtime,"
- "title,subtitle,description,category) VALUES(%d,"
- " \"%s\", \"%s\", \"%s\", \"%s\", \"%s\","
- " \"%s\");",
+ "title,subtitle,description,category,airdate,stars) "
+ "VALUES(%d, \"%s\", \"%s\", \"%s\", \"%s\", \"%s\","
+ " \"%s\", \"%s\", \"%s\");",
chanid,
(*i).start.toString("yyyyMMddhhmmss").ascii(),
(*i).end.toString("yyyyMMddhhmmss").ascii(),
(*i).title.utf8().data(),
(*i).subtitle.utf8().data(),
(*i).desc.utf8().data(),
- (*i).category.utf8().data());
+ (*i).category.utf8().data(),
+ (*i).airdate.utf8().data(),
+ (*i).stars.utf8().data());
if (!query.exec(querystr.utf8().data()))
{
MythContext::DBError("program insert", query);
}
+
+ QValueList<ProgRating>::iterator j = (*i).ratings.begin();
+ for (; j != (*i).ratings.end(); j++)
+ {
+ querystr.sprintf("INSERT INTO programrating (chanid,starttime,"
+ "system,rating) VALUES (%d, \"%s\", \"%s\", \"%s\");",
+ chanid,
+ (*i).start.toString("yyyyMMddhhmmss").ascii(),
+ (*j).system.utf8().data(),
+ (*j).rating.utf8().data());
+
+ if (!query.exec(querystr.utf8().data()))
+ {
+ MythContext::DBError("programrating insert", query);
+ }
+ }
}
}
}
@@ -852,10 +935,15 @@
void clearOldDBEntries(void)
{
- QString querystr = "DELETE FROM program WHERE starttime <= "
- "DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);";
QSqlQuery query;
+ QString querystr;
+ querystr = "DELETE FROM program WHERE starttime <= "
+ "DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);";
+ query.exec(querystr);
+
+ querystr = "DELETE FROM programrating WHERE starttime <= "
+ "DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);";
query.exec(querystr);
}
-------------- next part --------------
drop table if exists programrating;
CREATE TABLE programrating (
chanid int(10) unsigned NOT NULL default '0',
starttime timestamp(14) NOT NULL,
system char(8) NOT NULL default '',
rating char(8) NOT NULL default '',
UNIQUE KEY chanid (chanid,starttime,system,rating)
)
alter table program add airdate year not null, add stars float unsigned not null;
More information about the mythtv-dev
mailing list