Various linting improvements.

6 files changed, 155 insertions, 215 deletions

diff --git a/create_schema.sql b/create_schema.sql
index bec3756..0c34c50 100644
--- a/create_schema.sql
+++ b/create_schema.sql
@@ -242,10 +242,13 @@ CREATE TYPE linter_type AS ENUM (
 );
 
 CREATE TABLE linter_version (
+	ID				smallint,
 	VERSION_STRING	text,
 	GIT_COMMIT		bytea,
 	DEPLOYED_AT		timestamp,
-	LINTER			linter_type
+	LINTER			linter_type,
+	CONSTRAINT lv_pk
+		PRIMARY KEY (ID)
 );
 
 CREATE UNIQUE INDEX lv_li_da
@@ -266,35 +269,47 @@ CREATE TABLE lint_issue (
 );
 
 CREATE TABLE lint_cert_issue (
-	ID					bigserial,
-	CERTIFICATE_ID		integer,
+	CERTIFICATE_ID		bigint,
 	LINT_ISSUE_ID		integer,
 	ISSUER_CA_ID		integer,
-	NOT_BEFORE			timestamp,
+	NOT_BEFORE_DATE		date,
 	CONSTRAINT lci_pk
-		PRIMARY KEY (ID),
+		PRIMARY KEY (ISSUER_CA_ID, LINT_ISSUE_ID, NOT_BEFORE_DATE, CERTIFICATE_ID),
+	CONSTRAINT lci_ca_fk
+		FOREIGN KEY (ISSUER_CA_ID)
+		REFERENCES ca(ID),
+	CONSTRAINT lci_li_fk
+		FOREIGN KEY (LINT_ISSUE_ID)
+		REFERENCES lint_issue(ID),
 	CONSTRAINT lci_c_fk
 		FOREIGN KEY (CERTIFICATE_ID)
-		REFERENCES certificate(ID),
-	CONSTRAINT lci_ci_fk
+		REFERENCES certificate(ID)
+);
+
+CREATE INDEX lci_c
+	ON lint_cert_issue (CERTIFICATE_ID);
+
+CREATE TABLE lint_summary (
+	LINT_ISSUE_ID	integer,
+	ISSUER_CA_ID	integer,
+	NOT_BEFORE_DATE	date,
+	NO_OF_CERTS		integer,
+	CONSTRAINT ls_pk
+		PRIMARY KEY (LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE),
+	CONSTRAINT ls_li_fk
 		FOREIGN KEY (LINT_ISSUE_ID)
 		REFERENCES lint_issue(ID),
-	CONSTRAINT lci_ca_fk
+	CONSTRAINT ls_ca_fk
 		FOREIGN KEY (ISSUER_CA_ID)
 		REFERENCES ca(ID)
 );
 
-CREATE INDEX lci_c_ci
-	ON lint_cert_issue (CERTIFICATE_ID, LINT_ISSUE_ID);
-
-CREATE INDEX lci_ca_ci_nb_c
-	ON lint_cert_issue (ISSUER_CA_ID, LINT_ISSUE_ID, NOT_BEFORE, CERTIFICATE_ID);
+\i lint_summarizer.fnc
 
-CREATE INDEX lci_ci_nb
-	ON lint_cert_issue (LINT_ISSUE_ID, NOT_BEFORE);
-
-CREATE INDEX lci_nb_ca_ci
-	ON lint_cert_issue (NOT_BEFORE, ISSUER_CA_ID, LINT_ISSUE_ID);
+CREATE TRIGGER lint_summarizer
+	BEFORE INSERT OR DELETE on lint_cert_issue
+	FOR EACH ROW
+	EXECUTE PROCEDURE lint_summarizer();
 
 
 CREATE TABLE trust_context (
@@ -450,6 +465,7 @@ INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 1
 INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 195, 'EV Server Authentication', '2.16.756.1.17.3.22.51', 1 );
 INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 196, 'EV Server Authentication', '1.3.171.1.1.1.10.8', 1 );
 INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 197, 'EV Server Authentication', '1.2.616.1.113527.2.5.1.7', 1 );
