MSDE or MySQL as Database

[KingArgyle]

I'd put this on a medium to low priority list item, but it would be nice to migrate away from Access as a database, and move to a middle tier level database such as MSDE or MySQL. As with Access there are no royalty payments or restrictions. MSDE is basically SQL Server, but only allows a limited number of users to access it at a time. MySQL of course is open source, and can be included free of charge.

Setup of these might be a bit more complicated, but some of the benefits might be helpful for plugin/utility developers, as well as database maintenance. Indexing and speed of these two databases is much faster than Access.

[sub]

The initial versions of GB-PVR actually used MSDE, but it was too much hassle to deploy (and too big). If I do change databases in future it'll probably be back to MSDE, but I suspect this wont happen for quite a while - sorry.

I have no interest in moving to MySQL.

[jasonf]

Is there database-specific code being used that for now requires Access?

Ideally, I'd like to see a connection string configuration parameter be used, allowing for the user to point to an alternative ANSI SQL-compliant database (like SQL Server, Access, Oracle, MySQL, or whatever you have laying around). Access could still be deployed with the product as a default.

[sub]

Quote[/b] ]Is there database-specific code being used that for now requires Access?

Not really. The OleDB packages and SQL I use is generic enough that it would work with SQL Server/MSDE/Access. Possibly Oracle. Probably not with MySQL. The connection strings are in the config.xml file.

That said, there is one piece of code (introduced in the last release) that does a JRO flush connection to force Access to write its cached data to disk. This was done to work around a widely known problem with the Access JET database not comitting is cache disk in a timely fashion. This would probably stop you using any other database.

[KingArgyle]

Actually if you are following ANSI compliant SQL and not using any MS extensions it should be portable to MySQL as well. Initial setup would be a little more combersum however, as you would have to have DDL statements to create the necessary schemas for the database. The only one you might have a problem with is Oracle depending on the version of Oracle that is being run. Only Oracle 9i and above support the ANSI join syntax.

It would be a nice option though and definitely would be something that differentiates GB-PVR from any other PVR application out there. I'd probably still keep a default database, but might allow for the option to use another database. Again, low priority item in my opinon, there is other more important items to work on.

This could be something that you could add, and let your development community tweak it.

[sub]

Yes, it was the differences with JOIN I was thinking of for Oracle, and subqueries for MySQL.

[KingArgyle]

Actually, latest version of MySQL (version 4.1) added support for Subselects (subqueries).

The big limitation would be the database specific scalar functions, because they aren't standard and each database is allowed to implement their own functions.