xref: /aoo42x/main/connectivity/qa/connectivity/tools/CRMDatabase.java (revision e6b649b5)

/**************************************************************
 *
 * 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 connectivity.tools;

import com.sun.star.beans.PropertyValue;
import com.sun.star.beans.PropertyState;
import com.sun.star.container.ElementExistException;
import com.sun.star.container.NoSuchElementException;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.frame.XController;
import com.sun.star.frame.XModel;
import com.sun.star.io.IOException;
import com.sun.star.lang.IllegalArgumentException;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.lang.XComponent;
import com.sun.star.lang.XMultiServiceFactory;
import com.sun.star.sdb.XSingleSelectQueryComposer;
import com.sun.star.sdb.application.XDatabaseDocumentUI;
import com.sun.star.sdbc.SQLException;
import com.sun.star.sdbcx.XTablesSupplier;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.util.XRefreshable;
import connectivity.tools.sdb.Connection;

/** implements a small Customer Relationship Management database
 *
 *  Not finished, by far. Feel free to add features as you need them.
 */
public class CRMDatabase
{
    private static final String INTEGER = "INTEGER";
    private static final String VARCHAR50 = "VARCHAR(50)";
    private final XMultiServiceFactory        m_orb;
    private final HsqlDatabase                m_database;
    private final DataSource                  m_dataSource;
    private final Connection                  m_connection;

    /** constructs the CRM database
     */
    public CRMDatabase( XMultiServiceFactory _orb, boolean _withUI ) throws Exception
    {
        m_orb = _orb;

        m_database = new HsqlDatabase( m_orb );
        m_dataSource = m_database.getDataSource();

        if ( _withUI )
		{
			final XComponentLoader loader = UnoRuntime.queryInterface( XComponentLoader.class,
				m_orb.createInstance( "com.sun.star.frame.Desktop" ) );
			PropertyValue[] loadArgs = new PropertyValue[] {
				new PropertyValue( "PickListEntry", 0, false, PropertyState.DIRECT_VALUE )
			};
            loader.loadComponentFromURL( m_database.getDocumentURL(), "_blank", 0, loadArgs );
            getDocumentUI().connect();
            m_connection = new Connection( getDocumentUI().getActiveConnection() );
		}
        else
        {
            m_connection = m_database.defaultConnection();
        }

        createTables();
        createQueries();
    }

    /**
     * creates a CRMDatabase from an existing document, given by URL
     * @param _orb
     * @param _existingDocumentURL
     * @throws Exception
     */
    public CRMDatabase( XMultiServiceFactory _orb, final String _existingDocumentURL ) throws Exception
    {
        m_orb = _orb;

        m_database = new HsqlDatabase( m_orb, _existingDocumentURL );
        m_dataSource = m_database.getDataSource();
        m_connection = m_database.defaultConnection();
    }

    // --------------------------------------------------------------------------------------------------------
    /** returns the database document underlying the CRM database
     */
    public final HsqlDatabase getDatabase()
    {
        return m_database;
    }

    // --------------------------------------------------------------------------------------------------------
    /** returns the default connection to the database
     */
    public final Connection getConnection()
    {
        return m_connection;
    }

    // --------------------------------------------------------------------------------------------------------
    public void saveAndClose() throws SQLException, IOException
    {
        XDatabaseDocumentUI ui = getDocumentUI();
        if ( ui != null )
            ui.closeSubComponents();
        m_database.store();
        m_database.closeAndDelete();
    }

    // --------------------------------------------------------------------------------------------------------
    public XDatabaseDocumentUI getDocumentUI()
    {
        XModel docModel = UnoRuntime.queryInterface( XModel.class, m_database.getDatabaseDocument() );
        return UnoRuntime.queryInterface( XDatabaseDocumentUI.class, docModel.getCurrentController() );
    }

