Can you Browse the SQLite DataBase?

[Pioneer4x4]

Can you Browse the SQLite DataBase like you can an access database?
I used to search for all of my shows ahead of time in the .mdb and edit the subtitle field (with episode number, notes...) so once the do record and get remaned, the file and description will have the information I need.

I should add Browse, and sort, and enter data. I found "SQLite Database Browser" but it doesn't appear to sort.
I need to sort the PROGRAMME table by "name" field, then edit the data in the "sub_title" field.

[UncleJohnsBand]

Can you Browse the SQLite DataBase like you can an access database?
I used to search for all of my shows ahead of time in the .mdb and edit the subtitle field (with episode number, notes...) so once the do record and get remaned, the file and description will have the information I need.

I should add Browse, and sort, and enter data. I found "SQLite Database Browser" but it doesn't appear to sort.
I need to sort the PROGRAMME table by "name" field, then edit the data in the "sub_title" field.

I use Sqlite Admin, Sqlite Database Browser and SqliteSpy.....I like them all.

[Pioneer4x4]

I use Sqlite Admin, Sqlite Database Browser and SqliteSpy.....I like them all.

I tried all 3
Sqlite Database Browser will let me edit data.
SqliteSpy will let me view and sort the table the way I like, but not apply any changes.
And I guess I just can't figure out anything in Sqlite Admin

Do I have to write sql code to do this?

[Pioneer4x4]

Do I have to write sql code to do this?

I have been putzing with it.
Using SQLite Database Browser on a copy of GBPVR's db3, I have run sql commands such as

UPDATE PROGRAMME
SET sub_title=SUBSTR(unique_identifier,-4,4)||' - '||sub_title
WHERE name LIKE 'Ancient Mys%'

and it does what I used to do manually.
It will change the sub_title of an episode of "Ancient Mysteries With Leonard Nimoy" with unique_identifier of EP0799200016 and sub_title of "Metropolitan Museum of Art: Splendid Discovery" to "0016 - Metropolitan Museum of Art: Splendid Discovery". And when the recording gets renamed it will be 0016 - Metropolitan Museum of Art: Splendid Discovery.mpg.

Now I just need to exclude episodes with sub_titles that have already been altered. Maybe just search if the 6th char is a "-".

[UncleJohnsBand]

I tried all 3
Sqlite Database Browser will let me edit data.
SqliteSpy will let me view and sort the table the way I like, but not apply any changes.
And I guess I just can't figure out anything in Sqlite Admin

Do I have to write sql code to do this?

Now you know why I have all 3 installed....

[Pioneer4x4]

OK, I feel comfortable in sql code now. I can definately do what I want, and I can probably totally screw up the db also!
I have some sql code that I would like to run everytime the epg gets updated. Can I put calls to sqlite3 in a batch file to run after the epg is updated?

The code is
UPDATE PROGRAMME
SET sub_title=SUBSTR(unique_identifier,-4,4)||' - '||sub_title
WHERE (SUBSTR(sub_title,1,1)>"9" OR sub_title='') AND unique_identifier <"F"

What it does is add the last 4 digits of the "unique_identifier" to the beginning of the episode name "sub_title" if
1)subtitle doesn't start with a number (already done it)
2)subtitle is blank
3)if it is an episode, not show or movie (first two chars of unique_identifier)

The output looks like
"0001 - episodename.mpg"

it works like a charm in the <Execute SQL> tab of SQLite Browser!

[UncleJohnsBand]

OK, I feel comfortable in sql code now. I can definately do what I want, and I can probably totally screw up the db also!
I have some sql code that I would like to run everytime the epg gets updated. Can I put calls to sqlite3 in a batch file to run after the epg is updated?

The code is
UPDATE PROGRAMME
SET sub_title=SUBSTR(unique_identifier,-4,4)||' - '||sub_title
WHERE (SUBSTR(sub_title,1,1)>"9" OR sub_title='') AND unique_identifier <"F"

What it does is add the last 4 digits of the "unique_identifier" to the beginning of the episode name "sub_title" if
1)subtitle doesn't start with a number (already done it)
2)subtitle is blank
3)if it is an episode, not show or movie (first two chars of unique_identifier)

The output looks like
"0001 - episodename.mpg"

it works like a charm in the <Execute SQL> tab of SQLite Browser!

You would need a small program that can call the SQLite engine using that query. Then in the postupdateepg.bat file put a line that runs the program.

[Old Dog]

OK, I feel comfortable in sql code now. I can definately do what I want, and I can probably totally screw up the db also!
I have some sql code that I would like to run everytime the epg gets updated. Can I put calls to sqlite3 in a batch file to run after the epg is updated?

I Googled "commandline sqlite" and this was the first return...
http://www.sqlite.org/sqlite.html

[UncleJohnsBand]

I Googled "commandline sqlite" and this was the first return...
http://www.sqlite.org/sqlite.html

Looks like you have to go into an interactive mode to use this tool......

[UncleJohnsBand]

Here is a little app I threw together with SharpDevelop. It simply reads in a query command from a file and runs it against the GBPVR database.

I have included the source as well as the compiled app (located in the release\bin directory).

Just copy the exe and the .txt file to the gbpvr directory. From the command line (or in a batch file) just type appenduniqueid.

It will read whatever query string you have placed in query.txt and run it.

That's it.