Инструменты пользователя

Инструменты сайта


// BlogTNG и SQLite3

Как писал в прошлом сообщении в PHP 5.4.x нет SQLite2, есть поддержка только SQLite3. Пришлось немного напрячься и сделать поддержку оного в BlogTNG, дабы не впадать в уныние.

Подкатом подробности.

Поддержку вроде сделал, правда нужно смотреть на оптимизацию, как минимум в паре мест (res2row /получение записи по её номеру/, resRowCount /получение количества записей в выборке/): из-за отсутствия seek и num_rows в SQLite3, время доступа к произвольному элементу изменилось с O(1) на O(n).

Итак, собственно патч:

blogtng-sqlite3.diff
diff --git a/admin.php b/admin.php
index 26fb6a0..be6198d 100644
--- a/admin.php
+++ b/admin.php
@@ -24,7 +24,13 @@ class admin_plugin_blogtng extends DokuWiki_Admin_Plugin {
     function admin_plugin_blogtng() {
         $this->commenthelper =& plugin_load('helper', 'blogtng_comments');
         $this->entryhelper   =& plugin_load('helper', 'blogtng_entry');
-        $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite');
+        
+        if ($this->getConf('sqlite_version') == 'SQLite2')
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite');
+        else
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite3');
+            
+        
         $this->taghelper     =& plugin_load('helper', 'blogtng_tags');
     }
 
@@ -282,7 +288,7 @@ class admin_plugin_blogtng extends DokuWiki_Admin_Plugin {
         // FIXME selectable?
         $limit = 20;
 
-        $count = sqlite_num_rows($resid);
+        $count = $this->sqlitehelper->resRowCount($resid);
         $start = (isset($_REQUEST['btng']['query']['start'])) ? ($_REQUEST['btng']['query']['start'])  : 0;
         $end   = ($count >= ($start + $limit)) ? ($start + $limit) : $count;
         $cur   = ($start / $limit) + 1;
diff --git a/conf/default.php b/conf/default.php
index 601f2e6..d7a364c 100644
--- a/conf/default.php
+++ b/conf/default.php
@@ -9,3 +9,4 @@ $conf['editform_set_date']         = 0;
 $conf['tags']                      = '';
 $conf['receive_linkbacks']         = 1;
 $conf['send_linkbacks']            = 0;
+$conf['sqlite_version']            = 'SQLite3';
diff --git a/conf/metadata.php b/conf/metadata.php
index 5288a54..6907ae3 100644
--- a/conf/metadata.php
+++ b/conf/metadata.php
@@ -17,3 +17,4 @@ $meta['editform_set_date']        = array('onoff');
 $meta['tags']                     = array('string');
 $meta['receive_linkbacks']         = array('onoff');
 $meta['send_linkbacks']          = array('onoff');
+$meta['sqlite_version'] = array('multichoice', '_choices' => array('SQLite2', 'SQLite3'));
diff --git a/helper/comments.php b/helper/comments.php
index 2dbe331..8eee857 100644
--- a/helper/comments.php
+++ b/helper/comments.php
@@ -20,7 +20,12 @@ class helper_plugin_blogtng_comments extends DokuWiki_Plugin {
      * Constructor, loads the sqlite helper plugin
      */
     function helper_plugin_blogtng_comments() {
-        $this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        //$this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        if ($this->getConf('sqlite_version') == 'SQLite2')
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite');
+        else
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite3');
+
     }
 
     /**
@@ -48,7 +53,7 @@ class helper_plugin_blogtng_comments extends DokuWiki_Plugin {
         if ($resid === false) {
             return false;
         }
-        if (sqlite_num_rows($resid) == 0) {
+        if ($this->sqlitehelper->resRowCount($resid) == 0) {
             return null;
         }
         $result = $this->sqlitehelper->res2arr($resid);
@@ -311,7 +316,7 @@ class helper_plugin_blogtng_comments extends DokuWiki_Plugin {
     function unsubscribe($pid, $mail) {
         $sql = 'DELETE FROM subscriptions WHERE pid = ? AND mail = ?';
         $this->sqlitehelper->query($sql, $pid, $mail);
-        $upd = sqlite_changes($this->sqlitehelper->db);
+        $upd = $this->sqlitehelper->changes();
         if ($upd) {
             msg($this->getLang('unsubscribe_ok'), 1);
         } else {
@@ -325,7 +330,7 @@ class helper_plugin_blogtng_comments extends DokuWiki_Plugin {
     function optin($key) {
         $sql = 'UPDATE optin SET optin = 1 WHERE key = ?';
         $this->sqlitehelper->query($sql,$key);
-        $upd = sqlite_changes($this->sqlitehelper->db);
+        $upd = $this->sqlitehelper->changes();
 
         if($upd){
             msg($this->getLang('optin_ok'),1);
@@ -532,7 +537,7 @@ class helper_plugin_blogtng_comments extends DokuWiki_Plugin {
                    LIMIT ".(int) $num;
 
         $res = $this->sqlitehelper->query($query);
-        if(!sqlite_num_rows($res)) return; // no results found
+        if(!$this->sqlitehelper->resRowCount($res)) return; // no results found
         $res = $this->sqlitehelper->res2arr($res);
 
         // print all hits using the template
diff --git a/helper/entry.php b/helper/entry.php
index a154ea5..6226571 100644
--- a/helper/entry.php
+++ b/helper/entry.php
@@ -34,7 +34,12 @@ class helper_plugin_blogtng_entry extends DokuWiki_Plugin {
      * @author Michael Klier <chi@chimeric.de>
      */
     function helper_plugin_blogtng_entry() {
-        $this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        //$this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        if ($this->getConf('sqlite_version') == 'SQLite2')
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite');
+        else
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite3');
+
         $this->entry = $this->prototype();
     }
 
@@ -60,7 +65,7 @@ class helper_plugin_blogtng_entry extends DokuWiki_Plugin {
             $this->entry = $this->prototype();
             return self::RET_ERR_DB;
         }
-        if (sqlite_num_rows($resid) == 0) {
+        if ($this->sqlitehelper->resRowCount($resid) == 0) {
             $this->entry = $this->prototype();
             $this->entry['pid'] = $pid;
             return self::RET_ERR_NOENTRY;
@@ -275,7 +280,7 @@ class helper_plugin_blogtng_entry extends DokuWiki_Plugin {
                    WHERE '.$blog_query.$tag_query;
         $resid = $this->sqlitehelper->query($query);
         if (!$resid) return;
-        $count = sqlite_num_rows($resid);
+        $count = $this->sqlitehelper->resRowCount($resid);
         if($count <= $conf['limit']) return '';
 
         // we now prepare an array of pages to show
@@ -568,7 +573,7 @@ class helper_plugin_blogtng_entry extends DokuWiki_Plugin {
                 ORDER BY cnt DESC, created DESC
                    LIMIT ".(int) $num;
         $res = $this->sqlitehelper->query($query);
-        if(!sqlite_num_rows($res)) return; // no results found
+        if(!$this->sqlitehelper->resRowCount($res)) return; // no results found
         $res = $this->sqlitehelper->res2arr($res);
 
         // now do the output
@@ -814,7 +819,7 @@ class helper_plugin_blogtng_entry extends DokuWiki_Plugin {
                     ORDER BY A.created ' . (($type == 'prev') ? 'DESC' : 'ASC') . '
                        LIMIT 1';
             $res = $this->sqlitehelper->query($query, $pid);
-            if (sqlite_num_rows($res) > 0) {
+            if ($this->sqlitehelper->resRowCount($res) > 0) {
                 $row = $this->sqlitehelper->res2row($res, 0);
                 $related[$type] = $row;
             }
diff --git a/helper/linkback.php b/helper/linkback.php
index bb4eb15..1312744 100644
--- a/helper/linkback.php
+++ b/helper/linkback.php
@@ -37,7 +37,11 @@ class helper_plugin_blogtng_linkback extends DokuWiki_Plugin {
                          'status' => 'hidden',
                          'ip' => clientIP(true));
 
-        $sqlitehelper = plugin_load('helper', 'blogtng_sqlite');
+        if ($this->getConf('sqlite_version') == 'SQLite2')
+            $sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        else
+            $sqlitehelper =& plugin_load('helper', 'blogtng_sqlite3');
+
         $query = 'SELECT web, source FROM comments WHERE pid = ?';
 
         $resid = $sqlitehelper->query($query, $comment['pid']);
diff --git a/helper/sqlite.php b/helper/sqlite.php
index d23d179..bb0a9f4 100644
--- a/helper/sqlite.php
+++ b/helper/sqlite.php
@@ -196,6 +196,25 @@ class helper_plugin_blogtng_sqlite extends DokuWiki_Plugin {
 
 
     /**
+     * Return count of elements in result set
+     * @param $res
+     * @return int
+     */
+    function resRowCount($res){
+        return sqlite_num_rows($res);
+    }
+
+
+    /**
+     * Return count of changes after last update
+     * @return mixed
+     */
+    function changes(){
+        return sqlite_changes($this->db);
+    }
+
+
+    /**
      * Join the given values and quote them for SQL insertion
      */
     function quote_and_join($vals,$sep=',') {
diff --git a/helper/sqlite3.php b/helper/sqlite3.php
new file mode 100644
index 0000000..ff4ddbb
--- /dev/null
+++ b/helper/sqlite3.php
@@ -0,0 +1,260 @@
+<?php
+/**
+ * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
+ * @author     Andreas Gohr <andi@splitbrain.org>
+ */
+// must be run within Dokuwiki
+if(!defined('DOKU_INC')) die();
+
+if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
+require_once(DOKU_INC.'inc/infoutils.php');
+
+if(!defined('BLOGTNG_DIR')) define('BLOGTNG_DIR',DOKU_PLUGIN.'blogtng/');
+
+class helper_plugin_blogtng_sqlite3 extends DokuWiki_Plugin {
+
+    var $db = null;
+    var $rows = null;
+
+    /**
+     * constructor
+     */
+    function helper_plugin_blogtng_sqlite3(){
+        if (!extension_loaded('sqlite3')) {
+            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
+            if(function_exists('dl')) @dl($prefix . 'sqlite3.' . PHP_SHLIB_SUFFIX);
+        }
+
+        if(!class_exists('SQLite3')){
+            msg('blogtng plugin: SQLite3 support missing in this PHP install - plugin will not work',-1);
+        }
+    }
+
+    /**
+     * Open the database
+     */
+    function _dbconnect(){
+        global $conf;
+
+        if($this->db) return true;
+
+        $dbfile = $conf['metadir'].'/blogtng.sqlite3';
+        $init   = (!@file_exists($dbfile) || ((int) @filesize($dbfile)) < 3);
+
+        $error='';
+        //$this->db = sqlite_open($dbfile, 0666, $error);
+        $this->db = new SQLite3($dbfile);
+        
+        if(!$this->db){
+            msg("blogtng plugin: failed to open SQLite database ($error)",-1);
+            return false;
+        }
+
+        if($init) $this->_runupdatefile(BLOGTNG_DIR.'db/db.sql',1);
+        $this->_updatedb();
+        return true;
+    }
+
+    /**
+     * Return the current Database Version
+     */
+    function _currentDBversion(){
+        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
+        $res = $this->query($sql);
+        if(!$res) return false;
+        $row = $this->res2row($res,0);
+        return (int) $row['val'];
+    }
+
+    /**
+     * Update the database if needed
+     */
+    function _updatedb(){
+        $current = $this->_currentDBversion();
+        if(!$current){
+            msg('blogtng: no DB version found. DB probably broken.',-1);
+            return false;
+        }
+        $latest  = (int) trim(io_readFile(BLOGTNG_DIR.'db/latest.version'));
+
+        // all up to date?
+        if($current >= $latest) return true;
+        for($i=$current+1; $i<=$latest; $i++){
+            $file = sprintf(BLOGTNG_DIR.'db/update%04d.sql',$i);
+            if(file_exists($file)){
+                if(!$this->_runupdatefile($file,$i)){
+                    msg('blogtgng: Database upgrade failed for Version '.$i, -1);
+                    return false;
+                }
+            }
+        }
+        return true;
+    }
+
+    /**
+     * Updates the database structure using the given file to
+     * the given version.
+     */
+    function _runupdatefile($file,$version){
+        $sql  = io_readFile($file,false);
+
+        $sql = explode(";",$sql);
+        array_unshift($sql,'BEGIN TRANSACTION');
+        array_push($sql,"UPDATE opts SET val = $version WHERE opt = 'dbversion'");
+        array_push($sql,"COMMIT TRANSACTION");
+
+        foreach($sql as $s){
+            $s = preg_replace('!^\s*--.*$!m', '', $s);
+            $s = trim($s);
+            if(!$s) continue;
+            //$res = sqlite_query($this->db,"$s;");
+            $res = $this->db->query("$s;");
+            if ($res === false) {
+                //sqlite_query($this->db, 'ROLLBACK TRANSACTION');
+                $this->query('ROLLBACK TRANSACTION');
+                return false;
+            }
+        }
+
+        return ($version == $this->_currentDBversion());
+    }
+
+    /**
+     * Execute a query with the given parameters.
+     *
+     * Takes care of escaping
+     *
+     * @param string $sql - the statement
+     * @param arguments...
+     */
+    function query(){
+        if(!$this->_dbconnect()) return false;
+
+        // get function arguments
+        $args = func_get_args();
+        $sql  = trim(array_shift($args));
+
+        if(!$sql){
+            msg('No SQL statement given',-1);
+            return false;
+        }
+
+        if(count($args) > 0 && is_array($args[0])) $args = $args[0];
+        $argc = count($args);
+
+        // check number of arguments
+        if($argc < substr_count($sql,'?')){
+            msg('Not enough arguments passed for statement. '.
+                'Expected '.substr_count($sql,'?').' got '.
+                $argc.' - '.hsc($sql),-1);
+            return false;
+        }
+
+        // explode at wildcard, then join again
+        $parts = explode('?',$sql,$argc+1);
+        $args  = array_map(array($this,'quote_string'),$args);
+        $sql   = '';
+
+        while( ($part = array_shift($parts)) !== null ){
+            $sql .= $part;
+            $sql .= array_shift($args);
+        }
+
+        // execute query
+        $err = '';
+        //$res = @sqlite_query($this->db,$sql,SQLITE_ASSOC,$err);
+        $this->rows = null;
+        $res = $this->db->query($sql);
+        /*
+        if($err){
+            msg($err.' - '.hsc($sql),-1);
+            return false;
+        }elseif(!$res){
+            msg(sqlite_error_string(sqlite_last_error($this->db)).
+                ' - '.hsc($sql),-1);
+            return false;
+        }
+        */
+        if (!$res){
+            msg($this->db->lastErrorMsg().' - '.hsc($sql), -1);
+            return false;
+        }
+
+        return $res;
+    }
+
+    /**
+     * Returns a complete result set as array
+     */
+    function res2arr($res){
+        $data = array();
+
+        if (!@$res->reset())
+            return $data;
+
+        while(($row = $res->fetchArray()) !== false){
+            $data[] = $row;
+        }
+        return $data;
+    }
+
+    /**
+     * Return the wanted row from a given result set as
+     * associative array
+     */
+    function res2row($res,$rownum=0){
+        if (!@$res->reset())
+            return false;
+        
+        $row = array();
+        for ($i = 0; $i <= $rownum; $i++)
+        {
+            $row = $res->fetchArray();
+            if ($row === false)
+            {
+                return false;
+            }
+        }
+        
+        return $row;
+    }
+
+
+    /**
+     * Return count of elements in result set
+     * @param $res
+     * @return int
+     */
+    function resRowCount($res){
+        $data = $this->res2arr($res);
+        return count($data);
+    }
+
+
+    /**
+     * Return count of changes after last update
+     * @return mixed
+     */
+    function changes(){
+        return $this->db->changes();
+    }
+
+
+    /**
+     * Join the given values and quote them for SQL insertion
+     */
+    function quote_and_join($vals,$sep=',') {
+        $vals = array_map(array('helper_plugin_blogtng_sqlite3','quote_string'),$vals);
+        return join($sep,$vals);
+    }
+
+    /**
+     * Run sqlite_escape_string() on the given string and surround it
+     * with quotes
+     */
+    function quote_string($string){
+        if(!$this->_dbconnect()) return false;
+        return "'".$this->db->escapeString($string)."'";
+    }
+
+}
diff --git a/helper/tags.php b/helper/tags.php
index 8db864b..a9e8a19 100644
--- a/helper/tags.php
+++ b/helper/tags.php
@@ -21,7 +21,12 @@ class helper_plugin_blogtng_tags extends DokuWiki_Plugin {
      * Constructor, loads the sqlite helper plugin
      */
     function helper_plugin_blogtng_tags() {
-        $this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        //$this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        if ($this->getConf('sqlite_version') == 'SQLite2')
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite');
+        else
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite3');
+
     }
 
     /**
@@ -52,7 +57,7 @@ class helper_plugin_blogtng_tags extends DokuWiki_Plugin {
             msg('blogtng plugin: failed to load tags!', -1);
             $this->tags = array();
         }
-        if (sqlite_num_rows($resid) == 0) {
+        if ($this->sqlitehelper->resRowCount($resid) == 0) {
             $this->tags = array();
         }
 
@@ -131,11 +136,11 @@ class helper_plugin_blogtng_tags extends DokuWiki_Plugin {
         );
         foreach ($tags as $tag) {
             if ($tag{0} == '+') {
-                array_push($tag_clauses['AND'], 'tag = \'' . sqlite_escape_string(substr($tag, 1)) . '\'');
+                array_push($tag_clauses['AND'], 'tag = \'' . $this->sqlitehelper->quote_string(substr($tag, 1)) . '\'');
             } else if ($tag{0} == '-') {
-                array_push($tag_clauses['NOT'], 'tag != \'' . sqlite_escape_string(substr($tag, 1)) . '\'');
+                array_push($tag_clauses['NOT'], 'tag != \'' . $this->sqlitehelper->quote_string(substr($tag, 1)) . '\'');
             } else {
-                array_push($tag_clauses['OR'], 'tag = \'' . sqlite_escape_string($tag) . '\'');
+                array_push($tag_clauses['OR'], 'tag = \'' . $this->sqlitehelper->quote_string($tag) . '\'');
             }
         }
         $tag_clauses = array_map('array_unique', $tag_clauses);
diff --git a/syntax/topic.php b/syntax/topic.php
index d554208..f55818e 100644
--- a/syntax/topic.php
+++ b/syntax/topic.php
@@ -30,7 +30,12 @@ class syntax_plugin_blogtng_topic extends DokuWiki_Syntax_Plugin {
     );
 
     function syntax_plugin_blogtng_topic() {
-        $this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        //$this->sqlitehelper =& plugin_load('helper', 'blogtng_sqlite');
+        if ($this->getConf('sqlite_version') == 'SQLite2')
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite');
+        else
+            $this->sqlitehelper  =& plugin_load('helper', 'blogtng_sqlite3');
+
         $this->taghelper =& plugin_load('helper', 'blogtng_tags');
     }
 
@@ -106,7 +111,7 @@ class syntax_plugin_blogtng_topic extends DokuWiki_Syntax_Plugin {
 
         ob_start();
         $entryhelper =& plugin_load('helper', 'blogtng_entry');
-        $count = sqlite_num_rows($resid);
+        $count = $this->sqlitehelper->resRowCount($resid);
         for ($i = 0; $i < $count; $i++) {
             $entryhelper->load_by_res($resid, $i);
             $entryhelper->tpl_content($data['tpl'], 'list');

Теперь небольшое руководство по миграции (в том числе, при условии, что уже «усё пропало, шеф!»):

  1. что бы не угробить базу SQLite2 новая база хранится как blogtng.sqlite3 в data/cache
  2. для конвертирования в арче (пока не удалили sqlite2):
    sqlite blogtng.sqlite .dump | sqlite3 blogtng.sqlite3
  3. если в php нет поддержки sqlite2 через конфигуратор ничего уже не настроишь (да, я сделал так, что бы можно было выбрать формат в каком вести блог), поэтому в conf/local.php нужно добавить параметр:
    $conf['plugin']['blogtng']['sqlite_version'] = 'SQLite3';
  4. для профилактики, почистить кеш вики:
    cd data/cache
    find [0-9a-f] -type f | xargs rm -f
  5. и, вроде, PROFIT! :-)

UPD: патч был не полным, исправил

Комментарии