<?php
/**
 * Store hirarchical structure in DB
 *
 * $Id$
 *
 * @author gERD Schaufelberger <gerd@php-tools.net>
 * @license PHP License
 * @package WB
 * @subpackage db
 */

WBClass::load( 'WBLog'
			, 'WBDatasource_Callback'
            , 'WBDatasource_SQLCommon'
            , 'WBDatasource_SQL' );

/**
 * Store hirarchical structure in DB
 *
 * Limits:
 * The default settings use an alphabet of 41 characters and three digits for
 * each level. That results in a maximum depth of 85 levels if the tree column
 * is 255 characters wide.
 *     255 characters / 3 characters / level = 85 level
 *
 * Also each tree node may hava a maximum number of 68 921 children.
 *     41 ^ 3 =     68 921
 *
 * The switch "casesensitive" allows to extend the alphabet to 67 characters. This
 * allows up to 300 763 children per node. To use this feature, the column in the
 * database that stores the tree's path must be case sensitve, like "utf8-bin".
 *     67 ^ 3 =    300 763
 *
 * Besides the default settings, it is possible to change the number of digits used
 * for every level - parameter: alphalength. Default is 3 characters,
 *
 * Limits for "alphalength" set to 4:
 *   255 characters / 4 characters / level = 63.75 = 63 levels
 *   41 ^ 4 =  2 825 761 children per node
 *   67 ^ 4 = 20 151 121 children per node
 *
 * Limits for "alphalength" set to 2:
 *   255 characters / 2 characters / level = 127,5 = 127 levels
 *   41 ^ 2 =      1 681 children per node
 *   67 ^ 2 =      4 489 children per node
 *
 * Still it is possible to use a column that can store more than 255 characters.
 * However, this will increase the index size...
 *
 * @version 0.5.0
 * @package WB
 * @subpackage db
 */
class WBDatasource_Tree extends WBDatasource_SQLCommon
{
    /**
     * Prefix for triggered events
     * @var string
     */
    protected $eventPrefix  =   'tree';

    /**
     * default length of keys per level
     * @var int
     */
    protected $alphaLength  = 3;

    /**
     * encoding alphabet for keys
     * @var array
     */
    protected $alphaXX = array();

    /**
     * database connection
     * @var WBDatasource_SQL
     */
    protected $db;

    /**
     * table that stores tree
     * @var string
     */
    protected $table = 'tree';

    /**
     * table name in database
     * @var string
     */
    protected $tableName = 'tree';

    /**
     * tree column
     * @var string
     */
    protected $column = 'tree';

    /**
     * additional clause
     * @var array
     */
    protected $clause = array();

    /**
     * default values
     * @var array
     */
    protected $skel = array();

    /**
     * root note
     * @var array
     */
    private $root = array();

    /**
     * column of primary kes
     * @var string
     */
    protected $primary = 'id';

    /**
     * cache nodes
     * @var array
     */
    protected static $microCache =   array();

    /**
     * local cache reference to actual cache
     * @var array
     */
    protected $cache;

