till's blog

So frequently, I need to run small data migrations or transformations. Especially on the way to Doctrine, there's a lot to clean-up in a database which has been used and evolved over five years or so.

The other day, I wanted to run some transformations on the data located a history column in a pretty simple table — here's what it looks like:

mysql> SHOW FIELDS FROM data;
+-------------+------------------+------+-----+---------------------+----------------+
| Field       | Type             | Null | Key | Default             | Extra          |
+-------------+------------------+------+-----+---------------------+----------------+
| id          | int(11)          | NO   | PRI | NULL                | auto_increment | 
| first       | varchar(255)     | YES  | MUL | NULL                |                | 
| last        | varchar(255)     | YES  | MUL | NULL                |                | 
| email       | varchar(255)     | YES  | MUL | NULL                |                | 
| history     | varchar(255)     | YES  | MUL | NULL                |                | 
| rec_datemod | datetime         | YES  |     | NULL                |                | 
| rec_dateadd | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                | 
+-------------+------------------+------+-----+---------------------+----------------+
12 rows in set (0.02 sec)

Building a class to interface the table is simple:

<?php
class Data extends Zend_Db_Table_Abstract
{
    protected $_primary = 'id';
    protected $_name = 'data';
}

Now it should be easy to iterate across it, find data and save — wrong!

I dig Twitter's bootstrap library because as a developer, it helps me produce good looking forms without a lot of effort. Especially when administration interfaces are concerned, you I can only go so far — my first constraint is not being a great designer (to confirm, check out this blog) and two: I don't like working with ugly interfaces.

To cut to the chase...

Easybib_Form_Decorator

It's a decorator library for Zend_Form. Among the Twitter Bootstrap style, it also supports simple <div> and <table> styles — all courtesy of the awesome Michael Scholl. The code is MIT licensed: it should suit your budget just fine. ;-)

Installation

(Side-note: We got a PEAR channel.)

$ pear channel-discover easybib.github.com/pear
$ pear install easybib/Easybib_Form_Decorator-alpha

Usage

See the docs folder on github.

Fin

Questions, comments and feedback are always appreciated — pull requests are most welcome.

Here's how I literally wasted eight hours of my life. :-)

We signed up for Yahoo! Search Boss last week. The process itself was pretty straight:

1. Sign into your Yahoo! account at https://developer.apps.yahoo.com/
2. Click "New Project", fill out the form.
3. Then click on the project name, activate "Yahoo! Search Boss" by suppling some billing info.

Consumer key rejected?

The above process doesn't even take five minutes, but then I spent eight hours figuring out what oauth_problem=consumer_key_rejected means. Spent a couple hours googling, reading bug reports and even posted to the Yahoo! group associated with Search Boss.

To cut to the chase: When you create a new project, it's not sufficient to just activate "Yahoo! Search Boss" (and provide billing details and so on).

You have to select another (pointless) API along with it. Even if you don't use it. Apparently a consumer key and secret are only put into use when you selected one of their other offerings. For some reason, Yahoo! Search Boss doesn't work by itself.

So once I selected one of their other APIs (I went for Knowledge Plus), I copied my updated consumer secret, it magically worked.

Add to all of the above the constant link screw up in Yahoo!'s developer documentation. Since there's a v1 and a v2 of the API you're bound to find the wrong one for sure — the best way to find it: Google.

Small working example for request tokens?

Sure, so to get a request token, this is what you do with Zend_Oauth:

$consumer = new Zend_Oauth_Consumer($config);
$token    = $consumer->getRequestToken();

$config is an instance of Zend_Config. In addition to the oauth.ini later in the blog post, you'll also need requestTokenUrl to retrieve a request token.

The result ($token) is of Zend_Oauth_Token_Request.

Nice to know: In the process I discovered that in case an access token was needed, I had to make sure that $token contains oauth_verifier. This verifier is only supplied when you supply a callbackUrl (in your ini file). If you supply oob (out of bounds — no callback), the verifier has to be entered by the user manually later on.

Consumers and Tokens

And the best part of this discovery is, that the Search Boss API doesn't use any OAuth tokens at all.

I guess I must have overread that in the process and only figured it out when I started looking at the sample OAuth code, or rather stepped through it all to figure out what it is doing (and what I was doing wrong). (More on that later!)

