<commit_before>--#SET TERMINATOR @

/*
Copyright (c) 2012 - 2014, <NAME> (AngocA)
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

 1. Redistributions of source code must retain the above copyright notice, this
    list of conditions and the following disclaimer.
 2. Redistributions in binary form must reproduce the above copyright notice,
    this list of conditions and the following disclaimer in the documentation
    and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

/**
 * Tests for the implementation of the appenders.
 */

SET CURRENT SCHEMA LOGGER_1B @

BEGIN
-- Reserved names for errors.
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE AUTONOMOUS_VALUE ANCHOR LOGDATA.CONFIGURATION.VALUE;
DECLARE EXPECTED_MSG ANCHOR LOGDATA.LOGS.MESSAGE;
DECLARE ACTUAL_MSG ANCHOR LOGDATA.LOGS.MESSAGE;

-- For any other SQL State.
DECLARE CONTINUE HANDLER FOR SQLWARNING
  INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (4, 'Warning SQLCode ' || SQLCODE || '-SQLState ' || SQLSTATE);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (4, 'Exception SQLCode ' || SQLCODE || '-SQLState ' || SQLSTATE);
DECLARE CONTINUE HANDLER FOR NOT FOUND
  INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (5, 'Not found SQLCode ' || SQLCODE || '-SQLState ' || SQLSTATE);

-- Prepares the environment.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (4, 'TestsAppenders: Preparing environment');
UPDATE LOGDATA.CONF_APPENDERS
  SET PATTERN = '%m'
  WHERE NAME = 'Tables';
CALL LOGGER.ACTIVATE_CACHE();
CALL LOGGER.REFRESH_CACHE();
COMMIT;

-- Test01: Writes in LOGS with autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test01: Writes in LOGS with autonomous procedure');
SET AUTONOMOUS_VALUE = 'true';
SET EXPECTED_MSG = 'Test1-autonomous';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, EXPECTED_MSG);
COMMIT;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Test02: Writes in LOGS with non-autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test02: Writes in LOGS with non-autonomous procedure');
SET AUTONOMOUS_VALUE = 'false';
SET EXPECTED_MSG = 'Test2-non-autonomous';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, EXPECTED_MSG);
COMMIT;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Test03: Rollbacks in LOGS with autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test03: Rollbacks in LOGS with autonomous procedure');
SET AUTONOMOUS_VALUE = 'true';
SET EXPECTED_MSG = 'Test3-autonomous-rollback';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, 'Before');
COMMIT;
CALL LOGGER.FATAL(0, EXPECTED_MSG);
ROLLBACK;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = 'Before'
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Test04: Rollbacks in LOGS with non-autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test04: Rollbacks in LOGS with non-autonomous procedure');
SET AUTONOMOUS_VALUE = 'false';
SET EXPECTED_MSG = 'Before';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, EXPECTED_MSG);
COMMIT;
CALL LOGGER.FATAL(0, 'Test4-non-autonomous-rollback');
ROLLBACK;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Cleans the environment.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'TestsAppenders: Cleaning environment');
DELETE FROM LOGDATA.CONFIGURATION;
INSERT INTO LOGDATA.CONFIGURATION (KEY, VALUE)
  VALUES ('autonomousLogging', 'false'),
         ('defaultRootLevelId', '3'),
         ('internalCache', 'true'),
         ('logInternals', 'false'),
         ('secondsToRefresh', '30');
CALL LOGGER.ACTIVATE_CACHE();
CALL LOGGER.REFRESH_CACHE();
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'TestsAppenders: Finished succesfully');
COMMIT;

END @
<commit_msg>Delete message when cleaning<commit_after>--#SET TERMINATOR @

/*
Copyright (c) 2012 - 2014, <NAME> (AngocA)
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

 1. Redistributions of source code must retain the above copyright notice, this
    list of conditions and the following disclaimer.
 2. Redistributions in binary form must reproduce the above copyright notice,
    this list of conditions and the following disclaimer in the documentation
    and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

/**
 * Tests for the implementation of the appenders.
 */

SET CURRENT SCHEMA LOGGER_1B @

BEGIN
-- Reserved names for errors.
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE AUTONOMOUS_VALUE ANCHOR LOGDATA.CONFIGURATION.VALUE;
DECLARE EXPECTED_MSG ANCHOR LOGDATA.LOGS.MESSAGE;
DECLARE ACTUAL_MSG ANCHOR LOGDATA.LOGS.MESSAGE;

