Difference between revisions of "MediaWiki/Sql"
Jump to navigation
Jump to search
(syntax / typo) |
(adds more complex query, plus reference to tools) |
||
(4 intermediate revisions by one other user not shown) | |||
Line 1: | Line 1: | ||
− | + | Some database queries that you might want to use and bookmark in your database administration tool such as MySQL Query Browser. http://dev.mysql.com/downloads/gui-tools/5.0.html or SQLyog http://www.webyog.com/en/index.php | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | Some database queries that you might want to use and bookmark in your database administration tool such as | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
Here is a query that will show you the pages in your wiki that contain a particular string. In this example, I wanted to find all pages that were using the 'Icon' extension parser extension magic word '#icon'. | Here is a query that will show you the pages in your wiki that contain a particular string. In this example, I wanted to find all pages that were using the 'Icon' extension parser extension magic word '#icon'. | ||
− | <source lang=" | + | <source lang="sql"> |
SELECT DISTINCT | SELECT DISTINCT | ||
page_title | page_title | ||
Line 61: | Line 18: | ||
text | text | ||
WHERE | WHERE | ||
− | + | # old_text LIKE '%#icon%' | |
old_text REGEXP('#icon') # regex is easier to deal with | old_text REGEXP('#icon') # regex is easier to deal with | ||
) | ) | ||
Line 68: | Line 25: | ||
A more complex query that summarizes some information about an article | A more complex query that summarizes some information about an article | ||
(You supply the article title) | (You supply the article title) | ||
− | <source lang=" | + | <source lang="sql"> |
SELECT | SELECT | ||
p.page_id 'id', | p.page_id 'id', | ||
Line 92: | Line 49: | ||
</source> | </source> | ||
− | ==See Also== | + | == See Also == |
− | + | * [[mw:Manual:Database_access]] | |
− | |||
− | *[[mw:Manual:Database_access]] | ||
− | |||
− | |||
[[Category:Database]] | [[Category:Database]] | ||
Line 104: | Line 57: | ||
[[Category:Help]] | [[Category:Help]] | ||
[[Category:Tools]] | [[Category:Tools]] | ||
− |
Revision as of 09:48, 4 December 2008
Some database queries that you might want to use and bookmark in your database administration tool such as MySQL Query Browser. http://dev.mysql.com/downloads/gui-tools/5.0.html or SQLyog http://www.webyog.com/en/index.php
Here is a query that will show you the pages in your wiki that contain a particular string. In this example, I wanted to find all pages that were using the 'Icon' extension parser extension magic word '#icon'.
SELECT DISTINCT
page_title
FROM
page
RIGHT JOIN
revision
ON page_id = rev_page
WHERE
rev_text_id
IN (
SELECT
old_id
FROM
text
WHERE
# old_text LIKE '%#icon%'
old_text REGEXP('#icon') # regex is easier to deal with
)
A more complex query that summarizes some information about an article (You supply the article title)
SELECT
p.page_id 'id',
p.page_title 'title',
p.page_namespace 'namespace',
t.old_id 'latest text id',
t.old_text 'content',
r.rev_id,
r.rev_comment 'comment',
r.rev_user,
r.rev_user_text,
r.rev_parent_id 'previous revision'
FROM
text t
RIGHT JOIN
revision r
ON t.old_id = r.rev_text_id
RIGHT JOIN
page p
ON r.rev_id = p.page_latest
WHERE
p.page_title = 'Main_Page'