Tools and other software Batch to update (or clean) my TV recordings folder (1 Viewer)

[olli14]

I already asked for help in the german forum of mediaportal but there I did't get any answer.
All my tv recordings are stored in the folder "TV recordings". Further I have a folder "movies" where all my movies are stored. The folder "movies" is oberserved by moving pictures. When I record a movie and I want to keep this movie for a longer time then I move this movie from the folder "TV recordings" to the folder "movies" by using the plugin FileExplorer. Now I have to update the tv database because the tv plugin still shows the movie in the tv recordings submenu. I know that there is a button in the tv recordings submenu (in german: there is a button "Aufräumen") to do update the tv database but I would like to have a batch file.

Does someone has an idea?

I'm using the mySQL database.

 

[mm1352000]

...but I would like to have a batch file.

Why?

It would be possible to create a batch script to delete or update invalid entries.

MySQL supports the LOAD_FILE() function:
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_load-file

...which apparently can be used to check if a file exists. Therefore it may be possible to execute a "pure" MySQL script and avoid having to install any scripting environment (eg. PHP, Perl). For example, maybe try:

DELETE FROM Recording WHERE LOAD_FILE(fileName) IS NULL;

I suspect that this function would perform very slowly if you have many recordings. Therefore it may be better to create a "proper/full" script. I leave that up to you.

 

[olli14]

@mm1352000
Thanks for answering.

I don't know anything about mySQL Software. So please help me again.

I have all my tv recordings in the folder "e:\tv recordings". All tv series I have in the folder "e:\tv recordings\tv-series". I want these both folders to be checked for invalid entries and to update the mySQL database. Please can you show me exactly what I have to write down in my batch?

Thanks in advance.

olli14

 

[mm1352000]

...but I would like to have a batch file.

Why?

I'm still hoping you will answer why you want this script.
Why is the "clean up" function/button not enough? etc.

I don't know anything about mySQL Software.

Time to start learning then!

So please help me again.

Please first take some time to try to find a way towards the solution for yourself. If you get stuck I may be able to help you. However, I'm not going to just give you a solution, because then you will learn nothing and I would always have to help you in future if you had to fix or improve the script.

Please can you show me exactly what I have to write down in my batch?

No. As above, I'm not going to tell you exactly what you have to write. I've given you some tips. You are not helpless. If you really want this batch script, please be willing to put in some effort and time to get what you want.

 

[olli14]

Hi mm1352000,
you ask "why"! But I tried to explain the "why" in my first post. I wrote: ... When I record a movie and I want to keep this movie for a longer time then I move this movie from the folder "TV recordings" to the folder "movies" by using the plugin FileExplorer. Now I have to update the tv database because the tv plugin still shows the movie in the tv recordings submenu.
To update the tv database I can use the cleanup button/ function. But it's more comfortable to do this database update e.g. every morning with this batch.

Of course I want to learn but I need some help. Or is it as easy as this: create a Batch and copy this in the batch
DELETE FROM Recording WHERE LOAD_FILE(fileName) IS NULL;
and everything is working?

I will try!

 

[olli14]

I tested a little bit. These commands I used:
mysql --user=root --password=MediaPortal
use mptvdb;
delete recording where load_file(fileName) is null;
I got a message "7 rows are affected; 7 warnings"
Then I used the command "quit"
After that I started Mediaportal and now no tv recordings where shown anymore. I had to close mediaportal and had to start TV-server configuration. And there under "Recording --> Database import" I had to press the button "import selected files". Now all tv recording where shown again in Mediaportal.

Any ideas?

 

[mm1352000]

To update the tv database I can use the cleanup button/ function. But it's more comfortable to do this database update e.g. every morning with this batch.

Thanks for your answer. This is the part I was wanting to understand which was not included in your first post.

These commands I used:

Any ideas?

Take a look at the warnings. These may give some clues about why the script didn't do what was intended.

 

[olli14]

This script did absolutely not what I want. It deleted all 7 recording out of the database. MePo didn't show These 7 files anymore. I had to do a re-Import with "Recording --> Database Import" in tv-Server configuration. Then MePo Shows again these 7 files.
I have no idea how to do.
It's my understanding that I have to compare the existing files in the Folder "TV recordings" with the recording table in the mptvdb database.

 

[mm1352000]

Take a look at the warnings.

 

[olli14]

I got the warning:"Query ok, 8 rows affected, 8 warnings (0.00 sec)".
I don't understand what I can do further.
Attached two screenshots of what I have tested so far. See first screenshot: the table "recording" was empty after this test. I had to import all files again in tv-server configuration.
See second screenshot: at this point I had already deleted the file "RTL II Wetter.ts". That's why I got the info "8 rows in set, 7 warnings" I think.
Need some more help please!