+INSERT INTO trust_purpose ( ID, PURPOSE, PURPOSE_OID, DISPLAY_ORDER ) VALUES ( 198, 'EV Server Authentication', '1.3.6.1.4.1.4146.1.2', 1 );
 
 
 CREATE TABLE applicable_purpose(
@@ -701,6 +717,8 @@ GRANT SELECT ON lint_issue TO crtsh;
 
 GRANT SELECT ON lint_cert_issue TO crtsh;
 
+GRANT SELECT ON lint_summary TO crtsh;
+
 GRANT SELECT ON trust_context TO crtsh;
 
 GRANT SELECT ON trust_purpose TO crtsh;
diff --git a/drop_schema.sql b/drop_schema.sql
index bbe89f4..a9838bc 100644
--- a/drop_schema.sql
+++ b/drop_schema.sql
@@ -45,6 +45,12 @@ DROP FUNCTION lint_cached(
 );
 
 
+DROP TRIGGER lint_summarizer;
+
+DROP FUNCTION lint_summarizer(
+);
+
+
 DROP TABLE cached_response;
 
 DROP TABLE mozilla_root_hashes;
@@ -79,6 +85,8 @@ DROP TABLE trust_purpose;
 
 DROP TABLE trust_context;
 
+DROP TABLE lint_summary;
+
 DROP TABLE lint_cert_issue;
 
 DROP TABLE lint_issue;
diff --git a/lint_cached.fnc b/lint_cached.fnc
index 939f904..00f8965 100644
--- a/lint_cached.fnc
+++ b/lint_cached.fnc
@@ -88,29 +88,41 @@ BEGIN
 		END IF;
 
 		FOR l_record IN EXECUTE t_query USING t_certificate, t_certType LOOP
-			SELECT li.ID
-				INTO t_lintIssueID
-				FROM lint_issue li
-				WHERE li.LINTER = v_linter
-					AND li.SEVERITY = substr(l_record.LINT, 1, 1)
-					AND li.ISSUE_TEXT = substr(l_record.LINT, 4);
-			IF NOT FOUND THEN
-				INSERT INTO lint_issue (
-						LINTER, SEVERITY, ISSUE_TEXT
+			IF substr(l_record.LINT, 1, 1) IN ('W', 'E', 'F') THEN
+				SELECT li.ID
+					INTO t_lintIssueID
+					FROM lint_issue li
+					WHERE li.LINTER = v_linter
+						AND li.SEVERITY = substr(l_record.LINT, 1, 1)
+						AND li.ISSUE_TEXT = substr(l_record.LINT, 4);
+				IF NOT FOUND THEN
+					BEGIN
+						INSERT INTO lint_issue (
+								LINTER, SEVERITY, ISSUE_TEXT
+							)
+							VALUES (
+								v_linter, substr(l_record.LINT, 1, 1), substr(l_record.LINT, 4)
+							)
+							RETURNING ID
+								INTO t_lintIssueID;
+					EXCEPTION
+						WHEN unique_violation THEN
+							SELECT li.ID
+								INTO t_lintIssueID
+								FROM lint_issue li
+								WHERE li.LINTER = v_linter
+									AND li.SEVERITY = substr(l_record.LINT, 1, 1)
+									AND li.ISSUE_TEXT = substr(l_record.LINT, 4);
+					END;
+				END IF;
+				INSERT INTO lint_cert_issue (
+						CERTIFICATE_ID, lint_issue_ID, ISSUER_CA_ID, NOT_BEFORE_DATE
 					)
 					VALUES (
-						v_linter, substr(l_record.LINT, 1, 1), substr(l_record.LINT, 4)
-					)
-					RETURNING ID
-						INTO t_lintIssueID;
+						cert_id, t_lintIssueID, t_issuerCAID, x509_notBefore(t_certificate)::date
+					);
+				RETURN NEXT t_lintIssueID;
 			END IF;
-			INSERT INTO lint_cert_issue (
-					CERTIFICATE_ID, lint_issue_ID, ISSUER_CA_ID, NOT_BEFORE
-				)
-				VALUES (
-					cert_id, t_lintIssueID, t_issuerCAID, x509_notBefore(t_certificate)
-				);
-			RETURN NEXT t_lintIssueID;
 		END LOOP;
 
 		IF v_linter = 'cablint' THEN
diff --git a/lint_summarizer.fnc b/lint_summarizer.fnc
new file mode 100644
index 0000000..240eff3
--- /dev/null
+++ b/lint_summarizer.fnc
@@ -0,0 +1,35 @@
+CREATE OR REPLACE FUNCTION lint_summarizer(
+) RETURNS TRIGGER
+AS $$
+DECLARE
+	t_noOfCerts		lint_summary.NO_OF_CERTS%TYPE;
+BEGIN
+	IF TG_OP = 'INSERT' THEN
+		INSERT INTO lint_summary (
+				LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE, NO_OF_CERTS
+			)
+			VALUES (
+				new.LINT_ISSUE_ID, new.ISSUER_CA_ID, new.NOT_BEFORE_DATE, 1
+			)
+			ON CONFLICT (LINT_ISSUE_ID, ISSUER_CA_ID, NOT_BEFORE_DATE) DO UPDATE
+				SET NO_OF_CERTS = lint_summary.NO_OF_CERTS + 1;
+		RETURN NEW;
+	ELSIF TG_OP = 'DELETE' THEN
+		UPDATE lint_summary
+			SET NO_OF_CERTS = NO_OF_CERTS - 1
+			WHERE LINT_ISSUE_ID = old.LINT_ISSUE_ID
+				AND ISSUER_CA_ID = old.ISSUER_CA_ID
+				AND NOT_BEFORE_DATE = old.NOT_BEFORE_DATE
+			RETURNING NO_OF_CERTS
+				INTO t_noOfCerts;
+		IF FOUND AND (t_noOfCerts = 0) THEN
+			DELETE FROM lint_summary
+				WHERE LINT_ISSUE_ID = old.LINT_ISSUE_ID
+					AND ISSUER_CA_ID = old.ISSUER_CA_ID
+					AND NOT_BEFORE_DATE = old.NOT_BEFORE_DATE
+					AND NO_OF_CERTS = 0;
+		END IF;
+		RETURN OLD;
+	END IF;
+END;
+$$ LANGUAGE plpgsql STRICT;
diff --git a/lint_update_1week_stats.sql b/lint_update_1week_stats.sql
index bb8294f..8543ef2 100644
--- a/lint_update_1week_stats.sql
+++ b/lint_update_1week_stats.sql
@@ -9,9 +9,7 @@ SELECT linter,
 		0::bigint ERROR_CERTS,
 		0::bigint ERROR_ISSUES,
 		0::bigint WARNING_CERTS,
-		0::bigint WARNING_ISSUES,
-		0::bigint NOTICE_CERTS,
-		0::bigint NOTICE_ISSUES
+		0::bigint WARNING_ISSUES
 	FROM certificate c, ca, unnest(array_append(enum_range(NULL::linter_type), NULL)) linter
 	WHERE x509_notBefore(c.CERTIFICATE) >= date_trunc('day', statement_timestamp() - interval '1 week')
 		AND c.ISSUER_CA_ID = ca.ID
@@ -27,9 +25,9 @@ UPDATE lint_1week_summary_temp l1st
 				lci.ISSUER_CA_ID,
 				li.LINTER
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
-				AND li.SEVERITY NOT IN ('I', 'B')
+				AND li.SEVERITY IN ('W', 'E', 'F')
 			GROUP BY lci.ISSUER_CA_ID, li.LINTER
 		) sub
 	WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
