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