pdo.php

PhpWiki是sourceforge的一个开源项目

                             . " $want.hits AS hits"
                             . " FROM $link_tbl, $page_tbl linker, $page_tbl linkee"
                             . (!$include_empty ? ", $nonempty_tbl" : '')
                             . " WHERE linkfrom=linker.id AND linkto=linkee.id"
                             . " AND $have.pagename=?"
                             . (!$include_empty ? " AND $nonempty_tbl.id=$want.id" : "")
                             //. " GROUP BY $want.id"
                             . $exclude
                             . $orderby
                             . $limit);
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        $sth->execute();
        $result = $sth->fetch(PDO_FETCH_BOTH);
        return new WikiDB_backend_PDO_iter($this, $result, $this->page_tbl_field_list);
    }

    /**
     * Find if a page links to another page
     */
    function exists_link($pagename, $link, $reversed=false) {
        $dbh = &$this->_dbh;
        extract($this->_table_names);

        if ($reversed)
            list($have, $want) = array('linkee', 'linker');
        else
            list($have, $want) = array('linker', 'linkee');
        $sth = $dbh->prepare("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=?"
                             . " AND $want.pagename=?"
                             . "LIMIT 1");
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        $sth->bindParam(2, $link, PDO_PARAM_STR, 100);
        $sth->execute();
        return $sth->fetchSingle();
    }

    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='';

        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);
            $sth = $dbh->prepare($sql . " LIMIT $offset, $count");
        } else {
            $sth = $dbh->prepare($sql);
        }
        $sth->execute();
        $result = $sth->fetch(PDO_FETCH_BOTH);
        return new WikiDB_backend_PDO_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_PDO_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);
        $sth = $dbh->prepare("SELECT $fields FROM $table"
                                . " WHERE $join_clause"
                                . " AND ($search_clause)"
                                . " ORDER BY pagename");
        $sth->execute();
        $result = $sth->fetch(PDO_FETCH_NUM);
        return new WikiDB_backend_PDO_iter($this, $result, $field_list);
    }

    /*
     * 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);
            $sth = $dbh->prepare($sql . " LIMIT $offset, $count");
        } else {
            $sth = $dbh->prepare($sql);
        }
        $sth->execute();
        $result = $sth->fetch(PDO_FETCH_NUM);
        return new WikiDB_backend_PDO_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";
        if ($limit) {
            list($offset,$count) = $this->limit($limit);
            $sth = $dbh->prepare($sql . " LIMIT $offset, $count");
        } else {
            $sth = $dbh->prepare($sql);
        }
        $sth->execute();
        $result = $sth->fetch(PDO_FETCH_NUM);
        return new WikiDB_backend_PDO_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);
            $sth = $dbh->prepare($sql . " LIMIT $offset, $count");
        } else {
            $sth = $dbh->prepare($sql);
        }
        $sth->execute();
        $result = $sth->fetch(PDO_FETCH_NUM);
        return new WikiDB_backend_PDO_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");
            }
            $sth = $dbh->prepare("UPDATE $page_tbl SET pagename=? WHERE id=?");
            $sth->bindParam(1, $to, PDO_PARAM_STR, 100);
            $sth->bindParam(2, $id, PDO_PARAM_INT);
            $sth->execute();
        }
        $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();
        if (substr($backend_type,0,5) == 'mysql') {
            $sth = $dbh->prepare("REPLACE INTO $recent_tbl"
                                 . " (id, latestversion, latestmajor, latestminor)"
                                 . " SELECT id, $maxversion, $maxmajor, $maxminor"
                                 . " FROM $version_tbl"
                                 . ( $pageid ? " WHERE id=$pageid" : "")
                                 . " GROUP BY id" );
            $sth->execute();
        } else {
            $this->lock(array('recent'));
            $sth = $dbh->prepare("DELETE FROM $recent_tbl"
                                 . ( $pageid ? " WHERE id=$pageid" : ""));
            $sth->execute();
            $sth = $dbh->prepare( "INSERT INTO $recent_tbl"
                                  . " (id, latestversion, latestmajor, latestminor)"
                                  . " SELECT id, $maxversion, $maxmajor, $maxminor"
                                  . " FROM $version_tbl"
                                  . ( $pageid ? " WHERE id=$pageid" : "")
                                  . " GROUP BY id" );
            $sth->execute();
            $this->unlock(array('recent'));
        }
    }

    function _update_nonempty_table($pageid = false) {