[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 Offline
Team-XBMC Developer
Posts: 26,221
Joined: Oct 2003
Reputation: 178
Post: #16
1. I was merely pointing out that the data in the 3 current libraries is primarily independent, thus the only reason to join them is convenience rather than to cross-query.

2. We already support file://, and similarly already have URL constructs for rar:// and zip:// and the like. I'm not sure how this helps anything. At a broader scale, the whole idea of which client updates the database for scans etc. is something to think about. It doesn't really make a lot of sense for each client connected to a central db to run checks for new content - this is a role the central server should take, right?

3. I see little point in working on multiple libraries until one has been proven well-defined and working, but don't let that stop you Smile

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
ZIOLele Offline
Senior Member
Posts: 132
Joined: Oct 2008
Reputation: 0
Post: #17
jmarshall Wrote:1. I was merely pointing out that the data in the 3 current libraries is primarily independent, thus the only reason to join them is convenience rather than to cross-query.

2. We already support file://, and similarly already have URL constructs for rar:// and zip:// and the like. I'm not sure how this helps anything. At a broader scale, the whole idea of which client updates the database for scans etc. is something to think about. It doesn't really make a lot of sense for each client connected to a central db to run checks for new content - this is a role the central server should take, right?

3. I see little point in working on multiple libraries until one has been proven well-defined and working, but don't let that stop you Smile

Cheers,
Jonathan

1. I know that was only an idea.

2. yes it should be the central server or rather a server process that checks periodically for update and update the db (which may be located in another server). We can also use the client to update the db directly, but in this case there is concurrency to take in account.

3.that's the "problem" i think we should refer to the three libraries as "media" and then define a well formed and normalized db to accomodate all type of media, rather than approaching them separatedly.
find quote
ZIOLele Offline
Senior Member
Posts: 132
Joined: Oct 2008
Reputation: 0
Post: #18
Uhm, is the video db schema complete? Where are stored the scraped information?
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #19
jmarshall Wrote:1. I was merely pointing out that the data in the 3 current libraries is primarily independent, thus the only reason to join them is convenience rather than to cross-query.

I think we all concur on this point, it is a convenience rather than necessity. A convenience which can yield some nice side effects.

jmarshall Wrote:Perhaps the solution is to ignore it and force everything to be on a network drive that allows the same URL format for everything, or perhaps we just tag those paths that will be the same for all machines (i.e. network) - I don't know the best way to handle it.

Building on this idea a little further, would giving each XBMC instance in the network a unique name (resolvable by some means) of which becomes part of the URI.

For example -----
I have 3 XBMCs in my network, named XBMC-A, XBMC-B and XBMC-C respectively. I have also have a 'nix server, named MEDIA-SRV, which hosts a samba share and is serving a common mysql database.

XBMC-A is the only instance with local media on "E" drive, which consists of a couple of movies wrapped up in a rar archive as well as some individual music files. All XBMCs are configured to use the common media repository at smb://MEDIA-SRV/media/

Now, let's say that XBMC-B browses the database and wants to play a movie that's contained in the rar archive. The database indicates the URI for the path is "rar://XBMC-A/E/movies.rar/movie1.avi"
End example -----

This would indicate an absolute URI with protocol/type (eg. file, smb, rar), host (eg. hostname, devicename, or even static IP) and the path.

jmarshall Wrote:At a broader scale, the whole idea of which client updates the database for scans etc. is something to think about. It doesn't really make a lot of sense for each client connected to a central db to run checks for new content - this is a role the central server should take, right?

Yes multiple clients do not want be updating a single database at once. I see two reasonable ways of dealing with this.

1. The ability to configure a master database updater of which only they can update the database and scraping is simply disabled on other instances. This could be a little limiting.

2. Have a pseudo lock table in the database that indicates when the database is being updated and by whom, other fields such as last updated could also be warranted. When an XBMC instance wishes to update the database they would have to obtain a lock before proceeding. This would allow all instances in a network to potentially update the database provided they have the lock.

ZIOLele Wrote:Uhm, is the video db schema complete? Where are stored the scraped information?

If you are referring to the picture the "cXX" fields in "movie", "tvshow", "episode" and "musicvideo" tables actually represents the fields "c00" through to about "c20" in the real tables. It was merely compressed to aid layout.

Hopefully you're not too confused.
find quote
ZIOLele Offline
Senior Member
Posts: 132
Joined: Oct 2008
Reputation: 0
Post: #20
firnsy Wrote:If you are referring to the picture the "cXX" fields in "movie", "tvshow", "episode" and "musicvideo" tables actually represents the fields "c00" through to about "c20" in the real tables. It was merely compressed to aid layout.

Hopefully you're not too confused.

No, i'm not too confused tanks :-D
So, if i understand it clearly all the scraped infos are in this c00..c20 columns right? (i'm using your image to understand the db schema since I don't have a tool to explore the actual dbfiles.)
find quote
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #21
ZIOLele Wrote:So, if i understand it clearly all the scraped infos are in this c00..c20 columns right? (i'm using your image to understand the db schema since I don't have a tool to explore the actual dbfiles.)

Correct. The latest SVN has it up to c21.
find quote
joelmeans Offline
Member
Posts: 64
Joined: Jan 2009
Reputation: 0
Post: #22
Pretty sure the latest SVN only goes up to c20. I just submitted a patch today which adds a c21.
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 26,221
Joined: Oct 2003
Reputation: 178
Post: #23
It's non-trivial to change an XBMC-sent URI into something else. Rar's for instance are stored something like (details may not be 100, but it's something like this):

rar://<username>:<pass>@<urlencoded_path_of_rar>/<path_in_rar_to_file>

Now, <urlencoded_path_of_rar> may itself be inside a rar or inside a zip or whatever, which may in turn be inside some other container before eventually being on a smb server or whatever.

Also, note that even a smb:// source may be available to some clients but not others (admittedly, very much a corner-case).

Thus, even network drives need tagging with "available to this client". This completely kills the idea of just pointing XBMC at the database server and things just working.

I'm moving back from this idea in general. If you want to share libraries across multiple machines, IMO using something like UPnP is a better way to do it - you share the library and the media without having to care about it. XBMC already contains a UPnP server, so if we improve that to offer equivalent features as the local library then the problem is solved: Just run an XBMC master instance, share it's library and point the other instances at that one. UPnP can support editing of information from clients as well, and queuing up of updates can be automated on the master instance.

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
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #24
jmarshall Wrote:It's non-trivial to change an XBMC-sent URI into something else. Rar's for instance are stored something like (details may not be 100, but it's something like this):

rar://<username>:<pass>@<urlencoded_path_of_rar>/<path_in_rar_to_file>

Now, <urlencoded_path_of_rar> may itself be inside a rar or inside a zip or whatever, which may in turn be inside some other container before eventually being on a smb server or whatever.

Agreed. However, if someone is placing RAR's within RAR's, ZIP's or whatever then we shouldn't be overly concerned with supporting a user base that (I believe) has to be a minority.

Short of containing multiple subs which can be compressed due to the textual nature, I don't see any real benefit to compressing files. Happy to be educated (PM me to keep this on topic).


jmarshall Wrote:Also, note that even a smb:// source may be available to some clients but not others (admittedly, very much a corner-case).Thus, even network drives need tagging with "available to this client".

Agreed. The impetus for this proposal is the central management of media and its associated metadata, and so we can safely ignore this corner case.

jmarshall Wrote:This completely kills the idea of just pointing XBMC at the database server and things just working.

jmarshall Wrote:I'm moving back from this idea in general. If you want to share libraries across multiple machines, IMO using something like UPnP is a better way to do it - you share the library and the media without having to care about it. XBMC already contains a UPnP server, so if we improve that to offer equivalent features as the local library then the problem is solved: Just run an XBMC master instance, share it's library and point the other instances at that one. UPnP can support editing of information from clients as well, and queuing up of updates can be automated on the master instance.

I've been boning up on UPnP over the last few days and also think it has a lot of excellent features, but it too has its own set of issues of which XBMC is, currently, not in a position to address.

I will consider that the proposed database abstraction in its entirety, is at this time, more of a specific user base requirement and will redirect my efforts into the more janitorial work regarding the database.
find quote
da-anda Offline
Team-Kodi Member
Posts: 3,361
Joined: Jun 2009
Reputation: 39
Location: germany
Post: #25
In all the discussion about local and network media shared between clients (which is a cool thing) - don't forget to take user accounts into account that might be supported some day (like in Boxxee). So the DB needs to store two more additional informations per media:
- username
- access (public/private/specific usergroup ID)

public media can be seen from any other user - private media only for the user that added it to the library. And if usergroups would get supportet - only members of the given usergroup could see the items.

------------------------------------------------------------

Another feature that might be taken into account in the DB-schema is content rating for children etc - so maybe another db-field needs to be added
- pgr (or how that is called in america)

That way XBMC would be able to list only media within certain pgr ranges (for kids "0-12" years, or for adults that don't want to see their kids stuff "12-*").

------------------------------------------------------------

As for the mapping tables I'd suggest the following structure (naming is just a draft and could be changed):
- uid_local
- uid_foreign
- table_local
- table_foreign
- match_field
- sorting

That way you can use one mapping table (with optimized indexes) to link between various tables and get a much cleaner db schema - although queries get blown up a bit.

------------------------------------------------------------

As for the merging of tables. I also favor merged tables for all incommon fields (title,path,media-type,tags,rating,username,access,pgr,...) and have separate tables for media specific data.



Just some suggestions from a newbie Smile
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 26,221
Joined: Oct 2003
Reputation: 178
Post: #26
XBMC already has per-user profiles, allowing completely separate databases.

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
da-anda Offline
Team-Kodi Member
Posts: 3,361
Joined: Jun 2009
Reputation: 39
Location: germany
Post: #27
it's not about completely separated databases based on the current OS user (which you where refering to, right?), but user accounts inside the very same database and ACLs in a shared library, which are barely needed in client-/server setups. Nobody likes to do all the indexing and scraping work again only that his wife can use her prefered skin or whatever.

I really like XBMC, but in my eyes it lacks quite some "enterprise" features that would make XBMC "THE" kickass mediacenter solution.
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 26,221
Joined: Oct 2003
Reputation: 178
Post: #28
You can share databases across profiles. It assigns access based on path.

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
firnsy Offline
Team-XBMC Developer
Posts: 104
Joined: Jul 2009
Reputation: 0
Location: Australia
Post: #29
I thought I would post some long overdue progress on this proposal.

I have spent the last few months on and off integrating/replacing the existing dbiplus library with OpenDBX. I have updated the primary database files for Music, Video, Program and View.

Along the way I've also made a number of additions to OpenDBX that I will attempt to push upstream for consideration into it's mainline.

Whilst those files compile nicely, the final linking stage is proving to be far more difficult. My autoconf-fu is still in it's infancy and am in need of a little guidance. If someone is able to provide some mentoring/one-on-one assistance with integrating a new library to the XBMC compilation process I'm all ears (or eyes).

In the meantime, I'll keep plugging away Big Grin
find quote
dbrobins Offline
Member
Posts: 73
Joined: Dec 2009
Reputation: 0
Location: Redmond, WA
Post: #30
firnsy Wrote:Correct. The latest SVN has it up to c21.

As part of this database redesign it would be great if the cxx columns were renamed to whatever they're actually used for (and given appropriate non-"text" types). The database wiki page has a list of some uses, which would indicate that the mapping is stable. Or is that not true - can what is in the cxx columns change depending on the scraper?
find quote
Post Reply