[charrua]

@firnsy
I'm contributing to a different thread trying to troubleshoot other users issues, and I think I found a small bug:

When storing local paths in the db, they should be stored as: "C:/path/" but we are trying to store them as: "C:\path\" so XBMC is returning a MySQL syntax error because for MySQL the char "\" is reserved, network paths don't have that problem, because they are stored in the format "smb://NETWORK_ADDRESS/SHARED_PATH/".
Here's the log with the problem that helped me discover this bug: http://pastebin.com/6SU9dSJL
For more details you can check the last post of this thread.

[firnsy]

@firnsy
I'm contributing to a different thread trying to troubleshoot other users issues, and I think I found a small bug:

When storing local paths in the db, they should be stored as: "C:/path/" but we are trying to store them as: "C:\path\" so XBMC is returning a MySQL syntax error because for MySQL the char "\" is reserved, network paths don't have that problem, because they are stored in the format "smb://NETWORK_ADDRESS/SHARED_PATH/".
Here's the log with the problem that helped me discover this bug: http://pastebin.com/6SU9dSJL
For more details you can check the last post of this thread.

Yes this is indeed a bug in the SQL formatting of our queries for mysql.

In short an insert of 'C:\Path\To\Movies\' is legal in sqlite3 and illegal in mysql.

Could you please raise a ticket so it doesn't get lost in the noise.

[charrua]

Yes this is indeed a bug in the SQL formatting of our queries for mysql.
In short an insert of 'C:\Path\To\Movies\' is legal in sqlite3 and illegal in mysql.
Could you please raise a ticket so it doesn't get lost in the noise.

You got it, ticket #9022.

[bbushvt]

I've read through the thread and I'm very interested in getting a central mysql server up and running. Are there some directions on how to get this enhancement? If its part of the trunk (SVN) what changes do we need to make to the config files to get it to use a mysql DB running on another box?
Sorry if this was answered earlier and I missed it.

[charrua]

I've read through the thread and I'm very interested in getting a central mysql server up and running. Are there some directions on how to get this enhancement? If its part of the trunk (SVN) what changes do we need to make to the config files to get it to use a mysql DB running on another box?
Sorry if this was answered earlier and I missed it.

For details about configuration and troubleshooting, you can check the three links below:
http://wiki.xbmc.org/index.php?title=Adv...atabase.3E
http://forum.xbmc.org/showthread.php?tid=70603
http://forum.xbmc.org/showthread.php?tid=69838

[lazybones]

Another DBA who is also a user here with my thoughts..

- Normalizing the data and reducing the redundancy of the tables seems to be an excellent idea.
- I also agree that including the TYPE in the field name is a bad idea

I don't however see the case for supporting a network / service based DB... It seems to be a great deal of work that will increase complexity but has little or no value based on how XBMC actually works..

MySQL and PostgreSQL are designed for dealing with lots of transactions from many processes, however how many instances of XBMC are you going to see in one home network? SQLlite is actually faster when used locally and for reading can support concurrent access from multi-pule sources. It has built in locking to prevent concurrent writing from multi-pule sources.

What I propose instead would be a master / slave style sharing.. continue to use SQLLite and have an option to share a master DB from a network share or from XBMC it self on a special share.. Additional XBMC units could then be slaves.. From an end user point of view this would be much easier to manage as setting up a Service based DB and managing it probably well beyond the ability of a simple end user...

In the master slave setup all XBMC databases would be the same and sync up their records, all containing a common set.. Slaves would sync up common records to the master and sync back down any new records on the master they find... The master would control the deletion and DB cleanup tasks to ensure the databases stay clean.

I really don't think a LIVE network linked DB would work well for XBMC, for one it would be slow for retrieval of artwork and two it might take away bandwidth from video streaming tasks. Not to mention writes over the network during discovery would be slower.. Building up a local DB then syncing the DB should provide much better local performance and be fault tolerant to disconnects from network issues, or power loss on the master.

