User:AKA MBG/Database queries

Intro
Database of Simple wikipedia (9 Sept 2007) are used in experiments. I have tried to use stored routines in MySQL 5.

Links

 * MySQL 5.0 New Features: Stored Procedures
 * MySQL General Purpose Stored Routines Library
 * Bouman R. Nesting MySQL cursor loops // Blog. 2005

Number of categories

 * SELECT COUNT(*) AS size FROM page WHERE page_namespace=14;
 * 5602

Get categories-redirects

 * SELECT * FROM page WHERE page_namespace=14 AND page_is_redirect=1;

Get category ID by title and title by ID
Get category ID by title
 * SELECT page_id FROM page WHERE page_namespace=14 AND page_title='Wikis'
 * Result: 30440

Get parent category title by ID
 * SELECT cl_to FROM categorylinks WHERE cl_from=30440
 * Websites

Get child categories IDs by title

 * SELECT page_id,page_title FROM page,categorylinks WHERE cl_from=page_id AND page_namespace=14 AND cl_to='Websites'
 * 25521 | Wikimedia
 * 30440 | Wikis

Get number of articles and sub-categories which belong to the category

 * SELECT COUNT(page_id) FROM page,categorylinks WHERE cl_from=page_id AND cl_to='Websites'
 * 49, since category "Websites" has 2 subcategories and 47 articles.

Is a category 'a leaf'
Is a category 'a leaf', or there are sub-categories?
 * SELECT 0=COUNT(page_id) FROM page,categorylinks WHERE cl_from=page_id AND page_namespace=14 AND cl_to='Websites'

test: