AppStateESS/InternshipInventory

View on GitHub
class/SubselectDatabase.php

Summary

Maintainability
F
3 wks
Test Coverage
<?php
/**
 * This file is part of Internship Inventory.
 *
 * Internship Inventory is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.

 * Internship Inventory is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License version 3
 * along with Internship Inventory.  If not, see <http://www.gnu.org/licenses/>.
 *
 * Copyright 2011-2018 Appalachian State University
 */

namespace Intern;
use \PHPWS_Core;
use \PHPWS_Error;
use \PHPWS_DB;
use \PHPWS_DB_Where;

PHPWS_Core::initCoreClass('PHPWS_DB.php');
//PHPWS_Core::initCoreClass('DB/PHPWS_DB_Where.php');
//require_once PHPWS_SOURCE_DIR . 'src/phpws/src/DB/PHPWS_DB_Where.php';
require_once PHPWS_SOURCE_DIR . 'core/class/DB/PHPWS_DB_Where.php';

/**
 * A database class tailored for doing sub-selects
 *
 * @author  Matt McNaney <mcnaney at gmail dot com>
 * @author  Jeremy Booker
 * @package Intern
 */

// Defines set in parent class

class SubselectDatabase extends PHPWS_DB{

    public function __construct($table = null)
    {
        parent::__construct($table);
    }

    public static function _updateCurrent($key)
    {
        $GLOBALS['PHPWS_DB']['lib'] = $GLOBALS['PHPWS_DB']['dbs'][$key]['lib'];
        $GLOBALS['PHPWS_DB']['dsn'] = & $GLOBALS['PHPWS_DB']['dbs'][$key]['dsn'];
        $GLOBALS['PHPWS_DB']['connection'] = $GLOBALS['PHPWS_DB']['dbs'][$key]['connection'];
        $GLOBALS['PHPWS_DB']['tbl_prefix'] = & $GLOBALS['PHPWS_DB']['dbs'][$key]['tbl_prefix'];
        $GLOBALS['PHPWS_DB']['type'] = & $GLOBALS['PHPWS_DB']['dbs'][$key]['type'];
    }

    public static function query($sql, $prefix = true)
    {
        PHPWS_DB::touchDB();
        if ($prefix) {
            $sql = PHPWS_DB::prefixQuery($sql);
        }

        PHPWS_DB::logDB($sql);

        return $GLOBALS['PHPWS_DB']['connection']->query($sql);
    }

    public function getColumnInfo($col_name, $parsed = false)
    {
        if (!isset($this->_columnInfo)) {
            $this->getTableColumns();
        }

        if (isset($this->_columnInfo[$col_name])) {
            if ($parsed == true) {
                return $this->parsePearCol($this->_columnInfo[$col_name], true);
            } else {
                return $this->_columnInfo[$col_name];
            }
        } else {
            return null;
        }
    }
    /**
     * Gets information on all the columns in the current table
     */
    public function getTableColumns($fullInfo = false)
    {
        static $table_check = null;

        $table_compare = implode(':', $this->tables);

        if (!$table_check || $table_check == $table_compare) {
            if (isset($this->_allColumns) && $fullInfo == false) {
                return $this->_allColumns;
            } elseif (isset($this->_columnInfo) && $fullInfo == true) {
                return $this->_columnInfo;
            }
        }

        $table_check = $table_compare;
        foreach ($this->tables as $table) {
            if (!isset($table)) {
                return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::getTableColumns');
            }

            $table = $this->addPrefix($table);

            $GLOBALS['PHPWS_DB']['connection']->loadModule('Reverse', null, true);
            $columns = $GLOBALS['PHPWS_DB']['connection']->tableInfo($table);

            if (PHPWS_Error::isError($columns)) {
                PHPWS_Error::log('Could not get columns in table: ' . $table);
                PHPWS_Error::log($columns);
                return $columns;
            }

            foreach ($columns as $colInfo) {
                $col_name = & $colInfo['name'];
                $this->_columnInfo[$col_name] = $colInfo;
                $this->_allColumns[$col_name] = $col_name;
            }
        }

        if ($fullInfo == true) {
            return $this->_columnInfo;
        } else {
            return $this->_allColumns;
        }
    }

    /**
     * Returns true is the columnName is contained in the
     * current table
     */
    public function isTableColumn($column_name)
    {
        $columns = $this->getTableColumns();
        if (PHPWS_Error::isError($columns)) {
            return $columns;
        }
        if (strpos($column_name, '.')) {
            $a = explode('.', $column_name);
            $column_name = array_pop($a);
        }

        return in_array($column_name, $columns);
    }

    public function setMode($mode)
    {
        switch (strtolower($mode)) {
            case 'ordered':
                $this->mode = MDB2_FETCHMODE_ORDERED;
                break;

            case 'object':
                $this->mode = MDB2_FETCHMODE_OBJECT;
                break;

            case 'assoc':
                $this->mode = MDB2_FETCHMODE_ASSOC;
                break;
        }
    }

    public function getMode()
    {
        return $this->mode;
    }

    public static function isTable($table)
    {
        PHPWS_DB::touchDB();
        $tables = PHPWS_DB::listTables();

        $table = PHPWS_DB::addPrefix($table);
        return in_array($table, $tables);
    }

    public function listDatabases()
    {
        PHPWS_DB::touchDB();
        return $GLOBALS['PHPWS_DB']['connection']->getlistOf('databases');
    }

    public function addJoin($join_type, $join_from, $join_to, $join_on_1 = null, $join_on_2 = null, $ignore_tables = false)
    {
        $this->_join_tables[] = array('join_type' => $join_type,
            'join_from' => $join_from,
            'join_to' => $join_to,
            'join_on_1' => $join_on_1,
            'join_on_2' => $join_on_2,
            'ignore_tables' => $ignore_tables);
    }

    public function addTable($table, $as = null)
    {
        if (is_array($table)) {
            foreach ($table as $tbl_name) {
                $this->addTable($tbl_name);
            }
            return;
        }
        if (PHPWS_DB::allowed($table)) {
            if ($as) {
                $this->table_as[$as] = $table;
            } elseif (empty($this->tables) || !in_array($table, $this->tables)) {
                $this->tables[] = $table;
            }
        } else {
            return PHPWS_Error::get(PHPWS_DB_BAD_TABLE_NAME, 'core', 'PHPWS_DB::addTable', $table);
        }
        return true;
    }

    public function setTable($table)
    {
        $this->tables = array();
        $this->_join_tables = null;
        $this->_columnInfo = null;
        $this->_allColumns = null;
        return $this->addTable($table);
    }

    public function addSubSelect(PHPWS_DB $db, $as, $type='select')
    {
        $this->tables = array(); // reset table list
        $this->table_as = array();

        // Grab the SQL and throw parens around it

        //$sql = '(' . $db->getTheQuery($type) . ') as ' . $as;
        $sql = '(' . $db->getTheQuery($type) . ')';

        // brute force add it to the table list
        $this->table_as[$as] = $sql;
        //$this->tables[] = $sql;
    }

    public function setIndex($index)
    {
        $this->index = $index;
    }

    public function _getJoinOn($join_on_1, $join_on_2, $table1, $table2, $ignore_tables = false)
    {
        if (empty($join_on_1) || empty($join_on_2)) {
            return null;
        }

        if (is_object($table1) && get_class($table1) == 'PHPWS_DB') {
            if (empty($table1->subselect_as)) {
                return null;
            }
            $table1->return_query = true;
            $this->table_as[$table1->subselect_as] = sprintf('(%s)', $table1->select());
            $table1 = $table1->subselect_as;
        }

        if (is_object($table2) && get_class($table2) == 'PHPWS_DB') {
            if (empty($table2->subselect_as)) {
                return null;
            }

            $table2->return_query = true;
            $this->table_as[$table2->subselect_as] = sprintf('(%s)', $table2->select());
            $table2 = $table2->subselect_as;
        }

        if (is_array($join_on_1) && is_array($join_on_2)) {
            foreach ($join_on_1 as $key => $value) {
                if ($ignore_tables || preg_match('/\w\.\w/', $value)) {
                    $value1 = & $value;
                } else {
                    $value1 = $table1 . '.' . $value;
                }

                if ($ignore_tables || preg_match('/\w\.\w/', $join_on_2[$key])) {
                    $value2 = & $join_on_2[$key];
                } else {
                    $value2 = $table2 . '.' . $join_on_2[$key];
                }

                $retVal = array();
                $retVal[] = sprintf('%s = %s', $value1, $value2);
            }
            return implode(' AND ', $retVal);
        } else {
            return sprintf('%s.%s = %s.%s', $table1, $join_on_1, $table2, $join_on_2);
        }
    }

    public function getJoin()
    {
        if (empty($this->_join_tables)) {
            return null;
        }

        $join_info = array();
        $join_info['tables'] = array();
        foreach ($this->_join_tables as $join_array) {
            $dup = md5(serialize($join_array));
            if (isset($dup_list) && in_array($dup, $dup_list)) {
                continue;
            }
            $dup_list[] = $dup;
            extract($join_array);

            if ($result = $this->_getJoinOn($join_on_1, $join_on_2, $join_from, $join_to, $ignore_tables)) {
                $join_on = 'ON ' . $result;
            }

            if (is_object($join_to) && get_class($join_to) == 'PHPWS_DB') {
                if (empty($join_to->subselect_as)) {
                    return null;
                }
                $join_to = $join_to->subselect_as;
            }

            if (is_object($join_from) && get_class($join_from) == 'PHPWS_DB') {
                if (empty($join_from->subselect_as)) {
                    return null;
                }
                $join_from = $join_from->subselect_as;
            }

            // If join_to is aliased, then add the alias
            if (isset($this->table_as[$join_to])) {
                $join_to = $this->table_as[$join_to] . ' AS ' . $join_to;
            }
            $join_tables[] = $join_to;

            // If join_from is aliased, then add the alias
            if (isset($this->table_as[$join_from])) {
                $join_from = $this->table_as[$join_from] . ' AS ' . $join_from;
            }
            $join_tables[] = $join_from;

            if (in_array($join_from, $join_info['tables'])) {
                $allJoin[] = sprintf('%s %s %s', strtoupper($join_type) . ' JOIN', $join_to, $join_on);
            } elseif (in_array($join_to, $join_info['tables'])) {
                $allJoin[] = sprintf('%s %s %s', strtoupper($join_type) . ' JOIN', $join_from, $join_on);
            } else {
                //$allJoin[] = sprintf('%s %s %s %s', $join_from, strtoupper($join_type) . ' JOIN', $join_to, $join_on);
                //$allJoin[] = $join_from . ' ' . strtoupper($join_type) . ' JOIN ' . "$join_to $join_on";
                $allJoin[] = strtoupper($join_type) . ' JOIN ' . "$join_to $join_on";
            }

            $join_info['tables'] = $join_tables;
        }

        $join_info['join'] = implode(' ', $allJoin);
        return $join_info;
    }

    /**
     * if format is true, all tables in the array are returned. This
     * is used for select queries. If false, the first table is popped
     * off and returned
     */
    public function getTable($format = true)
    {
        if (empty($this->tables) && empty($this->table_as)) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::getTable');
        }

        if($format === false){
            return $this->getSourceTable(true);
        }

        $tableList = array();

        foreach($this->tables as $table){
            $tableList[] = $table;
        }

        // Add aliased tables
        foreach($this->table_as as $alias=>$table){
            $tableList[] = "$table AS $alias";
        }

        $join_info = $this->getJoin();

        $joinList = array();

        if ($join_info) {
            $joinList[] = $join_info['join'];
        }
        /*elseif (!empty($this->table_as)) {
         foreach ($this->table_as as $sub => $table) {
        $table_list[] = sprintf('%s as %s', $table, $sub);
        }
        }*/

