com.trackplus.ddl.DataReader.java Source code

Java tutorial

Introduction

Here is the source code for com.trackplus.ddl.DataReader.java

Source

/**
 * Genji Scrum Tool and Issue Tracker
 * Copyright (C) 2015 Steinbeis GmbH & Co. KG Task Management Solutions
    
 * <a href="http://www.trackplus.com">Genji Scrum Tool</a>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

/* $Id:$ */

package com.trackplus.ddl;

import org.apache.commons.codec.binary.Base64;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;

import java.io.*;
import java.sql.*;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

public class DataReader {

    private static final Logger LOGGER = LogManager.getLogger(DataReader.class);

    public static final int ASCII_LINE_SEPARATOR = 7;
    public static final int ASCII_DATA_SEPARATOR = 5;

    public static final char LINE_SEPARATOR = (char) ASCII_LINE_SEPARATOR;

    public static final int MAX_VALUE_STRING = 4000;

    public static final String FILE_NAME_DATA = "data.sql";
    public static final String FILE_NAME_INFO = "info.properties";
    public static final String FILE_NAME_DATA_UPDATE = "dataUpdate.sql";
    public static final String FILE_NAME_DATA_CLEAN = "dataClean.sql";
    public static final String FILE_NAME_DATA_UPDATE_CLEAN = "dataUpdateClean.sql";
    public static final String FILE_NAME_BLOB = "blob.dat";

    public static final String DATABASE_TYPE = "databaseType";
    public static final String SYSTEM_VERSION = "versionTrack";
    public static final String DB_VERSION = "versionDB";

    public DataReader() {
    }

    public static void writeDataToSql(DatabaseInfo databaseInfo, String dirName) throws DDLException {
        LOGGER.info("Exporting SQL data from \"" + databaseInfo.getUrl() + "\" ...");
        Map<String, String> info = new TreeMap<String, String>();
        java.util.Date d1 = new java.util.Date();
        info.put("start", d1.toString());
        info.put("driver", databaseInfo.getDriver());
        info.put("url", databaseInfo.getUrl());
        info.put("user", databaseInfo.getUser());
        info.put("user", databaseInfo.getUser());
        info.put("usePassword", Boolean.toString(databaseInfo.getPassword() != null));
        String databaseType = MetaDataBL.getDatabaseType(databaseInfo.getUrl());
        info.put(DATABASE_TYPE, databaseType);

        Connection connection = getConnection(databaseInfo);
        //log the database meta data information's
        logDatabaseMetaDataInfo(databaseInfo, connection);

        String[] versions = MetaDataBL.getVersions(connection);
        info.put(SYSTEM_VERSION, versions[0]);
        info.put(DB_VERSION, versions[1]);

        StringValueConverter stringValueConverter = new GenericStringValueConverter();

        BufferedWriter writer = createBufferedWriter(dirName + File.separator + FILE_NAME_DATA);
        BufferedWriter writerUpdate = createBufferedWriter(dirName + File.separator + FILE_NAME_DATA_UPDATE);
        BufferedWriter writerClean = createBufferedWriter(dirName + File.separator + FILE_NAME_DATA_CLEAN);
        BufferedWriter writerUpdateClean = createBufferedWriter(
                dirName + File.separator + FILE_NAME_DATA_UPDATE_CLEAN);
        BufferedWriter writerBlob = createBufferedWriter(dirName + File.separator + FILE_NAME_BLOB);

        int idx = 0;
        String[] tableNames = MetaDataBL.getTableNames();
        for (String tableName : tableNames) {
            LOGGER.debug("Processing table: " + tableName + "....");
            int count = getTableData(writer, writerClean, writerUpdate, writerUpdateClean, connection, tableName,
                    stringValueConverter);
            info.put("_" + tableName, count + "");
            LOGGER.debug("Records exported:" + count + "\n");
            idx = idx + count;
        }
        LOGGER.debug("Processing blob data ....");
        int count = getBlobTableData(writerBlob, connection);
        LOGGER.debug(" Blob record exported:" + count + "\n");
        info.put("table_BLOB", count + "");
        idx = idx + count;

        try {
            char dataSeparator = (char) ASCII_DATA_SEPARATOR;
            writerBlob.write(dataSeparator);
            writerBlob.newLine();
            writerBlob.newLine();
            writerBlob.write("--TMSPROJECTEXCHANGE");
            writerBlob.newLine();
        } catch (IOException e) {
            LOGGER.error("Error on close blob stream file :" + e.getMessage());
            throw new DDLException(e.getMessage(), e);
        }

        LOGGER.debug("Processing clob data ....");
        count = getClobTableData(writerBlob, connection);
        LOGGER.debug(" Clob record exported:" + count + "\n");
        info.put("table_TMSPROJECTEXCHANGE", count + "");
        idx = idx + count;
        info.put("allData", idx + "");

        try {
            writer.flush();
            writer.close();

            writerClean.flush();
            writerClean.close();

            writerUpdate.flush();
            writerUpdate.close();

            writerUpdateClean.flush();
            writerUpdateClean.close();

            writerBlob.flush();
            writerBlob.close();
        } catch (IOException e) {
            LOGGER.error("Error on close stream file: " + e.getMessage());
            throw new DDLException(e.getMessage(), e);
        }

        try {
            connection.close();
        } catch (SQLException e) {
            throw new DDLException(e.getMessage(), e);
        }

        java.util.Date d2 = new java.util.Date();
        long timeSpend = d2.getTime() - d1.getTime();
        info.put("timeSpend", Long.toString(timeSpend));

        writeInfoToFile(info, dirName + File.separator + FILE_NAME_INFO);
        LOGGER.info("Data generated. All records found: " + idx + ". Time spend: " + timeSpend + " ms!");
    }

    private static void logDatabaseMetaDataInfo(DatabaseInfo databaseInfo, Connection connection)
            throws DDLException {
        DatabaseMetaData databaseMetaData = null;
        try {
            databaseMetaData = connection.getMetaData();
            int majorVersion = databaseMetaData.getDatabaseMajorVersion();
            int minorVersion = databaseMetaData.getDatabaseMinorVersion();
            String productName = databaseMetaData.getDatabaseProductName();
            String productVersion = databaseMetaData.getDatabaseProductVersion();
            int driverMajorVersion = databaseMetaData.getDriverMajorVersion();
            int driverMinorVersion = databaseMetaData.getDriverMinorVersion();

            LOGGER.debug("DB DRIVER=" + databaseInfo.getDriver());
            LOGGER.debug("DB URL=" + databaseInfo.getUrl());
            LOGGER.debug("DB USER=" + databaseInfo.getUser());
            String password = databaseInfo.getPassword() == null ? null
                    : databaseInfo.getPassword().replaceAll(".", "*");
            LOGGER.debug("DB PASSWORD=" + password + "\n");

            LOGGER.debug("DB majorVersion=" + majorVersion);
            LOGGER.debug("DB minorVersion=" + minorVersion);
            LOGGER.debug("DB productName=" + productName);
            LOGGER.debug("DB productVersion=" + productVersion);
            LOGGER.debug("DB driverMajorVersion=" + driverMajorVersion);
            LOGGER.debug("DB driverMinorVersion=" + driverMinorVersion);
        } catch (SQLException e) {
            throw new DDLException(e.getMessage(), e);
        }
    }

