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 package testlib.uno; 23 24 import com.sun.star.beans.PropertyValue; 25 import com.sun.star.beans.PropertyState; 26 import com.sun.star.beans.XPropertySet; 27 import com.sun.star.container.ElementExistException; 28 import com.sun.star.container.XNameAccess; 29 import com.sun.star.frame.XStorable; 30 import com.sun.star.lang.XMultiServiceFactory; 31 import com.sun.star.sdb.XDocumentDataSource; 32 import com.sun.star.sdb.XOfficeDatabaseDocument; 33 import com.sun.star.sdbc.SQLException; 34 import com.sun.star.sdbc.XCloseable; 35 import com.sun.star.sdbc.XConnection; 36 import com.sun.star.sdbc.XDataSource; 37 import com.sun.star.sdbc.XStatement; 38 import com.sun.star.sdbcx.XAppend; 39 import com.sun.star.sdbcx.XTablesSupplier; 40 import com.sun.star.uno.UnoRuntime; 41 import com.sun.star.util.CloseVetoException; 42 43 import java.util.HashMap; 44 import java.util.Iterator; 45 import java.util.Set; 46 import java.io.File; 47 48 import org.openoffice.test.common.FileUtil; 49 50 51 public class DBUtil { 52 // the service factory 53 protected static XMultiServiceFactory m_orb; 54 // the URL of the temporary file used for the database document 55 protected static String m_databaseDocumentFile; 56 // the database document 57 protected static XOfficeDatabaseDocument m_databaseDocument; 58 // the data source belonging to the database document 59 protected static XDataSource m_dataSource; 60 // the default connection 61 protected static XConnection m_connection; 62 63 createNewDocument(final XMultiServiceFactory orb)64 static public void createNewDocument(final XMultiServiceFactory orb) 65 throws Exception { 66 m_orb = orb; 67 createDBDocument(); 68 } 69 loadNewDocument(final XMultiServiceFactory orb, final String _existingDocumentURL)70 static public void loadNewDocument(final XMultiServiceFactory orb, 71 final String _existingDocumentURL) throws Exception { 72 m_orb = orb; 73 getDocument(_existingDocumentURL); 74 } 75 76 /** 77 * creates an empty database document in a temporary location 78 */ createDBDocument()79 public static void createDBDocument() throws Exception { 80 final File documentFile = File.createTempFile("testdb", ".odb"); 81 if (documentFile.exists()) 82 documentFile.delete(); 83 m_databaseDocumentFile = FileUtil.getUrl(documentFile); 84 m_databaseDocument = (XOfficeDatabaseDocument) UnoRuntime 85 .queryInterface( 86 XOfficeDatabaseDocument.class, 87 m_orb.createInstance("com.sun.star.sdb.OfficeDatabaseDocument")); 88 m_dataSource = m_databaseDocument.getDataSource(); 89 90 final XPropertySet dsProperties = (XPropertySet) UnoRuntime 91 .queryInterface(XPropertySet.class, 92 m_databaseDocument.getDataSource()); 93 dsProperties.setPropertyValue("URL", "sdbc:embedded:hsqldb"); 94 95 final XStorable storable = (XStorable) UnoRuntime.queryInterface( 96 XStorable.class, m_databaseDocument); 97 storable.storeAsURL(m_databaseDocumentFile, 98 new PropertyValue[] { new PropertyValue("PickListEntry", 0, 99 false, PropertyState.DIRECT_VALUE) }); 100 } 101 102 getDocument(final String _docURL)103 public static void getDocument(final String _docURL) throws Exception { 104 m_databaseDocumentFile = _docURL; 105 106 final XNameAccess dbContext = (XNameAccess) UnoRuntime.queryInterface( 107 XNameAccess.class, 108 m_orb.createInstance("com.sun.star.sdb.DatabaseContext")); 109 final XDocumentDataSource dataSource = (XDocumentDataSource) UnoRuntime.queryInterface( 110 XDocumentDataSource.class, dbContext.getByName(_docURL)); 111 112 m_databaseDocument = dataSource.getDatabaseDocument(); 113 m_dataSource = m_databaseDocument.getDataSource(); 114 } 115 116 /** 117 * drops the table with a given name 118 * 119 * @param _name 120 * the name of the table to drop 121 * @param _ifExists 122 * TRUE if it should be dropped only when it exists. 123 */ dropTable(final String _name, final boolean _ifExists)124 static public void dropTable(final String _name, final boolean _ifExists) 125 throws SQLException { 126 final StringBuffer dropStatement = new StringBuffer("DROP TABLE \""); 127 dropStatement.append(_name); 128 if (_ifExists) { 129 dropStatement.append("\" IF EXISTS"); 130 } 131 executeSQL(dropStatement.toString()); 132 } 133 createTable(String _name, HsqlColumnDescriptor[] _columns, final boolean _dropIfExists)134 static public void createTable(String _name, 135 HsqlColumnDescriptor[] _columns, final boolean _dropIfExists) 136 throws SQLException { 137 if (_dropIfExists) { 138 dropTable(_name, true); 139 } 140 createTable(_name, _columns); 141 } 142 143 /** 144 * creates a table 145 */ createTable(String _name, HsqlColumnDescriptor[] _columns)146 static public void createTable(String _name, HsqlColumnDescriptor[] _columns) 147 throws SQLException { 148 StringBuffer createStatement = new StringBuffer( 149 "CREATE CACHED TABLE \""); 150 createStatement.append(_name); 151 createStatement.append("\" ( "); 152 153 String primaryKeyList = ""; 154 155 final HashMap foreignKeys = new HashMap(); 156 final HashMap foreignKeyRefs = new HashMap(); 157 158 final HsqlColumnDescriptor[] columns = _columns; 159 for (int i = 0; i < columns.length; ++i) { 160 if (i > 0) { 161 createStatement.append(", "); 162 } 163 164 createStatement.append("\"" + columns[i].getName()); 165 createStatement.append("\" " + columns[i].getTypeName()); 166 167 if (columns[i].isRequired()) { 168 createStatement.append(" NOT NULL"); 169 } 170 171 if (columns[i].isPrimaryKey()) { 172 if (primaryKeyList.length() > 0) { 173 primaryKeyList += ", "; 174 } 175 primaryKeyList += "\"" + columns[i].getName() + "\""; 176 } 177 178 if (columns[i].isForeignKey()) { 179 final String foreignTable = columns[i].getForeignTable(); 180 181 String foreignKeysForTable = foreignKeys 182 .containsKey(foreignTable) ? (String) foreignKeys 183 .get(foreignTable) : ""; 184 if (foreignKeysForTable.length() > 0) { 185 foreignKeysForTable += ", "; 186 } 187 foreignKeysForTable += "\"" + columns[i].getName() + "\""; 188 foreignKeys.put(foreignTable, foreignKeysForTable); 189 190 final StringBuffer foreignKeyRefsForTable = new StringBuffer( 191 foreignKeyRefs.containsKey(foreignTable) ? (String) foreignKeyRefs 192 .get(foreignTable) : ""); 193 if (foreignKeyRefsForTable.length() > 0) { 194 foreignKeyRefsForTable.append(", "); 195 } 196 foreignKeyRefsForTable.append("\"" 197 + columns[i].getForeignColumn() + "\""); 198 foreignKeyRefs.put(foreignTable, 199 foreignKeyRefsForTable.toString()); 200 } 201 } 202 203 if (primaryKeyList.length() > 0) { 204 createStatement.append(", PRIMARY KEY ("); 205 createStatement.append(primaryKeyList); 206 createStatement.append(')'); 207 } 208 209 final Set foreignKeyTables = foreignKeys.keySet(); 210 for (final Iterator foreignKey = foreignKeyTables.iterator(); foreignKey 211 .hasNext();) { 212 final String foreignTable = (String) foreignKey.next(); 213 214 createStatement.append(", FOREIGN KEY ("); 215 createStatement.append((String) foreignKeys.get(foreignTable)); 216 createStatement.append(") REFERENCES \""); 217 createStatement.append(foreignTable); 218 createStatement.append("\"("); 219 createStatement.append((String) foreignKeyRefs.get(foreignTable)); 220 createStatement.append(')'); 221 } 222 223 createStatement.append(')'); 224 225 // System.err.println( createStatement ); 226 executeSQL(createStatement.toString()); 227 } 228 229 230 /** 231 * executes the given SQL statement via the defaultConnection 232 */ executeSQL(final String statementString)233 static public void executeSQL(final String statementString) 234 throws SQLException { 235 final XStatement statement = defaultConnection().createStatement(); 236 statement.execute(statementString); 237 } 238 239 /** 240 * returns a connection to the database 241 * 242 * Multiple calls to this method return the same connection. The 243 * DbaseDatabase object keeps the ownership of the connection, so you don't 244 * need to (and should not) dispose/close it. 245 */ defaultConnection()246 static public XConnection defaultConnection() throws SQLException { 247 if (m_connection == null) 248 m_connection = m_databaseDocument.getDataSource().getConnection("", 249 ""); 250 251 return m_connection; 252 } 253 254 /** 255 * closes the database document 256 * 257 * Any CloseVetoExceptions fired by third parties are ignored, and any 258 * reference to the database document is released. 259 */ close()260 static public void close() { 261 // close connection 262 final XCloseable closeConn = (XCloseable) UnoRuntime.queryInterface( 263 XCloseable.class, m_connection != null ? m_connection : null); 264 if (closeConn != null) { 265 try { 266 closeConn.close(); 267 } catch (SQLException e) { 268 } 269 } 270 m_connection = null; 271 272 // close document 273 final com.sun.star.util.XCloseable closeDoc = (com.sun.star.util.XCloseable) UnoRuntime 274 .queryInterface(com.sun.star.util.XCloseable.class, 275 m_databaseDocument); 276 if (closeDoc != null) { 277 try { 278 closeDoc.close(true); 279 } catch (CloseVetoException e) { 280 } 281 } 282 m_databaseDocument = null; 283 } 284 285 /** 286 * returns the underlying database document 287 */ getDatabaseDocument()288 static public XOfficeDatabaseDocument getDatabaseDocument() { 289 return m_databaseDocument; 290 } 291 getDocumentURL()292 static public String getDocumentURL() { 293 return m_databaseDocumentFile; 294 } 295 } 296