PHP

a dumbed down version of wpdb for sqlite

I’ve been working, gradually, on a project using an sqlite3 database (for its convenience) and found myself missing the clean elegance of wpdb… so I implemented it. It was actually really easy to do, and I figured I would throw it up here for anyone else wishing to use it. The functionality that I build this around is obtainable here: http://php-sqlite3.sourceforge.net/pmwiki/pmwiki.php (don’t freak… its in apt…)

With this I can focus on the sql, which is different enough, and not fumble over function names and such… $db = new sqlite_wpdb($dbfile, 3); var_dump($db->get_results(“SELECT * FROM `mytable` LIMIT 5”));

the code is below… and hopefully not too mangled…

< ?php

class sqlite_wpdb {

        var $version = null;
        var $db = null;
        var $result = null;
        var $error = null;

        function sqwpdb($file, $version=3) { 
                return $this->__construct($file, $version); 
        }

        function __construct($file, $version=3) {
                $function = "sqlite{$version}_open";
                if ( !function_exists($function) )
                        return false;
                if ( !file_exists($file) )
                        return false;
                if ( !$this->db = @$function($file) )
                        return false;
                $this->version = $version;
                $this->fquery = "sqlite{$this->version}_query";
                $this->ferror = "sqlite{$this->version}_error";
                $this->farray = "sqlite{$this->version}_fetch_array";
                return $this;
        }

        function escape($string) {
                return str_replace("'", "''", $string);
        }

        function query($query) {
                if ( $this->result = call_user_func($this->fquery, $this->db, $query) )
                        return $this->result;
                $this->error = call_user_func($this->ferror, $this->db);
                return false;
        }

        function array_to_object($array) {
                if ( ! is_array($array) )
                        return $array;

                $object = new stdClass();
                foreach ( $array as $idx => $val ) {
                        $object->$idx = $val;
                }
                return $object;
        }

        function get_results($query) {
                if ( !$this->query($query) )
                        return false;
                $rval = array();
                while ( $row = $this->array_to_object(call_user_func($this->farray, $this->result)) ) {
                        $rval[] = $row;
                }
                return $rval;
        }

        function get_row($query) {
                if ( ! $results = $this->get_results($query) )
                        return false;
                return array_shift($results);
        }

        function get_var($query) {
                return $this->get_val($query);
        }

        function get_val($query) {
                if ( !$row = $this->get_row($query) )
                        return false;
                $row = get_object_vars($row);
                if ( !count($row) )
                        return false;
                return array_shift($row);
        }

        function get_col($query) {
                if ( !$results = $this->get_results($query) )
                        return false;
                $column = array();
                foreach ( $results as $row ) {
                        $row = get_object_vars($row);
                        if ( !count($row) )
                                continue;
                        $column[] = array_shift($row);
                }
                return $column;
        }

}

?>

Writing your own shell in php

I’ve always wanted to write my own simple shell in php.  Call me a glutin for punishment, but it seems like something that a lot of people could use to be able to do… If your web app had a command line interface for various things… like looking up stats, or users, or suspending naughty accounts, or whatever…. wouldnt that be cool and useful?  Talk about geek porn.  Anyways this this morning I got around to tinkering with the idea, and here is what i came up with… It’s rough, and empty, but its REALLY easy to extend and plug into any php application.

apokalyptik:~/phpshell$ ./shell.php

/home/apokalyptik/phpshell > hello

hi there

/home/apokalyptik/phpshell > hello world

hi there world

/home/apokalyptik/phpshell > cd ..

/home/apokalyptik/ > cd phpshell

/home/apokalyptik/phpshell > ls

shell.php

/home/apokalyptik//phpshell > exit

apokalyptik:~/phpshell$ ./shell.php

See the source here: shell.phps

Bash Coding Convention ../

We use dirname() a lot in php to make relative paths work from multiple locations like so. The advantages are many:

require dirname( dirname( __FILE__ ) ) . '/required-file.php';
$data = file_get_contents( dirname(__FILE__).'/data/info.dat');

But in bash we often dont do the same thing, we settle for the old standby “../”. Which is a shame because unless your directory structure is set up exactly right, and you have proper permissions, and you run the command from the right spot, it doesnt work as planned. I think part of the reason is that its not obvious how to reliably get a full path to the script from inside itself. Another reason is that ../ is shorter to type and easier to remember. Finally there’s always one time scripts for which this methodology is overkill. But if you’re planning to write a script which other people will (or might) be using, I think it’s good practice to do it right. Googling for things you’d think to search for on this subject does not yeild very informative results, or incomplete (incorrect) methods… so… here’s how to do the above php in bash:

source $(dirname $(dirname $(readlink -f $0)))/required-file.sh 
data=$(cat $(dirname $(readlink -f $0))/data/info.dat)

Hope this helps someone 🙂

As a side note, the OSX readlink binary functions differently. You’ll want to use a package manager to install gnu coreutils, and iether use greadlink, or link greadlink to a higher precedence location on your $PATH (I have /opt/local/bin:/opt/local/sbin: at the beginning of my $PATH)

Logging post data

