trjhtr

I have a few of hobbies, one is reading science fiction and fantasy and another is coding solutions. I thought I might have a chance to combine both hobbies on this project.

I have well over 1500 Science Fiction and Fantasy books that I have accumulated over the years. Some of them are signed by the authors. Most of them are Mass Market Paperbacks. Quite a few of them are in a series by the author.
(Insert photos here.)

Problem Statement

Over the years I have occasionally purchased the same book twice because I didn’t realize I already had it. I don’t actually know how many books I own. At some point I may want to sell some or all of the books. I don’t have a clue about the value of the library. It became obvious that a catalog or inventory of the all SF and F books I owned was necessary.

Six months ago a Microsoft excel spread sheet of books was started. I found I kept adding columns to the spreadsheet, and the spreadsheet seemed like it was insufficient to do the job. Some of the issues included multiple data entries for the same book in different lists for wish lists or for selling or buying books.
The user has the ability to add formats and categories. Formats and categories can’t be deleted once they are in use. The user can add and delete authors, books and author series. A book can be bought or sold. A book can be added to the wish list. A book on the wish list is updated when it is bought. A book may be borrowed from the library and read. Since these books started being purchased in 1968 the purchase information may not be available for all books. Deleting an author deletes all the authors’ series and books.

This database is the first part of the project, I couldn’t really create a friendly user interface until the database is working.
Due to feature creep this database can now handle other kinds of books besides science fiction and fantasy. A future version of this database will have an additional table for the status of the book (new or user) and the condition of the book (Excellent, Good, Fair, Poor).

Solution

I was unable to provide the entire solution due to size constraints on the question. The code is available here. The unit tests run by default. To prevent the unit tests from running comment out the call to booklibinventory.zzzRunAllUnitTests(); at the end of the SQL script.

For books there is no single identifying item, or rather the ISBN is the single identifying item based on author, title, format and edition, but some books printed in the 1960’s and 1970’s don’t have an ISBN on the book itself. This database uses the author title and format together as the identity of the book.

There are about 2790 lines of code and comments in this database. The first 240 lines are the data definitions of the tables. There are 1849 lines of stored procedures implementing the insert, update, delete and retrieval stored procedures. The last 701 line of code are unit tests in an attempt to make sure the stored procedures and functions work when I start developing the user interface.

This is the first relational schema I have designed from scratch. A year ago I wouldn’t have used any stored procedures because I didn’t know about the benefits of using stored procedures.

When I started this project I didn’t know about database partitioning, I learned about table normalization in the university and that is what I tried to do. The current design allows for addition of fields at a later time without modifying any existing tables. This solution requires additional joins when generating reports, but existing data won’t be invalidated.

Questions:

Is the normalization well designed, or would it be better off using partitioning?
Is my use of indexes good, am I using too many or too few?
Are there any obvious bottle necks that would affect the performance of inserts, update or queries?
Is there anything in the code that really shouldn’t be done, any bad practices?
Is there anything that should be added to the database prevent or help deter SQL injection?
What should be implemented that hasn’t been implemented (what features would you want)?

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema booklibinventory
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `booklibinventory` DEFAULT CHARACTER SET utf8 ;
USE `booklibinventory` ;

