Database::ShowColumns() - Code Metrics - Inspection of "scrut3" - salimkanoun/GaelO - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — dev (0fab21)

by Salim

created 2020-04-12 11:29 UTC

Database::ShowColumns() A

↳ Parent: Project

Complexity

Conditions	5
Paths	5

Size

Total Lines	15
Code Lines	12

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	12
nc	5
nop	2
dl	0
loc	15
c	0
b	0
f	0
cc	5
rs	9.5555

<?php

/**
 *	Class Database (PDO Extension)
 *  ---------------------------- 
 *  Description : encapsulates database operations & properties with PDO
 *  Updated	    : 22.05.2013
 *  Version     : 1.0.7
 *	Written by  : ApPHP
 *	Used in     : DataGridWizard, AdminPanel, EasyInstaller
 *	Syntax (standard)  : $db = new Database($database_host, $database_name, $database_username, $database_password, EI_DATABASE_TYPE, $is_installation);
 *	Syntax (singleton) : $db = Database::GetInstance($database_host, $database_name, $database_username, $database_password, EI_DATABASE_TYPE, $is_installation);
 *
 *  PUBLIC              STATIC				 PROTECTED           PRIVATE
 *  -------             ----------          ----------          ---------- 
 *  __construct         GetInstance                             PrepareLogSQL
 *	__destruct          IsConnected
 *	Create
 *	AllowTransactions
 *	Open             
 *	Close
 *	CloseCursor
 *	GetVersion
 *	GetDbDriver
 *	Query
 *	Exec
 *	AffectedRows
 *	RowCount
 *	ColumnCount
 *	InsertID
 *	SetEncoding
 *	Error
 *	ErrorCode
 *	ErrorInfo
 *	FetchAssoc
 *	FetchArray
 *	ShowTables
 *	ShowColumns
 *
 *	CHANGE LOG
 *	-----------
 *  1.0.7
 *      - added CloseCursor()
 *      - added "persistent_connection" (default - false)
 *      -
 *      -
 *      -
 *  1.0.6
 *      - changed syntaxt for mssql to 'sqlsrv'
 *      - added $PROJECT
 *      - returned old syntax for 'mssql'
 *      - added sqlsrv in ShowTables()
 *      - added $this->sth = null; to Query()
 *  1.0.5
 *      - removed Query() from ShowColumns() and ShowTables()
 *      - added AllowTransactions()
 *      - added schema parameter for ShowTables/ShowColumns
 *      - added ` for some queries
 *      - fixed some syntaxt errors for mssql in ShowColumns and ShowTables
 *  1.0.4
 *      - added ShowTables()
 *      - PrepareLogSQL code placed in separated function
 *      - updated ShowTables()
 *      - added additional check if object exists for Query, Exec etc. methods
 *      - added ShowColumns()
 *  1.0.3
 *      - improved Exec() - added $check_error parameter
 *      - improved Query() - added fetch type
 *      - added oci connection case in Open() method
 *      - improved oci connection string
 *      - added connect syntaxt for 'ibm'
 *  1.0.2
 *  	- added FetchAssoc()
 *  	- fixed bug in RowCount
 *  	- fixed bug in GetInstance()
 *  	- added IsConnected()
 *  	- fixed error with $installation property
 *	
 **/

class Database
{
    // connection parameters
	private $host = '';
	private $port = '';
	private $ssl;
	private $db_driver = '';
    private $database = '';
    private $user = '';
    private $password = '';
    private $allow_transactions = false;
	private $force_encoding = false;
    private $persistent_connection = false;
	private static $installation = false;
	private $use_mysql = false;

	private $error = '';
	private $affectedRows = '0';
    // database connection handler 
    private $dbh = NULL;
	// database statament handler 
	private $sth = NULL;
	// static data members	
	private static $objInstance; 

	// DataGridWizard, AdminPanel, EasyInstaller
	private static $PROJECT = 'EasyInstaller';  


