| Index: trunk/extensions/ArticleFeedback/sql/RecreatePK.sql |
| — | — | @@ -0,0 +1,38 @@ |
| | 2 | +-- Add aa_id as the new primary key to article_feedback and drop the old one. |
| | 3 | +-- Also change the indexing while we're at it |
| | 4 | + |
| | 5 | +-- In order to safely change the primary key even in replicated environments, |
| | 6 | +-- we have to create a new table with the new structure, copy over the data, |
| | 7 | +-- then rename the table. This is to ensure that the values of aa_id are |
| | 8 | +-- consistent across all slaves. |
| | 9 | + |
| | 10 | +-- Create new table |
| | 11 | +-- Would've used CREATE TABLE ... LIKE here but SQLite doesn't support ALTER TABLE ... DROP PRIMARY KEY |
| | 12 | +-- so we're stuck with duplicating the table structure. |
| | 13 | +CREATE TABLE /*_*/article_feedback2 ( |
| | 14 | + aa_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| | 15 | + aa_page_id integer unsigned NOT NULL, |
| | 16 | + aa_user_id integer NOT NULL, |
| | 17 | + aa_user_text varbinary(255) NOT NULL, |
| | 18 | + aa_user_anon_token varbinary(32) NOT NULL DEFAULT '', |
| | 19 | + aa_revision integer unsigned NOT NULL, |
| | 20 | + aa_timestamp binary(14) NOT NULL DEFAULT '', |
| | 21 | + aa_rating_id int unsigned NOT NULL, |
| | 22 | + aa_rating_value int unsigned NOT NULL, |
| | 23 | + aa_design_bucket int unsigned NOT NULL DEFAULT 0 |
| | 24 | +) /*$wgDBTableOptions*/; |
| | 25 | +CREATE INDEX /*i*/aa_page_user_token_id ON /*_*/article_feedback2 (aa_page_id, aa_user_text, aa_user_anon_token, aa_id); |
| | 26 | +CREATE INDEX /*i*/aa_revision ON /*_*/article_feedback2 (aa_revision); |
| | 27 | +CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/article_feedback2 (aa_timestamp); |
| | 28 | + |
| | 29 | +-- Copy the data, ordered by the old primary key |
| | 30 | +-- Need to specify the fields explicitly to avoid confusion with aa_id |
| | 31 | +INSERT INTO /*_*/article_feedback2 |
| | 32 | + (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) |
| | 33 | + 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 |
| | 34 | + FROM /*_*/article_feedback |
| | 35 | + ORDER BY aa_revision, aa_user_text, aa_rating_id, aa_user_anon_token; |
| | 36 | + |
| | 37 | +-- Drop the old table and rename the new table to the old name |
| | 38 | +DROP TABLE /*_*/article_feedback; |
| | 39 | +ALTER TABLE /*_*/article_feedback2 RENAME TO /*_*/article_feedback; |
| Property changes on: trunk/extensions/ArticleFeedback/sql/RecreatePK.sql |
| ___________________________________________________________________ |
| Added: svn:eol-style |
| 1 | 40 | + native |
| Index: trunk/extensions/ArticleFeedback/sql/ArticleFeedback.sql |
| — | — | @@ -14,6 +14,8 @@ |
| 15 | 15 | |
| 16 | 16 | -- Store article feedbacks (user rating per revision) |
| 17 | 17 | CREATE TABLE IF NOT EXISTS /*_*/article_feedback ( |
| | 18 | + -- Row ID (primary key) |
| | 19 | + aa_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| 18 | 20 | -- Foreign key to page.page_id |
| 19 | 21 | aa_page_id integer unsigned NOT NULL, |
| 20 | 22 | -- User Id (0 if anon) |
| — | — | @@ -31,11 +33,10 @@ |
| 32 | 34 | -- Value of the rating (0 is "unrated", else 1-5) |
| 33 | 35 | aa_rating_value int unsigned NOT NULL, |
| 34 | 36 | -- Which rating widget the user was given. Default of 0 is the "old" design |
| 35 | | - aa_design_bucket int unsigned NOT NULL DEFAULT 0, |
| 36 | | - -- 1 vote per user per revision |
| 37 | | - PRIMARY KEY (aa_revision, aa_user_text, aa_rating_id, aa_user_anon_token) |
| | 37 | + aa_design_bucket int unsigned NOT NULL DEFAULT 0 |
| 38 | 38 | ) /*$wgDBTableOptions*/; |
| 39 | | -CREATE INDEX /*i*/aa_user_page_revision ON /*_*/article_feedback (aa_user_id, aa_page_id, aa_revision); |
| | 39 | +CREATE INDEX /*i*/aa_page_user_token_id ON /*_*/article_feedback (aa_page_id, aa_user_text, aa_user_anon_token, aa_id); |
| | 40 | +CREATE INDEX /*i*/aa_revision ON /*_*/article_feedback (aa_revision); |
| 40 | 41 | -- Create an index on the article_feedback.aa_timestamp field |
| 41 | 42 | CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/article_feedback (aa_timestamp); |
| 42 | 43 | |
| Index: trunk/extensions/ArticleFeedback/ArticleFeedback.hooks.php |
| — | — | @@ -230,6 +230,15 @@ |
| 231 | 231 | $dir . '/sql/AddArticleFeedbackTimestampIndex.sql', |
| 232 | 232 | true |
| 233 | 233 | ) ); |
| | 234 | + |
| | 235 | + // This change recreates the PK on a new field. Check for that new field's existence |
| | 236 | + $updater->addExtensionUpdate( array( |
| | 237 | + 'addField', |
| | 238 | + 'article_feedback', |
| | 239 | + 'aa_id', |
| | 240 | + $dir . '/sql/RecreatePK.sql', |
| | 241 | + true |
| | 242 | + ) ); |
| 234 | 243 | } |
| 235 | 244 | return true; |
| 236 | 245 | } |
| Index: trunk/extensions/ArticleFeedback/api/ApiArticleFeedback.php |
| — | — | @@ -38,57 +38,60 @@ |
| 39 | 39 | } |
| 40 | 40 | |
| 41 | 41 | $dbw = wfGetDB( DB_MASTER ); |
| 42 | | - |
| 43 | | - // Query the latest ratings by this user for this page, |
| | 42 | + |
| | 43 | + $pageId = $params['pageid']; |
| | 44 | + $revisionId = $params['revid']; |
| | 45 | + |
| | 46 | + // Build array( rating ID => rating value ) |
| | 47 | + $ratings = array(); |
| | 48 | + foreach ( $wgArticleFeedbackRatings as $ratingID ) { |
| | 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, |
| 44 | 55 | // possibly for an older revision |
| 45 | | - // Select from the master to prevent replag-induced bugs |
| | 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. |
| 46 | 58 | $res = $dbw->select( |
| 47 | 59 | 'article_feedback', |
| 48 | 60 | array( 'aa_rating_id', 'aa_rating_value', 'aa_revision' ), |
| 49 | 61 | array( |
| 50 | | - 'aa_user_id' => $wgUser->getId(), |
| 51 | 62 | 'aa_user_text' => $wgUser->getName(), |
| 52 | 63 | 'aa_page_id' => $params['pageid'], |
| 53 | 64 | 'aa_rating_id' => $wgArticleFeedbackRatings, |
| 54 | 65 | 'aa_user_anon_token' => $token, |
| | 66 | + 'aa_id < ' . intval( $id ) |
| 55 | 67 | ), |
| 56 | 68 | __METHOD__, |
| 57 | 69 | array( |
| 58 | | - 'ORDER BY' => 'aa_revision DESC', |
| | 70 | + 'ORDER BY' => 'aa_id DESC', |
| 59 | 71 | 'LIMIT' => count( $wgArticleFeedbackRatings ), |
| 60 | 72 | ) |
| 61 | 73 | ); |
| 62 | | - |
| 63 | 74 | $lastRatings = array(); |
| 64 | | - |
| 65 | 75 | foreach ( $res as $row ) { |
| 66 | 76 | $lastRatings[$row->aa_rating_id]['value'] = $row->aa_rating_value; |
| 67 | 77 | $lastRatings[$row->aa_rating_id]['revision'] = $row->aa_revision; |
| 68 | 78 | } |
| 69 | | - |
| 70 | | - $pageId = $params['pageid']; |
| 71 | | - $revisionId = $params['revid']; |
| 72 | | - |
| 73 | | - foreach( $wgArticleFeedbackRatings as $rating ) { |
| 74 | | - $lastRating = false; |
| 75 | | - $lastRevision = false; |
| | 79 | + |
| | 80 | + foreach ( $wgArticleFeedbackRatings as $rating ) { |
| | 81 | + $lastPageRating = false; |
| | 82 | + $lastRevRating = false; |
| 76 | 83 | if ( isset( $lastRatings[$rating] ) ) { |
| 77 | | - $lastRating = intval( $lastRatings[$rating]['value'] ); |
| 78 | | - $lastRevision = intval( $lastRatings[$rating]['revision'] ); |
| | 84 | + $lastPageRating = intval( $lastRatings[$rating]['value'] ); |
| | 85 | + if ( intval( $lastRatings[$rating]['revision'] ) == $revisionId ) { |
| | 86 | + $lastRevRating = $lastPageRating; |
| | 87 | + } |
| 79 | 88 | } |
| 80 | | - |
| 81 | | - $thisRating = false; |
| 82 | | - if ( isset( $params["r{$rating}"] ) ) { |
| 83 | | - $thisRating = intval( $params["r{$rating}"] ); |
| 84 | | - } |
| 85 | | - |
| 86 | | - $this->insertRevisionRating( $pageId, $revisionId, $lastRevision, $rating, ( $thisRating - $lastRating ), |
| 87 | | - $thisRating, $lastRating |
| 88 | | - ); |
| | 89 | + $thisRating = intval( $params["r{$rating}"] ); |
| 89 | 90 | |
| 90 | | - $this->insertPageRating( $pageId, $rating, ( $thisRating - $lastRating ), $thisRating, $lastRating ); |
| 91 | | - |
| 92 | | - $this->insertUserRatings( $pageId, $revisionId, $wgUser, $token, $rating, $thisRating, $params['bucket'] ); |
| | 91 | + // Update counter tables |
| | 92 | + $this->insertRevisionRating( $pageId, $revisionId, $rating, $thisRating - $lastRevRating, |
| | 93 | + $thisRating, $lastRevRating |
| | 94 | + ); |
| | 95 | + $this->insertPageRating( $pageId, $rating, $thisRating - $lastPageRating, $thisRating, $lastPageRating ); |
| 93 | 96 | } |
| 94 | 97 | |
| 95 | 98 | $this->insertProperties( $revisionId, $wgUser, $token, $params ); |
| — | — | @@ -157,13 +160,12 @@ |
| 158 | 161 | * |
| 159 | 162 | * @param $pageId Integer: Page Id |
| 160 | 163 | * @param $revisionId Integer: Revision Id |
| 161 | | - * @param $lastRevision Integer: Revision Id of last rating |
| 162 | 164 | * @param $ratingId Integer: Rating Id |
| 163 | 165 | * @param $updateAddition Integer: Difference between user's last rating (if applicable) |
| 164 | 166 | * @param $thisRating Integer|Boolean: Value of the Rating |
| 165 | 167 | * @param $lastRating Integer|Boolean: Value of the last Rating |
| 166 | 168 | */ |
| 167 | | - private function insertRevisionRating( $pageId, $revisionId, $lastRevision, $ratingId, $updateAddition, $thisRating, $lastRating ) { |
| | 169 | + private function insertRevisionRating( $pageId, $revisionId, $ratingId, $updateAddition, $thisRating, $lastRating ) { |
| 168 | 170 | $dbw = wfGetDB( DB_MASTER ); |
| 169 | 171 | |
| 170 | 172 | // Try to insert a new "totals" row for this page,rev,rating set |
| — | — | @@ -179,58 +181,21 @@ |
| 180 | 182 | __METHOD__, |
| 181 | 183 | array( 'IGNORE' ) |
| 182 | 184 | ); |
| 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 | | - } |
| | 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 | + ); |
| 235 | 200 | } |
| 236 | 201 | /** |
| 237 | 202 | * Calculate the difference between the previous rating and this one |
| — | — | @@ -247,55 +212,45 @@ |
| 248 | 213 | } |
| 249 | 214 | |
| 250 | 215 | /** |
| 251 | | - * Inserts (or Updates, where appropriate) the users ratings for a specific revision |
| | 216 | + * Inserts the user's ratings for a specific revision |
| 252 | 217 | * |
| 253 | 218 | * @param $pageId Integer: Page Id |
| 254 | 219 | * @param $revisionId Integer: Revision Id |
| 255 | 220 | * @param $user User: Current User object |
| 256 | 221 | * @param $token Array: Token if necessary |
| 257 | | - * @param $ratingId Integer: Rating Id |
| 258 | | - * @param $ratingValue Integer: Value of the Rating |
| | 222 | + * @param $ratings Array: Keys are rating IDs, values are rating values |
| 259 | 223 | * @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 |
| 260 | 225 | */ |
| 261 | | - private function insertUserRatings( $pageId, $revisionId, $user, $token, $ratingId, $ratingValue, $bucket ) { |
| | 226 | + private function insertUserRatings( $pageId, $revisionId, $user, $token, $ratings, $bucket ) { |
| 262 | 227 | $dbw = wfGetDB( DB_MASTER ); |
| 263 | 228 | |
| 264 | 229 | $timestamp = $dbw->timestamp(); |
| 265 | | - |
| 266 | | - $dbw->insert( |
| 267 | | - 'article_feedback', |
| 268 | | - array( |
| | 230 | + |
| | 231 | + $rows = array(); |
| | 232 | + foreach ( $ratings as $ratingID => $ratingValue ) { |
| | 233 | + $rows[] = array( |
| 269 | 234 | 'aa_page_id' => $pageId, |
| 270 | 235 | 'aa_user_id' => $user->getId(), |
| 271 | 236 | 'aa_user_text' => $user->getName(), |
| 272 | 237 | 'aa_user_anon_token' => $token, |
| 273 | 238 | 'aa_revision' => $revisionId, |
| 274 | 239 | 'aa_timestamp' => $timestamp, |
| 275 | | - 'aa_rating_id' => $ratingId, |
| | 240 | + 'aa_rating_id' => $ratingID, |
| 276 | 241 | 'aa_rating_value' => $ratingValue, |
| 277 | 242 | '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__ |
| 298 | 243 | ); |
| 299 | 244 | } |
| | 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(); |
| 300 | 255 | } |
| 301 | 256 | |
| 302 | 257 | /** |
| Index: trunk/extensions/ArticleFeedback/api/ApiQueryArticleFeedback.php |
| — | — | @@ -187,14 +187,13 @@ |
| 188 | 188 | array( |
| 189 | 189 | 'aa_page_id' => $params['pageid'], |
| 190 | 190 | 'aa_rating_id' => $wgArticleFeedbackRatings, |
| 191 | | - 'aa_user_id' => $wgUser->getId(), |
| 192 | 191 | 'aa_user_text' => $wgUser->getName(), |
| 193 | 192 | 'aa_user_anon_token' => $this->getAnonToken( $params ), |
| 194 | 193 | ), |
| 195 | 194 | __METHOD__, |
| 196 | 195 | array( |
| 197 | 196 | 'LIMIT' => count( $wgArticleFeedbackRatings ), |
| 198 | | - 'ORDER BY' => 'aa_revision DESC', |
| | 197 | + 'ORDER BY' => 'aa_id DESC', |
| 199 | 198 | ), |
| 200 | 199 | array( |
| 201 | 200 | 'article_feedback_ratings' => array( 'LEFT JOIN', array( 'aar_id=aa_rating_id' ) ) |