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

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
index 2d78f9511521ec08ba3d3cca2cdea1e30fecf93a..7bc8e3603b5ebf859ad9d82dc6781ea00320e2e0 100644 (file)
--- 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 f91881800e919acda4a0978f2d7e1c9a5d935aac..f398087d16bf067213afc0b2507557de10cfc658 100644 (file)
--- 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 31ce4421277a98bf9657fa96bae3aa853643e02d..63dc812c5150c6ac07879ee8ecfc10435beb8d13 100644 (file)
--- 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 e647c053bd553b75934cc833aca375dc49f279b3..d2d39ba4fa1d6b9e2b26ff4ee2ffd89b8adb930a 100644 (file)
--- 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 24562ac89da7d276368c712191aa2f4967649bb7..07cd608400a2fb83c298ab2486cdbdfadc18be69 100644 (file)
--- 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 bd6f762586d6c79f07c8d4523948491ba8b96e8e..3dfc4faab5700647bb8ba0ecee6113fab6ebde2d 100644 (file)
--- 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();