Add SQL_QUERY command to protocol for running mysql queries

[lpgcritter@…]

There are currently a lot of operations that can't be done by a custom frontend without directly accessing the MYSQL database. This means that any custom frontend will need a dependency on mysql client libraries. It also means that the mysql server will need to be reconfigured to listen on the network interface.

I propose that a SQL_QUERY command be added to the mythtv protocol that can execute SQL and receive results from the mythtv database.

Example:

SELECT * FROM recordedseek WHERE chanid=1003 AND starttime='2008-11-17 21:00:00' LIMIT 3;
+--------+---------------------+------+--------+------+
| chanid | starttime           | mark | offset | type |
+--------+---------------------+------+--------+------+
|   1003 | 2008-11-17 21:00:00 |    0 |    376 |    9 |
|   1003 | 2008-11-17 21:00:00 |   12 | 356072 |    9 |
|   1003 | 2008-11-17 21:00:00 |   24 | 708760 |    9 |
+--------+---------------------+------+--------+------+

Frontend sends

SQL_QUERY[]:[]SELECT * FROM recordedseek WHERE chanid=1003 AND starttime='2008-11-17 21:00:00' LIMIT 3

Backend sends

3[]:[]1003[]:[]2008-11-17 21:00:00[]:[]0[]:[]376[]:[]9[]:[]1003[]:[]2008-11-17 21:00:00[]:[]12[]:[]356072[]:[]9[]:[]1003[]:[]2008-11-17 21:00:00[]:[]24[]:[]708760[]:[]9

The first param is the number of rows, the subsequent params are the row data.

For INSERT, UPDATE, DELETE, the result will only have one param and that is the number of rows affected.

On error it sends

-1[]:[]Error message here

[stuartm]

Feature request without a patch.

[lpgcritter@…]

SQL_QUERY patch against trunk

[lpgcritter@…]

Patch now attached

[Rob Smith]

It would be nice to have it as row count[]:[]column count[]:[]data

[robertm]

[TICKET CLEANUP] I do think something like this will ultimately be adopted when we go to an embedded DB. Until then, as this is a stale ticket with no ownership or obvious interest, I am closing it.