	//==========================================================================
    // Class Constructor
	// 		@param $database_host
	// 		@param $database_name
	// 		@param $database_username
	// 		@param $database_password
	// 		@param $db_driver
	// 		@param $force_encoding
	// 		@param $is_installation
	//==========================================================================
    function __construct($database_host='', $database_name='', $database_username='', $database_password='', $db_driver='', $force_encoding=false, $is_installation=false, $ssl=false)	
    {
		$this->host = $database_host;
		$this->port = '';
		$this->ssl=$ssl;
		$host_parts = explode(':', $database_host);		
		if(isset($host_parts[1]) && is_numeric($host_parts[1])){
			$this->host = $host_parts[0];	
			$this->port = $host_parts[1];	
		}
		
		if($database_host == '' && $database_name == ''){
			$config = new Config();	
			$this->host = $config->getHost();
			$this->user = $config->getUser();
			$this->password = $config->getPassword();
			$this->database = $config->getDatabase();
			$this->db_driver = $config->getDatabaseType();
            $this->allow_transactions = false;
		}else{
			$this->database  = $database_name;   	
			$this->user 	 = $database_username;
			$this->password  = $database_password;
			$this->db_driver = strtolower($db_driver);
			$this->allow_transactions = false;
		}
		$this->force_encoding = $force_encoding;
		
		self::$installation = ($is_installation) ? true : false;
	}

	//==========================================================================
    // Class Destructor
	//==========================================================================
    function __destruct()
	{
		// echo 'this object has been destroyed';
    }

    /**
     *	Allow transactions
     *	    @param $mode
     */
    public function AllowTransactions($mode = false)
    {
        $this->allow_transactions = ($mode == true) ? $mode : false;
    }

    /**
     *	Create database
     */
    public function Create()
    {
		$this->dbh = new PDO($this->db_driver.':host='.$this->host, $this->user, $this->password);
		$this->dbh->exec('CREATE DATABASE IF NOT EXISTS `'.$this->database.'`;');
		if($this->dbh->errorCode() != '00000'){
			$err = $this->dbh->errorInfo();
			$this->error = $err[2];
			return false; 
		}
		return true; 
	}

    /**
     *	Checks and opens connection with database
     */
    public function Open()
    {
        // Without PDO extension
        if($this->use_mysql && $this->db_driver == 'mysql'){
            // Choose the appropriate connect function 
            if($this->persistent_connection){
                $func = 'mysql_pconnect';
            } else {
                $func = 'mysql_connect';
            }    
            // Connect to the MySQL server 
            $this->dbh = $func($this->host, $this->user, $this->password);
            return true;
        }
        
        // With PDO extension
		if(version_compare(PHP_VERSION, '5.0.0', '<') || !defined('PDO::ATTR_DRIVER_NAME')){
			$this->error = 'You must have PHP 5 or newer installed to use PHP Data Objects (PDO) extension';
			return false; 
		}

		$port = (!empty($this->port)) ? ';port='.$this->port : '';
		try{
			switch($this->db_driver){
				case 'mssql':
                    $this->dbh = new PDO('mssql:host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
					break;
                case 'sqlsrv': 
                    $this->dbh = new PDO('sqlsrv:Server='.$this->host.$port.';Database='.$this->database, $this->user, $this->password);
					break;
				case 'sybase': 
					$this->dbh = new PDO('sybase:host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
					break;
				case 'sqlite':
					$this->dbh = new PDO('sqlite:my/database/path/database.db');
					break;
				case 'pgsql':
					$this->dbh = new PDO('pgsql:host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
					break;
                case 'ibm': 
                    $this->dbh = new PDO('ibm:'.$this->database, $this->user, $this->password);
                    break;
				case 'oci':
					// Look for valid parameters in product\10.2.0\server\NETWORK\ADMIN	
					// Example: $tns = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = private-22269fa)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ))';
					$port = (!empty($this->port)) ? $this->port : '1521';
					$tns = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = '.$this->host.')(PORT = '.$port.')) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = '.$this->database.') ))';
					$this->dbh = new PDO('oci:dbname='.$tns, $this->user, $this->password);
					break;
				case 'mysql':
				default:
                    if($this->ssl){
                        $sslOptions = array(
                            PDO::MYSQL_ATTR_SSL_CA => '',
                            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
                        );
                        $this->dbh = new PDO($this->db_driver.':host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password, $sslOptions);
                        
                    }else{
                        $this->dbh = new PDO($this->db_driver.':host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
                        
                    }
                    $this->dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                    
                    if($this->persistent_connection) $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
					break;
			}
			$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			if(empty($this->dbh)){
				return false;
			}else if($this->force_encoding){
				$this->dbh->exec('set names utf8');
			}
		}catch(PDOException $e){  
			$this->error = $e->getMessage();
			return false; 
		}            

        return true;
    }	
    
    /**
     *	Close connection 
     */
    public function Close()
    {
		$this->sth = null;
		$this->dbh = null;
    }

	/**
	 * Close cursor
	 * The following call to closeCursor() may be required by some drivers
	 */
    public function CloseCursor()
    {
		return ($this->dbh) ? $this->dbh->closeCursor() : '';
    }