I see XBMC as more of an appliance, keeping DB management out of sight of the user would be ideal in my mind.

[jmarshall]

Such users should simply use UPnP - no need to reinvent the wheel here. Agreed there are some improvements that can and should be made to our UPnP server (eg serving fanart to clients).

For those users that really do want a centralised database, I don't see any huge issue with supporting one - it actually has benefits as far as the internal sqlite database goes. The code becomes more modular and likely more efficient, and has the benefit of being more open to scrutiny from DBAs such as yourself - after all, none of us core developers are DBAs (or, perhaps more correctly, those that are don't want to work on this aspect of XBMC).

Artwork retrieval isn't a problem really - each XBMC client will ofcourse have a local cache of all of that - no matter how the system is arranged, there'll be time taken up caching local copies of artwork.

Similarly, network outages aren't really an issue in a home LAN, nor are power outages, and the centralised database is designed to handle data integrity.

firnsy has done some great work on getting an initial improved schema written up - we're just working through some more details and will have it available for comments in the coming weeks. It is imperative that we get this right first time. Obviously this schema applies to both sqlite and a central db.

Cheers,
Jonathan

[firnsy]

- Normalizing the data and reducing the redundancy of the tables seems to be an excellent idea.
- I also agree that including the TYPE in the field name is a bad idea

Sweet! Us to!

I don't however see the case for supporting a network / service based DB... It seems to be a great deal of work that will increase complexity but has little or no value based on how XBMC actually works..

My setup is a dedicated NAS (custom PC) that runs an additional mysql, web and torrent scraping server. I don't see how this is not a valid use case for me.

What I propose instead would be a master / slave style sharing.. continue to use SQLLite and have an option to share a master DB from a network share or from XBMC it self on a special share.. Additional XBMC units could then be slaves.. From an end user point of view this would be much easier to manage as setting up a Service based DB and managing it probably well beyond the ability of a simple end user...

As jmarshall put it this can be implemented through UPnP with a little extra work. To implement a hybrid backend solution is far more complex than supporting multiple backends.

In the master slave setup all XBMC databases would be the same and sync up their records, all containing a common set.. Slaves would sync up common records to the master and sync back down any new records on the master they find... The master would control the deletion and DB cleanup tasks to ensure the databases stay clean.

There are also issues with the master/slave and even distributed solutions. For example what happens when the master is offline and has new content that slave B hasn't sync'd yet but still wants to watch the new content?

I see XBMC as more of an appliance, keeping DB management out of sight of the user would be ideal in my mind.

I completely agree, that's why remote DB support needs to be enabled via advanced settings. Thus the out of the box experience has no DB management required from the user.

All that said, of all the threads in the development forum this is at least generating some insightful discussion. Stay tuned for tear down on the draft of the new schema in the next few weeks

[charrua]

All that said, of all the threads in the development forum this is at least generating some insightful discussion. Stay tuned for tear down on the draft of the new schema in the next few weeks

I agree completely with the central MySQL approach, and I don't think a master/slave SQLite db schema would be a practical option, because every XBMC client need to have write access to the DB to update settings, watched/unwatched flags, bookmarks, etc. In addition to that, my concurrency tests using SQLite dbs even for read-only operations were very very slow.
I also understand that an hybrid db solution(sqlite+MySQL) could be a lot of work to implement in the code, but have you consider my suggestion of storing "local-only" media info in a local SQLite db, and network shared media info in the central MySQL db?

[firnsy]

I also understand that an hybrid db solution(sqlite+MySQL) could be a lot of work to implement in the code, but have you consider my suggestion of storing "local-only" media info in a local SQLite db, and network shared media info in the central MySQL db?

This technically also requires some form of hybrid solution.

The main issue I see with this is that for a client with both local and remote media db connections how do you reliably join the two resulting data sets. We try to utilise the db backend to it's full extent with sorting and filtering. By having to combine two result sets (one from the local db and one from the remote db) we have to add this filtering/sorting capability in the xbmc core which adds a lot of overhead (and duplicity of function).

