1 /****************************************************************
2 * Licensed to the Apache Software Foundation (ASF) under one *
3 * or more contributor license agreements. See the NOTICE file *
4 * distributed with this work for additional information *
5 * regarding copyright ownership. The ASF licenses this file *
6 * to you under the Apache License, Version 2.0 (the *
7 * "License"); you may not use this file except in compliance *
8 * with the License. You may obtain a copy of the License at *
9 * *
10 * http://www.apache.org/licenses/LICENSE-2.0 *
11 * *
12 * Unless required by applicable law or agreed to in writing, *
13 * software distributed under the License is distributed on an *
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY *
15 * KIND, either express or implied. See the License for the *
16 * specific language governing permissions and limitations *
17 * under the License. *
18 ****************************************************************/
19
20
21 package org.apache.james.util.sql;
22
23 import org.apache.oro.text.perl.MalformedPerl5PatternException;
24 import org.apache.oro.text.perl.Perl5Util;
25 import org.w3c.dom.Attr;
26 import org.w3c.dom.Document;
27 import org.w3c.dom.Element;
28 import org.w3c.dom.NamedNodeMap;
29 import org.w3c.dom.NodeList;
30
31 import javax.xml.parsers.DocumentBuilder;
32 import javax.xml.parsers.DocumentBuilderFactory;
33 import java.io.File;
34 import java.io.InputStream;
35 import java.sql.Connection;
36 import java.sql.SQLException;
37 import java.util.HashMap;
38 import java.util.Iterator;
39 import java.util.Map;
40
41
42 /**
43 * Provides a set of SQL String resources (eg SQL Strings)
44 * to use for a database connection.
45 * This class allows SQL strings to be customised to particular
46 * database products, by detecting product information from the
47 * jdbc DatabaseMetaData object.
48 *
49 */
50 public class SqlResources
51 {
52 /**
53 * A map of statement types to SQL statements
54 */
55 private Map m_sql = new HashMap();
56
57 /**
58 * A map of engine specific options
59 */
60 private Map m_dbOptions = new HashMap();
61
62 /**
63 * A set of all used String values
64 */
65 static private Map stringTable = java.util.Collections.synchronizedMap(new HashMap());
66
67 /**
68 * A Perl5 regexp matching helper class
69 */
70 private Perl5Util m_perl5Util = new Perl5Util();
71
72 /**
73 * Configures a DbResources object to provide SQL statements from a file.
74 *
75 * SQL statements returned may be specific to the particular type
76 * and version of the connected database, as well as the database driver.
77 *
78 * Parameters encoded as $(parameter} in the input file are
79 * replace by values from the parameters Map, if the named parameter exists.
80 * Parameter values may also be specified in the resourceSection element.
81 *
82 * @param sqlFile the input file containing the string definitions
83 * @param sqlDefsSection
84 * the xml element containing the strings to be used
85 * @param conn the Jdbc DatabaseMetaData, taken from a database connection
86 * @param configParameters a map of parameters (name-value string pairs) which are
87 * replaced where found in the input strings
88 */
89 public void init(File sqlFile, String sqlDefsSection,
90 Connection conn, Map configParameters)
91 throws Exception
92 {
93 // Parse the sqlFile as an XML document.
94 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
95 DocumentBuilder builder = factory.newDocumentBuilder();
96 Document sqlDoc = builder.parse(sqlFile);
97
98 init(sqlDoc, sqlDefsSection, conn, configParameters);
99 }
100
101 /**
102 * Configures a DbResources object to provide SQL statements from an InputStream.
103 *
104 * SQL statements returned may be specific to the particular type
105 * and version of the connected database, as well as the database driver.
106 *
107 * Parameters encoded as $(parameter} in the input file are
108 * replace by values from the parameters Map, if the named parameter exists.
109 * Parameter values may also be specified in the resourceSection element.
110 *
111 * @param input the input stream containing the xml
112 * @param sqlDefsSection
113 * the xml element containing the strings to be used
114 * @param conn the Jdbc DatabaseMetaData, taken from a database connection
115 * @param configParameters a map of parameters (name-value string pairs) which are
116 * replaced where found in the input strings
117 */
118 public void init(InputStream input, String sqlDefsSection,
119 Connection conn, Map configParameters)
120 throws Exception
121 {
122 // Parse the InputStream as an XML document.
123 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
124 DocumentBuilder builder = factory.newDocumentBuilder();
125 Document sqlDoc = builder.parse(input);
126
127 init(sqlDoc, sqlDefsSection, conn, configParameters);
128 }
129
130
131 /**
132 * Configures a SqlResources object from an xml document.
133 *
134 * @param sqlDoc
135 * @param sqlDefsSection
136 * @param conn
137 * @param configParameters
138 * @throws SQLException
139 */
140 protected void init(Document sqlDoc, String sqlDefsSection,
141 Connection conn, Map configParameters) throws SQLException {
142 // First process the database matcher, to determine the
143 // sql statements to use.
144 Element dbMatcherElement =
145 (Element)(sqlDoc.getElementsByTagName("dbMatchers").item(0));
146 String dbProduct = null;
147 if ( dbMatcherElement != null ) {
148 dbProduct = matchDbConnection(conn, dbMatcherElement);
149 m_perl5Util = null; // release the PERL matcher!
150 }
151
152 // Now get the options valid for the database product used.
153 Element dbOptionsElement =
154 (Element)(sqlDoc.getElementsByTagName("dbOptions").item(0));
155 if ( dbOptionsElement != null ) {
156 // First populate the map with default values
157 populateDbOptions("", dbOptionsElement, m_dbOptions);
158 // Now update the map with specific product values
159 if ( dbProduct != null ) {
160 populateDbOptions(dbProduct, dbOptionsElement, m_dbOptions);
161 }
162 }
163
164
165 // Now get the section defining sql for the repository required.
166 NodeList sections = sqlDoc.getElementsByTagName("sqlDefs");
167 int sectionsCount = sections.getLength();
168 Element sectionElement = null;
169 boolean found = false;
170 for (int i = 0; i < sectionsCount; i++ ) {
171 sectionElement = (Element)(sections.item(i));
172 String sectionName = sectionElement.getAttribute("name");
173 if ( sectionName != null && sectionName.equals(sqlDefsSection) ) {
174 found = true;
175 break;
176 }
177
178 }
179 if ( !found ) {
180 StringBuffer exceptionBuffer =
181 new StringBuffer(64)
182 .append("Error loading sql definition file. ")
183 .append("The element named \'")
184 .append(sqlDefsSection)
185 .append("\' does not exist.");
186 throw new RuntimeException(exceptionBuffer.toString());
187 }
188
189 // Get parameters defined within the file as defaults,
190 // and use supplied parameters as overrides.
191 Map parameters = new HashMap();
192 // First read from the <params> element, if it exists.
193 Element parametersElement =
194 (Element)(sectionElement.getElementsByTagName("parameters").item(0));
195 if ( parametersElement != null ) {
196 NamedNodeMap params = parametersElement.getAttributes();
197 int paramCount = params.getLength();
198 for (int i = 0; i < paramCount; i++ ) {
199 Attr param = (Attr)params.item(i);
200 String paramName = param.getName();
201 String paramValue = param.getValue();
202 parameters.put(paramName, paramValue);
203 }
204 }
205 // Then copy in the parameters supplied with the call.
206 parameters.putAll(configParameters);
207
208 // 2 maps - one for storing default statements,
209 // the other for statements with a "db" attribute matching this
210 // connection.
211 Map defaultSqlStatements = new HashMap();
212 Map dbProductSqlStatements = new HashMap();
213
214 // Process each sql statement, replacing string parameters,
215 // and adding to the appropriate map..
216 NodeList sqlDefs = sectionElement.getElementsByTagName("sql");
217 int sqlCount = sqlDefs.getLength();
218 for ( int i = 0; i < sqlCount; i++ ) {
219 // See if this needs to be processed (is default or product specific)
220 Element sqlElement = (Element)(sqlDefs.item(i));
221 String sqlDb = sqlElement.getAttribute("db");
222 Map sqlMap;
223 if ( sqlDb.equals("")) {
224 // default
225 sqlMap = defaultSqlStatements;
226 }
227 else if (sqlDb.equals(dbProduct) ) {
228 // Specific to this product
229 sqlMap = dbProductSqlStatements;
230 }
231 else {
232 // for a different product
233 continue;
234 }
235
236 // Get the key and value for this SQL statement.
237 String sqlKey = sqlElement.getAttribute("name");
238 if ( sqlKey == null ) {
239 // ignore statements without a "name" attribute.
240 continue;
241 }
242 String sqlString = sqlElement.getFirstChild().getNodeValue();
243
244 // Do parameter replacements for this sql string.
245 Iterator paramNames = parameters.keySet().iterator();
246 StringBuffer replaceBuffer = new StringBuffer(64);
247 while ( paramNames.hasNext() ) {
248 String paramName = (String)paramNames.next();
249 String paramValue = (String)parameters.get(paramName);
250 replaceBuffer.append("${").append(paramName).append("}");
251 sqlString = substituteSubString(sqlString, replaceBuffer.toString(), paramValue);
252 if (paramNames.hasNext()) replaceBuffer.setLength(0);
253 }
254
255 // See if we already have registered a string of this value
256 String shared = (String) stringTable.get(sqlString);
257 // If not, register it -- we will use it next time
258 if (shared == null) {
259 stringTable.put(sqlString, sqlString);
260 } else {
261 sqlString = shared;
262 }
263
264 // Add to the sqlMap - either the "default" or the "product" map
265 sqlMap.put(sqlKey, sqlString);
266 }
267
268 // Copy in default strings, then overwrite product-specific ones.
269 m_sql.putAll(defaultSqlStatements);
270 m_sql.putAll(dbProductSqlStatements);
271 }
272
273 /**
274 * Compares the DatabaseProductName value for a jdbc Connection
275 * against a set of regular expressions defined in XML.
276 * The first successful match defines the name of the database product
277 * connected to. This value is then used to choose the specific SQL
278 * expressions to use.
279 *
280 * @param conn the JDBC connection being tested
281 * @param dbMatchersElement the XML element containing the database type information
282 *
283 * @return the type of database to which James is connected
284 *
285 */
286 private String matchDbConnection(Connection conn,
287 Element dbMatchersElement)
288 throws MalformedPerl5PatternException, SQLException
289 {
290 String dbProductName = conn.getMetaData().getDatabaseProductName();
291
292 NodeList dbMatchers =
293 dbMatchersElement.getElementsByTagName("dbMatcher");
294 for ( int i = 0; i < dbMatchers.getLength(); i++ ) {
295 // Get the values for this matcher element.
296 Element dbMatcher = (Element)dbMatchers.item(i);
297 String dbMatchName = dbMatcher.getAttribute("db");
298 StringBuffer dbProductPatternBuffer =
299 new StringBuffer(64)
300 .append("/")
301 .append(dbMatcher.getAttribute("databaseProductName"))
302 .append("/i");
303
304 // If the connection databaseProcuctName matches the pattern,
305 // use the match name from this matcher.
306 if ( m_perl5Util.match(dbProductPatternBuffer.toString(), dbProductName) ) {
307 return dbMatchName;
308 }
309 }
310 return null;
311 }
312
313 /**
314 * Gets all the name/value pair db option couples related to the dbProduct,
315 * and put them into the dbOptionsMap.
316 *
317 * @param dbProduct the db product used
318 * @param dbOptionsElement the XML element containing the options
319 * @param dbOptionsMap the <CODE>Map</CODE> to populate
320 *
321 */
322 private void populateDbOptions(String dbProduct, Element dbOptionsElement, Map dbOptionsMap)
323 {
324 NodeList dbOptions =
325 dbOptionsElement.getElementsByTagName("dbOption");
326 for ( int i = 0; i < dbOptions.getLength(); i++ ) {
327 // Get the values for this option element.
328 Element dbOption = (Element)dbOptions.item(i);
329 // Check is this element is pertinent to the dbProduct
330 // Notice that a missing attribute returns "", good for defaults
331 if (!dbProduct.equalsIgnoreCase(dbOption.getAttribute("db"))) {
332 continue;
333 }
334 // Put into the map
335 dbOptionsMap.put(dbOption.getAttribute("name"), dbOption.getAttribute("value"));
336 }
337 }
338
339 /**
340 * Replace substrings of one string with another string and return altered string.
341 * @param input input string
342 * @param find the string to replace
343 * @param replace the string to replace with
344 * @return the substituted string
345 */
346 private String substituteSubString( String input,
347 String find,
348 String replace )
349 {
350 int find_length = find.length();
351 int replace_length = replace.length();
352
353 StringBuffer output = new StringBuffer(input);
354 int index = input.indexOf(find);
355 int outputOffset = 0;
356
357 while ( index > -1 ) {
358 output.replace(index + outputOffset, index + outputOffset + find_length, replace);
359 outputOffset = outputOffset + (replace_length - find_length);
360
361 index = input.indexOf(find, index + find_length);
362 }
363
364 String result = output.toString();
365 return result;
366 }
367
368 /**
369 * Returns a named SQL string for the specified connection,
370 * replacing parameters with the values set.
371 *
372 * @param name the name of the SQL resource required.
373 * @return the requested resource
374 */
375 public String getSqlString(String name)
376 {
377 return (String)m_sql.get(name);
378 }
379
380 /**
381 * Returns a named SQL string for the specified connection,
382 * replacing parameters with the values set.
383 * throws ConfigurationException if a required resource cannot be found.
384 *
385 * @param name the name of the SQL resource required.
386 * @param required true if the resource is required
387 * @return the requested resource
388 */
389 public String getSqlString(String name, boolean required)
390 {
391 String sql = getSqlString(name);
392
393 if (sql == null && required) {
394 StringBuffer exceptionBuffer =
395 new StringBuffer(64)
396 .append("Required SQL resource: '")
397 .append(name)
398 .append("' was not found.");
399 throw new RuntimeException(exceptionBuffer.toString());
400 }
401 return sql;
402 }
403
404 /**
405 * Returns the dbOption string value set for the specified dbOption name.
406 *
407 * @param name the name of the dbOption required.
408 * @return the requested dbOption value
409 */
410 public String getDbOption(String name)
411 {
412 return (String)m_dbOptions.get(name);
413 }
414
415 }