/**

Table schema for sequence_number.csv

**/

CREATE TABLE IF NOT EXISTS `sequence_number` (
  `sequence` varchar(15) NOT NULL DEFAULT '',
  `subject` varchar(25) NOT NULL,
  PRIMARY KEY (`sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


/**

Table schema for log_level_actions.csv.

**/

CREATE TABLE IF NOT EXISTS `raw_log_actions` (
  `sequence` varchar(15) NOT NULL,
  `subject raw` varchar(45) DEFAULT NULL,
  `log expression` varchar(700) NOT NULL DEFAULT '',
  `start pos` INT NOT NULL DEFAULT 0,
  `log level` varchar(10) NOT NULL,
  `type FQN` varchar(200) NOT NULL DEFAULT '',
  `enclosing method` varchar(500) NOT NULL DEFAULT '',
  `DOI value` DECIMAL(15, 8) NOT NULL DEFAULT 0,
  `action` varchar(20) NOT NULL DEFAULT '',
  `new level` varchar(10) NOT NULL,
  `logging framework` varchar(20) NOT NULL
) DEFAULT CHARSET=utf8;

-----------------------------------
CREATE TABLE `needed_log_actions`(
SELECT A.sequence, A.`subject raw`, `log expression`,
 `start pos`, `log level`, `type FQN`,
 `enclosing method`, `DOI value`,
 `action`, `new level`
FROM raw_log_actions AS A
INNER JOIN sequence_number
ON A.sequence = sequence_number.sequence
WHERE `logging framework` = 'JAVA_UTIL_LOGGING');

-------------------------------

/**

For JUL.

**/

CREATE TABLE IF NOT EXISTS `input_log_invocations` (
  `subject raw` varchar(45) DEFAULT NULL,
  `log expression` varchar(700) NOT NULL DEFAULT '',
  `start pos` INT NOT NULL DEFAULT 0,
  `log level` varchar(10) NOT NULL,
  `type FQN` varchar(200) NOT NULL DEFAULT '',
  `enclosing method` varchar(500) NOT NULL DEFAULT '',
  `logging framework` varchar(20) NOT NULL,
  `DOI value` DECIMAL(15, 8) NOT NULL DEFAULT 0
) DEFAULT CHARSET=utf8;

------------------------------
/**

Get all old log levels and their new log levels.

**/
SELECT I.`subject raw`, I.`log expression`, I.`log level`, N.`new level` 
FROM input_log_invocations as I
LEFT JOIN needed_log_actions as N
ON I.`subject raw` = N.`subject raw` and 
I.`log expression` = N.`log expression` and
I.`start pos` = N.`start pos`
and I.`type FQN` = N.`type FQN`;

-------------------------------

CREATE TABLE IF NOT EXISTS `distribution` (
  `subject` varchar(25) NOT NULL,
  `subject raw` varchar(45) DEFAULT NULL,
  `log expression` varchar(700) NOT NULL DEFAULT '',
  `log level` varchar(10) NOT NULL,
  `new level` varchar(10) NOT NULL,
  `new level(adjusted)` varchar(10) NOT NULL,
  `old ordering` INT(1) NULL,
  `new ordering` INT(1) NULL
) DEFAULT CHARSET=utf8;
------------------------

SELECT subject, STD(`old ordering`), STD(`new ordering`) 
FROM distribution.distribution
GROUP BY subject


-------------------------
SELECT STD(`old ordering`), STD(`new ordering`) 
FROM distribution.distribution