        // Assemble table names and join list. NB: Joins are not separated by commas
        return implode(',', $tableList) . ' ' . implode(' ', $joinList);
    }

    public function resetTable()
    {
        $this->tables = array();
    }

    public function setGroupConj($group, $conj)
    {
        $conj = strtoupper($conj);
        if (empty($conj) || ($conj != 'OR' && $conj != 'AND')) {
            return false;
        }

        $this->where[$group]['conj'] = $conj;
    }

    public function addGroupBy($group_by)
    {
        if (PHPWS_DB::allowed($group_by)) {
            if (!strpos($group_by, '.')) {
                //$group_by = $this->tables[0] . '.' . $group_by;
                $group_by = $this->getSourceTable() . '.' . $group_by;
            }

            if (empty($this->group_by) || !in_array($group_by, $this->group_by)) {
                $this->group_by[] = $group_by;
            }
        }
        return true;
    }

    public function getGroupBy($dbReady = false)
    {
        if ((bool) $dbReady == true) {
            if (empty($this->group_by)) {
                return null;
            } else {
                return 'GROUP BY ' . implode(', ', $this->group_by);
            }
        }
        return $this->group_by;
    }

    /**
     * Puts the first group label into the second
     */
    public function groupIn($sub, $main)
    {
        $group_names = array_keys($this->where);
        if (!in_array($sub, $group_names) || !in_array($main, $group_names)) {
            return false;
        }
        $this->group_in[$sub] = $main;
        return true;
    }

    public function addWhere($column, $value = null, $operator = null, $conj = null, $group = null, $join = false)
    {
        PHPWS_DB::touchDB();

        $where = new PHPWS_DB_Where;
        $where->setJoin($join);
        $operator = strtoupper($operator);

        // If passed in value was an array, loop over the array and call this method once for each column name
        if (is_array($column)) {
            foreach ($column as $new_column => $new_value) {
                $result = $this->addWhere($new_column, $new_value, $operator, $conj, $group);
                if (PHPWS_Error::isError($result)) {
                    return $result;
                }
            }
            return true;
        } else {
            // Single column name passed in, check column name
            if (!PHPWS_DB::allowed($column) || preg_match('[^\w\.]', $column)) {
                return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::addWhere', $column);
            }
        }

        // If non-empty array of values passed in for this column name
        if (is_array($value) && !empty($value)) {
            if (!empty($operator) && $operator != 'IN' && $operator != 'NOT IN' &&
                    $operator != 'BETWEEN' && $operator != 'NOT BETWEEN') {
                $search_in = true;
            } else {
                if (empty($operator)) {
                    $operator = 'IN';
                }
                $search_in = false;
            }

            foreach ($value as $newVal) {
                if ($search_in) {
                    $result = $this->addWhere($column, $newVal, $operator, $conj, $group);
                    if (PHPWS_Error::isError($result)) {
                        return $result;
                    }
                } else {
                    $newVal = $GLOBALS['PHPWS_DB']['connection']->escape($newVal);

                    $new_value_list = array();
                    $new_value_list[] = $newVal;
                }
            }

            if (!$search_in && isset($new_value_list)) {
                $value = &$new_value_list;
            } else {
                return true;
            }
        } else {
            // Single value passed in
            if (is_null($value) || (is_string($value) && strtoupper($value) == 'NULL')) {
                if (empty($operator) || ( $operator != 'IS NOT' && $operator != '!=')) {
                    $operator = 'IS';
                } else {
                    $operator = 'IS NOT';
                }
                $value = 'NULL';
            } else {
                $value = $GLOBALS['PHPWS_DB']['connection']->escape($value);
            }
        }

        $source_table = $this->getSourceTable();
        //$source_table = $this->tables[0];
        if (is_string($column)) {
            if (substr_count($column, '.') == 1) {
                list($join_table, $join_column) = explode('.', $column);

                if (isset($this->table_as[$join_table])) {
                    $source_table = $join_table;
                    $column = & $join_column;
                } elseif (PHPWS_DB::inDatabase($join_table, $join_column)) {
                    $source_table = $join_table;
                    /***
                     * Commented out because this is trying to work too hard.
                     * If you (as a developer) haven't selected from or joined
                     * the table you're trying to add a 'WHERE' expression for,
                     * then I can't help you. The query will fail, and you'll figure it out.
                     */
                    //$this->addTable($join_table);
                }
            }
        }//TODO what do we do if $column isn't a string?

        $where->setColumn($column);
        $where->setTable($source_table);

        if (is_string($value)) {
            if (substr_count($value, '.') == 1) {
                list($join_table, $join_column) = explode('.', $value);
                if (isset($this->table_as[$join_table])) {
                    $where->setJoin(true);
                } elseif ($this->inDatabase($join_table, $join_column)) {
                    $where->setJoin(true);
                    $this->addTable($join_table);
                }
            }
        }

        $where->setValue($value);
        $where->setConj($conj);
        $where->setOperator($operator);

        if (isset($group)) {
            $this->where[$group]['values'][] = $where;
        } else {
            $this->where['afancyspecialgroup']['values'][] = $where;
        }
    }

    private function getSourceTable($use_as = true)
    {
        if (isset($this->tables[0])) {
            return $this->tables[0];
        } else {
            foreach ($this->table_as as $as => $table);
            reset($this->table_as);
            if ($use_as) {
                return $as;
            } else {
                return $table;
            }
        }
    }

    public static function checkOperator($operator)
    {
        $allowed = array('>',
            '>=',
            '<',
            '<=',
            '=',
            '!=',
            '<>',
            '<=>',
            'LIKE',
            'ILIKE',
            'NOT LIKE',
            'NOT ILIKE',
            'REGEXP',
            'RLIKE',
            'IN',
            'NOT IN',
            'BETWEEN',
            'NOT BETWEEN',
            'IS',
            'IS NOT',
            '~');

        return in_array(strtoupper($operator), $allowed);
    }

    public function setQWhere($where, $conj = 'AND')
    {
        $conj = strtoupper($conj);
        if (empty($conj) || ($conj != 'OR' && $conj != 'AND')) {
            return false;
        }

        $where = preg_replace('/where/i', '', $where);
        $this->qwhere['where'] = $where;
        $this->qwhere['conj'] = $conj;
    }

    // /**
    //  * Grabs the where variables from the object and creates a sql query
    //  */
    // public function getWhere($dbReady = false)
    // {
    //     $sql = array();
    //     $ignore_list = $where = null;
    //
    //     if (empty($this->where)) {
    //         if (isset($this->qwhere)) {
    //             return ' (' . $this->qwhere['where'] . ')';
    //         }
    //         return null;
    //     }
    //     $startMain = false;
    //     if ($dbReady) {
    //         foreach ($this->where as $group_name => $groups) {
    //             $hold = null;
    //             $subsql = array();
    //             if (!isset($groups['values'])) {
    //                 continue;
    //             }
    //
    //             $startSub = false;
    //             foreach ($groups['values'] as $whereVal) {
    //                 if ($startSub == true) {
    //                     $subsql[] = $whereVal->conj;
    //                 }
    //                 $subsql[] = $whereVal->get();
    //                 $startSub = true;
    //             }
    //
    //             $where_list = array();
    //             $where_list[$group_name]['group_sql'] = $subsql;
    //
    //             if (isset($groups['conj']) && $conj = $groups['conj']) {
    //                 $where_list[$group_name]['group_conj'] = $conj;
    //             } else {
    //                 $where_list[$group_name]['group_conj'] = 'AND';
    //             }
    //
    //             if (@$search_key = array_search($group_name, $this->group_in, true)) {
    //                 $where_list[$search_key]['group_in'][$group_name] = &$where_list[$group_name];
    //             }
    //         }
    //
    //         var_dump($where_list);
    //
    //         if (!empty($where_list)) {
    //             $sql[] = $this->_buildGroup($where_list, $ignore_list, true);
    //         }
    //
    //         if (isset($this->qwhere)) {
    //             $sql[] = $this->qwhere['conj'] . ' (' . $this->qwhere['where'] . ')';
    //         }
    //
    //         if (isset($sql)) {
    //             $where = implode(' ', $sql);
    //         }
    //         return $where;
    //     } else {
    //         return $this->where;
    //     }
    // }

    // /**
    //  * Handles the imbedding of where groups
    //  */
    // public function _buildGroup($where_list, &$ignore_list, $first = false)
    // {
    //     if (!$ignore_list) {
    //         $ignore_list = array();
    //     }
    //     foreach ($where_list as $group_name => $group_info) {
    //         if (isset($ignore_list[$group_name])) {
    //             continue;
    //         }
    //         $ignore_list[$group_name] = true;
    //         extract($group_info);
    //
    //         if (!$first) {
    //             $sql[] = $group_conj;
    //         } else {
    //             $first = false;
    //         }
    //
    //         if (!empty($group_in)) {
    //             $sql[] = '( ( ' . implode(' ', $group_sql) . ' )';
    //             $result = $this->_buildGroup($group_in, $ignore_list);
    //             if ($result) {
    //                 $sql[] = $result;
    //             }
    //             $sql[] = ' )';
    //         } else {
    //             $sql[] = '( ' . implode(' ', $group_sql) . ' )';
    //         }
    //     }
    //     if (!empty($sql)) {
    //         return implode(' ', $sql);
    //     }
    // }

    public function resetWhere()
    {
        $this->where = array();
    }

    public function isDistinct()
    {
        return (bool) $this->_distinct;
    }

    public function setDistinct($distinct = true)
    {
        $this->_distinct = (bool) $distinct;
    }

    public function addColumn($column, $max_min = null, $as = null, $count = false, $distinct = false, $coalesce = null)
    {

        /****
         * What is this for?
         * Also, this seems to be copy/pasted from PHPWS_DB::allowd()
         *
        if (preg_match('/[^\w\.*]/', $column)) {
            return false;
            // Just return false isn't helpful
        }
        */

        if (!in_array(strtolower($max_min), array('max', 'min'))) {
            $max_min = null;
        }

        $table = $this->getSourceTable();
        /*
        //$table = $this->tables[0];
        if (strpos($column, '.')) {
            list($table, $column) = explode('.', $column);
            if (!isset($this->table_as[$table])) {
                //$this->addTable($table);
            }
        }
        */

        if (!empty($as)) {
            if (!PHPWS_DB::allowed($as)) {
                return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::addColumn', $as);
            }
        }

        if (!PHPWS_DB::allowed($column)) {
            return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::addColumn', $column);
        }

        if ($distinct && !$count) {
            $this->addGroupBy($table . '.' . $column);
        }

        $col = array();
        $col['table'] = $table;
        $col['name'] = $column;
        $col['max_min'] = $max_min;
        $col['count'] = (bool) $count;
        $col['distinct'] = (bool) $distinct;
        $col['coalesce'] = $coalesce;
        if ($column != '*') {
            $col['as'] = $as;
        }

        $this->columns[] = $col;
    }

    /**
     * Adds a column name to the set of columns
     * *without* doing any sort of extra processing on
     * the column name passed in. This is helpful for adding
     * columns which are expressions involving functions other
     * than the min/max/count allowed in addColumn().
     *
     * NB: You *MUST* filter your input before using this method. It WILL allow SQL injection.
     *
     * @param string $columnName Any expression which is valid as an column name
     */
    public function addColumnRaw($columnName)
    {
        $this->columns[] = array('table'=>'',
                'name'=>$columnName,
                'max_min' => null,
                'count' => false,
                'distinct' => false,
                'coalesce' => null,
                'as' => null);
    }

    public function getAllColumns()
    {
        $columns = array();
        $columns[] = $this->getColumn(true);
        return $columns;
    }

    public function checkTableAs($table)
    {
        if ($found = array_search($table, $this->table_as)) {
            return $found;
        } else {
            return $table;
        }
    }

    public function getColumn($format = false)
    {
        if ($format) {
            if (empty($this->columns)) {
                //return $this->tables[0] . '.*';
                return $this->getSourceTable() . '.*';
            } else {
                foreach ($this->columns as $col) {
                    $as = null;
                    extract($col);

                    // Don't care if it's a table or not. If it's invalid, the database will tell us
                    $table = $this->checkTableAs($table);

                    if ($count) {
                        if ($distinct) {
                            $table_name = sprintf('count(distinct(%s.%s))', $table, $name);
                        } else {
                            $table_name = sprintf('count(%s.%s)', $table, $name);
                        }
                    } else if (!is_null($coalesce)) {
                        if ($distinct) {
                            $table_name = sprintf('coalesce(distinct(%s.%s), %s)', $table, $name, $coalesce);
                        } else {
                            $table_name = sprintf('coalesce(%s.%s, %s)', $table, $name, $coalesce);
                        }
                    } else {
                        if ($distinct) {
                            $table_name = sprintf('distinct(%s.%s)', $table, $name);
                        } else {
                            // If it looks like a valid table name, then prefix the column with the table name
                            if($this->inDatabase($table)){
                                $table_name = "$table.$name";
                            }else{
                                // Otherwise, just use the column name given... It's probably an expression
                                $table_name = $name;
                            }
                        }
                    }
                    if ($max_min) {
                        $table_name = strtoupper($max_min) . "($table_name)";
                    }
                    if (!empty($as)) {
                        $columns[] = "$table_name AS $as";
                    } else {
                        $columns[] = "$table_name";
                    }
                }
                return implode(', ', $columns);
            }
        } else {
            return $this->columns;
        }
    }

    /**
     * Sets the result array key to the value of the indexby column.
     * If you expect multiple results per index, you may wish to set
     * force_array to true. This will ensure the results per line are always
     * an array of results.
     *
     * For example, if you group by a foreign key you may get 2 results on one
     * index a only one on the other. Here is an example array were that the result:
     *
     * 'cat' => 0 => 'Whiskers'
     *          1 => 'Muffin'
     * 'dog' => 'Rover'
     *
     * If you knew that repeats were possible and set force_array to true, this
     * would be the result instead:
     *
     * 'cat' => 0 => 'Whiskers'
     *          1 => 'Muffin'
     * 'dog' => 0 => 'Rover'
     *
     */
    public function setIndexBy($indexby, $force_array = false, $ignore_dups = false)
    {
        if (strstr($indexby, '.')) {
            $indexby = substr($indexby, strpos($indexby, '.') + 1);
        }
        $this->indexby = $indexby;
        $this->force_array = (bool) $force_array;
        $this->ignore_dups = (bool) $ignore_dups;
    }

    public function getIndexBy()
    {
        return $this->indexby;
    }

    /**
     * Allows you to add an order or an array of orders to
     * a db query
     *
     * sending random or rand with or without the () will query random
     * element
     */
    public function addOrder($order)
    {
        if (is_array($order)) {
            foreach ($order as $value) {
                $this->addOrder($value);
            }
        } else {
            $order = preg_replace('/[^\w\s\.\(\),]/', '', $order);

            if (preg_match('/(random|rand)(\(\))?/i', $order)) {
                $this->order[] = $GLOBALS['PHPWS_DB']['lib']->randomOrder();
            } else {
                if (strpos($order, '.')) {
                    list($table, $new_order) = explode('.', $order);
                    $this->order[] = array('table' => $table, 'column' => $new_order);
                } else {
                    //$this->order[] = array('table' => $this->tables[0], 'column' => $order);
                    $this->order[] = array('table' => $this->getSourceTable(), 'column' => $order);
                }
            }
        }
    }

    /**
     * Allows you to add an order or an array of orders to
     * a db query
     *
     * sending random or rand with or without the () will query random
     * element
     */
    public function addOrderRaw($order)
    {
        if (is_array($order)) {
            foreach ($order as $value) {
                $this->addOrder($value);
            }
        } else {
            $this->order = $this->order . $order;
        }
    }

    public function getOrder($dbReady = false)
    {
        if (empty($this->order)) {
            return null;
        }

        if ($dbReady) {
            foreach ($this->order as $aOrder) {
                $order_list = array();
                if (is_array($aOrder)) {
                    $order_list[] = $this->checkTableAs($aOrder['table']) . '.' . $aOrder['column'];
                } else {
                    // for random orders
                    $order_list[] = $aOrder;
                }
            }
            return 'ORDER BY ' . implode(', ', $order_list);
        } else {
            return $this->order;
        }
    }

    public function resetOrder()
    {
        $this->order = array();
    }

    public function addValue($column, $value = null)
    {
        if (is_array($column)) {
            foreach ($column as $colKey => $colVal) {
                $result = $this->addValue($colKey, $colVal);
                if (PHPWS_Error::isError($result)) {
                    return $result;
                }
            }
        } else {
            if (!PHPWS_DB::allowed($column)) {
                return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::addValue', $column);
            }

            if (!empty($this->_joined_tables) && !strpos($column, '.')) {
                $all_columns = $this->getTableColumns(true);
                if (isset($all_columns[$column])) {
                    $column = $all_columns[$column]['table'] . '.' . $column;
                } else {
                    trigger_error("Column name '$column' not found", E_USER_ERROR);
                }
            }
            $this->values[$column] = $value;
        }
    }

    public function getValue($column)
    {
        if (empty($this->values) || !isset($this->values[$column])) {
            return null;
        }

        return $this->values[$column];
    }

    public function resetValues()
    {
        $this->values = array();
    }

    public function getAllValues()
    {
        if (!isset($this->values) || empty($this->values)) {
            return null;
        }

        return $this->values;
    }

    public function setLimit($limit, $offset = null)
    {
        unset($this->limit);

        if (is_array($limit)) {
            $_limit = $limit[0];
            $_offset = $limit[1];
        } elseif (preg_match('/,/', $limit)) {
            $split = explode(',', $limit);
            $_limit = trim($split[0]);
            $_offset = trim($split[1]);
        } else {
            $_limit = $limit;
            $_offset = $offset;
        }

        $this->limit['total'] = preg_replace('/[^\d\s]/', '', $_limit);

        if (isset($_offset)) {
            $this->limit['offset'] = preg_replace('/[^\d\s]/', '', $_offset);
        }

        return true;
    }

    public function getLimit($dbReady = false)
    {
        if (empty($this->limit)) {
            return null;
        }

        if ($dbReady) {
            return $GLOBALS['PHPWS_DB']['lib']->getLimit($this->limit);
        } else {
            return $this->limit;
        }
    }

    public function resetLimit()
    {
        $this->limit = '';
    }

    public function resetColumns()
    {
        $this->columns = null;
    }

    public function affectedRows()
    {
        $query = PHPWS_DB::lastQuery();
        $process = strtolower(substr($query, 0, strpos($query, ' ')));

        if ($process == 'select') {
            return false;
        }

        return $GLOBALS['PHPWS_DB']['connection']->affectedRows();
    }

    /**
     * Resets where, values, limits, order, columns, indexby, and qwhere
     * Does NOT reset locked tables but does remove any tables beyond the
     * initiating one
     */
    public function reset()
    {
        $this->resetWhere();
        $this->resetValues();
        $this->resetLimit();
        $this->resetOrder();
        $this->resetColumns();
        $this->indexby = null;
        $this->qwhere = null;
        //$tmp_table = $this->tables[0];
        $tmp_table = $this->getSourceTable();
        $this->tables = null;
        $this->tables = array($tmp_table);
    }

    public function lastQuery()
    {
        return $GLOBALS['PHPWS_DB']['connection']->last_query;
    }

    public function insert($auto_index = true)
    {
        PHPWS_DB::touchDB();
        $maxID = true;
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::insert');
        }

        $values = $this->getAllValues();

        if (!isset($values)) {
            return PHPWS_Error::get(PHPWS_DB_NO_VALUES, 'core', 'PHPWS_DB::insert');
        }

        if ($auto_index) {
            $idColumn = $this->getIndex();

            if (PHPWS_Error::isError($idColumn)) {
                return $idColumn;
            } elseif (isset($idColumn)) {
                $check_table = $this->addPrefix($table);
                $maxID = $GLOBALS['PHPWS_DB']['connection']->nextId($check_table);
                if (!empty($this->_joined_tables)) {
                    $values[$check_table . '.' . $idColumn] = $maxID;
                } else {
                    $values[$idColumn] = $maxID;
                }
            }
        }

        foreach ($values as $index => $entry) {
            $columns = array();
            $columns[] = $index;
            $set = array();
            $set[] = PHPWS_DB::dbReady($entry);
        }

        $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $set) . ')';
        $result = PHPWS_DB::query($query);

        if (DB::isError($result)) {
            return $result;
        } else {
            return $maxID;
        }
    }

    public function update($return_affected = false)
    {
        PHPWS_DB::touchDB();

        $table = $this->getTable(true);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::insert');
        }

        $values = $this->getAllValues();
        $where = $this->getWhere(true);

        if (!empty($where)) {
            $where = 'WHERE ' . $where;
        }

        if (empty($values)) {
            return PHPWS_Error::get(PHPWS_DB_NO_VALUES, 'core', 'PHPWS_DB::update');
        }

        $columns = array();
        foreach ($values as $index => $data) {
            $columns[] = $index . ' = ' . PHPWS_DB::dbReady($data);
        }

        $limit = $this->getLimit(true);
        $order = $this->getOrder(true);

        $query = "UPDATE $table SET " . implode(', ', $columns) . " $where $order $limit";
        $result = PHPWS_DB::query($query);

        if (DB::isError($result)) {
            return $result;
        } else {
            if ($return_affected) {
                return $this->affectedRows();
            } else {
                return true;
            }
        }
    }

    public function count()
    {
        return $this->select('count');
    }

    public function getSelectSQL($type)
    {
        if ($type == 'count' && empty($this->columns)) {
            $columns = null;
        } else {
            $columns = implode(', ', $this->getAllColumns());
        }

        $table = $this->getTable();

        if ($table == '') {
            throw new Exception('No "from" tables available.');
            //return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::select');
        }

        $where = $this->getWhere(true);
        $order = $this->getOrder(true);
        $limit = $this->getLimit(true);
        $group_by = $this->getGroupBy(true);

        $sql_array['columns'] = & $columns;
        $sql_array['table'] = & $table;
        $sql_array['where'] = & $where;
        $sql_array['group_by'] = & $group_by;
        $sql_array['order'] = & $order;
        $sql_array['limit'] = & $limit;

        return $sql_array;
    }

    /**
     * Returns the SQL query string for this db object
     *
     * @param String $type
     * @return String string
     */
    public function getTheQuery($type){
        $sql_array = $this->getSelectSQL($type);

        if (PHPWS_Error::isError($sql_array)) {
            throw new Exception($sql_array);
        }

        // extract will get $columns, $table, $where, $group_by
        // $order, and $limit
        extract($sql_array);

        if ($type == 'count' || $type == 'count_array') {
            if (empty($columns)) {
                // order and group_by are not needed if count is
                // using all rows
                $order = null;
                $group_by = null;
                $columns = 'COUNT(*)';
            } else {
                $add_group = $columns;
                $columns .= ', COUNT(*)';

                if (empty($group_by)) {
                    $group_by = "GROUP BY $add_group";
                }
            }
        }

        if (!empty($where)) {
            $where = 'WHERE ' . $where;
        }

        /*
        if ($this->isDistinct()) {
            $distinct = 'DISTINCT';
        } else {
            $distinct = null;
        }
        */

        $sql = "SELECT $columns FROM $table $where $group_by $order $limit";

        return $sql;
    }

    /**
     * Retrieves information from the database.
     * Select utilizes parameters set previously in the object
     * (i.e. addWhere, addColumn, setLimit, etc.)
     * You may also set the "type" of result: assoc (associative)
     * col (columns), min (minimum result), max (maximum result), one (a single column from a
     * single row), row (a single row), count (a tally of rows) or all, the default.
     * All returns an associate array containing the requested information.
     *
     */
    public function select($type = null, $sql = null)
    {
        if (empty($sql)) {
            if (!empty($this->sql)) {
                $sql = & $this->sql;
            }
        }
        PHPWS_DB::touchDB();
        if (isset($type) && is_string($type)) {
            $type = strtolower($type);
        }

        $mode = $this->getMode();
        $indexby = $this->getIndexBy();

        if (!isset($sql)) {
            $sql = $this->getTheQuery($type);
        } else {
            $mode = MDB2_FETCHMODE_ASSOC;
        }

        $sql = PHPWS_DB::prefixQuery($sql);

        if ($this->_test_mode) {
            exit($sql);
        }

        if ($this->return_query) {
            return trim($sql);
        }

        // assoc does odd things if the resultant return is two items or less
        // not sure why it is coded that way. Use the default instead

        switch ($type) {
            case 'assoc':
                PHPWS_DB::logDB($sql);
                return $GLOBALS['PHPWS_DB']['connection']->getAssoc($sql, null, null, $mode);

            case 'col':
                if (empty($sql) && empty($this->columns)) {
                    return PHPWS_Error::get(PHPWS_DB_NO_COLUMN_SET, 'core', 'PHPWS_DB::select');
                }

                if (isset($indexby)) {
                    PHPWS_DB::logDB($sql);
                    $result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);

                    if (PHPWS_Error::isError($result)) {
                        return $result;
                    }
                    return PHPWS_DB::_indexBy($result, $indexby, true);
                }
                PHPWS_DB::logDB($sql);
                return $GLOBALS['PHPWS_DB']['connection']->queryCol($sql);

            case 'min':
            case 'max':
            case 'one':
                PHPWS_DB::logDB($sql);
                return $GLOBALS['PHPWS_DB']['connection']->queryOne($sql, null, $mode);

            case 'row':
                PHPWS_DB::logDB($sql);
                return $GLOBALS['PHPWS_DB']['connection']->queryRow($sql, null, $mode);

            case 'count':
                PHPWS_DB::logDB($sql);
                if (empty($this->columns)) {
                    $result = $GLOBALS['PHPWS_DB']['connection']->queryRow($sql);
                    if (PHPWS_Error::isError($result)) {
                        return $result;
                    }
                    return $result['count'];
                } else {
                    $result = $GLOBALS['PHPWS_DB']['connection']->queryCol($sql);
                    if (PHPWS_Error::isError($result)) {
                        return $result;
                    }

                    return count($result);
                }

            case 'count_array':
                PHPWS_DB::logDB($sql);
                $result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
                if (PHPWS_Error::isError($result)) {
                    return $result;
                }
                return $result;


            case 'all':
            default:
                PHPWS_DB::logDB($sql);
                $result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
                if (PHPWS_Error::isError($result)) {
                    return $result;
                }

                if (isset($indexby)) {
                    return PHPWS_DB::_indexBy($result, $indexby);
                }

                return $result;
        }
    }

    public static function getRow($sql)
    {
        $db = new PHPWS_DB;
        return $db->select('row', $sql);
    }

    public static function getCol($sql)
    {
        $db = new PHPWS_DB;
        return $db->select('col', $sql);
    }

    public static function getAll($sql)
    {
        $db = new PHPWS_DB;
        return $db->select('all', $sql);
    }

    public static function getOne($sql)
    {
        $db = new PHPWS_DB;
        return $db->select('one', $sql);
    }

    public static function getAssoc($sql)
    {
        $db = new PHPWS_DB;
        return $db->select('assoc', $sql);
    }

    public function _indexBy($sql, $indexby, $colMode = false)
    {
        $rows = array();

        if (!is_array($sql) || empty($sql)) {
            return $sql;
        }
        $stacked = false;

        foreach ($sql as $item) {
            if (!isset($item[(string) $indexby])) {
                return $sql;
            }

            if ($colMode) {
                $col = $this->getColumn();
                $value = $item[$indexby];
                unset($item[$indexby]);

                foreach ($col as $key => $col_test) {
                    if ($col_test['name'] == $indexby) {
                        unset($col[$key]);
                        break;
                    }
                }

                $column = array_pop($col);
                if (isset($column['as'])) {
                    $col_check = $column['as'];
                } else {
                    $col_check = $column['name'];
                }

                if (isset($item[$col_check]) || $item[$col_check] === null) {
                    PHPWS_DB::_expandIndex($rows, $value, $item[$col_check], $stacked);
                }
            } else {
                PHPWS_DB::_expandIndex($rows, $item[$indexby], $item, $stacked);
            }
        }

        return $rows;
    }

    public function _expandIndex(&$rows, $index, $item, &$stacked)
    {
        if ($this->force_array) {
            $rows[$index][] = $item;
        } elseif (isset($rows[$index]) && !$this->ignore_dups) {
            if (is_array($rows[$index]) && !isset($rows[$index][0])) {
                $hold = $rows[$index];
                $rows[$index] = array();
                $rows[$index][] = $hold;
                $stacked = true;
            }
            if (!$stacked) {
                $hold = $rows[$index];
                $rows[$index] = array();
                $rows[$index][] = $hold;
                $stacked = true;
            }
            if (!is_array($rows[$index])) {
                $i = $rows[$index];
                $rows[$index] = array();
                $rows[$index][] = $i;
            }
            $rows[$index][] = $item;
        } else {
            $rows[$index] = $item;
        }
    }

    /**
     * increases the value of a table column
     */
    public function incrementColumn($column_name, $amount = 1)
    {
        $amount = (int) $amount;

        if ($amount == 0) {
            return true;
        }

        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::incrementColumn');
        }

        $where = $this->getWhere(true);
        if (!empty($where)) {
            $where = 'WHERE ' . $where;
        }

        if ($amount < 0) {
            $math = $amount;
        } else {
            $math = "+ $amount";
        }

        $query = "UPDATE $table SET $column_name = $column_name $math $where";
        $result = PHPWS_DB::query($query);

        if (DB::isError($result)) {
            return $result;
        } else {
            return true;
        }
    }

    /**
     * reduces the value of a table column
     */
    public function reduceColumn($column_name, $amount = 1)
    {
        return $this->incrementColumn($column_name, ($amount * -1));
    }