    /**
     * constructor
     *
     * The parameter will be loaded from config.xml, section "db", you may
     * overwrite the list of tables. This will cause to load the table list
     * from a different file.
     *
     * Parameter:
     * 	- treetable
     *  - treecolumn
     *  - alphalength
     *  - chroot root node's id or 0
     *
     * This class utilizes @link WBDatasource_SQL this implies that you may
     * use all constructor parameter of this class, as well.
     *
     * @param array $parameter
     */
    public function __construct( $parameter = array() )
    {
        $this->log = 	WBLog::start( __CLASS__ );
        $this->db   =   WBClass::create( 'WBDatasource_SQL', $parameter );

        // configure object
        if( isset( $parameter['treetable'] ) ) {
            $this->table    =    $parameter['treetable'];
        }
        $this->loadTables( $parameter );

        // find real table name
        $this->tableName    = $this->tables[$this->table]['table'];

        // length of key per branch
        if( isset( $this->tables[$this->table]['alphalength'] ) ) {
            $this->alphaLength    =    intval( $this->tables[$this->table]['alphalength'] );

        }
        if( isset( $parameter['alphalength'] ) ) {
            $this->alphaLength    =    intval( $parameter['alphalength'] );
        }

        if( isset( $this->tables[$this->table]['treecolumn'] ) ) {
            $this->column    =    $this->tables[$this->table]['treecolumn'];

        }
        if( isset( $parameter['treecolumn'] ) ) {
            $this->column   =    $parameter['treecolumn'];
        }

        // primary key, if any
        if( !isset( $this->tables[$this->table]['primary'] ) ) {
            WBClass::load( 'WBException_Config' );
            throw new WBException_Config( 'There is no primary key configured for tree table', 1, __CLASS__ );
        }
        $this->primary    =    $this->tables[$this->table]['primary'];

        // base 67 alphabet
        $this->alphaXX = array_merge( $this->alphaXX, array( '+', '-', '.', '/' ) );
        $this->alphaXX = array_merge( $this->alphaXX, range( '0', '9' ) );
        $this->alphaXX = array_merge( $this->alphaXX, array( '@' ) );
        $this->alphaXX = array_merge( $this->alphaXX, range( 'A', 'Z' ) );

        // support of case sensitive tree column
        if( isset( $this->tables[$this->table]['casesensitive'] ) && $this->tables[$this->table]['casesensitive'] ) {
            $this->alphaXX = array_merge( $this->alphaXX, range( 'a', 'z' ) );
        }

        // init cache
        if (!isset(self::$microCache[$this->table])) {
            self::$microCache[$this->table] = array();
        }

        $this->cache    =&  self::$microCache[$this->table];

        // chroot
        $this->root                 =   $this->skel;
        $this->root[$this->primary] =   null;
        $this->root[$this->column]  =   '';
        if (isset($parameter['chroot']) && !empty($parameter['chroot']) && '0' != $parameter['chroot']) {
            $clause     =   array();
            $clause[]   =   array(
                                    'field' =>  $this->primary,
                                    'value' =>  $parameter['chroot']
                                );
            $this->root =   $this->getByClause($clause);
        }

        $this->cache[null]          =   $this->root;
    }

    /**
     * make primary key available
     *
     * fetch column name of primary key
     *
     * @return string
     */
    public function getIdentifier($table = null, $asString = false)
    {
        if (empty($table)) {
            return $this->primary;
        }

        return parent::getIdentifier($table, $asString);
    }

    /**
     * set clause for use with every select
     *
     * This standard clause is appended to very SQL statment. This makes it quite easy
     * to manage multiple trees in in one table. E.g. there may be one tree for every
     * user defined by a column holding the user's id.
     *
     * @see setSkeleton()
     * @param array $clause
     */
    public function setClause($clause = array())
    {
        $this->clause    =    $clause;
        if (empty($this->root[$this->primary])) {
            return;
        }
        $this->clause[] =   array(
                                    'field'     =>  $this->column,
                                    'relation'  =>  'begins',
                                    'value'     =>  $this->root[$this->column]
                                );
    }

    /**
     * Get Current Clause
     *
     * @return array
     */
    public function getClause()
    {
        return $this->clause;
    }

    /**
     * define payload skeleton
     *
     * Set any default values for payload. This data is used whenever
     * a child is created.
     * This method is the counter part of {@link setClause()}
     *
     * @param array $data
     */
    public function setSkeleton($data = array())
    {
        $this->skel    =    $data;
        if (!empty($this->root[$this->primary])) {
            $this->root =   array_merge($this->skel, $this->root);
        }
    }

