/************************************************************************* * * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. * * Copyright 2000, 2010 Oracle and/or its affiliates. * * OpenOffice.org - a multi-platform office productivity suite * * This file is part of OpenOffice.org. * * OpenOffice.org is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License version 3 * only, as published by the Free Software Foundation. * * OpenOffice.org 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 version 3 for more details * (a copy is included in the LICENSE file that accompanied this code). * * You should have received a copy of the GNU Lesser General Public License * version 3 along with OpenOffice.org. If not, see * * for a copy of the LGPLv3 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 Exceptio */ 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(); } }