1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2000, 2010 Oracle and/or its affiliates. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * This file is part of OpenOffice.org. 10 * 11 * OpenOffice.org is free software: you can redistribute it and/or modify 12 * it under the terms of the GNU Lesser General Public License version 3 13 * only, as published by the Free Software Foundation. 14 * 15 * OpenOffice.org is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU Lesser General Public License version 3 for more details 19 * (a copy is included in the LICENSE file that accompanied this code). 20 * 21 * You should have received a copy of the GNU Lesser General Public License 22 * version 3 along with OpenOffice.org. If not, see 23 * <http://www.openoffice.org/license.html> 24 * for a copy of the LGPLv3 License. 25 * 26 ************************************************************************/ 27 package connectivity.tools; 28 29 import com.sun.star.beans.PropertyValue; 30 import com.sun.star.beans.PropertyState; 31 import com.sun.star.container.ElementExistException; 32 import com.sun.star.container.NoSuchElementException; 33 import com.sun.star.frame.XComponentLoader; 34 import com.sun.star.frame.XController; 35 import com.sun.star.frame.XModel; 36 import com.sun.star.io.IOException; 37 import com.sun.star.lang.IllegalArgumentException; 38 import com.sun.star.lang.WrappedTargetException; 39 import com.sun.star.lang.XComponent; 40 import com.sun.star.lang.XMultiServiceFactory; 41 import com.sun.star.sdb.XSingleSelectQueryComposer; 42 import com.sun.star.sdb.application.XDatabaseDocumentUI; 43 import com.sun.star.sdbc.SQLException; 44 import com.sun.star.sdbcx.XTablesSupplier; 45 import com.sun.star.uno.UnoRuntime; 46 import com.sun.star.util.XRefreshable; 47 import connectivity.tools.sdb.Connection; 48 49 /** implements a small Customer Relationship Management database 50 * 51 * Not finished, by far. Feel free to add features as you need them. 52 */ 53 public class CRMDatabase 54 { 55 private static final String INTEGER = "INTEGER"; 56 private static final String VARCHAR50 = "VARCHAR(50)"; 57 private final XMultiServiceFactory m_orb; 58 private final HsqlDatabase m_database; 59 private final DataSource m_dataSource; 60 private final Connection m_connection; 61 62 /** constructs the CRM database 63 */ 64 public CRMDatabase( XMultiServiceFactory _orb, boolean _withUI ) throws Exception 65 { 66 m_orb = _orb; 67 68 m_database = new HsqlDatabase( m_orb ); 69 m_dataSource = m_database.getDataSource(); 70 71 if ( _withUI ) 72 { 73 final XComponentLoader loader = UnoRuntime.queryInterface( XComponentLoader.class, 74 m_orb.createInstance( "com.sun.star.frame.Desktop" ) ); 75 PropertyValue[] loadArgs = new PropertyValue[] { 76 new PropertyValue( "PickListEntry", 0, false, PropertyState.DIRECT_VALUE ) 77 }; 78 loader.loadComponentFromURL( m_database.getDocumentURL(), "_blank", 0, loadArgs ); 79 getDocumentUI().connect(); 80 m_connection = new Connection( getDocumentUI().getActiveConnection() ); 81 } 82 else 83 { 84 m_connection = m_database.defaultConnection(); 85 } 86 87 createTables(); 88 createQueries(); 89 } 90 91 /** 92 * creates a CRMDatabase from an existing document, given by URL 93 * @param _orb 94 * @param _existingDocumentURL 95 * @throws Exceptio 96 */ 97 public CRMDatabase( XMultiServiceFactory _orb, final String _existingDocumentURL ) throws Exception 98 { 99 m_orb = _orb; 100 101 m_database = new HsqlDatabase( m_orb, _existingDocumentURL ); 102 m_dataSource = m_database.getDataSource(); 103 m_connection = m_database.defaultConnection(); 104 } 105 106 // -------------------------------------------------------------------------------------------------------- 107 /** returns the database document underlying the CRM database 108 */ 109 public final HsqlDatabase getDatabase() 110 { 111 return m_database; 112 } 113 114 // -------------------------------------------------------------------------------------------------------- 115 /** returns the default connection to the database 116 */ 117 public final Connection getConnection() 118 { 119 return m_connection; 120 } 121 122 // -------------------------------------------------------------------------------------------------------- 123 public void saveAndClose() throws SQLException, IOException 124 { 125 XDatabaseDocumentUI ui = getDocumentUI(); 126 if ( ui != null ) 127 ui.closeSubComponents(); 128 m_database.store(); 129 m_database.closeAndDelete(); 130 } 131 132 // -------------------------------------------------------------------------------------------------------- 133 public XDatabaseDocumentUI getDocumentUI() 134 { 135 XModel docModel = UnoRuntime.queryInterface( XModel.class, m_database.getDatabaseDocument() ); 136 return UnoRuntime.queryInterface( XDatabaseDocumentUI.class, docModel.getCurrentController() ); 137 } 138 139 // -------------------------------------------------------------------------------------------------------- 140 public XController loadSubComponent( final int _objectType, final String _name ) throws IllegalArgumentException, SQLException, NoSuchElementException 141 { 142 XDatabaseDocumentUI docUI = getDocumentUI(); 143 if ( !docUI.isConnected() ) 144 docUI.connect(); 145 146 XComponent subComponent = docUI.loadComponent( _objectType, _name, false ); 147 XController controller = UnoRuntime.queryInterface( XController.class, subComponent ); 148 if ( controller != null ) 149 return controller; 150 XModel document = UnoRuntime.queryInterface( XModel.class, subComponent ); 151 return document.getCurrentController(); 152 } 153 154 // -------------------------------------------------------------------------------------------------------- 155 private void createTables() throws SQLException 156 { 157 HsqlTableDescriptor table = new HsqlTableDescriptor( "categories", 158 new HsqlColumnDescriptor[] { 159 new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ), 160 new HsqlColumnDescriptor( "Name",VARCHAR50), 161 new HsqlColumnDescriptor( "Description", "VARCHAR(1024)" ), 162 new HsqlColumnDescriptor( "Image", "LONGVARBINARY" ) } ); 163 m_database.createTable( table, true ); 164 165 m_database.executeSQL( "INSERT INTO \"categories\" ( \"ID\", \"Name\" ) VALUES ( 1, 'Food' )" ); 166 m_database.executeSQL( "INSERT INTO \"categories\" ( \"ID\", \"Name\" ) VALUES ( 2, 'Furniture' )" ); 167 168 table = new HsqlTableDescriptor( "products", 169 new HsqlColumnDescriptor[] { 170 new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ), 171 new HsqlColumnDescriptor( "Name",VARCHAR50), 172 new HsqlColumnDescriptor( "CategoryID",INTEGER, HsqlColumnDescriptor.REQUIRED, "categories", "ID" ) } ); 173 m_database.createTable( table, true ); 174 175 m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 1, 'Oranges', 1 )" ); 176 m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 2, 'Apples', 1 )" ); 177 m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 3, 'Pears', 1 )" ); 178 m_database.executeSQL( "INSERT INTO \"products\" VALUES ( 4, 'Strawberries', 1 )" ); 179 180 table = new HsqlTableDescriptor( "customers", 181 new HsqlColumnDescriptor[] { 182 new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ), 183 new HsqlColumnDescriptor( "Name",VARCHAR50), 184 new HsqlColumnDescriptor( "Address",VARCHAR50), 185 new HsqlColumnDescriptor( "City",VARCHAR50), 186 new HsqlColumnDescriptor( "Postal",VARCHAR50), 187 new HsqlColumnDescriptor( "Comment","LONGVARCHAR")} ); 188 m_database.createTable( table, true ); 189 190 m_database.executeSQL( "INSERT INTO \"customers\" VALUES(1,'Food, Inc.','Down Under','Melbourne','509','Prefered') " ); 191 m_database.executeSQL( "INSERT INTO \"customers\" VALUES(2,'Simply Delicious','Down Under','Melbourne','518',null) " ); 192 m_database.executeSQL( "INSERT INTO \"customers\" VALUES(3,'Pure Health','10 Fish St.','San Francisco','94107',null) " ); 193 m_database.executeSQL( "INSERT INTO \"customers\" VALUES(4,'Milk And More','Arlington Road 21','Dublin','31021','Good one.') " ); 194 195 table = new HsqlTableDescriptor( "orders", 196 new HsqlColumnDescriptor[] { 197 new HsqlColumnDescriptor( "ID",INTEGER, HsqlColumnDescriptor.PRIMARY ), 198 new HsqlColumnDescriptor( "CustomerID",INTEGER, HsqlColumnDescriptor.REQUIRED, "customers", "ID" ), 199 new HsqlColumnDescriptor( "OrderDate", "DATE" ), 200 new HsqlColumnDescriptor( "ShipDate", "DATE" ) } ); 201 m_database.createTable( table, true ); 202 203 m_database.executeSQL( "INSERT INTO \"orders\" (\"ID\", \"CustomerID\", \"OrderDate\") VALUES(1, 1, {D '2009-01-01'})" ); 204 m_database.executeSQL( "INSERT INTO \"orders\" VALUES(2, 2, {D '2009-01-01'}, {D '2009-01-23'})" ); 205 206 table = new HsqlTableDescriptor( "orders_details", 207 new HsqlColumnDescriptor[] { 208 new HsqlColumnDescriptor( "OrderID",INTEGER, HsqlColumnDescriptor.PRIMARY, "orders", "ID" ), 209 new HsqlColumnDescriptor( "ProductID",INTEGER, HsqlColumnDescriptor.PRIMARY, "products", "ID" ), 210 new HsqlColumnDescriptor( "Quantity",INTEGER) } ); 211 m_database.createTable( table, true ); 212 213 m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(1, 1, 100)" ); 214 m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(1, 2, 100)" ); 215 m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(2, 2, 2000)" ); 216 m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(2, 3, 2000)" ); 217 m_database.executeSQL( "INSERT INTO \"orders_details\" VALUES(2, 4, 2000)" ); 218 219 // since we created the tables by directly executing the SQL statements, we need to refresh 220 // the tables container 221 m_connection.refreshTables(); 222 } 223 224 // -------------------------------------------------------------------------------------------------------- 225 private void validateUnparseable() 226 { 227 /* 228 // The "unparseable" query should be indeed be unparseable by OOo (though a valid HSQL query) 229 XSingleSelectQueryComposer composer; 230 QueryDefinition unparseableQuery; 231 try 232 { 233 final XMultiServiceFactory factory = UnoRuntime.queryInterface( 234 XMultiServiceFactory.class, m_database.defaultConnection().getXConnection() ); 235 composer = UnoRuntime.queryInterface( 236 XSingleSelectQueryComposer.class, factory.createInstance( "com.sun.star.sdb.SingleSelectQueryComposer" ) ); 237 unparseableQuery = m_dataSource.getQueryDefinition( "unparseable" ); 238 } 239 catch( Exception e ) 240 { 241 throw new RuntimeException( "caught an unexpected exception: " + e.getMessage() ); 242 } 243 244 boolean caughtExpected = false; 245 try 246 { 247 composer.setQuery( unparseableQuery.getCommand() ); 248 } 249 catch (WrappedTargetException e) { } 250 catch( SQLException e ) 251 { 252 caughtExpected = true; 253 } 254 255 if ( !caughtExpected ) 256 throw new RuntimeException( "Somebody improved the parser! This is bad :), since we need an unparsable query here!" ); 257 */ 258 } 259 260 // -------------------------------------------------------------------------------------------------------- 261 private void createQueries() throws ElementExistException, WrappedTargetException, com.sun.star.lang.IllegalArgumentException 262 { 263 m_database.getDataSource().createQuery( 264 "all orders", 265 "SELECT \"orders\".\"ID\" AS \"Order No.\", " + 266 "\"customers\".\"Name\" AS \"Customer Name\", " + 267 "\"orders\".\"OrderDate\" AS \"Order Date\", " + 268 "\"orders\".\"ShipDate\" AS \"Ship Date\", " + 269 "\"orders_details\".\"Quantity\", " + 270 "\"products\".\"Name\" AS \"Product Name\" " + 271 "FROM \"orders_details\" AS \"orders_details\", " + 272 "\"orders\" AS \"orders\", " + 273 "\"products\" AS \"products\", " + 274 "\"customers\" AS \"customers\" " + 275 "WHERE ( \"orders_details\".\"OrderID\" = \"orders\".\"ID\" " + 276 "AND \"orders_details\".\"ProductID\" = \"products\".\"ID\" " + 277 "AND \"orders\".\"CustomerID\" = \"customers\".\"ID\" )" 278 ); 279 280 m_database.getDataSource().createQuery( 281 "unshipped orders", 282 "SELECT * " + 283 "FROM \"all orders\"" + 284 "WHERE ( \"ShipDate\" IS NULL )" 285 ); 286 287 m_database.getDataSource().createQuery( "parseable", "SELECT * FROM \"customers\"" ); 288 m_database.getDataSource().createQuery( "parseable native", "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VIEWS", false ); 289 /* 290 m_database.getDataSource().createQuery( "unparseable", 291 "SELECT {fn DAYOFMONTH ('2001-01-01')} AS \"ID_VARCHAR\" FROM \"products\"", false ); 292 */ 293 validateUnparseable(); 294 } 295 } 296