[20918] improvement: support for both adodb and pdo database abstraction
Sigurd Nes <[email protected]>
| Newsgroups | gmane.comp.web.phpgroupware.cvs |
|---|---|
| Message-ID | <[email protected]> |
Revision: 20918
http://svn.sv.gnu.org/viewvc/?view=rev&root=phpgroupware&revision=20918
Author: sigurdne
Date: 2010-01-05 21:07:59 +0000 (Tue, 05 Jan 2010)
Log Message:
-----------
improvement: support for both adodb and pdo database abstraction
Modified Paths:
--------------
people/sigurdne/modules/phpgwapi/trunk/inc/class.db.inc.php
Added Paths:
-----------
people/sigurdne/modules/phpgwapi/trunk/inc/class.db_adodb.inc.php
people/sigurdne/modules/phpgwapi/trunk/inc/class.db_pdo.inc.php
Modified: people/sigurdne/modules/phpgwapi/trunk/inc/class.db.inc.php
===================================================================
--- people/sigurdne/modules/phpgwapi/trunk/inc/class.db.inc.php 2010-01-05 21:02:01 UTC (rev 20917)
+++ people/sigurdne/modules/phpgwapi/trunk/inc/class.db.inc.php 2010-01-05 21:07:59 UTC (rev 20918)
@@ -18,13 +18,23 @@
$GLOBALS['phpgw_info']['server']['db_type'] = 'mysql';
}
+ if ( empty($GLOBALS['phpgw_info']['server']['db_abstraction']) )
+ {
+ require_once PHPGW_API_INC . '/class.db_pdo.inc.php';
+ }
+ else
+ {
+ require_once PHPGW_API_INC . "/class.db_{$GLOBALS['phpgw_info']['server']['db_abstraction']}.inc.php";
+ }
+
+
/**
* Database abstraction class to allow phpGroupWare to use multiple database backends
*
* @package phpgwapi
* @subpackage database
*/
- class phpgwapi_db
+ abstract class phpgwapi_db_
{
/**
* @var object $adodb holds the legacy ADOdb object
@@ -173,154 +183,13 @@
* @param string $User name of database user (optional)
* @param string $Password password for database user (optional)
*/
- public function connect($Database = null, $Host = null, $User = null, $Password = null)
- {
- if ( !is_null($Database) )
- {
- $this->Database = $Database;
- }
-
- if ( !is_null($Host) )
- {
- $this->Host = $Host;
- }
-
- if ( !is_null($User) )
- {
- $this->User = $User;
- }
-
- if ( !is_null($Password) )
- {
- $this->Password = $Password;
- }
+ abstract public function connect($Database = null, $Host = null, $User = null, $Password = null);
- $persistent = isset($GLOBALS['phpgw_info']['server']['db_persistent']) && $GLOBALS['phpgw_info']['server']['db_persistent'] ? true : false;
- switch ( $this->Type )
- {
- case 'postgres':
- try
- {
- $this->db = new PDO("pgsql:dbname={$this->Database};host={$this->Host}", $this->User, $this->Password, array(PDO::ATTR_PERSISTENT => $persistent));
- }
- catch(PDOException $e){}
- break;
- case 'mysql':
- try
- {
- $this->db = new PDO("mysql:host={$this->Host};dbname={$this->Database}", $this->User, $this->Password, array(PDO::ATTR_PERSISTENT => $persistent));
- }
- catch(PDOException $e){}
- break;
- case 'sybase':
- case 'mssql':
- /*
- * On Windows, you should use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases,
- * as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.
- */
- try
- {
- $this->db = new PDO("mssql:host={$this->Host},1433;dbname={$this->Database}", $this->User, $this->Password, array(PDO::ATTR_PERSISTENT => $persistent));
- }
- catch(PDOException $e){}
- break;
- case 'oracle':
- try
- {
- $this->db = new PDO("OCI:dbname={$this->Database};charset=UTF-8", $this->User, $this->Password);
- }
- catch(PDOException $e){}
- break;
- case 'db2':
- try
- {
- $port = 50000; // configurable?
- $this->db = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE={$this->Database}; HOSTNAME={$this->Host};PORT=50000;PROTOCOL=TCPIP;", $this->User, $this->Password);
- }
- catch(PDOException $e){}
- break;
- case 'MSAccess':
- try
- {
- $this->db = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin"); // FIXME: parameter for database location
- }
- catch(PDOException $e){}
- break;
- case 'dblib':
- try
- {
- $port = 10060; // configurable?
- $this->db = new PDO("dblib:host={$this->Host}:{$port};dbname={$this->Database}", $this->User, $this->Password);
- }
- catch(PDOException $e){}
- break;
- case 'Firebird':
- try
- {
- $this->db = new PDO("firebird:dbname=localhost:C:\Programs\Firebird\DATABASE.FDB", $this->User, $this->Password);// FIXME: parameter for database location
- }
- catch(PDOException $e){}
- break;
- case 'Informix':
- //connect to an informix database cataloged as InformixDB in odbc.ini
- try
- {
- $this->db = new PDO("informix:DSN=InformixDB", $this->User, $this->Password);
- }
- catch(PDOException $e){}
- break;
- case 'SQLite':
- try
- {
- $this->db = new PDO("sqlite:/path/to/database.sdb"); // FIXME: parameter for database location
- }
- catch(PDOException $e){}
- break;
- case 'odbc':
- try
- {
- $dsn = 'something';// FIXME
- /*$dsn refers to the $dsn data source configured in the ODBC driver manager.*/
- $this->db = new PDO("odbc:DSN={$dsn}", $this->User, $this->Password);
- // $this->db = new PDO("odbc:{$dsn}", $this->User, $this->Password);
- }
- catch(PDOException $e){}
- break;
- default:
- //do nothing for now
- }
-
- if ( isset($e) && $e )
- {
- echo 'could not connect to server';
-// echo $e->getMessage();
- exit;
- }
- if($this->Halt_On_Error == 'yes')
- {
- $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- }
- }
/**
- * Legacy supprt for quyering metadata from database
- *
- */
- protected function _connect_adodb()
- {
- require_once PHPGW_API_INC . '/adodb/adodb.inc.php';
- $this->adodb = newADOConnection($this->Type);
- $this->adodb->SetFetchMode(ADODB_FETCH_BOTH);
- return @$this->adodb->connect($this->Host, $this->User, $this->Password, $this->Database);
- }
-
- /**
* Close a connection to a database - only needed for persistent connections
*/
- public function disconnect()
- {
- $this->db = null;
- }
+ abstract public function disconnect();
/**
* Escape strings before sending them to the database
@@ -328,42 +197,22 @@
* @param string $str the string to be escaped
* @return string escaped sting
*/
- public function db_addslashes($str)
- {
- if ( is_null($str) )
- {
- return '';
- }
+ abstract public function db_addslashes($str);
- if ( !is_object($this->db) ) //workaround
- {
- return addslashes($str);
- }
-
- return substr($this->db->quote($str), 1, -1);
- }
-
/**
* Convert a unix timestamp to a rdms specific timestamp
*
* @param int unix timestamp
* @return string rdms specific timestamp
*/
- public function to_timestamp($epoch)
- {
- return date($this->datetime_format(), $epoch);
- }
-
+ abstract public function to_timestamp($epoch);
/**
* Convert a rdms specific timestamp to a unix timestamp
*
* @param string rdms specific timestamp
* @return int unix timestamp
*/
- public function from_timestamp($timestamp)
- {
- return strtotime($timestamp);
- }
+ abstract public function from_timestamp($timestamp);
/**
* Execute a query with limited result set
@@ -394,64 +243,8 @@
* @param bool $exec true for exec, false for query
* @return integer current query id if sucesful and null if fails
*/
- public function query($sql, $line = '', $file = '', $exec = false)
- {
+ abstract public function query($sql, $line = '', $file = '', $exec = false);
- if ( !$this->db )
- {
- $this->connect();
- }
-
- if(!$exec)
- {
- if(preg_match('/(^INSERT INTO|^DELETE FROM|^CREATE TABLE|^DROP TABLE|^ALTER TABLE|^UPDATE)/i', $sql)) // need it for MySQL
- {
- $exec = true;
- }
- }
-
- try
- {
- if($exec)
- {
- $this->affected_rows = $this->db->exec($sql);
- return true;
- }
- else
- {
- $statement_object = $this->db->query($sql);
- if($this->fetchmode == 'ASSOC')
- {
- $this->resultSet = $statement_object->fetchAll(PDO::FETCH_ASSOC);
- }
- else
- {
- $this->resultSet = $statement_object->fetchAll(PDO::FETCH_BOTH);
- }
- }
- }
-
- catch(PDOException $e)
- {
- if ( $e && $this->Halt_On_Error == 'yes' )
- {
- if($file)
- {
- trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
- }
- else
- {
- trigger_error("$sql\n". $e->getMessage(), E_USER_ERROR);
- }
- $this->transaction_abort();
- exit;
- }
- }
-
- $this->delayPointer = true;
- return true;
- }
-
/**
* Execute a query with limited result set
*
@@ -463,97 +256,14 @@
* @return integer current query id if sucesful and null if fails
*/
- function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = 0)
- {
- $offset = intval($offset);
- $num_rows = intval($num_rows);
-
- if ($num_rows == 0)
- {
- $maxmatches = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'];
- $num_rows = (isset($maxmatches)?intval($maxmatches):15);
- }
-
- if( $this->Type == 'mssql' )
- {
- $Query_String = str_replace('SELECT ', 'SELECT TOP ', $Query_String);
- $Query_String = str_replace('SELECT TOP DISTINCT', 'SELECT DISTINCT TOP ', $Query_String);
- $Query_String = str_replace('TOP ', 'TOP ' . ($offset + $num_rows) . ' ', $Query_String);
-
- }
- else
- {
- if ($offset == 0)
- {
- $Query_String .= ' LIMIT ' . $num_rows;
- }
- else
- {
- $Query_String .= ' LIMIT ' . $num_rows . ' OFFSET ' . $offset;
- }
- }
-
- if ($this->debug)
- {
- printf("Debug: limit_query = %s<br />offset=%d, num_rows=%d<br />\n", $Query_String, $offset, $num_rows);
- }
-
- try
- {
- $statement_object = $this->db->query($Query_String);
- if($this->fetchmode == 'ASSOC')
- {
- $this->resultSet = $statement_object->fetchAll(PDO::FETCH_ASSOC);
- }
- else
- {
- $this->resultSet = $statement_object->fetchAll(PDO::FETCH_BOTH);
- }
- }
-
- catch(PDOException $e)
- {
- if ( $e && $this->Halt_On_Error == 'yes' )
- {
- if($file)
- {
- trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
- }
- else
- {
- trigger_error("$sql\n". $e->getMessage(), E_USER_ERROR);
- }
- $this->transaction_abort();
- exit;
- }
- }
-
- $this->delayPointer = true;
- return true;
- }
+ abstract function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = 0);
/**
* Move to the next row in the results set
*
* @return bool was another row found?
*/
- public function next_record()
- {
- if($this->resultSet && current($this->resultSet))
- {
- if($this->delayPointer)
- {
- $this->delayPointer = false;
- $this->Record = current($this->resultSet);
- return true;
- }
-
- $row = next($this->resultSet);
- $this->Record =& $row;
- return !!$row;
- }
- return false;
- }
+ abstract public function next_record();
/**
* Move to position in result set
@@ -561,62 +271,28 @@
* @param int $pos required row (optional), default first row
* @return int 1 if sucessful or 0 if not found
*/
- public function seek($pos = 0)
- {
- if($this->resultSet)
- {
- reset($this->resultSet);
- for ($i=0; $i<$pos; $i++)
- {
- $row = next($this->resultSet);
- }
- return $row;
- }
- return false;
- }
+ abstract public function seek($pos = 0);
/**
* Begin transaction
*
* @return integer|boolean current transaction id
*/
- public function transaction_begin()
- {
-/*
- $bt = debug_backtrace();
- echo "<b>db::{$bt[0]['function']} Called from file: {$bt[0]['file']} line: {$bt[0]['line']}</b><br/>";
- unset($bt);
-*/
- $this->Transaction = $this->db->beginTransaction();
- return $this->Transaction;
- }
+ abstract public function transaction_begin();
/**
* Complete the transaction
*
* @return boolean True if sucessful, False if fails
*/
- public function transaction_commit()
- {
-/*
- $bt = debug_backtrace();
- echo "<b>db::{$bt[0]['function']} Called from file: {$bt[0]['file']} line: {$bt[0]['line']}</b><br/>";
- unset($bt);
-*/
- $this->Transaction = false;
- return $this->db->commit();
- }
+ abstract public function transaction_commit();
/**
* Rollback the current transaction
*
* @return boolean True if sucessful, False if fails
*/
- public function transaction_abort()
- {
- $this->Transaction = false;
- return $this->db->rollBack();
- }
+ abstract public function transaction_abort();
/**
* Find the value of the last insertion on the current db connection
@@ -626,68 +302,16 @@
* @param string $field not needed - kept for backward compatibility
* @return integer the id, -1 if fails
*/
- public function get_last_insert_id($table, $field = '')
- {
- switch ( $GLOBALS['phpgw_info']['server']['db_type'] )
- {
- case 'postgres':
- $sequence = $this->_get_sequence_field_for_table($table, $field);
- $ret = $this->db->lastInsertId($sequence);
- break;
- case 'mssql':
- $this->query("SELECT @@identity", __LINE__, __FILE__);
- $this->next_record();
- $ret = $this->f(0);
- break;
- default:
- $ret = $this->db->lastInsertId();
- }
-
- if($ret)
- {
- return $ret;
- }
- return -1;
- }
+ abstract public function get_last_insert_id($table, $field = '');
/**
- * Find the name of sequense for postgres
- *
- * @param string $table name of table
- * @return string name of the sequense, false if fails
- */
- protected function _get_sequence_field_for_table($table, $field ='')
- {
- //old naming of sequenses
- $sql = "SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*'"
- . " AND relname = '{$table}_{$field}_seq' AND relkind='S' ORDER BY relname";
- $this->query($sql,__LINE__,__FILE__);
- if ($this->next_record())
- {
- return $this->f('relname');
- }
- $sql = "SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*'"
- . " AND relname = 'seq_{$table}' AND relkind='S' ORDER BY relname";
- $this->query($sql,__LINE__,__FILE__);
- if ($this->next_record())
- {
- return $this->f('relname');
- }
- return false;
- }
-
-
- /**
* Lock a table
*
* @param string $table name of table to lock
* @param string $mode type of lock required (optional), default write
* @return boolean True if sucessful, False if fails
*/
- public function lock($table, $mode='write')
- {
- $this->transaction_begin();
- }
+ abstract public function lock($table, $mode='write');
/**
@@ -695,10 +319,7 @@
*
* @return boolean True if sucessful, False if fails
*/
- public function unlock()
- {
- $this->transaction_commit();
- }
+ abstract public function unlock();
/**
* Prepare the VALUES component of an INSERT sql statement by guessing data types
@@ -785,56 +406,32 @@
*
* @return integer number of rows
*/
- public function affected_rows()
- {
- return $this->affected_rows;
- }
+ abstract public function affected_rows();
/**
* Number of rows in current result set
*
* @return integer number of rows
*/
- public function num_rows()
- {
- if($this->resultSet)
- {
- return count($this->resultSet);
- }
- return 0;
- }
-
+ abstract public function num_rows();
/**
* Number of fields in current row
*
* @return integer number of fields
*/
- public function num_fields()
- {
- if($this->resultSet)
- {
- return $this->resultSet->fieldCount();
- }
- return 0;
- }
+ abstract public function num_fields();
/**
* Short hand for num_rows()
* @return integer Number of rows
* @see num_rows()
*/
- public function nf()
- {
- return $this->num_rows();
- }
+ abstract public function nf();
/**
* Short hand for print @see num_rows
*/
- public function np()
- {
- print $this->num_rows();
- }
+ abstract public function np();
/**
* Return the value of a filed
@@ -843,24 +440,7 @@
* @param boolean $strip_slashes string escape chars from field(optional), default false
* @return string the field value
*/
- public function f($name, $strip_slashes = False)
- {
- if($this->resultSet)
- {
- if( isset($this->Record[$name]) )
- {
- if ($strip_slashes || ($this->auto_stripslashes && ! $strip_slashes))
- {
- return stripslashes($this->Record[$name]);
- }
- else
- {
- return $this->Record[$name];
- }
- }
- return '';
- }
- }
+ abstract public function f($name, $strip_slashes = False);
/**
* Print the value of a field
@@ -868,11 +448,7 @@
* @param string $field name of field to print
* @param bool $strip_slashes string escape chars from field(optional), default false
*/
- public function p($field, $strip_slashes = True)
- {
- //echo "depi: p";
- print $this->f($field, $strip_slashes);
- }
+ abstract public function p($field, $strip_slashes = True);
/**
* Get the id for the next sequence - not implemented!
@@ -892,45 +468,7 @@
* @param boolean $full optional, default False summary information, True full information
* @return array Table meta data
*/
- public function metadata($table = '',$full = false)
- {
- if(!$this->adodb || !$this->adodb->IsConnected())
- {
- $this->_connect_adodb();
- }
- if(!($return = $this->adodb->MetaColumns($table,$full)))
- {
- $return = array();
- }
- $this->adodb->close();
- return $return;
-
- /*
- * Due to compatibility problems with Table we changed the behavior
- * of metadata();
- * depending on $full, metadata returns the following values:
- *
- * - full is false (default):
- * $result[]:
- * [0]["table"] table name
- * [0]["name"] field name
- * [0]["type"] field type
- * [0]["len"] field length
- * [0]["flags"] field flags
- *
- * - full is true
- * $result[]:
- * ["num_fields"] number of metadata records
- * [0]["table"] table name
- * [0]["name"] field name
- * [0]["type"] field type
- * [0]["len"] field length
- * [0]["flags"] field flags
- * ["meta"][field name] index of field named "field name"
- * The last one is used, if you have a field name, but no index.
- * Test: if (isset($result['meta']['myfield'])) { ...
- */
- }
+ abstract public function metadata($table = '',$full = false);
/**
* Returns an associate array of foreign keys, or false if not supported.
@@ -940,19 +478,8 @@
* @param boolean $upper optional, default False. If $upper is true, then the table names (array keys) are upper-cased.
* @return array Table meta data
*/
- public function MetaForeignKeys($table, $owner=false, $upper=false)
- {
- if(!$this->adodb || !$this->adodb->IsConnected())
- {
- $this->_connect_adodb();
- }
- if(!($return = $this->adodb->MetaForeignKeys($table, $owner, $upper)))
- {
- $return = array();
- }
- $this->adodb->close();
- return $return;
- }
+ abstract public function MetaForeignKeys($table, $owner=false, $upper=false);
+
/**
* Returns an associate array of foreign keys, or false if not supported.
*
@@ -961,19 +488,7 @@
* @return array Index data
*/
- public function metaindexes($table, $primary = false)
- {
- if(!$this->adodb || !$this->adodb->IsConnected())
- {
- $this->_connect_adodb();
- }
- if(!($return = $this->adodb->MetaIndexes($table, $primary)))
- {
- $return = array();
- }
- $this->adodb->close();
- return $return;
- }
+ abstract public function metaindexes($table, $primary = false);
/**
* Error handler
@@ -982,32 +497,15 @@
* @param int $line line of calling method/function (optional)
* @param string $file file of calling method/function (optional)
*/
- public function halt($msg, $line = '', $file = '')
- {
- $this->db->rollBack();
- }
+ abstract public function halt($msg, $line = '', $file = '');
/**
* Get a list of table names in the current database
*
* @return array list of the tables
*/
- public function table_names()
- {
- if(!$this->adodb || !$this->adodb->IsConnected())
- {
- $this->_connect_adodb();
- }
+ abstract public function table_names();
- $return = $this->adodb->MetaTables('TABLES');
- $this->adodb->close();
- if ( !$return )
- {
- return array();
- }
- return $return;
- }
-
/**
* Return a list of indexes in current database
*
@@ -1027,43 +525,8 @@
* @returns bool was the new db created?
* @throws Exception invalid db-name
*/
- public function create_database($adminname = '', $adminpasswd = '')
- {
- //THIS IS CALLED BY SETUP DON'T KILL IT!
- if ( $this->db )
- {
- $this->db = null; //close the dead connection to be safe
- }
-
- $this->connect();
+ abstract public function create_database($adminname = '', $adminpasswd = '');
- if ( !$this->db )
- {
- echo 'Connection FAILED<br />';
- return False;
- }
-
- if( !preg_match('/^[a-z0-9_]+$/i', $this->Database) )
- {
- throw new Exception(lang('ERROR: the name %1 contains illegal charackter for cross platform db-support', $this->Database));
- }
-
- //create the db
- $this->db->exec("CREATE DATABASE {$this->Database}");
-
- //Grant rights on the db
- switch ($GLOBALS['phpgw_info']['server']['db_type'])
- {
- case 'mysql':
- $this->db->exec("GRANT ALL ON {$this->Database}.*"
- . " TO {$this->User}@{$_SERVER['SERVER_NAME']}"
- . " IDENTIFIED BY '{$this->Password}'");
- default:
- //do nothing
- }
- $this->db = null;
- return True;
- }
/**
* Get the correct date format for DATE field for a particular RDBMS
*
@@ -1142,28 +605,8 @@
* @return boolean TRUE on success or FALSE on failure
*/
- public function insert($sql_string, $valueset, $line = '', $file = '')
- {
- try
- {
- $statement_object = $this->db->prepare($sql_string);
- foreach($valueset as $fields)
- {
- foreach($fields as $field => $entry)
- {
- $statement_object->bindParam($field, $entry['value'], $entry['type']);
- }
- $ret = $statement_object->execute();
- }
- }
+ abstract public function insert($sql_string, $valueset, $line = '', $file = '');
- catch(PDOException $e)
- {
- trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
- }
- return $ret;
- }
-
/**
* Execute prepared SQL statement for select
*
@@ -1172,27 +615,7 @@
* @return boolean TRUE on success or FALSE on failure
*/
- public function select($sql_string, $params, $line = '', $file = '')
- {
- try
- {
- $statement_object = $this->db->prepare($sql_string);
- $statement_object->execute($params);
- if($this->fetchmode == 'ASSOC')
- {
- $this->resultSet = $statement_object->fetchAll(PDO::FETCH_ASSOC);
- }
- else
- {
- $this->resultSet = $statement_object->fetchAll(PDO::FETCH_BOTH);
- }
- }
- catch(PDOException $e)
- {
- trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
- }
- $this->delayPointer = true;
- }
+ abstract public function select($sql_string, $params, $line = '', $file = '');
/**
* Finds the next ID for a record at a table
@@ -1227,5 +650,4 @@
$next_id = $this->f('maximum')+1;
return $next_id;
}
-
}
Added: people/sigurdne/modules/phpgwapi/trunk/inc/class.db_adodb.inc.php
===================================================================
--- people/sigurdne/modules/phpgwapi/trunk/inc/class.db_adodb.inc.php (rev 0)
+++ people/sigurdne/modules/phpgwapi/trunk/inc/class.db_adodb.inc.php 2010-01-05 21:07:59 UTC (rev 20918)
@@ -0,0 +1,760 @@
+<?php
+ /**
+ * Database abstraction class
+ * @author NetUSE AG Boris Erdmann, Kristian Koehntopp
+ * @author Dan Kuykendall, Dave Hall and others
+ * @copyright Copyright (C) 1998-2000 NetUSE AG Boris Erdmann, Kristian Koehntopp
+ * @copyright Portions Copyright (C) 2001-2006 Free Software Foundation, Inc. http://www.fsf.org/
+ * @license http://www.fsf.org/licenses/lgpl.html GNU Lesser General Public License
+ * @link http://www.sanisoft.com/phplib/manual/DB_sql.php
+ * @package phpgwapi
+ * @subpackage database
+ * @version $Id$
+ */
+
+ if ( empty($GLOBALS['phpgw_info']['server']['db_type']) )
+ {
+ $GLOBALS['phpgw_info']['server']['db_type'] = 'mysql';
+ }
+ /**
+ * Include concrete database implementation
+ */
+ require_once PHPGW_API_INC . '/adodb/adodb-exceptions.inc.php';
+ require_once PHPGW_API_INC . '/adodb/adodb.inc.php';
+
+ /**
+ * Database abstraction class to allow phpGroupWare to use multiple database backends
+ *
+ * @package phpgwapi
+ * @subpackage database
+ */
+ class phpgwapi_db extends phpgwapi_db_
+ {
+
+ /**
+ * Constructor
+ * @param string $query query to be executed (optional)
+ * @param string $db_type the database engine being used
+ */
+ public function __construct($query = null, $db_type = null)
+ {
+ parent::__construct($query, $db_type);
+ }
+
+ /**
+ * Called when object is cloned
+ */
+ public function __clone()
+ {
+ $this->adodb = clone($this->adodb);
+ }
+
+ /**
+ * Destructor
+ */
+ public function __destruct()
+ {
+ //FIXME will be reenabled when cloning is sorted in property
+ // $this->disconnect();
+ }
+
+ /**
+ * Get current connection id
+ * @return int current connection id
+ */
+ function link_id()
+ {
+ if(!$this->adodb->isConnected())
+ {
+ $this->connect();
+ }
+ return $this->adodb->_connectionID;
+ }
+
+ /**
+ * Get current query id
+ * @return int id of current query
+ */
+ public function query_id()
+ {
+ return $this->Query_ID;
+ }
+
+ /**
+ * Open a connection to a database
+ *
+ * @param string $Database name of database to use (optional)
+ * @param string $Host database host to connect to (optional)
+ * @param string $User name of database user (optional)
+ * @param string $Password password for database user (optional)
+ */
+ public function connect($Database = null, $Host = null, $User = null, $Password = null)
+ {
+ if ( !is_null($Database) )
+ {
+ $this->Database = $Database;
+ }
+
+ if ( !is_null($Host) )
+ {
+ $this->Host = $Host;
+ }
+
+ if ( !is_null($User) )
+ {
+ $this->User = $User;
+ }
+
+ if ( !is_null($Password) )
+ {
+ $this->Password = $Password;
+ }
+
+ $type = $this->Type;
+ if ( $type == 'mysql' )
+ {
+ $type = 'mysqlt';
+ }
+ $this->adodb = newADOConnection($this->Type);
+ $this->adodb->SetFetchMode(ADODB_FETCH_BOTH);
+ return @$this->adodb->connect($this->Host, $this->User, $this->Password, $this->Database);
+ }
+
+ /**
+ * Close a connection to a database - only needed for persistent connections
+ */
+ public function disconnect()
+ {
+ $this->adodb->close();
+ }
+
+ /**
+ * Escape strings before sending them to the database
+ *
+ * @param string $str the string to be escaped
+ * @return string escaped sting
+ */
+ public function db_addslashes($str)
+ {
+ if ( is_null($str) )
+ {
+ return '';
+ }
+
+ if ( !is_object($this->adodb) ) //workaround
+ {
+ return addslashes($str);
+ }
+ return substr($this->adodb->Quote($str), 1, -1);
+ }
+
+ /**
+ * Convert a unix timestamp to a rdms specific timestamp
+ *
+ * @param int unix timestamp
+ * @return string rdms specific timestamp
+ */
+ public function to_timestamp($epoch)
+ {
+ return substr($this->adodb->DBTimeStamp($epoch), 1, -1);
+ }
+
+ /**
+ * Convert a rdms specific timestamp to a unix timestamp
+ *
+ * @param string rdms specific timestamp
+ * @return int unix timestamp
+ */
+ public function from_timestamp($timestamp)
+ {
+ return $this->adodb->UnixTimeStamp($timestamp);
+ }
+
+ /**
+ * Execute a query
+ *
+ * @param string $sql the query to be executed
+ * @param mixed $line the line method was called from - use __LINE__
+ * @param string $file the file method was called from - use __FILE__
+ * @return integer current query id if sucesful and null if fails
+ */
+ public function query($sql, $line = '', $file = '',$exec = false)
+ {
+ if ( !$this->adodb->isConnected() )
+ {
+ $this->connect();
+ }
+
+ try
+ {
+ $this->resultSet = $this->adodb->Execute($sql);
+ }
+
+ catch(Exception $e)
+ {
+ if ( $e && $this->Halt_On_Error == 'yes' )
+ {
+ if($file)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ else
+ {
+ trigger_error("$sql\n". $e->getMessage(), E_USER_ERROR);
+ }
+ $this->transaction_abort();
+ exit;
+ }
+ }
+
+ $this->delayPointer = true;
+ return true;
+ }
+
+ /**
+ * Execute a query with limited result set
+ *
+ * @param string $Query_String the query to be executed
+ * @param integer $offset row to start from
+ * @param integer $line the line method was called from - use __LINE__
+ * @param string $file the file method was called from - use __FILE__
+ * @param integer $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']
+ * @return integer current query id if sucesful and null if fails
+ */
+ public function limit_query($Query_String, $offset = -1, $line = '', $file = '', $num_rows = -1)
+ {
+ if ( (int) $num_rows <= 0 )
+ {
+ $num_rows = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'];
+ }
+
+ if ( !$this->adodb->isConnected() )
+ {
+ $this->connect();
+ }
+
+ try
+ {
+ $this->resultSet = $this->adodb->SelectLimit($Query_String, $num_rows, $offset);
+ }
+
+ catch(Exception $e)
+ {
+ if ( $e && $this->Halt_On_Error == 'yes' )
+ {
+ if($file)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ else
+ {
+ trigger_error("$sql\n". $e->getMessage(), E_USER_ERROR);
+ }
+ $this->transaction_abort();
+ exit;
+ }
+ }
+
+ $this->delayPointer = true;
+ return true;
+ }
+
+ /**
+ * Move to the next row in the results set
+ *
+ * @return bool was another row found?
+ */
+ public function next_record()
+ {
+ if($this->resultSet && $this->resultSet->RecordCount())
+ {
+ if($this->delayPointer)
+ {
+ $this->delayPointer = false;
+ $this->Record =& $this->resultSet->fields;
+ return true;
+ }
+
+ if(!$this->resultSet->EOF)
+ {
+ $row = $this->resultSet->MoveNext();
+ $this->Record =& $this->resultSet->fields;
+ return !!$row;
+ }
+ }
+ return false;
+ }
+
+ /**
+ * Move to position in result set
+ *
+ * @param int $pos required row (optional), default first row
+ * @return int 1 if sucessful or 0 if not found
+ */
+ public function seek($pos = 0)
+ {
+ if($this->resultSet)
+ {
+ return $this->resultSet->Move($pos);
+ }
+ return false;
+ }
+
+ /**
+ * Begin transaction
+ *
+ * @return integer|boolean current transaction id
+ */
+ public function transaction_begin()
+ {
+ $this->Transaction = $this->adodb->StartTrans();
+ return $this->Transaction;
+ }
+
+ /**
+ * Complete the transaction
+ *
+ * @return boolean True if sucessful, False if fails
+ */
+ public function transaction_commit()
+ {
+ $this->Transaction = false;
+ return $this->adodb->CompleteTrans();
+ }
+
+ /**
+ * Rollback the current transaction
+ *
+ * @return boolean True if sucessful, False if fails
+ */
+ public function transaction_abort()
+ {
+ $this->Transaction = false;
+ $this->adodb->FailTrans();
+ return $this->adodb->HasFailedTrans();
+ }
+
+ /**
+ * Find the primary key of the last insertion on the current db connection
+ *
+ * @param string $table name of table the insert was performed on
+ * @param string $field the autoincrement primary key of the table
+ * @return integer the id, -1 if fails
+ */
+ public function get_last_insert_id($table, $field ='')
+ {
+ switch ( $GLOBALS['phpgw_info']['server']['db_type'] )
+ {
+ case 'postgres':
+ $params = explode('.',$this->adodb->pgVersion);
+
+ if ($params[0] < 8 || ($params[0] == 8 && $params[1] ==0))
+ {
+ $oid = pg_getlastoid($this->adodb->_resultid);
+ if ($oid == -1)
+ {
+ return -1;
+ }
+
+ $result = @pg_Exec($this->adodb->_connectionID, "select $field from $table where oid=$oid");
+ }
+ else
+ {
+ $result = @pg_Exec($this->adodb->_connectionID, "select lastval()");
+ }
+
+ if (!$result)
+ {
+ return -1;
+ }
+
+ $Record = @pg_fetch_array($result, 0);
+
+ @pg_freeresult($result);
+ if (!is_array($Record)) /* OID not found? */
+ {
+ return -1;
+ }
+ return $Record[0];
+ break;
+ case 'mssql':
+ /* MSSQL uses a query to retrieve the last
+ * identity on the connection, so table and field are ignored here as well.
+ */
+ if(!isset($table) || $table == '' || !isset($field) || $field == '')
+ {
+ return -1;
+ }
+ $result = @mssql_query("select @@identity", $this->adodb->_queryID);
+ if(!$result)
+ {
+ return -1;
+ }
+ return mssql_result($result, 0, 0);
+ break;
+ default:
+ return $this->adodb->Insert_ID($table, $field);
+ }
+ }
+
+ /**
+ * Lock a table
+ *
+ * @param string $table name of table to lock
+ * @param string $mode type of lock required (optional), default write
+ * @return boolean True if sucessful, False if fails
+ */
+ public function lock($table, $mode='write')
+ {
+ //$this->adodb->BeginTrans();
+ }
+
+
+ /**
+ * Unlock a table
+ *
+ * @return boolean True if sucessful, False if fails
+ */
+ public function unlock()
+ {
+ //$this->adodb->CommitTrans();
+ }
+
+
+ /**
+ * Get the number of rows affected by last update
+ *
+ * @return integer number of rows
+ */
+ public function affected_rows()
+ {
+ return $this->adodb->Affected_Rows();
+ }
+
+ /**
+ * Number of rows in current result set
+ *
+ * @return integer number of rows
+ */
+ public function num_rows()
+ {
+ if($this->resultSet)
+ {
+ return $this->resultSet->RecordCount();
+ }
+ return 0;
+ }
+
+ /**
+ * Number of fields in current row
+ *
+ * @return integer number of fields
+ */
+ public function num_fields()
+ {
+ if($this->resultSet)
+ {
+ return $this->resultSet->fieldCount();
+ }
+ return 0;
+ }
+
+ /**
+ * Short hand for num_rows()
+ * @return integer Number of rows
+ * @see num_rows()
+ */
+ public function nf()
+ {
+ return $this->num_rows();
+ }
+
+ /**
+ * Short hand for print @see num_rows
+ */
+ public function np()
+ {
+ print $this->num_rows();
+ }
+
+ /**
+ * Return the value of a filed
+ *
+ * @param string $String name of field
+ * @param boolean $strip_slashes string escape chars from field(optional), default false
+ * @return string the field value
+ */
+ public function f($name, $strip_slashes = False)
+ {
+ if($this->resultSet && get_class($this->resultSet) != 'adorecordset_empty')
+ {
+ if( isset($this->resultSet->fields[$name]) )
+ {
+ if ($strip_slashes || ($this->auto_stripslashes && ! $strip_slashes))
+ {
+ return stripslashes($this->resultSet->fields[$name]);
+ }
+ else
+ {
+ return $this->resultSet->fields[$name];
+ }
+ }
+ return '';
+ }
+ }
+
+ /**
+ * Print the value of a field
+ *
+ * @param string $field name of field to print
+ * @param bool $strip_slashes string escape chars from field(optional), default false
+ */
+ public function p($field, $strip_slashes = True)
+ {
+ //echo "depi: p";
+ print $this->f($field, $strip_slashes);
+ }
+
+ /**
+ * Get the id for the next sequence - not implemented!
+ *
+ * @param string $seq_name name of the sequence
+ * @return integer sequence id
+ */
+ public function nextid($seq_name)
+ {
+ //echo "depi: nextid";
+ }
+
+ /**
+ * Get description of a table
+ *
+ * @param string $table name of table to describe
+ * @param boolean $full optional, default False summary information, True full information
+ * @return array Table meta data
+ */
+ public function metadata($table = '',$full = false)
+ {
+ if($this->debug)
+ {
+ //echo "depi: metadata";
+ }
+
+ if(!$this->adodb->IsConnected())
+ {
+ $this->connect();
+ }
+ if(!($return =& $this->adodb->MetaColumns($table,$full)))
+ {
+ $return = array();
+ }
+ return $return;
+
+ /*
+ * Due to compatibility problems with Table we changed the behavior
+ * of metadata();
+ * depending on $full, metadata returns the following values:
+ *
+ * - full is false (default):
+ * $result[]:
+ * [0]["table"] table name
+ * [0]["name"] field name
+ * [0]["type"] field type
+ * [0]["len"] field length
+ * [0]["flags"] field flags
+ *
+ * - full is true
+ * $result[]:
+ * ["num_fields"] number of metadata records
+ * [0]["table"] table name
+ * [0]["name"] field name
+ * [0]["type"] field type
+ * [0]["len"] field length
+ * [0]["flags"] field flags
+ * ["meta"][field name] index of field named "field name"
+ * The last one is used, if you have a field name, but no index.
+ * Test: if (isset($result['meta']['myfield'])) { ...
+ */
+ }
+
+ /**
+ * Returns an associate array of foreign keys, or false if not supported.
+ *
+ * @param string $table name of table to describe
+ * @param boolean $owner optional, default False. The optional schema or owner can be defined in $owner.
+ * @param boolean $upper optional, default False. If $upper is true, then the table names (array keys) are upper-cased.
+ * @return array Table meta data
+ */
+ public function MetaForeignKeys($table = '', $owner=false, $upper=false)
+ {
+ if(!$this->adodb->IsConnected())
+ {
+ $this->connect();
+ }
+ if(!($return =& $this->adodb->MetaForeignKeys($table, $owner, $upper)))
+ {
+ $return = array();
+ }
+ return $return;
+ }
+
+ /**
+ * Returns an associate array of foreign keys, or false if not supported.
+ *
+ * @param string $table name of table to describe
+ * @param boolean $primary optional, default False.
+ * @return array Index data
+ */
+
+ public function metaindexes($table, $primary = false)
+ {
+ if(!$this->adodb->IsConnected())
+ {
+ $this->connect();
+ }
+ if(!($return = $this->adodb->MetaIndexes($table, $primary)))
+ {
+ $return = array();
+ }
+ return $return;
+ }
+
+ /**
+ * Error handler
+ *
+ * @param string $msg error message
+ * @param int $line line of calling method/function (optional)
+ * @param string $file file of calling method/function (optional)
+ */
+ public function halt($msg, $line = '', $file = '')
+ {
+ $this->adodb->RollbackTrans();
+ }
+
+ /**
+ * Get a list of table names in the current database
+ *
+ * @return array list of the tables
+ */
+ public function table_names()
+ {
+ if(!$this->adodb->IsConnected())
+ {
+ $this->connect();
+ }
+
+ $return = $this->adodb->MetaTables('TABLES');
+ if ( !$return )
+ {
+ return array();
+ }
+ return $return;
+ }
+
+ /**
+ * Create a new database
+ *
+ * @param string $adminname Name of database administrator user (optional)
+ * @param string $adminpasswd Password for the database administrator user (optional)
+ * @returns bool was the new db created?
+ * @throws Exception invalid db-name
+ */
+ public function create_database($adminname = '', $adminpasswd = '')
+ {
+ //THIS IS CALLED BY SETUP DON'T KILL IT!
+ if ( $this->adodb->IsConnected() )
+ {
+ $this->adodb->Disconnect(); //close the dead connection to be safe
+ }
+
+ $this->adodb = newADOConnection($GLOBALS['phpgw_info']['server']['db_type']);
+ $this->adodb->NConnect($this->Host, $adminname, $adminpasswd);
+
+ if ( !$this->adodb->IsConnected() )
+ {
+ echo 'Connection FAILED<br />';
+ return False;
+ }
+
+ if( !preg_match('/^[a-z0-9_]+$/i', $this->Database) )
+ {
+ throw new Exception(lang('ERROR: the name %1 contains illegal charackter for cross platform db-support', $this->Database));
+ }
+
+ //create the db
+ $this->adodb->Execute("CREATE DATABASE {$this->Database}");
+
+ //Grant rights on the db
+ switch ($GLOBALS['phpgw_info']['server']['db_type'])
+ {
+ case 'mysql':
+ $this->adodb->Execute("GRANT ALL ON {$this->Database}.*"
+ . " TO {$this->User}@{$_SERVER['SERVER_NAME']}"
+ . " IDENTIFIED BY '{$this->Password}'");
+ default:
+ //do nothing
+ }
+ $this->adodb->Disconnect();
+ return True;
+ }
+
+ /**
+ * Execute prepared SQL statement for insert
+ *
+ * @param string $sql_string
+ * @param array $valueset values,id and datatypes for the insert
+ * Use type = PDO::PARAM_STR for strings and type = PDO::PARAM_INT for integers
+ * @return boolean TRUE on success or FALSE on failure
+ */
+
+ public function insert($sql_string, $valueset, $line = '', $file = '')
+ {
+ try
+ {
+ $stmt = $this->adodb->Prepare($sql_string);
+
+ foreach($valueset as $fields)
+ {
+ $values = array();
+ foreach($fields as $field => $entry)
+ {
+ $values[] = $entry['value'];
+ }
+ $this->adodb->Execute($stmt, $values);
+ }
+ }
+ catch(Exception $e)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ }
+
+ /**
+ * Execute prepared SQL statement for select
+ *
+ * @param string $sql_string
+ * @param array $params conditions for the select
+ * @return boolean TRUE on success or FALSE on failure
+ */
+
+ public function select($sql_string, $params, $line = '', $file = '')
+ {
+ try
+ {
+ if($this->fetchmode == 'ASSOC')
+ {
+ $this->adodb->SetFetchMode(ADODB_FETCH_ASSOC);
+ }
+ else
+ {
+ $this->adodb->SetFetchMode(ADODB_FETCH_BOTH);
+ }
+ $this->resultSet = $this->adodb->Execute($sql, $params);
+ }
+ catch(Exception $e)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ $this->delayPointer = true;
+ }
+ }
Added: people/sigurdne/modules/phpgwapi/trunk/inc/class.db_pdo.inc.php
===================================================================
--- people/sigurdne/modules/phpgwapi/trunk/inc/class.db_pdo.inc.php (rev 0)
+++ people/sigurdne/modules/phpgwapi/trunk/inc/class.db_pdo.inc.php 2010-01-05 21:07:59 UTC (rev 20918)
@@ -0,0 +1,922 @@
+<?php
+ /**
+ * Database abstraction class
+ * @author NetUSE AG Boris Erdmann, Kristian Koehntopp
+ * @author Dan Kuykendall, Dave Hall and others
+ * @author Sigurd Nes
+ * @copyright Copyright (C) 1998-2000 NetUSE AG Boris Erdmann, Kristian Koehntopp
+ * @copyright Portions Copyright (C) 2001-2006 Free Software Foundation, Inc. http://www.fsf.org/
+ * @license http://www.fsf.org/licenses/lgpl.html GNU Lesser General Public License
+ * @link http://www.sanisoft.com/phplib/manual/DB_sql.php
+ * @package phpgwapi
+ * @subpackage database
+ * @version $Id: class.db.inc.php 3450 2009-08-30 16:46:12Z sigurd $
+ */
+
+ if ( empty($GLOBALS['phpgw_info']['server']['db_type']) )
+ {
+ $GLOBALS['phpgw_info']['server']['db_type'] = 'mysql';
+ }
+
+ /**
+ * Database abstraction class to allow phpGroupWare to use multiple database backends
+ *
+ * @package phpgwapi
+ * @subpackage database
+ */
+ class phpgwapi_db extends phpgwapi_db_
+ {
+ var $resultSet;
+
+ /**
+ * Constructor
+ * @param string $query query to be executed (optional)
+ * @param string $db_type the database engine being used
+ */
+ public function __construct($query = null, $db_type = null)
+ {
+ parent::__construct($query, $db_type);
+ }
+
+ /**
+ * Called when object is cloned
+ */
+ public function __clone()
+ {
+
+ }
+
+ /**
+ * Destructor
+ */
+ public function __destruct()
+ {
+
+ }
+
+ /**
+ * Backward compatibility for get current connection id
+ * @return bool true
+ */
+ function link_id()
+ {
+ if(!$this->db)
+ {
+ $this->connect();
+ }
+ return true;
+ }
+
+ /**
+ * Open a connection to a database
+ *
+ * @param string $Database name of database to use (optional)
+ * @param string $Host database host to connect to (optional)
+ * @param string $User name of database user (optional)
+ * @param string $Password password for database user (optional)
+ */
+ public function connect($Database = null, $Host = null, $User = null, $Password = null)
+ {
+ if ( !is_null($Database) )
+ {
+ $this->Database = $Database;
+ }
+
+ if ( !is_null($Host) )
+ {
+ $this->Host = $Host;
+ }
+
+ if ( !is_null($User) )
+ {
+ $this->User = $User;
+ }
+
+ if ( !is_null($Password) )
+ {
+ $this->Password = $Password;
+ }
+
+ $persistent = isset($GLOBALS['phpgw_info']['server']['db_persistent']) && $GLOBALS['phpgw_info']['server']['db_persistent'] ? true : false;
+ switch ( $this->Type )
+ {
+ case 'postgres':
+ try
+ {
+ $this->db = new PDO("pgsql:dbname={$this->Database};host={$this->Host}", $this->User, $this->Password, array(PDO::ATTR_PERSISTENT => $persistent));
+ }
+ catch(PDOException $e){}
+ break;
+ case 'mysql':
+ try
+ {
+ $this->db = new PDO("mysql:host={$this->Host};dbname={$this->Database}", $this->User, $this->Password, array(PDO::ATTR_PERSISTENT => $persistent));
+ }
+ catch(PDOException $e){}
+ break;
+ case 'sybase':
+ case 'mssql':
+ /*
+ * On Windows, you should use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases,
+ * as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.
+ */
+ try
+ {
+ $this->db = new PDO("mssql:host={$this->Host},1433;dbname={$this->Database}", $this->User, $this->Password, array(PDO::ATTR_PERSISTENT => $persistent));
+ }
+ catch(PDOException $e){}
+ break;
+ case 'oracle':
+ try
+ {
+ $this->db = new PDO("OCI:dbname={$this->Database};charset=UTF-8", $this->User, $this->Password);
+ }
+ catch(PDOException $e){}
+ break;
+ case 'db2':
+ try
+ {
+ $port = 50000; // configurable?
+ $this->db = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE={$this->Database}; HOSTNAME={$this->Host};PORT=50000;PROTOCOL=TCPIP;", $this->User, $this->Password);
+ }
+ catch(PDOException $e){}
+ break;
+ case 'MSAccess':
+ try
+ {
+ $this->db = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin"); // FIXME: parameter for database location
+ }
+ catch(PDOException $e){}
+ break;
+ case 'dblib':
+ try
+ {
+ $port = 10060; // configurable?
+ $this->db = new PDO("dblib:host={$this->Host}:{$port};dbname={$this->Database}", $this->User, $this->Password);
+ }
+ catch(PDOException $e){}
+ break;
+ case 'Firebird':
+ try
+ {
+ $this->db = new PDO("firebird:dbname=localhost:C:\Programs\Firebird\DATABASE.FDB", $this->User, $this->Password);// FIXME: parameter for database location
+ }
+ catch(PDOException $e){}
+ break;
+ case 'Informix':
+ //connect to an informix database cataloged as InformixDB in odbc.ini
+ try
+ {
+ $this->db = new PDO("informix:DSN=InformixDB", $this->User, $this->Password);
+ }
+ catch(PDOException $e){}
+ break;
+ case 'SQLite':
+ try
+ {
+ $this->db = new PDO("sqlite:/path/to/database.sdb"); // FIXME: parameter for database location
+ }
+ catch(PDOException $e){}
+ break;
+ case 'odbc':
+ try
+ {
+ $dsn = 'something';// FIXME
+ /*$dsn refers to the $dsn data source configured in the ODBC driver manager.*/
+ $this->db = new PDO("odbc:DSN={$dsn}", $this->User, $this->Password);
+ // $this->db = new PDO("odbc:{$dsn}", $this->User, $this->Password);
+ }
+ catch(PDOException $e){}
+ break;
+ default:
+ //do nothing for now
+ }
+
+ if ( isset($e) && $e )
+ {
+ echo 'could not connect to server';
+// echo $e->getMessage();
+ exit;
+ }
+ if($this->Halt_On_Error == 'yes')
+ {
+ $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ }
+ }
+
+ /**
+ * Legacy supprt for quyering metadata from database
+ *
+ */
+ protected function _connect_adodb()
+ {
+ require_once PHPGW_API_INC . '/adodb/adodb.inc.php';
+ $this->adodb = newADOConnection($this->Type);
+ $this->adodb->SetFetchMode(ADODB_FETCH_BOTH);
+ return @$this->adodb->connect($this->Host, $this->User, $this->Password, $this->Database);
+ }
+
+ /**
+ * Close a connection to a database - only needed for persistent connections
+ */
+ public function disconnect()
+ {
+ $this->db = null;
+ }
+
+ /**
+ * Escape strings before sending them to the database
+ *
+ * @param string $str the string to be escaped
+ * @return string escaped sting
+ */
+ public function db_addslashes($str)
+ {
+ if ( is_null($str) )
+ {
+ return '';
+ }
+
+ if ( !is_object($this->db) ) //workaround
+ {
+ return addslashes($str);
+ }
+
+ return substr($this->db->quote($str), 1, -1);
+ }
+
+ /**
+ * Convert a unix timestamp to a rdms specific timestamp
+ *
+ * @param int unix timestamp
+ * @return string rdms specific timestamp
+ */
+ public function to_timestamp($epoch)
+ {
+ return date($this->datetime_format(), $epoch);
+ }
+
+ /**
+ * Convert a rdms specific timestamp to a unix timestamp
+ *
+ * @param string rdms specific timestamp
+ * @return int unix timestamp
+ */
+ public function from_timestamp($timestamp)
+ {
+ return strtotime($timestamp);
+ }
+
+
+ /**
+ * Execute a query
+ *
+ * @param string $sql the query to be executed
+ * @param mixed $line the line method was called from - use __LINE__
+ * @param string $file the file method was called from - use __FILE__
+ * @param bool $exec true for exec, false for query
+ * @return integer current query id if sucesful and null if fails
+ */
+ public function query($sql, $line = '', $file = '', $exec = false)
+ {
+
+ if ( !$this->db )
+ {
+ $this->connect();
+ }
+
+ if(!$exec)
+ {
+ if(preg_match('/(^INSERT INTO|^DELETE FROM|^CREATE TABLE|^DROP TABLE|^ALTER TABLE|^UPDATE)/i', $sql)) // need it for MySQL
+ {
+ $exec = true;
+ }
+ }
+
+ try
+ {
+ if($exec)
+ {
+ $this->affected_rows = $this->db->exec($sql);
+ return true;
+ }
+ else
+ {
+ $statement_object = $this->db->query($sql);
+ if($this->fetchmode == 'ASSOC')
+ {
+ $this->resultSet = $statement_object->fetchAll(PDO::FETCH_ASSOC);
+ }
+ else
+ {
+ $this->resultSet = $statement_object->fetchAll(PDO::FETCH_BOTH);
+ }
+ }
+ }
+
+ catch(PDOException $e)
+ {
+ if ( $e && $this->Halt_On_Error == 'yes' )
+ {
+ if($file)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ else
+ {
+ trigger_error("$sql\n". $e->getMessage(), E_USER_ERROR);
+ }
+ $this->transaction_abort();
+ exit;
+ }
+ }
+
+ $this->delayPointer = true;
+ return true;
+ }
+
+ /**
+ * Execute a query with limited result set
+ *
+ * @param string $Query_String the query to be executed
+ * @param integer $offset row to start from
+ * @param integer $line the line method was called from - use __LINE__
+ * @param string $file the file method was called from - use __FILE__
+ * @param integer $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']
+ * @return integer current query id if sucesful and null if fails
+ */
+
+ function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = 0)
+ {
+ $offset = intval($offset);
+ $num_rows = intval($num_rows);
+
+ if ($num_rows == 0)
+ {
+ $maxmatches = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'];
+ $num_rows = (isset($maxmatches)?intval($maxmatches):15);
+ }
+
+ if( $this->Type == 'mssql' )
+ {
+ $Query_String = str_replace('SELECT ', 'SELECT TOP ', $Query_String);
+ $Query_String = str_replace('SELECT TOP DISTINCT', 'SELECT DISTINCT TOP ', $Query_String);
+ $Query_String = str_replace('TOP ', 'TOP ' . ($offset + $num_rows) . ' ', $Query_String);
+
+ }
+ else
+ {
+ if ($offset == 0)
+ {
+ $Query_String .= ' LIMIT ' . $num_rows;
+ }
+ else
+ {
+ $Query_String .= ' LIMIT ' . $num_rows . ' OFFSET ' . $offset;
+ }
+ }
+
+ if ($this->debug)
+ {
+ printf("Debug: limit_query = %s<br />offset=%d, num_rows=%d<br />\n", $Query_String, $offset, $num_rows);
+ }
+
+ try
+ {
+ $statement_object = $this->db->query($Query_String);
+ if($this->fetchmode == 'ASSOC')
+ {
+ $this->resultSet = $statement_object->fetchAll(PDO::FETCH_ASSOC);
+ }
+ else
+ {
+ $this->resultSet = $statement_object->fetchAll(PDO::FETCH_BOTH);
+ }
+ }
+
+ catch(PDOException $e)
+ {
+ if ( $e && $this->Halt_On_Error == 'yes' )
+ {
+ if($file)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ else
+ {
+ trigger_error("$sql\n". $e->getMessage(), E_USER_ERROR);
+ }
+ $this->transaction_abort();
+ exit;
+ }
+ }
+
+ $this->delayPointer = true;
+ return true;
+ }
+
+ /**
+ * Move to the next row in the results set
+ *
+ * @return bool was another row found?
+ */
+ public function next_record()
+ {
+ if($this->resultSet && current($this->resultSet))
+ {
+ if($this->delayPointer)
+ {
+ $this->delayPointer = false;
+ $this->Record = current($this->resultSet);
+ return true;
+ }
+
+ $row = next($this->resultSet);
+ $this->Record =& $row;
+ return !!$row;
+ }
+ return false;
+ }
+
+ /**
+ * Move to position in result set
+ *
+ * @param int $pos required row (optional), default first row
+ * @return int 1 if sucessful or 0 if not found
+ */
+ public function seek($pos = 0)
+ {
+ if($this->resultSet)
+ {
+ reset($this->resultSet);
+ for ($i=0; $i<$pos; $i++)
+ {
+ $row = next($this->resultSet);
+ }
+ return $row;
+ }
+ return false;
+ }
+
+ /**
+ * Begin transaction
+ *
+ * @return integer|boolean current transaction id
+ */
+ public function transaction_begin()
+ {
+/*
+ $bt = debug_backtrace();
+ echo "<b>db::{$bt[0]['function']} Called from file: {$bt[0]['file']} line: {$bt[0]['line']}</b><br/>";
+ unset($bt);
+*/
+ $this->Transaction = $this->db->beginTransaction();
+ return $this->Transaction;
+ }
+
+ /**
+ * Complete the transaction
+ *
+ * @return boolean True if sucessful, False if fails
+ */
+ public function transaction_commit()
+ {
+/*
+ $bt = debug_backtrace();
+ echo "<b>db::{$bt[0]['function']} Called from file: {$bt[0]['file']} line: {$bt[0]['line']}</b><br/>";
+ unset($bt);
+*/
+ $this->Transaction = false;
+ return $this->db->commit();
+ }
+
+ /**
+ * Rollback the current transaction
+ *
+ * @return boolean True if sucessful, False if fails
+ */
+ public function transaction_abort()
+ {
+ $this->Transaction = false;
+ return $this->db->rollBack();
+ }
+
+ /**
+ * Find the value of the last insertion on the current db connection
+ * To use this function safely in Postgresql you MUST wrap it in a beginTransaction() commit() block
+ *
+ * @param string $table name of table the insert was performed on
+ * @param string $field not needed - kept for backward compatibility
+ * @return integer the id, -1 if fails
+ */
+ public function get_last_insert_id($table, $field = '')
+ {
+ switch ( $GLOBALS['phpgw_info']['server']['db_type'] )
+ {
+ case 'postgres':
+ $sequence = $this->_get_sequence_field_for_table($table, $field);
+ $ret = $this->db->lastInsertId($sequence);
+ break;
+ case 'mssql':
+ $this->query("SELECT @@identity", __LINE__, __FILE__);
+ $this->next_record();
+ $ret = $this->f(0);
+ break;
+ default:
+ $ret = $this->db->lastInsertId();
+ }
+
+ if($ret)
+ {
+ return $ret;
+ }
+ return -1;
+ }
+
+ /**
+ * Find the name of sequense for postgres
+ *
+ * @param string $table name of table
+ * @return string name of the sequense, false if fails
+ */
+ protected function _get_sequence_field_for_table($table, $field ='')
+ {
+ //old naming of sequenses
+ $sql = "SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*'"
+ . " AND relname = '{$table}_{$field}_seq' AND relkind='S' ORDER BY relname";
+ $this->query($sql,__LINE__,__FILE__);
+ if ($this->next_record())
+ {
+ return $this->f('relname');
+ }
+ $sql = "SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*'"
+ . " AND relname = 'seq_{$table}' AND relkind='S' ORDER BY relname";
+ $this->query($sql,__LINE__,__FILE__);
+ if ($this->next_record())
+ {
+ return $this->f('relname');
+ }
+ return false;
+ }
+
+
+ /**
+ * Lock a table
+ *
+ * @param string $table name of table to lock
+ * @param string $mode type of lock required (optional), default write
+ * @return boolean True if sucessful, False if fails
+ */
+ public function lock($table, $mode='write')
+ {
+ $this->transaction_begin();
+ }
+
+
+ /**
+ * Unlock a table
+ *
+ * @return boolean True if sucessful, False if fails
+ */
+ public function unlock()
+ {
+ $this->transaction_commit();
+ }
+
+
+ /**
+ * Get the number of rows affected by last update
+ *
+ * @return integer number of rows
+ */
+ public function affected_rows()
+ {
+ return $this->affected_rows;
+ }
+
+ /**
+ * Number of rows in current result set
+ *
+ * @return integer number of rows
+ */
+ public function num_rows()
+ {
+ if($this->resultSet)
+ {
+ return count($this->resultSet);
+ }
+ return 0;
+ }
+
+ /**
+ * Number of fields in current row
+ *
+ * @return integer number of fields
+ */
+ public function num_fields()
+ {
+ if($this->resultSet)
+ {
+ return $this->resultSet->fieldCount();
+ }
+ return 0;
+ }
+
+ /**
+ * Short hand for num_rows()
+ * @return integer Number of rows
+ * @see num_rows()
+ */
+ public function nf()
+ {
+ return $this->num_rows();
+ }
+
+ /**
+ * Short hand for print @see num_rows
+ */
+ public function np()
+ {
+ print $this->num_rows();
+ }
+
+ /**
+ * Return the value of a filed
+ *
+ * @param string $String name of field
+ * @param boolean $strip_slashes string escape chars from field(optional), default false
+ * @return string the field value
+ */
+ public function f($name, $strip_slashes = False)
+ {
+ if($this->resultSet)
+ {
+ if( isset($this->Record[$name]) )
+ {
+ if ($strip_slashes || ($this->auto_stripslashes && ! $strip_slashes))
+ {
+ return stripslashes($this->Record[$name]);
+ }
+ else
+ {
+ return $this->Record[$name];
+ }
+ }
+ return '';
+ }
+ }
+
+ /**
+ * Print the value of a field
+ *
+ * @param string $field name of field to print
+ * @param bool $strip_slashes string escape chars from field(optional), default false
+ */
+ public function p($field, $strip_slashes = True)
+ {
+ //echo "depi: p";
+ print $this->f($field, $strip_slashes);
+ }
+
+ /**
+ * Get the id for the next sequence - not implemented!
+ *
+ * @param string $seq_name name of the sequence
+ * @return integer sequence id
+ */
+ public function nextid($seq_name)
+ {
+ //echo "depi: nextid";
+ }
+
+ /**
+ * Get description of a table
+ *
+ * @param string $table name of table to describe
+ * @param boolean $full optional, default False summary information, True full information
+ * @return array Table meta data
+ */
+ public function metadata($table = '',$full = false)
+ {
+ if(!$this->adodb || !$this->adodb->IsConnected())
+ {
+ $this->_connect_adodb();
+ }
+ if(!($return = $this->adodb->MetaColumns($table,$full)))
+ {
+ $return = array();
+ }
+ $this->adodb->close();
+ return $return;
+
+ /*
+ * Due to compatibility problems with Table we changed the behavior
+ * of metadata();
+ * depending on $full, metadata returns the following values:
+ *
+ * - full is false (default):
+ * $result[]:
+ * [0]["table"] table name
+ * [0]["name"] field name
+ * [0]["type"] field type
+ * [0]["len"] field length
+ * [0]["flags"] field flags
+ *
+ * - full is true
+ * $result[]:
+ * ["num_fields"] number of metadata records
+ * [0]["table"] table name
+ * [0]["name"] field name
+ * [0]["type"] field type
+ * [0]["len"] field length
+ * [0]["flags"] field flags
+ * ["meta"][field name] index of field named "field name"
+ * The last one is used, if you have a field name, but no index.
+ * Test: if (isset($result['meta']['myfield'])) { ...
+ */
+ }
+
+ /**
+ * Returns an associate array of foreign keys, or false if not supported.
+ *
+ * @param string $table name of table to describe
+ * @param boolean $owner optional, default False. The optional schema or owner can be defined in $owner.
+ * @param boolean $upper optional, default False. If $upper is true, then the table names (array keys) are upper-cased.
+ * @return array Table meta data
+ */
+ public function MetaForeignKeys($table, $owner=false, $upper=false)
+ {
+ if(!$this->adodb || !$this->adodb->IsConnected())
+ {
+ $this->_connect_adodb();
+ }
+ if(!($return = $this->adodb->MetaForeignKeys($table, $owner, $upper)))
+ {
+ $return = array();
+ }
+ $this->adodb->close();
+ return $return;
+ }
+ /**
+ * Returns an associate array of foreign keys, or false if not supported.
+ *
+ * @param string $table name of table to describe
+ * @param boolean $primary optional, default False.
+ * @return array Index data
+ */
+
+ public function metaindexes($table, $primary = false)
+ {
+ if(!$this->adodb || !$this->adodb->IsConnected())
+ {
+ $this->_connect_adodb();
+ }
+ if(!($return = $this->adodb->MetaIndexes($table, $primary)))
+ {
+ $return = array();
+ }
+ $this->adodb->close();
+ return $return;
+ }
+
+ /**
+ * Error handler
+ *
+ * @param string $msg error message
+ * @param int $line line of calling method/function (optional)
+ * @param string $file file of calling method/function (optional)
+ */
+ public function halt($msg, $line = '', $file = '')
+ {
+ $this->db->rollBack();
+ }
+
+ /**
+ * Get a list of table names in the current database
+ *
+ * @return array list of the tables
+ */
+ public function table_names()
+ {
+ if(!$this->adodb || !$this->adodb->IsConnected())
+ {
+ $this->_connect_adodb();
+ }
+
+ $return = $this->adodb->MetaTables('TABLES');
+ $this->adodb->close();
+ if ( !$return )
+ {
+ return array();
+ }
+ return $return;
+ }
+
+
+ /**
+ * Create a new database
+ *
+ * @param string $adminname Name of database administrator user (optional)
+ * @param string $adminpasswd Password for the database administrator user (optional)
+ * @returns bool was the new db created?
+ * @throws Exception invalid db-name
+ */
+ public function create_database($adminname = '', $adminpasswd = '')
+ {
+ //THIS IS CALLED BY SETUP DON'T KILL IT!
+ if ( $this->db )
+ {
+ $this->db = null; //close the dead connection to be safe
+ }
+
+ $this->connect();
+
+ if ( !$this->db )
+ {
+ echo 'Connection FAILED<br />';
+ return False;
+ }
+
+ if( !preg_match('/^[a-z0-9_]+$/i', $this->Database) )
+ {
+ throw new Exception(lang('ERROR: the name %1 contains illegal charackter for cross platform db-support', $this->Database));
+ }
+
+ //create the db
+ $this->db->exec("CREATE DATABASE {$this->Database}");
+
+ //Grant rights on the db
+ switch ($GLOBALS['phpgw_info']['server']['db_type'])
+ {
+ case 'mysql':
+ $this->db->exec("GRANT ALL ON {$this->Database}.*"
+ . " TO {$this->User}@{$_SERVER['SERVER_NAME']}"
+ . " IDENTIFIED BY '{$this->Password}'");
+ default:
+ //do nothing
+ }
+ $this->db = null;
+ return True;
+ }
+
+ /**
+ * Execute prepared SQL statement for insert
+ *
+ * @param string $sql_string
+ * @param array $valueset values,id and datatypes for the insert
+ * Use type = PDO::PARAM_STR for strings and type = PDO::PARAM_INT for integers
+ * @return boolean TRUE on success or FALSE on failure
+ */
+
+ public function insert($sql_string, $valueset, $line = '', $file = '')
+ {
+ try
+ {
+ $statement_object = $this->db->prepare($sql_string);
+ foreach($valueset as $fields)
+ {
+ foreach($fields as $field => $entry)
+ {
+ $statement_object->bindParam($field, $entry['value'], $entry['type']);
+ }
+ $ret = $statement_object->execute();
+ }
+ }
+
+ catch(PDOException $e)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ return $ret;
+ }
+
+ /**
+ * Execute prepared SQL statement for select
+ *
+ * @param string $sql_string
+ * @param array $params conditions for the select
+ * @return boolean TRUE on success or FALSE on failure
+ */
+
+ public function select($sql_string, $params, $line = '', $file = '')
+ {
+ try
+ {
+ $statement_object = $this->db->prepare($sql_string);
+ $statement_object->execute($params);
+ if($this->fetchmode == 'ASSOC')
+ {
+ $this->resultSet = $statement_object->fetchAll(PDO::FETCH_ASSOC);
+ }
+ else
+ {
+ $this->resultSet = $statement_object->fetchAll(PDO::FETCH_BOTH);
+ }
+ }
+ catch(PDOException $e)
+ {
+ trigger_error('Error: ' . $e->getMessage() . "<br>SQL: $sql\n in File: $file\n on Line: $line\n", E_USER_ERROR);
+ }
+ $this->delayPointer = true;
+ }
+ }