Refactor DB and app_t structure - preparation for OCSP checking.

New version of DB is needed to keep separately certificates from
each signatrue. Because application can be signed by more than one
signature we need to keep all certificates from all signatures in
separate lists to make building certificates' chains easier.

* Add operators (==, !=, <) for app_t structre
* Add operators (==, !=) for event_t structure
* Add tests for operators
* Remove check_id from app_t structure

Change-Id: I966dd81420618325d1afa55bcbc656291ccb4238

2 files changed, 65 insertions, 47 deletions

diff --git a/db/cert-checker.sql b/db/cert-checker.sql
index 0103a49..2503e7c 100644
--- a/db/cert-checker.sql
+++ b/db/cert-checker.sql
@@ -21,13 +21,21 @@ CREATE TABLE IF NOT EXISTS ocsp_urls (
   date   INTEGER NOT NULL
 );
 
+-- Table 'chains_to_check'
+CREATE TABLE IF NOT EXISTS chains_to_check (
+  chain_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  check_id INTEGER NOT NULL,
+
+  FOREIGN KEY (check_id) REFERENCES to_check(check_id) ON DELETE CASCADE
+);
+
 -- Table 'certs_to_check'
 CREATE TABLE IF NOT EXISTS certs_to_check (
-  cert_id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  check_id    INTEGER NOT NULL,
+  chain_id    INTEGER NOT NULL,
   certificate TEXT NOT NULL,
 
-  FOREIGN KEY (check_id) REFERENCES to_check(check_id) ON DELETE CASCADE
+  PRIMARY KEY (chain_id, certificate),
+  FOREIGN KEY (chain_id) REFERENCES chains_to_check(chain_id) ON DELETE CASCADE
 );
 
 COMMIT TRANSACTION;
diff --git a/db/cert-checker.xml b/db/cert-checker.xml
index c0221fc..e72b044 100644
--- a/db/cert-checker.xml
+++ b/db/cert-checker.xml
@@ -2,41 +2,41 @@
 <!-- SQL XML created by WWW SQL Designer, http://code.google.com/p/wwwsqldesigner/ -->
 <sql>
 <datatypes db="mysql">
-	<group label="Numeric" color="rgb(238,238,170)">
-	 	<type label="TINYINT" length="0" sql="TINYINT" quote=""/>
-	 	<type label="SMALLINT" length="0" sql="SMALLINT" quote=""/>
-	 	<type label="MEDIUMINT" length="0" sql="MEDIUMINT" quote=""/>
-	 	<type label="INT" length="0" sql="INT" quote=""/>
-		<type label="Integer" length="0" sql="INTEGER" quote=""/>
-		<type label="BIGINT" length="0" sql="BIGINT" quote=""/>
-		<type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote=""/>
-		<type label="Single precision" length="0" sql="FLOAT" quote=""/>
-		<type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote=""/>
-	</group>
+    <group label="Numeric" color="rgb(238,238,170)">
+        <type label="TINYINT" length="0" sql="TINYINT" quote=""/>
+        <type label="SMALLINT" length="0" sql="SMALLINT" quote=""/>
+        <type label="MEDIUMINT" length="0" sql="MEDIUMINT" quote=""/>
+        <type label="INT" length="0" sql="INT" quote=""/>
+        <type label="Integer" length="0" sql="INTEGER" quote=""/>
+        <type label="BIGINT" length="0" sql="BIGINT" quote=""/>
+        <type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote=""/>
+        <type label="Single precision" length="0" sql="FLOAT" quote=""/>
+        <type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote=""/>
+    </group>
 
-	<group label="Character" color="rgb(255,200,200)">
-		<type label="Char" length="1" sql="CHAR" quote="'"/>
-		<type label="Varchar" length="1" sql="VARCHAR" quote="'"/>
-		<type label="Text" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>
-		<type label="Binary" length="1" sql="BINARY" quote="'"/>
-		<type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>
-		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
-	</group>
+    <group label="Character" color="rgb(255,200,200)">
+        <type label="Char" length="1" sql="CHAR" quote="'"/>
+        <type label="Varchar" length="1" sql="VARCHAR" quote="'"/>
+        <type label="Text" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>
+        <type label="Binary" length="1" sql="BINARY" quote="'"/>
+        <type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>
+        <type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
+    </group>
 
