[jmarshall]

Some thoughts. Apologies - it's a bit of a braindump :p

Short form for those that can't be arsed reading it all: How paths are defined per client is the key piece of information that needs to be determined.

Long form for those with time on their hands:

1. Consolidation is really just for the benefit of not having multiple databases on the server. There is very little need to cross-link the 3 databases we have at the moment, and the small amount of cross linking that might be wanted can easily be done externally. Music videos naturally belong in the music database rather than the video database, though there are ofcourse technical issues in getting them in there (lack of tagging a major one, and also having to make sure the player system supports them well.)

2. The big problem is dealing with paths and profiles as already discussed above. This is a non-trivial problem. Ideally, we want to be able to just point XBMC at a database and we're done. We don't want to have to care that the paths may be different and so on. Thus, translation of paths needs to be either done in the database or in the client. It seems to me that doing this in the client makes the most sense, though I currently have no solid ideas on how to handle this. It's not as simple as a copy and paste for several reasons - rar/zip/stacks/multipaths etc. all need handling, and these are not naturally hierarchical. Multipaths are easy (just store multiple rows to define a 'node'), and stacks are probably doable as well. Rars and Zips are tricky, as theoretically you can have arbitrary numbers of movies within rars which may be within a zip or whatever. This is something that needs solving prior to finalizing how paths and urls are stored. 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.

3. Metadata has to be completely separate from file data. The link has to be very carefully setup. My thinking for the video library is as follows:

a. A "video" table that basically contains a filtered version of the filesystem. Each entry has a URL to the item on the filesystem that this entry covers (could be a folder or a file, a stack of files, or part of a file), along with the video URL that should be played when the item is clicked on, along with some other pertinent info, such as pathid (path that this item resides in), nodeid ("node" or source that this item resides in), a hash to enable rescraping and so on. Each entry represents a playable item.

b. A "filedetails" table would have information on the underlying file(s) for that item, such as the streamdetails and other file-based information (size, date, etc.) This is stuff that can be removed when the "video" item is removed, as it's easily generated from the file.

c. A "metadata" table handles all the metadata for this item. This is scraped info and local info. It'll be fixed field and will also have key/value pairs. This is information that may be scraped from online sources or local sources, and thus should not be removed if possible. It may be split into two, as I see a use for a single-row of information that gets key details needed for sorting (eg title, sorttitle, originaltitle, rating, runtime - one off stuff) and additional information used for grouping (genre, group, actor, director, studio, year, <insert_user_definable_grouping> etc.)

d. Multiple "video" items may link to a "metadata" item, so a video->metadata link can be made. It's a n:1 relationship though I think, so a foreign key in the video table to the metadata table will suffice.

The idea is that the video table allows for fast listing based on path or fast listing based on node or source (eg "all movies"). Once we have the listing, we then background load the rest of the information (one query per entry).

Cheers,
Jonathan