| Index: trunk/extensions/ArticleFeedback/sql/RecreatePK.sql |
| — | — | @@ -1,38 +0,0 @@ |
| 2 | | - |
| 3 | | - |
| 4 | | -CREATE TABLE /*_*/article_feedback2 ( |
| 5 | | - aa_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| 6 | | - aa_page_id integer unsigned NOT NULL, |
| 7 | | - aa_user_id integer NOT NULL, |
| 8 | | - aa_user_text varbinary(255) NOT NULL, |
| 9 | | - aa_user_anon_token varbinary(32) NOT NULL DEFAULT '', |
| 10 | | - aa_revision integer unsigned NOT NULL, |
| 11 | | - aa_timestamp binary(14) NOT NULL DEFAULT '', |
| 12 | | - aa_rating_id int unsigned NOT NULL, |
| 13 | | - aa_rating_value int unsigned NOT NULL, |
| 14 | | - aa_design_bucket int unsigned NOT NULL DEFAULT 0 |
| 15 | | -) /*$wgDBTableOptions*/; |
| 16 | | -CREATE INDEX /*i*/aa_page_user_token_id ON /*_*/article_feedback2 (aa_page_id, aa_user_text, aa_user_anon_token, aa_id); |
| 17 | | -CREATE INDEX /*i*/aa_revision ON /*_*/article_feedback2 (aa_revision); |
| 18 | | -CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/article_feedback2 (aa_timestamp); |
| 19 | | - |
| 20 | | -INSERT INTO /*_*/article_feedback2 |
| 21 | | - (aa_page_id, aa_user_id, aa_user_text, aa_user_anon_token, aa_revision, aa_timestamp, aa_rating_id, aa_rating_value, aa_design_bucket) |
| 22 | | - SELECT aa_page_id, aa_user_id, aa_user_text, aa_user_anon_token, aa_revision, aa_timestamp, aa_rating_id, aa_rating_value, aa_design_bucket |
| 23 | | - FROM /*_*/article_feedback |
| 24 | | - ORDER BY aa_revision, aa_user_text, aa_rating_id, aa_user_anon_token; |
| 25 | | - |
| 26 | | -DROP TABLE /*_*/article_feedback; |
| 27 | | -ALTER TABLE /*_*/article_feedback2 RENAME TO /*_*/article_feedback; |
| Index: trunk/extensions/ArticleFeedback/sql/ArticleFeedback.sql |
| — | — | @@ -1,7 +1,5 @@ |
| 2 | 2 | -- Store article feedbacks (user rating per revision) |
| 3 | 3 | CREATE TABLE IF NOT EXISTS /*_*/article_feedback ( |
| 4 | | - -- Row ID (primary key) |
| 5 | | - aa_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| 6 | 4 | -- Foreign key to page.page_id |
| 7 | 5 | aa_page_id integer unsigned NOT NULL, |
| 8 | 6 | -- User Id (0 if anon) |
| — | — | @@ -19,10 +17,11 @@ |
| 20 | 18 | -- Value of the rating (0 is "unrated", else 1-5) |
| 21 | 19 | aa_rating_value int unsigned NOT NULL, |
| 22 | 20 | -- Which rating widget the user was given. Default of 0 is the "old" design |
| 23 | | - aa_design_bucket int unsigned NOT NULL DEFAULT 0 |
| | 21 | + aa_design_bucket int unsigned NOT NULL DEFAULT 0, |
| | 22 | + -- 1 vote per user per revision |
| | 23 | + PRIMARY KEY (aa_revision, aa_user_text, aa_rating_id, aa_user_anon_token) |
| 24 | 24 | ) /*$wgDBTableOptions*/; |
| 25 | | -CREATE INDEX /*i*/aa_page_user_token_id ON /*_*/article_feedback (aa_page_id, aa_user_text, aa_user_anon_token, aa_id); |
| 26 | | -CREATE INDEX /*i*/aa_revision ON /*_*/article_feedback (aa_revision); |
| | 25 | +CREATE INDEX /*i*/aa_user_page_revision ON /*_*/article_feedback (aa_user_id, aa_page_id, aa_revision); |
| 27 | 26 | -- Create an index on the article_feedback.aa_timestamp field |
| 28 | 27 | CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/article_feedback (aa_timestamp); |
| 29 | 28 | CREATE INDEX /*i*/aa_page_id ON /*_*/article_feedback (aa_page_id, aa_timestamp); |
| Index: trunk/extensions/ArticleFeedback/api/ApiArticleFeedback.php |
| — | — | @@ -38,60 +38,57 @@ |
| 39 | 39 | } |
| 40 | 40 | |
| 41 | 41 | $dbw = wfGetDB( DB_MASTER ); |
| 42 | | - |
| 43 | | - $pageId = $params['pageid']; |
| 44 | | - $revisionId = $params['revid']; |
| 45 | | - |
| 46 | | - // Build array( rating ID => rating value ) |
| 47 | | - $ratings = array(); |
| 48 | | - foreach ( $wgArticleFeedbackRatingTypes as $ratingID => $unused ) { |
| 49 | | - $ratings[$ratingID] = intval( $params["r{$ratingID}"] ); |
| 50 | | - } |
| 51 | | - // Insert the new ratings |
| 52 | | - $id = $this->insertUserRatings( $pageId, $revisionId, $wgUser, $token, $ratings, $params['bucket'] ); |
| 53 | | - |
| 54 | | - // Query the previous ratings by this user for this page, |
| | 42 | + |
| | 43 | + // Query the latest ratings by this user for this page, |
| 55 | 44 | // possibly for an older revision |
| 56 | | - // Use aa_id < $id to make sure we get the one before ours even if other ratings were inserted after |
| 57 | | - // insertUserRatings() but before selectRow(); this prevents race conditions from messing things up. |
| | 45 | + // Select from the master to prevent replag-induced bugs |
| 58 | 46 | $res = $dbw->select( |
| 59 | 47 | 'article_feedback', |
| 60 | 48 | array( 'aa_rating_id', 'aa_rating_value', 'aa_revision' ), |
| 61 | 49 | array( |
| | 50 | + 'aa_user_id' => $wgUser->getId(), |
| 62 | 51 | 'aa_user_text' => $wgUser->getName(), |
| 63 | 52 | 'aa_page_id' => $params['pageid'], |
| 64 | 53 | 'aa_rating_id' => array_keys( $wgArticleFeedbackRatingTypes ), |
| 65 | 54 | 'aa_user_anon_token' => $token, |
| 66 | | - 'aa_id < ' . intval( $id ) |
| 67 | 55 | ), |
| 68 | 56 | __METHOD__, |
| 69 | 57 | array( |
| 70 | | - 'ORDER BY' => 'aa_id DESC', |
| | 58 | + 'ORDER BY' => 'aa_revision DESC', |
| 71 | 59 | 'LIMIT' => count( $wgArticleFeedbackRatingTypes ), |
| 72 | 60 | ) |
| 73 | 61 | ); |
| | 62 | + |
| 74 | 63 | $lastRatings = array(); |
| | 64 | + |
| 75 | 65 | foreach ( $res as $row ) { |
| 76 | 66 | $lastRatings[$row->aa_rating_id]['value'] = $row->aa_rating_value; |
| 77 | 67 | $lastRatings[$row->aa_rating_id]['revision'] = $row->aa_revision; |
| 78 | 68 | } |
| 79 | | - |
| | 69 | + |
| | 70 | + $pageId = $params['pageid']; |
| | 71 | + $revisionId = $params['revid']; |
| | 72 | + |
| 80 | 73 | foreach ( $wgArticleFeedbackRatingTypes as $ratingID => $unused ) { |
| 81 | | - $lastPageRating = false; |
| 82 | | - $lastRevRating = false; |
| | 74 | + $lastRating = false; |
| | 75 | + $lastRevision = false; |
| 83 | 76 | if ( isset( $lastRatings[$ratingID] ) ) { |
| 84 | | - $lastPageRating = intval( $lastRatings[$ratingID]['value'] ); |
| 85 | | - if ( intval( $lastRatings[$ratingID]['revision'] ) == $revisionId ) { |
| 86 | | - $lastRevRating = $lastPageRating; |
| 87 | | - } |
| | 77 | + $lastRating = intval( $lastRatings[$ratingID]['value'] ); |
| | 78 | + $lastRevision = intval( $lastRatings[$ratingID]['revision'] ); |
| 88 | 79 | } |
| 89 | | - $thisRating = intval( $params["r{$ratingID}"] ); |
| | 80 | + |
| | 81 | + $thisRating = false; |
| | 82 | + if ( isset( $params["r{$ratingID}"] ) ) { |
| | 83 | + $thisRating = intval( $params["r{$ratingID}"] ); |
| | 84 | + } |
| | 85 | + |
| | 86 | + $this->insertRevisionRating( $pageId, $revisionId, $lastRevision, $ratingID, ( $thisRating - $lastRating ), |
| | 87 | + $thisRating, $lastRating |
| | 88 | + ); |
| 90 | 89 | |
| 91 | | - // Update counter tables |
| 92 | | - $this->insertRevisionRating( $pageId, $revisionId, $ratingID, $thisRating - $lastRevRating, |
| 93 | | - $thisRating, $lastRevRating |
| 94 | | - ); |
| 95 | | - $this->insertPageRating( $pageId, $ratingID, $thisRating - $lastPageRating, $thisRating, $lastPageRating ); |
| | 90 | + $this->insertPageRating( $pageId, $ratingID, ( $thisRating - $lastRating ), $thisRating, $lastRating ); |
| | 91 | + |
| | 92 | + $this->insertUserRatings( $pageId, $revisionId, $wgUser, $token, $ratingID, $thisRating, $params['bucket'] ); |
| 96 | 93 | } |
| 97 | 94 | |
| 98 | 95 | $this->insertProperties( $revisionId, $wgUser, $token, $params ); |
| — | — | @@ -160,12 +157,13 @@ |
| 161 | 158 | * |
| 162 | 159 | * @param $pageId Integer: Page Id |
| 163 | 160 | * @param $revisionId Integer: Revision Id |
| | 161 | + * @param $lastRevision Integer: Revision Id of last rating |
| 164 | 162 | * @param $ratingId Integer: Rating Id |
| 165 | 163 | * @param $updateAddition Integer: Difference between user's last rating (if applicable) |
| 166 | 164 | * @param $thisRating Integer|Boolean: Value of the Rating |
| 167 | 165 | * @param $lastRating Integer|Boolean: Value of the last Rating |
| 168 | 166 | */ |
| 169 | | - private function insertRevisionRating( $pageId, $revisionId, $ratingId, $updateAddition, $thisRating, $lastRating ) { |
| | 167 | + private function insertRevisionRating( $pageId, $revisionId, $lastRevision, $ratingId, $updateAddition, $thisRating, $lastRating ) { |
| 170 | 168 | $dbw = wfGetDB( DB_MASTER ); |
| 171 | 169 | |
| 172 | 170 | // Try to insert a new "totals" row for this page,rev,rating set |
| — | — | @@ -181,21 +179,58 @@ |
| 182 | 180 | __METHOD__, |
| 183 | 181 | array( 'IGNORE' ) |
| 184 | 182 | ); |
| 185 | | - |
| 186 | | - // Apply the difference between the previous and new ratings to the current "totals" row |
| 187 | | - $dbw->update( |
| 188 | | - 'article_feedback_revisions', |
| 189 | | - array( |
| 190 | | - 'afr_total = afr_total + ' . $updateAddition, |
| 191 | | - 'afr_count = afr_count + ' . $this->getCountChange( $lastRating, $thisRating ), |
| 192 | | - ), |
| 193 | | - array( |
| 194 | | - 'afr_page_id' => $pageId, |
| 195 | | - 'afr_rating_id' => $ratingId, |
| 196 | | - 'afr_revision' => $revisionId, |
| 197 | | - ), |
| 198 | | - __METHOD__ |
| 199 | | - ); |
| | 183 | + |
| | 184 | + // If that succeded in inserting a row, then we are for sure rating a previously unrated |
| | 185 | + // revision, and we need to add more information about this rating to the new "totals" row, |
| | 186 | + // as well as remove the previous rating values from the previous "totals" row |
| | 187 | + if ( $dbw->affectedRows() ) { |
| | 188 | + // If there was a previous rating, there should be a "totals" row for it's revision |
| | 189 | + if ( $lastRating ) { |
| | 190 | + // Deduct the previous rating values from the previous "totals" row |
| | 191 | + $dbw->update( |
| | 192 | + 'article_feedback_revisions', |
| | 193 | + array( |
| | 194 | + 'afr_total = afr_total - ' . intval( $lastRating ), |
| | 195 | + 'afr_count = afr_count - 1', |
| | 196 | + ), |
| | 197 | + array( |
| | 198 | + 'afr_page_id' => $pageId, |
| | 199 | + 'afr_rating_id' => $ratingId, |
| | 200 | + 'afr_revision' => $lastRevision |
| | 201 | + ), |
| | 202 | + __METHOD__ |
| | 203 | + ); |
| | 204 | + } |
| | 205 | + // Add this rating's values to the new "totals" row |
| | 206 | + $dbw->update( |
| | 207 | + 'article_feedback_revisions', |
| | 208 | + array( |
| | 209 | + 'afr_total' => $thisRating, |
| | 210 | + 'afr_count' => $thisRating ? 1 : 0, |
| | 211 | + ), |
| | 212 | + array( |
| | 213 | + 'afr_page_id' => $pageId, |
| | 214 | + 'afr_rating_id' => $ratingId, |
| | 215 | + 'afr_revision' => $revisionId, |
| | 216 | + ), |
| | 217 | + __METHOD__ |
| | 218 | + ); |
| | 219 | + } else { |
| | 220 | + // Apply the difference between the previous and new ratings to the current "totals" row |
| | 221 | + $dbw->update( |
| | 222 | + 'article_feedback_revisions', |
| | 223 | + array( |
| | 224 | + 'afr_total = afr_total + ' . $updateAddition, |
| | 225 | + 'afr_count = afr_count + ' . $this->getCountChange( $lastRating, $thisRating ), |
| | 226 | + ), |
| | 227 | + array( |
| | 228 | + 'afr_page_id' => $pageId, |
| | 229 | + 'afr_rating_id' => $ratingId, |
| | 230 | + 'afr_revision' => $revisionId, |
| | 231 | + ), |
| | 232 | + __METHOD__ |
| | 233 | + ); |
| | 234 | + } |
| 200 | 235 | } |
| 201 | 236 | /** |
| 202 | 237 | * Calculate the difference between the previous rating and this one |
| — | — | @@ -212,45 +247,55 @@ |
| 213 | 248 | } |
| 214 | 249 | |
| 215 | 250 | /** |
| 216 | | - * Inserts the user's ratings for a specific revision |
| | 251 | + * Inserts (or Updates, where appropriate) the users ratings for a specific revision |
| 217 | 252 | * |
| 218 | 253 | * @param $pageId Integer: Page Id |
| 219 | 254 | * @param $revisionId Integer: Revision Id |
| 220 | 255 | * @param $user User: Current User object |
| 221 | 256 | * @param $token Array: Token if necessary |
| 222 | | - * @param $ratings Array: Keys are rating IDs, values are rating values |
| | 257 | + * @param $ratingId Integer: Rating Id |
| | 258 | + * @param $ratingValue Integer: Value of the Rating |
| 223 | 259 | * @param $bucket Integer: Which rating widget was the user shown |
| 224 | | - * @return int Return value of $dbw->insertID(). This is the aa_id of the first (MySQL) or last (SQLite) inserted row |
| 225 | 260 | */ |
| 226 | | - private function insertUserRatings( $pageId, $revisionId, $user, $token, $ratings, $bucket ) { |
| | 261 | + private function insertUserRatings( $pageId, $revisionId, $user, $token, $ratingId, $ratingValue, $bucket ) { |
| 227 | 262 | $dbw = wfGetDB( DB_MASTER ); |
| 228 | 263 | |
| 229 | 264 | $timestamp = $dbw->timestamp(); |
| 230 | | - |
| 231 | | - $rows = array(); |
| 232 | | - foreach ( $ratings as $ratingID => $ratingValue ) { |
| 233 | | - $rows[] = array( |
| | 265 | + |
| | 266 | + $dbw->insert( |
| | 267 | + 'article_feedback', |
| | 268 | + array( |
| 234 | 269 | 'aa_page_id' => $pageId, |
| 235 | 270 | 'aa_user_id' => $user->getId(), |
| 236 | 271 | 'aa_user_text' => $user->getName(), |
| 237 | 272 | 'aa_user_anon_token' => $token, |
| 238 | 273 | 'aa_revision' => $revisionId, |
| 239 | 274 | 'aa_timestamp' => $timestamp, |
| 240 | | - 'aa_rating_id' => $ratingID, |
| | 275 | + 'aa_rating_id' => $ratingId, |
| 241 | 276 | 'aa_rating_value' => $ratingValue, |
| 242 | 277 | 'aa_design_bucket' => $bucket, |
| | 278 | + ), |
| | 279 | + __METHOD__, |
| | 280 | + array( 'IGNORE' ) |
| | 281 | + ); |
| | 282 | + |
| | 283 | + if ( !$dbw->affectedRows() ) { |
| | 284 | + $dbw->update( |
| | 285 | + 'article_feedback', |
| | 286 | + array( |
| | 287 | + 'aa_timestamp' => $timestamp, |
| | 288 | + 'aa_rating_value' => $ratingValue, |
| | 289 | + ), |
| | 290 | + array( |
| | 291 | + 'aa_page_id' => $pageId, |
| | 292 | + 'aa_user_text' => $user->getName(), |
| | 293 | + 'aa_revision' => $revisionId, |
| | 294 | + 'aa_rating_id' => $ratingId, |
| | 295 | + 'aa_user_anon_token' => $token, |
| | 296 | + ), |
| | 297 | + __METHOD__ |
| 243 | 298 | ); |
| 244 | 299 | } |
| 245 | | - |
| 246 | | - // In MySQL, there is native support for multi-row inserts and our rows |
| 247 | | - // will automatically get consecutive insertIDs. In SQLite this seems |
| 248 | | - // to be the case if you use a transaction, but I couldn't find anything |
| 249 | | - // on the web that states whether this is true. |
| 250 | | - $dbw->begin(); |
| 251 | | - $dbw->insert( 'article_feedback', $rows, __METHOD__ ); |
| 252 | | - $dbw->commit(); |
| 253 | | - |
| 254 | | - return $dbw->insertID(); |
| 255 | 300 | } |
| 256 | 301 | |
| 257 | 302 | /** |
| Index: trunk/extensions/ArticleFeedback/api/ApiQueryArticleFeedback.php |
| — | — | @@ -179,13 +179,14 @@ |
| 180 | 180 | array( |
| 181 | 181 | 'aa_page_id' => $params['pageid'], |
| 182 | 182 | 'aa_rating_id' => array_keys( $wgArticleFeedbackRatingTypes ), |
| | 183 | + 'aa_user_id' => $wgUser->getId(), |
| 183 | 184 | 'aa_user_text' => $wgUser->getName(), |
| 184 | 185 | 'aa_user_anon_token' => $this->getAnonToken( $params ), |
| 185 | 186 | ), |
| 186 | 187 | __METHOD__, |
| 187 | 188 | array( |
| 188 | 189 | 'LIMIT' => count( $wgArticleFeedbackRatingTypes ), |
| 189 | | - 'ORDER BY' => 'aa_id DESC', |
| | 190 | + 'ORDER BY' => 'aa_revision DESC', |
| 190 | 191 | ) |
| 191 | 192 | ); |
| 192 | 193 | $ratings = array(); |