Wednesday, 25 July 2012

How to unserialize data using mysql without using php

I'm working on a system that has data in a column serialized with php - not my design I should add...

Rather than pulling the data and unserializing each record in php, I wanted to do this in mysql. It seems to be a common problem for developers.

After hunting through all the mysql functions I found SUBSTRING_INDEX.

For example if you have a table like this (data has been modified to protect the innocent!)


Use this query, the a:5 above tells me that there are 5 columns. It does depend on the developer knowing how many columns are in the serialized data and the structure being the same. But you get the idea.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM audit
WHERE object_type=42

Which gives me this


Then all I need to do is use some column aliases

SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS invoiceid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS balance,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS broughtforward,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS userid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS customerid
FROM audit
WHERE object_type=42


Thursday, 5 July 2012

Reset windows password

Had a friend who forgot her Windows 7 password....

Straight away I tried ophcrack - installed to a usb stick via unetbootin.

Unfortutately it wasn't able to crack the password...

So I tried pogostick which will remove the password. Just download the lastest usb??????.zip and extract to a usb stick. This worked! But there is a caveat, it will lose any encrypted files.

Note: to use the usb stick, switch off your windows computer, insert the usb stick, then switch it on and follow the on screen instructions. If you get windows then you will need to change the boot order in your bios/cmos set up. To set up this up, you will need to press a key immediately after switching on - they key varies between manufacturers, usually it is F2.

Saturday, 16 June 2012

Open source languages - job trends

I was chatting to a colleague yesterday about switching to open source. I don't think he took much convincing anyway, but these job trends speak for themselves...

Tuesday, 12 June 2012

Easy peasy way to convert Microsoft DOCX to PDF in Linux

I use Linux, so have no need to use Microsoft Office - I either use Google Docs or OpenOffice.

But I often receive Microsoft documents from clients and colleagues.

Unfortunately, I've found that if I open a Microsoft Word (docx) file in OpenOffice, the formatting is wonky (technical term) - Often the images are excluded altogether...

Google Docs doesn't always cope with the formatting either...

So the easy peasy way is to sign into with a hotmail/live/msn account.

Upload and open the docx file, then go to the file->print option in SkyDrive (not the browser print option)

If you are using Chrome or Chromium, it will then bring up the printer options - there is an option to save as pdf

et viola!! I now have a readable version.

Friday, 1 June 2012

ssh via Nautilus


Just discovered I can use ssh with Nautilus...

Select Go -> Location from the menu (or press ctrl+L)

Then type ssh://

Easy peasy...

Wednesday, 2 May 2012

How to install Oracle JRE 6 in Ubuntu

There's a couple of quirks in Netbeans which seems to be a problem with OpenJDK.

Most irritating is being unable to copy and paste to/from a browser.

Installing Oracle Java 6 seems to solve the quirks

First run this which will create a package on your computer

Then install the package

Then edit  netbeans.conf  to use the Oracle Java 6

gksudo gedit ~/netbeans-7.1.1/etc/netbeans.conf 

Change to


And restart Netbeans

Friday, 20 April 2012

Move a recent git commit and work in progress to another branch

Thanks to @LukeCarrier !!

The Issue

I had already pushed the master and moved onto writing code for ticket 324. I did a commit on the master the night before then continued coding the next day.

I then wanted to move the code to a test branch and remove from the master.

The solution

git status
git stash
git status
git reset --soft HEAD^
git status
git log
git reflog
git checkout -b ticket324
git status
git stash list
git stash pop
git commit
git checkout master
git status

The git stash saved todays changes. The git reset -- soft HEAD^ reverted the commit I made the previous night.

I then created a new branch with git checkout -b ticket324.

The restored the changes saved in the stash with git stash pop.

Then did a git commit to save my changes the the branch ticket324.

And then double checked the master branch to make sure the changes had been removed.

Monday, 9 April 2012

How to delete a Draw Something account

Like most people, I installed the free version of Draw Something then upgraded to stop the annoying adverts.

But the paid version wouldn't let me use the same username.

