Delimiter //
/**	
 * Test of "Checks if fields ares unique"
 * 
 * @tested LOADER_CHECK_UNIQUE()
 */
Drop Procedure if exists TEST_LOADER_CHECK_UNIQUE//
Create Procedure TEST_LOADER_CHECK_UNIQUE()
Begin

    -- Initial dataset
    Call LOADER_TEST_MOCK_STATUS_CLEAN();

	Drop Temporary Table If Exists TABLE_TEST_LOADER_CHECK_UNIQUE ;
	Create Temporary Table TABLE_TEST_LOADER_CHECK_UNIQUE (
		LOADER_STATUS_ID_LOADER_STATUS int not null primary key, 
		CODE1 varchar(10) null, 
		CODE2 varchar(10) null, 
		LIBELLE varchar(50)
	);

    Call LOADER_TEST_MOCK_STATUS_PUSH(1, Null);
    Call LOADER_TEST_MOCK_STATUS_PUSH(2, Null);
    Call LOADER_TEST_MOCK_STATUS_PUSH(3, Null);
    Call LOADER_TEST_MOCK_STATUS_PUSH(4, Null);
    Call LOADER_TEST_MOCK_STATUS_PUSH(5, Null);
    Call LOADER_TEST_MOCK_STATUS_PUSH(6, Null);

	Insert Into TABLE_TEST_LOADER_CHECK_UNIQUE(LOADER_STATUS_ID_LOADER_STATUS, CODE1, CODE2, LIBELLE) 
	Values 
		(1, 'A', 'B', ''),
		(2, 'A', 'C', ''),
		(3, 'D', 'B', ''),
		(4, 'E', 'F', ''),
		(5, 'E', 'F', ''),
		(6, 'G', 'H', '')
	;

	-- Single column test
    CALL LOADER_CHECK_UNIQUE (
			'TABLE_TEST_LOADER_CHECK_UNIQUE'
			, 'CODE1'
			, null
			, 'BATCH_NUM'
			, 1
			, 'ERR_CNT'
            , 'ERR_MSG'
            , null
			, null
		);

    -- Assertions
    CALL utAssert_eq('Table with single colonne unique contrainte violation are flagged'
        , 1
        , (SELECT LOADER_STATUS_ERRCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 1)
    );

    CALL utAssert_eq('Table without single unique contrainte violation are not flagged'
        , 0
        , (SELECT Coalesce(LOADER_STATUS_ERRCOUNT, 0) FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 3)
    );

	-- Twin column test
    CALL LOADER_CHECK_UNIQUE (
			'TABLE_TEST_LOADER_CHECK_UNIQUE'
			, 'CODE1'
			, 'CODE2'
			, 'BATCH_NUM'
			, 1
			, 'ERR_CNT'
            , 'ERR_MSG'
            , null
			, null
		);

	CALL utAssert_eq('Table with twin column unique contrainte violation are flagged '
        , 2
        , (SELECT LOADER_STATUS_ERRCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 4)
    );

	CALL utAssert_eq('Table without twin column unique contrainte violation are not flagged '
        , 1
        , (SELECT LOADER_STATUS_ERRCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 1)
    );

	CALL utAssert_eq('Table without any column unique contrainte violation are not flagged '
        , 0
        , (SELECT LOADER_STATUS_ERRCOUNT FROM LOADER_STATUS WHERE ID_LOADER_STATUS = 6)
    );

    -- clean
    Drop Temporary Table TABLE_TEST_LOADER_CHECK_UNIQUE ;
    Call LOADER_TEST_MOCK_STATUS_CLEAN();

End //
Delimiter ;