recursive category count
No One
Actions

Authored By

	stwalkerster
	Apr 8 2016, 7:14 PM

Size

1 KB

Referenced Files

None

Subscribers

None

recursive category count
View Options

	-- create a temporary table to hold page IDs for results
	create temporary table if NOT EXISTS u2204__testing.pageid ( id int(11) PRIMARY KEY );

	-- enable recursion
	set MAX_SP_RECURSION_DEPTH = 255;

	-- create a function to do the work
	DROP PROCEDURE IF EXISTS u2204__testing.categoryCount;
	DELIMITER ';;'
	CREATE PROCEDURE u2204__testing.categoryCount(in categoryname varchar(255)) BEGIN
	-- define some variables I'm going to use
	declare pid int;
	declare pns int;
	declare ptitle VARCHAR(255);
	declare done int default false;

	-- declare a cursor (think a SQL statement that gets executed into a set of results, which you can then loop over)
	declare cursor_i cursor for select page_id, page_namespace, page_title from enwiki_p.categorylinks
	inner join enwiki_p.page on page_id = cl_from
	where cl_to = categoryname;
	declare continue HANDLER for not found set done = true;

	-- OK, we're ready to start. Open the cursor to execute the query
	open cursor_i;

	-- loop over the results in the cursor
	read_loop: LOOP
	-- get the current row and put the results into the three variables
	FETCH cursor_i INTO pid, pns, ptitle;

	-- done is set above when there's no more results, so quit the loop
	if done THEN
	LEAVE read_loop;
	END IF;

	-- check the page namespace is 14, and if it is, call this procedure again with the new category title
	if pns = 14 THEN
	call u2204__testing.categoryCount(ptitle);
	END IF;

	-- insert this page's ID into the results table (doing a dummy operation if the key already exists)
	insert into u2204__testing.pageid values (pid) on DUPLICATE KEY UPDATE id = id;

	END LOOP;

	-- we're done with the cursor, so close it
	close cursor_i;

	-- end of the stored procedure declaration
	END;;
	DELIMITER ';'

	-- call the stored procedure we just defined
	CALL u2204__testing.categoryCount('Philosophy');

	-- simple count from the results.
	select count(*) from u2204__testing.pageid;

File Metadata

Mime Type: text/plain; charset=utf-8
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 276
Default Alt Text: recursive category count (1 KB)

recursive category countNo OneActions

recursive category countView Options

File Metadata

Event Timeline

recursive category count
No One
Actions

recursive category count
View Options