PHP Dal::query_one方法代码示例

 public function parse_cookie($cookie)
 {
     // Parse the given cookie
     if (!preg_match("/^uid:(\\d+):([a-z0-9]+):([a-z0-9]+)\$/", $cookie, $m)) {
         Logger::log("Invalid login cookie received: {$cookie}", LOGGER_WARNING);
         // Invalid cookie - ignore it
         return FALSE;
     }
     list(, $this->user_id, $this->series, $this->token) = $m;
     $this->user_id = (int) $this->user_id;
     // Flush old cookies
     Dal::query("DELETE FROM login_cookies WHERE expires < NOW()");
     // Locate our cookie
     $r = Dal::query_one("SELECT token FROM login_cookies WHERE user_id=? AND series=?", array($this->user_id, $this->series));
     if (!$r) {
         // Totally invalid - we don't even know of the series.  Probably timed out.
         return FALSE;
     }
     list($token) = $r;
     if ($token != $this->token) {
         // Possible attack detected - invalidate all sessions for this user
         Dal::query("DELETE FROM login_cookies WHERE user_id=?", array($this->user_id));
         Logger::log("Invalidated all sessions for user {$this->user_id} as a valid series ID but invalid token was presented -- someone has possibly had their login cookie stolen!", LOGGER_WARNING);
         return FALSE;
     }
     // Success -- assign a new token
     $this->token = $this->make_token();
     Dal::query("UPDATE login_cookies SET token=?, expires=DATE_ADD(NOW(), INTERVAL " . LoginCookie::$cookie_lifetime . " SECOND) WHERE user_id=? AND series=?", array($this->token, $this->user_id, $this->series));
     return $this->user_id;
 }

 public function delete($leaf)
 {
     //TODO: use innodb so this actually matters
     list($file_id, $servers) = Dal::query_one("SELECT file_id, servers FROM local_files WHERE filename=? FOR UPDATE", array($leaf));
     try {
         if (!$file_id) {
             throw new PAException(FILE_NOT_FOUND, "Unable to find file {$leaf} in local_files table:");
         }
         $path = $this->getPath($leaf);
         $server_ids = explode(",", $servers);
         if (in_array(PA::$server_id, $server_ids)) {
             if (empty($path)) {
                 throw new PAException(FILE_NOT_FOUND, "Unable to delete nonexistent file {$path}");
             }
             if (!@unlink($path)) {
                 throw new PAException(STORAGE_ERROR, "Error deleting {$path}");
             }
             $server_ids = array_filter($server_ids, "not_this_server");
             $servers = implode(",", $server_ids);
         }
         Dal::query("UPDATE local_files SET is_deletion=1, timestamp=NOW(), servers=? WHERE file_id=?", array($file_id, $servers));
     } catch (PAException $e) {
         Dal::rollback();
         throw $e;
     }
     return TRUE;
 }

 public static function getExtCache($user_id, $key)
 {
     $row = Dal::query_one("SELECT data FROM ext_cache WHERE user_id=? AND cache_key=? AND expires < NOW()", array($user_id, $key));
     if ($row) {
         return unserialize($row[0]);
     }
     return NULL;
 }

 public function load_first($user_id, $album_type)
 {
     $this->album_type = $album_type;
     $sql = "SELECT CC.collection_id FROM {contentcollections} as CC, {contentcollections_albumtype} AS CCA WHERE CC.collection_id = CCA.contentcollection_id AND CCA.album_type_id = ? AND CC.is_active = ? AND CC.author_id = ? AND CC.type = ? order by CC.collection_id";
     $data = array($album_type, 1, $user_id, 2);
     $r = Dal::query_one($sql, $data);
     if (empty($r)) {
         throw new PAException(CONTENT_COLLECTION_NOT_FOUND, "No albums of type {$album_type} found");
     }
     list($collection_id) = $r;
     $this->load($collection_id);
 }

 function testProfileReadingFunctions()
 {
     // find a user with 'newcss' set
     list($uid, $css) = Dal::query_one("SELECT user_id, field_value FROM user_profile_data WHERE field_type='ui' AND field_name='newcss' ORDER BY user_id LIMIT 1");
     if (empty($uid)) {
         echo "Test not possible as nobody has the newcss field set.  Try again on a more populated database.\n";
         return;
     }
     // find another field, so we can test with more than one
     list($f2_name, $f2_value) = Dal::query_one("SELECT field_name, field_value FROM user_profile_data WHERE field_type='ui' AND user_id=? AND field_name <>'newcss' AND field_value IS NOT NULL LIMIT 1", $uid);
     echo "getting ui/newcss and {$f2_name} properties from user_profile_data for user_id {$uid}.\n";
     $user = new User();
     $user->load((int) $uid);
     // load just the newcss field
     echo "getting just the newcss property for user {$uid}\n";
     $css2 = $user->get_profile_field('ui', 'newcss');
     $this->assertEquals($css, $css2);
     // load just the second field
     echo "getting just the {$f2_name} property for user {$uid}\n";
     $v = $user->get_profile_field('ui', $f2_name);
     $this->assertEquals($v, $f2_value);
     // load newcss and the second field, with get_profile_fields()
     echo "getting the newcss and {$f2_name} properties, with get_profile_fields()\n";
     $data = $user->get_profile_fields('ui', array('newcss', 'graagh', $f2_name));
     $this->assertEquals($css, $data['newcss']);
     $this->assertEquals(NULL, $data['graagh']);
     $this->assertEquals($f2_value, $data[$f2_name]);
     // try again, flushing the cache first
     Cache::reset();
     echo "(without cache) getting the newcss and {$f2_name} properties, with get_profile_fields()\n";
     $data = $user->get_profile_fields('ui', array('newcss', 'graagh', $f2_name));
     $this->assertEquals($css, $data['newcss']);
     $this->assertEquals(NULL, $data['graagh']);
     $this->assertEquals($f2_value, $data[$f2_name]);
     // regression test (phil) 2007-04-01, for bug spotted by martin
     // 2007-03-23: make sure we don't crash if we request fields that
     // are all cached.
     echo "regression: make sure it doesn't crash if everything is in the cache\n";
     $data = $user->get_profile_fields('ui', array('newcss'));
     $this->assertEquals($css, $data['newcss']);
     // try by loading the entire 'ui' section
     echo "getting entire ui section for user {$uid}\n";
     $ui = User::load_profile_section($uid, "ui");
     $this->assertEquals($css, $ui['newcss']['value']);
     $this->assertEquals($f2_value, $ui[$f2_name]['value']);
 }

