From: Felix Dörre Date: Sun, 1 Feb 2015 16:04:45 +0000 (+0100) Subject: ADD: scheme versioning, last time "Please execute database manager" ;-) X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=53a66914a544d38be7e626aec2e74542745d4fda ADD: scheme versioning, last time "Please execute database manager" ;-) --- diff --git a/build.xml b/build.xml index 7a6cc5a0..f4876bec 100644 --- a/build.xml +++ b/build.xml @@ -121,7 +121,7 @@ + includes="gigi.jar,native/*.so,src/org/cacert/gigi/database/tableStructure.sql,static/**,templates/**" /> @@ -132,7 +132,7 @@ + includes="../src/org/cacert/gigi/database/tableStructure.sql,**,templates/**" /> @@ -143,7 +143,7 @@ + includes="../src/org/cacert/gigi/database/tableStructure.sql,**,templates/**" /> @@ -287,7 +287,7 @@ - + diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java index 661565d8..49658fb7 100644 --- a/src/org/cacert/gigi/database/DatabaseConnection.java +++ b/src/org/cacert/gigi/database/DatabaseConnection.java @@ -1,5 +1,7 @@ package org.cacert.gigi.database; +import java.io.IOException; +import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; @@ -9,8 +11,12 @@ import java.sql.Statement; import java.util.HashMap; import java.util.Properties; +import org.cacert.gigi.database.SQLFileManager.ImportType; + public class DatabaseConnection { + public static final int CURRENT_SCHEMA_VERSION = 1; + public static final int CONNECTION_TIMEOUT = 24 * 60 * 60; private Connection c; @@ -96,6 +102,41 @@ 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(); + int version = 0; + if (rs.next()) { + version = rs.getInt(1); + } + if (version == CURRENT_SCHEMA_VERSION) { + return; // Good to go + } + if (version > CURRENT_SCHEMA_VERSION) { + throw new Error("Invalid database version. Please fix this."); + } + upgrade(version); + } + + private static void upgrade(int version) { + try { + Statement s = getInstance().c.createStatement(); + while (version < CURRENT_SCHEMA_VERSION) { + InputStream resourceAsStream = DatabaseConnection.class.getResourceAsStream("upgrade/from_" + version + ".sql"); + if (resourceAsStream == null) { + throw new Error("Upgrade script from version " + version + " was not found."); + } + SQLFileManager.addFile(s, resourceAsStream, ImportType.PRODUCTION); + version++; + } + s.addBatch("INSERT INTO schemeVersion SET version='" + version + "'"); + System.out.println("UPGRADING Database to version " + version); + s.executeBatch(); + System.out.println("done."); + s.close(); + } catch (SQLException e) { + e.printStackTrace(); + } catch (IOException e) { + e.printStackTrace(); + } } public void beginTransaction() throws SQLException { diff --git a/src/org/cacert/gigi/database/SQLFileManager.java b/src/org/cacert/gigi/database/SQLFileManager.java new file mode 100644 index 00000000..676d9e66 --- /dev/null +++ b/src/org/cacert/gigi/database/SQLFileManager.java @@ -0,0 +1,61 @@ +package org.cacert.gigi.database; + +import java.io.ByteArrayOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +public class SQLFileManager { + + public static enum ImportType { + /** + * Execute Script as-as + */ + PRODUCTION, + /** + * Execute Script, but changing Engine=InnoDB to Engine=Memory + */ + TEST, + /** + * Execute INSERT statements as-is, and TRUNCATE instead of DROPPING + */ + TRUNCATE + } + + public static void addFile(Statement stmt, InputStream f, ImportType type) throws IOException, SQLException { + String sql = readFile(f); + sql = sql.replaceAll("--[^\n]+\n", "\n"); + String[] stmts = sql.split(";"); + Pattern p = Pattern.compile("\\s*DROP TABLE IF EXISTS `([^`]+)`"); + for (String string : stmts) { + Matcher m = p.matcher(string); + string = string.trim(); + if (string.equals("")) { + continue; + } + if (m.matches() && type == ImportType.TRUNCATE) { + String sql2 = "TRUNCATE `" + m.group(1) + "`"; + stmt.addBatch(sql2); + continue; + } + if (type == ImportType.PRODUCTION || string.startsWith("INSERT")) { + stmt.addBatch(string); + } else if (type == ImportType.TEST) { + stmt.addBatch(string.replace("ENGINE=InnoDB", "ENGINE=Memory")); + } + } + } + + private static String readFile(InputStream f) throws IOException { + ByteArrayOutputStream baos = new ByteArrayOutputStream(); + int len; + byte[] buf = new byte[4096]; + while ((len = f.read(buf)) > 0) { + baos.write(buf, 0, len); + } + return new String(baos.toByteArray()); + } +} diff --git a/doc/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql similarity index 98% rename from doc/tableStructure.sql rename to src/org/cacert/gigi/database/tableStructure.sql index 867a1c63..e72c2701 100644 --- a/doc/tableStructure.sql +++ b/src/org/cacert/gigi/database/tableStructure.sql @@ -341,3 +341,10 @@ CREATE TABLE `adminLog` ( `information` varchar(50) NOT NULL DEFAULT '', PRIMARY 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(1); diff --git a/tests/org/cacert/gigi/testUtils/ManagedTest.java b/tests/org/cacert/gigi/testUtils/ManagedTest.java index ce96cf28..6ca4c768 100644 --- a/tests/org/cacert/gigi/testUtils/ManagedTest.java +++ b/tests/org/cacert/gigi/testUtils/ManagedTest.java @@ -38,6 +38,7 @@ import org.cacert.gigi.GigiApiException; import org.cacert.gigi.database.DatabaseConnection; import org.cacert.gigi.database.GigiPreparedStatement; import org.cacert.gigi.database.GigiResultSet; +import org.cacert.gigi.database.SQLFileManager.ImportType; import org.cacert.gigi.dbObjects.EmailAddress; import org.cacert.gigi.dbObjects.Group; import org.cacert.gigi.dbObjects.ObjectCache; @@ -48,7 +49,6 @@ import org.cacert.gigi.pages.account.MyDetails; import org.cacert.gigi.pages.main.RegisterPage; import org.cacert.gigi.testUtils.TestEmailReciever.TestMail; import org.cacert.gigi.util.DatabaseManager; -import org.cacert.gigi.util.DatabaseManager.ImportType; import org.cacert.gigi.util.ServerConstants; import org.cacert.gigi.util.SimpleSigner; import org.junit.After; diff --git a/util-testing/org/cacert/gigi/util/DatabaseManager.java b/util/org/cacert/gigi/util/DatabaseManager.java similarity index 50% rename from util-testing/org/cacert/gigi/util/DatabaseManager.java rename to util/org/cacert/gigi/util/DatabaseManager.java index 0dadd00a..891be0f4 100644 --- a/util-testing/org/cacert/gigi/util/DatabaseManager.java +++ b/util/org/cacert/gigi/util/DatabaseManager.java @@ -1,22 +1,20 @@ package org.cacert.gigi.util; import java.io.File; +import java.io.FileInputStream; import java.io.FileReader; 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; import java.util.Properties; -import java.util.regex.Matcher; -import java.util.regex.Pattern; -public class DatabaseManager { +import org.cacert.gigi.database.DatabaseConnection; +import org.cacert.gigi.database.SQLFileManager; +import org.cacert.gigi.database.SQLFileManager.ImportType; - public static String readFile(File f) throws IOException { - return new String(Files.readAllBytes(f.toPath())); - } +public class DatabaseManager { public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException { boolean test = false; @@ -40,57 +38,19 @@ public class DatabaseManager { run(args, test ? ImportType.TEST : ImportType.PRODUCTION); } - public static enum ImportType { - /** - * Execute Script as-as - */ - PRODUCTION, - /** - * Execute Script, but changing Engine=InnoDB to Engine=Memory - */ - TEST, - /** - * Execute INSERT statements as-is, and TRUNCATE instead of DROPPING - */ - TRUNCATE - } - public static void run(String[] args, ImportType truncate) throws ClassNotFoundException, SQLException, IOException { Class.forName(args[0]); Connection conn = DriverManager.getConnection(args[1], args[2], args[3]); conn.setAutoCommit(false); Statement stmt = conn.createStatement(); - addFile(stmt, new File("doc/tableStructure.sql"), truncate); + SQLFileManager.addFile(stmt, DatabaseConnection.class.getResourceAsStream("tableStructure.sql"), truncate); File localData = new File("doc/sampleData.sql"); if (localData.exists()) { - addFile(stmt, localData, ImportType.PRODUCTION); + SQLFileManager.addFile(stmt, new FileInputStream(localData), ImportType.PRODUCTION); } stmt.executeBatch(); conn.commit(); stmt.close(); } - private static void addFile(Statement stmt, File f, ImportType type) throws IOException, SQLException { - String sql = readFile(f); - sql = sql.replaceAll("--[^\n]+\n", "\n"); - String[] stmts = sql.split(";"); - Pattern p = Pattern.compile("\\s*DROP TABLE IF EXISTS `([^`]+)`"); - for (String string : stmts) { - Matcher m = p.matcher(string); - string = string.trim(); - if (string.equals("")) { - continue; - } - if (m.matches() && type == ImportType.TRUNCATE) { - String sql2 = "TRUNCATE `" + m.group(1) + "`"; - stmt.addBatch(sql2); - continue; - } - if (type == ImportType.PRODUCTION || string.startsWith("INSERT")) { - stmt.addBatch(string); - } else if (type == ImportType.TEST) { - stmt.addBatch(string.replace("ENGINE=InnoDB", "ENGINE=Memory")); - } - } - } }