Java > Open Source Codes > com > genimen > djeneric > repository > rdbms > SqlXlator


1 /*
2  * Copyright (c) 2001-2005 by Genimen BV (www.genimen.com) All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without modification, is permitted
5  * provided that the following conditions are met:
6  * - Redistributions of source code must retain the above copyright notice, this list of conditions
7  * and the following disclaimer.
8  * - Redistributions in binary form must reproduce the above copyright notice, this list of
9  * conditions and the following disclaimer in the documentation and/or other materials
10  * provided with the distribution.
11  * - All advertising materials mentioning features or use of this software must display the
12  * following acknowledgment: "This product includes Djeneric."
13  * - Products derived from this software may not be called "Djeneric" nor may
14  * "Djeneric" appear in their names without prior written permission of Genimen BV.
15  * - Redistributions of any form whatsoever must retain the following acknowledgment: "This
16  * product includes Djeneric."
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL GENIMEN BV, DJENERIC.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30 package com.genimen.djeneric.repository.rdbms;
31
32 import java.util.ArrayList ;
33 import java.util.HashMap ;
34 import java.util.Iterator ;
35 import java.util.Stack ;
36
37 import com.genimen.djeneric.language.Messages;
38 import com.genimen.djeneric.repository.DjContext;
39 import com.genimen.djeneric.repository.DjExtent;
40 import com.genimen.djeneric.repository.DjProperty;
41 import com.genimen.djeneric.repository.exceptions.CatalogException;
42 import com.genimen.djeneric.repository.exceptions.ObjectNotDefinedException;
43 import com.genimen.djeneric.repository.sqlparser.SqlParser;
44 import com.genimen.djeneric.repository.sqlparser.core.ParseException;
45 import com.genimen.djeneric.repository.sqlparser.core.TokenContext;
46 import com.genimen.djeneric.structure.PropertyUsage;
47 import com.genimen.djeneric.util.DjLogger;
48
49 public class SqlXlator extends SqlParser
50 {
51   private final static int MAX_STATEMENTS_COMBINED = 50;
52
53   RdbmsPersistenceManager _mgr;
54   HashMap [] _statementExtentSets = new HashMap [MAX_STATEMENTS_COMBINED];
55   // 50 statements combined in 1 should be more than a human can possibly handle.
56 // Every statement (stmts can be nested, unioned etc!) has it's own hashmap
57 // containing the tables used by the (sub) statement. These hasmaps are
58 // indexed by the tablealias
59
60   ArrayList _columns;
61
62   int _uniqueNumber;
63   int _stmtCount;
64   int _prevStatementType;
65   String _prevTable;
66   // previous statement type in sequential order (top/down)
67 Stack _currentStatementType;
68   Stack _currentStatementIndex;
69
70   DjContext _context;
71
72   final static int DISCOVER = 0;
73   final static int MODIFY = 1;
74
75   int _status;
76   boolean _gatherSqlMetaData = false;
77
78   public final static int TRANSLATE_TO_MAPPING = 0;
79   public final static int TRANSLATE_TO_ALIAS = 1;
80   public final static int TRANSLATE_TO_LOGICAL = 2;
81
82   int _sqlTranslationMode = TRANSLATE_TO_MAPPING;
83
84   public SqlXlator(RdbmsPersistenceManager mgr, DjContext context)
85   {
86     _mgr = mgr;
87     _context = context;
88   }
89
90   public void setTranslationMode(int tp)
91   {
92     _sqlTranslationMode = tp;
93   }
94
95   public String getMapping(DjProperty prop)
96   {
97     if (_sqlTranslationMode == TRANSLATE_TO_MAPPING) return prop.getMapping();
98     if (_sqlTranslationMode == TRANSLATE_TO_ALIAS) return prop.getAlias();
99
100     // default to logical
101 return prop.getName();
102   }
103
104   public String translate(String sql) throws Exception
105   {
106     for (int i = 0; i < _statementExtentSets.length; i++)
107     {
108       _statementExtentSets[i] = null;
109     }
110
111     _currentStatementType = new Stack ();
112     _currentStatementIndex = new Stack ();
113     _columns = new ArrayList ();
114
115     setStatement(sql);
116
117     _uniqueNumber = 0;
118     _stmtCount = 0;
119     _status = DISCOVER;
120     parse();
121
122     _uniqueNumber = 0;
123     _status = MODIFY;
124     return parse();
125   }
126
127   // returns a ArrayList of PropertyUsage objects. A column usage is either based on a column, or on an expression
128 public ArrayList getMetaData()
129   {
130     return _columns;
131   }
132
133   public void setGatherMetaData(boolean b)
134   {
135     _gatherSqlMetaData = b;
136   }
137
138   public boolean isGatheringMetaData()
139   {
140     return _gatherSqlMetaData;
141   }
142
143   int getCurrentStatementType()
144   {
145     Integer i = (Integer ) _currentStatementType.peek();
146     return i.intValue();
147   }
148
149   int getCurrentStatementIndex()
150   {
151     Integer i = (Integer ) _currentStatementIndex.peek();
152     return i.intValue();
153   }
154
155   HashMap getExtentSet()
156   {
157     return getExtentSet(getCurrentStatementIndex());
158   }
159
160   HashMap getExtentSet(int idx)
161   {
162     if (_statementExtentSets[idx] == null)
163     {
164       _statementExtentSets[idx] = new HashMap ();
165     }
166     return _statementExtentSets[idx];
167   }
168
169   public void enterStatement(TokenContext ctxt, int /*TokenContext.*/
170   statementType)
171   {
172     _uniqueNumber++;
173     _stmtCount++;
174     _currentStatementIndex.push(new Integer (_uniqueNumber));
175     _currentStatementType.push(new Integer (statementType));
176   }
177
178   public void exitStatement(TokenContext ctxt, int /*TokenContext.*/
179   statementType, StringBuffer theStatement)
180   {
181     _prevStatementType = statementType;
182     // previous statement type in sequential order (top/down)
183 _currentStatementType.pop();
184     _currentStatementIndex.pop();
185   }
186
187   public void handleTable(TokenContext ctxt, StringBuffer tableName, StringBuffer tableAlias) throws ParseException
188   {
189     try
190     {
191       stripQuotes(tableName);
192       // in case of quoted identifiers
193 stripQuotes(tableAlias);
194
195       _prevTable = tableName.toString();
196       if (_status == DISCOVER)
197       {
198         String name = tableName.toString().toLowerCase();
199         String alias = name;
200         if (tableAlias.length() != 0)
201         {
202           alias = tableAlias.toString().toLowerCase();
203         }
204
205         RdbmsExtent t = new RdbmsExtent(name, name, alias, alias, name, name, name);
206         getExtentSet().put(alias, t);
207       }
208       else
209       {
210         // MODIFY
211 // Selects should always use an alias cause of object_type in the where,
212 // so if necessary add the table name itself as an alias
213 if ((tableAlias.length() == 0) && (getCurrentStatementType() == TokenContext.STMT_TYPE_SELECT))
214         {
215           tableAlias.append(tableName.toString().toLowerCase());
216         }
217
218         if (_sqlTranslationMode == TRANSLATE_TO_MAPPING)
219         {
220           tableName.setLength(0);
221           tableName.append(RdbmsPersistenceManager.POLYMORPH_TABLE);
222         }
223         else if (_sqlTranslationMode == TRANSLATE_TO_ALIAS)
224         {
225           DjExtent table = _mgr.getExtent(tableName.toString());
226           tableName.setLength(0);
227           tableName.append(table.getAlias());
228         }
229         else
230         {
231           DjExtent table = _mgr.getExtentByObjectType(tableName.toString());
232           tableName.setLength(0);
233           tableName.append(table.getObjectType());
234         }
235       }
236     }
237     catch (ObjectNotDefinedException x)
238     {
239       throw new CatalogException(x);
240     }
241   }
242
243   public DjProperty findProperty(String tableAlias, String columnName, StringBuffer foundInTableAlias)
244       throws ParseException
245   {
246     foundInTableAlias.setLength(0);
247     if (tableAlias.length() != 0)
248     {
249       DjExtent ext = (DjExtent) getExtentSet().get(tableAlias);
250       if (ext == null)
251       {
252         // Table alias not in the current statement. This could mean a
253 // correlated query i.e. referencing a table in another
254 // (sub) statement. So go find it there then
255 for (int i = 0; i < _stmtCount; i++)
256         {
257           ext = (DjExtent) getExtentSet(i).get(tableAlias);
258           if (ext != null) break;
259           // Found it!
260 }
261       }
262
263       // If it's still not found we have an error on our hands
264 if (ext == null) throw new CatalogException(Messages.getString("SqlXlator.TableAliasNotDefined", tableAlias));
265
266       try
267       {
268         DjExtent table = _mgr.getExtent(ext.getName());
269         DjProperty col = table.getProperty(columnName);
270         foundInTableAlias.append(ext.getAlias().toLowerCase());
271
272         return col;
273       }
274       catch (ObjectNotDefinedException x)
275       {
276         throw new CatalogException(x.getMessage());
277       }
278     }
279     else
280     {
281       // try to find by searching all tables
282 DjProperty theCol = null;
283       Iterator allTables = getExtentSet().values().iterator();
284       while (allTables.hasNext())
285       {
286         DjExtent ext = (DjExtent) allTables.next();
287
288         DjExtent table;
289         try
290         {
291           table = _mgr.getExtent(ext.getName());
292           if (table.hasProperty(columnName))
293           {
294             if (theCol != null) throw new CatalogException(Messages.getString("SqlXlator.ColumnWithoutAlias",
295                                                                               columnName));
296             theCol = table.getProperty(columnName);
297             foundInTableAlias.append(ext.getAlias().toLowerCase());
298           }
299         }
300         catch (ObjectNotDefinedException x)
301         {
302           throw new CatalogException(x.getMessage());
303         }
304       }
305       return theCol;
306     }
307   }
308
309   private void stripQuotes(StringBuffer b)
310   {
311     if (b.length() == 0) return;
312
313     if (b.charAt(0) == '\"') b.deleteCharAt(0);
314     if (b.charAt(b.length() - 1) == '\"') b.deleteCharAt(b.length() - 1);
315   }
316
317   String _firstColumnNameSinceExpression = null;
318   DjProperty _firstColumnSinceExpression = null;
319
320   public void handleColumn(TokenContext ctxt, StringBuffer tableAlias, StringBuffer columnName) throws ParseException
321   {
322     if (_status == DISCOVER) return;
323
324     stripQuotes(tableAlias);
325     // in case of quoted identifiers
326 stripQuotes(columnName);
327
328     StringBuffer foundInTableAlias = new StringBuffer (40);
329
330     DjProperty col = findProperty(tableAlias.toString(), columnName.toString(), foundInTableAlias);
331     if (col == null) throw new CatalogException(Messages.getString("SqlXlator.ColumnNotDefined", columnName));
332
333     if (_firstColumnNameSinceExpression == null)
334     {
335       _firstColumnNameSinceExpression = columnName.toString();
336       _firstColumnSinceExpression = col;
337     }
338
339     columnName.setLength(0);
340     columnName.append(getMapping(col));
341     // We must always use a column alias in selects
342 if ((tableAlias.length() == 0) && (getCurrentStatementType() == TokenContext.STMT_TYPE_SELECT))
343     {
344       tableAlias.append(foundInTableAlias.toString());
345     }
346   }
347
348   public boolean isSingleColumnExpression(StringBuffer expr)
349   {
350     final String markers = " *()-+=|<>/";
351     for (int i = 0; i < expr.length(); i++)
352     {
353       if (markers.indexOf(expr.charAt(i)) != -1) return false;
354     }
355     return true;
356   }
357
358   private void processMetadata(DjProperty col, String alias)
359   {
360     if (isGatheringMetaData() && getCurrentStatementIndex() == 1)
361     {
362       // only register top level columns
363 _columns.add(new PropertyUsage(col, alias));
364     }
365   }
366
367   private void processMetadata(String expression, String alias)
368   {
369     if (isGatheringMetaData() && getCurrentStatementIndex() == 1)
370     {
371       // only register top level columns
372 _columns.add(new PropertyUsage(expression, alias));
373     }
374   }
375
376   public void handleSelectedColumnExpression(TokenContext ctxt, StringBuffer expression, StringBuffer aliasName)
377   {
378     if (_status == DISCOVER) return;
379
380     stripQuotes(aliasName);
381     // in case of quoted identifiers
382
383     String metadataAlias = aliasName.toString().toLowerCase();
384     // We force every column to have an alias, this is needed because the alias is the only
385 // way to keep a reference to the external column. This column itself is renamed to the internal
386 // name. Side effect is that an expression that does not have an alias will get the
387 // alias of the first column that is used in the expression.
388 // So, whenever an expression is defined in the statement it is safest to use an alias.
389 if (aliasName.length() == 0)
390     {
391       if (_firstColumnNameSinceExpression != null && isSingleColumnExpression(expression))
392       {
393         metadataAlias = _firstColumnNameSinceExpression.toString().toLowerCase();
394         aliasName.append(metadataAlias);
395       }
396       else
397       {
398         aliasName.append("\"" + expression + "\"");
399         metadataAlias = expression.toString().toLowerCase();
400       }
401     }
402
403     if (_firstColumnNameSinceExpression != null && isSingleColumnExpression(expression))
404     {
405       processMetadata(_firstColumnSinceExpression, metadataAlias);
406     }
407     else
408     {
409       processMetadata(expression.toString(), metadataAlias);
410     }
411
412     _firstColumnNameSinceExpression = null;
413     _firstColumnSinceExpression = null;
414   }
415
416   boolean appendInternalWhere(StringBuffer result) throws ObjectNotDefinedException
417   {
418     Iterator allTables = getExtentSet().values().iterator();
419
420     boolean addAnd = false;
421     boolean stuffAdded = false;
422
423     while (allTables.hasNext())
424     {
425       DjExtent ext = (DjExtent) allTables.next();
426       DjExtent table = _mgr.getExtent(ext.getName());
427
428       if (_sqlTranslationMode == TRANSLATE_TO_MAPPING)
429       {
430         if (addAnd)
431         {
432           result.append("\nand ");
433         }
434
435         // The delete and update are always on 1 table so we do not need/shouldn't use an alias
436 // So only the select should.
437 if (getCurrentStatementType() == TokenContext.STMT_TYPE_SELECT)
438         {
439           result.append(ext.getAlias().toLowerCase() + ".");
440         }
441         result.append(RdbmsPersistenceManager.INTERNAL_TYPE_COLUMN + " = '" + table.getInternalCode() + "' ");
442         addAnd = true;
443         stuffAdded = true;
444
445         if (_context != null)
446         {
447           // is the statement within a specific context?
448 if (addAnd) result.append("\nand ");
449           if (getCurrentStatementType() == TokenContext.STMT_TYPE_SELECT)
450           {
451             result.append(ext.getAlias().toLowerCase() + ".");
452           }
453           result.append(RdbmsPersistenceManager.INTERNAL_CONTEXT_COLUMN + " = :"
454                         + RdbmsPersistenceManager.CONTEXT_PARAM_NAME);
455           addAnd = true;
456           stuffAdded = true;
457         }
458       }
459     }
460     return stuffAdded;
461   }
462
463   public void handleWhereClause(TokenContext ctxt, StringBuffer where) throws ParseException
464   {
465     if (_status == DISCOVER) return;
466
467     StringBuffer result = new StringBuffer (200);
468
469     try
470     {
471       boolean stuffAdded = appendInternalWhere(result);
472       if (where.length() != 0)
473       {
474         if (stuffAdded) result.append("\nand (");
475         else result.append("(");
476         result.append(where.toString());
477         result.append(")");
478       }
479       where.setLength(0);
480       where.append(result.toString());
481     }
482     catch (ObjectNotDefinedException x)
483     {
484       DjLogger.log(x);
485       throw new CatalogException(x.getMessage());
486     }
487   }
488
489   public void handleSelectFullRecord(TokenContext ctxt, String tableAlias, StringBuffer fullRecordSelect)
490       throws ParseException
491   {
492     if (_status == DISCOVER) return;
493
494     try
495     {
496       tableAlias = tableAlias.toLowerCase();
497       // we store them in the hash as lower, so fix that now too.
498 if (tableAlias.length() == 0)
499       {
500         // Just a *?
501 // This is tricky, we assume there is one table, so if we get the first from the HashMap were ok..
502 Iterator it = getExtentSet().values().iterator();
503         if (!it.hasNext()) throw new CatalogException(Messages.getString("SqlXlator.MissingTableSpec"));
504         tableAlias = ((DjExtent) it.next()).getAlias().toLowerCase();
505         if (it.hasNext()) throw new CatalogException(Messages.getString("SqlXlator.MultiTableSpec"));
506       }
507       DjExtent ext = (DjExtent) getExtentSet().get(tableAlias);
508       if (ext == null) throw new CatalogException(Messages.getString("SqlXlator.AliasNotDefined", tableAlias));
509
510       DjExtent table = _mgr.getExtent(ext.getName());
511       DjProperty cols[] = table.getProperties();
512       fullRecordSelect.setLength(0);
513       for (int i = 0; i < cols.length; i++)
514       {
515         fullRecordSelect.append(ext.getAlias().toLowerCase() + "." + getMapping(cols[i]) + " as " + cols[i].getName());
516         if (i != cols.length - 1) fullRecordSelect.append(", ");
517         processMetadata(cols[i], cols[i].getName());
518       }
519     }
520     catch (ObjectNotDefinedException x)
521     {
522       DjLogger.log(x);
523       throw new CatalogException(x.getMessage());
524     }
525   }
526
527   public void handleSelectList(TokenContext ctxt, StringBuffer selectList)
528   {
529     if (_status == DISCOVER) return;
530     if (_prevStatementType == TokenContext.STMT_TYPE_INSERT)
531     {
532       // Are we dealing with the insert part of an insert/select?
533 selectList.insert(0, "'" + _prevTable + "', ");
534       // registered by the handleTable
535 }
536   }
537
538   int _insertValueCount;
539
540   public void handleInsertColumnList(TokenContext ctxt, StringBuffer insertColumns) throws ParseException
541   {
542     if (_status == DISCOVER) return;
543
544     if (insertColumns.length() == 0)
545     {
546       // insert without a column spec? (yuck!!)
547 try
548       {
549         Iterator allTables = getExtentSet().values().iterator();
550         while (allTables.hasNext())
551         {
552           DjExtent ext = (DjExtent) allTables.next();
553           DjExtent table = _mgr.getExtent(ext.getName());
554           for (int i = 0; i < table.getPropertyCount() && i < _insertValueCount; i++)
555           {
556             if (i != 0) insertColumns.append(", ");
557             insertColumns.append(getMapping(table.getProperty(i)));
558           }
559         }
560       }
561       catch (ObjectNotDefinedException x)
562       {
563         throw new CatalogException(x.getMessage());
564       }
565     }
566     insertColumns.insert(0, RdbmsPersistenceManager.INTERNAL_TYPE_COLUMN + ", ");
567     if (_context != null && _sqlTranslationMode == TRANSLATE_TO_MAPPING)
568     {
569       insertColumns.insert(0, RdbmsPersistenceManager.INTERNAL_CONTEXT_COLUMN + ", ");
570     }
571   }
572
573   public void handleInsertValueList(TokenContext ctxt, StringBuffer insertColumns) throws ParseException
574   {
575     if (_status == DISCOVER)
576     {
577       //count the number of values in the statement, needed later by handleInsertColumnList()
578 _insertValueCount = 1;
579       for (int i = 0; i < insertColumns.length(); i++)
580         if (insertColumns.charAt(i) == ',') _insertValueCount++;
581     }
582     else try
583     {
584       Iterator allTables = getExtentSet().values().iterator();
585       while (allTables.hasNext())
586       {
587         DjExtent ext = (DjExtent) allTables.next();
588         DjExtent table = _mgr.getExtent(ext.getName());
589         insertColumns.insert(0, "'" + table.getInternalCode() + "', ");
590         if (_context != null && _sqlTranslationMode == TRANSLATE_TO_MAPPING)
591         {
592           insertColumns.insert(0, ":" + RdbmsPersistenceManager.CONTEXT_PARAM_NAME + ", ");
593         }
594       }
595     }
596     catch (ObjectNotDefinedException x)
597     {
598       DjLogger.log(x);
599       throw new CatalogException(x.getMessage());
600     }
601   }
602
603 }

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags