[wstewart]

xbox should not be considered a limitation. and no, code would have to be ported

That would be great. Anyway all that would be needed is an ODBC API to sqlite for XBOX and XBMC doesn`t need a full ODBC implementation anyway.

[Gamester17]

If the library is open source C or C++ and runs on Windows (Win32) then it can probably be ported to the Xbox without any problems, so don't worry about that, ...someone else can do that after it been proven to work in XBMC for Linux, Mac, and Windows.

It is true that Xbox support is not a requirement/limitation, but good to try keep in mind that having a small memory footprint is great if possible in any case

[wstewart]

thanks for the feedback. For XBOX, I doubt it will be as simple as an open source library, the best approach most likely will be to create a small ODBC API to sqlite, this would give the lowest footprint.

One place there will be an impact is plugins and scripts that access the database directly. They will also have to change to use an ODBC API, I forget which plugin/script I saw that access the database.

It will probably be a month or so before I am ready to start coding. Between now and then, I have to complete the conversion of my system from mythtv to xbmc (frontends, server and my large video collection). I will also be looking more closely at the code as well as the ODBC API in the meantime before starting coding.

I guess once there is enough code ready it would be best to create a new branch for development and testing. How does that get done?

[wstewart]

ok, getting a little discouraged with ODBC. Looks to me like drivers (ie. postgres and mysql) are not free for OS-X and for windows. For Linux they are. This is not desirable for XBMC. Haven't seen an sqlite driver for a local database so far to be able to mimic/utilize the current database.

iODBC is built-in to OS-X, but no drivers part of OS-X. I have yet to find a free driver that is useful.

Also it seems that the database must be configured in the ODBC manager, then referred to by the source name given in the ODBC manager when calling the API. Still investigating, but if this is the case, I don't think it desirable to have to configure the database in both XBMC and in the ODBC source manager. I would prefer to configure everything within XBMC.

Anyone has some more experience in ODBC that could lend some advice on all this?

So unless my investigation turns up something different on ODBC, I think I may be looking at a different solution.

If I understand everything right, the ODBC driver translates generic sql to the particular database, maybe the easiest is to just replace the existing sqlite layer with a translator based on existing Linux open source ODBC drivers or rather put a switch to select either sqlite or a translator.

[slash]

Hi,

May I jump in the discussion.

Would it be easier, for example, to just allow users (at install) to choose between SQLite (local DB) and Postgres (local or central)?

This can be done without the use of ODBC. Of course it would limit the choice of database to use, but personally I don't see a problem there.

[ccMatrix]

Mysql has an ODBC connector for pretty much all platforms available
Similarly PostgreSQL does provide connectors here

I think it might be a good idea to have a sqlite and a ODBC path in the code. Otherwise XBMC would have to setup the ODBC connector for sqlite and data source on installation which would be a pain.

Basically XBMC would connect to the ODBC connection created by the user with the ODBC manager of their operating system. There the user should configure all necessary information such as server and authentication. The connectors listed above are just some options available to the user but this isn't really related to XBMC. The advanced user would have to take care of obtaining the XBMC connector and setting it up properly in the ODBC manager.

In XBMC the user would have to enter the name of the data source he/she created in the ODBC manager. All SQL statements would then be sent to this data source in XBMC.

If that works XBMC might later integrate an ODBC manager since on platforms like XBox and AppleTV there probably is no ODBC manager available. But working with the system ODBC manager would be a good start.

The libodbc++ library looks like a good candidate for a multi-platform library. The unixODBC project provides an ODBC manager for linux with GUIs for KDE and Gnome. On Windows the ODBC manager should be provided by the OS itself.

I just found another library named Simple C++ ODBC Database API. The examples on the page look very simple but I don't know if it provides enough features for XBMC.

This is all I could find for now. Hope this helps you.

[wstewart]

Thanks ccMatrix, that is my understanding too, thanks for confirming it.

Do you think it will be a little cumbersome for the user to configure in the ODBC manager first, then to configure for the source within XBMC?

But I think the main problem with ODBC is finding database drivers for OS-X and Windows that don't cost anything

[bidossessi]

Thanks ccMatrix, that is my understanding too, thanks for confirming it.

Do you think it will be a little cumbersome for the user to configure in the ODBC manager first, then to configure for the source within XBMC?

Most new XBMC users are non-tech, so it will probably be cumbersome. However, we should keep in mind that most users who want to go the centralized database route are ready/willing to invest a little more time and research into making it work. They probably already have a central db and are thinking of ways to have XBMC stock it's stuff in there as well (my case anyway).
Maybe XBMC-ODBC connector could be hidden in an advanced setting so that only people who REALLY want to set it up will go that far.

XBMC's ease of setup/use should not be lost in this endeavour if possible, but a centralized database is not really something trivial, and the user should not expect it to be, in my opinion.

[slash]

ok, getting a little discouraged with ODBC. Looks to me like drivers (ie. postgres and mysql) are not free for OS-X and for windows. For Linux they are. This is not desirable for XBMC. Haven't seen an sqlite driver for a local database so far to be able to mimic/utilize the current database.

Not sure if this helps:

Sqlite ODBC driver : http://www.ch-werner.de/sqliteodbc/

Postgres ODBC for Windows : http://pgfoundry.org/projects/psqlodbc/

[ccMatrix]

Most new XBMC users are non-tech, so it will probably be cumbersome. However, we should keep in mind that most users who want to go the centralized database route are ready/willing to invest a little more time and research into making it work. They probably already have a central db and are thinking of ways to have XBMC stock it's stuff in there as well (my case anyway).
Maybe XBMC-ODBC connector could be hidden in an advanced setting so that only people who REALLY want to set it up will go that far.

XBMC's ease of setup/use should not be lost in this endeavour if possible, but a centralized database is not really something trivial, and the user should not expect it to be, in my opinion.

Same here. This is also why I wouldn't change the current sqlite stuff to use ODBC but keep separate sqlite/ODBC routines in the code. An advanced setting sounds like the place to put this since it is clearly something for advanced users. If someone manages to setup a database server, home network and owns several HTPCs to make real use of the feature, he probably can get the ODBC stuff working. And there will probably be tutorials in the forum once it is implemented.

I just tried to create an ODBC datasource on Windows Vista Business to connect to a mysql database on a Linux machine. The ODBC connector provided by the official mysql page was detected without any problems, in the configuration I could set datasource-name, server, port, username, password and database. That was all that was necessary on Windows to have it working. On Linux (Ubuntu Server) I had to modify the mysql my.cnf since mysql by default binds to localhost instead of LAN or 0.0.0.0 IP which disables external connections. So this is already a step for advanced users there.

From that test it also looks like in ODBC every datasource is connected to one database. So for XBMC there would be several datasources for the program, video, music, views databases.

I didn't have a look at the XBMC database code itself but from the look at the examples of the Simple ODBC API library the ODBC code seems to be pretty simple since it only needs to know the datasource name.

Code:

<advancedsettings>
    <odbc>
        <music_database>xbmc_music</music_database>
        <program_database>xbmc_program</program_database>
        <video_database>xbmc_video</video_database>
        <viewmodes_database>xbmc_viewmodes</viewmodes_database>
    </odbc>
</advancedsettings>

One thing to also think about is the profile location. If the profile is local, but the database is on a central server, when a user adds a movie to the database, the fanart/poster/thumb will be downloaded to the machine on which the user adds the movie. Other machines would have access to the same database and can list the movie but do not have the images to display. So to make this really work XBMC would also have to use a centralized location for e.g. Thumbnails (and probably script/plugin data etc.). Just having the profile stored on a share wouldn't be helpful since it could be possible to have e.g. two XBMC setups, one running on a FullHD and one on a 720p TV - they would need to have separate resolution information. There might be other settings which need to be kept de-centralized as well.

[bidossessi]

Maybe this is where the unified media manager will come in, on the central storage server, if all media is kept on the central storage (which seems desirable but not always possible), maybe going as far as keeping track of each XBMC instance's settings (XBOX=no HD, htpc=no xbox games) and so on...

local vs remote can quickly become a nightmare for people with media spread accross several 'instances' of XBMC on a LAN.

[wstewart]

thanks for all the feedback, this is great.

ccMatrix, for each profile we will need to have a different database also since this is what XBMC currently does with sqlite (or am I wrong). I was thinking more along the lines of merging the video, music, programs and views into one database and naming the database to the profile. For example

xmbc_master
xmbc_bill

Merging the individual databases into one should be transparent to XBMC assuming the table names do not conflict between music, video, programs and videos. I don't believe they do.

[spiff]

tables surely conflict, e.g. version file path

[ccMatrix]

thanks for all the feedback, this is great.

ccMatrix, for each profile we will need to have a different database also since this is what XBMC currently does with sqlite (or am I wrong). I was thinking more along the lines of merging the video, music, programs and views into one database and naming the database to the profile. For example

XBMC_master
XBMC_bill

Merging the individual databases into one should be transparent to XBMC assuming the table names do not conflict between music, video, programs and videos. I don't believe they do.

Every profile has a different advancedsettings.xml file and therefore can use a different database. There shouldn't be any work necessary to handle this.

Maybe instead of this being a ODBC feature, it should be a centralize feature and in the advancedsettings the user would specify the ODBC data sources as well as paths for the shared stuff like thumbnails. This setting would then overwrite the profile path if set (for the shared items only).

[wstewart]

Mysql only supports a single level hierarchical structure, ie a database name and then the tables. This is probably true of postgres also, but its been a while since I worked with postgres.

To do this will require to name each of the databases as
xbmc_video_master
xbmc_video_bill
xbmc_music_master
xbmc_music_bill
etc,

I am not a big fan of this approach, I would prefer all the tables (video, music, etc) for a given profile to be in one database.

This is how mythtv does it and I much prefer it.

I will look at the conflicting table names to see if there may be some easy and backward compatible way to handle this. Since the video, music and program database code is all in separate files, then maybe a straight forward modification is to add a video_, music_, program_ prefix to the table name when sqlite is not selected as the database by the user.