PHP PDO::sqliteCreateFunction方法代码示例

function _init()
{
    global $CRUD;
    $CRUD['TITLE'] = "CRUD";
    $CRUD['SELF'] = $_SERVER["SCRIPT_NAME"];
    // loose "index.php" if nec (regexes are fugly in php. Feh.)
    $CRUD["SELF"] = preg_replace('/([\\/\\\\])index\\.php$/i', '$1', $CRUD["SELF"]);
    foreach (array('BUTTONS', 'HIDDENS', 'MESSAGES', 'ERRORS', 'CONTENT', 'PRECONTENT', 'POSTCONTENT', 'Atitle', 'Aartist', 'Alabel', 'Areleased_day', 'Areleased_month', 'Areleased_year', 'Ttrack_number', 'Ttitle', 'Tduration') as $v) {
        $CRUD[$v] = '';
    }
    switch (DBENGINE) {
        case 'sqlite3':
            try {
                $dbh = new PDO('sqlite:' . DBFILE, 'unused', 'unused');
                $dbh->sqliteCreateFunction('SEC_TO_TIME', 'sec_to_time', 1);
                // custom functions ...
                $dbh->sqliteCreateFunction('TIME_TO_SEC', 'time_to_sec', 1);
                $dbh->sqliteCreateAggregate('SUM_SEC_TO_TIME', 'sum_sec_to_time_step', 'sum_sec_to_time_finalize', 1);
            } catch (PDOException $e) {
                error($e->getMessage());
            }
            break;
        case 'mysql':
            // connect to the database (persistent)
            try {
                $dbh = new PDO('mysql:host=localhost;dbname=' . MYSQLDB, MYSQLUSER, MYSQLPASS, array(PDO::ATTR_PERSISTENT => true));
            } catch (PDOException $e) {
                error($e->getMessage());
            }
            break;
        default:
            error('unsupported DBENGINE specified: ' . DBENGINE);
    }
    $CRUD['dbh'] = $dbh;
}

 /**
  * {@inheritdoc}
  */
 public static function open(array &$connection_options = array())
 {
     // Allow PDO options to be overridden.
     $connection_options += array('pdo' => array());
     $connection_options['pdo'] += array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_STRINGIFY_FETCHES => TRUE);
     $pdo = new \PDO('sqlite:' . $connection_options['database'], '', '', $connection_options['pdo']);
     // Create functions needed by SQLite.
     $pdo->sqliteCreateFunction('if', array(__CLASS__, 'sqlFunctionIf'));
     $pdo->sqliteCreateFunction('greatest', array(__CLASS__, 'sqlFunctionGreatest'));
     $pdo->sqliteCreateFunction('pow', 'pow', 2);
     $pdo->sqliteCreateFunction('exp', 'exp', 1);
     $pdo->sqliteCreateFunction('length', 'strlen', 1);
     $pdo->sqliteCreateFunction('md5', 'md5', 1);
     $pdo->sqliteCreateFunction('concat', array(__CLASS__, 'sqlFunctionConcat'));
     $pdo->sqliteCreateFunction('concat_ws', array(__CLASS__, 'sqlFunctionConcatWs'));
     $pdo->sqliteCreateFunction('substring', array(__CLASS__, 'sqlFunctionSubstring'), 3);
     $pdo->sqliteCreateFunction('substring_index', array(__CLASS__, 'sqlFunctionSubstringIndex'), 3);
     $pdo->sqliteCreateFunction('rand', array(__CLASS__, 'sqlFunctionRand'));
     $pdo->sqliteCreateFunction('regexp', array(__CLASS__, 'sqlFunctionRegexp'));
     // 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', array(__CLASS__, 'sqlFunctionLikeBinary'));
     // Create a user-space case-insensitive collation with UTF-8 support.
     $pdo->sqliteCreateCollation('NOCASE_UTF8', array('Drupal\\Component\\Utility\\Unicode', 'strcasecmp'));
     // Execute sqlite init_commands.
     if (isset($connection_options['init_commands'])) {
         $pdo->exec(implode('; ', $connection_options['init_commands']));
     }
     return $pdo;
 }

 /**
  * {@inheritdoc}
  */
 public static function open(array &$connection_options = array())
 {
     // Allow PDO options to be overridden.
     $connection_options += array('pdo' => array());
     $connection_options['pdo'] += array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_STRINGIFY_FETCHES => TRUE);
     $pdo = new \PDO('sqlite:' . $connection_options['database'], '', '', $connection_options['pdo']);
     // Create functions needed by SQLite.
     $pdo->sqliteCreateFunction('if', array(__CLASS__, 'sqlFunctionIf'));
     $pdo->sqliteCreateFunction('greatest', array(__CLASS__, 'sqlFunctionGreatest'));
     $pdo->sqliteCreateFunction('pow', 'pow', 2);
     $pdo->sqliteCreateFunction('exp', 'exp', 1);
     $pdo->sqliteCreateFunction('length', 'strlen', 1);
     $pdo->sqliteCreateFunction('md5', 'md5', 1);
     $pdo->sqliteCreateFunction('concat', array(__CLASS__, 'sqlFunctionConcat'));
     $pdo->sqliteCreateFunction('concat_ws', array(__CLASS__, 'sqlFunctionConcatWs'));
     $pdo->sqliteCreateFunction('substring', array(__CLASS__, 'sqlFunctionSubstring'), 3);
     $pdo->sqliteCreateFunction('substring_index', array(__CLASS__, 'sqlFunctionSubstringIndex'), 3);
     $pdo->sqliteCreateFunction('rand', array(__CLASS__, 'sqlFunctionRand'));
     $pdo->sqliteCreateFunction('regexp', array(__CLASS__, 'sqlFunctionRegexp'));
     // Create a user-space case-insensitive collation with UTF-8 support.
     $pdo->sqliteCreateCollation('NOCASE_UTF8', array('Drupal\\Component\\Utility\\Unicode', 'strcasecmp'));
     // Execute sqlite init_commands.
     if (isset($connection_options['init_commands'])) {
         $pdo->exec(implode('; ', $connection_options['init_commands']));
     }
     return $pdo;
 }

 /**
  * Sets up the PDO SQLite table and initialises the PDO connection
  *
  * @param  array  $config  configuration
  *
  * @throws  Cache_Exception
  *
  * @uses  Arr::get
  */
 protected function __construct(array $config)
 {
     parent::__construct($config);
     $database = Arr::get($this->_config, 'database', NULL);
     if ($database === NULL) {
         throw new Cache_Exception('Database path not available in Gleez Cache configuration');
     }
     // Load new Sqlite DB
     $this->_db = new PDO('sqlite:' . $database);
     // Test for existing DB
     $result = $this->_db->query("SELECT * FROM sqlite_master WHERE name = 'caches' AND type = 'table'")->fetchAll();
     // If there is no table, create a new one
     if (0 == count($result)) {
         $database_schema = Arr::get($this->_config, 'schema', NULL);
         if ($database_schema === NULL) {
             throw new Cache_Exception('Database schema not found in Gleez Cache configuration');
         }
         try {
             // Create the caches table
             $this->_db->query(Arr::get($this->_config, 'schema', NULL));
             $this->_db->query(Arr::get($this->_config, 'index', NULL));
         } catch (PDOException $e) {
             throw new Cache_Exception('Failed to create new SQLite caches table with the following error : :error', array(':error' => $e->getMessage()));
         }
     }
     // Registers a User Defined Function for use in SQL statements
     $this->_db->sqliteCreateFunction('regexp', array($this, 'removePatternRegexpCallback'), 2);
 }

    /**
     * Connect to local sqlite db
     *
     * @param string $locking_mode        	
     * @return \PDO
     */
    private function cache_local_connect($locking_mode = 'NORMAL')
    {
        try {
            $table = <<<EOD
CREATE TABLE "cachedb" (
"itemid"  INTEGER PRIMARY KEY AUTOINCREMENT,
"title"  TEXT,
"link"  TEXT,
"stemmed"  TEXT,
"metaphone"  TEXT,
"ts"  DATETIME DEFAULT (datetime( 'now', 'utc'))
);
EOD;
            $cachelocal = new \PDO('sqlite::memory:');
            $cachelocal->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
            $cachelocal->setAttribute(\PDO::ATTR_TIMEOUT, 20);
            $cachelocal->exec("PRAGMA locking_mode = {$locking_mode}");
            $cachelocal->exec($table);
            // Create custom function for similar titles
            $cachelocal->sqliteCreateFunction('similarity', array('Rss\\Util\\Cache\\FeedCache\\FeedCache', 'SIMILARITY'), 2);
            // Create custom function for all terms in a title included in a published one.
            $cachelocal->sqliteCreateFunction('allterms', array('Rss\\Util\\Cache\\FeedCache\\FeedCache', 'ALLTERMS'), 2);
            return $cachelocal;
        } catch (\Exception $e) {
            $m = 'Error connecting to sqlite ' . $e->getCode() . ' ' . $e->getMessage();
            $this->fail($m);
        }
    }

