SQL help needed

[bgowland]

I'm currently developing the dvbt radio plugin to maintain a 7 day radio EPG in a SQLite DB (that's the easy bit).

The problem I've got is I want to extract the Now/Next info from the DB, i.e., assuming I've got a full 7 days of EPG, as soon as I start the plugin, I can identify the Now/Next playing radio shows without having to scavenge the dvbt stream.

I can get the 'Now' info with the following (example) SQL statement

Code:

SELECT PROGRAMMES.service_id, PROGRAMMES.title, PROGRAMMES.description, PROGRAMMES.start_time, PROGRAMMES.end_time FROM PROGRAMMES WHERE (start_time <= '2008-05-24 22:50:40Z') AND (end_time > '2008-05-24 22:50:40Z')

The start and end times are created using

Code:

DateTime.Now.ToString("u")

This works fine and I can pull all of the Now playing shows into a DataSet.

The problem is getting the Next shows. I get the DataSet to create a DataTableReader and step through the rows. For each row, I then query the DB with the following example

Code:

SELECT PROGRAMMES.service_id, PROGRAMMES.title, PROGRAMMES.description, PROGRAMMES.start_time, PROGRAMMES.end_time FROM PROGRAMMES WHERE (service_id = '18496') AND (start_time = '2008-05-24 23:00:00Z')

In this case, the service_id matches that of the row returned by the DataSet's DataTableReader and the start_time is the end_time of that show. In other words, the Next show is the one that starts at the end_time of a Now playing show (obviously).

The problem is, I don't get any results returned despite the fact that there are shows in the DB which match the service_id and start_time that I'm passing in the SQL statement.

Any ideas?

Cheers,
Brian

[Graham]

Health Warning: My SQL experience is limited and rusty and probably best ignored.

I quite like the idea of views and/or sub queries.

Create view Now as select blah from blah where start_time is less than or equal to "now" and end_time is greater than "now"

Select blah from blah where channel = blah and start_time equals (select end_time from Now where channel = blah)

A view or a sub-query or both might help split one big complicated query into smaller simpler queries.

Apologies if I have missed the point.

Good luck

Graham

[bgowland]

Health Warning: My SQL experience is limited and rusty and probably best ignored.

I'm by no means an expert in SQL and find it constanly frustrating - sometimes I can run off a pretty complex SQL query and it works first time. Then the simplest things just refuse to work.

It appears the query for the 'Next' service_id/start_time doesn't like the datetime format despite it being exactly the same as the one used when I get the 'Now' programmes. I experimented with simply querying for anything with a known start_time and it still returned no results.

I've put in a workaround. I pull all programmes with the same service_id then I just step through comparing the end of the Now programme with the start of each Next programme, converting the DATETIME values to strings. Annoying - it works once I've got the data but not in the query.

I wish I could work out how to setup stored procedures in SQLite - it'd make things easier.

Cheers,
Brian

[Ommina]

No stored procedures in SQLite -- so you're off the hook for figuring out how to make them.

How are start_time and end_time stored in the database? Depending on what format they are in, you maybe be able to get away with something like:

Code:

SELECT PROGRAMMES.service_id, PROGRAMMES.title, PROGRAMMES.description, PROGRAMMES.start_time, PROGRAMMES.end_time FROM PROGRAMMES WHERE datetime('now') Between start_time and end_time;

SQLite will change datetime('now') to, well, the date and time as of, er, now, so you don't have to build any SQL statements on-the-fly. (This is also very effective for querying sub's Recording_Schedule table to see if something is recording.)

As for the next show. Hm. How about:

Code:

Select {Stuff} From Programmes Where start_time > datetime('now') Order By start_time Limit 1

That should give you one record, with the details of the next programme, and no stepping through rows required.

caveat: it will still depend on what format the start and end times are in the database. "Additional futzing may be required."

[bgowland]

No stored procedures in SQLite -- so you're off the hook for figuring out how to make them.

I thought I'd read somewhere that SQLite doesn't support stored procedures - thanks for confirming.

How are start_time and end_time stored in the database? Depending on what format they are in, you maybe be able to get away with something like:

Now we're talking!!!!

Yep, the start_time and end_time fields are DATETIME data types so your datetime('now') example works like a charm. Also for the 'Next' stuff, I still need to check the start time coincides with the end time of the Now programme but you're right - I don't need to cycle through loads of results when using 'Order By start_time Limit 1'. Thanks.


Cheers,
Brian