    /**
     * move node into another place
     *
     * Nove the node itself as well as all child nodes.
     * The primary ids will not be changed.
     *
     * @param string $id node to move
     * @param string $des destination node's id
     * @param array $data additional payload to save
     * @return string node's id
     */
    public function moveChild($id, $des, $data = array())
    {
        // source node to move
        $node       =   $this->get($id);

        // update node itself
        $path   =   $this->getNextPath($des);
        $set    =   array();
        if (!is_array($data)) {
            $data   =   array();
        }
        $data[$this->column]    =   $path;

        // allow to update payload as well
        foreach ($data as $field => $value) {
            $set[]    =    $this->tableName . '.' . $field . '=' . $this->db->quote($value);
        }

        // update node's cache
        if (isset($this->cache[$id])) {
            foreach ($data as $field => $value) {
                $this->cache[$id][$field]   =   $value;
            }
        }

        // identify node by primary key
        $options    =   array();
        $clause     =   $this->clause;
        $clause[]   =   array(
                        'field'     =>    $this->primary,
                        'value'     =>    $id
                    );
        $this->_addDeletedFlag2Clause($this->table, $clause, $options);
        $this->trigger('beforesave', $this->table, $id, $clause, $data);

        $where      = array();
        $this->_clause2Where( $this->table, $clause, $where );

        $query      =   array();
        $query[]    =  'UPDATE';
        $query[]    =   $this->tableName;
        $query[]    =   'SET ' . implode( ', ', $set );
        $query[]    =   'WHERE ' . implode(' AND ', $where);
        $query      =   implode(' ', $query);
        $this->db->query( $query );

        $this->trigger('save', $this->table, $id, $clause, $data);

        // update all children
        $clause     =   $this->clause;
        $clause[]   =   array(
                        'field'     =>  $this->primary,
                        'relation'  =>  'not',
                        'value'     =>  $id
                    );
        $clause[]   =   array(
                        'field'     =>  $this->column,
                        'relation'  =>  'begins',
                        'value'     =>  $node[$this->column]
                    );
        $this->_addDeletedFlag2Clause($this->table, $clause, $options);
        $where  = array();
        $this->_clause2Where( $this->table, $clause, $where );

        $query      =   array();
        $query[]    =   'SELECT';
        $query[]    =   $this->tableName . '.' . $this->column . ' AS path, '
                        . $this->tableName . '.' . $this->primary;
        $query[]    =   'FROM';
        $query[]    =   $this->tableName;
        $query[]    =   'WHERE ' . implode(' AND ', $where);
        $query      =   implode(' ', $query);
        $res        =   $this->db->query( $query );

        $clause     =   $this->clause;
        $index      =   count($clause);
        $clause[]   =   array(
                        'field' =>  $this->primary,
                        'value' =>  null
                    );

        // update path of each child
        $length     =   strlen($node[$this->column]);
        while( $l = $res->fetch_assoc() ) {
            $value  =   $path . substr($l['path'], $length);

            // update path
            $set    =   array();
            $set[]  =   $this->tableName . '.' . $this->column . '=' . $this->db->quote( $value );

            // select by id
            $clause[$index]['value'] =   $l[$this->primary];
            $where  = array();
            $this->_clause2Where($this->table, $clause, $where);

            $query      =   array();
            $query[]    =  'UPDATE';
            $query[]    =   $this->tableName;
            $query[]    =   'SET ' . implode(', ', $set);
            $query[]    =   'WHERE ' . implode(' AND ', $where);
            $query      =   implode(' ', $query);
            $resUpdate  =   $this->db->query($query);

            // update cached nodes
            if (isset($this->cache[$l[$this->primary]])) {
                $this->cache[$l[$this->primary]][$this->column] =   $value;
            }
        }
        $res->free_result();

        return $id;
    }

   /**
    * append child node
    *
    * Save data as a child node
    *
    * @param string|null $parent primary id of parent record or null for root
    * @param array $data associative array to be saved to database
    * @return string $key of new record
    */
    public function addChild( $parent, $data )
    {
        if( empty( $data ) ) {
            WBClass::load( 'WBException_Argument' );
            throw new WBException_Argument( 'Cannot add child with empty payload', 1, __CLASS__ );
        }

        // merge with default values
        $data    =    array_merge( $this->skel, $data );

        $data[$this->column]    =   $this->getNextPath($parent);

        // insert changed date
        if( isset( $this->tables[$this->table]['changed'] ) ) {
            $data[$this->tables[$this->table]['changed']] =   gmdate( 'Y-m-d H:i:s' );
        }

        // insert created date
        if( isset( $this->tables[$this->table]['created'] ) ) {
            $data[$this->tables[$this->table]['created']] =   gmdate( 'Y-m-d H:i:s' );
        }

        $this->trigger('beforesave', $this->table, '__new', array(), $data);

        // split data to match SQL syntax
        $fields =   array_keys($data);
        $values =   array_map(array($this->db, 'quote'), array_values($data));

        $query      =   array();
        $query[]    =   'INSERT';
        $query[]    =   'INTO ' . $this->tableName;
        $query[]    =   '(' . implode(', ', $fields) . ')';
        $query[]    =   'VALUES (' . implode(', ', $values) . ')';
        $query      =   implode(' ', $query);

        $res        =   $this->db->query( $query );
        $dbc        =   $this->db->connect();
        $id         =   $dbc->insert_id;

        $this->trigger('save', $this->table, $id, array(), $data);
        return $id;
    }

