imported>mutante |
Hoof Hearted (talk | contribs) (Fix redirect) |
||
(9 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
Ok, enough of trying to find the right queries after the upgrade to 1.5 |
Ok, enough of trying to find the right queries after the upgrade to 1.5 [[MediaWiki]]. |
||
Lets just log the sql queries from the server. |
Lets just log the [[MySQL|sql]] queries from the server. |
||
{{TOC right}} |
|||
=== |
===A page being requested=== |
||
⚫ | |||
⚫ | |||
SELECT page_id FROM `page` WHERE page_namespace = '0' AND page_title = 'Ogg' LIMIT 1; |
SELECT page_id FROM `page` WHERE page_namespace = '0' AND page_title = 'Ogg' LIMIT 1; |
||
==== |
====get page_latest==== |
||
SELECT page_id,page_namespace,page_title,page_restrictions,page_counter,page_is_redirect,page_is_new,page_random,page_touched,page_latest,page_len FROM `page` WHERE page_id = '17369' LIMIT 1; |
SELECT page_id,page_namespace,page_title,page_restrictions,page_counter,page_is_redirect,page_is_new,page_random,page_touched,page_latest,page_len FROM `page` WHERE page_id = '17369' LIMIT 1; |
||
after having page_latest (32808) ,which is = rev_id in revision we can: |
after having page_latest (32808) ,which is = rev_id in revision we can: |
||
==== |
====get revision_text_id==== |
||
SELECT rev_text_id from revision where rev_id=32808; |
SELECT rev_text_id from revision where rev_id=32808; |
||
after having rev_text_id (32770), which is = old_id, we can finally: |
after having rev_text_id (32770), which is = old_id, we can finally: |
||
==== |
====get the actual content==== |
||
SELECT old_text,old_flags FROM `text` WHERE old_id = '32770'; |
SELECT old_text,old_flags FROM `text` WHERE old_id = '32770'; |
||
<pre> |
<pre> |
||
brion |
brion: old_id=rev_text_id |
||
brion: rev_id=page_latest |
brion: rev_id=page_latest |
||
brion: rev_page=page_id |
|||
</pre> |
</pre> |
||
=== |
===Searching for content=== |
||
To seach for content in pages and return page titles that match, we would have to revert the process: |
To seach for content in pages and return page titles that match, we would have to revert the process: |
||
==== |
====get old_id of matching page==== |
||
select old_id from text where old_text LIKE "%vollmilch%" limit 1; |
select old_id from text where old_text LIKE "%vollmilch%" limit 1; |
||
<pre> |
<pre> |
||
Line 46: | Line 39: | ||
</pre> |
</pre> |
||
==== |
====get rev_page from old_id==== |
||
⚫ | |||
<pre> |
|||
+----------+ |
|||
| rev_page | |
|||
+----------+ |
|||
| 10425 | |
|||
+----------+ |
|||
</pre> |
|||
⚫ | |||
select page_title from page where page_id=10425; |
|||
<pre> |
|||
+------------+ |
|||
| page_title | |
|||
+------------+ |
|||
| Sandbox | |
|||
+------------+ |
|||
</pre> |
|||
====Multiple search results==== |
|||
⚫ | |||
select old_id from text where old_text LIKE "%finn%" LIMIT 5; |
|||
<pre> |
<pre> |
||
+--------+ |
+--------+ |
||
| |
| old_id | |
||
+--------+ |
+--------+ |
||
| |
| 19561 | |
||
| 12621 | |
|||
| 12622 | |
|||
| 12682 | |
|||
| 12683 | |
|||
+--------+ |
+--------+ |
||
</pre> |
</pre> |
||
select distinct rev_page from revision where rev_text_id IN (19561,12621,12622,12682,12683); |
|||
⚫ | |||
<pre> |
|||
+----------+ |
|||
| rev_page | |
|||
+----------+ |
|||
| 9559 | |
|||
| 9609 | |
|||
| 13622 | |
|||
+----------+ |
|||
</pre> |
|||
select page_title from page where page_id IN (9559,9609,13622); |
|||
<pre> |
|||
+----------------+ |
|||
| page_title | |
|||
+----------------+ |
|||
| Debian/Posters | |
|||
| Donations | |
|||
| Forum | |
|||
+----------------+ |
|||
</pre> |
|||
...OR, one could as well just use the '''searchindex''' table: |
|||
select si_page,si_title from searchindex where si_text LIKE "%Vorbis%"; |
|||
<pre> |
|||
+---------+-----------+ |
|||
| si_page | si_title | |
|||
+---------+-----------+ |
|||
| 9746 | gnump3d | |
|||
| 17369 | ogg | |
|||
| 17368 | zinf | |
|||
| 17367 | icecast | |
|||
| 15735 | converter | |
|||
+---------+-----------+ |
|||
</pre> |
|||
but we want the nice page titles, not this lowercase version, so we join the table with page, since si_page = page_id |
|||
select page_title from searchindex join page on page_id=si_page where si_text LIKE "%Vorbis%"; |
|||
<pre> |
<pre> |
||
+------------+ |
+------------+ |
||
| page_title | |
| page_title | |
||
+------------+ |
+------------+ |
||
| |
| Converter | |
||
| GnuMp3d | |
|||
| Icecast | |
|||
| Ogg | |
|||
| Zinf | |
|||
+------------+ |
+------------+ |
||
</pre> |
</pre> |
||
dddooh, so much easier. ;) |
|||
===ok, got it=== |
|||
< mutante> ok,with newer pages i can use rev_id=page_latest and old_id=rev_text_id, but with older pages i have equal values in rev_id and rev_text_id and i get an empty set if i then try to select page_title from page where page_latest= |
|||
<brion> mutante: rev_page=page_id |
|||
< brion> page_latest points to the latest rev_id in the page for convenience |
|||
[[Category:Mediawiki]] |
Latest revision as of 16:09, 29 August 2022
Ok, enough of trying to find the right queries after the upgrade to 1.5 MediaWiki.
Lets just log the sql queries from the server.
A page being requested[edit]
get page_id[edit]
SELECT page_id FROM `page` WHERE page_namespace = '0' AND page_title = 'Ogg' LIMIT 1;
get page_latest[edit]
SELECT page_id,page_namespace,page_title,page_restrictions,page_counter,page_is_redirect,page_is_new,page_random,page_touched,page_latest,page_len FROM `page` WHERE page_id = '17369' LIMIT 1;
after having page_latest (32808) ,which is = rev_id in revision we can:
get revision_text_id[edit]
SELECT rev_text_id from revision where rev_id=32808;
after having rev_text_id (32770), which is = old_id, we can finally:
get the actual content[edit]
SELECT old_text,old_flags FROM `text` WHERE old_id = '32770';
brion: old_id=rev_text_id brion: rev_id=page_latest brion: rev_page=page_id
Searching for content[edit]
To seach for content in pages and return page titles that match, we would have to revert the process:
get old_id of matching page[edit]
select old_id from text where old_text LIKE "%vollmilch%" limit 1;
+--------+ | old_id | +--------+ | 32778 | +--------+
get rev_page from old_id[edit]
select rev_page from revision where rev_text_id=32778;
+----------+ | rev_page | +----------+ | 10425 | +----------+
result / page_title[edit]
select page_title from page where page_id=10425;
+------------+ | page_title | +------------+ | Sandbox | +------------+
Multiple search results[edit]
select old_id from text where old_text LIKE "%finn%" LIMIT 5;
+--------+ | old_id | +--------+ | 19561 | | 12621 | | 12622 | | 12682 | | 12683 | +--------+
select distinct rev_page from revision where rev_text_id IN (19561,12621,12622,12682,12683);
+----------+ | rev_page | +----------+ | 9559 | | 9609 | | 13622 | +----------+
select page_title from page where page_id IN (9559,9609,13622);
+----------------+ | page_title | +----------------+ | Debian/Posters | | Donations | | Forum | +----------------+
...OR, one could as well just use the searchindex table:
select si_page,si_title from searchindex where si_text LIKE "%Vorbis%";
+---------+-----------+ | si_page | si_title | +---------+-----------+ | 9746 | gnump3d | | 17369 | ogg | | 17368 | zinf | | 17367 | icecast | | 15735 | converter | +---------+-----------+
but we want the nice page titles, not this lowercase version, so we join the table with page, since si_page = page_id
select page_title from searchindex join page on page_id=si_page where si_text LIKE "%Vorbis%";
+------------+ | page_title | +------------+ | Converter | | GnuMp3d | | Icecast | | Ogg | | Zinf | +------------+
dddooh, so much easier. ;)
ok, got it[edit]
< mutante> ok,with newer pages i can use rev_id=page_latest and old_id=rev_text_id, but with older pages i have equal values in rev_id and rev_text_id and i get an empty set if i then try to select page_title from page where page_latest=
<brion> mutante: rev_page=page_id
< brion> page_latest points to the latest rev_id in the page for convenience