function explain_query($sql, $args, $query_time)
{
    if (!preg_match("/^\\s*SELECT/i", $sql)) {
        return;
    }
    echo "================================================================================\n";
    echo sprintf("%.04f s | SQL: %s\n", $query_time, $sql);
    global $_timed_queries;
    $_timed_queries[$sql][] = $query_time;
    $explain = Dal::query("EXPLAIN {$sql}", $args);
    $tables = array();
    while ($r = Dal::row_assoc($explain)) {
        if (!empty($r['table'])) {
            $tables[] = $r['table'];
        }
        echo "\n";
        foreach ($r as $k => $v) {
            echo sprintf("%15s: %s\n", $k, $v);
        }
    }
    foreach ($tables as $table) {
        echo "--------------------------------------------------------------------------------\n";
        try {
            $create_table = Dal::query_one("SHOW CREATE TABLE {$table}");
        } catch (PAException $e) {
            if ($e->getCode() != DB_QUERY_FAILED) {
                throw $e;
            }
            $bits = preg_split("/(\\s+|,)/", $sql);
            $pos = array_search($table, $bits);
            if ($pos === NULL) {
                throw new PAException(GENERAL_SOME_ERROR, "Failed to find real name for table {$table} in query {$sql}");
            }
            $table = strtolower($bits[$pos - 1]) == 'as' ? $bits[$pos - 2] : $bits[$pos - 1];
            $create_table = Dal::query_one("SHOW CREATE TABLE {$table}");
        }
        echo $create_table[1] . "\n";
    }
}

 public static function count_all_content()
 {
     list($ct) = Dal::query_one("SELECT COUNT(*) FROM {contents}");
     return $ct;
 }

 /**
  * get the login name from the user id
  */
 public static function get_login_name_from_id($user_id)
 {
     Logger::log("Enter: function User::get_login_name_from_id with user_id=" . $user_id);
     list($login) = Dal::query_one("SELECT login_name FROM {users} WHERE user_id=? AND is_active=1", array($user_id));
     Logger::log("Exit: function User::get_login_name_from_id");
     return $login;
 }

 public function run(PHPUnit_Framework_TestResult $result = NULL)
 {
     if ($result === NULL) {
         $result = new PHPUnit_Framework_TestResult();
         $result->startTest($this);
         $counter = 0;
         foreach ($this->queries as $query_data) {
             $query = 'EXPLAIN ' . $query_data['query'];
             $parameters = $query_data['parameters'];
             $parameters_print = '';
             try {
                 if (!empty($parameters)) {
                     $res = Dal::query($query, $parameters);
                     $parameters_print = 'PARAMETERS:' . "\n";
                     foreach ($parameters as $param) {
                         $parameters_print .= '- ' . $param . "\n";
                     }
                 } else {
                     $res = Dal::query($query);
                 }
             } catch (PAException $e) {
                 try {
                     PHPUnit_Framework_Assert::assertEquals($e->getCode(), DB_QUERY_FAILED);
                 } catch (PHPUnit_Framework_AssertionFailedError $e) {
                     $result->addFailure($this, $e);
                 } catch (Exception $e) {
                     $result->addError($this, $e);
                 }
             }
             $tables = array();
             print "{{{ ==================================================================\n";
             $query_row = wordwrap($explain . "QUERY: \"{$query}\"", 70);
             print $query_row . "\n";
             if (!empty($parameters_print)) {
                 print "----------------------------------------------------------------------\n";
                 print $parameters_print;
             }
             while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
                 print "----------------------------------------------------------------------\n";
                 print 'ID: ' . $row->id . "\n";
                 print 'SELECT TYPE: ' . $row->select_type . "\n";
                 print 'TABLE: ' . $row->table . "\n";
                 if (!empty($row->table)) {
                     $tables[] = $row->table;
                 }
                 print 'TYPE: ' . $row->type . "\n";
                 print 'POSSIBLE KEYS: ' . $row->possible_keys . "\n";
                 print 'KEY: ' . $row->key . "\n";
                 print 'KEY LENGTH: ' . $row->key_len . "\n";
                 print 'REFERENCE: ' . $row->ref . "\n";
                 print 'ROWS: ' . $row->rows . "\n";
                 print 'EXTRA: ' . $row->Extra . "\n";
                 $counter++;
             }
             // Now show all the tables used in the query.
             foreach ($tables as $table) {
                 print "----------------------------------------------------------------------\n";
                 try {
                     $create_table = Dal::query_one("SHOW CREATE TABLE {$table}");
                 } catch (PAException $e) {
                     if ($e->getCode() != DB_QUERY_FAILED) {
                         throw $e;
                     }
                     $bits = preg_split("/(\\s+|,)/", $query);
                     $pos = array_search($table, $bits);
                     if ($pos === NULL) {
                         throw new PAException(GENERAL_SOME_ERROR, "Failed to find real name for table {$table} in query {$sql}");
                     }
                     $table = strtolower($bits[$pos - 1]) == 'as' ? $bits[$pos - 2] : $bits[$pos - 1];
                     $create_table = Dal::query_one("SHOW CREATE TABLE {$table}");
                 }
                 echo $create_table[1] . "\n";
             }
             print "================================================================== }}}\n";
         }
         $result->endTest($this);
         return $result;
     }
 }

 static function add_spam_term($term, $blacklist = 1)
 {
     $r = Dal::query_one("SELECT id FROM spam_terms WHERE term=?", array($term));
     if ($r[0]) {
         return;
     }
     // we already have this term
     Dal::query("INSERT INTO spam_terms SET term=?, blacklist=?", array($term, $blacklist));
 }

function render_main_page_area($user)
{
    global $admin_password;
    $page_url = PA::$url . "/comment_management.php";
    $paging_url = "{$page_url}?";
    // url to pass to the pager object
    $msg = "";
    $path_info = @$_SERVER['PATH_INFO'];
    // see if the user is logged in as an admin
    if ($path_info == "/login") {
        if (@$_REQUEST['admin_password'] == $admin_password) {
            $_SESSION['comment_management_is_admin'] = TRUE;
        } else {
            $msg = "Incorrect password!  Try again...";
        }
    } else {
        if ($path_info == "/logout") {
            $_SESSION['comment_management_is_admin'] = FALSE;
            $msg = "You are now logged out (of admin mode).";
        }
    }
    $is_admin = @$_SESSION['comment_management_is_admin'];
    $limit_set = NULL;
    // set this to an array with keys 'comment_id' to limit display to those keys
    $current_search_terms = NULL;
    // current search terms
    switch ($path_info) {
        case '/analyze_comment':
            $comment_id = (int) @$_REQUEST['comment'];
            if (!$is_admin) {
                $msg = "Sorry, only administrators can analyze comments at the moment :(";
            } elseif ($comment_id) {
                $cmt = new Comment();
                $cmt->load($comment_id);
                $cmt->index_spam_domains();
                $msg = "<p>Analysis of comment {$comment_id}:</p><hr/><p>" . nl2br(htmlspecialchars($cmt->comment)) . "</p><hr/><ul>";
                $hosts = $cmt->get_link_hosts();
                foreach ($hosts as $domain => $links) {
                    $msg .= "<li><b>" . htmlspecialchars($domain) . "</b> (<a href=\"{$page_url}/analyze_domain?domain=" . htmlspecialchars($domain) . "\">analyze</a>): ";
                    $dom = new SpamDomain($domain);
                    if ($dom->blacklisted) {
                        $msg .= " BLACKLISTED";
                    }
                    $msg .= "<ul>";
                    foreach ($links as $link) {
                        list($url, $linktexts) = $link;
                        $msg .= "<li>" . htmlspecialchars($url) . " -> " . implode(" | ", array_map("htmlspecialchars", $linktexts)) . "</li>";
                    }
                    $msg .= "</ul></li>";
                }
                $msg .= "</ul><hr/>";
            }
            break;
        case '/search':
            $current_search_terms = @$_REQUEST['q'];
            if (!$is_admin) {
                $msg = "Sorry, only administrators can search comments at the moment :(";
            } elseif ($current_search_terms) {
                $paging_url = "{$page_url}/search?q=" . urlencode($current_search_terms) . "&";
                $limit_set = Comment::search($current_search_terms);
            }
            break;
        case '/stats':
            $msg = "<p>Stats:</p>";
            list($n) = Dal::query_one("SELECT COUNT(*) FROM {comments}");
            list($n_deleted) = Dal::query_one("SELECT COUNT(*) FROM {comments} WHERE is_active=0");
            $n_active = $n - $n_deleted;
            $msg .= "<li>{$n} comments ({$n_active} active / {$n_deleted} deleted)</li>";
            list($n_ham) = Dal::query_one("SELECT COUNT(*) FROM {comments} WHERE is_active=1 AND spam_state=0");
            $n_spam = $n_active - $n_ham;
            $msg .= "<li>{$n_spam} active+spam / {$n_ham} active+not spam</li>";
            list($n_no_class) = Dal::query_one("SELECT COUNT(*) FROM {comments} WHERE is_active=1 AND akismet_spam IS NULL");
            $msg .= "<li>{$n_no_class} active comments not (yet?) classified by Akismet</li>";
            list($n_akismet_del) = Dal::query_one("SELECT COUNT(*) FROM {comments} WHERE is_active=0 AND akismet_spam=1");
            $msg .= "<li>{$n_akismet_del} comments flagged as spam by akismet and deleted</li>";
            break;
        case '/add_spam_term':
            $spam_term = @$_REQUEST['term'];
            if (!$is_admin) {
                $msg = "Sorry, only administrators can add spam terms at the moment.";
            } elseif ($spam_term) {
                // find the comments
                $matches = Comment::search($spam_term);
                $n_deleted = count($matches);
                // add the term
                Comment::add_spam_term($spam_term);
                // and delete the comments
                $blk_size = 1000;
                $F_fetch_ids = create_function('$item', 'return $item["comment_id"];');
                for ($i = 0; $i < count($matches); $i += $blk_size) {
                    Comment::set_spam_state(array_map($F_fetch_ids, array_slice($matches, $i, $blk_size)), SPAM_STATE_SPAM_WORDS);
                }
                $msg = "Added <b>" . htmlspecialchars($spam_term) . '</b> to the spam term database, and deleted ' . $n_deleted . ' comments containing it.';
            }
            break;
        case '/analyze_domain':
            $domain = @$_REQUEST['domain'];
            if (!$is_admin) {
                $msg = "Sorry, only administrators can analyze domains.";
            } else {
//.........这里部分代码省略.........

 public static function count_domains()
 {
     list($total) = Dal::query_one("SELECT COUNT(*) FROM spam_domains");
     list($total_blacklisted) = Dal::query_one("SELECT COUNT(*) FROM spam_domains WHERE blacklisted=1");
     return array($total, $total_blacklisted);
 }

 public static function relation_exists($user_id, $relation_id)
 {
     $r = Dal::query_one("SELECT * FROM {relations} WHERE user_id=? AND relation_id=?", array($user_id, $relation_id));
     if ($r) {
         return true;
     } else {
         return false;
     }
 }

 function is_modified($path)
 {
     $r = Dal::query_one("SELECT kind,path,hash FROM svn_objects WHERE is_active=1 AND path=?", array($path));
     if (!$r) {
         throw new Subversion_Failure("Attempt to check modification for a nonexistent file {$path}");
     }
     list($kind, $leaf, $hash) = $r;
     $path = "{$this->root}/{$leaf}";
     return $this->_check_modified($kind, $path, $hash);
 }

 public function count_persona_service_paths($persona_service_id)
 {
     list($count) = Dal::query_one("SELECT COUNT(*) FROM {persona_service_paths} WHERE persona_service_id = ?", array($persona_service_id));
     return intval($count);
 }