[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
JPDeckers Offline
Donor
Posts: 22
Joined: Jan 2010
Reputation: 0
Location: Eindhoven, The Netherlands
Post: #91
firnsy Wrote:You haven't missed anything. It would be set to -1.
...
However this function is not used, since we make use of autoincrementing fields for id's, thus letting the db backend take care of that.
This function should perhaps be deprecated to prevent its abuse.

Ah, I see now, I was under the impression that the nextid-function w/ sequence-table was still used, thus couldn't figure out how it would work, or why nobody would have noticed this Smile

tnx.
find quote
bradvido88 Offline
Donor
Posts: 800
Joined: Nov 2008
Reputation: 9
Location: MN
Post: #92
firnsy; Wrote:You are correct. It only exists on the trunk and hasn't been ported to the XBOX branch.
I'm not sure if the appropriate branch maintainers plan to merge it or not.
Interesting. Any idea who i could talk to or where I could discuss this solution getting merged with the XBOX branch?

XBMC.MyLibrary (add anything to the library)
ForTheLibrary (Argus TV & XBMC Library PVR Integration)
SageTV & XBMC PVR Integration
find quote
spiff Offline
Retired Developer
Posts: 12,386
Joined: Nov 2003
Post: #93
you can do it. that's all we offer - xbox is abandoned by all but one dev and i seriously doubt he has any interest.
find quote
bradvido88 Offline
Donor
Posts: 800
Joined: Nov 2008
Reputation: 9
Location: MN
Post: #94
spiff Wrote:you can do it. that's all we offer - xbox is abandoned by all but one dev and i seriously doubt he has any interest.

Sad to hear that. XBOX does everything perfectly... except for High Def. I really like using it to play music throughout the house since I can use my RF controller and control it from whatever room I'm in.
Oh well... I guess I'll give up on my XBOX feature requesting :o

Back on topic -- Is this MySQL solution expected to perform well with concurrent XBMC users? I've only set it up on one instance to use MySQL so far and wanted to check and make sure since I will have multiple XBMC's running and using the database at the same time.

XBMC.MyLibrary (add anything to the library)
ForTheLibrary (Argus TV & XBMC Library PVR Integration)
SageTV & XBMC PVR Integration
find quote
spiff Offline
Retired Developer
Posts: 12,386
Joined: Nov 2003
Post: #95
yeah, mysql handles that beatifully - kinda the point of a dbmsSmile
find quote
bradvido88 Offline
Donor
Posts: 800
Joined: Nov 2008
Reputation: 9
Location: MN
Post: #96
Awesome, thanks.

So what does the future have in hold for us? I know there has been work on "major database changes"... Is that something separate from this? Will it break this once it's in place?

XBMC.MyLibrary (add anything to the library)
ForTheLibrary (Argus TV & XBMC Library PVR Integration)
SageTV & XBMC PVR Integration
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #97
bradvido88 Wrote:Awesome, thanks.

So what does the future have in hold for us? I know there has been work on "major database changes"... Is that something separate from this? Will it break this once it's in place?

Initially, most likely as it will be a rather significant rewrite. That is any DB restructuring would be considered rather significant.

However, it is possible to export to NFO and I don't see why you won't be able to import from them into the new structure.

Ultimately, you can always backup your DB and revert in the worst case.
find quote
jurrabi Offline
Moderator
Posts: 201
Joined: Jan 2005
Reputation: 5
Location: Las Palmas, Canary Islands
Post: #98
First of all let me say this has been a nice 10 pages reading of witch I only lament one thing... the missing pages from the lighting.

It's been so long since I was hopping that XBMC will go to a centralized database... And now here they are the first steps.

I'm gonna test this as soon as I can get the svn to compile again (I had 28745 compiling yesterday but with today's adds it is not linking again Sad )

I, myselft are kind of a, let's say, advanced-experienced programmer with centraliced database systems (does SAP R/3 ring any bells) so for once I find a place I think I can contribute... or at least try.

Sorry if, for a first post, there are some questions that might been discussed or solved, but I will like to get up to date asap and try and help with evolving this.

1. In all this talk I have not seen any mention to transactional updating of the database and little consideration about concurrent updating. Is the concurrent updating taken into consideration in the new centralized design?

2. Also I saw the discussions in the beginning about how to manage the different client perspectives about file locations, but I didn't find any conclusion on that. I know for other things I work on (and by some vague indications by spiff) that a new URI is coming to the svn... can that be related?

3. Talking about pure database evolution (apart from db centralization and abstraction) I feel that XBMC media database lacks since always of a better object modeling. I will refer now to the movies object model. I must warn everybody that not being English native speaking might make this a lot more dificult for me to explain, but I'll do my best.
In the actual model we have a table for movies that store movie (as movie entity that has a title, a director, a production year, etc). This movies table has a field for a File that is (in my view of the model) an example of the movie.
If you want an object oriented programing analogy, movies will be classes (some how) and files will be objets.

The movie has attributes like title, director, year... etc.
And file has attributes like coded, resolution, languaje, etc.

The problem right now is that there is a correspondece 1:1 on movies-files relation when in the ideas world I can have one movie but many files of that movie (one in HD, another is a stream from the internet).

As you all know right now If we have 2 files for a movie that movie just appears twice in the database. And that can work, but it is not ideal.

