MySQL: Bar Charts

 Working Notes  No Responses »
May 192014
 

So there I was, contemplating whether I could produce a nice and simple bar chart showing the number of films I’ve watched per month. Using just MySQL.

I knew it was easy to produce a simple numerical count with count(*) and a group by clause, but a bar chart? Turns out it was easy with the repeat string function.

mysql> select date_format(whence, "%Y-%m") "Month", 
          repeat('*', count(*)) "Number of Films" 
          from films group by date_format(whence, "%Y-%m");
+---------+------------------------------------------------+
| Month   | Number of Films                                |
+---------+------------------------------------------------+
| 2007-05 | ****                                           |
| 2007-06 | ******                                         |
| 2007-07 | ******                                         |
| 2007-08 | *****                                          |
| 2007-09 | ******                                         |
| 2007-10 | ***                                            |
| 2007-12 | ****                                           |
| 2008-01 | *****                                          |
| 2008-02 | ****                                           |
| 2008-03 | *                                              |
| 2008-04 | *****                                          |
| 2008-05 | *****                                          |
| 2008-06 | ******                                         |
| 2008-07 | ****                                           |
| 2008-08 | *****                                          |
| 2008-09 | *****                                          |
| 2008-10 | ****                                           |
| 2008-11 | *****                                          |
| 2008-12 | ***                                            |
| 2009-01 | ***                                            |
| 2009-03 | **                                             |
| 2009-04 | ***                                            |
| 2009-05 | **                                             |
| 2009-06 | ****                                           |
| 2009-07 | ****                                           |
| 2009-08 | *                                              |
| 2009-09 | **                                             |
| 2009-10 | ****                                           |
| 2009-11 | **                                             |
| 2009-12 | **                                             |
| 2010-01 | **********                                     |
| 2010-02 | **********                                     |
| 2010-03 | *************************                      |
| 2010-04 | *****************************                  |
| 2010-05 | ********************************************** |
| 2010-06 | ***********************                        |
| 2010-07 | ****************                               |
| 2010-08 | **********                                     |
| 2010-09 | ************                                   |
| 2010-10 | **********                                     |
| 2010-11 | ********                                       |
| 2010-12 | *********                                      |
| 2011-01 | *******************                            |
| 2011-02 | *************                                  |
| 2011-03 | **                                             |
| 2011-04 | ************                                   |
| 2011-05 | ********                                       |
| 2011-06 | ***                                            |
| 2011-07 | ****                                           |
| 2011-08 | *********************                          |
| 2011-09 | *                                              |
| 2011-10 | **                                             |
| 2011-11 | ***************                                |
| 2011-12 | ********************                           |
| 2012-01 | ********************                           |
| 2012-02 | *******                                        |
| 2012-03 | *******                                        |
| 2012-04 | *****                                          |
| 2012-05 | ******                                         |
| 2012-06 | *******                                        |
| 2012-07 | **************                                 |
| 2012-08 | ************                                   |
| 2012-09 | ***************                                |
| 2012-10 | *******************                            |
| 2012-11 | ****************                               |
| 2012-12 | *******                                        |
| 2013-01 | *************                                  |
| 2013-02 | *************                                  |
| 2013-03 | ******************                             |
| 2013-04 | ******************                             |
| 2013-05 | ********                                       |
| 2013-06 | ************                                   |
| 2013-07 | **************                                 |
| 2013-08 | ***                                            |
| 2013-09 | *************                                  |
| 2013-10 | ********                                       |
| 2013-11 | ***************                                |
| 2013-12 | ***********************                        |
| 2014-01 | *************************                      |
| 2014-02 | ********                                       |
| 2014-03 | *************                                  |
| 2014-04 | ****************                               |
| 2014-05 | ************                                   |
+---------+------------------------------------------------+
83 rows in set (0.02 sec)

“Mounting” A VDI Image Under Linux

 Computing, Working Notes  No Responses »
