I have since a couple of years used the XBMC database directly (through ODBC) to
- get nice formatted lists of movies and TV series
- finding doubles
- moving whole TV-series to other locations without loosing all the user data (watched, time when watched, etc)
- and much more.
I have noticed a couple of things with it.
- the database is not normalized so it has to be some problems for you to use it when programming, compared to a normailzed database.
- It has structure problems. For instance it does have the information about the id of the movie connected to the users data (watched/unwatched, when it was imported and watched). Then it deletes the private information when moving or deleting and then take the movie back after a while.
- a lot of "Cxx" field names
- a lot of wrong types for fields (char when it is supposed to be numeric and such, for instance the season and episode field (C11,C12?)
- and also here, much more
I have been waiting for Eden to see if you were going to change the structure of the database but now I can see that it is not done any major changes.
My question is simply if you are interested in me rewriting the whole database and (among a lot of other things) make it normalized? That is having the right tables, the right connections between the tables and the right fields in the right tables.
I will not go into much details if you are not interested but some of the differences would be:
- to separate movie/series data with the users information about movies/series. That would make it possible to move movies/series without loosing the users information. Show that movies are offline (maybe make profiles obsolete with a toggle for showing/not showing offline movies/series because then you can have sources for all the places you are with your laptop (if not using a centralized database)). Delete a whole TV-series and after some time you want to see it again and then put it back (maybe in another format) and still be able to have it marked as watched and also see when it was watched that last time. And even if you mark it unwatched (because you want to see it again) the information of when it was last seen will still be there.
- when it is normalized it is much easier and more natural to use the database when coding.
- better naming of fields
- all fields will be with the right type so they don't have to be converted before use.
(I think) there is a smart way of creating new versions of a database in XBMC. That is one of the reason why a total rewrite wouldn't be that hard to implement. And it is often impossible to do all the coding changes in the same time but that is also easy to use. For all the reading and hopefully a lot of the writing (depending on how SQLlite/MySQL is handling that, and that I don't know) of the database it is possible to use SQL views that looks exactly as the old database looks like, making the transformations easy and the code-changing a process that can be done whenever all the different programmers have the time to do it.
This is not any criticism of how the database looks right now. It is very common that the database gets like this after some years of using when building a lot of new features onto the first version.
In my profession I have been working with MS SQL Server for over 15 years.