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

4Feb/160

Avoiding TCP/IP Port Exhaustion

Having had lots of network problems with TIME_WAIT and "no client ports available", I finally got around to tweaking the TCP/IP pool settings on my Windows Server.
The following settings can be found in the registry at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
I used the following settings (DWORD32 Decimal):

  1. TcpTimedWaitDelay = 30
  2. MaxUserPort = 65534
  3. TcpNumConnections = 16777214
  4. TcpMaxDataRetransmissions = 5

I probably need to look at what's running in more detail at some point, but these settings have kept everything humming along for the time being.

Filed under: Fixes No Comments
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
19Jan/160

771 – 775 Xeon CPU Mod

Through some late night surfing I recently discovered the socket 771 to 775 CPU mod. Provided you have a socket 775 Intel motherboard with a supported chipset, there's a good chance you can run a server class Xeon CPU in your humble old desktop motherboard.

Knowing this, curiosity got the better of me, not least since I had a salvaged EVGA nForce 790i Ultra SLI Motherboard sat in my garage which I've been meaning to test for ages anyway. The motherboard is pretty nice considering it's 8 years old. Triple SLI, DDR3, 1600MHZ FSB... I always felt having it collecting cobwebs.

One (albet expensive at £10) pre-flashed BIOS chip later I was ready to look at which CPU to buy. Now the fastest CPU, which actually demands a 1600MHz bus is the 3.4GHz 12MB Intel Xeon X5492, but those are running at £60+ due to the increased demand. Although the board is probably worth £100 working on eBay, I wasn't about to risk buying a CPU for an untested board. Instead I opted for an Intel Xeon X5460 3.16GHz 12MB cache with a FSB of 1333MHz, the same as the RAM I had. Given that it cost me £20 delivered from Korea, I plan on overclocking it as much as humanely possible using the 1600Mhz headroom.

The mod is pretty simple. You connect some of the pins on the underside of the CPU with a pre-made piece of tape and cut the tabs off the CPU socket allowing the chip to sit flush.
I've not had time to test yet as I need to source a cheap PSU for the build. Speaking of which I've also got an Ati 7850 with a blown mosfet and chipped resistor to fix, if that happens then I could have a nice little ghetto gaming rig.

Filed under: Fixes, Tweaks No Comments
7Jan/160

VS2015 DNVM bug

In trying to run all the fancy new open source .net framework stuff I discovered a bug in the DNVM script which is included with the automatically downloaded RC1 version included with Visual Studio 2015. As documented here it's been noted to only affect users with a space in their username.

Running the self update commands in dnvm didn't fix the issue and I had to resort to manually swapping the files in "C:\Program Files\Microsoft DNX\Dnvm" with the latest from the github repo at https://github.com/aspnet/dnvm/tree/dev/src

Filed under: Fixes No Comments
7Jan/160

Unbricking my Acer C720 Chromebook

Almost inevitability through all my tinkering with my Chromebook, whilst I was trying to install Windows via a modified ROM, I managed to brick it.

Luckily like with any other bad BIOS flash the situation can be reversed by directly flashing the chip. To do so I used a Raspberry Pi with the SPI interface enabled along with an SOIC clip. There are other methods but this is by far the fastest and the most cost effective solution.

SPI can be enabled in Raspbian by running sudo raspi-config, selecting option 9, followed by A5. A reboot is then required for the module to be loaded by default. A quick check after reboot with lsmod | grep spi_ should show
“spi_bcm2708″ or “spi_bcm2835” listed in the output.

Wiring up the Pi is made easier with the help of my favourite pinout diagram which can be found here: http://riscpi.co.uk/definitive-raspberry-pi-pinout/ Pin 1 of the chip can be ID'd by looking for a dot by the pin.

Chip -> Pi
1 -> CE0
2 -> MISO
3 -> 3.3v
4 -> GND
5 -> MOSI
6 -> SLCK
7 -> 3.3v
8 -> 3.3v

I've not given the specific pin# so you look at the diagram and don't do something silly like put 5v through the chip causing it to fry!
Flashing
Install flashrom:
sudo apt-get install build-essential pciutils usbutils libpci-dev libusb-dev libftdi1 libftdi-dev zlib1g-dev subversion libusb-1.0-0-dev
svn co https://code.coreboot.org/svn/flashrom/trunk flashrom
cd flashrom
make
wget https://dl.dropboxusercontent.com/u/59964215/chromebook/ROM/coreboot-seabios-peppy-20151128-coolstar-celeron.rom
./flashrom -w coreboot-seabios-peppy-20151128-coolstar-celeron.rom -VVV -p linux_spi:dev=/dev/spidev0.0

The process takes a minute or two, don't be alarmed by the lack of feedback, once it's found the chip it's usually working
Flash OK

Filed under: Fixes No Comments
4Jan/160

Huawei G510 digitiser replacement

Nice easy one to fix, digitiser was also a barginlious £8. Removed some screws, clean the broken glass, add some new double sided 3M tape and put the glass in place. Can be done in half an hour.

Filed under: Fixes No Comments
16May/120

Noisy Wii

I've observed the DVD drive being quite noisy on my Nintendo Wii ever since I bought it second hand from my local Gamestation. This wasn't too big of a problem from me as I rip all of the games that I purchase. Nevertheless it had been bothering me so I opened it up last night to take a look at what might be causing the racket. I dismantled it it down to the DVD drive and tried running a game with the cover off to see if I could locate the source of the noise. After mucking about with the metal tabs near the spindle as others suggested with not joy, I then removed the top cover from the DVD drive. Immediately the problem became apparent; there was a snapped plastic tab at the front where the discs slide in. Rather than trying to wrangle it back in, I simply removed it and now it's as quiet as can be.

I also had a third party controller which was making some unwanted noise, specifically from the motor used for vibration. Rather than making the usual low pitched rumble, I was getting an annoying clacking sound. Turns out whoever put the remote together at the factory had forgotten to peel off the paper from the double sided tape, D'oh!

Filed under: Fixes No Comments
18Aug/110

Netgear DGN2000 capacitors

Got around to opening up my wireless router to check for faulty capacitors today. Turns out all three Taiwanese-made Teapo capacitors had gone bad (visibly bulging) and needed replacing. Replacement caps for this router should be at rated to at least 105C, 2x 470uf 25v and 1x 100uf 25v caps are needed.

The repair itself is simple:

  • Remove the four rubber feet and take out the torx screws (T8)
  • Lift off the case and remove the system board
  • De-solder old capacitors, taking note of polarity - the square solder point is positive
  • Solder in new capacitors
  • Replace case and screws

Replacing the two 470uf caps solved the issue of Ethernet port 4 no longer working. I haven't swapped out the 100uf yet, not sure what part of the system it affects.

Filed under: Fixes No Comments