| Index: branches/wmf/1.17wmf1/extensions/ArticleFeedback/sql/AddStatsHighsLowsTable.sql |
| — | — | @@ -1,11 +0,0 @@ |
| 2 | | -CREATE TABLE IF NOT EXISTS /*_*/article_feedback_stats_highs_lows ( |
| 3 | | - afshl_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| 4 | | - afshl_page_id integer unsigned NOT NULL, |
| 5 | | - -- combined average rating |
| 6 | | - afshl_avg_overall double unsigned NOT NULL, |
| 7 | | - -- json object of rating key => avg value |
| 8 | | - afshl_avg_ratings varbinary(255) NOT NULL, |
| 9 | | - -- timestamp of insertion job |
| 10 | | - afshl_ts binary(14) NOT NULL |
| 11 | | -) /*$wgDBTableOptions*/; |
| 12 | | -CREATE INDEX /*i*/ afshl_ts_avg_overall ON /*_*/article_feedback_stats_highs_lows (afshl_ts, afshl_avg_overall); |
| Index: branches/wmf/1.17wmf1/extensions/ArticleFeedback/sql/ArticleFeedback.sql |
| — | — | @@ -35,6 +35,8 @@ |
| 36 | 36 | PRIMARY KEY (aa_revision, aa_user_text, aa_rating_id, aa_user_anon_token) |
| 37 | 37 | ) /*$wgDBTableOptions*/; |
| 38 | 38 | CREATE INDEX /*i*/aa_user_page_revision ON /*_*/article_feedback (aa_user_id, aa_page_id, aa_revision); |
| | 39 | +-- Create an index on the article_feedback.aa_timestamp field |
| | 40 | +CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/article_feedback (aa_timestamp); |
| 39 | 41 | |
| 40 | 42 | -- Aggregate rating table for a page |
| 41 | 43 | CREATE TABLE IF NOT EXISTS /*_*/article_feedback_pages ( |
| — | — | @@ -82,3 +84,26 @@ |
| 83 | 85 | afp_value_text varbinary(255) DEFAULT '' NOT NULL |
| 84 | 86 | ) /*$wgDBTableOptions*/; |
| 85 | 87 | CREATE UNIQUE INDEX /*i*/afp_rating_key ON /*_*/article_feedback_properties (afp_revision, afp_user_text, afp_user_anon_token, afp_key); |
| | 88 | + |
| | 89 | +CREATE TABLE IF NOT EXISTS /*_*/article_feedback_stats ( |
| | 90 | + afs_page_id integer unsigned NOT NULL, |
| | 91 | + -- data point to be used for ordering this data |
| | 92 | + afs_orderable_data double unsigned NOT NULL, |
| | 93 | + -- json object of stat data |
| | 94 | + afs_data varbinary(255) NOT NULL, |
| | 95 | + afs_stats_type_id integer unsigned NOT NULL, |
| | 96 | + -- timestamp of insertion job |
| | 97 | + afs_ts binary(14) NOT NULL |
| | 98 | +) /*$wgDBTableOptions*/; |
| | 99 | +CREATE UNIQUE INDEX /*i*/afs_type_ts_page ON /*_*/article_feedback_stats(afs_stats_type_id, afs_ts, afs_page_id); |
| | 100 | +CREATE INDEX /*i*/ afs_type_ts_orderable ON /*_*/article_feedback_stats (afs_stats_type_id, afs_ts, afs_orderable_data); |
| | 101 | + |
| | 102 | +CREATE TABLE IF NOT EXISTS /*_*/article_feedback_stats_types ( |
| | 103 | + afst_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| | 104 | + afst_type varbinary(255) NOT NULL |
| | 105 | +) /*$wgDBTableOptions*/; |
| | 106 | +CREATE UNIQUE INDEX /*i*/afst_type ON /*_*/article_feedback_stats_types( afst_type ); |
| | 107 | + |
| | 108 | +-- Pre-populate table with stat types |
| | 109 | +INSERT INTO article_feedback_stats_types ( afst_type ) VALUES ( 'highs_and_lows' ); |
| | 110 | +INSERT INTO article_feedback_stats_types ( afst_type ) VALUES ( 'problems' ); |