Miscellany

CREATE LANGUAGE plpgsql;

DROP FUNCTION initRank();
DROP FUNCTION normalizeGraph();
DROP FUNCTION saveRank();
DROP FUNCTION sumParent(integer);
DROP FUNCTION normalizeRank();

-- Initialisation des valeurs de pagerank de chaque page
CREATE OR REPLACE FUNCTION initRank() RETURNS integer AS $$
DECLARE
	nb_nodes FLOAT;		-- Nombre de noeuds (ou pages) dans le graphe
BEGIN
	SELECT count(*) INTO nb_nodes FROM page;
	UPDATE page SET page_rank = 1::float/nb_nodes, page_rank_old = 1::float/nb_nodes;

	RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- Normalisation des poids des liens du graphe
CREATE OR REPLACE FUNCTION normalizeGraph() RETURNS integer AS $$
DECLARE
	res RECORD; 		-- Liste d'adjacences des noeuds sortant d'un noeud (outlink)
BEGIN
	FOR res IN 
		SELECT link_parent_id as id, count(link_parent_id) as nb_nodes
		FROM link 
		GROUP BY link_parent_id
	LOOP
		UPDATE link SET link_weight = 1::float/res.nb_nodes::float 
		WHERE link_parent_id = res.id;
	END LOOP;
	RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- Sauvegarde de la valeur de pagerank 
CREATE OR REPLACE FUNCTION saveRank() RETURNS integer AS $$
DECLARE
	res RECORD; 		-- Liste des valeurs de pagerank de chaque noeud
BEGIN
	FOR res IN 
		SELECT page_id AS id, page_rank AS rank FROM page
	LOOP
		UPDATE page SET page_rank_old = res.rank 
		WHERE page_id = res.id;
	END LOOP;
	RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- Effectue la somme des poids des noeuds entrant d'un noeud
CREATE OR REPLACE FUNCTION sumParent(integer) RETURNS float AS $$
DECLARE
	node_id ALIAS FOR $1; 		-- Noeud (ou page) à pondérer
	in_nodes RECORD;		-- Ensemble des liens entrants du noeud (ou page)
	sum FLOAT;			-- pondération
BEGIN
	sum := 0;

	-- Parcourt de tous les noeuds entrant de node.id
	FOR in_nodes IN
		SELECT link_weight AS weight, page_rank AS rank
		FROM link, page
		WHERE link_parent_id = page_id
		AND link_child_id = node_id
		ORDER BY link_parent_id
		
	LOOP
		sum := sum + (in_nodes.rank * in_nodes.weight);
	END LOOP;

	RETURN sum;
END;
$$ LANGUAGE plpgsql;

-- Normalisation des valeurs de pagerank
CREATE OR REPLACE FUNCTION normalizeRank() RETURNS integer AS $$
DECLARE
	node RECORD;
	sum FLOAT;
BEGIN
	sum := 0;

	-- Parcourt de tous les noeuds afin d'obtenir la somme des pagerank
	FOR node IN
		SELECT page_rank AS rank 
		FROM page 
		ORDER BY page_id
	LOOP
		sum := sum + node.rank;
	END LOOP;

	-- Parcourt de tous les noeuds afin de normaliser le pagerank
	FOR node IN
		SELECT page_id AS id, page_rank AS rank
		FROM page
		ORDER BY page_id
	LOOP
		UPDATE page SET page_rank = (node.rank/sum) 
		WHERE page_id = node.id;
	END LOOP;

	RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- Calcul du pagerank du graphe
CREATE OR REPLACE FUNCTION computePageRank(float) RETURNS integer AS $$
DECLARE
	epsilon ALIAS FOR $1;
	node RECORD;
	sum FLOAT;
	t INTEGER;
	test FLOAT;
BEGIN
	t := 0;
	sum := 0;
	test := 0;
	-- Initialisation des valeurs de pagerank en fonction du nombre de noeud (ou page)
	PERFORM initRank();
	--RAISE NOTICE 'initRank()';

	-- Normalisation des poids des liens du graphe
	PERFORM normalizeGraph();
	--RAISE NOTICE 'normalizeGraph()';

	LOOP
		--RAISE NOTICE 'LOOP(%)', t;
		--RAISE NOTICE 'epsilon(%)', epsilon;

		test := 0;

		-- Sauvegarde des nouvelles valeur de pagerank
		PERFORM saveRank();
		-- RAISE NOTICE 'saveRank()';

		-- Parcourt de tous les noeuds en vue d'une pondération des valeurs de pagerank
		FOR node IN
			SELECT page_id AS id FROM page 
			ORDER BY page_id
		LOOP
			sum := sumParent(node.id);
			RAISE NOTICE 'sumParent(%)', node.id;
			UPDATE page SET page_rank = sum 
			WHERE page_id = node.id;
			RAISE NOTICE 'Noeud(%):(%)', node.id, sum ;
		END LOOP;

		-- Normalisation des valeurs de pagerank
		PERFORM normalizeRank();

		-- Parcourt de tous les noeuds afin de verifier l'ecart entre les valeurs
		FOR node IN
			SELECT page_rank AS rank, page_rank_old AS rankold
			FROM page 
			ORDER BY page_id
		LOOP
			test := test + abs(node.rank - node.rankold);
		END LOOP;

		t := t + 1;
		RAISE NOTICE 'test de epsilon avec (%)', test;

		EXIT WHEN epsilon >= test;

	END LOOP;
	RAISE NOTICE '(%) boucle(s) executée(s)', t;

	RETURN 1;
END;
$$ LANGUAGE plpgsql;

indexer$# RETURN 1;
indexer$# END;
indexer$# $$ LANGUAGE plpgsql;
ERREUR:  unterminated string
CONTEXTE : compile of PL/pgSQL function "computepagerank" near line 59
indexer=#