-- For any other SQL State.
DECLARE CONTINUE HANDLER FOR SQLWARNING
  INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (4, 'Warning SQLCode ' || SQLCODE || '-SQLState ' || SQLSTATE);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (4, 'Exception SQLCode ' || SQLCODE || '-SQLState ' || SQLSTATE);
DECLARE CONTINUE HANDLER FOR NOT FOUND
  INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (5, 'Not found SQLCode ' || SQLCODE || '-SQLState ' || SQLSTATE);

-- Prepares the environment.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (4, 'TestsAppenders: Preparing environment');
UPDATE LOGDATA.CONF_APPENDERS
  SET PATTERN = '%m'
  WHERE NAME = 'Tables';
CALL LOGGER.ACTIVATE_CACHE();
CALL LOGGER.REFRESH_CACHE();
COMMIT;

-- Test01: Writes in LOGS with autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test01: Writes in LOGS with autonomous procedure');
SET AUTONOMOUS_VALUE = 'true';
SET EXPECTED_MSG = 'Test1-autonomous';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, EXPECTED_MSG);
COMMIT;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Test02: Writes in LOGS with non-autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test02: Writes in LOGS with non-autonomous procedure');
SET AUTONOMOUS_VALUE = 'false';
SET EXPECTED_MSG = 'Test2-non-autonomous';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, EXPECTED_MSG);
COMMIT;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Test03: Rollbacks in LOGS with autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test03: Rollbacks in LOGS with autonomous procedure');
SET AUTONOMOUS_VALUE = 'true';
SET EXPECTED_MSG = 'Test3-autonomous-rollback';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, 'Before');
COMMIT;
CALL LOGGER.FATAL(0, EXPECTED_MSG);
ROLLBACK;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = 'Before'
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Test04: Rollbacks in LOGS with non-autonomous procedure.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'Test04: Rollbacks in LOGS with non-autonomous procedure');
SET AUTONOMOUS_VALUE = 'false';
SET EXPECTED_MSG = 'Before';
CALL LOGGER.ACTIVATE_CACHE();
UPDATE LOGDATA.CONFIGURATION
  SET VALUE = AUTONOMOUS_VALUE
  WHERE KEY = 'autonomousLogging';
CALL LOGGER.REFRESH_CACHE();
CALL LOGGER.FATAL(0, EXPECTED_MSG);
COMMIT;
CALL LOGGER.FATAL(0, 'Test4-non-autonomous-rollback');
ROLLBACK;
SELECT MESSAGE INTO ACTUAL_MSG
  FROM LOGS
  WHERE DATE = (SELECT MAX(DATE)
    FROM LOGS);
IF (EXPECTED_MSG <> ACTUAL_MSG) THEN
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Different msg');
 INSERT INTO LOGDATA.LOGS (DATE, LEVEL_ID, MESSAGE) VALUES 
   (GENERATE_UNIQUE(), 2, 'Expected "' || COALESCE(EXPECTED_MSG, '-') || '" actual "' || COALESCE(ACTUAL_MSG, '-') || '"');
END IF;
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = EXPECTED_MSG
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
COMMIT;

-- Cleans the environment.
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'TestsAppenders: Cleaning environment');
DELETE FROM LOGDATA.CONFIGURATION;
INSERT INTO LOGDATA.CONFIGURATION (KEY, VALUE)
  VALUES ('autonomousLogging', 'false'),
         ('defaultRootLevelId', '3'),
         ('internalCache', 'true'),
         ('logInternals', 'false'),
         ('secondsToRefresh', '30');
DELETE FROM LOGDATA.LOGS
  WHERE MESSAGE = 'A manual CONF_LOGGERS_EFFECTIVE update should be realized.'
  AND DATE = (SELECT MAX(DATE) FROM LOGDATA.LOGS);
CALL LOGGER.ACTIVATE_CACHE();
CALL LOGGER.REFRESH_CACHE();
INSERT INTO LOGDATA.LOGS (LEVEL_ID, MESSAGE) VALUES (3, 'TestsAppenders: Finished succesfully');
COMMIT;

END @

{'fraction_non_alphanumeric': 0.10699588477366255, 'fraction_numerical': 0.008916323731138546, 'mean_word_length': 5.017746595130004, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4733038', 'n_tokens_mistral': 5182, 'n_tokens_neox': 4781, 'n_words': 1577}