Location: PHPKode > projects > Gregarius > gregarius/cls/db/db.sqlite.php
<?php

###############################################################################
# Gregarius - A PHP based RSS aggregator.
# Copyright (C) 2003 - 2006 Marco Bonetti
#
###############################################################################
# This program is free software and open source software; you can redistribute
# it and/or modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the License,
# or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
# more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA  or visit
# http://www.gnu.org/licenses/gpl.html
#
###############################################################################
# E-mail:      mbonetti at gmail dot com
# Web page:    http://gregarius.net/
#
###############################################################################
# SQLITE wrapper developped by Olivier Ruffin
# E-mail: blog at ruffin dot info
# Web page: http://www.ruffin.info
#
# to use this wrapper, you must edit dbinit.php and set the constant:
# - DBSERVER with the full path to your sqlite db file
# - DBTYPE to 'sqlite'
# all others DB const are ignored
# if the DB file does not exists, it will be created and default schema will
# be created
# BE SURE that the path to your file is writable by the web server
# Exemple:
# define ('DBTYPE','sqlite');
# define (DBSERVER, "/path/to/my/gregarius.db")
#
# history
# v0.1: release
# v0.2: fixed a parser bug that wrongly replaced some text values
# v0.3: fixed a parser trim bug which lead to items behing marked unread after
#       each refresh of a feed
# v0.4: added alter table and fixed processing of columns update schema
###############################################################################

require_once(dirname(__FILE__) . '/db.php');

class SqliteDB extends DB {

	var $db=false;
	var $dbpath="";
	var $debug=false;

	function  SQLite() {
		parent::DB();
	}

	//// The following are just wrappers of their mysql counterpart for now
	//// maybe one day I shall support other rdbms and add some differentiation
	//// in here.

	function DBConnect($dbpath, $dbuname, $dbpass) {
		if (defined("DBDEBUG")) $this->debug=constant("DBDEBUG");
		$this->dbpath=$dbpath;
		$this->db=@sqlite_open($dbpath,0666,$msg_err);
		if (!$this->db) {
			die( "<h1>Error connecting to the database!</h1>\n"
			."<p>Have you edited dbinit.php and correctly defined "
			."the database path?</p>\n" );
		}
		sqlite_query($this->db,"PRAGMA synchronous = FULL");

		//we emulate via callback some missing MySQL function
		sqlite_create_function($this->db,"year","__cb_sqlite_year",1);
		sqlite_create_function($this->db,"month","__cb_sqlite_month",1);
		sqlite_create_function($this->db,"day","__cb_sqlite_dayofmonth",1);
		sqlite_create_function($this->db,"dayofmonth","__cb_sqlite_dayofmonth",1);
		sqlite_create_function($this->db,"from_unixtime","__cb_sqlite_from_unixtime");
		sqlite_create_function($this->db,"unix_timestamp","__cb_sqlite_unix_timestamp");
		sqlite_create_function($this->db,"now","__cb_sqlite_now");
		sqlite_create_function($this->db,"md5","__cb_sqlite_md5");
	}

	function DBSelectDB($dbname) {
		return true; //sqlite only contain one DB so no need of this
	}

