Opened 11 years ago

Closed 9 years ago

#6060 closed enhancement (wontfix)

Add SQL_QUERY command to protocol for running mysql queries

Reported by: lpgcritter@… Owned by: Isaac Richards
Priority: minor Milestone: unknown
Component: MythTV - General Version: unknown
Severity: medium Keywords: protocol mysql
Cc: Ticket locked: no

Description

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

Attachments (1)

sql_query.patch (2.6 KB) - added by lpgcritter@… 11 years ago.
SQL_QUERY patch against trunk

Download all attachments as: .zip

Change History (8)

comment:1 Changed 11 years ago by stuartm

Resolution: invalid
Status: newclosed

Feature request without a patch.

Changed 11 years ago by lpgcritter@…

Attachment: sql_query.patch added

SQL_QUERY patch against trunk

comment:2 Changed 11 years ago by lpgcritter@…

Patch now attached

comment:3 Changed 11 years ago by Janne Grunau

Resolution: invalid
Status: closednew

comment:4 Changed 10 years ago by stuartm

Milestone: 0.22unknown

comment:5 Changed 10 years ago by stuartm

Component: mythtvMythTV - General

comment:6 Changed 9 years ago by Rob Smith

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

comment:7 Changed 9 years ago by robertm

Resolution: wontfix
Status: newclosed

[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.

Note: See TracTickets for help on using tickets.