testlib/uno/DBUtil.java

/**************************************************************
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you 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.
 *
 *************************************************************/

package testlib.uno;

import com.sun.star.beans.PropertyValue;
import com.sun.star.beans.PropertyState;
import com.sun.star.beans.XPropertySet;
import com.sun.star.container.ElementExistException;
import com.sun.star.container.XNameAccess;
import com.sun.star.frame.XStorable;
import com.sun.star.lang.XMultiServiceFactory;
import com.sun.star.sdb.XDocumentDataSource;
import com.sun.star.sdb.XOfficeDatabaseDocument;
import com.sun.star.sdbc.SQLException;
import com.sun.star.sdbc.XCloseable;
import com.sun.star.sdbc.XConnection;
import com.sun.star.sdbc.XDataSource;
import com.sun.star.sdbc.XStatement;
import com.sun.star.sdbcx.XAppend;
import com.sun.star.sdbcx.XTablesSupplier;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.util.CloseVetoException;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.io.File;

import org.openoffice.test.common.FileUtil;


public class DBUtil {
    // the service factory
    protected static XMultiServiceFactory m_orb;
    // the URL of the temporary file used for the database document
    protected static String m_databaseDocumentFile;
    // the database document
    protected static XOfficeDatabaseDocument m_databaseDocument;
    // the data source belonging to the database document
    protected static XDataSource m_dataSource;
    // the default connection
    protected static XConnection m_connection;


    static public void createNewDocument(final XMultiServiceFactory orb)
            throws Exception {
        m_orb = orb;
        createDBDocument();
    }

    static public void loadNewDocument(final XMultiServiceFactory orb,
            final String _existingDocumentURL) throws Exception {
        m_orb = orb;
        getDocument(_existingDocumentURL);
    }

    /**
     * creates an empty database document in a temporary location
     */
    public static void createDBDocument() throws Exception {
        final File documentFile = File.createTempFile("testdb", ".odb");
        if (documentFile.exists())
            documentFile.delete();
        m_databaseDocumentFile = FileUtil.getUrl(documentFile);
        m_databaseDocument = (XOfficeDatabaseDocument) UnoRuntime
                .queryInterface(
                        XOfficeDatabaseDocument.class,
                        m_orb.createInstance("com.sun.star.sdb.OfficeDatabaseDocument"));
        m_dataSource = m_databaseDocument.getDataSource();

        final XPropertySet dsProperties = (XPropertySet) UnoRuntime
                .queryInterface(XPropertySet.class,
                        m_databaseDocument.getDataSource());
        dsProperties.setPropertyValue("URL", "sdbc:embedded:hsqldb");

        final XStorable storable = (XStorable) UnoRuntime.queryInterface(
                XStorable.class, m_databaseDocument);
        storable.storeAsURL(m_databaseDocumentFile,
                new PropertyValue[] { new PropertyValue("PickListEntry", 0,
                        false, PropertyState.DIRECT_VALUE) });
    }


    public static void getDocument(final String _docURL) throws Exception {
        m_databaseDocumentFile = _docURL;

        final XNameAccess dbContext = (XNameAccess) UnoRuntime.queryInterface(
                XNameAccess.class,
                m_orb.createInstance("com.sun.star.sdb.DatabaseContext"));
        final XDocumentDataSource dataSource = (XDocumentDataSource) UnoRuntime.queryInterface(
                XDocumentDataSource.class, dbContext.getByName(_docURL));

        m_databaseDocument = dataSource.getDatabaseDocument();
        m_dataSource = m_databaseDocument.getDataSource();
    }

    /**
     * drops the table with a given name
     *
     * @param _name
     *            the name of the table to drop
     * @param _ifExists
     *            TRUE if it should be dropped only when it exists.
     */
    static public void dropTable(final String _name, final boolean _ifExists)
            throws SQLException {
        final StringBuffer dropStatement = new StringBuffer("DROP TABLE \"");
        dropStatement.append(_name);
        if (_ifExists) {
            dropStatement.append("\" IF EXISTS");
        }
        executeSQL(dropStatement.toString());
    }

    static public void createTable(String _name,
            HsqlColumnDescriptor[] _columns, final boolean _dropIfExists)
            throws SQLException {
        if (_dropIfExists) {
            dropTable(_name, true);
        }
        createTable(_name, _columns);
    }

