Educating the world

Our blog has over 10,000 readers a month

SQL for beginners

October 4th, 2012

There are plenty of articles on how to get going with SQL (or MySQL) for beginners. However, there aren’t many articles which take you back to basics describing the building blocks used and help you understand the concept of what a database actually is.

This document will hopefully introduce you to the kinds of things that SQL is capable of. Each example has the command and the results so hopefully you can just read and understand it. I’m aiming this document at people who may come across SQL as part of someone else’s job and feel like they should know a bit about it, but don’t really know where to begin.

So let’s get going…

Structured Query Language or SQL is a computer language used to get stuff out of a database. To understand this statement we need to know what a computer language is and what a database is. Simple again! A computer language is just a set of commands that will make the computer do stuff and a database is multiple groups of stuff.

Now that we have got that out of the way, let’s begin. For this lesson we’ll be using MySQL, partly because it’s free and partly because there are some instructions here to set up MySQL and PHP on a test system.

To understand SQL you have to understand a little bit more about the structure of a database. The groups of stuff I mentioned earlier are called tables. A table is a grid of rows and columns. A database is a collection of tables. Now you have, everything you need to know! So let’s do an example we can all relate to.

We’ll create a table to hold information on people. Table names tend to be plural names like people, parts, events, etc so in this case people and not person because the table holds lots of persons! We collect some relevant information together: first name, sex, age. Then we’ll try to do some thing useful with them.

Before we create the people table we must have a think about the best way of storing the information in the table. Each attribute, field or column needs to have a type. First name is text, sex is an enumeration of male or female and age is an integer. You could make all columns text but we would like to do sums with the age column so we’ll need to use a column type that allows this.

In the early days of programming the commands were separated by new lines but as the languages got more complex people wanted to spread the code over several lines and so a semi-colon was used to denote the end of a statement.

first_name VARCHAR(20)
,sex ENUM(’male’, ‘female’)
,age INT

The words in capitals are keywords, they are reserved for use by the computer language. i.e. you can’t have a table called table! Test columns need to have a maximum length so you need to specify it.

Now that we have a table let’s put some stuff in it:

INSERT INTO people(first_name, sex, age) VALUES
(’David’, ‘male’, ‘40′)
,(’Jon’, ‘male’, ‘30′)
,(’Tom’, ‘male’, ‘20′)
,(’Sophie’, ‘female’, ‘10′);

It’s only a small table so let’s list the contents.

SELECT first_name, sex, age FROM people;

Produces the output:

| first_name | sex    | age  |
| David      | male   |   40 |
| Jon        | male   |   30 |
| Tom        | male   |   20 |
| Sophie     | female |   10 |

Here you can definitely see the rows and columns structure I mentioned earlier. Each row is a complete set of information, that is there is an entry for every column. We didn’t put any restrictions on the table so we could have not supplied information for some of the columns if we wanted. In which case it might have looked something like this.

| first_name | sex    | age  |
| NULL       | male   |   40 |
| Jon        | NULL   |   30 |
| NULL       | NULL   | NULL |
| Sophie     | female |   10 |

Where NULL denotes the absence of data. In database terms NULL values are a bit of a bane. This special NULL symbol says the information is not provided and so we can’t assume anything about it. Most applications usually filter out the rows where there are gaps in the information.

Do you remember SETs from school? All of SQL is based on that idea. So lets look at our data and rephrase our question using SET-speak.

Show me all the information for a person where the person is over 21 is translated into

SELECT first_name, sex, age
FROM people
WHERE age > 21;

| first_name | sex  | age  |
| David      | male |   40 |
| Jon        | male |   30 |

Show me all the first names of all the females is translated into

SELECT first_name FROM people WHERE sex = ‘female’;

| first_name |
| Sophie     |

You may think that we are using the WHERE clause to filter the data to get results but what we are doing is far more subtle than that. We are creating new sets by dividing up existing sets. The sets may be added together in a union or subtracted from each other in an intersection. When we create our subset of data we can use it as the bases for another query. This can be shown in the following query:

SELECT first_name FROM people
WHERE age < 35
AND first_name IN (
SELECT first_name FROM people WHERE sex = ‘male’

The inner SELECT is the sub-query and produces a set of males names. That set of people is used with another set filter to reduce the set even further.

| first_name |
| Jon        |
| Tom        |

The previous query could have been written as the following but quite often you’ll find that the data is not conveniently placed in the same table! it’s in another table or is the result of a calculation.

SELECT first_name FROM people
WHERE age < 35 AND sex = ‘male’;

Now that we understand a little more about the concepts of sets and creating them let’s look at doing something (more) useful.

We will start simple and count the number of males and females in the data set. To the average MySQL’er this will seem like an odd choice to talk about first but it will hopefully be obvious (later) that it is the only starting point.

SELECT COUNT(first_name), sex
FROM people

Produces the output:

| COUNT(first_name) | sex    |
|                 3 | male   |
|                 1 | female |

The GROUP BY command is used all the time to collapse sets of information so that they can be aggregated. In this case we are collapsing around the sex column. To the SQL engine that gathers the rows the GROUP BY effectively makes the data look like this.

| first_name | sex    | age  |
| ?????      | male   |   40 |
| ???        | male   |   30 |
| ???        | male   |   20 |
| ??????     | female |   10 |

There aren’t many aggregate operations we can do on text columns like first_name, so the information is meaningless and unpredictable. The data is treated as a separate set of each unique entry in sex. Conceptually the data set has been split into two:

| first_name | sex    | age  |
| ?????      | male   |   40 |
| ???        | male   |   30 |
| ???        | male   |   20 |


| first_name | sex    | age  |
| ??????     | female |   10 |

The GROUP BY will allow you to create aggregate operations. For example:

SELECT COUNT(first_name), sex
FROM people

shows the number of rows counted in each of the two groups of data, giving the result:

| COUNT(1) | sex    |
|        3 | male   |
|        1 | female |

or the total age and average for those sets.

SELECT SUM(age), AVG(age), sex
FROM people


| SUM(age) | AVG(age) | sex    |
|       90 |  30.0000 | male   |
|       10 |  10.0000 | female |

When the GROUP BY is applied to the whole table the GROUP BY keywords may be omitted but it is there implicitly. For example:

SELECT SUM(age), AVG(age) FROM people;

| SUM(age) | AVG(age) |
|      100 |  25.0000 |

This document introduces the concepts of sets, creating subsets and then treating a set as an entire entity and doing something useful. A table is one big set and next time we’ll look at joining tables together to see what matches and what’s missing.

Installing VirtualBox, CentOS 5.4, Apache, PHP and MySQL

September 11th, 2012

I’ve recently done a project that uses load balancing. mod_proxy is a blog for another day but it was based on a LAMP stack. I install this all the time for lots of different projects. Some are development projects and some are just system administration projects.

My desktop of choice is Windows. I hate it slightly less than I hate MacOS, so I’m going to be installing my LAMP stack inside a virtual machine.

First up let’s get VirtualBox up running:

  1. Download VirtualBox for your operating system from
  2. Install it, selecting all the defaults.

Next we’ll set up a Virtual machine which will run our guest operating system:

  1. Download the latest copy of CentOS from their list of mirrors. At the time of writing this article was CentOS-5.4-i386-bin-DVD.
  2. Run VirtualBox.
  3. Click the New button to create a new virtual machine.
  4. Give the virtual machine a name. You can give it the name of the operating system or the project you will be working on.
  5. Select Linux as the operating system.
  6. CentOS wasn’t in the list so I just selected Other Linux, I don’t think this does anything.
  7. Click Next, to configure the Memory.
  8. There 6GB in my PC so I’ll give 1024MB to the virtual machine. You can change this later if you like.
  9. Click Next, to configure the Virtual Hard Disk.
  10. Accept the defaults to create new hard disk.
  11. Click Next, to start the virtual disk creation wizard.
  12. Select the VDI(Virtual Disk Image) and click Next.
  13. Select Dynamically allocated because it creates a smaller file which helps with housekeeping and backups at the cost of a small amount of speed each time the hard disk needs to grow. You’ll only really notice this at the beginning when your installing everything but once you’re done you’ll find that it hardly grows after that.
  14. You can leave the hard disk name the same as the operating system.
  15. CentOS should install in a couple of gigabytes so accept the default of 8GB. The file won’t be 8GB straight away.
  16. Select Next, then Create and then Create again.
  17. The virtual machine’s creation should be instant. We’ll tweak a few settings and install our guest operating system.
  18. Click the Settings button.
  19. Select the System tab.
  20. No one has a floppy disk drive anymore so switching it off will speed up your boot slightly. Uncheck Floppy and move it to the bottom of the list.
  21. We’ll connect our ISO file to the virtual machine’s CD-ROM drive. So select the Storage tab and highlight the Empty CD-ROM drive.
  22. Select the Network tab and change the Attached to Bridged Adaptor. This will allow you to pick up a real network address from the DHCP server which makes testing easier.
  23. The right hand panel will change so click the CD-ROM icon on the far right to open the file selector.
  24. Select Choose a virtual CD/DVD disk file and navigate to your ISO image and open it up.
  25. Click Ok to close the Settings panel.

Right, off we go. Click Start and read the popup boxes and click your way through them. VirtualBox will launch the Virtual Machine and will attempt to boot from the ISO image we gave it earlier. You will start to see the CentOS installation happening in a new window that’ll open up

  1. Press Return to install in graphical mode.
  2. Don’t bother checking the CD media because it’s really just a file and so won’t suffer from any corruption, hopefully! So select Skip.
  3. The virtual machine will think about it for a second and then launch the installation application in X-windows.
  4. Select your language, keyboard country. If you are English then select United Kingdom.
  5. Your virtual disk has not been partitioned or formatting and the installer notices. So click Yes and accept the defaults.
  6. Select the defaults for Network Devices.
  7. Select your location for the timezone.
  8. Enter your Root password. If the virtual machine is going to be on the internet then choose your password carefully. If it’s just a test system then just choose something simple and small!
  9. The next screen allows you to select an installation profile. I’m going to be just doing PHP development so don’t need to bother with all sorts of desktop applications like Open Office etc. I do want web servers and networking tools. Select Server - GUI.
  10. Select Customise now and then Next. We’re going speed up the installation by not installing a load of stuff.
  11. Under Applications unselect most of them except the editors.
  12. Under Development make sure you select the Development Libraries and Development Tools.
  13. Under Servers make sure you explicity check MySQL Database.
  14. Open the Web Server and explicitly set php-mysql.
  15. Under Base System -> System Tools, explicitly select tn (telnet is a handy connection tester) and Wireshark (for packet sniffing).
  16. Under Languages select your country support.
  17. Click Next and Next again to start the install.
  18. 4 minutes later you can click the Reboot button.
  19. When the the virtual PC reboots it puts you in the platform configuration screens. Click Forward.
  20. If your not on the internet make life simple and switch of the Firewall and the SELinux.
    You don’t need Kdump either.
  21. Create yourself a User.
  22. It’s nice to have a bit of sound so test that.
  23. Click Finish and reboot.

When the virtual machine reboots stop it so we can change the boot order. We don’t want to boot from CD every time.

  1. Click Settings -> System
  2. Move Hard Disk to above CD/DVD ROM.
  3. Then click OK.
  4. Next click the Start button, to boot the virtual machine.
  5. You will be booted to the X-Windows login prompt.
  6. Login as the root user.
  7. Open a terminal window; it’s under Application -> Terminal.
  8. We want Apache and MySQL to start when the virtual machine boots into multi-user mode (runlevel 3).

    cd /etc/rc3.d
    ln -s ../init.d/httpd S99http
    ln -s ../init.d/mysql S99mysql

  9. Now we’ll start the services

    /etc/init.d/httpd start
    /etc/init.d/mysqld start

We should be up and running so we’ll create a .php file to test that PHP and MySQL are working together.

  1. Change to the web server document root:

    cd /var/www/html

  2. Create a file called pdo.php and fill it with:


    $pdo = new PDO("mysql:host=localhost;dbname=mysql""root""");
    $sql "SELECT NOW()";
    $stmt $pdo->query$sql );
    $obj $stmt->fetch(PDO::FETCH_OBJ);
  3. Get the virtual machines IP address with /sbin/ifconfig.
  4. Open a browser and navigate to http://<ipaddress>/pdo.php
  5. You should see the following output in the web browser:

    object(stdClass)#3 (1) { ["NOW()"]=> string(19) “2012-06-11 00:37:14″ }

Congratulations that was easy.

Now that you have a fresh clean system it’s a good idea to create a clone. We can use this clone to create clean copies for other projects without having to go through all the hassle of installing the operating system. We’ll have to shutdown the virtual machine so from the command line type:

shutdown -h now

In VirtualBox:

  1. Right-click on the virtual machine and select Clone.
  2. Give it a name ending in “clean” so you know this one shouldn’t be touched.
  3. The first question VirtualBox asks is whether you want to reinitialise the MAC address on the network cards. The MAC address is the physical network address so if you want to run multiple clones at the same time then you’ll have to do this reinitialise step. Otherwise you could be in a world of IP address conflicts as both clones will appear to be the same machine. So do a Full Clone to keep it safe.

My installation was about 3GB so it took a few seconds to copy all the data to make the clone.

Firefox doesn't install UK English for British installations by default

September 10th, 2012

If you download Firefox from the main download page, the site doesn’t detect that you are British. You get the standard American download. The result of this is that web sites detect your locale from your browser then layout internationalised content using this detection.

When we started using CIPHR we found that everyone using Firefox was being shown their dates in the wrong (American) order.

Firefox by default installs the languages English (en) and English U.S. (en-us) but not English GB (en-gb).

  1. So from within the menu in Firefox, select Tools -> Options -> Content Tab.
  2. From the Languages section select Choose.
  3. From the Select a language to add drop down select English/United Kingdom [en-gb].
  4. Click Add.
  5. Use the Move Up / Move Down to position en-gb at the top of the preferences.
  6. Click OK to finish.

You may have to refresh the page but you shouldn’t have to restart Firefox.

Alternatively you could download the British version of Firefox. When you navigate to it forwards you to Unfortunately there isn’t a so you’ll have to click the Systems & Languages link under the download button. Scroll down to the line English (British) and click the Download link from there and follow the same procedure as before.

Cisco ASDM 5.2 for ASA ClassCastException X509TrustManagerImpl to X509ExtendedTrustManager

August 31st, 2012

I hadn’t used my Cisco ASDM 5.2 for ASA for a while and when I needed to add a couple of extra users to the VPN it didn’t work.
Everything seemed to load in ok but when I tried to submit my login credentials it hung. I loaded the Java console and got the following exception when I tried to authenticate.

Using JRE version 1.7.0_05 Java HotSpot(TM) Client VM
User home directory = C:\Users\mrn
c:   clear console window
f:   finalize objects on finalization queue
g:   garbage collect
h:   display this help message
m:   print memory usage
q:   hide console
s:   dump system properties
ASDM Application Logging Started at Wed Aug 29 12:46:43 BST 2012
Local DM Launcher Version = 1.5.20
Local DM Launcher Version Display = 1.5(20)
OK button clicked
Cache location = C:/Users/mrn/.asdm/cache
Exception in thread "AWT-EventQueue-0" java.lang.ClassCastException: cannot be cast to
	at Source)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at java.lang.Class.newInstance0(Unknown Source)
	at java.lang.Class.newInstance(Unknown Source)
	at Source)
	at Source)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.awt.EventQueue.access$000(Unknown Source)
	at java.awt.EventQueue$ Source)
	at java.awt.EventQueue$ Source)
	at Method)
	at$1.doIntersectionPrivilege(Unknown Source)
	at$1.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue$ Source)
	at java.awt.EventQueue$ Source)
	at Method)
	at$1.doIntersectionPrivilege(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at Source)

The problem is caused by Cisco ASDM 5.2 requiring Java 6. In spite of having Java 6 and 7 installed, the Cisco software doesn’t know the difference and just accepts the first installation as being the only installation. Version 7 comes first and so the Cisco software tries to use it.

Sun have a couple of internal classes that only sun are supposed to use because they might change without warning. Low and behold they did! X509TrustManagerImpl no longer has the same interface as X509ExtendedTrustManager and so we get a ClassCastException.

In order to work around the problem we must launch the Cisco ASDM 5.2 from an environment where it only knows about Java 6.

Create a file called asdm-launcher.bat and place it into C:\Program Files (x86)\Cisco Systems\ASDM. Use your favourite text editor and add the following to the file:

cd C:\Program Files (x86)\Cisco Systems\ASDM

“C:\Program Files\Java\jre6\bin\javaw.exe” -Xms64m -Xmx512m -Dsun.swing.enableImprovedDragGesture=true -classpath lzma.jar;jploader.jar;asdm-launcher.jar;retroweaver-rt-2.0.jar

When the batch file has been saved:

  1. Create a shortcut on the desktop (or wherever) to use it. To make it more similar to the old shortcut you can change the icon used.

  2. Right-click on the shortcut and select Properties then Change Icon.
  3. Click Browse
  4. Enter "C:\Program Files (x86)\Cisco Systems\ASDM\asdm-launcher.exe" and hit return.
  5. Select the icon you would like and press OK.

There is a file called "C:\Program Files (x86)\Cisco Systems\ASDM\asdm-launcher.conf" which contains instructions to launch the Java Virtual Machine (JVM) with certain parameters but I couldn’t get it to pick up a different version of Java. So if anyone knows what goes into that file so that we don’t need a hacked up batch file then let us know in the comments.

I blog things that take ages to find on the interweb but here’s the source of my final solution:
[ ]

Special Characters in HTML

August 16th, 2012

I always need to remember the special HTML entity codes for characters. Many blogging applications re-render the characters to make them more readable but when you want to cut and paste the text it’s all wrong. The best example of this is double quotes. If I write

echo "hello world"

into the article body, after rendering it comes out as:

echo “hello world”

This is fine for standard text but if you are writing computer code it’s useless. Try cutting and pasting the above 2 examples into a unix command shell. You will get

hello world

into the article body it actually comes out as

“hello world”


There are thousands of web sites where they publish what all these codes are and most of them are a bit rubbish. I came across this on Steve DeGraeve’s site which is one of the better ones. I thought I’d blog it so I don’t lose it in the melee of similar pages returned by Google’s search.