From: Felix Dörre <felix@dogcraft.de>
Date: Wed, 25 Jun 2014 12:18:51 +0000 (+0200)
Subject: Database Manager
X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=79e4e5abf2c48fdf6dd07c7278e99f93cde5f59d

Database Manager
---

diff --git a/doc/tableStructure.sql b/doc/tableStructure.sql
new file mode 100644
index 00000000..e132aec0
--- /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 b049bc3f..bf47552a 100644
--- 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
index 00000000..34f80f9b
--- /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();
+	}
+}