--                         Unit Testing for MySQL
--                                   by
--                          Warren Willmey 2006
--                                V 1.0.1
--
-- Dont forget to give web user Call Procedure access rights or the PHP wont be able to see them!
--
-- SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES;
--
-- Run this script file from the MySQL console with the following line:
--		source src/utMySQL_pkg_TestSuite.sql
--
delimiter //
--
-- ---------------------------------------------------------------------------
--
DROP PROCEDURE IF EXISTS utAssert_eq //
--
CREATE PROCEDURE utAssert_eq( IN sAssertionMessage MEDIUMTEXT
							, IN xExpectedResult   MEDIUMTEXT
							, IN xActualResult     MEDIUMTEXT )
							COMMENT 'utMySQL 1.0.1 test frame work by Warren Willmey 2006'
							
BEGIN
	--
	DECLARE bFailed    BOOLEAN DEFAULT TRUE;
	DECLARE iNullCount INTEGER DEFAULT    0;
	--
	SET @utMysqlTotalAssertions = @utMysqlTotalAssertions + 1;
	--
	--
	IF xExpectedResult is NULL THEN
	  --
	  SET iNullCount = iNullCount + 1;
	  --
	END IF;
	--
	IF xActualResult is NULL THEN
	  --
	  SET iNullCount = iNullCount + 1;
	  --
	END IF;
	--
	--
	IF iNullCount = 0 THEN
	  --
	  -- Both xExpectedResult & xActualResult are not NULL, so values are ok to compare.
	  --
	  IF STRCMP( BINARY( xExpectedResult ), BINARY( xActualResult ) ) = 0 THEN
	    --
	    SET bFailed = FALSE;
	    --
	  END IF;
	  --
	ELSEIF iNullCount = 2 THEN
	  --
	  SET bFailed = FALSE;
	  --
	END IF;
	--
	--
	IF bFailed THEN
	  --
	  SET @utMysqlFailedAssertions = @utMysqlFailedAssertions + 1;
	  --
	  SELECT  SUBSTRING( @utMysqlTestProcName, 6 ) 'Unit Test Name'
	  		, sAssertionMessage    'utAssert_eq Message'
	  		, xExpectedResult      'Expected Result'
	  		, xActualResult        'Actual Result';
	  --
	END IF;
	--
END
//
--
-- ---------------------------------------------------------------------------
--
DROP PROCEDURE IF EXISTS utAssert_notEq //
--
CREATE PROCEDURE utAssert_notEq ( IN sAssertionMessage MEDIUMTEXT
								, IN xExpectedResult   MEDIUMTEXT
								, IN xActualResult     MEDIUMTEXT )
							    COMMENT 'utMySQL 1.0.1 test frame work by Warren Willmey 2006'
BEGIN
	--
	DECLARE bFailed    BOOLEAN DEFAULT TRUE;
	DECLARE iNullCount INTEGER DEFAULT    0;
	--
	SET @utMysqlTotalAssertions = @utMysqlTotalAssertions + 1;
	--
	--
	--
	IF xExpectedResult is NULL THEN
	  --
	  SET iNullCount = iNullCount + 1;
	  --
	END IF;
	--
	IF xActualResult is NULL THEN
	  --
	  SET iNullCount = iNullCount + 1;
	  --
	END IF;
	--
	--
	IF iNullCount = 0 THEN
	  --
	  -- Both xExpectedResult & xActualResult are not NULL, so values are ok to compare.
	  --
	  IF STRCMP( BINARY( xExpectedResult ), BINARY( xActualResult ) ) != 0 THEN
	    --
	    SET bFailed = FALSE;
	    --
	  END IF;
	  --
	ELSEIF iNullCount = 1 THEN
	  --
	  SET bFailed = FALSE;
	  --
	END IF;
	--
	--
	IF bFailed THEN
	  --	
	  SET @utMysqlFailedAssertions = @utMysqlFailedAssertions + 1;
	  --
	  SELECT  SUBSTRING( @utMysqlTestProcName, 6 ) 'Unit Test Name'
	  		, sAssertionMessage    'utAssert_notEq Message'
	  		, xExpectedResult      'Expected Result'
	  		, xActualResult        'Actual Result';
	  --
	END IF;
	--
