Java tutorial
/*************************GO-LICENSE-START********************************* * Copyright 2014 ThoughtWorks, Inc. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. *************************GO-LICENSE-END***********************************/ package com.thoughtworks.go.server.database; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.io.LineNumberReader; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.zip.Deflater; import com.thoughtworks.go.util.SystemEnvironment; import com.thoughtworks.go.util.ZipUtil; import org.apache.commons.dbcp.BasicDataSource; import org.apache.log4j.Logger; import static com.thoughtworks.go.util.ExceptionUtils.bomb; import static org.apache.commons.io.FileUtils.deleteDirectory; public class MigrateHsqldbToH2 implements Migration { public static final String BACKUP_ALREADY_EXISTS = "Cannot upgrade database from hsql. A backup database already directoryExists."; Pattern createTable = Pattern.compile("CREATE (.*) TABLE (.*?)\\(", Pattern.COMMENTS); private SystemEnvironment env; private static final String BACKUP_FILE_NAME_DATE_PATTERN = "yyyy-MM-dd-hhmm"; public static final int LINES_PER_DOT = 2000; private BasicDataSource source; private static final Logger LOGGER = Logger.getLogger(MigrateHsqldbToH2.class); public MigrateHsqldbToH2(BasicDataSource source, SystemEnvironment env) { this.source = source; this.env = env; } public void migrate() { runScript(); } private void runScript() { File oldHsqlScript = new File(oldHsql(), "cruise.script"); if (oldHsql().exists()) { LOGGER.info(String.format("Found database at %s to be hsql. Migrating it to h2db.", oldHsqlScript.getAbsolutePath())); try { backupOldDb(dbDirectory(), oldHsql()); if (oldHsqlScript.exists()) { backupNewTemplateDb(dbDirectory(), newDb()); replayScript(new File(oldHsql(), "cruise.script")); replayScript(new File(oldHsql(), "cruise.log")); } deleteDirectory(oldHsql()); LOGGER.info( String.format("Finished Migrating from hsqldb to h2db", oldHsqlScript.getAbsolutePath())); } catch (IOException e) { bomb("Could not migrate old hsqldb to h2. IOException occured.", e); } catch (SQLException e) { bomb("Could not migrate old hsqldb to h2. SQL error occurred.", e); } } } private File newDb() { return new File(dbDirectory(), "h2db"); } private File oldHsql() { return new File(dbDirectory(), "hsqldb"); } private File dbDirectory() { return env.getDbPath().getParentFile(); } private void backupNewTemplateDb(File dbDirectory, File newDb) throws IOException { File backup = new File(dbDirectory, "h2db-template-backup-" + dateString() + ".zip"); new ZipUtil().zip(newDb, backup, Deflater.DEFAULT_COMPRESSION); deleteDirectory(newDb); if (newDb.exists()) { bomb("Database " + newDb + " could not be deleted."); } } private void backupOldDb(File dbDirectory, File oldHsql) throws IOException { File backupFile = new File(dbDirectory, "hsqldb-upgrade-backup-" + dateString() + ".zip"); if (backupFile.exists()) { bomb(BACKUP_ALREADY_EXISTS); } new ZipUtil().zip(oldHsql, backupFile, Deflater.DEFAULT_COMPRESSION); } private String dateString() { return new SimpleDateFormat(BACKUP_FILE_NAME_DATE_PATTERN).format(new Date()); } private void replayScript(File scriptFile) throws SQLException, IOException { if (!scriptFile.exists()) { return; } System.out.println("Migrating hsql file: " + scriptFile.getName()); Connection con = source.getConnection(); Statement stmt = con.createStatement(); stmt.executeUpdate("SET REFERENTIAL_INTEGRITY FALSE"); LineNumberReader reader = new LineNumberReader(new FileReader(scriptFile)); String line; while ((line = reader.readLine()) != null) { try { String table = null; Matcher matcher = createTable.matcher(line); if (matcher.find()) { table = matcher.group(2).trim(); } if (line.equals("CREATE SCHEMA PUBLIC AUTHORIZATION DBA")) { continue; } if (line.equals("CREATE SCHEMA CRUISE AUTHORIZATION DBA")) { continue; } if (line.startsWith("CREATE USER SA PASSWORD")) { continue; } if (line.contains("BUILDEVENT VARCHAR(255)")) { line = line.replace("BUILDEVENT VARCHAR(255)", "BUILDEVENT LONGVARCHAR"); } if (line.contains("COMMENT VARCHAR(4000)")) { line = line.replace("COMMENT VARCHAR(4000)", "COMMENT LONGVARCHAR"); } if (line.contains("CREATE MEMORY TABLE")) { line = line.replace("CREATE MEMORY TABLE", "CREATE CACHED TABLE"); } if (table != null && table.equals("MATERIALPROPERTIES") && line.contains("VALUE VARCHAR(255),")) { line = line.replace("VALUE VARCHAR(255),", "VALUE LONGVARCHAR,"); } if (line.startsWith("GRANT DBA TO SA")) { continue; } if (line.startsWith("CONNECT USER")) { continue; } if (line.contains("DISCONNECT")) { continue; } if (line.contains("AUTOCOMMIT")) { continue; } stmt.executeUpdate(line); if (reader.getLineNumber() % LINES_PER_DOT == 0) { System.out.print("."); System.out.flush(); } if (reader.getLineNumber() % (80 * LINES_PER_DOT) == 0) { System.out.println(); } } catch (SQLException e) { bomb("Error executing : " + line, e); } } stmt.executeUpdate("SET REFERENTIAL_INTEGRITY TRUE"); stmt.executeUpdate("CHECKPOINT SYNC"); System.out.println("\nDone."); reader.close(); stmt.close(); con.close(); } }