<?php
/***********************************************************************

		Copyright (c) 2011, Andrew V. Pleshakov	(hide@address.com)
		All rights reserved.

		This file is part of SPA (SQLite PHP Admin)
		See file LICENSE.txt for details

************************************************************************/

// +------------------------------------------------------------------------+
// | Power SQLite23 class v 2.01                                            |
// +------------------------------------------------------------------------+
// | Copyright (c) 2010-11 Andrew V. Pleshakov                              |
// | Email         hide@address.com                                       |
// | Web                                                                    |
// +------------------------------------------------------------------------+

class SQLite23 {

	var $_version = '0';	// full lib version number (eg, for displaying)
	var $_ver = 0;			// internal version (0,2,3)
	var $error = '';
	var $_file = '';

	var $_db = null;
	var $_command = '';
	var $_result = null;
	var $_type;

	var $_transaction = array();
	var $_openTransaction = false;

    var $_busyTimeout = 30000;

	function SQLite23($file) {
		if(!file_exists($file)) return ; // do nothing yet? see createDB() below
    	$this->_file = $file;
		// trying SQLite v.2
		try {
			$this->_db = new SQLiteDatabase($this->_file);
			$this->_version = sqlite_libversion();
			$this->_ver = 2;
			$this->_type = SQLITE_NUM;
			return;
		} catch (SQLiteException $e) { }
		$fp = fopen($file,'rb');
		$sql3 = 'SQLite format 3';
		$header = fread($fp,strlen($sql3));
		fclose($fp);
		if($header != $sql3) return;
		try {
			$this->_db = new SQLite3($this->_file);
			$ver = SQLite3::version();
			$this->_version = $ver['versionString'];
			$this->_ver = 3;
			$this->_type = SQLITE3_NUM;
			return;
		} catch (Exception $e) { }
	}

	function close() {
		unset($this->_db);
	}

	function createDB($file,$ver) { // creates a new database with preferred version $ver
	// returns FALSE on success, like many POSIX c functions
	   switch($ver) {
		 case 2:
		 // trying SQLite v.2
			try {
				$db = new SQLiteDatabase($file);
				if(!@$db->queryExec('VACUUM',$err)) // for headers info in file
					throw new Exception($err);
				unset($db);
				return false;
			} catch (Exception $e) {
				unset($db);
				@unlink($file);
				return ($e->getMessage());
			}
		 case 3: // trying SQLite v.3
			try {
				$db = new SQLite3($file);
				if(!@$db->query('VACUUM')) // for headers info in file
					throw new Exception($db->lastErrorMsg());
				unset($db);
				return false;
			} catch (Exception $e) {
				unset($db);
				@unlink($file);
				return ($e->getMessage());
			}
		 default: // should not be
			return 'A version should be 2 or 3 by now';
	   }
	}

	function getTables() {	// return all table names from the table as an array
		$this->query("SELECT name FROM SQLITE_MASTER WHERE type = 'table' ORDER by name");
		$tabs = array();
		while($row = $this->fetchArray())
			$tabs[] = $row[0];
		return $tabs;
	}

	function getTableColumns($tablename) {
		$this->query("PRAGMA table_info('$tablename')");
		$type = $this->_type;//reserve type
		$tInfo = array();
		while($row = $this->fetchArray('ASSOC')) 
			$tInfo[] = $row;// for omitting an empty row
		$this->_type = $type;
		foreach($tInfo as $k => $v) {
			$v['type'] = strtoupper($v['type']);
			$v['notnull'] = intval($v['notnull']) ? 'NOT NULL' : 'NULL';
			if(intval($v['pk'])) $v['type'] .= ' PRIMARY KEY';
			$tInfo[$k] = $v;
		}
		return $tInfo;
	}

	function getIndexes($tablename) {
		$this->query("PRAGMA index_list('$tablename')");
		$type = $this->_type;//reserve type
		$tInfo = array();
		while($row = $this->fetchArray('ASSOC')) 
			$tInfo[] = $row;// for omitting an empty row
		$this->_type = $type;
		return $tInfo;
	}

	function getSchema($tablename) {	// extract and parses table schema for CREATE aueries
		$this->query("SELECT sql FROM sqlite_master WHERE tbl_name='$tablename' AND type='table'");
		while($r = $this->fetchArray())
			$schema = $r[0];
		$schema = str_replace("\r\n","\n",$schema);
		$schema = str_replace("\r","\n",$schema);
		$lastchar = ' ';
		$n = strlen($schema);
		$rem = $onvalues = $canhavacomma = false;

		$aFields = array();
		$field = '';
		for ($i = 0; $i < $n; $i++)
		{
			if($rem && $schema[$i] != "\n") continue;
			switch ($schema[$i]) {
				case "\n":
					$rem = false;
					break;
				case '-':
					if(($schema[$i+1] == '-') && preg_match('/(\W|-)/',$lastchar)) {
						$rem = true;
						break;
					}
					if ($onvalues) $field .= $schema[$i];	
					break;
				case '(':
					if (!$onvalues)
						$onvalues = true;
					else {
						$canhavacomma = true;
						$field .= $schema[$i];
					}
					break;
				case ')':
					if ($canhavacomma) {
						$canhavacomma = false;
						$field .= $schema[$i];
					}
					else {
						$onvalues = false;
						$aFields[] = trim($field);
					}
					break;
				case ',':
					if (!$canhavacomma)	{
						$aFields[] = trim($field);
						$field = '';
					}
					else
						$field .= $schema[$i];
					break;
				default:
					if ($onvalues)
						$field .= $schema[$i];
			}
			$lastchar = $schema[$i];
		}
		return $aFields;
	}

