summaryrefslogtreecommitdiff
path: root/maintenance/postgres
diff options
context:
space:
mode:
Diffstat (limited to 'maintenance/postgres')
-rw-r--r--maintenance/postgres/archives/patch-user_former_groups.sql5
-rw-r--r--maintenance/postgres/compare_schemas.pl6
-rw-r--r--maintenance/postgres/mediawiki_mysql2postgres.pl26
-rw-r--r--maintenance/postgres/tables.sql41
4 files changed, 50 insertions, 28 deletions
diff --git a/maintenance/postgres/archives/patch-user_former_groups.sql b/maintenance/postgres/archives/patch-user_former_groups.sql
new file mode 100644
index 00000000..1ba011e3
--- /dev/null
+++ b/maintenance/postgres/archives/patch-user_former_groups.sql
@@ -0,0 +1,5 @@
+CREATE TABLE user_former_groups (
+ ufg_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ ufg_group TEXT NOT NULL
+);
+CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);
diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl
index 7e3cdf71..18210fcf 100644
--- a/maintenance/postgres/compare_schemas.pl
+++ b/maintenance/postgres/compare_schemas.pl
@@ -94,7 +94,7 @@ sub parse_sql {
next if /^\s*\-\-/ or /^\s+$/;
s/\s*\-\- [\w ]+$//;
chomp;
-
+
if (/CREATE\s*TABLE/i) {
if (m{^CREATE TABLE /\*_\*/(\w+) \($}) {
$table = $1;
@@ -179,6 +179,10 @@ while (<$newfh>) {
next if /^CREATE TRIGGER/ or /^ FOR EACH ROW/;
next if /^INSERT INTO/ or /^ VALUES \(/;
next if /^ALTER TABLE/;
+ next if /^DROP SEQUENCE/;
+ next if /^DROP FUNCTION/;
+
+
chomp;
if (/^\$mw\$;?$/) {
diff --git a/maintenance/postgres/mediawiki_mysql2postgres.pl b/maintenance/postgres/mediawiki_mysql2postgres.pl
index 2b2bf50e..16012762 100644
--- a/maintenance/postgres/mediawiki_mysql2postgres.pl
+++ b/maintenance/postgres/mediawiki_mysql2postgres.pl
@@ -1,7 +1,7 @@
#!/usr/bin/perl
## Convert data from a MySQL mediawiki database into a Postgres mediawiki database
-## svn: $Id: mediawiki_mysql2postgres.pl 65542 2010-04-26 13:46:04Z demon $
+## svn: $Id: mediawiki_mysql2postgres.pl 86721 2011-04-22 18:47:17Z mah $
## NOTE: It is probably easier to dump your wiki using maintenance/dumpBackup.php
## and then import it with maintenance/importDump.php
@@ -181,7 +181,7 @@ $MYSQLSOCKET and $conninfo .= "\n-- socket $MYSQLSOCKET";
print qq{
-- Dump of MySQL Mediawiki tables for import into a Postgres Mediawiki schema
-- Performed by the program: $0
--- Version: $VERSION (subversion }.q{$LastChangedRevision: 65542 $}.qq{)
+-- Version: $VERSION (subversion }.q{$LastChangedRevision: 86721 $}.qq{)
-- Author: Greg Sabino Mullane <greg\@turnstep.com> Comments welcome
--
-- This file was created: $now
@@ -279,8 +279,8 @@ for my $t (@torder, 'objectcache', 'querycache') {
}
print "\n\n";
-print qq{-- Temporarily rename pagecontent to "text"\n};
-print qq{ALTER TABLE pagecontent RENAME TO "text";\n\n};
+print qq{-- Temporarily rename pagecontent to "${table_prefix}text"\n};
+print qq{ALTER TABLE pagecontent RENAME TO "${table_prefix}text";\n\n};
print qq{-- Allow rc_ip to contain empty string, will convert at end\n};
print qq{ALTER TABLE recentchanges ALTER rc_ip TYPE text USING host(rc_ip);\n\n};
@@ -304,9 +304,9 @@ INSERT INTO page VALUES (0,-1,'Dummy Page','',0,0,0,default,now(),0,10);
if (length $table_prefix) {
print qq{\n\n-- Temporarily renaming tables to accomodate the table_prefix "$table_prefix"\n\n};
for my $t (@torder) {
- next if $t eq '---';
+ next if $t eq '---' or $t eq 'text' or $t eq 'user';
my $tname = $special{$t}||$t;
- printf qq{ALTER TABLE %-18s RENAME TO "${table_prefix}$tname"\n}, qq{"$tname"};
+ printf qq{ALTER TABLE %-18s RENAME TO "${table_prefix}$tname";\n}, qq{"$tname"};
}
}
@@ -391,9 +391,9 @@ if (length $table_prefix) {
$maxsize = length "$_$table_prefix" if length "$_$table_prefix" > $maxsize;
}
for my $t (@torder) {
- next if $t eq '---' or $t eq 'text';
+ next if $t eq '---' or $t eq 'text' or $t eq 'user';
my $tname = $special{$t}||$t;
- printf qq{ALTER TABLE %*s RENAME TO "$tname"\n}, $maxsize+1, qq{"${table_prefix}$tname"};
+ printf qq{ALTER TABLE %*s RENAME TO "$tname";\n}, $maxsize+1, qq{"${table_prefix}$tname"};
}
}
@@ -409,13 +409,13 @@ for my $t (sort keys %tz) {
## Reset sequences
print q{
SELECT setval('filearchive_fa_id_seq', 1+coalesce(max(fa_id) ,0),false) FROM filearchive;
-SELECT setval('ipblocks_ipb_id_val', 1+coalesce(max(ipb_id) ,0),false) FROM ipblocks;
+SELECT setval('ipblocks_ipb_id_seq', 1+coalesce(max(ipb_id) ,0),false) FROM ipblocks;
SELECT setval('job_job_id_seq', 1+coalesce(max(job_id) ,0),false) FROM job;
-SELECT setval('log_log_id_seq', 1+coalesce(max(log_id) ,0),false) FROM logging;
+SELECT setval('logging_log_id_seq', 1+coalesce(max(log_id) ,0),false) FROM logging;
SELECT setval('page_page_id_seq', 1+coalesce(max(page_id),0),false) FROM page;
-SELECT setval('pr_id_val', 1+coalesce(max(pr_id) ,0),false) FROM page_restrictions;
-SELECT setval('rc_rc_id_seq', 1+coalesce(max(rc_id) ,0),false) FROM recentchanges;
-SELECT setval('rev_rev_id_val', 1+coalesce(max(rev_id) ,0),false) FROM revision;
+SELECT setval('page_restrictions_pr_id_seq', 1+coalesce(max(pr_id) ,0),false) FROM page_restrictions;
+SELECT setval('recentchanges_rc_id_seq', 1+coalesce(max(rc_id) ,0),false) FROM recentchanges;
+SELECT setval('revision_rev_id_seq', 1+coalesce(max(rev_id) ,0),false) FROM revision;
SELECT setval('text_old_id_seq', 1+coalesce(max(old_id) ,0),false) FROM pagecontent;
SELECT setval('trackbacks_tb_id_seq', 1+coalesce(max(tb_id) ,0),false) FROM trackbacks;
SELECT setval('user_user_id_seq', 1+coalesce(max(user_id),0),false) FROM mwuser;
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 8e869da7..ac0258ff 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -9,6 +9,21 @@
BEGIN;
SET client_min_messages = 'ERROR';
+DROP SEQUENCE IF EXISTS user_user_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS page_page_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS revision_rev_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS page_restrictions_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS ipblocks_ipb_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS recentchanges_rc_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS logging_log_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS trackbacks_tb_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS job_job_id_seq CASCADE;
+DROP SEQUENCE IF EXISTS category_cat_id_seq CASCADE;
+DROP FUNCTION IF EXISTS page_deleted() CASCADE;
+DROP FUNCTION IF EXISTS ts2_page_title() CASCADE;
+DROP FUNCTION IF EXISTS ts2_page_text() CASCADE;
+DROP FUNCTION IF EXISTS add_interwiki(TEXT,INT,SMALLINT) CASCADE;
+
CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
CREATE TABLE mwuser ( -- replace reserved word 'user'
user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
@@ -39,6 +54,12 @@ CREATE TABLE user_groups (
);
CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
+CREATE TABLE user_former_groups (
+ ufg_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ ufg_group TEXT NOT NULL
+);
+CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group);
+
CREATE TABLE user_newtalk (
user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
user_ip TEXT NULL,
@@ -63,12 +84,12 @@ CREATE TABLE page (
page_len INTEGER NOT NULL
);
CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title);
-CREATE INDEX page_main_title ON page (page_title) WHERE page_namespace = 0;
-CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 1;
-CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2;
-CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3;
-CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4;
-CREATE INDEX page_mediawiki_title ON page (page_title) WHERE page_namespace = 8;
+CREATE INDEX page_main_title ON page (page_title text_pattern_ops) WHERE page_namespace = 0;
+CREATE INDEX page_talk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 1;
+CREATE INDEX page_user_title ON page (page_title text_pattern_ops) WHERE page_namespace = 2;
+CREATE INDEX page_utalk_title ON page (page_title text_pattern_ops) WHERE page_namespace = 3;
+CREATE INDEX page_project_title ON page (page_title text_pattern_ops) WHERE page_namespace = 4;
+CREATE INDEX page_mediawiki_title ON page (page_title text_pattern_ops) WHERE page_namespace = 8;
CREATE INDEX page_random_idx ON page (page_random);
CREATE INDEX page_len_idx ON page (page_len);
@@ -384,14 +405,6 @@ CREATE TABLE watchlist (
CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user);
CREATE INDEX wl_user ON watchlist (wl_user);
-CREATE TABLE math (
- math_inputhash BYTEA NOT NULL UNIQUE,
- math_outputhash BYTEA NOT NULL,
- math_html_conservativeness SMALLINT NOT NULL,
- math_html TEXT,
- math_mathml TEXT
-);
-
CREATE TABLE interwiki (
iw_prefix TEXT NOT NULL UNIQUE,
generated by cgit v1.2.3-54-g00ecf (git 2.45.2) at 2024-11-16 14:27:43 +0000