[RFC] Database Abstraction Proposal - Request for Comments (Devs and DB Admins only!)

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
jmarshall Online
Team-XBMC Developer
Posts: 25,672
Joined: Oct 2003
Reputation: 169
Post: #31
@firnsky: Check trac before you do anything else - there are patches there for mysql for instance.

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
wstewart Offline
Senior Member
Posts: 212
Joined: Jan 2009
Reputation: 1
Location: Canada
Post: #32
jmarshall Wrote:@firnsky: Check trac before you do anything else - there are patches there for mysql for instance.

Looks very interesting: http://trac.xbmc.org/ticket/7529
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #33
wstewart Wrote:Looks very interesting: http://trac.xbmc.org/ticket/7529

Indeed a good find.

Surprisingly a lot of the database abstraction corrections are contained in my patches. Oh well not all is lost, since loopingz integration is a lot less obtrusive than mine I will take jmarshall's suggestion in trac and assist with providing some quality patches.
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #34
If you haven't already noticed we are making some headway with getting remote database handling (initially via mysql) into the XBMC mainline. The first patch which improves abstraction and removes some of the sqlite3_* calls that had creeped in have been removed[1] and are currently in the SVN/git trees.

I have just submitted the next patch which will allow MySQL connections and transacting with limited functionality[2]. The trac description provides sufficient explanation on the patch.

Ok, so here comes the request, in order to move forward there is some massaging required of the database schema. For example in MySQL, when you index a BLOB or TEXT column (which XBMC does), you must specify a prefix length for the index. Alternatively we can set these columns to a defined lengths from the outset.

Columns currently affected [SQL to provide the max length]:

MusicDatabase
  • strArtist
  • strAlbum
  • strGenre
  • strTitle
  • strPath
  • lastplayed
  • strThumb

VideoDatabase
  • strFile [select max(length(strFilename)) from files;]
  • strPath [select max(length(strPath)) from path;]
  • cXX [select max(length(cXX)) from movie;] (where XX is currently 00 through to 20, and tvshow/musicvideo in place of movie also)

To save arbitrarily setting each of these TEXT columns to VARCHAR(255) it would be nice to know what people currently have in there databases. I understand this is an obscure request but it will ensure minimal changes to the schema as possible.

Alternatively, if someone can provide me XeXe's fake FS[3] I believe that would be an excellent sample set for determining the appropriate values.

Whose getting excited?? I AM!!! Laugh

[1] #7529
[2] #8169
[3] Fake FS
find quote
jmarshall Online
Team-XBMC Developer
Posts: 25,672
Joined: Oct 2003
Reputation: 169
Post: #35
lastplayed is a date, so that should be easy enough.

strThumb is _currently_ a reference to a special:// path but this will likely change with some proposed thumb caching changes - it'll likely end up just a URL of where the thumb file originated (i.e. could be http:// or smb:// or whatever).

Note that strFile in the videodb in particular can be rather long - it could for instance be a rar:// url which would contain the (url encoded) full path to the rar as well as the full path within the rar to the file in question. You'll need the paths at least to be reasonably large.

Some of the cXX ones store plot information can be very large (eg more than 64k of text?) Most of these are not indexed though.

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
Andrewishy Offline
Junior Member
Posts: 33
Joined: Oct 2008
Reputation: 0
Post: #36
Hi All

I just spent the day playing with the code and added the following features which means for me i wouldn't want to have or need a shared database:

1. Smart Clean, after scanning for new content any file that is missing but the dir is readable is removed from the db

2. After updating and cleaning the db is automatically exported to the files, this means that all my other xbmc instances no longer need to get all the info from the scraper.

This means all the content each xbmc sees will have access to the stored data in the same location as the video content. as all my content is now on NAS drives it also means that i dont need to have the clients all on at once also having the client create the db means that its faster than constantly accessing the NAS drive.

This is a win win situation for me :-)

However saying this I think a unified db would be really good from a software architecture point of view , but i would include pictures in the same db and try and make it a bit more flexible so that the skinners can create specific fields and cool search engines.

