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

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Discussions (https://forum.kodi.tv/forumdisplay.php?fid=222)
+--- Forum: Feature Requests (https://forum.kodi.tv/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


- firnsy - 2009-07-27

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

CapnBry Wrote: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?
Code:
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).

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

CapnBry Wrote: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

firnsy Wrote: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 Smile


- firnsy - 2009-07-29

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

CapnBry Wrote: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

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

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


wstewart Wrote: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

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


ZIOLele Wrote: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

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

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

firnsy Wrote: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

ZIOLele Wrote: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

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


- spiff - 2009-11-16

we're still sitting here waiting for your patches