student-code-quality/src/SQL/rq3Figure2.sql at master · hiekekeuning/student-code-quality · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
-- Checkstyle
select total(ai)*1000/10833 from
(
	select sf_id, total(tc)/total(ml) ai
	from(
		select sf_id, total(count) tc, loc ml
		from (snapshot s
		inner join event e on e.id=s.event_id
		inner join loc l on l.filename=s.name)
		left join issue3 i on (i.source_file_id=s.sf_id AND i.event_id=s.event_id and i.name <> "LawOfDemeter")
		where session_id in
		  (select session_id from startup_event se inner join extension ex
		   on ex.event_id=se.id where name LIKE "Checkstyle")
		group by s.sf_id, s.event_id)
	group by sf_id
);

-- PMD
select total(ai)*1000/4299 from
(
	select sf_id, total(tc)/total(ml) ai
	from(
		select sf_id, total(count) tc, loc ml
		from (snapshot s
		inner join event e on e.id=s.event_id
		inner join loc l on l.filename=s.name)
		left join issue3 i on (i.source_file_id=s.sf_id AND i.event_id=s.event_id and i.name <> "LawOfDemeter")
		where session_id in
		  (select session_id from startup_event se inner join extension ex
		   on ex.event_id=se.id where name LIKE "PMD%")
		group by s.sf_id, s.event_id)
	group by sf_id
);

-- PatternCoder
select total(ai)*1000/609 from
(
	select sf_id, total(tc)/total(ml) ai
	from(
		select sf_id, total(count) tc, loc ml
		from (snapshot s
		inner join event e on e.id=s.event_id
		inner join loc l on l.filename=s.name)
		left join issue3 i on (i.source_file_id=s.sf_id AND i.event_id=s.event_id and i.name <> "LawOfDemeter")
		where session_id in
		  (select session_id from startup_event se inner join extension ex
		   on ex.event_id=se.id where name LIKE "PatternCoder")
		group by s.sf_id, s.event_id)
	group by sf_id
);

-- Some tool
select total(ai)*1000/12442 from
(
	select sf_id, total(tc)/total(ml) ai
	from(
		select sf_id, total(count) tc, loc ml
		from ((snapshot s
		inner join event e on e.id=s.event_id)
		inner join loc l on l.filename=s.name)
		left join issue3 i on (i.source_file_id=s.sf_id AND i.event_id=s.event_id AND i.name <> "LawOfDemeter")
		where session_id in
		  (select session_id from startup_event se inner join extension ex
		  on ex.event_id=se.id)
		group by s.sf_id, s.event_id)
	group by sf_id
);

-- No tool
select total(ai)*1000/436324 from
(
	select sf_id, total(tc)/total(ml) ai
	from(
		select sf_id, total(count) tc, loc ml
		from (snapshot s
		inner join event e on e.id=s.event_id
		inner join loc l on l.filename=s.name)
		left join issue3 i on (i.source_file_id=s.sf_id AND i.event_id=s.event_id AND i.name <> "LawOfDemeter")
		where session_id not in
		  (select session_id from startup_event se inner join extension ex
		  on ex.event_id=se.id)
		and session_id IN (SELECT session_id FROM startup_event)
		group by s.sf_id, s.event_id)
	group by sf_id
);