query.php

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· PHP 代码 · 共 1,920 行 · 第 1/5 页

            if (DB::isError($res)) {
                //$this->_errorSet($res->getMessage());
                // i think we only need to log here, since this method is never used
                // directly for the user's functionality, which means if it fails it
                // is most probably an appl error
                $this->_errorLog($res->getUserInfo());
                return false;
            }
            foreach ($res as $key=>$val) {
                $res[$key]['name'] = $val['COLUMN_NAME'];
            }
        } else {
            if (!is_object($this->db)) {
                return false;
            }
            $res = $this->db->tableinfo($table);
            if (DB::isError($res)) {
                $this->_errorSet($res->getUserInfo());
                return false;
            }
        }

        $ret = array();
        foreach ($res as $key => $val) {
            $ret[$val['name']] = $val;
        }
        $this->_metadata[$table] = $ret;
        return $ret;
    }



    //
    //  methods for building the query
    //

    // }}}
    // {{{ _buildFrom()

    /**
     * build the from string
     *
     * @access     private
     * @return     string  the string added after FROM
     */
    function _buildFrom()
    {
        $from = $this->table;
        $join = $this->getJoin();

        if (!$join) {  // no join set
            return $from;
        }
        // handle the standard join thingy
        if (isset($join['default']) && count($join['default'])) {
            $from .= ','.implode(',',array_keys($join['default']));
        }

        // handle left/right joins
        foreach (array('left', 'right') as $joinType) {
            if (isset($join[$joinType]) && count($join[$joinType])) {
                foreach($join[$joinType] as $table => $condition) {
                    // replace the _TABLENAME_COLUMNNAME by TABLENAME.COLUMNNAME
                    // since oracle doesnt work with the _TABLENAME_COLUMNNAME which i think is strange
// FIXXME i think this should become deprecated since the setWhere should not be used like this: '_table_column' but 'table.column'
                    $regExp = '/_('.$table.')_([^\s]+)/';
                    $where = preg_replace($regExp, '$1.$2', $condition);

                    // add the table name before any column that has no table prefix
                    // since this might cause "unambiguous column" errors
                    if ($meta = $this->metadata()) {
                        foreach ($meta as $aCol=>$x) {
                            // this covers the LIKE,IN stuff: 'name LIKE "%you%"'  'id IN (2,3,4,5)'
                            $condition = preg_replace('/\s'.$aCol.'\s/', " {$this->table}.$aCol ", $condition);
                            // replace also the column names which are behind a '='
                            // and do this also if the aCol is at the end of the where clause
                            // that's what the $ is for
                            $condition = preg_replace('/=\s*'.$aCol.'(\s|$)/', "={$this->table}.$aCol ", $condition);
                            // replace if colName is first and possibly also if at the beginning of the where-string
                            $condition = preg_replace('/(^\s*|\s+)'.$aCol.'\s*=/', "$1{$this->table}.$aCol=", $condition);
                        }
                    }
                    $from .= ' '.strtoupper($joinType).' JOIN '.$table.' ON '.$condition;
                }
            }
        }
        return $from;
    }

    // }}}
    // {{{ getTableShortName()

    /**
     *   this method gets the short name for a table
     *
     *   get the short name for a table, this is needed to properly build the
     *   'AS' parts in the select query
     * @param  string  the real table name
     * @return string  the table's short name
     */
    function getTableShortName($table)
    {
        $tableSpec = $this->getTableSpec(false);
        if (isset($tableSpec[$table]['shortName']) && $tableSpec[$table]['shortName']) {
//print "$table ... ".$tableSpec[$table]['shortName'].'<br>';
            return $tableSpec[$table]['shortName'];
        }

        $possibleTableShortName = preg_replace($this->_tableNameToShortNamePreg, '', $table);
//print "$table ... $possibleTableShortName<br>";
        return $possibleTableShortName;
    }

    // }}}
    // {{{ getTableSpec()

    /**
     * gets the tableSpec either indexed by the short name or the name
     * returns the array for the tables given as parameter or if no
     * parameter given for all tables that exist in the tableSpec
     *
     * @param      array   table names (not the short names!)
     * @param      boolean if true the table is returned indexed by the shortName
     *                       otherwise indexed by the name
     * @return     array   the tableSpec indexed
     */
    function getTableSpec($shortNameIndexed=true, $tables=array())
    {
        $newSpec = array();
        foreach ($this->tableSpec as $aSpec) {
            if (sizeof($tables)==0 || in_array($aSpec['name'],$tables)) {
                if ($shortNameIndexed) {
                    $newSpec[$aSpec['shortName']] = $aSpec;
                } else {
                    $newSpec[$aSpec['name']] = $aSpec;
                }
            }
        }
        return $newSpec;
    }

    // }}}
    // {{{ _buildSelect()

    /**
     *   build the 'SELECT <what> FROM ... 'for a select
     *
     * @version    2002/07/11
     * @access     public
     * @author     Wolfram Kriesing <[e-mail]>
     * @param      string      if given use this string
     * @return     string      the what-clause
     */
    function _buildSelect($what=null)
    {
        // what has preference, that means if what is set it is used
        // this is only because the methods like 'get' pass an individually built value, which
        // is supposed to be used, but usually it's generically build using the 'getSelect' values
        if (empty($what) && $this->getSelect()) {
            $what = $this->getSelect();
        }

        //
        // replace all the '*' by the real column names, and take care of the dontSelect-columns!
        //
        $dontSelect = $this->getDontSelect();
        $dontSelect = $dontSelect ? explode(',', $dontSelect) : array(); // make sure dontSelect is an array

        // here we will replace all the '*' and 'table.*' by all the columns that this table
        // contains. we do this so we can easily apply the 'dontSelect' values.
        // and so we can also handle queries like: 'SELECT *,count() FROM ' and 'SELECT table.*,x FROM ' too
        if (strpos($what, '*') !== false) {
            // subpattern 1 get all the table names, that are written like this: 'table.*' including '*'
            // for '*' the tablename will be ''
            preg_match_all('/([^,]*)(\.)?\*\s*(,|$)/U', $what, $res);
//print "$what ... ";print_r($res);print "<br>";
            $selectAllFromTables = array_unique($res[1]); // make the table names unique, so we do it all just once for each table
            $tables = array();
            if (in_array('', $selectAllFromTables)) { // was there a '*' ?
                // get all the tables that we need to process, depending on if joined or not
                $tables = $this->getJoin() ?
                                array_merge($this->getJoin('tables'), array($this->table)) : // get the joined tables and this->table
                                array($this->table);        // create an array with only this->table
            } else {
                $tables = $selectAllFromTables;
            }

            $cols = array();
            foreach ($tables as $aTable) {      // go thru all the tables and get all columns for each, and handle 'dontSelect'
                if ($meta = $this->metadata($aTable)) {
                    foreach ($meta as $colName => $x) {
                        // handle the dontSelect's
                        if (in_array($colName, $dontSelect) || in_array("$aTable.$colName", $dontSelect)) {
                            continue;
                        }

                        // build the AS clauses
                        // put " around them to enable use of reserved words, i.e. SELECT table.option as option FROM...
                        // and 'option' actually is a reserved word, at least in mysql
                        // put double quotes around them, since pgsql doesnt work with single quotes
                        // but don't do this for ibase because it doesn't work!
                        if ($aTable == $this->table) {
                            if ($this->db->phptype == 'ibase') {
                                $cols[$aTable][] = $this->table. '.' .$colName . ' AS '. $colName;
                            } else {
                                $cols[$aTable][] = $this->table. '.' .$colName . ' AS "'. $colName .'"';
                            }
                        } else {
                            $cols[$aTable][] = "$aTable.$colName AS \"_".$this->getTableShortName($aTable)."_$colName\"";
                        }
                    }
                }
            }

            // put the extracted select back in the $what
            // that means replace 'table.*' by the i.e. 'table.id AS _table_id'
            // or if it is the table of this class replace 'table.id AS id'
            if (in_array('', $selectAllFromTables)) {
                $allCols = array();
                foreach ($cols as $aTable) {
                    $allCols[] = implode(',', $aTable);
                }
                $what = preg_replace('/(^|,)\*($|,)/', '$1'.implode(',',$allCols).'$2', $what);
                // remove all the 'table.*' since we have selected all anyway (because there was a '*' in the select)
                $what = preg_replace('/[^,]*(\.)?\*\s*(,|$)/U', '', $what);
            } else {
                foreach ($cols as $tableName => $aTable) {
                    if (is_array($aTable) && sizeof($aTable)) {
                        // replace all the 'table.*' by their select of each column
                        $what = preg_replace('/(^|,)\s*'.$tableName.'\.\*\s*($|,)/', '$1'.implode(',',$aTable).'$2', $what);
                    }
                }
            }
        }

        if ($this->getJoin()) {
            // replace all 'column' by '$this->table.column' to prevent ambigious errors
            $metadata = $this->metadata();
            if (is_array($metadata)) {
                foreach ($metadata as $aCol => $x) {
                    // handle ',id as xid,MAX(id),id' etc.
// FIXXME do this better!!!
                    $what = preg_replace(  "/(^|,|\()(\s*)$aCol(\)|\s|,|as|$)/i",
                                            // $2 is actually just to keep the spaces, is not really
                                            // necessary, but this way the test works independent of this functionality here
                                            "$1$2{$this->table}.$aCol$3",
                                            $what);
                }
            }
            // replace all 'joinedTable.columnName' by '_joinedTable_columnName'
            // this actually only has an effect if there was no 'table.*' for 'table'
            // if that was there, then it has already been done before
            foreach ($this->getJoin('tables') as $aTable) {
                if ($meta = $this->metadata($aTable)) {
                    foreach ($meta as $aCol=>$x) {
                        // dont put the 'AS' behind it if there is already one
                        if (preg_match("/$aTable.$aCol\s*as/i",$what)) {
                            continue;
                        }
                        // this covers a ' table.colName ' surrounded by spaces, and replaces it by ' table.colName AS _table_colName'
                        $what = preg_replace('/\s'.$aTable.'.'.$aCol.'\s/', " $aTable.$aCol AS _".$this->getTableShortName($aTable)."_$aCol ", $what);
                        // replace also the column names which are behind a ','
                        // and do this also if the aCol is at the end that's what the $ is for
                        $what = preg_replace('/,\s*'.$aTable.'.'.$aCol.'(,|\s|$)/', ",$aTable.$aCol AS _".$this->getTableShortName($aTable)."_$aCol$1", $what);
                        // replace if colName is first and possibly also if at the beginning of the where-string
                        $what = preg_replace('/(^\s*|\s+)'.$aTable.'.'.$aCol.'\s*,/', "$1$aTable.$aCol AS _".$this->getTableShortName($aTable)."_$aCol,", $what);
                    }
                }
            }
        }
        return $what;
    }

    // }}}
    // {{{ _buildWhere()

    /**
     * Build WHERE clause
     *
     * @param  string $where WHERE clause
     * @return string $where WHERE clause after processing
     * @access private
     */
    function _buildWhere($where='')
    {
        $where = trim($where);
        $originalWhere = $this->getWhere();
        if ($originalWhere) {
            if (!empty($where)) {
                $where = $originalWhere.' AND '.$where;
            } else {
                $where = $originalWhere;
            }
        }
        $where = trim($where);

        if ($join = $this->getJoin()) {     // is join set?
            // only those where conditions in the default-join have to be added here
            // left-join conditions are added behind 'ON', the '_buildJoin()' does that
            if (isset($join['default']) && count($join['default'])) {
                // we have to add this join-where clause here
                // since at least in mysql a query like: select * from tableX JOIN tableY ON ...
                // doesnt work, may be that's even SQL-standard...
                if (!empty($where)) {
                    $where = implode(' AND ', $join['default']).' AND '.$where;
                } else {
                    $where = implode(' AND ', $join['default']);
                }
            }
            // replace the _TABLENAME_COLUMNNAME by TABLENAME.COLUMNNAME
            // since oracle doesnt work with the _TABLENAME_COLUMNNAME which i think is strange
// FIXXME i think this should become deprecated since the setWhere should not be used like this: '_table_column' but 'table.column'
            $regExp = '/_('.implode('|', $this->getJoin('tables')).')_([^\s]+)/';
            $where = preg_replace($regExp, '$1.$2', $where);
            // add the table name before any column that has no table prefix
            // since this might cause "unambigious column" errors
            if ($meta = $this->metadata()) {
                foreach ($meta as $aCol => $x) {
                    // this covers the LIKE,IN stuff: 'name LIKE "%you%"'  'id IN (2,3,4,5)'
                    $where = preg_replace('/\s'.$aCol.'\s/', " {$this->table}.$aCol ", $where);
                    // replace also the column names which are behind a '='
                    // and do this also if the aCol is at the end of the where clause
                    // that's what the $ is for
                    $where = preg_replace('/([=<>])\s*'.$aCol.'(\s|$)/', "$1{$this->table}.$aCol ", $where);
                    // replace if colName is first and possibly also if at the beginning of the where-string
                    $where = preg_replace('/(^\s*|\s+)'.$aCol.'\s*([=<>])/', "$1{$this->table}.$aCol$2", $where);
                }
            }
        }
        return $where;
    }

    // }}}
    // {{{ _buildOrder()

    /**
     *
     *
     * @version    2002/07/11
     * @access     public
     * @author     Wolfram Kriesing <[e-mail]>
     * @param
     * @return
     */
    function _buildOrder()
    {
        $order = $this->getOrder();
        // replace 'column' by '$this->table.column' if the column is defined for $this->table
        if ($meta = $this->metadata()) {
            foreach ($meta as $aCol=>$x) {
                $order = preg_replace('/(^\s*|\s+|,)'.$aCol.'\s*(,)?/U', "$1{$this->table}.$aCol$2", $order);
            }
        }
        return $order;
    }

    // }}}
    // {{{ _buildGroup()

    /**
     *   Build the group-clause, replace 'column' by 'table.column'.
     *
     * @access public
     * @param void
     * @return string the rendered group clause
     */
    function _buildGroup()
    {
        $group = $this->getGroup();
        // replace 'column' by '$this->table.column' if the column is defined for $this->table
        if ($meta = $this->metadata()) {
            foreach ($meta as $aCol => $x) {
                $group = preg_replace('/(^\s*|\s+|,)'.$aCol.'\s*(,)?/U', "$1{$this->table}.$aCol$2", $group);
            }
        }
        return $group;
    }

    // }}}
    // {{{ _buildHaving()

    /**
     *
     * @version    2003/06/0