@@ -42,9 +40,9 @@ UPDATE lint_1week_summary_temp l1st
 				count(*) ALL_ISSUES,
 				lci.ISSUER_CA_ID
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
-				AND li.SEVERITY NOT IN ('I', 'B')
+				AND li.SEVERITY IN ('W', 'E', 'F')
 			GROUP BY lci.ISSUER_CA_ID
 		) sub
 	WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
@@ -59,7 +57,7 @@ UPDATE lint_1week_summary_temp l1st
 				lci.ISSUER_CA_ID,
 				li.LINTER
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
 				AND li.SEVERITY = 'F'
 			GROUP BY lci.ISSUER_CA_ID, li.LINTER
@@ -74,7 +72,7 @@ UPDATE lint_1week_summary_temp l1st
 				count(*) FATAL_ISSUES,
 				lci.ISSUER_CA_ID
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
 				AND li.SEVERITY = 'F'
 			GROUP BY lci.ISSUER_CA_ID
@@ -91,7 +89,7 @@ UPDATE lint_1week_summary_temp l1st
 				lci.ISSUER_CA_ID,
 				li.LINTER
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
 				AND li.SEVERITY = 'E'
 			GROUP BY lci.ISSUER_CA_ID, li.LINTER
@@ -106,7 +104,7 @@ UPDATE lint_1week_summary_temp l1st
 				count(*) ERROR_ISSUES,
 				lci.ISSUER_CA_ID
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
 				AND li.SEVERITY = 'E'
 			GROUP BY lci.ISSUER_CA_ID
@@ -123,7 +121,7 @@ UPDATE lint_1week_summary_temp l1st
 				lci.ISSUER_CA_ID,
 				li.LINTER
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
 				AND li.SEVERITY = 'W'
 			GROUP BY lci.ISSUER_CA_ID, li.LINTER
@@ -138,7 +136,7 @@ UPDATE lint_1week_summary_temp l1st
 				count(*) WARNING_ISSUES,
 				lci.ISSUER_CA_ID
 			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
+			WHERE lci.NOT_BEFORE_DATE >= date_trunc('day', statement_timestamp() - interval '1 week')
 				AND lci.LINT_ISSUE_ID = li.ID
 				AND li.SEVERITY = 'W'
 			GROUP BY lci.ISSUER_CA_ID
@@ -146,38 +144,6 @@ UPDATE lint_1week_summary_temp l1st
 	WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
 		AND l1st.LINTER IS NULL;
 