But anyway, I'm not afraid to learn new things.

Two for the price of one!

So once I mastered the credentials, it lead me to another thing: OAuth implementations in PHP are pretty sucky, and PHP doesn't seem to be alone here either. :-) And even if the implementation is not so sucky, their documentation usually is and real life examples are rather sparse.

So for example, I honestly don't know why developers who build OAuth wrappers rather explain OAuth for the who knows how many time, instead of providing example code. Example code goes far, and for reading yada yada just link to oauth.net. :-)

In case of the Zend Framework (and all things Zend_Oauth), I used the components unit tests to figure out a direction though that didn't take me too far.

Da codez.

In the end, I wrapped OAuth.php into a unit test and wrote some code using Zend_Oauth_Http_Utility until the requests looked the same. Here's what I ended up with!

oauth.ini

[production]
requestMethod   = 'GET'
signatureMethod = 'HMAC-SHA1'
consumerKey     = 'YOUR CONSUMER KEY'
consumerSecret  = 'YOUR CONSUMER SECRET'
version         = '1.0'

For simplicity, I'm not showing [testing] etc..

Nice to know: In case you're wondering how I came up with all the names, have a look into Zend_Oauth_Config::setOptions(). I particulary enjoyed looking for that one via an undocumented Zend_Oauth_Client::__call(). If you were wondering what @method is for in phpdoc, this is exactly it. ;-)

Make it all work!

So first off, we read the configuration file and push it all into an array $oauthParams.

We also initialize another array called $query (which is later appended to the URL).

Last but not least, we define $url, which is the address of the Search Boss endpoint.

$oauthCfg = new Zend_Config('oauth.ini', 'production');

$oauthParams = array(
    'oauth_version'          => $oauthCfg->version,
    'oauth_nonce'            => sha1(time() + rand(0,10)),
    'oauth_timestamp'        => time(),
    'oauth_consumer_key'     => $oauthCfg->consumerKey,
    'oauth_signature_method' => $oauthCfg->signatureMethod,
);

$query = array(
    'q'      => 'search query for yahoo boss',
    'format' => 'json',
);

$url = 'http://yboss.yahooapis.com/ysearch/limitedweb';

Both arrays are combined into $params for signature creation:

$params = array_merge($oauthParams, $query)

$utility   = new Zend_Oauth_Http_Utility;
$signature = $utility->sign(
    $params,
    $oauthCfg->signatureMethod,
    $oauthCfg->consumerSecret,
    null,
    $oauthCfg->requestMethod,
);

Then the signature is added to $oauthParams and we'll convert the array into an Authorization header:

$oauthParams['oauth_signature'] = $signature;

$oauthHeader = '';
foreach ($oauthParams as $key => $value) {
    $oauthHeader .= rawurlencode($key) . '="' . rawurlencode($value) . '",';
}
$oauthHeader = 'Authorization: OAuth ' . substr($oauthHeader, 0, -1);

Last but not least, we do the request!

First we assemble the complete URL, then we assign all the variables to the $client object and finally do a request!

$url = $url . '?' . http_build_query($query);

$client   = new Zend_Http_Client;
$response = $client->setUri($url)
    ->setMethod($oauthCfg->requestMethod)
    ->setHeaders($oauthHeader)
    ->request();
var_dump($response->getBody()); // json here!

If all went well $response contains an instance of Zend_Http_Response.

I hope I didn't forget anything, but it should be relatively straight-forward to wrap this into a lightweight object oriented wrapper.

Other pointers:

• always evaluate the status code (hint: REST-API)
• use try/catch

Fin

That's all. Sure hope this saves someone else some time.

After running away screaming from Zend_XmlRpc we migrated of our internal webservices are RESTful nowadays — which implies that we make heavy use of HTTP status codes and so on.

On the PHP side of things we implemented almost all of those webservices using the Zend Framework where some parts are replaced by in-house replacements (mostly stripped-down and optimized versions equivalents of Zend_Foo) and a couple nifty PEAR packages.

RESTful — how does it work?

Building a RESTful API means to adhere to the HTTP standard. URLs are resources and the appropriate methods (DELETE, GET, POST, PUT) are used on them. Add status codes in the mix and ready you are.

To keep it simple for this blog post the following status codes are more or less relevant for a read-only (GET) API:

• 200: it's A-OK
• 400: a bad request, e.g. a parameter missing
• 401: unauthorized
• 404: nothing was found

... and this is just the beginning — check out a complete list of HTTP status codes.

Setup

To serve PHP almost all of our application servers are setup like the following:

1. nginx in the front
2. php (fpm) processes in the back

Nginx and PHP are glued together using fastcgi (and unix-domain sockets).

For an indepth example of our setup check out the nginx-app and php-fpm recipes (along with our launchpad repository).

Problem?

The other day, I noticed that for some reason whenever our API returned an error — e.g. a 404, for an empty result — it would display a standard nginx error page and not the actual response.

Solution

Digging around in /etc/nginx/fastcgi_params, I discovered the following:

fastcgi_intercept_errors on;

So what this does is that it intercepts any errors from the PHP backends and attempts to display an nginx error page. All errors may include the various PHP parse errors but apparently also a PHP generated page with a 404 status code.

So for example, the following code served by a PHP backend triggers the nginx page:

header("HTTP/1.1 404 Not Found);

The obvious fix seems simple:

fastcgi_intercept_errors off;

Sidenote: I think a similar issue might be in nginx's proxy_intercept_errors.

For both directives the manual suggests that they will intercept any status code higher than 400 — or 4xx and 5xx. But that's not all.

Tell me why?!

Reviewing the manual, I noticed that nginx will only act on fastcgi_intercept_errors on; when an error_page is (previously) defined. Checking out the rest of my sites configuration, the following part is to blame:

location / {
    error_page 404 /index.php;

    include /etc/nginx/fastcgi_params;

    fastcgi_pass  phpfpm;
    fastcgi_index index.php;

    fastcgi_param SCRIPT_FILENAME /var/www/current/www/index.php;

    index  index.php index.html index.htm;
}

So indeed the error_page 404 /index.php is what set it all off to begin with. And that's what I ended up removing, though it doesn't hurt to understand the implications of fastcgi_intercept_errors.

I think historically we used the 404 error handler as a cheap excuse for a rewrite rule since we only serve dynamically generated pages (and super-SEO-friendly URLs) to begin with. But that doesn't seem to be necessary — testing will be required.

Fin

The moral of the story is: nginx is really never to blame. ;-)

This is precisly what happens when you copy/paste configurations from the Internetz and don't review each and every single line to understand the full scope. In the end this was more or less a picnic on my part but I wanted to share it anyway because it was one of those WTF-moments for me.

I'm currently running a little database analysis to see how many of our users might be affected by Gawker's breach of security the other week. Since we use Zend Framework, Zend_Db is our weapon of choice.

Running the queries I noticed, that Zend_Db doesn't seem to support an IN-clause with a straight fetchAll().

The IN-clause is only supported when I wrap my statement with Zend_Db_Select, which is something I rarely do. Part of the reason is that I still don't feel comfortable writing my SQL in a DSL which doesn't really do anything besides wrapping a string into an object and IMHO it doesn't add to readability either.

And the other reason is that I don't plan to run this import against any other database than MySQL. Which is why I don't see the need for abstraction either.

Example

The following code fails:

// $db = Zend_Db::factory($config);
$sql    = "SELECT foo FROM bar WHERE foo IN(?)";
$params = array(1,2,3,4);
$db->fetchAll($sql, $params);

The warning

PHP Warning:  mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in
prepared statement in /opt/library/Zend/Db/Statement/Mysqli.php on line 204

The exception

Mysqli statement execute error : No data supplied for parameters in prepared statement

Solution

My solution is simple:

$sql    = "SELECT foo FROM bar WHERE foo IN (?)";
$params = array('foo', 'bar', 'foobar', "foo\bar", '\bar', "'foobar'");
$query  = str_replace('?', substr(str_repeat('?, ', count($params)), 0, -2), $sql);
$db->fetchAll($query, $params);

I'm expanding a single ? to as many ?'s as necessary. The reason why I did this, is that I wanted to be able to use Zend_Db's quoting. The data I'm dealing with is really messy and I didn't feel like repairing it.

Of course there may be other and maybe more simple ways to do it.

Fin

That's all, happy hacking.

(Update, 2010/12/19 10:05 PM: Refined my example. Apparently people don't read the text (= blog post) around the code. :-))