    public static Connection getConnection(DatabaseInfo databaseInfo) throws DDLException {
        try {
            Class.forName(databaseInfo.getDriver());
        } catch (ClassNotFoundException e) {
            throw new DDLException(e.getMessage(), e);
        }
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(databaseInfo.getUrl(), databaseInfo.getUser(),
                    databaseInfo.getPassword());
        } catch (SQLException e) {
            throw new DDLException(e.getMessage(), e);
        }
        return connection;
    }

    public static BufferedWriter createBufferedWriter(String fileName) throws DDLException {
        BufferedWriter writer = null;
        try {
            File file = new File(fileName);
            writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "UTF-8"));
        } catch (Exception e) {
            LOGGER.error("Error on create/write file " + fileName + " :" + e.getMessage());
            throw new DDLException(e.getMessage(), e);
        }
        return writer;
    }

    private static void writeInfoToFile(Map<String, String> info, String fileName) throws DDLException {
        BufferedWriter writer = createBufferedWriter(fileName);
        Iterator<String> it = info.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            String value = info.get(key);
            try {
                writer.write(key);
                writer.write("=");
                writer.write(value);
                writer.newLine();
            } catch (IOException ex) {
                throw new DDLException(ex.getMessage(), ex);
            }
        }

        try {
            writer.flush();
            writer.close();
        } catch (IOException e) {
            LOGGER.error("Error on close stream file " + fileName + " :" + e.getMessage());
            throw new DDLException(e.getMessage(), e);
        }
    }

    private static int getTableData(BufferedWriter writer, BufferedWriter writerClean, BufferedWriter writerUpdate,
            BufferedWriter writerUpdateClean, Connection con, String tableName,
            StringValueConverter stringValueConverter) throws DDLException {
        Statement st = MetaDataBL.createStatement(con);
        String sql = "SELECT * FROM " + tableName;
        ResultSet rs;
        try {
            rs = st.executeQuery(sql);
        } catch (SQLException e) {
            throw new DDLException(e.getMessage(), e);
        }

        int idx = 0;
        int idxUpdate = 0;

        try {
            ResultSetMetaData md = rs.getMetaData();
            String primaryKey = MetaDataBL.getPrimaryKey(tableName, con);
            int columnCount = md.getColumnCount();
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO " + tableName + "(");
            for (int i = 0; i < columnCount; i++) {
                String columnName = md.getColumnName(i + 1);
                sb.append(columnName);
                if (i < columnCount - 1) {
                    sb.append(", ");
                }
            }
            sb.append(") VALUES(");

            StringBuilder updateSQL;
            while (rs.next()) {
                StringBuilder line = new StringBuilder();
                line.append(sb);
                String primaryKeyValue = rs.getString(primaryKey);
                for (int i = 0; i < columnCount; i++) {
                    String value;
                    String columnName = md.getColumnName(i + 1);
                    try {
                        value = stringValueConverter.getStringValue(md, i + 1, rs, tableName);
                    } catch (DDLException ex) {
                        LOGGER.warn("Error: " + ex.getMessage() + " for column:" + columnName + " in table:"
                                + tableName + " primary key " + primaryKey + "=" + primaryKeyValue
                                + ". Will be set to NULL!");
                        value = null;
                    }
                    if (value != null && value.length() > MAX_VALUE_STRING) {

                        if ("MAILBODY".equalsIgnoreCase(columnName) || "CLOBVALUE".equalsIgnoreCase(columnName)) {
                            //blob
                        } else {
                            updateSQL = new StringBuilder();
                            updateSQL.append("UPDATE ").append(tableName).append(" SET ").append(columnName)
                                    .append("=");
                            updateSQL.append(value).append("\n WHERE ").append(primaryKey).append("=")
                                    .append(primaryKeyValue);
                            updateSQL.append(";");

                            MetaDataBL.appendLine(writerUpdateClean, updateSQL.toString());

                            updateSQL.append(LINE_SEPARATOR);
                            MetaDataBL.appendLine(writerUpdate, updateSQL.toString());

                            idxUpdate++;
                            value = null;
                        }
                    }
                    line.append(value);
                    if (i < columnCount - 1) {
                        line.append(", ");
                    }
                }
                line.append(");");

                MetaDataBL.appendLine(writerClean, line.toString());

                line.append(LINE_SEPARATOR);
                MetaDataBL.appendLine(writer, line.toString());

                idx++;
            }
        } catch (SQLException e) {
            throw new DDLException(e.getMessage(), e);
        }
        try {
            rs.close();
        } catch (SQLException e) {
            throw new DDLException(e.getMessage(), e);
        }
        if (idxUpdate > 0) {
            LOGGER.warn("There was " + idxUpdate + " records with String size>" + MAX_VALUE_STRING
                    + " found in table:" + tableName);
        }
        return idx;
    }

    private static int getBlobTableData(BufferedWriter writer, Connection connection) throws DDLException {
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM TBLOB");
            int idx = 0;
            while (rs.next()) {
                StringBuilder line = new StringBuilder();

                //OBJECTID
                String value = rs.getString("OBJECTID");
                line.append(value).append(",");

                //BLOBVALUE
                Blob blobValue = rs.getBlob("BLOBVALUE");
                if (blobValue != null) {
                    String str = new String(Base64.encodeBase64(blobValue.getBytes(1l, (int) blobValue.length())));
                    if (str.length() == 0) {
                        str = " ";
                    }
                    line.append(str);
                } else {
                    line.append("null");
                }
                line.append(",");

                //TPUUID
                value = rs.getString("TPUUID");
                line.append(value);
                writer.write(line.toString());
                writer.newLine();
                idx++;
            }
            rs.close();
            return idx;
        } catch (SQLException ex) {
            throw new DDLException(ex.getMessage(), ex);
        } catch (IOException ex) {
            throw new DDLException(ex.getMessage(), ex);
        }
    }

    private static int getClobTableData(BufferedWriter writer, Connection connection) throws DDLException {
        Statement st = MetaDataBL.createStatement(connection);
        try {
            ResultSet rs = st.executeQuery("SELECT * FROM TMSPROJECTEXCHANGE");
            int idx = 0;
            String[] columns = MetaDataBL.getColumnsMsProjectExchange();
            while (rs.next()) {
                StringBuilder line = new StringBuilder();
                for (int i = 0; i < columns.length; i++) {
                    String value = rs.getString(columns[i]);
                    if (value != null && "FILECONTENT".equals(columns[i])) {
                        value = encodeBase64FileContent(value);
                    }
                    line.append(value);
                    if (i < columns.length - 1) {
                        line.append(",");
                    }
                }
                MetaDataBL.appendLine(writer, line.toString());
                idx++;
            }
            rs.close();
            return idx;
        } catch (SQLException ex) {
            throw new DDLException(ex.getMessage(), ex);
        }
    }

    private static String encodeBase64FileContent(String value) {
        value = Base64.encodeBase64String(value.getBytes());
        if (value.length() == 0) {
            //empty value we replace with  space char, otherwise will not be precessed correctly to database restore
            value = " ";
        }
        return value;
    }

}