upd: convert to PostgreSQL

diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java
index 8e20f2a712004a4f41e2bcb169cde9a469889636..9a8b10eaebbcb926ee3a0659a3ef6fabc80b12b7 100644 (file)
--- a/src/org/cacert/gigi/database/DatabaseConnection.java
+++ b/src/org/cacert/gigi/database/DatabaseConnection.java
@@ -4,12 +4,14 @@ import java.io.IOException;
 import java.io.InputStream;
 import java.sql.Connection;
 import java.sql.DriverManager;
-import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.HashMap;
 import java.util.Properties;
+import java.util.StringJoiner;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
 
 import org.cacert.gigi.database.SQLFileManager.ImportType;
 
@@ -39,15 +41,8 @@ public class DatabaseConnection {
 
     private void tryConnect() {
         try {
-            c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?zeroDateTimeBehavior=convertToNull", credentials.getProperty("sql.user"), credentials.getProperty("sql.password"));
-            PreparedStatement ps = c.prepareStatement("SET SESSION wait_timeout=?, time_zone='+0:00';");
-            try {
-                ps.setInt(1, CONNECTION_TIMEOUT);
-                ps.execute();
-                adHoc = c.createStatement();
-            } finally {
-                ps.close();
-            }
+            c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?socketTimeout=" + CONNECTION_TIMEOUT, credentials.getProperty("sql.user"), credentials.getProperty("sql.password"));
+            adHoc = c.createStatement();
         } catch (SQLException e) {
             e.printStackTrace();
         }
@@ -55,10 +50,26 @@ public class DatabaseConnection {
 
     public GigiPreparedStatement prepare(String query) {
         ensureOpen();
+        query = preprocessQuery(query);
         GigiPreparedStatement statement = statements.get(query);
         if (statement == null) {
             try {
-                statement = new GigiPreparedStatement(c.prepareStatement(query, Statement.RETURN_GENERATED_KEYS));
+                statement = new GigiPreparedStatement(c.prepareStatement(query, query.startsWith("SELECT ") ? Statement.NO_GENERATED_KEYS : Statement.RETURN_GENERATED_KEYS));
+            } catch (SQLException e) {
+                throw new Error(e);
+            }
+            statements.put(query, statement);
+        }
+        return statement;
+    }
+
+    public GigiPreparedStatement prepareScrollable(String query) {
+        ensureOpen();
+        query = preprocessQuery(query);
+        GigiPreparedStatement statement = statements.get(query);
+        if (statement == null) {
+            try {
+                statement = new GigiPreparedStatement(c.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
             } catch (SQLException e) {
                 throw new Error(e);
             }
@@ -105,7 +116,7 @@ public class DatabaseConnection {
             throw new Error("Re-initiaizing is forbidden.");
         }
         credentials = conf;
-        GigiResultSet rs = getInstance().prepare("SELECT version FROM schemeVersion ORDER BY version DESC LIMIT 1").executeQuery();
+        GigiResultSet rs = getInstance().prepare("SELECT version FROM \"schemeVersion\" ORDER BY version DESC LIMIT 1;").executeQuery();
         int version = 0;
         if (rs.next()) {
             version = rs.getInt(1);
@@ -119,6 +130,10 @@ public class DatabaseConnection {
         upgrade(version);
     }
 
+    public void beginTransaction() throws SQLException {
+        c.setAutoCommit(false);
+    }
+
     private static void upgrade(int version) {
         try {
             Statement s = getInstance().c.createStatement();
@@ -146,10 +161,6 @@ public class DatabaseConnection {
         }
     }
 
-    public void beginTransaction() throws SQLException {
-        c.setAutoCommit(false);
-    }
-
     public void commitTransaction() throws SQLException {
         c.commit();
         c.setAutoCommit(true);
@@ -165,4 +176,39 @@ public class DatabaseConnection {
             e.printStackTrace();
         }
     }
+
+    public static final String preprocessQuery(String originalQuery) {
+        originalQuery = originalQuery.replace('`', '"');
+        if (originalQuery.matches("^INSERT INTO [^ ]+ SET .*")) {
+            Pattern p = Pattern.compile("INSERT INTO ([^ ]+) SET (.*)");
+            Matcher m = p.matcher(originalQuery);
+            if (m.matches()) {
+                String replacement = "INSERT INTO " + toIdentifier(m.group(1));
+                String[] parts = m.group(2).split(",");
+                StringJoiner columns = new StringJoiner(", ");
+                StringJoiner values = new StringJoiner(", ");
+                for (int i = 0; i < parts.length; i++) {
+                    String[] split = parts[i].split("=", 2);
+                    columns.add(toIdentifier(split[0]));
+                    values.add(split[1]);
+                }
+                replacement += "(" + columns.toString() + ") VALUES(" + values.toString() + ")";
+                return replacement;
+            }
+        }
+
+        //
+        return originalQuery;
+    }
+
+    private static CharSequence toIdentifier(String ident) {
+        ident = ident.trim();
+        if ( !ident.startsWith("\"")) {
+            ident = "\"" + ident;
+        }
+        if ( !ident.endsWith("\"")) {
+            ident = ident + "\"";
+        }
+        return ident;
+    }
 }

diff --git a/src/org/cacert/gigi/database/SQLFileManager.java b/src/org/cacert/gigi/database/SQLFileManager.java
index 9aaebfae883c5ead9ff40d14a29846dac7f10095..400884af484d15f3d04310b883ae996d693b3a36 100644 (file)
--- a/src/org/cacert/gigi/database/SQLFileManager.java
+++ b/src/org/cacert/gigi/database/SQLFileManager.java
@@ -27,9 +27,10 @@ public class SQLFileManager {
 
     public static void addFile(Statement stmt, InputStream f, ImportType type) throws IOException, SQLException {
         String sql = readFile(f);
-        sql = sql.replaceAll("--[^\n]+\n", "\n");
+        sql = sql.replaceAll("--[^\n]*\n", "\n");
+        sql = sql.replaceAll("#[^\n]*\n", "\n");
         String[] stmts = sql.split(";");
-        Pattern p = Pattern.compile("\\s*DROP TABLE IF EXISTS `([^`]+)`");
+        Pattern p = Pattern.compile("\\s*DROP TABLE IF EXISTS \"([^\"]+)\"");
         for (String string : stmts) {
             Matcher m = p.matcher(string);
             string = string.trim();
@@ -39,8 +40,9 @@ public class SQLFileManager {
             if ((string.contains("profiles") || string.contains("cacerts")) && type != ImportType.PRODUCTION) {
                 continue;
             }
+            string = DatabaseConnection.preprocessQuery(string);
             if (m.matches() && type == ImportType.TRUNCATE) {
-                String sql2 = "TRUNCATE `" + m.group(1) + "`";
+                String sql2 = "DELETE FROM \"" + m.group(1) + "\"";
                 stmt.addBatch(sql2);
                 continue;
             }

diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql
index 98133297ca4462a25e3cd75ab7929e709a4c3466..f451ede2aea269704b53ecb0af8f1a97a0f82562 100644 (file)
--- a/src/org/cacert/gigi/database/tableStructure.sql
+++ b/src/org/cacert/gigi/database/tableStructure.sql
@@ -1,332 +1,376 @@
-DROP TABLE IF EXISTS `certOwners`;
-CREATE TABLE `certOwners` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `modified` timestamp NULL DEFAULT NULL,
-  `deleted` timestamp NULL DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `users`;
-CREATE TABLE `users` (
-  `id` int(11) NOT NULL,
-  `email` varchar(255) NOT NULL DEFAULT '',
-  `password` varchar(255) NOT NULL DEFAULT '',
-  `fname` varchar(255) NOT NULL DEFAULT '',
-  `mname` varchar(255) NOT NULL DEFAULT '',
-  `lname` varchar(255) NOT NULL DEFAULT '',
-  `suffix` varchar(50) NOT NULL DEFAULT '',
-  `dob` date NOT NULL DEFAULT '0000-00-00',
-  `verified` int(1) NOT NULL DEFAULT '0',
-  `ccid` int(3) NOT NULL DEFAULT '0',
-  `regid` int(5) NOT NULL DEFAULT '0',
-  `locid` int(7) NOT NULL DEFAULT '0',
-  `listme` int(1) NOT NULL DEFAULT '0',
-  `contactinfo` varchar(255) NOT NULL DEFAULT '',
-  `language` varchar(5) NOT NULL DEFAULT '',
-  PRIMARY KEY (`id`),
-  KEY `ccid` (`ccid`),
-  KEY `regid` (`regid`),
-  KEY `locid` (`locid`),
-  KEY `email` (`email`),
-  KEY `stats_users_verified` (`verified`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `organisations`;
-CREATE TABLE IF NOT EXISTS `organisations` (
-  `id` int(11) NOT NULL,
-  `name` varchar(100) NOT NULL,
-  `state` varchar(2) NOT NULL,
-  `province` varchar(100) NOT NULL,
-  `city` varchar(100) NOT NULL,
-  `contactEmail` varchar(100) NOT NULL,
-  `creator` int(11) NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `domains`;
-CREATE TABLE `domains` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `memid` int(11) NOT NULL,
-  `domain` varchar(255) NOT NULL,
-  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `modified` datetime NULL DEFAULT NULL,
-  `deleted` datetime NULL DEFAULT NULL,
-  PRIMARY KEY (`id`),
-  KEY `memid` (`memid`),
-  KEY `domain` (`domain`),
-  KEY `stats_domains_deleted` (`deleted`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `emails`;
-CREATE TABLE `emails` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `memid` int(11) NOT NULL DEFAULT '0',
-  `email` varchar(255) NOT NULL DEFAULT '',
-  `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`),
-  KEY `memid` (`memid`),
-  KEY `stats_email_hash` (`hash`),
-  KEY `stats_email_deleted` (`deleted`),
-  KEY `email` (`email`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `emailPinglog`;
-CREATE TABLE `emailPinglog` (
-  `when` datetime NOT NULL,
-  `uid` int(11) NOT NULL,
-  `email` varchar(255) NOT NULL,
-  `type` enum('fast', 'active') NOT NULL,
-  `status` enum('open', 'success', 'failed') NOT NULL,
-  `result` varchar(255) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `pingconfig`;
-CREATE TABLE `pingconfig` (
-  `id` int(13) NOT NULL AUTO_INCREMENT,
-  `domainid` int(11) NOT NULL,
-  `type` enum('email', 'ssl', 'http', 'dns') NOT NULL,
-  `info` varchar(255) NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `domainPinglog`;
-CREATE TABLE `domainPinglog` (
-  `when` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `configId` int(13) NOT NULL,
-  `state` enum('open', 'success', 'failed') NOT NULL,
-  `challenge` varchar(16),
-  `result` varchar(255)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `baddomains`;
-CREATE TABLE `baddomains` (
-  `domain` varchar(255) NOT NULL DEFAULT ''
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `alerts`;
-CREATE TABLE `alerts` (
-  `memid` int(11) NOT NULL DEFAULT '0',
-  `general` tinyint(1) NOT NULL DEFAULT '0',
-  `country` tinyint(1) NOT NULL DEFAULT '0',
-  `regional` tinyint(1) NOT NULL DEFAULT '0',
-  `radius` tinyint(1) NOT NULL DEFAULT '0',
-  PRIMARY KEY (`memid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `user_agreements`;
-CREATE TABLE `user_agreements` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `memid` int(11) NOT NULL,
-  `secmemid` int(11) DEFAULT NULL,
-  `document` varchar(50) DEFAULT NULL,
-  `date` datetime DEFAULT NULL,
-  `active` int(1) NOT NULL,
-  `method` varchar(100) NOT NULL,
-  `comment` varchar(100) DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `certs`;
-CREATE TABLE `certs` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `memid` int(11) NOT NULL DEFAULT '0',
-  `serial` varchar(50) NOT NULL DEFAULT '',
-  `keytype` char(2) NOT NULL DEFAULT 'NS',
-  `codesign` tinyint(1) NOT NULL DEFAULT '0',
-  `md` enum('md5','sha1','sha256','sha512') NOT NULL DEFAULT 'sha512',
-  `profile` int(3) NOT NULL,
-  `caid` int(3) NULL DEFAULT NULL,
-
-  `csr_name` varchar(255) NOT NULL DEFAULT '',
-  `csr_type` enum('CSR', 'SPKAC') NOT NULL,
-  `crt_name` varchar(255) NOT NULL DEFAULT '',
-  `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,
-  `certhash` char(40) DEFAULT NULL,
-  `description` varchar(100) NOT NULL DEFAULT '',
-  PRIMARY KEY (`id`),
-  KEY `emailcerts_pkhash` (`pkhash`),
-  KEY `revoked` (`revoked`),
-  KEY `created` (`created`),
-  KEY `memid` (`memid`),
-  KEY `serial` (`serial`),
-  KEY `stats_emailcerts_expire` (`expire`),
-  KEY `emailcrt` (`crt_name`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `certAvas`;
-CREATE TABLE `certAvas` (
-  `certid` int(11) NOT NULL,
-  `name` varchar(20) NOT NULL,
-  `value` varchar(255) NOT NULL,
-
-  PRIMARY KEY (`certid`, `name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `clientcerts`;
-CREATE TABLE `clientcerts` (
-  `id` int(11) NOT NULL,
-  `disablelogin` int(1) NOT NULL DEFAULT '0',
-
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `profiles`;
-CREATE TABLE `profiles` (
-  `id` int(3) NOT NULL AUTO_INCREMENT,
-  `keyname` varchar(60) NOT NULL,
-  `include` varchar(200) NOT NULL,
-  `requires` varchar(200) NOT NULL,
-  `name` varchar(100) NOT NULL,
-  PRIMARY KEY (`id`),
-  UNIQUE (`keyname`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `subjectAlternativeNames`;
-CREATE TABLE `subjectAlternativeNames` (
-  `certId` int(11) NOT NULL,
-  `contents` varchar(50) NOT NULL,
-  `type` enum('email','DNS') NOT NULL
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `cacerts`;
-CREATE TABLE `cacerts` (
-  `id` int(3) NOT NULL AUTO_INCREMENT,
-  `keyname` varchar(60) NOT NULL,
-  `link` varchar(160) NOT NULL,
-  `parentRoot` int(3) NOT NULL,
-  `validFrom` datetime NULL DEFAULT NULL,
-  `validTo` datetime NULL DEFAULT NULL,
-  PRIMARY KEY (`id`),
-  UNIQUE (`keyname`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `jobs`;
-CREATE TABLE `jobs` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `targetId` int(11) NOT NULL,
-  `task` enum('sign','revoke') NOT NULL,
-  `state` enum('open', 'done', 'error') NOT NULL DEFAULT 'open',
-  `warning` int(2) NOT NULL DEFAULT '0',
-  `executeFrom` DATE,
-  `executeTo` VARCHAR(11),
-  PRIMARY KEY (`id`),
-  KEY `state` (`state`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `notary`;
-CREATE TABLE `notary` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `from` int(11) NOT NULL DEFAULT '0',
-  `to` int(11) NOT NULL DEFAULT '0',
+DROP TABLE IF EXISTS "certOwners";
+CREATE TABLE "certOwners" (
+  "id" serial NOT NULL,
+  "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "modified" timestamp NULL DEFAULT NULL,
+  "deleted" timestamp NULL DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+
+DROP TABLE IF EXISTS "users";
+CREATE TABLE "users" (
+  "id" int NOT NULL,
+  "email" varchar(255) NOT NULL DEFAULT '',
+  "password" varchar(255) NOT NULL DEFAULT '',
+  "fname" varchar(255) NOT NULL DEFAULT '',
+  "mname" varchar(255) NOT NULL DEFAULT '',
+  "lname" varchar(255) NOT NULL DEFAULT '',
+  "suffix" varchar(50) NOT NULL DEFAULT '',
+  "dob" date NOT NULL,
+  "verified" boolean NOT NULL DEFAULT 'false',
+  "ccid" int NOT NULL DEFAULT '0',
+  "regid" int NOT NULL DEFAULT '0',
+  "locid" int NOT NULL DEFAULT '0',
+  "listme" boolean NOT NULL DEFAULT 'false',
+  "contactinfo" varchar(255) NOT NULL DEFAULT '',
+  "language" varchar(5) NOT NULL DEFAULT '',
+  PRIMARY KEY ("id")
+);
+CREATE INDEX ON "users" ("ccid");
+CREATE INDEX ON "users" ("regid");
+CREATE INDEX ON "users" ("locid");
+CREATE INDEX ON "users" ("email");
+CREATE INDEX ON "users" ("verified");
+
+
+DROP TABLE IF EXISTS "organisations";
+CREATE TABLE IF NOT EXISTS "organisations" (
+  "id" int NOT NULL,
+  "name" varchar(100) NOT NULL,
+  "state" varchar(2) NOT NULL,
+  "province" varchar(100) NOT NULL,
+  "city" varchar(100) NOT NULL,
+  "contactEmail" varchar(100) NOT NULL,
+  "creator" int NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+DROP TABLE IF EXISTS "domains";
+CREATE TABLE "domains" (
+  "id" serial NOT NULL,
+  "memid" int NOT NULL,
+  "domain" varchar(255) NOT NULL,
+  "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "modified" timestamp NULL DEFAULT NULL,
+  "deleted" timestamp NULL DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX ON "domains" ("memid");
+CREATE INDEX ON "domains" ("domain");
+CREATE INDEX ON "domains" ("deleted");
+
+DROP TABLE IF EXISTS "emails";
+CREATE TABLE "emails" (
+  "id" serial NOT NULL,
+  "memid" int NOT NULL DEFAULT '0',
+  "email" varchar(255) NOT NULL DEFAULT '',
+  "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "modified" timestamp NULL DEFAULT NULL,
+  "deleted" timestamp NULL DEFAULT NULL,
+  "hash" varchar(50) NOT NULL DEFAULT '',
+  "attempts" smallint NOT NULL DEFAULT '0',
+  PRIMARY KEY ("id")
+);
+CREATE INDEX ON "emails" ("memid");
+CREATE INDEX ON "emails" ("hash");
+CREATE INDEX ON "emails" ("deleted");
+CREATE INDEX ON "emails" ("email");
+
+DROP TABLE IF EXISTS "emailPinglog";
+DROP TABLE IF EXISTS "domainPinglog";
+
+DROP TYPE IF EXISTS "emailPingType";
+CREATE TYPE "emailPingType" AS ENUM ('fast', 'active');
+DROP TYPE IF EXISTS "pingState";
+CREATE TYPE "pingState" AS ENUM ('open', 'success', 'failed');
+
+CREATE TABLE "emailPinglog" (
+  "when" timestamp NOT NULL,
+  "uid" int NOT NULL,
+  "email" varchar(255) NOT NULL,
+  "type" "emailPingType" NOT NULL,
+  "status" "pingState" NOT NULL,
+  "result" varchar(255) NOT NULL
+);
+
+DROP TABLE IF EXISTS "pingconfig";
+
+DROP TYPE IF EXISTS "pingType";
+CREATE TYPE "pingType" AS ENUM ('email', 'ssl', 'http', 'dns');
+
+CREATE TABLE "pingconfig" (
+  "id" serial NOT NULL,
+  "domainid" int NOT NULL,
+  "type" "pingType" NOT NULL,
+  "info" varchar(255) NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+
+CREATE TABLE "domainPinglog" (
+  "when" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "configId" int NOT NULL,
+  "state" "pingState" NOT NULL,
+  "challenge" varchar(16),
+  "result" varchar(255)
+);
+
+DROP TABLE IF EXISTS "baddomains";
+CREATE TABLE "baddomains" (
+  "domain" varchar(255) NOT NULL DEFAULT ''
+);
+
+
+DROP TABLE IF EXISTS "alerts";
+CREATE TABLE "alerts" (
+  "memid" int NOT NULL DEFAULT '0',
+  "general" boolean NOT NULL DEFAULT 'false',
+  "country" boolean NOT NULL DEFAULT 'false',
+  "regional" boolean NOT NULL DEFAULT 'false',
+  "radius" boolean NOT NULL DEFAULT 'false',
+  PRIMARY KEY ("memid")
+);
+
+DROP TABLE IF EXISTS "user_agreements";
+CREATE TABLE "user_agreements" (
+  "id" serial NOT NULL,
+  "memid" int NOT NULL,
+  "secmemid" int DEFAULT NULL,
+  "document" varchar(50) DEFAULT NULL,
+  "date" timestamp DEFAULT NULL,
+  "active" boolean NOT NULL,
+  "method" varchar(100) NOT NULL,
+  "comment" varchar(100) DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+
+DROP TABLE IF EXISTS "certs";
+
+DROP TYPE IF EXISTS "mdType";
+CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
+
+DROP TYPE IF EXISTS "csrType";
+CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
+
+CREATE TABLE "certs" (
+  "id" serial NOT NULL,
+  "memid" int NOT NULL DEFAULT '0',
+  "serial" varchar(50) NOT NULL DEFAULT '',
+  "keytype" char(2) NOT NULL DEFAULT 'NS',
+  "codesign" boolean NOT NULL DEFAULT 'false',
+  "md" "mdType" NOT NULL DEFAULT 'sha512',
+  "profile" int NOT NULL,
+  "caid" int NULL DEFAULT NULL,
+
+  "csr_name" varchar(255) NOT NULL DEFAULT '',
+  "csr_type" "csrType" NOT NULL,
+  "crt_name" varchar(255) NOT NULL DEFAULT '',
+  "created" timestamp NULL DEFAULT NULL,
+  "modified" timestamp NULL DEFAULT NULL,
+  "revoked" timestamp NULL DEFAULT NULL,
+  "expire" timestamp NULL DEFAULT NULL,
+  "renewed" boolean NOT NULL DEFAULT 'false',
+  "disablelogin" boolean NOT NULL DEFAULT 'false',
+  "pkhash" char(40) DEFAULT NULL,
+  "certhash" char(40) DEFAULT NULL,
+  "description" varchar(100) NOT NULL DEFAULT '',
+  PRIMARY KEY ("id")
+);
+CREATE INDEX ON "certs" ("pkhash");
+CREATE INDEX ON "certs" ("revoked");
+CREATE INDEX ON "certs" ("created");
+CREATE INDEX ON "certs" ("memid");
+CREATE INDEX ON "certs" ("serial");
+CREATE INDEX ON "certs" ("expire");
+CREATE INDEX ON "certs" ("crt_name");
+
+
+
+DROP TABLE IF EXISTS "certAvas";
+CREATE TABLE "certAvas" (
+  "certid" int NOT NULL,
+  "name" varchar(20) NOT NULL,
+  "value" varchar(255) NOT NULL,
+
+  PRIMARY KEY ("certid", "name")
+);
+
+DROP TABLE IF EXISTS "clientcerts";
+CREATE TABLE "clientcerts" (
+  "id" int NOT NULL,
+  "disablelogin" boolean NOT NULL DEFAULT 'false',
+
+  PRIMARY KEY ("id")
+);
+
+DROP TABLE IF EXISTS "profiles";
+CREATE TABLE "profiles" (
+  "id" serial NOT NULL,
+  "keyname" varchar(60) NOT NULL,
+  "include" varchar(200) NOT NULL,
+  "requires" varchar(200) NOT NULL,
+  "name" varchar(100) NOT NULL,
+  PRIMARY KEY ("id"),
+  UNIQUE ("keyname")
+);
+
+DROP TABLE IF EXISTS "subjectAlternativeNames";
+
+DROP TYPE IF EXISTS "SANType";
+CREATE TYPE "SANType" AS ENUM ('email', 'DNS');
+
+CREATE TABLE "subjectAlternativeNames" (
+  "certId" int NOT NULL,
+  "contents" varchar(50) NOT NULL,
+  "type" "SANType" NOT NULL
+);
+
+DROP TABLE IF EXISTS "cacerts";
+CREATE TABLE "cacerts" (
+  "id" serial NOT NULL,
+  "keyname" varchar(60) NOT NULL,
+  "link" varchar(160) NOT NULL,
+  "parentRoot" int NOT NULL,
+  "validFrom" timestamp NULL DEFAULT NULL,
+  "validTo" timestamp NULL DEFAULT NULL,
+  PRIMARY KEY ("id"),
+  UNIQUE ("keyname")
+);
+
+DROP TABLE IF EXISTS "jobs";
+
+DROP TYPE IF EXISTS "jobType";
+CREATE TYPE "jobType" AS ENUM ('sign', 'revoke');
+DROP TYPE IF EXISTS "jobState";
+CREATE TYPE "jobState" AS ENUM ('open', 'done', 'error');
+
+
+CREATE TABLE "jobs" (
+  "id" serial NOT NULL,
+  "targetId" int NOT NULL,
+  "task" "jobType" NOT NULL,
+  "state" "jobState" NOT NULL DEFAULT 'open',
+  "warning" smallint NOT NULL DEFAULT '0',
+  "executeFrom" DATE,
+  "executeTo" VARCHAR(11),
+  PRIMARY KEY ("id")
+);
+
+CREATE INDEX ON "jobs" ("state");
+
+DROP TABLE IF EXISTS "notary";
+
+DROP TYPE IF EXISTS "notaryType";
+CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted');
+
+CREATE TABLE "notary" (
+  "id" serial NOT NULL,
+  "from" int NOT NULL DEFAULT '0',
+  "to" int NOT NULL DEFAULT '0',
 # total points that have been entered
-  `points` int(3) NOT NULL DEFAULT '0',
+  "points" int NOT NULL DEFAULT '0',
 # awarded and the "experience points" are calculated virtually
 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
 # TTP is default ttp assurance
-  `method` enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted') NOT NULL DEFAULT 'Face to Face Meeting',
-  `location` varchar(255) NOT NULL DEFAULT '',
-  `date` varchar(255) NOT NULL DEFAULT '',
+  "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
+  "location" varchar(255) NOT NULL DEFAULT '',
+  "date" varchar(255) NOT NULL DEFAULT '',
 # date when assurance was entered
-  `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 #?
-  `expire` datetime NULL DEFAULT NULL,
+  "expire" timestamp NULL DEFAULT NULL,
 #?????????????????
-  `sponsor` int(11) NOT NULL DEFAULT '0',
+  "sponsor" int NOT NULL DEFAULT '0',
 # date when assurance was deleted (or 0)
-  `deleted` datetime NULL DEFAULT NULL,
-  PRIMARY KEY (`id`),
-  KEY `from` (`from`),
-  KEY `to` (`to`),
-  KEY `stats_notary_when` (`when`),
-  KEY `stats_notary_method` (`method`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-
-DROP TABLE IF EXISTS `cats_passed`;
-CREATE TABLE `cats_passed` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `user_id` int(11) NOT NULL,
-  `variant_id` int(11) NOT NULL,
-  `pass_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-  PRIMARY KEY (`id`),
-  UNIQUE KEY `test_passed` (`user_id`,`variant_id`,`pass_date`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+  "deleted" timestamp NULL DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+
+CREATE INDEX ON "notary"("from");
+CREATE INDEX ON "notary"("to");
+CREATE INDEX ON "notary"("when");
+CREATE INDEX ON "notary"("method");
+
+
+DROP TABLE IF EXISTS "cats_passed";
+CREATE TABLE "cats_passed" (
+  "id" serial NOT NULL,
+  "user_id" int NOT NULL,
+  "variant_id" int NOT NULL,
+  "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY ("id"),
+  UNIQUE ("user_id","variant_id","pass_date")
+);
 
 # --------------------------------------------------------
 
 #
-# Table structure for table `cats_type`
+# Table structure for table "cats_type"
 #
 
-DROP TABLE IF EXISTS `cats_type`;
-CREATE TABLE `cats_type` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `type_text` varchar(255) NOT NULL,
-  PRIMARY KEY (`id`),
-  UNIQUE KEY `type_text` (`type_text`)
-) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `arbitrations`;
-CREATE TABLE IF NOT EXISTS `arbitrations` (
-  `user` int(11) NOT NULL,
-  `arbitration` varchar(20) NOT NULL,
-  PRIMARY KEY (`user`,`arbitration`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `user_groups`;
-CREATE TABLE IF NOT EXISTS `user_groups` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `user` int(11) NOT NULL,
-  `permission` enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer') NOT NULL,
-  `granted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `deleted` timestamp NULL DEFAULT NULL,
-  `grantedby` int(11) NOT NULL,
-  `revokedby` int(11) DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `org_admin`;
-CREATE TABLE IF NOT EXISTS `org_admin` (
-  `orgid` int(11) NOT NULL,
-  `memid` int(11) NOT NULL,
-  `master` enum('y', 'n') NOT NULL,
-  `creator` int(11) NOT NULL,
-  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `deleter` int(11) NULL DEFAULT NULL,
-  `deleted` timestamp NULL DEFAULT NULL,
-  KEY (`orgid`, `memid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `adminLog`;
-CREATE TABLE `adminLog` (
-  `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `uid` int(11) unsigned NOT NULL,
-  `admin` int(11) unsigned NOT NULL,
-  `type` varchar(100) NOT NULL DEFAULT '',
-  `information` varchar(50) NOT NULL DEFAULT '',
-  KEY (`when`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
-DROP TABLE IF EXISTS `schemeVersion`;
-CREATE TABLE `schemeVersion` (
-  `version` int(5) NOT NULL,
-  PRIMARY KEY (`version`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-INSERT INTO schemeVersion(version)  VALUES(4);
+DROP TABLE IF EXISTS "cats_type";
+CREATE TABLE "cats_type" (
+  "id" serial NOT NULL,
+  "type_text" varchar(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  UNIQUE ("type_text")
+);
+
+DROP TABLE IF EXISTS "arbitrations";
+CREATE TABLE IF NOT EXISTS "arbitrations" (
+  "user" int NOT NULL,
+  "arbitration" varchar(20) NOT NULL,
+  PRIMARY KEY ("user","arbitration")
+);
+
+DROP TABLE IF EXISTS "user_groups";
+
+DROP TYPE IF EXISTS "userGroup";
+CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer');
+
+CREATE TABLE IF NOT EXISTS "user_groups" (
+  "id" serial NOT NULL,
+  "user" int NOT NULL,
+  "permission" "userGroup" NOT NULL,
+  "granted" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "deleted" timestamp NULL DEFAULT NULL,
+  "grantedby" int NOT NULL,
+  "revokedby" int DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+
+DROP TABLE IF EXISTS "org_admin";
+
+DROP TYPE IF EXISTS "yesno";
+CREATE TYPE "yesno" AS enum('y', 'n');
+
+
+CREATE TABLE IF NOT EXISTS "org_admin" (
+  "orgid" int NOT NULL,
+  "memid" int NOT NULL,
+  "master" "yesno" NOT NULL,
+  "creator" int NOT NULL,
+  "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "deleter" int NULL DEFAULT NULL,
+  "deleted" timestamp NULL DEFAULT NULL
+);
+CREATE INDEX ON "org_admin"("orgid", "memid");
+
+
+DROP TABLE IF EXISTS "adminLog";
+CREATE TABLE "adminLog" (
+  "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "uid" int NOT NULL,
+  "admin" int NOT NULL,
+  "type" varchar(100) NOT NULL DEFAULT '',
+  "information" varchar(50) NOT NULL DEFAULT ''
+);
+CREATE INDEX ON "adminLog"("when");
+
+
+DROP TABLE IF EXISTS "schemeVersion";
+CREATE TABLE "schemeVersion" (
+  "version" smallint NOT NULL,
+  PRIMARY KEY ("version")
+);
+INSERT INTO "schemeVersion" (version)  VALUES(4);

diff --git a/src/org/cacert/gigi/dbObjects/CACertificate.java b/src/org/cacert/gigi/dbObjects/CACertificate.java
index b6ede830e902887f8d58dcb34ba44fbebdb73e76..bdfc6531f564b4479d85c6e366dc0032b335100e 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/CACertificate.java
+++ b/src/org/cacert/gigi/dbObjects/CACertificate.java
@@ -31,7 +31,7 @@ public class CACertificate implements IdCachable {
 
     private CACertificate(int id) {
         this.id = id;
-        GigiPreparedStatement conn = DatabaseConnection.getInstance().prepare("SELECT keyname, parentRoot, link FROM cacerts WHERE id = ?");
+        GigiPreparedStatement conn = DatabaseConnection.getInstance().prepare("SELECT `keyname`, `parentRoot`, `link` FROM `cacerts` WHERE `id`=?");
         conn.setInt(1, id);
         GigiResultSet res = conn.executeQuery();
         if ( !res.next()) {
@@ -109,7 +109,7 @@ public class CACertificate implements IdCachable {
 
                 X500Principal subj = toInsert.getSubjectX500Principal();
                 boolean self = toInsert.getIssuerX500Principal().equals(subj);
-                GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("SELECT id, parentRoot FROM cacerts WHERE keyname=?");
+                GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("SELECT `id`, `parentRoot` FROM `cacerts` WHERE `keyname`=?");
                 q.setString(1, names.get(subj));
                 GigiResultSet res = q.executeQuery();
                 int id;
@@ -128,14 +128,14 @@ public class CACertificate implements IdCachable {
                         link = "http://g2.crt.cacert.org/g2/" + parts[1] + "/" + parts[0] + "-" + parts[2] + ".crt";
 
                     }
-                    GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("INSERT INTO cacerts SET parentRoot=?, keyname=?, link=?");
+                    GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("INSERT INTO `cacerts` SET `parentRoot`=?, `keyname`=?, `link`=?");
                     q2.setInt(1, self ? 0 : inserted.get(toInsert.getIssuerX500Principal()));
                     q2.setString(2, keyname);
                     q2.setString(3, link);
                     q2.execute();
                     id = q2.lastInsertId();
                     if (self) {
-                        GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("UPDATE cacerts SET parentRoot=?, id=?");
+                        GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("UPDATE `cacerts` SET `parentRoot`=?, `id`=?");
                         q3.setInt(1, id);
                         q3.setInt(2, id);
                         q3.execute();

diff --git a/src/org/cacert/gigi/dbObjects/Certificate.java b/src/org/cacert/gigi/dbObjects/Certificate.java
index 85ba0346e82e5b3494308b5fdd9f51ff0d4bc14d..fc92b309870c171eafb34e0071eb1d5edc2a542d 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/Certificate.java
+++ b/src/org/cacert/gigi/dbObjects/Certificate.java
@@ -165,7 +165,7 @@ public class Certificate {
         profile = CertificateProfile.getById(rs.getInt("profile"));
         this.serial = rs.getString("serial");
 
-        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("SELECT contents, type FROM `subjectAlternativeNames` WHERE certId=?");
+        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("SELECT `contents`, `type` FROM `subjectAlternativeNames` WHERE `certId`=?");
         ps2.setInt(1, id);
         GigiResultSet rs2 = ps2.executeQuery();
         sans = new LinkedList<>();
@@ -247,15 +247,15 @@ public class Certificate {
         }
         Notary.writeUserAgreement(owner, "CCA", "issue certificate", "", true, 0);
 
-        GigiPreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO certs SET md=?, csr_type=?, crt_name='', memid=?, profile=?");
-        inserter.setString(1, md);
+        GigiPreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, crt_name='', memid=?, profile=?");
+        inserter.setString(1, md.toLowerCase());
         inserter.setString(2, csrType.toString());
         inserter.setInt(3, owner.getId());
         inserter.setInt(4, profile.getId());
         inserter.execute();
         id = inserter.lastInsertId();
 
-        GigiPreparedStatement san = DatabaseConnection.getInstance().prepare("INSERT INTO subjectAlternativeNames SET certId=?, contents=?, type=?");
+        GigiPreparedStatement san = DatabaseConnection.getInstance().prepare("INSERT INTO `subjectAlternativeNames` SET `certId`=?, contents=?, type=?::`SANType`");
         for (SubjectAlternateName subjectAlternateName : sans) {
             san.setInt(1, id);
             san.setString(2, subjectAlternateName.getName());
@@ -263,7 +263,7 @@ public class Certificate {
             san.execute();
         }
 
-        GigiPreparedStatement insertAVA = DatabaseConnection.getInstance().prepare("INSERT certAvas SET certid=?, name=?, value=?");
+        GigiPreparedStatement insertAVA = DatabaseConnection.getInstance().prepare("INSERT INTO `certAvas` SET certid=?, name=?, value=?");
         insertAVA.setInt(1, id);
         for (Entry<String, String> e : dn.entrySet()) {
             insertAVA.setString(2, e.getKey());
@@ -276,7 +276,7 @@ public class Certificate {
             fos.write(csr.getBytes("UTF-8"));
         }
 
-        GigiPreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE certs SET csr_name=? WHERE id=?");
+        GigiPreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE `certs` SET `csr_name`=? WHERE id=?");
         updater.setString(1, csrName);
         updater.setInt(2, id);
         updater.execute();
@@ -359,8 +359,8 @@ public class Certificate {
         }
         // TODO caching?
         try {
-            String concat = "group_concat(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')))";
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT certs.id, " + concat + " as subject, md, csr_name, crt_name,memid, profile, certs.serial FROM `certs` LEFT JOIN certAvas ON certAvas.certid=certs.id WHERE serial=? GROUP BY certs.id");
+            String concat = "string_agg(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')), '')";
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT certs.id, " + concat + " as `subject`, `md`, `csr_name`, `crt_name`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certid`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`");
             ps.setString(1, serial);
             GigiResultSet rs = ps.executeQuery();
             return new Certificate(rs);

diff --git a/src/org/cacert/gigi/dbObjects/CertificateOwner.java b/src/org/cacert/gigi/dbObjects/CertificateOwner.java
index 5be24fc8562ef7615cd4e5863c37040f3a277fa8..9560224e3601283bce748b51033ba29b2365112a 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/CertificateOwner.java
+++ b/src/org/cacert/gigi/dbObjects/CertificateOwner.java
@@ -25,7 +25,7 @@ public abstract class CertificateOwner implements IdCachable {
     public static synchronized CertificateOwner getById(int id) {
         CertificateOwner u = myCache.get(id);
         if (u == null) {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT *, users.id AS uid, organisations.id AS oid FROM certOwners LEFT JOIN users ON users.id=certOwners.id LEFT JOIN organisations ON organisations.id = certOwners.id WHERE certOwners.id=? AND deleted is null");
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT *, `users`.`id` AS uid, `organisations`.`id` AS oid FROM `certOwners` LEFT JOIN `users` ON `users`.`id`=`certOwners`.`id` LEFT JOIN `organisations` ON `organisations`.`id` = `certOwners`.`id` WHERE `certOwners`.`id`=? AND `deleted` is null");
             ps.setInt(1, id);
             try (GigiResultSet rs = ps.executeQuery()) {
                 if ( !rs.next()) {
@@ -48,7 +48,7 @@ public abstract class CertificateOwner implements IdCachable {
             if (id != 0) {
                 throw new Error("refusing to insert");
             }
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO certOwners() VALUES()");
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `certOwners` DEFAULT VALUES");
             ps.execute();
             id = ps.lastInsertId();
             myCache.put(this);
@@ -58,7 +58,7 @@ public abstract class CertificateOwner implements IdCachable {
     }
 
     public Domain[] getDomains() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM domains WHERE memid=? AND deleted IS NULL");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `domains` WHERE `memid`=? AND `deleted` IS NULL");
         ps.setInt(1, getId());
 
         try (GigiResultSet rs = ps.executeQuery()) {
@@ -106,7 +106,7 @@ public abstract class CertificateOwner implements IdCachable {
     public abstract boolean isValidEmail(String email);
 
     public void delete() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE certOwners SET deleted=NOW() WHERE id=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `certOwners` SET `deleted`=NOW() WHERE `id`=?");
         ps.setInt(1, getId());
         ps.execute();
         myCache.remove(this);

diff --git a/src/org/cacert/gigi/dbObjects/CertificateProfile.java b/src/org/cacert/gigi/dbObjects/CertificateProfile.java
index 5ac1f761ceb8da69ac45757907a7f9ccd56d750f..3a04090310f889f4c85a325f4d4b4217c5b6996c 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/CertificateProfile.java
+++ b/src/org/cacert/gigi/dbObjects/CertificateProfile.java
@@ -188,7 +188,7 @@ public class CertificateProfile implements IdCachable {
             }
 
             String[] parts = f.getName().split("\\.")[0].split("-", 2);
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT keyname, include, requires, name FROM `profiles` WHERE id=?");
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `keyname`, `include`, `requires`, `name` FROM `profiles` WHERE `id`=?");
             ps.setInt(1, Integer.parseInt(parts[0]));
             GigiResultSet rs = ps.executeQuery();
 
@@ -203,7 +203,7 @@ public class CertificateProfile implements IdCachable {
                     throw new Error("Config error. Certificate Profile mismatch");
                 }
             } else {
-                GigiPreparedStatement insert = DatabaseConnection.getInstance().prepare("INSERT INTO `profiles` SET keyname=?, include=?, requires=?, name=?, id=?");
+                GigiPreparedStatement insert = DatabaseConnection.getInstance().prepare("INSERT INTO `profiles` SET `keyname`=?, `include`=?, `requires`=?, `name`=?, `id`=?");
                 insert.setString(1, parts[1]);
                 insert.setString(2, p.getProperty("include"));
                 insert.setString(3, p.getProperty("requires", ""));
@@ -213,7 +213,7 @@ public class CertificateProfile implements IdCachable {
             }
         }
 
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id, keyname, name, requires, include FROM `profiles`");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `keyname`, `name`, `requires`, `include` FROM `profiles`");
         GigiResultSet rs = ps.executeQuery();
         while (rs.next()) {
             CertificateProfile cp = new CertificateProfile(rs.getInt("id"), rs.getString("keyName"), rs.getString("name"), rs.getString("requires"), rs.getString("include"));

diff --git a/src/org/cacert/gigi/dbObjects/Domain.java b/src/org/cacert/gigi/dbObjects/Domain.java
index 7e35225f4d99d409cee53e9aec64544c8563df9a..283efdae6557441f5bc748334d5f14529c1e81bd 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/Domain.java
+++ b/src/org/cacert/gigi/dbObjects/Domain.java
@@ -81,7 +81,7 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     private Domain(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, domain FROM `domains` WHERE id=? AND deleted IS NULL");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid`, `domain` FROM `domains` WHERE `id`=? AND `deleted` IS NULL");
         ps.setInt(1, id);
 
         GigiResultSet rs = ps.executeQuery();
@@ -156,7 +156,7 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     private static void checkInsert(String suffix) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `domains` WHERE (domain=? OR (CONCAT('.', domain)=RIGHT(?,LENGTH(domain)+1)  OR RIGHT(domain,LENGTH(?)+1)=CONCAT('.',?))) AND deleted IS NULL");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `domains` WHERE (`domain`=? OR (CONCAT('.', `domain`)=RIGHT(?,LENGTH(`domain`)+1)  OR RIGHT(`domain`,LENGTH(?)+1)=CONCAT('.',?))) AND `deleted` IS NULL");
         ps.setString(1, suffix);
         ps.setString(2, suffix);
         ps.setString(3, suffix);
@@ -188,7 +188,7 @@ public class Domain implements IdCachable, Verifyable {
         if (id == 0) {
             throw new GigiApiException("not inserted.");
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domains` SET deleted=CURRENT_TIMESTAMP WHERE id=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domains` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?");
         ps.setInt(1, id);
         ps.execute();
     }
@@ -226,7 +226,7 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     public void addPing(PingType type, String config) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO pingconfig SET domainid=?, type=?, info=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `pingconfig` `SET` `domainid`=?, `type`=?, `info`=?");
         ps.setInt(1, id);
         ps.setString(2, type.toString().toLowerCase());
         ps.setString(3, config);
@@ -235,21 +235,21 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     public synchronized void verify(String hash) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE domainPinglog SET state='success' WHERE challenge=? AND configId IN (SELECT id FROM pingconfig WHERE domainId=?)");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domainPinglog` `SET` `state`='success' WHERE `challenge`=? AND `configId` `IN` (SELECT `id` FROM `pingconfig` WHERE `domainId`=?)");
         ps.setString(1, hash);
         ps.setInt(2, id);
         ps.executeUpdate();
     }
 
     public boolean isVerified() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM domainPinglog INNER JOIN pingconfig ON pingconfig.id=domainPinglog.configId WHERE domainid=? AND state='success'");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `domainPinglog` INNER JOIN `pingconfig` ON `pingconfig`.`id`=`domainPinglog`.`configId` WHERE `domainid`=? AND `state`='success'");
         ps.setInt(1, id);
         GigiResultSet rs = ps.executeQuery();
         return rs.next();
     }
 
     public DomainPingExecution[] getPings() throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT state, type, info, result, configId FROM domainPinglog INNER JOIN pingconfig ON pingconfig.id=domainPinglog.configid WHERE pingconfig.domainid=? ORDER BY `when` DESC;");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `state`, `type`, `info`, `result`, `configId` FROM `domainPinglog` INNER JOIN `pingconfig` ON `pingconfig`.`id`=`domainPinglog`.`configid` WHERE `pingconfig`.`domainid`=? ORDER BY `when` DESC;");
         ps.setInt(1, id);
         GigiResultSet rs = ps.executeQuery();
         rs.last();
@@ -273,10 +273,9 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     public static int searchUserIdByDomain(String domain) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid FROM domains WHERE domain = ?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `domains` WHERE `domain` = ?");
         ps.setString(1, domain);
         GigiResultSet res = ps.executeQuery();
-        res.beforeFirst();
         if (res.next()) {
             return res.getInt(1);
         } else {

diff --git a/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java b/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java
index c6d053af05136d8b22489b116cd76baf881e9fee..47e07dd372e3c34c96aaa7b1aa37177584268ebd 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java
+++ b/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java
@@ -28,7 +28,7 @@ public class DomainPingConfiguration implements IdCachable {
     private String info;
 
     private DomainPingConfiguration(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id, domainid, type, info FROM pingconfig WHERE id=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `domainid`, `type`, `info` FROM `pingconfig` WHERE `id`=?");
         ps.setInt(1, id);
 
         GigiResultSet rs = ps.executeQuery();

diff --git a/src/org/cacert/gigi/dbObjects/EmailAddress.java b/src/org/cacert/gigi/dbObjects/EmailAddress.java
index c499b1a97264207becee6cb1348e73e9292e5dd0..fbd7057ad8d6bb550bae00ba8279ac7cb4063da0 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, Verifyable {
     private String hash = null;
 
     private EmailAddress(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, email, hash FROM `emails` WHERE id=? AND deleted is NULL");
+        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/Group.java b/src/org/cacert/gigi/dbObjects/Group.java
index a40403e8aedadb481c95badd82d356c307c390f5..8a2c1de479755ed0bce9d529dfffd014e93f1295 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/Group.java
+++ b/src/org/cacert/gigi/dbObjects/Group.java
@@ -22,7 +22,7 @@ public enum Group {
     }
 
     public User[] getMembers(int offset, int count) {
-        GigiPreparedStatement gps = DatabaseConnection.getInstance().prepare("SELECT user FROM user_groups WHERE permission=? AND deleted is NULL LIMIT ?,?");
+        GigiPreparedStatement gps = DatabaseConnection.getInstance().prepareScrollable("SELECT `user` FROM `user_groups` WHERE `permission`=?::`userGroup` AND `deleted` IS NULL OFFSET ? LIMIT ?");
         gps.setString(1, dbName);
         gps.setInt(2, offset);
         gps.setInt(3, count);

diff --git a/src/org/cacert/gigi/dbObjects/Organisation.java b/src/org/cacert/gigi/dbObjects/Organisation.java
index 9beb0f57823e65e57d7c683c4aafaa2de69378ac..79c088c81ed6ce24dd80e5fbc159a015704e4abc 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/Organisation.java
+++ b/src/org/cacert/gigi/dbObjects/Organisation.java
@@ -120,14 +120,14 @@ public class Organisation extends CertificateOwner {
         if ( !actor.isInGroup(Group.ORGASSURER) && !isMaster(actor)) {
             throw new GigiApiException("Only org assurer or master-admin may add admins to an organisation.");
         }
-        GigiPreparedStatement ps1 = DatabaseConnection.getInstance().prepare("SELECT 1 FROM org_admin WHERE orgid=? AND memid=? AND deleted is null");
+        GigiPreparedStatement ps1 = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `org_admin` WHERE `orgid`=? AND `memid`=? AND `deleted` IS NULL");
         ps1.setInt(1, getId());
         ps1.setInt(2, admin.getId());
         GigiResultSet result = ps1.executeQuery();
         if (result.next()) {
             return;
         }
-        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("INSERT INTO org_admin SET orgid=?, memid=?, creator=?, master=?");
+        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("INSERT INTO `org_admin` SET `orgid`=?, `memid`=?, `creator`=?, `master`=?::`yesno`");
         ps2.setInt(1, getId());
         ps2.setInt(2, admin.getId());
         ps2.setInt(3, actor.getId());
@@ -147,7 +147,7 @@ public class Organisation extends CertificateOwner {
     }
 
     public List<Affiliation> getAllAdmins() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, master FROM org_admin WHERE orgid=? AND deleted is null");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepareScrollable("SELECT `memid`, `master` FROM `org_admin` WHERE `orgid`=? AND `deleted` IS NULL");
         ps.setInt(1, getId());
         GigiResultSet rs = ps.executeQuery();
         rs.last();
@@ -160,7 +160,7 @@ public class Organisation extends CertificateOwner {
     }
 
     public static Organisation[] getOrganisations(int offset, int count) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT certOwners.id FROM organisations inner join certOwners on certOwners.id=organisations.id where certOwners.deleted is null LIMIT ?,?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepareScrollable("SELECT `certOwners`.`id` FROM `organisations` INNER JOIN `certOwners` ON `certOwners`.`id`=`organisations`.`id` WHERE `certOwners`.`deleted` IS NULL OFFSET ? LIMIT ?");
         ps.setInt(1, offset);
         ps.setInt(2, count);
         GigiResultSet res = ps.executeQuery();
@@ -180,7 +180,7 @@ public class Organisation extends CertificateOwner {
                 cert.revoke();
             }
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE organisations SET name=?, state=?, province=?, city=?, contactEmail=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `organisations` SET `name`=?, `state`=?, `province`=?, `city`=?, `contactEmail`=?");
         ps.setString(1, o);
         ps.setString(2, c);
         ps.setString(3, st);

diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java
index 911d98b75c34d8184b522d85f2daddcd058b7966..69a654878cfe4a1d0797f49f702e9ce8edc85c5d 100644 (file)
--- a/src/org/cacert/gigi/dbObjects/User.java
+++ b/src/org/cacert/gigi/dbObjects/User.java
@@ -52,7 +52,7 @@ public class User extends CertificateOwner {
             locale = Language.getLocaleFromString(localeStr);
         }
 
-        GigiPreparedStatement psg = DatabaseConnection.getInstance().prepare("SELECT permission FROM user_groups WHERE user=? AND deleted is NULL");
+        GigiPreparedStatement psg = DatabaseConnection.getInstance().prepare("SELECT `permission` FROM `user_groups` WHERE `user`=? AND `deleted` is NULL");
         psg.setInt(1, rs.getInt("id"));
 
         try (GigiResultSet rs2 = psg.executeQuery()) {
@@ -86,7 +86,7 @@ public class User extends CertificateOwner {
 
     public void insert(String password) {
         int id = super.insert();
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("insert into `users` set `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?");
+        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("INSERT INTO `users` SET `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?");
         query.setString(1, email);
         query.setString(2, PasswordHash.hash(password));
         query.setString(3, name.getFname());
@@ -100,7 +100,7 @@ public class User extends CertificateOwner {
     }
 
     public void changePassword(String oldPass, String newPass) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `password` FROM users WHERE id=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `password` FROM `users` WHERE `id`=?");
         ps.setInt(1, getId());
         try (GigiResultSet rs = ps.executeQuery()) {
             if ( !rs.next()) {
@@ -251,9 +251,8 @@ public class User extends CertificateOwner {
 
         for (EmailAddress email : getEmails()) {
             if (email.getId() == delMail.getId()) {
-                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE emails SET deleted=? WHERE id=?");
-                ps.setDate(1, new Date(System.currentTimeMillis()));
-                ps.setInt(2, delMail.getId());
+                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `emails` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?");
+                ps.setInt(1, delMail.getId());
                 ps.execute();
                 return;
             }
@@ -263,7 +262,7 @@ public class User extends CertificateOwner {
 
     public synchronized Assurance[] getReceivedAssurances() {
         if (receivedAssurances == null) {
-            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM notary WHERE `to`=? AND deleted IS NULL");
+            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM `notary` WHERE `to`=? AND `deleted` IS NULL");
             query.setInt(1, getId());
 
             try (GigiResultSet res = query.executeQuery()) {
@@ -379,7 +378,7 @@ public class User extends CertificateOwner {
 
     public void grantGroup(User granter, Group toGrant) {
         groups.add(toGrant);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO user_groups SET user=?, permission=?, grantedby=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?");
         ps.setInt(1, getId());
         ps.setString(2, toGrant.getDatabaseName());
         ps.setInt(3, granter.getId());
@@ -388,7 +387,7 @@ public class User extends CertificateOwner {
 
     public void revokeGroup(User revoker, Group toRevoke) {
         groups.remove(toRevoke);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE user_groups SET deleted=CURRENT_TIMESTAMP, revokedby=? WHERE deleted is NULL AND permission=? AND user=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `user_groups` SET `deleted`=CURRENT_TIMESTAMP, `revokedby`=? WHERE `deleted` IS NULL AND `permission`=?::`userGroup` AND `user`=?");
         ps.setInt(1, revoker.getId());
         ps.setString(2, toRevoke.getDatabaseName());
         ps.setInt(3, getId());
@@ -397,7 +396,7 @@ public class User extends CertificateOwner {
 
     public List<Organisation> getOrganisations() {
         List<Organisation> orgas = new ArrayList<>();
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT orgid FROM org_admin WHERE `memid`=? AND deleted is NULL");
+        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT `orgid` FROM `org_admin` WHERE `memid`=? AND `deleted` IS NULL");
         query.setInt(1, getId());
         try (GigiResultSet res = query.executeQuery()) {
             while (res.next()) {
@@ -418,7 +417,7 @@ public class User extends CertificateOwner {
     }
 
     public static User getByEmail(String mail) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT users.id FROM users INNER JOIN certOwners ON certOwners.id = users.id WHERE email=? AND deleted IS NULL");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `email`=? AND `deleted` IS NULL");
         ps.setString(1, mail);
         try (GigiResultSet rs = ps.executeQuery()) {
             if ( !rs.next()) {
@@ -431,7 +430,7 @@ public class User extends CertificateOwner {
 
     public static User[] findByEmail(String mail) {
         LinkedList<User> results = new LinkedList<User>();
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT users.id FROM users INNER JOIN certOwners ON certOwners.id = users.id WHERE users.email LIKE ? AND deleted IS NULL GROUP BY users.id ASC LIMIT 100");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `users`.`email` LIKE ? AND `deleted` IS NULL GROUP BY `users`.`id` LIMIT 100");
         ps.setString(1, mail);
         try (GigiResultSet rs = ps.executeQuery()) {
             while (rs.next()) {
@@ -442,7 +441,7 @@ public class User extends CertificateOwner {
     }
 
     public EmailAddress[] getEmails() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM emails WHERE memid=? AND deleted is NULL");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `emails` WHERE `memid`=? AND `deleted` IS NULL");
         ps.setInt(1, getId());
 
         try (GigiResultSet rs = ps.executeQuery()) {

diff --git a/src/org/cacert/gigi/email/EmailProvider.java b/src/org/cacert/gigi/email/EmailProvider.java
index 6494bf8d65893a8a8c3f79f1a5d74f00c85ace02..a625e40193367b397a8f2c296385a6ec0dd79fad 100644 (file)
--- a/src/org/cacert/gigi/email/EmailProvider.java
+++ b/src/org/cacert/gigi/email/EmailProvider.java
@@ -145,10 +145,11 @@ public abstract class EmailProvider {
                         continue;
                     }
 
-                    GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("insert into `emailPinglog` set `when`=NOW(), `email`=?, `result`=?, `uid`=?");
+                    GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`pingState`");
                     statmt.setString(1, address);
                     statmt.setString(2, line);
                     statmt.setInt(3, forUid);
+                    statmt.setString(4, "success");
                     statmt.execute();
 
                     if (line == null || !line.startsWith("250")) {
@@ -160,10 +161,11 @@ public abstract class EmailProvider {
 
             }
         }
-        GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("insert into `emailPinglog` set `when`=NOW(), `email`=?, `result`=?, `uid`=?");
+        GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`pingState`");
         statmt.setString(1, address);
         statmt.setString(2, "Failed to make a connection to the mail server");
         statmt.setInt(3, forUid);
+        statmt.setString(4, "failed");
         statmt.execute();
         return FAIL;
     }

diff --git a/src/org/cacert/gigi/pages/LoginPage.java b/src/org/cacert/gigi/pages/LoginPage.java
index 91b6b1b7139383ce20c1d44d61e68b8e19a65406..06006cf264d7ac9507077a709c49c69874b4c304 100644 (file)
--- a/src/org/cacert/gigi/pages/LoginPage.java
+++ b/src/org/cacert/gigi/pages/LoginPage.java
@@ -134,12 +134,17 @@ public class LoginPage extends Page {
     }
 
     public static User fetchUserBySerial(String serial) {
+        if ( !serial.matches("[A-Fa-f0-9]+")) {
+            throw new Error("serial malformed.");
+        }
         GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `certs` WHERE `serial`=? AND `disablelogin`='0' AND `revoked` is NULL");
-        ps.setString(1, serial);
+        ps.setString(1, serial.toLowerCase());
         GigiResultSet rs = ps.executeQuery();
         User user = null;
         if (rs.next()) {
             user = User.getById(rs.getInt(1));
+        } else {
+            System.out.println("User with serial " + serial + " not found.");
         }
         rs.close();
         return user;

diff --git a/src/org/cacert/gigi/pages/main/Signup.java b/src/org/cacert/gigi/pages/main/Signup.java
index 66665af2163a89a387fbefbd3f69a9f18fe3a918..a637f83d462c899ac668a1839e22e6bbda77f1bb 100644 (file)
--- a/src/org/cacert/gigi/pages/main/Signup.java
+++ b/src/org/cacert/gigi/pages/main/Signup.java
@@ -123,8 +123,8 @@ public class Signup extends Form {
         if (isFailed(out)) {
             return false;
         }
-        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");
+        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();
@@ -134,7 +134,7 @@ public class Signup extends Form {
         }
         r1.close();
         r2.close();
-        GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("select `domain` from `baddomains` where `domain`=RIGHT(?, LENGTH(`domain`))");
+        GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("SELECT `domain` FROM `baddomains` WHERE `domain`=RIGHT(?, LENGTH(`domain`))");
         q3.setString(1, buildup.getEmail());
 
         GigiResultSet r3 = q3.executeQuery();
@@ -185,12 +185,12 @@ public class Signup extends Form {
             EmailAddress ea = new EmailAddress(buildup, buildup.getEmail());
             ea.insert(Page.getLanguage(req));
 
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("insert into `alerts` set `memid`=?," + " `general`=?, `country`=?, `regional`=?, `radius`=?");
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `alerts` SET `memid`=?," + " `general`=?, `country`=?, `regional`=?, `radius`=?");
             ps.setInt(1, memid);
-            ps.setString(2, general ? "1" : "0");
-            ps.setString(3, country ? "1" : "0");
-            ps.setString(4, regional ? "1" : "0");
-            ps.setString(5, radius ? "1" : "0");
+            ps.setBoolean(2, general);
+            ps.setBoolean(3, country);
+            ps.setBoolean(4, regional);
+            ps.setBoolean(5, radius);
             ps.execute();
             Notary.writeUserAgreement(buildup, "CCA", "account creation", "", true, 0);
 

diff --git a/src/org/cacert/gigi/pages/wot/AssurePage.java b/src/org/cacert/gigi/pages/wot/AssurePage.java
index cc20c4d7a900b9183856da9c2551b8d3c5d326cf..924cc953966a1ce6c9e0f34329b4f84beb2dd17c 100644 (file)
--- a/src/org/cacert/gigi/pages/wot/AssurePage.java
+++ b/src/org/cacert/gigi/pages/wot/AssurePage.java
@@ -2,6 +2,8 @@ package org.cacert.gigi.pages.wot;
 
 import java.io.IOException;
 import java.io.PrintWriter;
+import java.sql.Date;
+import java.util.Calendar;
 import java.util.HashMap;
 
 import javax.servlet.http.HttpServletRequest;
@@ -75,19 +77,20 @@ public class AssurePage extends Page {
 
         GigiResultSet rs = null;
         try {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT users.id, verified FROM users inner join certOwners on certOwners.id=users.id WHERE email=? AND dob=? AND deleted is null");
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `users`.`id`, `verified` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id`=`users`.`id` WHERE `email`=? AND `dob`=? AND `deleted` IS NULL");
             ps.setString(1, req.getParameter("email"));
-            String day = req.getParameter("year") + "-" + req.getParameter("month") + "-" + req.getParameter("day");
-            ps.setString(2, day);
+            Calendar c = Calendar.getInstance();
+            c.set(Integer.parseInt(req.getParameter("year")), Integer.parseInt(req.getParameter("month")) - 1, Integer.parseInt(req.getParameter("day")));
+            ps.setDate(2, new Date(c.getTimeInMillis()));
             rs = ps.executeQuery();
             int id = 0;
             if (rs.next()) {
                 id = rs.getInt(1);
-                int verified = rs.getInt(2);
+                boolean verified = rs.getBoolean(2);
                 if (rs.next()) {
                     out.println("Error, ambigous user. Please contact support@cacert.org.");
                 } else {
-                    if (verified == 0) {
+                    if ( !verified) {
                         out.println(translate(req, "User is not yet verified. Please try again in 24 hours!"));
                     } else if (getUser(req).getId() == id) {
 

diff --git a/src/org/cacert/gigi/ping/PingerDaemon.java b/src/org/cacert/gigi/ping/PingerDaemon.java
index ceb88e9a68b80447207749c4797a3599cd8049c6..bb6dc5afa0b164c074eb82d5e1ec0d832b95ecca 100644 (file)
--- a/src/org/cacert/gigi/ping/PingerDaemon.java
+++ b/src/org/cacert/gigi/ping/PingerDaemon.java
@@ -31,8 +31,8 @@ public class PingerDaemon extends Thread {
 
     @Override
     public void run() {
-        searchNeededPings = DatabaseConnection.getInstance().prepare("SELECT pingconfig.id FROM pingconfig LEFT JOIN domainPinglog ON domainPinglog.configId=pingconfig.id INNER JOIN domains ON domains.id=pingconfig.domainid WHERE ( domainPinglog.configId IS NULL) AND domains.deleted IS NULL GROUP BY pingconfig.id");
-        enterPingResult = DatabaseConnection.getInstance().prepare("INSERT INTO domainPinglog SET configId=?, state=?, result=?, challenge=?");
+        searchNeededPings = DatabaseConnection.getInstance().prepare("SELECT `pingconfig`.`id` FROM `pingconfig` LEFT JOIN `domainPinglog` ON `domainPinglog`.`configId` = `pingconfig`.`id` INNER JOIN `domains` ON `domains`.`id` = `pingconfig`.`domainid` WHERE ( `domainPinglog`.`configId` IS NULL) AND `domains`.`deleted` IS NULL GROUP BY `pingconfig`.`id`");
+        enterPingResult = DatabaseConnection.getInstance().prepare("INSERT INTO `domainPinglog` SET `configId`=?, `state`=?, `result`=?, `challenge`=?");
         pingers.put(PingType.EMAIL, new EmailPinger());
         pingers.put(PingType.SSL, new SSLPinger(truststore));
         pingers.put(PingType.HTTP, new HTTPFetch());

diff --git a/src/org/cacert/gigi/util/Job.java b/src/org/cacert/gigi/util/Job.java
index f5287717308ae736a33669663d1fd9bcfe5195d9..e6f50140f35862f8a534b85759ca58ed291685a4 100644 (file)
--- a/src/org/cacert/gigi/util/Job.java
+++ b/src/org/cacert/gigi/util/Job.java
@@ -33,7 +33,7 @@ public class Job {
 
     public static Job sign(Certificate targetId, Date start, String period) throws GigiApiException {
         CertificateValiditySelector.checkValidityLength(period);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?, executeFrom=?, executeTo=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`, executeFrom=?, executeTo=?");
         ps.setInt(1, targetId.getId());
         ps.setString(2, JobType.SIGN.getName());
         ps.setDate(3, start);
@@ -44,7 +44,7 @@ public class Job {
 
     public static Job revoke(Certificate targetId) {
 
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`");
         ps.setInt(1, targetId.getId());
         ps.setString(2, JobType.REVOKE.getName());
         ps.execute();

diff --git a/src/org/cacert/gigi/util/Notary.java b/src/org/cacert/gigi/util/Notary.java
index 767230b0a82dfd470af7b356500a07dff3b8a056..a2dfe0f865953a83bc19956fd1d13028327b37be 100644 (file)
--- a/src/org/cacert/gigi/util/Notary.java
+++ b/src/org/cacert/gigi/util/Notary.java
@@ -15,11 +15,11 @@ import org.cacert.gigi.output.DateSelector;
 public class Notary {
 
     public static void writeUserAgreement(User member, String document, String method, String comment, boolean active, int secmemid) {
-        GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("insert into `user_agreements` set `memid`=?, `secmemid`=?," + " `document`=?,`date`=NOW(), `active`=?,`method`=?,`comment`=?");
+        GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("INSERT INTO `user_agreements` SET `memid`=?, `secmemid`=?," + " `document`=?,`date`=NOW(), `active`=?,`method`=?,`comment`=?");
         q.setInt(1, member.getId());
         q.setInt(2, secmemid);
         q.setString(3, document);
-        q.setInt(4, active ? 1 : 0);
+        q.setBoolean(4, active);
         q.setString(5, method);
         q.setString(6, comment);
         q.execute();

diff --git a/src/org/cacert/gigi/util/PasswordHash.java b/src/org/cacert/gigi/util/PasswordHash.java
index ed0d4f3c251903a0400552e1db0f6b2a1e75b20e..04e686189339b1aa936d73f60dbf2fda44a2ffd4 100644 (file)
--- a/src/org/cacert/gigi/util/PasswordHash.java
+++ b/src/org/cacert/gigi/util/PasswordHash.java
@@ -46,7 +46,7 @@ public class PasswordHash {
         }
     }
 
-    private static String sha1(String password) {
+    public static String sha1(String password) {
         try {
             MessageDigest md = MessageDigest.getInstance("SHA1");
             byte[] digest = md.digest(password.getBytes("UTF-8"));

diff --git a/tests/org/cacert/gigi/TestUserGroupMembership.java b/tests/org/cacert/gigi/TestUserGroupMembership.java
index c1834acdeae36a8e4ba30950e8c74c26dea79ad4..3808df5d891cc6f0d696bfeac944b473dea7cc22 100644 (file)
--- a/tests/org/cacert/gigi/TestUserGroupMembership.java
+++ b/tests/org/cacert/gigi/TestUserGroupMembership.java
@@ -82,7 +82,7 @@ public class TestUserGroupMembership extends ManagedTest {
     }
 
     private GigiResultSet fetchGroupRowsFor(User u) throws SQLException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT * FROM user_groups WHERE user=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT * FROM `user_groups` WHERE `user`=?");
         ps.setInt(1, u.getId());
         GigiResultSet rs = ps.executeQuery();
         return rs;

diff --git a/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java b/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java
index c8a6c1394d8627dd1303bf6134b4468dfc32deaa..3960cba0f5ec50200b7fa937102fb3af9c3b2c9a 100644 (file)
--- a/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java
+++ b/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java
@@ -53,7 +53,7 @@ public class TestOrgaManagement extends ClientTest {
         if (affiliation2.getTarget().getId() == u2.getId()) {
             affiliation2 = allAdmins.get(1);
         }
-        assertSame(u.getId(), affiliation2.getTarget().getId());
+        assertEquals(u.getId(), affiliation2.getTarget().getId());
         assertFalse(affiliation2.isMaster());
 
         executeBasicWebInteraction(cookie, ViewOrgPage.DEFAULT_PATH + "/" + orgs[0].getId(), "del=" + URLEncoder.encode(u.getEmail(), "UTF-8") + "&email=&do_affiliate=y", 1);

diff --git a/tests/org/cacert/gigi/testUtils/ManagedTest.java b/tests/org/cacert/gigi/testUtils/ManagedTest.java
index a21079c491837675728cb9b8d821323c1eab7a21..8686835455db8e1f6e79244b13cb9c71a801010d 100644 (file)
--- a/tests/org/cacert/gigi/testUtils/ManagedTest.java
+++ b/tests/org/cacert/gigi/testUtils/ManagedTest.java
@@ -273,7 +273,7 @@ public class ManagedTest extends ConfiguredTest {
         try {
             ter.receive().verify();
 
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM users where email=?");
+            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `users` WHERE `email`=?");
             ps.setString(1, email);
 
             try (GigiResultSet rs = ps.executeQuery()) {

diff --git a/tests/org/cacert/gigi/util/TestNotary.java b/tests/org/cacert/gigi/util/TestNotary.java
index 4bfb26d606911da047832be92e9ae7841fa0a849..03f157f33b15d8d3de1e6fb08ba5ecd07244336c 100644 (file)
--- a/tests/org/cacert/gigi/util/TestNotary.java
+++ b/tests/org/cacert/gigi/util/TestNotary.java
@@ -64,7 +64,7 @@ public class TestNotary extends ManagedTest {
             users[i] = User.getById(id);
         }
         int id = createAssuranceUser("fn", "ln", createUniqueName() + "@email.org", TEST_PASSWORD);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE users SET dob=TIMESTAMPADD(YEAR,-15,NOW()) WHERE id=?");
+        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `users` SET dob=NOW() - interval '15 years' WHERE id=?");
         ps.setInt(1, id);
         ps.execute();
         User assurer = User.getById(id);

diff --git a/tests/org/cacert/gigi/util/TestPasswordMigration.java b/tests/org/cacert/gigi/util/TestPasswordMigration.java
index 030794cb03947ce5938bba4f1386bb9b190f13d0..2992e05c9bf0eb9b70e4cb60194ce5ccf89c0a1f 100644 (file)
--- a/tests/org/cacert/gigi/util/TestPasswordMigration.java
+++ b/tests/org/cacert/gigi/util/TestPasswordMigration.java
@@ -20,8 +20,8 @@ public class TestPasswordMigration extends ManagedTest {
 
     @Test
     public void testPasswordMigration() throws IOException {
-        GigiPreparedStatement stmt = DatabaseConnection.getInstance().prepare("UPDATE users SET `password`=SHA1(?) WHERE id=?");
-        stmt.setString(1, "a");
+        GigiPreparedStatement stmt = DatabaseConnection.getInstance().prepare("UPDATE users SET `password`=? WHERE id=?");
+        stmt.setString(1, PasswordHash.sha1("a"));
         stmt.setInt(2, ru.getUser().getId());
         stmt.execute();
         String cookie = login(ru.getUser().getEmail(), "a");

diff --git a/util-testing/org/cacert/gigi/DevelLauncher.java b/util-testing/org/cacert/gigi/DevelLauncher.java
index 4137aa5d5427e9c362b7effb5239caa86aeeab65..9fab71c9cfc784d8cf80a5e2b2a5c8a8745478a7 100644 (file)
--- a/util-testing/org/cacert/gigi/DevelLauncher.java
+++ b/util-testing/org/cacert/gigi/DevelLauncher.java
@@ -188,6 +188,11 @@ public class DevelLauncher {
                 if ( !ticketUsed) {
                     HttpSession sess = req.getSession();
                     User user = User.getById(1);
+                    if (user == null) {
+                        resp.getWriter().println("ticket consumed but no user available for that action");
+                        ticketUsed = true;
+                        return true;
+                    }
                     sess.setAttribute(LOGGEDIN, true);
                     sess.setAttribute(Language.SESSION_ATTRIB_NAME, user.getPreferredLocale());
                     sess.setAttribute(USER, user);

diff --git a/util-testing/org/cacert/gigi/util/SimpleSigner.java b/util-testing/org/cacert/gigi/util/SimpleSigner.java
index 9cae722d27fdffe3af8ca0f27052f5c5807b98c7..764faf057c3078f6a9e69c5b1859d5d5d73d53a4 100644 (file)
--- a/util-testing/org/cacert/gigi/util/SimpleSigner.java
+++ b/util-testing/org/cacert/gigi/util/SimpleSigner.java
@@ -134,19 +134,19 @@ public class SimpleSigner {
             throw new IllegalStateException("already running");
         }
         running = true;
-        readyCerts = DatabaseConnection.getInstance().prepare("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, executeFrom, executeTo, profile FROM jobs " + //
-                "INNER JOIN certs ON certs.id=jobs.targetId " + //
+        readyCerts = DatabaseConnection.getInstance().prepare("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + //
+                "INNER JOIN certs ON certs.id=jobs.`targetId` " + //
                 "INNER JOIN profiles ON profiles.id=certs.profile " + //
                 "WHERE jobs.state='open' "//
                 + "AND task='sign'");
 
-        getSANSs = DatabaseConnection.getInstance().prepare("SELECT contents, type FROM subjectAlternativeNames " + //
-                "WHERE certId=?");
+        getSANSs = DatabaseConnection.getInstance().prepare("SELECT contents, type FROM `subjectAlternativeNames` " + //
+                "WHERE `certId`=?");
 
         updateMail = DatabaseConnection.getInstance().prepare("UPDATE certs SET crt_name=?," + " created=NOW(), serial=?, caid=? WHERE id=?");
         warnMail = DatabaseConnection.getInstance().prepare("UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?");
 
-        revoke = DatabaseConnection.getInstance().prepare("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.targetId=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
+        revoke = DatabaseConnection.getInstance().prepare("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
         revokeCompleted = DatabaseConnection.getInstance().prepare("UPDATE certs SET revoked=NOW() WHERE id=?");
 
         finishJob = DatabaseConnection.getInstance().prepare("UPDATE jobs SET state='done' WHERE id=?");
@@ -297,7 +297,7 @@ public class SimpleSigner {
                 String ca = caP.getProperty("ca") + "_2015_1";
 
                 HashMap<String, String> subj = new HashMap<>();
-                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT name, value FROM certAvas WHERE certId=?");
+                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT name, value FROM `certAvas` WHERE certId=?");
                 ps.setInt(1, rs.getInt("id"));
                 GigiResultSet rs2 = ps.executeQuery();
                 while (rs2.next()) {