Kodi Community Forum
database export/import: is the files table ignored? - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: database export/import: is the files table ignored? (/showthread.php?tid=378233)

Pages: 1 2

database export/import: is the files table ignored? - badbob001 - 2024-07-17

How can I import the content of the files table in MyVideos???.db into the mysql dg that I'm using now? I'm only interested in importing the watched data for files. I tried the library database export function but it unfortunately ignores files (seems to only export actors, movies, sets, musicvideos, and tvshows).

Any other option to import the files table data into mysql?

Update: I looked into importing the file table into the same table in mysql and:
  • I think I need to import the path table
  • I need to update the idFile and ifPath values so they conflict with the target table.
So not a simple task. Has something been written to make this easier?

RE: database export/import: is the files table ignored? - scott967 - 2024-07-21

For watched and resume, export library to separate xml files with overwrite on the database that has what you want.   On the new database import from the xml files (for example, set content on the source and scan, or do refresh on individual items).

scott s.
.

RE: database export/import: is the files table ignored? - badbob001 - 2024-07-23

(2024-07-21, 05:24)scott967 Wrote: For watched and resume, export library to separate xml files with overwrite on the database that has what you want.   On the new database import from the xml files (for example, set content on the source and scan, or do refresh on individual items).

I've tried this before without success, but to be sure:
  1. I installed a blank instance of kodi on windows and then I add two video source folders (e:\temp\videos\source-1\ and e:\temp\videos\source-2\) and set them as type None. 
  2. Through Videos > Files > [source name], I watch some video files.
  3. Close Kodi and view %username%\AppData\Roaming\Kodi\userdata\Database\MyVideos131.db with 'DB Browser for SQLite'. Confirm that table 'path' has entries to source folders, and table 'files' has entries to each video file watched.
  4. Close DB Browser and run Kodi.
  5. System > Media > (Video Library) Export Library > Separate > (export thumbnails/fanart) No > (overwrite) Yes
According to the documentation, this should export nfo files, which I assume would be created next to each video file. I look at the source folders and do not see any nfo files created. The kodi log, even in debug, doesn't show any entries for the export activity.

I do the export again but choose 'Single File'. I think the output should be an xml file. I expect kodi to ask me to pick the folder to save the export file but it doesn't. Again, debug log doesn't seem to show any export activity.

My guess is that the library export function is essentially being ignored because my video library is empty (eg no movies, tv shows, or music videos) and the export doesn't consider video files.

RE: database export/import: is the files table ignored? - Hitcher - 2024-07-23

(2024-07-23, 18:25)badbob001 Wrote: I installed a blank instance of kodi on windows and then I add two video source folders (e:\temp\videos\source-1\ and e:\temp\videos\source-2\) and set them as type None.
They need to be set as Movies or TV Shows.

RE: database export/import: is the files table ignored? - Karellen - 2024-07-23

And nfo files are a library feature. If the movies/tv shows are not in the library, then there is nothing to export.

RE: database export/import: is the files table ignored? - JWoofWoof - 2024-07-23

I have a very similar need to transfer watched and resume status from Kodi 17.6 to 19.5. (I have to use 19.5 for a specific skin.)  I exported as single file, and get all the info I need. However, I don't want it to import anything except playcount, lastplayed, and resume info.

I can clean the single xml file with:

grep -E 'videodb>|<\?xml|version>|<path>/|<filenameandpath>|<basepath>|movie>|resume>|<position>|<total>|<playcount>|<lastplayed>' videodb.xml > watcheddb.xml

But what I don't know is what the importer keys on... is it path? title? Something else? Would this even work to MERGE the watched info without changing anything else?

RE: database export/import: is the files table ignored? - Karellen - 2024-07-23

Maybe try this addon... https://forum.kodi.tv/showthread.php?tid=164652

RE: database export/import: is the files table ignored? - JWoofWoof - 2024-07-23

(2024-07-23, 22:21)Karellen Wrote: Maybe try this addon... https://forum.kodi.tv/showthread.php?tid=164652

I have seen that one. Unfortunately, it relies on TMDB ids, and our home videos don't have tmdb entries of course. There was another one:

https://forum.kodi.tv/showthread.php?tid=129448

But it keyed on title and year, and again, most of my vids don't have year metadata. And it no longer works anyway.

RE: database export/import: is the files table ignored? - Karellen - 2024-07-23

The other method is direct editing of the database. But if you choose to attempt this, make sure you have a backup of your database first, in case you screw up the process.

Get a copy of your v17.6 database that has all the playcount data
After scanning your library into v19.5 make a copy of the database for backup.
Open both v17.6 and v19.5 databases
Go to the files table in both.
Sort both tables by filename.
Hopefully all the filenames are identical in both databases, so both lists should be identical.
Now just like an Excel spreadsheet simply copy the contents of one column into the same column in the other database.
Save the database

RE: database export/import: is the files table ignored? - JWoofWoof - 2024-07-23

Interesting! That sounds like a possible solution, since both have the same sources, and both can be cleaned/scraped first so the filename entries SHOULD be the same count/order.

Thank you. I will give that a try once I find a DB viewer for macos that will do the Excel like column copy.

RE: database export/import: is the files table ignored? - Karellen - 2024-07-23

(2024-07-23, 23:14)JWoofWoof Wrote: I will give that a try once I find a DB viewer for macos that will do the Excel like column copy.
This is what I, and many others here, use... https://sqlitebrowser.org/

RE: database export/import: is the files table ignored? - JWoofWoof - 2024-07-24

Thanks again for the help.

I am a little stuck. The 2 databases have the exact same count of items in movie and movie_view. I even pasted the c22 path columns from the old and new db's into 2 different text files, and did a diff on them, and they are identical. But, the files table is vastly different due to some 3000 dead entries that don't go away with a normal DB clean operation.

movie_view is the only one that has both the path and the lastPlayed, viewCount, etc that I want to copy from DB to DB. DB Browser refuses to allow me to past into a view, only into a table... which is sort of odd that it allows me to unlock the view for editing, but not let me paste without an error stating that editing in view mode is not allowed. I thought I saw that view editing was added years ago, but apparently I'm missing a few steps.

I may try going down the path of using a deep db clean with script.database.cleaner (the old one for 17 and the new one for 19+), and see if I can get matching file tables.

RE: database export/import: is the files table ignored? - Karellen - 2024-07-24

(2024-07-24, 07:27)JWoofWoof Wrote: movie_view is the only one that has both the path and the lastPlayed, viewCount
No, only work on the files table.

RE: database export/import: is the files table ignored? - JWoofWoof - 2024-07-24

(2024-07-24, 07:31)Karellen Wrote:
(2024-07-24, 07:27)JWoofWoof Wrote: movie_view is the only one that has both the path and the lastPlayed, viewCount
No, only work on the files table.
OK, this isn't going to work without a lot more effort than expected. The files table from MyVideos107.db is full of every file Kodi has ever seen, and even the script.database.cleaner doesn't touch that table, so the table from 17 and 19 will never match to be able to do a column copy. I'd have to do a dump of every item in the files table with a lastPlayed entry, build the full path using the paths table, and then create a file with SQL UPDATE/SET/WHERE statements to try and pull it all back in using filename as the key. It would break on DVD VIDEO_TS entries, but I could live with that.

RE: database export/import: is the files table ignored? - JWoofWoof - 2024-07-25

I did find a solution to transfer last played, play count and resume info from Kodi 17 to 19, without just upgrading the db in-place (It was ancient and crusty and I really wanted a clean start.)

This works for non-scraped titles as well as scraped titles. Probably only works for movies, but that is all I cared about for home movies not indexed by tmdb.

On new machine, starting out with a clean Kodi install, using sources with the same paths. Scan with TMDB to catch all the commercial stuff.

The transfer:
Export strPath, strFilename, playCount, resume info, etc as a tab delimited file from SQlite studio from the old DB. (I used a select statement to grab just what I wanted from the various tables)
Use shell script to iterate through each entry in the tab separated file to see if it still exists by path+filename.
If present, create an .nfo that contains just the info present in the db (playCount, lastPlayed, etc.) and place it at the same path.
Change advancessettings.xml to set importwatchedstate and importresumepoint to true.
Update library to pull this info in.
Remove the advanced settings so it doesn't reimport the generated nfos.
Search media source for files ending in .nfo that were generated on the date the script above ran and delete them just to be sure they don't get imported again later.
Profit.

This forum uses Lukasz Tkacz MyBB addons.