Lets say you have a relatively complex php web application, like wordpress. You have it running under apache (which is common.) You have good control of your site via .htaccess (which is common — permalinks and all.) And something happens to your blog (e.g. someone is exploiting some unknown vulnerability to compromise your content), which you want to track down. So you want to log, for instance, HTTP POST data. Your first instinct might be to add some logging code to index.php (mine was) But there are a lot of possible places which might be directly accessed, especially in the admin. So The trick I use (and this is probably the only time I’ve ever condoned this) is to use PHPs auto_prepend_file functionality.

Make a /home/user/postlog/ directory, then a /home/user/postlog/logs/ directory (and chmod a+rw that one.) Next make a simple /home/user/postlog/postlog.php file with the following contents:

<?php 
if ( isset($_POST) && is_array($_POST) && count($_POST) > 0 ) { 
  $log_dir = dirname( __FILE__ ) . '/logs/'; 
  $log_name = "posts-" . $_SERVER['REMOTE_ADDR'] . "-" . date("Y-m-d-H") . ".log"; 
  $log_entry = gmdate('r') . "\t" . $_SERVER['REQUEST_URI'] . "
" . serialize($_POST) . "

"; 
  $fp=fopen( $log_dir . $log_name, 'a' ); 
  fputs($fp, $log_entry); 
  fclose($fp); } 
?>

Finally add this line to the top of your .htaccess file:

php_value auto_prepend_file /home/user/postlog/postlog.php

If all went well this should start logging any request to any php file with any post data into the /home/user/postlog/logs/ direcory (with a unique log per ip per day)

colorizing php cli scripts

It’s pretty common in most scripting languages which center around the command line (bash, perl, etc) to find information on colorizing your shell script output, mainly because those languages are tied very tightly to command line use. It can be difficult, however, to find information about adding this same nice feature to a php cli script. The reason is simple: most people dont use php for cli applications; most cli programmers use something else. It’s not difficult to adapt the same techniques listed in most bash howtos (generally in the section reserved for colorizing your command prompt) for generating colored terminal output for php.

echo "\033[31mred\033[37m
";

echo "\033[32mgreen\033[37m
";

echo "\033[41;30mblack on red\033[40;37m
";

Simple, functional, useful (even if a bit complicated.) I leave it to you to lookup a bash prompt colorization howto to hunt down your own list of escape color codes (call it homework.)

Cheers

so-you-wanna-see-an-image

We’ve been asked how we manage serving files from Amazons very cool S3 service at WordPress.com… This is how. (covering a requested image already stored on S3, not the upload -> s3 process)

A request comes into pound for a file. Pound hashes the hostname (via a custom patch which we have not, but may, release) , to determine which of several backend servers the request should hit. Pound forwards the request to that server. This, of course, means that a given blog always serves from the same backend server. The only exception to the afore-mentioned rule is if that server is, for some reason, unavailable in which case it picks another server to serve that hostname from temporarily.

The request then comes into varnishd on the backend servers. The varnishd daemon checks its 300Gb worth of files cache and (for the sake of this example) finds nothing (hey, new images are uploaded all the time!) Varnishd then checks with the web server (running on the same machine, just bound to a different IP/Port#) and that request is handled by a custom script.

So, a http daemon on the same backend server runs the file request. The custom script checks the DB to gather information on the file (specifically which DC’s it is in, size, mod time, and whether its deleted or not) all this info is saved in memcached for 5 minutes. The script increments and checks the “hawtness” (term courtesy of Barry) of the file in memcached (if the file has been accessed over a certain # of times it is then deemed “hawt”, and a special header is sent with the response telling varnishd to put the file into its cache. When that happens the request would be served directly by varnishd in the previous paragraph and never hit the httpd or this script again (or at least not until the cache entry expires.)) At this point, assuming the file should exist (deleted = 0 in the files db) we fetch the file from a backend source.

Which backend source depends on where it is available. The order of preference is as follows: Always fetch from Amazon S3 if the file lives there (no matter what, the following preferences only ever occur if, for some reason, s3 = 0 in the files db), and if that fails fetch from the one files server we still have (which has larger slower disks, and is used for archiving purposes and fault tolerance only)

After fetching the file from the back end… the custom script hands the data and programatically generated headers to the http daemon, which hands the data to varnishd, varnishd hands the data to pound, pound hands the data to the requesting client, and the image appears in the web browser.

And there was much rejoicing (yay.)

For the visual people among us who like visuals and stuff… (I like visuals…) here goes…

PHP CLI Status Indicator

Most times when people write command line scripts they just let the output flow down the screen as a status indicator, or just figure “it’s done when it’s done” But sometimes it would be nice to have a simple clean status indicator, allowing you to monitor progress and gauge time-to-completion. This is actually very easy to accomplish. Simply use
instead of
in your output. Obviously the example below is very simplified, and this can be applied in a much more sophisticated fashion. But it works.

$row_count = get_total_rows_for_processing();
$limit=10000;
echo "\r\n[  0%]";
for ( $i=0; $i < = $row_count; $i = $i + $limit ) {
  $query="SELECT * FROM table LIMIT {$limit} OFFSET {$i}";
  // do whatever
  $pct = round((($i+$offset)/$row_count)*100);
  if ( $pct < 10 ) {
    echo "\r[  $pct%]";
  } else {
    if ( $pct < 100 ) {
      echo "\r[ $pct%]";
    } else {
      echo "\r[$pct%]";
    }
  }
}
echo "\r[100%]\r\n";