File modules/database/driver/PDODriver.class.php

Last commit: Thu Feb 16 01:04:38 2023 +0100	Jan Dankert	New: Tags for base objects.


     1 <?php
     2 
     3 //
     4 // +----------------------------------------------------------------------+
     5 // | PHP version 4.0                                                      |
     6 // +----------------------------------------------------------------------+
     7 // | Copyright (c) 1997-2001 The PHP Group                                |
     8 // +----------------------------------------------------------------------+
     9 // | This source file is subject to version 2.02 of the PHP license,      |
    10 // | that is bundled with this package in the file LICENSE, and is        |
    11 // | available at through the world-wide-web at                           |
    12 // | http://www.php.net/license/2_02.txt.                                 |
    13 // | If you did not receive a copy of the PHP license and are unable to   |
    14 // | obtain it through the world-wide-web, please send a note to          |
    15 // | license@php.net so we can mail you a copy immediately.               |
    16 // +----------------------------------------------------------------------+
    17 // | Authors: Stig Bakken <ssb@fast.no>                                   |
    18 // |          Jan Dankert <phpdb@jandankert.de>                           |
    19 // +----------------------------------------------------------------------+
    20 //
    21 namespace database\driver;
    22 
    23 use database\Sql;
    24 use logger\Logger;
    25 use \PDO;
    26 use \PDOException;
    27 use PDOStatement;
    28 use util\exception\DatabaseException;
    29 
    30 /**
    31  * Implementation of all database operations in PDO.
    32  *
    33  * PDO is available since PHP 5.1 and OpenRat CMS forces a newer PHP version than this. So there should be no problem to rely on PDO.
    34  *
    35  * @author Jan Dankert
    36  */
    37 class PDODriver
    38 {
    39 	/**
    40 	 * Die PDO-Verbindung.
    41 	 *
    42 	 * @var PDO
    43 	 */
    44 	private $connection;
    45 	
    46 
    47     /**
    48      * @var PDOStatement
    49      */
    50     public $stmt;
    51 
    52 
    53 	/**
    54 	 * Connect to a database
    55 	 *
    56 	 * @param $conf array connection configuration
    57 	 * @throws DatabaseException
    58 	 */
    59     function connect( $conf )
    60 	{
    61 		if ( !defined('PDO::ATTR_DRIVER_NAME') ) {
    62 			// This should never happen, because PHP is always bundled with PDO.
    63 			// but maybe... some installation could miss the module.
    64 			throw new DatabaseException('PDO unavailable');
    65 		}
    66 
    67 		$dsn    = $conf['dsn'     ]; // Optional a pre-configured DSN.
    68 		$user   = $conf['user'    ];
    69 		$pw     = $conf['password'];
    70 
    71 		if   ( ! $dsn ) {
    72 			// No DSN is configured, so we are building a DSN.
    73 			$driver = $conf['driver'];
    74 
    75 			if (!in_array($driver,PDO::getAvailableDrivers(),TRUE))
    76 				throw new DatabaseException('PDO driver '.$driver.' is not available');
    77 
    78 			$dsnParts = [];
    79 			if   ( $conf['host'] )
    80 				$dsnParts[ $driver.':host' ] = $conf['host']; // Hostname for RDBMS with IP-stack
    81 			elseif   ( $conf['file'] )
    82 					$dsnParts[ $driver.':'.$conf['file'] ] = $conf['host']; // Filename for SQLITE
    83 
    84 			if   ( $conf['database'] )
    85 				$dsnParts['dbname' ] = $conf['database'];
    86 			if   ( $conf['port'] )
    87 				$dsnParts['port' ] = $conf['port'];
    88 			if   ( $conf['charset'] )
    89 				$dsnParts['charset'] = $conf['charset' ];
    90 
    91 			// Building the DSN for PDO.
    92 			$dsn = implode('; ',array_map( function($key,$value) {
    93 				return $key.'='.$value;
    94 			},array_keys($dsnParts),$dsnParts));
    95 		}
    96 
    97 		// we must have a prefix or suffix
    98 		// this is because some table names are reserved words in some RDBMS
    99 		if   ( ! $conf['prefix'] && ! $conf['suffix'] )
   100 			throw new DatabaseException('database tables must have a prefix or a suffix, both are empty.');
   101 		
   102 		$options = array();
   103 		foreach( $conf as $c )
   104 			if	( is_string($c) && substr($c,0,7) == 'option_' )
   105 				$options[substr($c,8)] = $conf[$c];
   106 
   107 		if	( $conf['persistent'])
   108 		    // From the docs:
   109 		    // "Many web applications will benefit from making persistent connections to database servers.
   110 		    //  Persistent connections are not closed at the end of the script, but are cached and re-used
   111 		    //  when another script requests a connection using the same credentials."
   112 		    // "The persistent connection cache allows you to avoid the overhead of establishing a new
   113 		    // connection every time a script needs to talk to a database, resulting in a faster web application."
   114 			$options[ PDO::ATTR_PERSISTENT ] = true;
   115 
   116 		// From the docs:
   117 		// "try to use native prepared statements (if FALSE).
   118 		//  It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query"
   119 		$options[ PDO::ATTR_EMULATE_PREPARES  ] = false;
   120 		
   121 		// Convert numeric values to strings when fetching => NO
   122 		$options[ PDO::ATTR_STRINGIFY_FETCHES ] = false;
   123 
   124 		// From the docs:
   125 		// "If this value is FALSE, PDO attempts to disable autocommit so that the connection begins a transaction."
   126         // We do NOT need transactions for reading actions (GET requests).
   127         // We are opening a transaction with PDO::beginTransaction at the beginning of a  POST-request.
   128         // do NOT set this to false, otherwise there will be left open transactions.
   129 		//$options[ PDO::ATTR_AUTOCOMMIT ] = true;
   130 			
   131 		// We like Exceptions
   132 		$options[ PDO::ERRMODE_EXCEPTION       ] = true;
   133 		$options[ PDO::ATTR_DEFAULT_FETCH_MODE ] = PDO::FETCH_ASSOC;
   134 
   135         try
   136         {
   137             $this->connection = new PDO($dsn, $user, $pw, $options);
   138         }
   139         catch(\PDOException $e)
   140         {
   141             throw new DatabaseException("Could not connect to database with DSN '$dsn'",$e);
   142         }
   143 
   144         // This should never happen, because PDO should throw an exception if the connection fails.
   145 		if	( !is_object($this->connection) )
   146 			throw new DatabaseException("Could not connect to database with DSN '$dsn', Reason: ".PDO::errorInfo() );
   147     }
   148 
   149 
   150     /**
   151      * Disconnects the database connection.
   152      *
   153      * @return bool
   154      */
   155     public function disconnect()
   156 	{
   157 	    // There is no disconnection-function.
   158         // So the GC will call the finalize-method of the connection object.
   159 		$this->connection = null;
   160 
   161 		return true;
   162 	}
   163 
   164 
   165     /**
   166      * @param $stmt PDOStatement
   167      * @param $query Sql
   168      * @return PDOStatement
   169      * @throws DatabaseException
   170      */
   171     public function execute($stmt, $query)
   172 	{
   173         try {
   174             $erg = $stmt->execute();
   175 
   176             if	( $erg === false )
   177                 throw new DatabaseException( 'Could not execute statement: '.implode('/',$stmt->errorInfo()) );
   178         }
   179         catch( \PDOException $e ) {
   180             throw new DatabaseException( 'database query failed: '.implode('/',$e->errorInfo ));
   181         }
   182 
   183 
   184 		return $stmt;
   185 	}
   186 
   187 
   188 	/**
   189 	 * @param $stmt PDOStatement
   190 	 * @return array Row
   191 	 */
   192 	public function fetchAssocRow($stmt)
   193 	{
   194 		return $stmt->fetch( PDO::FETCH_ASSOC );
   195 	}
   196 
   197 
   198 	/**
   199 	 * Fetches all rows from the resultset
   200 	 * @param $stmt PDOStatement
   201 	 * @return array Row
   202 	 */
   203 	public function fetchAllRows($stmt)
   204 	{
   205 		return $stmt->fetchAll( PDO::FETCH_ASSOC );
   206 	}
   207 
   208 
   209 	/**
   210 	 * Fetches the next row with a numbered-based array.
   211 	 * @param $stmt PDOStatement
   212 	 * @return array Row
   213 	 */
   214 	public function fetchIndexedRow($stmt)
   215 	{
   216 		return $stmt->fetch( PDO::FETCH_NUM );
   217 	}
   218 
   219 
   220 
   221 	/**
   222 	 * Fetches the first column of the next row.
   223 	 *
   224 	 * @param $stmt PDOStatement
   225 	 * @return mixed Row
   226 	 */
   227 	public function fetchFirstColumn($stmt) {
   228 		return $stmt->fetchColumn();
   229 	}
   230 
   231 
   232 	/**
   233 	 * Fetches all first columns from the result set
   234 	 *
   235 	 * @param $stmt PDOStatement
   236 	 * @return array
   237 	 */
   238 	public function fetchAllFirstColumn($stmt) {
   239 		return $stmt->fetchAll( PDO::FETCH_COLUMN );
   240 	}
   241 
   242 
   243 	/**
   244 	 * Prepares a SQL query and gets the Statement.
   245 	 *
   246 	 * @param $query string SQL-query
   247 	 * @param $param array parameters
   248 	 * @return PDOStatement
   249 	 * @throws DatabaseException
   250 	 */
   251 	public function prepare( $query,$param)
   252 	{
   253 		$offset = 0;
   254 		foreach( $param as $name=>$pos)
   255 		{
   256 			$name = ':'.$name;
   257 			$pos  += $offset;
   258 			$query = substr($query,0,$pos).$name.substr($query,$pos);
   259 			
   260 			$offset = $offset + strlen($name);
   261 		}
   262 
   263 		$stmt = $this->connection->prepare($query);
   264 		
   265 		if	( $stmt === false )
   266 			throw new DatabaseException("Could not prepare statement:\n$query\nCause: ".implode(' / ',$this->connection->errorInfo()) );
   267 
   268 		return $stmt;
   269 	}
   270 
   271 
   272     /**
   273      * Binding a parameter value.
   274      *
   275      * @param $stmt PDOStatement
   276      * @param $param
   277      * @param $value
   278      */
   279 	public function bind( $stmt,$param,$value )
   280 	{
   281 		$name = ':'.$param;
   282 		
   283 		if	( is_string($value) )
   284 			$type = PDO::PARAM_STR;
   285 		elseif( is_int($value)) 
   286 			$type = PDO::PARAM_INT;
   287 		elseif( is_null($value))
   288 			$type = PDO::PARAM_NULL;
   289 		else
   290 			throw new DatabaseException( 'Unknown type for parameter '.$name.': '.gettype($value) );
   291 		
   292 		$stmt->bindValue($name,$value,$type);
   293 	}
   294 	
   295 	
   296 	
   297 	/**
   298      * Startet eine Transaktion.
   299      */
   300 	public function start()
   301 	{
   302 		$this->connection->beginTransaction();
   303 	}
   304 
   305 	
   306 	
   307 	/**
   308      * Beendet eine Transaktion.
   309      */
   310 	public function commit()
   311 	{
   312 		$this->connection->commit();
   313 	}
   314 
   315 
   316 	/**
   317      * Bricht eine Transaktion ab.
   318      */
   319 	public function rollback()
   320 	{
   321 		try
   322 		{
   323 			$this->connection->rollBack();
   324 		}
   325 		catch ( PDOException $e )
   326 		{
   327 			// Kommt vor, wenn keine Transaktion existiert.
   328 		}
   329 	}
   330 	
   331 
   332 	/**
   333 	 * Why this? See http://e-mats.org/2008/07/fatal-error-exception-thrown-without-a-stack-frame-in-unknown-on-line-0/
   334 	 * 
   335 	 * @return array
   336 	 */
   337 	function __sleep() {
   338     	return array();
   339   }  
   340 	
   341 }