<dream> It would be so cool to be able to add news feeds, documentaries and short clips (youTube), sports, shopping, Live TV... all in the same database as the standard TV Shows / moves. some clever software that opens up the internals of the db may be able to enable this. </dream>
find quote
spiff Offline
Retired Developer
Posts: 12,386
Joined: Nov 2003
Post: #37
1) is already there? need some refinement but it is definitely there
find quote
Andrewishy Offline
Junior Member
Posts: 33
Joined: Oct 2008
Reputation: 0
Post: #38
the clean is there but it cleans the whole database (so if my NAS is down i loose the db. you just set the variable in the advanced xml file

What i did is copied it and altered it so that it just did a clean of all files that are not there as long as you can see the directory (so NAS there but re-named file)
find quote
Andrewishy Offline
Junior Member
Posts: 33
Joined: Oct 2008
Reputation: 0
Post: #39
Some Q’s and A’s first

Q. Why is there an internal database?
A. So xbmc can quickly access all the data

Q. What’s in the database?
A. A location of the media (where the video file is) and a load of automatic metadata generated from scrapers based on the filename convention

Q. If you have multiple xbmc’s can they share the database?
A. No, the location of all the files is dependant on what xbmc can see and different instances may see different files systems

Q. where is the video stored?
A. The video is stored either locally, remotely on the home network (NAS or another PC) or is streamed via the internet.

Q. Can all the database be exported to files somewhere?
A. Yes, the export feature saves all the data and pictures in the same location as the video.

Q. Do I need to have all the instances of xbmc on to share the databases?
A. if you shared the databases directly then yes this would have to happen


As the simplest solution is often the best I think that using the export feature and saving the database to a sub directory where the video is located ( for example .database .Actors .fanart etc) and putting a hash in the filename would allow all instances of xbmc to update to the latest information on boot.

So some things I have found out:

1. There were a few bugs in the export feature (it didn’t export everything)
2. If the data has changed then a “don’t overwrite” will not overwrite the file.
3. The files have no version or hash on them so on an update they are not re-read if they have been changed.
4. The watched count is in the nfo file so that if you watched the program on one PC then it should be able to know that you have watched it on all PC’s (this does not work as the nfo is not read even if its changed)

So in I think rather than overhauling the database so it can be shared or use a central db running on another PC I think simply storing the info in a correct manner with the video files will allow all instances of xbmc to access this.

I would however propose the following steps to make this happen:

1. Re-visit the internal database so it has all the info in it required for this feature (
2. Create a more consistent format for the export feature export all data in the database and all pointed to pictures and store this in a hidden sub directory where the video is located. These files may either need to be labelled with hashes or date/time stamped something to identify that they have changed without having to read them all
3. overhaul the scanning so the data in the hidden files is checked to see if it is newer than what’s in the database
4. automatic export and cleaning of the database (after each scan) so that all new items are saved with the video.

Some issues that need to be addressed:
1. what happens if two xbmc’s try and write to the files at the same time? – probably wont happen but worth thinking about
2. different versions of xbmc? Maybe we need a version number in the stored files too so that the instances know what the data will mean or will the xml format be fine?
3. would it be better to store a raw database in each dir with the same format as the internal database? – maybe this would be more work and less usable as metadata
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #40
Andrewishy Wrote:Q. What’s in the database?
A. A location of the media (where the video file is) and a load of automatic metadata generated from scrapers based on the filename convention
It also stores a lot of other metadata (which you mention) such as playcount, lastplayed, etc which are not scraped.

Andrewishy Wrote:Q. If you have multiple xbmc’s can they share the database?
A. No, the location of all the files is dependant on what xbmc can see and different instances may see different files systems
Yes. There are patches submitted on trac to allow the sharing of databases (via MySQL).

Andrewishy Wrote:Q. Do I need to have all the instances of xbmc on to share the databases?
A. if you shared the databases directly then yes this would have to happen
Only if you wanted to share the local SQLite3 database. If you are using a central database (eg MySQL) then no not all instances need to be on.

Andrewishy Wrote:As the simplest solution is often the best I think that using the export feature and saving the database to a sub directory where the video is located ( for example .database .Actors .fanart etc) and putting a hash in the filename would allow all instances of xbmc to update to the latest information on boot.
Oddly enough your are trying to create another database, albeit a file based one.

Andrewishy Wrote:I would however propose the following steps to make this happen:
1. Re-visit the internal database so it has all the info in it required for this feature (
2. Create a more consistent format for the export feature export all data in the database and all pointed to pictures and store this in a hidden sub directory where the video is located. These files may either need to be labelled with hashes or date/time stamped something to identify that they have changed without having to read them all
3. overhaul the scanning so the data in the hidden files is checked to see if it is newer than what’s in the database
4. automatic export and cleaning of the database (after each scan) so that all new items are saved with the video.
1. A more information rich is a great idea.
2. A consistent export feature is also a great idea.


Andrewishy Wrote:Some issues that need to be addressed:
1. what happens if two xbmc’s try and write to the files at the same time? – probably wont happen but worth thinking about
2. different versions of xbmc? Maybe we need a version number in the stored files too so that the instances know what the data will mean or will the xml format be fine?
3. would it be better to store a raw database in each dir with the same format as the internal database? – maybe this would be more work and less usable as metadata
1. This will be an issue of your file based database system. You need to ensure it is ACID compliant. Which is much easier achieved with existing database systems.

3. I don't think a local db per folder will ever take off.

The simplest and most elegant solution is in fact to take the existing database infrastructure and is make it compatible with other databases. That is the intent of this thread.

Whilst I think a unified export feature is nice. The advanced feature set of a centralised database (via MySQL, etc) will lead to far more elegant solutions than a file based one.
find quote
megacrypto Offline
Senior Member
Posts: 219
Joined: Apr 2008
Reputation: 0
Post: #41
Im sorry, i dont quite follow what the mysql patch does exactly? Im running XBMC r26004, does that mean that i can use my mySQL DB to store my data? if so, how can I achieve that?

I don't know if this was mentioned before, but in order to have a central DB, there will have to be an id for each XBMC box (master - client or something like that). Also some of the DB tables will have to include such box ID in case the paths are different from one box to the other (i.e. my box i have the folders named in one way, my kids box they have their folders named in another way, etc..)

But, kindly, if there is a way to use mySQL with XBMC, could someone plz explain how.

thanks
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #42
The improved abstraction was pushed into mainline around r25622 and ticket #8169 has the initial patches to integrate mysql support. It is pending review for inclusion, and given the holiday season it may take a few more weeks before it gets a decent look.

All necessary information should be available in the ticket. If you're regularly compiling from the source then I would encourage you to apply the patches, test and provide feedback of your efforts.
find quote
Andrewishy Offline
Junior Member
Posts: 33
Joined: Oct 2008
Reputation: 0
Post: #43
Thanks for the comments firnsy

Just one concern about the central db is it will require a server to be on all the time, I'm currently using a dumb NAS drive for my content so don't have another spare server (and I don't think the average user would have one either)

I'll create another thread with some more ideas and will have a play with the code if anyone would be interested in a patch to do this
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #44
Andrewishy Wrote:Just one concern about the central db is it will require a server to be on all the time, I'm currently using a dumb NAS drive for my content so don't have another spare server (and I don't think the average user would have one either)

Yes it does require a server to be up all the time (just like your NAS has to be up all the time). The patches should safely fall back to the local database (via SQLite3) when the remote database is not available.

This is an advanced feature set and is not expected to be initially adopted by the average user with a dumb NAS drive. You could always re-flash your NAS and make it smarter Laugh
find quote
Andrewishy Offline
Junior Member
Posts: 33
Joined: Oct 2008
Reputation: 0
Post: #45
Quote:Originally Posted by firnsy
Quote:Originally Posted by Andrewishy
Just one concern about the central db is it will require a server to be on all the time, I'm currently using a dumb NAS drive for my content so don't have another spare server (and I don't think the average user would have one either)
Yes it does require a server to be up all the time (just like your NAS has to be up all the time). The patches should safely fall back to the local database (via SQLite3) when the remote database is not available.

This is an advanced feature set and is not expected to be initially adopted by the average user with a dumb NAS drive. You could always re-flash your NAS and make it smarter
The NAS is an off the shelf device and only supports file sharing and i so don't want to re-flash it to make it do other things (its a Ethernet/USB external HD not a full uPnP device so low power but good enough for HD video transfer).

Good luck with the db stuff, I'll concentrate on the exporting of the data as I think these are separate features.

I'll propose something in another thread after some more thoughts
find quote
Post Reply