Login   Register  
PHP Classes
elePHPant
Icontem

File: sqlite3.inc.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Fanache A. Remus  >  SQL class for PHP  >  sqlite3.inc.php  >  Download  
File: sqlite3.inc.php
Role: Class source
Content type: text/plain
Description: SQLite ver. 3 class
Class: SQL class for PHP
Access different databases with the same interface
Author: By
Last change: - rename sql class from SQL_DB to sqltype_DB (where sqltype is MYSQL or MYSQLI or SQLITE2 or SQLITE3 or MSSQL)
(this is required if you want to use 2 different sql databases)
(Note: after update to this revision you need to initialize class with specific name, see examples)
- corrected a bug on mysql/mysqli class (bug sql port if is different from 3306)
- add $this->sql_query_monitor (use to monitor query)
(Note: before this revision if you use $this->sql_tracer you get all log from class, include filters and other data and now with $this->sql_query_monitor you can see now only executed queries)
- corrected a bug on all classes on log
(before this update if is to many queries you get an memory error, and now you can avoid this problem by using $this-sql_debug = false;)
- add $this->query_log() (protected function - use $this->sql_query_log instead)
- corrected a bug on $this->real_escape() function
- corrected a bug on $this->free_result() function
- add example_2_db.php (example for using 2 databases of same type)
- add example_from_mysqli_to_sqlite3.php (example for 2 different databases)
- update example.php
- added new function for mysql::set_time_zone
- code review (set all messages and comments on english language)
- bug fixed on MSSQL::real_escape
- added new function array_sqlfilter() (improvement)
- change function name from real_escape_string to real_escape
- change function name from sql_prepare_data to prepare_data
- corect bug on real_escape function
- corect bug on prepare_data function
Date: 3 months ago
Size: 11,807 bytes
 

Contents

Class file image Download
<?php
/**
 * @Package: FAR-PHP CMS
 * @Author: Fanache A. Remus (Birkoff)
 * @$Date$
 * @Contact: www.farsoft.far-php.ro - contact@far-php.ro
 * @$Rev$
 * @$Id$
 *
 * SQLite 3 class
 *
**/

// This file can not be accessed independently from index
if(!defined('FAR_ANTIHACK')) { header('HTTP/1.0 403 Forbidden'); header('Status: 403 Forbidden'); die("<br>You don't have access here."); }

class SQLITE3_DB {
	
	// initializing the internal variables
	// monitoring data
	var $sql_tracer		= array();
	// if true provides information on every function called, otherwise only provides information of queries
	var $sql_debug		= false;
	// connecting
	var $conn			= NULL;
	// variable that holds the results returned
	var $result			= false;
	// db info
	var $db_server		= 'sqlite3.db';
	var $db_port		= '0666';
	var $db_username	= '';
	var $db_password	= '';
	var $db_name		= '';
	var $db_encoding	= 'utf8';
    var $db_time_zone   = 'Europe/Bucharest';
	var $db_persist		= false;
	
	/**
	 * class instance
	 *
	 * @param str $db_server	- sql server name
	 * @param int $db_port		- sql server connection port
	 * @param str $db_username	- sql username
	 * @param str $db_password	- sql password
	 * @param str $db_name		- database name
	 * @param str $db_encoding	- encoding data type
	 * @param bool $db_persist	- connection type
	 *
	**/
	function SQLITE3_DB ($db_server='', $db_port=0666, $db_username='', $db_password='', $db_name='', $db_encoding='', $db_persist=false) {
		if ( ! empty($db_server) )
			$this->db_server = $db_server;
		if ( ! empty($db_port) && is_numeric($db_port) )
			$this->db_port = $db_port;
		if ( ! empty($db_username) )
			$this->db_username = $db_username;
		if ( ! empty($db_password) ) 
			$this->db_password = $db_password;
		if ( ! empty($db_name) ) 
			$this->db_name = $db_name;
		if ( ! empty($db_encoding) )
			$this->db_encoding = $db_encoding;
		if ( $db_persist === true )
			$this->db_persist = $db_persist;
		$this->open();
	}
	
	/**
	 * connecting to sql server
	 * database selection
	 * encoding type setting
	 *
	 * @return resource $conn - false on failure
	 *
	**/
	function open() {
		if ( $this->connect() === false )
			return false;
		return $this->conn;
	}
	