Download modules/database/driver/PDODriver.class.php


History
Thu, 16 Feb 2023 01:04:38 +0100	Jan Dankert	New: Tags for base objects.
Sun, 7 Mar 2021 00:10:20 +0100	Jan Dankert	Refactoring: Hopefully more performance while accessing the database resultsets.
Sat, 6 Mar 2021 22:11:06 +0100	Jan Dankert	Cleanup: PDODriver#fetchrow() now only needs 1 argument.
Mon, 30 Nov 2020 09:57:36 +0100	Jan Dankert	Fix: aborting transaction before changing the database connection; Refactoring: Cleanup databases
Thu, 19 Nov 2020 10:45:05 +0100	Jan Dankert	Fix: Default database.
Mon, 26 Oct 2020 09:08:25 +0100	Jan Dankert	Using 'dsn' as name for DSN.
Fri, 23 Oct 2020 11:25:21 +0200	Jan Dankert	Database connection: Adding the TCP-Port, Support for SQLITE.
Fri, 23 Oct 2020 10:31:36 +0200	Jan Dankert	If the PDO DSN is not configured, it is generated.
Sun, 4 Oct 2020 21:24:40 +0200	Jan Dankert	Fix: Throw correct DatabaseException
Sat, 29 Aug 2020 03:23:06 +0200	Jan Dankert	Refactoring: Improved Exception-Handling; New: Generating pages using a page context which considers page aliases.
Sun, 23 Feb 2020 00:16:20 +0100	Jan Dankert	Refactoring: Namespacing for module 'database'.
Sun, 23 Feb 2020 00:03:40 +0100	Jan Dankert	Refactoring: Namespaces for modules 'logger' and 'language'
Thu, 30 May 2019 00:10:13 +0200	Jan Dankert	New: Aliases integriert, jedoch noch nicht sprachspezifisch (folgt noch).
Mon, 20 May 2019 01:15:43 +0200	Jan Dankert	Refactoring: Das Prüfen der DB-Version erfolgt mit der Standard-DB-Verbindung. Erst beim Update wird auf die Admin-Verbindung gewechselt.
Tue, 25 Sep 2018 00:18:51 +0200	Jan Dankert	DB-Benutzernamen ins Log schreiben, wenn ein Fehler passiert.
Sat, 21 Jul 2018 00:00:21 +0200	Jan Dankert	Datenbank-Modul weiter aufgeräumt und alten Kram entfernt. Das erzeugte Prepared-Statement wird nun im Statement gespeichert, da wo es hingehört.
Tue, 22 May 2018 22:59:32 +0200	Jan Dankert	Direkte Logausgabe, wenn Datenbankverbindung fehlschlägt. Und die RTE richtig befüllen.
Fri, 9 Feb 2018 21:59:27 +0100	Jan Dankert	NICHT autocommit=false setzen. Denn damit erzeugt ein SELECT bei Isolation-Level REPEATABLE_READ (Standard in Mysql 5.x) eine Transaktion. Wenn diese offen bleibt und die Verbindung wiederverwendet wird, liest ein SELECT ältere Werte, alsl in der Datenbank stehen. Abgesehen davon, dass alle Datenbankprozesse mit offenen Transaktionen belegt werden und die Resourcen schnell erschöpft sind.
Fri, 8 Dec 2017 23:42:39 +0100	Jan Dankert	Schickere Logmeldungen...
Fri, 8 Dec 2017 00:06:49 +0100	Jan Dankert	Nur kleine Optimierungen im Database-Modul.
Thu, 7 Dec 2017 23:02:20 +0100	Jan Dankert	Kleinere Verbesserung im Datenbank-Treiber, z.B. Exception-Handling.
Tue, 5 Dec 2017 23:56:04 +0100	Jan Dankert	Datenbank-Klassen auf Namespace umgestellt.