×
Create a new article
Write your page title here:
We currently have 3,187 articles on s23. Type your article name above or create one of the articles listed here!



    s23
    3,187Articles

    MediaWiki/reverse engineering

    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

    Cookies help us deliver our services. By using our services, you agree to our use of cookies.

    Recent changes

  • Vaxima • 5 hours ago
  • Vaxima • 7 hours ago
  • Vaxima • 7 hours ago
  • Vaxima • 7 hours ago
  • Cookies help us deliver our services. By using our services, you agree to our use of cookies.