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 
64 	static public void createNewDocument(final XMultiServiceFactory orb)
65 			throws Exception {
66 		m_orb = orb;
67 		createDBDocument();
68 	}
69 
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 	 */
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 
103 	public static void getDocument(final String _docURL) throws Exception {
104 		m_databaseDocumentFile = _docURL;
105 
106 		final XNameAccess dbContext = UnoRuntime.queryInterface(
107 				XNameAccess.class,
108 				m_orb.createInstance("com.sun.star.sdb.DatabaseContext"));
109 		final XDocumentDataSource dataSource = 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 	 */
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 
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 	 */
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 	 */
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 	 */
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 	 */
260 	static public void close() {
261 		// close connection
262 		final XCloseable closeConn = 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 = 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 	 */
288 	static public XOfficeDatabaseDocument getDatabaseDocument() {
289 		return m_databaseDocument;
290 	}
291 
292 	static public String getDocumentURL() {
293 		return m_databaseDocumentFile;
294 	}
295 }
296