/*
    public function delete($return_affected = false)
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::delete');
        }

        $where = $this->getWhere(true);
        $limit = $this->getLimit(true);
        $order = $this->getOrder(true);

        if (!empty($where)) {
            $where = 'WHERE ' . $where;
        }
        $sql = "DELETE FROM $table $where $order $limit";
        $result = PHPWS_DB::query($sql);

        if (DB::isError($result)) {
            return $result;
        } else {
            if ($return_affected) {
                return $this->affectedRows();
            } else {
                return true;
            }
        }
    }
    */

    /**
     * Static call only
     * check_existence - of table
     * sequence_table  - if true, drop sequence table as well
     */
    public static function dropTable($table, $check_existence = true, $sequence_table = true)
    {
        PHPWS_DB::touchDB();

        // was using IF EXISTS but not cross compatible
        if ($check_existence && !PHPWS_DB::isTable($table)) {
            return true;
        }

        $result = PHPWS_DB::query("DROP TABLE $table");

        if (PHPWS_Error::isError($result)) {
            return $result;
        }

        if ($sequence_table && PHPWS_DB::isSequence($table)) {
            $result = $GLOBALS['PHPWS_DB']['lib']->dropSequence($table . '_seq');
            if (PHPWS_Error::isError($result)) {
                return $result;
            }
        }

        return true;
    }

    public static function isSequence($table)
    {
        $table = PHPWS_DB::addPrefix($table);
        return is_numeric($GLOBALS['PHPWS_DB']['connection']->nextId($table));
    }

    public function truncateTable()
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::truncateTable()');
        }

        $sql = "TRUNCATE TABLE $table";

        return PHPWS_DB::query($sql);
    }

    public function dropTableIndex($name = null)
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::dropTableIndex');
        }

        if (empty($name)) {
            $name = str_replace('_', '', $table) . '_idx';
        }
        $sql = $GLOBALS['PHPWS_DB']['lib']->dropTableIndex($name, $table);
        return $this->query($sql);
    }

    /**
     * Creates an index on a table. column variable can be a string or
     * an array of strings representing column names.
     * The name of the index is optional. The function will create one based
     * on the table name. Setting your index name might be a smart thing to do
     * in case you ever need to DROP it.
     */
    public function createTableIndex($column, $name = null, $unique = false)
    {
        if (!DB_ALLOW_TABLE_INDEX) {
            return false;
        }

        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::createTableIndex');
        }

        if (is_array($column)) {
            foreach ($column as $col) {
                if (!$this->isTableColumn($col)) {
                    return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::createTableIndex');
                }
            }
            $column = implode(',', $column);
        } else {
            if (!$this->isTableColumn($column)) {
                return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::createTableIndex');
            }
        }

        if (empty($name)) {
            $name = str_replace('_', '', $table) . '_idx';
        }

        if ($unique) {
            $unique_idx = 'UNIQUE ';
        } else {
            $unique_idx = ' ';
        }

        $sql = sprintf('CREATE %sINDEX %s ON %s (%s)', $unique_idx, $name, $table, $column);

        return $this->query($sql);
    }

    public function createPrimaryKey($column = 'id')
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::createTableIndex');
        }
        $sql = sprintf('alter table %s add primary key(%s)', $table, $column);
        return $this->query($sql);
    }

    public function createTable()
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::createTable');
        }

        $values = $this->getAllValues();

        foreach ($values as $column => $value) {
            $parameters[] = $column . ' ' . $value;
        }


        $sql = "CREATE TABLE $table ( " . implode(', ', $parameters) . ' )';
        return PHPWS_DB::query($sql);
    }

    /**
     * Renames a table column
     * Because databases disagree on their commands to change column
     * names, this function requires different factory files.
     * Factory files must handle the prefixing.
     */
    public function renameTableColumn($old_name, $new_name)
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::renameTableColumn');
        }

        $specs = $this->getColumnInfo($old_name, true);
        if (empty($specs)) {
            return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::renameTableColumn', $old_name);
        }

        $sql = $GLOBALS['PHPWS_DB']['lib']->renameColumn($table, $old_name, $new_name, $specs);

        return $this->query($sql, false);
    }

    /**
     * Adds a column to the database table
     *
     * Returns error object if fails. Returns false if table column already
     * exists. Returns true is successful.
     *
     * @param string  column    Name of column to add
     * @param string  parameter Specifics of table column
     * @param string  after     If supported, add column after this column
     * @param boolean indexed   Create an index on the column if true
     * @returns mixed
     */
    public function addTableColumn($column, $parameter, $after = null, $indexed = false)
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::addTableColumn');
        }

        if (!PHPWS_DB::allowed($column)) {
            return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::addTableColumn', $column);
        }

        if ($this->isTableColumn($column)) {
            return false;
        }

        $sql = $GLOBALS['PHPWS_DB']['lib']->addColumn($table, $column, $parameter, $after);

        foreach ($sql as $val) {
            $result = PHPWS_DB::query($val);
            if (PHPWS_Error::isError($result)) {
                return $result;
            }
        }

        if ($indexed == true && DB_ALLOW_TABLE_INDEX) {
            $indexSql = "CREATE INDEX $column on $table($column)";
            $result = PHPWS_DB::query($indexSql);
            if (PHPWS_Error::isError($result)) {
                return $result;
            }
        }
        return true;
    }

    public function alterColumnType($column, $parameter)
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::alterColumnType');
        }

        if (!PHPWS_DB::allowed($column)) {
            return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::alterColumnType', $column);
        }

        if (!$this->isTableColumn($column)) {
            return false;
        }

        $sql = $GLOBALS['PHPWS_DB']['lib']->alterTableColumn($table, $column, $parameter);
        $this->begin();
        foreach ($sql as $val) {
            $result = $this->query($val);
            if (PHPWS_Error::isError($result)) {
                $this->rollback();
                return $result;
            }
        }
        $this->commit();
        return true;
    }

    public function dropTableColumn($column)
    {
        $table = $this->getTable(false);
        if (!$table) {
            return PHPWS_Error::get(PHPWS_DB_ERROR_TABLE, 'core', 'PHPWS_DB::dropColumn');
        }

        if (!PHPWS_DB::allowed($column)) {
            return PHPWS_Error::get(PHPWS_DB_BAD_COL_NAME, 'core', 'PHPWS_DB::dropTableColumn', $column);
        }

        if ($this->isTableColumn($column)) {
            $sql = "ALTER TABLE $table DROP $column";
            return PHPWS_DB::query($sql);
        } else {
            return true;
        }
    }

    public static function getDBType()
    {
        return $GLOBALS['PHPWS_DB']['connection']->phptype;
    }

    public static function disconnect()
    {
        if (empty($GLOBALS['PHPWS_DB']['dbs'])) {
            return;
        }

        foreach ($GLOBALS['PHPWS_DB']['dbs'] as $db) {
            if (!empty($db['connection'])) {
                $db['connection']->disconnect();
            }
        }
        unset($GLOBALS['PHPWS_DB']);
    }

    /**
     * Imports a SQL dump file into the database.
     * This function can not be called statically.
     */
    public static function importFile($filename, $report_errors = true)
    {
        if (!is_file($filename)) {
            return PHPWS_Error::get(PHPWS_FILE_NOT_FOUND, 'core', 'PHPWS_DB::importFile');
        }
        $data = file_get_contents($filename);
        return PHPWS_DB::import($data, $report_errors);
    }

    /**
     * Imports a SQL dump into the database.
     * This function can not be called statically.
     * @returns True if successful, false if not successful and report_errors = false or
     *               Error object if report_errors = true
     */
    public static function import($text, $report_errors = true)
    {
        PHPWS_DB::touchDB();

        // first_import makes sure at least one query was completed
        // successfully
        $first_import = false;

        $sqlArray = PHPWS_Text::sentence($text);
        $error = false;

        foreach ($sqlArray as $sqlRow) {
            if (empty($sqlRow) || preg_match("/^[^\w\d\s\\(\)]/i", $sqlRow)) {
                continue;
            }

            $sqlCommand[] = $sqlRow;

            if (preg_match("/;$/", $sqlRow)) {
                $query = implode(' ', $sqlCommand);
                $sqlCommand = array();

                if (!DB_ALLOW_TABLE_INDEX &&
                        preg_match('/^create index/i', $query)) {
                    continue;
                }

                PHPWS_DB::homogenize($query);

                $result = PHPWS_DB::query($query);

                if (DB::isError($result)) {
                    if ($report_errors) {
                        return $result;
                    } else {
                        PHPWS_Error::log($result);
                        $error = true;
                    }
                }
                $first_import = true;
            }
        }

        if (!$first_import) {
            if ($report_errors) {
                return PHPWS_Error::get(PHPWS_DB_IMPORT_FAILED, 'core', 'PHPWS_DB::import');
            } else {
                PHPWS_Error::log(PHPWS_DB_IMPORT_FAILED, 'core', 'PHPWS_DB::import');
                $error = true;
            }
        }

        if ($error) {
            return false;
        } else {
            return true;
        }
    }

    public static function homogenize(&$query)
    {
        $query_list = explode(',', $query);

        $from[] = '/int\(\d+\)/iU';
        $to[] = 'int';

        if (PHPWS_DB::getDBType() != 'mysql' &&
                PHPWS_DB::getDBType() != 'mysqli') {
            $from[] = '/mediumtext|longtext/i';
            $to[] = 'text';
        }

        foreach ($query_list as $command) {
            // Remove mysql specific call
            $command = str_ireplace('unsigned', '', $command);
            $command = preg_replace('/ default (\'\'|""|``)/i', '', $command);

            if (preg_match('/\s(smallint|int)\s/i', $command)) {
                if (!preg_match('/\snull/i', $command)) {
                    $command = str_ireplace(' int ', ' INT NOT NULL ', $command);
                    $command = str_ireplace(' smallint ', ' SMALLINT NOT NULL ', $command);
                }

                if (!preg_match('/\sdefault/i', $command)) {
                    $command = str_ireplace(' int ', ' INT DEFAULT 0 ', $command);
                    $command = str_ireplace(' smallint ', ' SMALLINT DEFAULT 0 ', $command);
                }

                $command = preg_replace('/ default \'(\d+)\'/Ui', ' DEFAULT \\1', $command);
            }



            $command = preg_replace($from, $to, $command);
            $newlist[] = $command;
        }

        $query = implode(',', $newlist);
        $GLOBALS['PHPWS_DB']['lib']->readyImport($query);
    }

    public function parsePearCol($info, $strip_name = false)
    {
        $setting = $GLOBALS['PHPWS_DB']['lib']->export($info);
        if (isset($info['flags'])) {
            if (stristr($info['flags'], 'multiple_key')) {
                if (DB_ALLOW_TABLE_INDEX) {
                    $column_info['index'] = 'CREATE INDEX ' . $info['name'] . ' on ' . $info['table']
                            . '(' . $info['name'] . ');';
                }
                $info['flags'] = str_replace(' multiple_key', '', $info['flags']);
            }

            $preFlag = array('/not_null/i', '/primary_key/i', '/default_(\w+)?/i', '/blob/i', '/%3a%3asmallint/i', '/unique_key/');
            $postFlag = array('NOT NULL', '', "DEFAULT '\\1'", '', '', 'UNIQUE KEY');

            $flags = ' ' . preg_replace($preFlag, $postFlag, $info['flags']);
        } else {
            $flags = null;
        }


        if ($strip_name == true) {
            $column_info['parameters'] = $setting . $flags;
        } else {
            $column_info['parameters'] = $info['name'] . " $setting" . $flags;
        }

        return $column_info;
    }

    public function parseColumns($columns)
    {
        static $primary_keys = array();
        //$table = $this->tables[0];
        $table = $this->getSourceTable();
        foreach ($columns as $info) {
            if (!is_array($info)) {
                continue;
            }

            if (stristr($info['flags'], 'primary_key')) {
                $primary_keys[$table][] = $info['name'];
            }


            $result = $this->parsePearCol($info);
            if (isset($result['index'])) {
                $column_info['index'][] = $result['index'];
            }

            $column_info['parameters'][] = $result['parameters'];
        }
        if (!empty($primary_keys[$table])) {
            $column_info['parameters'][] = sprintf('PRIMARY KEY (%s)', implode(',', $primary_keys[$table]));
        }

        return $column_info;
    }

    public function quote($text)
    {
        return $GLOBALS['PHPWS_DB']['connection']->quote($text);
    }

    public static function extractTableName($sql_value)
    {
        $temp = explode(' ', trim($sql_value));

        if (!is_array($temp)) {
            return null;
        }
        foreach ($temp as $whatever) {
            if (empty($whatever)) {
                continue;
            }
            $format[] = $whatever;
        }

        if (empty($format)) {
            return null;
        }

        switch (trim(strtolower($format[0]))) {
            case 'insert':
                if (stristr($format[1], 'into')) {
                    return preg_replace('/\(+.*$/', '', str_replace('`', '', $format[2]));
                } else {
                    return preg_replace('/\(+.*$/', '', str_replace('`', '', $format[1]));
                }

            case 'update':
                return preg_replace('/\(+.*$/', '', str_replace('`', '', $format[1]));

            case 'select':
            case 'show':
                return preg_replace('/\(+.*$/', '', str_replace('`', '', $format[3]));

            case 'drop':
            case 'alter':
                return preg_replace('/;/', '', str_replace('`', '', $format[2]));

            default:
                return preg_replace('/\W/', '', $format[2]);
        }
    }