Feb 042014
 

So I found myself in the position of wanting to poke around the file system of a virtual Windows machine – the kind of poking around you would prefer I didn’t if it were your Windows machine – and needed to make a VDI disk image available as a block device under Linux so it could be mounted in the normal fashion.

Googling around found some instructions; which didn’t work properly. Solutions were also available, but I’m writing up the ‘fixed’ instructions here to save myself time in case I need it again.

First step is to become root; if you need help doing that, this is probably the wrong page for you!

Next step is to install the Debian package qemu-utils which contains the tool qemu-nbd which we’ll need later. We also need to load the network block device module with a parameter :-

apt-get install qemu-utils
modprobe nbd max_part=16

This parameter is the key here – for some reason the default on at least some of my Linux machines is not to create additional block devices for any additional partitions that show up.

The next step is to ‘attach’ the VDI image (or presumably anything supported by qemu-img which covers pretty much everything popular) and tell the Linux kernel that there may be some new partitions to create device files for :-

qemu-nbd -n -c /dev/nbd0 disk.vdi
partx -a /dev/nbd0
partx: /dev/nbd0: error adding partitions 1-2

(Added the “-n” flag after reading about some more problems and a work-around; as I haven’t tested it, be careful!)

The error from partx indicates that qemu-nbd managed to create the partitions itself, but there are hints that this sometimes doesn’t happen so I’ve included the command here “just in case”. Once the partition block devices are present, they can be used as any ordinary devices.

Once finished, unmount anything mounted and release the block device with :-

qemu-nbd -d /dev/nbd0
rmmod nbd

Linux Dream Cheeky USB Thunder Missile Launcher

 Computing, Linux, Working Notes  No Responses »
Jan 162014
 

This is not original work, but merely a set of notes on how to do the set up. The core information (and the code) came from this blog posting.

Essentially I’ve re-ordered the steps in which to work and excluded anything other than the bare essentials to get it all working. With the intention I can get my missile launcher working at home and at work  😎

Step 1 is to prevent the HID driver from clamping on to the missile launcher. This was done by :-

  1. Editing /etc/default/grub and adding usbhid.quirks=0x2123:0x1010:0x04 to the existing variable GRUB_CMDLINE_LINUX_DEFAULT.
  2. Run update-grub (I always manage to forget this).
  3. Reboot the machine and check /var/log/messages for 2123 (the VendorID) to see if it has been claimed by usbhid (which will show up as a line beginning generic-usb 0003:2123:1010.0006: hiddev0 if it does claim it).

The next step is to download and compile the code given in the blog link above. If you need instructions on how to do this, then you probably need to look elsewhere – it builds easily.

Once built, an sudo insmod launcher_driver.ko will verify that the kernel module loads – you can double check by looking at /var/log/messages.

It’s also necessary to install both the kernel driver and the control program manually :-

  1. Copy the compiled kernel module to /lib/modules: sudo cp  launcher_driver.ko /lib/modules
  2. Edit /etc/rc.local and add the command: /sbin/insmod /lib/modules/launcher_driver.ko
  3. Copy the control program to a sensible location: sudo install launcher_control /opt/bin

There’s probably better ways of doing this, and better places to stick things but as you’re following my instructions you’re stuck with my suggestions! It’s tempting to try a reboot at this stage to verify that this works, but as there’s just one small extra step we may as well get that done too. This is to create a udev rule to set up a device file in /dev.

Create a file (/etc/udev/rules.d/99-usb-launcher.rules) with the following contents :-

KERNEL=="launcher?*",MODE="0660",GROUP="cdrom"

The choice of group name is rather inappropriate except it will work well enough, and I have changed the permissions on this to something a little more restrictive. This can be tested with sudo udevadm trigger which will re-run udev. This should change the permissions on any existing /dev/launcher* file(s). If it doesn’t work, the blog pointer above is the place to head.