END
//
--
-- ---------------------------------------------------------------------------
--
DROP PROCEDURE IF EXISTS utMySQL_pkg_TestSuite //
--
CREATE PROCEDURE utMySQL_pkg_TestSuite( ) COMMENT 'utMySQL 1.0.1 test frame work by Warren Willmey 2006'
utMySQL_pkg_TestSuite: BEGIN
    --
	DECLARE iTimeStamp TIMESTAMP DEFAULT CAST((NOW() + 0) AS UNSIGNED);
    DECLARE bDone BOOLEAN DEFAULT FALSE;
	DECLARE sProcName VARCHAR( 255 );
    DECLARE bCallut_SetUp    BOOLEAN DEFAULT FALSE;
    DECLARE bCallut_TearDown BOOLEAN DEFAULT FALSE;
	--
	DECLARE curProcedureNames CURSOR FOR
	SELECT    routine_name
	  FROM    information_schema.routines
	 WHERE    routine_schema  = DATABASE()
	   AND    routine_type    = 'PROCEDURE'
	   AND    routine_name   != 'ut_SetUp'
	   AND    routine_name   != 'ut_TearDown'
	   AND    routine_name   LIKE 'ut\_%'
	 ORDER BY routine_name   ASC;
	--
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET bDone = TRUE;
	--
	SELECT CONCAT( DATABASE(), ' : ', NOW() ) 'Starting MySQL Test Suite';
	--
	SET @utMysqlFailedAssertions = 0;
	SET @utMysqlTotalAssertions  = 0;
	SET @utMysqlTestProcName     = 'CALL utMySQL_pkg_SelfTest()';
	--
	CALL utMySQL_pkg_SelfTest();
	--
	IF 0 != @utMysqlFailedAssertions THEN
	  --
	  SELECT 'Critical Error, self test failed.' Panic;
	  --
	  LEAVE utMySQL_pkg_TestSuite;
	  --
	END IF;
	--
	SET @utMysqlFailedAssertions = 0;
	SET @utMysqlTotalAssertions  = 0;
	SET @utMysqlTestProcName     = '';
	--
	SELECT  TRUE
	  INTO  bCallut_SetUp
	  FROM  information_schema.routines
	 WHERE  routine_schema = DATABASE()
	   AND  routine_name   = 'ut_SetUp';	
	--
	SELECT  TRUE
	  INTO  bCallut_TearDown
	  FROM  information_schema.routines
	 WHERE  routine_schema = DATABASE()
	   AND  routine_name   = 'ut_TearDown';	
	--
	PREPARE sCallut_SetUp    FROM 'CALL ut_SetUp()';
	PREPARE sCallut_TearDown FROM 'CALL ut_TearDown()';
	--
	OPEN curProcedureNames;
	--
    SET bDone = FALSE;
	REPEAT
		--
		FETCH curProcedureNames INTO sProcName;
		--
		IF NOT bDone THEN
			--
			IF bCallut_SetUp THEN
				EXECUTE sCallut_SetUp;
			END IF;
			--
		  	SELECT sProcName 'Running unit test:';
		  	--
		  	SET @utMysqlTestProcName = concat( 'CALL ', sProcName, '()' );
		  	--
		  	PREPARE sDynamicSQL FROM @utMysqlTestProcName;
		  	EXECUTE sDynamicSQL;
		  	DEALLOCATE PREPARE sDynamicSQL;
		  	--
		  	IF bCallut_TearDown THEN
				EXECUTE sCallut_TearDown;
			END IF;
			--
		END IF;
		--
	UNTIL bDone END REPEAT;
    --
    CLOSE curProcedureNames;
    --
  	DEALLOCATE PREPARE sCallut_SetUp;
	DEALLOCATE PREPARE sCallut_TearDown;
    --
    SELECT @utMysqlTotalAssertions 'Total Assertions:', @utMysqlFailedAssertions 'Assertions Failed:';
	--
	SELECT CONCAT( ( TIMESTAMPDIFF( second, iTimeStamp, CAST((NOW() + 0) AS UNSIGNED) ) ), 's' ) 'Time Taken:';
	--
END utMySQL_pkg_TestSuite
//
--
-- ---------------------------------------------------------------------------
--
DROP PROCEDURE IF EXISTS utMySQL_pkg_SelfTest //
--
CREATE PROCEDURE utMySQL_pkg_SelfTest( ) COMMENT 'utMySQL 1.0.1 test frame work by Warren Willmey 2006'
  --