    /**
     * calulate next path string
     *
     * select last children's path and add 1
     *
     * @param string $parent id of parent node
     * @return string path string
     */
    protected function getNextPath($parent)
    {
        // fetch parent
        $trunk    =    '';
        if( $parent ) {
            $node = $this->get( $parent );
            if( $node !== null ) {
                $trunk = $node[$this->column];
            }
        }

        // find last branch
        $path   = '';
        $suffix = str_repeat( '_', $this->alphaLength );

        $options    =   array();
        $clause     =    $this->clause;
        $clause[]   =    array(
                            'field'     =>    $this->column,
                            'relation'  =>    'native',
                            'operation' =>    'LIKE',
                            'value'     =>    $trunk . $suffix
                        );
        $this->_addDeletedFlag2Clause($this->table, $clause, $options);

        // build query
        $where  = array();
        $this->_clause2Where( $this->table, $clause, $where );
        $query  = 'SELECT max( ' . $this->tableName . '.' . $this->column .' )'
                . ' FROM ' . $this->tableName
                . ' WHERE ' . implode( ' AND ', $where );
        $res    =   $this->db->query( $query );
        $row    =   $res->fetch_row();
        if( isset( $row[0] ) ) {
            $path   =    $row[0];
        }

        // create new path string
        if( !strlen( $path ) ) {
            $path    = $trunk . str_repeat( $this->alphaXX[0], $this->alphaLength );
            return $path;
        }

        $path    =  str_split( $path, $this->alphaLength );
        $last    =  array_pop( $path );
        $last    =  str_split( $last );

        $max     =  count( $this->alphaXX ) - 1;
        $alphaXX =  array_flip( $this->alphaXX );

        // add one
        for( $i = count( $last ) - 1; $i >= 0; --$i ) {
            $c  = $last[$i];
            $j  = $alphaXX[$c];
            if( $j < $max ) {
                ++$j;
                $last[$i] = $this->alphaXX[$j];
                $c  = $last[$i];
                break;
            }

            $j  = 0;
            $last[$i]   = $this->alphaXX[$j];
        }

        $path    = $trunk . implode( '', $last );
        return $path;
    }

    /**
     * Remove node and children
     *
     * Delete node by id. Also wipe out all child nodes
     *
     * @param string $id of node to delete
     * @return int number of deleted nodes
     */
    public function delete($id)
    {
        $c          =   $this->getTableInfo($this->table);
        $clause     =    $this->clause;
        $clause[]   =    array(
                        'field'	=>    $this->primary,
                        'value'	=>    $id
                    );
        $where      = array();
        $this->_clause2Where( $this->table, $clause, $where );

        $query  = 'SELECT ' . $this->column
                . ' FROM ' . $this->tableName
                . ' WHERE ' . implode( ' AND ', $where );

        $res    =    $this->db->query( $query );
        $row    =    $res->fetch_array();
        $path   =    $row[0];
        $res->free_result();

        $clause =    $this->clause;
        if( $path ) {
            $clause[]    =    array(
            						'field'    => $this->column,
            						'value'    => $path,
            						'relation' => 'begins'
                                    );
        }

        $q      =   array();
        $q[]    =   'DELETE FROM';
        $q[]    =   $c['table'];
        if (isset($c['delete']) && !empty($c['delete'])) {
            $q[0]  =   'UPDATE';
            $q[]   =   'SET';
            $q[]   =   $c['delete'] . '=1';
            if (!empty($c['changed'])) {
                $q[]   =   ', ' . $c['changed'] . '="' . gmdate('Y-m-d H:i:s') . '"';
            }
        }
        $where	=	array();
        $this->_clause2Where($this->table, $clause, $where);
        $q[]    =   'WHERE';
        $q[]    =   implode(' AND ', $where);
        $query  =   implode(' ', $q);
        $this->db->query( $query );
        $dbc    =    $this->db->connect();
        return $dbc->affected_rows;
    }

    /**
     * fetch node by id
     *
     * Load node from database by id
     *
     * @param string $id
     * @param array $options
     * @return array $node data
     * @throws WBException_Datasource
     */
    public function get( $id, $options = array() )
    {
        if (!is_array($options)) {
            $options    =   array();
        }
        if (!isset($options['withIdAlias'])) {
           $options['withIdAlias']  =   true;
        }

        if( $id == null ) {
            $row    =   $this->root;
            if ($options['withIdAlias']) {
                $row['id']          =   $row[$this->primary];
                $row[$this->column . 'level']   =   0;
            }
            return $row;
        }

        // load from cache or database
        if (isset($this->cache[$id])) {
            $node   =   $this->cache[$id];
        } else {
            $clause    =    $this->clause;
            $this->_addDeletedFlag2Clause($this->table, $clause, $options);
            $clause[]  =    array(
                            'field'	=>    $this->primary,
                            'value'	=>    $id
                        );

            $node   =   $this->getByClause($clause);
            $this->cache[$id]   =   $node;
        }

        if( $options['withIdAlias'] ) {
            $node['id']    =   $node[$this->primary];
        }

        return $node;
    }

    /**
     * Fetch Node by Clause
     *
     * Load node from database by clause
     *
     * @param array $clause
     * @param array $options
     * @return array $node data
     * @throws WBException_Datasource
     */
    private function getByClause($clause, $options = array())
    {
        if (!is_array($options)) {
            $options    =   array();
        }
        $where  = array();
        $this->_addDeletedFlag2Clause($this->table, $clause, $options);
        $this->_clause2Where($this->table, $clause, $where);

        $query  = 'SELECT *'
                . ' FROM ' . $this->tableName
                . ' WHERE ' . implode(' AND ', $where);

        $res    =    $this->db->query( $query );
        $node   =    $res->fetch_assoc();
        $res->free_result();
        $this->translate($this->table, $node);

        if (empty($node)) {
            WBClass::load('WBException_Datasource');
            throw new WBException_Datasource('Node id does not exist!', 1, __CLASS__);
        }

        $node[$this->column . 'level']  =   strlen($node[$this->column]) / $this->alphaLength;

        return $node;
    }

    /**
     * update existing node
     *
     * This allows you to change node's payload
     *
     * @param string $id node's id
     * @param array $data update values
     * @return string $id
     */
    public function set($id, $data)
    {
        $old    =   $this->get($id);
        if (empty($old)) {
            WBClass::load( 'WBException_Datasource' );
            throw new WBException_Datasource( 'Node id does not exist!', 2, __CLASS__ );
        }

        $clause    =    $this->clause;
        $clause[]  =    array(
                        'field' =>    $this->primary,
                        'value' =>    $id
                    );

        $this->trigger('beforesave', $this->table, $id, $clause, $data);

        $set    =   array();
        foreach( $data as $field => $value ) {
            $set[]    =    $this->tableName . '.' . $field . '=' . $this->db->quote( $value );
        }

        $where  = array();
        $this->_clause2Where( $this->table, $clause, $where );
        $query  = 'UPDATE '. $this->tableName
                . ' SET ' . implode( ', ', $set )
                . ' WHERE ' . implode( ' AND ', $where );

        $res    =    $this->db->query( $query );

        // update node's cache
        if (isset($this->cache[$id])) {
            foreach ($data as $field => $value) {
                $this->cache[$id][$field]   =   $value;
            }
        }

        $this->trigger('save', $this->table, $id, array(), $data);
        return $id;
    }

    /**
     * get parent node
     *
     * Fetch parent in tree
     *
     * @param string $id node id
     * @param int $depth how far up you like to climb, use 0 for unlimited
     * @param array $options gerneral options
     * @return array $list list of parent nodes
     * @throws WBException_Argument
     */
    public function getParent( $id, $depth = 1, $options = array() )
    {
        if( !is_int( $depth ) || $depth < 0 ) {
            WBClass::load( 'WBException_Argument' );
            throw new WBException_Argument( 'Parameter "depth" must be an integer greater or equal 0', 2, __CLASS__ );
        }

        if(empty($id)) {
            return array();
        }

        $node    =    $this->get( $id, $options );

        // the parent node is root!
        if( strlen( $node[$this->column] ) <= $this->alphaLength ) {
            return array();
        }

        // build clause by path
        $clause  =    $this->clause;
        $path    =    str_split( $node[$this->column], $this->alphaLength );
        array_pop( $path );
        $i       =    1;
        $c       =    array();
        while( true ) {
            $c[]    =    array(
                        'field'	=>    $this->column,
                        'value'	=>    implode( '', $path )
                    );
            array_pop( $path );
            if( $depth == 0 ) {
                if( empty( $path ) ) {
                    break;
                }
                continue;
            }

            ++$i;
            if( $depth < $i ) {
                break;
            }
        }

        $clause[]    =    array(
                    'type'	    =>    'complex',
                    'bond'	    =>    'OR',
                    'clause'	=>    $c
                );

        $this->_addDeletedFlag2Clause($this->table, $clause, $options);

        $where =    array();
        $this->_clause2Where( $this->table, $clause, $where );
        $query =    'SELECT *'
               .    ' FROM ' . $this->tableName
               .    ' WHERE ' . implode( ' AND ', $where )
               .    ' ORDER BY ' . $this->column;

        $res    =   $this->db->query( $query );

        if( !isset( $options['withIdAlias'] ) ) {
           $options['withIdAlias']  =   true;
        }

        // verify callback
        if( isset( $options['callback'] ) ) {
            if( !( $options['callback'] instanceof WBTable_Callback ) ) {
                WBClass::load('WBException_Argument');
                throw new WBException_Argument( 'The callback object must be an instance of "WBTable_Callback"', 3, __CLASS__ );
            }
        } else {
            $options['callback']    =   null;
        }

        $list   =   array();
        while( $l = $res->fetch_assoc() ) {

            if( $options['withIdAlias'] ) {
                $l['id']    =   $l[$this->primary];
            }

            if( $options['callback'] ) {
                $options['callback']->onDatasourceGet( $this->table, $l[$this->primary], $l );
            }

            $this->translate($this->table, $l);

            $list[] =   $l;
        }
        $res->free_result();
        return $list;
    }

    /**
     * fetch child nodes by id
     *
     * Load child nodes from database. The parent node is defined by id
     *
     * @param string $id parent's node id or parent node array
     * @param int $depth number of levels to fetch
     * @param array $options
     * @return array
     */
    public function getChildren($id, $depth = 1,  $options = array(), $clause = array())
    {
        if( !is_int( $depth ) || $depth < 0 ) {
            WBClass::load( 'WBException_Argument' );
            throw new WBException_Argument( 'Parameter "depth" must be an integer greater or equal 0', 2, __CLASS__ );
        }

        if( $id === null ) {
            // root node is parent
            $parent =   $this->root;
        } else {
            if (is_array($id)) {
                $parent =   $id;
                $id     =   $parent[$this->primary];
            } else {
                $parent =    $this->get( $id );
                if( $parent == null ) {
                    return null;
                }
            }
        }

        if (!is_array($clause)) {
            $clause =   array();
        }
        $clause     =   array_merge($clause, $this->clause);
        if( $id !== null ) {
            $clause[]  =    array(
                            'field'	    =>    $this->primary,
                            'relation'	=>    'not',
                            'value'	    =>    $id
                        );
        }
        if( $depth == 0 ) {
            $clause[]    =    array(
                                'field'	    =>    $this->column,
                                'relation'	=>    'begins',
                                'value'	    =>    $parent[$this->column]
                            );
        } else {
            $c    =    array();
            for( $i = 1; $i <= $depth; ++$i ) {
                $branch =    $parent[$this->column] . str_repeat( '_', $i * $this->alphaLength );
                $c[]    =    array(
                                'field'	    =>    $this->column,
                                'relation'	=>    'native',
                                'operation'	=>    'LIKE',
                                'value'	    =>    $branch
                            );
            }
            $clause[]    =    array(
                                'type'	    =>    'complex',
                                'bond'	    =>    'OR',
                                'clause'    =>    $c
                            );
        }

        // finally build query
        $this->_addDeletedFlag2Clause($this->table, $clause, $options);
        $where    =    array();
        $this->clause2Where($this->table, $clause, $where);


        $query      =   array('SELECT * FROM');
        $query[]    =   $this->tableName;
        $this->addJoin($this->table, $options, $query);
        $query[]    =   'WHERE';
        $query[]    =   implode(' AND ', $where);
        $this->addGroupBy($this->table, $options, $query);
        $this->_addOrderBy($this->table, $options, $query);

        $res    =   $this->db->query(implode(' ', $query));

        if( !isset( $options['withIdAlias'] ) ) {
           $options['withIdAlias']  =   true;
        }

        // verify callback
        if( isset( $options['callback'] ) ) {
            if( !( $options['callback'] instanceof WBTable_Callback ) ) {
                throw new WBException_Argument( 'The callback object must be an instance of "WBTable_Callback"', 3, __CLASS__ );
            }
        } else {
            $options['callback']    =   null;
        }

        $list   =   array();
        while( $l = $res->fetch_assoc() ) {

            if( $options['withIdAlias'] ) {
                $l['id']    =   $l[$this->primary];
            }

            if( $options['callback'] ) {
                $options['callback']->onDatasourceGet( $this->table, $l[$this->primary], $l );
            }

            $this->translate($this->table, $l);

            $list[] =   $l;
        }
        $res->free_result();
        return $list;
    }
}