MySQL – funkcje do obróbki numerów ISBN

Szef zleciÅ‚ mi jakiÅ› czas temu optymalizacjÄ™ przetwarzania numerów ISBN w naszym systemie. Dotychczas używana klasa napisana w PHP jest fajna, funkcjonalna, itp. Ma jednÄ… dużą wadÄ™. Jest napisana w PHP ;P OznaczaÅ‚o to konieczność pobrania danych z MySQL do PHP, przetworzenia w PHP, zapisania do MySQL. W momencie, gdy danych do przetworzenia byÅ‚o _DUÅ»O_, caÅ‚y proces trwaÅ‚ o wiele za dÅ‚ugo. Jeszcze do tego caÅ‚y proces miaÅ‚ siÄ™ cyklicznie powtarzać co jakiÅ› czas…

RozwiÄ…zanie – funkcje skÅ‚adowane obrabiajÄ…ce dane w samej bazie, bez koniecznoÅ›ci wykonywania dużej iloÅ›ci zapytaÅ„, bez przesyÅ‚ania danych.

DziÄ™ki takiemu podejÅ›ciu odÅ›miecenie dużej iloÅ›ci numerów ISBN bÄ™dÄ…cych w najróżniejszych formatach (‚ISBN 123-456-78-9-0’, ‚numer isbn 1234567890’, ‚nr ISBN 1-2-3-4-5-67890’, itp, itd) zamyka siÄ™ w jednym zapytaniu:

update ksiazki set isbn=ISBN13FromISBN10(isbn);

Poniżej przykłady implementacji.

Konwersja z ISBN10 na ISBN13:

DROP FUNCTION IF EXISTS ISBN13FromISBN10;
delimiter //
CREATE FUNCTION ISBN13FromISBN10(isbnIN VARCHAR(255)) RETURNS VARCHAR(20)
BEGIN
DECLARE i,j INT;
DECLARE isbnOUT VARCHAR(20);
DECLARE position INT;
DECLARE checksum INT;
DECLARE c CHAR(1);
DECLARE done BOOLEAN;

SET isbnOUT='';
SET c='';
SET i=1;
SET position=1;
SET done=false;
SET checksum=0;

REPEAT
SET c=SUBSTR(isbnIN,i,1);
IF c>='0' and c<='9' THEN SET checksum=checksum+c*position; SET position=position+1; SET isbnOUT=concat(isbnOUT,c); IF position>9 THEN
SET checksum=checksum % 11;
IF checksum=10 THEN
SET checksum='X';
END IF;
SET i=i+1;

REPEAT
SET c=SUBSTR(isbnIN,i,1);

IF (c>='0' and c<='9') or c='X' THEN SET done=true; IF c=checksum THEN SET isbnOUT=CONCAT('978',isbnOUT); SET done=true; SET j=1; SET checksum=0; REPEAT SET checksum=checksum+substr(isbnOUT,j,1); SET checksum=checksum+substr(isbnOUT,j+1,1)*3; SET j=j+2; UNTIL j>12 END REPEAT;

SET checksum=(10-(checksum % 10)) % 10;
SET isbnOUT=CONCAT(isbnOUT,checksum);
ELSE
SET isbnOUT=NULL;
END IF;
END IF;

SET i=i+1;
IF i>LENGTH(isbnIN) and done=false THEN
SET isbnOUT=NULL;
SET done=true;
END IF;
UNTIL done END REPEAT;
END IF;
END IF;

SET i=i+1;
IF i>LENGTH(isbnIN) and done=false THEN
SET done=true;
SET isbnOUT=NULL;
END IF;
UNTIL done END REPEAT;

RETURN isbnOUT;
END;
//
delimiter ;

select ISBN13FromISBN10('83-7389-320-2');

Normalizacja do ISBN13:

DROP FUNCTION IF EXISTS ISBN13Normalise;
delimiter //
CREATE FUNCTION ISBN13Normalise(isbnIN VARCHAR(255)) RETURNS VARCHAR(20)
BEGIN
DECLARE isbnOUT VARCHAR(20);
DECLARE position INT;
DECLARE len INT;
DECLARE c CHAR(1);

SET isbnOUT='';
SET position=1;
SET len=0;

REPEAT
SET c=SUBSTR(isbnIN,position,1);
IF c>='0' and c<='9' THEN SET isbnOUT=CONCAT(isbnOUT,c); SET len=len+1; END IF; SET position=position+1; UNTIL position>LENGTH(isbnIN) or len=13 END REPEAT;

IF len<13 THEN SET isbnOUT=NULL; END IF; RETURN isbnOUT; END; // delimiter ; select ISBN13Normalise('ISBN 978-837-389-320-7');

Normalizacja do ISBN10:

DROP FUNCTION IF EXISTS ISBN10Normalise;
delimiter //
CREATE FUNCTION ISBN10Normalise(isbnIN VARCHAR(255)) RETURNS VARCHAR(20)
BEGIN
DECLARE isbnOUT VARCHAR(20);
DECLARE position INT;
DECLARE len INT;
DECLARE c CHAR(1);

SET isbnOUT='';
SET position=1;
SET len=0;

SET isbnIN=UPPER(isbnIN);

REPEAT
SET c=SUBSTR(isbnIN,position,1);
IF (c>='0' and c<='9') or c='X' THEN SET isbnOUT=CONCAT(isbnOUT,c); SET len=len+1; END IF; SET position=position+1; UNTIL position>LENGTH(isbnIN) or len=10 or c='X' END REPEAT;

IF len<10 THEN SET isbnOUT=NULL; END IF; RETURN isbnOUT; END; // delimiter ; select ISBN10Normalise('ISBN 83-7389-320-2');

Normalizacja zgrubna uwzględniająca ISBN10 i ISBN13 (może dawać niepewne wyniki):

DROP FUNCTION IF EXISTS ISBNNormalize;
delimiter //
CREATE FUNCTION ISBNNormalize(isbnIN VARCHAR(255)) RETURNS VARCHAR(20)
BEGIN
DECLARE isbnOUT VARCHAR(20);
DECLARE position INT;
DECLARE len INT;
DECLARE c CHAR(1);

SET isbnOUT='';
SET position=1;
SET len=0;

SET isbnIN=UPPER(isbnIN);

REPEAT
SET c=SUBSTR(isbnIN,position,1);
IF (c>='0' and c<='9') or c='X' THEN SET isbnOUT=CONCAT(isbnOUT,c); SET len=len+1; END IF; SET position=position+1; UNTIL position>LENGTH(isbnIN) or c='X' END REPEAT;

IF (len!=10 and len!=13) THEN
SET isbnOUT=NULL;
END IF;

RETURN isbnOUT;
END;
//

delimiter ;

select ISBNNormalize('003-44-877_4-7');
select ISBN10CalcChecksum('0034487747');

Wyliczenie sumy kontrolnej dla ISBN13

DROP FUNCTION IF EXISTS ISBN13CalcChecksum;
delimiter //
CREATE FUNCTION ISBN13CalcChecksum(isbnIN VARCHAR(255)) RETURNS CHAR(1)
BEGIN
DECLARE position INT;
DECLARE checksum INT;

SET position=1;
SET checksum=0;

REPEAT
SET checksum=checksum+substr(isbnIN,position,1)+substr(isbnIN,position+1,1)*3;
SET position=position+2;
UNTIL position>12 END REPEAT;

SET checksum=(10-(checksum % 10)) % 10;

RETURN checksum;
END;
//
delimiter ;

select ISBN13CalcChecksum('9788373893207');

Wyliczenie sumy kontrolnej dla ISBN10

DROP FUNCTION IF EXISTS ISBN10CalcChecksum;
delimiter //
CREATE FUNCTION ISBN10CalcChecksum(isbnIN VARCHAR(255)) RETURNS CHAR(1)
BEGIN
DECLARE position INT;
DECLARE checksum INT;

SET position=1;
SET checksum=0;

REPEAT
SET checksum=checksum+SUBSTR(isbnIN,position,1)*position;
SET position=position+1;
UNTIL position>9 END REPEAT;

SET checksum=checksum % 11;
IF checksum=10 THEN
SET checksum='X';
END IF;

RETURN checksum;
END;
//
delimiter ;

select ISBN10CalcChecksum('8373893202');