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]*';

22Jun/170

Sickrage – Update SQL database when files move

I currently have all my ripped TV shows spread across several drives in Windows. I use Sickrage to maintain an index of them as it produces media information which can be used by Kodi. Rather than using RAID, I have chosen to split the volumes by first letter of each show. I do this as I'd rather try recovery on a single NTFS drive/partition and I'm not in the mood to go ZFS anytime soon.
This does mean I sometimes have to shuffle around data between the drives which makes Sickrage and Kodi unhappy. Rather than go through updating paths manually however I tend to run the following scripts using SQLiteSpy after taking a backup of the db file.

Can view shows 0-9 and A-G using:
select * from tv_shows where show_name glob '[1-9A-Ga-g]*' order by show_name asc

You can move shows H-S from T:\ to U:\ with:
UPDATE tv_shows SET location = 'U:\' || SUBSTR(location,4) WHERE show_name GLOB '[H-Sh-s]*'

6Feb/160

PHP 7 Apache x64 Upgrades

Updating to the latest releases of Apache and PHP 7, both of which are running in x64 mode was quite painless. A few things needed updating; scripts running the older mysql connector, scripts using pass by reference (=&) and phpVirtualBox. 64bit PHP, finally.

Filed under: Upgrades No Comments