    /**
     * creates a table
     */
    static public void createTable(String _name, HsqlColumnDescriptor[] _columns)
            throws SQLException {
        StringBuffer createStatement = new StringBuffer(
                "CREATE CACHED TABLE \"");
        createStatement.append(_name);
        createStatement.append("\" ( ");

        String primaryKeyList = "";

        final HashMap foreignKeys = new HashMap();
        final HashMap foreignKeyRefs = new HashMap();

        final HsqlColumnDescriptor[] columns = _columns;
        for (int i = 0; i < columns.length; ++i) {
            if (i > 0) {
                createStatement.append(", ");
            }

            createStatement.append("\"" + columns[i].getName());
            createStatement.append("\" " + columns[i].getTypeName());

            if (columns[i].isRequired()) {
                createStatement.append(" NOT NULL");
            }

            if (columns[i].isPrimaryKey()) {
                if (primaryKeyList.length() > 0) {
                    primaryKeyList += ", ";
                }
                primaryKeyList += "\"" + columns[i].getName() + "\"";
            }

            if (columns[i].isForeignKey()) {
                final String foreignTable = columns[i].getForeignTable();

                String foreignKeysForTable = foreignKeys
                        .containsKey(foreignTable) ? (String) foreignKeys
                        .get(foreignTable) : "";
                if (foreignKeysForTable.length() > 0) {
                    foreignKeysForTable += ", ";
                }
                foreignKeysForTable += "\"" + columns[i].getName() + "\"";
                foreignKeys.put(foreignTable, foreignKeysForTable);

                final StringBuffer foreignKeyRefsForTable = new StringBuffer(
                        foreignKeyRefs.containsKey(foreignTable) ? (String) foreignKeyRefs
                                .get(foreignTable) : "");
                if (foreignKeyRefsForTable.length() > 0) {
                    foreignKeyRefsForTable.append(", ");
                }
                foreignKeyRefsForTable.append("\""
                        + columns[i].getForeignColumn() + "\"");
                foreignKeyRefs.put(foreignTable,
                        foreignKeyRefsForTable.toString());
            }
        }

        if (primaryKeyList.length() > 0) {
            createStatement.append(", PRIMARY KEY (");
            createStatement.append(primaryKeyList);
            createStatement.append(')');
        }

        final Set foreignKeyTables = foreignKeys.keySet();
        for (final Iterator foreignKey = foreignKeyTables.iterator(); foreignKey
                .hasNext();) {
            final String foreignTable = (String) foreignKey.next();

            createStatement.append(", FOREIGN KEY (");
            createStatement.append((String) foreignKeys.get(foreignTable));
            createStatement.append(") REFERENCES \"");
            createStatement.append(foreignTable);
            createStatement.append("\"(");
            createStatement.append((String) foreignKeyRefs.get(foreignTable));
            createStatement.append(')');
        }

        createStatement.append(')');

        // System.err.println( createStatement );
        executeSQL(createStatement.toString());
    }


    /**
     * executes the given SQL statement via the defaultConnection
     */
    static public void executeSQL(final String statementString)
            throws SQLException {
        final XStatement statement = defaultConnection().createStatement();
        statement.execute(statementString);
    }

    /**
     * returns a connection to the database
     *
     * Multiple calls to this method return the same connection. The
     * DbaseDatabase object keeps the ownership of the connection, so you don't
     * need to (and should not) dispose/close it.
     */
    static public XConnection defaultConnection() throws SQLException {
        if (m_connection == null)
            m_connection = m_databaseDocument.getDataSource().getConnection("",
                    "");

        return m_connection;
    }

    /**
     * closes the database document
     *
     * Any CloseVetoExceptions fired by third parties are ignored, and any
     * reference to the database document is released.
     */
    static public void close() {
        // close connection
        final XCloseable closeConn = (XCloseable) UnoRuntime.queryInterface(
                XCloseable.class, m_connection != null ? m_connection : null);
        if (closeConn != null) {
            try {
                closeConn.close();
            } catch (SQLException e) {
            }
        }
        m_connection = null;

        // close document
        final com.sun.star.util.XCloseable closeDoc = (com.sun.star.util.XCloseable) UnoRuntime
                .queryInterface(com.sun.star.util.XCloseable.class,
                        m_databaseDocument);
        if (closeDoc != null) {
            try {
                closeDoc.close(true);
            } catch (CloseVetoException e) {
            }
        }
        m_databaseDocument = null;
    }

    /**
     * returns the underlying database document
     */
    static public XOfficeDatabaseDocument getDatabaseDocument() {
        return m_databaseDocument;
    }

    static public String getDocumentURL() {
        return m_databaseDocumentFile;
    }
}