* @copyright 2004/2005 by gERD Schaufelberger * @package wombatSite * @subpackage datasource */ /** * wbDatasource * * Abstraction layer for (MySQL) Databse-Connections * * Actually, this just saves typing SQL-statements. Therefore it is not * a very good adstraction layer. Still it is very handy... * * * @version 1.0.9 * @package wombatSite * @subpackage datasource */ class wbDatasource { /** * data connection PEAR::db-object * @var object $_dc */ var $_dc = null; /** * session object * @var object $_sess */ var $_sess = null; /** * callback object * @var object $_callback */ var $_callback = null; /** * knwon tables * * @access private * @var array $_tables */ var $_tables = array(); /** * @access private * List of pager offsets * @var array */ var $_pager = array(); /** * @access private * @var int $_debug whether datasource runs in debug mode */ var $_debug = false; /** * constructor creates datasource * * @access public * @param array $tableSepcs table configuration */ function __construct( &$tableSpecs ) { // load dataconnection $this->_dc =& wbFactory::singleton( 'DB' ); $this->_sess =& wbFactory::singleton( 'patSession' ); $conf =& wbFactory::singleton( 'patConfiguration' ); // load config $conf->loadConfig( $tableSpecs ); $this->_tables = $conf->getConfigValue( 'tables' ); $conf->clearConfigValue(); } /** * constructor wrapper for PHP4 * * @access public * @param array $tableSepcs table configuration * @see __contruct() */ function wbDatasource( &$tableSpecs ) { $this->__construct( $tableSpecs ); } /** * set reference to callback object * * @access public * @param object $callback * @return boolean $result true on success */ function setCallback( &$callback ) { // set callback-object $this->_callback =& $callback; return true; } /** * switch debugging mode on or of * * @access private * @param int $switch 1|0 * @return boolean $result true on success */ function setDebug( $switch = 0 ) { if( !$switch ) { $this->_debug = false; return true; } $this->_debug = true; return true; } /** * check database connection * * @access private * @return boolean $result true on success, parError otherwise */ function check() { if( patErrorManager::isError( $this->_dc ) ) { return $this->_dc; } return true; } /** * set options for a table * * @access public * @param array $options multiple options for multiple tables * @return boolean $result true on success */ function setOptions( $options ) { foreach( $options as $table => $opts ) { foreach( $opts as $key => $value ) { $this->_tables[$table][$key] = $value; } } return true; } /** * getPrimaryKey * * @access private * @param string $table table name * @return string $key named primary key */ function getPrimaryKey( $table ) { return $this->_tables[$table]['primary']; } /** * count entries of a table, matching the clause * * @access private * @param string $table named table in db * @param int $foreign * @param array $clause key-value pairs * @return int $result number of entries on success */ function count( $table, $foreign = null, $clause = null ) { if( !is_array( $clause ) ) { $clause = array(); } if( is_array( $table ) ) { $tables = $table; $table = $table[0]; $primary = $this->_tables[$table]['primary']; $allTables = array(); foreach( $tables as $tab ) { array_push( $allTables, $this->_tables[$tab]['table'] ); } $query = 'SELECT COUNT( * ) FROM ' . implode( ', ', $allTables ); // group by primary key of first table $group = $this->_tables[$table]['table'] . '.' . $this->_tables[$table]['primary']; } else { $primary = $this->_tables[$table]['primary']; $query = 'SELECT COUNT( * ) FROM ' . $this->_tables[$table]['table']; // add foreign key if( $foreign !== null && isset( $this->_tables[$table]['foreign'] ) ) { $fKey = $this->_tables[ $this->_tables[$table]['foreign'] ]['primary']; array_push( $clause, array( 'field' => $fKey, 'value' => $foreign ) ); } $group = null; } // skip entries marked as deleted. if( isset( $this->_tables[$table]['delete'] ) ) { list( $key, $value ) = explode( '=', $this->_tables[$table]['delete'] ); array_push( $clause, array( 'relation' => 'not_in_set', 'field' => $key, 'value' => $value ) ); } if( !empty( $clause ) ) { $where = array(); $this->_clause2Where( $table, $clause, $where ); $query .= ' WHERE ' . implode( ' AND ', $where ); } if( $group ) { // $query .= ' GROUP BY ' . $group; } $this->_addDebugMessage( $query, 'Count SQL' ); $result = $this->_dc->getOne( $query ); if( PEAR::isError( $result ) ) { return patErrorManager::raiseError( 'wbDatasource:1', 'Counting entries failed', 'Query: ' . $query ); } return $result; } /** * recieve field names for duplicate entries * * @access public * @param string $table named table to get data from * @param int $id id of entry * @param array &$data associative array to be saved to database * @param array $clause key-value pairs for clause * @return array $result empty array if no dublicates were found, array containing fields if copies were found * @deprecated This is a typo! * @see getDuplicates() */ function getDublicates( $table, $id, &$data, $clause = array() ) { patErrorManager::raiseNotice( 'wbDatasource:x', 'Deprecated function call', 'function: getDublicates() ' ); return $this->getDuplicates( $table, $id, $data, $clause ); } /** * recieve field names for duplicate entries * * @access public * @param string $table named table to get data from * @param int $id id of entry * @param array &$data associative array to be saved to database * @param array $clause key-value pairs for clause * @return array $result empty array if no dublicates were found, array containing fields if copies were found */ function getDuplicates( $table, $id, &$data, $clause = array() ) { // check for copies if( !isset( $this->_tables[$table]['avoiddublicate'] ) ) { return array(); } $primary = $this->_tables[$table]['primary']; $dups = array(); $where = array( $this->_tables[$table]['table'] . '.%s LIKE %s' ); if( !is_array( $clause ) ) { $clause = array(); } // id selected if( $id != 'new' ) { array_push( $clause, array( 'relation' => 'not', 'field' => $primary, 'value' => $id ) ); } // skip entries marked as deleted. if( isset( $this->_tables[$table]['delete'] ) ) { list( $key, $value ) = explode( '=', $this->_tables[$table]['delete'] ); array_push( $clause, array( 'relation' => 'not_in_set', 'field' => $key, 'value' => $value ) ); } // add foreign key if( isset( $this->_tables[$table]['foreign'] ) ) { $fTable = $this->_tables[$table]['foreign']; $fPrimary = $this->_tables[$fTable]['primary']; if( isset( $data[$fPrimary] ) ) { $fKey = $data[$fPrimary]; array_push( $clause, array( 'field' => $fPrimary, 'value' => $fKey ) ); } } // query base $this->_clause2Where( $table, $clause, $where ); $query = 'SELECT '. $primary . ' FROM ' . $this->_tables[$table]['table'] . ' WHERE ' . implode( ' AND ', $where ); // check each duplicate field $dFields = explode( ',', $this->_tables[$table]['avoiddublicate'] ); foreach( $dFields as $df ) { if( !array_key_exists( $df, $data ) ) { continue; } $q = sprintf( $query, $df, $this->_dc->quote( $data[$df] ) ); $this->_addDebugMessage( $q, 'Dup check SQL' ); $result = $this->_dc->query( $q ); if( DB::isError( $result ) ) { return patErrorManager::raiseError( 'wbDatasource:8', 'Recieving dublicates failed!', 'Query failed: ' . $q ); } if( $result->numRows() > 0 ) { array_push( $dups, $df ); } } return $dups; } /** * save entry in table * * @access private * @param string $table named table in db * @param mixed $key primary id or 'new' for a new record * @param array &$data associative array to be saved to database * @param array $clause key-value pairs for clause * @param bool $dupCheck use duplicate check * @return int $result primary key on success ucuoError-Object on error */ function save( $table, $key, &$data, $clause = array(), $dupCheck = true ) { // no data, no save if( empty( $data ) ) { return true; } // force clause to be an array if( !is_array( $clause ) ) { $clause = array(); } // require a key or at least a clause! if( empty( $clause ) && $key == null ) { $msg = sprintf( 'Need a key or a clause! Table: "%s", Key: "%s".', $table, $key ); $this->_addDebugMessage( $msg, 'Save data failed' ); return patErrorManager::raiseError( 'wbDatasource:6', 'Save data failed!', 'No Key and no clause set' ); } // perform dublicate check if( $dupCheck ) { $dups = $this->getDuplicates( $table, $key, $data, $clause ); if( !empty( $dubs ) ) { $msg = sprintf( 'Table: "%s", Key: "%s", dublicated fields: "%s".', $table, $key, implode( '","', $dups ) ); $this->_addDebugMessage( $msg, 'Save data failed' ); return patErrorManager::raiseNotice( 'wbDatasource:7', 'Save data failed!', 'Dublicate entry found' ); } } // callback if( $this->_useCallback( $table, 'save' ) ) { $result = $this->_callback->callForSave( $table, $data, $key ); if( patErrorManager::isError( $result ) ) { return $result; } } // build query $primary = $this->_tables[$table]['primary']; $query = 'UPDATE '; $where = array(); if( $key == 'new' ) { if( !empty( $primary ) ) { $key = $this->_dc->nextId( $this->_tables[$table]['table'] ); if( DB::isError( $key ) ) { return patErrorManager::raiseError( 'wbDatasource:7', 'Save data failed!', 'Could not get nextId for table: ' . $table ); } $data[$primary] = $key; } $query = 'INSERT INTO '; $clause = array(); } else if( $key != null ) { array_push( $where, $this->_tables[$table]['table'] . '.' . $primary . '=' . $this->_dc->quote( $key ) ); } $this->_clause2Where( $table, $clause, $where ); $set = array(); foreach( $data as $field => $value ) { array_push( $set, $this->_tables[$table]['table'] . '.' . $field . '=' . $this->_dc->quote( $value ) ); } $query .= $this->_tables[$table]['table'] . ' SET ' . implode( ', ', $set ); if( !empty( $where ) ) { $query .= ' WHERE '. implode( ' AND ', $where ); } $this->_addDebugMessage( $query, 'Save SQL' ); $result = $this->_dc->query( $query ); if( DB::isError( $result ) ) { return patErrorManager::raiseError( 'wbDatasource:7', 'Save data failed!', 'Query failed: ' . $query ); } return $key; } /** * delete entry/ies from db * * @access public * @param string $table named table in db * @param mixed $key null if no key is used, primary key otherwise * @param array $clause additional clause * @return int $result number of rows deletete */ function delete( $table, $key, $clause = array() ) { if( $key === null && empty( $clause ) ) { return patErrorManager::raiseWarning( 'wbDatasource:10', 'Delete failed', 'Need either primary or clause or both - nothing found!' ); } // use callback if( $this->_useCallback( $table, 'delete' ) ) { $result = $this->_callback->callForDelete( $table, $clause, $key ); if( patErrorManager::isError( $result ) ) { return $result; } } $primary = $this->_tables[$table]['primary']; $where = array(); if( $key != null ) { array_push( $where, $primary . '=' . $this->_dc->quote( $key ) ); } if( !empty( $clause ) ) { $this->_clause2Where( $table, $clause, $where ); } // delete linked data $foreign = array(); foreach( $this->_tables as $tab => $tabConf ) { if( isset( $tabConf['foreign'] ) && $tabConf['foreign'] === $table ) { if( !$this->_checkFlag( $tab, 'noforeigndelete' ) ) { array_push( $foreign, $tab ); } } } if( !empty( $foreign ) ) { $query = 'SELECT ' . $primary . ' FROM ' . $this->_tables[$table]['table'] . ' WHERE ' . implode( ' AND ', $where ); $keys = $this->_dc->getCol( $query ); if( DB::isError( $keys ) ) { return patErrorManager::raiseWarning( 'wbDatasource:12', 'Delete failed, could not select keys', 'Failed sending query: ' . $query ); } // get clause for delete entries in foreign table $clause = array( array( 'field' => $primary ), ); foreach( $foreign as $f ) { foreach( $keys as $k ) { $clause[0]['value'] = $k; $this->delete( $f, null, $clause ); } } } // finally delete all entries in primary table if( isset( $this->_tables[$table]['delete'] ) ) { list( $key, $value ) = explode( '=', $this->_tables[$table]['delete'] ); $query = 'UPDATE ' . $this->_tables[$table]['table'] . ' SET ' . $key . '=CONCAT( '. $key .' ,'. $this->_dc->quote( ',' . $value ) .' )' . ' WHERE ' . implode( ' AND ', $where ); } else { $query = 'DELETE FROM ' . $this->_tables[$table]['table'] . ' WHERE ' . implode( ' AND ', $where ); } $result = $this->_dc->query( $query ); $this->_addDebugMessage( $query, 'Delete data SQL' ); if( DB::isError( $result ) ) { return patErrorManager::raiseWarning( 'wbDatasource:11', 'Delete failed', 'Failed sending query: ' . $query ); } return 1; } /** * recieve list of ids * * @access public * @param string $table named table in db * @param int $foreign if of foreign key * @param array $clause additional clause * @return array $ids list of primary keys */ function getIds( $table, $foreign = null, $clause = array() ) { $primary = $this->_tables[$table]['primary']; if( $foreign !== null && isset( $this->_tables[$table]['foreign'] ) ) { $fKey = $this->_tables[ $this->_tables[$table]['foreign'] ]['primary']; array_push( $clause, array( 'field' => $fKey, 'value' => $foreign ) ); } // skip entries marked as deleted. if( isset( $this->_tables[$table]['delete'] ) ) { list( $key, $value ) = explode( '=', $this->_tables[$table]['delete'] ); array_push( $clause, array( 'relation' => 'not_in_set', 'field' => $key, 'value' => $value ) ); } $where = array( 1 ); $this->_clause2Where( $table, $clause, $where ); $query = 'SELECT '. $primary .' FROM '. $this->_tables[$table]['table'] .' WHERE ' . implode( ' AND ', $where ); // add order by if( isset( $this->_tables[$table]['order'] ) ) { $query .= ' ORDER BY ' . $this->_tables[$table]['table'] . '.'. $this->_tables[$table]['order']; if( isset( $this->_tables[$table]['orderdir'] ) ) { $query .= ' ' . $this->_tables[$table]['orderdir']; } } $this->_addDebugMessage( $query, 'Get ids SQL' ); $result = $this->_dc->getCol( $query ); if( DB::isError( $result ) ) { return patErrorManager::raiseError( 'wbDatasource:13', 'Get ids failed', 'Query: ' . $query ); } return $result; } /** * recieve a single entry from database * * @access public * @param string $table named table in db * @param int $id id of entry or "first" or "last", or "random" * @param int $foreign if of foreign key * @param array $clause additional clause * @return array $entry assiciative array containing entry from db or number of rows if not one */ function getEntry( $table, $id, $foreign = null, $clause = array() ) { $primary = $this->_tables[$table]['primary']; $order = array( $primary, 'ASC' ); if( isset( $this->_tables[$table]['order'] ) ) { $order[0] = $this->_tables[$table]['order']; if( isset( $this->_tables[$table]['orderdir'] ) ) { $order[1] = $this->_tables[$table]['orderdir']; } } $limit = ''; $where = array( 1 ); if( $id !== null ) { $this->_addDebugMessage( 'Select from "'. $table .'" by id "'. $id .'"', 'Get Entry' ); switch( $id ) { case 'random': $count = $this->count( $table, $foreign, $clause ); $offset = rand( 0, ( $count - 1 ) ); $limit = $offset . ',1'; break; case 'first': $limit = '0,1'; break; case 'last': $limit = '0,1'; if( $order[1] == 'ASC' ) { $order[1] = 'DESC'; } else { $order[1] = 'ASC'; } break; default: // normal id array_push( $where, $primary . '=' . $this->_dc->quote( $id ) ); break; } } if( $foreign !== null && isset( $this->_tables[$table]['foreign'] ) ) { $fKey = $this->_tables[ $this->_tables[$table]['foreign'] ]['primary']; array_push( $clause, array( 'field' => $fKey, 'value' => $foreign ) ); } // skip entries marked as deleted. if( isset( $this->_tables[$table]['delete'] ) ) { list( $key, $value ) = explode( '=', $this->_tables[$table]['delete'] ); array_push( $clause, array( 'relation' => 'not_in_set', 'field' => $key, 'value' => $value ) ); } $this->_clause2Where( $table, $clause, $where ); $query = 'SELECT * FROM '. $this->_tables[$table]['table'] .' WHERE ' . implode( ' AND ', $where ); if( !empty( $limit ) ) { $query .= ' ORDER BY ' . $this->_tables[$table]['table'] . '.' . $order[0] . ' ' . $order[1]; $query .= ' LIMIT ' . $limit; } $this->_addDebugMessage( $query, 'Get entry SQL' ); $result = $this->_dc->query( $query ); // check result if( DB::isError( $result ) ) { return patErrorManager::raiseError( 'wbDatasource:5', 'Get entry failed', 'Query: ' . $query . ' Message: ' . $result->getMessage() ); } // expect a single row if( ( $rows = $result->numRows() ) != 1 ) { return $rows; } $entry = $result->fetchRow( DB_FETCHMODE_ASSOC ); if( $this->_useCallback( $table, 'getentry' ) ) { $result = $this->_callback->callForGetentry( $table, $entry, $entry[$primary] ); if( patErrorManager::isError( $result ) ) { return $result; } } return $entry; } /** * recieve multiple rows from a table * * @access public * @param string $table named table in db * @param int $foreign if foreign key * @param array $clause additional clause * @return array $entries associative array containing entries from db */ function getEntries( $table, $foreign = null, $clause = array(), $offset = null ) { $primary = null; if( isset( $this->_tables[$table]['primary'] ) ) { $primary = $this->_tables[$table]['primary']; } $where = array(); $group = null; $query = 'SELECT * FROM '; $lTable = $this->_tables[$table]['table']; if( $foreign !== null && isset( $this->_tables[$table]['foreign'] ) ) { $fKey = $this->_tables[ $this->_tables[$table]['foreign'] ]['primary']; array_push( $where, $lTable . '.' . $fKey . '=' . $this->_dc->quote( $foreign ) ); } if( isset( $this->_tables[$table]['foreign'] ) ) { $rTable = $this->_tables[ $this->_tables[$table]['foreign'] ]['table']; $query .= $rTable . ', '; $fKey = $this->_tables[ $this->_tables[$table]['foreign'] ]['primary']; array_push( $where, $lTable . '.' . $fKey . '=' . $rTable . '.' . $fKey ); if( !empty( $primary ) ) { $group = $lTable . '.' . $primary; } } $query .= $this->_tables[$table]['table']; // skip entries marked as deleted. if( isset( $this->_tables[$table]['delete'] ) ) { list( $key, $value ) = explode( '=', $this->_tables[$table]['delete'] ); array_push( $clause, array( 'relation' => 'not_in_set', 'field' => $key, 'value' => $value ) ); } $this->_clause2Where( $table, $clause, $where ); if( !empty( $where ) ) { $query .= ' WHERE ' . implode( ' AND ', $where ); } if( $group ) { $query .= ' GROUP BY ' . $group; } // add order by if( isset( $this->_tables[$table]['order'] ) ) { $query .= ' ORDER BY ' . $this->_tables[$table]['table'] . '.'. $this->_tables[$table]['order']; if( isset( $this->_tables[$table]['orderdir'] ) ) { $query .= ' ' . $this->_tables[$table]['orderdir']; } } /** * As this limit is too big for even big integers, it must be a string! * This hack is required to satify MySQL 4.1 :-/ */ $limit = '18446744073709551614'; // add limit and offset if( isset( $this->_tables[$table]['limit'] ) ) { $limit = $this->_tables[$table]['limit']; } if( $limit == '18446744073709551614' || $offset !== null ) { if( $offset === null || $offset < 0 ) { $offset = 0; } $query .= ' LIMIT ' . $offset . ',' . $limit; } // get entries from db $this->_addDebugMessage( $query, 'Get entries SQL' ); $entries = $this->_dc->getAll( $query, array(), DB_FETCHMODE_ASSOC ); if( DB::isError( $entries ) ) { return patErrorManager::raiseError( 'wbDatasource:4', 'Get entries failed!', 'Query: ' . $query ); } $class = get_class( $this->_callback ); // callback? if( $this->_useCallback( $table, 'getentries' ) ) { for( $i = 0; $i < count( $entries ); ++$i ) { $result = $this->_callback->callForGetentries( $table, $entries[$i], $entries[$i][$primary] ); if( patErrorManager::isError( $result ) ) { return $result; } } } return $entries; } /** * submitt a complex query and get join * * @access public * @param array $tables list of tables, the first table must be the most significant table * @param array $selects complex list for selects; * @param array $clause complex clause * @param array $order 'order' = fieldname, 'orderdir' = asc|desc, 'limit' = number * @return boolean $result true on success */ function getJoin( $tables, $selects = null, $clause = array(), $options = null ) { if( !is_array( $tables ) ) { $tables = array( $tables ); } if( empty( $selects ) ) { $select = $this->_tables[$tables[0]]['table'] . '.*'; } else { $select = array(); foreach( $selects as $tab => $value ) { // select all or none of a table if( !is_array( $value ) ) { switch( $value ) { case 'all': $sel = $this->_tables[$tab]['table'] . '.*'; array_push( $select, $sel ); break; case 'none'; break; } continue; } // more specific select for( $i = 0; $i < count( $value ); ++$i ) { if( isset( $value[$i]['direct'] ) ) { $sel = $value[$i]['direct']; } else { if( isset( $value[$i]['function'] ) ) { $sel = $value[$i]['function'] . '( '. $this->_tables[$tab]['table'] . '.' . $value[$i]['field'] .' )'; } else { $sel = $this->_tables[$tab]['table'] . '.' . $value[$i]['field']; } } if( isset( $value[$i]['as'] ) ) { $sel .= ' AS ' . $value[$i]['as']; } array_push( $select, $sel ); } } $select = implode( ', ', $select ); } $allTables = array(); foreach( $tables as $tab ) { array_push( $allTables, $this->_tables[$tab]['table'] ); } $query = 'SELECT ' . $select . ' FROM ' . implode( ', ', $allTables ); $firstTable = array_shift( $allTables ); // group by primary key of first table $group = $firstTable . '.' . $this->_tables[$tables[0]]['primary']; // build where clause $where = array(); // add foreign keys to clause foreach( $tables as $table ) { if( isset( $this->_tables[$table]['foreign'] ) && in_array( $this->_tables[$table]['foreign'], $tables ) ) { // check direction of foreign keys if( $this->_tables[$table]['foreign'] == $tables[0] ) { $foreign = $this->_tables[$tables[0]]['primary']; } else { $foreign = $this->_tables[ $this->_tables[$table]['foreign'] ]['primary']; } array_push( $where, $firstTable . '.' . $foreign . '=' // why is this? //. $this->_tables[$this->_tables[$table]['foreign']]['table'] . '.' . $foreign . $this->_tables[$table]['table'] . '.' . $foreign ); } } $this->_clause2Where( $tables[0], $clause, $where ); $query .= ' WHERE ' . implode( ' AND ', $where ) . ' GROUP BY ' . $group; // common options if( $options === null ) { // load options from first table $options = $this->_tables[$tables[0]]; } else { foreach( $this->_tables[$tables[0]] as $opt => $value ) { if( !isset( $options[$opt] ) ) { $options[$opt] = $value; } } } // add order by if( isset( $options['order'] ) ) { $query .= ' ORDER BY ' . $this->_tables[$tables[0]]['table'] . '.' . $options['order']; if( isset( $options['orderdir'] ) ) { $query .= ' ' . $options['orderdir']; } } // add limit if( isset( $options['limit'] ) && $options['limit'] != -1 ) { $offset = ''; if( isset( $options['offset'] ) ) { $offset = $options['offset'] . ', '; } $query .= ' LIMIT ' . $offset . $options['limit']; } // get entries from db $this->_addDebugMessage( $query, 'Get join SQL' ); $entries = $this->_dc->getAll( $query, array(), DB_FETCHMODE_ASSOC ); if( DB::isError( $entries ) ) { return patErrorManager::raiseWarning( 'wbDatasource:5', 'Join failed!', 'Query: ' . $query ); } // callback? if( $this->_useCallback( $tables[0], 'getjoin' ) ) { for( $i = 0; $i < count( $entries ); ++$i ) { $result = $this->_callback->callForGetjoin( $tables[0], $entries[$i] ); if( patErrorManager::isError( $result ) ) { return $result; } } } return $entries; } /** * helper function tranlates clause-array to where array * * @access private * @return boolean $result true on success */ function _clause2Where( $table, $clause, &$where ) { if( !is_array( $clause ) ) { return true; } // manage clause for( $i = 0; $i < count( $clause ); ++$i ) { // strange wrong argument! if( !isset( $clause[$i] ) ) { return patErrorManager::raiseError( 'wbDatasource:2', 'Clause to where converter failed', 'Clause has wrong format! - numberd array expected!' ); } if( !isset( $clause[$i]['table'] ) ) { $clause[$i]['table'] = $table; } $type = 'simple'; if( isset( $clause[$i]['type'] ) ) { $type = $clause[$i]['type']; } switch( strtolower( $type ) ) { case 'simple'; $whereClause = $this->_getWhereClause( $clause[$i] ); break; case 'complex': $myWhere = array(); $this->_clause2Where( $table, $clause[$i]['clause'], $myWhere ); $whereClause = '( ' . implode( ' ' . $clause[$i]['bond'] . ' ', $myWhere ) . ' )'; break; } array_push( $where, $whereClause ); } return true; } /** * helper function tranlates clause-array to where array * * @access private * @param array $clause clause to be translated * @return boolean $result true on success */ function _getWhereClause( $clause ) { if( !isset( $clause['valuetype'] ) ) { $clause['valuetype'] = '_default_'; } switch( strtolower( $clause['valuetype'] ) ) { case 'function': $value = $clause['value']; break; case 'foreign': $foreign = $this->_tables[$clause['foreign']]['table']; $value = $foreign . '.' . $clause['value']; break; default: if( is_array( $clause['value'] ) ) { $value = array(); foreach( $clause['value'] as $v ) { $value[] = $this->_dc->quote( $v ); } $value = implode( ', ', $value ); break; } $value = $this->_dc->quote( $clause['value'] ); break; } if( !isset( $clause['relation'] ) ) { $clause['relation'] = '_default_'; } // select relation switch( strtolower( $clause['relation'] ) ) { case 'in': $rel = "%s IN( %s )"; break; case 'like': $rel = "%s LIKE '%%%s%%'"; $value = substr( $value, 1, -1 ); break; case 'begins': case 'begin': $rel = "%s LIKE '%s%%'"; $value = substr( $value, 1, -1 ); break; case 'ends': case 'end': $rel = "%s LIKE '%%%s'"; $value = substr( $value, 1, -1 ); break; case 'not_in_set': $rel = 'NOT FIND_IN_SET( %2$s, %1$s )'; break; case 'in_set': $rel = 'FIND_IN_SET( %2$s, %1$s )'; break; case '<': $rel = '%s<%s'; break; case '<=': $rel = '%s<=%s'; break; case '>': $rel = '%s>%s'; break; case '>=': $rel = '%s>=%s'; break; case '<>': case 'not': $rel = '%s<>%s'; break; default: $rel = '%s=%s'; break; } return sprintf( $rel, $this->_tables[$clause['table']]['table'] . '.' . $clause['field'], $value ); } /** * interface to recieve field-list * * @access public * @param string $table name of table * @return array $fields list of fields * @see _recieveFieldList() */ function getFieldList( $table ) { $this->_recieveFieldList( $table ); return $this->_tables[$table]['fields']; } /** * calculate offset for pager * * @access public * @param string $table named table * @param string $goto named goto - like "next, prev, current" or a numeric value * @param int $foreign * @param array $clause * @return int $offset the current offset */ function getPager( $table, $goto, $foreign = null, $clause = null ) { // make sure that the offset will be only calculated once per request $cachId = $table . $foreign . md5( serialize( $clause ) ); if( isset( $this->_pager[$cachId] ) ) { return $this->_pager[$cachId]; } // need some information about table and "page" $total = $this->count( $table, $foreign, $clause ); if( is_array( $table ) ) { $table = $table[0]; } $limit = -1; if( isset( $this->_tables[$table]['limit'] ) ) { $limit = $this->_tables[$table]['limit']; } $pages = 1; if( $limit > 0 ) { $pages = ceil( $total / $limit ); } $pager = array(); $pager['next'] = 0; $pager['prev'] = 0; $pager['offset'] = 0; // page offset // $pager['queryoffset'] = 0; // read to use in query offset $pager['current'] = 1; $pager['limit'] = $limit; $pager['pages'] = $pages; $pager['total'] = $total; // no limit, no pager if( !isset( $this->_tables[$table]['limit'] ) || $this->_tables[$table]['limit'] == 0 ) { return $pager; } // keep offset in session! $offset = $this->_sess->get( 'wbDatasource_' . $table . '_offset' ); if( $offset === null ) { $offset = 0; } // select extimated next offset switch( $goto ) { case 'last': $offset = $pages; break; // keep offset case 'current': break; case 'first': $offset = 0; break; case 'prev': --$offset; break; case 'next': ++$offset; break; default: if( is_numeric( $goto ) ) { $offset = $goto - 1; } break; } // build pager if( $offset < 0 ) { $offset = 0; } if( ( $pages - 1 ) < $offset ) { $offset = $pages - 1; } if( ( $pages - 1 ) > $offset ) { $pager['next'] = 1; } if( $offset > 0 ) { $pager['prev'] = 1; } $this->_sess->set( 'wbDatasource_' . $table . '_offset', $offset ); $pager['offset'] = $offset; $pager['current'] = $offset + 1; $this->_pager[$cachId] = $pager; return $pager; } /** * save list of table fiels into member-variable * * @access private * @param string $table name of table * @return boolean $result true on success * @todo check whether 'DESC table' works in all cases */ function _recieveFieldList( $table ) { // this has to be done only once per table if( isset( $this->_tables[$table]['fields'] ) && !empty( $this->_tables[$table]['fields'] ) ) { return true; } $query = 'DESC ' . $this->_tables[$table]['table']; $entry = $this->_dc->getCol( $query ); if( DB::isError( $entry ) ) { return patErrorManager::raiseError( 'wbDatasource:3', 'Could not get list of fields', 'DB-Query failed: '. $query .'!' ); } $this->_tables[$table]['fields'] = $entry; return true; } /** * check whether a specific flag is set * * @access private * @param string $table name of table * @param string $name flags name * @return boolean $result true if flag is set */ function _checkFlag( $table, $name ) { if( !isset( $this->_tables[$table]['flags'] ) ) { return false; } $flags = explode( ',', strtolower( $this->_tables[$table]['flags'] ) ); $name = strtolower( $name ); if( in_array( $name, $flags) ) { return true; } return false; } /** * check whether callback should be used * * @access private * @param string $table name of table * @param string $name callback name * @return boolean $result true if callback exists */ function _useCallback( $table, $name ) { if( !$this->_callback ) { return false; } if( !isset( $this->_tables[$table]['callback'] ) ) { return false; } $calldefs = explode( ',', $this->_tables[$table]['callback'] ); if( in_array( $name, $calldefs ) ) { return true; } return false; } /** * add debug message to debugger * * * @access private * @param string $text * @param string $itle * @return boolean $result true on success */ function _addDebugMessage( $text, $title = null ) { if( !$this->_debug ) { return true; } return wbDebugger::addMsg( 'wbDatasource', $text, $title ); } } ?>