    /**
     *	Returns database engine version
     */
	public function GetVersion()
	{
        if($this->use_mysql && $this->db_driver == 'mysql'){
            $version = mysql_get_server_info();
        }else{
            $version = $this->dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
        }
        // clean version number from alphabetic characters
		return preg_replace('/[^0-9,.]/', '', $version);
	}

    /**
     *	Get DB driver
     */
    public function GetDbDriver()
    {
		return $this->db_driver;
    }

    /**
     *	Runs query
     *		@param $query
     *		@param $fetch_mode
     */
    public function Query($query = '', $fetch_mode = PDO::FETCH_ASSOC)
    {
        if(!$this->dbh || empty($query)) return false;
        
        if($this->use_mysql && $this->db_driver == 'mysql'){
            $this->sth = mysql_query($query, $this->dbh);            
            return ($this->sth != false);            
        }else{
            try{  
                $this->sth = $this->dbh->query($query);
                if($this->sth !== FALSE){
                    $this->sth->setFetchMode($fetch_mode);
                    return $this->sth;
                }
                else return false; 
            }catch(PDOException $e){
                $this->error = $e->getMessage();
                $this->sth = null;
                if(!self::$installation){
                    $sql_log = $this->PrepareLogSQL($e, $query);
                    $this->Exec($sql_log);
                }
                return false; 
            }                        
        }    
    }

    /**
     *	Executes query
     *		@param $query
     *		@param $check_error
     */
    public function Exec($query = '', $check_error = true)
	{
        if(!$this->dbh) return false;
        if(empty($query)) return false;  
		try{
			$this->affectedRows = $this->dbh->exec($query);
			return $this->affectedRows;	
		}catch(PDOException $e){            
			if($check_error){
				$this->error = $e->getMessage();				
				if(!self::$installation){
					$sql_log = $this->PrepareLogSQL($e, $query);
					$this->Query($sql_log);
				}				
			}			
			return false; 
		}		
	}

    /**
     *	Set encoding and collation on database
     *		@param $encoding
     *		@param $collation
     */
    public function SetEncoding($encoding = 'utf8', $collation = 'utf8_unicode_ci')
    {		
		if(empty($encoding)) $encoding = 'utf8';
        if(empty($collation)) $collation = 'utf8_unicode_ci';    
        $sql_variables = array(
                'character_set_client'  =>$encoding,
                'character_set_server'  =>$encoding,
                'character_set_results' =>$encoding,
                'character_set_database'=>$encoding,
                'character_set_connection'=>$encoding,
                'collation_server'      =>$collation,
                'collation_database'    =>$collation,
                'collation_connection'  =>$collation
        );
        foreach($sql_variables as $var => $value){
            $sql = 'SET '.$var.'='.$value;
            $this->Query($sql);
        }        
    }

    /**
     *	Returns affected rows after exec()
     */
    public function AffectedRows()
    {
		return $this->affectedRows;
    }	

    /**
     *	Returns rows count for query()
     */
    public function RowCount()
    {
		return (isset($this->sth)) ? $this->sth->rowCount() : 0; 
    }		

    /**
     *	Returns columns count for query()
     */
    public function ColumnCount()
    {
		return $this->sth->columnCount(); 
    }		

    /**
     *	Returns last insert ID
     */
	public function InsertID()
    {
		return $this->dbh->lastInsertId();
    }

    /**
     *	Returns error 
     */
    public function Error()
    {
		return $this->error;		
    }
	
    /**
     *	Returns error code
     */
    public function ErrorCode()
    {
        return ($this->dbh) ? $this->dbh->errorCode() : false;
    }

    /**
     *	Returns error code
     */
    public function ErrorInfo()
    {
		return ($this->sth) ? $this->sth->errorInfo() : false;
    }
 
	/**
	 * Fetch assoc
	 */
    public function FetchAssoc()
    {
		return ($this->sth) ? $this->sth->fetch(PDO::FETCH_ASSOC) : false;
    }
 
	/**
	 * Fetch array
	 */
    public function FetchArray()
    {
		return ($this->sth) ? $this->sth->fetch(PDO::FETCH_BOTH) : false;
    }
	
