Difference between revisions of "MediaWiki SQL queries"

From Organic Design wiki
(all mw queries in here not separate articles)
 
m (Search all pages with specific text in their current revision)
 
(One intermediate revision by the same user not shown)
Line 21: Line 21:
 
<source lang="mysql">
 
<source lang="mysql">
 
SELECT user_name FROM user WHERE user_id NOT IN (SELECT DISTINCT rev_user FROM revision);
 
SELECT user_name FROM user WHERE user_id NOT IN (SELECT DISTINCT rev_user FROM revision);
 +
</source>
 +
 +
== Select the text of the latest revision of a title ==
 +
<source lang="mysql">
 +
SELECT old_text FROM page,text,revision
 +
WHERE page_title='{!title!}'
 +
  AND page_namespace={!numeric_namespace!}
 +
  AND rev_id=page_latest AND old_id=rev_text_id;
 +
</source>
 +
 +
== Search all pages with specific text in their current revision ==
 +
This is basically a full text search which is expensive, but useful for making lists of pages containing text that's difficult to search for such as symbols or parser functions.
 +
<source lang="mysql">
 +
SELECT page_namespace,page_title FROM page,text,revision
 +
WHERE rev_id=page_latest
 +
  AND old_id=rev_text_id
 +
  AND old_text like '%{!search phrase!}%'
 
</source>
 
</source>
  

Latest revision as of 13:14, 17 January 2023

Reset a user password

Before reading further consider the password reset extension which gives a simple form to reset wiki passwords.

You need to log in to the database directly. First obtain the user's id as in the following example (alternatively, go to Special:Userlist and get the id from there):

SELECT user_id FROM prefix_user WHERE user_name='WikiSysop';

Then apply the query shown in the following example to set the password for that user id (here the WikiSysop user id is 1): Note that the password is a nested concatenation of both the md5 of the user_id combined with the md5 password.

UPDATE prefix_user SET user_password=md5(CONCAT('1-',md5('newpassword'))) WHERE user_id=1;

Set a user preference

You can change a preference such as the skin with the following SQL query. Remove the WHERE clause to apply to all users.

UPDATE user SET user_options = REPLACE(user_options,'skin=monobook','skin=organicdesign') WHERE user_name = 'Foo';

Select all user who have not made edits

SELECT user_name FROM user WHERE user_id NOT IN (SELECT DISTINCT rev_user FROM revision);

Select the text of the latest revision of a title

SELECT old_text FROM page,text,revision
 WHERE page_title='title'
   AND page_namespace=numeric_namespace
   AND rev_id=page_latest AND old_id=rev_text_id;

Search all pages with specific text in their current revision

This is basically a full text search which is expensive, but useful for making lists of pages containing text that's difficult to search for such as symbols or parser functions.

SELECT page_namespace,page_title FROM page,text,revision
 WHERE rev_id=page_latest
   AND old_id=rev_text_id
   AND old_text like '%search phrase%'

See also