Changeset 427

Timestamp:

2011-04-22 08:23:54 (1 year ago)

Author:

hannes

Message:

redundant storage of number of posts per topic for better performance

Files:

• trunk/classes/misc/Post.php (modified) (1 diff)
• trunk/classes/pages/Category.php (modified) (1 diff)
• trunk/classes/pages/Delete.php (modified) (1 diff)
• trunk/classes/pages/Topic.php (modified) (3 diffs)
• trunk/db/mysql.sql (modified) (1 diff)
• trunk/db/postgresql.sql (modified) (1 diff)
• trunk/db/sqlite.sql (modified) (1 diff)

trunk/classes/misc/Post.php

@@ -524 +524 @@
-                        }
-                        // get last page of topic
-
-
-
-
-
+
-                        // calculate page
-row
+numberofposts
-                        // send e-mail notifications (using the language the receipient has set)
-                        $q = $C->prepare('SELECT member FROM ' . $SETTINGS['dbtableprefix'] . 'subscriptions WHERE topic = :id');

trunk/classes/pages/Category.php

@@ -85 +85 @@
-                    $q3 = NULL;
-                    // number of posts
-COUNT(postid) AS posts FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic IN (SELECT topicid FROM ' . $SETTINGS['dbtableprefix'] . 'topics WHERE forum=:forum)
+SUM(posts) AS posts FROM ' . $SETTINGS['dbtableprefix'] . 'topics WHERE forum = :forum
-                    $q4->bindParam(':forum', $row2['forumid'], PDO::PARAM_INT, 12);
-                    $q4->execute();

trunk/classes/pages/Delete.php

@@ -85 +85 @@
-                    $q->execute();
-                    $q = NULL;
+                    // recount posts
+                    Topic::countPosts($INPUT['id']);
-                    // success message
-                    $div = $this->html->body->addChild('div', $LANG['post_deleted']);

trunk/classes/pages/Topic.php

@@ -72 +72 @@
-            $postbuttons = new Postbuttons(TRUE, $row->forum, TRUE, $row->topicid, $row->closed);
-            $this->html->body->addElement($postbuttons->get());
-            // get number of posts
-            $q2 = $C->prepare('SELECT COUNT(postid) AS posts FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic=:topic');
-            $q2->bindParam(':topic', $row->topicid, PDO::PARAM_INT, 12);
-            $q2->execute();
-            $row2 = $q2->fetchObject();
-            $q2 = NULL;
-            // calculate start for this page
-            if (!isset($INPUT['page']) || $INPUT['page'] == '') {
@@ -84 +78 @@
-            $_start = $SETTINGS['postsperpage'] * ($INPUT['page'] - 1);
-            // build page navigation
-2
+
-            // display page navigation
-            if ($_pagination = $pages->display('topic', Array('id'), Array($row->topicid))) {
@@ -269 +263 @@
-    } /* function getTitle */
-
+    /**
+     * Function:    countPosts
+     * Description: write updated number of posts into the database
+     * Input:       $id - topic's id
+     * Returns:     the number of posts (-1 on error)
+     **/
+    public static function countPosts($id) {
+        global $C, $SETTINGS;
+        $q = $C->prepare('SELECT COUNT(*) FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic = :id');
+        $q->bindParam(':id', $id, PDO::PARAM_INT, 12);
+        if ($q->execute()) {
+            $number = $q->fetchColumn();
+            $q = NULL;
+            $q = $C->prepare('UPDATE ' . $SETTINGS['dbtableprefix'] . 'topics SET posts = :number WHERE topicid = :id');
+            $q->bindParam(':number', $number, PDO::PARAM_INT);
+            $q->bindParam(':id', $id, PDO::PARAM_INT);
+            if ($q->execute()) {
+                $q = NULL;
+                return $number;
+            } else {
+                $q = NULL;
+                return -1;
+            }
+        } else {
+            $q = NULL;
+            return -1;
+        }
+    } /* function countPosts */
+
-} /* class Topic */
-

trunk/db/mysql.sql

@@ -59 +59 @@
-  pinned INTEGER NOT NULL DEFAULT 0,
-  poll INTEGER NOT NULL DEFAULT 0,
-
+
+
-) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-

trunk/db/postgresql.sql

@@ -59 +59 @@
-  pinned INTEGER NOT NULL DEFAULT 0,
-  poll INTEGER NOT NULL DEFAULT 0,
-
+
+
-);
-

trunk/db/sqlite.sql

@@ -59 +59 @@
-  pinned INTEGER NOT NULL DEFAULT 0,
-  poll INTEGER NOT NULL DEFAULT 0,
-
+
+
-);
-