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

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.

Pretty sure the latest SVN only goes up to c20. I just submitted a patch today which adds a c21.

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

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.

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

XBMC already has per-user profiles, allowing completely separate databases.

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.

You can share databases across profiles. It assigns access based on path.

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

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?