Multiuser atomic SQL; day one

Recently we've taken over support for a new, externally developed application that sends text messages to mobiles in response to shortcode requests for more information about cars, houses etc for sale.

Under development and testing everything was fine. However, when it went live the registration process occasionally failed. After a brief examination I noticed this method for inserting records in the database class:

$newId = $this->conn->nextID($tableName . "_id");

I suspected that this did something a little unusual.

It had been written in Php5 backed by MySQL and was using the Pear DB package. It rapidly became clear the book "Professional PHP5" had been used a little too religiously. The book suggests this facade method for generating and getting the next ID for a new record that hides the operational differences between MySQL, PostreSQL, Oracle and other RDBMS.

Under the hood, Pear DB creates an id tracking table of the same name suffixed with "_id_seq" for each table storing data with unique ids. It gets the value from this tracking table, adds one to the value, stores it back, and returns this value as the next id to be used in the insert statement for the new record.

So I heard websites and the internet was a concurrent multiuser enviroment? Can anyone guess what might be the problem generating unique ids this way?

MySQL gives the us the auto_increment feature. It also gives us the mysql_insert_id method. This allows us to get the last auto generated id if we need it:

$newId = mysql_insert_id($this->conn->connection);

"But surely, when inserting new records in a concurrent multiuser environment, we need actions like this to be as atomic as possible?" I hear you ask. The force (read SQL) is strong with this one. Correct.

MySQL allows us to provide a value for an auto_increment field, or we can supply 'null' and allow MySQL to do it for us.

So to fix this intermittent registration failure, we replace the line:

$newId = $this->conn->nextID($tableName . "_id");

with:

$newId = 'null';

And LO, the bug is gone.
Needless to say Pear DB has been replaced by something else. And interestingly if you read on a little in the book you'll see "we strongly recommend... ideally, allow your RDBMS to [generate the id]"

So what? Read books carefully. Know the technology you're using. Concurrency requires atomic superness.

Vagaries of geocoding a postcode

Plotting 'lat lngs' on a map is a fairly straight forward task. Especially doing it on a Google map.

Using a .NET implementation of a Google map is nice and easy. Doing it with PHP and javaScript is a bit more of a work up, but perhaps more satisfying when you get dirty and use some of the natty little Google features available.

However, in the course of writing a site for a Letting agent [www.batterseaflats.com] I came across an unexpected result when geocoding via HTTP with Google.

To make the site really easy to administrate details of properties I thought it would be nice to have a field containing the property's postcode, and during the save process, geocode up the postcode to get a nice 'lat lng' to show on a map. Of course I based the expected result on the performance of locating a postcode via Google's own maps.google.co.uk interface, which is nice and accurate.

Unfortunately, I often ended up with addresses that weren't quite in the right place. Much as I like Germany, I was quite sure I shouldn't be displaying properties outside of London!

I switched to using the GClientGeocoder Google object, but it still wasn't accurate enough. (Consider having four or five properties in the same street to advertise, you really want them to be in the right place on the street).

So I thought I'd let the user geocode an address string (because flats are sometimes in blocks with a name that can cause problems trying to guess what the user has typed in), then allow them to move the point around on the map. A simple feature, which the client really liked!

Use GClientGeocoder to get location:

function locateAddress(address) {

geocoder.getLatLng(

address,

function(point) {

if (!point) {

alert(address + " not found");

} else {

showPoint(point);

}

});

}

When creating the marker on the map, enable the 'draggable' GMarker property:

var marker = new GMarker(point, { icon: iconAvailable, draggable: true, dragCrossmove: true });

and listen for the marker's 'dragend' and capture the new location:

GEvent.addListener(marker, "dragend", function(latlng) {

captureLatLng(latlng);

});

And when displaying the list of properties, go crazy by synchronising the mouse hovering on the sidebar list of entries with the markers by adding a Event Listeners for 'mouseover' and 'mouseout' switching the styles as needed:

GEvent.addDomListener(div, 'mouseover', function() {

div.className = 'sideHighlight';

marker.setImage('images/highlightHouse.png');

});

GEvent.addDomListener(div, 'mouseout', function() {

div.className = 'sideNormal';

marker.setImage('images/house.png');

});

Details of the Battersea Flats project can be found here: http://www.compsoft.co.uk/Portfolio_Battersea_Flats