how to access gbpvr.db3 file using SQLite

[zehd]

The plugin I am currently working on deals with manually editing commercials in a comskip txt file. But since I expect the user to include their Recordings folder as one of the folders where files are selected from, I would like to be able to give the user more than just the file name with the show name and various numbers.

You say that you just completed a tool which gets information from GBPVR's database. How did you go about doing that, since that is basically what I am trying to do.

Yeah it's pretty straight forward. Although I use the dll that I mentioned in the previous post, I would bet that the dotnet dll that was mentioned would work the same way.

Basically, I send an SQL query to the dll. The results are written to a Listview. I then pull what I want out of the listview

My other feature that I have in ZRename, is the download of the associated epguides.com html page (minus pics or attachments) and then read the html one line at a time into a for next loop, which matches text against search criteria. I can get most show's season and episode numbers that way...

Someone else has wanted to see the source code for it. And it's really the first one that I won't mind sharing. (I just don't want to have my hobby taken away from me... )

[HydroChronic]

Just from looking at some of the sample code in the forums for the System.Data.SQLite I can tell I will need as much help as possible in the beginning. I have no real experience with databases outside of Excel, so this should be very interesting. The most important feature I am currently concerned with is simply reading from the database. Although I won't have much time to play around with SQLite until sometime next week due to finals as I said any help will be much appreciated.

Thanks.

[Wakalaka]

Just from looking at some of the sample code in the forums for the System.Data.SQLite I can tell I will need as much help as possible in the beginning. I have no real experience with databases outside of Excel, so this should be very interesting. The most important feature I am currently concerned with is simply reading from the database. Although I won't have much time to play around with SQLite until sometime next week due to finals as I said any help will be much appreciated.

Opens gbpvr.db3, counts # of capture sources, returns a few of the items from each row. The biggest problem I had was type casting. I had to set a breakpoint at each line where I wanted the data read to make sure it was cast without an exception.

Code:

// Tuner status strings are returned in order according to the allocation_order item
            // of each CAPTURE_SOURCE in the database, but Capture Source names are indexed by oid.
            // Store the allocation_order item for each Capture Source, starting at oid 1.
            //
            // GetTunerStatus() changes the strings so they're sorted by oid instead of allocation_order.
            SQLiteConnection sqconn;
            string dbpath = "gbpvr.db3";
            string strconn = "Data Source=" + dbpath + ";Version=3;New=False;";
            sqconn = new SQLiteConnection(strconn);
            sqconn.Open();

            SQLiteCommand       sqc;
            SQLiteDataReader    sqr;
            string              strSQL;
            bool                result = false;

            strSQL = "SELECT COUNT(*) FROM CAPTURE_SOURCE";
            sqc = new SQLiteCommand(strSQL, sqconn);
            sqr = sqc.ExecuteReader();
            result = sqr.Read();
            uint sourceCount = (uint)((Int64)sqr[0]);
            alloc_order = new uint[sourceCount];
            StatusLog.LogMessage(MyPlugInLogFileName, "InitTunerList: sourceCount = " + sourceCount.ToString());

            uint i = 1, j = 0;
            do
            {
                strSQL = "SELECT * FROM CAPTURE_SOURCE WHERE oid='" + i.ToString() + "'";
                sqc = new SQLiteCommand(strSQL, sqconn);
                sqr = sqc.ExecuteReader();
                result = sqr.Read();
                if (result)
                {
                    alloc_order[j] = (uint)((int)sqr["allocation_order"]);
                    StatusLog.LogMessage(MyPlugInLogFileName, "InitTunerList: oid=" + i.ToString() + "; alloc_order[" + j.ToString() + "]=" + alloc_order[j].ToString() + "; name=" + sqr["name"] + "; class=" + sqr["recording_source_class"]);
                    i++;
                    j++;
                }
            } while (result);
            sqconn.Close();

[HydroChronic]

Hey Guys,

I've downloaded an SQL Database Browser so I can start exploring the GBPVR databases, but I can't seem to find anywhere in its databases that GBPVR stores the information I'm looking for. The only place I can find this information is in recordings-dump.xml. I had thought that the "Programme" table would have had the Recordings information, but it looks like it is the EPG. Where did you guys find the Recordings information?
This is killing me.

[zehd]

Hey Guys,

I've downloaded an SQL Database Browser so I can start exploring the GBPVR databases, but I can't seem to find anywhere in its databases that GBPVR stores the information I'm looking for. The only place I can find this information is in recordings-dump.xml. I had thought that the "Programme" table would have had the Recordings information, but it looks like it is the EPG. Where did you guys find the Recordings information?
This is killing me.

Basically, the 'filename' is stored in the Recording_Schedule table.... You take the programme_oid and search the Programme table for a match. Then you get the Title and Sub_title (episode name) for the show. You can use that information to build a new filename. Then you go back to the Recording_Schedule table and change the value in the 'filename'. You do that at about the same time that you actually rename the file, and GB-PVR will be forever fooled...

I suppose you could use an inner join to reference the title and sub_title on the same line as the recording_schedule record...

[HydroChronic]

GBPVR.db3 only stores information regarding the Recordings Plugin, right? So it won't contain information about files in a directory other than the Recordings directory?

I ask because if it only holds information on files in the Recordings directory, then I don't have to search through the database for every file, only the ones in the Recordings directory. This will speed up rendering and execution.

[HydroChronic]

I'm having problems with my references using System.Data.SQLite. For some reason the compiler keeps looking for the "SQLiteConnection", "SQLiteCommand" and "SQLiteDataReader" datatypes in System.Data.Common.DbConnection instead of System.Data.SQLite. Can someone post their "using" lines so I can make sure I'm including the reference correctly?

Hope someone can help. This is the last feature I want to include before releasing a beta.

[zehd]

GBPVR.db3 only stores information regarding the Recordings Plugin, right? So it won't contain information about files in a directory other than the Recordings directory?

I ask because if it only holds information on files in the Recordings directory, then I don't have to search through the database for every file, only the ones in the Recordings directory. This will speed up rendering and execution.

Yes, the recordings panel use the recording_schedule...

I'm a little confused. Possibly because you're coming at it from a different direction.

Here's the direction I would take:
Get a file list. Select one file in the list, have it match what should be only one match in the recording_schedule, rebuild the file name as you wish, rename the file, refresh the file list...

You should have code to handle no episode data, and duplicates (increment) the filename, (filename (1), filename (2), etc...

[zehd]

You know you could sub out the task... just call ZRename, addepisode or renamerecording and then refresh the list when it's done...

My version shoots off to the internet and tries and gets seasons and episodes numbers...

And if the rename program is in the postprocessing.bat, then there won't be renaming required at all

[HydroChronic]

You know you could sub out the task... just call ZRename, addepisode or renamerecording and then refresh the list when it's done...

My version shoots off to the internet and tries and gets seasons and episodes numbers...

And if the rename program is in the postprocessing.bat, then there won't be renaming required at all

I am not trying to rename the files, I am just trying to pull the file's Recording info from GBPVR so I can display it in the file list instead of the file name. Much like the Recordings plugin does for files. My biggest problem now is that I'm having problems with my references using System.Data.SQLite.

As I posted before. Really confused by it, since I have System.Data.SQLite referenced in the project and in my "using"s lines.