Lastly, there’s a couple of corrections to the launcher_control.c that is convenient to make :-

% diff launcher_control.c launcher_control.c.orig
63c63
<         while ((c = getopt(argc, argv, "m:lrudfsht:")) != -1) {
---
>         while ((c = getopt(argc, argv, "mlrudfsht:")) != -1) {
97,98c97
< 		fprintf(stderr, "Couldn't open file: %s\n", dev);
<                 /*perror("Couldn't open file: %m");*/
---
>                 perror("Couldn't open file: %m");

 

Down Mixing Quadrophic and 5.1 Surround Sound with SOX

 Computing, Working Notes  1 Response »
Jan 112014
 

As categorised, this is a “working note” and explains how I ‘down mix’ quadrophic FLAC files into stereo files with the assistance of sox. This may well not be the best method and indeed I may be getting it totally wrong – who knows what two channels are supposed to go where?

It turns out that my portable media player doesn’t understand FLAC files with more than two channels, and as I have a number of these obtained from somewhere I needed a way to make them playable. The first step is to identify FLAC files with more than two channels. It turns out that the venerable file does that quite adequately :-

% file one.flac two.flac
one.flac:              FLAC audio bitstream data, 24 bit, stereo, 96 kHz, 24179840 samples
two.flac:              FLAC audio bitstream data, 24 bit, 4 channels, 96 kHz, 24179840 samples

As you can see the third column of the information about the file identifies the number of channels; it also identifies 5.1 surround sound files as 6 channel files. And quite possibly wilder and woollier kinds of channel numbers too.

Now onto the conversion. This is simply the following :-

% sox input-file.flac output-file.flac remix 1v0.5,3v0.5 2v0.5,3v0.5 norm
% file output-file.flac
output-file.flac:      FLAC audio bitstream data, 24 bit, stereo, 96 kHz, 24179840 samples

The details of what sox command to use … and specifically the remix parameters came from a post discussing the problem here. I’m not qualified to assess the details, but the results seem fine. For extra points, the remix parameters for 5.1 surround sound are: 1v0.3694,3v0.2612,4v0.3694 2v0.3694,3v0.2612,5v0.3694.

FreeBSD: Updating the Source

 Computing, Working Notes  No Responses »
Oct 252013
 

This is about updating FreeBSD once you have broken free of binary updates – probably not the best thing to do, but I needed to do it for various reasons. This isn’t a recommended method; merely the method I use. It is more about gathering the instructions I need.

First update the source :-

cd /usr/src
make update SVN_UPDATE=yes

Next step is to build the world :-

make -j 4 buildworld

This step takes quite a while, so it’s very helpful to add the “-j 4” to build in parallel.

The next step is to build the locally configured kernel. This usually starts with configuring it, but comparing the GENERIC configuration with the local configuration shows that in my case at the time of writing I didn’t have to re-configure.

make buildkernel KERNCONF=${NAME-OF-YOUR-CONFIG}

Once built the kernel needs to be installed :-

make installkernel KERNCONF=${NAME-OF-YOUR-CONFIG}

Next is to boot to single user mode which is done by pressing a key other than Return during the 10 second countdown, and entering boot -s at the prompt. Once booted into single user mode, the following steps need to be taken :-

# mount -u /; zfs mount -a
(To mount all filesystems with zfs in read-write mode)
# mergemaster -p
(To start the update of the configuration files)
# cd /usr/src
# make installworld
(To install the new user land)
# mergemaster
(To complete the update of the configuration files)
# make delete-old

That’s it for single-user mode. Now reboot into full multi-user and carry on …

Next step is to update the ports :-

# portsnap fetch
# portsnap update
# pkgdb -F
# cd /usr/ports/ports-mgmt/portupgrade
# make deinstall
# make install clean
# portupgrade -af --batch

This of course takes a very long time to complete.

And that should be it … seems to have been successful for me.

 Older Entries  Newer Entries