/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package datahelper;

import java.sql.DriverManager;
import org.junit.Assert;
import org.junit.Test;

/**
 *
 * @author Lisewski
 */
public class DataHelperTest {

    public DataHelperTest() {
    }
    /**
     * Test of CopyData method, of class DataHelper.
     */

    @Test
    public void testMSSQLToMSSQL() throws Exception {
        System.out.println("CopyData from SQL Server back to SQLServer different Schema");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("org.sqlite.JDBC");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
//        var targetConnection = DriverManager.getConnection("jdbc:sqlite:c:/dev/test.db");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "Clearing.IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }
    @Test
    public void testMSSQLToSQLite() throws Exception {
        System.out.println("CopyData from SQL Server to SQLite");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("org.sqlite.JDBC");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:sqlite:c:/dev/test.db");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }
    
    @Test
    public void testMSSQLToH2() throws Exception {
        System.out.println("CopyData from SQL Server to H2");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("org.h2.Driver");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:h2:c:/GPS/h2.db;AUTO_SERVER=TRUE;user=sa");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }

    @Test
    public void testMSSQLToHyperSQL() throws Exception {
        System.out.println("CopyData from SQL Server to HyperSQL");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("org.hsqldb.jdbcDriver");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:hsqldb:c:/GPS/hsqldb.db;AUTO_SERVER=TRUE;user=sa");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }
    @Test
    public void testMSSQLToDerby() throws Exception {
        System.out.println("CopyData from SQL Server to Derby");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:derby:c:/GPS/testd;create=true");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }

    @Test
    public void testMSSQLToPG() throws Exception {
        System.out.println("CopyData from SQL Server to PostgreSQL");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("org.postgresql.Driver");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:postgresql://localhost/test?user=paul&password=pass");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }

    @Test
    public void testMSSQLToMySQL() throws Exception {
        System.out.println("CopyData from SQL Server to MySQL");
        
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Class.forName("com.mysql.cj.jdbc.Driver");
        
        var sourceConnection = DriverManager.getConnection("jdbc:sqlserver://aws.database;databaseName=AlexisPTSTest;user=gpspl;password=yW2S6KXdY8");
        var targetConnection = DriverManager.getConnection("jdbc:mysql://paul@localhost/test?useLegacyDatetimeCode=false&serverTimezone=Europe/London");
        var sourceSQL = "SELECT * FROM IPMFileHistory";
        var targetTable = "IPMFileHistory";
        var iRows = DataHelper.CopyData(sourceConnection, targetConnection, sourceSQL, targetTable, true);

        System.out.println(iRows);
        Assert.assertTrue(iRows > 0);
    }
}