	/**
	 * Show tables from current database
	 *      @param $schema
	 */
    public function ShowTables($schema = '')
    {
		switch($this->db_driver){
			case 'mssql';
            case 'sqlsrv':
				$sql = 'SELECT * FROM sys.all_objects WHERE type = \'U\'';
				break;
            case 'pgsql':
                $sql = 'SELECT tablename FROM pg_tables WHERE tableowner = current_user';
                break;
            case 'sqlite':
                $sql = 'SELECT * FROM sqlite_master WHERE type=\'table\'';
                break;
			case 'oci':
				$sql = 'SELECT * FROM system.tab';
				break;
			case 'ibm':
				$sql = 'SELECT TABLE_NAME FROM qsys2.systables'.(($schema != '') ? ' WHERE TABLE_SCHEMA = \''.$schema.'\'' : '');
				break;
			case 'mysql':
			default:
				$sql = 'SHOW TABLES IN `'.$this->database.'`';	
				break;
		}
        return $sql;
    }
	
	/**
	 * Show columns from current database
	 */
    public function ShowColumns($table, $schema = '')
    {
		switch($this->db_driver){
			case 'ibm':
                $sql = 'SELECT COLUMN_NAME FROM qsys2.syscolumns WHERE TABLE_NAME = \''.$table.'\''.(($schema != '') ? ' AND TABLE_SCHEMA = \''.$schema.'\'' : ''); 
				break;
			case 'mssql':
                $sql = 'select COLUMN_NAME, data_type, character_maximum_length from '.$this->database.'.information_schema.columns where table_name = \''.$table.'\'';
				break;
			case 'mysql':
			default:
				$sql = 'SHOW COLUMNS FROM `'.$table.'`';
				break;
		}
        return $sql;
    }


	//==========================================================================
    // Returns DB instance or create initial connection 
	// 		@param $database_host
	// 		@param $database_name
	// 		@param $database_username
	// 		@param $database_password
	// 		@param $db_driver
	// 		@param $force_encoding
	// 		@param $is_installation
	//==========================================================================
	public static function GetInstance($database_host = '', $database_name = '', $database_username = '', $database_password = '', $db_driver = '', $force_encoding = false, $is_installation = false, $ssl=false)
	{
		$database_port = '';
		
		$host_parts = explode(':', $database_host);		
		if(isset($host_parts[1]) && is_numeric($host_parts[1])){
			$database_host = $host_parts[0];	
			$database_port = $host_parts[1];	
		}
		
		if($database_host == ''){
			$config = new Config();	
			$database_host = $config->getHost();
			$database_name = $config->getDatabase();
			$database_username = $config->getUser();
			$database_password = $config->getPassword();			
			$db_driver = $config->getDatabaseType();
		}
		
		if(!self::$objInstance){
			self::$objInstance = new Database($database_host, $database_name, $database_username, $database_password, $db_driver, $force_encoding, $is_installation, $ssl);
			self::$objInstance->Open();
        }		
        return self::$objInstance; 
	}
	
	/**
	 * Check if connected
	 */
	public static function IsConnected()
	{
		return (self::$objInstance) ? true : false; 
	}
	
