| Index: trunk/phase3/maintenance/postgres/tables.sql |
| — | — | @@ -11,7 +11,7 @@ |
| 12 | 12 | SET client_min_messages = 'ERROR'; |
| 13 | 13 | |
| 14 | 14 | CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0; |
| 15 | | -CREATE TABLE "user" ( |
| | 15 | +CREATE TABLE mwuser ( -- replace reserved word 'user' |
| 16 | 16 | user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'), |
| 17 | 17 | user_name TEXT NOT NULL UNIQUE, |
| 18 | 18 | user_real_name TEXT, |
| — | — | @@ -26,20 +26,20 @@ |
| 27 | 27 | user_touched TIMESTAMPTZ, |
| 28 | 28 | user_registration TIMESTAMPTZ |
| 29 | 29 | ); |
| 30 | | -CREATE INDEX user_email_token_idx ON "user" (user_email_token); |
| | 30 | +CREATE INDEX user_email_token_idx ON mwuser (user_email_token); |
| 31 | 31 | |
| 32 | 32 | -- Create a dummy user to satisfy fk contraints especially with revisions |
| 33 | | -INSERT INTO "user" |
| | 33 | +INSERT INTO mwuser |
| 34 | 34 | VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now()); |
| 35 | 35 | |
| 36 | 36 | CREATE TABLE user_groups ( |
| 37 | | - ug_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE CASCADE, |
| | 37 | + ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, |
| 38 | 38 | ug_group TEXT NOT NULL |
| 39 | 39 | ); |
| 40 | 40 | CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group); |
| 41 | 41 | |
| 42 | 42 | CREATE TABLE user_newtalk ( |
| 43 | | - user_id INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, |
| | 43 | + user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, |
| 44 | 44 | user_ip CIDR NULL |
| 45 | 45 | ); |
| 46 | 46 | CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id); |
| — | — | @@ -86,7 +86,7 @@ |
| 87 | 87 | rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, |
| 88 | 88 | rev_text_id INTEGER NULL, -- FK |
| 89 | 89 | rev_comment TEXT, |
| 90 | | - rev_user INTEGER NOT NULL REFERENCES "user"(user_id), |
| | 90 | + rev_user INTEGER NOT NULL REFERENCES mwuser(user_id), |
| 91 | 91 | rev_user_text TEXT NOT NULL, |
| 92 | 92 | rev_timestamp TIMESTAMPTZ NOT NULL, |
| 93 | 93 | rev_minor_edit CHAR NOT NULL DEFAULT '0', |
| — | — | @@ -99,7 +99,7 @@ |
| 100 | 100 | |
| 101 | 101 | |
| 102 | 102 | CREATE SEQUENCE text_old_id_val; |
| 103 | | -CREATE TABLE "text" ( |
| | 103 | +CREATE TABLE pagecontent ( -- replaces reserved word 'text' |
| 104 | 104 | old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'), |
| 105 | 105 | old_text TEXT, |
| 106 | 106 | old_flags TEXT |
| — | — | @@ -111,7 +111,7 @@ |
| 112 | 112 | ar_title TEXT NOT NULL, |
| 113 | 113 | ar_text TEXT, |
| 114 | 114 | ar_comment TEXT, |
| 115 | | - ar_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 115 | + ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 116 | 116 | ar_user_text TEXT NOT NULL, |
| 117 | 117 | ar_timestamp TIMESTAMPTZ NOT NULL, |
| 118 | 118 | ar_minor_edit CHAR NOT NULL DEFAULT '0', |
| — | — | @@ -203,8 +203,8 @@ |
| 204 | 204 | CREATE TABLE ipblocks ( |
| 205 | 205 | ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), |
| 206 | 206 | ipb_address CIDR NULL, |
| 207 | | - ipb_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| 208 | | - ipb_by INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, |
| | 207 | + ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| | 208 | + ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, |
| 209 | 209 | ipb_reason TEXT NOT NULL, |
| 210 | 210 | ipb_timestamp TIMESTAMPTZ NOT NULL, |
| 211 | 211 | ipb_auto CHAR NOT NULL DEFAULT '0', |
| — | — | @@ -230,7 +230,7 @@ |
| 231 | 231 | img_major_mime TEXT DEFAULT 'unknown', |
| 232 | 232 | img_minor_mime TEXT DEFAULT 'unknown', |
| 233 | 233 | img_description TEXT NOT NULL, |
| 234 | | - img_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 234 | + img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 235 | 235 | img_user_text TEXT NOT NULL, |
| 236 | 236 | img_timestamp TIMESTAMPTZ |
| 237 | 237 | ); |
| — | — | @@ -245,7 +245,7 @@ |
| 246 | 246 | oi_height SMALLINT NOT NULL, |
| 247 | 247 | oi_bits SMALLINT NOT NULL, |
| 248 | 248 | oi_description TEXT, |
| 249 | | - oi_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 249 | + oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 250 | 250 | oi_user_text TEXT NOT NULL, |
| 251 | 251 | oi_timestamp TIMESTAMPTZ NOT NULL |
| 252 | 252 | ); |
| — | — | @@ -258,7 +258,7 @@ |
| 259 | 259 | fa_archive_name TEXT, |
| 260 | 260 | fa_storage_group VARCHAR(16), |
| 261 | 261 | fa_storage_key CHAR(64), |
| 262 | | - fa_deleted_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 262 | + fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 263 | 263 | fa_deleted_timestamp TIMESTAMPTZ NOT NULL, |
| 264 | 264 | fa_deleted_reason TEXT, |
| 265 | 265 | fa_size SMALLINT NOT NULL, |
| — | — | @@ -270,7 +270,7 @@ |
| 271 | 271 | fa_major_mime TEXT DEFAULT 'unknown', |
| 272 | 272 | fa_minor_mime TEXT DEFAULT 'unknown', |
| 273 | 273 | fa_description TEXT NOT NULL, |
| 274 | | - fa_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 274 | + fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 275 | 275 | fa_user_text TEXT NOT NULL, |
| 276 | 276 | fa_timestamp TIMESTAMPTZ |
| 277 | 277 | ); |
| — | — | @@ -285,7 +285,7 @@ |
| 286 | 286 | rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), |
| 287 | 287 | rc_timestamp TIMESTAMPTZ NOT NULL, |
| 288 | 288 | rc_cur_time TIMESTAMPTZ NOT NULL, |
| 289 | | - rc_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 289 | + rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 290 | 290 | rc_user_text TEXT NOT NULL, |
| 291 | 291 | rc_namespace SMALLINT NOT NULL, |
| 292 | 292 | rc_title TEXT NOT NULL, |
| — | — | @@ -310,7 +310,7 @@ |
| 311 | 311 | |
| 312 | 312 | |
| 313 | 313 | CREATE TABLE watchlist ( |
| 314 | | - wl_user INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, |
| | 314 | + wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, |
| 315 | 315 | wl_namespace SMALLINT NOT NULL DEFAULT 0, |
| 316 | 316 | wl_title TEXT NOT NULL, |
| 317 | 317 | wl_notificationtimestamp TIMESTAMPTZ |
| — | — | @@ -366,7 +366,7 @@ |
| 367 | 367 | log_type TEXT NOT NULL, |
| 368 | 368 | log_action TEXT NOT NULL, |
| 369 | 369 | log_timestamp TIMESTAMPTZ NOT NULL, |
| 370 | | - log_user INTEGER REFERENCES "user"(user_id) ON DELETE SET NULL, |
| | 370 | + log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL, |
| 371 | 371 | log_namespace SMALLINT NOT NULL, |
| 372 | 372 | log_title TEXT NOT NULL, |
| 373 | 373 | log_comment TEXT, |
| — | — | @@ -418,8 +418,8 @@ |
| 419 | 419 | FOR EACH ROW EXECUTE PROCEDURE ts2_page_title(); |
| 420 | 420 | |
| 421 | 421 | |
| 422 | | -ALTER TABLE text ADD textvector tsvector; |
| 423 | | -CREATE INDEX ts2_page_text ON text USING gist(textvector); |
| | 422 | +ALTER TABLE pagecontent ADD textvector tsvector; |
| | 423 | +CREATE INDEX ts2_page_text ON pagecontent USING gist(textvector); |
| 424 | 424 | CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS |
| 425 | 425 | $mw$ |
| 426 | 426 | BEGIN |
| — | — | @@ -432,7 +432,7 @@ |
| 433 | 433 | END; |
| 434 | 434 | $mw$; |
| 435 | 435 | |
| 436 | | -CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text |
| | 436 | +CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent |
| 437 | 437 | FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); |
| 438 | 438 | |
| 439 | 439 | CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS |
| Index: trunk/phase3/includes/SearchPostgres.php |
| — | — | @@ -98,8 +98,8 @@ |
| 99 | 99 | $match = $this->parseQuery( $filteredTerm, $fulltext ); |
| 100 | 100 | |
| 101 | 101 | $query = "SELECT page_id, page_namespace, page_title, old_text AS page_text ". |
| 102 | | - "FROM page p, revision r, text t WHERE p.page_latest = r.rev_id " . |
| 103 | | - "AND r.rev_text_id = t.old_id AND $fulltext @@ to_tsquery('$match')"; |
| | 102 | + "FROM page p, revision r, pagecontent c WHERE p.page_latest = r.rev_id " . |
| | 103 | + "AND r.rev_text_id = c.old_id AND $fulltext @@ to_tsquery('$match')"; |
| 104 | 104 | |
| 105 | 105 | ## Redirects |
| 106 | 106 | if (! $this->showRedirects) |
| Index: trunk/phase3/includes/DatabasePostgres.php |
| — | — | @@ -378,16 +378,12 @@ |
| 379 | 379 | } |
| 380 | 380 | |
| 381 | 381 | function tableName( $name ) { |
| 382 | | - # Replace backticks into double quotes |
| 383 | | - $name = strtr($name,'`','"'); |
| 384 | | - |
| 385 | | - # Now quote PG reserved keywords |
| | 382 | + # Replace reserved words with better ones |
| 386 | 383 | switch( $name ) { |
| 387 | 384 | case 'user': |
| 388 | | - case 'old': |
| 389 | | - case 'group': |
| 390 | | - return '"' . $name . '"'; |
| 391 | | - |
| | 385 | + return 'mwuser'; |
| | 386 | + case 'text': |
| | 387 | + return 'pagecontent'; |
| 392 | 388 | default: |
| 393 | 389 | return $name; |
| 394 | 390 | } |