How to change pages from the database

From Cosmin's Wiki

Jump to: navigation, search

It often happens that you wonder where the actual page content is stored in your database, as you would probably want to make some changes "by hand", or, more likely, you might want to do a "bulk change" for all pages (eg. adding adsense content to all your pages). Of course, adding something to all your pages might be done by editing the style (could be done, but that is not the purpose of this post). Another way is to do these changes with a sql script directly in the database, and I'm going to show you how to do this:

First of all a little bit of description of the wikidb database schema. The main table in the wiki database is page. However, this table does not contain the actual content of any of your wiki pages. These are all stored in the text table in the database.

Well, but the text table contains all versions from all revisions of your pages. As such, you need to pass via the revision table to get the current content of any of your pages.

In order to obtain the content of a certain page, I am using the following sql query:

SELECT t.old_text
FROM page p
JOIN revision rev ON rev.rev_id = p.page_latest
JOIN `text` t ON t.old_id = rev.rev_text_id
WHERE 1 = 1
AND p.page_title ='your page title';

As an example, should you want to add the "Author: Cosmin" to all the pages starting with 'MySQL'without having to edit each and every one of them, you could use a small script like this one:

UPDATE `text` SET old_text=concat(old_text,' \r\n <p>Author: Cosmin</p>')
WHERE old_id IN (
    SELECT rev.rev_text_id
    FROM page p
    JOIN revision rev ON rev.rev_id = p.page_latest
    WHERE 1 = 1
    AND p.page_title NOT LIKE '%MySQL%'

For a complete reference about the database model and a description of all tables and columns, visit the this page on the MediaWiki website.