BEGIN
    --
    DECLARE sTestString1 VARCHAR( 255 ) DEFAULT 'NULL';
    DECLARE sTestString2 VARCHAR( 255 ) DEFAULT  NULL ;
    DECLARE dTestDate1   DATE           DEFAULT  NOW();
    DECLARE dTestDate2   DATE           DEFAULT  NULL ;
    --
    -- String tests.
    --
	CALL utAssert_eq(    'Test for equality (string)'                     , 'Badger'                  , 'Badger' );
	CALL utAssert_notEq( 'Test for non-equality (string, case different)' , 'CHEESE'                  , 'cheese' );
	CALL utAssert_notEq( 'Test for non-equality (string, different words)', 'Badger gets the CHEESE!' , 'Weasel gets the crumbs (Ha ha!)' );
	--
	-- Numerical checks.
	--
	CALL utAssert_eq(    'Test for equality (numerical)'          , 12345678,  12345678  );
	CALL utAssert_notEq( 'Test for non-equality (numerical)'      , 12345678,  87654321  );
	CALL utAssert_eq(    'Test for equality (numerical vs string)', 12345678, '12345678' );
	CALL utAssert_eq(    'Test for equality (zero)'               ,        0,         0  );
	CALL utAssert_eq(    'Test for equality (negative zero)'      ,        0,        -0  );
	CALL utAssert_notEq( 'Test for equality (floating zero)'      ,        0,       0.0  );
	CALL utAssert_notEq( 'Test for equality (-floating zero)'     ,        0,      -0.0  );
	--
	-- Null checks.
	--
	CALL utAssert_eq(    'Test for equality (null)'              , null,  null    );
	CALL utAssert_eq(    'Test for equality (null in caps)'      , NULL,  null    );
	CALL utAssert_notEq( 'Test for non-equality (null vs string)', NULL, 'NULL'   );
	CALL utAssert_notEq( 'Test for non-equality (null vs string)', NULL, 'Cheese' );
	CALL utAssert_notEq( 'Test for equality (null vs boolean)'   , NULL,  false   );
	CALL utAssert_notEq( 'Test for equality (null vs numerical)' , NULL,  0       );
	CALL utAssert_notEq( 'Test for equality (null vs numerical)' , NULL,  10      );
	CALL utAssert_notEq( 'Test for equality (null vs float)'     , NULL,  0.0     );
	--
	-- Boolean checks.
	--	
	CALL utAssert_eq(    'Test for equality (boolean)'               ,  true, true );
	CALL utAssert_Eq(    'Test for equality (boolean case sensitive)',  TRUE, true );
	CALL utAssert_notEq( 'Test for none-equality (bool, str)'        ,  true,'TRUE');
	CALL utAssert_eq(    'Test for equality (boolean captials)'      ,  TRUE, TRUE );	
	CALL utAssert_eq(    'Test for equality (boolean false)'         , false, false);	
	CALL utAssert_notEq( 'Test for none-equality (boolean)'          , false, true );	
	--
	-- Cross data type holes (gotchas!).
	--	
	CALL utAssert_notEq( 'Test for non-equality (null vs string variable)'    ,  NULL , sTestString1 );
	CALL utAssert_eq(    'Test the length of the "null" string'               ,     4 , CHARACTER_LENGTH( sTestString1 ) );
	CALL utAssert_notEq( 'Make sure CHARACTER_LENGTH() is not returning null' , 'NULL', CHARACTER_LENGTH( sTestString1 ) );
	CALL utAssert_notEq( 'Make sure CHARACTER_LENGTH() is not returning null' ,  NULL , CHARACTER_LENGTH( sTestString1 ) );
	CALL utAssert_eq(    'Test for equality (null vs a variable set to null)' ,  NULL , sTestString2 );
	CALL utAssert_eq(    'The CHARACTER_LENGTH() of a null string is null'    ,  NULL , CHARACTER_LENGTH( sTestString2 ) );
	--
	-- Date checks.
	--
	CALL utAssert_notEq( 'Test for non-equality (null date vs date)'          ,  dTestDate1, dTestDate2 );
	CALL utAssert_eq(    'Test for equality (null vs null date)'              ,  NULL      , dTestDate2 );
	CALL utAssert_notEq( 'Test for non-equality (date vs NOW()+1 )'           ,  dTestDate1, NOW() + 1  );
	--
	SET dTestDate2 = dTestDate1;
	--
	CALL utAssert_eq( 'Test for equality (date vs date)'                      ,  dTestDate1, dTestDate2 );
	--
END
//
--
-- ---------------------------------------------------------------------------
--
delimiter ;
