Database Manager

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
new file mode 100644 (file)
index 0000000..e132aec
--- /dev/null
+++ b/doc/tableStructure.sql
@@ -0,0 +1,86 @@
+DROP TABLE IF EXISTS `users`;
+CREATE TABLE `users` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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',
+  `admin` tinyint(1) NOT NULL DEFAULT '0',
+  `language` varchar(5) 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',
+  `locked` tinyint(1) NOT NULL DEFAULT '0',
+  `assurer_blocked` tinyint(1) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`id`),
+  KEY `ccid` (`ccid`),
+  KEY `regid` (`regid`),
+  KEY `locid` (`locid`),
+  KEY `email` (`email`),
+  KEY `stats_users_created` (`created`),
+  KEY `stats_users_verified` (`verified`),
+  KEY `userverified` (`verified`)
+) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
+
+
+DROP TABLE IF EXISTS `email`;
+CREATE TABLE `email` (
+  `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',
+  `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=latin1;
+
+DROP TABLE IF EXISTS `pinglog`;
+CREATE TABLE `pinglog` (
+  `when` datetime NOT NULL,
+  `uid` int(11) NOT NULL,
+  `email` varchar(255) NOT NULL,
+  `result` varchar(255) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+DROP TABLE IF EXISTS `baddomains`;
+CREATE TABLE `baddomains` (
+  `domain` varchar(255) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+
+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=latin1;
+
+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=61411 DEFAULT CHARSET=latin1;

diff --git a/src/org/cacert/gigi/User.java b/src/org/cacert/gigi/User.java
index b049bc3ffde11edb06d49e4263dfad0001f604f7..bf47552ac56a7b3e7285eefe20588f895e8aa984 100644 (file)
--- a/src/org/cacert/gigi/User.java
+++ b/src/org/cacert/gigi/User.java
@@ -85,9 +85,7 @@ public class User {
                PreparedStatement query = DatabaseConnection.getInstance().prepare(
                                "insert into `users` set `email`=?, `password`=?, "
                                                + "`fname`=?, `mname`=?, `lname`=?, "
-                                               + "`suffix`=?, `dob`=?, `created`=NOW(),"
-                                               + " `orgadmin`=0, `adadmin`=0, `locked`=0,"
-                                               + " `uniqueID`=0, `otphash`='', `otppin`=0");
+                                               + "`suffix`=?, `dob`=?, `created`=NOW(), locked=0");
                query.setString(1, email);
                query.setString(2, PasswordHash.hash(password));
                query.setString(3, fname);

diff --git a/util/org/cacert/gigi/util/DatabaseManager.java b/util/org/cacert/gigi/util/DatabaseManager.java
new file mode 100644 (file)
index 0000000..34f80f9
--- /dev/null
+++ b/util/org/cacert/gigi/util/DatabaseManager.java
@@ -0,0 +1,36 @@
+package org.cacert.gigi.util;
+
+import java.io.File;
+import java.io.IOException;
+import java.nio.file.Files;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+public class DatabaseManager {
+       public static String readFile(File f) throws IOException {
+               return new String(Files.readAllBytes(f.toPath()));
+       }
+       public static void main(String[] args) throws SQLException,
+                       ClassNotFoundException, IOException {
+               if (args.length < 4) {
+                       System.err
+                                       .println("Usage: com.mysql.jdbc.Driver jdbc:mysql://localhost/cacert user password");
+                       return;
+               }
+               Class.forName(args[0]);
+               Connection conn = DriverManager
+                               .getConnection(args[1], args[2], args[3]);
+               Statement stmt = conn.createStatement();
+               String sql = readFile(new File("doc/tableStructure.sql"));
+               String[] stmts = sql.split(";");
+               for (String string : stmts) {
+                       if (!string.trim().equals("")) {
+                               stmt.addBatch(string);
+                       }
+               }
+               stmt.executeBatch();
+               stmt.close();
+       }
+}