Chad Don Cooper Projects and ponderings…

24Jun/170

Kodi – Update SQL databases when files move

In a similar vain to updating my ripped TV show paths in Sickrage's database, I needed to do the same for Kodi.
I used this script to change shows beginning with certain letter ranges to a specific folder.


-- Update path table
UPDATE path SET strPath = REPLACE(strPath,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where strPath glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE path SET strPath = REPLACE(strPath,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where strPath glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE path SET strPath = REPLACE(strPath,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where strPath glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update episode table
UPDATE episode SET c18 = REPLACE(c18,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where c18 glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE episode SET c18 = REPLACE(c18,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where c18 glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE episode SET c18 = REPLACE(c18,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where c18 glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update art table
UPDATE art SET url = REPLACE(url,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where url glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE art SET url = REPLACE(url,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where url glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE art SET url = REPLACE(url,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where url glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update tvshow
UPDATE tvshow SET c16 = REPLACE(c16,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where c16 glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE tvshow SET c16 = REPLACE(c16,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where c16 glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE tvshow SET c16 = REPLACE(c16,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where c16 glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

-- Update files
UPDATE files SET strFilename = REPLACE(strFilename,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 1 (#-G)')
where strFilename glob 'smb://server/Video/TV 1 (#-S)/[1-9A-Ga-g]*';

UPDATE files SET strFilename = REPLACE(strFilename,'smb://server/Video/TV 1 (#-S)', 'smb://server/Video/TV 2 (H-S)')
where strFilename glob 'smb://server/Video/TV 1 (#-S)/[H-Sh-s]*';

UPDATE files SET strFilename = REPLACE(strFilename,'smb://server/Video/TV 2 (T-Z)', 'smb://server/Video/TV 3 (T-Z)')
where strFilename glob 'smb://server/Video/TV 2 (T-Z)/[T-Zt-z]*';

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.