-	<group label="Date &amp; Time" color="rgb(200,255,200)">
-		<type label="Date" length="0" sql="DATE" quote="'"/>
-		<type label="Time" length="0" sql="TIME" quote="'"/>
-		<type label="Datetime" length="0" sql="DATETIME" quote="'"/>
-		<type label="Year" length="0" sql="YEAR" quote=""/>
-		<type label="Timestamp" length="0" sql="TIMESTAMP" quote="'"/>
-	</group>
-	
-	<group label="Miscellaneous" color="rgb(200,200,255)">
-		<type label="ENUM" length="1" sql="ENUM" quote=""/>
-		<type label="SET" length="1" sql="SET" quote=""/>
-		<type label="Bit" length="0" sql="bit" quote=""/>
-	</group>
-</datatypes><table x="760" y="211" name="to_check">
+    <group label="Date &amp; Time" color="rgb(200,255,200)">
+        <type label="Date" length="0" sql="DATE" quote="'"/>
+        <type label="Time" length="0" sql="TIME" quote="'"/>
+        <type label="Datetime" length="0" sql="DATETIME" quote="'"/>
+        <type label="Year" length="0" sql="YEAR" quote=""/>
+        <type label="Timestamp" length="0" sql="TIMESTAMP" quote="'"/>
+    </group>
+    
+    <group label="Miscellaneous" color="rgb(200,200,255)">
+        <type label="ENUM" length="1" sql="ENUM" quote=""/>
+        <type label="SET" length="1" sql="SET" quote=""/>
+        <type label="Bit" length="0" sql="bit" quote=""/>
+    </group>
+</datatypes><table x="730" y="211" name="to_check">
 <row name="check_id" null="0" autoincrement="1">
 <datatype>INTEGER</datatype>
 </row>
@@ -51,15 +51,12 @@
 </row>
 <row name="verified" null="0" autoincrement="0">
 <datatype>INTEGER</datatype>
-<default>2</default></row>
+<default>NULL</default></row>
 <key type="PRIMARY" name="">
 <part>check_id</part>
 </key>
 </table>
-<table x="1132" y="211" name="ocsp_urls">
-<row name="url_id" null="0" autoincrement="1">
-<datatype>INTEGER</datatype>
-</row>
+<table x="1222" y="211" name="ocsp_urls">
 <row name="issuer" null="0" autoincrement="0">
 <datatype>MEDIUMTEXT</datatype>
 </row>
@@ -70,21 +67,34 @@
 <datatype>INT</datatype>
 </row>
 <key type="PRIMARY" name="">
-<part>url_id</part>
+<part>issuer</part>
+<part>url</part>
 </key>
 </table>
-<table x="928" y="211" name="certs_to_check">
+<table x="897" y="211" name="certs_to_check">
 <row name="check_id" null="0" autoincrement="0">
 <datatype>INTEGER</datatype>
 <relation table="to_check" row="check_id" />
 </row>
-<row name="certificate" null="0" autoincrement="0">
-<datatype>MEDIUMTEXT</datatype>
+<row name="chain_id" null="0" autoincrement="0">
+<datatype>INTEGER</datatype>
+<default>NULL</default><relation table="certs" row="chain_id" />
 </row>
 <key type="PRIMARY" name="">
 <part>check_id</part>
-<part>certificate</part>
+<part>chain_id</part>
+</key>
+</table>
+<table x="1097" y="235" name="certs">
+<row name="chain_id" null="0" autoincrement="0">
+<datatype>INTEGER</datatype>
+<default>NULL</default></row>
+<row name="certificates" null="0" autoincrement="0">
+<datatype>MEDIUMTEXT</datatype>
+<default>'NULL'</default></row>
+<key type="PRIMARY" name="">
+<part>certificates</part>
+<part>chain_id</part>
 </key>
 </table>
 </sql>
-