SQL Joins

I've never used SQL joins frequently enough to remember which one is which, and end up looking them up. This visual explanation of joins using Venn diagrams is a great quick reference.  The examples don't include duplicate in the tables, and it doesn't have a visual representation of a cross join a natural join or a self join.

Concatenate rows into a text string

I needed to concatenate multiple rows of a table together in Caché SQL. I did this using XMLAGG, which returns all of the values used within a specified column as a concatenated string.  If I'd wanted the data to be comma separated then I could have used the LIST function instead.

SELECT ReportName,XMLAGG(ReportLine) As Report FROM Reports GROUP BY ReportName

If you want to do the same thing with a different database then Stack Overflow thread might be able to help.

Ripping CDs on Ubuntu

We want to convert our CD collection to flac so that we can free up some shelf space, and so that we have a backup of some of the more obscure CDs.  I did a trail rip on my fit-pc2 to see how long it took to rip an album and find a ripper I liked. The winner was Audex as it copes gracefully with problems and for configurable file naming.

Rhythmbox
There wasn't a dedicated ripper installed so I tried  Rhythmbox first.  It ripped a couple of tracks of Burn Out fairly quickly, but then got stuck about 15% of the way through track 3.  The percentage complete did continue going up, but painfully slowly. I couldn't see any way of cancelling the rip other than ejecting the CD or exiting the program.

Sound Juicer
Sound Juicer is Ubuntu's default CD-ripping application, but it wasn't installed on my box. I installed it and checked the settings and it was set to rip to flac. I tried ripping the same CD again and ripping progressed quickly, but it was ripping as ogg. I checked the settings and it still said flac, so I changed it to ogg and back to flac and then tried again. This time it did rip as flac, but again it hung on the 3rd track.

Asunder
The Ubuntu Documentation described Asunder as "an easy-to-use, plain CD ripper" which sounded good, so I tried that next. It ripped and then encoded as both ogg and flac. Again it got stuck on track 3. It has a nice progress indicator that shows the progress ripping and encoding. This that it showed that it hadn't finished ripping the track when it hung, so it looks like it's ripping rather than encoding which is the problem.

Audex
I wasn't totally happy with any of the rippers I'd tried so I used Ubuntu Software Centre to see what else was available and found Audex. It looks nice and it was obvious how edit the settings. I sett it going and again it got stuck on track 3, but brought up a dialog box telling me that ripping performance had been poor for the last 5 minutes, and asked me if I wanted to continue. I said selected continue, and it continued ripping extremely slowly. It has ripped a number of CDs without any problems; we've now finished 'A' with only a couple of CDs which ripped in less than realtime.

Update January 2012 - Audex passed a long term use test as we're still using it after ripping most of our CD collection.

Android control of Rhythmbox

We're currently using Rhythmbox as our audio player on Ubuntu. I'd like to be able to control the music remotely and the logical thing to use is my Android phone.

First I installed (URemoteDesktop) on my phone and the server from GuateDroid on my computer. The server requires Java to be installed, and Ubuntu doesn't include Java so I needed to install that before it would work. I initially installed the restricted extras, which claim they include Java, but it still didn't work so I installed Sun Java 6. URemoteDesktop does allow you to change the volume, skip and pause, as well as things like switching desktop, turning the screen on & off, presentation controls and typing. However it doesn't let me start listening to an album without being at the computer.

As there didn't seem to be many apps around I decided to investigate web control. First up was Rhytmote. This did what I wanted, but the interface is a big table of all the songs. This is just about usable at the moment, but it's not going to be once we've finished ripping all our CDs. It also didn't seem to have any way of playing a whole album.

Next I tried RhythmWeb from here. I got a web page, but no songs showing up. Whilst trying to find a solution I found a newer version. The interface has a powerful search, play queue management, tag clouds (though the tag sizes seem a bit random), and the ability to play podcasts, and most importantly works well in a mobile browser. It also gets bonus points for the music in the screen shots on their website. One problem is that Rhythmbox decides to play Derby Ram (which is the first track if I list all tracks in Rhythmbox) after it runs out of things in the play queue.

Rhythmbox & Android

Rhythmbox can be made to recognise portable music players, and if you use it to copy songs on then it will automatically transcode them from flac to mp3. To do this you need to install mp3 support in Ubuntu. Then create an empty file called .is_audio_player in the top level directory of the Android file system. If Rhythmbox is open you'll need to restart it to get it to find the device. The default is 128kbps, for instructions on how to change this see this post on Android Edge. If Rhythmbox doesn't know where to put the files then add a line to tell it.  The Rhythmbox FAQ has an example. For my HTC X One I had to set it to:

audio_folders=Music/

Copying files onto my phone took around 10 mins for an album, which seemed a bit slow. I suspect transcoding was the limiting factor, and it'd probably be faster on a higher powered machine.

[This post was edited on 15/06/2012 to add info regarding HTC X One]

Android 2.1

I've successfully upgraded my phone to 2.1! After my failed attempt last week I did some more research and found a blog post which suggested taking the battery out during the upgrade. The trick is to take the battery out when it gets stuck on step 3 "upgrading the firmware on the phone". It then tells you that there has been an error, and that you need to press Volume Down + Camera + Power. When you do this the phone displays a Blue SIM logo and a downloading status bar. You can then click back through steps 1 & 2, and when you get to step 3 it actually installs the update, only taking a minute or two. The phone then restarts and you have to re-associate it with you google account and wait for it to resync your contacts, mail, etc. This took a surprisingly long time, I'm not sure how long as I gave up and went to bed! If you had AppBrain installed then the next thing to do install that and use it to re-install your apps.

Trying to upgrade my Phone

Last year I bought a Samsung Galaxy Portal, which runs Android 1.5. I was fairly impressed, particurly with the Gmail contact sync as well as geeky things like geotagging photos, and latitude.

I had problems getting it to talk to my computer. After a bit of research I discovered that it will only talk to Samsung's "New PC Studio" if USB Debugging is turned on, and will only show up as a USB drive if it is turned off. When connected to Ubuntu it doesn't seem to matter.

Trying this with the version of "New PC Studio" showed that it recognised my phone but it wouldn't actually do anything with it. I wanted to upgrade my phone to a newer version of Android so I can play with some of the new features so I did some more research. After trying a couple of versions which people had suggested I found one which worked - 1.5.1.10064_2 (I actually installed 1.4.0 IL2 which upgraded itself), but you can download 1.5 from Samsung now). I tried updating the phone, but for some reason the phone rebooted during the upgrade and the upgrade didn't complete. PC Studio told me to press the volume down, power and camera buttons to do a restore, but this turned the phone on and everything seems fine. Now if I load PC Studio and go into the upgrade option it says I need to restore my phone, but since it's working fine I reluctant to do that. I'll come back to this another day.