Change timezone on Ubuntu server (CLI only)

Posted At : September 6, 2008 1:19 AM | Posted By : Mark Lynch
Related Categories: Ubuntu, Systems admin, Open Source, Linux
It should be simple to do this and it is. The hardest part was finding the correct way to do it, so for future reference:

Using the command line, you can use dpkg-reconfigure tzdata.

sudo dpkg-reconfigure tzdata

Follow the instructions to reconfigure the timezone. This was taken from the ubuntu wiki article

Cheers, Mark

Comments (0) | del.icio.us | Linking Blogs

Custom Language files for Asterisk on Ubuntu

Posted At : September 4, 2008 4:27 AM | Posted By : Mark Lynch
Related Categories: Asterisk, Linux, Ubuntu
There are a number of steps needed to get custom language files working in a nice fashion for asterisk on ubuntu.

For this example I'm going to replace the standard digits of 0 to 10 with files that I've had recorded.

Create files in the correct format:

Starting with files in the format: 0.wav, 1.wav .... 10.wav which are 44.1kHz 16 bit PCM mono wav files.

We can convert them to alaw, ulaw and gsm formats which reduces asterisk transcoding with the following script: (requires sox to be installed)

#!/bin/sh
#
# Script to batch convert wav files to gsm,alaw and ulaw formats
# to help reduce overhead for asterisk server
# Run it in a directory of wav files.

for a in *.wav; do
   echo "Processing $a";
   baseName=`echo $a|sed -e s/.wav//`;
   #Convert to 8bit wav first at 8000Hz - this can take a while
   sox "$a" -r 8000 -c1 "$baseName_temp.wav" resample -ql;

   echo "...additional formats";
   #Convert to gsm
   sox "$baseName_temp.wav" "$baseName.gsm";

   #convert to alaw - note using .alaw extension as asterisk likes that
   sox "$baseName_temp.wav" -t al "$baseName.alaw";
   #convert to ulaw - note using .ulaw extension as asterisk likes that
   sox "$baseName_temp.wav" -t ul "$baseName.ulaw";

   #clean up the .wav files
   rm "$baseName_temp.wav"
done

Move the files to the correct place on the server:

I'm going to use the name 'learnosity' for the language as these are the files that we've recorded. So we need to create the following directory on the asterisk machine:

/usr/share/asterisk/sounds/digits/learnosity

If you wanted to do more audio files, you can create learnosity directories for the other audio files.

Once this is done, copy the alaw, ulaw and gsm files in but leave the original wav files out.

Tell asterisk to use these files:

The easiest way to use these language files is to set the channel language for the sections you want to use it:

Here's an example from extensions.conf:

exten => 4,1,Answer()
exten => 4,n,SayDigits(123456789)
exten => 4,n,Set(CHANNEL(language)=learnosity)
exten => 4,n,SayDigits(123456789)
exten => 4,n,Hangup()

If you dial extension 4 you get the digits 1 to 9 in the default language and if you've done everything correctly you will get your newly recorded digits the second time.

Cheers, Mark

Comments (0) | del.icio.us | Linking Blogs

MySQL and Temporary tables and CASE syntax

Posted At : August 31, 2008 10:47 AM | Posted By : Mark Lynch
Related Categories: ColdFusion, mysql
For some reason I've always avoided the use of temporary tables in SQL (MySQL or otherwise) but today I came across a difficult problem that temporary tables made very simple.

I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial.

The cut down query below demonstrates how it works.

  • First we drop the temporary table in case one has been left lying around.
  • Then we select a query into the temporary table which include some complex subqueries.
  • We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
    • 'yes' if all users for a class are selected
    • 'partial' if some users are selected
    • 'no' if none are selected.
<cfquery name="q" datasource="#variables.myDSN#">
   DROP TEMPORARY TABLE IF EXISTS tmp_exams;
   /* Create temporary table query which includes student counts */
   CREATE TEMPORARY TABLE tmp_exams
   SELECT   e.id
         , e.name
         , (SELECT count(1) FROM tbl_user_classes uXc
WHERE uXc.school_class_id = <cfqueryparam value="#arguments.school_class_id#" cfsqltype="cf_sql_integer">) AS class_count
, (SELECT count(1) FROM tbl_user_classes uXc
INNER JOIN tbl_users_exams uXe ON uXe.user_id = uXc.user_id
WHERE uXc.school_class_id = <cfqueryparam value="#arguments.school_class_id#" cfsqltype="cf_sql_integer">
AND exam_id = e.id) AS class_count_assigned
   FROM   tbl_exams e;
   /* Select out the relevant info */
   SELECT e.id
         , e.name
         , e.class_count
         , e.class_count_assigned
       ,(CASE WHEN class_count_assigned = 0 THEN 'no'
          WHEN class_count = class_count_assigned THEN 'yes'
          ELSE 'partial' END) AS active
   FROM tmp_exams e
   WHERE   1=1
   ORDER BY e.name
</cfquery>

Mostly posted for my future reference but hope it helps out.

Cheers, Mark

Comments (0) | del.icio.us | Linking Blogs

PHP and MySQL 5 bit fields

Posted At : August 25, 2008 2:21 AM | Posted By : Mark Lynch
Related Categories: mysql, Open Source, PHP
While debugging some PHP code the other night I came across a particularly strange problem with MySQL Bit fields.

I was returning a query with some bit fields into my class but the it was not returning true or false when I switched the data.

A bit of hunting and it turned out the that it was being returned as a binary type and after a few failed attempts to convert it to an integer or boolean on the PHP side I found this bug report on it.

So the moral of the story is if you are selecting BIT types from a mysql DB in PHP don't do this:

SELECT myBit
FROM tbl_example
WHERE id = 1

Instead cast the bit to an integer in MySQL like so:

SELECT CAST(myBit AS unsigned integer) AS myBit,
FROM tbl_example
WHERE id = 1

Voila, it works like expected.

Cheers, Mark Lynch

Comments (1) | del.icio.us | Linking Blogs

Howto refresh /dev/disk/by-uuid on Ubuntu

Posted At : August 15, 2008 11:08 PM | Posted By : Mark Lynch
Related Categories: HOWTO, Linux, Open Source, Ubuntu
I was recently setting up a Ubuntu server and was partitioning it after it had been installed.

There was lots of free space on the drives as the root partition was only using a small portion of the disk. After running fdisk to partition it and mkfs.ext3 on the partitions to format them I couldn't see them in /dev/disk/by-uuid.

A quick google presented the solution:

sudo udevtrigger

According to the man page this makes udev request the kernel device uevents, which in essence makes it read the disk info again and show it all up so you can mount it happily.

Cheers, Mark

Comments (0) | del.icio.us | Linking Blogs
BlogCFC was created by Raymond Camden. This blog is running version 5.1.004.