ODBC (Open Database Connectivity) database abstraction layer or API for XBMC? [Archive]

Cheekyboy

2008-09-20, 01:52

I'm sure this has cropped up in one guise or another on various threads but I'm not sure theres a specific thread to discuss the technicalities of it so I thought I'd start one.

As xbmc grows and springs up on more and more platforms I was wondering if it would be possible to incorporte an ODBC layer between XBMC and the database. This would allow different platforms to use, should they wish, different database engines and would also allow users to chose their own scale of system to suite their needs, from small scale to power user. I'm sure theres a whole host of SQL Server, Oracle, MySQL devs out their itching to get their media collection onto their favorite db platform. I'd be more than happy to get stuck in on the SQL Server implimentation

jmarshall

2008-09-20, 02:03

All the db functions (pretty much) including queries and so on are done in the *Database.cpp/h files.

There's a couple of other calls with queries, notably the Smartplaylist.cpp file, and the httpapi stuff, but that should at least be a starting place.

We ofcourse welcome with open arms anyone wanting to do work in this area.

Cheers,
Jonathan

Gamester17

2008-09-21, 02:23

OK, so from what I understand the ODBC API would make it simpler for XBMC to change the actual SQL database server back-end if needed/wanted, like if someone for example like to fork XBMC for Linux to use PostgresSQL (http://en.wikipedia.org/wiki/PostgreSQL) or MySQL (http://en.wikipedia.org/wiki/MySQL) instead of SQLite, ...plus it would maybe also enable better direct access for the Web Interface (HTTP API) and Python to the databases as well, is that correct?

Are there any other Pros and Cons with ODBC? ???

@Cheekyboy, for tracking purposes please also submit a new feature request ticket for this on trac
http://xbmc.org/trac

By the way, I found these two related topic threads when I did a search for "ODBC" in the forums:
http://xbmc.org/forum/showthread.php?t=32278
http://xbmc.org/forum/showthread.php?t=29107

Here is some information I found about ODBC (Open Database Connectivity) on wikipedia:

http://en.wikipedia.org/wiki/ODBCIn computing, Open Database Connectivity (ODBC) provides a standard software API method for using database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems.

http://en.wikipedia.org/wiki/IODBCiODBC (Independent Open DataBase Connectivity) offers an open source, platform-independent implementation of both the ODBC and X/Open specifications, generally used on platforms other than Microsoft Windows. OpenLink Software maintains and supports the iODBC project and distributes its software under the LGPL and/or BSD License. Apple chose to bundle iODBC into Mac OS X and Darwin, starting with Darwin 6.0 and Mac OS X v10.2. Programmers have ported iODBC to several other operating systems and hardware platforms, including Mac OS 9, Linux (x86, x86-64, IA-64, Alpha, MIPS, and ARM), Solaris (SPARC and x86), AIX, HP-UX (PA-RISC and Itanium), Compaq Tru64, Digital UNIX, Dynix, Generic UNIX 5.4, FreeBSD, DG-UX, and OpenVMS.

http://en.wikipedia.org/wiki/UnixODBCunixODBC

The unixODBC project — headed, maintained and supported by Easysoft Director Nick Gorham — has become the most common driver-manager for non-Microsoft Windows platforms (and for one Microsoft platform, Interix). It offered full ODBC3 support and Unicode in advance of its competitors. Most Linux distributions now ship it, including Red Hat, Mandriva and Gentoo. Several vendors of commercial databases, including IBM (DB2, Informix), Oracle and SAP (Ingres) use it for their own drivers. It includes GUI support for both KDE and GNOME. Many open source projects — including OpenOffice.org and Glade — also make use of it. It builds on any platform that supports the GNU autoconf tools (in other words, most of them). For licensing, UnixODBC uses the LGPL and the GPL.

Cheekyboy

2008-09-21, 21:53

Hi Gamester

The idea would be to put the ODBC interface layer between the XBMC code calls to the database and the actual database itself. I'm not sure if there is an official ODBC driver for SQLite but i found this as a starter for 10 http://www.ch-werner.de/sqliteodbc/html/index.html Its existence suggests there probably isnt an official one
Basically, once XBMC was changed to use ODBC as the database connection method rather than, I'm guessing here as I havent read the code, a specific SQlite one you could substitute SQLite for any other database engine that supports the SQL feature XBMC uses. Usually the changing of databases is just a configuration change with no code changes so could be controlled via advancedsettings.xml.

I tried to raise a trac but for some reason I cant log in :(

Gamester17

2008-09-22, 02:15

I tried to raise a trac but for some reason I cant log in :(Both the username and password are case sensitive, (while the forum login is not), that is usually the problem so please try again with that in mind.

Cheekyboy

2008-09-22, 12:15

Ticket #4954 raised in new Feature Requests. I have added OLEdb/ADOdb as other possibilities because really it doesnt matter which technology is used, whichever one has best support in the code across all platforms will do.

Gamester17

2008-09-23, 15:34

After the research I now done (as a non-programmer but technically inclined person) I have come to the conclusion that ODBC is the API that I think would fit XBMC codebase for this purpose, that is as an database abstraction layer between XBMC GUI (XBMC's libGUI code) and the SQL databases it uses currently for videos and music.

FYI; OLEDB is designed by Microsoft as is not cross-platform so that goes out the door directly as it would not fit XBMC's multi-platform design.

As for ADOdb I have come to a other conclusion; it would be great to have both ODBC and ADOdb in XBMC but not to serve the same purpose. ODBC looks to be to definitely be the database abstraction layer to use between the XBMC GUI (XBMC's libGUI code) and the SQL databases for videos and music, however would also be good to have in ADOdb but as database abstraction layer between XBMC's python scripts and XBMC's Web Interface because that is what ADOdb is designed to be. So what I have done is to create a separate ticket on trac for ADOdb, see trac ticket #4946 (http://xbmc.org/trac/ticket/4946).

So I think we should only concentrate on looking at ODBC in this ticket.

In my research I found iODBC which is an cross-platform open source (LGPL) library for ODBC which I guess will probably suit XBMC codebase best for the purpose of being the database abstraction layer between XBMC GUI (XBMC's libGUI code) and the SQL databases for videos and music.

iODBC offers a platform-independent ODBC SDK and runtime offering that enables the development of ODBC-compliant applications and drivers outside the Windows platform.
http://en.wikipedia.org/wiki/iODBC

Ticket #4954 raised in new Feature Requests. I have added OLEdb/ADOdb as other possibilities because really it doesnt matter which technology is used, whichever one has best support in the code across all platforms will do.I rewrote your request for ODBC only as OLEdb is not cross-platform (unlike ODBC/ADOdb) and ADOdb would not suit this purpose in XBMC, ADOdb would however serve a other purpose in XBMC (as an database abstraction layer for python scripts and the web interface) so I created a separate ticket on trac for ADOdb.

:grin:

wstewart

2009-06-11, 05:52

unixODBC is another one that looks interesting. It seems to be compatible with Microsoft's ODBC implementation and should be a good option to support the linux/OS-x builds, while remaining compatible with a windows build that uses the MS API.

Looks like unixODBC and iODBC are interchangeable.

Gamester17

2009-06-11, 18:57

Note! I still do not fully understand if ADOdb SQL database abstraction layer API would be better here instead of ODBC or not?
http://xbmc.org/forum/showthread.php?t=29107

Does anyone know the differences between ADOdb and ODBC? ???
http://en.wikipedia.org/wiki/ADOdb

If their function is the same for our purposes then ADOdb Lite looks very nice at first glance:
http://en.wikipedia.org/wiki/ADOdb_Lite

Which would be best for XBMC here, remembering that XBMC is cross-platform needing to run on multiple operating-systems and processor-architectures, and that XBMC is designed to be an embedded application with a small memory footprint as possible? Cross-platform and platform-independence is very important if it is to be accepted into the XBMC mainline code!

PS! Know that we been highly recommended by all to use PostgresSQL (http://www.postgresql.org) instead of MySQL! :nod:
http://en.wikipedia.org/wiki/PostgreSQL (worth keeping in mind so that the API is compatible)

wstewart

2009-06-11, 19:32

I think ODBC will be a better choice since it is support in windows.

"ADOdb is a database abstraction library for PHP", which likely means there is no C library for it. We need a C library to port the code over, which ODBC does have, ODBC will also support PHP and Perl I believe.

As far as mysql vs postgresql, won`t matter which you use with ODBC as long as there is a driver for it. It is totally transparent with ODBC from what I understand. Personally I prefer mysql, I have used both mysql and postgres.

Just starting to get into it, but the code changes from Sqlite to ODBC may not be too bad. Still would use similar SQL statements that are currently in the code, just need to just different function calls for the API.

szsori

2009-06-11, 19:44

I think ODBC will be a better choice since it is support in windows.

As far as mysql vs postgresql, won`t matter which you use with ODBC as long as there is a driver for it. It is totally transparent with ODBC from what I understand. Personally I prefer mysql, I have used both mysql and postgres.

You lose some functionality if you make things work specifically for ODBC. Yes, you can do all of the straightforward database calls with it, but you lose flexibility with some of the stored proc (and other) stuff. ADOdb handles those types of things better. Still, I think either method would work sufficiently for XBMC's purposes.

Postgres is by far the better option. I hope my recommendation prompted the "highly recommended" comment. ;) MySQL is fine for some projects and is more familiar for people, but if you need a more strict database with proper foreign keys (relationships = good), triggers, stored procs (stored functions in Postgres), etc then Postgres becomes the better option. MySQL can do some of those things, but having a database optimize based on foreign keys is a big deal.

One important thing with this discussion is trying to use an abstraction layer that allows you to connect to remote databases. That would allow a centralized database server for multiple XBMC systems, which handles another big feature request at the same time. ADOdb implementations will most definitely have that functionality. I'd be careful if choosing ODBC, since not all of them can say the same. In some cases you need an ODBC bridge to connect remotely, which is a complete pain in the ass. Just something to look into. :)

wstewart

2009-06-12, 01:08

Thanks szsori some food for thought on ODBC.

The only reason I am looking at this is that I want my database on my server, so I`ll look at ODBC to see if there may be a problem. I doubt it on the Linux side, but maybe the windows side.

The great thing about ODBC is that (in theory at least) I will be able to use Mysql while you will be able to use Postgresql and it will be totally transparent to XBMC.

Gamester17

2009-06-12, 15:08

The only reason I am looking at this is that I want my database on my server, so I`ll look at ODBC to see if there may be a problem. I doubt it on the Linux side, but maybe the windows side.Keeping cross-platform support and platform-independence in mind is very important if it is to ever be accepted into the XBMC mainline code!

http://xbmc.org/wiki/?title=Development_Notes

All code should strive to be platform agnostic - XBMC is a multi-platform software, thus any single platform specific features should be discussed with other team members before implemented, and software portability should always be kept in mind. All major new features and functions should be developed in a separate branch or committed in small increments so that other members have the opportunity to review the code and comment on it during development.

XBMC today runs on Intel (x86/x86-64), PPC (PowerPC), and ARM processor-architectures, and on Linux, Mac OS X (version 10.4 and 10.5, also Apple TV OS), Windows, and Xbox operating-systems/platforms, ...and someone is currently in the process of also porting the XBMC on ARM branch in our SVN to Android operating-system.

:nod:

wstewart

2009-06-12, 19:13

Keeping cross-platform support and platform-independence in mind is very important if it is to ever be accepted into the XBMC mainline code!

I fully understand that and this is my intention, to ensure it can be addressed for windows also. Thinking about it, I doubt windows will be a problem since windows does support microsoft server side databases. OS-x should be able to use the same ODBC library as Linux.

The only place there may be concern is XBOX, I do not have one and do not have experience with it. Does anyone know if ODBC is supported on XBOX somehow? If so it will be key to know which revision is supported.

spiff

2009-06-12, 19:16

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

wstewart

2009-06-12, 19:46

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

2009-06-12, 22:10

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

2009-06-13, 01:12

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

2009-06-13, 07:31

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

2009-06-13, 07:53

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

2009-06-13, 08:28

Mysql has an ODBC connector for pretty much all platforms available (http://dev.mysql.com/downloads/connector/odbc/5.1.html)
Similarly PostgreSQL does provide connectors here (http://www.postgresql.org/ftp/odbc/)

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 (http://libodbcxx.sourceforge.net/) looks like a good candidate for a multi-platform library. The unixODBC (http://www.unixodbc.org/) 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 (http://simpledb.sourceforge.net/). 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

2009-06-13, 15:13

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

2009-06-13, 15:31

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

2009-06-13, 17:07

ccMatrix

2009-06-13, 17:51

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.
<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

2009-06-13, 17:59

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

2009-06-13, 19:18

spiff

2009-06-13, 19:30

tables surely conflict, e.g. version file path

ccMatrix

2009-06-13, 19:38

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

2009-06-13, 20:13

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.

szsori

2009-06-13, 22:17

MPauley73

2009-06-13, 22:35

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

Drivers and source is on this page...
http://dev.mysql.com/downloads/connector/odbc/5.1.html

ccMatrix

2009-06-13, 23:01

It's actually smarter to have all media information in one database, and then have a single table that associates the individual profile with each media item (listed as "direntry" in the schema). That way you're not duplicating information and it's really simple to manage and perform cross-profile queries. It also easily handles things that cross profile boundaries.

That is true but it seems to be a much deeper change in the overall database structure XBMC is using. I think for the ODBC/centralized feature it would be best to just focus on ODBC integration while keeping everything else intact. Effectively this would mean adding ODBC to the database class and adding an overwrite to point to a shared folder that contains thumbs etc.

Later a change like that could be done and then applied to both sqlite and ODBC.

rernst

2009-06-14, 00:00

szsori

2009-06-14, 09:09

Still don't get that you merely want to create a database for XBMC. Where is the abstraction? I do not believe the database level should matter at all. The abstraction should occur at the API level (however that is implemented).

What if you wanted to store the actual information in some distributed fashion and use an object broker. This ODBC stuff is way outdated.

ODBC or no ODBC should not matter. Nobody but UMM should read the information directly, regardless of its origins. Indeed, the UMM API may very well be a realtime aggregator.

I thought that was something we agreed on but apparently not so.

I don't believe we're in the UMM forum, are we? Beyond that, database abstration is a good thing whether it's handled in XBMC or outside in UMM. If done in XBMC, then XBMC can easily connect to UMM's database directly. I think trying to complicate it by making an API between the systems is far more complex (and unnecessary) than just allowing XBMC to directly interface UMM.

As for the XBMC developers, they haven't officially supported any type of database move to UMM nor do I expect they would. More likely is that they'd support it as another type of source the same as a UPnP server. That would still allow XBMC to function on its own without needing UMM, but would still allow the power that a system like UMM could bring.

In a nutshell, ODBC (or similar) is a good thing and can help with the UMM stuff at some point (direct interaction with UMM without needing an API). Additionally, UMM is far from being a reality and XBMC development shouldn't be adjusted to account for something that doesn't exist yet.

That is true but it seems to be a much deeper change in the overall database structure XBMC is using. I think for the ODBC/centralized feature it would be best to just focus on ODBC integration while keeping everything else intact. Effectively this would mean adding ODBC to the database class and adding an overwrite to point to a shared folder that contains thumbs etc.

Later a change like that could be done and then applied to both sqlite and ODBC.

jmarshall is making some pretty significant changes to the library anyway. If those changes are actually implemented, it would probably be smart to do it "the right way" the first time around. :)

Gamester17

2009-06-14, 16:14

I don't believe we're in the UMM forum, are we?No this is the XBMC Feature Suggestions Forum and the discussion is about implementing database abstraction into XBMC itself :;):

Ability to share and synchronize XBMC userdata profiles between multiple machines (http://xbmc.org/forum/showthread.php?t=15971) and the possibility of having a XBMC "Server" - centralized XBMC management for multiple XBMC devices/platforms (http://xbmc.org/forum/showthread.php?t=37315) or feature like "Follow Me", save bookmark/playlist on one XBMC machine and then resume on a other (http://xbmc.org/forum/showthread.php?t=24415) iare just a few of the reasons why people want this database abstraction in XBMC itself.

Something like a networked Unified Media Manager (UMM) (http://xbmc.org/forum/showthread.php?t=50388) is also a thing that might be able to take advantage of a such database abstraction in XBMC if implemented, but that is not directly what this discussion is about.

Soju

2009-06-14, 22:33

ccMatrix

2009-06-14, 23:06

Yeah that makes sense to me. ODBC (JDBC etc) is just a connectivity protocol, and do not per se enable any abstraction layer. ADOdb (http://phplens.com/lens/adodb/adodb-py-docs.htm#databases) (esp for Python) like Gamster17 posted, and OpenDBX (http://en.wikipedia.org/wiki/OpenDBX) are proper open abstraction APIs.

The idea behind this is to use a technology like ODBC to allow an advanced user to let XBMC connect to pretty much any SQL based database he has setup. Thereby he can use a central server to store the database for several XBMC installations in the house and they are automatically in sync since they use the same database.

Currently the main discussion should be about which technology to use. AFAIK ADOdb is not platform independent so while it might be considered to provide database access to the integrated WebServer (see previous discussion) it isn't a very good candidate for this endeavor.

OpenDBX currently also seems to be Unix/Linux/OS-X solution. Real native Windows support is still missing so it might be an alternative in the future but isn't currently an option - except if someone wants to add Windows support and then integrate it into XBMC.

Soju

2009-06-15, 00:24

The idea behind this is to use a technology like ODBC to allow an advanced user to let XBMC connect to pretty much any SQL based database he has setup.

I agree. But ODBC is not per se an abstraction layer. It allows for an ODBC socket transactions. There are other things that databases do that ODBC in and of itself will not accomplish. Plus it's an aging protocol that is rather latency sensitive (I should know, started out as a DBA on Keyfile, Sqlserver 7 and Oracle 8 ;)). Typically:

Application --> ADO --> ODBC --> Database

Thereby he can use a central server to store the database for several XBMC installations in the house and they are automatically in sync since they use the same database.

Thats great but that's the back end... which can be virtually anything. My feelings are that you should have multiple ways of accessing the XBMC suite. There are already great, well developed APIs (e.g., HTTP-API), having the right levels of abstraction built in will allow for rapid prototyping minimally. I would think at this stage flexibility is essential. Developing a framework is key, not hardcoding any sort of quickfix.

AFAIK ADOdb is not platform independent so while it might be considered to provide database access to the integrated WebServer (see previous discussion) it isn't a very good candidate for this endeavor.

It's true not everything will run of Windows well, especially on all versions of Windows. That said, (http://www.rampant-books.com/t_gogala_easyphp_ADOdb.htm) if Oracle can hook up to it, so can all the others on the list.

OpenDBX currently also seems to be Unix/Linux/OS-X solution. Real native Windows support is still missing so it might be an alternative in the future but isn't currently an option - except if someone wants to add Windows support and then integrate it into XBMC.

What do you mean by real native support? We don't have OSS kernel hackers in Windows you know. OpenGL and X both run well on windows, and still by MS standards they are not "native". Here is a OpenDBX (http://www.linuxnetworks.de/doc/index.php/OpenDBX/Setup/Manual_builds) statement on the matter:

Starting with release 1.3.10, the Windows platform is supported via the Cygwin or MinGW environments. Since version 1.3.11, the applications in "utils/" directory are built by default. If you want to leave this out of the build process, you can disable compilation... The steps to build the OpenDBX library are the same as in the Unix environment.

MinGW (http://en.wikipedia.org/wiki/MinGW)is a "native" port of GNU to windows. Cygwin is more "emulated" but no less functional IMHO. You can just about script anything in windows with the latter *nix style.

BTW, what do people think of Apache Derby RDBMS? Yes it's Java based but it's pretty light weight and comes embedded.

Soju

2009-06-15, 04:32

Heres an interesting discussion. (http://edin.no-ip.com/legacy/?q=what_should_or_shouldnt_be_duties_database_abst ract_layer) Whats not mentioned is the variability and scalability encountered when you sink an embedded db and a server. Locking contentions and replication, among many other factors are important. If you want to dictate one back end DBMS like mySQL or pgSQL then it's a no brainer to just implement ODBC and code up the necessary hooks, calls and lookups etc. But if flexibility to integrate different DBMS architectures are goals then you are right, something like ADOdb is the very way to go. I'm looking at OpenDBX (http://www.linuxnetworks.de/doc/index.php/OpenDBX) for a work project, it'll probably be faster than ADOdb for somethings, but not sure it will add anything other than performance to the mix.

wstewart

2009-06-15, 05:25

I'm looking at LibDBI and OpenDBX and I am liking them so far. They look very interesting, I think they may be a bit easier to use than ODBC and I think a lot less code modification to adapt XBMC to it.

OpenDBX has drivers for postgres, mysql and sqlite. Would not need to use a manager like in ODBC, could put the server ip/username/password directly in XBMC as settings from GUI since these are passed as parameters to the api routine to initialize the db connection. The same API can be used for sqlite, for the existing xbmc database implementation, when opening the connection you give it the location and name of the database file instead of the ip/username.

I have to look a little more closely at the windows side, if it is workable. But if we were to embedded the code in XBMC, it is light weight and may work for the XBOX implementation.

rernst

2009-06-15, 06:16

Wow, man, sorry. Yes, I thought I was responding to a UMM post.

I don't believe we're in the UMM forum, are we? Beyond that, database abstration is a good thing whether it's handled in XBMC or outside in UMM. If done in XBMC, then XBMC can easily connect to UMM's database directly. I think trying to complicate it by making an API between the systems is far more complex (and unnecessary) than just allowing XBMC to directly interface UMM.

As for the XBMC developers, they haven't officially supported any type of database move to UMM nor do I expect they would. More likely is that they'd support it as another type of source the same as a UPnP server. That would still allow XBMC to function on its own without needing UMM, but would still allow the power that a system like UMM could bring.

In a nutshell, ODBC (or similar) is a good thing and can help with the UMM stuff at some point (direct interaction with UMM without needing an API). Additionally, UMM is far from being a reality and XBMC development shouldn't be adjusted to account for something that doesn't exist yet.

jmarshall is making some pretty significant changes to the library anyway. If those changes are actually implemented, it would probably be smart to do it "the right way" the first time around. :)

Soju

2009-06-17, 20:57

wstewart

2009-06-18, 03:00

Are you thinking of taking a crack at it?

Yes, but not probably for a month or so, but in the meantime I will be looking over the code and possible solutions (ie. OpenDBX, ODBC) getting ready to code. Just is the process of converting over from mythtv, gotta finish that first, got a big video collection.

ccMatrix

2009-06-23, 22:12

I was just over at sourceforge to vote in the community choice awards, and found the following project:
http://cppdbc.sourceforge.net/

Maybe this could be useful for XBMC?

wstewart

2009-06-24, 07:22

I was just over at sourceforge to vote in the community choice awards, and found the following project:
http://cppdbc.sourceforge.net/

Maybe this could be useful for XBMC?

Ya, I did the same thing ... going through the sourceforge projects .. and I found it too, I am taking a look at it

Gamester17

2009-06-24, 17:14

I was just over at sourceforge to vote in the community choice awards, and found the following project:
http://cppdbc.sourceforge.net/

Maybe this could be useful for XBMC?Sounds good at first glance at the project summery:

http://cppdbc.sourceforge.net/This is the C++dbc project.

Its goal is to provide several database connectivity APIs (like MySQL, SqLite, ODBC and so on) for developers of C++ under a single API, that is easy to use and extensible for further APIs.

http://sourceforge.net/projects/cppdbc/A C++ database connectivity class library supporting different database management systems under a simple interface. The API is hiding implementation details from developers, so class usage is same for each database management system.

Though it seems to be a new project with only two developers, and the development status is listed as "Alpha", so one has to question how much work it will be to maintain it is if the upstream developers ever abandon that project(?), ...it is also listed as "Operating System : All 32-bit MS Windows (95/98/NT/2000/XP)" however it might still be portable? Also no documenation other than this diagram?
http://cppdbc.sourceforge.net/imgs/diagram1.jpg

Again, remember that XBMC has to run on Linux/Mac/Windows OS and x86/AMD64/PPC/ARM CPU.

wstewart

2009-06-24, 19:33

...it is also listed as "Operating System : All 32-bit MS Windows (95/98/NT/2000/XP)" however it might still be portable? Also no documenation other than this diagram?

I saw that too after posting, it can't be used for xbmc.

LibDBI and OpenDBX are looking pretty good at the moment, but I need to look in more detail at the lower level interface to know what sort of dependencies it has to know if it is suitable for xbmc to support cross platform.

ABarbaccia

2009-06-30, 22:45

So, this is my first post, so go easy. I'm new to XBMC (MythTV user) but enjoy xbmc as a slimmed down, simpler (prettier) interface when I'm not using mythtv. Also, my roommates and guests use xbmc. That being said, I think this iniative is huge and would like to help where I can. It's been a while since I coded in C/C++ but I think I could pick it back up.

From earlier:

Application --> ADO --> ODBC --> Database

ADO is microsoft specific, but it's the right concept.

Ideally the application should make all data requests through an API. The API then interprets these requests differently depending on the setup.

For example, if you wanted to retrieve all the video sources, there would be an API call for this. It would probably get passed the profile name or id.

So, in the case of sqllite, it would search the appropriate db for that profile and return the sources. For an ODBC connection, it would probably run a query joining the profiles table to the video_sources table. That way you could use best practices in RDBMS and keep simplicity with sqllite.

Seeing this is a big task to take on, I think the first step would be to abstract the current sqllite calls into an agreed-upon api. For starters we would need to determine which functions are needed, what parameters are passed, and what values are returned.

Once the API is created, thoughts about creating a second ODBC backend to the API will become a more realistic goal.

Anyone want to help me start? :nod:

CapnBry

2009-07-01, 16:22

It's an even larger task than you're probably thinking it is. Something to be mindful of is that "standard SQL" ain't, and that just making an abstraction layer removing sqlite objects and replacing them with generic objects probably won't cut it. As it is now, XBMC already uses an abstraction layer (dbiplus), which only happens to have a sqlite implementation.

Sure, making it be able to point to any database library is a task, but going through and checking every hand-built SQL statement in XBMC for compatibility against all database libraries is even bigger. Not that I'm trying to discourage anyone from implementing this, because I'd like to see it, but I think you're going to find that replacing dbiplus::DataSet with FooDataSet is the easiest part.

spiff

2009-07-01, 16:29

also we're looking into replacing sql database usage with a document store thingie instead

ABarbaccia

2009-07-01, 16:49

@CapnBry: If only!!

I agree, it is a sizable task that would require significant code changes. I have some ideas on how to implement it. I'll take some time to play around with my ideas and attach a small patch for proof of concept / review of approach.

@spiff: Can you explain this more or point to a forum thread. I'd love to know more.

spiff

2009-07-01, 16:51

no posts written up on it just yet. i can't recall what the thingie was called, basically
you query over http and get json returned. mdd/jmarshall was looking at it at devcon and it looked quite promising.

stay tuned ;)

CapnBry

2009-07-01, 18:33

That's what I get for missing devcon, was there also punch and pie?

wstewart

2009-07-01, 19:10

I have been reviewing the XBMC code. There appears to be a database abstraction layer in XBMC already. It appears the original intent was to be able to support other databases, there is in fact references to Mysql. There are hooks to be able to supply a host address, userid and password for the database to the abstraction layer.

The SQL statements used in the XBMC code are fairly generic and should be able to remain as is and then translated to the appropriate database specific sql syntax. This is what the data abstraction libraries discussed previously do anyway.

The main alteration to the code will be to modify the video/music/program/view database code to support passing a database address, userid and password to the current abstraction layer instead of assuming an sqlite file name. And to provide settings in the GUI to set the database type (sqlite or mysql), userid and password. Second would be to create a driver for mysql based on the current XBMC sqlite driver code.

My current intention would be to expand the current XBMC data abstraction layer to support a networked database (mysql) rather than incorporating some other API. At this stage I don`t think we need to incorporate one of the data abstraction api`s previously discussed, but I may borrow some driver code from there.

jmarshall

2009-07-02, 02:30

The db alternative discussed at the XBMC DevCon was the use of CouchDb for the video library:
http://en.wikipedia.org/wiki/CouchDB
http://couchdb.apache.org

Essentially it's a document based distributed storage system. Thus, multiple clients are automatically kept in sync across the network.

Views (written in javascript) are then placed over the top, and are indexed/cached. They're also documents, so view creation on one client is propogated to the rest.

You communicate with the db over http and get json results. There are also API's for various languages, C/C++ not being one of them, however.

Essentially we're in an exploratory phase: It seems to be well suited (we don't really use the relational side of the db all that much, after all) to our needs, but the main question is whether or not it can do all we want and can return results fast enough to be useful. If it does, then the problem of concurrent databases on multiple clients is solved. You also get full editting of the database from any web browser.

You need an Erlang daemon running, but that shouldn't be of any major issue I should think.

If anyone wants to help out at the exploratory phase, give me a shout. Essentially we need to:

1. Setup an example db with a bunch of documents with whatever fields we want.
2. Setup a bunch of views to give us what we have today.
3. Look at how easy/hard it is to generate dynamic views for smartplaylists and the like - perhaps they can simply be generated directly based on input from the UI and be kept in the db rather than hanging on to their XML descriptions?

Cheers,
Jonathan

wstewart

2009-07-02, 05:33

jmarshall, so has it been decided to go in that direction instead for XBMC?

I ask because I don't want to spend time working to expand the current database abstraction code for mysql, if in the end it won't be incorporated.

jmarshall

2009-07-02, 05:56

No, it's been decided to investigate it. If it turns out we can do everything we want to with it, with acceptable performance, then yes, I believe we will go with it.

If it turns out to not do what we want, or doesn't have the performance we need, then we'll drop the idea.

In either case, the Music Library is likely to stay as-is for the foreseeable future.

Cheers,
Jonathan

wstewart

2009-07-02, 08:10

Ok, then I will continue my efforts to update the existing database code.

ABarbaccia

2009-07-02, 16:56

In either case, the Music Library is likely to stay as-is for the foreseeable future.

Why so?

Also, reading more about CouchDB and its decentralized nature. How does it know where the other hosts are located?

spiff

2009-07-02, 17:01

they are accessed through http - so whatever url you use... which prob needs manual configuring

ABarbaccia

2009-07-02, 17:15

from CouchDB.org:

CouchDB is a peer-based distributed database system, it allows for users and servers to access and update the same shared data while disconnected and then bi-directionally replicate those changes later.

I just don't understand if there is a master or always-on shared file store somewhere that all machines are aware of.

I saw some performance metrics between CouchDB and mysql. On a dataset of 50,000 records, query performance was identical between the two. Index and table creation was 50x slower, but that is only seen during the first query to the view.

jmarshall

2009-07-03, 01:19

The music library will stay as-is simply as I don't have the time to commit to looking at it. Assuming the video library rewrite goes well, there's no reason why couchdb can't be used for everything else (it makes sense to do so, after all).

To get a feel for how couch works, pop over here:

http://labs.mudynamics.com/wp-content/uploads/2009/04/icouch.html

Cheers,
Jonathan

wstewart

2009-07-03, 02:40

Assuming the video library rewrite goes well...

Could you provide more info about the "video library rewrite"? I've read the other thread where you are asking for idea, but I don't have a clear idea of the objectives.

Besides an overview of the re-write, I am most interested in the planned changes to the video database code.

jmarshall

2009-07-03, 02:59

The other thread sums things up quite well.

Each "root node" in the library is essentially an independent source. Think of how files mode works, but change it so that when you click on a source you can have either folder browsing or browsing by your library. There is no longer a files and library view. It's all in one.

Automatic updating as you browse is supported - essentially the database is pre-filled with filename information, and then local or online sources are queried for the metadata info.

Arbitrary information will be allowed (key/value pairs) beyond the basic info to retrieve and sort a listing. If we stick with SQL, then this additional info is retrieved in a second pass of the database, so we'll have a fast initial listing, and metadata info (thumbs etc.) will then be background filled. Obviously it'll be cached so that next time you go in you'll get the metadata straight away. Not sure exactly how we'll do it with CouchDb.

Nodes at the root level can be virtual folders of nodes, or can be filter nodes (eg smartplaylists), category nodes (eg genres) or can be actual sources. Obviously the entire hierarchy is user-definable - it'll basically be a filesystem on disc.

In short a much more flexible system for the power users, while still having things work simply for those that just wanna add a folder of videos and play 'em.

As for changes in the database code: Essentially it's a rewrite from scratch.

Cheers,
Jonathan

wstewart

2009-07-03, 03:43

Much appreciated for the explanation, I hope my suggestion for custom categories (ie. children, mom, dad, exercise, home vids) will be considered.

I'm just getting into XBMC database code, if it works out, I hope to leave as much of the existing video/music/program database code as is and just translate it at the database abstraction level. If it works, this should work well for your code re-write.

The main changes I see are adding host name, password and userid and perhaps the table names may need to change. I think that each profile will need its own database and all tables would be in the profile database. But since the table names conflict between music, video, etc., I am leaning towards prefixing the table names with video_, music_, etc.

jmarshall

2009-07-03, 03:55

"Each root node is a separate source". You can put whatever you like in each of them. They're completely independent, so you can have all your kiddie movies nicely separated out.

Furthermore, the idea is that Category nodes or Filter nodes can filter or categorize across multiple source nodes if you wish to combine them in some way.

Cheers,
Jonathan

slash

2009-07-03, 15:30

If anyone wants to help out at the exploratory phase, give me a shout. Essentially we need to:

1. Setup an example db with a bunch of documents with whatever fields we want.
2. Setup a bunch of views to give us what we have today.
3. Look at how easy/hard it is to generate dynamic views for smartplaylists and the like - perhaps they can simply be generated directly based on input from the UI and be kept in the db rather than hanging on to their XML descriptions?

Cheers,
Jonathan

The concept sound very interesting. I would be glad to help on this.

I've been looking a way to distribute the library and managing offline medias.

Let me know how I can contribute.

Cheers

Gamester17

2009-07-05, 23:27

I saw some performance metrics between CouchDB and mysql. On a dataset of 50,000 records, query performance was identical between the two. Index and table creation was 50x slower, but that is only seen during the first query to the view....and PostgreSQL is twice as fast as MySQL on multi-processor CPUs?
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

:rolleyes:

ABarbaccia

2009-07-10, 04:35

I think that each profile will need its own database and all tables would be in the profile database. But since the table names conflict between music, video, etc., I am leaning towards prefixing the table names with video_, music_, etc.

This could be done with a single database.

Add a profile table and a profile_id field to the music, video, etc tables. Then you can query only the rows that correspond to a specific profile. The database abstraction layer will have to be made aware of the active profile_id and sql modified to include it. you're sql will be something like this: select artist from music where profile_id = 1.

Setting up multiple databases would require granting access to multiple databases and lots of ugly other stuff too.

wstewart

2009-07-10, 06:12

This could be done with a single database.

Add a profile table and a profile_id field to the music, video, etc tables. Then you can query only the rows that correspond to a specific profile. The database abstraction layer will have to be made aware of the active profile_id and sql modified to include it. you're sql will be something like this: select artist from music where profile_id = 1.

Setting up multiple databases would require granting access to multiple databases and lots of ugly other stuff too.

I agree that would be idea, but I am looking to maintain compatibility with the existing sqlite databases and minimize the code impact at this stage. Perhaps I should give some more thought though.

Gamester17

2009-07-11, 18:26

firnsy

2009-07-27, 10:49

G'day,

I've been lurking the forums for a long time and with my 3rd XBMC client being built, complete centralised management of my media is moving high on my priority list.

I have been doing a lot of browsing of the codebase lately and agree that it is going to be a sizeable task with:
2400+ SQL calls (most of which are "portable"),
separate databases for music, video, programs and views, and
the current user profile implementation.
However, there are a lot of fruits to bear by going down this path and with the UMM project in its relative infancy, the video DB getting an overhaul it would be a good time to get this under way.

Of all projects described in this thread, I'm of the opinion that OpenDBX[1] is the most appropriate with it's preference for speed whilst still remaining very lightweight. It supports the most likely of choices for DB backends (mysql, postgresql) as well as the current DB backend (sqlite3). The project has a very tight API, and is still reasonably maintained.

I believe the appropriate steps for pushing this forward include, but are not limited to:
Determine who will be contributing (including dev platforms) to this effort
Examine the 2400+ SQL calls and ensure their "portability"[2]
Integrate the OpenDBX library (perhaps initially coexisting with dbiplus and incrementally phased out)
Coordinate and align all DB calls to a the common API
Document the code and API (join the "commenting" revolution)
Let the good times roll ...

Looking forward to give back to something I've taken SO much from :;):

Footnotes:
[1] http://www.linuxnetworks.de/doc/index.php/OpenDBX
[2] http://www.linuxnetworks.de/doc/index.php/Portable_SQL

jmarshall

2009-07-27, 11:08

Looks reasonable to me - it's available for all platforms we serve.

CapnBry

2009-07-27, 16:03

I'm all for replacing the DB backend with something client/server, but isn't OpenDBX kind of ...light on functionality? Sure it provides a common API to a bunch of databases, but it doesn't seem to add anything beyond very basic string-based query/result. Notably missing:
-- Retrieving result values in native types (every value is char *)
-- Parameterized queries

The first we have with the current dbiplus, the second would be nice to have instead of using the workaround "escape the SQL" method.

Again I will also bring up the fact that SQL itself is the tricky bit. How will this line of code look in a backend-agnostic manner?
UPDATE song SET iTimesPlayed=iTimesPlayed+1, lastplayed=CURRENT_TIMESTAMP where idSong=%ld
Things like CURRENT_TIMESTAMP, LIMIT, ALTER/CREATE TABLE are specific their backends. Ideally the abstraction layer chosen should help reduce these issues. I've never been happy with any of the SQL abstraction layers though, so maybe this point is moot.

At the very least I'd expect the chosen dbiplus replacement to support 1 (if not both) of the issues I listed above.

firnsy

2009-07-28, 00:17

Notably missing:
-- Retrieving result values in native types (every value is char *)
-- Parameterized queries

Retrieving values in native types can be wrapped quite easily, whilst every "value" is a char* the column type (ie. INT, BOOL, etc) is also available to facilitate the conversion.

Yes parameterized queries would be nice to have though not essential since, for the reason you mention, dbiplus doesn't have it either. "escape the SQL" will have to linger a bit longer.

Again I will also bring up the fact that SQL itself is the tricky bit. How will this line of code look in a backend-agnostic manner?
UPDATE song SET iTimesPlayed=iTimesPlayed+1, lastplayed=CURRENT_TIMESTAMP where idSong=%ld
Things like CURRENT_TIMESTAMP, LIMIT, ALTER/CREATE TABLE are specific their backends. Ideally the abstraction layer chosen should help reduce these issues.

SQL is the tricky part and the main reason why this replacement will not be easy. All the current SQL statements have to be assessed and modified for "portability". Fortunately the command you mention above is "portable" and will work just fine with the big 3 backends (sqlite3, mysql and postgresql).

Things like CURRENT_TIMESTAMP, LIMIT, ALTER/CREATE TABLE are specific their backends. Ideally the abstraction layer chosen should help reduce these issues.

Yes there will be SQL commands that are specific to their backends and will differ from the SQL 2003 standards. However, as XBMC has used SQLite3 a lot of the SQL command extensions you seen in mysql and postgresql have been avoided already and should pose little problem ... touch wood.

At the very least I'd expect the chosen dbiplus replacement to support 1 (if not both) of the issues I listed above.

Whilst I agree OpenDBX is not the holy grail of solutions, I believe it satisfies more of the essential requirements as a replacement than it lacks in desirable requirements.

CapnBry

2009-07-28, 04:19

Retrieving values in native types can be wrapped quite easily, whilst every "value" is a char* the column type (ie. INT, BOOL, etc) is also available to facilitate the conversion.

Well of course converting strings to ints or floats and back again is as old as time itself, but doesn't it seem odd that a DB library only supports one type? There's "a thin layer" and then there's "we stopped developing right at proof-of-concept".

Just my evaluation, but hey you're doing the work so knock yourself out :)

firnsy

2009-07-29, 09:00

Well of course converting strings to ints or floats and back again is as old as time itself, but doesn't it seem odd that a DB library only supports one type?

I completely agree.

There's "a thin layer" and then there's "we stopped developing right at proof-of-concept".

Elegantly put :laugh: ... Perhaps we can give a little bit back to OpenDBX in this process, and keep the FOSS wheels turning.

CapnBry

2009-07-29, 15:18

Whew I thought maybe I was being to judgmental of OpenDBX. I'm glad we're in agreement, and look forward to seeing what comes of this.

ZIOLele

2009-07-31, 17:36

I think that each profile will need its own database and all tables would be in the profile database. But since the table names conflict between music, video, etc., I am leaning towards prefixing the table names with video_, music_, etc

Please don't. that's not the way to design a good database in that way the db would'nt be normalized and you would be keeping the same type of information(filepath or whatever) across multiple tables.

I agree that would be idea, but I am looking to maintain compatibility with the existing sqlite databases and minimize the code impact at this stage. Perhaps I should give some more thought though.

Isn't simpler to just keep the sqllite implementation, and build the new implementation with a central db making it right on the first step?
with an option in advancedsettings.xml you switch type and set the data source and you are ready to go.
Also for fanart, pics ecc why not store them on the db?

firnsy

2009-08-01, 03:41

Please don't. that's not the way to design a good database in that way the db would'nt be normalized and you would be keeping the same type of information(filepath or whatever) across multiple tables.

I agree with this, the purpose of the database is to remove as much duplication as possible.

Isn't simpler to just keep the sqllite implementation, and build the new implementation with a central db making it right on the first step?
with an option in advancedsettings.xml you switch type and set the data source and you are ready to go.
Also for fanart, pics ecc why not store them on the db?

Yes and no.

Yes it's simple to keep the existing code add another implementation side by side, but it complicates things when determining which database takes precedence. Eventually one implementation should remain.

The only issue I see with storing fanart and pics on the DB is access times, particularly if its a remote database. Alternatively, the DB could at least store the paths to the fanart and pics to allow co-location which would avoid having multiple copies on each front end.

Right now I'm documenting my proposals (it's the engineer in me) to put forward to the development quorum to determine the best way forward. If we're going to add multiple database backend support it is a significant enough modification to also address the database design.

ZIOLele

2009-08-01, 12:46

Yes and no.

Yes it's simple to keep the existing code add another implementation side by side, but it complicates things when determining which database takes precedence. Eventually one implementation should remain.

I don't understand what you mean with "which database takes precedence."
the user choose which implementation to use in advanced settings, so is up to the user to choose precedence. Or are you talking abount a "fallback" in the case the db is not online?

Another solution could be, if xbmc is programmed in c++, to reimplement the api to acess db with a common interface and then implement two different classes one for local access to sqlite dbs, and one to access remote db with odbc. That way xbmc will use only the api of the interface ad would be not aware of what or where the db is.

The only issue I see with storing fanart and pics on the DB is access times, particularly if its a remote database. Alternatively, the DB could at least store the paths to the fanart and pics to allow co-location which would avoid having multiple copies on each front end.

Well the access time could be an isse, but when we talk of remote db, it means a db in the same net which tipically is at least a 100Mbit net, not of a remote db access with a vpn or similar(well if this is done rigth we could in effect share a db across internet :cool:) so, i don't see access time to the db to be so treatening. If we store only the path of the pics we need also to make that path shared on the lan, and an option to configure that path in xbmc.

Right now I'm documenting my proposals (it's the engineer in me) to put forward to the development quorum to determine the best way forward. If we're going to add multiple database backend support it is a significant enough modification to also address the database design.

I totaly agree

mehuge

2009-11-08, 01:32

If we store only the path of the pics we need also to make that path shared on the lan, and an option to configure that path in xbmc.

Which ofc negates the benefit of not storing it in the DB in the first place. i.e. storing in the DB will probably provide similar if not better performance.

kay.one

2009-11-16, 09:59

Well the access time could be an isse, but when we talk of remote db, it means a db in the same net which tipically is at least a 100Mbit net, not of a remote db access with a vpn or similar(well if this is done rigth we could in effect share a db across internet :cool:) so, i don't see access time to the db to be so treatening. If we store only the path of the pics we need also to make that path shared on the lan, and an option to configure that path in xbmc.

I totaly agree

This is actually one of the reasons that I'm really exited about this feature, i just moved from windows to an ION box booting of a flash drive, and I can assure you that accessing and updating fan-arts over my gigabit network will be alot faster that the my crappy kingstone flash drive.

btw, this post has been quite for the past three months or so, would some one please provide some updates.

thanks alot.