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

Category queries

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: SELECT IsLeafCat('Websites');

CREATE FUNCTION IsLeafCat (title char(255))
    RETURNS tinyint(1) unsigned
    COMMENT 'Is a category "a leaf", or there are sub-categories?'
  BEGIN
      DECLARE r tinyint(1) unsigned;
      SELECT (0=COUNT(page_id)) FROM page,categorylinks WHERE cl_from=page_id AND page_namespace=14 AND cl_to=title INTO r;
      RETURN r;
  END