The work around is to open the free version with your existing username - click the cog in the top right corner, click account and change the user name to something else - then go into the paid version and change the username to the one you had in the free version. You'll have to do the same with the email too.

It doesn't transfer your games unfortunately.

Thursday, 1 March 2012

Netbeans dummy window...

Netbeans not starting? Getting a dummy window?

Open a terminal and try this

killall -9 java

Try Netbeans again and it will magically appear!!

Friday, 17 February 2012

Editing large files

I like Geany for text editing, but it struggles with very large files.

vim is a command line editor that can easily handle large files but its not at all user friendly - the keyboard commands can be quite complex.

Fortunately there is a gui version of vim called Gvim. It has menus, so you don't need to remember all of the vim commands and it handles very large files. Really handy if you want to do a search and replace with a 180mb sql text file.

Install gvim from the Ubuntu Software Centre

Wednesday, 8 February 2012

IT Apprenticeships

Fabulous website created by our apprentices for apprentices

Answer to question on LinkedIn - decision tree

My answer was too long!! :) So posting the answer here with a link on linkedin

Question :

What is the best way to do a "simple" IT expert file?

We have a project where we are trying to determine the best equipment for a particular type of wastewater project. We would like the user to specify the type of water, location, etc. and then we can provide an answer for the best type of equipment, regulations to follow, advice, etc.

Any suggestions on how to best do this?

My answer :

I would have 5 tables

3 main tables for the names and details eg:

equipment : bucket, small sewer, large sewer
location : house, factory
water_type : leak, sewerage

Then 2 tables pairing the options to the equipment

equipment_location : (Small Sewer, House), (Bucket, House), (Large Sewer, Factory), (Small Sewer, Factory), (Bucket, Factory)

equipment_water_type : (Small Sewer, Sewerage), (Large Sewer, Sewerage), (Small Sewer, Sewerage), (Bucket, Leak), (Bucket, Leak)

Then maybe create a view as equipment options eg:

equipment_name, location_name, water_type_name
Bucket, Factory, Leak
Bucket, House, Leak
Large Sewer, Factory, Sewerage
Small Sewer, Factory, Sewerage
Small Sewer, House, Sewerage

Then use a filter from the users form selections using whatever method, drop down lists, checkboxes etc.

eg: if the user selects house and leak

SELECT equipment_name
FROM equipment_options 
WHERE location_name='house' 
AND water_type_name='leak'

result = 'bucket'

eg: or if the user selects factory and sewerage

SELECT equipment_name
FROM equipment_options 
WHERE location_name='factory' 
AND water_type_name='sewerage'

results = 'small sewer' and 'large sewer'

I'm using names above, but a developer would use id's.

And here is a MySQL dump of something I prepared earlier ;) You can import this directly into MySQL

SET time_zone = "+00:00";

/*!40101 SET NAMES utf8 */;

  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)

INSERT INTO `equipment` (`id`, `name`) VALUES
(1, 'Large Sewer'),
(2, 'Small Sewer'),
(3, 'Bucket');

CREATE TABLE IF NOT EXISTS `equipment_location` (
  `equipment_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  PRIMARY KEY (`equipment_id`,`location_id`),
  KEY `location_id` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `equipment_location` (`equipment_id`, `location_id`) VALUES
(2, 1),
(3, 1),
(1, 2),
(2, 2),
(3, 2);
CREATE TABLE IF NOT EXISTS `equipment_options` (
`equipment_id` int(11)
,`equipment_name` text
,`location_id` int(11)
,`location_name` text
,`water_type_id` int(11)
,`water_type_name` text
CREATE TABLE IF NOT EXISTS `equipment_water_type` (
  `equipment_id` int(11) NOT NULL,
  `water_type_id` int(11) NOT NULL,
  PRIMARY KEY (`equipment_id`,`water_type_id`),
  KEY `water_type_id` (`water_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `equipment_water_type` (`equipment_id`, `water_type_id`) VALUES
(1, 1),
(2, 1),
(3, 2);

  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)

INSERT INTO `location` (`id`, `name`) VALUES
(1, 'House'),
(2, 'Factory');

  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)

INSERT INTO `water_type` (`id`, `name`) VALUES
(1, 'Sewerage'),
(2, 'Leak');
DROP TABLE IF EXISTS `equipment_options`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `equipment_options` AS select `e`.`id` AS `equipment_id`,`e`.`name` AS `equipment_name`,`l`.`id` AS `location_id`,`l`.`name` AS `location_name`,`w`.`id` AS `water_type_id`,`w`.`name` AS `water_type_name` from ((((`equipment` `e` join `equipment_location` `el` on((`el`.`equipment_id` = `e`.`id`))) join `equipment_water_type` `ew` on((`ew`.`equipment_id` = `e`.`id`))) join `location` `l` on((`l`.`id` = `el`.`location_id`))) join `water_type` `w` on((`w`.`id` = `ew`.`water_type_id`))) order by `e`.`name`,`l`.`name`,`w`.`name`;

ALTER TABLE `equipment_location`
  ADD CONSTRAINT `equipment_location_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
  ADD CONSTRAINT `equipment_location_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`);

ALTER TABLE `equipment_water_type`
  ADD CONSTRAINT `equipment_water_type_ibfk_4` FOREIGN KEY (`water_type_id`) REFERENCES `water_type` (`id`),
  ADD CONSTRAINT `equipment_water_type_ibfk_3` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`);


Tuesday, 31 January 2012

Tip for checking for database changes from an app

When testing for database changes while running an app.

Make a database dump just before you make a change

Dump as a sql file - phpmyadmin->export or pgadmin3->backup (select 'insert commands' in the options will be easier to read)

Do what you need to do then repeat the above.

Name the dumps as beforeaction.sql and afteraction.sql

Then from a terminal enter

diff -u -s beforeaction.sql afteraction.sql > changes.txt

Open changes.txt in your favourite editor.

Any line beginning with a +INSERT will be a new record.

Any lines beginning with -INSERT will have a matching +INSERT and will be an UPDATE - just look for the differences in the columns.

Thats it :)

Monday, 23 January 2012

Monitor slow Postgresql queries

To monitor slow Postgresql queries, open a terminal and enter (the folder locations are for Ubuntu 11.10, they may be slightly different for other Linux distributions)

gksudo gedit /etc/postgresql/9.1/main/postgresql.conf

(change 9.1 to your version of Postgresql)

Search for and modify the following conf parameters

# Switch logging on
logging_collector = on

# Switch logging durations on
log_duration = on

# Log queries that are over 200ms
log_min_duration_statement = 200

# Log is recorded as a CSV file
log_destination = csvlog

Save the file and restart Postgresql

sudo /etc/init.d/postgresql restart

The logs will then be saved in the following folder


You will need to be root to access the logs, probably easy to start Nautilus as root

gksudo nautilus

Sunday, 15 January 2012

Web Application Building Blocks

These are the building blocks for a typical web application

Larger version

This graphic is my attempt at demonstrating the relationship between the server and the client.

The server uses server side scripting to generate and deliver a html page back to the client browser.

Client side glossary

In web terms, the client is usually a web browser such as Chrome, Firefox, IE. Chrome is fast becoming the most popular browser, this needs to be taken into consideration during design.

AJAX (Asynchronous JavaScript and XML)
Usually, the browser will send a request to the server and the server sends back a html file. To cut down on traffic and to make things a little speedier, developers can use Ajax. This is a snippet of javascript code that sends a request to the server which returns a snippet of data, then a part of the page is modified without having to reload the page.

A typical example is when using a form - for example a list of cars - rather than sending all of the data about all models, we can use a form with a drop down list of car makes - then when a make is selected, use ajax to build a drop down list of models for that make. Have a look at there isn't a list of models until you select a make. AJAX tutorial.

CSS (Cascading Style Sheets)
This defines the style for elements within a html page. Such as font, size, colour, borders etc. The style can be defined within an element, within a html page or more commonly, in a separate file with a css extension. This allows a website to use consistent styling, which looks far more professional. Some html elements have been introduced specifically for use with styling such as <DIV> and <SPAN>. Typically, css is part of a web template or theme.  CSS tutorial

