Source for file db.class.php

Documentation is available at db.class.php

<?php
/**
* database wrapper class
*
* LICENSE:
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*
* @package ea-Geier
* @subpackage code/base
* @author m2mtech <tech@m2m.at>
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License Version 2
* @version $Id: db.class.php 141 2007-08-31 20:52:47Z m2mtech $
* @link http://www.ea-geier.at/
*/
/**
* path to ADOdb library
* @ignore
* attention: might have been defined already in
* config/config.php or code/config.php
*/
if (!defined('eaADODB_DIR')) define('eaADODB_DIR', '3party/adodb/adodb494/');
/**
* load adobdb library
*/
require_once(eaADODB_DIR . 'adodb.inc.php');
require_once(eaADODB_DIR . 'session/adodb-cryptsession2.php');
require_once(eaADODB_DIR . 'session/crypt.inc.php');
/**
* wrapper for ADOdb database library
*
* for basic initialization etc.
*
* @package ea-Geier
*/
class eaDB {
/**
* database resource
*
* @var object
*/
var $_db = false;
/**
* table prefix
*
* @var string
*/
var $_prefix = '';
/**
* client tables
*
* @var string
*/
var $_clientTables = array();
/**
* configuration data
*
* @var array
*/
var $conf = false;
/**
* user data
*
* @var array
*/
var $user = false;
/**
* constructor of database wrapper
*
* defines the environmental db settings
* and starts session if not already done
*
* @param array configuration data
* @param boolean enable adobdb debug (false)
*/
function eaDB(&$conf, $debug = false) {
// prepare configuration
$this->conf =& $conf;
if (isset($conf['dsn'])) $dsn = $conf['dsn'];
else return false;
if (isset($conf['tablePrefix'])) $prefix = $conf['tablePrefix'];
else $prefix = '';
if (isset($conf['sessionName'])) $sessionName = $conf['sessionName'];
else $sessionName = '';
// connect to db
if (!$this->_db = ADONewConnection($dsn)) return false;
if (defined('eaDEBUG') && strpos(eaDEBUG, 'dbDebug'))
$this->_db->debug = true;
else $this->_db->debug = $debug;
$this->_db->SetFetchMode(ADODB_FETCH_ASSOC);
// register table prefix
$this->_prefix = $prefix;
// session handling - only with first database connection
if ((!isset($GLOBALS['ADODB_SESS_CONN']) || !$GLOBALS['ADODB_SESS_CONN']) && !headers_sent()) {
$GLOBALS['ADODB_SESS_CONN'] =& $this->_db;
if ($this->_db->debug) ob_start();
$vars = array ('HTTP_ACCEPT_CHARSET', 'HTTP_ACCEPT_ENCODING', 'HTTP_ACCEPT_LANGUAGE', 'HTTP_USER_AGENT', 'REMOTE_ADDR');
$sessionKey = 'ea'; foreach($vars as $var) $sessionKey .= $var;
ADOdb_Session::encryptionKey(md5($sessionKey));
ADODB_Session::table($prefix . 'sessions2');
ADODB_Session::optimize(true);
if ($sessionName) session_name($sessionName);
session_start();
if ($this->_db->debug) ob_end_flush();
}
// load configuration
if (!$dummy = $this->select('name, value, what', $this->table('config')))
return false;
foreach ($dummy as $item) switch ($item['what']) {
case 'select':
$split = explode(';', $item['value']);
if (!isset($split[1])) $split = explode(',', $split[0]);
$conf[$item['name']] = trim($split[0]);
break;
case 'encrypt':
if (!isset($crypt)) $crypt = new MD5Crypt;
$conf[$item['name']] = $this->decrypt($item['value']);
break;
default:
$conf[$item['name']] = $item['value'];
}
// load client tables
if (isset($conf['txtClientTables']) && $conf['txtClientTables'])
$this->_clientTables = $conf['txtClientTables'];
return true;
}
/**
* switch to client database - if necessary
*
* @param integer clientID
* @return boolean status
*/
function switchToClientDB($client) {
// write & close session into current database
if (isset($GLOBALS['ADODB_SESS_CONN'])) session_write_close();
// fetch dsn
if (!isset($this->clients[$client]['db'])) {
if (!$dummy = $this->selectOne('db', $this->table('clients'), array('id' => $client))) return false;
$dsn = $this->decrypt($dummy['db']);
} else $dsn = $this->clients[$client]['db'];
$this->_prefix .= $client . '_';
if (strpos($dsn, 'dsnTest') !== false) $dsn = false; // simpletest artefact
if (!$dsn) return false;
// switch db
$oldDebug = $this->_db->debug;
if (!$this->_db = ADONewConnection($dsn)) return false;
$this->_db->debug = $oldDebug;
$this->_db->SetFetchMode(ADODB_FETCH_ASSOC);
return true;
}
/**
* checks for database error
*
* @return boolean status
*/
function error() {
if (!$this->_db) return true;
//print_r($this->_db);
return false;
}
/**
* login validation
*
* @param eaInput input values
* @return array user data | false
*/
function checkLogin(&$in) {
$conf =& $this->conf;
$user =& $this->user;
if (($login = $in->post('mailLogin')) && ($password = $in->post('passLogin'))) {
$encrypt = true;
} else {
if (!isset($_SESSION['lg'])) return false;
if (!isset($_SESSION['pw'])) return false;
$login = $_SESSION['lg'];
$password = $_SESSION['pw'];
$encrypt = false;
}
$what = 'u.*';
$table = $this->table('users') . ' u';
$where = '(mail = ' . $this->escape($login) . ')';
if ($encrypt)
$where .= ' and (pw = ' . $this->escape(md5($password)) . ')';
else $where .= ' and (pw = ' . $this->escape($password) . ')';
if (!$user = $this->selectOne($what, $table, $where)) return false;
if ($encrypt) {
adodb_session_regenerate_id();
$_SESSION['lg'] = $user['mail'];
$_SESSION['pw'] = $user['pw'];
}
// get user configuration
$table = $this->table('userconfig') . ' u, ' . $this->table('config') . ' c';
$where = "(u.userID = '" . $user['id'] . "') and (u.name = c.name) and (c.context = 'user')";
if ($dummy = $this->select('u.*', $table, $where)) {
foreach ($dummy as $var) $conf[$var['name']] = $var['value'];
}
return $user;
}
/**
* logout function
*
* deletes session data
* @return status
*/
function logout() {
if (!isset($_SESSION)) return false;
foreach ($_SESSION as $key => $val) $_SESSION[$key] = '';
return true;
}
/**
* checks if user exists
*
* @param string username
* @return boolean/string status/name
*/
function isUser($name, $get = 'name') {
$what = 'u.*';
$table = $this->table('users') . ' u';
$where = 'mail = ' . $this->escape($name);
if (!$dummy = $this->selectOne($what, $table, $where)) return false;
switch ($get) {
case 'id':
return $dummy['id'];
case 'name': default:
if ($dummy['name']) return $dummy['name'];
}
return true;
}
/**
* create user
*
* @param string/array username/userdata
* @param string password (optional if userdata)
* @return boolean status
*/
function createUser($user, $pw = '') {
$what = array();
if (is_array($user)) {
$vars = array('mail', 'name', 'pw');
foreach ($vars as $var) switch ($var) {
case 'pw':
if ($pw) $user['pw'] = $pw;
$what['pw'] = md5($user['pw']);
break;
default:
$what[$var] = $user[$var];
}
} else {
$what = array('name' => 'dummy', 'mail' => $user, 'pw' => md5($pw));
}
$what['lastModified'] = $this->now();
return $this->insert($what, $this->table('users'));
}
/**
* update user
*
* @param string old user
* @param array new userdata
* @param boolean change session
* @return boolean status
*/
function updateUser($oldUser, $userData, $session = false) {
if (isset($userData['mail']) && ($userData['mail'] != $oldUser) && $this->isUser($userData['mail']))
return false;
$what = array();
$vars = array('mail', 'name', 'pw');
foreach ($vars as $var) if (isset($userData[$var])) switch ($var) {
case 'pw':
$what['pw'] = md5($userData['pw']);
break;
default:
$what[$var] = $userData[$var];
}
$where = array('mail' => $oldUser);
if (!$this->update($what, $this->table('users'), $where)) return false;
if ($session) {
if (isset($userData['mail'])) $_SESSION['lg'] = $userData['mail'];
if (isset($userData['pw'])) $_SESSION['pw'] = md5($userData['pw']);
}
return true;
}
/**
* delete user
*
* @param string username
* @return boolean status
*/
function deleteUser($user) {
if (!$this->isUser($user)) return false;
$table = $this->table('users');
$where = array('mail' => $user);
if (!$user = $this->selectOne('id', $table, $where)) return false;
$cTable = $this->table('clients');
$cWhere = array('owner' => $user['id']);
$clients = $this->select('id', $cTable, $cWhere);
$this->_db->StartTrans();
if ($clients) foreach ($clients as $client)
$this->deleteClient($client['id']);
$this->delete($this->table('userconfig'), array('userId' => $user['id']));
$this->delete($table, $where);
$this->_db->CompleteTrans();
return true;
}
/**
* set password for user
*
* @param string username
* @param string password
* @return boolean status
*/
function setPW($user, $pw) {
return $this->updateUser($user, array('pw' => $pw));
}
/**
* get user configuration variables
*
* @return array configuration data
*/
function getUserConf() {
$where = "(context = 'user')";
if ($this->user['mail'] != eaSYSADMIN) $where .= " and (rights = 'user')";
return $this->select('*', $this->table('config'), $where);
}
/**
* update user configuration
*
* @param integer user ID
* @param array changed configruration
* @return boolean status
*/
function updateUserConf($userID, $changed) {
$result = true;
$table = $this->table('userconfig');
$where = array('userID', 'name');
foreach ($changed as $key => $val) {
$what = array('userID' => $userID, 'name' => $key, 'value' => $val, 'lastModified' => $this->now());
if (!$this->_db->Replace($table, $what, $where, true)) $result = false;
}
return $result;
}
/**
* create client
*
* @param array client data
* @param array optional user data
* @return boolean status
*/
function createClient(&$data, $user = false, $demo = false) {
$conf =& $this->conf;
if (!$user) $user =& $this->user;
$what['name'] = $data['txtNewClientName'];
$what['owner'] = $user['id'];
$what['db'] = $this->encrypt($conf['dsnNewClientDB']);
$what['lastModified'] = $this->now();
if (!$this->insert($what, $this->table('clients'))) return false;
$clientID = $this->lastID();
if ($this->_clientTables) {
$oldPrefix = $this->_prefix;
$oldDebug = $this->_db->debug;
$switched = $this->switchToClientDB($clientID);
require_once(eaADODB_DIR . 'adodb-xmlschema03.inc.php');
foreach ($this->_clientTables as $table) {
$schema = new adoSchema($this->_db);
if ($demo)
$schema->ParseSchemaString(str_replace($table, $this->_prefix . $table, $schema->ConvertSchemaFile('admin/sql/demo_' . $table . '.sql')));
else
$schema->ParseSchemaString(str_replace($table, $this->_prefix . $table, $schema->ConvertSchemaFile('admin/sql/' . $table . '.sql')));
$schema->ContinueOnError(true);
$schema->ExecuteSchema();
}
$this->_prefix = $oldPrefix;
if ($switched) { // switch back
$this->_db = ADONewConnection($conf['dsn']);
$this->_db->SetFetchMode(ADODB_FETCH_ASSOC);
}
$this->_db->debug = $oldDebug;
}
return $clientID;
}
/**
* update client
*
* @param integer clientID
* @param array new client data
* @return boolean status
*/
function updateClient($id, $data) {
$what = array();
$vars = array('name');
foreach ($vars as $var) if (isset($data[$var])) switch ($var) {
default:
$what[$var] = $data[$var];
}
$where = array('id' => $id);
return $this->update($what, $this->table('clients'), $where);
}
/**
* delete client
*
* @param integer client id
* @return boolean status
*/
function deleteClient($id) {
if (!$id) return false;
if ($this->_clientTables) {
$oldPrefix = $this->_prefix;
$oldDebug = $this->_db->debug;
$switched = $this->switchToClientDB($id);
foreach ($this->_clientTables as $table)
$this->drop($this->table($table));
$this->_prefix = $oldPrefix;
if ($switched) {
$this->_db = ADONewConnection($this->conf['dsn']);
$this->_db->SetFetchMode(ADODB_FETCH_ASSOC);
}
$this->_db->debug = $oldDebug;
}
$cTable = $this->table('clients');
$ccTable = $this->table('clientconfig');
$this->_db->StartTrans();
$this->delete($ccTable, array('clientID' => $id));
$this->delete($cTable, array('id' => $id));
$this->_db->CompleteTrans();
return true;
}
/**
* get assigned clients
*
* @param eaInput input values
* @return boolean status
*/
function getClients(&$in) {
$conf =& $this->conf;
$user =& $this->user;
$opt =& $in->options;
$where = "(owner = '" . $user['id'] . "') or (users like '-" . $user['id'] . "-')";
if (isset($user['mail']) && ($user['mail'] == eaSYSADMIN)) $where = '1';
if (!$result = $this->select('*', $this->table('clients'), $where, 'name asc')) return false;
foreach ($result as $r) {
$r['db'] = $this->decrypt($r['db']);
$this->clients[$r['id']] = $r;
$opt['clients'][$r['id']] = $r['name'];
}
return true;
}
/**
* checks if user is owner
*
* @param integer clientID
* @return boolean status
*/
function isOwner($id) {
$conf =& $this->conf;
$user =& $this->user;
if (isset($user['mail']) && ($user['mail'] == eaSYSADMIN)) return true;
if (isset($this->clients)) {
if (!isset($this->clients[$id])) return false;
if ($this->clients[$id]['owner'] != $this->user['id']) return false;
return true;
}
if (!$this->select('id', $this->table('clients'), array('owner' => $user['id'])))
return false;
return true;
}
/**
* get client configuration variables
*
* @param integer clientID
* @return array configuration data
*/
function getClientConf($id) {
$where = "(context = 'client')";
if ($this->user['mail'] != eaSYSADMIN) $where .= " and (rights = 'owner')";
return $this->select('*', $this->table('config'), $where);
}
/**
* load client configuration into configuration variables
*
* @param integer clientID
* @return integer clientID
*/
function loadClientConf($id) {
if (!$id) return false;
$conf =& $this->conf;
$table = $this->table('clientconfig');
$where = array('clientID' => $id);
if ($dummy = $this->select('*', $table, $where)) {
foreach ($dummy as $var) $conf[$var['name']] = $var['value'];
}
return $id;
}
/**
* update client configuration
*
* @param integer client ID
* @param array changed configruration
* @return boolean status
*/
function updateClientConf($id, $changed) {
$result = true;
$table = $this->table('clientconfig');
$where = array('clientID', 'name');
foreach ($changed as $key => $val) {
$what = array('clientID' => $id, 'name' => $key, 'value' => $val, 'lastModified' => $this->now());
if (!$this->_db->Replace($table, $what, $where, true)) $result = false;
}
return $result;
}
/**
* add user to client
*
* @param integer client ID
* @param string email address
* @return boolean status
*/
function addUserToClient($client, $user) {
// get userID
if (!$user = $this->selectOne('id', $this->table('users'), array('mail' => $user)))
return false;
// get client data
$table = $this->table('clients');
$where['id'] = $client;
if (!$client = $this->selectOne('users', $table, $where)) return false;
if (preg_match('/-' . $user['id'] . '-/', $client['users'])) return true;
if (strlen($client['users']) > 2) $client['users'] .= $user['id'] . '-';
else $client['users'] = '-' . $user['id'] . '-';
return $this->update($client, $table, $where);
}
/**
* remove user from client
*
* @param integer clientID
* @param integer userID
* @return boolean status
*/
function delUserClient($client, $user) {
// get client data
$table = $this->table('clients');
$where['id'] = $client;
if (!$client = $this->selectOne('users', $table, $where)) return false;
$client['users'] = preg_replace('/-' . $user . '-/', '-', $client['users']);
return $this->update($client, $table, $where);
}
/**
* get client users
*
* @param integer client ID
* @return array user data
*/
function getClientUsers($id) {
$what = 'u.id, u.mail';
$table = $this->table('clients') . ' c, ' . $this->table('users') . ' u';
$where = "(c.id = " . $this->escape($id) . ") and (c.users like " . $this->_db->Concat("'%-'", 'u.id', "'-%'") . ")";
if (!$users = $this->select($what, $table, $where, 'u.mail asc')) return false;
foreach ($users as $user) $result[$user['id']] = $user['mail'];
return $result;
}
/**
* get system configuration variables
*
* @param string username
* @return array configuration data
*/
function getSysConf() {
if ($this->user['mail'] != eaSYSADMIN) return false;
$where = "(context = 'system')";
return $this->select('*', $this->table('config'), $where);
}
/**
* update system configuration
*
* @param array changed configruration
* @return boolean status
*/
function updateSysConf($changed) {
if ($this->user['mail'] != eaSYSADMIN) return false;
if (!$changed) return false;
if (!is_array($changed)) return false;
foreach ($changed as $key => $val) $what[] = array($val, $key);
$sql = 'update ' . $this->table('config') . ' set value=? where name=?';
if (!$this->_db->Execute($sql, $what)) return false;
return true;
}
/**
* select one single database row
*
* @param string columns
* @param string table
* @param string/array conditions
* @param string sorting
* @return array result
*/
function selectOne($what, $table = '', $where = '', $order = '') {
return $this->select($what, $table, $where, $order, 1);
}
/**
* select database rows
*
* @param string columns
* @param string table
* @param string/array conditions
* @param string sorting
* @param integer number of rows
* @param integer offset
* @param string group by
* @return array result
*/
function select($what, $table = '', $where = '', $order = '', $rows = -1, $offset = -1, $group = '') {
if ($table) $table = ' from ' . $table;
if ($where) {
if (is_array($where)) {
$dummy = '';
$next = '';
$nameQuote = $this->_db->nameQuote;
foreach ($where as $key => $val) {
$dummy .= $next . '(' . $nameQuote . $key . $nameQuote . ' = ' . $this->escape($val) . ')';
$next = ' and ';
}
$where = ' where ' . $dummy;
} else $where = ' where ' . $where;
}
if ($order) $order = ' order by ' . $order;
if ($group) $group = ' group by ' . $group;
if (!$rs = $this->_db->SelectLimit('select ' . $what . $table . $where . $group . $order, $rows, $offset))
return false;
if (!$rs->EOF) {
if ($rows == 1) $arr = $rs->fields;
else $arr = $rs->getArray();
} else $arr = array();
$rs->Close();
return $arr;
}
/**
* escape value string
*
* @param string string to escape
* @return string escaped string
*/
function escape($string, $noQuote = false) {
$return = $this->_db->qstr($string);
if ($noQuote) $return = substr($return, 1, -1);
return $return;
}
/**
* escape key string
*
* @param string string to escape
* @return string escaped string
*/
function escapeKey($string) {
return $this->_db->nameQuote . $string . $this->_db->nameQuote;
}
/**
* encrypt string
*
* @param string string to encrypt
* @param string key [dsn]
* @return string encrypted string
*/
function encrypt($string, $key = false) {
if (!isset($this->_crypt)) $this->_crypt = new MD5Crypt;
if (!$key) {
if (isset($this->conf['dsn'])) $key = $this->conf['dsn'];
else $key = 'dsn';
}
return $this->_crypt->Encrypt($string, $key);
}
/**
* decrypt string
*
* @param string string to decrypt
* @param string key [dsn]
* @return string decrypted string
*/
function decrypt($string, $key = false) {
if (!isset($this->_crypt)) $this->_crypt = new MD5Crypt;
if (!$key) {
if (isset($this->conf['dsn'])) $key = $this->conf['dsn'];
else $key = 'dsn';
}
return $this->_crypt->Decrypt($string, $key);
}
/**
* return real table name
*
* @param string preliminary table name
* @return string table name
*/
function table($string) {
if (!$this->_prefix) return $string;
return $this->_prefix . $string;
}
/**
* insert array into table
*
* @param array $what key => value
* @param string $table table
* @return boolean result
*/
function insert($what, $table) {
$cols = '';
$vals = '';
$next = '';
$nameQuote = $this->_db->nameQuote;
foreach ($what as $key => $val) {
$cols .= $next . $nameQuote . $key . $nameQuote;
$vals .= $next . $this->escape($val);
$next = ', ';
}
$sql = 'insert into ' . $table . '(' . $cols . ') values (' . $vals . ')';
if (!$this->_db->Execute($sql)) return false;
return true;
}
/**
* update table
*
* @param string/array sql string / key => value
* @param string table
* @param string/array conditions
* @return boolean result
*/
function update($what, $table, $where = '') {
if (!$what) return false;
if (is_array($what)) {
$dummy = '';
$next = '';
$nameQuote = $this->_db->nameQuote;
foreach ($what as $key => $val) {
$dummy .= $next . $nameQuote . $key . $nameQuote . ' = ' . $this->escape($val);
$next = ', ';
}
$what = $dummy;
}
if ($where) {
if (is_array($where)) {
$dummy = ' where ';
$next = '';
$nameQuote = $this->_db->nameQuote;
foreach ($where as $key => $val) {
$dummy .= $next . '(' . $nameQuote . $key . $nameQuote . ' = ' . $this->escape($val) . ')';
$next = ' and ';
}
$where = $dummy;
} else $where = ' where ' . $where;
}
$sql = 'update ' . $table . ' set ' . $what . $where;
if (!$this->_db->Execute($sql)) return false;
return true;
}
/**
* delete datasets
*
* @param string table
* @param string/array conditions
* @return boolean result
*/
function delete($table, $where = '') {
if ($where) {
if (is_array($where)) {
$dummy = ' where ';
$next = '';
$nameQuote = $this->_db->nameQuote;
foreach ($where as $key => $val) {
$dummy .= $next . '(' . $nameQuote . $key . $nameQuote . ' = ' . $this->escape($val) . ')';
$next = ' and ';
}
$where = $dummy;
} else $where = ' where ' . $where;
}
$sql = 'delete from ' . $table . $where;
if (!$this->_db->Execute($sql)) return false;
return true;
}
/**
* get last inserted id
*
* @return integer last id
*/
function lastID() {
return $this->_db->Insert_ID();
}
/**
* drop table
*
* @param string table
* @return boolean result
*/
function drop($table) {
if (!$table) return false;
$sql = 'drop table ' . $table;
if (!$this->_db->Execute($sql)) return false;
return true;
}
/**
* generate sql time string from unix timestamp
*
* @param integer unix time stamp
* @return string sql time string
*/
function time($time) {
return str_replace("'", '', $this->_db->DBTimeStamp($time));
}
/**
* generate sql time string for current time
*
* @param integer unix time stamp
* @return string sql time string
*/
function now() {
// require_once(eaADODB_DIR . 'adodb-time.inc.php');
return $this->time(time());
}
}
/**
* output function forwarding to error_log
*
* @param string $msg error message
* @param boolean $newline send new line command
*/
function eaADODBerrorLog($msg, $newline = true) {
error_log(strip_tags($msg));
return true;
}
?>

Documentation generated on Sun, 09 Sep 2007 17:08:27 +0200 by phpDocumentor 1.3.1

Source for file db.class.php

ea-Geier

Packages: