Non-normalized fields in databases?

Hello, XBMC'ers. I'm a newbie here and want to take a crack at writing a plugin or two.

After opening my Videos database with a SQLite browser, I noticed a lot of non-normalized fields, especially in the "movie" table. For example, why does column "c14" contain genre information when there's a separate genre table (and the corresponding join table)? Likewise, "c18" has a string for the studio, but there's also a "studio" table.

Is there a reason for this? Some kind of backwards compatibility? Performance? Or just years of accumulated cruft?

Am I correct to assume that my future plugin should query this data the "right way" - by use of the join tables?

(Disclaimer: I may be a newbie when it comes to playing with XBMC, but I've been a web & database developer for what feels like forever. I wonder where I put my merit badge for coding Perl .cgi scripts in Notepad? )


-Dito

Here is a few pages that might help you out.

http://wiki.xbmc.org/index.php?title=Database
http://wiki.xbmc.org/index.php?title=Dat...Schema_4.0
http://forum.xbmc.org/showthread.php?tid=55794

Direct access to the database can be troublesome in the general population. This is because probably about 50% of users now use MySQL in place of SQLite. And even XBMC developers have been talking about changing to a different database system. Right now there is only one method that is database indepentant access(with out importing modules in python) and that is HTTP API. The only downfall is that HTTP API is depreciated and will eventually be removed. JSON RPC does provide some access(though only output right now.)

Dito Wrote:Hello, XBMC'ers. I'm a newbie here and want to take a crack at writing a plugin or two.

After opening my Videos database with a SQLite browser, I noticed a lot of non-normalized fields, especially in the "movie" table. For example, why does column "c14" contain genre information when there's a separate genre table (and the corresponding join table)? Likewise, "c18" has a string for the studio, but there's also a "studio" table.

Is there a reason for this? Some kind of backwards compatibility? Performance? Or just years of accumulated cruft?

Am I correct to assume that my future plugin should query this data the "right way" - by use of the join tables?

(Disclaimer: I may be a newbie when it comes to playing with XBMC, but I've been a web & database developer for what feels like forever. I wonder where I put my merit badge for coding Perl .cgi scripts in Notepad? )


-Dito

Thanks for the info.

I read some of the material on the proposal for a revamped database, but I didn't get far enough to realize an entirely new database engine was a possibility. Interesting stuff.

Is there any reason NOT to use the Python libraries for SQLite? Is JSON the way, the future, and the light at the end of the tunnel?

Dito Wrote:Thanks for the info.

I read some of the material on the proposal for a revamped database, but I didn't get far enough to realize an entirely new database engine was a possibility. Interesting stuff.

Is there any reason NOT to use the Python libraries for SQLite? Is JSON the way, the future, and the light at the end of the tunnel?

If you plan on submitting to the XBMC repo you will need to make it work for both SQLite and MySQL databases. If only going to be using it on your own, the SQLite module is really good and quick. JSON RPC provides good retrieval access to the Libraries in XBMC, at the moment there is not any Editing access.

Several things here:

1. Do NOT access the database directly (via SQL). This relies on the layout staying the same which is almost certainly not the case, so your addon will break.

2. See 1.

3. Do NOT use the http-api to attempt to do the same thing. Again, this relies on the layout staying the same which is almost certainly not the case, so your addon will break.

4. See 3.

5. DO use jsonrpc for this stuff. It doesn't rely on the layout staying the same and the API is versioned so that hopefully your add-on won't break.

As for the non-normalisation in the db, this is because the db is designed for fast access - update frequency is very low, where as reads from the db happen a whole heap in comparison. Genres, for example, are a perfect example of this. If it were fully normalised, then we'd have to join the genre table which would both slow things down, as well as retrieving around 10 times the number of rows (one per genre) for each movie. We'd then have to scan through all those rows and dump it all into the movie objects. With it flat, it's much faster - no need to join to the genre table, and a single row per movie keeps things trivial.

Ofcourse, genres (and pretty much anything else that fits in this category) are also one of the things we don't actually need to fetch quickly (it's not used for sorting, and when we're filtering by genre we ofcourse join the genre table anyway) so this example is a bit silly, as we could request genre information on a per-movie basis off-thread after the main info is already in place. But given the current system, I think you get why things are the way they are.

Cheers,
Jonathan