Jump to content

User:Topbanana/Reports/This image may be GFDL but not properly marked as such/SQL

From Wikipedia, the free encyclopedia

To recreate this report, create a Link Analysis Database then run the SQL below.


CREATE TABLE img (
    -- Basic article details
    img_id mediumint(7) unsigned NOT NULL,
    img_title varchar(255) binary NOT NULL,
    img_text mediumtext NOT NULL,

	img_is_gdfl tinyint(1) unsigned NOT NULL,
    
	PRIMARY KEY( img_id ),
	UNIQUE INDEX( img_title ),
	INDEX ( img_is_gdfl )
) ENGINE=MyISAM;


REPLACE INTO img ( img_id, img_title, img_text, img_is_gdfl )
SELECT cur_id, cur_title, cur_text, 0
FROM cur
WHERE cur_namespace = 6;


-- Properly linked images
UPDATE img
SET    img_is_gdfl = 1
WHERE  locate( '{{GFDL}}', img_text ) > 0
AND    img_is_gdfl = 0;

-- Images linked directly to category
UPDATE img
SET    img_is_gdfl = 2
WHERE  locate( 'Category:GFDL images', img_text ) > 0
AND    img_is_gdfl = 0;

-- Images linked to GDFL article
UPDATE img
SET    img_is_gdfl = 3
WHERE  locate( '[[GNU_Free_Documentation_License]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 3
WHERE  locate( '[[GNU Free Documentation License]]', img_text ) > 0
AND    img_is_gdfl = 0;


-- Images linked to redirects to the GDFL article

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[Back-Cover Texts]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNUFreeDocumentationLicense]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU/FDL]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GFDL]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU Free Documentation License/Secondary sections]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU Free Documentation License/Front-Cover Texts]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU Free Documentation License/Secondary Sections]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU FDL]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[FDL]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU Free Documentation Licence]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GNU FDL/]]', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 4
WHERE  locate( '[[GDFL]]', img_text ) > 0
AND    img_is_gdfl = 0;

-- Images otherwise containing GFDL or GNU FDL

UPDATE img
SET    img_is_gdfl = 5
WHERE  locate( 'GFDL', img_text ) > 0
AND    img_is_gdfl = 0;

UPDATE img
SET    img_is_gdfl = 5
WHERE  locate( 'GNU FDL', img_text ) > 0
AND    img_is_gdfl = 0;