1a1e6784eSAndreas Gohr<?php 2a1e6784eSAndreas Gohr/** 3a1e6784eSAndreas Gohr * DokuWiki Plugin sqlite (Helper Component) 4a1e6784eSAndreas Gohr * 5a1e6784eSAndreas Gohr * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html 6a1e6784eSAndreas Gohr * @author Andreas Gohr <gohr@cosmocode.de> 7a1e6784eSAndreas Gohr */ 8a1e6784eSAndreas Gohr 9a1e6784eSAndreas Gohr// must be run within Dokuwiki 10a1e6784eSAndreas Gohrif (!defined('DOKU_INC')) die(); 11a1e6784eSAndreas Gohr 12a1e6784eSAndreas Gohrif (!defined('DOKU_LF')) define('DOKU_LF', "\n"); 13a1e6784eSAndreas Gohrif (!defined('DOKU_TAB')) define('DOKU_TAB', "\t"); 14a1e6784eSAndreas Gohrif (!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/'); 15a1e6784eSAndreas Gohr 1687fa2c18Sstretchyboyif (!defined('DOKU_EXT_SQLITE')) define('DOKU_EXT_SQLITE', 'sqlite'); 1787fa2c18Sstretchyboyif (!defined('DOKU_EXT_PDO')) define('DOKU_EXT_PDO', 'pdo'); 1887fa2c18Sstretchyboy 1987fa2c18Sstretchyboy 20a1e6784eSAndreas Gohrclass helper_plugin_sqlite extends DokuWiki_Plugin { 21a1e6784eSAndreas Gohr var $db = null; 22a1e6784eSAndreas Gohr var $dbname = ''; 23*7ed6069fSAdrian Lang var $extension = null; 24a1e6784eSAndreas Gohr function getInfo() { 25a1e6784eSAndreas Gohr return confToHash(dirname(__FILE__).'plugin.info.txt'); 26a1e6784eSAndreas Gohr } 27a1e6784eSAndreas Gohr 28a1e6784eSAndreas Gohr /** 29a1e6784eSAndreas Gohr * constructor 30a1e6784eSAndreas Gohr */ 31a1e6784eSAndreas Gohr function helper_plugin_sqlite(){ 3287fa2c18Sstretchyboy 3387fa2c18Sstretchyboy if(!$this->extension) 3487fa2c18Sstretchyboy { 3587fa2c18Sstretchyboy if (!extension_loaded('pdo_sqlite')) { 3687fa2c18Sstretchyboy $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : ''; 3787fa2c18Sstretchyboy if(function_exists('dl')) @dl($prefix . 'pdo_sqlite.' . PHP_SHLIB_SUFFIX); 3887fa2c18Sstretchyboy } 3987fa2c18Sstretchyboy 4087fa2c18Sstretchyboy if(class_exists('pdo')){ 4187fa2c18Sstretchyboy $this->extension = DOKU_EXT_PDO; 4287fa2c18Sstretchyboy } 4387fa2c18Sstretchyboy } 4487fa2c18Sstretchyboy 4587fa2c18Sstretchyboy if(!$this->extension) 46*7ed6069fSAdrian Lang { 47*7ed6069fSAdrian Lang if (!extension_loaded('sqlite')) { 48*7ed6069fSAdrian Lang $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : ''; 49*7ed6069fSAdrian Lang if(function_exists('dl')) @dl($prefix . 'sqlite.' . PHP_SHLIB_SUFFIX); 50*7ed6069fSAdrian Lang } 51*7ed6069fSAdrian Lang 52*7ed6069fSAdrian Lang if(function_exists('sqlite_open')){ 53*7ed6069fSAdrian Lang $this->extension = DOKU_EXT_SQLITE; 54*7ed6069fSAdrian Lang } 55*7ed6069fSAdrian Lang } 56*7ed6069fSAdrian Lang 57*7ed6069fSAdrian Lang if(!$this->extension) 58d8139b19Sstretchyboy 5987fa2c18Sstretchyboy { 6087fa2c18Sstretchyboy msg('SQLite & PDO SQLite support missing in this PHP install - plugin will not work',-1); 61a1e6784eSAndreas Gohr } 62a1e6784eSAndreas Gohr } 63a1e6784eSAndreas Gohr 64a1e6784eSAndreas Gohr /** 65a1e6784eSAndreas Gohr * Initializes and opens the database 66a1e6784eSAndreas Gohr * 67a1e6784eSAndreas Gohr * Needs to be called right after loading this helper plugin 68a1e6784eSAndreas Gohr */ 69a1e6784eSAndreas Gohr function init($dbname,$updatedir){ 70a1e6784eSAndreas Gohr global $conf; 71a1e6784eSAndreas Gohr 72a1e6784eSAndreas Gohr // check for already open DB 73a1e6784eSAndreas Gohr if($this->db){ 74a1e6784eSAndreas Gohr if($this->dbname == $dbname){ 75a1e6784eSAndreas Gohr // db already open 76a1e6784eSAndreas Gohr return true; 77a1e6784eSAndreas Gohr } 78a1e6784eSAndreas Gohr // close other db 7987fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE) 8087fa2c18Sstretchyboy { 81a1e6784eSAndreas Gohr sqlite_close($this->db); 8287fa2c18Sstretchyboy } 8387fa2c18Sstretchyboy else 8487fa2c18Sstretchyboy { 8587fa2c18Sstretchyboy $this->db->close(); 8687fa2c18Sstretchyboy } 87a1e6784eSAndreas Gohr $this->db = null; 88a1e6784eSAndreas Gohr $this->dbname = ''; 89a1e6784eSAndreas Gohr } 90a1e6784eSAndreas Gohr 91a1e6784eSAndreas Gohr $this->dbname = $dbname; 9287fa2c18Sstretchyboy 9387fa2c18Sstretchyboy $fileextension = '.sqlite'; 9487fa2c18Sstretchyboy 95ff97cc8fSstretchyboy $this->dbfile = $conf['metadir'].'/'.$dbname.$fileextension; 96ff97cc8fSstretchyboy 97ff97cc8fSstretchyboy $init = (!@file_exists($this->dbfile) || ((int) @filesize($this->dbfile)) < 3); 98a1e6784eSAndreas Gohr 9987fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE) 10087fa2c18Sstretchyboy { 101a1e6784eSAndreas Gohr $error=''; 102ff97cc8fSstretchyboy $this->db = sqlite_open($this->dbfile, 0666, $error); 103a1e6784eSAndreas Gohr if(!$this->db){ 104a1e6784eSAndreas Gohr msg("SQLite: failed to open SQLite ".$this->dbname." database ($error)",-1); 105a1e6784eSAndreas Gohr return false; 106a1e6784eSAndreas Gohr } 107a1e6784eSAndreas Gohr 108b5b947d7SAndreas Gohr // register our custom aggregate function 109b5b947d7SAndreas Gohr sqlite_create_aggregate($this->db,'group_concat', 110b5b947d7SAndreas Gohr array($this,'_sqlite_group_concat_step'), 111b5b947d7SAndreas Gohr array($this,'_sqlite_group_concat_finalize'), 2); 11287fa2c18Sstretchyboy } 11387fa2c18Sstretchyboy else 11487fa2c18Sstretchyboy { 115ff97cc8fSstretchyboy $dsn = 'sqlite:'.$this->dbfile; 11687fa2c18Sstretchyboy 11787fa2c18Sstretchyboy try { 11887fa2c18Sstretchyboy $this->db = new PDO($dsn); 11987fa2c18Sstretchyboy } catch (PDOException $e) { 12087fa2c18Sstretchyboy msg("SQLite: failed to open SQLite ".$this->dbname." database (".$e->getMessage().")",-1); 12187fa2c18Sstretchyboy return false; 12287fa2c18Sstretchyboy } 12387fa2c18Sstretchyboy $this->db->sqliteCreateAggregate('group_concat', 124ff97cc8fSstretchyboy array($this,'_pdo_group_concat_step'), 125ff97cc8fSstretchyboy array($this,'_pdo_group_concat_finalize')); 12687fa2c18Sstretchyboy } 127b5b947d7SAndreas Gohr 128a1e6784eSAndreas Gohr $this->_updatedb($init,$updatedir); 129a1e6784eSAndreas Gohr return true; 130a1e6784eSAndreas Gohr } 131a1e6784eSAndreas Gohr 132a1e6784eSAndreas Gohr /** 133a1e6784eSAndreas Gohr * Return the current Database Version 134a1e6784eSAndreas Gohr */ 135a1e6784eSAndreas Gohr function _currentDBversion(){ 136a1e6784eSAndreas Gohr $sql = "SELECT val FROM opts WHERE opt = 'dbversion';"; 137a1e6784eSAndreas Gohr $res = $this->query($sql); 138a1e6784eSAndreas Gohr if(!$res) return false; 139a1e6784eSAndreas Gohr $row = $this->res2row($res,0); 140a1e6784eSAndreas Gohr return (int) $row['val']; 141a1e6784eSAndreas Gohr } 142a1e6784eSAndreas Gohr /** 143a1e6784eSAndreas Gohr * Update the database if needed 144a1e6784eSAndreas Gohr * 145a1e6784eSAndreas Gohr * @param bool $init - true if this is a new database to initialize 146a1e6784eSAndreas Gohr * @param string $updatedir - Database update infos 147a1e6784eSAndreas Gohr */ 1484d9093b4Sstretchyboy function _updatedb($init,$updatedir) 1494d9093b4Sstretchyboy { 150a1e6784eSAndreas Gohr if($init){ 1514d9093b4Sstretchyboy 152a1e6784eSAndreas Gohr $current = 0; 153a1e6784eSAndreas Gohr }else{ 154a1e6784eSAndreas Gohr $current = $this->_currentDBversion(); 155a1e6784eSAndreas Gohr if(!$current){ 156a1e6784eSAndreas Gohr msg('SQLite: no DB version found. '.$this->dbname.' DB probably broken.',-1); 157a1e6784eSAndreas Gohr return false; 158a1e6784eSAndreas Gohr } 159a1e6784eSAndreas Gohr } 160a1e6784eSAndreas Gohr 161a1e6784eSAndreas Gohr // in case of init, add versioning table 162a1e6784eSAndreas Gohr if($init){ 163a1e6784eSAndreas Gohr if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql',0)){ 16487fa2c18Sstretchyboy msg('SQLite: '.$this->dbname.' database upgrade failed for version ', -1); 165a1e6784eSAndreas Gohr return false; 166a1e6784eSAndreas Gohr } 167a1e6784eSAndreas Gohr } 168a1e6784eSAndreas Gohr 169a1e6784eSAndreas Gohr $latest = (int) trim(io_readFile($updatedir.'/latest.version')); 170a1e6784eSAndreas Gohr 171a1e6784eSAndreas Gohr // all up to date? 172a1e6784eSAndreas Gohr if($current >= $latest) return true; 173a1e6784eSAndreas Gohr for($i=$current+1; $i<=$latest; $i++){ 174a1e6784eSAndreas Gohr $file = sprintf($updatedir.'/update%04d.sql',$i); 175a1e6784eSAndreas Gohr if(file_exists($file)){ 176a1e6784eSAndreas Gohr if(!$this->_runupdatefile($file,$i)){ 177a1e6784eSAndreas Gohr msg('SQLite: '.$this->dbname.' database upgrade failed for version '.$i, -1); 178a1e6784eSAndreas Gohr return false; 179a1e6784eSAndreas Gohr } 180a1e6784eSAndreas Gohr } 181a1e6784eSAndreas Gohr } 182a1e6784eSAndreas Gohr return true; 183a1e6784eSAndreas Gohr } 184a1e6784eSAndreas Gohr 185a1e6784eSAndreas Gohr /** 186a1e6784eSAndreas Gohr * Updates the database structure using the given file to 187a1e6784eSAndreas Gohr * the given version. 188a1e6784eSAndreas Gohr */ 189a1e6784eSAndreas Gohr function _runupdatefile($file,$version){ 190a1e6784eSAndreas Gohr $sql = io_readFile($file,false); 191a1e6784eSAndreas Gohr 192a1e6784eSAndreas Gohr $sql = explode(";",$sql); 193a1e6784eSAndreas Gohr array_unshift($sql,'BEGIN TRANSACTION'); 194a1e6784eSAndreas Gohr array_push($sql,"INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')"); 195a1e6784eSAndreas Gohr array_push($sql,"COMMIT TRANSACTION"); 196a1e6784eSAndreas Gohr 197a1e6784eSAndreas Gohr foreach($sql as $s){ 198a1e6784eSAndreas Gohr $s = preg_replace('!^\s*--.*$!m', '', $s); 199a1e6784eSAndreas Gohr $s = trim($s); 200a1e6784eSAndreas Gohr if(!$s) continue; 201fd69a32cSAndreas Gohr 202fd69a32cSAndreas Gohr 203a1e6784eSAndreas Gohr $res = $this->query("$s;"); 204a1e6784eSAndreas Gohr if ($res === false) { 20587fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE) 20687fa2c18Sstretchyboy { 207a1e6784eSAndreas Gohr sqlite_query($this->db, 'ROLLBACK TRANSACTION'); 208a1e6784eSAndreas Gohr return false; 209a1e6784eSAndreas Gohr } 21005f176edSstretchyboy else 21105f176edSstretchyboy { 21205f176edSstretchyboy return false; 21305f176edSstretchyboy } 214a1e6784eSAndreas Gohr } 21587fa2c18Sstretchyboy } 216a1e6784eSAndreas Gohr 217a1e6784eSAndreas Gohr return ($version == $this->_currentDBversion()); 218a1e6784eSAndreas Gohr } 219a1e6784eSAndreas Gohr 220a1e6784eSAndreas Gohr /** 221fd69a32cSAndreas Gohr * Emulate ALTER TABLE 222fd69a32cSAndreas Gohr * 223fd69a32cSAndreas Gohr * The ALTER TABLE syntax is parsed and then emulated using a 224fd69a32cSAndreas Gohr * temporary table 225fd69a32cSAndreas Gohr * 226fd69a32cSAndreas Gohr * @author <jon@jenseng.com> 227fd69a32cSAndreas Gohr * @link http://code.jenseng.com/db/ 228fb394683SAndreas Gohr * @author Andreas Gohr <gohr@cosmocode.de> 229fd69a32cSAndreas Gohr */ 230fd69a32cSAndreas Gohr function _altertable($table,$alterdefs){ 231fb394683SAndreas Gohr 232fb394683SAndreas Gohr // load original table definition SQL 233fd69a32cSAndreas Gohr $result = $this->query("SELECT sql,name,type 234fd69a32cSAndreas Gohr FROM sqlite_master 235fd69a32cSAndreas Gohr WHERE tbl_name = '$table' 236fb394683SAndreas Gohr AND type = 'table'"); 237fb394683SAndreas Gohr 23887fa2c18Sstretchyboy if(($result === false) || ($this->extension == DOKU_EXT_SQLITE && sqlite_num_rows($result)<=0)){ 239fd69a32cSAndreas Gohr msg("ALTER TABLE failed, no such table '".hsc($table)."'",-1); 240fd69a32cSAndreas Gohr return false; 241fd69a32cSAndreas Gohr } 24287fa2c18Sstretchyboy 24387fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 24487fa2c18Sstretchyboy { 245fb394683SAndreas Gohr $row = sqlite_fetch_array($result); 24687fa2c18Sstretchyboy } 24787fa2c18Sstretchyboy else 24887fa2c18Sstretchyboy { 24987fa2c18Sstretchyboy $row = $result->fetch(PDO::FETCH_ASSOC); 25087fa2c18Sstretchyboy } 25187fa2c18Sstretchyboy 25287fa2c18Sstretchyboy if($row === false){ 25387fa2c18Sstretchyboy msg("ALTER TABLE failed, table '".hsc($table)."' had no master data",-1); 25487fa2c18Sstretchyboy return false; 25587fa2c18Sstretchyboy } 25687fa2c18Sstretchyboy 257fd69a32cSAndreas Gohr 258fb394683SAndreas Gohr // prepare temporary table SQL 259fd69a32cSAndreas Gohr $tmpname = 't'.time(); 260fd69a32cSAndreas Gohr $origsql = trim(preg_replace("/[\s]+/"," ", 261fd69a32cSAndreas Gohr str_replace(",",", ", 262fd69a32cSAndreas Gohr preg_replace('/\)$/',' )', 263fd69a32cSAndreas Gohr preg_replace("/[\(]/","( ",$row['sql'],1))))); 264fd69a32cSAndreas Gohr $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'",$tmpname,$origsql,1)),6); 265fd69a32cSAndreas Gohr $createindexsql = array(); 266fb394683SAndreas Gohr 267fb394683SAndreas Gohr // load indexes to reapply later 268fb394683SAndreas Gohr $result = $this->query("SELECT sql,name,type 269fb394683SAndreas Gohr FROM sqlite_master 270fb394683SAndreas Gohr WHERE tbl_name = '$table' 271fb394683SAndreas Gohr AND type = 'index'"); 272fb394683SAndreas Gohr if(!$result){ 273fb394683SAndreas Gohr $indexes = array(); 274fb394683SAndreas Gohr }else{ 275fb394683SAndreas Gohr $indexes = $this->res2arr($result); 276fb394683SAndreas Gohr } 277fb394683SAndreas Gohr 278fb394683SAndreas Gohr 279fd69a32cSAndreas Gohr $i = 0; 280fd69a32cSAndreas Gohr $defs = preg_split("/[,]+/",$alterdefs,-1,PREG_SPLIT_NO_EMPTY); 281fd69a32cSAndreas Gohr $prevword = $table; 282fd69a32cSAndreas Gohr $oldcols = preg_split("/[,]+/",substr(trim($createtemptableSQL),strpos(trim($createtemptableSQL),'(')+1),-1,PREG_SPLIT_NO_EMPTY); 283fd69a32cSAndreas Gohr $newcols = array(); 284fd69a32cSAndreas Gohr 285665af209SAdrian Lang for($i=0;$i<count($oldcols);$i++){ 286fd69a32cSAndreas Gohr $colparts = preg_split("/[\s]+/",$oldcols[$i],-1,PREG_SPLIT_NO_EMPTY); 287fd69a32cSAndreas Gohr $oldcols[$i] = $colparts[0]; 288fd69a32cSAndreas Gohr $newcols[$colparts[0]] = $colparts[0]; 289fd69a32cSAndreas Gohr } 290fd69a32cSAndreas Gohr $newcolumns = ''; 291fd69a32cSAndreas Gohr $oldcolumns = ''; 292fd69a32cSAndreas Gohr reset($newcols); 293fd69a32cSAndreas Gohr while(list($key,$val) = each($newcols)){ 294fd69a32cSAndreas Gohr $newcolumns .= ($newcolumns?', ':'').$val; 295fd69a32cSAndreas Gohr $oldcolumns .= ($oldcolumns?', ':'').$key; 296fd69a32cSAndreas Gohr } 297fd69a32cSAndreas Gohr $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table; 298fd69a32cSAndreas Gohr $dropoldsql = 'DROP TABLE '.$table; 299fd69a32cSAndreas Gohr $createtesttableSQL = $createtemptableSQL; 300fd69a32cSAndreas Gohr 301fd69a32cSAndreas Gohr foreach($defs as $def){ 302fd69a32cSAndreas Gohr $defparts = preg_split("/[\s]+/",$def,-1,PREG_SPLIT_NO_EMPTY); 303fd69a32cSAndreas Gohr $action = strtolower($defparts[0]); 304fd69a32cSAndreas Gohr switch($action){ 305fd69a32cSAndreas Gohr case 'add': 306665af209SAdrian Lang if(count($defparts) < 2){ 307fd69a32cSAndreas Gohr msg('ALTER TABLE: not enough arguments for ADD statement',-1); 308fd69a32cSAndreas Gohr return false; 309fd69a32cSAndreas Gohr } 310fd69a32cSAndreas Gohr $createtesttableSQL = substr($createtesttableSQL,0,strlen($createtesttableSQL)-1).','; 311665af209SAdrian Lang for($i=1;$i<count($defparts);$i++) 312fd69a32cSAndreas Gohr $createtesttableSQL.=' '.$defparts[$i]; 313fd69a32cSAndreas Gohr $createtesttableSQL.=')'; 314fd69a32cSAndreas Gohr break; 315fd69a32cSAndreas Gohr 316fd69a32cSAndreas Gohr case 'change': 317665af209SAdrian Lang if(count($defparts) <= 3){ 318fd69a32cSAndreas Gohr msg('ALTER TABLE: near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').($defparts[2]?' '.$defparts[2]:'').'": syntax error',-1); 319fd69a32cSAndreas Gohr return false; 320fd69a32cSAndreas Gohr } 321fd69a32cSAndreas Gohr 322fd69a32cSAndreas Gohr if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){ 323fd69a32cSAndreas Gohr if($newcols[$defparts[1]] != $defparts[1]){ 324fd69a32cSAndreas Gohr msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1); 325fd69a32cSAndreas Gohr return false; 326fd69a32cSAndreas Gohr } 327fd69a32cSAndreas Gohr $newcols[$defparts[1]] = $defparts[2]; 328fd69a32cSAndreas Gohr $nextcommapos = strpos($createtesttableSQL,',',$severpos); 329fd69a32cSAndreas Gohr $insertval = ''; 330665af209SAdrian Lang for($i=2;$i<count($defparts);$i++) 331fd69a32cSAndreas Gohr $insertval.=' '.$defparts[$i]; 332fd69a32cSAndreas Gohr if($nextcommapos) 333fd69a32cSAndreas Gohr $createtesttableSQL = substr($createtesttableSQL,0,$severpos).$insertval.substr($createtesttableSQL,$nextcommapos); 334fd69a32cSAndreas Gohr else 335fd69a32cSAndreas Gohr $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1)).$insertval.')'; 336fd69a32cSAndreas Gohr } else { 337fd69a32cSAndreas Gohr msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1); 338fd69a32cSAndreas Gohr return false; 339fd69a32cSAndreas Gohr } 340fd69a32cSAndreas Gohr break; 341fd69a32cSAndreas Gohr case 'drop': 342665af209SAdrian Lang if(count($defparts) < 2){ 343fd69a32cSAndreas Gohr msg('ALTER TABLE: near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error',-1); 344fd69a32cSAndreas Gohr return false; 345fd69a32cSAndreas Gohr } 346fd69a32cSAndreas Gohr if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){ 347fd69a32cSAndreas Gohr $nextcommapos = strpos($createtesttableSQL,',',$severpos); 348fd69a32cSAndreas Gohr if($nextcommapos) 349fd69a32cSAndreas Gohr $createtesttableSQL = substr($createtesttableSQL,0,$severpos).substr($createtesttableSQL,$nextcommapos + 1); 350fd69a32cSAndreas Gohr else 351fd69a32cSAndreas Gohr $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1) - 1).')'; 352fd69a32cSAndreas Gohr unset($newcols[$defparts[1]]); 353fd69a32cSAndreas Gohr }else{ 354fd69a32cSAndreas Gohr msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1); 355fd69a32cSAndreas Gohr return false; 356fd69a32cSAndreas Gohr } 357fd69a32cSAndreas Gohr break; 358fd69a32cSAndreas Gohr default: 359fd69a32cSAndreas Gohr msg('ALTER TABLE: near "'.$prevword.'": syntax error',-1); 360fd69a32cSAndreas Gohr return false; 361fd69a32cSAndreas Gohr } 362665af209SAdrian Lang $prevword = $defparts[count($defparts)-1]; 363fd69a32cSAndreas Gohr } 364fd69a32cSAndreas Gohr 365fd69a32cSAndreas Gohr // this block of code generates a test table simply to verify that the 366fd69a32cSAndreas Gohr // columns specifed are valid in an sql statement 367fd69a32cSAndreas Gohr // this ensures that no reserved words are used as columns, for example 368fd69a32cSAndreas Gohr $res = $this->query($createtesttableSQL); 369fd69a32cSAndreas Gohr if($res === false) return false; 370fd69a32cSAndreas Gohr 371fd69a32cSAndreas Gohr $droptempsql = 'DROP TABLE '.$tmpname; 372fd69a32cSAndreas Gohr $res = $this->query($droptempsql); 373fd69a32cSAndreas Gohr if($res === false) return false; 374fd69a32cSAndreas Gohr 375fd69a32cSAndreas Gohr 376fd69a32cSAndreas Gohr $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'",$table,$createtesttableSQL,1)),17); 377fd69a32cSAndreas Gohr $newcolumns = ''; 378fd69a32cSAndreas Gohr $oldcolumns = ''; 379fd69a32cSAndreas Gohr reset($newcols); 380fd69a32cSAndreas Gohr while(list($key,$val) = each($newcols)){ 381fd69a32cSAndreas Gohr $newcolumns .= ($newcolumns?', ':'').$val; 382fd69a32cSAndreas Gohr $oldcolumns .= ($oldcolumns?', ':'').$key; 383fd69a32cSAndreas Gohr } 384fd69a32cSAndreas Gohr 385fd69a32cSAndreas Gohr $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname; 386fd69a32cSAndreas Gohr 387fd69a32cSAndreas Gohr $res = $this->query($createtemptableSQL); //create temp table 388fd69a32cSAndreas Gohr if($res === false) return false; 389fd69a32cSAndreas Gohr $res = $this->query($copytotempsql); //copy to table 390fd69a32cSAndreas Gohr if($res === false) return false; 391fd69a32cSAndreas Gohr $res = $this->query($dropoldsql); //drop old table 392fd69a32cSAndreas Gohr if($res === false) return false; 393fd69a32cSAndreas Gohr 394fd69a32cSAndreas Gohr $res = $this->query($createnewtableSQL); //recreate original table 395fd69a32cSAndreas Gohr if($res === false) return false; 396fd69a32cSAndreas Gohr $res = $this->query($copytonewsql); //copy back to original table 397fd69a32cSAndreas Gohr if($res === false) return false; 398fb394683SAndreas Gohr 399fb394683SAndreas Gohr foreach($indexes as $index){ // readd indexes 400fb394683SAndreas Gohr $res = $this->query($index['sql']); 401fb394683SAndreas Gohr if($res === false) return false; 402fb394683SAndreas Gohr } 403fb394683SAndreas Gohr 404fd69a32cSAndreas Gohr $res = $this->query($droptempsql); //drop temp table 405fd69a32cSAndreas Gohr if($res === false) return false; 406fd69a32cSAndreas Gohr 407fd69a32cSAndreas Gohr return $res; // return a valid resource 408fd69a32cSAndreas Gohr } 409fd69a32cSAndreas Gohr 410fd69a32cSAndreas Gohr /** 411a1e6784eSAndreas Gohr * Execute a query with the given parameters. 412a1e6784eSAndreas Gohr * 413a1e6784eSAndreas Gohr * Takes care of escaping 414a1e6784eSAndreas Gohr * 415a1e6784eSAndreas Gohr * @param string $sql - the statement 416a1e6784eSAndreas Gohr * @param arguments... 417a1e6784eSAndreas Gohr */ 418a1e6784eSAndreas Gohr function query(){ 419a1e6784eSAndreas Gohr if(!$this->db) return false; 420a1e6784eSAndreas Gohr 421a1e6784eSAndreas Gohr // get function arguments 422a1e6784eSAndreas Gohr $args = func_get_args(); 423a1e6784eSAndreas Gohr $sql = trim(array_shift($args)); 424fd69a32cSAndreas Gohr $sql = rtrim($sql,';'); 425a1e6784eSAndreas Gohr 426a1e6784eSAndreas Gohr if(!$sql){ 427a1e6784eSAndreas Gohr msg('No SQL statement given',-1); 428a1e6784eSAndreas Gohr return false; 429a1e6784eSAndreas Gohr } 430a1e6784eSAndreas Gohr 431a1e6784eSAndreas Gohr $argc = count($args); 43212f8c9c7SAdrian Lang if($argc > 0 && is_array($args[0])) { 43312f8c9c7SAdrian Lang $args = $args[0]; 43412f8c9c7SAdrian Lang $argc = count($args); 43512f8c9c7SAdrian Lang } 436a1e6784eSAndreas Gohr 437a1e6784eSAndreas Gohr // check number of arguments 438a1e6784eSAndreas Gohr if($argc < substr_count($sql,'?')){ 439a1e6784eSAndreas Gohr msg('Not enough arguments passed for statement. '. 440a1e6784eSAndreas Gohr 'Expected '.substr_count($sql,'?').' got '. 441a1e6784eSAndreas Gohr $argc.' - '.hsc($sql),-1); 442a1e6784eSAndreas Gohr return false; 443a1e6784eSAndreas Gohr } 444a1e6784eSAndreas Gohr 445a1e6784eSAndreas Gohr // explode at wildcard, then join again 446a1e6784eSAndreas Gohr $parts = explode('?',$sql,$argc+1); 447a1e6784eSAndreas Gohr $args = array_map(array($this,'quote_string'),$args); 448a1e6784eSAndreas Gohr $sql = ''; 449a1e6784eSAndreas Gohr 450a1e6784eSAndreas Gohr while( ($part = array_shift($parts)) !== null ){ 451a1e6784eSAndreas Gohr $sql .= $part; 452a1e6784eSAndreas Gohr $sql .= array_shift($args); 453a1e6784eSAndreas Gohr } 454a1e6784eSAndreas Gohr 455fd69a32cSAndreas Gohr // intercept ALTER TABLE statements 45687fa2c18Sstretchyboy $match = null; 457fd69a32cSAndreas Gohr if(preg_match('/^ALTER\s+TABLE\s+([\w\.]+)\s+(.*)/i',$sql,$match)){ 458fd69a32cSAndreas Gohr return $this->_altertable($match[1],$match[2]); 459fd69a32cSAndreas Gohr } 460fd69a32cSAndreas Gohr 461a1e6784eSAndreas Gohr // execute query 462a1e6784eSAndreas Gohr $err = ''; 46387fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 46487fa2c18Sstretchyboy { 465a1e6784eSAndreas Gohr $res = @sqlite_query($this->db,$sql,SQLITE_ASSOC,$err); 466a1e6784eSAndreas Gohr if($err){ 467d9cff31cSAndreas Gohr msg($err.':<br /><pre>'.hsc($sql).'</pre>',-1); 468a1e6784eSAndreas Gohr return false; 469a1e6784eSAndreas Gohr }elseif(!$res){ 470a1e6784eSAndreas Gohr msg(sqlite_error_string(sqlite_last_error($this->db)). 471d9cff31cSAndreas Gohr ':<br /><pre>'.hsc($sql).'</pre>',-1); 472a1e6784eSAndreas Gohr return false; 473a1e6784eSAndreas Gohr } 47487fa2c18Sstretchyboy } 47587fa2c18Sstretchyboy else 47687fa2c18Sstretchyboy { 47787fa2c18Sstretchyboy $result = false; 478a1e6784eSAndreas Gohr 47987fa2c18Sstretchyboy $res = $this->db->query($sql); 48087fa2c18Sstretchyboy 48187fa2c18Sstretchyboy if(!$res){ 48287fa2c18Sstretchyboy $err = $this->db->errorInfo(); 48387fa2c18Sstretchyboy msg($err[2].':<br /><pre>'.hsc($sql).'</pre>',-1); 48487fa2c18Sstretchyboy return false; 48587fa2c18Sstretchyboy } 48687fa2c18Sstretchyboy } 487a1e6784eSAndreas Gohr return $res; 488a1e6784eSAndreas Gohr } 489a1e6784eSAndreas Gohr 490a1e6784eSAndreas Gohr /** 491a1e6784eSAndreas Gohr * Returns a complete result set as array 492a1e6784eSAndreas Gohr */ 493a1e6784eSAndreas Gohr function res2arr($res){ 494a1e6784eSAndreas Gohr $data = array(); 49587fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 49687fa2c18Sstretchyboy { 497a1e6784eSAndreas Gohr if(!sqlite_num_rows($res)) return $data; 498a1e6784eSAndreas Gohr sqlite_rewind($res); 499e598d748SAdrian Lang while(($row = sqlite_fetch_array($res, SQLITE_ASSOC)) !== false){ 500a1e6784eSAndreas Gohr $data[] = $row; 501a1e6784eSAndreas Gohr } 50287fa2c18Sstretchyboy } 50387fa2c18Sstretchyboy else 50487fa2c18Sstretchyboy { 50587fa2c18Sstretchyboy $data = $res->fetchAll(PDO::FETCH_ASSOC); 50687fa2c18Sstretchyboy if(!count(data)) 50787fa2c18Sstretchyboy { 50887fa2c18Sstretchyboy return false; 50987fa2c18Sstretchyboy } 51087fa2c18Sstretchyboy } 511a1e6784eSAndreas Gohr return $data; 512a1e6784eSAndreas Gohr } 513a1e6784eSAndreas Gohr 514a1e6784eSAndreas Gohr /** 515a1e6784eSAndreas Gohr * Return the wanted row from a given result set as 516a1e6784eSAndreas Gohr * associative array 517a1e6784eSAndreas Gohr */ 518a1e6784eSAndreas Gohr function res2row($res,$rownum=0){ 51987fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 52087fa2c18Sstretchyboy { 521a1e6784eSAndreas Gohr if(!@sqlite_seek($res,$rownum)){ 522a1e6784eSAndreas Gohr return false; 523a1e6784eSAndreas Gohr } 524e598d748SAdrian Lang return sqlite_fetch_array($res, SQLITE_ASSOC); 525a1e6784eSAndreas Gohr } 52687fa2c18Sstretchyboy else 52787fa2c18Sstretchyboy { 52887fa2c18Sstretchyboy //very dirty replication of the same functionality (really must look at cursors) 52987fa2c18Sstretchyboy $data = array(); 53087fa2c18Sstretchyboy //do we need to rewind? 53187fa2c18Sstretchyboy $data = $res->fetchAll(PDO::FETCH_ASSOC); 53287fa2c18Sstretchyboy if(!count(data)) 53387fa2c18Sstretchyboy { 53487fa2c18Sstretchyboy return false; 53587fa2c18Sstretchyboy } 53687fa2c18Sstretchyboy 53787fa2c18Sstretchyboy if(!isset($data[$rownum])) 53887fa2c18Sstretchyboy { 53987fa2c18Sstretchyboy return false; 54087fa2c18Sstretchyboy } 54187fa2c18Sstretchyboy else 54287fa2c18Sstretchyboy { 54387fa2c18Sstretchyboy return $data[$rownum]; 54487fa2c18Sstretchyboy } 54587fa2c18Sstretchyboy } 54687fa2c18Sstretchyboy } 547a1e6784eSAndreas Gohr 548fee3b689Sstretchyboy /** 549fee3b689Sstretchyboy * Return the first value from the first row. 550fee3b689Sstretchyboy */ 551fee3b689Sstretchyboy function res2single($res) 552fee3b689Sstretchyboy { 55387fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 55487fa2c18Sstretchyboy { 555fee3b689Sstretchyboy return sqlite_fetch_single($res); 556fee3b689Sstretchyboy } 55787fa2c18Sstretchyboy else 55887fa2c18Sstretchyboy { 55987fa2c18Sstretchyboy $data = $res->fetchAll(PDO::FETCH_NUM); 56087fa2c18Sstretchyboy if(!count(data)) 56187fa2c18Sstretchyboy { 56287fa2c18Sstretchyboy return false; 56387fa2c18Sstretchyboy } 56487fa2c18Sstretchyboy return $data[0][0]; 56587fa2c18Sstretchyboy } 56687fa2c18Sstretchyboy } 567a1e6784eSAndreas Gohr 568a1e6784eSAndreas Gohr /** 569a1e6784eSAndreas Gohr * Join the given values and quote them for SQL insertion 570a1e6784eSAndreas Gohr */ 571a1e6784eSAndreas Gohr function quote_and_join($vals,$sep=',') { 572a1e6784eSAndreas Gohr $vals = array_map(array('helper_plugin_sqlite','quote_string'),$vals); 573a1e6784eSAndreas Gohr return join($sep,$vals); 574a1e6784eSAndreas Gohr } 575a1e6784eSAndreas Gohr 576a1e6784eSAndreas Gohr /** 577a1e6784eSAndreas Gohr * Run sqlite_escape_string() on the given string and surround it 578a1e6784eSAndreas Gohr * with quotes 579a1e6784eSAndreas Gohr */ 580a1e6784eSAndreas Gohr function quote_string($string){ 58187fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 58287fa2c18Sstretchyboy { 583a1e6784eSAndreas Gohr return "'".sqlite_escape_string($string)."'"; 584a1e6784eSAndreas Gohr } 58587fa2c18Sstretchyboy else 58687fa2c18Sstretchyboy { 58787fa2c18Sstretchyboy return $this->db->quote($string); 58887fa2c18Sstretchyboy } 58987fa2c18Sstretchyboy } 590a1e6784eSAndreas Gohr 591a1e6784eSAndreas Gohr 592b5b947d7SAndreas Gohr /** 593fee3b689Sstretchyboy * Escape string for sql 594fee3b689Sstretchyboy */ 595fee3b689Sstretchyboy function escape_string($str) 596fee3b689Sstretchyboy { 59787fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 59887fa2c18Sstretchyboy { 599fee3b689Sstretchyboy return sqlite_escape_string($str); 600fee3b689Sstretchyboy } 60187fa2c18Sstretchyboy else 60287fa2c18Sstretchyboy { 60387fa2c18Sstretchyboy return trim($this->db->quote($str), "'"); 60487fa2c18Sstretchyboy } 60587fa2c18Sstretchyboy } 606fee3b689Sstretchyboy 607fee3b689Sstretchyboy 608ff97cc8fSstretchyboy 609fee3b689Sstretchyboy /** 610b5b947d7SAndreas Gohr * Aggregation function for SQLite 611b5b947d7SAndreas Gohr * 612b5b947d7SAndreas Gohr * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine 613b5b947d7SAndreas Gohr */ 614ff97cc8fSstretchyboy function _sqlite_group_concat_step(&$context, $string, $separator = ',') { 615ff97cc8fSstretchyboy $context['sep'] = $separator; 616ff97cc8fSstretchyboy $context['data'][] = $string; 617ff97cc8fSstretchyboy } 618ff97cc8fSstretchyboy 619ff97cc8fSstretchyboy /** 620ff97cc8fSstretchyboy * Aggregation function for SQLite 621ff97cc8fSstretchyboy * 622ff97cc8fSstretchyboy * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine 623ff97cc8fSstretchyboy */ 624ff97cc8fSstretchyboy function _sqlite_group_concat_finalize(&$context) { 625ff97cc8fSstretchyboy $context['data'] = array_unique($context['data']); 626ff97cc8fSstretchyboy return join($context['sep'],$context['data']); 627ff97cc8fSstretchyboy } 628ff97cc8fSstretchyboy 629ff97cc8fSstretchyboy 630ff97cc8fSstretchyboy /** 631ff97cc8fSstretchyboy * Aggregation function for SQLite via PDO 632ff97cc8fSstretchyboy * 633ff97cc8fSstretchyboy * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine 634ff97cc8fSstretchyboy */ 635ff97cc8fSstretchyboy function _pdo_group_concat_step(&$context, $rownumber, $string, $separator = ',') { 63687fa2c18Sstretchyboy if(is_null($context)) 63787fa2c18Sstretchyboy { 63887fa2c18Sstretchyboy $context = array( 63987fa2c18Sstretchyboy 'sep' => $separator, 64087fa2c18Sstretchyboy 'data' => array() 64187fa2c18Sstretchyboy ); 64287fa2c18Sstretchyboy } 64387fa2c18Sstretchyboy 644b5b947d7SAndreas Gohr $context['data'][] = $string; 64587fa2c18Sstretchyboy return $context; 646b5b947d7SAndreas Gohr } 647b5b947d7SAndreas Gohr 648b5b947d7SAndreas Gohr /** 649ff97cc8fSstretchyboy * Aggregation function for SQLite via PDO 650b5b947d7SAndreas Gohr * 651b5b947d7SAndreas Gohr * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine 652b5b947d7SAndreas Gohr */ 653ff97cc8fSstretchyboy function _pdo_group_concat_finalize(&$context, $rownumber) 65487fa2c18Sstretchyboy { 65587fa2c18Sstretchyboy if(!is_array($context)) 65687fa2c18Sstretchyboy { 65787fa2c18Sstretchyboy return null; 65887fa2c18Sstretchyboy } 659b5b947d7SAndreas Gohr $context['data'] = array_unique($context['data']); 660b5b947d7SAndreas Gohr return join($context['sep'],$context['data']); 661b5b947d7SAndreas Gohr } 662b5b947d7SAndreas Gohr 663e7112ccbSAdrian Lang /** 664e7112ccbSAdrian Lang * Keep separate instances for every call to keep database connections 665e7112ccbSAdrian Lang */ 666e7112ccbSAdrian Lang function isSingleton() { 667e7112ccbSAdrian Lang return false; 668e7112ccbSAdrian Lang } 669fee3b689Sstretchyboy 670fee3b689Sstretchyboy /** 671fee3b689Sstretchyboy * fetch the next row as zero indexed array 672fee3b689Sstretchyboy */ 673fee3b689Sstretchyboy function res_fetch_array($res) 674fee3b689Sstretchyboy { 67587fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 67687fa2c18Sstretchyboy { 677fee3b689Sstretchyboy return sqlite_fetch_array($res, SQLITE_NUM); 678fee3b689Sstretchyboy } 67987fa2c18Sstretchyboy else 68087fa2c18Sstretchyboy { 68187fa2c18Sstretchyboy return $res->fetch(PDO::FETCH_NUM); 68287fa2c18Sstretchyboy } 68387fa2c18Sstretchyboy } 684fee3b689Sstretchyboy 685fee3b689Sstretchyboy 686fee3b689Sstretchyboy /** 687fee3b689Sstretchyboy * fetch the next row as assocative array 688fee3b689Sstretchyboy */ 689fee3b689Sstretchyboy function res_fetch_assoc($res) 690fee3b689Sstretchyboy { 69187fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 69287fa2c18Sstretchyboy { 693fee3b689Sstretchyboy return sqlite_fetch_array($res, SQLITE_ASSOC); 694fee3b689Sstretchyboy } 69587fa2c18Sstretchyboy else 69687fa2c18Sstretchyboy { 69787fa2c18Sstretchyboy return $res->fetch(PDO::FETCH_ASSOC); 69887fa2c18Sstretchyboy } 69987fa2c18Sstretchyboy } 700fee3b689Sstretchyboy 701fee3b689Sstretchyboy 702fee3b689Sstretchyboy /** 703fee3b689Sstretchyboy * Count the number of records in rsult 704fee3b689Sstretchyboy */ 705fee3b689Sstretchyboy function res2count($res) { 70687fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 70787fa2c18Sstretchyboy { 708fee3b689Sstretchyboy return sqlite_num_rows($res); 709fee3b689Sstretchyboy } 71087fa2c18Sstretchyboy else 71187fa2c18Sstretchyboy { 71287fa2c18Sstretchyboy $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/i'; 71387fa2c18Sstretchyboy if (preg_match($regex, $res->queryString, $output) > 0) { 71487fa2c18Sstretchyboy $stmt = $this->db->query("SELECT COUNT(*) FROM {$output[1]}", PDO::FETCH_NUM); 71587fa2c18Sstretchyboy 71687fa2c18Sstretchyboy return $stmt->fetchColumn(); 71787fa2c18Sstretchyboy } 71887fa2c18Sstretchyboy 71987fa2c18Sstretchyboy return false; 72087fa2c18Sstretchyboy } 72187fa2c18Sstretchyboy } 72224a03f6cSstretchyboy 72324a03f6cSstretchyboy 72424a03f6cSstretchyboy /** 72524a03f6cSstretchyboy * Count the number of records changed last time 72624a03f6cSstretchyboy */ 72724a03f6cSstretchyboy function countChanges($db, $res) 72824a03f6cSstretchyboy { 72987fa2c18Sstretchyboy if($this->extension == DOKU_EXT_SQLITE ) 73087fa2c18Sstretchyboy { 73124a03f6cSstretchyboy return sqlite_changes($db); 73224a03f6cSstretchyboy } 73387fa2c18Sstretchyboy else 73487fa2c18Sstretchyboy { 73587fa2c18Sstretchyboy return $res->rowCount(); 73687fa2c18Sstretchyboy } 73787fa2c18Sstretchyboy } 738a1e6784eSAndreas Gohr} 739a1e6784eSAndreas Gohr 740a1e6784eSAndreas Gohr// vim:ts=4:sw=4:et:enc=utf-8: 741