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');