Talk:SQL

I need to add links to an article I have on SQL, I also propose a section listing every mediawiki extension that contains SQL queries --Sven 14:09, 11 February 2008 (NZDT)

Sounds like a job for SMW - adding script related properties to them all. Or maybe to their talk pages since scripts aren't wikitext articles - or could add a namespace for that - like properties:? --Nad 14:26, 11 February 2008 (NZDT)

Ideas for script-related properties

• has db queries
• classes/methods used
• classes defined
• hooks used
• hooks defined

Absolutely --Sven 15:55, 11 February 2008 (NZDT)

Selecting current articles by category

See MediaWiki schema for a description of the tables. Essentially categorylinks store the category member relationships , page identifies the title and metadata information, and text contains the actual wikitext of articles.

The MediaWiki allows variable article content to be stored as key => value pairs where the atomic unit for the content varies depending on the content of the article. A way around this is to use categorization to group common atomic unit structure together. Basically this is a filtering problem, however queried atomic unit structure needs to be checked downstream,

Example;

• Category: Category:Rosaceae
• Article: Rosaceae (Latest revision number is 1352)

SELECT  * from categorylinks WHERE cl_to like 'Rosaceae';

This selects all pages (article names) from category Rosaceae.

SELECT * from page
WHERE page_id IN (SELECT  cl_from from categorylinks WHERE cl_to like 'Rosaceae');

This selects all revisions of articles from category Rosaceae.

SELECT * FROM revision 
WHERE rev_page IN (SELECT  cl_from from categorylinks WHERE cl_to like 'Rosaceae');

From the output of this information it can be seen that what we want is the rev_text_id that is largest for any rev_page.

This selects all text from articles of category Rosaceae;

Select * from text
WHERE old_id IN (SELECT page_latest from page WHERE page_id IN (SELECT  cl_from from categorylinks WHERE cl_to like 'Rosaceae'));

Nads approach for quicker query

select text from catlinks,text where catlinks.cl_to = "cat" and text.id = cat.id
SELECT * from 1120_text 
WHERE old_id IN (SELECT page_id from 1120_page, 1120_categorylinks WHERE cl_to = "Selenium" AND cl_from = page_id);