I want to be free

Are you aware of Federated engine in MySQL (apart from MyISAM and InnoDB)?

This engine allows you to define a table that sucks data from another table, even from a remore server. The tables definition must be the same.

I use it for the following:

1. Every time I rebuild the project, I have wait for 15 minutes while two big tables are created and filled with data — these are geo data tables (world cities, regions, etc), 4 mln records, and POI table, 2 mln records. I use Federated tables to create two separate databases and just link these tables in my project.
2. These tables are shared between several environments (dev, test and live) on the same server.

To check if your MySQL server has the Federated engine supported, you can use just a phpMyAdmin — go to home page of you phpMyAdmin installation (click Home picture), then choose Engines tab and check there.

If it’s not enabled (gray), open your my.ini file, find the “[mysqld]” part and make it to look like this:

[mysqld]
federated

P.S. If you have an error in the table definition, phpMyAdmin shows your database as empty. To fix this, log in via mysql console and try to make a SELECT from this poorly defined table and you get the error message to work with.

That’s easy, you need 2 things:

1. Fresh currencies exchange rates
2. Some way to excange amount from one currency to another.

This how I did it: get values from European Central Bank (ECB) for step #1 and wrote MySQL user defined function for step #2.

Here is how to export currencies rates from ECB (EUR is a base currency, and I add self rate as 1:1). First I create such database table:

