adodb.php

PhpWiki是sourceforge的一个开源项目

        $dbh = &$this->_dbh;
        extract($this->_table_names);

        if ($reversed)
            list($have, $want) = array('linkee', 'linker');
        else
            list($have, $want) = array('linker', 'linkee');
        $qpagename = $dbh->qstr($pagename);
        $qlink = $dbh->qstr($link);
        $row = $dbh->GetRow("SELECT IF($want.pagename,1,0)"
                                . " FROM $link_tbl, $page_tbl linker, $page_tbl linkee, $nonempty_tbl"
                                . " WHERE linkfrom=linker.id AND linkto=linkee.id"
                                . " AND $have.pagename=$qpagename"
                                . " AND $want.pagename=$qlink"
                                . "LIMIT 1");
        return $row[0];
    }

    function get_all_pages($include_empty=false, $sortby=false, $limit=false, $exclude='') {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        $orderby = $this->sortby($sortby, 'db');
        if ($orderby) $orderby = ' ORDER BY ' . $orderby;
        if ($exclude) // array of pagenames
            $exclude = " AND $page_tbl.pagename NOT IN ".$this->_sql_set($exclude);
        else 
            $exclude='';

        //$dbh->SetFetchMode(ADODB_FETCH_ASSOC);
        if (strstr($orderby, 'mtime ')) { // was ' mtime'
            if ($include_empty) {
                $sql = "SELECT "
                    . $this->page_tbl_fields
                    ." FROM $page_tbl, $recent_tbl, $version_tbl"
                    . " WHERE $page_tbl.id=$recent_tbl.id"
                    . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
                    . $exclude
                    . $orderby;
            }
            else {
                $sql = "SELECT "
                    . $this->page_tbl_fields
                    . " FROM $nonempty_tbl, $page_tbl, $recent_tbl, $version_tbl"
                    . " WHERE $nonempty_tbl.id=$page_tbl.id"
                    . " AND $page_tbl.id=$recent_tbl.id"
                    . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
                    . $exclude
                    . $orderby;
            }
        } else {
            if ($include_empty) {
                $sql = "SELECT "
                    . $this->page_tbl_fields
                    . " FROM $page_tbl"
                    . ($exclude ? " WHERE $exclude" : '')
                    . $orderby;
            } else {
                $sql = "SELECT "
                    . $this->page_tbl_fields
                    . " FROM $nonempty_tbl, $page_tbl"
                    . " WHERE $nonempty_tbl.id=$page_tbl.id"
                    . $exclude
                    . $orderby;
            }
        }
        if ($limit) {
            // extract from,count from limit
            list($offset,$count) = $this->limit($limit);
            $result = $dbh->SelectLimit($sql, $count, $offset);
        } else {
            $result = $dbh->Execute($sql);
        }
        //$dbh->SetFetchMode(ADODB_FETCH_NUM);
        return new WikiDB_backend_ADODB_iter($this, $result, $this->page_tbl_field_list);
    }
        
    /**
     * Title search.
     */
    function text_search($search, $fullsearch=false) {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        
        $table = "$nonempty_tbl, $page_tbl";
        $join_clause = "$nonempty_tbl.id=$page_tbl.id";
        $fields = $this->page_tbl_fields;
        $field_list = $this->page_tbl_field_list;
        $searchobj = new WikiDB_backend_ADODB_search($search, $dbh);
        
        if ($fullsearch) {
            $table .= ", $recent_tbl";
            $join_clause .= " AND $page_tbl.id=$recent_tbl.id";

            $table .= ", $version_tbl";
            $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";

            $fields .= ",$page_tbl.pagedata as pagedata," . $this->version_tbl_fields;
            $field_list = array_merge($field_list, array('pagedata'), $this->version_tbl_field_list);
            $callback = new WikiMethodCb($searchobj, "_fulltext_match_clause");
        } else {
            $callback = new WikiMethodCb($searchobj, "_pagename_match_clause");
        }
        
        $search_clause = $search->makeSqlClauseObj($callback);
        $result = $dbh->Execute("SELECT $fields FROM $table"
                                . " WHERE $join_clause"
                                . " AND ($search_clause)"
                                . " ORDER BY pagename");
        return new WikiDB_backend_ADODB_iter($this, $result, $field_list);
    }
    /*
    function _sql_match_clause($word) {
        //not sure if we need this.  ADODB may do it for us
        $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);  

        // (we need it for at least % and _ --- they're the wildcard characters
        //  for the LIKE operator, and we need to quote them if we're searching
        //  for literal '%'s or '_'s.  --- I'm not sure about \, but it seems to
        //  work as is.
        $word = $this->_dbh->qstr("%".strtolower($word)."%");
        $page_tbl = $this->_table_names['page_tbl'];
        return "LOWER($page_tbl.pagename) LIKE $word";
    }
    function _fullsearch_sql_match_clause($word) {
        $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);  //not sure if we need this
        // (see above)
        $word = $this->_dbh->qstr("%".strtolower($word)."%");
        $page_tbl = $this->_table_names['page_tbl'];
        return "LOWER($page_tbl.pagename) LIKE $word OR content LIKE $word";
    }
    */

    /*
     * TODO: efficiently handle wildcards exclusion: exclude=Php* => 'Php%', 
     *       not sets. See above, but the above methods find too much. 
     * This is only for already resolved wildcards:
     * " WHERE $page_tbl.pagename NOT IN ".$this->_sql_set(array('page1','page2'));
     */
    function _sql_set(&$pagenames) {
        $s = '(';
        foreach ($pagenames as $p) {
            $s .= ($this->_dbh->qstr($p).",");
        }
        return substr($s,0,-1).")";
    }

    /**
     * Find highest or lowest hit counts.
     */
    function most_popular($limit=20, $sortby='-hits') {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        $order = "DESC";
        if ($limit < 0){ 
            $order = "ASC"; 
            $limit = -$limit;
            $where = "";
        } else {
            $where = " AND hits > 0";
        }
        if ($sortby != '-hits') {
            if ($order = $this->sortby($sortby, 'db'))  $orderby = " ORDER BY " . $order;
            else $orderby = "";
        } else
            $orderby = " ORDER BY hits $order";
        $sql = "SELECT " 
            . $this->page_tbl_fields
            . " FROM $nonempty_tbl, $page_tbl"
            . " WHERE $nonempty_tbl.id=$page_tbl.id"
            . $where
            . $orderby;
        if ($limit) {
            // extract from,count from limit
            list($offset,$count) = $this->limit($limit);
            $result = $dbh->SelectLimit($sql, $count, $offset);
        } else {
            $result = $dbh->Execute($sql);
        }
        return new WikiDB_backend_ADODB_iter($this, $result, $this->page_tbl_field_list);
    }

    /**
     * Find recent changes.
     */
    function most_recent($params) {
        $limit = 0;
        $since = 0;
        $include_minor_revisions = false;
        $exclude_major_revisions = false;
        $include_all_revisions = false;
        extract($params);

        $dbh = &$this->_dbh;
        extract($this->_table_names);

        $pick = array();
        if ($since)
            $pick[] = "mtime >= $since";
        
        if ($include_all_revisions) {
            // Include all revisions of each page.
            $table = "$page_tbl, $version_tbl";
            $join_clause = "$page_tbl.id=$version_tbl.id";

            if ($exclude_major_revisions) {
                // Include only minor revisions
                $pick[] = "minor_edit <> 0";
            }
            elseif (!$include_minor_revisions) {
                // Include only major revisions
                $pick[] = "minor_edit = 0";
            }
        }
        else {
            $table = "$page_tbl, $recent_tbl";
            $join_clause = "$page_tbl.id=$recent_tbl.id";
            $table .= ", $version_tbl";
            $join_clause .= " AND $version_tbl.id=$page_tbl.id";
                
            if ($exclude_major_revisions) {
                // Include only most recent minor revision
                $pick[] = 'version=latestminor';
            }
            elseif (!$include_minor_revisions) {
                // Include only most recent major revision
                $pick[] = 'version=latestmajor';
            }
            else {
                // Include only the latest revision (whether major or minor).
                $pick[] ='version=latestversion';
            }
        }
        $order = "DESC";
        if($limit < 0){
            $order = "ASC";
            $limit = -$limit;
        }
        $where_clause = $join_clause;
        if ($pick)
            $where_clause .= " AND " . join(" AND ", $pick);
        $sql = "SELECT "
            . $this->page_tbl_fields . ", " . $this->version_tbl_fields
            . " FROM $table"
            . " WHERE $where_clause"
            . " ORDER BY mtime $order";
        // FIXME: use SQL_BUFFER_RESULT for mysql?
        if ($limit) {
            // extract from,count from limit
            list($offset,$count) = $this->limit($limit);
            $result = $dbh->SelectLimit($sql, $count, $offset);
        } else {
            $result = $dbh->Execute($sql);
        }
        //$result->fields['version'] = $result->fields[6];
        return new WikiDB_backend_ADODB_iter($this, $result, 
            array_merge($this->page_tbl_field_list, $this->version_tbl_field_list));
    }

    /**
     * Find referenced empty pages.
     */
    function wanted_pages($exclude_from='', $exclude='', $sortby=false, $limit=false) {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        if ($orderby = $this->sortby($sortby, 'db', array('pagename','wantedfrom')))
            $orderby = 'ORDER BY ' . $orderby;
            
        if ($exclude_from) // array of pagenames
            $exclude_from = " AND linked.pagename NOT IN ".$this->_sql_set($exclude_from);
        if ($exclude) // array of pagenames
            $exclude = " AND $page_tbl.pagename NOT IN ".$this->_sql_set($exclude);

        /* 
         all empty pages, independent of linkstatus:
           select pagename as empty from page left join nonempty using(id) where isnull(nonempty.id);
         only all empty pages, which have a linkto:
           select page.pagename, linked.pagename as wantedfrom from link, page as linked 
             left join page on(link.linkto=page.id) left join nonempty on(link.linkto=nonempty.id) 
             where isnull(nonempty.id) and linked.id=link.linkfrom;  
        */
        $sql = "SELECT $page_tbl.pagename,linked.pagename as wantedfrom"
            . " FROM $link_tbl,$page_tbl as linked "
            . " LEFT JOIN $page_tbl ON($link_tbl.linkto=$page_tbl.id)"
            . " LEFT JOIN $nonempty_tbl ON($link_tbl.linkto=$nonempty_tbl.id)" 
            . " WHERE ISNULL($nonempty_tbl.id) AND linked.id=$link_tbl.linkfrom"
            . $exclude_from
            . $exclude
            . $orderby;
        if ($limit) {
            // extract from,count from limit
            list($offset,$count) = $this->limit($limit);
            $result = $dbh->SelectLimit($sql, $count, $offset);
        } else {
            $result = $dbh->Execute($sql);
        }
        return new WikiDB_backend_ADODB_iter($this, $result, array('pagename','wantedfrom'));
    }

    /**
     * Rename page in the database.
     */
    function rename_page($pagename, $to) {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        
        $this->lock(array('page','version','recent','nonempty','link'));
        if ( ($id = $this->_get_pageid($pagename, false)) ) {
            if ($new = $this->_get_pageid($to, false)) {
                // Cludge Alert!
                // This page does not exist (already verified before), but exists in the page table.
                // So we delete this page.
                $dbh->query("DELETE FROM $page_tbl WHERE id=$new");
                $dbh->query("DELETE FROM $version_tbl WHERE id=$new");
                $dbh->query("DELETE FROM $recent_tbl WHERE id=$new");
                $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$new");
                // We have to fix all referring tables to the old id
                $dbh->query("UPDATE $link_tbl SET linkfrom=$id WHERE linkfrom=$new");
                $dbh->query("UPDATE $link_tbl SET linkto=$id WHERE linkto=$new");
            }
            $dbh->query(sprintf("UPDATE $page_tbl SET pagename=%s WHERE id=$id",
                                $dbh->qstr($to)));
        }
        $this->unlock(array('page'));
        return $id;
    }

    function _update_recent_table($pageid = false) {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        extract($this->_expressions);

        $pageid = (int)$pageid;

        // optimize: mysql can do this with one REPLACE INTO.
        $backend_type = $this->backendType();