testtexttable.java

来自「纯Java的数据库」· Java 代码 · 共 460 行 · 第 1/2 页

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.test;

import java.io.IOException;
import java.io.PrintStream;
import java.sql.SQLException;

import org.hsqldb.lib.FileUtil;

/** test various text table features
 *
 * @author [e-mail]
 */
public class TestTextTable extends TestBase
{
    java.sql.Statement      m_statement;
    java.sql.Connection     m_connection;

    private class TextTableDescriptor
    {
        private String      m_name;
        private String      m_columnSpec;
        private String      m_separator;
        private String      m_separatorSpec;
        private Object[][]  m_data;

        public TextTableDescriptor( String name, String columnSpec, String separator, String separatorSpec, Object[][] data )
        {
            m_name = name;
            m_columnSpec = columnSpec;
            m_separator = separator;
            m_separatorSpec = separatorSpec;
            m_data = data;
        }

        public final String     getName() { return m_name; }
        public final String     getColumnSpec() { return m_columnSpec; }
        public final String     getSeparator() { return m_separator; }
        public final String     getSeparatorSpec() { return m_separatorSpec; }
        public final Object[][] getData() { return m_data; }

        public final Object[][] appendRowData(Object[] rowData) {
            Object[][] newData = new Object[m_data.length+1][rowData.length];
            for (int row=0; row<m_data.length; ++row)
                newData[row] = m_data[row];
            newData[m_data.length] = rowData;
            m_data = newData;
            return m_data;
        }

        /** creates a text file as described by this instance
         */
        private void createTextFile() {

            PrintStream textFile = null;
            try {
                String completeFileName = m_name + ".csv";
                textFile = new PrintStream(FileUtil.getDefaultInstance().openOutputStreamElement(completeFileName));
                new java.io.File(completeFileName).deleteOnExit();
            } catch (IOException ex) {
                fail(ex.toString());
            }

            for (int row=0; row<m_data.length; ++row) {

                StringBuffer buf = new StringBuffer();
                int colCount = m_data[row].length;
                for (int col=0; col<colCount; ++col) {

                    buf.append(m_data[row][col].toString());
                    if (col+1 != colCount)
                        buf.append(m_separator);
                }
                textFile.println(buf.toString());
            }
            textFile.close();
        }

        private String getDataSourceSpec()
        {
            return m_name + ".csv;encoding=UTF-8;fs=" + m_separatorSpec;
        }

        private void createTable( java.sql.Connection connection ) throws SQLException
        {
            String createTable = "DROP TABLE \"" + m_name + "\" IF EXISTS;";
            createTable += "CREATE TEXT TABLE \"" + m_name + "\" ( " + m_columnSpec + " );";
            connection.createStatement().execute(createTable);

            boolean test = isReadOnly( m_name );

            String setTableSource = "SET TABLE \"" + m_name + "\" SOURCE \"" + getDataSourceSpec() + "\"";
            connection.createStatement().execute(setTableSource);
        }
    };

    TextTableDescriptor     m_products = new TextTableDescriptor(
                                            "products",
                                            "ID INTEGER PRIMARY KEY, \"name\" VARCHAR(20)",
                                            "\t",
                                            "\\t",
                                            new Object[][] {
                                                new Object[] { new Integer( 1 ), "Apples" },
                                                new Object[] { new Integer( 2 ), "Oranges" }
                                            } );

    TextTableDescriptor     m_customers = new TextTableDescriptor(
                                            "customers",
                                            "ID INTEGER PRIMARY KEY,"
                                                + "\"name\" VARCHAR(50),"
                                                + "\"address\" VARCHAR(50),"
                                                + "\"city\" VARCHAR(50),"
                                                + "\"postal\" VARCHAR(50)",
                                            ";",
                                            "\\semi",
                                            new Object[][] {
                                                new Object[] { new Integer( 1 ), "Food, Inc.", "Down Under", "Melbourne", "509" },
                                                new Object[] { new Integer( 2 ), "Simply Delicious", "Down Under", "Melbourne", "518" },
                                                new Object[] { new Integer( 3 ), "Pure Health", "10 Fish St.", "San Francisco", "94107" }
                                            } );

    /** Creates a new instance of TestTextTable */
    public TestTextTable( String testName ) {
        super(testName,null,false);
    }

    /** sets up all text files for the test database
     */
    private void setupTextFiles() {

        m_products.createTextFile();
        m_customers.createTextFile();
    }

    /** creates the database tables needed for the test
     */
    private void setupDatabase() {

        try {
            m_connection = newConnection();
            m_statement  = m_connection.createStatement();

            m_products.createTable(m_connection);
            m_customers.createTable(m_connection);
        } catch (SQLException ex) {
            fail(ex.toString());
        }
    }

    public void setUp() {
        super.setUp();
        setupTextFiles();
        setupDatabase();
    }

    protected void tearDown() {
        executeStatement("SHUTDOWN");
        super.tearDown();
    }

    /** returns the data source definition for a given text table
     */
    private String getDataSourceSpec( String tableName )
    {
        String spec = null;
        try {
            java.sql.ResultSet results = m_statement.executeQuery(
                    "SELECT DATA_SOURCE_DEFINTION FROM INFORMATION_SCHEMA.SYSTEM_TEXTTABLES "
                +   "WHERE TABLE_NAME='" + tableName + "'" );
            results.next();
            spec = results.getString(1);
        } catch (SQLException ex) {
            fail("getDataSourceSpec(" + tableName + ") failed: " + ex.toString());
        }
        return spec;
    }

    /** determines whether a given table is currently read-only
     */
    private boolean isReadOnly( String tableName ) {
        boolean isReadOnly = true;
        try {
            java.sql.ResultSet systemTables = m_statement.executeQuery(
                    "SELECT READ_ONLY FROM INFORMATION_SCHEMA.SYSTEM_TABLES "
                +   "WHERE TABLE_NAME='" + m_products.getName() + "'");
            systemTables.next();
            isReadOnly = systemTables.getBoolean(1);
        } catch (SQLException ex) {
            fail("isReadOnly(" + tableName + ") failed: " + ex.toString());
        }
        return isReadOnly;
    }

    /** checks different field separators
     */
    private void checkSeparators() {

        String[][] separators = new String[][] {
            // special separators
            new String[] { ";", "\\semi" },