recursive category count
No One
Actions

Authored By

	stwalkerster
	Apr 8 2016, 5:46 PM

Size

1 KB

Referenced Files

None

Subscribers

None

recursive category count
View Options

	create temporary table if NOT EXISTS u2204__testing.pageid ( id int(11) PRIMARY KEY );

	set MAX_SP_RECURSION_DEPTH = 255;

	DROP PROCEDURE IF EXISTS u2204__testing.categoryCount;
	DELIMITER ';;'
	CREATE PROCEDURE u2204__testing.categoryCount(in categoryname varchar(255)) BEGIN
	declare pid int;
	declare pns int;
	declare ptitle VARCHAR(255);
	declare done int default false;

	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;

	open cursor_i;
	read_loop: LOOP
	FETCH cursor_i INTO pid, pns, ptitle;

	if done THEN
	LEAVE read_loop;
	END IF;

	if pns = 14 THEN
	call u2204__testing.categoryCount(ptitle);
	END IF;

	insert into u2204__testing.pageid values (pid) on DUPLICATE KEY UPDATE id = id;

	END LOOP;

	close cursor_i;
	END;;
	DELIMITER ';'
	CALL u2204__testing.categoryCount('Philosophy');

	select count(*) from u2204__testing.pageid;