2

Where does mediawiki-1.20.2 with SQLite 3.7.13 store the content of pages? I want to dump the markdown code. I want to get all pages as shown when the "edit" button has been hit.

The maintenance/sqlite.php --backup-to script does not work.

SELECT page_title FROM page; only queries page titles.

These are the relations:

 sqlite> .tables
 archive               module_deps           searchindex_segdir  
 category              msg_resource          searchindex_segments
 categorylinks         msg_resource_links    site_identifiers    
 change_tag            objectcache           site_stats          
 external_user         oldimage              sites               
 externallinks         page                  tag_summary         
 filearchive           page_props            templatelinks       
 hitcounter            page_restrictions     text                
 image                 pagelinks             transcache          
 imagelinks            protected_titles      updatelog           
 interwiki             querycache            uploadstash         
 ipblocks              querycache_info       user                
 iwlinks               querycachetwo         user_former_groups  
 job                   recentchanges         user_groups         
 l10n_cache            redirect              user_newtalk        
 langlinks             revision              user_properties     
 log_search            searchindex           valid_tag           
 logging               searchindex_content   watchlist           
 sqlite>

I did not run any DDL or DML statements, except for selects.

Improve this question
2
  • 1
    Yes, SELECT page_title FROM page; will only return the page_title column from the pages table. What happens when you SELECT * FROM page;? Alternatively, run .SCHEMA page to see the structure of the table.
    – Mike Fitzpatrick
    Oct 15, 2014 at 23:44
  • From a reader's not a DBA's perspective, SELECT page_title FROM page; returns list of page titles and IDs. So to say, no content.
    – user2964971
    Oct 17, 2014 at 15:24

2 Answers 2

Reset to default
2

The text of pages is stored in the text table. To get there from page, you will need to go through revision. The SQL query could look something like:

SELECT page_title, old_text
FROM page
JOIN revision ON page_latest = rev_id
JOIN text ON rev_text_id = old_id
Improve this answer
2
  • First, I did pepare a dump: sqlite> .mode csv - sqlite> .header on - sqlite> .out File.csv.txt. Then, I did run the query as given. Now I have a text file containing all page names followed by page content, in unmodified markdown. Definitely readable.
    – user2964971
    Oct 17, 2014 at 15:36
  • This is what I used before, but the database structure is different now. I added a separate answer for current Mediawiki versions.
    – mivk
    Mar 14, 2023 at 22:59
0

To export Mediawiki pages as plain text, the query in svick's answer used to work well before version 1.31.

Since then, with the introduction of Multi-Content-Revisions, the structure has changed and the id of the current text of a page can be found in the content_address field of the content table.

This is what I use now, with Mediawiki 1.35 and a PostgreSQL database:

SELECT page_title, page_touched, old_text
FROM page
JOIN slots       ON page.page_latest       = slots.slot_revision_id
JOIN "content"   ON slots.slot_content_id  = content.content_id
JOIN pagecontent ON old_id = REGEXP_REPLACE(content.content_address, '^\D*','')::INTEGER
WHERE page_namespace = 0

For SQLite or MySQL, the table pagecontent is named text, and the regexp_replace function would probably need to be adapted.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .