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