Wikipedia:WikiProject Interlanguage Links/Scripts: Difference between revisions

Revision as of 12:53, 19 August 2007

Revision as of 13:30, 19 August 2007

Line 4:

<pre>

----------------------------------------------------------------------------------------

CREATE TABLE inter (

from_lang varchar(10),

from_title varchar(255),

to_lang varchar(10),

to_title varchar(255)

);

----------------------------------------------------------------------------------------

USE en;

Line 26 ⟶ 27:

WHERE page.page_id = langlinks.ll_from

AND page.page_namespace = 0;

----------------------------------------------------------------------------------------

... etc

Line 33 ⟶ 35:

<pre>

----------------------------------------------------------------------------------------

update inter set from_title = replace( from_title, '_', ' ' );

update inter set to_title = replace( to_title, '_', ' ' );

Line 47:

ALTER TABLE inter ADD INDEX ( from_lang, from_title );

ALTER TABLE inter ADD INDEX ( to_lang, to_title );

----------------------------------------------------------------------------------------

</pre>

And extract suggested red links, ie:

<pre>

----------------------------------------------------------------------------------------

CREATE TABLE suggestions (

from_lang varchar(10),

from_title varchar(255),

to_lang varchar(10),

to_title varchar(255)

);

INSERT INTO suggestions

SELECT DISTINCT a.from_lang, a.from_title, b.to_lang, b.to_title

FROM inter a

INNER JOIN inter b

ON a.to_lang = b.from_lang

AND a.to_title = b.from_title

LEFT JOIN inter c

ON c.from_lang = a.from_lang

AND c.from_title = a.from_title

AND c.to_lang = b.to_lang // Not c.to_title - any link from a to c existsing is sufficient

WHERE a.from_lang = 'en'

AND b.to_lang != a.from_lang

AND c.from_lang IS NULL;

// a:x -> b:x and b:x exists and no link from b:? to a:x exists => b:x -> a:x

CREATEINSERT TABLEINTO suggestions (

SELECT concat( '*[[', a.to_title, ']] &rarr; [[:', a.from_lang, ':', a.from_title, ']]' )

ANDSELECT a.to_lang, a.to_title, =a.from_lang, ba.from_title

FROM inter a

INNER JOIN en.page p

Line 88 ⟶ 69:

AND a.to_lang = 'en'

AND b.from_lang IS NULL;

</pre>

Finally, extract these suggestions in a human-reabale format

<pre>

----------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS report_suggestions;

DELIMITER //

CREATE PROCEDURE report_suggestions( group_size INT, flang VARCHAR(10), tlang VARCHAR(10) )

BEGIN

DECLARE sug_pos, sug_base, done INT;

DECLARE ftitle, ttitle VARCHAR(255);

DECLARE sug CURSOR FOR SELECT from_title, to_title FROM suggestions WHERE from_lang = flang AND to_lang = tlang;

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET done = 1;

SET sug_pos = 0;

SET sug_base = 0;

SET done = 0;

OPEN sug;

REPEAT

FETCH sug INTO ftitle, ttitle;

IF NOT done THEN

IF sug_pos = 0 THEN

SELECT concat( '=== ', sug_base, ' - ', sug_base + group_size - 1, ' ===' );

SET sug_base = sug_base + group_size;

SET sug_pos = group_size - 1;

ELSE

SET sug_pos = sug_pos - 1;

END IF;

SELECT concat( '*[[', a.to_titleftitle, ']] &rarr; [[:', a.from_langtlang, ':', a.from_titlettitle, ']]' );

END IF;

UNTIL done END REPEAT;

CLOSE sug;

END;

//

DELIMITER ;

call report_suggestions( 10, 'en', 'sv' );

----------------------------------------------------------------------------------------

</pre>