CREATE TABLE IF NOT EXISTS `currency` (
  `code` char(3) NOT NULL DEFAULT '',
  `rate` decimal(10,5) NOT NULL COMMENT 'Rate to EUR got from www.ecb.int',
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Currency rates (regularly updated)';

Now let’s fill it with rates:

<?php

class CurrencyController extends Controller_Ajax_Action {

  public function importAction() {

    $db = Zend_Registry::get('db');
    $db->beginTransaction();

    $url = 'http://www.ecb.int/stats/eurofxref/eurofxref.zip?1c7a343768baab4322620e3498553b5a';
    try {
      $contents = file_get_contents($url);
      $contents = archive::unzip($contents);
      $contents = explode("\n", $contents);

      $names = explode(',', $contents[0]);
      $rates = explode(',', $contents[1]);

      $names[] = 'EUR';
      $rates[] = 1;

      for ($i = 1; $i < sizeof($names); $i++) {
        if (!(float) $rates[$i]) continue;
        $db->query( sprintf('INSERT INTO `currency`(`code`, `rate`)
              VALUES ("%s", %10.5f)
              ON DUPLICATE KEY UPDATE `rate`=VALUES(`rate`)',
             trim( $names[$i] ),
             trim( $rates[$i] )
        ) );
      }

      $db->commit();
    } catch ( Exception $O_o ) {
      error_log( $O_o->getMessage() );
      $db->rollback();
    }

  }
}

Now let’s create a SQL function for handy converts. Create a udf.sql file and add this in it:

DELIMITER //

DROP  FUNCTION IF EXISTS EXCHANGE;
CREATE FUNCTION EXCHANGE( amount DOUBLE, cFrom CHAR(3), cTo CHAR(3) ) RETURNS DOUBLE READS SQL DATA DETERMINISTIC
    COMMENT 'converts money amount from one currency to another'
BEGIN
    DECLARE rateFrom DOUBLE DEFAULT 0;
    DECLARE rateTo DOUBLE DEFAULT 0;

    SELECT `rate` INTO rateFrom FROM `currency` WHERE `code` = cFrom;
    SELECT `rate` INTO rateTo   FROM `currency` WHERE `code` = cTo;

    IF ISNULL( rateFrom ) OR ISNULL( rateTo ) THEN
        RETURN NULL;
    END IF;

    RETURN amount * rateTo / rateFrom;
END; //

DELIMITER ;

and run this command in your shell:

mysql --user=USER --password=PASS DATABASE < udf.sql

This is how you can use this function — how to convert 10 US dollars to Canadian dollars:

SELECT EXCHANGE( 10, 'USD', 'CAD')

which results in $10 = 10.93 Canadian dollars.

P.S. Consider adding the currency export action call to your cron scripts.

P.P.S. A function to unzip the data file can be got at php.net

I want to share a couple of features I use to handle AJAX requests in projects based on Zend Framework.

1. AJAX request handling

What: some parts of your application can be not loaded if currect request is AJAX.

Why: you don’t need views, templates, some routes — so you can add an AJAX check in your Initializer or bootstrap file and avoid loading not necessary things.

How: Zend Request object has a isXmlHttpRequest method to find out whether it’s AJAX request or not. It’s based on ‘X-Requested-With‘ header, which is sent by jQuery, Prototype, Scriptaculous, YUI and MochiKit frameworks.

2. AJAX Controller

Most AJAX controller’s methods I saw had an exit() inside to not to output Zend’s template — it is a work-around. The proper way to do so is to tell to Zend not to load anything. One step forward is to create an abstract Controller class and inherit all you AJAX classes from it:

/library/Koodix/Controller/Ajax/Action.php:

<?php

require_once 'Zend/Controller/Action.php';

abstract class Koodix_Controller_Ajax_Action
  extends Zend_Controller_Action
{
    public function init() {
        //disable the standard layout output
        $this->_helper->layout()->disableLayout();
        $this->_helper->viewRenderer->setNoRender();
    }

    public function postDispatch() {
        //envelope and output json field
            if( !empty( $this->json ) ) {
                echo json_encode( $this->json );
            }
        }
}

/application/modules/default/controllers/AjaxController.php:

<?php

class AjaxController extends Koodix_Controller_Ajax_Action
{
 // bla-bla-bla

Take a look at postDispatch method — idea behind it is to convert to JSON and output anything that is set to json field of your controller. If you want to send JSON data in special header (and not in body, like it’s done in my example), you can do it in this method.

You are working on a project and you want to protect the beta version with password so that only allowed people (beta testers) could access it.

You decide not to invent the wheel and to use the standard HTTP authentication.

First idea is to use your Apache web server to do this, so you write something like that in .htaccess file:

AuthName "Private zone"
AuthType Basic
AuthUserFile /path/to/.htpasswd
require valid-use

This solution is simple and that’s why good.

A problem comes on stage when a Flash file uploader is added to your project – usually it cannot “login” to your site, i.e. users are not able to use the Flash file uploader behind beta login.

That’s how I solved it.

It’s not the web server who must solve this (Apache), it’s the application server (PHP). So remove the lines above from .htaccess and use Zend_Auth_Adapter_Http for this purpose — it’s Zend’s HTTP Authentication Adapter.

What concerns the Flash uploader: it sends ‘Shockwave Flash’ as value of ‘User-Agent’ request header. So in your Initializer or Bootstrap file (where you load Zend_Auth_Adapter_Http) check this header value, and if it’s not Flash’s, go for HTTP authentication.

P.S. Hackers can assume this and fake the header to access your site. To cope with that, use an additional secret request variable (Flash uploaders allow this) and check it at server side.

Reverse Geocoding is process opposite to Geocoding (when you get map coordinates by city/country given). So the idea is to get city name by coordinates on the map.

Why you may need it? For example, user is supposed to add a marker on the map, and you want to check that the marker is within a particular country or region.

Solutions:

1. Google GeoCoder (JavaScript) can work with coordinates to retrieve location name. Advantage is that return information is translated to the preferred language of your browser. Here is an example you can play with.
2. GeoNames.org offers several services, among which you can find Reverse Geocoding web service (REST or JSON) which can work with coordinates and postal codes. Advantage — you can download city/region/country names and POI with coordinates.

By the way, here are results of benchmarking Google vs. GeoNames.

Data feeds often come in XML format, so your application must be able to deal with that format.

As I already wrote, data in CSV format (comma separated values) can be loaded to database extremely fast. So my idea was to convert XML data files to CSV and then use bulk load to database. My tests shown that this is faster in 10-100 times than one by one inserts.

Yesterday I decided to write a generalized solution for this, and it turned out that there is no need: it’s just coming — MySQL 6 will have such feature!

How it works: you create a table, name its columns exactly as XML nodes/attributes names or — and MySQL server will load it correspondently.

Example — you downloaded a POI list file (Points of Interest) called poi.xml that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<gpx>
    <wpt lat="58.691931900" lon="11.253125962">
        <name>Parking</name>
    </wpt>
    <wpt lat="58.315525000" lon="12.305828000">
          <name>Fast Food Restaurant:Max i trollhattan</name>
    </wpt>
    <wpt lat="57.717958100" lon="11.880860600">
          <name>Picnic spot</name>
    </wpt>
</gpx>

You create a MySQL table:

CREATE TABLE IF NOT EXISTS `poi` (
  `lat` varchar(255) NOT NULL,
  `lon` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL
) DEFAULT CHARSET=utf8;

OK, now you load the XML data to your table:

LOAD XML INFILE '\\path\\to\\poi.txt'
INTO TABLE `poi`
ROWS IDENTIFIED BY '<wpt>'

Voila!

The good thing is that MySQL 6 is already available in alpha version — good enough for development purposes; I gave it a try — it takes 5 seconds to load 4.8 Mb of data in 19 files.

I use Assembla for my projects, and I found that export/import tickets feature works tricky — it works based on a combination of JSON and CSV format. It seems the guys in Assembla are on a way to proprietary formats The only usage of it is only for moving tickets from one Assembla space to another.

So this post is about how to use what they offer to add lots of tickets that you have listed in your text file. I am lazy — instead of adding 90+ tickets manually, I’d better create a tool to do it for me.

Task

You have tickets titles and descriptions as text, and you want to add these tickets to Assembla.

My solution

Let’s investigate what format should our data be of so that Assembla could digest it. Go to a space → Tickets → Settings → scroll down and find Export & Import → click “Export your tickets” links and open the dump file in a text editor. Though it’s indicated that “Tickets imported and exported in JSON format.“, it’s not. It’s a mix.

The dump file contains blocks that represent spaces, milestones, tickets, comments, custom fields values and tickets associations. Every such block contains 2 parts: list of fields and bigger part — actual data of this fields.

What we need is tickets part.

This is a format definition part:

tickets:fields, ["id","number","reporter_id","assigned_to_id","space_id","summary","priority","status","description","created_on","updated_at","milestone_id","component_id","notification_list","completed_date","working_hours","is_story","from_support"]

And this is an example of ticket data:

tickets, ["40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","Property owner profile - create new","3","3","","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]

So, the solution is to create a list of tickets data by the format given. Here is how I did that.

1. I have Excel spreadsheet with two columns — the ticket title and description.

2. Export it as CSV, and open it having it like this:

Home page: Update design,Set numbers after locations
Photos: hide step 2,Show only Step 1 until you select files and then show step 2 to upload files.

3. Use text replace to put every value in quotes (hint: use a line ending symbol as a replacement — even Microsoft Word can do that)

"Home page: Update design","Set numbers after locations"
"Photos upload: hide step 2","Show Step 2 only if files are selected."

(One way round is to load the CSV data into database by PHPMyAdmin and export it back as CSV — it will be quoted )

4. Add Assembla header to the top of this file

5. Move ‘summary‘ and ‘description‘ columns go first, so it was

tickets:fields, ["id","number","reporter_id","assigned_to_id","space_id","summary","priority",...

and it becomes

tickets:fields, ["summary","description","id","number","reporter_id","assigned_to_id","space_id","priority",...

(We need all the columns because it seems that Assembla's import engine is not smart enough to deal with missing fields).

6. Edit our tickets data lines (text replace again) so that it looked like Assembla tickets data, i.e. add 'tickets, [' at the beginning, dummy values for the rest of the fields and the closing square bracket. Don't forget that we moved 'summary' and 'description' columns to go first!. So we have:

tickets, ["Home page: Update design","Set numbers after locations","40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","3","3","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]
tickets, ["Photos upload: hide step 2","Show Step 2 only if files are selected.","40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","3","3","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]

By the way, I prefer to clear the values of some columns like ‘id‘, ‘number‘ (ticket number) and so on — so that Assembla assigned it itself.

OK, it seems your file is ready to be fed to Assembla. I advise you to create an empty free space and try on it first if don’t want to mess up your current space.

If the import engine has some parsing problems with your file (missing quote for example) you will get a notice. If you get an Application Error, some columns or their values are missing — check your file.

One more tip — your tickets will be added as ‘No milestone‘. If you want your new tickets to be added into a new milestone, paste a milestone defenition in the beginning of your file and replace milestone ID in the tickets data by it’s ID:

milestones:fields, ["id","due_date","title","user_id","created_at","created_by","space_id","description","is_completed","completed_date","from_basecamp","basecamp_milestone_id","updated_at","updated_by"]
milestones, ["1111","2009-09-09","Big shot of development","cv2gFo","2009-08-25 13:12:09","cv2gFo","bktPVqwKmr3OWYeJe5aVNr","Work hard to finish all that was discussed.","0",null,"0",null,"2009-08-25 13:12:09","cv2gFo"]

You have a web application hosted on a server and you want to set file access permissions by chmod command, different for files and folders.

Why different? A folder must have ‘x’ (access) flag:

drwxr-xr-x

At the same time a file shouldn’t have it (otherwise it would be executable):

rw-r--r--

How can you set it up for all nested files/folders in your application folder?

You can solve this task by setting common persmissions for all files and folders (step 1) and then make folders accessable (step 2), recursively:

chmod -R 644 .
chmod -R a+X .

But chmod allows you to set up only access flag for folders, you cannot set one persmission for folders and another for files.

Here is a script written by my collegue Anton [vojtoshik] Voitovych to solve this task.

Copy it and save to a file (I saved it in /bin/rchmod — thus it’s accessable for all users of my system):

#!/bin/bash

files="0644";
directories="0755";

path=$1;
shift;

while getopts "d:f:" OPTION
do
case $OPTION in
f) files="$OPTARG" ;;
d) directories="$OPTARG" ;;
esac
done

if [ "$path" != '' ]; then
    find "$path" -type d ! -name "." -exec chmod "$directories" {} \;
    find "$path" -type f -exec chmod "$files" {} \;
else
    echo "Usage: rchmod <path_to_directory> [-d <directories mode>] [-f <files mode>]";
    echo -ne \\n;
fi

OK, now make it executable:

chmod 755 rchmod

How to use it.

1. You are in your application folder and want to set 755 (drwxr-xr-x) mode for folders and 644 (rw-r--r--) mode for files which is the default:

rchmod .

2. You want to set 123 permissions only for folders in a particular folder::

rchmod /some/path/to/folder -d 123

3. You want to set 123 permissions for folders and 456 permission for files in a particular folder::

rchmod /some/path/to/folder -d 123 -f 456

Note: don’t forget that every time after that command run you should make some folders writable (wp-content/uploads, cache, temp — what else you have in your webapp).

After authentication in my project it takes about 10-20 minutes for the auth session to expire, which is not handy — you go to get a snack and see a login screen coming back.

This is how to make the TTL of your auth session longer:

$s = new Zend_Session_Namespace(
    $this->_auth->getStorage()->getNamespace() );

$s->setExpirationSeconds( strtotime('30 day', 0) );

Now your users will log in for 30 days.

You can call this code in your login action after successful authentication and before the redirect to the landing page.

There are situations when you need to separate processing of big amount of data between several “agents”, e.g.:

• you have a long list of websites which must be checked for being alive (404 error check) by your web-clawlers;
• a queue of photos to be resized or videos to be converted;
• articles that your editors must review;
• catalogue of blog feeds that your system must import posts from;
• etc.

The idea to do this is simple:

1. Give a small piece of big work to an agent.
2. Mark this piece as given to him (so that none other starts to do the same job) and remember the time stamp when the job was given or when the job becomes obsolete (this agent is dead, let’s give this job to someone else).
3. If work is done — go to step #1.
4. After some period of time (1 hour) check all the time stamps, and if some agents didn’t cope with the job, mark the jobs as free so that others could start to work on it.

The problem is between steps #1 and #2 — while you gave a job to Agent 1 and going to mark it as given to him, what if Agent 2 is given by the same job? If you have many Agents, this  can happen at real. This situations is called concurrent read/write.

To overcome this a lock can be used.

In this article I wil explain, how to use locks in Zend project with MySQL database.

First of all, MySQL documentation tells that SELECT .. FOR UPDATE can be used for that purpose. First step is to select records by that statement, and second step is to mark them as locked. Requirements are to use InnoDB storage and to frame these two statements in a transaction.

Happily, Zend_Db_Table_Select has a special method forUpdate() that implements SELECT .. FOR UPDATE statement. Zend_Db can cope with transactions as well. Let’s try it!

To lock a record, we need two fields:

1. one to remember ID of agent that is processing this record (let’s call this column ‘locked_by‘)
2. one another to know the time when the lock becomes obsolete (let’s call this column ‘expires_at‘)

I wrote a  class that inherits from Zend_Db_Table and helps to get records with locking them.

<?php

class Koodix_Db_Table_Lockable extends Zend_Db_Table
{
    protected $_lockedByField = 'locked_by';
    protected $_expiresAtField = 'expires_at';
    protected $_TTL = '1 HOUR'; //time to live for lock

    public function fetchLocked( Zend_Db_Table_Select $select,
        $lockerID ) {

        $db = $this->getAdapter();
        $db->beginTransaction();

        $column = $db->quoteIdentifier( $this->_lockedByField );
        $select->forUpdate()
             ->where("$column=? OR $column IS NULL", $lockerID);

        $data = $this->fetchAll($select);
        if( empty($data) ) return null;

        $expiresAt = new Zend_Db_Expr('DATE_ADD( NOW(),
            INTERVAL ' . $this->_TTL . ')');
        if( sizeof($this->_primary) > 1 ) {
            foreach( $data as $item ) {
                $item->{$this->_lockedByField} = $lockerID;
                $item->{$this->_expiresAtField} = $expiresAt;

                $item->save();
            }
        }
        else {
            $arrIds = array();
            foreach( $data as $item ) {
                $arrIds[] = $item->id;
            }

            $this->update(
                array(
                    $this->_lockedByField => $lockerID,
                    $this->_expiresAtField => $expiresAt,
                ),
                $db->quoteIdentifier(current($this->_primary)) .
                    ' IN ("'.implode('","', $arrIds).'")'
            );
        }

        $db->commit();
        return $data;
    }

    public function releaseLocks( ) {

        $column = $db->quoteIdentifier( $this->_expiresAtField );

        return $this->update(
            array(
                $this->_lockedByField => null,
                $this->_expiresAtField => null,
            ),
            "$column <= NOW()"
        );
        ;
    }
}

If the table has a composite primary key (containing more than one column), the ActiveRecord approach is used, so the save() method for every record is called, that’s simple (drawback — multiple update queries). Otherwise, if it is a deep-seated table with one ID column as a primary key, then the IDs are collected in a list and all records are updated by a single statement with IN in where clause (which is much faster).

TTL (‘Time to Live‘) — period of time when lock is allowed. In my application the default is one hour. Format of TTL can be seen in MySQL documentation.

And now how to use it.

Let’s imagine you have several editors that divide the big articles list and review them. My model class has a method fetchForUser() that returns no more than 5 articles for current user (by given user ID).

This is an Article table model, inherited from the class above. Usually such classes are located at

application/default/models/ArticleTable.php

<?php
class ArticleTable extends Koodix_Db_Table_Lockable
{
    protected $_name = 'article';

    public function fetchForUser( $userId, $count=5 ) {

        $select = $this->select()
            ->where('reviewed = 0')
            ->order('expires_at DESC')
            ->order('date_imported DESC')
            ->limit( $count );

        return $this->fetchLocked($select, $userId);
    }
}

Note: if the editor refreses the page, the expres_at fields is refreshed by current time as well.

As for step four of our algorithm (releasing all obsolete locks) — create an action in your backend controller, call your table model releaseLocks() method in it and call that action periodically by Cron.

To boost the performance of the lock releasing, create an index on the expires_at column. (Because of this reason I rejected the ‘locked_since‘ column in favor of ‘expires_at‘)

P.S. In my database date/time columns have DATETIME type. If you use INT to store timestamps, convert it to unix time and back.