MediaWiki 数据库

代码片断

根据标题选取内容

MySQL

select old_text from wikitext where old_id in (select rev_text_id from wikirevision where rev_page=(select page_id from wikipage where page_title='{title}'));

PostgreSQL

select old_text from pagecontent where old_id in (select rev_text_id from revision where rev_page=(select page_id from page where page_title='标题'));

寻找包含特定内容的页面之标题

PostgreSQL

MediaWiki 1.35

select page_title
from pagecontent
  join content on (cast(substring(content_address, 4) as integer) = old_id)
  join slots on (slot_content_id = content_id)
  join slot_roles on (slot_role_id = role_id)
  join revision on (slot_revision_id = rev_id)
  join page on (rev_page = page_id)
where
  role_name = 'main' and
  old_text like '%扫雷%';

批量更新页面内容

SQLite3

select load_extension('/ldata/DATA/src/sqlite-regex-replace-ext/glib_replace');

update text
set old_text = regex_replace('<code (\w+)', old_text, '<syntaxhighlight lang=\1')
where old_id in (
  select old_id from text join revision on (text.old_id = revision.rev_text_id) join page on (revision.rev_page = page.page_id)
  where old_text like '%<code%' and page_latest = rev_id
);

update text
set old_text = regex_replace('</code>', old_text, '</' || 'syntaxhighlight>')
where old_id in (
  select old_id from text join revision on (text.old_id = revision.rev_text_id) join page on (revision.rev_page = page.page_id)
  where old_text like '%</code>%' and page_latest = rev_id
);

PostgreSQL

update pagecontent
set old_text = replace(old_text, %(old)s, %(new)s)
where old_id in (
  select old_id from pagecontent join revision on (pagecontent.old_id = revision.rev_text_id) join page on (revision.rev_page = page.page_id)
  where old_text like %(search)s and page_latest = rev_id
        and page_title not like %(exclude)s
);

参见

• MediaWiki
• mw:Manual:Database layout