From: Felix Dörre <felix@dogcraft.de>
Date: Tue, 13 Oct 2015 08:36:21 +0000 (+0200)
Subject: upd: database column rename
X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=b5e3f4933a9cd240d39ed906577c2c93a48529d1

upd: database column rename
---

diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java
index 9a8b10ea..6aa479cc 100644
--- a/src/org/cacert/gigi/database/DatabaseConnection.java
+++ b/src/org/cacert/gigi/database/DatabaseConnection.java
@@ -17,7 +17,7 @@ import org.cacert.gigi.database.SQLFileManager.ImportType;
 
 public class DatabaseConnection {
 
-    public static final int CURRENT_SCHEMA_VERSION = 4;
+    public static final int CURRENT_SCHEMA_VERSION = 5;
 
     public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;
 
diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql
index f451ede2..c4e51737 100644
--- a/src/org/cacert/gigi/database/tableStructure.sql
+++ b/src/org/cacert/gigi/database/tableStructure.sql
@@ -188,11 +188,11 @@ CREATE INDEX ON "certs" ("crt_name");
 
 DROP TABLE IF EXISTS "certAvas";
 CREATE TABLE "certAvas" (
-  "certid" int NOT NULL,
+  "certId" int NOT NULL,
   "name" varchar(20) NOT NULL,
   "value" varchar(255) NOT NULL,
 
-  PRIMARY KEY ("certid", "name")
+  PRIMARY KEY ("certId", "name")
 );
 
 DROP TABLE IF EXISTS "clientcerts";
@@ -373,4 +373,4 @@ CREATE TABLE "schemeVersion" (
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );
-INSERT INTO "schemeVersion" (version)  VALUES(4);
+INSERT INTO "schemeVersion" (version)  VALUES(5);
diff --git a/src/org/cacert/gigi/database/upgrade/from_4.sql b/src/org/cacert/gigi/database/upgrade/from_4.sql
new file mode 100644
index 00000000..80e78ca0
--- /dev/null
+++ b/src/org/cacert/gigi/database/upgrade/from_4.sql
@@ -0,0 +1 @@
+ALTER TABLE "certAvas" RENAME "certid" TO "certId";
diff --git a/src/org/cacert/gigi/dbObjects/Certificate.java b/src/org/cacert/gigi/dbObjects/Certificate.java
index fc92b309..8ee811db 100644
--- a/src/org/cacert/gigi/dbObjects/Certificate.java
+++ b/src/org/cacert/gigi/dbObjects/Certificate.java
@@ -263,7 +263,7 @@ public class Certificate {
             san.execute();
         }
 
-        GigiPreparedStatement insertAVA = DatabaseConnection.getInstance().prepare("INSERT INTO `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());
@@ -360,7 +360,7 @@ public class Certificate {
         // TODO caching?
         try {
             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`");
+            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);
@@ -375,7 +375,7 @@ 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 certs.id=? GROUP BY certs.id");
+            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 certs.id=? GROUP BY certs.id");
             ps.setInt(1, id);
             GigiResultSet rs = ps.executeQuery();
 
diff --git a/util-testing/org/cacert/gigi/util/SimpleSigner.java b/util-testing/org/cacert/gigi/util/SimpleSigner.java
index 764faf05..b61f851e 100644
--- a/util-testing/org/cacert/gigi/util/SimpleSigner.java
+++ b/util-testing/org/cacert/gigi/util/SimpleSigner.java
@@ -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()) {