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

23Jan/160

Erratic wipers

So the wipers on my Z4 had been misbehaving for a while, deciding to park at a random point on the windshield rather than at the bottom. I was kinda living with it until recently when they wouldn't stop at all or they'd stop and not start again without "rebooting" (:D) the car.

Anyhow I finally got around to fixing them, the issue was with grease inside the motor getting onto the copper contact plate. Cleaning the grease off the plate cures all the problems. Hardest part is removing the tray under the windscreen. This uses the usual BMW screws made from a plastic-cheese composite to hold it in place. Any screwdriver I used seems to shear the heads so if I was to do it again I'd buy spares to replace the ones I take out. BMW decided to use plastic clips to hold screws that themselves hold the tray down vertically too. Four of these clips snapped off, they were plastic welded on from what I could tell. I used gorilla glue to put these back after fixing the motor. One of the white screw also went AWOL for a while, I fished this out of a rubber drain on the right side of the engine bay. There are a couple of these rubber drains which have flaps at the end, I made sure these were all clear of rotting leaves before I put everything back together.

Filed under: Fixes, Problems No Comments
25Nov/140

HD-DVD disc rot

Turns out it was a good thing Blu-ray won the HD war after all, since a good chunk of the movies I own on HD-DVD are no longer working. Despite having no visible scratches or marks, a high percentage of discs, mostly Warner Brothers releases, have suffered disc rot. I'm about halfway through ripping my collection, the picture shows a working pile (left) versus a non working pile.

Working vs non-working HD-DVDs

Filed under: Problems No Comments
4Jan/140

Faulty Optoma EW533ST

I've had a faulty Optoma EW533ST kicking around for sometime now. It has a common fault with the DLP (DMD) chip which manifests itself as lots of white and black dead pixels.
The projector itself is actually an Optoma GT720, a fairly well respected 720p projector, which comes highly recommended for gaming, as stated in this review.
Specs:

  • Display Technology Single 0.56" WXGA DC3 DMD chip DLP® Technology by Texas Instruments
  • Resolution Native WXGA 1280 x 800
  • Brightness 2500 ANSI Lumens
  • Contrast 3000:1
  • Noise Level 27/29dB (STD/BRIGHT mode)
  • Aspect Ratio 16:10 Native, 4:3/16:9 Compatible
  • I/O Connectors Inputs: PC/Video: HDMI, 15 Pin D-sub (RGB/YPbPr/Wireless), S-Video, Composite, Audio In - RCA
  • The lamp life is quoted as high as 5000hrs!

All in all, worth saving in my opinion. To avoid ordering the wrong DMD chip I proceeded to strip down the projector and remove the DMD chip to get the part number, which turned out to be 1280-6038b. Reading showed that the 1280-6038b and 1280-6039b are both interchangeable, along with some other models. Further to this a newer model, the 1280-6338B, was released which apparently addresses the dead pixel issue.

The chip I have purchased can be found here: http://www.aliexpress.com/item/100-New-DMD-new-version-1280-6338B-replace-1280-6038B/898295952.html.

Filed under: Problems, Repairs No Comments
27Jun/110

R4i 3DS Gold + V2.0.1-3E

Had some trouble getting the R4i Gold 3DS working on my 3DS firmware v2.0.1-3E. Turns out there are three main types of R4i Gold cards on the market: R4i Gold V2.0, R4i Gold upgrade and the R4i Gold V1.4.1. Each has it's respective website printed on the box which seems to be the easiest way of figuring out which you've bought.

Each of these cards uses a different firmware and some of the kernels aren't cross-compatible. My particular card is the V2.0 from r4ids.com. The firmware on the card's official website does not work with a fully updated 3DS at present. A Japanese game appears in the menu but a system error occurs when you try to run it. After some research it turns out the card is (unofficially) compatible with the Acekard firmware. There is some debate on whether cross-flashing will break the R4i, but for me it went smoothly and the card now boots as Ubisoft Chessmaster.

Update (Jul 5): A new firmware has been released on r4ids.com. I haven't checked it out as the Acekard firmware has been working fine.

6Jun/110

Netgear DGN2000

So I woke up this morning to find the Internet not working on my desktop PC. Pages were loading a small amount, usually to the title, then stopping completely. I tried to log into the router to reboot it... same problem. At breakfast I'm using my pocket PC to check my mail and it's all working fine, which I found weird. Not having any more time to check out the cause, I headed off to work.

Lunch time at work usually allows me to catch up non essential jobs which can require a period of time without interruption. Today I wanted to make a new PHP clock for the digital signage system which sync'd with an NTP server. To save me the effort of setting up a development web server I normally use my Apache server at home. Upon trying to log in remotely today I found the same network issue as I saw in the morning, data was barely downloading. Logging into the router however was fine, so I figured the switch between the server and router was playing up. Unfortunately I bought an unmanaged switch so there was nothing I could do until I got home.

I get home later, make coffee and reboot the switch. The problem is still there. Go to the garage with a laptop, swap some cables, same problem, packet loss and slow ping times. Nothing helped by testing the switch. Next I went to the router with the laptop and plugged into another port, everything worked fine! After testing the cable to the garage and moving things to different ports I got down to the issue. The fourth port of the wireless router had developed a fault.

Looking around the Netgear forums this appears to be a common problem. I now have two choices, send the unit back under warranty and await its return or open the unit up and swap out all the capacitors.

TLDR: Netgear DGN2000 wireless routers have a high failure rate on the Ethernet ports, which I was unaware of 8 months ago when I purchased one 🙁