Mở rộng các hàm query cho SQLite với PDO

Lệnh query của SQLite không được mạnh mẽ như của MySQL do thiếu rất nhiều hàm xử lý. Thư viện sau mở rộng các hàm điều kiện tìm kiếm cho SQLite tương tự như MySQL với PHP PDO.

Đoạn code sau tham khảo từ mã nguồn của CMS nổi tiếng Drupal. Các hàm được thêm mới bao gồm:

Trong đó chỉ cần sử dụng hàm sqlite_open với đầu vào là đường dẫn file SQLite, giá trị trả về là đối tượng PDO hỗ trợ những chức năng trên.

<?php
function sqlite_open($dbname) {
    // Create (connect to) SQLite database in file
    $pdo = new \PDO('sqlite:'.$dbname);

    $pdo->sqliteCreateFunction('if', 'sqlite_function_if');
    $pdo->sqliteCreateFunction('greatest', 'sqlite_function_greatest');
    $pdo->sqliteCreateFunction('pow', 'pow', 2);
    $pdo->sqliteCreateFunction('exp', 'exp', 1);
    $pdo->sqliteCreateFunction('length', 'strlen', 1);
    $pdo->sqliteCreateFunction('md5', 'md5', 1);
    $pdo->sqliteCreateFunction('concat', 'sqlite_function_concat');
    $pdo->sqliteCreateFunction('concat_ws', 'sqlite_function_concatws');
    $pdo->sqliteCreateFunction('substring', 'sqlite_function_substring', 3);
    $pdo->sqliteCreateFunction('substring_index', 'sqlite_function_substringindex', 3);
    $pdo->sqliteCreateFunction('rand', 'sqlite_function_rand');
    $pdo->sqliteCreateFunction('regexp', 'sqlite_function_regexp');
    // SQLite does not support the LIKE BINARY operator, so we overload the
    // non-standard GLOB operator for case-sensitive matching. Another option
    // would have been to override another non-standard operator, MATCH, but
    // that does not support the NOT keyword prefix.
    $pdo->sqliteCreateFunction('glob', 'sqlite_function_likebinary');

    // Set errormode to exceptions
    $pdo->setAttribute(PDO::ATTR_ERRMODE, 
            PDO::ERRMODE_EXCEPTION);

    return $pdo;
}

/**
 * SQLite compatibility implementation for the IF() SQL function.
 */
function sqlite_function_if($condition, $expr1, $expr2 = NULL) {
    return $condition ? $expr1 : $expr2;
}

/**
 * SQLite compatibility implementation for the GREATEST() SQL function.
 */
function sqlite_function_greatest() {
    $args = func_get_args();
    foreach ($args as $v) {
        if (!isset($v)) {
            unset($args);
        }
    }
    if (count($args)) {
        return max($args);
    } else {
        return NULL;
    }
}

/**
 * SQLite compatibility implementation for the CONCAT() SQL function.
 */
function sqlite_function_concat() {
    $args = func_get_args();
    return implode('', $args);
}

/**
 * SQLite compatibility implementation for the CONCAT_WS() SQL function.
 *
 * @see http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat-ws
 */
function sqlite_function_concatws() {
    $args = func_get_args();
    $separator = array_shift($args);
    // If the separator is NULL, the result is NULL.
    if ($separator === FALSE || is_null($separator)) {
        return NULL;
    }
    // Skip any NULL values after the separator argument.
    $args = array_filter($args, function ($value) {
        return !is_null($value);
    });
    return implode($separator, $args);
}

/**
 * SQLite compatibility implementation for the SUBSTRING() SQL function.
 */
function sqlite_function_substring($string, $from, $length) {
    return substr($string, $from - 1, $length);
}

/**
 * SQLite compatibility implementation for the SUBSTRING_INDEX() SQL function.
 */
function sqlite_function_substringindex($string, $delimiter, $count) {
    // If string is empty, simply return an empty string.
    if (empty($string)) {
        return '';
    }
    $end = 0;
    for ($i = 0; $i < $count; $i++) {
        $end = strpos($string, $delimiter, $end + 1);
        if ($end === FALSE) {
            $end = strlen($string);
        }
    }
    return substr($string, 0, $end);
}

/**
 * SQLite compatibility implementation for the RAND() SQL function.
 */
function sqlite_function_rand($seed = NULL) {
    if (isset($seed)) {
        mt_srand($seed);
    }
    return mt_rand() / mt_getrandmax();
}

/**
 * SQLite compatibility implementation for the REGEXP SQL operator.
 *
 * The REGEXP operator is natively known, but not implemented by default.
 *
 * @see http://www.sqlite.org/lang_expr.html#regexp
 */
function sqlite_function_regexp($pattern, $subject) {
    // preg_quote() cannot be used here, since $pattern may contain reserved
    // regular expression characters already (such as ^, $, etc). Therefore,
    // use a rare character as PCRE delimiter.
    $pattern = '#' . addcslashes($pattern, '#') . '#i';
    return preg_match($pattern, $subject);
}

/**
 * SQLite compatibility implementation for the LIKE BINARY SQL operator.
 *
 * SQLite supports case-sensitive LIKE operations through the
 * 'case_sensitive_like' PRAGMA statement, but only for ASCII characters, so
 * we have to provide our own implementation with UTF-8 support.
 *
 * @see https://sqlite.org/pragma.html#pragma_case_sensitive_like
 * @see https://sqlite.org/lang_expr.html#like
 */
function sqlite_function_likebinary($pattern, $subject) {
    // Replace the SQL LIKE wildcard meta-characters with the equivalent regular
    // expression meta-characters and escape the delimiter that will be used for
    // matching.
    $pattern = str_replace(array('%', '_'), array('.*?', '.'), preg_quote($pattern, '/'));
    return preg_match('/^' . $pattern . '$/', $subject);
}