-- -----------------------------------------------------
-- Table `booklibinventory`.`authorstab`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`authorstab` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`authorstab` (
 `idAuthors` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `LastName` VARCHAR(20) NOT NULL,
 `FirstName` VARCHAR(20) NOT NULL,
 `MiddleName` VARCHAR(20) NULL DEFAULT NULL,
 `YearOfBirth` VARCHAR(4) NULL DEFAULT NULL,
 `YearOfDeath` VARCHAR(4) NULL DEFAULT NULL,
 PRIMARY KEY (`idAuthors`, `LastName`, `FirstName`),
 UNIQUE INDEX `idAuthors_UNIQUE` (`idAuthors` ASC),
 INDEX `LastName` (`LastName` ASC),
 INDEX `LastCMFirst` (`LastName` ASC, `FirstName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bookcategories`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bookcategories` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bookcategories` (
 `idBookCategories` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `CategoryName` VARCHAR(45) NOT NULL COMMENT 'This will be strings like Non-Fiction, Mystery, Science-Fiction, Fantasy, Poetry, Art etc.',
 PRIMARY KEY (`idBookCategories`, `CategoryName`),
 UNIQUE INDEX `idBookCategories_UNIQUE` (`idBookCategories` ASC),
 INDEX `CategoryNames` (`CategoryName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bksynopsis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bksynopsis` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bksynopsis` (
 `BookFKbd` INT(10) UNSIGNED NOT NULL,
 `StoryLine` VARCHAR(1024) NULL DEFAULT NULL,
 PRIMARY KEY (`BookFKbd`),
 INDEX `BookFKbD` (`BookFKbd` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bookformat`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bookformat` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bookformat` (
 `idFormat` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `FormatName` VARCHAR(45) NOT NULL,
 PRIMARY KEY (`idFormat`, `FormatName`),
 UNIQUE INDEX `idFormat_UNIQUE` (`idFormat` ASC),
 UNIQUE INDEX `FormatName_UNIQUE` (`FormatName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`bookinfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`bookinfo` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`bookinfo` (
 `idBookInfo` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `TitleFKbi` INT(10) UNSIGNED NOT NULL,
 `AuthorFKbi` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key Into Author Table',
 `CategoryFKbi` INT(10) UNSIGNED NOT NULL,
 `BookFormatFKbi` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key Into Format Table',
 `SeriesFKBi` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key into Series Table',
 PRIMARY KEY (`idBookInfo`, `TitleFKbi`, `AuthorFKbi`),
 UNIQUE INDEX `idBookInfo_UNIQUE` (`idBookInfo` ASC),
 INDEX `CategoryFKbI` (`CategoryFKbi` ASC),
 INDEX `AuthorFKbi` (`AuthorFKbi` ASC),
 INDEX `BookFormatFKBi` (`BookFormatFKbi` ASC),
 INDEX `SeriesFKBi` (`SeriesFKBi` ASC),
 INDEX `TitleFKbi` (`TitleFKbi` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`forsale`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`forsale` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`forsale` (
 `BookFKfs` INT(10) UNSIGNED NOT NULL,
 `IsForSale` TINYINT(4) NOT NULL DEFAULT '0',
 `AskingPrice` DOUBLE NOT NULL DEFAULT '0',
 `EstimatedValue` DOUBLE NOT NULL DEFAULT '0',
 PRIMARY KEY (`BookFKfs`),
 INDEX `BookFKfs` (`BookFKfs` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`haveread`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`haveread` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`haveread` (
 `BookFKhr` INT(10) UNSIGNED NOT NULL,
 `HaveReadBook` TINYINT(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`BookFKhr`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`isbn`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`isbn` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`isbn` (
 `BookFKiSBN` INT(10) UNSIGNED NOT NULL,
 `ISBNumber` VARCHAR(32) NULL DEFAULT NULL,
 PRIMARY KEY (`BookFKiSBN`),
 INDEX `ISBNumber` (`ISBNumber` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`owned`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`owned` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`owned` (
 `BookFKo` INT(10) UNSIGNED NOT NULL,
 `IsOwned` TINYINT(4) NOT NULL,
 `IsWishListed` TINYINT NOT NULL,
 PRIMARY KEY (`BookFKo`),
 INDEX `BookFKo` (`BookFKo` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`publishinginfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`publishinginfo` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`publishinginfo` (
 `BookFKPubI` INT(10) UNSIGNED NOT NULL COMMENT 'Foreign Key into the Book Info Table.',
 `Copyright` VARCHAR(4) NOT NULL,
 `Edition` INT(10) UNSIGNED NULL DEFAULT NULL,
 `Publisher` VARCHAR(45) NULL DEFAULT NULL,
 `OutOfPrint` TINYINT(4) NULL DEFAULT NULL COMMENT 'Is the book still being printed or has it lapsed.',
 `Printing` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'A book may be printed may times. This will indicate which printing it is. Check the back of the title page.',
 PRIMARY KEY (`BookFKPubI`),
 INDEX `BookFKPubI` (`BookFKPubI` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`purchaseinfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`purchaseinfo` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`purchaseinfo` (
 `BookFKPurI` INT(10) UNSIGNED NOT NULL,
 `PurchaseDate` DATE NULL DEFAULT NULL,
 `ListPrice` DOUBLE NULL DEFAULT NULL,
 `PaidPrice` DOUBLE NULL DEFAULT NULL,
 `Vendor` VARCHAR(64) NULL DEFAULT NULL,
 PRIMARY KEY (`BookFKPurI`),
 INDEX `BookFKPurI` (`BookFKPurI` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`series`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`series` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`series` (
 `idSeries` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `AuthorFK` INT(10) UNSIGNED NOT NULL COMMENT 'Foriegn Key into Author Table',
 `SeriesName` VARCHAR(128) NOT NULL,
 PRIMARY KEY (`idSeries`, `AuthorFK`, `SeriesName`),
 UNIQUE INDEX `idSeries_UNIQUE` (`idSeries` ASC),
 INDEX `AuthorFKs` (`AuthorFK` ASC),
 INDEX `SeriesTitle` (`SeriesName` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`signedbyauthor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`signedbyauthor` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`signedbyauthor` (
 `BookFKsba` INT(10) UNSIGNED NOT NULL,
 `IsSignedByAuthor` TINYINT(4) NULL DEFAULT NULL,
 PRIMARY KEY (`BookFKsba`),
 INDEX `BookFKsba` (`BookFKsba` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`title`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`title` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`title` (
 `idTitle` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `TitleStr` VARCHAR(128) NOT NULL,
 PRIMARY KEY (`idTitle`, `TitleStr`),
 UNIQUE INDEX `idTitle_UNIQUE` (`idTitle` ASC),
 INDEX `TitleStr` (`TitleStr` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `booklibinventory`.`volumeinseries`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `booklibinventory`.`volumeinseries` ;

CREATE TABLE IF NOT EXISTS `booklibinventory`.`volumeinseries` (
 `BookFKvs` INT(10) UNSIGNED NOT NULL,
 `SeriesFK` INT(10) UNSIGNED NOT NULL,
 `VolumeNumber` INT(10) UNSIGNED NULL DEFAULT NULL,
 PRIMARY KEY (`BookFKvs`),
 INDEX `BookFKvs` (`BookFKvs` ASC),
 INDEX `SeriesFKvs` (`SeriesFK` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `booklibinventory` ;

-- -----------------------------------------------------
-- function findAuthorKey
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findAuthorKey`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findAuthorKey`(
 firstName VARCHAR(20),
 lastName VARCHAR(20)
) RETURNS INT
BEGIN

 SET @authorKey = 0;

 SELECT COUNT(*) INTO @authorCount FROM authorstab;
 IF @authorCount > 0 THEN
 SELECT authorstab.idAuthors INTO @authorKey
 FROM authorstab
 WHERE authorsTab.LastName = lastName AND authorsTab.FirstName = firstName;
 IF @authorKey IS NULL THEN
 SET @authorKey = 0;
 END IF;
 END IF;

 RETURN @authorKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findBookKey
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findBookKey`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findBookKey`(
 authorLast VARCHAR(20),
 authorFirst VARCHAR(20),
 titleStr VARCHAR(128),
 formatStr VARCHAR(45)
) RETURNS INT
BEGIN

 SET @bookKey = 0;

 SET @authorKey = findauthorKey(authorFirst, authorLast);

 SET @titleKey = findTitleKey(titleStr);

 SET @formatKey = findFormatKeyFromStr(formatStr);

 IF @authorKey > 0 AND @titleKey > 0 THEN
 SET @bookKey = findBookKeyFromKeys(@authorKey, @titleKey, @formatKey);
 END IF;

 RETURN @bookKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findBookKeyFast
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findBookKeyFast`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findBookKeyFast`(
 authorLast VARCHAR(20),
 authorFirst VARCHAR(20),
 titleStr VARCHAR(128),
 formatStr VARCHAR(45)
) RETURNS INT
BEGIN

 /*
 * There may be multiple copies of a book in the library, one of each format.
 * Specifying the format makes it distinct.
 */

 SELECT BKI.idBookInfo INTO @bookKey FROM bookinfo as BKI
 INNER JOIN authorsTab AS a ON a.idAuthors = BKI.AuthorFKbi
 INNER JOIN title AS t ON t.idTitle = BKI.TitleFKbi
 INNER JOIN bookformat AS bf ON bf.idFormat = BKI.BookFormatFKBi
 WHERE a.LastName = authorLast AND a.FirstName = authorFirst AND t.TitleStr = titleStr and bf.FormatName = formatStr;

 IF @bookKey IS NULL THEN
 SET @bookKey = 0;
 END IF;


 RETURN @bookKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findBookKeyFromKeys
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findBookKeyFromKeys`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findBookKeyFromKeys`(
 authorKey INT,
 titleKey INT,
 formatKey INT
) RETURNS INT
BEGIN

 SET @bookKey = 0;

 IF authorKey > 0 AND titleKey > 0 then
 SELECT bookinfo.idBookInfo INTO @bookKey 
 FROM BookInfo 
 WHERE bookinfo.AuthorFKbi = authorKey AND bookinfo.TitleFKbi = titleKey AND bookinfo.BookFormatFKbi = formatKey;
 IF @bookKey IS NULL THEN
 SET @bookKey = 0;
 END IF;
 END IF;

 RETURN @bookKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function findTitleKey
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`findTitleKey`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `findTitleKey`(
 TitleStr VARCHAR(128)
) RETURNS INT
BEGIN

 SELECT title.idTitle INTO @titleKey FROM title WHERE title.TitleStr = TitleStr;
 IF @titleKey IS NULL THEN
 SET @titleKey = 0;
 END IF;

 RETURN @titleKey;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- function insertTitleIfNotExist
-- -----------------------------------------------------

USE `booklibinventory`;
DROP function IF EXISTS `booklibinventory`.`insertTitleIfNotExist`;

DELIMITER $$
USE `booklibinventory`$$
CREATE FUNCTION `insertTitleIfNotExist`(
 titleStr VARCHAR(128)
) RETURNS INT
BEGIN

 SET @titleKey = findTitleKey(titleStr);

 if @titleKey < 1 THEN
 INSERT INTO title (title.TitleStr) VALUES(titleStr);
 SET @titleKey := LAST_INSERT_ID();
 END IF;

 RETURN @titleKey;
END$$

DELIMITER ;


/*
 * Data inserts, deletions and updates.
 */

-- -----------------------------------------------------
-- procedure UpdateAuthor
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`UpdateAuthor`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `UpdateAuthor`(
 IN LastName VARCHAR(20),
 IN FirstName VARCHAR(20),
 IN MiddleName VARCHAR(20),
 IN DOB VARCHAR(4),
 IN DOD VARCHAR(4)
)
BEGIN

 UPDATE authorstab 
 SET 
 authorstab.MiddleName = MiddleName,
 authorstab.YearOfBirth = DOB,
 authorstab.YearOfDeath = DOD
 WHERE authorstab.LastName = LastName AND authorstab.FirstName = FirstName;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure addAuthor
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addAuthor`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addAuthor`(
 IN authorLastName VARCHAR(20),
 IN authorFirstName VARCHAR(20),
 IN authorMiddleName VARCHAR(20),
 IN dob VARCHAR(4),
 IN dod VARCHAR(4)
)
BEGIN

 INSERT INTO authorstab (authorstab.LastName, authorstab.FirstName, authorstab.MiddleName, authorstab.YearOfBirth, authorstab.YearOfDeath)
 VALUES(authorLastName, authorFirstName, authorMiddleName, dob, dod);
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure addBookToLibrary
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addBookToLibrary`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addBookToLibrary`
(
 IN categoryName VARCHAR(45),
 IN authorLastName VARCHAR(20),
 IN authorFirstName VARCHAR(20),
 IN titleStr VARCHAR(128), 
 IN bookFormatStr VARCHAR(45),
 IN copyright VARCHAR(4),
 IN edition INT,
 IN printing INT,
 IN publisher VARCHAR(45),
 IN outOfPrint TINYINT,
 IN seriesName VARCHAR(128),
 IN volumeNumber INT,
 IN iSBNumber VARCHAR(32),
 IN iSignedByAuthor TINYINT,
 IN isOwned TINYINT,
 IN isWishListed TINYINT,
 IN isForSale TINYINT,
 IN askingPrice DOUBLE,
 IN estimatedValue DOUBLE,
 IN haveRead TINYINT,
 IN bookDescription VARCHAR(1024),
 OUT bookKey INT
)
BEGIN

 -- All book data except for purchasing data will be added directly or indirectly from this procedure.
 -- Purchasing data will be handled outside of this procedure because the book may be added to a wishlist
 -- instead of added to the library.
 -- Each independent portion of the data will have it's own add procedure that will be called here.

 SET @titleKey = 0, @formatKey = 0, @authorKey = 0, @seriesKey = 0;

 SET @authorKey = findAuthorKey(authorFirstName, authorLastName);

 -- If the author isn't found then the user has to add the author before they add any books or
 -- Series by the author.
 if @authorKey > 0 then
 SET @formatKey = findFormatKeyFromStr(BookFormatStr);
 IF @formatKey > 0 THEN
 SET @seriesKey = findSeriesKeyByAuthKeyTitle(@authorKey, SeriesName);
 SET @titleKey = insertTitleIfNotExist(titleStr);
 SET @categoryKey = findCategoryKeyFromStr(categoryName);

 SET bookKey = findBookKeyFromKeys(@authorKey, @titleKey, @formatKey);
 IF bookKey < 1 THEN
 -- Don't add a book if it is already in the library. There will be special cases such as when a book has been signed by the author
 -- but these will be added later.
 INSERT INTO bookinfo (bookinfo.AuthorFKbi, bookinfo.TitleFKbi, bookinfo.CategoryFKbi, bookinfo.BookFormatFKbi, bookinfo.SeriesFKbi)
 VALUES (@authorKey, @titleKey, @categoryKey, @formatKey, @seriesKey);
 SET bookKey := LAST_INSERT_ID();

 CALL insertOrUpdatePublishing(bookKey, copyright, edition, printing, publisher, outOfPrint);
 IF iSBNumber IS NOT NULL OR LENGTH(iSBNumber) > 1 THEN
 -- Mass Market Paperback Books older than 1985 may not have an isbn printed on them any where.
 CALL insertOrUpdateISBN(bookKey, iSBNumber);
 END IF;
 CALL insertOrUpdateOwned(bookKey, isOwned, isWishListed);
 CALL insertOrUpdateHaveRead(bookKey, haveRead);
 CALL insertOrUpdateVolumeInSeries(bookKey, volumeNumber, @seriesKey);
 IF isOwned > 0 THEN
 CALL insertOrUpdateForSale(bookKey, isForSale, askingPrice, estimatedValue);
 END IF;
 CALL insertOrUpdateIsSignedByAuthor(bookKey, iSignedByAuthor);
 IF bookDescription IS NOT NULL OR LENGTH(bookDescription) > 0 THEN
 -- Try to save space if there is no description.
 CALL insertOrUpdateSynopsis(bookKey, bookDescription);
 END IF;
 END IF;

 END IF;
 END IF;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure buyBook
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`buyBook`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `buyBook`
(
 IN categoryName VARCHAR(45),
 IN authorLastName VARCHAR(20),
 IN authorFirstName VARCHAR(20),
 IN titleStr VARCHAR(128), 
 IN bookFormatStr VARCHAR(45),
 IN copyright VARCHAR(4),
 IN edition INT,
 IN printing INT,
 IN publisher VARCHAR(45),
 IN outOfPrint TINYINT,
 IN seriesName VARCHAR(128),
 IN volumeNumber INT,
 IN iSBNumber VARCHAR(32),
 IN iSignedByAuthor TINYINT,
 IN bookDescription VARCHAR(1024),
 IN purchaseDate DATE,
 IN listPrice DOUBLE,
 IN pricePaid DOUBLE,
 IN vendor VARCHAR(64),
 OUT bookKey INT -- allows the calling program or procedure to test for failure.
)
BEGIN

 SET @estimatedValue = listPrice - 1.00;

 SET @IsBookAlreadyInDB = findBookKeyFast(authorLastName, authorFirstName, TitleStr, bookFormatStr);
 IF @IsBookAlreadyInDB < 1 THEN
 -- The book was not already read or wishlisted.
 -- Some fields such as IsOwned are added by default because the book was purchased.
 CALL addBookToLibrary(
 categoryName,
 authorLastName,
 authorFirstName,
 titleStr, 
 bookFormatStr,
 copyright,
 edition,
 printing,
 publisher,
 outOfPrint,
 seriesName,
 volumeNumber,
 iSBNumber,
 iSignedByAuthor,
 1, -- IsOwned
 0, -- IsWishlisted
 0, -- IsForsale
 @estimatedValue, -- Asking Price
 @estimatedValue, -- Estimated Value
 0, -- HaveReadBook This is assumed to be false, this might be a bug.
 bookDescription,
 bookKey
 );
 IF bookKey IS NOT NULL AND bookKey > 0 THEN
 CALL insertOrUpdatePurchaseInfo(bookKey, purchaseDate, listPrice, pricePaid, vendor);
 END IF;
 ELSE
 SET bookKey = @IsBookAlreadyInDB;
 -- The book was wishlisted or already read, update any changes.
 CALL insertOrUpdatePurchaseInfo(bookKey, purchaseDate, listPrice, pricePaid, vendor);
 CALL insertOrUpdatePublishing(bookKey, copyright, edition, printing, publisher, outOfPrint);
 CALL insertOrUpdateOwned(bookKey, 1, 0);
 CALL insertOrUpdateForSale(bookKey, 0, @estimatedValue, @estimatedValue);
 CALL insertOrUpdateIsSignedByAuthor(bookKey, iSignedByAuthor);
 IF iSBNumber IS NOT NULL OR LENGTH(iSBNumber) > 1 THEN
 -- Mass Market Paperback Books older than 1985 may not have an isbn printed on them any where.
 CALL insertOrUpdateISBN(bookKey, iSBNumber);
 END IF;
 IF bookDescription IS NOT NULL OR LENGTH(bookDescription) > 0 THEN
 -- Try to save space if there is no description.
 CALL insertOrUpdateSynopsis(bookKey, bookDescription);
 END IF;
 END IF;


END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteAuthor
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`deleteAuthor`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `deleteAuthor`
(
 IN authorLast VARCHAR(20),
 IN authorFirst VARCHAR(20),
 IN authorMiddle VARCHAR(20)
)
BEGIN
 -- This procedure deletes everything associated with the specified author
 -- including books, series and volumes in series. It affects almost every table
 -- in this database.
 -- Do not delete formats and categories.

 DELETE a, BKI, s, v, i, sba, pub, pur, o, fs, hr, BDesk
 FROM authorstab AS a 
 LEFT JOIN series AS s ON s.AuthorFK = a.idAuthors
 LEFT JOIN volumeinseries AS v ON v.SeriesFK = s.idSeries
 INNER JOIN bookinfo AS BKI ON BKI.AuthorFKbi = a.idAuthors
 LEFT JOIN isbn AS i ON i.BookFKiSBN = BKI.idBookInfo
 LEFT JOIN signedbyauthor AS sba ON sba.BookFKsba = BKI.idBookInfo
 LEFT JOIN publishinginfo AS pub ON pub.BookFKPubI = BKI.idBookInfo
 LEFT JOIN purchaseinfo AS pur ON pur.BookFKPurI = BKI.idBookInfo
 LEFT JOIN owned AS o ON o.BookFKo = BKI.idBookInfo
 LEFT JOIN forsale AS fs ON fs.BookFKfs = BKI.idBookInfo
 LEFT JOIN haveread AS hr ON hr.BookFKhr = BKI.idBookInfo
 LEFT JOIN bksynopsis AS BDesk ON BDesk.BookFKbd = BKI.idBookInfo 
 WHERE a.LastName = authorLast AND a.FirstName = authorFirst AND a.MiddleName = authorMiddle;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure deleteBook
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`deleteBook`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `deleteBook`
(
 IN authorLast VARCHAR(20),
 IN authorFirst VARCHAR(20),
 IN titleStr VARCHAR(128),
 IN formatStr VARCHAR(45)
)
BEGIN

 -- Do not delete authors, titles, series, formats or categories. These may be shared with other books.

 DELETE BKI, i, sba, pub, pur, v, o, fs, hr, BDesk
 FROM bookinfo AS BKI 
 INNER JOIN authorsTab AS a ON a.idAuthors = BKI.AuthorFKbi
 INNER JOIN title AS t ON t.idTitle = BKI.TitleFKbi
 INNER JOIN bookformat AS bf ON bf.idFormat = BKI.BookFormatFKBi
 LEFT JOIN isbn AS i ON i.BookFKiSBN = BKI.idBookInfo
 LEFT JOIN signedbyauthor AS sba ON sba.BookFKsba = BKI.idBookInfo
 LEFT JOIN publishinginfo AS pub ON pub.BookFKPubI = BKI.idBookInfo
 LEFT JOIN purchaseinfo AS pur ON pur.BookFKPurI = BKI.idBookInfo
 LEFT JOIN volumeinseries AS v ON v.BookFKvs = BKI.idBookInfo
 LEFT JOIN owned AS o ON o.BookFKo = BKI.idBookInfo
 LEFT JOIN forsale AS fs ON fs.BookFKfs = BKI.idBookInfo
 LEFT JOIN haveread AS hr ON hr.BookFKhr = BKI.idBookInfo
 LEFT JOIN bksynopsis AS BDesk ON BDesk.BookFKbd = BKI.idBookInfo 
 WHERE a.LastName = authorLast AND a.FirstName = authorFirst AND t.TitleStr = titleStr and bf.FormatName = formatStr;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure insertOrUpdatePublishing
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`insertOrUpdatePublishing`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `insertOrUpdatePublishing` 
(
 IN bookKey INT,
 IN copyright VARCHAR(4),
 IN edition INT,
 IN printing INT,
 IN publisher VARCHAR(45),
 IN outOfPrint TINYINT
)
BEGIN

 -- DECLARE testCopyright VARCHAR(4);

 SET @testKey = NULL;
 SELECT publishinginfo.Copyright INTO @testCopyright FROM publishinginfo WHERE publishinginfo.BookFKPubI = bookKey;

 IF @testCopyright IS NULL THEN
 INSERT INTO publishinginfo (
 publishinginfo.BookFKPubI,
 publishinginfo.Copyright,
 publishinginfo.Edition,
 publishinginfo.Printing,
 publishinginfo.Publisher,
 publishinginfo.OutOfPrint
 )
 VALUES(
 bookKey,
 copyright,
 edition,
 printing,
 publisher,
 outOfPrint
 )
 ;
 ELSE
 UPDATE publishinginfo
 SET
 publishinginfo.Copyright = copyright,
 publishinginfo.Edition = edition,
 publishinginfo.Printing = printing,
 publishinginfo.Publisher = publisher,
 publishinginfo.OutOfPrint = outOfPrint
 WHERE publishinginfo.BookFKPubI = bookKey;
 END IF;
END$$

DELIMITER ;
-- -----------------------------------------------------
-- procedure addCategory
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addCategory`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addCategory`
(
 categoryName VARCHAR(45)
)
BEGIN

 SET @categoryKey = NULL;

 SELECT bookcategories.idBookCategories INTO @categoryKey
 FROM bookcategories
 WHERE bookcategories.CategoryName = categoryName;

 -- Prevent adding the same category again to avoid breaking the unique key structure.

 IF @categoryKey IS NULL THEN
 INSERT INTO bookcategories (bookcategories.CategoryName) VALUES(categoryName);
 END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure addFormat
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`addFormat`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `addFormat` (IN bookFormatStr VARCHAR(45))
BEGIN

 SET @formatKey = findFormatKeyFromStr(bookFormatStr);

 -- Prevent adding the same format again to avoid breaking the unique key structure.
 IF @formatKey < 1 THEN
 INSERT INTO bookformat (bookformat.FormatName) VALUES(bookFormatStr);
 END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getAllBooks
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBooks`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBooks`()
BEGIN

 SELECT
 a.LastName,
 a.FirstName,
 t.TitleStr,
 bf.FormatName,
 BCat.CategoryName,
 i.ISBNumber,
 pub.Copyright,
 pub.Edition,
 pub.Publisher,
 pub.OutOfPrint,
 pub.Printing,
 s.SeriesName,
 v.VolumeNumber,
 pur.PurchaseDate,
 pur.ListPrice,
 pur.PaidPrice,
 pur.Vendor,
 sba.IsSignedByAuthor,
 o.IsOwned,
 o.IsWishListed,
 hr.HaveReadBook,
 fs.IsForSale,
 fs.AskingPrice,
 fs.EstimatedValue,
 BDesk.StoryLine
 FROM bookinfo AS BKI 
 INNER JOIN authorsTab AS a ON a.idAuthors = BKI.AuthorFKbi
 INNER JOIN title AS t ON t.idTitle = BKI.TitleFKbi
 INNER JOIN bookformat AS bf ON bf.idFormat = BKI.BookFormatFKBi
 INNER JOIN bookcategories AS BCat ON BCat.idBookCategories = BKI.CategoryFKbI
 LEFT JOIN isbn AS i ON i.BookFKiSBN = BKI.idBookInfo
 LEFT JOIN signedbyauthor AS sba ON sba.BookFKsba = BKI.idBookInfo
 LEFT JOIN publishinginfo AS pub ON pub.BookFKPubI = BKI.idBookInfo
 LEFT JOIN purchaseinfo AS pur ON pur.BookFKPurI = BKI.idBookInfo
 LEFT JOIN series AS s ON s.idSeries = BKI.SeriesFKbi
 LEFT JOIN volumeinseries AS v ON v.BookFKvs = BKI.idBookInfo
 LEFT JOIN owned AS o ON o.BookFKo = BKI.idBookInfo
 LEFT JOIN forsale AS fs ON fs.BookFKfs = BKI.idBookInfo
 LEFT JOIN haveread AS hr ON hr.BookFKhr = BKI.idBookInfo
 LEFT JOIN bksynopsis AS BDesk ON BDesk.BookFKbd = BKI.idBookInfo 
 ORDER BY BCat.CategoryName, a.LastName, a.FirstName, s.SeriesName, v.VolumeNumber, t.TitleStr;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getAllBookCategoriesWithKeys
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBookCategoriesWithKeys`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBookCategoriesWithKeys` ()
BEGIN

/*
 * Example usage would be to get all the categories to CREATE a control that embeds the primary key rather than the text.
 */

 SELECT bookcategories.CategoryName, bookcategories.idBookCategories FROM bookcategories;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getAllBookFormatsWithKeys
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBookFormatsWithKeys`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBookFormatsWithKeys`()
BEGIN

/*
 * Example usage would be to get all the formats to CREATE a control embeds the primary key rather than the text.
 */

 SELECT bookformat.FormatName, bookformat.idFormat FROM bookformat;

END$$

DELIMITER ;

/*
 * Start of functions that allow the user to update books in a limited manner.
 */

-- -----------------------------------------------------
-- procedure putBookUpForSale
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`putBookUpForSale`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `putBookUpForSale`
(
 IN authorFirstName VARCHAR(20),
 IN authorLastName VARCHAR(20),
 IN bookTitle VARCHAR(128),
 IN bookFormat VARCHAR(45),
 IN askingPrice DOUBLE,
 IN estimatedValue DOUBLE
)
BEGIN

 SET @isForSale = 1;

 SET @bookKey = findBookKeyFast(authorLastName, authorFirstName, bookTitle, bookFormat);

 CALL insertOrUpdateForSale(@bookKey, @isForSale, askingPrice, estimatedValue);

END$$

DELIMITER ;

/*
 * Once only code called during installation or testing.
 */

-- -----------------------------------------------------
-- procedure initBookInventoryTool
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`initBookInventoryTool`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `initBookInventoryTool` ()
BEGIN

-- Initialize some basic formats, user can add more later.
 CALL addFormat('Hardcover');
 CALL addFormat('Trade Paperback');
 CALL addFormat('Mass Market Paperback');
 CALL addFormat('eBook PDF');
 CALL addFormat('eBook Kindle');
 CALL addFormat('eBook iBooks');
 CALL addFormat('eBook EPUB');
 CALL addFormat('eBook HTML');

-- Initialize some basic categories, user can add more later.
 CALL addCategory('Non-Fiction');
 CALL addCategory('Non-Fiction: Biography');
 CALL addCategory('Non-Fiction: Biology');
 CALL addCategory('Non-Fiction: Computer');
 CALL addCategory('Non-Fiction: Electrical Engineering');
 CALL addCategory('Non-Fiction: History');
 CALL addCategory('Textbook');
 CALL addCategory('Poetry');
 CALL addCategory('Art');
 CALL addCategory('Dictionary');
 CALL addCategory('Encyclopedia');
 CALL addCategory('Fiction');
 CALL addCategory('Fiction: Anime');
 CALL addCategory('Fiction: Fantasy');
 CALL addCategory('Fiction: Horror');
 CALL addCategory('Fiction: Romance');
 CALL addCategory('Fiction: Science Fiction');
 CALL addCategory('Fiction: Western');

END$$

DELIMITER ;

/*
 * Unit testing procedures.
 */

-- -----------------------------------------------------
-- procedure zzzUnitTestAddAuthors
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestAddAuthors`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestAddAuthors` ()
BEGIN
SET @procName = 'zzzUnitTestAddAuthors';

 CALL addAuthor('Heinlein', 'Robert', 'Anson', '1907', '1988');
 CALL addAuthor('Asimov', 'Isaac', NULL, '1920', '1992');
 CALL addAuthor('Clarke', 'Arthur', 'Charles', '1917', '2008');
 CALL addAuthor('Le Guin', 'Ursula', 'Kroeber', '1929', '2018');
 CALL addAuthor('Bradbury', 'Ray', 'Douglas ', '1920', '2012');
 CALL addAuthor('Dick', 'Philip', 'Kindred', '1928', '1982');
 CALL addAuthor('Wells', 'Herbert', 'George', '1866', '1946');
 CALL addAuthor('Silverberg', 'Robert', NULL, '1935', NULL);
 CALL addAuthor('Zimmer Bradley', 'Marion', 'Eleanor', '1930', '1999');
 CALL addAuthor('Norton', 'Andre', 'Alice', '1912', '2005');
 CALL addAuthor('Drake', 'David', NULL, '1945', NULL);
 CALL addAuthor('Weber', 'David', 'Mark', '1952', NULL);
 CALL addAuthor('Baxter', 'Stephen', NULL, '1957', NULL);
 CALL addAuthor('Knuth', 'Donald', 'Ervin', '1938', NULL);

 IF (SELECT COUNT(*) FROM authorstab) != 14 THEN
 SELECT @procName, COUNT(*) FROM series;
 SELECT * FROM series;
 END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestAddAuthorSeries
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestAddAuthorSeries`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestAddAuthorSeries` ()
BEGIN
SET @procName = 'zzzUnitTestAddAuthorSeries';

 CALL addAuthorSeries('David', 'Weber', 'Safehold');
 CALL addAuthorSeries('David', 'Weber', 'Honor Harrington');
 CALL addAuthorSeries('David', 'Weber', 'Honorverse');
 CALL addAuthorSeries('Marion', 'Zimmer Bradley', 'Darkover');
 CALL addAuthorSeries('Isaac', 'Asimov', 'Foundation');
 CALL addAuthorSeries('Stephen', 'Baxter', 'Northland');
 CALL addAuthorSeries('Donald', 'Knuth', 'The Art of Computer Programming');
-- The follow statement should fail to insert the series since John Ringo has not been added to authorstab.
 CALL addAuthorSeries('John', 'Ringo', 'Kildar');

 IF (SELECT COUNT(*) FROM series) != 7 THEN
 SELECT @procName, COUNT(*) FROM series;
 SELECT * FROM series;
 END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestAddBookToLibrary
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestAddBookToLibrary`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestAddBookToLibrary` ()
BEGIN
/*
 * The following procedures are tested by this procedure.
 * addBookToLibrary
 * insertOrUpdatePublishing
 * insertOrUpdateOwned
 * insertOrUpdateHaveRead
 * insertOrUpdateVolumeInSeries
 * insertOrUpdateForSale()
 * insertOrUpdateIsSignedByAuthor
 * insertOrUpdateSynopsis
 * insertOrUpdateISBN
 * insertOrUpdatePurchaseInfo
 *
 * The following functions are tested by this procedure:
 * findAuthorKey
 * findFormatKeyFromStr
 * findSeriesKeyByAuthKeyTitle
 * insertTitleIfNotExist
 * findCategoryKeyFromStr
 * findBookKeyFromKeys
 *
 */

 DECLARE bookKey INT;

SET @procName = 'zzzUnitTestAddBookToLibrary';

 CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'On Basilisk Station', 'Mass Market Paperback', '1993', 1, 9, 'Baen Books', 0, 'Honor Harrington', 1,
 '0-7434-3571-0', 0, 1, 0, 0, 8.99, 8.99, 1, 'bookDescription', bookKey);
 IF (bookKey != 1) THEN
 SELECT @procName, bookKey;
 SELECT COUNT(*) FROM bookinfo;
 END IF;

 CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'Honor of the Queen', 'Mass Market Paperback', '1993', 1, 10, 'Baen Books', 0, 'Honor Harrington', 2,
 '978-0-7434-3572-7', 0, 1, 0, 0, 6.99, 6.99, 1, NULL, bookKey);
 IF (bookKey != 2) THEN
 SELECT @procName, bookKey;
 SELECT COUNT(*) FROM bookinfo;
 END IF;

 CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'Short Victorious War', 'Mass Market Paperback', '1994', 1, 8, 'Baen Books', 0, 'Honor Harrington', 3,
 '0-7434-3573-7', 0, 1, 0, 0, 6.99, 6.99, 1, NULL, bookKey);
 IF (bookKey != 3) THEN
 SELECT @procName, bookKey;
 SELECT COUNT(*) FROM bookinfo;
 END IF;

 CALL addBookToLibrary('Fiction: Science Fiction', 'Weber', 'David', 'Field of Dishonor', 'Mass Market Paperback', '1994', 1, 6, 'Baen Books', 0, 'Honor Harrington', 4,
 '0-7434-3574-5', 0, 1, 0, 0, 7.99, 7.99, 1, NULL, bookKey);
 IF (bookKey != 4) THEN
 SELECT @procName, bookKey;
 SELECT COUNT(*) FROM bookinfo;
 END IF;

 CALL addBookToLibrary('Fiction: Science Fiction', 'Norton', 'Andre', 'Star Guard', 'Mass Market Paperback', '1955', 1, NULL, 'Harcourt', 0, NULL, NULL,
 NULL, 0, 0, 1, NULL, NULL, NULL, 1, NULL, bookKey);
 IF (bookKey != 5) THEN
 SELECT @procName, bookKey;
 SELECT COUNT(*) FROM bookinfo;
 END IF;

 -- The following statement should fail to add a book since David Brin is not in authorstab.
 -- The failure is indicated by bookKey being zero.
 CALL addBookToLibrary('Fiction: Science Fiction', 'Brin', 'David', 'Uplift War', 'Hard Cover', '1987', 1, 1, 'Phantasia Press', 0, NULL, NULL,
 0-932096-44-1, 1, 1, 0, 0, 100.00, 100.00, 1, NULL, bookKey);
 IF (bookKey != 0) THEN
 SELECT @procName, bookKey;
 SELECT COUNT(*) FROM bookinfo;
 END IF;
 IF (SELECT COUNT(*) FROM bookinfo) != 5 THEN
 SELECT @procName, COUNT(*) FROM bookInfo;
 SELECT * FROM bookInfo;
 END IF;

 IF (SELECT COUNT(*) FROM publishinginfo) != 5 THEN
 SELECT @procName, COUNT(*) FROM publishinginfo;
 SELECT * FROM publishinginfo;
 END IF;

 IF (SELECT COUNT(*) FROM bksynopsis) != 1 THEN
 SELECT @procName, COUNT(*) FROM bksynopsis;
 SELECT * FROM bksynopsis;
 END IF;

 IF (SELECT COUNT(*) FROM forsale) != 4 THEN
 SELECT @procName, COUNT(*) FROM forsale;
 SELECT * FROM forsale;
 END IF;

 IF (SELECT COUNT(*) FROM haveread) != 5 THEN
 SELECT @procName, COUNT(*) FROM haveread;
 SELECT * FROM haveread;
 END IF;

 IF (SELECT COUNT(*) FROM owned) != 5 THEN
 SELECT @procName, COUNT(*) FROM owned;
 SELECT * FROM owned;
 END IF;

 IF (SELECT COUNT(*) FROM signedbyauthor) != 5 THEN
 SELECT @procName, COUNT(*) FROM signedbyauthor;
 SELECT * FROM signedbyauthor;
 END IF;

 IF (SELECT COUNT(*) FROM isbn) != 4 THEN
 SELECT @procName, COUNT(*) FROM isbn;
 SELECT * FROM isbn;
 END IF;

 IF (SELECT COUNT(*) FROM purchaseinfo) != 0 THEN
 SELECT @procName, COUNT(*) FROM purchaseinfo;
 SELECT * FROM purchaseinfo;
 END IF;

 IF (SELECT COUNT(*) FROM title) != 5 THEN
 SELECT @procName, COUNT(*) FROM title;
 SELECT * FROM title;
 END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestUserUpdates
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestUserUpdates`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestUserUpdates` ()
BEGIN

/*
 * This procedure tests the buyBook procedure. Since the buyBook procedure call addBookToLibrary, everything tested
 * by zzzUnitTestAddBookToLibrary is also tested by this procedure.
 *
 */

 DECLARE bookKey INT;
 SET @procName = 'zzzUnitTestUserUpdates';

 SELECT COUNT(*) INTO @forSaleCount FROM forsale WHERE forsale.IsForSale = 1;
 CALL putBookUpForSale('David', 'Weber', 'Honor of the Queen', 'Mass Market Paperback', 10.99, 7.99);
 IF (SELECT COUNT(*) FROM forsale WHERE forsale.IsForSale = 1) != (@forSaleCount + 1) THEN
 SELECT @procName, COUNT(*) FROM forsale;
 SELECT * FROM forsale;
 END IF;
 SELECT COUNT(*) INTO @forSaleCount FROM forsale;
 -- CALL getAllBooksForSale();

 SELECT COUNT(*) INTO @haveReadCount FROM haveread WHERE haveread.HaveReadBook = 1;
 CALL finishedReadingBook('Stephen', 'Baxter', 'Stone Spring', 'Mass Market Paperback');
 CALL finishedReadingBook('Stephen', 'Baxter', 'Bronze Summer', 'Mass Market Paperback');
 IF (SELECT COUNT(*) FROM haveread WHERE haveread.HaveReadBook = 1) != (@haveReadCount + 2) THEN
 SELECT @procName, COUNT(*) FROM haveread;
 SELECT * FROM haveread;
 END IF;
 -- CALL getAllBooksThatWereRead();

 CALL bookSold('David', 'Weber', 'Honor of the Queen', 'Mass Market Paperback');
 IF (SELECT COUNT(*) FROM forsale) != (@forSaleCount - 1) THEN
 SELECT @procName, COUNT(*) FROM forsale;
 SELECT * FROM forsale;
 END IF;

 -- Test update buy buying wish listed book.
 Set @buyDate = CURDATE();
 CALL buyBook('Fiction: Science Fiction', 'Norton', 'Andre', 'Star Guard', 'Mass Market Paperback', '1955', 3, 4, 'Harcourt', 0, NULL, NULL,
 '978-0-345-35036-7', 0, 'Testing 1 2 3', @buyDate, 7.99, 7.99, 'Amazon', bookKey);

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzUnitTestFunctions
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestFunctions`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestFunctions` ()
BEGIN

SET @procName = 'zzzUnitTestFunctions';

 /*
 * The functions not explicitly tested here are tested indirectly 
 * through the function calls here with the exception of insertTitleIfNotExist
 */

 SET @authorKey = findAuthorKey('Arthur','Clarke');
 IF @authorKey != 3 THEN
 SELECT @procName, @authorKey;
 SELECT authorstab.FirstName, authorstab.LastName FROM authorstab WHERE idAuthors = @authorKey;
 END IF;

 SET @bookKey = findBookKeyFast('Baxter', 'Stephen', 'Stone Spring', 'Mass Market Paperback');
 IF (@bookKey != 6) THEN
 SELECT @procName, @bookKey;
 SELECT * FROM bookinfo WHERE bookinfo.idBookInfo = @bookKey;
 END IF;

 SET @titleKey = findTitleKey('Star Guard');
 IF (@titleKey != 5) THEN
 SELECT @procName, @titleKey;
 SELECT * FROM title WHERE title.idTitle = @titleKey;
 END IF;

 SET @categoryKey = findCategoryKeyFromStr('Non-Fiction: Electrical Engineering');
 IF (@categoryKey != 5) THEN
 SELECT @procName, @categoryKey;
 SELECT * FROM bookcategories; -- WHERE bookcategories.idBookCategories = @categoryKey;
 END IF;

 SET @formatKey = findFormatKeyFromStr('Mass Market Paperback');
 IF (@formatKey != 3) THEN
 SELECT @procName, @formatKey;
 SELECT * FROM bookformat WHERE bookformat.idFormat = @formatKey;
 END IF;

 SET @seriesKey = findSeriesKey('David', 'Weber', 'Honorverse');
 IF (@seriesKey != 3) THEN
 SELECT @procName, @seriesKey;
 SELECT * FROM series WHERE series.idSeries = @seriesKey;
 END IF;

END$$

DELIMITER ; 
-- -----------------------------------------------------
-- procedure zzzUnitTestDelete
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzUnitTestDelete`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzUnitTestDelete` ()
BEGIN

 SELECT COUNT(*) INTO @bookCount FROM bookinfo;

 CALL deleteBook('Weber', 'David', 'Honor of the Queen', 'Mass Market Paperback');

 IF (SELECT COUNT(*) FROM bookinfo) != (@bookCount - 1) THEN
 SELECT * FROM bookinfo;
 END IF;
 SET @bookCount = @bookCount - 1;
 IF (SELECT COUNT(*) FROM isbn) > @bookCount THEN
 SELECT * FROM isbn;
 END IF;
 IF (SELECT COUNT(*) FROM signedbyauthor) > @bookCount THEN
 SELECT * FROM signedbyauthor;
 END IF;
 IF (SELECT COUNT(*) FROM haveread) > @bookCount THEN
 SELECT * FROM haveread;
 END IF;
 IF (SELECT COUNT(*) FROM forsale) > @bookCount THEN
 SELECT * FROM forsale;
 END IF;

 SELECT COUNT(*) INTO @bookCount FROM bookinfo;
 SELECT COUNT(*) INTO @seriesCount FROM series;
 SELECT COUNT(*) INTO @authorCount FROM authorstab;

 CALL deleteAuthor('Knuth', 'Donald', 'Ervin');

 IF (SELECT COUNT(*) FROM bookinfo) != (@bookCount - 3) THEN
 SELECT * FROM bookinfo;
 END IF;
 IF (SELECT COUNT(*) FROM series) != (@seriesCount - 1) THEN
 SELECT * FROM series;
 END IF;
 IF (SELECT COUNT(*) FROM authorstab) != (@authorsCount - 1) THEN
 SELECT * FROM authors;
 END IF;
 SET @bookCount = @bookCount - 3;
 IF (SELECT COUNT(*) FROM haveread) > @bookCount THEN
 SELECT * FROM haveread;
 END IF;
 IF (SELECT COUNT(*) FROM forsale) > @bookCount THEN
 SELECT * FROM forsale;
 END IF;
 IF (SELECT COUNT(*) FROM owned) > @bookCount THEN
 SELECT * FROM owned;
 END IF;
 IF (SELECT COUNT(*) FROM purchaseinfo) > @bookCount THEN
 SELECT * FROM purchaseinfo;
 END IF;
 IF (SELECT COUNT(*) FROM publishinginfo) > @bookCount THEN
 SELECT * FROM publishinginfo;
 END IF;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure zzzRunAllUnitTests
-- -----------------------------------------------------

USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`zzzRunAllUnitTests`;

DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `zzzRunAllUnitTests` ()
BEGIN
 /*
 * The unit tests are in a specific order. Data from the early test procedures
 * is required by the later test procedures.
 *
 * The general functionality of the unit tests is to run the procedures or functions
 * and then test values that would be affected by the routine. If the test failed
 * then a select is run to show the error. No output means no errors.
 */

 SET @ShowAllResults = 1;

 CALL zzzUnitTestInitProcedure();
 CALL zzzUnitTestAddAuthors();
 CALL zzzUnitTestAddAuthorSeries();
 CALL zzzUnitTestAddBookToLibrary();
 CALL zzzUnitTestBuyBook();
 CALL zzzUnitTestFunctions();

 CALL addMoreBooksForInterst();

 -- Test all the data retrieval procedures to see that they return data rows.
 -- These tests by default will provide output.
 IF @showAllResults > 0 THEN
 CALL getAllBookFormatsWithKeys();
 CALL getAllBookCategoriesWithKeys();
 CALL getAllBooksInLib(); -- Test selecting all fields
 CALL getAllBooksByThisAuthor('Baxter', 'Stephen');
 CALL getAllWishListBooks();
 CALL getAllBooksThatWereRead();
 CALL getThisAuthorsData('Norton','Andre');
 CALL getAllSeriesByThisAuthor('Weber', 'David');
 CALL getAllSeriesData();
 CALL getAllAuthorsData();
 CALL getBookData('Weber', 'David', 'Honor of the Queen', 'Mass Market Paperback');
 CALL getAuthorDataByLastName('Asimov'); -- This could be changed if more authors are added, such as all the Greens.
 CALL getAllBooksSignedByAuthor();
 END IF;

 CALL zzzUnitTestUserUpdates();
 CALL getAllBooks(); -- Test selecting all fields all books
 CALL zzzUnitTestDelete ();
 CALL getAllBooks(); -- Test selecting all fields all books

END$$

DELIMITER ;

CALL booklibinventory.zzzRunAllUnitTests();

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;