I'm guessing the use case here is that if you have local media and the network media/db is down then you can still watch your local content only.

If so, then this is currently in the "too complex basket". The primary use case for the central DB, as I see it, is where you have central repository of information (db and media) and multiple clients (essentially dumb) that use their local storage only for caching.

[charrua]

This technically also requires some form of hybrid solution.

Yes, it is a hybrid db solution, that's why I said that I understand that it could be a lot of work to implement.

The main issue I see with this is that for a client with both local and remote media db connections how do you reliably join the two resulting data sets. We try to utilise the db backend to it's full extent with sorting and filtering. By having to combine two result sets (one from the local db and one from the remote db) we have to add this filtering/sorting capability in the xbmc core which adds a lot of overhead (and duplicity of function).

Maybe we can have separate lists for local and networked media files, so we won't need to combine the datasets, but we'll still have the flexibility to access some local media that it's not network shared.

I'm guessing the use case here is that if you have local media and the network media/db is down then you can still watch your local content only.

The use case is not only reduced to that, it's also when your network is up but you have some media (e.g.personal videos or pictures) that you only want to give access from the local XBMC instance.

If so, then this is currently in the "too complex basket". The primary use case for the central DB, as I see it, is where you have central repository of information (db and media) and multiple clients (essentially dumb) that use their local storage only for caching.

I can't argue with that because I'm not really shure how much complexity this local/remote feature could add. Anyway I still think it can be a useful addition to the current possibilities. Thanks for considering it.

[firnsy]

Yes, it is a hibrid db solution, that's why I said that I understand that it could be a lot of work to implement.

My bad.

The use case is not only reduced to that, it's also when your network is up but you have some media (e.g.personal videos or pictures) that you only want to give access from the local XBMC instance.

It may not be all that bad, currently any local media to the client is also stored on the network accessible DB. There may be some flexibility afforded by the new draft schema/queries to ensure local media is filtered appropriately.

We can argue that when it's posted for review.

I can't argue with that because I'm not really shure how much complexity this local/remote feature could add. Anyway I still think it can be a useful addition to the current possibilities. Thanks for considering it.

Definitely useful and not at all forgotten. Once we start moving to a new db structure, the answer may simply reveal itself.

[charrua]

It may not be all that bad, currently any local media to the client is also stored on the network accessible DB. There may be some flexibility afforded by the new draft schema/queries to ensure local media is filtered appropriately.
We can argue that when it's posted for review.
Definitely useful and not at all forgotten. Once we start moving to a new db structure, the answer may simply reveal itself.

Thanks for the clarification. I really look forward to the new db structure being revealed

[nabab]

Hi there,

That's a brilliant improvement you're working on!

jMarshall mentioned the artwork will be cached on the local machine, so I have a question about what kind of memory is expected to be necessary on that satellite machine.

I'm planning to set up a huge server at home connected to a 1Gb network with all my media collection, with several thousands music and videos.
I am trying at the moment a VIA Pico-ITX machine, that I would like to use as terminal everywhere in the house where media is needed, connected directly through the LAN, and controlled with a Logitech Remote.
The OS will be Ubuntu installed on a 4 or 8 Gb Flash disk maximum, and no hard drive, so I wonder if in this configuration I will have enough space to manage this cache if the library is really huge...

Sorry if my question is too anticipative; I'm looking for my best move on the hardware as I definitely want XBMC to be the media center.

Cheers!

Thomas

[bradvido88]

I understand storing media on your local box, but why not reference that media by a universal name for all your network clients.

So instead of C:\media\localmedia as your source
You use: smb:\\myclientpc\localmedia

(or some other protocol if your not a smb guy)

This is what I do. It allows me to take advantage of unused space on my dumb clients, works well with the central DB storage, and allows the media to be accessed by every xbmc client instead of just locally.

I should mention that all my xbmc PC clients are always-on, so this may work better for my case than yours.

Hope this helps someone.