| Index: trunk/extensions/LiquidThreads/lqt.sql |
| — | — | @@ -1,5 +1,5 @@ |
| 2 | 2 | CREATE TABLE /*$wgDBprefix*/thread ( |
| 3 | | - thread_id int(8) unsigned NOT NULL auto_increment, |
| | 3 | + thread_id int(8) unsigned NOT NULL PRIMARY KEY auto_increment, |
| 4 | 4 | thread_root int(8) unsigned UNIQUE NOT NULL, |
| 5 | 5 | thread_ancestor int(8) unsigned NOT NULL, |
| 6 | 6 | thread_parent int(8) unsigned NULL, |
| — | — | @@ -19,29 +19,27 @@ |
| 20 | 20 | |
| 21 | 21 | -- Special thread types (deleted/move trace/normal) |
| 22 | 22 | thread_type int(4) unsigned NOT NULL default 0, |
| 23 | | - |
| | 23 | + |
| 24 | 24 | -- Sort key |
| 25 | 25 | thread_sortkey varchar(255) NOT NULL default '', |
| 26 | | - |
| | 26 | + |
| 27 | 27 | -- Reply count, -1 means uninitialised. |
| 28 | 28 | thread_replies int(8) DEFAULT -1, |
| 29 | | - |
| | 29 | + |
| 30 | 30 | -- Signature |
| 31 | 31 | thread_signature TINYBLOB NULL, |
| 32 | 32 | |
| 33 | | - PRIMARY KEY thread_id (thread_id), |
| 34 | | - UNIQUE INDEX thread_root_page (thread_root), |
| 35 | | - INDEX thread_ancestor (thread_ancestor, thread_parent), |
| 36 | | - INDEX thread_article_title (thread_article_namespace, thread_article_title, thread_sortkey), |
| 37 | | - INDEX thread_article (thread_article_id, thread_sortkey), |
| 38 | | - INDEX thread_modified (thread_modified), |
| 39 | | - INDEX thread_created (thread_created), |
| 40 | | - INDEX thread_summary_page (thread_summary_page), |
| 41 | | - INDEX (thread_author_id,thread_author_name), |
| 42 | | - INDEX (thread_sortkey) |
| | 33 | + UNIQUE (thread_root) |
| 43 | 34 | ) /*$wgDBTableOptions*/; |
| | 35 | +CREATE INDEX thread_ancestor ON /*$wgDBprefix*/thread (thread_ancestor, thread_parent); |
| | 36 | +CREATE INDEX thread_article_title ON /*$wgDBprefix*/thread (thread_article_namespace, thread_article_title, thread_sortkey); |
| | 37 | +CREATE INDEX thread_article ON /*$wgDBprefix*/thread (thread_article_id, thread_sortkey); |
| | 38 | +CREATE INDEX thread_modified ON /*$wgDBprefix*/thread (thread_modified); |
| | 39 | +CREATE INDEX thread_created ON /*$wgDBprefix*/thread (thread_created); |
| | 40 | +CREATE INDEX thread_summary_page ON /*$wgDBprefix*/thread (thread_summary_page); |
| | 41 | +CREATE INDEX thread_author_name ON /*$wgDBprefix*/thread (thread_author_id,thread_author_name); |
| | 42 | +CREATE INDEX thread_sortkey ON /*$wgDBprefix*/thread (thread_sortkey); |
| 44 | 43 | |
| 45 | | - |
| 46 | 44 | -- Old storage table for "historical" (i.e. non-current) threads |
| 47 | 45 | -- Now superseded by thread_history. |
| 48 | 46 | CREATE TABLE /*$wgDBprefix*/historical_thread ( |
| — | — | @@ -52,7 +50,8 @@ |
| 53 | 51 | hthread_contents BLOB NOT NULL, |
| 54 | 52 | hthread_change_type int(4) unsigned NOT NULL, |
| 55 | 53 | hthread_change_object int(8) unsigned NULL, |
| 56 | | - PRIMARY KEY hthread_id_revision (hthread_id, hthread_revision) |
| | 54 | + |
| | 55 | + PRIMARY KEY (hthread_id, hthread_revision) |
| 57 | 56 | ) /*$wgDBTableOptions*/; |
| 58 | 57 | |
| 59 | 58 | CREATE TABLE /*$wgDBprefix*/user_message_state ( |
| — | — | @@ -60,32 +59,30 @@ |
| 61 | 60 | ums_thread int(8) unsigned NOT NULL, |
| 62 | 61 | ums_read_timestamp varbinary(14), |
| 63 | 62 | |
| 64 | | - PRIMARY KEY (ums_user, ums_thread), |
| 65 | | - KEY (ums_user,ums_read_timestamp) |
| | 63 | + PRIMARY KEY (ums_user, ums_thread) |
| 66 | 64 | ) /*$wgDBTableOptions*/; |
| | 65 | +CREATE INDEX ums_user_read ON /*$wgDBprefix*/user_message_state (ums_user,ums_read_timestamp); |
| 67 | 66 | |
| 68 | 67 | -- "New" storage location for history data. |
| 69 | 68 | CREATE TABLE /*_*/thread_history ( |
| 70 | | - th_id int unsigned NOT NULL auto_increment, |
| | 69 | + th_id int unsigned NOT NULL PRIMARY KEY auto_increment, |
| 71 | 70 | th_thread int unsigned NOT NULL, |
| 72 | | - |
| | 71 | + |
| 73 | 72 | th_timestamp varbinary(14) NOT NULL, |
| 74 | | - |
| | 73 | + |
| 75 | 74 | th_user int unsigned NOT NULL, |
| 76 | 75 | th_user_text varchar(255) NOT NULL, |
| 77 | | - |
| | 76 | + |
| 78 | 77 | th_change_type int unsigned NOT NULL, |
| 79 | 78 | th_change_object int unsigned NOT NULL, |
| 80 | 79 | th_change_comment TINYTEXT NOT NULL, |
| 81 | | - |
| | 80 | + |
| 82 | 81 | -- Actual content, stored as a serialised thread row. |
| 83 | | - th_content LONGBLOB NOT NULL, |
| 84 | | - |
| 85 | | - PRIMARY KEY (th_id), |
| 86 | | - KEY (th_thread,th_timestamp), |
| 87 | | - KEY (th_timestamp,th_thread), |
| 88 | | - KEY (th_user,th_user_text) |
| | 82 | + th_content LONGBLOB NOT NULL |
| 89 | 83 | ) /*$wgDBTableOptions*/; |
| | 84 | +CREATE INDEX th_thread_timestamp ON /*$wgDBprefix*/thread_history (th_thread,th_timestamp); |
| | 85 | +CREATE INDEX th_timestamp_thread ON /*$wgDBprefix*/thread_history (th_timestamp,th_thread); |
| | 86 | +CREATE INDEX th_user_text ON /*$wgDBprefix*/thread_history (th_user,th_user_text); |
| 90 | 87 | |
| 91 | 88 | -- Storage for "pending" relationships from import |
| 92 | 89 | CREATE TABLE /*_*/thread_pending_relationship ( |
| — | — | @@ -103,7 +100,7 @@ |
| 104 | 101 | tr_user_text varbinary(255) NOT NULL, |
| 105 | 102 | tr_type varbinary(64) NOT NULL, |
| 106 | 103 | tr_value int NOT NULL, |
| 107 | | - |
| 108 | | - PRIMARY KEY (tr_thread,tr_user,tr_user_text,tr_type,tr_value), |
| 109 | | - KEY (tr_user,tr_user_text,tr_type,tr_value) |
| | 104 | + |
| | 105 | + PRIMARY KEY (tr_thread,tr_user,tr_user_text,tr_type,tr_value) |
| 110 | 106 | ) /*$wgDBTableOptions*/; |
| | 107 | +CREATE INDEX tr_user_text_value ON /*$wgDBprefix*/thread_reaction (tr_user,tr_user_text,tr_type,tr_value); |