SQLite Create Function

AccessLog.php

<?php
namespace Acme;

class AccessLog {

    public $id;
    public $access_date;
    public $uri;
    public $uri_path;
    public $uri_query;
    public $remote_addr;
    public $user_agent;
    public $response_code;

    public static function getInstance()
    {
        return new self;
    }

    public function import($vars)
    {
        foreach (array_keys(get_class_vars(get_class($this))) as $key) {
            if (array_key_exists($key, $vars)) {
                $this->{$key} = $vars[$key];
            }
        }
        return $this;
    }

    public function toArray()
    {
        return get_object_vars($this);
    }
}

pdo-sqlite-create-function.php

<?php
namespace Acme;
class U {

    public static function H($data, $filter = null)
    {
        $var = (is_callable($filter))
            ? $filter($data)
            : $data;
        return htmlspecialchars($var, ENT_QUOTES, 'UTF-8');
    }

    public static function buildParams($keys, $arr)
    {
        $params = array();
        foreach ($keys as $key) {
            $params[sprintf(':%s', $key)] = (array_key_exists($key, $arr))
                ? $arr[$key]
                : null;
        }
        return $params;
    }

    public static function createAccessLog($env)
    {
        $props = array();
        if (isset($env['REQUEST_TIME'])) {
            $props['access_date'] = date('Y-m-d H:i:s', $env['REQUEST_TIME']);
        }
        if (isset($env['REQUEST_URI'])) {
            $props['uri'] = $env['REQUEST_URI'];
            $props['uri_path'] = mb_substr(parse_url($env['REQUEST_URI'], PHP_URL_PATH), 0, 255, 'UTF-8');
            $props['uri_query'] = parse_url($env['REQUEST_URI'], PHP_URL_QUERY);
        }
        if (isset($env['REMOTE_ADDR'])) {
            $props['remote_addr'] = mb_substr($env['REMOTE_ADDR'], 0, 255, 'UTF-8');
        }
        if (isset($env['HTTP_USER_AGENT'])) {
            $props['user_agent'] = $env['HTTP_USER_AGENT'];
        }
        return AccessLog::getInstance()->import($props);
    }
}

$db = new \PDO('sqlite::memory:');
$db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$query = <<< SQL
CREATE TABLE IF NOT EXISTS accesslogs
(
    id            INTEGER      NOT NULL PRIMARY KEY,
    access_date   DATETIME     NOT NULL,
    uri           TEXT         NOT NULL,
    uri_path      VARCHAR(255) NOT NULL,
    uri_query     TEXT,
    remote_addr   VARCHAR(255),
    user_agent    TEXT
);
SQL;
$db->exec($query);
$db->exec('CREATE INDEX idx_accesslogs_uri_path ON accesslogs(uri_path)');
$db->exec('DELETE FROM accesslogs;');

$sql = <<< SQL
INSERT INTO
    accesslogs
(
    access_date
   ,uri
   ,uri_path
   ,uri_query
   ,remote_addr
   ,user_agent
) VALUES (
    :access_date
   ,:uri
   ,:uri_path
   ,:uri_query
   ,:remote_addr
   ,:user_agent
);
SQL;
$statement = $db->prepare($sql);

$env = $_SERVER;
$access_list = array();
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => '/articles/1',
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => '/articles/1/',
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => '/articles/search?cat=1',
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => '/articles/search?cat[]=&cat[]=',
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => '/articles/search?cat[]=1&cat[]=2',
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => '/articles/search?tag=',
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => sprintf('/articles/search?tag=%s', rawurlencode('鈴木')),
)));
$access_list[] = U::createAccessLog(array_merge($env, array(
    'REQUEST_URI' => sprintf('/articles/search?tag=%s', rawurlencode('佐藤')),
)));

$keys = array('access_date', 'uri', 'uri_path', 'uri_query', 'remote_addr', 'user_agent');

$db->beginTransaction();
foreach ($access_list as $access) {
    $statement->execute(U::buildParams($keys, $access->toArray()));
}
$db->commit();

// ユーザー定義関数を登録していないのでエラー
try {
    $db->query(
        "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles';",
        \PDO::FETCH_ASSOC
    );
} catch (\PDOException $e) {
    // SQLSTATE[HY000]: General error: 1 no such function: REGEXP
    echo $e->getMessage();
}