	/**
	 * connecting to the database
	 *
	 * @param str $db_server
	 * @param int $db_port
	 * @param str $db_username
	 * @param str $db_password
	 *
	 * @return bool
	 *
	**/
	function connect($db_server='', $db_port=0666, $db_username='', $db_password='') {
		if ( ! empty($db_server) )
			$this->db_server = $db_server;
		if ( ! empty($db_port) && is_numeric($db_port) )
			$this->db_port = $db_port;
		if ( ! empty($db_username) )
			$this->db_username = $db_username;
		if ( ! empty($db_password) ) 
			$this->db_password = $db_password;
		// if it is already connected
		if( is_resource($this->conn) )
			return true;
		// Choose the appropriate connect function
		if ( $this->db_persist )			
			$this->conn = new SQLite3($this->db_server);
		else
			$this->conn = new SQLite3($this->db_server);
		// Connect to the database server
		if( $this->conn === false) {
			if ( $this->sql_debug )
				$this->sql_monitor('connect', 'connect', '', $this->conn, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
			return false;
		} else {
			if ( $this->sql_debug )
				$this->sql_monitor('connect', 'connect', '', $this->conn, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
			return true;
		}
	}

	/**
	 * database selection
	 *
	 * @param str $db_name
	 *
	 * @return bool
	 *
	**/
	function select_db($db_name='') {
		return true; // does not apply to sqlite3
	}
	
	/**
	 * charset setting used
	 *
	 * @param str $db_encoding
	 *
	 * @return bool
	 *
	**/
	function set_charset($db_encoding='') {
		return true; // does not apply to sqlite3
	}

    /**
     * set time zone
     * 
     * @param str $time_zone
     * 
     * @return bool 
     */
    function set_time_zone($time_zone='') {
        return true; // does not apply to sqlite3
    }

	/**
	 * monitoring data and errors
	 *
	 * @param str $function_name	- function name that is monitoring
	 * @param str $param_name		- function parameter name
	 * @param obj $param_val		- function parameter value
	 * @param obj $return_val		- value returned
	 * @param int $line				- line number where you can find values
	 * @param str $file				- file name
	 * @param str $error			- reported error
	 *
	**/
	function sql_monitor($function_name, $param_name, $param_val, $return_val, $line, $file, $error_nr=0, $error_val=0) {

		$this->sql_tracer[] = array(
			'function_name' => $function_name,
			'param_name'	=> $param_name,
			'param_val'		=> $param_val,
			'return_val'	=> $return_val,
			'line'			=> $line,
			'file'			=> $file,
			'error_nr'		=> $error_nr,
			'error_val'		=> $error_val,
			);
	}
	
	/**
	 * identification number of the current error - mysql_errno
	 *
	 * @return int $nr
	 *
	**/
	function sql_errno() {
		if ( ! is_resource($this->conn) )
			return 0;
		else
			return $this->conn->lastErrorCode();
	}
	
	/**
	 * SQLite3::lastErrorMsg
	 *
	 * @return str $error
	 *
	**/
	function sql_error() {
		if ( ! is_resource($this->conn) )
			return 0;
		else
			return $this->conn->lastErrorMsg();
	}
	
	/**
	 * SQLite3::query
	 *
	 * @param str $query
	 *
	 * @return resource $result
	 *
	**/
	function query($query) {
		if ( empty($query) )
			return false;
		$this->result = @$this->conn->query($query);
		if ( $this->sql_debug )
		    $this->sql_monitor('query', 'query', $query, $this->result, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $this->result;
	}
	
	/**
	 * SQLite3::fetchArray
	 *
	 * @param resource $rezultat
	 * @param bool $type - true for MYSQL_ASSOC, false for MYSQL_NUM, null for MYSQL_BOTH
	 *
	 * @return array $result
	 *
	**/
	function fetch_array($rezultat=NULL, $type=true) {
		if ( is_resource($rezultat) )
			$result = $rezultat;
		else
			$result = $this->result;
		if ( $type === true)
			$rez = $result->fetchArray(SQLITE3_ASSOC);
		elseif ( $type === false)
			$rez = $result->fetchArray(SQLITE3_NUM);
		else
			$rez = $result->fetchArray(SQLITE3_BOTH);
		if ( $this->sql_debug )
			$this->sql_monitor('fetch_array', 'result', $result, $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $rez;
	}
	
	/**
	 * SQLite3::close
	 *
	 * @return bool $rez
	 *
	**/
	function close() {
		$rez = $this->conn->close();
		if ( $this->sql_debug )
			$this->sql_monitor('close', '', '', $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $rez;
	}
	
	/**
	 * SQLite3::query (start tranzaction)
	 *
	 * @return resource
	 *
	**/
	function start_tranzaction() {
		return $this->query('BEGIN TRANSACTION');
	}
	
	/**
	 * SQLite3::query (rollback tranzaction)
	 *
	 * @return resource
	 *
	**/
	function rollback() {
		return $this->query('ROLLBACK');
	}
	
	/**
	 * SQLite3::query (commit tranzaction)
	 *
	 * @return resource
	 *
	**/
	function commit() {
		return $this->query('COMMIT');
	}

	/**
	 * SQLite3::changes
	 *
	 * @return int $rows
	 *
	**/
	function affected_rows() {
		$rows = $this->conn->changes();
		if ( $this->sql_debug )
			$this->sql_monitor('affected_rows', '', '', $rows, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $rows;
	}
	
	/**
	 * SQLite3::lastInsertRowID
	 *
	 * @return int $id
	 *
	**/
	function insert_id() {
		$id = $this->conn->lastInsertRowID();
		if ( $this->sql_debug )
			$this->sql_monitor('insert_id', '', '', $id, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $id;
	}
	
	/**
	 * SQLite3::escapeString
	 *
	 * @param mixed $str
	 *
	 * @return mixed $str
	 *
	**/
	function real_escape($str) {
		if ( is_array($str) ) {
			if ( function_exists('sqlite_escape_string') ) 
				$rez = array_map('sqlite_escape_string', $str);
			else
				$rez = array_map('SQL_DB::sqlite3_escape_string', $str);
		} else {
			$rez = $this->conn->escapeString($str);
		}
		if ( $this->sql_debug )
			$this->sql_monitor('real_escape', 'str', $str, $rez, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $rez;
	}

	/*
	 * filtering data for SQLITE3
	 *
	 * @param str/int $data
	 *
	 * @return str/int $data - filtered
	 */
	function sqlite3_escape_string($data) {
		if ( !isset($data) or empty($data) ) return '';
		if ( is_numeric($data) ) return $data;
	
		$non_displayables = array(
			'/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
			'/%1[0-9a-f]/',             // url encoded 16-31
			'/[\x00-\x08]/',            // 00-08
			'/\x0b/',                   // 11
			'/\x0c/',                   // 12
			'/[\x0e-\x1f]/'             // 14-31
		);
		foreach ( $non_displayables as $regex )
			$data = preg_replace( $regex, '', $data );
		$data = str_replace("'", "''", $data );
		return $data;
	}
	
	/**
	 * SQLite3::fetchArray (total count)
	 *
	 * @param resource $rezultat
	 *
	 * @return int $result
	 *
	**/
	function num_rows($rezultat=NULL) {
		if ( ! is_resource($rezultat) )
			$rez = $this->result;
		else
			$rez = $rezultat;
		$total = $rez->fetchArray(SQLITE3_NUM);
		$result = is_array($total) ? count($total) : 0;
		if ( $this->sql_debug )
			$this->sql_monitor('num_rows', 'rez', $rez, $result, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $result;
	}
	
	/**
	 * SQLite3::fetchArray
	 *
	 * @param resource $rezultat
	 * @param int $row
	 *
	 * @return str $result
	 *
	**/
	function result($rezultat=NULL, $row=0) {
		if ( ! is_resource($rezultat) )
			$rez = $this->result;
		else
			$rez = $rezultat;
		$rows = $rez->fetchArray(SQLITE3_NUM);
		$result = $rows[$row];	
		if ( $this->sql_debug )
			$this->sql_monitor('num_rows', 'rez', $rez, $result, __LINE__, __FILE__, $this->sql_errno(), $this->sql_error());
		return $result;
	}
	/**
	 * does not apply to sqlite3
	 *
	 * @return bool
     *
	**/
	function sql_info() {
		return true;
	}
	
	/**
	 * does not apply to sqlite3
	 *
	 * @return bool
	 *
    **/
	function free_result() {
		return true;
	}

    /**
     * set busyTimeout
     * 
     * @param int $ms
     * 
     * @return bool
     */
    function set_timeout($ms=10000) {
        return $this->conn->busyTimeout($ms); 
    }

	/**
	 * Preparation for working with SQL data
	 *
	 * @param array $values	- values that need to be changed
	 * @param bool $tilde	- what type of quotes used
	 *
	 * @return string $values
	 *
	**/
	function prepare_data($values, $tilde = false) {
		if (! is_array($values) || count($values) == 0)
			return false;

		if ($tilde)
			$values = count($values) ? "'" . implode("', '", $values) . "'" : '';
		else
			$values = count($values) ? "'" . implode("', '", $values) . "'" : '';

		return $values;
	}

	/**
	 * Filter an array by keys
	 *
	 * @param	array	$array	associative array to filter
	 * @param	array	$filter	list of keys to return (in this order)
	 *
	 * @return	array	a new array containing only those keys and values from $array which exists in $filter too (keep order from $filter)
	 *
	**/
	function array_sqlfilter($array, $filter) {
		if (! is_array($array) || count($array) == 0)
			return false;
		if (! is_array($filter) || count($filter) == 0)
			return false;

		$result = array();
		foreach ($filter as $key) {
			if (isset($array[$key]))
				$result[$key] = $array[$key]; 
		}
		return $result;
	}

}
?>