-UPDATE lint_1week_summary_temp l1st
-	SET NOTICE_CERTS = sub.NOTICE_CERTS,
-		NOTICE_ISSUES = sub.NOTICE_ISSUES
-	FROM (
-		SELECT count(DISTINCT lci.CERTIFICATE_ID) NOTICE_CERTS,
-				count(*) NOTICE_ISSUES,
-				lci.ISSUER_CA_ID,
-				li.LINTER
-			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
-				AND lci.LINT_ISSUE_ID = li.ID
-				AND li.SEVERITY = 'N'
-			GROUP BY lci.ISSUER_CA_ID, li.LINTER
-		) sub
-	WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
-		AND l1st.LINTER = sub.LINTER;
-UPDATE lint_1week_summary_temp l1st
-	SET NOTICE_CERTS = sub.NOTICE_CERTS,
-		NOTICE_ISSUES = sub.NOTICE_ISSUES
-	FROM (
-		SELECT count(DISTINCT lci.CERTIFICATE_ID) NOTICE_CERTS,
-				count(*) NOTICE_ISSUES,
-				lci.ISSUER_CA_ID
-			FROM lint_cert_issue lci, lint_issue li
-			WHERE lci.NOT_BEFORE >= date_trunc('day', statement_timestamp() - interval '1 week')
-				AND lci.LINT_ISSUE_ID = li.ID
-				AND li.SEVERITY = 'N'
-			GROUP BY lci.ISSUER_CA_ID
-		) sub
-	WHERE l1st.ISSUER_CA_ID = sub.ISSUER_CA_ID
-		AND l1st.LINTER IS NULL;
-
 ANALYZE lint_1week_summary_temp;
 
 GRANT SELECT ON lint_1week_summary_temp TO httpd;
diff --git a/web_apis.fnc b/web_apis.fnc
index 6e3a335..b05c02c 100644
--- a/web_apis.fnc
+++ b/web_apis.fnc
@@ -126,7 +126,7 @@ DECLARE
 	t_joinToCertificate_table	text;
 	t_joinToCTLogEntry	text;
 	t_showIdentity		boolean;
-	t_minNotBefore		timestamp;
+	t_minNotBefore		date;
 	t_minNotBeforeString	text;
 	t_excludeExpired	text;
 	t_excludeAffectedCerts	text;
@@ -308,10 +308,10 @@ BEGIN
 
 	t_temp := get_parameter('minNotBefore', paramNames, paramValues);
 	IF t_temp IS NULL THEN
-		t_minNotBefore := date_trunc('day', statement_timestamp() - interval '1 week');
+		t_minNotBefore := (statement_timestamp() at time zone 'UTC' - interval '1 week')::date;
 		t_minNotBeforeString := '';
 	ELSE
-		t_minNotBefore := t_temp::timestamp;
+		t_minNotBefore := t_temp::date;
 		t_minNotBeforeString := '&minNotBefore=' || t_temp;
 	END IF;
 
