DELIMITER //
DROP DATABASE IF EXISTS mysql_bookdb//
CREATE DATABASE mysql_bookdb//

USE mysql_bookdb//

GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE ON mysql_bookdb.* TO george@localhost IDENTIFIED BY 'password'//
GRANT FILE ON *.* TO george@localhost IDENTIFIED BY 'password'////


CREATE TABLE IF NOT EXISTS AUTHORS
(
    ID INT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(ID)
) ENGINE MyISAM DEFAULT CHARSET=utf8//


CREATE TABLE IF NOT EXISTS NAMES
(
    ID INT(11) NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    PRIMARY KEY(ID),
    UNIQUE (NAME)
) ENGINE MyISAM DEFAULT CHARSET=utf8//


CREATE TABLE IF NOT EXISTS FULLNAMES
(
    AUTHOR_ID INT(11) NOT NULL,
    NAME_ID INT(11) NOT NULL,
    ORDINAL_POSITION INT(11),
    FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS (ID),
    FOREIGN KEY (NAME_ID) REFERENCES NAMES (ID)
) ENGINE MyISAM DEFAULT CHARSET=utf8//



CREATE TABLE IF NOT EXISTS AUTHORSHIP
(
    ID INT(11) NOT NULL AUTO_INCREMENT,
    AUTHOR_ID INT(11) NOT NULL,
    BOOK_ID INT(11) NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS (ID),
    FOREIGN KEY (BOOK_ID) REFERENCES BOOKS (ID)
) ENGINE MyISAM DEFAULT CHARSET=utf8//



CREATE TABLE IF NOT EXISTS BOOKS
(
    ID INT(11) NOT NULL AUTO_INCREMENT,
    TITLE1 VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY(ID)
) ENGINE MyISAM DEFAULT CHARSET=utf8//

CREATE FUNCTION AddAuthor2(_name1 VARCHAR(100), _name2 VARCHAR(100))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE name_id1 INT DEFAULT NULL;
    DECLARE name_id2 INT DEFAULT NULL;

    DECLARE author_id1 INT DEFAULT NULL;
    DECLARE author_id2 INT DEFAULT NULL;

    SELECT N.ID INTO name_id1 FROM NAMES AS N WHERE N.NAME LIKE UPPER(_name1);
    IF name_id1 IS NULL THEN
       INSERT INTO NAMES (NAME) VALUES (UPPER(_name1));
       SELECT LAST_INSERT_ID() INTO name_id1;
    END IF;

    SELECT N.ID INTO name_id2 FROM NAMES AS N WHERE N.NAME LIKE UPPER(_name2);
    IF name_id2 IS NULL THEN
       INSERT INTO NAMES (NAME) VALUES (UPPER(_name2));
       SELECT LAST_INSERT_ID() INTO name_id2;
    END IF;

    SELECT FN.AUTHOR_ID INTO author_id1
    FROM FULLNAMES AS FN
    WHERE FN.NAME_ID = name_id1 AND FN.ORDINAL_POSITION = 0;

    SELECT FN.AUTHOR_ID INTO author_id2
    FROM FULLNAMES AS FN
    WHERE FN.NAME_ID = name_id2 AND FN.ORDINAL_POSITION = 1;

    IF author_id1 IS NULL OR author_id2 IS NULL OR (author_id1 != author_id2) THEN
        INSERT INTO AUTHORS () VALUES ();
        SELECT LAST_INSERT_ID() INTO author_id1;
        INSERT INTO FULLNAMES (AUTHOR_ID, NAME_ID, ORDINAL_POSITION) VALUES (author_id1, name_id1, 0);
        INSERT INTO FULLNAMES (AUTHOR_ID, NAME_ID, ORDINAL_POSITION) VALUES (author_id1, name_id2, 1);
    END IF;


    RETURN author_id1;
END//

SELECT AddAuthor2('Hans', 'Hansen');
SELECT AddAuthor2('Hans', 'Jensen');
SELECT AddAuthor2('Hans', 'Nielsen');

----------------------------
gauss:/home/george/Desktop# mysql --user=root --password=password < books4_db.sql
AddAuthor2('Hans', 'Hansen')
1
AddAuthor2('Hans', 'Jensen')
2
ERROR 1172 (42000) at line 98: RESULT consisted OF more than one ROW
gauss:/home/george/Desktop#