DHTML (Dynamic HTML)
This isn't a language, it is simply a term used to describe HTML content that can change dynamically using a combination of HTML, JavaScript, HTML DOM and CSS. DHTML tutorial

HTML (Hypertext Markup Language)
HTML is the language of the web. HTML is a set of tags that are used to define the content, layout and the formatting of the web document. Web browsers use the HTML tags to define how to display the text. HTML page and HTML document are just different terms for the same thing. HTML tutorial

HTML DOM (HTML Document Object Model)
DOM represents a HTML document as a tree structure with properties and methods. This allows JavaScript to manipulate HTML documents on the client side without having to go back to the server HTML DOM tutorial

JavaScript (client side scripting)
The most popular client side scripting language, originally developed by Netscape. Not to be confused with Java - these are separate languages.
JavaScript tutorial.

There is also JScript and VBScript, both developed by Microsoft for client side scripting in IE. But I strongly suggested learning JavaScript because it is so widely used.

There are also javascript libraries that have been developed to do most of the common functions and routines. The most popular being JQuery but there is also Yui developed by Yahoo.

JSON is an alternative to XML for exchanging data using JavaScript, usually with Ajax.

Client side objects / plugins

Basically, a browser plugin or object is used when the capabilities of the browser have been reached. ie. it doesn't do what you want it to do. Only use a plugin when no other option is available. Some typical plugins are:

  • Flash - probably the most common plugin used - great for creating games, animation and playing videos. But please don't use it for website design. In the future, it might be replaced by HTML5.
  • ActiveX - developed by Microsoft for Internet Explorer. Avoid it simply because it only works in IE.
  • Adobe Air - developed by Adobe for creating desktop applications.
  • QuickTime - multimedia player developed by Apple
  • Shockwave - multimedia player developed by Adobe
  • Java Applets - Java programs that can be downloaded and run on the desktop. Quite common for enterprise applications.

Server side glossary

Web Server
A server is a computer that delivers services or information to other computers. In web terms, a web server delivers content to web browsers. About 65% of the web uses Apache, which is open source. Microsoft have IIS (Internet Information Server) which is used by about 14% of the web.

Server side scripting
These are programs that reside and are run by a web server to generate web pages that are readable by the client. They typically use a combination of HTML code and a scripting language surrounded by tags. Please see my blog about web development for a better description.

The most popular server side scripting languages are:

Database server
All that data needs to be stored somewhere. Typically a database server is used such as MySql, Postgresql, Oracle, SQL Server, etc. For development, you will need to learn SQL - this is a language used to update and retrieve data.

Using a framework can save a developer a LOT of time. They basically have all of the functions and routines required for a typical application. Meaning the developer can concentrate on the nitty gritty stuff. There are application frameworks for each scripting language as well as frameworks for common applications such as content management, eCommerce, eLearning, etc.

XML (eXtensible Markup Language)

XML is used for data transfer. Typically to/from a remote web service. XML tutorial

Most server side scripting languages will have support for XML. 

See also

And that concludes the introduction to web application development. Hope it has been helpful.

Friday, 6 January 2012

Testing Internet Explorer versions

Perfect for testing Internet Explorer versions - Microsoft have provided these images for Windows

Update: 22-Jan-2012

Follow these instructions to setup in VirtualBox.

To get the mouse working, you will also need to install guest additions.

Download VirtualBox 4.1.8 Oracle VM VirtualBox Extension Pack.

Then open the download, which will open VirtualBox by default.

Then start Windows, login as Administrator.

Select "Install Guest Additions" from the VirtualBox Devices menu.

It will ask to reboot Windows and then you are done :)

Thursday, 5 January 2012

Open Source and Security

Its often said by those not in the know, that open source software isn't secure. Its often difficult for me to explain why. Simply saying because everyone can see the code doesn't make a lot of sense - surely that means its less secure? 

Well... Imagine you are a president of a country, or organisation, that isn't in favour with the rest of the world - would you trust software where you couldn't see what the source code was doing?

Here is an excellent article about Richard Stallman who was saying the same thing 30 years ago.