@@ -2465,29 +2465,23 @@ Content-Type: text/plain; charset=UTF-8
         </TR>
 ';
 				FOR l_record IN (
-							SELECT count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
+							SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
 									CASE li.SEVERITY
 										WHEN 'F' THEN 1
 										WHEN 'E' THEN 2
 										WHEN 'W' THEN 3
-										WHEN 'N' THEN 4
-										WHEN 'I' THEN 5
-										WHEN 'B' THEN 6
-										ELSE 7
+										ELSE 4
 									END ISSUE_TYPE,
 									CASE li.SEVERITY
 										WHEN 'F' THEN '<SPAN class="fatal">&nbsp; &nbsp;FATAL:'
 										WHEN 'E' THEN '<SPAN class="error">&nbsp; &nbsp;ERROR:'
 										WHEN 'W' THEN '<SPAN class="warning">&nbsp;WARNING:'
-										WHEN 'N' THEN '<SPAN class="notice">&nbsp; NOTICE:'
-										WHEN 'I' THEN '<SPAN>&nbsp; &nbsp; INFO:'
-										WHEN 'B' THEN '<SPAN>&nbsp; &nbsp; &nbsp;BUG:'
 										ELSE '<SPAN>&nbsp; &nbsp; &nbsp; &nbsp;' || li.SEVERITY || ':'
 									END ISSUE_HEADING
-								FROM lint_cert_issue lci, lint_issue li
-								WHERE lci.NOT_BEFORE >= t_minNotBefore
-									AND lci.ISSUER_CA_ID = t_value::integer
-									AND lci.LINT_ISSUE_ID = li.ID
+								FROM lint_summary ls, lint_issue li
+								WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore
+									AND ls.ISSUER_CA_ID = t_value::integer
+									AND ls.LINT_ISSUE_ID = li.ID
 									AND li.LINTER = 'cablint'
 								GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT
 								ORDER BY ISSUE_TYPE, NUM_CERTS DESC
@@ -2520,29 +2514,23 @@ Content-Type: text/plain; charset=UTF-8
         </TR>
 ';
 				FOR l_record IN (
-							SELECT count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
+							SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
 									CASE li.SEVERITY
 										WHEN 'F' THEN 1
 										WHEN 'E' THEN 2
 										WHEN 'W' THEN 3
-										WHEN 'N' THEN 4
-										WHEN 'I' THEN 5
-										WHEN 'B' THEN 6
-										ELSE 7
+										ELSE 4
 									END ISSUE_TYPE,
 									CASE li.SEVERITY
 										WHEN 'F' THEN '<SPAN class="fatal">&nbsp; &nbsp;FATAL:'
 										WHEN 'E' THEN '<SPAN class="error">&nbsp; &nbsp;ERROR:'
 										WHEN 'W' THEN '<SPAN class="warning">&nbsp;WARNING:'
-										WHEN 'N' THEN '<SPAN class="notice">&nbsp; NOTICE:'
-										WHEN 'I' THEN '<SPAN>&nbsp; &nbsp; INFO:'
-										WHEN 'B' THEN '<SPAN>&nbsp; &nbsp; &nbsp;BUG:'
 										ELSE '<SPAN>&nbsp; &nbsp; &nbsp; &nbsp;' || li.SEVERITY || ':'
 									END ISSUE_HEADING
-								FROM lint_cert_issue lci, lint_issue li
-								WHERE lci.NOT_BEFORE >= t_minNotBefore
-									AND lci.ISSUER_CA_ID = t_value::integer
-									AND lci.LINT_ISSUE_ID = li.ID
+								FROM lint_summary ls, lint_issue li
+								WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore
+									AND ls.ISSUER_CA_ID = t_value::integer
+									AND ls.LINT_ISSUE_ID = li.ID
 									AND li.LINTER = 'x509lint'
 								GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT
 								ORDER BY ISSUE_TYPE, NUM_CERTS DESC
@@ -2575,29 +2563,23 @@ Content-Type: text/plain; charset=UTF-8
         </TR>
 ';
 				FOR l_record IN (
-							SELECT count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
+							SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
 									CASE li.SEVERITY
 										WHEN 'F' THEN 1
 										WHEN 'E' THEN 2
 										WHEN 'W' THEN 3
-										WHEN 'N' THEN 4
-										WHEN 'I' THEN 5
-										WHEN 'B' THEN 6
-										ELSE 7
+										ELSE 4
 									END ISSUE_TYPE,
 									CASE li.SEVERITY
 										WHEN 'F' THEN '<SPAN class="fatal">&nbsp; &nbsp;FATAL:'
 										WHEN 'E' THEN '<SPAN class="error">&nbsp; &nbsp;ERROR:'
 										WHEN 'W' THEN '<SPAN class="warning">&nbsp;WARNING:'
-										WHEN 'N' THEN '<SPAN class="notice">&nbsp; NOTICE:'
-										WHEN 'I' THEN '<SPAN>&nbsp; &nbsp; INFO:'
-										WHEN 'B' THEN '<SPAN>&nbsp; &nbsp; &nbsp;BUG:'
 										ELSE '<SPAN>&nbsp; &nbsp; &nbsp; &nbsp;' || li.SEVERITY || ':'
 									END ISSUE_HEADING
-								FROM lint_cert_issue lci, lint_issue li
-								WHERE lci.NOT_BEFORE >= t_minNotBefore
-									AND lci.ISSUER_CA_ID = t_value::integer
-									AND lci.LINT_ISSUE_ID = li.ID
+								FROM lint_summary ls, lint_issue li
+								WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore
+									AND ls.ISSUER_CA_ID = t_value::integer
+									AND ls.LINT_ISSUE_ID = li.ID
 									AND li.LINTER = 'zlint'
 								GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT
 								ORDER BY ISSUE_TYPE, NUM_CERTS DESC
@@ -3075,9 +3057,6 @@ Content-Type: text/plain; charset=UTF-8
 							WHEN 'F' THEN '<SPAN class="fatal">&nbsp;FATAL:'
 							WHEN 'E' THEN '<SPAN class="error">&nbsp;ERROR:'
 							WHEN 'W' THEN '<SPAN class="warning">&nbsp;WARNING:'
-							WHEN 'N' THEN '<SPAN class="notice">&nbsp;NOTICE:'
-							WHEN 'I' THEN '<SPAN>&nbsp;INFO:'
-							WHEN 'B' THEN '<SPAN>&nbsp;BUG:'
 							ELSE '<SPAN>&nbsp;' || li.SEVERITY || ':'
 						END || ' ' || li.ISSUE_TEXT || '&nbsp;</SPAN>'
 					INTO t_temp
@@ -3151,7 +3130,7 @@ Content-Type: text/plain; charset=UTF-8
 						'	WHERE c.ISSUER_CA_ID = $1::integer' || chr(10) ||
 						'		AND c.ID = lci.CERTIFICATE_ID' || chr(10) ||
 						'		AND lci.ISSUER_CA_ID = $1::integer' || chr(10) ||
-						'		AND lci.NOT_BEFORE >= $3' || chr(10) ||
+						'		AND lci.NOT_BEFORE_DATE >= $3' || chr(10) ||
 						'		AND lci.LINT_ISSUE_ID = $2::integer' || chr(10) ||
 						'		AND lci.LINT_ISSUE_ID = li.ID' || chr(10);
 				IF t_linter IS NOT NULL THEN
@@ -3406,7 +3385,7 @@ Content-Type: text/plain; charset=UTF-8
 				END IF;
 				t_where := t_where || chr(10) ||
 							'        AND lci.LINT_ISSUE_ID = $1::integer' || chr(10) ||
-							'        AND lci.NOT_BEFORE >= $2' || chr(10) ||
+							'        AND lci.NOT_BEFORE_DATE >= $2' || chr(10) ||
 							'        AND lci.LINT_ISSUE_ID = li.ID' || chr(10) ||
 							'        AND ca.LINTING_APPLIES';
 				IF t_linter IS NOT NULL THEN
@@ -3784,11 +3763,10 @@ Content-Type: application/atom+xml
 					t_output := t_output || ' ' || t_dirSymbol;
 				END IF;
 				t_output := t_output || '</TH>
-      <TH colspan="3"><A title="These errors are fatal to the checks and prevent most further checks from being executed.  These are extremely bad errors."><SPAN class="fatal">&nbsp;FATAL&nbsp;</SPAN></A></TH>
-      <TH colspan="3"><A title="These are issues where the certificate is not compliant with the standard."><SPAN class="error">&nbsp;ERROR&nbsp;</SPAN></A></TH>
-      <TH colspan="3"><A title="These are issues where a standard recommends differently but the standard uses terms such as ''SHOULD'' or ''MAY''."><SPAN class="warning">&nbsp;WARNING&nbsp;</SPAN></A></TH>
-      <TH colspan="3"><A title="These are items known to cause issues with one or more implementations of certificate processing but are not errors according to the standard."><SPAN class="notice">&nbsp;NOTICE&nbsp;</SPAN></A></TH>
-      <TH colspan="3"><A title="FATAL + ERROR + WARNING + NOTICE">ALL</A></TH>
+      <TH colspan="2"><A title="These errors are fatal to the checks and prevent most further checks from being executed.  These are extremely bad errors."><SPAN class="fatal">&nbsp;FATAL&nbsp;</SPAN></A></TH>
+      <TH colspan="2"><A title="These are issues where the certificate is not compliant with the standard."><SPAN class="error">&nbsp;ERROR&nbsp;</SPAN></A></TH>
+      <TH colspan="2"><A title="These are issues where a standard recommends differently but the standard uses terms such as ''SHOULD'' or ''MAY''."><SPAN class="warning">&nbsp;WARNING&nbsp;</SPAN></A></TH>
+      <TH colspan="2"><A title="FATAL + ERROR + WARNING">ALL</A></TH>
     </TR>
     <TR>
       <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=4' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>';
@@ -3801,11 +3779,6 @@ Content-Type: application/atom+xml
 					t_output := t_output || ' ' || t_dirSymbol;
 				END IF;
 				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=6' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>';
-				IF t_sort = 6 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
       <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=7' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>';
 				IF t_sort = 7 THEN
 					t_output := t_output || ' ' || t_dirSymbol;
@@ -3816,11 +3789,6 @@ Content-Type: application/atom+xml
 					t_output := t_output || ' ' || t_dirSymbol;
 				END IF;
 				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=9' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>';
-				IF t_sort = 9 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
       <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=10' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>';
 				IF t_sort = 10 THEN
 					t_output := t_output || ' ' || t_dirSymbol;
@@ -3831,26 +3799,6 @@ Content-Type: application/atom+xml
 					t_output := t_output || ' ' || t_dirSymbol;
 				END IF;
 				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=12' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>';
-				IF t_sort = 12 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=13' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>';
-				IF t_sort = 13 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=14' || t_groupByParameter || t_issuerOParameter || '">%</A>';
-				IF t_sort = 14 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=15' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>';
-				IF t_sort = 15 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
       <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=16' || t_groupByParameter || t_issuerOParameter || '"># Certs</A>';
 				IF t_sort = 16 THEN
 					t_output := t_output || ' ' || t_dirSymbol;
@@ -3861,11 +3809,6 @@ Content-Type: application/atom+xml
 					t_output := t_output || ' ' || t_dirSymbol;
 				END IF;
 				t_output := t_output || '</TH>
-      <TH><A href="?' || urlEncode(t_cmd) || '=' || urlEncode(t_value) || '&dir=' || t_oppositeDirection || '&sort=18' || t_groupByParameter || t_issuerOParameter || '"># Issues</A>';
-				IF t_sort = 18 THEN
-					t_output := t_output || ' ' || t_dirSymbol;
-				END IF;
-				t_output := t_output || '</TH>
     </TR>
 ';
 			END IF;
@@ -3874,40 +3817,26 @@ Content-Type: application/atom+xml
 				t_query := 'SELECT NULL::integer ISSUER_CA_ID,' || chr(10) ||
 							'		(sum(l1s.CERTS_ISSUED))::bigint CERTS_ISSUED,' || chr(10) ||
 							'		(sum(l1s.ALL_CERTS))::bigint ALL_CERTS,' || chr(10) ||
-							'		(sum(l1s.ALL_ISSUES))::bigint ALL_ISSUES,' || chr(10) ||
 							'		((sum(l1s.ALL_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric ALL_PERC,' || chr(10) ||
 							'		(sum(l1s.FATAL_CERTS))::bigint FATAL_CERTS,' || chr(10) ||
-							'		(sum(l1s.FATAL_ISSUES))::bigint FATAL_ISSUES,' || chr(10) ||
 							'		((sum(l1s.FATAL_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric FATAL_PERC,' || chr(10) ||
 							'		(sum(l1s.ERROR_CERTS))::bigint ERROR_CERTS,' || chr(10) ||
-							'		(sum(l1s.ERROR_ISSUES))::bigint ERROR_ISSUES,' || chr(10) ||
 							'		((sum(l1s.ERROR_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric ERROR_PERC,' || chr(10) ||
 							'		(sum(l1s.WARNING_CERTS))::bigint WARNING_CERTS,' || chr(10) ||
-							'		(sum(l1s.WARNING_ISSUES))::bigint WARNING_ISSUES,' || chr(10) ||
 							'		((sum(l1s.WARNING_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric WARNING_PERC,' || chr(10) ||
-							'		(sum(l1s.NOTICE_CERTS))::bigint NOTICE_CERTS,' || chr(10) ||
-							'		(sum(l1s.NOTICE_ISSUES))::bigint NOTICE_ISSUES,' || chr(10) ||
-							'		((sum(l1s.NOTICE_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric NOTICE_PERC,' || chr(10) ||
 							'		get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') ISSUER_ORGANIZATION_NAME,' || chr(10) ||
 							'		NULL ISSUER_FRIENDLY_NAME' || chr(10);
 			ELSE
 				t_query := 'SELECT l1s.ISSUER_CA_ID::integer,' || chr(10) ||
 							'		l1s.CERTS_ISSUED::bigint,' || chr(10) ||
 							'		l1s.ALL_CERTS::bigint,' || chr(10) ||
-							'		l1s.ALL_ISSUES::bigint,' || chr(10) ||
 							'		((l1s.ALL_CERTS * 100) / l1s.CERTS_ISSUED::numeric) ALL_PERC,' || chr(10) ||
 							'		l1s.FATAL_CERTS::bigint,' || chr(10) ||
-							'		l1s.FATAL_ISSUES::bigint,' || chr(10) ||
 							'		((l1s.FATAL_CERTS * 100) / l1s.CERTS_ISSUED::numeric) FATAL_PERC,' || chr(10) ||
 							'		l1s.ERROR_CERTS::bigint,' || chr(10) ||
-							'		l1s.ERROR_ISSUES::bigint,' || chr(10) ||
 							'		((l1s.ERROR_CERTS * 100) / l1s.CERTS_ISSUED::numeric) ERROR_PERC,' || chr(10) ||
 							'		l1s.WARNING_CERTS::bigint,' || chr(10) ||
-							'		l1s.WARNING_ISSUES::bigint,' || chr(10) ||
 							'		((l1s.WARNING_CERTS * 100) / l1s.CERTS_ISSUED::numeric) WARNING_PERC,' || chr(10) ||
-							'		l1s.NOTICE_CERTS::bigint,' || chr(10) ||
-							'		l1s.NOTICE_ISSUES::bigint,' || chr(10) ||
-							'		((l1s.NOTICE_CERTS * 100) / l1s.CERTS_ISSUED::numeric) NOTICE_PERC,' || chr(10) ||
 							'		get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') ISSUER_ORGANIZATION_NAME,' || chr(10) ||
 							'		get_ca_name_attribute(l1s.ISSUER_CA_ID, ''_friendlyName_'') ISSUER_FRIENDLY_NAME' || chr(10);
 			END IF;
@@ -3939,32 +3868,18 @@ Content-Type: application/atom+xml
 				t_query := t_query || 'ORDER BY FATAL_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 5 THEN
 				t_query := t_query || 'ORDER BY FATAL_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 6 THEN
-				t_query := t_query || 'ORDER BY FATAL_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 7 THEN
 				t_query := t_query || 'ORDER BY ERROR_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 8 THEN
 				t_query := t_query || 'ORDER BY ERROR_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 9 THEN
-				t_query := t_query || 'ORDER BY ERROR_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 10 THEN
 				t_query := t_query || 'ORDER BY WARNING_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 11 THEN
 				t_query := t_query || 'ORDER BY WARNING_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 12 THEN
-				t_query := t_query || 'ORDER BY WARNING_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 13 THEN
-				t_query := t_query || 'ORDER BY NOTICE_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 14 THEN
-				t_query := t_query || 'ORDER BY NOTICE_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 15 THEN
-				t_query := t_query || 'ORDER BY NOTICE_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 16 THEN
 				t_query := t_query || 'ORDER BY ALL_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			ELSIF t_sort = 17 THEN
 				t_query := t_query || 'ORDER BY ALL_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
-			ELSIF t_sort = 18 THEN
-				t_query := t_query || 'ORDER BY ALL_ISSUES ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
 			END IF;
 
 			FOR l_record IN EXECUTE t_query USING t_issuerO LOOP
@@ -3993,19 +3908,12 @@ Content-Type: application/atom+xml
 '      <TD>' || l_record.CERTS_ISSUED::text || '</TD>
       <TD>' || l_record.FATAL_CERTS::text || '</TD>
       <TD>' || replace(round(l_record.FATAL_PERC, 2)::text, '.00', '') || '</TD>
-      <TD>' || l_record.FATAL_ISSUES::text || '</TD>
       <TD>' || l_record.ERROR_CERTS::text || '</TD>
       <TD>' || replace(round(l_record.ERROR_PERC, 2)::text, '.00', '') || '</TD>
-      <TD>' || l_record.ERROR_ISSUES::text || '</TD>
       <TD>' || l_record.WARNING_CERTS::text || '</TD>
       <TD>' || replace(round(l_record.WARNING_PERC, 2)::text, '.00', '') || '</TD>
-      <TD>' || l_record.WARNING_ISSUES::text || '</TD>
-      <TD>' || l_record.NOTICE_CERTS::text || '</TD>
-      <TD>' || replace(round(l_record.NOTICE_PERC, 2)::text, '.00', '') || '</TD>
-      <TD>' || l_record.NOTICE_ISSUES::text || '</TD>
       <TD>' || l_record.ALL_CERTS::text || '</TD>
       <TD>' || replace(round(l_record.ALL_PERC, 2)::text, '.00', '') || '</TD>
-      <TD>' || l_record.ALL_ISSUES::text || '</TD>
     </TR>
 ';
 				END IF;
@@ -4057,7 +3965,7 @@ Content-Type: application/atom+xml
 
 		t_query := 'SELECT li.ID, li.ISSUE_TEXT,';
 		IF t_excludeAffectedCerts IS NULL THEN
-			t_query := t_query || ' count(DISTINCT lci.CERTIFICATE_ID) NUM_CERTS,';
+			t_query := t_query || ' sum(ls.NO_OF_CERTS) NUM_CERTS,';
 		ELSE
 			t_query := t_query || ' -1::bigint NUM_CERTS,';
 		END IF;
@@ -4066,39 +3974,32 @@ Content-Type: application/atom+xml
 					'			WHEN ''F'' THEN 1' || chr(10) ||
 					'			WHEN ''E'' THEN 2' || chr(10) ||
 					'			WHEN ''W'' THEN 3' || chr(10) ||
-					'			WHEN ''N'' THEN 4' || chr(10) ||
-					'			WHEN ''I'' THEN 5' || chr(10) ||
-					'			WHEN ''B'' THEN 6' || chr(10) ||
-					'			ELSE 7' || chr(10) ||
+					'			ELSE 4' || chr(10) ||
 					'		END ISSUE_TYPE,' || chr(10) ||
 					'		CASE li.SEVERITY' || chr(10) ||
 					'			WHEN ''F'' THEN ''FATAL''' || chr(10) ||
 					'			WHEN ''E'' THEN ''ERROR''' || chr(10) ||
 					'			WHEN ''W'' THEN ''WARNING''' || chr(10) ||
-					'			WHEN ''N'' THEN ''NOTICE''' || chr(10) ||
-					'			WHEN ''I'' THEN ''INFO''' || chr(10) ||
-					'			WHEN ''B'' THEN ''BUG''' || chr(10) ||
 					'			ELSE li.SEVERITY ' || chr(10) ||
 					'		END ISSUE_HEADING,' || chr(10) ||
 					'		CASE li.SEVERITY' || chr(10) ||
 					'			WHEN ''F'' THEN ''class="fatal"''' || chr(10) ||
 					'			WHEN ''E'' THEN ''class="error"''' || chr(10) ||
 					'			WHEN ''W'' THEN ''class="warning"''' || chr(10) ||
-					'			WHEN ''N'' THEN ''class="notice"''' || chr(10) ||
 					'			ELSE ''''' || chr(10) ||
 					'		END ISSUE_CLASS' || chr(10);
 		IF t_excludeAffectedCerts IS NULL THEN
 			t_query := t_query ||
-					'	FROM lint_cert_issue lci, lint_issue li, ca' || chr(10) ||
-					'	WHERE lci.LINT_ISSUE_ID = li.ID' || chr(10) ||
-					'		AND lci.ISSUER_CA_ID = ca.ID' || chr(10) ||
+					'	FROM lint_summary ls, lint_issue li, ca' || chr(10) ||
+					'	WHERE ls.LINT_ISSUE_ID = li.ID' || chr(10) ||
+					'		AND ls.ISSUER_CA_ID = ca.ID' || chr(10) ||
 					'		AND ca.LINTING_APPLIES' || chr(10);
 			IF t_linter IS NOT NULL THEN
 				t_query := t_query ||
 					'		AND li.LINTER = ''' || t_linter || '''' || chr(10);
 			END IF;
 			t_query := t_query ||
-					'		AND lci.NOT_BEFORE >= $1' || chr(10) ||
+					'		AND ls.NOT_BEFORE_DATE >= $1' || chr(10) ||
 					'	GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT' || chr(10);
 		ELSE
 			t_query := t_query ||