A PDO_SQLite version of Gohr's Data plugin
Compatible with DokuWiki
A PDO_SQLite version of Gohr's Data plugin
Since stretchyboy made an excellent fork of Gohr's sqlite and data plugins to accomodate pdo_sqlite support, I regard this plugin as obsolete.
Fetch the forks here:
– Louwrens van Dellen 2010/05/28 19:28
This plugin is identical to data plugin version 2009-02-13. Except that this plugin requires PHP extension PDO_SQLite instead of extension SQLite.
I wanted to use the data plugin but the webhoster had disabled the SQLite extension to PHP. The PHP extension PDO_SQlite was enabled so I figured I'll edit Andi's code to use this extension instead.
You cannot use both plugins - data and datapdo -, so while this plugin is really a patch. It depends on data and also conflicts with data (same syntax). I wouldn't mind if this patch becomes just a configuration option of the original data plugin.
I haven't hosted the plugin tarball anywhere yet. So instead I've created a small patch file.
Please install the data plugin first, move it to its new name and then apply the patch if you have shell access to your installation:
cd /path/to/dokuwiki/lib/plugins/ mv data datapdo cd datapdo patch -p1 < datapdo-plugin.patch
For your convenience I've appended the patch file:
diff -rupN data/action.php datapdo/action.php --- data/action.php 2009-02-06 21:40:42.000000000 +0100 +++ datapdo/action.php 2009-07-24 21:45:16.000000000 +0200 @@ -8,19 +8,19 @@ if(!defined('DOKU_INC')) die(); require_once(DOKU_PLUGIN.'action.php'); -class action_plugin_data extends DokuWiki_Action_Plugin { +class action_plugin_datapdo extends DokuWiki_Action_Plugin { /** - * will hold the data helper plugin + * will hold the datapdo helper plugin */ var $dthlp = null; /** * Constructor. Load helper plugin */ - function action_plugin_data(){ - $this->dthlp =& plugin_load('helper', 'data'); - if(!$this->dthlp) msg('Loading the data helper failed. Make sure the data plugin is installed.',-1); + function action_plugin_datapdo(){ + $this->dthlp =& plugin_load('helper', 'datapdo'); + if(!$this->dthlp) msg('Loading the datapdo helper failed. Make sure the datapdo plugin is installed.',-1); } /** @@ -43,22 +43,25 @@ class action_plugin_data extends DokuWik */ function _handle(&$event, $param){ $data = $event->data; + if(strpos($data[0][1],'dataentry') !== false) return; // plugin seems still to be there if(!$this->dthlp->_dbconnect()) return; $id = $data[1].":".$data[2]; // get page id - $sql = "SELECT pid FROM pages WHERE page ='".sqlite_escape_string($id)."'"; - $res = sqlite_query($this->dthlp->db, $sql); - $pid = (int) sqlite_fetch_single($res); + $sql = "SELECT pid FROM pages WHERE page = :id"; + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':id' => $id)); + $pid = (int) $sth->fetch(PDO::FETCH_NUM); if(!$pid) return; // we have no data for this page $sql = "DELETE FROM data WHERE pid = $pid"; - sqlite_query($this->dthlp->db, $sql); + $this->dthlp->db->query($this->dthlp->db, $sql); $sql = "DELETE FROM pages WHERE pid = $pid"; - sqlite_query($this->dthlp->db, $sql); + $this->dthlp->db->query($this->dthlp->db, $sql); + } } diff -rupN data/helper.php datapdo/helper.php --- data/helper.php 2009-02-13 15:26:25.000000000 +0100 +++ datapdo/helper.php 2009-07-24 21:45:16.000000000 +0200 @@ -14,21 +14,16 @@ require_once(DOKU_INC.'inc/infoutils.php /** * This is the base class for all syntax classes, providing some general stuff */ -class helper_plugin_data extends DokuWiki_Plugin { +class helper_plugin_datapdo extends DokuWiki_Plugin { var $db = null; /** * constructor */ - function helper_plugin_data(){ - if (!extension_loaded('sqlite')) { - $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : ''; - @dl($prefix . 'sqlite.' . PHP_SHLIB_SUFFIX); - } - - if(!function_exists('sqlite_open')){ - msg('data plugin: SQLite support missing in this PHP install - plugin will not work',-1); + function helper_plugin_datapdo(){ + if (!extension_loaded('pdo_sqlite')) { + msg('datapdo plugin: PDO_SQLite support missing in this PHP install - plugin will not work',-1); } } @@ -37,11 +32,11 @@ class helper_plugin_data extends DokuWik */ function getInfo(){ return array( - 'author' => 'Andreas Gohr', - 'email' => 'andi@splitbrain.org', - 'date' => '2009-02-13', - 'name' => 'Structured Data Plugin', - 'desc' => 'Add and query structured data in your wiki', + 'author' => 'Louwrens van Dellen', + 'email' => 'louwrens.van.dellen@gmail.com', + 'date' => '2009-07-24', + 'name' => 'Structured Data Plugin using PDO_SQlite', + 'desc' => 'Modified version of Gohr\' Data plugin', 'url' => 'http://www.dokuwiki.org/plugin:data', ); } @@ -121,8 +116,8 @@ class helper_plugin_data extends DokuWik break; case 'tag': $outs[] = '<a href="'.wl(str_replace('/',':',cleanID($key)),array('dataflt'=>$key.':'.$val )). - '" title="'.sprintf($this->getLang('tagfilter'),hsc($val)). - '" class="wikilink1">'.hsc($val).'</a>'; + '" title="'.sprintf($this->getLang('tagfilter'),hsc($val)). + '" class="wikilink1">'.hsc($val).'</a>'; break; default: if(substr($type,0,3) == 'img'){ @@ -144,6 +139,7 @@ class helper_plugin_data extends DokuWik * @returns array with key, type, ismulti, title */ function _column($col){ + if(strtolower(substr($col,-1)) == 's'){ $col = substr($col,0,-1); $multi = true; @@ -165,13 +161,17 @@ class helper_plugin_data extends DokuWik $init = (!@file_exists($dbfile) || !@filesize($dbfile)); $error=''; - $this->db = sqlite_open($dbfile, 0666, $error); + $dsn = 'sqlite:/'.$dbfile; /* DSN is sqlite:/absolute/path/dataplugin.sqlite */ + $this->db = new PDO($dsn); if(!$this->db){ - msg("data plugin: failed to open SQLite database ($error)",-1); + msg("datapdo plugin: failed to open PDO-SQLite database ($error)",-1); return false; - } + } - if($init) $this->_initdb(); + if($init){ + $this->_initdb(); + msg("datapdo plugin: initialized!"); + } return true; } @@ -180,9 +180,9 @@ class helper_plugin_data extends DokuWik * create the needed tables */ function _initdb(){ - sqlite_query($this->db,'CREATE TABLE pages (pid INTEGER PRIMARY KEY, page, title);'); - sqlite_query($this->db,'CREATE UNIQUE INDEX idx_page ON pages(page);'); - sqlite_query($this->db,'CREATE TABLE data (eid INTEGER PRIMARY KEY, pid INTEGER, key, value);'); - sqlite_query($this->db,'CREATE INDEX idx_key ON data(key);'); + $this->db->query('CREATE TABLE pages (pid INTEGER PRIMARY KEY, page, title);'); + $this->db->query('CREATE UNIQUE INDEX idx_page ON pages(page);'); + $this->db->query('CREATE TABLE data (eid INTEGER PRIMARY KEY, pid INTEGER, key, value);'); + $this->db->query('CREATE INDEX idx_key ON data(key);'); } } diff -rupN data/syntax/cloud.php datapdo/syntax/cloud.php --- data/syntax/cloud.php 2008-02-08 11:43:28.000000000 +0100 +++ datapdo/syntax/cloud.php 2009-07-24 21:45:16.000000000 +0200 @@ -8,19 +8,19 @@ if(!defined('DOKU_INC')) die(); require_once(DOKU_PLUGIN.'syntax.php'); -class syntax_plugin_data_cloud extends DokuWiki_Syntax_Plugin { +class syntax_plugin_datapdo_cloud extends DokuWiki_Syntax_Plugin { /** - * will hold the data helper plugin + * will hold the datapdo helper plugin */ var $dthlp = null; /** * Constructor. Load helper plugin */ - function syntax_plugin_data_cloud(){ - $this->dthlp =& plugin_load('helper', 'data'); - if(!$this->dthlp) msg('Loading the data helper failed. Make sure the data plugin is installed.',-1); + function syntax_plugin_datapdo_cloud(){ + $this->dthlp =& plugin_load('helper', 'datapdo'); + if(!$this->dthlp) msg('Loading the datapdo helper failed. Make sure the datapdo plugin is installed.',-1); } /** @@ -56,7 +56,7 @@ class syntax_plugin_data_cloud extends D * Connect pattern to lexer */ function connectTo($mode) { - $this->Lexer->addSpecialPattern('----+ *datacloud(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_data_cloud'); + $this->Lexer->addSpecialPattern('----+ *datacloud(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_datapdo_cloud'); } @@ -104,7 +104,7 @@ class syntax_plugin_data_cloud extends D $data['page'] = cleanID($line[1]); break; default: - msg("data plugin: unknown option '".hsc($line[0])."'",-1); + msg("datapdo plugin: unknown option '".hsc($line[0])."'",-1); } } @@ -126,18 +126,19 @@ class syntax_plugin_data_cloud extends D // build query $sql = "SELECT value, COUNT(pid) as cnt FROM data - WHERE key = '".sqlite_escape_string($data['field'])."' - GROUP BY value"; - if($data['min']) $sql .= ' HAVING cnt >= '.$data['min']; + WHERE key = :field + GROUP BY value"; //'".sqlite_escape_string()."' + if($data['min']) $sql .= ' HAVING cnt >= :min'; //.$data['min']; $sql .= ' ORDER BY cnt DESC'; - if($data['limit']) $sql .= ' LIMIT '.$data['limit']; + if($data['limit']) $sql .= ' LIMIT :limit'; //.$data['limit']; // build cloud data $tags = array(); - $res = sqlite_query($this->dthlp->db,$sql); + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':field'=>$data['field'], ':min'=>$data['min'], ':limit'=>$data['limit'])); $min = 0; $max = 0; - while ($row = sqlite_fetch_array($res, SQLITE_NUM)) { + while ($row = $sth->fetch(PDO::FETCH_NUM)) { if(!$max) $max = $row[1]; $min = $row[1]; $tags[$row[0]] = $row[1]; @@ -149,8 +150,8 @@ class syntax_plugin_data_cloud extends D foreach($tags as $tag => $lvl){ $renderer->doc .= '<li class="cl'.$lvl.'">'; $renderer->doc .= '<a href="'.wl($data['page'],array('datasrt'=>$_GET['datasrt'], - 'dataflt'=>$data['field'].':'.$tag )). - '" title="'.sprintf($this->getLang('tagfilter'),hsc($tag)).'" class="wikilink1">'.hsc($tag).'</a>'; + 'dataflt'=>$data['field'].':'.$tag )). + '" title="'.sprintf($this->getLang('tagfilter'),hsc($tag)).'" class="wikilink1">'.hsc($tag).'</a>'; $renderer->doc .= '</li>'; } $renderer->doc .= '</ul>'; diff -rupN data/syntax/entry.php datapdo/syntax/entry.php --- data/syntax/entry.php 2009-02-13 15:26:25.000000000 +0100 +++ datapdo/syntax/entry.php 2009-07-24 21:45:17.000000000 +0200 @@ -8,19 +8,19 @@ if(!defined('DOKU_INC')) die(); require_once(DOKU_PLUGIN.'syntax.php'); -class syntax_plugin_data_entry extends DokuWiki_Syntax_Plugin { +class syntax_plugin_datapdo_entry extends DokuWiki_Syntax_Plugin { /** - * will hold the data helper plugin + * will hold the datapdo helper plugin */ var $dthlp = null; /** * Constructor. Load helper plugin */ - function syntax_plugin_data_entry(){ - $this->dthlp =& plugin_load('helper', 'data'); - if(!$this->dthlp) msg('Loading the data helper failed. Make sure the data plugin is installed.',-1); + function syntax_plugin_datapdo_entry(){ + $this->dthlp =& plugin_load('helper', 'datapdo'); + if(!$this->dthlp) msg('Loading the datapdo helper failed. Make sure the datapdo plugin is installed.',-1); } /** @@ -56,7 +56,7 @@ class syntax_plugin_data_entry extends D * Connect pattern to lexer */ function connectTo($mode) { - $this->Lexer->addSpecialPattern('----+ *dataentry(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_data_entry'); + $this->Lexer->addSpecialPattern('----+ *dataentry(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_datapdo_entry'); } @@ -156,54 +156,50 @@ class syntax_plugin_data_entry extends D $error = ''; if(!$title) $title = $id; - $id = sqlite_escape_string($id); - $title = sqlite_escape_string($title); // begin transaction - $sql = "BEGIN TRANSACTION"; - sqlite_query($this->dthlp->db,$sql); + $this->dthlp->db->beginTransaction(); // store page info - $sql = "INSERT OR IGNORE INTO pages (page,title) VALUES ('$id','$title')"; - sqlite_query($this->dthlp->db,$sql,SQLITE_NUM); + $sql = "INSERT OR IGNORE INTO pages (page,title) VALUES (:id,:title)"; + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':id'=>$id, ':title'=>$title)); // Update title if insert failed (record already saved before) - $sql = "UPDATE pages SET title = '$title' WHERE page = '$id'"; - sqlite_query($this->dthlp->db,$sql,SQLITE_NUM); + $sql = "UPDATE pages SET title = :title WHERE page = :id"; + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':id'=>$id, ':title'=>$title)); // fetch page id - $sql = "SELECT pid FROM pages WHERE page = '$id'"; - $res = sqlite_query($this->dthlp->db, $sql); - $pid = (int) sqlite_fetch_single($res); + $sql = "SELECT pid FROM pages WHERE page = :id"; + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':id'=>$id)); + $pid = $sth->fetchColumn(); + //msg('datapdo plugin: database updated on pid='.$pid); if(!$pid){ - msg("data plugin: failed saving data",-1); + msg("datapdo plugin: failed saving data",-1); return false; } // remove old data - $sql = "DELETE FROM data WHERE pid = $pid"; - sqlite_query($this->dthlp->db,$sql); + $sql = "DELETE FROM data WHERE pid = :pid"; + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':pid'=>$pid)); // insert new data + $sql = "INSERT INTO data (pid, key, value) VALUES (:pid, :key, :v)"; + $sth = $this->dthlp->db->prepare($sql); foreach ($data['data'] as $key => $val){ - $k = sqlite_escape_string($key); - if(is_array($val)) foreach($val as $v){ - $v = sqlite_escape_string($v); - $sql = "INSERT INTO data (pid, key, value) VALUES ($pid, '$k', '$v')"; - sqlite_query($this->dthlp->db,$sql); - }else { - $v = sqlite_escape_string($val); - $sql = "INSERT INTO data (pid, key, value) VALUES ($pid, '$k', '$v')"; - sqlite_query($this->dthlp->db,$sql); + if (!is_array($val)) $val = array($val); + foreach($val as $v){ + $sth->execute(array(':pid'=>$pid, ':key'=>$key, ':v'=>$v)); } } // finish transaction - $sql = "COMMIT TRANSACTION"; - sqlite_query($this->dthlp->db,$sql); - - sqlite_close($this->dthlp->db); + $this->dthlp->db->commit(); + return true; } diff -rupN data/syntax/related.php datapdo/syntax/related.php --- data/syntax/related.php 2008-02-08 19:43:25.000000000 +0100 +++ datapdo/syntax/related.php 2009-07-24 21:45:17.000000000 +0200 @@ -9,19 +9,19 @@ if(!defined('DOKU_INC')) die(); require_once(DOKU_PLUGIN.'syntax.php'); -class syntax_plugin_data_related extends DokuWiki_Syntax_Plugin { +class syntax_plugin_datapdo_related extends DokuWiki_Syntax_Plugin { /** - * will hold the data helper plugin + * will hold the datapdo helper plugin */ var $dthlp = null; /** * Constructor. Load helper plugin */ - function syntax_plugin_data_related(){ - $this->dthlp =& plugin_load('helper', 'data'); - if(!$this->dthlp) msg('Loading the data helper failed. Make sure the data plugin is installed.',-1); + function syntax_plugin_datapdo_related(){ + $this->dthlp =& plugin_load('helper', 'datapdo'); + if(!$this->dthlp) msg('Loading the datapdo helper failed. Make sure the datapdo plugin is installed.',-1); } /** @@ -57,7 +57,7 @@ class syntax_plugin_data_related extends * Connect pattern to lexer */ function connectTo($mode) { - $this->Lexer->addSpecialPattern('----+ *datarelated(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_data_related'); + $this->Lexer->addSpecialPattern('----+ *datarelated(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_datapdo_related'); } @@ -96,10 +96,10 @@ class syntax_plugin_data_related extends case 'cols': $cols = explode(',',$line[1]); foreach($cols as $col){ - $col = trim($col); - if(!$col) continue; - list($key,$type) = $this->dthlp->_column($col); - $data['cols'][$key] = $type; + $col = trim($col); + if(!$col) continue; + list($key,$type) = $this->dthlp->_column($col); + $data['cols'][$key] = $type; } break; case 'limit': @@ -110,9 +110,9 @@ class syntax_plugin_data_related extends case 'sort': list($sort) = $this->dthlp->_column($line[1]); if(substr($sort,0,1) == '^'){ - $data['sort'] = array(substr($sort,1),'DESC'); + $data['sort'] = array(substr($sort,1),'DESC'); }else{ - $data['sort'] = array($sort,'ASC'); + $data['sort'] = array($sort,'ASC'); } break; case 'where': @@ -123,26 +123,26 @@ class syntax_plugin_data_related extends case 'filteror': case 'or': if(preg_match('/^(.*?)(=|<|>|<=|>=|<>|!=|=~|~)(.*)$/',$line[1],$matches)){ - list($key) = $this->dthlp->_column(trim($matches[1])); - $val = trim($matches[3]); - $val = sqlite_escape_string($val); //pre escape - $com = $matches[2]; - if($com == '<>'){ + list($key) = $this->dthlp->_column(trim($matches[1])); + $val = trim($matches[3]); + $val = PDO::quote($val); //pre escape + $com = $matches[2]; + if($com == '<>'){ $com = '!='; - }elseif($com == '=~' || $com == '~'){ + }elseif($com == '=~' || $com == '~'){ $com = 'LIKE'; $val = str_replace('*','%',$val); - } + } - $data['filter'][] = array('key' => $key, - 'value' => $val, - 'compare' => $com, - 'logic' => $logic - ); + $data['filter'][] = array('key' => $key, + 'value' => $val, + 'compare' => $com, + 'logic' => $logic + ); } break; default: - msg("data plugin: unknown option '".hsc($line[0])."'",-1); + msg("datapdo plugin: unknown option '".hsc($line[0])."'",-1); } } @@ -161,14 +161,14 @@ class syntax_plugin_data_related extends $sql = $this->_buildSQL($data,$ID); if(!$sql) return true; // sql build - $res = sqlite_query($this->dthlp->db,$sql); - if(!sqlite_num_rows($res)) return true; // no rows matched + $sth = $this->dthlp->db->query($sql); + if(!$sth->rowCount()) return true; // no rows matched $renderer->doc .= '<dl class="'.$data['classes'].'">'; $renderer->doc .= '<dt>'.htmlspecialchars($data['title']).'</dt>'; $renderer->doc .= '<dd>'; $renderer->listu_open(); - while ($row = sqlite_fetch_array($res, SQLITE_ASSOC)) { + while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { $renderer->listitem_open(1); $renderer->internallink($row['page']); $renderer->listitem_close(); @@ -198,21 +198,23 @@ class syntax_plugin_data_related extends foreach (array_keys($data['cols']) as $col){ // get values for current page: $values = array(); - $sql = "SELECT A.value - FROM data A, pages B - WHERE key = '".sqlite_escape_string($col)."' - AND A.pid = B.pid - AND B.page = '".sqlite_escape_string($id)."'"; - $res = sqlite_query($this->dthlp->db,$sql); - while ($row = sqlite_fetch_array($res, SQLITE_NUM)) { + $sql = "SELECT A.value" + ." FROM data A, pages B" + ." WHERE key = :col" //'".sqlite_escape_string($col)."' + ." AND A.pid = B.pid" + ." AND B.page = :id" ; //'".sqlite_escape_string($id)."'"; + $sth = $this->dthlp->db->prepare($sql); + $sth->execute(array(':id'=>$id, ':title'=>$col)); + + while ($row = $sth->fetch(PDO::FETCH_NUM)) { if($row[0]) $values[] = $row[0]; } if(!count($values)) continue; // no values? ignore the column. $found = true; - $values = array_map('sqlite_escape_string',$values); + $values = array_map('PDO::quote',$values); - $cond[] = " ( T1.key = '".sqlite_escape_string($col)."'". - " AND T1.value IN ('".join("','",$values)."') )\n"; + $cond[] = " ( T1.key = '".PDO::quote($col)."'" + ." AND T1.value IN ('".join("','",$values)."') )\n"; } $where .= ' AND ('.join(' OR ',$cond).') '; @@ -232,7 +234,7 @@ class syntax_plugin_data_related extends if(!$tables[$col]){ $tables[$col] = 'T'.(++$cnt); $from .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid'; - $from .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'"; + $from .= ' AND '.$tables[$col].".key = '".PDO::quote($col)."'"; } $order = ', '.$tables[$col].'.value '.$data['sort'][1]; @@ -257,11 +259,11 @@ class syntax_plugin_data_related extends if(!$tables[$col]){ $tables[$col] = 'T'.(++$cnt); $from .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid'; - $from .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'"; + $from .= ' AND '.$tables[$col].".key = '".PDO::quote($col)."'"; } $where .= ' '.$filter['logic'].' '.$tables[$col].'.value '.$filter['compare']. - " '".$filter['value']."'"; //value is already escaped + " '".$filter['value']."'"; //value is already escaped } } @@ -272,10 +274,10 @@ class syntax_plugin_data_related extends $sql = "SELECT pages.pid, pages.page as page, pages.title as title, COUNT(*) as rel FROM pages, data as T1 $from WHERE pages.pid = T1.pid - AND pages.page != '".sqlite_escape_string($id)."' - $where - GROUP BY pages.pid - ORDER BY rel DESC$order"; + AND pages.page != '".PDO::quote($id)."' + $where + GROUP BY pages.pid + ORDER BY rel DESC$order"; // limit if($data['limit']){ diff -rupN data/syntax/table.php datapdo/syntax/table.php --- data/syntax/table.php 2008-02-08 23:11:17.000000000 +0100 +++ datapdo/syntax/table.php 2009-07-24 21:45:17.000000000 +0200 @@ -8,19 +8,19 @@ if(!defined('DOKU_INC')) die(); require_once(DOKU_PLUGIN.'syntax.php'); -class syntax_plugin_data_table extends DokuWiki_Syntax_Plugin { +class syntax_plugin_datapdo_table extends DokuWiki_Syntax_Plugin { /** - * will hold the data helper plugin + * will hold the datapdo helper plugin */ var $dthlp = null; /** * Constructor. Load helper plugin */ - function syntax_plugin_data_table(){ - $this->dthlp =& plugin_load('helper', 'data'); - if(!$this->dthlp) msg('Loading the data helper failed. Make sure the data plugin is installed.',-1); + function syntax_plugin_datapdo_table(){ + $this->dthlp =& plugin_load('helper', 'datapdo'); + if(!$this->dthlp) msg('Loading the datapdo helper failed. Make sure the datapdo plugin is installed.',-1); } /** @@ -56,7 +56,7 @@ class syntax_plugin_data_table extends D * Connect pattern to lexer */ function connectTo($mode) { - $this->Lexer->addSpecialPattern('----+ *datatable(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_data_table'); + $this->Lexer->addSpecialPattern('----+ *datatable(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+',$mode,'plugin_datapdo_table'); } @@ -91,14 +91,15 @@ class syntax_plugin_data_table extends D case 'cols': $cols = explode(',',$line[1]); foreach($cols as $col){ - $col = trim($col); - if(!$col) continue; - list($key,$type) = $this->dthlp->_column($col); - $data['cols'][$key] = $type; - - // fix type for special type - if($key == '%pageid%') $data['cols'][$key] = 'page'; - if($key == '%title%') $data['cols'][$key] = 'title'; + + $col = trim($col); + if(!$col) continue; + list($key,$type) = $this->dthlp->_column($col); + $data['cols'][$key] = $type; + + // fix type for special type + if($key == '%pageid%') $data['cols'][$key] = 'page'; + if($key == '%title%') $data['cols'][$key] = 'title'; } break; case 'title': @@ -108,8 +109,8 @@ class syntax_plugin_data_table extends D case 'headers': $cols = explode(',',$line[1]); foreach($cols as $col){ - $col = trim($col); - $data['headers'][] = $col; + $col = trim($col); + $data['headers'][] = $col; } break; case 'limit': @@ -120,9 +121,9 @@ class syntax_plugin_data_table extends D case 'sort': list($sort) = $this->dthlp->_column($line[1]); if(substr($sort,0,1) == '^'){ - $data['sort'] = array(substr($sort,1),'DESC'); + $data['sort'] = array(substr($sort,1),'DESC'); }else{ - $data['sort'] = array($sort,'ASC'); + $data['sort'] = array($sort,'ASC'); } break; case 'where': @@ -133,26 +134,26 @@ class syntax_plugin_data_table extends D case 'filteror': case 'or': if(preg_match('/^(.*?)(=|<|>|<=|>=|<>|!=|=~|~)(.*)$/',$line[1],$matches)){ - list($key) = $this->dthlp->_column(trim($matches[1])); - $val = trim($matches[3]); - $val = sqlite_escape_string($val); //pre escape - $com = $matches[2]; - if($com == '<>'){ + list($key) = $this->dthlp->_column(trim($matches[1])); + $val = trim($matches[3]); + $val = $this->dthlp->db->quote($val); //pre escape + $com = $matches[2]; + if($com == '<>'){ $com = '!='; - }elseif($com == '=~' || $com == '~'){ + }elseif($com == '=~' || $com == '~'){ $com = 'LIKE'; $val = str_replace('*','%',$val); - } + } - $data['filter'][] = array('key' => $key, - 'value' => $val, - 'compare' => $com, - 'logic' => $logic - ); + $data['filter'][] = array('key' => $key, + 'value' => $val, + 'compare' => $com, + 'logic' => $logic + ); } break; default: - msg("data plugin: unknown option '".hsc($line[0])."'",-1); + msg("datapdo plugin: unknown option '".hsc($line[0])."'",-1); } } @@ -185,23 +186,25 @@ class syntax_plugin_data_table extends D #dbg($data); $sql = $this->_buildSQL($data); // handles GET params, too #dbg($sql); + // register our custom aggregate function - sqlite_create_aggregate($this->dthlp->db,'group_concat', - array($this,'_sqlite_group_concat_step'), - array($this,'_sqlite_group_concat_finalize'), 2); + $this->dthlp->db->sqliteCreateAggregate('group_concat', + array($this,'_sqlite_group_concat_step'), + array($this,'_sqlite_group_concat_finalize') ); // run query + $sth = $this->dthlp->db->query($sql); + + $cols = array_keys($data['cols']); $types = array_values($data['cols']); - $res = sqlite_query($this->dthlp->db,$sql); // build table $renderer->doc .= '<table class="inline dataplugin_table '.$data['classes'].'">'; // build column headers $renderer->doc .= '<tr>'; - $cols = array_keys($data['cols']); foreach($data['headers'] as $num => $head){ $col = $cols[$num]; @@ -215,7 +218,7 @@ class syntax_plugin_data_table extends D } } $renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$col, 'dataofs'=>$_GET['dataofs'], 'dataflt'=>$_GET['dataflt'])). - '" title="'.$this->getLang('sort').'">'.hsc($head).'</a>'; + '" title="'.$this->getLang('sort').'">'.hsc($head).'</a>'; $renderer->doc .= '</th>'; } @@ -223,9 +226,10 @@ class syntax_plugin_data_table extends D // build data rows $cnt = 0; - while ($row = sqlite_fetch_array($res, SQLITE_NUM)) { + while ($row = $sth->fetch(PDO::FETCH_NUM)) { $renderer->doc .= '<tr>'; foreach($row as $num => $col){ + $renderer->doc .= '<td>'.$this->dthlp->_formatData($cols[$num],$col,$types[$num],$renderer).'</td>'; } $renderer->doc .= '</tr>'; @@ -242,15 +246,15 @@ class syntax_plugin_data_table extends D if($prev < 0) $prev = 0; $renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$_GET['datasrt'], 'dataofs'=>$prev, 'dataflt'=>$_GET['dataflt'] )). - '" title="'.$this->getLang('prev').'" class="prev">'.$this->getLang('prev').'</a>'; + '" title="'.$this->getLang('prev').'" class="prev">'.$this->getLang('prev').'</a>'; } $renderer->doc .= ' '; - if(sqlite_num_rows($res) > $data['limit']){ + if($sth->rowCount() > $data['limit']){ $next = $offset + $data['limit']; $renderer->doc .= '<a href="'.wl($ID,array('datasrt'=>$_GET['datasrt'], 'dataofs'=>$next, 'dataflt'=>$_GET['dataflt'] )). - '" title="'.$this->getLang('next').'" class="next">'.$this->getLang('next').'</a>'; + '" title="'.$this->getLang('next').'" class="next">'.$this->getLang('next').'</a>'; } $renderer->doc .= '</th></tr>'; } @@ -292,7 +296,7 @@ class syntax_plugin_data_table extends D if(!$tables[$col]){ $tables[$col] = 'T'.(++$cnt); $from .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid'; - $from .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'"; + $from .= ' AND '.$tables[$col].".key = ".$this->dthlp->db->quote($col).""; } $select[] = 'group_concat('.$tables[$col].".value,'\n')"; } @@ -311,7 +315,7 @@ class syntax_plugin_data_table extends D if(!$tables[$col]){ $tables[$col] = 'T'.(++$cnt); $from .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid'; - $from .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'"; + $from .= ' AND '.$tables[$col].".key = ".$this->dthlp->db->quote($col).""; } $order = 'ORDER BY '.$tables[$col].'.value '.$data['sort'][1]; @@ -336,11 +340,11 @@ class syntax_plugin_data_table extends D if(!$tables[$col]){ $tables[$col] = 'T'.(++$cnt); $from .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid'; - $from .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'"; + $from .= ' AND '.$tables[$col].".key = ".$this->dthlp->db->quote($col).""; } $where .= ' '.$filter['logic'].' '.$tables[$col].'.value '.$filter['compare']. - " '".$filter['value']."'"; //value is already escaped + " '".$filter['value']."'"; //value is already escaped } } @@ -353,10 +357,10 @@ class syntax_plugin_data_table extends D if(!$tables[$col]){ $tables[$col] = 'T'.(++$cnt); $from .= ' LEFT JOIN data AS '.$tables[$col].' ON '.$tables[$col].'.pid = pages.pid'; - $from .= ' AND '.$tables[$col].".key = '".sqlite_escape_string($col)."'"; + $from .= ' AND '.$tables[$col].".key = ".$this->dthlp->db->quote($col).""; } - $where .= ' AND '.$tables[$col].".value = '".sqlite_escape_string($val)."'"; + $where .= ' AND '.$tables[$col].".value = ".$this->dthlp->db->quote($val).""; } // were any data tables used? @@ -370,7 +374,7 @@ class syntax_plugin_data_table extends D $sql = "SELECT ".join(', ',$select)." FROM pages $from WHERE $where - GROUP BY pages.page + GROUP BY pages.page $order"; // offset and limit @@ -391,9 +395,12 @@ class syntax_plugin_data_table extends D * * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine */ - function _sqlite_group_concat_step(&$context, $string, $separator = ',') { - $context['sep'] = $separator; - $context['data'][] = $string; + function _sqlite_group_concat_step( &$context , $rownumber , $string , $separator=', ') { + if (!is_array($context)){ + $context = array('data'=>array(), 'sep'=>$separator); + } + $context['data'][] = $string; + return $context; } /** @@ -401,9 +408,9 @@ class syntax_plugin_data_table extends D * * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine */ - function _sqlite_group_concat_finalize(&$context) { - $context['data'] = array_unique($context['data']); - return join($context['sep'],$context['data']); + function _sqlite_group_concat_finalize(&$context, $rownumber) { + $context['data'] = array_unique($context['data']); + return join($context['sep'],$context['data']); } }