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 »

In the article ‘An alternative way of EAV modelling’, I discussed how to do EAV modelling by casting all values (except text) to integers. I’ll continue on that and talk about more advanced topics like multi-value fields.

As binary set
Not all questions have only a single option. Some fields we want to represent by radio-buttons, allow the user to select any number of options. For this we can mimic the behaviour of the SET field type of MySQL. A SET is almost similar to an ENUM, except that each bit represents an option. The value can have multiple bits enabled to represent multiple options.

Example: field options for field ‘programming language’

+-------+-------------+
| value | description |
+-------+-------------+
| 1     | C/C++       |
| 2     | PHP         |
| 4     | Java        |
| 8     | Python      |
| 16    | Ruby        |
+-------+-------------+

Choosing ‘C/C++’, ‘PHP’ and ‘Python’ would result in value 11.

There are 2 major disadvantages to this approach. First, with an integer, the number of options is limited to 4*8 = 32. Second, retrieving the description of options would cause joining field on field_option to be done like:

SELECT value_display(`field_name`, value_display(`field_type`, `value`.`value`, GROUP_CONCAT(`field_option`.`description`), `value`.`text`, `precision`, `date_format`)) FROM `value` INNER JOIN `field` ON `value`.`fid` = `field`.`fid` LEFT JOIN `field_option` ON `value`.`fid` = `field_option`.`fid` AND (IF(`field`.`field_type`='SET, `value`.`value` & `field_option`.`value`, `value`.`value` = `field_option`.`value`)) WHERE `value`.`item_id`=? GROUP BY `value`.`fid`;

Because of this, the index on the value isn’t used. Instead, a function has to be performed on each option of the field. This should still give decent enough performance, because this scales linearly and not exponential.

Multiple values
Another way is to store multiple values for the same property. This would mean replacing the primary key from the value table by a normal index.

CREATE TABLE `value` (
  `item_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `fid` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `text` text,
  KEY `item_field`  (`item_id`,`fid`),
  KEY `value` (`fid`,`value`),
  KEY `text` (`text`(255))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
SELECT value_display(`field_name`, value_display(`field_type`, `value`.`value`, GROUP_CONCAT(`field_option`.`description`), `value`.`text`, `precision`, `date_format`)) FROM `value` INNER JOIN `field` ON `value`.`fid` = `field`.`fid` LEFT JOIN `field_option` ON `value`.`fid` = `field_option`.`fid` AND `value`.`value` = `field_option`.`value` WHERE `value`.`item_id`=? GROUP BY `value`.`fid`;

The disadvantage of this, is that structural integrity is no longer enforced by the database. Only for a field with the type ‘SET’, multiple values should be allowed. However the database will allow multiple values for other field types (like numeric fields) as well. It is up to the application to replace existing values.

Ranges with a single field
A completely different type of multi-value is ranges. A range has a top and bottom value. We can solve this by saving 2 values in the database. Of the values, the the highest one is always the upper limit and the lowest is the bottom limit.

delimiter |
CREATE FUNCTION `value_display` (`type` enum('NUMBER', 'ENUM', 'DATE', 'TIME', 'TEXT'), `value` INT, <strong>`max_value` INT</strong>, `option` VARCHAR(255), `text` TEXT, `precision` INT, `date_format` VARCHAR(50)) RETURNS VARCHAR(255) CHARACTER SET latin1 NO SQL
BEGIN
  // ...
END
 
SELECT value_display(`field_name`, value_display(`field_type`, MIN(`value`.`value`), IF(COUNT(*)>1, MAX(`value`.`value`, NULL), GROUP_CONCAT(`field_option`.`description`), `value`.`text`, `precision`, `date_format`)) FROM `value` INNER JOIN `field` ON `value`.`fid` = `field`.`fid` LEFT JOIN `field_option` ON `value`.`fid` = `field_option`.`fid` AND `value`.`value` = `field_option`.`value` WHERE `value`.`item_id`=? GROUP BY `value`.`fid`;

Range by 2 fields
Another was of looking at ranges is that the are simply 2 individual fields, eg `min weight` and `max weight`. When filtering you might want to get all items that have 70 kg in their weight range. You would translate this to `min weight` <= 70 and `max weight` >= 70. (In the example query `min weight` has fid=2 and `max weight` fid=3.)

SELECT `item`.* FROM `item` INNER JOIN `value` ON `item`.`id`=`value`.`item_id` AND `value`.`fid` IN (2, 3) WHERE (`value`.`fid`=2 AND `value`.`value` <= 70) OR (`value`.`fid`=3 AND `value`.`value` >= 70) HAVING count(*) =2 GROUP BY `item`.`id`;

Conclusion
We see that using multi-value fields forces to group on fid and generally complicates the queries. If you only need ranges it might be a good idea to use 2 fields instead to keep the queries simpler.

The code in this article has not been tested.

Officially PHP doesn’t support multiple inheritance. There are several ways around this, without having to duplicate code.

Wrapper

The most commonly used method is to use a wrapper object.

<?php
 
abstract class FsNode
{
  public $path;
 
  public function __construct($path) {
    $this->path = $path;
  }
 
  public function rename($newname) {
    rename($this->path, $newname);
    $this->path = $newname;
  }
}
 
class File extends FsNode
{
  public function getContents() {
    return file_get_contents($this->path);
  }
}
 
class Dir extends FsNode
{
  public function scandir() {
    return scandir($this->path);
  }
}
 
class Symlink
{
  protected $node;
 
  public function __construct($node) {
    $this->node  = $node;
  }
 
  public function target($resolve=false) {
    return $resolve ? realpath($this->node->path) : readlink($this->node->path);
  }
 
  public function __call($method, $args) {
    return call_user_func_array(array($this->node, $method), $args);
  }
}
 
$dir = new Dir("/proc");
$linktodir = new Symlink(new Dir("/proc/self"));
 
var_dump($linktodir->scandir()); // Will be called through __call()
echo $linktodir->target(true), "\n";

A disadvantage is that is no longer possible to see if a node is a dir by using instanceof. Also, if most of the methods are defined in the wrapped class, this solution will hurt performance.

Mixin

A far more interesting approach is to use a mixins. When you call a non-static method, $this is always passed to that method. This is also the case if the calling object is not inherited from the called class. We can use that to our advantage to do the reverse of the wrapper.

abstract class FsNode
{
  public $mixin;
  public $path;
 
  public function __construct($path, $mixin=null) {
    $this->path = $path;
    $this->mixin = $mixin;
  }
 
  function rename($newname) {
    rename($this->path, $newname);
    $this->path = $newname;
  }
 
  public  function __call($method, $args) {
    if (isset($this->mixin) && ctype_alnum($method) && is_callable(array($this->mixin, $method))) {
      return eval("return {$this->mixin}::$method(" . (!empty($args) ? '$args[' . join('], $args[', array_keys($args)) . ']' : '') . ");");
    }
    trigger_error("Call to undefined method " . get_class($this) . "::$method()", E_USER_ERROR);
  }
}
 
class File extends FsNode
{
  public function getContents() {
    return file_get_contents($this->path);
  }
}
 
class Dir extends FsNode
{
  public function scandir() {
    return scandir($this->path);
  }
}
 
class Symlink extends FsNode
{
  public function target($resolve=false) {
    return $resolve ? realpath($this->path) : readlink($this->path);
  }
}
 
$dir = new Dir("/proc");
$linktodir = new Dir("/proc/self", 'Symlink');
 
var_dump($linktodir->scandir());
echo $linktodir->target(true), "\n"; // Will be called through __call()

caveat: The Symlink class is never instantiated. Properties defined in the Symlink class are ignored. Also, since Symlink doesn’t extends Dir, it’s not possible to access protected properties defined in Dir.

Compile time mixin

To see if a node is a symlink, you would need to do

if (isset($file->mixin) && ($file->mixin === 'Symlink' || is_subclass_of($file->mixin, 'Symlink'))) {
  //...
}

It would be nicer if you could simply use instance of. This is only possible by defining all combination. We can still use mixins though to prevent having to duplicate code.

abstract class FsNode
{
  protected $mixin;
  // Same as above
}
 
class File extends FsNode
{
  // Same as above
}
 
class Dir extends FsNode
{
  // Same as above
}
 
interface Symlink
{}
 
abstract class Symlink_Methods extends FsNode
{
  public function target($resolve=false) {
    return $resolve ? realpath($this->path) : readlink($this->path);
  }
}
 
class SymlinkFile extends File implements Symlink
{
  protected $mixin = 'Symlink_Methods';
}
 
class SymlinkDir extends Dir implements Symlink
{
  protected $mixin = 'Symlink_Methods';
}
 
$dir = new Dir("/proc");
$linktodir = new SymlinkDir("/proc/self");
 
var_dump($linktodir->scandir());
echo $linktodir->target(true), "\n"; // Will be called through __call()
 
if ($dir instanceof Dir)  ; // True
if ($dir instanceof Symlink)  ; // False
if ($linktodir instanceof Dir)  ; // True
if ($linktodir instanceof Symlink)  ; // True

The hardened-php project has yet to release a suhosin patch for PHP 5.3.0. We’re already using PHP 5.3, therefore I’ve modified the 0.9.7 patch for 5.2.10 to work with 5.3.0.

-> Download Suhosin patch for PHP 5.3 <-

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.

Sun will be bought by Oracle. Will this be the beginning of the end of MySQL?

MySQL has a serious market share. For that oracle has already tried to buy MySQL back in 2006. In an interview responding to the offer in 2006, MySQL chief Marten Mickos told that the reason for declining was that they wanted to keep MySQL an independent product. From that I assume that oracle wanted to merge Oracle DB and MySQL technology. Even though MySQL will be part of a major merger for which the rules aren’t yet clear, you might think that Oracle hasn’t changed their ideas about what they want with MySQL in the last 3 years.

Won’t MySQL just lose most of its market share if it become something else. Other databases like PostgreSQL have been making mayor steps and are in many expects better than MySQL. MySQL has remained to be the only serious open-source RDBMS in respects of market share though. I believe this is mainly because MySQL is known, tried and tested. This might be a fragile thing though.

Based on Oracle’s decision, I might just take a more serious look at PostgreSQL. Changing is usually not so nice, but change often is.

Any thoughts? Leave a comment or trackback.

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 »

Happy new year!! It has been way to quit on my blog lately. My new years resolution is to change this though. I have much in store, though I’m starting of with something small (but handy).

Simple replacement
String replacement is often used as a way to apply templating. You might replace “%a:test” with “~~test~~” using the regexp: %a:(\w+), replacing it with “~~$1~~”.

Trying to escape
The only problem now, is that I can’t use “%a:” any more within my string. This could be solved by allowing escaping using the backslash. In the regexp we can use a negative lookbehind to see if the character before the % isn’t a backslash: (?<!\\)%a:(\w+).

Escaping the escaping
Now we’re close, however now it’s not possible to use “\%:a” anywhere. We need to be able to escape the backslash as well. We could state the problem as needing to match %a if there isn’t an uneven number of backslashes in front of it. Checking for an uneven number in a negative lookbehind isn’t possible unfortunately, so we need to get the backslashes into the match. We can say: match 0 or more pairs of backslashes, followed by “%a:”, if there is no backslash in front of it. This results in the regexp:
(?<!\\)((?:\\{2})*+)%a:(\w+), replacing it for “$1~~$2~~”.

To finish up
To only thing is that \% and \\ will still be displayed as that. This can simply be solved with a str_replace.

Data stored in a database is often also useful for statistical purposes. If you own a web-shop you want to be able to create a report about turnover. You can get statistical information by using GROUP BY, eg.

SELECT DATE_FORMAT(invoice.date, '%M') AS `month`, COUNT(*) AS `invoice_count`, SUM(`invoice`.`amount`) AS `turnover`
FROM `invoice`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY MONTH(`invoice`.`date`)

month     invoice_count  turnover
January   84             9532.26
February  141            20857.61
March     91             10922.71
April     112            15044.48
May       101            9676.60
June      137            12860.88
July      281            34291.20
August    191            26377.66
September 103            16324.78
October   99             12873.23

If you are selling a wide variety of products, you might like to see the turnover for each product category. You could do this with a simple GROUP BY as:

SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, `category`.`description` AS `category`, COUNT(*) AS `product_count`, SUM(`invoice_product`.`amount`) AS `turnover`
FROM `invoice` INNER JOIN `invoice_product` ON `invoice`.`id` = `invoice_product`.`invoice_id` LEFT JOIN `product` ON `invoice_product`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31' 
GROUP BY MONTH(`invoice`.`date`), `category`.`id`

month     category   product_count  turnover
January   Hardware   62             4821.31
January   Software   51             4419.41
January   Cables     12             291.54
February  Hardware   71             8408.93
February  Software   101            11726.36
February  Cables     17             312.32
February  Other      2              410.00
March     Hardware   21             2371.58
March     Software   81             8238.81
March     Cables     13             312.32
...

This would give you each category in a different row, ordered by month. Though this contains all the information the format is far from nice. Instead you would like to have 1 row per month with each category as a column as the information about the invoices as well.

SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, COUNT(DISTINCT `invoice`.`id`) AS `product_count`, COUNT(*) AS `invoice_count`, SUM(`invoice_product`.`amount`) AS `turnover`,
  SUM(`product`.`category_id`=1) AS `hardware_count`, SUM(IF(`product`.`category_id`=1, `invoice_product`.`amount`, 0)) AS `hardware_turnover`,
  SUM(`product`.`category_id`=2) AS `software_count`, SUM(IF(`product`.`category_id`=2, `invoice_product`.`amount`, 0)) AS `software_turnover`,
  SUM(`product`.`category_id`=3) AS `cables_count`, SUM(IF(`product`.`category_id`=3, `invoice_product`.`amount`, 0)) AS `cables_turnover`,
  SUM(`product`.`category_id`=4) AS `other_count`, SUM(IF(`product`.`category_id`=4, `invoice_product`.`amount`, 0)) AS `other_turnover`
FROM `invoice` INNER JOIN `invoice_product` ON `invoice`.`id` = `invoice_product`.`invoice_id` LEFT JOIN `product` ON `invoice_product`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY MONTH(`invoice`.`date`), `category`.`id`

month     invoice_count  turnover    hardware_count  hardware_turnover  software_count  software_turnover  cables_count  cables_turnover  other_count  other_turnover
January   84             9532.26     62              4821.31            51              4419.41            12            291.54           0            0
February  141            20857.61    71              8408.93            101             11726.36           17            312.32           2            410.00
March     91             10922.71    21              2371.58            81              8238.81            13            312.32           0            0
...

The big downside of this method is that you need to modify the query if a category is added. This can be solved though by dynamically creating the query in a PHP script.

If you want to do advanced statistical you should have a look at OLAP cubes. Pentaho is an open-source reporting app which supports MySQL. http://mondrian.pentaho.org/

All data is fictional. The SQL queries are untested.