Hi,
I updated the MDB2 wrapper ..
It also seems to work with the session class, so the mdb2 specific
handling can be disabled in main.inc.
There is one problem with the affectedRows() method.
MDB2 (as well as the new PDO extension) require the use of the exec()
method for DML (INSERT/UPDATE/DELETE..) and then it directly returns the
affected rows number.
To accommodate this the wrapper would need to be expanded. Either
query() needs to get another parameter or even better there needs to be
an exec() method in the wrapper that behaves like MDB2/PDO. Its easy to
get the same behavior with DB.
I also attached an xml schema file. This file can use used with
MDB2_Schema (or ezc/DatabaseSchema) as a single portable RDBMS schema
file from which both mentioned packages can even generate alter
statements. So instead of having to maintain multiple files per RDBMS
and one installation/upgrading file for each you fold everything into a
single file.
Do note however that currently there is no way to handle UPDATE/DELETE
statements, and there is no support for FOREIGN KEY's at this point. If
there is interest I can help in writing an installer for this. The file
can also be used at packaging time to generate the SQL files you guys
currently ship (http://pooteeweet.org/blog/312).
regards,
Lukas
<?php
/*
+-----------------------------------------------------------------------+
| program/include/rcube_db.inc |
| |
| This file is part of the RoundCube Webmail client |
| Copyright (C) 2005, RoundCube Dev. - Switzerland |
| Licensed under the GNU GPL |
| |
| PURPOSE: |
| PEAR:DB wrapper class that implements PEAR DB functions |
| See http://pear.php.net/package/DB |
| |
+-----------------------------------------------------------------------+
| Author: David Saez Padros <[EMAIL PROTECTED]> |
+-----------------------------------------------------------------------+
$Id: rcube_db.inc,v 1.16 2006/02/19 17:39:26 roundcube Exp $
*/
/**
* Obtain the PEAR::DB class that is used for abstraction
*/
require_once('MDB2.php');
/**
* Database independent query interface
*
* This is a wrapper for the PEAR::DB class
*
* @package RoundCube Webmail
* @author David Saez Padros <[EMAIL PROTECTED]>
* @author Thomas Bruederli <[EMAIL PROTECTED]>
* @version 1.16
* @link http://pear.php.net/package/DB
*/
class rcube_db
{
var $db_dsnw; // DSN for write operations
var $db_dsnr; // DSN for read operations
var $db_connected = false; // Already connected ?
var $db_mode = ''; // Connection mode
var $db_handle = 0; // Connection handle
var $a_query_results = array('dummy');
var $last_res_id = 0;
/**
* Object constructor
*
* @param string DSN for read/write operations
* @param string Optional DSN for read only operations
*/
function __construct($db_dsnw, $db_dsnr='')
{
if ($db_dsnr=='')
$db_dsnr=$db_dsnw;
$this->db_dsnw = $db_dsnw;
$this->db_dsnr = $db_dsnr;
$dsn_array = MDB2::parseDSN($db_dsnw);
$this->db_provider = $dsn_array['phptype'];
}
/**
* PHP 4 object constructor
*
* @see rcube_MDB2::__construct
*/
function rcube_db($db_dsnw,$db_dsnr='')
{
$this->__construct($db_dsnw,$db_dsnr);
}
/**
* Connect to specific database
*
* @param string DSN for DB connections
* @return object PEAR database handle
* @access private
*/
function dsn_connect($dsn)
{
// Use persistent connections if available
$dbh = MDB2::connect($dsn, array('persistent' => TRUE, 'portability' =>
MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL));
if (PEAR::isError($dbh))
{
raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__,
'file' => __FILE__,
'message' => $dbh->getMessage()), TRUE, FALSE);
}
else if ($this->db_provider=='sqlite')
{
$dsn_array = MDB2::parseDSN($dsn);
if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials))
$this->_sqlite_create_database($dbh, $this->sqlite_initials);
}
return $dbh;
}
/**
* Connect to appropiate databse
* depending on the operation
*
* @param string Connection mode (r|w)
* @access public
*/
function db_connect($mode)
{
$this->db_mode = $mode;
// Already connected
if ($this->db_connected)
{
// no replication, current connection is ok
if ($this->db_dsnw==$this->db_dsnr)
return;
// connected to master, current connection is ok
if ($this->db_mode=='w')
return;
// Same mode, current connection is ok
if ($this->db_mode==$mode)
return;
}
if ($mode=='r')
$dsn = $this->db_dsnr;
else
$dsn = $this->db_dsnw;
$this->db_handle = $this->dsn_connect($dsn);
$this->db_connected = true;
}
/**
* Execute a SQL query
*
* @param string SQL query to execute
* @param mixed Values to be inserted in query
* @return number Query handle identifier
* @access public
*/
function query()
{
$params = func_get_args();
$query = array_shift($params);
return $this->_query($query, 0, 0, $params);
}
/**
* Execute a SQL query with limits
*
* @param string SQL query to execute
* @param number Offset for LIMIT statement
* @param number Number of rows for LIMIT statement
* @param mixed Values to be inserted in query
* @return number Query handle identifier
* @access public
*/
function limitquery()
{
$params = func_get_args();
$query = array_shift($params);
$offset = array_shift($params);
$numrows = array_shift($params);
return $this->_query($query, $offset, $numrows, $params);
}
/**
* Execute a SQL query with limits
*
* @param string SQL query to execute
* @param number Offset for LIMIT statement
* @param number Number of rows for LIMIT statement
* @param array Values to be inserted in query
* @return number Query handle identifier
* @access private
*/
function _query($query, $offset, $numrows, $params)
{
// Read or write ?
if (strtolower(trim(substr($query,0,6)))=='select')
$mode='r';
else
$mode='w';
$this->db_connect($mode);
if ($this->db_provider == 'sqlite')
$this->_sqlite_prepare();
if ($numrows || $offset)
$result = $this->db_handle->setLimit($numrows,$offset);
if (empty($params)) {
$result = $this->db_handle->query($query);
} else {
$params = (array)$params;
$q = $this->db_handle->prepare($query);
$result = $q->execute($params);
$q->free();
}
// add result, even if it's an error
return $this->_add_result($result);
}
/**
* Get number of rows for a SQL query
* If no query handle is specified, the last query will be taken as reference
*
* @param number Optional query handle identifier
* @return mixed Number of rows or FALSE on failure
* @access public
*/
function num_rows($res_id=NULL)
{
if (!$this->db_handle)
return FALSE;
if ($result = $this->_get_result($res_id))
return $result->numRows();
else
return FALSE;
}
/**
* Get number of affected rows fort he last query
*
* @return mixed Number of rows or FALSE on failure
* @access public
*/
function affected_rows($result = null)
{
if (!$this->db_handle)
return FALSE;
return $result;
}
/**
* Get last inserted record ID
* For Postgres databases, a sequence name is required
*
* @param string Sequence name for increment
* @return mixed ID or FALSE on failure
* @access public
*/
function insert_id($sequence = '')
{
if (!$this->db_handle || $this->db_mode=='r')
return FALSE;
return $this->db_handle->lastInsertID($sequence);
}
/**
* Get an associative array for one row
* If no query handle is specified, the last query will be taken as reference
*
* @param number Optional query handle identifier
* @return mixed Array with col values or FALSE on failure
* @access public
*/
function fetch_assoc($res_id=NULL)
{
$result = $this->_get_result($res_id);
return $this->_fetch_row($result, MDB2_FETCHMODE_ASSOC);
}
/**
* Get an index array for one row
* If no query handle is specified, the last query will be taken as reference
*
* @param number Optional query handle identifier
* @return mixed Array with col values or FALSE on failure
* @access public
*/
function fetch_array($res_id=NULL)
{
$result = $this->_get_result($res_id);
return $this->_fetch_row($result, MDB2_FETCHMODE_ORDERED);
}
/**
* Get co values for a result row
*
* @param object Query result handle
* @param number Fetch mode identifier
* @return mixed Array with col values or FALSE on failure
* @access private
*/
function _fetch_row($result, $mode)
{
if (PEAR::isError($result))
{
raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__,
'file' => __FILE__,
'message' => $this->db_link->getMessage()), TRUE,
FALSE);
return FALSE;
}
return $result->fetchRow($mode);
}
/**
* Formats input so it can be safely used in a query
*
* @param mixed Value to quote
* @return string Quoted/converted string for use in query
* @access public
*/
function quote($input, $type = null)
{
// create DB handle if not available
if (!$this->db_handle)
$this->db_connect('r');
// escape pear identifier chars
$rep_chars = array('?' => '\?',
'!' => '\!',
'&' => '\&');
return $this->db_handle->quote($input, $type);
}
/**
* Quotes a string so it can be safely used as a table or column name
*
* @param string Value to quote
* @return string Quoted string for use in query
* @deprecated Replaced by rcube_MDB2::quote_identifier
* @see rcube_MDB2::quote_identifier
* @access public
*/
function quoteIdentifier($str)
{
return $this->quote_identifier($str);
}
/**
* Quotes a string so it can be safely used as a table or column name
*
* @param string Value to quote
* @return string Quoted string for use in query
* @access public
*/
function quote_identifier($str)
{
if (!$this->db_handle)
$this->db_connect('r');
return $this->db_handle->quoteIdentifier($str);
}
/**
* Return SQL statement to convert a field value into a unix timestamp
*
* @param string Field name
* @return string SQL statement to use in query
* @access public
*/
function unixtimestamp($field)
{
switch($this->db_provider)
{
case 'pgsql':
return "EXTRACT (EPOCH FROM $field)";
break;
default:
return "UNIX_TIMESTAMP($field)";
}
}
/**
* Return SQL statement to convert from a unix timestamp
*
* @param string Field name
* @return string SQL statement to use in query
* @access public
*/
function fromunixtime($timestamp)
{
switch($this->db_provider)
{
case 'mysqli':
case 'mysql':
case 'sqlite':
return "FROM_UNIXTIME($timestamp)";
default:
return date("'Y-m-d H:i:s'", $timestamp);
}
}
/**
* Adds a query result and returns a handle ID
*
* @param object Query handle
* @return mixed Handle ID or FALE on failure
* @access private
*/
function _add_result($res)
{
// sql error occured
if (PEAR::isError($res))
{
raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__,
'file' => __FILE__,
'message' => $res->getMessage() . " Query: " .
substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE,
FALSE);
return FALSE;
}
else
{
$res_id = sizeof($this->a_query_results);
$this->a_query_results[$res_id] = $res;
$this->last_res_id = $res_id;
return $res_id;
}
}
/**
* Resolves a given handle ID and returns the according query handle
* If no ID is specified, the last ressource handle will be returned
*
* @param number Handle ID
* @return mixed Ressource handle or FALE on failure
* @access private
*/
function _get_result($res_id=NULL)
{
if ($res_id==NULL)
$res_id = $this->last_res_id;
if ($res_id && isset($this->a_query_results[$res_id]))
return $this->a_query_results[$res_id];
else
return FALSE;
}
/**
* Create a sqlite database from a file
*
* @param object SQLite database handle
* @param string File path to use for DB creation
* @access private
*/
function _sqlite_create_database($dbh, $file_name)
{
if (empty($file_name) || !is_string($file_name))
return;
$data = '';
if ($fd = fopen($file_name, 'r'))
{
$data = fread($fd, filesize($file_name));
fclose($fd);
}
if (strlen($data))
sqlite_exec($dbh->connection, $data);
}
/**
* Add some proprietary database functions to the current SQLite handle
* in order to make it MySQL compatible
*
* @access private
*/
function _sqlite_prepare()
{
include_once('include/rcube_sqlite.inc');
// we emulate via callback some missing MySQL function
sqlite_create_function($this->db_handle->connection, "from_unixtime",
"rcube_sqlite_from_unixtime");
sqlite_create_function($this->db_handle->connection, "unix_timestamp",
"rcube_sqlite_unix_timestamp");
sqlite_create_function($this->db_handle->connection, "now",
"rcube_sqlite_now");
sqlite_create_function($this->db_handle->connection, "md5",
"rcube_sqlite_md5");
}
} // end class rcube_db
?>
<?xml version="1.0" encoding="ISO-8859-1" ?>
<database>
<name>roundcube2</name>
<create>true</create>
<overwrite>false</overwrite>
<table>
<name>cache</name>
<declaration>
<field>
<name>cache_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
<autoincrement>1</autoincrement>
</field>
<field>
<name>user_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>session_id</name>
<type>text</type>
<length>40</length>
<notnull>false</notnull>
<default></default>
</field>
<field>
<name>cache_key</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>created</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>data</name>
<type>clob</type>
<notnull>true</notnull>
</field>
<index>
<name>user_id</name>
<field>
<name>user_id</name>
<sorting>ascending</sorting>
</field>
</index>
<index>
<name>cache_key</name>
<field>
<name>cache_key</name>
<sorting>ascending</sorting>
</field>
</index>
<index>
<name>session_id</name>
<field>
<name>session_id</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>
<table>
<name>contacts</name>
<declaration>
<field>
<name>contact_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
<autoincrement>1</autoincrement>
</field>
<field>
<name>user_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>changed</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>del</name>
<type>integer</type>
<unsigned>false</unsigned>
<length>1</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>name</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>email</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>firstname</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>surname</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>vcard</name>
<type>clob</type>
<notnull>true</notnull>
</field>
<index>
<name>user_id</name>
<field>
<name>user_id</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>
<table>
<name>identities</name>
<declaration>
<field>
<name>identity_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
<autoincrement>1</autoincrement>
</field>
<field>
<name>user_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>del</name>
<type>integer</type>
<unsigned>false</unsigned>
<length>1</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>standard</name>
<type>boolean</type>
<unsigned>false</unsigned>
<length>1</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>name</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>organization</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>email</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>reply-to</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>bcc</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>signature</name>
<type>clob</type>
<notnull>true</notnull>
</field>
<index>
<name>user_id</name>
<field>
<name>user_id</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>
<table>
<name>messages</name>
<declaration>
<field>
<name>message_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
<autoincrement>1</autoincrement>
</field>
<field>
<name>user_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>del</name>
<type>integer</type>
<unsigned>false</unsigned>
<length>1</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>cache_key</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>created</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>idx</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>uid</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>subject</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>from</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>to</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>cc</name>
<type>text</type>
<length>255</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>date</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>size</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>
<field>
<name>headers</name>
<type>clob</type>
<notnull>true</notnull>
</field>
<field>
<name>body</name>
<type>clob</type>
<notnull>false</notnull>
</field>
<index>
<name>user_id</name>
<field>
<name>user_id</name>
<sorting>ascending</sorting>
</field>
</index>
<index>
<name>cache_key</name>
<field>
<name>cache_key</name>
<sorting>ascending</sorting>
</field>
</index>
<index>
<name>idx</name>
<field>
<name>idx</name>
<sorting>ascending</sorting>
</field>
</index>
<index>
<name>uid</name>
<field>
<name>uid</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>
<table>
<name>session</name>
<declaration>
<field>
<name>sess_id</name>
<type>text</type>
<length>40</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>created</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>changed</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>ip</name>
<type>text</type>
<length>15</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>vars</name>
<type>clob</type>
<notnull>true</notnull>
</field>
<index>
<name>primary</name>
<primary>true</primary>
<field>
<name>sess_id</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>
<table>
<name>users</name>
<declaration>
<field>
<name>user_id</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
<autoincrement>1</autoincrement>
</field>
<field>
<name>username</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>mail_host</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>alias</name>
<type>text</type>
<length>128</length>
<notnull>true</notnull>
<default></default>
</field>
<field>
<name>created</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>last_login</name>
<type>timestamp</type>
<notnull>true</notnull>
<default>0000-00-00 00:00:00</default>
</field>
<field>
<name>language</name>
<type>text</type>
<length>5</length>
<notnull>true</notnull>
<default>en</default>
</field>
<field>
<name>preferences</name>
<type>clob</type>
<notnull>true</notnull>
</field>
<index>
<name>primary</name>
<primary>true</primary>
<field>
<name>user_id</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>
</database>
