Source code: com/rohanclan/ashpool/core/SelectFilter.java
1 /*
2 * Ashpool - XML Database
3 * Copyright (C) 2003 Rob Rohan
4 * This program is free software; you can redistribute it and/or modify it
5 * under the terms of the GNU General Public License as published by the
6 * Free Software Foundation; either version 2 of the License, or (at your
7 * option) any later version.
8 *
9 * This program is distributed in the hope that it will be useful, but
10 * WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 * General Public License for more details.
13 *
14 * You should have received a copy of the GNU General Public License along
15 * with this program; if not, write to the Free Software Foundation, Inc.,
16 * 675 Mass Ave, Cambridge, MA 02139, USA.
17 *
18 *
19 * SelectFilter.java
20 *
21 * Created on February 1, 2003, 10:16 AM
22 */
23
24 package com.rohanclan.ashpool.core;
25
26 import java.util.*;
27 import java.sql.*;
28 import org.xml.sax.*;
29 import org.xml.sax.helpers.*;
30 import java.io.*;
31
32 /**
33 * Tries to take an SQL statement, turn it into a style sheet and apply it to
34 * an xml file to get a ResultSet. Attempts to emulate an sql select statment
35 * @author rob
36 */
37 public class SelectFilter {
38 private String mainfile;
39 private TableManager tableman;
40 private BasicXSLEngine bXSL;
41 private XMLReader reader;
42 private XMLtoResultSetFilter xmlfilter;
43
44 /** Creates a new instance of SelectFilter */
45 public SelectFilter(TableManager tman) throws Exception{
46 tableman = tman;
47 //bXSL = new BasicXSLEngine();
48 reader = XMLReaderFactory.createXMLReader("com.icl.saxon.aelfred.SAXDriver");
49 }
50
51 /** sets the table manager */
52 public void setTableManager(TableManager tman){
53 tableman = tman;
54 }
55
56 /** sets the xslt engine */
57 public void setXSLEngine(BasicXSLEngine bxsl){
58 this.bXSL = bxsl;
59 }
60
61 /** get the names, and types of a tables columns
62 */
63 public void getTableColumns(String tablename, AResultSet ars) throws Exception {
64
65 bXSL.clearParams();
66
67 //get a result stream
68 java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
69 java.io.InputStream is;
70
71 bXSL.setParam("tablename", tablename + TableManager.TABLEEXT);
72
73 if(tableman.hasSchema(tablename)){
74 //get the stylesheet for a table with a schema
75 is = this.getClass().getResourceAsStream(
76 "/com/rohanclan/ashpool/libxslt/getColumnNamesSchema.xsl"
77 );
78 bXSL.setParam("schemaname", tablename + TableManager.TABLESCHEMA);
79 //apply the stylesheet to the schema
80 bXSL.transform(tableman.getSchemaInputStream(tablename),is,baos);
81 }else{
82 //get the stylesheet for a table without a schema
83 is = this.getClass().getResourceAsStream(
84 "/com/rohanclan/ashpool/libxslt/getColumnNames.xsl"
85 );
86 //apply the stylesheet to the table
87 bXSL.transform(tableman.getTableInputStream(tablename),is,baos);
88 }
89
90
91 //create a new resultset filter
92 xmlfilter = new XMLtoResultSetFilter(reader, ars);
93 //make an out an in
94 InputSource inputSource = new InputSource(
95 new ByteArrayInputStream(baos.toByteArray())
96 );
97
98 //fill in the resultset with the trasfrom results
99 xmlfilter.parse(inputSource);
100 }
101
102 /** returns the XML document results in a bytearray from the generated xslt
103 * from the sql statement */
104 public byte[] executeQuery(String sql) throws Exception{
105 java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
106
107 //System.out.println(createXPath(sql));
108 //create a style sheet from the sql, and get it ready to transform
109 java.io.ByteArrayInputStream bais = new java.io.ByteArrayInputStream(
110 createXPath(sql).getBytes("UTF-8")
111 );
112
113 //apply the stylesheet and get the results
114 long ttime = bXSL.transform(tableman.getTableInputStream(mainfile),bais,baos);
115
116 System.out.println("** Select Took: " + ttime + "ms");
117
118 return baos.toByteArray();
119 }
120
121 /** fills a result set with with the results of an sql->xslt query
122 */
123 public void executeQuery(String sql, AResultSet ars) throws Exception{
124 //get the results of the sql into an input source
125 InputSource inputSource = new InputSource(
126 new ByteArrayInputStream(executeQuery(sql))
127 );
128
129 //resultset build timer
130 long ttime = new java.util.Date().getTime();
131
132 //run the results through the XML->ResultSet
133 xmlfilter = new XMLtoResultSetFilter(reader, ars);
134
135 xmlfilter.parse(inputSource);
136
137 System.out.println("** XML->ResultSet Took: "
138 + (new java.util.Date().getTime() - ttime) + "ms");
139 }
140
141 /** try to convert an sql select statement into an xslt stylesheet */
142 private String createXPath(String sql){
143 StringTokenizer stok = new StringTokenizer(sql," ");
144
145 StringBuffer whereclause = new StringBuffer();
146 StringBuffer fieldclause = new StringBuffer("*");
147 StringBuffer orderbyclause = new StringBuffer();
148 StringBuffer orderbyclauseU = new StringBuffer();
149
150 //skip 'select'
151 stok.nextElement();
152
153 //get the field names
154 String fieldnames="";
155 while(!fieldnames.toLowerCase().equals("from")){
156 fieldnames = stok.nextElement().toString();
157
158 if(fieldnames.equals("*")){
159 //select *
160 }else if(fieldnames.toLowerCase().startsWith("sum(")
161 || fieldnames.toLowerCase().startsWith("count(")){
162 //is this a function?
163 fieldclause.append(fieldnames);
164 }else if(fieldnames.toLowerCase().equals("as")){
165 //nothing just yet
166 }else if(fieldnames.equals("(")){
167 //subquery
168 }else if(fieldnames.equals(")")){
169 //end subquery
170 }else if(!fieldnames.toLowerCase().equals("from")){
171 //anything else should be a field name
172 //start the field clause if needed
173 if(fieldclause.length() == 1){
174 fieldclause.append("[");
175 }
176 if(fieldnames.endsWith(",")){
177 //trim out the comma
178 fieldclause.append(" name() = '"
179 + fieldnames.toString().trim().substring(0,fieldnames.length() -1) + "' "
180 );
181 //get ready for more field names
182 fieldclause.append("or");
183 }else{
184 //this is the last field name specified
185 fieldclause.append(" name() = '"
186 + fieldnames.toString().trim() + "' "
187 );
188
189 fieldclause.append("]");
190 }
191 }
192 }
193
194 //get the handle to the file
195 if(fieldnames.toLowerCase().equals("from")){
196 mainfile = stok.nextElement().toString();
197 }
198
199
200 if(stok.hasMoreElements()){
201 //if there is a where clause, the begining of the xpath command needs
202 //to include all child nodes
203 /* [clientid = 3 or firstname = 'Steve'] */
204 StringBuffer andorwhere = new StringBuffer();
205 StringBuffer currentcolumn = new StringBuffer();
206 StringBuffer searchop = new StringBuffer();
207
208 while(stok.hasMoreElements()){
209 //andorwhere = stok.nextElement().toString().toLowerCase();
210 andorwhere.delete(0, andorwhere.length());
211 andorwhere.append(stok.nextElement().toString());
212
213 if(andorwhere.toString().toLowerCase().equals("where")){
214 whereclause.append("[");
215 }else if(andorwhere.toString().equals(")")){
216 whereclause.append(" " + andorwhere.toString());
217 if(!stok.hasMoreElements()){
218 break;
219 }
220 }else if(andorwhere.toString().toLowerCase().equals("order")
221 || andorwhere.toString().toLowerCase().equals("by")){
222
223 //System.out.println("found order");
224 break;
225 }else{
226 whereclause.append(" " + andorwhere.toString() + " ");
227 }
228
229 //check for a grouping marker and group if need be, else just
230 //add the column to the where clause
231 andorwhere.delete(0, andorwhere.length());
232 andorwhere.append(stok.nextElement().toString());
233
234 if(andorwhere.toString().equals("(")){
235 //open parent + real and or where clause
236 whereclause.append(" " + andorwhere.toString() + " ");
237
238 currentcolumn.delete(0, currentcolumn.length());
239 currentcolumn.append(stok.nextElement().toString() + " ");
240
241 whereclause.append(currentcolumn.toString());
242 }else if(andorwhere.toString().equals(")")){
243 whereclause.append(" " + andorwhere.toString());
244 if(!stok.hasMoreElements()){
245 break;
246 }
247 }else{
248
249 currentcolumn.delete(0,currentcolumn.length());
250 currentcolumn.append(andorwhere + " ");
251
252 whereclause.append(currentcolumn.toString());
253 }
254
255 String opr = stok.nextElement().toString();
256
257 searchop.delete(0,searchop.length());
258 searchop.append(escapeOperator(opr));
259
260 if(searchop.toString().equals("= contains(")){
261 searchop.append(currentcolumn + ",");
262 //searchop.append(stok.nextElement().toString());
263
264 if(stok.hasMoreTokens()){
265 String criteria = stok.nextElement().toString();
266 if(criteria.startsWith("'")){
267 while(!criteria.endsWith("'")){
268 searchop.append(" " + criteria.toString());
269 criteria = stok.nextElement().toString();
270 }
271 searchop.append(" " + criteria.toString());
272 }else{
273 searchop.append(" " + criteria.toString() + " ");
274 }
275 }
276
277 searchop.append(") ");
278 whereclause.append(searchop.toString());
279 }else{
280 whereclause.append(searchop.toString());
281
282 if(stok.hasMoreTokens()){
283 String criteria = stok.nextElement().toString();
284 if(criteria.startsWith("'")){
285 while(!criteria.endsWith("'")){
286 whereclause.append(" " + criteria.toString());
287 criteria = stok.nextElement().toString();
288 }
289 whereclause.append(" " + criteria.toString());
290 }else{
291 whereclause.append(" " + criteria.toString() + " ");
292 }
293 }
294 }
295 }
296 //close out the where clause
297 if(whereclause.length() > 0){
298 whereclause.append("]");
299 }
300
301 //the where clause should break if it hits the word order
302 //pickup order by clase here
303 //this could look like order by clientid desc, firstname, lastname desc
304
305 //only need to override if desc
306 String xslorder = "ascending";
307 //text or numeric (only text right now)
308 String xsltype = "text";
309 StringBuffer currentkeyword = new StringBuffer();
310 while(stok.hasMoreElements()){
311 //String currentkeyword = stok.nextElement().toString().toLowerCase();
312 currentkeyword.delete(0, currentkeyword.length());
313 currentkeyword.append(stok.nextElement().toString());
314
315 if(currentkeyword.toString().equals("by")){
316 //first run
317 }else if(currentkeyword.toString().toLowerCase().equals("desc")
318 || currentkeyword.toString().toLowerCase().equals("desc,")){
319
320 xslorder = "descending";
321 orderbyclause.append(O_sort + xslorder + D_sort + xsltype + E_sort);
322 }else if(currentkeyword.toString().toLowerCase().equals("asc")
323 || currentkeyword.toString().toLowerCase().equals("asc,")){
324
325 xslorder = "ascending";
326 orderbyclause.append(O_sort + xslorder + D_sort + xsltype + E_sort);
327 }else{
328 //this is a column specificaion this could end with a comma
329 if(currentkeyword.toString().endsWith(",")){
330 currentkeyword.delete(currentkeyword.length() - 1,currentkeyword.length());
331 }
332 orderbyclause.append(S_sort + currentkeyword);
333 }
334
335 }
336
337 }
338 //build the style sheet we are goint to use
339 //Start (whereclause) After where (fields) After limit
340 return S_XSLT
341 + whereclause.toString()
342 + SF_XSLT
343 + orderbyclause.toString()
344 + AS_XSLT
345 + AW_XSLT + fieldclause.toString()
346 + AL_XSLT;
347 }
348
349 /** convert normal sql operators to xml operators */
350 private String escapeOperator(String inop){
351 String opr = "";
352 if(inop.equals(">")){
353 opr = ">";
354 }else if(inop.equals("<")){
355 opr = "<";
356 }else if(inop.equals(">=")){
357 opr = ">=";
358 }else if(inop.equals("<=")){
359 opr = "<=";
360 }else if(inop.toLowerCase().trim().equals("like")){
361 opr = "= contains(";
362 }else{
363 opr = inop;
364 }
365 return opr;
366 }
367
368 ////////////////////////////////////////////////////////////////////////////
369 /** the basics of the xslt stylesheet */
370 private static final String S_XSLT ="<?xml version=\"1.0\" encoding=\"utf-8\"?>"
371 + "<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">"
372 + "<xsl:output method=\"xml\" indent=\"no\" encoding=\"UTF-8\"/>"
373 + "<xsl:template match=\"/\">"
374 + "<t>"
375 + "<xsl:apply-templates select=\"*/*"; /* [clientid = 3 or firstname = 'Steve'] */
376
377 private static final String SF_XSLT = "\">";
378
379 /* sort items go here */
380 private static final String AS_XSLT= "</xsl:apply-templates>"
381 + "</t>"
382 + "</xsl:template>"
383
384 + "<xsl:template match=\"*/*\">" ;
385
386 private static final String AW_XSLT = //"\">"
387 "<r>"
388 + "<xsl:copy-of select=\""; /* [name() = 'firstname' or name() = 'lastname'] */
389 private static final String AL_XSLT = "\"/>"
390 + "</r>"
391 + "</xsl:template>"
392 + "<xsl:template match=\"text()\"/>"
393 + "</xsl:stylesheet>";
394
395 ///////////////////////////////////////////////////////////////////////////
396 /** the basics of the a sort */
397 private static final String S_sort ="<xsl:sort select=\"";
398 private static final String O_sort ="\" order=\"";
399 private static final String D_sort ="\" data-type=\"";
400 private static final String E_sort ="\"/>";
401 }