    // --------------------------------------------------------------------------------------------------------
    public XController loadSubComponent( final int _objectType, final String _name ) throws IllegalArgumentException, SQLException, NoSuchElementException
    {
        XDatabaseDocumentUI docUI = getDocumentUI();
        if ( !docUI.isConnected() )
            docUI.connect();

        XComponent subComponent = docUI.loadComponent( _objectType, _name, false );
        XController controller = UnoRuntime.queryInterface( XController.class, subComponent );
        if ( controller != null )
            return controller;
        XModel document = UnoRuntime.queryInterface( XModel.class, subComponent );
        return document.getCurrentController();
    }

    // --------------------------------------------------------------------------------------------------------
    private void createTables() throws SQLException
    {
        HsqlTableDescriptor table = new HsqlTableDescriptor( "categories",
            new HsqlColumnDescriptor[] {
                new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ),
                new HsqlColumnDescriptor( "Name",VARCHAR50),
                new HsqlColumnDescriptor( "Description", "VARCHAR(1024)" ),
                new HsqlColumnDescriptor( "Image", "LONGVARBINARY" ) } );
        m_database.createTable( table, true );

        m_database.executeSQL( "INSERT INTO \"categories\" ( \"ID\", \"Name\" ) VALUES ( 1, 'Food' )" );
        m_database.executeSQL( "INSERT INTO \"categories\" ( \"ID\", \"Name\" ) VALUES ( 2, 'Furniture' )" );

        table = new HsqlTableDescriptor( "products",
            new HsqlColumnDescriptor[] {
                new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ),
                new HsqlColumnDescriptor( "Name",VARCHAR50),
                new HsqlColumnDescriptor( "CategoryID",INTEGER, HsqlColumnDescriptor.REQUIRED, "categories", "ID" ) } );
        m_database.createTable( table, true );

        m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 1, 'Oranges', 1 )" );
        m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 2, 'Apples', 1 )" );
        m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 3, 'Pears', 1 )" );
        m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 4, 'Strawberries', 1 )" );

        table = new HsqlTableDescriptor( "customers",
            new HsqlColumnDescriptor[] {
                new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ),
                new HsqlColumnDescriptor( "Name",VARCHAR50),
                new HsqlColumnDescriptor( "Address",VARCHAR50),
                new HsqlColumnDescriptor( "City",VARCHAR50),
                new HsqlColumnDescriptor( "Postal",VARCHAR50),
                new HsqlColumnDescriptor( "Comment","LONGVARCHAR")} );
        m_database.createTable( table, true );

        m_database.executeSQL( "INSERT INTO \"customers\" VALUES(1,'Food, Inc.','Down Under','Melbourne','509','Prefered') " );
        m_database.executeSQL( "INSERT INTO \"customers\" VALUES(2,'Simply Delicious','Down Under','Melbourne','518',null) " );
        m_database.executeSQL( "INSERT INTO \"customers\" VALUES(3,'Pure Health','10 Fish St.','San Francisco','94107',null) " );
        m_database.executeSQL( "INSERT INTO \"customers\" VALUES(4,'Milk And More','Arlington Road 21','Dublin','31021','Good one.') " );

        table = new HsqlTableDescriptor( "orders",
            new HsqlColumnDescriptor[] {
                new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ),
                new HsqlColumnDescriptor( "CustomerID",INTEGER, HsqlColumnDescriptor.REQUIRED, "customers", "ID"  ),
                new HsqlColumnDescriptor( "OrderDate", "DATE" ),
                new HsqlColumnDescriptor( "ShipDate", "DATE" ) } );
        m_database.createTable( table, true );

        m_database.executeSQL( "INSERT INTO \"orders\" (\"ID\", \"CustomerID\", \"OrderDate\") VALUES(1, 1, {D '2009-01-01'})" );
        m_database.executeSQL( "INSERT INTO \"orders\" VALUES(2, 2, {D '2009-01-01'}, {D '2009-01-23'})" );

        table = new HsqlTableDescriptor( "orders_details",
            new HsqlColumnDescriptor[] {
                new HsqlColumnDescriptor( "OrderID",INTEGER, HsqlColumnDescriptor.PRIMARY, "orders", "ID" ),
                new HsqlColumnDescriptor( "ProductID",INTEGER, HsqlColumnDescriptor.PRIMARY, "products", "ID"  ),
                new HsqlColumnDescriptor( "Quantity",INTEGER) } );
        m_database.createTable( table, true );

        m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(1, 1, 100)" );
        m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(1, 2, 100)" );
        m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(2, 2, 2000)" );
        m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(2, 3, 2000)" );
        m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(2, 4, 2000)" );

        // since we created the tables by directly executing the SQL statements, we need to refresh
        // the tables container
        m_connection.refreshTables();
    }

    // --------------------------------------------------------------------------------------------------------
    private void validateUnparseable()
    {
    /*
        // The "unparseable" query should be indeed be unparseable by OOo (though a valid HSQL query)
        XSingleSelectQueryComposer composer;
        QueryDefinition unparseableQuery;
        try
        {
            final XMultiServiceFactory factory = UnoRuntime.queryInterface(
                    XMultiServiceFactory.class, m_database.defaultConnection().getXConnection() );
            composer = UnoRuntime.queryInterface(
                    XSingleSelectQueryComposer.class, factory.createInstance( "com.sun.star.sdb.SingleSelectQueryComposer" ) );
            unparseableQuery = m_dataSource.getQueryDefinition( "unparseable" );
        }
        catch( Exception e )
        {
            throw new RuntimeException( "caught an unexpected exception: " + e.getMessage() );
        }

        boolean caughtExpected = false;
        try
        {
            composer.setQuery( unparseableQuery.getCommand() );
        }
        catch (WrappedTargetException e) { }
        catch( SQLException e )
        {
            caughtExpected = true;
        }

        if ( !caughtExpected )
            throw new RuntimeException( "Somebody improved the parser! This is bad :), since we need an unparsable query here!" );
            */
    }

    // --------------------------------------------------------------------------------------------------------
    private void createQueries() throws ElementExistException, WrappedTargetException, com.sun.star.lang.IllegalArgumentException
    {
        m_database.getDataSource().createQuery(
            "all orders",
            "SELECT \"orders\".\"ID\" AS \"Order No.\", " +
                    "\"customers\".\"Name\" AS \"Customer Name\", " +
                    "\"orders\".\"OrderDate\" AS \"Order Date\", " +
                    "\"orders\".\"ShipDate\" AS \"Ship Date\", " +
                    "\"orders_details\".\"Quantity\", " +
                    "\"products\".\"Name\" AS \"Product Name\" " +
            "FROM \"orders_details\" AS \"orders_details\", " +
                  "\"orders\" AS \"orders\", " +
                  "\"products\" AS \"products\", " +
                  "\"customers\" AS \"customers\" " +
            "WHERE  ( \"orders_details\".\"OrderID\" = \"orders\".\"ID\" " +
                 "AND \"orders_details\".\"ProductID\" = \"products\".\"ID\" " +
                 "AND \"orders\".\"CustomerID\" = \"customers\".\"ID\" )"
        );

        m_database.getDataSource().createQuery(
            "unshipped orders",
            "SELECT * " +
            "FROM \"all orders\"" +
            "WHERE ( \"ShipDate\" IS NULL )"
        );

        m_database.getDataSource().createQuery( "parseable", "SELECT * FROM \"customers\"" );
        m_database.getDataSource().createQuery( "parseable native", "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VIEWS", false );
/*
        m_database.getDataSource().createQuery( "unparseable",
            "SELECT {fn DAYOFMONTH ('2001-01-01')} AS \"ID_VARCHAR\" FROM \"products\"", false );
*/
        validateUnparseable();
    }
}