[jmgibson1981]

https://www.erol.name/how-to-additionall...d-entries/

I was searching for a way to clean out my sql db of stuff that accumulated over time. I found the above website and I tried it. It didn't seem to hurt anything and wiped out 1400 entries give or take. I'm posting here to get other's opinions as I know virtually nothing about sql syntax or what the above does. As it recommends always make a backup before messing with your db. I suppose I'm just wondering if it's worth it to run these in a script once every 6 months or so depending on db activity?

[Klojum]

website

Hmm... An article posted on 20. 11. 2015. The Kodi database has seen a couple of changes since then.
That article is only mentioning the "files" table from which entries are removed. The Kodi database holds a few more database tables than that.

I'll be the first to admit that Kodi's "garbage collection" could do with an upgrade, but nothing of the data that is left behind now is a "problem" in terms of overall usage.

I'm posting here to get other's opinions as I know virtually nothing about sql syntax or what the above does.

I would suggest if you have no clue of what SQL is or can do, you better leave the databases as they are. They are quite small compared to the media collections that most Kodi users have, so nothing will get in the way.

If you want to do a rigorous database cleaning, you can do the following:
- Enable the watched state and resume points for export in the advancedsettings.xml file and restart Kodi.
- Do a full Video Library Export (preferably with separate nfo files), and exit Kodi.
- Rename/remove the MyVideos116.db SQLite file or the MySQL/MariaDB MyVideos116 database table.
- Restart Kodi, reinstate/confirm your video source(s), and scrape your media collection.
- You should have a cleansed video library without any phantom entries.

As an example, I'll show you which queries IMO should be done when removing a movie or a tv show.

When removing a movie:

php:
'DELETE FROM actor_link     WHERE media_type="movie" AND media_id='.$idFile;

'DELETE FROM actor_link     WHERE media_type="movie" AND media_id='.$idFile;

'DELETE FROM country_link   WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM director_link  WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM writer_link    WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM uniqueid       WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM studio_link    WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM rating         WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM genre_link     WHERE media_type="movie" AND media_id='.$idMovie;

'DELETE FROM art            WHERE media_type IN ("movie", "episode") AND media_id='.$idMovie;

'DELETE FROM streamdetails  WHERE idFile IN ( SELECT idFile FROM movie WHERE idMovie='.$idMovie.')';

'DELETE FROM bookmark       WHERE idFile IN ( SELECT idFile FROM movie WHERE idMovie='.$idMovie.')';

'DELETE FROM path           WHERE idParentPath='.$movie_root_path.' OR idPath='.$movie_root_path;

'DELETE FROM files          WHERE idFile IN ( SELECT idFile FROM movie WHERE idMovie='.$idMovie.')';

'DELETE FROM movie          WHERE idMovie='.$idMovie;

When removing a tv show:

php:
'DELETE FROM path           WHERE idParentPath='.$tvshow_root_path.' OR idPath='.$tvshow_root_path;

'DELETE FROM actor_link     WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM country_link   WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM director_link  WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM writer_link    WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM uniqueid       WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM studio_link    WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM rating         WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM genre_link     WHERE media_type="tvshow" AND media_id='.$idShow;

'DELETE FROM art            WHERE media_type IN ("tvshow", "episode") AND media_id='.$idShow;

'DELETE FROM streamdetails  WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

'DELETE FROM bookmark       WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

'DELETE FROM files          WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

'DELETE FROM tvshowlinkpath WHERE idShow='.$idShow;

'DELETE FROM episode        WHERE idShow='.$idShow;

'DELETE FROM seasons        WHERE idShow='.$idShow;

'DELETE FROM tvshow         WHERE idShow='.$idShow;

Don't use these lines, they have variables that require content.
It goes to show that you'll need a bit more than just deleting records from the 'files' table alone if you truely want to clean up the database.

[jmgibson1981]

- Restart Kodi, reinstate/confirm your video source(s), and scrape your media collection.
- You should have a cleansed video library without any phantom entries.

would it get my watched / resume points from the nfo files? That is my main thing. Personally I could give a damn about the watched status, and the resume is just nice. But my wife would lose it if i lost both of those. Those are her things. She is on a goal to watch all media. I keep adding to the library faster than she can watch it. Just something she likes to try to do.

I assume the .nfo would just be put in the sources folders and I could remove them afterwards?

 "problem" in terms of overall usage

Just minor ocd.

[Klojum]

would it get my watched / resume points from the nfo files? That is my main thing.

Yes. Double-check the contents of a nfo file if you have doubts before deleting your database.

I assume the .nfo would just be put in the sources folders and I could remove them afterwards?

Nfo files are placed next to their video files. Removing them is unnecessary but is of course possible.
Alternatively do an export to a single file if you prefer that.