There is some missing important information about a media database. For example: languages. One very important filtering data that is missing right know in XBMC is the language of a file. If I'm selecting a movie to see with friends, an important data for decision is the language as some people can't watch English or French or Italian spoken movies. That, of course is data associated to the file (not the movie. the movie can have a language as the original language, but that's it).

There are many more suggestions to do about db evolution (like to the paths table) but since there is a big DB evolution pending and there are some areas (lie the new URIs) that have to be uncovered to be able to give the best oriented suggestions I think I'll leave it here.


Again I want to end this with another thanks to all involved in this and asking forgiveness if this post is not best placed here.

Jur.

Always read the XBMC Online Manual,Frequently Asked Questions and search the forum before posting.
For troubleshooting and bug reporting use -> Log file.
Read the new posting policy!

(This post was last modified: 2010-03-23 21:21 by jurrabi.)
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 26,221
Joined: Oct 2003
Reputation: 178
Post: #99
1. MySQL handles concurrency for us. It only affects the central model, ofcourse.

2. Nothing has been decided for this. As long as all of the files are on the same central server it won't be a problem. The only difficulty is where the URI according to the clients don't match.

3. Obviously that will be taken care of. The files <-> movies mapping will be done via a link table. Audio languages etc. are essentially the same as genres/tags/directors/actors and all other "grouping" variables, so could potentially be handled the same way. The only difference here is where we store and retrieve that information. One presume that information is in the file(s) themselves rather than the movie entry, thus a property of the files table.

If you'd like to contribute to a potential database restructing, we hope to have a proposed schema available for discussion in the coming weeks. Feel free to do up your own schema ideas in the meantime!

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
jurrabi Offline
Moderator
Posts: 201
Joined: Jan 2005
Reputation: 5
Location: Las Palmas, Canary Islands
Post: #100
I'll be glad to try and help.

Where were those databases school notes? Wink

Always read the XBMC Online Manual,Frequently Asked Questions and search the forum before posting.
For troubleshooting and bug reporting use -> Log file.
Read the new posting policy!

find quote
spiff Offline
Retired Developer
Posts: 12,386
Joined: Nov 2003
Post: #101
and just to explain why the db is as it is; i had to do it and learn sql/general database shit at the same time - obviously quality has suffered a bit.
find quote
jurrabi Offline
Moderator
Posts: 201
Joined: Jan 2005
Reputation: 5
Location: Las Palmas, Canary Islands
Post: #102
And I don't think anyone would ever complain of the result. I think it does the job pretty well...

As everybody around here always says: if you can help, just do it. Wink

Always read the XBMC Online Manual,Frequently Asked Questions and search the forum before posting.
For troubleshooting and bug reporting use -> Log file.
Read the new posting policy!

find quote
charrua Offline
Senior Member
Posts: 273
Joined: Nov 2008
Reputation: 14
Post: #103
jurrabi Wrote:There are many more suggestions to do about db evolution (like to the paths table) but since there is a big DB evolution pending and there are some areas (lie the new URIs) that have to be uncovered to be able to give the best oriented suggestions I think I'll leave it here.
Again I want to end this with another thanks to all involved in this and asking forgiveness if this post is not best placed here.
Jur.
It's great to see that the database design brainstorming is starting again in here! Smile
jmarshall Wrote:2. Nothing has been decided for this. As long as all of the files are on the same central server it won't be a problem. The only difficulty is where the URI according to the clients don't match.
Just to "unstrike" some of what the lightning stroke: What if we store the local paths (the ones that are not network shared) and local media file info in a local database (they are not accessible by other clients anyway) and we keep the shared resources info in the central DB? That way we'll also be able to browse the local library, even after loosing connectivity to the networked db and media files. And also the URIs in the central db will always match for all the networked clients.
spiff Wrote:and just to explain why the db is as it is; i had to do it and learn sql/general database shit at the same time - obviously quality has suffered a bit.
From my point of view XBMC is an outstanding piece of software, I don't know any other media center as full featured and complete while still being user friendly and one out of the box solution that almost does it all. You, the other developers and the rest of the contributors are constantly updating and improving it, in the top of that it's completely free and open source. What else could anybody ask for?

jurrabi Wrote:And I don't think anyone would ever complain of the result. I think it does the job pretty well...
As everybody around here always says: if you can help, just do it. Wink
+1 That's the attitude!
(This post was last modified: 2010-03-24 14:03 by charrua.)
find quote
aiken Offline
Junior Member
Posts: 32
Joined: Aug 2007
Reputation: 0
Post: #104
If you folks don't mind a non-XBMC geek (but a yea-verily database geek) jumping in, I'd strongly discourage naming fields based on their datatype.

What happens if something that starts as a BOOL ends up, down the road, with more than two states? It's converted to an INT... and then it's either misleadingly named, or a huge amount of code and query refactoring needs to take place.

The only advantage to including the type in the name is the mnemonic benefit to reduce errors. And that is worth something. But, in my experience, names that are based on function rather than definition are much, much easier to maintain going forward.

My two cents. Many props to the XBMC team, and I look forward to future versions!
find quote
pecinko Offline
Donor
Posts: 3,846
Joined: Dec 2008
Reputation: 52
Location: Prague / Belgrade
Post: #105
Hi guys, If you would like a comment from user point of view, I would suggest organizing Db in a way that could provide for MULTImedia experience. In other words so you can keep music album with corelating videos and pictures in one place. Of course, same should be appliable to other sections - movies with soundtrack music and photos, keeping vacation videos and photos in one place...

Maybe scan directory structure and keep track of dir names, regardless of content type in it?

My skins:

Amber
Quartz

find quote
Post Reply