| Index: trunk/extensions/ArticleFeedback/sql/ArticleFeedback.sql |
| — | — | @@ -32,10 +32,13 @@ |
| 33 | 33 | aa_rating_value int unsigned NOT NULL, |
| 34 | 34 | -- Which rating widget the user was given. Default of 0 is the "old" design |
| 35 | 35 | aa_design_bucket int unsigned NOT NULL DEFAULT 0, |
| | 36 | + aa_design_bucket int unsigned NOT NULL DEFAULT 0, |
| 36 | 37 | -- 1 vote per user per revision |
| 37 | 38 | PRIMARY KEY (aa_revision, aa_user_text, aa_rating_id, aa_user_anon_token) |
| 38 | 39 | ) /*$wgDBTableOptions*/; |
| 39 | 40 | CREATE INDEX /*i*/aa_user_page_revision ON /*_*/article_feedback (aa_user_id, aa_page_id, aa_revision); |
| | 41 | +-- Create an index on the article_feedback.aa_timestamp field |
| | 42 | +CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/article_feedback (aa_timestamp); |
| 40 | 43 | |
| 41 | 44 | -- Aggregate rating table for a page |
| 42 | 45 | CREATE TABLE IF NOT EXISTS /*_*/article_feedback_pages ( |
| — | — | @@ -83,3 +86,26 @@ |
| 84 | 87 | afp_value_text varbinary(255) DEFAULT '' NOT NULL |
| 85 | 88 | ) /*$wgDBTableOptions*/; |
| 86 | 89 | CREATE UNIQUE INDEX /*i*/afp_rating_key ON /*_*/article_feedback_properties (afp_revision, afp_user_text, afp_user_anon_token, afp_key); |
| | 90 | + |
| | 91 | +CREATE TABLE IF NOT EXISTS /*_*/article_feedback_stats ( |
| | 92 | + afs_page_id integer unsigned NOT NULL, |
| | 93 | + -- data point to be used for ordering this data |
| | 94 | + afs_orderable_data double unsigned NOT NULL, |
| | 95 | + -- json object of stat data |
| | 96 | + afs_data varbinary(255) NOT NULL, |
| | 97 | + afs_stats_type_id integer unsigned NOT NULL, |
| | 98 | + -- timestamp of insertion job |
| | 99 | + afs_ts binary(14) NOT NULL |
| | 100 | +) /*$wgDBTableOptions*/; |
| | 101 | +CREATE UNIQUE INDEX /*i*/afs_type_ts_page ON /*_*/article_feedback_stats(afs_stats_type_id, afs_ts, afs_page_id); |
| | 102 | +CREATE INDEX /*i*/ afs_type_ts_orderable ON /*_*/article_feedback_stats (afs_stats_type_id, afs_ts, afs_orderable_data); |
| | 103 | + |
| | 104 | +CREATE TABLE IF NOT EXISTS /*_*/article_feedback_stats_types ( |
| | 105 | + afst_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| | 106 | + afst_type varbinary(255) NOT NULL |
| | 107 | +) /*$wgDBTableOptions*/; |
| | 108 | +CREATE UNIQUE INDEX /*i*/afst_type ON /*_*/article_feedback_stats_types( afst_type ); |
| | 109 | + |
| | 110 | +-- Pre-populate table with stat types |
| | 111 | +INSERT INTO article_feedback_stats_types ( afst_type ) VALUES ( 'highs_and_lows' ); |
| | 112 | +INSERT INTO article_feedback_stats_types ( afst_type ) VALUES ( 'problems' ); |