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)
- 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'));
