Arnold’s wor(l)ds

In the article ‘Versioning MySQL data‘, I showed the basics of implementing a revisioning system using trigger. As Jens Schauder already pointed out, often the data of a record is spread across multiple tables, like an invoice with multiple invoice lines. Having each invoice line versioned individually isn’t really useful. Instead we want a new revision of the whole invoice on each change.

The perfect solution
Ideally a change of one or more parts of the invoice would be changed, a new revision would be created. There are several issues in actually creating this those. Detecting the change of multiple parts of the invoice at once, generating a single revision, would mean we need to know if the actions are done within the same transaction. Unfortunately there is a connection_id(), but no transaction_id() function in MySQL. Also, the query would fail when a query inserts or updates a record in the child table, using the parent table. We need to come up with something else.

In the implementation we currently have in production, we version the rows in the parent as well in the child tables. For each version of the parent row, we register which versions of the child rows ware set. This however has really complicated the trigger code and tends to need a lot of checking an querying slowing the write process down. Since nobody ever looks at the versions of the child rows, the application forces a new version of the parent row. The benefits of versioning both are therefor minimal.

Only versioning the parent
For this new (simplified) implementation, we will only have one revision number across all tables of the record. Changing data from the parent table, will trigger a new version. This will not only copy the parent row to the revisioning table, but also the rows of the children.

Writing to the child will not trigger a new version, instead it will update the data in the revisioning table. This means that when changing the record, you need to write to the parent table, before writing to the child tables. To force a new version without changing values use

UPDATE mytable SET _revision=NULL WHERE id=$id

Continue Reading »

As a developer you’re probably using a versioning control system, like subversion or git, to safeguard your data. Advantages of using a VCS are that you can walk to the individual changes for a document, see who made each change and revert back to specific revision if needed. These are features which would also be nice for data stored in a database. With the use of triggers we can implement versioning for data stored in a MySQL db.

The revisioning table
We will not store the different versions of the records in the original table. We want this solution to be in the database layer instead of putting all the logic in the application layer. Instead we’ll create a new table, which stores all the different versions and lives next to the original table, which only contains the current version of each record. This revisioning table is copy of the original table, with a couple of additional fields.

CREATE TABLE `_revision_mytable` LIKE `mytable`;
 
ALTER TABLE `_revision_mytable`
  CHANGE `id` `id` int(10) UNSIGNED,
  DROP PRIMARY KEY,
  ADD `_revision` bigint UNSIGNED AUTO_INCREMENT,
  ADD `_revision_previous` bigint UNSIGNED NULL,
  ADD `_revision_action` enum('INSERT','UPDATE') DEFAULT NULL,
  ADD `_revision_user_id` int(10) UNSIGNED NULL,
  ADD `_revision_timestamp` datetime NULL DEFAULT NULL,
  ADD `_revision_comment` text NULL,
  ADD PRIMARY KEY (`_revision`),
  ADD INDEX (`_revision_previous`),
  ADD INDEX `org_primary` (`id`);

The most important field is `_revision`. This field contains a unique identifier for a version of a record from the table. Since this is the unique identifier in the revisioning table, the original id field becomes a normal (indexed) field.
Continue Reading »

I’ve seen a lot of methods used to take a website temporarily off-line for maintenance. Most involve a using PHP to disable the site or renaming the index file. There is however a far better method of doing this, by placing the following in the vhost file or in an .htaccess file in the document root:

Header always set Retry-After "Thu, 18 Jun 2009 08:00:00 +0200"
Redirect 503 /

This way you are sure no part of the site is used. Also by returning a 503 http response, search-engine crawlers will not reindex your site right at the moment it is down. You can use ‘ErrorDocument’ to place a different text than the apache default.

Associated websites often share user information, so a visitor only has to register once and can use that username and password for all sites. A good example for this is Google. You can use you google account for GMail, Blogger, iGoogle, google code, etc. This is nice, but it would be even nicer if logging in for GMail would mean I’m also logged in for the other websites. For that you need to implement single sign-on (SSO).

There are many single sign-on applications and protocols. Most of these are fairly complex. Applications often come with full user management solutions. This makes them difficult to integrate. Most solutions also don’t work well with AJAX, because redirection is used to let the visitor log in at the SSO server.

