Java > Open Source Codes > org > pentaho > plugin > sql > SQLBaseComponent


1 /*
2  * Copyright 2006 Pentaho Corporation. All rights reserved.
3  * This software was developed by Pentaho Corporation and is provided under the terms
4  * of the Mozilla Public License, Version 1.1, or any later version. You may not use
5  * this file except in compliance with the license. If you need a copy of the license,
6  * please go to http://www.mozilla.org/MPL/MPL-1.1.txt. The Original Code is the Pentaho
7  * BI Platform. The Initial Developer is Pentaho Corporation.
8  *
9  * Software distributed under the Mozilla Public License is distributed on an "AS IS"
10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
11  * the license for the specific language governing your rights and limitations.
12  *
13  * Created Sep 8, 2005
14  * @author mbatchel
15  */
16 package org.pentaho.plugin.sql;
17
18 import java.text.DecimalFormat ;
19 import java.text.Format ;
20 import java.text.SimpleDateFormat ;
21 import java.util.ArrayList ;
22 import java.util.List ;
23 import java.util.StringTokenizer ;
24 import java.util.regex.Matcher ;
25
26 import org.apache.commons.logging.Log;
27 import org.pentaho.core.component.IDataComponent;
28 import org.pentaho.core.connection.IPentahoConnection;
29 import org.pentaho.core.connection.IPentahoMetaData;
30 import org.pentaho.core.connection.IPentahoResultSet;
31 import org.pentaho.core.connection.PentahoDataTransmuter;
32 import org.pentaho.core.connection.memory.MemoryMetaData;
33 import org.pentaho.core.connection.memory.MemoryResultSet;
34 import org.pentaho.core.util.IParameterResolver;
35 import org.pentaho.core.util.TemplateUtil;
36 import org.pentaho.data.PentahoConnectionFactory;
37 import org.pentaho.messages.Messages;
38 import org.pentaho.plugin.ComponentBase;
39 import org.pentaho.plugin.core.StandardSettings;
40
41 public abstract class SQLBaseComponent extends ComponentBase implements IDataComponent, IParameterResolver {
42     private static final String TRANSFORM = "transform"; //$NON-NLS-1$
43
44     private static final String PIVOT_COLUMN = "pivot-column"; //$NON-NLS-1$
45
46     private static final String MEASURES_COLUMN = "measures-column"; //$NON-NLS-1$
47
48     private static final String FORMAT_TYPE = "format-type"; //$NON-NLS-1$
49
50     private static final String FORMAT_STRING = "format-string"; //$NON-NLS-1$
51
52     private static final String ORDERED_MAPS = "ordered-maps"; //$NON-NLS-1$
53
54     private static final String TRANSFORM_SORTBYCOL = "sort-by-col"; //$NON-NLS-1$
55
56     private static final String SORT_FORMAT_TYPE = "sort-format-type"; //$NON-NLS-1$
57
58     private static final String SORT_FORMAT_STRING = "sort-format-string"; //$NON-NLS-1$
59
60     public static final String PREPARE_PARAMETER_PREFIX = "PREPARE"; //$NON-NLS-1$
61
62     private List preparedParameters = new ArrayList ();
63     
64     private IPentahoResultSet rSet;
65     
66     protected IPentahoConnection connection;
67
68     public abstract boolean validateSystemSettings();
69
70     public abstract String getResultOutputName();
71
72     public abstract Log getLogger();
73
74     public IPentahoResultSet getResultSet() {
75         return rSet;
76     }
77
78     public boolean validateAction() {
79         String actionName = getActionName();
80         try {
81
82             boolean SQLNodeIsParameter = isDefinedInput(StandardSettings.QUERY_NAME);
83             if (!SQLNodeIsParameter) {
84                 boolean baseQueryIsParameter = isDefinedInput(StandardSettings.SQL_QUERY);
85                 if (!baseQueryIsParameter) {
86                     error(Messages.getErrorString("SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
87 return false;
88                 }
89             }
90             if (!isDefinedInput(StandardSettings.CONNECTION) && !isDefinedInput(StandardSettings.JNDI)) {
91                 error(Messages.getErrorString("SQLBaseComponent.ERROR_0002_CONNECTION_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
92 return false;
93             }
94
95             String outputName = getResultOutputName();
96             if (outputName != null) {
97                 if (!isDefinedOutput(outputName)) {
98                     error(Messages.getErrorString("SQLBaseComponent.ERROR_0003_OUTPUT_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
99 return false;
100                 }
101             }
102             return true;
103         } catch (Exception e) {
104             error(Messages.getErrorString("SQLBaseComponent.ERROR_0004_VALIDATION_FAILED", actionName), e); //$NON-NLS-1$
105 }
106
107         return false;
108
109     }
110
111     public void done() {
112     }
113
114     protected boolean executeAction() {
115
116         try {
117             String actionName = getActionName();
118             String querySetting = StandardSettings.SQL_QUERY;
119             boolean SQLNodeIsParameter = isDefinedInput(StandardSettings.QUERY_NAME);
120             if (SQLNodeIsParameter) {
121                 querySetting = getInputStringValue(StandardSettings.QUERY_NAME);
122             }
123             String baseQuery = getInputStringValue(querySetting);
124             if (baseQuery == null) {
125                 error(Messages.getErrorString("SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", actionName)); //$NON-NLS-1$
126 return false;
127             }
128             //
129 // Cleanup between executions...
130 //
131 dispose();
132             connection = getDatasourceConnection();
133             if (connection == null) {
134                 return false;
135             }
136             boolean live = "true".equals(getInputStringValue(StandardSettings.LIVE)); //$NON-NLS-1$
137 boolean transform = "true".equals(getInputStringValue(TRANSFORM)); //$NON-NLS-1$
138
139             if (transform) {
140                 int columnToPivot = Integer.parseInt(getInputStringValue(PIVOT_COLUMN));
141                 int measuresColumn = Integer.parseInt(getInputStringValue(MEASURES_COLUMN));
142                 String formatType = getInputStringValue(FORMAT_TYPE);
143                 String formatString = getInputStringValue(FORMAT_STRING);
144                 Format format = null;
145
146                 runQuery(baseQuery, false); // The side effect of
147 // this is that rSet is
148 // now the valid result
149 // Create the format if there is one
150 if (formatType != null && formatType.length() > 0) {
151                     if (StandardSettings.DECIMAL_FORMAT_TYPE.equalsIgnoreCase(formatString)) {
152                         format = new DecimalFormat (formatString);
153                     } else if (StandardSettings.DATE_FORMAT_TYPE.equalsIgnoreCase(formatType)) {
154                         format = new SimpleDateFormat (formatString);
155                     }
156                 }
157                 // transform rSet here
158 String orderedMaps = getInputStringValue(ORDERED_MAPS);
159                 boolean orderOutputColumns = "true".equalsIgnoreCase(orderedMaps); //$NON-NLS-1$
160
161                 // Sort-by column information
162 int transformSortByColumn = 0;
163                 String sortColumn = getInputStringValue(TRANSFORM_SORTBYCOL);
164                 if (sortColumn != null) {
165                     transformSortByColumn = Integer.parseInt(sortColumn);
166                 }
167                 String sortFormatType = getInputStringValue(SORT_FORMAT_TYPE);
168                 String sortFormatString = getInputStringValue(SORT_FORMAT_STRING);
169                 Format sortFormat = null;
170                 if (sortFormatType != null && sortFormatType.length() > 0) {
171                     if (StandardSettings.DECIMAL_FORMAT_TYPE.equalsIgnoreCase(sortFormatString)) {
172                         sortFormat = new DecimalFormat (sortFormatString);
173                     } else if (StandardSettings.DATE_FORMAT_TYPE.equalsIgnoreCase(sortFormatType)) {
174                         sortFormat = new SimpleDateFormat (sortFormatString);
175                     }
176                 }
177
178                 rSet = PentahoDataTransmuter.crossTab(rSet, columnToPivot - 1, measuresColumn - 1, transformSortByColumn - 1, format, sortFormat, orderOutputColumns);
179                 // then set the outputResult
180 if (getResultOutputName() != null) {
181                     setOutputValue(getResultOutputName(), rSet);
182                 }
183                 return true;
184             } else {
185                 return runQuery(baseQuery, live);
186             }
187         } catch (Exception e) {
188             error(Messages.getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
189 }
190
191         return false;
192     }
193
194     protected boolean runQuery(String rawQuery, boolean live) {
195
196         try {
197             if (connection == null) {
198                 error(Messages.getErrorString( "SQLBaseComponent.ERROR_0007_NO_CONNECTION" )); //$NON-NLS-1$
199 return false;
200             }
201             if (!connection.initialized()) {
202                 error(Messages.getErrorString( "SQLBaseComponent.ERROR_0007_NO_CONNECTION" )); //$NON-NLS-1$
203 return false;
204             }
205
206             String query = applyInputsToFormat(rawQuery);
207             if (debug)
208                 debug(Messages.getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
209
210             if (live) {
211
212                 // set the result set as the ourput
213 IPentahoResultSet resultSet = null;
214                 if (preparedParameters.size()>0) {
215                   resultSet = connection.prepareAndExecuteQuery(query, preparedParameters);
216                 } else {
217                   resultSet = connection.executeQuery(query);
218                 }
219                 rSet = resultSet;
220                 // After preparation and execution, we need to clear out the
221 // prepared parameters.
222 preparedParameters.clear();
223                 if (resultSet != null) {
224                     if (getResultOutputName() != null) {
225                         setOutputValue(this.getResultOutputName(), resultSet);
226                     }
227                     return true;
228                 } else {
229                     // close the connection
230 error(Messages.getErrorString("SQLRule.ERROR_0006_EXECUTE_FAILED", getActionName())); //$NON-NLS-1$
231 connection.close();
232                     return false;
233                 }
234
235             } else {
236                 // execute the query, read the results and cache them
237 try {
238                     IPentahoResultSet resultSet = null;
239                     if (preparedParameters.size()>0) {
240                       resultSet = connection.prepareAndExecuteQuery(query, preparedParameters);
241                     } else {
242                       resultSet = connection.executeQuery(query);
243                     }
244                     // After preparation and execution, we need to clear out the
245 // prepared parameters.
246 preparedParameters.clear();
247                     try {
248                         IPentahoMetaData metadata = resultSet.getMetaData();
249                         Object columnHeaders[][] = metadata.getColumnHeaders();
250
251                         MemoryMetaData metaData = new MemoryMetaData(columnHeaders, null);
252                         MemoryResultSet cachedResultSet = new MemoryResultSet(metaData);
253
254                         int columnCount = columnHeaders[0].length;
255                         String columnNames[] = new String [columnCount];
256                         for (int columnNo = 0; columnNo < columnCount; columnNo++) {
257                             columnNames[columnNo] = columnHeaders[0][columnNo].toString();
258                         }
259
260                         Object [] rowObjects = resultSet.next();
261                         while (rowObjects != null) {
262                             cachedResultSet.addRow(rowObjects);
263                             rowObjects = resultSet.next();
264                         }
265                         rSet = cachedResultSet;
266                         if (getResultOutputName() != null) {
267                             setOutputValue(this.getResultOutputName(), cachedResultSet);
268                         }
269                     } finally {
270                         resultSet.close();
271                     }
272                 } finally {
273                     connection.close();
274                     connection = null;
275                 }
276             }
277             return true;
278         } catch (Exception e) {
279             error(Messages.getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
280 }
281
282         return false;
283     }
284
285     public void dispose() {
286         if (rSet != null) {
287             rSet.close();
288         }
289         rSet = null;
290         if (connection != null) {
291             connection.close();
292         }
293         connection = null;
294     }
295     
296     public int resolveParameter(String template, String parameter, Matcher parameterMatcher, int copyStart, StringBuffer results) {
297       
298       StringTokenizer tokenizer = new StringTokenizer (parameter, ":"); //$NON-NLS-1$
299 if (tokenizer.countTokens() == 2) { // Currently, the component only handles one bit of metadata
300 String parameterPrefix = tokenizer.nextToken();
301         String inputName = tokenizer.nextToken();
302         if (parameterPrefix.equals(PREPARE_PARAMETER_PREFIX)) {
303           // We know this parameter is for us.
304 // First, is this a special input
305 Object parameterValue = TemplateUtil.getSystemInput(inputName, getRuntimeContext());
306           if ( (parameterValue == null) && isDefinedInput(inputName)) {
307             parameterValue = this.getInputValue(inputName);
308           }
309           if (parameterValue != null) {
310             // We have a parameter value - now, it's time to create a parameter and build up the
311 // parameter string
312 int start = parameterMatcher.start();
313             int end = parameterMatcher.end();
314             // First, find out if the parameter was quoted...
315 if ( (start>0) && (end<template.length()) ) {
316               if ( (template.charAt(start-1) == '\'') && (template.charAt(end) == '\'') ) {
317                 // Ok, the parameter was quoted as near as we can tell. So, we need
318 // to increase the size of the amount we overwrite by one in each
319 // direction. This is for backward compatibility.
320 start--;
321                 end++;
322               }
323             }
324             // We now have a valid start and end. It's time to see whether we're dealing
325 // with an array, a result set, or a scalar.
326 StringBuffer parameterBuffer = new StringBuffer ();
327             if (parameterValue instanceof String ) {
328               preparedParameters.add(parameterValue);
329               parameterBuffer.append('?');
330             } else if (parameterValue instanceof Object []) {
331               Object [] pObj = (Object [])parameterValue;
332               for (int i=0; i<pObj.length; i++) {
333                 preparedParameters.add(pObj[i]);
334                 parameterBuffer.append( (parameterBuffer.length()==0) ? "?":",?"); //$NON-NLS-1$ //$NON-NLS-2$
335 }
336             } else if (parameterValue instanceof IPentahoResultSet) {
337               IPentahoResultSet rs = (IPentahoResultSet) parameterValue;
338               // See if we can find a column in the metadata with the same
339 // name as the input
340 IPentahoMetaData md = rs.getMetaData();
341               int columnIdx = -1;
342               if (md.getColumnCount() == 1) {
343                   columnIdx = 0;
344               } else {
345                   columnIdx = md.getColumnIndex(new String [] { parameter });
346               }
347               if (columnIdx < 0) {
348                   error(Messages.getErrorString("Template.ERROR_0005_COULD_NOT_DETERMINE_COLUMN")); //$NON-NLS-1$
349 return -1;
350               }
351               int rowCount = rs.getRowCount();
352               Object valueCell = null;
353               // TODO support non-string columns
354 for (int i = 0; i < rowCount; i++) {
355                 valueCell = rs.getValueAt(i, columnIdx);
356                 preparedParameters.add(valueCell);
357                 parameterBuffer.append( (parameterBuffer.length()==0) ? "?":",?"); //$NON-NLS-1$ //$NON-NLS-2$
358 }
359             } else if (parameterValue instanceof List ) {
360               List pObj = (List )parameterValue;
361               for (int i=0; i<pObj.size(); i++) {
362                 preparedParameters.add(pObj.get(i));
363                 parameterBuffer.append( (parameterBuffer.length()==0) ? "?":",?"); //$NON-NLS-1$ //$NON-NLS-2$
364 }
365             } else {
366               // If we're here, we know parameterValue is not null and not a string
367 this.preparedParameters.add(parameterValue);
368               parameterBuffer.append('?');
369             }
370             
371             // OK - We have a parameterBuffer and have filled out the preparedParameters
372 // list. It's time to change the SQL to insert our parameter marker and tell
373 // the caller we've done our job.
374 results.append(template.substring(copyStart, start));
375             copyStart = end;
376             results.append(parameterBuffer);
377             return copyStart;
378           }
379         }
380       }
381       
382       return -1; // Nothing here for us - let default behavior through
383 }
384     
385     public IPentahoConnection getDatasourceConnection() {
386         IPentahoConnection con;
387         try {
388             con = getConnection();
389             try {
390                 con.clearWarnings();
391             } catch (Exception ex) {
392             }
393             return con;
394         } catch (Exception ex) {
395         }
396
397         waitFor(200);
398         try {
399             con = getConnection();
400             try {
401                 con.clearWarnings();
402             } catch (Exception ex) {
403             }
404             return con;
405         } catch (Exception ex) {
406         }
407
408         waitFor(500);
409         try {
410             con = getConnection();
411             try {
412                 con.clearWarnings();
413             } catch (Exception ex) {
414             }
415             return con;
416         } catch (Exception ex) {
417         }
418
419         waitFor(2000);
420         con = getConnection();
421         try {
422             con.clearWarnings();
423         } catch (Exception ex) {
424         }
425         return con;
426     }
427
428     protected void waitFor(int millis) {
429         try {
430             if (debug)
431                 debug(Messages.getString("SQLBaseComponent.DEBUG_WAITING_FOR_CONNECTION", Integer.toString(millis))); //$NON-NLS-1$
432 Thread.sleep(millis);
433         } catch (Exception ex) {
434             // ignore the interrupted exception, if it happens
435 }
436     }
437
438     protected IPentahoConnection getConnection() {
439         IPentahoConnection connection = null;
440         try {
441             String jndiName = getInputStringValue(StandardSettings.JNDI);
442             if (jndiName != null) {
443                 connection = PentahoConnectionFactory.getConnection(PentahoConnectionFactory.SQL_DATASOURCE, jndiName, this);
444             }
445             if (connection == null) {
446                 String driver = getInputStringValue(StandardSettings.DRIVER);
447                 String userId = getInputStringValue(StandardSettings.USERID);
448                 String password = getInputStringValue(StandardSettings.PASSWORD);
449                 String connectionInfo = getInputStringValue(StandardSettings.CONNECTION);
450                 if (driver == null && connectionInfo == null) {
451                     // TODO raise an error
452 }
453                 connection = PentahoConnectionFactory.getConnection(PentahoConnectionFactory.SQL_DATASOURCE, driver, connectionInfo, userId, password, this);
454             }
455             if (connection == null) {
456                 error(Messages.getErrorString("SQLBaseComponent.ERROR_0005_INVALID_CONNECTION")); //$NON-NLS-1$
457 return null;
458             }
459             return connection;
460         } catch (Exception e) {
461             error(Messages.getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$
462 }
463         return null;
464     }
465
466     public boolean init() {
467         return true;
468     }
469
470 }
471

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags