#120525# - [QA TASK]Write DB test cases via UNO API

Patch by: Wang Li Feng <phoenix.wanglf@gmail.com>
Review by: Liu Zhe <aliuzhe@gmail.com>

3 files changed, 537 insertions, 0 deletions

diff --git a/test/testuno/source/testcase/uno/db/DBAccess.java b/test/testuno/source/testcase/uno/db/DBAccess.java
new file mode 100644
index 000000000000..019db4b01d63
--- /dev/null
+++ b/test/testuno/source/testcase/uno/db/DBAccess.java
@@ -0,0 +1,162 @@
+/**************************************************************
+ *
+ * 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 testcase.uno.db;
+
+import com.sun.star.beans.PropertyValue;
+import com.sun.star.container.XNameAccess;
+import com.sun.star.frame.FrameSearchFlag;
+import com.sun.star.frame.XComponentLoader;
+import com.sun.star.frame.XModel;
+import com.sun.star.frame.XStorable;
+import com.sun.star.lang.XComponent;
+import com.sun.star.lang.XMultiServiceFactory;
+import com.sun.star.sdb.XOfficeDatabaseDocument;
+import com.sun.star.sdb.application.XDatabaseDocumentUI;
+import com.sun.star.sdbcx.XTablesSupplier;
+import com.sun.star.uno.Exception;
+import com.sun.star.uno.UnoRuntime;
+
+import testlib.uno.HsqlColumnDescriptor;
+import testlib.uno.DBUtil;
+
+import java.io.IOException;
+
+// ---------- junit imports -----------------
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
+import org.openoffice.test.common.FileUtil;
+import org.openoffice.test.common.Testspace;
+import org.openoffice.test.uno.UnoApp;
+
+import static org.junit.Assert.*;
+
+/**
+ * test case for Base's application UI
+ */
+public class DBAccess {
+    UnoApp app = new UnoApp();
+    private XOfficeDatabaseDocument m_databaseDocument;
+    private XDatabaseDocumentUI m_documentUI;
+
+//  public DBAccess() {
+//      super();
+//  }
+
+
+    @Before
+    public void before() throws java.lang.Exception {
+        app.start();
+        String a = null;
+        switchToDocument(a);
+    }
+
+    @After
+    public void after() throws java.lang.Exception {
+        closeDocument();
+        app.close();
+    }
+
+    private void closeDocument() {
+        DBUtil.close();
+        m_databaseDocument = null;
+        m_documentUI = null;
+
+    }
+
+    private void switchToDocument(String _documentURL)
+            throws java.lang.Exception {
+        // close previous database document
+        closeDocument();
+
+        if (_documentURL == null) {
+            DBUtil.createNewDocument(getMSF());
+        } else {
+            DBUtil.loadNewDocument(getMSF(), _documentURL);
+        }
+        m_databaseDocument = DBUtil.getDatabaseDocument();
+
+    }
+
+
+    @Test
+    public void testSaveAs() throws Exception, IOException, java.lang.Exception {
+
+        m_databaseDocument = saveAndReloadDoc(m_databaseDocument, "", "odb");
+        XModel docModel = UnoRuntime.queryInterface(XModel.class,
+                m_databaseDocument);
+        m_documentUI = UnoRuntime.queryInterface(XDatabaseDocumentUI.class,
+                docModel.getCurrentController());
+        m_documentUI.connect();
+        assertTrue("could not connect to " + DBUtil.getDocumentURL(),
+                m_documentUI.isConnected());
+
+    }
+
+    @Test
+    public void testCreateTable() throws java.lang.Exception {
+        // create a table in the database
+        DBUtil.createTable("test", new HsqlColumnDescriptor[] {
+                new HsqlColumnDescriptor("a", "VARCHAR(50)"),
+                new HsqlColumnDescriptor("b", "VARCHAR(50)"),
+                new HsqlColumnDescriptor("c", "VARCHAR(50)") });
+        switchToDocument(DBUtil.getDocumentURL());
+        // ---save and reload database document
+        m_databaseDocument = saveAndReloadDoc(m_databaseDocument, "", "odb");
+
+        XModel docModel = UnoRuntime.queryInterface(XModel.class,
+                m_databaseDocument);
+        m_documentUI = UnoRuntime.queryInterface(XDatabaseDocumentUI.class,
+                docModel.getCurrentController());
+        m_documentUI.connect();
+        XTablesSupplier suppTables = UnoRuntime.queryInterface(
+                XTablesSupplier.class, m_documentUI.getActiveConnection());
+        XNameAccess tables = suppTables.getTables();
+        assertTrue("the newly created table has not been written",
+                tables.hasByName("test"));
+    }
+
+    protected XMultiServiceFactory getMSF() {
+        final XMultiServiceFactory xMSF1 = UnoRuntime.queryInterface(
+                XMultiServiceFactory.class, app.getComponentContext()
+                        .getServiceManager());
+        return xMSF1;
+    }
+
+    private XOfficeDatabaseDocument saveAndReloadDoc(
+            XOfficeDatabaseDocument m_databaseDocument2, String sFilter,
+            String sExtension) throws java.lang.Exception {
+        String filePath = Testspace.getPath("tmp/basetest." + sExtension);
+        PropertyValue[] aStoreProperties = new PropertyValue[2];
+        aStoreProperties[0] = new PropertyValue();
+        aStoreProperties[1] = new PropertyValue();
+        aStoreProperties[0].Name = "Override";
+        aStoreProperties[0].Value = true;
+        aStoreProperties[1].Name = "FilterName";
+        XStorable xStorable = (XStorable) UnoRuntime.queryInterface(
+                XStorable.class, m_databaseDocument2);
+        xStorable.storeToURL(FileUtil.getUrl(filePath), aStoreProperties);
+
+        return UnoRuntime.queryInterface(XOfficeDatabaseDocument.class,
+                app.loadDocument(filePath));
+    }
+}
diff --git a/test/testuno/source/testlib/uno/DBUtil.java b/test/testuno/source/testlib/uno/DBUtil.java
new file mode 100644
index 000000000000..2de5a4f30bc4
--- /dev/null
+++ b/test/testuno/source/testlib/uno/DBUtil.java
@@ -0,0 +1,295 @@
+/**************************************************************
+ *
+ * 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 = UnoRuntime.queryInterface(
+                XNameAccess.class,
+                m_orb.createInstance("com.sun.star.sdb.DatabaseContext"));
+        final XDocumentDataSource dataSource = 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 = 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 = 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;
+    }
+}
diff --git a/test/testuno/source/testlib/uno/HsqlColumnDescriptor.java b/test/testuno/source/testlib/uno/HsqlColumnDescriptor.java
new file mode 100644
index 000000000000..1373abbd55a4
--- /dev/null
+++ b/test/testuno/source/testlib/uno/HsqlColumnDescriptor.java
@@ -0,0 +1,80 @@
+/**************************************************************
+ *
+ * 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;
+
+/** is a very simply and rudimentary descriptor of table columns, for creating HSQLDB tables
+ */
+public class HsqlColumnDescriptor
+{
+    private String Name;
+    private String TypeName;
+    private boolean Required;
+    private boolean PrimaryKey;
+    private String ForeignTable;
+    private String ForeignColumn;
+
+    public final String getName() { return Name; }
+    public final String getTypeName() { return TypeName; }
+    public final boolean isRequired() { return Required; }
+    public final boolean isPrimaryKey() { return PrimaryKey; }
+
+    public final boolean isForeignKey() { return ( ForeignTable.length() != 0 ) && ( ForeignColumn.length() != 0 ); }
+    public final String getForeignTable() { return ForeignTable; }
+    public final String getForeignColumn() { return ForeignColumn; }
+
+    /// determines that a column is required, i.e. not nullable
+    public final static int REQUIRED    = 1;
+    /// determines that a column is part of the primary key of its table
+    public final static int PRIMARY     = 2;
+
+    public HsqlColumnDescriptor( String _Name, String _TypeName )
+    {
+        Name = _Name;
+        TypeName = _TypeName;
+        Required = false;
+        PrimaryKey = false;
+        ForeignTable = "";
+        ForeignColumn = "";
+    }
+
+    public HsqlColumnDescriptor( String _Name, String _TypeName, int _Flags )
+    {
+        Name = _Name;
+        TypeName = _TypeName;
+        Required = ( _Flags & REQUIRED ) != 0;
+        PrimaryKey = ( _Flags & PRIMARY ) != 0;
+        ForeignTable = "";
+        ForeignColumn = "";
+    }
+
+    public HsqlColumnDescriptor( String _Name, String _TypeName, int _Flags, String _ForeignTable, String _ForeignColumn )
+    {
+        Name = _Name;
+        TypeName = _TypeName;
+        Required = ( _Flags & REQUIRED ) != 0;
+        PrimaryKey = ( _Flags & PRIMARY ) != 0;
+        ForeignTable = _ForeignTable;
+        ForeignColumn = _ForeignColumn;
+    }
+};