	/**
	 * Prepare log SQL
	 */
	private function PrepareLogSQL($e, $query)
	{
        if(self::$PROJECT == 'AdminPanel'){
            $sql_log = '';
            $error_no = $e->getCode();
            $error_descr  = 'ENV:        '.$_SERVER['SERVER_NAME'].'<br><br>';
            $error_descr .= 'TIME:       '.@date('M d, Y g:i A').'<br><br>';
            $error_descr .= 'SCRIPT:     '.$_SERVER['PHP_SELF'].'<br><br>';
            $error_descr .= 'ERROR LINE: '.(int)$e->getLine().'<br><br>'; 
            $error_descr .= 'ERROR:      '.$this->error.'<br><br>';
            $error_descr .= 'QUERY:      '.$query.'<br><br>';
            $current_file = basename($_SERVER['PHP_SELF'], '.php').'.php';
            
            $ip_address = get_ip_address();
            $sql_log = 'INSERT INTO '.TABLE_SYSTEM_LOGS.' (id, log_type, title, file_name, log, ip_address, date_created)
                        VALUES (NULL, \'Error\', \'DB Error #'.$error_no.'\', \''.$current_file.'\', \''.encode_text($error_descr).'\', \''.$ip_address.'\', \''.@date('Y-m-d H:i:s').'\')';
            return $sql_log;            
        }else{
            return '';
        }
	}
	
}



1			<?php
2
3			/**
4			* Class Database (PDO Extension)
5			* ----------------------------
6			* Description : encapsulates database operations & properties with PDO
7			* Updated : 22.05.2013
8			* Version : 1.0.7
9			* Written by : ApPHP
10			* Used in : DataGridWizard, AdminPanel, EasyInstaller
11			* Syntax (standard) : $db = new Database($database_host, $database_name, $database_username, $database_password, EI_DATABASE_TYPE, $is_installation);
12			* Syntax (singleton) : $db = Database::GetInstance($database_host, $database_name, $database_username, $database_password, EI_DATABASE_TYPE, $is_installation);
13			*
14			* PUBLIC STATIC PROTECTED PRIVATE
15			* ------- ---------- ---------- ----------
16			* __construct GetInstance PrepareLogSQL
17			* __destruct IsConnected
18			* Create
19			* AllowTransactions
20			* Open
21			* Close
22			* CloseCursor
23			* GetVersion
24			* GetDbDriver
25			* Query
26			* Exec
27			* AffectedRows
28			* RowCount
29			* ColumnCount
30			* InsertID
31			* SetEncoding
32			* Error
33			* ErrorCode
34			* ErrorInfo
35			* FetchAssoc
36			* FetchArray
37			* ShowTables
38			* ShowColumns
39			*
40			* CHANGE LOG
41			* -----------
42			* 1.0.7
43			* - added CloseCursor()
44			* - added "persistent_connection" (default - false)
45			* -
46			* -
47			* -
48			* 1.0.6
49			* - changed syntaxt for mssql to 'sqlsrv'
50			* - added $PROJECT
51			* - returned old syntax for 'mssql'
52			* - added sqlsrv in ShowTables()
53			* - added $this->sth = null; to Query()
54			* 1.0.5
55			* - removed Query() from ShowColumns() and ShowTables()
56			* - added AllowTransactions()
57			* - added schema parameter for ShowTables/ShowColumns
58			* - added ` for some queries
59			* - fixed some syntaxt errors for mssql in ShowColumns and ShowTables
60			* 1.0.4
61			* - added ShowTables()
62			* - PrepareLogSQL code placed in separated function
63			* - updated ShowTables()
64			* - added additional check if object exists for Query, Exec etc. methods
65			* - added ShowColumns()
66			* 1.0.3
67			* - improved Exec() - added $check_error parameter
68			* - improved Query() - added fetch type
69			* - added oci connection case in Open() method
70			* - improved oci connection string
71			* - added connect syntaxt for 'ibm'
72			* 1.0.2
73			* - added FetchAssoc()
74			* - fixed bug in RowCount
75			* - fixed bug in GetInstance()
76			* - added IsConnected()
77			* - fixed error with $installation property
78			*
79			**/
80
81			class Database
82			{
83			// connection parameters
84			private $host = '';
85			private $port = '';
86			private $ssl;
87			private $db_driver = '';
88			private $database = '';
89			private $user = '';
90			private $password = '';
91			private $allow_transactions = false;
92			private $force_encoding = false;
93			private $persistent_connection = false;
94			private static $installation = false;
95			private $use_mysql = false;
96
97			private $error = '';
98			private $affectedRows = '0';
99			// database connection handler
100			private $dbh = NULL;
101			// database statament handler
102			private $sth = NULL;
103			// static data members
104			private static $objInstance;
105
106			// DataGridWizard, AdminPanel, EasyInstaller
107			private static $PROJECT = 'EasyInstaller';
108
109
110			//==========================================================================
111			// Class Constructor
112			// @param $database_host
113			// @param $database_name
114			// @param $database_username
115			// @param $database_password
116			// @param $db_driver
117			// @param $force_encoding
118			// @param $is_installation
119			//==========================================================================
120			function __construct($database_host='', $database_name='', $database_username='', $database_password='', $db_driver='', $force_encoding=false, $is_installation=false, $ssl=false)
121			{
122			$this->host = $database_host;
123			$this->port = '';
124			$this->ssl=$ssl;
125			$host_parts = explode(':', $database_host);
126			if(isset($host_parts[1]) && is_numeric($host_parts[1])){
127			$this->host = $host_parts[0];
128			$this->port = $host_parts[1];
129			}
130
131			if($database_host == '' && $database_name == ''){
132			$config = new Config();
133			$this->host = $config->getHost();
134			$this->user = $config->getUser();
135			$this->password = $config->getPassword();
136			$this->database = $config->getDatabase();
137			$this->db_driver = $config->getDatabaseType();
138			$this->allow_transactions = false;
139			}else{
140			$this->database = $database_name;
141			$this->user = $database_username;
142			$this->password = $database_password;
143			$this->db_driver = strtolower($db_driver);
144			$this->allow_transactions = false;
145			}
146			$this->force_encoding = $force_encoding;
147
148			self::$installation = ($is_installation) ? true : false;
149			}
150
151			//==========================================================================
152			// Class Destructor
153			//==========================================================================
154			function __destruct()
155			{
156			// echo 'this object has been destroyed';
157			}
158
159			/**
160			* Allow transactions
161			* @param $mode
162			*/
163			public function AllowTransactions($mode = false)
164			{
165			$this->allow_transactions = ($mode == true) ? $mode : false;
166			}
167
168			/**
169			* Create database
170			*/
171			public function Create()
172			{
173			$this->dbh = new PDO($this->db_driver.':host='.$this->host, $this->user, $this->password);
174			$this->dbh->exec('CREATE DATABASE IF NOT EXISTS `'.$this->database.'`;');
175			if($this->dbh->errorCode() != '00000'){
176			$err = $this->dbh->errorInfo();
177			$this->error = $err[2];
178			return false;
179			}
180			return true;
181			}
182
183			/**
184			* Checks and opens connection with database
185			*/
186			public function Open()
187			{
188			// Without PDO extension
189			if($this->use_mysql && $this->db_driver == 'mysql'){
190			// Choose the appropriate connect function
191			if($this->persistent_connection){
192			$func = 'mysql_pconnect';
193			} else {
194			$func = 'mysql_connect';
195			}
196			// Connect to the MySQL server
197			$this->dbh = $func($this->host, $this->user, $this->password);
198			return true;
199			}
200
201			// With PDO extension
202			if(version_compare(PHP_VERSION, '5.0.0', '<') \|\| !defined('PDO::ATTR_DRIVER_NAME')){
203			$this->error = 'You must have PHP 5 or newer installed to use PHP Data Objects (PDO) extension';
204			return false;
205			}
206
207			$port = (!empty($this->port)) ? ';port='.$this->port : '';
208			try{
209			switch($this->db_driver){
210			case 'mssql':
211			$this->dbh = new PDO('mssql:host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
212			break;
213			case 'sqlsrv':
214			$this->dbh = new PDO('sqlsrv:Server='.$this->host.$port.';Database='.$this->database, $this->user, $this->password);
215			break;
216			case 'sybase':
217			$this->dbh = new PDO('sybase:host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
218			break;
219			case 'sqlite':
220			$this->dbh = new PDO('sqlite:my/database/path/database.db');
221			break;
222			case 'pgsql':
223			$this->dbh = new PDO('pgsql:host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
224			break;
225			case 'ibm':
226			$this->dbh = new PDO('ibm:'.$this->database, $this->user, $this->password);
227			break;
228			case 'oci':
229			// Look for valid parameters in product\10.2.0\server\NETWORK\ADMIN
230			// Example: $tns = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = private-22269fa)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ))';
231			$port = (!empty($this->port)) ? $this->port : '1521';
232			$tns = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = '.$this->host.')(PORT = '.$port.')) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = '.$this->database.') ))';
233			$this->dbh = new PDO('oci:dbname='.$tns, $this->user, $this->password);
234			break;
235			case 'mysql':
236			default:
237			if($this->ssl){
238			$sslOptions = array(
239			PDO::MYSQL_ATTR_SSL_CA => '',
240			PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
241			);
242			$this->dbh = new PDO($this->db_driver.':host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password, $sslOptions);
243
244			}else{
245			$this->dbh = new PDO($this->db_driver.':host='.$this->host.$port.';dbname='.$this->database, $this->user, $this->password);
246
247			}
248			$this->dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
249
250			if($this->persistent_connection) $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
251			break;
252			}
253			$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
254			if(empty($this->dbh)){
255			return false;
256			}else if($this->force_encoding){
257			$this->dbh->exec('set names utf8');
258			}
259			}catch(PDOException $e){
260			$this->error = $e->getMessage();
261			return false;
262			}
263
264			return true;
265			}
266
267			/**
268			* Close connection
269			*/
270			public function Close()
271			{
272			$this->sth = null;
273			$this->dbh = null;
274			}
275
276			/**
277			* Close cursor
278			* The following call to closeCursor() may be required by some drivers
279			*/
280			public function CloseCursor()
281			{
282			return ($this->dbh) ? $this->dbh->closeCursor() : '';
283			}
284
285			/**
286			* Returns database engine version
287			*/
288			public function GetVersion()
289			{
290			if($this->use_mysql && $this->db_driver == 'mysql'){
291			$version = mysql_get_server_info();
292			}else{
293			$version = $this->dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
294			}
295			// clean version number from alphabetic characters
296			return preg_replace('/[^0-9,.]/', '', $version);
297			}
298
299			/**
300			* Get DB driver
301			*/
302			public function GetDbDriver()
303			{
304			return $this->db_driver;
305			}
306
307			/**
308			* Runs query
309			* @param $query
310			* @param $fetch_mode
311			*/
312			public function Query($query = '', $fetch_mode = PDO::FETCH_ASSOC)
313			{
314			if(!$this->dbh \|\| empty($query)) return false;
315
316			if($this->use_mysql && $this->db_driver == 'mysql'){
317			$this->sth = mysql_query($query, $this->dbh);
318			return ($this->sth != false);
319			}else{
320			try{
321			$this->sth = $this->dbh->query($query);
322			if($this->sth !== FALSE){
323			$this->sth->setFetchMode($fetch_mode);
324			return $this->sth;
325			}
326			else return false;
327			}catch(PDOException $e){
328			$this->error = $e->getMessage();
329			$this->sth = null;
330			if(!self::$installation){
331			$sql_log = $this->PrepareLogSQL($e, $query);
332			$this->Exec($sql_log);
333			}
334			return false;
335			}
336			}
337			}
338
339			/**
340			* Executes query
341			* @param $query
342			* @param $check_error
343			*/
344			public function Exec($query = '', $check_error = true)
345			{
346			if(!$this->dbh) return false;
347			if(empty($query)) return false;
348			try{
349			$this->affectedRows = $this->dbh->exec($query);
350			return $this->affectedRows;
351			}catch(PDOException $e){
352			if($check_error){
353			$this->error = $e->getMessage();
354			if(!self::$installation){
355			$sql_log = $this->PrepareLogSQL($e, $query);
356			$this->Query($sql_log);
357			}
358			}
359			return false;
360			}
361			}
362
363			/**
364			* Set encoding and collation on database
365			* @param $encoding
366			* @param $collation
367			*/
368			public function SetEncoding($encoding = 'utf8', $collation = 'utf8_unicode_ci')
369			{
370			if(empty($encoding)) $encoding = 'utf8';
371			if(empty($collation)) $collation = 'utf8_unicode_ci';
372			$sql_variables = array(
373			'character_set_client' =>$encoding,
374			'character_set_server' =>$encoding,
375			'character_set_results' =>$encoding,
376			'character_set_database'=>$encoding,
377			'character_set_connection'=>$encoding,
378			'collation_server' =>$collation,
379			'collation_database' =>$collation,
380			'collation_connection' =>$collation
381			);
382			foreach($sql_variables as $var => $value){
383			$sql = 'SET '.$var.'='.$value;
384			$this->Query($sql);
385			}
386			}
387
388			/**
389			* Returns affected rows after exec()
390			*/
391			public function AffectedRows()
392			{
393			return $this->affectedRows;
394			}
395
396			/**
397			* Returns rows count for query()
398			*/
399			public function RowCount()
400			{
401			return (isset($this->sth)) ? $this->sth->rowCount() : 0;
402			}
403
404			/**
405			* Returns columns count for query()
406			*/
407			public function ColumnCount()
408			{
409			return $this->sth->columnCount();
410			}
411
412			/**
413			* Returns last insert ID
414			*/
415			public function InsertID()
416			{
417			return $this->dbh->lastInsertId();
418			}
419
420			/**
421			* Returns error
422			*/
423			public function Error()
424			{
425			return $this->error;
426			}
427
428			/**
429			* Returns error code
430			*/
431			public function ErrorCode()
432			{
433			return ($this->dbh) ? $this->dbh->errorCode() : false;
434			}
435
436			/**
437			* Returns error code
438			*/
439			public function ErrorInfo()
440			{
441			return ($this->sth) ? $this->sth->errorInfo() : false;
442			}
443
444			/**
445			* Fetch assoc
446			*/
447			public function FetchAssoc()
448			{
449			return ($this->sth) ? $this->sth->fetch(PDO::FETCH_ASSOC) : false;
450			}
451
452			/**
453			* Fetch array
454			*/
455			public function FetchArray()
456			{
457			return ($this->sth) ? $this->sth->fetch(PDO::FETCH_BOTH) : false;
458			}
459
460			/**
461			* Show tables from current database
462			* @param $schema
463			*/
464			public function ShowTables($schema = '')
465			{
466			switch($this->db_driver){
467			case 'mssql';
468			case 'sqlsrv':
469			$sql = 'SELECT * FROM sys.all_objects WHERE type = \'U\'';
470			break;
471			case 'pgsql':
472			$sql = 'SELECT tablename FROM pg_tables WHERE tableowner = current_user';
473			break;
474			case 'sqlite':
475			$sql = 'SELECT * FROM sqlite_master WHERE type=\'table\'';
476			break;
477			case 'oci':
478			$sql = 'SELECT * FROM system.tab';
479			break;
480			case 'ibm':
481			$sql = 'SELECT TABLE_NAME FROM qsys2.systables'.(($schema != '') ? ' WHERE TABLE_SCHEMA = \''.$schema.'\'' : '');
482			break;
483			case 'mysql':
484			default:
485			$sql = 'SHOW TABLES IN `'.$this->database.'`';
486			break;
487			}
488			return $sql;
489			}
490
491			/**
492			* Show columns from current database
493			*/
494			public function ShowColumns($table, $schema = '')
495			{
496			switch($this->db_driver){
497			case 'ibm':
498			$sql = 'SELECT COLUMN_NAME FROM qsys2.syscolumns WHERE TABLE_NAME = \''.$table.'\''.(($schema != '') ? ' AND TABLE_SCHEMA = \''.$schema.'\'' : '');
499			break;
500			case 'mssql':
501			$sql = 'select COLUMN_NAME, data_type, character_maximum_length from '.$this->database.'.information_schema.columns where table_name = \''.$table.'\'';
502			break;
503			case 'mysql':
504			default:
505			$sql = 'SHOW COLUMNS FROM `'.$table.'`';
506			break;
507			}
508			return $sql;
509			}
510
511
512			//==========================================================================
513			// Returns DB instance or create initial connection
514			// @param $database_host
515			// @param $database_name
516			// @param $database_username
517			// @param $database_password
518			// @param $db_driver
519			// @param $force_encoding
520			// @param $is_installation
521			//==========================================================================
522			public static function GetInstance($database_host = '', $database_name = '', $database_username = '', $database_password = '', $db_driver = '', $force_encoding = false, $is_installation = false, $ssl=false)
523			{
524			$database_port = '';
525
526			$host_parts = explode(':', $database_host);
527			if(isset($host_parts[1]) && is_numeric($host_parts[1])){
528			$database_host = $host_parts[0];
529			$database_port = $host_parts[1];
530			}
531
532			if($database_host == ''){
533			$config = new Config();
534			$database_host = $config->getHost();
535			$database_name = $config->getDatabase();
536			$database_username = $config->getUser();
537			$database_password = $config->getPassword();
538			$db_driver = $config->getDatabaseType();
539			}
540
541			if(!self::$objInstance){
542			self::$objInstance = new Database($database_host, $database_name, $database_username, $database_password, $db_driver, $force_encoding, $is_installation, $ssl);
543			self::$objInstance->Open();
544			}
545			return self::$objInstance;
546			}
547
548			/**
549			* Check if connected
550			*/
551			public static function IsConnected()
552			{
553			return (self::$objInstance) ? true : false;
554			}
555
556			/**
557			* Prepare log SQL
558			*/
559			private function PrepareLogSQL($e, $query)
560			{
561			if(self::$PROJECT == 'AdminPanel'){
562			$sql_log = '';
563			$error_no = $e->getCode();
564			$error_descr = 'ENV: '.$_SERVER['SERVER_NAME'].'<br><br>';
565			$error_descr .= 'TIME: '.@date('M d, Y g:i A').'<br><br>';
566			$error_descr .= 'SCRIPT: '.$_SERVER['PHP_SELF'].'<br><br>';
567			$error_descr .= 'ERROR LINE: '.(int)$e->getLine().'<br><br>';
568			$error_descr .= 'ERROR: '.$this->error.'<br><br>';
569			$error_descr .= 'QUERY: '.$query.'<br><br>';
570			$current_file = basename($_SERVER['PHP_SELF'], '.php').'.php';
571
572			$ip_address = get_ip_address();
573			$sql_log = 'INSERT INTO '.TABLE_SYSTEM_LOGS.' (id, log_type, title, file_name, log, ip_address, date_created)
574			VALUES (NULL, \'Error\', \'DB Error #'.$error_no.'\', \''.$current_file.'\', \''.encode_text($error_descr).'\', \''.$ip_address.'\', \''.@date('Y-m-d H:i:s').'\')';
575			return $sql_log;
576			}else{
577			return '';
578			}
579			}
580
581			}
582
583

salimkanoun / GaelO

Branch — dev (0fab21)

Database::ShowColumns() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like