[mythtv-users] mythtv dropping mysql???

[Robert Kulagowski]

On Mon, Oct 20, 2014 at 10:59 PM, Tom Dexter <digitalaudiorock at gmail.com> wrote:
> I'd assume that all the underlying SQL would be the same regardless of
> which was in use(??).

[No flamewar, for real. The JSON service is for early adopters; if the
new DD-XML service gives you what you need, then that's that.]

I just finished implementing sqlite implementation for the Schedules
Direct JSON grabber, so that MySQL or MariaDB (or MythTV) are no
longer required. I found that there are enough differences that it
wasn't an impossible task, but it took me a weekend to understand that
even though PHP had a PDO interface to the underlying database, there
are still syntactical differences. There are certain generic SQL
things that don't matter based on the backend, but some things do.
sqlite doesn't have a ON DUPLICATE/UPSERT logic, so you've got to work
around it. Plus others.

There's been a Google Summer of Code that investigated an embedded
database for MythTV, but since that was years ago and MythTV isn't
using an embedded database, there was probably implementation or
performance issues.

If the mythtv-devs decide to go to an embedded database, then I
believe it should be adequate; I just benchmarked an empty sqlite
database for the JSON grabber, worst case scenario, and run times are
reasonable: (5400 RPM magnetic storage on a dev box)

2014-10-21 04:36:05:Determining if there are updated schedules.
2014-10-21 04:36:09:1290 schedules to download.
2014-10-21 04:36:25:Writing to /tmp/mfdb_schedules_LKLwMi/schedule.json
2014-10-21 04:36:25:Parsing schedule for stationID:10021
...
2014-10-21 04:36:29:Parsing schedule for stationID:89839
2014-10-21 04:36:29:There are 52959 programIDs in the upcoming schedule.
2014-10-21 04:36:29:Retrieving existing MD5 values from local cache.
2014-10-21 04:36:29:Need to download 52959 new or updated programs.
2014-10-21 04:36:29:Requesting more than 10000 programs. Please be patient.
2014-10-21 04:36:29:Maximum number of programs we're downloading per call: 2000
2014-10-21 04:36:29:Retrieving chunk 1 of 27.
2014-10-21 04:36:37:Retrieving chunk 2 of 27.
2014-10-21 04:36:42:Retrieving chunk 3 of 27.
2014-10-21 04:36:50:Retrieving chunk 4 of 27.
2014-10-21 04:36:54:Retrieving chunk 5 of 27.
...
2014-10-21 04:39:36:Retrieving chunk 27 of 27.
2014-10-21 04:39:39:Performing inserts of JSON data.
2014-10-21 04:39:40:100 / 52959
...
2014-10-21 04:40:19:52800 / 52959
2014-10-21 04:40:19:52900 / 52959
2014-10-21 04:40:19:Completed local database program updates.

So it's possible to put 50K schedules+programs into a database in  ~4
minutes at which point the reschedule time is constrained by local
resources.