	function query($query) {
		$this->_command = $query;
		switch($this->_ver) {
			case 2:
				$this->_result = @$this->_db->query($query,$this->_type,$err);
				if (!$this->_result) {
					if($err) $this->error = $err;
					else $this->error = sqlite_error_string($this->_db->lastError());
					return false;
				}
				return true;
			case 3:
				$this->_result = @$this->_db->query($query);
				if (!$this->_result) {
					$this->error = $this->_db->lastErrorMsg();
					return false;
				}
				return true;
			default:
				return false;
		}
	}
	
	function exec($query) {	// Execute a resultless query
		$this->_command = $query;
		switch($this->_ver) {
			case 2:
				if(!@$this->_db->queryExec($query,$err)) {
					if($err) $this->error = $err;
					else $this->error = sqlite_error_string($this->_db->lastError());
					return false;
				}
				return true;
			case 3:
				if(!@$this->_db->exec($query)) {
					$this->error = $this->_db->lastErrorMsg();
					return false;
				}
				return true;
			default:
				return false;
		}
	}
	
	public function lastQuery() {	// last Executed query
		return $this->_command;
	}

	function single($query, $row = false) {
	//Execute a single query with immediate result of a first column or ent.row
		$this->_command = $query;
		switch($this->_ver) {
			case 2:
				if(!($res = @$this->_db->singleQuery($query,$row))) {
					$this->error = sqlite_error_string($this->_db->lastError());
					return false;
				}
				return $res;
			case 3:
				if(!($res = @$this->_db->querySingle($query,$row))) {
					$this->error = $this->_db->lastErrorMsg();
					return false;
				}
				return $res;
			default:
				return false;
		}
	}

	function fetchArray($type = null) {
		if(isset($type)) $this->setType($type);
		if($this->_ver == 2)
			return @$this->_result->fetch($this->_type);
		else
			return @$this->_result->fetchArray($this->_type);
	}

	function setType($type) {
		$type = strtolower($type);
		switch($this->_ver) {
		 case 2:
			if ($type == 'assoc') $this->_type = SQLITE_ASSOC;
			if ($type == 'num') $this->_type = SQLITE_NUM;
			if ($type == 'both') $this->_type = SQLITE_BOTH;
			break;
		 case 3:
			if ($type == 'assoc') $this->_type = SQLITE3_ASSOC;
			if ($type == 'num') $this->_type = SQLITE3_NUM;
			if ($type == 'both') $this->_type = SQLITE3_BOTH;
			break;
		}
	}

	function lastInsertId() {
		if($this->_ver == 2)
			return $this->_db->lastInsertRowid();
		else
			return $this->_db->lastInsertRowID();
	}

 	function affectedRows() {
		return $this->_db->changes();
	}

	function numRows($table = '') {
		if ($table) {
			$res = $this->_db->query("SELECT COUNT(*) FROM '$table'");
			if($this->_ver == 2)
				$row = $res->fetch();
			else
				$row = $res->fetchArray();
			return $row[0];
		}
		else {
			if ($this->_ver == 2) return $this->_result->numRows();
			return 0;	// numRows not implemented in SQLite3Result class?
		}
	}

	function numFields() {
		if($this->_ver == 2)
			return $this->_result->numFields();
		else
			return $this->_result->numColumns();
	}

	function fieldName($i) {
		if($this->_ver == 2)
			return $this->_result->fieldName($i);
		else
			return $this->_result->columnName($i);
	}

	function escapeString($string) {
		if($this->_ver == 2)
			return sqlite_escape_string($string);
		else
			return $this->_db->escapeString($string);
	}

	function libVersion() {
		return $this->_version;
	}

	function dbInfo() {
		$dbInfo['size'] = filesize($this->_file);
		$dbInfo['last_mod'] = date("Y-m-d H:i", filemtime($this->_file));
		$dbInfo['last_acc'] = date("Y-m-d H:i", fileatime($this->_file));
		$dbInfo['owner'] = fileowner($this->_file);
		clearstatcache();
		return $dbInfo;
	}

	function setWaitingTime($milliseconds) {
		$this->_busyTimeout = $milliseconds;
		if($this->_ver == 2)
			$this->_db->busyTimeout($milliseconds);
		elseif(phpversion() >= '5.3.3')	// not implemented eariler
			$this->_db->busyTimeout($milliseconds);
	}

	function vacuum() {
		$this->query('VACUUM');
	}

	function turboMode()
	{
		$this->query("PRAGMA default_synchronous = OFF");
	}

	function addFunction($name, $originalFunction, $num_args = -1) {
		$this->_db->createFunction($name, $originalFunction, $num_args);
	}

};