	function alter_table($table,$alterdefs){
		/* function written by jon jenseng and realeased as Opensource
		on his web site: http://code.jenseng.com/db/
		SQlite does not fully support ALTER TABLE, and it support it only
		in SQLite 3.2 +
		So we use this function to execute alter table query
		*/
		if($alterdefs != ''){
			$result = @sqlite_query($this->db,"SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '".$table."' ORDER BY type DESC");
			if(@sqlite_num_rows($result)>0){
				$row = @sqlite_fetch_array($result); //table sql
				$tmpname = 't'.time();
				$origsql = trim(preg_replace("/[\s]+/"," ",str_replace(",",", ",preg_replace("/[\(]/","( ",$row['sql'],1))));
				$createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'",$tmpname,$origsql,1)),6);
				$createindexsql = array();
				$i = 0;
				$defs = preg_split("/[,]+/",$alterdefs,-1,PREG_SPLIT_NO_EMPTY);
				$prevword = $table;
				$oldcols = preg_split("/[,]+/",substr(trim($createtemptableSQL),strpos(trim($createtemptableSQL),'(')+1),-1,PREG_SPLIT_NO_EMPTY);
				$newcols = array();
				for($i=0;$i<sizeof($oldcols);$i++){
					$colparts = preg_split("/[\s]+/",$oldcols[$i],-1,PREG_SPLIT_NO_EMPTY);
					$oldcols[$i] = $colparts[0];
					$newcols[$colparts[0]] = $colparts[0];
				}
				$newcolumns = '';
				$oldcolumns = '';
				reset($newcols);
				while(list($key,$val) = each($newcols)){
					$newcolumns .= ($newcolumns?', ':'').$val;
					$oldcolumns .= ($oldcolumns?', ':'').$key;
				}
				$copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table;
				$dropoldsql = 'DROP TABLE '.$table;
				$createtesttableSQL = $createtemptableSQL;
				foreach($defs as $def){
					$defparts = preg_split("/[\s]+/",$def,-1,PREG_SPLIT_NO_EMPTY);
					$action = strtolower($defparts[0]);
					switch($action){
						case 'add':
							if(sizeof($defparts) <= 2){
								trigger_error('near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error',E_USER_WARNING);
								return false;
							}
							$createtesttableSQL = substr($createtesttableSQL,0,strlen($createtesttableSQL)-1).',';
							for($i=1;$i<sizeof($defparts);$i++) $createtesttableSQL.=' '.$defparts[$i];
							$createtesttableSQL.=')';
							break;
		 				case 'change':
							if(sizeof($defparts) <= 3){
								trigger_error('near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').($defparts[2]?' '.$defparts[2]:'').'": syntax error',E_USER_WARNING);
								return false;
							}
							if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
								if($newcols[$defparts[1]] != $defparts[1]){
									trigger_error('unknown column "'.$defparts[1].'" in "'.$table.'"',E_USER_WARNING);
									return false;
								}
								$newcols[$defparts[1]] = $defparts[2];
								$nextcommapos = strpos($createtesttableSQL,',',$severpos);
								$insertval = '';
								for($i=2;$i<sizeof($defparts);$i++) $insertval.=' '.$defparts[$i];
								if($nextcommapos) $createtesttableSQL = substr($createtesttableSQL,0,$severpos).$insertval.substr($createtesttableSQL,$nextcommapos);
								else $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1)).$insertval.')';
							}
							else{
								trigger_error('unknown column "'.$defparts[1].'" in "'.$table.'"',E_USER_WARNING);
								return false;
							}
							break;
		 				case 'drop':
							if(sizeof($defparts) < 2){
								trigger_error('near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error',E_USER_WARNING);
								return false;
							}
							if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
								$nextcommapos = strpos($createtesttableSQL,',',$severpos);
								if($nextcommapos)$createtesttableSQL = substr($createtesttableSQL,0,$severpos).substr($createtesttableSQL,$nextcommapos + 1);
								else $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1) - 1).')';
								unset($newcols[$defparts[1]]);
							}
							else{
								trigger_error('unknown column "'.$defparts[1].'" in "'.$table.'"',E_USER_WARNING);
								return false;
							}
							break;
		 				default:
							trigger_error('near "'.$prevword.'": syntax error',E_USER_WARNING);
							return false;
					}
					$prevword = $defparts[sizeof($defparts)-1];
				}

				//this block of code generates a test table simply to verify that the columns specifed are valid in an sql statement
				//this ensures that no reserved words are used as columns, for example
				@sqlite_query($this->db,$createtesttableSQL);
				if ($this->rss_sql_error()) return false;

				$droptempsql = 'DROP TABLE '.$tmpname;
				@sqlite_query($this->db,$droptempsql);
				//end block

				$createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'",$table,$createtesttableSQL,1)),17);
				$newcolumns = '';
				$oldcolumns = '';
				reset($newcols);
				while(list($key,$val) = each($newcols)){
					$newcolumns .= ($newcolumns?', ':'').$val;
					$oldcolumns .= ($oldcolumns?', ':'').$key;
				}
				$copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;

				@sqlite_query($this->db,$createtemptableSQL); //create temp table
				@sqlite_query($this->db,$copytotempsql); //copy to table
				@sqlite_query($this->db,$dropoldsql); //drop old table

				@sqlite_query($this->db,$createnewtableSQL); //recreate original table
				@sqlite_query($this->db,$copytonewsql); //copy back to original table
				@sqlite_query($this->db,$droptempsql); //drop temp table
			}
			else{
				trigger_error('no such table: '.$table,E_USER_WARNING);
				return false;
			}
			return true;
  		}
	}

	function is_beetween_quote($search,$query) {
		//check that $search is not inside quote into query
		return preg_match("/('([^']|'')*".preg_quote($search)."([^']|'')*')/is",$query);
	}

	function mysql2sqlite($query) {
  		//converts mysql specific syntax to sqlite syntax
  		//order of replace is important to optimize stuff
  		$doReturn=false;

		//table struct query
		if (preg_match("/^\s*show\s+tables/is",$query)) {
			$query="SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
			$doReturn=true;
  		}
  		else if (preg_match("/^\s*drop\s+table\s+(if\s+exists)/is",$query,$matches)) {
  			$query=str_replace($matches[1],"",$query);
			$query=preg_replace("/(\s+)/is"," ",$query); // no risk to trim data here
			$doReturn=true;
  		}
  		else if (preg_match("/^\s*alter\s+table/is",$query,$matches)) {
			$query=preg_replace("/(COLUMN)/is","",$query);
			$query=preg_replace("/(\s+)/is"," ",$query); // no risk to trim data here
			$doReturn=true;
		}
		else if (preg_match("/^\s*create\s+table/is",$query)) {
			/* We are using a class found into SQLLiteManager project!
			 We need to check the licence of SQLLiteManager and (include it?)
			 Otherwise, we must rebuild this function by ourself
			 */
			include_once("ParsingQuery.class.php");
			$query=preg_replace("/(\s+)/is"," ",$query); // no risk to trim data here
			$parse=new ParsingQuery($query,2); //2=type mysql
			$query=$parse->convertQuery();
			$doReturn=true;
		}

		if ($doReturn || !$query) return $query;

		//count(distinct(x)) is not supported
		if (preg_match("/^\s*select\s+(count\s*\(distinct\s*\(([^)]+)\)\s*\))/is",$query,$matches)) {
			$field=$matches[2];
			$query=str_replace($matches[0],"select count(*)",$query);
			$query=preg_replace("/from\s/is","from (select distinct($field) from ",$query);
			$query.=")";
		}

		/*
		we must be carefull to not change text content but only SQL part
		We do a really dirty hack here:
		we replace all texts by an REFERENCES that will not interfere into the parsing
		$i=0;
		$tabStrings=array();

		//when note description are too big, doing basic str_replace/preg_replace
		//sometime crash... so we try to be less "aggressive"
		$len=strlen($query);
		$quoteOpen=false;
		$j=0;
		$new_query=$buffer=$field="";
		$tabReplace=array();
		for ($i=0;$i<$len;$i++) {
			$current=substr($query,$i,1);
			if ($i<$len-1) $next=substr($query,$i+1,1);
			else $next="";
			if ("$current"=="'") {
				if (!$quoteOpen) {
					if ($next=="'") {
						if ($i<$len-2 && substr($query,$i+2,1)!="'") {
							$new_query.="''";
							$i++;
							continue;
						}
					}
					$buffer=$current;
					$field="__GREGA_${j}__";
					$new_query.=$field;
					$quoteOpen=true;
					$j++;
				}
				else if ("$next"=="'") {
					$buffer.="''";
					$i++;
				}
				else {
					//closing quote
					$buffer.=$current;
					$tabReplace["$field"]=$buffer;
					$buffer="";
					$quoteOpen=false;
				}
			}
			else if ($quoteOpen) $buffer.=$current;
			else $new_query.=$current;
		}
		unset($query);

		$query=preg_replace("/(\s+)/is"," ",$new_query); // we can now trim data, but not before to prevent a change in values

		//we restore all the strings values
		if (is_array($tabReplace) && count($tabReplace)>0) {
			foreach ($tabReplace as $search=>$replace) {
				$query=str_replace($search,$replace,$query);
				unset($tabReplace["$search"]);
			}
			unset($tabReplace);
		}
		*/

		return $query;
	}


	function rss_query ($query, $dieOnError=true, $preventRecursion=false) {
		//we use a wrapper to convert MySQL specific instruction to sqlite
		$result=false;
		$GLOBALS["sqlite_extented_error"]="";
		$errorString = " (none) ";

		$this->debugLog("SQL BEFORE: $query");
		$query=$this->mysql2sqlite($query);
		if (is_array($query)) {
			$this->debugLog("SQL AFTER: ".implode("\n",$query));
		}
		else $this->debugLog("SQL AFTER: $query");

		if (is_string($query) && preg_match("/^alter table/is",$query)) {
			$queryparts = preg_split("/[\s]+/",$query,4,PREG_SPLIT_NO_EMPTY);
			$tablename = $queryparts[2];
			$alterdefs = $queryparts[3];
			if (strtolower($queryparts[1]) != 'table' || $queryparts[2] == '') {
				$error=1;
				$errorString = 'near "'.$queryparts[0] . '": syntax error';
			}
			else{
				set_error_handler(array(&$this, 'rss_catch_error_handler'));
				$result = $this->alter_table($tablename,$alterdefs);
				restore_error_handler();
			}
		}
		else {
			if (is_array($query)) {
				//means that it's a SCHEMA creation so we process each query
				foreach ($query as $sql_query) {
					set_error_handler(array(&$this, 'rss_catch_error_handler'));
					$result =  sqlite_query($this->db,$sql_query);
					restore_error_handler();
					if ($error = $this -> rss_sql_error()) {
						if ($error==1 && preg_match("/DROP TABLE/is",$sql_query)) {
							//not a real error to drop a table which do not exists
							$error=0;
							continue;
						}
						break;
					}
				}
			}
			else {
				set_error_handler(array(&$this, 'rss_catch_error_handler'));
				$result =  sqlite_query($this->db,$query);
				restore_error_handler();
			}
			if ($error = $this -> rss_sql_error()) {
				if ($error==1 && is_string($query) && preg_match("/^\s*DROP TABLE/is",$query)) {
					//not a real error to drop a table which do not exists
					$error=0;
				}
				else $errorString = $this -> rss_sql_error_message();
			}
		}

		if ($error) $this->debugLog("SQL EXEC ERR: $error - $errorString");
		else $this->debugLog("SQL EXEC OK");

		if ($error == 1 && $dieOnError && !$preventRecursion) {
			if (preg_match("/no\s+such\s+table/is",$errorString)) {
				ob_start();

				require_once(dirname(__FILE__) . '/../../init.php');
				require_once(dirname(__FILE__) . '/../../schema.php');
				checkSchema();
				ob_clean();
				return $this -> rss_query ($query, $dieOnError, true);
			}
			else if (preg_match("/(no\s+such\s+column\s*:\s*|no\s+column\s+named\s+)(.+)/is",$errorString,$matches)) {
				ob_start();
				require_once(dirname(__FILE__) . '/../../init.php');
				require_once(dirname(__FILE__) . '/../../schema.php');
				checkSchemaColumns(trim($matches[2]));
				ob_clean();
				return $this -> rss_query ($query, $dieOnError, true);
			}
		}

		if ($error && $dieOnError) {
			die("<p>Failed to execute the SQL query <pre>$query</pre> </p>"
			."<p>Error $error: $errorString</p>");
		}
		return $result;
	}

	function rss_fetch_row($rs) {
		if ($rs) return @sqlite_fetch_array($rs,SQLITE_NUM);
		else return false;
	}

	function rss_fetch_assoc($rs) {
		if ($rs) return @sqlite_fetch_array($rs,SQLITE_ASSOC);
		else return false;
	}

	function rss_num_rows($rs) {
		if ($rs) return @sqlite_num_rows($rs);
		else return false;
	}

	function rss_sql_error() {
		$err_code=@sqlite_last_error($this->db);
		return $err_code;
	}

	function rss_sql_error_message () {
		$errorString="";
		$error=@sqlite_last_error($this->db);
		if ($error) $errorString=@sqlite_error_string($error)." : ".$GLOBALS["sqlite_extented_error"];
		return $errorString;
	}

	function rss_insert_id() {
		if ($this->db) return @sqlite_last_insert_rowid($this->db);
		else return false;
	}

	function rss_real_escape_string($string) {
		return @sqlite_escape_string ($string);
	}

	function debugLog($str) {
		if ($this->debug===true) {
			$file=@dirname($this->dbpath);
			if ($file) {
			$file.="/debug.log";;
			$fp=@fopen($file,"a");
			@fputs($fp,"-== " . date('r') . " =======================================-\n");
			@fputs($fp,trim($str)."\n\n");
			@fclose($fp);
			}
		}
	}//end function

	function rss_is_sql_error($kind) {
		switch ($kind) {
		 case RSS_SQL_ERROR_NO_ERROR:
			return (@sqlite_last_error($this->db) == 0);
			break;
		 case RSS_SQL_ERROR_DUPLICATE_ROW:
			return (@sqlite_last_error($this->db) == 19);
			break;
		 default:
			return false;
		}
	}

	//error handler taken from sqlitemanager project
	function rss_catch_error_handler($errno, $errstr, $errfile, $errline) {
		if (preg_match("/:(.*)/", $errstr, $errorResult)) $GLOBALS["sqlite_extented_error"] = $errorResult[1];
	}

}