I’ve written a simple single sign-on solution (400 lines of code), which works by linking sessions. This solutions works for normal websites as well as AJAX sites.
Continue Reading »

I was reading this month’s php|architect. It has a nice article about EAV modeling. I had seen this db structure in other project, but didn’t know that it was called EAV. For those who don’t read php|architect, EAV describes a method of saving a large set of attributes, only some of which apply to an individual entity. Normally you would create a table, with a row for each entity and save each attribute in a column. With EAV you save each attribute as a row.

This makes selecting the data quite tedious. If you can life with some constraints, there is an easier way to do this.
Continue Reading »

The normal way to save a state, like the fact that a user is logged in, is to use sessions. Session work really nice, you can save all kind of data on the server (in a temp file) which is identified by a hash which is known on the client in a cookie or by url rewriting.

However if you only need to save a small bit of login info, like a username and timestamp, using sessions is a bit of an overkill. Also, if your system would grow beyond to a size where you need load balancing over multiple servers, having a solution with sessions would be somewhat of a burden. You would need to store them in a centralized place, like a DB server.

Another way to do this, is to create an authentication hash. The idea is fairly simple: Join the information you want to known into a single string and append an md5 key of all the info + a secret word. On each request, check if the hash is available and correct, otherwise redirect the user to a login form. You can use the hash the same way PHP uses the session hash, using cookies or URL rewriting.

[snip php-source]code/authhash/authhash.php[/snip]
Download the code

Personally I love using regular expressions. It simply the best way to parse/split/replace/validate pieces of text. Many people look at regular expressions as black magic. They might be able to create a regex, but don’t understand the flow. When you are creating more advanced expressions, it is important to understand the inner workings for many reasons, including performance.

Let’s have a look at the following regular expression: ^(\d+)(\d+)(\d+)$. It matches one or more digits and does that three times. It is an illogical expression, but bear with me.

Let’s match the string “12345678901234567890″. When we look at the inner workings of the regex engine we can see what is happening:
Continue Reading »

Yesterday, PHPBuilder posted an article about sending a form using AJAX. This article shows how to post a simple form. But looking closely at the example, you can also see what the problem is using the plain XMLHttpRequest object and writing an implementation yourself.

First of all you to write some rather difficult javascript code. Next you need to completely rewrite your page, looking nor working like a normal HTML form. Last and most important, the function fetches values using getElementById(). This is not really very flexible, because you will need not only the form, but the function as well when you add a field.

Using an AJAX library can really help you here. Javeline TelePort has got a brand new method, which automatically creates a HTTP post request of a form and sends it to the server.
Continue Reading »

Please use http://beepme.javeline.com/bugs/ to report bugs and not this blog.

We have been working hard on the BeepMe and FeedMe Facebook applications. And BeepMe is almost ready to be published.

BeepMe is a notifier for Facebook that runs in your system tray. Whenever you get a new message, friend invite, etc you’ll get a pop-up. There also a cool feature to add a text box to your Facebook profile allowing friends (or others depending on you settings) to send a pop-up message to you instantly.

I have BeepMe running under Linux, though it is a windows only application. Using crossover (commercial version of wine) I am able to run it on my gnome desktop.

Goto http://apps.facebook.com/_beepme/ to give BeepMe a try.

We at Javeline are currently working on creating 2 applications for FeedMe and BeepMe. FeedMe is an RSS aggregator and reader and BeepMe is a notifier. Both will use Javeline PlatForm and BeepMe will also use DeskRun. But more on this later.

Creating a facebook interface works quite well. They supply a PHP client API and decent documentation http://developers.facebook.com. Because of the provided SDK, most of what happens is magic. When you develop an Ajax application, you might run into some unexpected trouble because of this. Instead of getting the requested data, you get a <script> tag with an redirect to the facebook login page.

What you need to do it send the complete HTTP query with the request, eg:

Normally facebook solve this problem internally, but it can only do so if is in the browser and in an iframe.

Anyway, if you are using facebook, stay tuned for these apps, because they will pebble (rock is a bit of an overstatement) your world.