From: Felix Dörre <felix@dogcraft.de>
Date: Sun, 28 Sep 2014 15:54:55 +0000 (+0200)
Subject: Deleted should be NULL if not set, and not strange date.
X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=701ba7f582c84412cabd47aeb9d785b93a892c07

Deleted should be NULL if not set, and not strange date.
---

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
index 2d78f951..7bc8e360 100644
--- a/doc/tableStructure.sql
+++ b/doc/tableStructure.sql
@@ -50,8 +50,8 @@ CREATE TABLE `domains` (
   `memid` int(11) NOT NULL,
   `domain` varchar(255) NOT NULL,
   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `deleted` datetime DEFAULT NULL,
+  `modified` datetime NULL DEFAULT NULL,
+  `deleted` datetime NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `memid` (`memid`),
   KEY `domain` (`domain`),
@@ -63,9 +63,9 @@ CREATE TABLE `emails` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `memid` int(11) NOT NULL DEFAULT '0',
   `email` varchar(255) NOT NULL DEFAULT '',
-  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `modified` datetime NULL DEFAULT NULL,
+  `deleted` datetime NULL DEFAULT NULL,
   `hash` varchar(50) NOT NULL DEFAULT '',
   `attempts` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
@@ -149,10 +149,10 @@ CREATE TABLE `certs` (
   `csr_name` varchar(255) NOT NULL DEFAULT '',
   `csr_type` enum('CSR', 'SPKAC') NOT NULL,
   `crt_name` varchar(255) NOT NULL DEFAULT '',
-  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `revoked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `created` timestamp NULL DEFAULT NULL,
+  `modified` datetime NULL DEFAULT NULL,
+  `revoked` datetime NULL DEFAULT NULL,
+  `expire` datetime NULL DEFAULT NULL,
   `renewed` tinyint(1) NOT NULL DEFAULT '0',
   `disablelogin` int(1) NOT NULL DEFAULT '0',
   `pkhash` char(40) DEFAULT NULL,
@@ -244,13 +244,13 @@ CREATE TABLE `notary` (
   `location` varchar(255) NOT NULL DEFAULT '',
   `date` varchar(255) NOT NULL DEFAULT '',
 # date when assurance was entered
-  `when` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 #?
-  `expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `expire` datetime NULL DEFAULT NULL,
 #?????????????????
   `sponsor` int(11) NOT NULL DEFAULT '0',
 # date when assurance was deleted (or 0)
-  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `deleted` datetime NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `from` (`from`),
   KEY `to` (`to`),
diff --git a/src/org/cacert/gigi/dbObjects/EmailAddress.java b/src/org/cacert/gigi/dbObjects/EmailAddress.java
index f9188180..f398087d 100644
--- a/src/org/cacert/gigi/dbObjects/EmailAddress.java
+++ b/src/org/cacert/gigi/dbObjects/EmailAddress.java
@@ -22,7 +22,7 @@ public class EmailAddress implements IdCachable {
     private String hash = null;
 
     private EmailAddress(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, email, hash FROM `emails` WHERE id=? AND deleted=0");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, email, hash FROM `emails` WHERE id=? AND deleted is NULL");
         ps.setInt(1, id);
 
         GigiResultSet rs = ps.executeQuery();
diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java
index 31ce4421..63dc812c 100644
--- a/src/org/cacert/gigi/dbObjects/User.java
+++ b/src/org/cacert/gigi/dbObjects/User.java
@@ -167,7 +167,7 @@ public class User extends CertificateOwner {
     }
 
     public int getAssurancePoints() {
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT sum(points) FROM `notary` where `to`=? AND `deleted`=0");
+        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT sum(points) FROM `notary` where `to`=? AND `deleted` is NULL");
         query.setInt(1, getId());
         GigiResultSet rs = query.executeQuery();
         int points = 0;
@@ -179,7 +179,7 @@ public class User extends CertificateOwner {
     }
 
     public int getExperiencePoints() {
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT count(*) FROM `notary` where `from`=? AND `deleted`=0");
+        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT count(*) FROM `notary` where `from`=? AND `deleted` is NULL");
         query.setInt(1, getId());
         GigiResultSet rs = query.executeQuery();
         int points = 0;
@@ -248,7 +248,7 @@ public class User extends CertificateOwner {
     }
 
     public EmailAddress[] getEmails() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM emails WHERE memid=? AND deleted=0");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM emails WHERE memid=? AND deleted is NULL");
         ps.setInt(1, getId());
         GigiResultSet rs = ps.executeQuery();
         rs.last();
@@ -286,7 +286,7 @@ public class User extends CertificateOwner {
     }
 
     public Certificate[] getCertificates() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT serial FROM certs WHERE memid=? AND revoked=0");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT serial FROM certs WHERE memid=? AND revoked IS NULL");
         ps.setInt(1, getId());
         GigiResultSet rs = ps.executeQuery();
         rs.last();
@@ -364,7 +364,7 @@ public class User extends CertificateOwner {
 
     public Assurance[] getReceivedAssurances() {
         if (receivedAssurances == null) {
-            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM notary WHERE `to`=? AND deleted=0");
+            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM notary WHERE `to`=? AND deleted IS NULL");
             query.setInt(1, getId());
             GigiResultSet res = query.executeQuery();
             res.last();
@@ -382,7 +382,7 @@ public class User extends CertificateOwner {
 
     public Assurance[] getMadeAssurances() {
         if (madeAssurances == null) {
-            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM notary WHERE `from`=? AND deleted=0");
+            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM notary WHERE `from`=? AND deleted is NULL");
             query.setInt(1, getId());
             GigiResultSet res = query.executeQuery();
             res.last();
diff --git a/src/org/cacert/gigi/pages/LoginPage.java b/src/org/cacert/gigi/pages/LoginPage.java
index e647c053..d2d39ba4 100644
--- a/src/org/cacert/gigi/pages/LoginPage.java
+++ b/src/org/cacert/gigi/pages/LoginPage.java
@@ -83,7 +83,7 @@ public class LoginPage extends Page {
 
     private void tryAuthWithCertificate(HttpServletRequest req, X509Certificate x509Certificate) {
         String serial = x509Certificate.getSerialNumber().toString(16).toUpperCase();
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `certs` WHERE `serial`=? AND `disablelogin`='0' AND `revoked` = '0000-00-00 00:00:00'");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `certs` WHERE `serial`=? AND `disablelogin`='0' AND `revoked` is NULL");
         ps.setString(1, serial);
         GigiResultSet rs = ps.executeQuery();
         if (rs.next()) {
diff --git a/src/org/cacert/gigi/pages/main/Signup.java b/src/org/cacert/gigi/pages/main/Signup.java
index 24562ac8..07cd6084 100644
--- a/src/org/cacert/gigi/pages/main/Signup.java
+++ b/src/org/cacert/gigi/pages/main/Signup.java
@@ -119,8 +119,8 @@ public class Signup extends Form {
         if (isFailed(out)) {
             return false;
         }
-        GigiPreparedStatement q1 = DatabaseConnection.getInstance().prepare("select * from `emails` where `email`=? and `deleted`=0");
-        GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("select * from certOwners inner join users on users.id=certOwners.id where `email`=? and `deleted`=0");
+        GigiPreparedStatement q1 = DatabaseConnection.getInstance().prepare("select * from `emails` where `email`=? and `deleted` IS NULL");
+        GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("select * from certOwners inner join users on users.id=certOwners.id where `email`=? and `deleted` IS NULL");
         q1.setString(1, buildup.getEmail());
         q2.setString(1, buildup.getEmail());
         GigiResultSet r1 = q1.executeQuery();
diff --git a/src/org/cacert/gigi/util/Notary.java b/src/org/cacert/gigi/util/Notary.java
index bd6f7625..3dfc4faa 100644
--- a/src/org/cacert/gigi/util/Notary.java
+++ b/src/org/cacert/gigi/util/Notary.java
@@ -29,7 +29,7 @@ public class Notary {
         if (assurer.getId() == target.getId()) {
             throw new GigiApiException("You cannot assure yourself.");
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `notary` where `to`=? and `from`=? AND `deleted`=0");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `notary` where `to`=? and `from`=? AND `deleted` IS NULL");
         ps.setInt(1, target.getId());
         ps.setInt(2, assurer.getId());
         GigiResultSet rs = ps.executeQuery();