<?php
/**
 * Datasource abstraction for access to mySQL
 *
 * CAUTION: This version contains a quick hack in getJoin() (foreign keys)
 *
 * @author gERD Schaufelberger <gerd@exit0.net>
 * @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 );
    }
}
?>