// END FUNC extractTableName

    /**
     * Prepares a value for database writing or reading
     *
     * @author Matt McNaney <matt at NOSPAM dot tux dot appstate dot edu>
     * @param  mixed $value The value to prepare for the database.
     * @return mixed $value The prepared value
     * @access public
     */
     /*
    public function dbReady($value = null)
    {
        if (is_array($value) || is_object($value)) {
            return PHPWS_DB::dbReady(serialize($value));
        } elseif (is_string($value)) {
            return "'" . $GLOBALS['PHPWS_DB']['connection']->escape($value) . "'";
        } elseif (is_null($value)) {
            return 'NULL';
        } elseif (is_bool($value)) {
            return ($value ? 1 : 0);
        } else {
            return $value;
        }
    }
    */

// END FUNC dbReady()

    /**
     * Adds module title and class name to the load_class variable.
     * This list is called on the successful query of a loadObject or
     * getObjects. The list of files is erased as the files would not
     * need to be required again.
     */
    public function loadClass($module, $file)
    {
        $this->load_class[] = array($module, $file);
    }

    /**
     * Requires the classes, if any, in the load_class variable
     */
    public function requireClasses()
    {
        if ($this->load_class && is_array($this->load_class)) {
            foreach ($this->load_class as $files) {
                if (!is_array($files)) {
                    continue;
                }
                PHPWS_Core::initModClass($files[0], $files[1]);
            }
            $this->load_class = null;
        }
    }

    /**
     * @author Matt McNaney <mcnaney at gmail dot com>
     * @param  object $object        Object variable filled with result.
     * @param  object $require_where If true, require a where parameter or
     *                               have the id set
     * @return mixed                 Returns true if object properly populated and false otherwise
     *                               Returns error object if something goes wrong
     * @access public
     */
    public function loadObject($object, $require_where = true)
    {
        if (!is_object($object)) {
            return PHPWS_Error::get(PHPWS_DB_NOT_OBJECT, 'core', 'PHPWS_DB::loadObject');
        }

        if ($require_where && empty($object->id) && empty($this->where)) {
            return PHPWS_Error::get(PHPWS_DB_NO_ID, 'core', 'PHPWS_DB::loadObject', get_class($object));
        }

        if ($require_where && empty($this->where)) {
            $this->addWhere('id', $object->id);
        }

        $variables = $this->select('row');

        if (PHPWS_Error::isError($variables)) {
            return $variables;
        } elseif (empty($variables)) {
            return false;
        }

        return PHPWS_Core::plugObject($object, $variables);
    }