//sqlite callbacks to emulate some MySQL func
if (!defined("SQLITE_CALLBACK")) {
	define("SQLITE_CALLBACK",true);
	function __cb_sqlite_month($timestamp) {
		$timestamp=trim($timestamp);
		if (!preg_match("/^[0-9]+$/is",$timestamp)) $ret=strtotime($timestamp);
		else $ret=$timestamp;
		$ret=date("m",$ret);
		__cb_sqlite_debug("MONTH ($timestamp) = $ret");
		return $ret;
	}

	function __cb_sqlite_year($timestamp) {
		$timestamp=trim($timestamp);
		if (!preg_match("/^[0-9]+$/is",$timestamp)) $ret=strtotime($timestamp);
		else $ret=$timestamp;
		$ret=date("Y",$ret);
		__cb_sqlite_debug("YEAR ($timestamp) =  $ret");
		return $ret;
	}

	function __cb_sqlite_dayofmonth($timestamp) {
		$timestamp=trim($timestamp);
		if (!preg_match("/^[0-9]+$/is",$timestamp)) $ret=strtotime($timestamp);
		else $ret=$timestamp;
		$ret=date("d",$ret);
		__cb_sqlite_debug("DAYOFMONTH ($timestamp) = $ret");
		return $ret;
	}

	function __cb_sqlite_from_unixtime($timestamp) {
		$timestamp=trim($timestamp);
		if (!preg_match("/^[0-9]+$/is",$timestamp)) $ret=strtotime($timestamp);
		else $ret=$timestamp;
		$ret=date("Y-m-d H:i:s",$ret);
		__cb_sqlite_debug("FROM_UNIXTIME ($timestamp) = $ret");
		return $ret;
	}

	function __cb_sqlite_unix_timestamp($timestamp="") {
		$timestamp=trim($timestamp);
		if (!$timestamp) $ret=time();
		else if (!preg_match("/^[0-9]+$/is",$timestamp)) $ret=strtotime($timestamp);
		else $ret=$timestamp;
		__cb_sqlite_debug("UNIX_TIMESTAMP ($timestamp) = $ret");
		return $ret;
	}

	function __cb_sqlite_now() {
		return date("Y-m-d H:i:s");
	}

	function __cb_sqlite_md5($str) {
		return md5($str);
	}

	function __cb_sqlite_debug($str) {
		if (defined("DBDEBUG") && constant("DBDEBUG")==true) {
			$file="/tmp/debug_cb.log";
			$fp=@fopen($file,"a");
			@fputs($fp,"-== " . date('r') . " =======================================-\n");
			@fputs($fp,trim($str)."\n\n");
			@fclose($fp);
		}
	}

}
?>
Return current item: Gregarius