function _init()
{
    global $CRUD;
    $CRUD['TITLE'] = "CRUD APP";
    $CRUD['SELF'] = $_SERVER["SCRIPT_NAME"];
    // loose "index.php" if nec (regexes are fugly in php. Feh.)
    $CRUD["SELF"] = preg_replace('/([\\/\\\\])index\\.php$/i', '$1', $CRUD["SELF"]);
    foreach (array('DBVERSION', 'BUTTONS', 'HIDDENS', 'MESSAGES', 'ERRORS', 'CONTENT', 'PRECONTENT', 'POSTCONTENT', 'Atitle', 'Aartist', 'Alabel', 'Areleased_day', 'Areleased_month', 'Areleased_year', 'Ttrack_number', 'Ttitle', 'Tduration') as $v) {
        $CRUD[$v] = '';
    }
    switch (DBENGINE) {
        case 'sqlite3':
            try {
                $dbh = new PDO('sqlite:' . DBFILE, 'unused', 'unused');
                $dbh->sqliteCreateFunction('SEC_TO_TIME', 'sec_to_time', 1);
                // custom functions ...
                $dbh->sqliteCreateFunction('TIME_TO_SEC', 'time_to_sec', 1);
                $dbh->sqliteCreateAggregate('SUM_SEC_TO_TIME', 'sum_sec_to_time_step', 'sum_sec_to_time_finalize', 1);
                $CRUD['DBVERSION'] = SQLite3::version();
                $CRUD['DBVERSION'] = 'SQLite version ' . $CRUD['DBVERSION']['versionString'];
            } catch (PDOException $e) {
                error($e->getMessage());
            }
            break;
        case 'mysql':
            // connect to the database (persistent)
            try {
                $dbh = new PDO('mysql:host=localhost;dbname=' . MYSQLDB, MYSQLUSER, MYSQLPASS, array(PDO::ATTR_PERSISTENT => true));
                $sth = $dbh->query("SHOW VARIABLES WHERE variable_name = 'version'");
                $CRUD['DBVERSION'] = 'MySQL server version ' . $sth->fetchColumn(1);
                if ($dbh) {
                    set_mysql_album_len_function($dbh);
                }
            } catch (PDOException $e) {
                error($e->getMessage());
            }
            break;
        case 'pgsql':
            // connect to the database (persistent)
            try {
                $dbh = new PDO('pgsql:host=localhost;port=5432;dbname=' . PGSQLDB, PGSQLUSER, PGSQLPASS, array(PDO::ATTR_PERSISTENT => true));
                $dbh->exec("set client_encoding to 'latin1'");
                $sth = $dbh->query('SELECT VERSION()');
                $CRUD['DBVERSION'] = explode(' ', $sth->fetchColumn());
                $CRUD['DBVERSION'] = 'PostgreSQL server version ' . $CRUD['DBVERSION'][1];
            } catch (PDOException $e) {
                error($e->getMessage());
            }
            break;
        default:
            error('unsupported DBENGINE specified: ' . DBENGINE);
    }
    $CRUD['dbh'] = $dbh;
}

 /**
  * getOdb: Creates PDO object
  */
 protected function getOdb($engine, $file)
 {
     switch ($engine) {
         case "sqlite":
             if ("/" !== $file[0]) {
                 $file = realpath(".") . "/{$file}";
             }
             $odb = new PDO("sqlite:{$file}");
             $odb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
             $odb->sqliteCreateFunction("regexp", "regexp", 2);
             $odb->sqliteCreateFunction("concat_ws", "concat_ws");
             break;
     }
     return $odb;
 }

 protected function getDb($o)
 {
     static $db;
     if (!isset($db)) {
         try {
             $db = new PDO('sqlite:' . $this->realpath);
         } catch (Exception $e) {
             $o->error_msg = $e->getMessage();
             return;
         }
         $db->sqliteCreateFunction('php', array(__CLASS__, 'php'));
         $db->sqliteCreateFunction('preg_match', 'preg_match', 2);
     }
     return $db;
 }

 public function isSuccessivePost($lcount, $com, $timestamp, $pass, $passcookie, $host, $isupload)
 {
     $FileIO = PMCLibrary::getFileIOInstance();
     if (!$this->prepared) {
         $this->dbPrepare();
     }
     if (!$this->ENV['PERIOD.POST']) {
         return false;
     }
     // 關閉連續投稿檢查
     $timestamp = intval($timestamp);
     $tmpSQL = 'SELECT pwd,host FROM ' . $this->tablename . ' WHERE time > ' . ($timestamp - (int) $this->ENV['PERIOD.POST']);
     // 一般投稿時間檢查
     if ($isupload) {
         $tmpSQL .= ' OR time > ' . ($timestamp - (int) $this->ENV['PERIOD.IMAGEPOST']);
     } else {
         $tmpSQL .= " OR md5(com) = '" . md5($com) . "'";
     }
     // 內文一樣的檢查 (與上者兩者擇一)
     $this->con->sqliteCreateFunction('md5', 'md5', 1);
     // Register MD5 function
     $result = $this->con->query($tmpSQL) or $this->_error_handler('Get the post to check the succession failed', __LINE__);
     while (list($lpwd, $lhost) = $result->fetch(PDO::FETCH_NUM)) {
         // 判斷為同一人發文且符合連續投稿條件
         if ($host == $lhost || $pass == $lpwd || $passcookie == $lpwd) {
             return true;
         }
     }
     return false;
 }

 /**
  * Checks for dependencies and initializes the database.
  *
  * @return void
  */
 public function onLoad()
 {
     if (!extension_loaded('PDO') || !extension_loaded('pdo_sqlite')) {
         $this->fail('PDO and pdo_sqlite extensions must be installed');
     }
     $this->plugins->getPlugin('Command');
     $dir = dirname(__FILE__) . '/' . $this->getName();
     $path = $dir . '/lart.db';
     $exists = file_exists($path);
     if (!$exists) {
         mkdir($dir);
     }
     try {
         $this->db = new PDO('sqlite:' . $path);
     } catch (PDO_Exception $e) {
         throw new Phergie_Plugin_Exception($e->getMessage());
     }
     $this->db->sqliteCreateFunction('preg_match', 'preg_match');
     if (!$exists) {
         $this->db->exec('
             CREATE TABLE lart (
                 name VARCHAR(255),
                 definition TEXT,
                 hostmask VARCHAR(50),
                 tstamp VARCHAR(19)
             )
         ');
         $this->db->exec('
             CREATE UNIQUE INDEX lart_name ON lart (name)
         ');
     }
     $this->save = $this->db->prepare('
         REPLACE INTO lart (name, definition, hostmask, tstamp)
         VALUES (:name, :definition, :hostmask, :tstamp)
     ');
     $this->process = $this->db->prepare('
         SELECT *
         FROM lart
         WHERE preg_match(name, :name)
     ');
     $this->select = $this->db->prepare('
         SELECT *
         FROM lart
         WHERE name = :name
     ');
     $this->delete = $this->db->prepare('
         DELETE FROM lart
         WHERE name = :name
     ');
 }

 /**
  * Checks for dependencies and initializes the database.
  *
  * @return void
  */
 public function onLoad()
 {
     if (!extension_loaded('PDO') || !extension_loaded('pdo_sqlite')) {
         $this->fail('PDO and pdo_sqlite extensions must be installed');
     }
     $this->plugins->getPlugin('Command');
     $defaultDbLocation = dirname(__FILE__) . '/' . $this->getName() . '/lart.db';
     $fileName = $this->getConfig('lart.sqlite_db', $defaultDbLocation);
     $dirName = dirname($fileName);
     $exists = file_exists($fileName);
     if (!file_exists($dirName)) {
         mkdir($dirName);
     }
     if (file_exists($fileName) && !is_writable($fileName) || !file_exists($fileName) && !is_writable($dirName)) {
         throw new Phergie_Plugin_Exception('SQLite file exists and cannot be written or does not exist ' . ' and cannot be created: ' . $fileName);
     }
     try {
         $this->db = new PDO('sqlite:' . $fileName);
     } catch (PDO_Exception $e) {
         throw new Phergie_Plugin_Exception($e->getMessage());
     }
     $this->db->sqliteCreateFunction('preg_match', 'preg_match');
     if (!$exists) {
         $this->db->exec('CREATE TABLE lart (
                 name VARCHAR(255),
                 definition TEXT,
                 hostmask VARCHAR(50),
                 tstamp VARCHAR(19)
             )');
         $this->db->exec('CREATE UNIQUE INDEX lart_name ON lart (name)');
     }
     $this->save = $this->db->prepare('REPLACE INTO lart (name, definition, hostmask, tstamp)
         VALUES (:name, :definition, :hostmask, :tstamp)');
     $this->process = $this->db->prepare('SELECT *
         FROM lart
         WHERE preg_match(:name, name)');
     $this->select = $this->db->prepare('SELECT *
         FROM lart
         WHERE name = :name');
     $this->delete = $this->db->prepare('DELETE FROM lart
         WHERE name = :name');
 }

function db_connect($host, $user, $pass, $dbname = '')
{
    global $mdb_res, $pdo_error;
    try {
        $mdb_res = new PDO("sqlite:{$dbname}");
        // PDO::sqliteCreateFunction() does not works on windows PHP 5.0.5
        // and pdo_sqlite build of 2005-11-08 15:11:44 (http://pecl4win.php.net/ext.php/php_pdo_sqlite.dll)
        $mdb_res->sqliteCreateFunction('now', 'mdb_sqlite_now', 0);
        $mdb_res->sqliteCreateFunction('md5', 'md5', 1);
        $mdb_res->sqliteCreateFunction('unix_timestamp', 'strtotime', 1);
        $mdb_res->sqliteCreateFunction('from_unixtime', 'mdb_sqlite_from_unixtime', 1);
        $mdb_res->sqliteCreateFunction('password', 'md5', 1);
        $mdb_res->sqliteCreateFunction('old_password', 'md5', 1);
        return $mdb_res;
    } catch (PDOException $e) {
        $pdo_error = $e;
        echo 'Connection failed: ' . $e->getMessage();
        return false;
    }
}

function database_connect($database_path, $database_name)
{
    global $dbHandle;
    /////////////connect to database//////////////////////
    try {
        $dbHandle = new PDO('sqlite:' . $database_path . DIRECTORY_SEPARATOR . $database_name . '.sq3');
    } catch (PDOException $e) {
        print "Error: " . $e->getMessage() . "<br/>";
        print "PHP extensions PDO and PDO_SQLite must be installed.";
        die;
    }
    //SWITCH TO WAL MODE IF SQLITE >3.7.0, DELETE MODE >3.6.0 <3.7.1
    $result = $dbHandle->query('SELECT sqlite_version()');
    $sqlite_version = $result->fetchColumn();
    $result = null;
    if (version_compare($sqlite_version, "3.5.9", ">")) {
        $journal_mode = 'DELETE';
        // There are read-only databases!
        if (version_compare($sqlite_version, "3.7.0", ">") && $database_name != 'journals' && $database_name != 'styles') {
            $journal_mode = 'WAL';
        }
        $dbHandle->query('PRAGMA journal_mode=' . $journal_mode);
    }
    $dbHandle->exec("PRAGMA cache_size = 1000000");
    $dbHandle->sqliteCreateFunction('search_strip_tags', 'sqlite_strip_tags', 1);
    return $dbHandle;
}

    $cols = $data['cols'];
    $i_hits = 1;
    $i_count = 3 * $cols * $phrases + $i_hits;
    //
    for ($p = 0; $p < $phrases; $p++) {
        for ($c = 0; $c < $cols; $c++) {
            $hits = $data[3 * ($c + $p * $cols) + $i_hits];
            $tokens = $data[$c + $i_count];
            if ($tokens > 0) {
                $rank += $hits / $tokens;
            }
        }
    }
    // echo "\t// ($hits/$tokens) " . json_encode($data, JSON_UNESCAPED_SLASHES) . PHP_EOL;
    return $rank;
};
$db->sqliteCreateFunction('rank', $rank, 1);
// ----------------------------------------------------------
echo "# Running the query ... \n";
$start = microtime(true);
// rank(matchinfo(document, 'pcxl'))
$select = $db->prepare("SELECT docid, title, rank" . " FROM document d" . " JOIN (" . "    SELECT docid, rank(matchinfo(document, 'pcxl')) AS rank " . "    FROM document" . "    WHERE document MATCH :query" . "    ORDER BY rank DESC" . "    LIMIT 10" . " ) AS r USING (docid)" . " ORDER BY r.rank DESC");
$select->execute([':query' => "Feynman"]);
echo "# Fetching the results ... \n";
$count = 0;
foreach ($select as $row) {
    echo "--> " . json_encode($row, JSON_UNESCAPED_SLASHES) . PHP_EOL;
    $count++;
}
$elapsed = number_format((microtime(true) - $start) * 1000, 2, '.', '') . " ms";
echo "==> {$count} rows fetched in {$elapsed}\n";

     //$db_tmp = new PDO('sqlite::memory:'); //sqlite 3
 } catch (PDOException $error) {
     print "error: " . $error->getMessage() . "<br/>";
     die;
 }
 //add additional functions to SQLite - bool PDO::sqliteCreateFunction ( string function_name, callback callback [, int num_args] )
 if (!function_exists('php_now')) {
     function php_now()
     {
         if (function_exists("date_default_timezone_set") and function_exists("date_default_timezone_get")) {
             @date_default_timezone_set(@date_default_timezone_get());
         }
         return date("Y-m-d H:i:s");
     }
 }
 $db_tmp->sqliteCreateFunction('now', 'php_now', 0);
 //add the database structure
 require_once "resources/classes/schema.php";
 $schema = new schema();
 $schema->db = $db_tmp;
 $schema->db_type = $db_type;
 $schema->sql();
 $schema->exec();
 //get the contents of the sql file
 if (file_exists('/usr/share/examples/fusionpbx/resources/install/sql/sqlite.sql')) {
     $filename = "/usr/share/examples/fusionpbx/resources/install/sql/sqlite.sql";
 } else {
     $filename = $_SERVER["DOCUMENT_ROOT"] . PROJECT_PATH . '/resources/install/sql/sqlite.sql';
 }
 $file_contents = file_get_contents($filename);
 unset($filename);