1<?php 2 3 4/** 5 * abstract for the adapter that gives access to different sqlite backends 6 */ 7abstract class helper_plugin_sqlite_adapter { 8 protected $dbname = ''; 9 protected $fileextension; 10 protected $dbfile; 11 protected $db = null; 12 protected $data = array(); 13 protected $nativealter = false; 14 15 /** 16 * return name of adapter 17 * 18 * @return string backend name as defined in helper.php 19 */ 20 public abstract function getName(); 21 22 /** 23 * Should the nativ ALTER TABLE implementation be used instead of workaround? 24 * 25 * @param bool $set 26 */ 27 public function setUseNativeAlter($set) { 28 $this->nativealter = $set; 29 } 30 31 /** 32 * The file extension used by the adapter 33 * 34 * @return string 35 */ 36 public function getFileextension() { 37 return $this->fileextension; 38 } 39 40 /** 41 * @return string database name when set, otherwise an empty string 42 */ 43 public function getDbname() { 44 return $this->dbname; 45 } 46 47 /** 48 * Gives direct access to the database 49 * 50 * This is only usefull for the PDO Adapter as this gives direct access to the PDO object 51 * nontheless it should generally not be used 52 * 53 * @return null|PDO|resource 54 */ 55 public function getDb() { 56 return $this->db; 57 } 58 59 /** 60 * Returns the path to the database file (if initialized) 61 * 62 * @return string 63 */ 64 public function getDbFile() { 65 return $this->dbfile; 66 } 67 68 /** 69 * Registers a User Defined Function for use in SQL statements 70 * 71 * @param string $function_name The name of the function used in SQL statements 72 * @param callable $callback Callback function to handle the defined SQL function 73 * @param int $num_args Number of arguments accepted by callback function 74 */ 75 public abstract function create_function($function_name, $callback, $num_args); 76 77 /** 78 * Initializes and opens the database 79 * Needs to be called right after loading this helper plugin 80 * 81 * @param string $dbname - name of database 82 * @param bool $init - true if this is a new database to initialize 83 * @param bool $sqliteupgrade 84 * @return bool 85 */ 86 public function initdb($dbname, &$init, $sqliteupgrade = false) { 87 global $conf; 88 89 // check for already open DB 90 if($this->db) { 91 if($this->dbname == $dbname) { 92 // db already open 93 return true; 94 } 95 // close other db 96 $this->closedb(); 97 98 $this->db = null; 99 $this->dbname = ''; 100 } 101 102 $this->dbname = $dbname; 103 $this->dbfile = $conf['metadir'].'/'.$dbname.$this->fileextension; 104 105 $init = (!@file_exists($this->dbfile) || ((int) @filesize($this->dbfile)) < 3); 106 return $this->opendb($init, $sqliteupgrade); 107 } 108 109 /** 110 * Checks of given dbfile has Sqlite format 3 111 * 112 * first line tell the format of db file http://marc.info/?l=sqlite-users&m=109383875408202 113 */ 114 public static function isSqlite3db($dbfile) { 115 $firstline = @file_get_contents($dbfile, false, null, 0, 15); 116 return $firstline == 'SQLite format 3'; 117 } 118 119 /** 120 * open db connection 121 * 122 * @param bool $init true if this is a new database to initialize 123 * @param bool $sqliteupgrade when connecting to a new database: 124 * false stops connecting to an .sqlite3 db when an .sqlite2 db already exist and warns instead, 125 * true let connecting so upgrading is possible 126 * @return bool true if connecting to sqlite3 db succeed 127 */ 128 protected abstract function opendb($init, $sqliteupgrade = false); 129 130 /** 131 * close current db connection 132 */ 133 protected abstract function closedb(); 134 135 /** 136 * Execute a query with the given parameters. 137 * 138 * Takes care of escaping 139 * 140 * @param array $args Array with sql string and parameters 141 * @return bool|\PDOStatement|\SQLiteResult 142 */ 143 public function query($args) { 144 if(!$this->db) return false; 145 146 //reset previous result 147 $this->data = array(); 148 149 $sql = $this->prepareSql($args); 150 if(!$sql) return false; 151 152 // intercept ALTER TABLE statements 153 if(!$this->nativealter) { 154 $match = null; 155 if(preg_match('/^ALTER\s+TABLE\s+([\w\.]+)\s+(.*)/i', $sql, $match)) { 156 return $this->_altertable($match[1], $match[2]); 157 } 158 } 159 160 // execute query 161 return $this->executeQuery($sql); 162 } 163 164 /** 165 * Execute a raw query 166 * 167 * @param $sql.. 168 */ 169 public abstract function executeQuery($sql); 170 171 /** 172 * Prepare a query with the given arguments. 173 * 174 * Takes care of escaping 175 * 176 * @param array $args 177 * array of arguments: 178 * - string $sql - the statement 179 * - arguments... 180 * @return bool|string 181 */ 182 public function prepareSql($args) { 183 184 $sql = trim(array_shift($args)); 185 $sql = rtrim($sql, ';'); 186 187 if(!$sql) { 188 if(!defined('SIMPLE_TEST')) msg('No SQL statement given', -1); 189 return false; 190 } 191 192 $argc = count($args); 193 if($argc > 0 && is_array($args[0])) { 194 $args = $args[0]; 195 $argc = count($args); 196 } 197 198 // check number of arguments 199 $qmc = substr_count($sql, '?'); 200 if($argc < $qmc) { 201 if(!defined('SIMPLE_TEST')) msg( 202 'Not enough arguments passed for statement. '. 203 'Expected '.$qmc.' got '. 204 $argc.' - '.hsc($sql), -1 205 ); 206 return false; 207 }elseif($argc > $qmc){ 208 if(!defined('SIMPLE_TEST')) msg( 209 'Too much arguments passed for statement. '. 210 'Expected '.$qmc.' got '. 211 $argc.' - '.hsc($sql), -1 212 ); 213 return false; 214 } 215 216 // explode at wildcard, then join again 217 $parts = explode('?', $sql, $argc + 1); 218 $args = array_map(array($this, 'quote_string'), $args); // TODO 219 $sql = ''; 220 221 while(($part = array_shift($parts)) !== null) { 222 $sql .= $part; 223 $sql .= array_shift($args); 224 } 225 226 return $sql; 227 } 228 229 /** 230 * Emulate ALTER TABLE 231 * 232 * The ALTER TABLE syntax is parsed and then emulated using a 233 * temporary table 234 * 235 * @author <jon@jenseng.com> 236 * @link http://code.jenseng.com/db/ 237 * @author Andreas Gohr <gohr@cosmocode.de> 238 */ 239 protected function _altertable($table, $alterdefs) { 240 241 // load original table definition SQL 242 $result = $this->query( 243 array( 244 "SELECT sql,name,type 245 FROM sqlite_master 246 WHERE tbl_name = '$table' 247 AND type = 'table'" 248 ) 249 ); 250 251 if(($result === false) || ($this->getName() == DOKU_EXT_SQLITE && $this->res2count($result) <= 0)) { 252 msg("ALTER TABLE failed, no such table '".hsc($table)."'", -1); 253 return false; 254 } 255 256 $row = $this->res_fetch_assoc($result); 257 258 if($row === false) { 259 msg("ALTER TABLE failed, table '".hsc($table)."' had no master data", -1); 260 return false; 261 } 262 263 // prepare temporary table SQL 264 $tmpname = 't'.time(); 265 $origsql = trim( 266 preg_replace( 267 "/[\s]+/", " ", 268 str_replace( 269 ",", ", ", 270 preg_replace( 271 '/\)$/', ' )', 272 preg_replace("/[\(]/", "( ", $row['sql'], 1) 273 ) 274 ) 275 ) 276 ); 277 $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'", $tmpname, $origsql, 1)), 6); 278 279 // load indexes to reapply later 280 $result = $this->query( 281 array( 282 "SELECT sql,name,type 283 FROM sqlite_master 284 WHERE tbl_name = '$table' 285 AND type = 'index'" 286 ) 287 ); 288 if(!$result) { 289 $indexes = array(); 290 } else { 291 $indexes = $this->res2arr($result); 292 } 293 294 $defs = preg_split("/[,]+/", $alterdefs, -1, PREG_SPLIT_NO_EMPTY); 295 $prevword = $table; 296 $oldcols = preg_split("/[,]+/", substr(trim($createtemptableSQL), strpos(trim($createtemptableSQL), '(') + 1), -1, PREG_SPLIT_NO_EMPTY); 297 $newcols = array(); 298 299 for($i = 0; $i < count($oldcols); $i++) { 300 $colparts = preg_split("/[\s]+/", $oldcols[$i], -1, PREG_SPLIT_NO_EMPTY); 301 $oldcols[$i] = $colparts[0]; 302 $newcols[$colparts[0]] = $colparts[0]; 303 } 304 $newcolumns = ''; 305 $oldcolumns = ''; 306 foreach ($newcols as $key => $val) { 307 $newcolumns .= ($newcolumns ? ', ' : '').$val; 308 $oldcolumns .= ($oldcolumns ? ', ' : '').$key; 309 } 310 $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table; 311 $dropoldsql = 'DROP TABLE '.$table; 312 $createtesttableSQL = $createtemptableSQL; 313 314 foreach($defs as $def) { 315 $defparts = preg_split("/[\s]+/", $def, -1, PREG_SPLIT_NO_EMPTY); 316 $action = strtolower($defparts[0]); 317 switch($action) { 318 case 'add': 319 if(count($defparts) < 2) { 320 msg('ALTER TABLE: not enough arguments for ADD statement', -1); 321 return false; 322 } 323 $createtesttableSQL = substr($createtesttableSQL, 0, strlen($createtesttableSQL) - 1).','; 324 for($i = 1; $i < count($defparts); $i++) 325 $createtesttableSQL .= ' '.$defparts[$i]; 326 $createtesttableSQL .= ')'; 327 break; 328 329 case 'change': 330 if(count($defparts) <= 3) { 331 msg('ALTER TABLE: near "'.$defparts[0].($defparts[1] ? ' '.$defparts[1] : '').($defparts[2] ? ' '.$defparts[2] : '').'": syntax error', -1); 332 return false; 333 } 334 335 if($severpos = strpos($createtesttableSQL, ' '.$defparts[1].' ')) { 336 if($newcols[$defparts[1]] != $defparts[1]) { 337 msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"', -1); 338 return false; 339 } 340 $newcols[$defparts[1]] = $defparts[2]; 341 $nextcommapos = strpos($createtesttableSQL, ',', $severpos); 342 $insertval = ''; 343 for($i = 2; $i < count($defparts); $i++) 344 $insertval .= ' '.$defparts[$i]; 345 if($nextcommapos) 346 $createtesttableSQL = substr($createtesttableSQL, 0, $severpos).$insertval.substr($createtesttableSQL, $nextcommapos); 347 else 348 $createtesttableSQL = substr($createtesttableSQL, 0, $severpos - (strpos($createtesttableSQL, ',') ? 0 : 1)).$insertval.')'; 349 } else { 350 msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"', -1); 351 return false; 352 } 353 break; 354 case 'drop': 355 if(count($defparts) < 2) { 356 msg('ALTER TABLE: near "'.$defparts[0].($defparts[1] ? ' '.$defparts[1] : '').'": syntax error', -1); 357 return false; 358 } 359 if($severpos = strpos($createtesttableSQL, ' '.$defparts[1].' ')) { 360 $nextcommapos = strpos($createtesttableSQL, ',', $severpos); 361 if($nextcommapos) 362 $createtesttableSQL = substr($createtesttableSQL, 0, $severpos).substr($createtesttableSQL, $nextcommapos + 1); 363 else 364 $createtesttableSQL = substr($createtesttableSQL, 0, $severpos - (strpos($createtesttableSQL, ',') ? 0 : 1) - 1).')'; 365 unset($newcols[$defparts[1]]); 366 } else { 367 msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"', -1); 368 return false; 369 } 370 break; 371 default: 372 msg('ALTER TABLE: near "'.$prevword.'": syntax error', -1); 373 return false; 374 } 375 $prevword = $defparts[count($defparts) - 1]; 376 } 377 378 // this block of code generates a test table simply to verify that the 379 // columns specifed are valid in an sql statement 380 // this ensures that no reserved words are used as columns, for example 381 $res = $this->query(array($createtesttableSQL)); 382 if($res === false) return false; 383 384 $droptempsql = 'DROP TABLE '.$tmpname; 385 $res = $this->query(array($droptempsql)); 386 if($res === false) return false; 387 388 $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'", $table, $createtesttableSQL, 1)), 17); 389 $newcolumns = ''; 390 $oldcolumns = ''; 391 foreach($newcols as $key => $val) { 392 $newcolumns .= ($newcolumns ? ', ' : '').$val; 393 $oldcolumns .= ($oldcolumns ? ', ' : '').$key; 394 } 395 396 $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname; 397 398 $res = $this->query(array($createtemptableSQL)); //create temp table 399 if($res === false) return false; 400 $res = $this->query(array($copytotempsql)); //copy to table 401 if($res === false) return false; 402 $res = $this->query(array($dropoldsql)); //drop old table 403 if($res === false) return false; 404 405 $res = $this->query(array($createnewtableSQL)); //recreate original table 406 if($res === false) return false; 407 $res = $this->query(array($copytonewsql)); //copy back to original table 408 if($res === false) return false; 409 410 foreach($indexes as $index) { // readd indexes 411 $res = $this->query(array($index['sql'])); 412 if($res === false) return false; 413 } 414 415 $res = $this->query(array($droptempsql)); //drop temp table 416 if($res === false) return false; 417 418 return $res; // return a valid resource 419 } 420 421 /** 422 * Join the given values and quote them for SQL insertion 423 */ 424 public function quote_and_join($vals, $sep = ',') { 425 $vals = array_map(array($this, 'quote_string'), $vals); 426 return join($sep, $vals); 427 } 428 429 /** 430 * Run sqlite_escape_string() on the given string and surround it 431 * with quotes 432 */ 433 public abstract function quote_string($string); 434 435 /** 436 * Escape string for sql 437 */ 438 public abstract function escape_string($str); 439 440 /** 441 * Close the result set and it's cursors 442 * 443 * @param $res 444 */ 445 public abstract function res_close($res); 446 447 /** 448 * Returns a complete result set as array 449 */ 450 public abstract function res2arr($res, $assoc = true); 451 452 /** 453 * Return the next row of the given result set as associative array 454 */ 455 public abstract function res2row($res); 456 457 /** 458 * Return the first value from the next row. 459 */ 460 public abstract function res2single($res); 461 462 /** 463 * fetch the next row as zero indexed array 464 */ 465 public abstract function res_fetch_array($res); 466 467 /** 468 * fetch the next row as assocative array 469 */ 470 public abstract function res_fetch_assoc($res); 471 472 /** 473 * Count the number of records in result 474 * 475 * This function is really inperformant in PDO and should be avoided! 476 */ 477 public abstract function res2count($res); 478 479 /** 480 * Count the number of records changed last time 481 * 482 * Don't work after a SELECT statement in PDO 483 */ 484 public abstract function countChanges($res); 485 486 /** 487 * Do not serialize the DB connection 488 * 489 * @return array 490 */ 491 public function __sleep() { 492 $this->db = null; 493 return array_keys(get_object_vars($this)); 494 } 495 496 /** 497 * On deserialization, reinit database connection 498 */ 499 public function __wakeup() { 500 $init = false; 501 $this->initdb($this->dbname, $init); 502 } 503 504} 505 506// vim:ts=4:sw=4:et:enc=utf-8: 507