// END FUNC loadObject

    /**
     * Creates an array of objects constructed from the submitted
     * class name.
     *
     * Use this function instead of select() to get an array of objects.
     * Note that your class variables and column names MUST match exactly.
     * Unmatched pairs will be ignored.
     *
     * --- Any extra parameters after class_name are piped into ---
     * --- a class method called postPlug. If the function    ---
     * --- does not exist, nothing happens. Previously, the     ---
     * --- the variables were put into the constructor.         ---
     * Example:
     * $db->getObjects('Class_Name', 'foo');
     * class Class_Name {
     * function postPlug($extra_param) {
     * } // end constuctor
     * } //end class
     *
     * @author Matthew McNaney <mcnaney at gmail dot com>
     * @param string $class_name Name of class used in object
     * @return array $items      Array of objects
     * @access public
     */
    public function getObjects($class_name)
    {
        $items = null;
        $result = $this->select();

        if (empty($result)) {
            return null;
        }

        if (PHPWS_Error::isError($result) || !isset($result)) {
            return $result;
        }

        $this->requireClasses();

        if (!class_exists($class_name)) {
            return PHPWS_Error::get(PHPWS_CLASS_NOT_EXIST, 'core', 'PHPWS_DB::getObjects', $class_name);
        }

        $num_args = func_num_args();
        if ($num_args > 1) {
            $args = func_get_args();
            array_shift($args);
        } else {
            $args = null;
        }

        foreach ($result as $indexby => $itemResult) {
            $genClass = new $class_name;

            if (isset($itemResult[0]) && is_array($itemResult[0])) {
                foreach ($itemResult as $key => $sub) {
                    $genClass = new $class_name;
                    PHPWS_Core::plugObject($genClass, $sub, $args);
                    $items[$indexby][] = $genClass;
                }
            } else {
                PHPWS_Core::plugObject($genClass, $itemResult, $args);
                $items[$indexby] = $genClass;
            }
        }

        return $items;
    }

    public function saveObject($object, $stripChar = false, $autodetect_id = true)
    {
        if (!is_object($object)) {
            return PHPWS_Error::get(PHPWS_WRONG_TYPE, 'core', 'PHPWS_DB::saveObject', _('Type') . ': ' . gettype($object));
        }

        $object_vars = get_object_vars($object);

        if (!is_array($object_vars)) {
            return PHPWS_Error::get(PHPWS_DB_NO_OBJ_VARS, 'core', 'PHPWS_DB::saveObject');
        }

        foreach ($object_vars as $column => $value) {
            if ($stripChar == true) {
                $column = substr($column, 1);
            }

            $isTblColumn = $this->isTableColumn($column);

            if (PHPWS_Error::isError($isTblColumn)) {
                throw new Exception('Could not determine if column ' . $column . ' is a valid column in this table. Check table ownership.');
            }

            if (!$isTblColumn) {
                continue;
            }

            if ($autodetect_id && ($column == 'id' && $value > 0)) {
                $this->addWhere('id', $value);
            }

            $this->addValue($column, $value);
        }

        if (isset($this->qwhere) || !empty($this->where)) {
            $result = $this->update();
        } else {
            $result = $this->insert($autodetect_id);

            if (is_numeric($result)) {
                if (array_key_exists('id', $object_vars)) {
                    $object->id = (int) $result;
                } elseif (array_key_exists('_id', $object_vars)) {
                    $object->_id = (int) $result;
                }
            }
        }

        $this->resetValues();

        return $result;
    }

    public static function allowed($value)
    {
        if (!is_string($value)) {
            return false;
        }

        $reserved = array('ADD', 'ALL', 'ALTER', 'ANALYZE', 'AND', 'AS', 'ASC', 'AUTO_INCREMENT', 'BDB',
            'BERKELEYDB', 'BETWEEN', 'BIGINT', 'BINARY', 'BLOB', 'BOTH', 'BTREE', 'BY', 'CASCADE',
            'CASE', 'CHANGE', 'CHAR', 'CHARACTER', 'COLLATE', 'COLUMN', 'COLUMNS', 'CONSTRAINT', 'CREATE',
            'CROSS', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', 'DATE',
            'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'DEC', 'DECIMAL', 'DEFAULT',
            'DELAYED', 'DELETE', 'DESC', 'DESCRIBE', 'DISTINCT', 'DISTINCTROW',
            'DOUBLE', 'DROP', 'ELSE', 'ENCLOSED', 'ERRORS', 'ESCAPED', 'EXISTS', 'EXPLAIN', 'false', 'FIELDS',
            'FLOAT', 'FOR', 'FOREIGN', 'FROM', 'FULLTEXT', 'FUNCTION', 'GEOMETRY', 'GRANT', 'GROUP',
            'HASH', 'HAVING', 'HELP', 'HIGH_PRIORITY', 'HOUR_MINUTE', 'HOUR_SECOND',
            'IF', 'IGNORE', 'IN', 'INDEX', 'INFILE', 'INNER', 'INNODB', 'INSERT', 'INT',
            'INTEGER', 'INTERVAL', 'INTO', 'IS', 'JOIN', 'KEY', 'KEYS', 'KILL', 'LEADING',
            'LEFT', 'LIKE', 'LIMIT', 'LINES', 'LOAD', 'LOCK', 'LONG', 'LONGBLOB', 'LONGTEXT',
            'LOW_PRIORITY', 'MASTER_SERVER_ID', 'MATCH', 'MEDIUMBLOB', 'MEDIUMINT', 'MEDIUMTEXT',
            'MIDDLEINT', 'MINUTE_SECOND', 'MRG_MYISAM', 'NATURAL', 'NOT', 'NULL', 'NUMERIC', 'ON', 'OPTIMIZE',
            'OPTION', 'OPTIONALLY', 'OR', 'ORDER', 'OUTER', 'OUTFILE', 'PRECISION', 'PRIMARY', 'PRIVILEGES',
            'PROCEDURE', 'PURGE', 'READ', 'REAL', 'REFERENCES', 'REGEXP', 'RELEASE', 'RENAME', 'REPLACE', 'REQUIRE',
            'RESTRICT', 'RETURNS', 'REVOKE', 'RIGHT', 'RLIKE', 'RTREE', 'SELECT', 'SET', 'SHOW',
            'SMALLINT', 'SONAME', 'SPATIAL', 'SQL_BIG_RESULT', 'SQL_CALC_FOUND_ROWS', 'SQL_SMALL_RESULT',
            'SSL', 'STARTING', 'STRAIGHT_JOIN', 'STRIPED', 'TABLE', 'TABLES', 'TERMINATED', 'THEN', 'TINYBLOB',
            'TINYINT', 'TINYTEXT', 'TO', 'TRAILING', 'true', 'TYPES', 'UNION', 'UNIQUE', 'UNLOCK', 'UNSIGNED',
            'UPDATE', 'USAGE', 'USE', 'USER_RESOURCES', 'USING', 'VALUES', 'VARBINARY', 'VARCHAR', 'VARYING',
            'WARNINGS', 'WHEN', 'WHERE', 'WITH', 'WRITE', 'XOR', 'YEAR_MONTH', 'ZEROFILL');

        if (in_array(strtoupper($value), $reserved)) {
            return false;
        }

        if (preg_match('/[^\w\*\.]/', $value)) {
            return false;
        }

        return true;
    }

    /**
     * Crutch function from old database
     */
    public static function sqlFriendlyName($name)
    {
        if (!PHPWS_DB::allowed($name)) {
            return false;
        }

        return preg_replace('/\W/', '', $name);
    }

    public function updateSequenceTable()
    {
        $this->addColumn('id', 'max');

        $max_id = $this->select('one');

        if (PHPWS_Error::isError($max_id)) {
            return $max_id;
        }

        if ($max_id > 0) {
            $seq_table = $this->getTable(false) . '_seq';
            if (!$this->isTable($seq_table)) {
                $table = $this->addPrefix($this->getTable(false));
                $GLOBALS['PHPWS_DB']['connection']->nextId($table);
            }

            $seq = new PHPWS_DB($seq_table);
            $result = $seq->select('one');
            if (PHPWS_Error::logIfError($result)) {
                return false;
            }

            $seq->addValue('id', $max_id);
            if (!$result) {
                return $seq->insert(false);
            } else {
                return $seq->update();
            }
        }

        return true;
    }

    public static function addPrefix($table)
    {
        if (isset($GLOBALS['PHPWS_DB']['tbl_prefix'])) {
            return $GLOBALS['PHPWS_DB']['tbl_prefix'] . $table;
        }
        return $table;
    }

    public static function getPrefix()
    {
        if (isset($GLOBALS['PHPWS_DB']['tbl_prefix'])) {
            return $GLOBALS['PHPWS_DB']['tbl_prefix'];
        }
        return null;
    }

    /**
     * @author Matthew McNaney
     * @author Hilmar
     */
    public static function prefixQuery($sql)
    {
        if (!$GLOBALS['PHPWS_DB']['tbl_prefix']) {
            return $sql;
        }
        $tables = PHPWS_DB::pullTables($sql);

        if (empty($tables)) {
            return $sql;
        }

        foreach ($tables as $tbl) {
            $tbl = trim($tbl);
            $sql = PHPWS_DB::prefixVary($sql, $tbl);
        }
        return $sql;
    }

    /**
     * Prefix tablenames, but not within 'quoted values', called from prefixQuery
     * @author Hilmar
     */
    public static function prefixVary($sql, $tbl)
    {
        $repl = true;
        $ar = explode("'", $sql);

        foreach ($ar as $v) {
            if ($repl) {
                $subsql[] = preg_replace("/([\s\W])$tbl(\W)|([\s\W])$tbl$/", '$1${3}' . $GLOBALS['PHPWS_DB']['tbl_prefix'] . $tbl . '$2', $v);
                $repl = false;
            } else {
                $subsql[] = $v;
                if (substr($v, -1, 1) == "\\")
                    continue;
                $repl = true;
            }
        }
        $sql = implode('\'', $subsql);

        return $sql;
    }

    public static function pullTables($sql)
    {
        $sql = preg_replace('/ {2,}/', ' ', trim($sql));
        $sql = preg_replace('/[\n\r]/', ' ', $sql);
        $command = substr($sql, 0, strpos($sql, ' '));

        $matches = null;
        $tables = array();

        switch (strtolower($command)) {
            case 'alter':
                if (!preg_match('/alter table/i', $sql)) {
                    return false;
                }
                $aQuery = explode(' ', preg_replace('/[^\w\s]/', '', $sql));
                $tables[] = $aQuery[2];
                break;

            case 'create':
                if (preg_match('/^create (unique )?index/i', $sql)) {
                    $start = stripos($sql, ' on ') + 4;
                    $para = stripos($sql, '(');
                    $length = $para - $start;
                    $table = substr($sql, $start, $length);
                } else {
                    $aTable = explode(' ', $sql);
                    $table = $aTable[2];
                }
                $tables[] = trim(preg_replace('/\W/', '', $table));

                // Find any tables used in foreign key contstraints
                if (preg_match_all('/references (\S*)\s*\(\S*\)/i', $sql, $matches)) {
                    foreach ($matches[1] as $match) {
                        $tables[] = $match;
                    }
                }
                break;

            case 'delete':
                $start = stripos($sql, 'from') + 4;
                $end = strlen($sql) - $start;
                $table = substr($sql, $start, $end);
                $table = preg_replace('/where.*/i', '', $table);
                if (preg_match('/using/i', $table)) {
                    $table = preg_replace('/[^\w\s,]/', '', $table);
                    $table = preg_replace('/\w+ using/iU', '', $table);
                    return explode(',', preg_replace('/[^\w,]/', '', $table));
                }
                $tables[] = preg_replace('/\W/', '', $table);
                break;

            case 'drop':
                $start = stripos($sql, 'on') + 2;
                $length = strlen($sql) - $start;
                if (preg_match('/^drop index/i', $sql)) {
                    $table = substr($sql, $start, $length);
                    $tables[] = preg_replace('/[^\w,]/', '', $table);
                } else {
                    $table = preg_replace('/drop |table |if exists/i', '', $sql);
                    return explode(',', preg_replace('/[^\w,]/', '', $table));
                }
                break;

            case 'insert':
                $table = preg_replace('/insert |into | values|\(.*\)/i', '', $sql);
                $tables[] = preg_replace('/\W/', '', $table);
                break;

            case 'select':
                $start = stripos($sql, 'from') + 4;
                $table = substr($sql, $start, strlen($sql) - $start);

                if ($where = stripos($table, ' where ')) {
                    $table = substr($table, 0, $where);
                }

                if ($order = stripos($table, ' order by')) {
                    $table = substr($table, 0, $order);
                }

                if ($group = stripos($table, ' group by')) {
                    $table = substr($table, 0, $group);
                }

                if ($having = stripos($table, ' having ')) {
                    $table = substr($table, 0, $having);
                }

                if ($limit = stripos($table, ' limit ')) {
                    $table = substr($table, 0, $limit);
                }

                $table = str_ireplace(' join ', ' ', $table);
                $table = str_ireplace(' right ', ' ', $table);
                $table = str_ireplace(' left ', ' ', $table);
                $table = str_ireplace(' inner ', ' ', $table);
                $table = str_ireplace(' outer ', ' ', $table);
                $table = str_ireplace(' on ', ' ', $table);
                $table = str_ireplace(' and ', ' ', $table);
                $table = str_ireplace(' or ', ' ', $table);
                $table = str_ireplace(' not ', ' ', $table);
                $table = str_ireplace('=', ' ', $table);
                $table = str_ireplace(',', ' ', $table);
                $table = preg_replace('/\w+\.\w+/', ' ', $table);
                $table = preg_replace('/(as \w+)/i', '', $table);
                $table = preg_replace('/ \d+$| \d+ /', ' ', $table);
                $table = preg_replace('/\'.*\'/', ' ', trim($table));
                $table = preg_replace('/ {2,}/', ' ', trim($table));
                $tables = explode(' ', $table);

                return $tables;

            case 'update':
                $aTable = explode(' ', $sql);
                $tables[] = preg_replace('/\W/', '', $aTable[1]);
                break;

            case 'lock':
                $sql = preg_replace('/lock tables/i', '', $sql);
                $aTable = explode(',', $sql);

                foreach ($aTable as $tbl) {
                    $tables[] = substr($tbl, 0, strpos(trim($tbl) + 1, ' '));
                }
                break;
        }

        return $tables;
    }

    public function setLock($table, $status = 'write')
    {
        if (!is_string($table) || !is_string($status)) {
            return false;
        }

        $status = strtolower($status);

        if ($status != 'read' && $status != 'write') {
            return false;
        }

        if (in_array($table, $this->tables)) {
            $this->locked[] = array('table' => $table,
                'status' => $status);
        }
    }

    public function lockTables()
    {
        if (!ALLOW_TABLE_LOCKS) {
            return true;
        }

        if (empty($this->locked)) {
            return false;
        }

        $query = $GLOBALS['PHPWS_DB']['lib']->lockTables($this->locked);
        return $this->query($query);
    }

    public function unlockTables()
    {
        if (!ALLOW_TABLE_LOCKS) {
            return true;
        }

        $query = $GLOBALS['PHPWS_DB']['lib']->unlockTables();
        return $this->query($query);
    }

    /**
     * Move row in a table based on a column designating the current order
     * direction == 1 means INCREASE order by one
     * direction == -1 means DECREASE order by one
     * @param string  order_column Table column that contains the order of the entries
     * @param string  id_column    Name of the id_column
     * @param integer id           Id of current row
     * @param integer direction    Direction to move the row
     */
    public function moveRow($order_column, $id_column, $id, $direction = 1)
    {
        if (!($direction == 1 || $direction == -1)) {
            if (strtolower($direction) == 'down') {
                $direction = 1;
            } elseif (strtolower($direction) == 'up') {
                $direction = -1;
            } else {
                return false;
            }
        }

        $total_rows = $this->count();
        if ($total_rows < 2) {
            return;
        }

        $db = clone($this);
        $db->reset();
        $db->addWhere($id_column, $id);
        $db->addColumn($order_column);
        $current_order = $db->select('one');

        if ($current_order == 1 && $direction == -1) {
            // moving up when current item is at top of list
            // need to shift all other items down and pop this on the end
            PHPWS_DB::begin();

            if (PHPWS_Error::logIfError($this->reduceColumn($order_column))) {
                PHPWS_DB::rollback();
                return false;
            }
            $db->reset();
            $db->addWhere($id_column, $id);
            $db->addValue($order_column, $total_rows);
            if (PHPWS_Error::logIfError($db->update())) {
                PHPWS_DB::rollback();
                return false;
            }
            PHPWS_DB::commit();
            unset($db);
            return true;
        } elseif ($current_order == $total_rows && $direction == 1) {
            // moving down when current item is at bottom/end of list
            // need to shift all other items up and shift this on the beginning
            PHPWS_DB::begin();
            if (PHPWS_Error::logIfError($this->incrementColumn($order_column))) {
                PHPWS_DB::rollback();
                return false;
            }
            $db->reset();
            $db->addWhere($id_column, $id);
            $db->addValue($order_column, 1);
            if (PHPWS_Error::logIfError($db->update())) {
                PHPWS_DB::rollback();
                return false;
            }
            PHPWS_DB::commit();
            unset($db);
            return true;
        } else {
            PHPWS_DB::begin();
            $db = clone($this);
            $db->addWhere($order_column, $current_order + $direction);
            $db->addValue($order_column, $current_order);
            if (PHPWS_Error::logIfError($db->update())) {
                PHPWS_DB::rollback();
                return false;
            }

            $db = clone($this);
            $db->addWhere($id_column, $id);
            $db->addValue($order_column, $current_order + $direction);
            if (PHPWS_Error::logIfError($db->update())) {
                PHPWS_DB::rollback();
                return false;
            }
            unset($db);
            return true;
        }
    }

    public function setSubselectAs($ssa)
    {
        if ($this->allowed($ssa)) {
            $this->subselect_as = $ssa;
        }
    }
}