2013-02-08, 21:31
Wiki_Structure#XBMC_Core (wiki) see database section
2013-02-18, 23:33
I'v done some tuning on the model, if anyone is interested in testing it.
First off, the NODELINKFILE table needs a slight tweek. This script will make that adjustment to any existing copies of the db.
I've added some indexes to improve view performance, here.
And here are some views that demonstrate how the database allows for per profile filtering of content.
The EPISODE_V view is the most complex and in my test environment, it will return a complete list of episodes available to a profile in 0.343 seconds.
The complete database script is here. (I know I should use GIT, but its blocked at work... I will get this into a source control soon)
My db is relatively small, see the data below. I would really like to test performance with a larger db. Anyone out there willing to give me a copy of their Frodo database?
Current Database stats
First off, the NODELINKFILE table needs a slight tweek. This script will make that adjustment to any existing copies of the db.
I've added some indexes to improve view performance, here.
And here are some views that demonstrate how the database allows for per profile filtering of content.
The EPISODE_V view is the most complex and in my test environment, it will return a complete list of episodes available to a profile in 0.343 seconds.
The complete database script is here. (I know I should use GIT, but its blocked at work... I will get this into a source control soon)
My db is relatively small, see the data below. I would really like to test performance with a larger db. Anyone out there willing to give me a copy of their Frodo database?
Current Database stats
Code:
Type Count
Country 36
Episode 2439
Genre 35
Movie 397
Season 189
Set 57
Studio 279
TV Show 67
2013-02-19, 00:04
It would be interesting to know if 256MB/512MB Raspberry Pi database performance is better or worse with the proposed database schema, what kind of kit are you testing on right now? My metadata is in MySQL otherwise I'd have slung you my database for testing!
2013-02-19, 00:17
Currently testing on Win 7 32 bit Core2 Duo with 2Gb RAM.
2013-02-19, 00:30
It's just the increased number of joins that worries me - hopefully you can keep those on lesser systems in mind!
2013-02-19, 00:40
Yes, there are a lot of joins. I have examined the explain plans on all the views, and they are using either the PK or the additional indexes that I have defined, which should mean solid performance (in theory at least....)
2013-03-01, 10:10
Hey, it looks like the links are hosed. i was wondering if i could grab this to play around with.
2013-03-01, 11:00
Nice work DecK.
It would be fantastic if you could get some of this into a place that will aid others in helping out (i.e. github).
A couple of thoughts in the meantime:
1. Currently scrapers and scraper settings are saved per-path. i.e. not just at the source level. Consider a single TV Show for example that needs scraping in German or some such. i.e. putting scraper settings only at the source level means potentially losing this.
2. Not all paths are hierarchical. i.e. you can't always just store a relative path and concat them together. Simple examples are rar's, others are multipath sources, though perhaps this is a property of the source (1:N mapping).
3. It would be useful to be able to define "this profile has access to everything excluding things that match this set of rules". This should be possible I think with your model, but it needs to be able to hit quite a few potential fields - obvious ones being source, mpaa rating etc.
4. It would be nice to be able to associate a folder with an item in the library, rather than just a file. i.e. consider browsing the filesystem - we want to be able to quickly replace file and folder items with their library counterparts.
Cheers,
Jonathan
It would be fantastic if you could get some of this into a place that will aid others in helping out (i.e. github).
A couple of thoughts in the meantime:
1. Currently scrapers and scraper settings are saved per-path. i.e. not just at the source level. Consider a single TV Show for example that needs scraping in German or some such. i.e. putting scraper settings only at the source level means potentially losing this.
2. Not all paths are hierarchical. i.e. you can't always just store a relative path and concat them together. Simple examples are rar's, others are multipath sources, though perhaps this is a property of the source (1:N mapping).
3. It would be useful to be able to define "this profile has access to everything excluding things that match this set of rules". This should be possible I think with your model, but it needs to be able to hit quite a few potential fields - obvious ones being source, mpaa rating etc.
4. It would be nice to be able to associate a folder with an item in the library, rather than just a file. i.e. consider browsing the filesystem - we want to be able to quickly replace file and folder items with their library counterparts.
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.
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.
2013-03-01, 21:43
(2013-03-01, 10:10)el_cabong Wrote: Hey, it looks like the links are hosed. i was wondering if i could grab this to play around with.
OK, I've uploaded two scripts to Github.
https://github.com/declankenny/Video_Database_Proposal
To test the new structure
- Make a copy of your video database.
- Open the copy in your editor of choice and run the Build_New_Structure script
- Then run the Migrate_Sample_Data script to populate the new tables and delete the old.
2013-03-01, 21:58
(2013-03-01, 11:00)jmarshall Wrote: It would be fantastic if you could get some of this into a place that will aid others in helping out (i.e. github).Done, see prior post.
(2013-03-01, 11:00)jmarshall Wrote: Currently scrapers and scraper settings are saved per-path. i.e. not just at the source level. Consider a single TV Show for example that needs scraping in German or some such. i.e. putting scraper settings only at the source level means potentially losing this.Sorry to be pedantic, but when you say 'path' do you mean folder(directory) or could you also be referring to an individual file?
(2013-03-01, 11:00)jmarshall Wrote: 2. Not all paths are hierarchical. i.e. you can't always just store a relative path and concat them together. Simple examples are rar's,Can you explain this a little more? I've never used a rar and don't see how a path cannot be hierarchical. Sorry for being dumb...
(2013-03-01, 11:00)jmarshall Wrote: others are multipath sources, though perhaps this is a property of the source (1:N mapping).In my thinking, each source has a single root folder. This would be a change from the current way it works but would not be a reduction in functionallity.Current multipath sources would become multiple sources.
(2013-03-01, 11:00)jmarshall Wrote: 3. It would be useful to be able to define "this profile has access to everything excluding things that match this set of rules". This should be possible I think with your model, but it needs to be able to hit quite a few potential fields - obvious ones being source, mpaa rating etc.Rule based accessis a great idea. It will need some though as it could get very complex (nested AND OR NOT condition). I'm not sure I would want to bite that much off in the initial pass.
(2013-03-01, 11:00)jmarshall Wrote: 4. It would be nice to be able to associate a folder with an item in the library, rather than just a file. i.e. consider browsing the filesystem - we want to be able to quickly replace file and folder items with their library counterparts.This one should be very straight forward. I think I can cover it with eitherthe addition of a FK in the Node table back to the directory table - if its a single folder, or a NodeLinkDirectory table for M:M relationships.
2013-03-05, 10:10
(2013-02-08, 01:39)DecK Wrote: As my post here got no traction at all, I've decided to take it a step further and work up a prototype for a new video database. The attached files consitiute that prototype.
At this point I am looking for feedback of any kind.
Nice to see you have not given up on this and I sure hope it will eventually make it to XBMC.
Here are some comments from user/skinner point of view, which I hope you will find useful:
Quote:add new media types (Home Movies, Documentries, etc.) to the library without the need to add new tables.
It would probably be good to have thumb and fanart support for new media types in DB. Reasoning is that you can do this in skin for standard items, but not with new (custom) media types.
Quote:allow movies to be in multiple sets.
Don't know what you think, but it might be useful to have organization units (sets, collections, groups call it what you like) for any media. Reasoning is you probably would like to organize your Home movies into collections as well, not just Movies. Movies could probably benefit from more flexible, user defined, sets as well.
2013-03-05, 14:48
would be great to have a UML of the database design to get a better overview and can optimize even more in a collaborative way (using some opensource UML tool - probably MySQL-Workbench).
2013-03-05, 15:06
I'm not sure if this applies here, but one thing I really want for movies is to have the (Watched, Unwatched, All) view setting be node/playlist specific, rather than global. When I go to the Home Movies node, I would like the view to show "All" content (because they have all been watched many times), but for the Theatrical Movie node, I would like the view to show only "Unwatched". Currently, if I forget to switch back to "All" view after watching Theatrical Movies, the next person who goes to the Home Movies node gets an empty list (then you get those confused calls for help 
).
).
2013-03-05, 16:56
(2013-03-05, 14:48)da-anda Wrote: would be great to have a UML of the database design to get a better overview and can optimize even more in a collaborative way (using some opensource UML tool - probably MySQL-Workbench).
Here is the UML for "File" section.
And the "Library" section.
(2013-03-05, 10:10)pecinko Wrote: It would probably be good to have thumb and fanart support for new media types in DB. Reasoning is that you can do this in skin for standard items, but not with new (custom) media types.This model will allow for any type of artwork to be associated with any node type.
(2013-03-05, 10:10)pecinko Wrote: Don't know what you think, but it might be useful to have organization units (sets, collections, groups call it what you like) for any media. Reasoning is you probably would like to organize your Home movies into collections as well, not just Movies. Movies could probably benefit from more flexible, user defined, sets as well.This is supported by this model.
Any type of 'group' would just be another NodeType and adding individual items to an instance of that type would simple mean adding records to the NodeLinkNode table to define the content of that group.
2013-03-05, 17:16
(2013-03-05, 15:06)sfontes Wrote: I'm not sure if this applies here, but one thing I really want for movies is to have the (Watched, Unwatched, All) view setting be node/playlist specific, rather than global. When I go to the Home Movies node, I would like the view to show "All" content (because they have all been watched many times), but for the Theatrical Movie node, I would like the view to show only "Unwatched". Currently, if I forget to switch back to "All" view after watching Theatrical Movies, the next person who goes to the Home Movies node gets an empty list (then you get those confused calls for help
This is more of a Skin design request rather than a feature of the database.
