/**

Table schema for results.csv.

**/

CREATE TABLE `tool_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`)
);


/**

Table schema for repo.csv.

**/

CREATE TABLE IF NOT EXISTS `tool_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;


/**

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 `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,
  `distance` INT NOT NULL DEFAULT 0,
  `direction` varchar(10) NOT NULL DEFAULT ''
) DEFAULT CHARSET=utf8;


/**

Table schema for input_log_invocations.csv

**/

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;


CREATE TABLE IF NOT EXISTS `git_commits` (
  `subject raw` varchar(45) DEFAULT NULL,
  `sha1` varchar(60) NOT NULL DEFAULT '',
  `Java lines added` INT NOT NULL DEFAULT 0,
  `Java lines removed` INT NOT NULL DEFAULT 0,
  `methods found` INT NOT NULL DEFAULT 0,
  `interaction events` INT NOT NULL DEFAULT 0,
  `run time (s)` DECIMAL(6, 3) NOT NULL DEFAULT 0
) DEFAULT CHARSET=utf8;


/**

Import data from CSV files:
https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html

**/


/**

Table schema for the log actions which are needed in the paper. We only need on combination of settings for every subject.

**/
CREATE TABLE `needed_log_actions`
(SELECT subject, sequence_number.sequence, `start pos`, `type FQN`, `logging framework`, `DOI value`, `distance`, `direction`
FROM sequence_number 
inner join  log_actions 
on sequence_number.sequence = log_actions.sequence);


/**

Table schema to store number of logging frameworks for every subject.

**/

CREATE TABLE  `framework_number`
AS(
	SELECT `subject`, count(distinct `logging framework`) as `framework`
	FROM tool_results
	inner join 
		(SELECT DISTINCT `subject raw`, `logging framework`
		FROM input_log_invocations) AS A
	on tool_results.`subject raw` = A.`subject raw`
	group by `subject`);

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

/**

Pivot table.

**/
SELECT C.`subject`, `average distance`, `STD distance`,`average DOI value`, `STD DOI value`, `lowered`, `raised`, `framework`, `number of commits processed` 
FROM
	(SELECT B.`subject`, `lowered`, `raised`, `average distance`, `STD distance`, `average DOI value`, `STD DOI value`, `number of commits processed` 
	FROM 

		(SELECT `subject`, 
				COUNT(IF(`direction` = 'LOWERED', `direction`, null)) AS `lowered`,
				COUNT(IF(`direction` = 'RAISED', `direction`, null)) AS `raised`,
				AVG(`distance`) as `average distance`,
                STD(`distance`) as `STD distance`,
                AVG(`DOI value`) as `average DOI value`,
                STD(`DOI value`) as `STD DOI value`
		FROM needed_log_actions
		GROUP BY `subject` ) as A 

	RIGHT JOIN

		(SELECT DISTINCT subject, `number of commits processed` 
		FROM tool_repos
		inner join sequence_number
		on tool_repos.sequence = sequence_number.sequence) as B

	on A.subject = B.subject) AS C
    
LEFT JOIN framework_number
on C.subject = framework_number.subject;

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


/**

Data for git.

**/
SELECT `subject raw`, sum(`Java lines added`) as `total lines added`, 
		sum(`Java lines removed`) as `total lines removed`, 
        sum(`interaction events`) as `total interaction event`
FROM git_commits
GROUP BY `subject raw`;
