/**

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 results.csv.

**/

CREATE TABLE `raw_results` (
	`sequence` VARCHAR(15) NOT NULL DEFAULT '',
	`subject raw` VARCHAR(45) NOT NULL DEFAULT '',
	`subject` VARCHAR(25) NOT NULL DEFAULT '',
	`repo URL` VARCHAR(60) NOT NULL DEFAULT '', 
	`decay factor` INT NOT NULL DEFAULT 1,
	`input logging statements` INT NOT NULL DEFAULT 0,
	`candidate logging statements` INT NOT NULL DEFAULT 0, 	
	`passing logging statements` INT NOT NULL DEFAULT 0,	
	`failures` INT NOT NULL DEFAULT 0,	
	`transformed logging statements` INT NOT NULL DEFAULT 0,
	`log level not lowered in catch blocks` INT NOT NULL DEFAULT 0,	
	`log level not lowered in if statements` INT NOT NULL DEFAULT 0,	
	`log level not transformed due to if condition` INT NOT NULL DEFAULT 0,
	`log level not lowered due to keywords` INT NOT NULL DEFAULT 0,	
	`log level not raised without keywords`	INT NOT NULL DEFAULT 0,
	`log level adjusted by max transformation distance` INT NOT NULL DEFAULT 0,	
	`log level adjusted by inheritance` INT NOT NULL DEFAULT 0,	
	`use log category (SEVERE/WARNING/CONFIG)` VARCHAR(5) NOT NULL, 	
	`use log category (CONFIG)` VARCHAR(5) NOT NULL,	
	`not lower log levels of logs inside of catch blocks` VARCHAR(5) NOT NULL,
	`not lower log levels of logs inside of if statements` VARCHAR(5) NOT NULL,
	`not lower log levels in their messages with keywords` VARCHAR(5) NOT NULL,
	`not raise log levels in their message without keywords` VARCHAR(5) NOT NULL,	
	`consider if condition having log level` VARCHAR(5) NOT NULL,
	`consistent log level in inheritance` VARCHAR(5) NOT NULL,	
	`maximum transformation distance` INT NOT NULL DEFAULT 10,
	`time (s)` DECIMAL(10, 3) NULL,
	PRIMARY KEY (`sequence`, `subject raw`)
);


/**

Import sequence_number.csv & results.csv into database.

**/

---------results-----------

SELECT R.sequence, R.subject, R.`repo URL`, 
R.`input logging statements`, `candidate logging statements`,
`passing logging statements`, failures, `transformed logging statements`,
`log level not lowered in catch blocks`, `log level not lowered in if statements`,
`log level not transformed due to if condition`, `log level not lowered due to keywords`,
`use log category (SEVERE/WARNING/CONFIG)`, `use log category (CONFIG)`,
`not lower log levels of logs inside of catch blocks`, 
`not lower log levels of logs inside of if statements`,
`not lower log levels in their messages with keywords`,
`consider if condition having log level`,
`time (s)`
FROM raw_results AS R
INNER JOIN sequence_number
ON R.sequence = sequence_number.sequence;

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

Table schema for repo.csv.

**/

CREATE TABLE IF NOT EXISTS `raw_repos` (
  `sequence` varchar(15) NOT NULL DEFAULT '',
  `repo URL` varchar(60) DEFAULT NULL,
  `SHA-1 of head` varchar(40) DEFAULT NULL,
  `N for commits` int(4) DEFAULT NULL,
  `number of commits processed` int(4) DEFAULT NULL,
  `actual number of commits` int(4) DEFAULT NULL,
  `average Java lines added` double(18,3) DEFAULT NULL,
  `average Java lines removed` double(17,3) DEFAULT NULL,
  PRIMARY KEY (`sequence`),
  KEY `repo URL` (`repo URL`)
) DEFAULT CHARSET=utf8;

----------------------------------------------
SELECT R.sequence, `repo URL`, `SHA-1 of head`, `N for commits`,
`number of commits processed`, `actual number of commits`,
`average Java lines added`, `average Java lines removed`
FROM raw_repos as R
INNER JOIN sequence_number 
ON R.sequence = sequence_number.sequence;

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

/**

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;

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

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

