org.kawanfw.test.api.client.DatabaseMetaDataTest.java Source code

Java tutorial

Introduction

Here is the source code for org.kawanfw.test.api.client.DatabaseMetaDataTest.java

Source

/*
 * This file is part of AceQL. 
 * AceQL: Remote JDBC access over HTTP.                                     
 * Copyright (C) 2015,  KawanSoft SAS
 * (http://www.kawansoft.com). All rights reserved.                                
 *                                                                               
 * AceQL is free software; you can redistribute it and/or                 
 * modify it under the terms of the GNU Lesser General Public                    
 * License as published by the Free Software Foundation; either                  
 * version 2.1 of the License, or (at your option) any later version.            
 *                                                                               
 * AceQL 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             
 * Lesser General Public License for more details.                               
 *                                                                               
 * You should have received a copy of the GNU Lesser General Public              
 * License along with this library; if not, write to the Free Software           
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  
 * 02110-1301  USA
 *
 * Any modifications to this file must keep this entire header
 * intact.
 */
package org.kawanfw.test.api.client;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import org.apache.commons.io.IOUtils;
import org.junit.Assert;
import org.junit.Test;
import org.kawanfw.commons.util.FrameworkFileUtil;
import org.kawanfw.commons.util.FrameworkSystemUtil;
import org.kawanfw.sql.api.client.RemoteConnection;
import org.kawanfw.sql.api.util.ResultSetPrinter;
import org.kawanfw.sql.api.util.SqlUtil;
import org.kawanfw.test.parms.ConnectionLoader;
import org.kawanfw.test.parms.SqlTestParms;
import org.kawanfw.test.util.MessageDisplayer;

public class DatabaseMetaDataTest {

    public static void main(String[] args) throws Exception {
        SqlTestParms.ACEQL_URL = SqlTestParms.ACEQL_URL_TOMCAT_EMBEDED_LOCALHOST_SSL_2;
        new DatabaseMetaDataTest().test();
    }

    @Test
    public void test() throws Exception {
        Connection connection = null;
        try {
            connection = ConnectionLoader.getAceqlConnection();
            test(connection);

        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    public void test(Connection connection) throws Exception {
        MessageDisplayer.initClassDisplay(this.getClass().getSimpleName());

        DatabaseMetaData databaseMetaData = connection.getMetaData();

        // Test that getMetaData() will return value from cache
        databaseMetaData = connection.getMetaData();

        if (connection instanceof RemoteConnection) {
            MessageDisplayer.display("Java Version : " + System.getProperty("java.version"));
            MessageDisplayer.display("AceQL Version: " + ((RemoteConnection) connection).getVersion());
            MessageDisplayer.display("AceQL Url    : " + ((RemoteConnection) connection).getUrl());
            MessageDisplayer.display("");
        }

        if (connection instanceof RemoteConnection) {
            MessageDisplayer.display("((RemoteConnection)connection).clone();");
            Connection connection2 = ((RemoteConnection) connection).clone();
            @SuppressWarnings("unused")
            DatabaseMetaData databaseMetaData2 = connection2.getMetaData();
            connection2.close();
        }

        MessageDisplayer.display("General info (no Assert done):");

        MessageDisplayer.display("connection.getCatalog()                     : " + connection.getCatalog());

        MessageDisplayer.display(
                "databaseMetaData.getDatabaseProductName()   : " + databaseMetaData.getDatabaseProductName());
        MessageDisplayer.display(
                "databaseMetaData.getDatabaseProductVersion(): " + databaseMetaData.getDatabaseProductVersion());
        MessageDisplayer.display(
                "databaseMetaData.getDatabaseMajorVersion()  : " + databaseMetaData.getDatabaseMajorVersion());
        MessageDisplayer.display(
                "databaseMetaData.getDatabaseMinorVersion()  : " + databaseMetaData.getDatabaseMinorVersion());
        MessageDisplayer.display(
                "databaseMetaData.allProceduresAreCallable() : " + databaseMetaData.allProceduresAreCallable());
        // SystemOutHandle.display(DatabaseMetaData.bestRowSession);
        MessageDisplayer.display("");

        // SystemOutHandle.display(databaseMetaData.autoCommitFailureClosesAllResultSets());

        MessageDisplayer.display("databaseMetaData.getCatalogTerm(): " + databaseMetaData.getCatalogTerm());

        try {

            MessageDisplayer.display(
                    "databaseMetaData.supportsStoredProcedures(): " + databaseMetaData.supportsStoredProcedures());

            MessageDisplayer.display("databaseMetaData.supportsStoredFunctionsUsingCallSyntax(): "
                    + databaseMetaData.supportsStoredFunctionsUsingCallSyntax());

        } catch (Throwable e) {
            MessageDisplayer.display(e.toString());
        }

        MessageDisplayer.display("connection.getAutoCommit(): " + connection.getAutoCommit());

        MessageDisplayer.display("databaseMetaData.getDefaultTransactionIsolation()    : "
                + databaseMetaData.getDefaultTransactionIsolation());

        MessageDisplayer
                .display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_READ_UNCOMMITTED): "
                        + databaseMetaData
                                .supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED));

