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      */
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 Exceptio
92      */
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      */
105     public final HsqlDatabase getDatabase()
106     {
107         return m_database;
108     }
109 
110     // --------------------------------------------------------------------------------------------------------
111     /** returns the default connection to the database
112      */
113     public final Connection getConnection()
114     {
115         return m_connection;
116     }
117 
118     // --------------------------------------------------------------------------------------------------------
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     // --------------------------------------------------------------------------------------------------------
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     // --------------------------------------------------------------------------------------------------------
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     // --------------------------------------------------------------------------------------------------------
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     // --------------------------------------------------------------------------------------------------------
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     // --------------------------------------------------------------------------------------------------------
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