Kodi Community Forum
ODBC (Open Database Connectivity) database abstraction layer or API for XBMC? - Printable Version

+- Kodi Community Forum (http://forum.kodi.tv)
+-- Forum: Development (/forumdisplay.php?fid=32)
+--- Forum: Feature Suggestions (/forumdisplay.php?fid=9)
+--- Thread: ODBC (Open Database Connectivity) database abstraction layer or API for XBMC? (/showthread.php?tid=37449)

Pages: 1 2 3 4 5 6


ODBC (Open Database Connectivity) database abstraction layer or API for XBMC? - Cheekyboy - 2008-09-20 00: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 01: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


What are the Pros and Cons with ODBC? - Gamester17 - 2008-09-21 01: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 or 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? Confused

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

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



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

http://en.wikipedia.org/wiki/ODBC
Quote:In 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/IODBC
Quote:iODBC (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/UnixODBC
Quote:unixODBC

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 20: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 Sad


- Gamester17 - 2008-09-22 01:15

Cheekyboy Wrote:I tried to raise a trac but for some reason I cant log in Sad
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 11: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.


ODBC is the way to go, and iODBC cross-platform open source library is the best... - Gamester17 - 2008-09-23 14: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.

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

Cheekyboy Wrote: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.

Big Grin


- wstewart - 2009-06-11 04: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.


ODBC or ADOdb? - Gamester17 - 2009-06-11 17: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://forum.xbmc.org/showthread.php?tid=29107

Does anyone know the differences between ADOdb and ODBC? Confused
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 instead of MySQL! Nod
http://en.wikipedia.org/wiki/PostgreSQL (worth keeping in mind so that the API is compatible)


- wstewart - 2009-06-11 18: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 18:44

wstewart Wrote: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. Wink 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. Smile


- wstewart - 2009-06-12 00: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 14:08

wstewart Wrote: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://wiki.xbmc.org/?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 18:13

Gamester17 Wrote: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 18:16

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