// ユーザー定義関数 regexp を登録
$db->sqliteCreateFunction('regexp', function($pattern, $value) {
    mb_regex_encoding('UTF-8');
    return (false !== mb_ereg($pattern, $value)) ? 1 : 0;
});

// ユーザー定義関数 param_exists を登録
$db->sqliteCreateFunction('param_exists', function($value, $param, $match = null) {
    parse_str($value, $output);
    if (array_key_exists($param, $output) && isset($output[$param])) {
        if (is_string($output[$param]) && strlen($output[$param]) >= 1) {
            if (!isset($match) || strcmp($output[$param], $match) === 0) {
                return 1;
            }
        } elseif (is_array($output[$param])) {
            return (count(array_filter($output[$param], function($val) use ($match) {
                if ((isset($val) && strlen($val) >= 1)) {
                    if (!isset($match) || strcmp($val, $match) === 0) {
                        return true;
                    }
                }
                return false;
            })) >= 1);
        }
    }
    return 0;
});

$statement_list = array();

$statement_list = array();

// 全件
$statement_list['all'] = $db->query(
    'SELECT * FROM accesslogs;',
    \PDO::FETCH_ASSOC
);

// "/articles/{記事ID}" または "/articles/{記事ID}/" へのアクセスを抽出
$statement_list["uri_path REGEXP '^/articles/[[:digit:]]/?$'"] = $db->query(
    "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles/[[:digit:]]/?$';",
    \PDO::FETCH_ASSOC
);

// "/articles/search" へのアクセスを抽出
$statement_list["uri_path REGEXP '^/articles/search$'"] = $db->query(
    "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles/search$';",
    \PDO::FETCH_ASSOC
);

// "/articles/search" へのアクセスのうち、"tag"パラメータを含むものを抽出
$statement_list["uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'tag')"] = $db->query(
    "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'tag');",
    \PDO::FETCH_ASSOC
);

// "/articles/search" へのアクセスのうち、"cat"パラメータを含むものを抽出
$statement_list["uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'cat')"] = $db->query(
    "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'cat');",
    \PDO::FETCH_ASSOC
);

// "/articles/search" へのアクセスのうち、"tag"パラメータに"鈴木"を含むものを抽出
$statement_list["uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'tag', '鈴木')"] = $db->query(
    "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'tag', '鈴木');",
    \PDO::FETCH_ASSOC
);

// "/articles/search" へのアクセスのうち、"tag"パラメータに"佐藤"を含むものを抽出
$statement_list["uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'tag', '佐藤')"] = $db->query(
    "SELECT * FROM accesslogs WHERE uri_path REGEXP '^/articles/search$' AND param_exists(uri_query, 'tag', '佐藤');",
    \PDO::FETCH_ASSOC
);

$title = 'SQLite Create Function';

$source = highlight_string(file_get_contents(__FILE__), true);

?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title><?=U::H($title)?></title>
<style type="text/css">
body {font-family:monospace;}
.source {border:dotted 1px #999;padding:5px;}
table {border-collapse:collapse;margin:5px 0px;}
caption {text-align:left;font-weight:bold;}
th,td {padding:2px;border:solid 1px #999999;}
</style>
</head>
<body>

<h1><?=U::H($title)?></h1>

<?php foreach ($statement_list as $name => $statement) : ?>
<table width="100%">
    <caption><?=U::H($name)?></caption>
    <thead>
        <tr>
            <th>id</th>
            <th>uri</th>
            <th>path</th>
            <th>query</th>
            <th>query(decoded)</th>
        </tr>
    </thead>
    <tbody>
<?php foreach ($statement as $index => $item) : ?>
    <tr>
        <td width="5%"><?=U::H($item['id'])?></td>
        <td width="35%"><?=U::H($item['uri'])?></td>
        <td width="20%"><?=U::H($item['uri_path'])?></td>
        <td width="20%"><?=U::H($item['uri_query'])?></td>
        <td width="20%"><?=U::H($item['uri_query'], 'rawurldecode')?></td>
    </tr>
<?php endforeach ?>
    </tbody>
</table>
<?php endforeach ?>

<div class="source"><?=$source?></div>

</body>
</html>