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