        MessageDisplayer.display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_READ_COMMITTED): "
                + databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED));

        MessageDisplayer.display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_REPEATABLE_READ): "
                + databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ));

        MessageDisplayer.display("databaseMetaData.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE): "
                + databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE));

        MessageDisplayer
                .display("databaseMetaData.supportsBatchUpdates()    : " + databaseMetaData.supportsBatchUpdates());
        MessageDisplayer
                .display("databaseMetaData.supportsSavepoints()      : " + databaseMetaData.supportsSavepoints());
        MessageDisplayer.display(
                "databaseMetaData.supportsGetGeneratedKeys(): " + databaseMetaData.supportsGetGeneratedKeys());

        if (!new SqlUtil(connection).isTeradata() && !new SqlUtil(connection).isInformix()) {
            Assert.assertEquals(true,
                    databaseMetaData.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED));
        }

        Assert.assertEquals("databaseMetaData.supportsBatchUpdates()", true,
                databaseMetaData.supportsBatchUpdates());

        if (!new SqlUtil(connection).isSQLAnywhere() && !new SqlUtil(connection).isAccess()) {
            Assert.assertEquals("databaseMetaData.supportsGetGeneratedKeys()", true,
                    databaseMetaData.supportsGetGeneratedKeys());
        }
        // Informix does not support savepoints
        SqlUtil sqlUtil = new SqlUtil(connection);
        if (!sqlUtil.isInformix() && !sqlUtil.isTeradata() && !new SqlUtil(connection).isAccess()) {
            Assert.assertEquals(true, databaseMetaData.supportsSavepoints());
        }

        MessageDisplayer.display("");

        String catalog = null;
        String schema = null;
        String table = "customer";

        // Table name must be uppercase for Oracle & DB2, lowercase for MySQL
        // and PostgreSQL
        if (new SqlUtil(connection).isOracle() || new SqlUtil(connection).isHSQLDB()
                || new SqlUtil(connection).isDB2()) {
            table = table.toUpperCase();
        }

        ResultSet rs = null;

        if (!new SqlUtil(connection).isAccess()) {

            rs = databaseMetaData.getPrimaryKeys(catalog, schema, table);

            printResultSet(rs);

            boolean rsNext = false;

            while (rs.next()) {
                rsNext = true;
                String keyColumnName = rs.getString("COLUMN_NAME");
                MessageDisplayer.display("Primary Key is: " + keyColumnName + " for Table: " + table);
                Assert.assertEquals("customer_id", keyColumnName.toLowerCase());
            }

            if (!new SqlUtil(connection).isH2()) {
                Assert.assertEquals(true, rsNext);
            }

            rs.close();
        }

        // boolean returnNow = true;
        // if (returnNow) return;

        String[] types = { "TABLE", "VIEW" };
        rs = databaseMetaData.getTables(null, null, null, types);

        Set<String> tablesSet = new HashSet<String>();

        Set<String> ourTables = new HashSet<String>();
        ourTables.add("banned_usernames");
        ourTables.add("customer");
        ourTables.add("customer_auto");
        ourTables.add("orderlog");
        ourTables.add("user_login");

        MessageDisplayer.display("");
        while (rs.next()) {
            table = rs.getString("TABLE_NAME");

            if (ourTables.contains(table.toLowerCase())) {
                MessageDisplayer.display("Table: " + table);
            }

            tablesSet.add(table.toLowerCase());
        }

        // printResultSet(rs);

        testTable("banned_usernames", tablesSet);
        testTable("customer", tablesSet);
        testTable("orderlog", tablesSet);
        testTable("user_login", tablesSet);

        rs.close();
    }

    /**
     * Create our own temp file
     * 
     * @return the tempfile to create
     */
    private synchronized File createAceqlTempFile() {
        String unique = FrameworkFileUtil.getUniqueId();
        String tempDir = FrameworkFileUtil.getKawansoftTempDir();
        String tempFile = tempDir + File.separator + "print-stream-" + unique + ".kawanfw.txt";

        return new File(tempFile);
    }

    /**
     * Print a ResultSet and reinit it
     * 
     * @param rs
     * @throws SQLException
     */
    private void printResultSet(ResultSet rs) throws SQLException {

        MessageDisplayer.display();

        if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
            System.out.println("Impossible to print the ResultSet: TYPE_FORWARD_ONLY");
            MessageDisplayer.display();
            return;
        }

        ResultSetPrinter resultSetPrinter;
        rs.beforeFirst();

        // Special treatment for Androd
        if (FrameworkSystemUtil.isAndroid()) {

            File file = createAceqlTempFile();

            // MessageDisplayer.display(file.toString());

            PrintStream ps = null;
            BufferedReader reader = null;

            try {

                ps = new PrintStream(new BufferedOutputStream(new FileOutputStream(file)));

                resultSetPrinter = new ResultSetPrinter(rs, ps, false);
                resultSetPrinter.print();

                ps.close();

                // Or, read back the file and print it with
                reader = new BufferedReader(new FileReader(file));

                String line = null;
                while ((line = reader.readLine()) != null) {
                    MessageDisplayer.display(line);
                }

            } catch (IOException ioe) {
                throw new SQLException(ioe);
            } finally {
                IOUtils.closeQuietly(reader);
                IOUtils.closeQuietly(ps);
                file.delete();
            }

        } else {
            resultSetPrinter = new ResultSetPrinter(rs, System.out, false);
            resultSetPrinter.print();
        }

        rs.beforeFirst();

        MessageDisplayer.display();

    }

    /**
     * Test that a table is in the set
     * 
     * @param table
     *            the table
     * @param tablesSet
     *            the tables set
     */
    private void testTable(String table, Set<String> tablesSet) {
        Assert.assertTrue("DatabaseMedata does not contain table " + table, tablesSet.contains(table));
    }

}