VideoDatabase Optimalisation (don't use path in lookup) (1 Viewer)

[HenriM]

Finally I can give my first code-contribution to Mediaportal...

In the past it always annoyed me that for Movies the Videodatabase includes the path in the filenames in order to distinguish between Movies. In my opinion this is absolutely not needed and in most cases also not wanted. Let me first explain you my use-case.

I have two folders on my harddisk called:

S:\Movies
S:\Movies New

I always want to keep the movie information of both folders up to date and thus 'feed' it always immediately with IMDB information.

When files in the 'Movies New' are archived, I move the files to the 'Movies' directory. However up to now I always have to retrieve the information of these files again from IMDB again due to the fact that Mediaportal includes the path in its search in the Videodatabase for the movie. I don't think this is wanted/needed. I don't expect different Movies to have the same filename (including path).

When I check the code I saw the reason for this. It seems like a optimalisation for looking up a Movie which is done in 2 stages: first find all files of a specfied path and then search the filename in the returned list. I don't think this will gain you much performance here. We are not talking here about huge databases with video information which we want to traverse...

The solution is actually quite easy:

\Databases\Video\SqlLite\VideoDatabaseSqlLite.cs
Line 169:

Change
strSQL = String.Format("select * from files where idpath={0}", lPathId);
into
strSQL = String.Format("select * from files");

This way the path will not be included in the search in the Videodatabase.

Now my question to the official MP Development Team:
"What do you think of this change? Do you agree with it. If no, why not and if yes, how can I put this code into the official source-code?"

 

[Inker]

That won't work. I mean it will work, but only in shares view.

What if you view your movies by genre, or title, or any other db view and then want to play one? The path won't be right so MP can't find the file. So it doesnt really help when you move your files around, db views will become pretty much information only in this case.

You could add an option to update the path info when MP can't find the file in db views. For instace you could make it an option to input "search folders", so when you're in db view and want to play a file that it can't find, it could go through those folders, look for the filename and then update the db and play the file from the new location. Another option would be that in shares view when in the new location you could modify the lookup routine so that if the filename already exists the path will be updated and hence the db location will be correct again. The first option will require new configuration options, the second one not.

Hope this info helps

 

[HenriM]

Inker many thanks for your info. You are absolutely right. I completely oversaw this issue.

I was thinking what else is the most elegant solution and came up with the next solution.

\Databases\Video\SqlLite\VideoDatabaseSqlLite.cs
Line 169:

Just adding next piece of code on the same spot:

strSQL = String.Format("update files set idpath={0} where strFileName='{1}'", lPathId, strFileName);
results = m_db.Execute(strSQL);

just before the already existing code

strSQL = String.Format("select * from files where idpath={0}", lPathId);
results = m_db.Execute(strSQL);

This fix the problem and has an extra positive side-effect (assuming that filename will always be unique for each video-file, which in my opinion is a valid assumption). This way the database will always be up to date, even when moving files to another directory. There is only one minor issue. The change will not be visible in case the new directory is not already part of the 'titles' table. However nothing to worry about, because with the very first update of the new directory to this table, the problem is again automatically solved.

What do you think of this simple but elegant solution Inker?

PS: Probaly it's even better to put the updating between a transaction.

 

[Inker]

If I understand correctly that would be my solution #2.

As you mentioned it will only work if the path is already in the db. Not good. Add it if it isn't in.
You update the pathID for every file each time you browse a folder....not good because it will be slow (and unnessarily so). Doing it while browsing a folder with 200 items will probably kill performace. Why don't you add a few lines of code into the imdb lookup code, so if you move a file you can simply do a lookup again, but if your new code detecs the filename already in the db it will simply update (or insert) the path and not do the actual lookup again. In this case you could add a question to he user "A file with the same filename is already in the Database. Did you move this file?" This way it will work for users with several files with the same name but will also be simple and fast for users who move their files around.

It will require some user interaction, but IMHO it would be better and it will not remove functionality for other users.

 

[HenriM]

Thanks for your constructive input Inker! I Really appreciate it.

I agree that at this moment the solution is too straightforward. I can polish it a little (e.g. add to 'path' table when not present yet (already did that) and only update 'files' table when needed) but probaly it's better to follow your suggestion. I try to look into this ASAP and come back to you.

 

[HenriM]

Inker, now I have a much cleaner solution, thanks to your suggestions. I did next things:

Files VideoDatabase.cs and IVideoDatabase.cs

Added 2 methods to class VideoDatabase:
static public int CheckFileNamePresent(string strFilenameAndPath)
static public int UpdateForFileName(string strFilenameAndPath)

Implementation of these methods in File VideoDatabaseSqlLite.cs

Further in GUIVideoFiles.cs these 2 new methods are used:

In Method 'OnInfo'
line 1117 Added next code:

// Check if filename is already present in database
int lPathId = VideoDatabase.CheckFileNamePresent(strFile);
if (lPathId > 0)
{
VideoDatabase.UpdateForFileName(strFile);
}

If you want I can PM you the files. I am not sure yet if we should bother the user with a message-box in case a filename is already present in the database. I mean, for videofiles I don't expect that different videos with the same filename (should) exist. It think it's better that we silently update the database (files and movie table) with the corrent info in this case. What's your opinion Inker?

BTW: I really enjoyed this exercise very much. Is it possible/wanted that I can help the Mediaportal Development Team with programming issues? I could for example try to solve some documented bugs.

 

[zag2me]

HenriM, best thing to do is join the irc channel and talk with the dev team, its a really cool place to be and much easier to understand mediaportal programming. I'm sure they would welcome your input with open arms.

 

[Inker]

Very nice Henri.

To supply your code please submit a patch to sourceforge. The devs will then implement it (provided it meets certain standards).

And yes, you would absolutely be welcome to help out fixing bugs. Just submit some patches and after that you'll be usually invited to join the team. If you'd like join us in IRC to talk. Just announce your interested in helping out and people will point you in the right direction.

 

[HenriM]

I have submitted a patch in sourceforge with title: 'Videodatabae Improvement'
(yes the title does contain a typo )