View Javadoc

1   /* (c) Copyright 2003 Caleigo AB, All rights reserved. 
2    * 
3    * This library is free software; you can redistribute it and/or
4    * modify it under the terms of the GNU Lesser General Public
5    * License as published by the Free Software Foundation; either
6    * version 2.1 of the License, or (at your option) any later version.
7    * 
8    * This library is distributed in the hope that it will be useful,
9    * but WITHOUT ANY WARRANTY; without even the implied warranty of
10   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
11   * Lesser General Public License for more details.
12   * 
13   * You should have received a copy of the GNU Lesser General Public
14   * License along with this library; if not, write to the Free Software
15   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
16   *  
17   */
18  
19  package org.caleigo.core.service;
20  
21  
22  import java.util.*;
23  import java.sql.Timestamp;
24  import java.text.*;
25  
26  import org.caleigo.core.*;
27  import org.caleigo.core.exception.*;
28  
29  
30  /*** This class containes help methods for building valid SQL language
31   * statements based on CEL entities, descriptors and qualifiers.
32   *
33   * The class can be used by any IDataService implementation that connects
34   * to SQL based databases.
35   *
36   * @author  Dennis Zikovic
37   * @author  Niklas Norberg
38   * @version 1.00
39   * 
40   *//* 
41   *
42   * WHEN        WHO               WHY & WHAT
43   * -----------------------------------------------------------------------------
44   * 2001-07-24  Dennis Zikovic    Creation
45   * 2004-03-31  Niklas Norberg    Reimplementation for composite
46   */
47  public class SQLToolKit implements java.io.Serializable
48  {
49      
50      // Constants ---------------------------------------------------------------
51      private static final String CREATE = "CREATE TABLE ";
52      private static final String SELECT = "SELECT ";
53      private static final String INSERT = "INSERT INTO ";
54      private static final String UPDATE = "UPDATE ";
55      private static final String SET    = "SET ";
56      private static final String DELETE = "DELETE FROM ";
57      private static final String FROM = "FROM ";
58      private static final String WHERE = "WHERE ";
59      private static final String AND    = "AND ";
60      private static final String ALIAS  = "ALIAS";
61      private static final String ON     = "ON ";
62      private static final String ORDER = "ORDER BY ";
63      private static final String ALTER_TABLE = "ALTER TABLE ";
64      
65      public static final String DATABASE_HSQLDB = "HSQLDB";
66      public static final String DATABASE_ORACLE = "ORACLE";
67      
68      // Class members -----------------------------------------------------------
69      private static boolean sIsQuotingIdentifiersByDefault = true;
70      private static boolean sUseUppercaseIdentifiersByDefault = false;
71      private static String sDefaultQuotingString = "\"";
72      
73      // Data members ------------------------------------------------------------
74      private Map mDataTypeConverterMap;
75      private Map mQualifierParserMap;
76      private boolean mIsQuotingIdentifiers = sIsQuotingIdentifiersByDefault;
77      private boolean mIsUsingUppercaseIdentifiers = sUseUppercaseIdentifiersByDefault;
78      private String mQuotingString = sDefaultQuotingString;
79      private String mDatabaseMode;
80      private SQLToolKit.IDatabaseSchema mSchema; 
81      
82      private static Random rnd = new Random();
83      
84      // Static methods ----------------------------------------------------------
85      public static void setIsQuotingIdentifiersByDefault(boolean quote)
86      {
87          sIsQuotingIdentifiersByDefault = quote;
88      }
89      
90      public static boolean isQuotingIdentifiersByDefault()
91      {
92          return sIsQuotingIdentifiersByDefault;
93      }
94      
95      public static void setIsUsingUppercaseIdentifiersByDefault(boolean force)
96      {
97          sUseUppercaseIdentifiersByDefault = force;
98      }
99      
100     public static boolean isUsingUppercaseIdentifiersByDefault()
101     {
102         return sUseUppercaseIdentifiersByDefault;
103     }
104     
105     public static void setDefaultQuotingString(String defaultQuotingString)
106     {
107         sDefaultQuotingString = defaultQuotingString;
108     }
109     
110     public static String getDefaultQuotingString()
111     {
112         return sDefaultQuotingString;
113     }
114     
115     
116     // Constructors ------------------------------------------------------------
117     public SQLToolKit()
118     {
119         // Create type concersion map and load it with default converters.
120         mDataTypeConverterMap = new HashMap();
121         mDataTypeConverterMap.put(DataType.STRING, new StringTypeConverter());
122         mDataTypeConverterMap.put(DataType.BOOLEAN, new BooleanTypeConverter()); 
123         mDataTypeConverterMap.put(DataType.DATE, new DateTypeConverter()); 
124         
125         // Create type parser map and load it with default parsers.
126         mQualifierParserMap = new HashMap();
127         mQualifierParserMap.put(RelationQualifier.class, new RelationQualifierParser());
128         mQualifierParserMap.put(CompositeQualifier.class, new CompositeQualifierParser());
129         mQualifierParserMap.put(NegateQualifier.class, new NegateQualifierParser());
130         mDatabaseMode = DATABASE_HSQLDB;
131         mSchema = new HsqldbDatabaseSchema();
132     }
133     
134     public SQLToolKit(String databaseMode)
135     {
136         this();
137         this.mDatabaseMode = databaseMode;
138         if (DATABASE_HSQLDB.equals(databaseMode)) 
139         {
140             mSchema = new HsqldbDatabaseSchema();
141         } else if (DATABASE_ORACLE.equals(databaseMode)) 
142         {
143             mSchema = new OracleDatabaseSchema();
144         }
145         
146     }
147     
148     // Access methods ----------------------------------------------------------
149     public void addDataTypeConverter(DataType dataType, IDataTypeConverter converter)
150     {
151         mDataTypeConverterMap.remove(dataType);
152         mDataTypeConverterMap.put(dataType, converter);
153     }
154     
155     public IDataTypeConverter getDataTypeConverter(DataType dataType)
156     {
157         return (IDataTypeConverter)mDataTypeConverterMap.get(dataType);
158     }
159     
160     public void addQualifierParser(Class qualifierClass, IQualifierParser parser)
161     {
162         mDataTypeConverterMap.remove(qualifierClass);
163         mDataTypeConverterMap.put(qualifierClass, parser);
164     }
165     
166     public IQualifierParser getQualifierParser(Class qualifierClass)
167     {
168         return (IQualifierParser)mDataTypeConverterMap.get(qualifierClass);
169     }
170     
171     public void setQuotingIdentifiers(boolean allwaysQuote)
172     {
173         mIsQuotingIdentifiers = allwaysQuote;
174     }
175     
176     public void setQuotingString(String quotingString)
177     {
178         mQuotingString = quotingString;
179     }
180     
181     public String getQuotingString()
182     {
183         return mQuotingString;
184     }
185     
186     public boolean isQuotingIdentifiers()
187     {
188         return mIsQuotingIdentifiers;
189     }    
190     
191     // Help methods ------------------------------------------------------------
192     public String buildSelectCommandWithOrder(DataQuery query)
193     {
194         // Build basic select command
195         StringBuffer command = new StringBuffer(1000);
196         command.append(this.buildSelectCommand(query));
197 
198         // Build the ORDER part. 
199         if(query.getEntityCollator()!=null)
200         {
201             EntityCollator collator = query.getEntityCollator();
202             
203             command.append(' ');
204             command.append(ORDER);
205             for(int j=0; j<collator.getFieldCount(); j++)
206             {
207                 if(j>0)
208                     command.append(", ");
209                 command.append(this.formatIdentifier(collator.getFieldCollator(j).getFieldDescriptor().getEntityDescriptor().getSourceName()));
210                 command.append('.');
211                 command.append(this.formatIdentifier(collator.getFieldCollator(j).getFieldDescriptor().getSourceName()));
212                 if(!collator.getFieldCollator(j).isAscending())
213                     command.append(" DESC");
214             }
215         }
216         
217         return command.toString();
218     }
219     
220     /*** Builds the SQL SELECT-command.
221      * @param dataQuery contains data to build a complete and minimal SQL-select
222      * query. The data query's entity descriptor determines the SELECT- and
223      * FROM-expressions. The WHERE-expression are determined by the data query's
224      * qualifier and external qualifier's. In the case the data query's entity
225      * descriptor is an instance of ICompositeEntityDescriptor this descriptor
226      * contains a field group tree that builds JOIN-clauses in the
227      * FROM-expression, it optionally contains a default qualifier that
228      * determines a part of the WHERE-expression.
229      * 
230      * @return a String a'la:</br>
231      * "SELECT EntityDescriptor-CodeName.FieldDescriptor-SourceName FROM
232      * EntityDescriptor-SourceName EntityDescriptor-CodeName WHERE
233      * EntityDescriptor-CodeName.FieldDescriptor-SourceName = 5"</br>
234      * i.e.</br>
235      *  "SELECT E.ID FROM Employees E WHERE E.ID = 5".</br> 
236      */
237     public String buildSelectCommand(DataQuery dataQuery)
238     {      
239         IEntityDescriptor entityDescriptor = dataQuery.getEntityDescriptor();
240         boolean base, composite;
241         base = composite = false;
242         
243         //Validate type of entity descriptor
244         if ( entityDescriptor instanceof IBaseEntityDescriptor )
245             base = true;
246         else if ( entityDescriptor instanceof ICompositeEntityDescriptor )
247             composite = true;
248         else
249             /*throw new InvalidDescriptorException("The instance of the " +
250                     "descriptor must be either of the type " +
251                     "IBaseEntityDescriptor or ICompositeEntityDescriptor");*/
252             base = true;
253         
254         DataQuery.ExternalQualifier[] xTernalQualifiers = dataQuery.getExternalQualifierArray();
255         
256         //Validate the xternalQualifiers
257         for (int i = 0; i < xTernalQualifiers.length; i++)
258             this.validateXternalQualifier(xTernalQualifiers[i], entityDescriptor);
259         
260         SQLSelectCommand sqlSelectCommand = new SQLSelectCommand();
261         
262         //Make aliases uniq
263         if (base)
264         {
265             sqlSelectCommand.setRootAlias(entityDescriptor.getCodeName());
266         }
267         else// if (composite)
268         {
269             sqlSelectCommand.setRootAlias( ((ICompositeEntityDescriptor)entityDescriptor).getRootFieldGroup().getEntityIdentity() );
270             sqlSelectCommand.collectAllEntityRelationPathsAndMakeUniqAliases( ((ICompositeEntityDescriptor)entityDescriptor).getRootFieldGroup() );
271         }
272         
273         sqlSelectCommand.collectAllEntityRelationPathsAndMakeUniqAliases(xTernalQualifiers);
274         
275         //DBG
276 //        sqlSelectCommand.printUniqAliases();
277 //        sqlSelectCommand.printAllPathsAndBelongingArray();
278         
279         // Build the SELECT part.
280         sqlSelectCommand.append(SELECT);
281         if (base)
282             this.appendSelectExpressionForEntityDescriptor( entityDescriptor, sqlSelectCommand );
283         else// if (composite)
284             this.appendSelectExpressionForCompositeEntityDescriptor( (ICompositeEntityDescriptor)entityDescriptor, sqlSelectCommand );
285         sqlSelectCommand.append(' ');
286         
287         // Build the FROM part.
288         sqlSelectCommand.append(FROM);
289         if (base)
290             this.appendFromExpressionForEntityDescriptor(entityDescriptor, sqlSelectCommand);
291         else// if (composite)
292             this.appendFromExpressionForCompositeEntityDescriptor( ((ICompositeEntityDescriptor)entityDescriptor), sqlSelectCommand );
293         sqlSelectCommand.append(' ');
294         
295         //Build the optional INNER JOINS for xternal qualifiers
296         for (int i = 0; i < xTernalQualifiers.length; i++)
297             appendInnerJoinClauseForXternalQualifiers(xTernalQualifiers[i], sqlSelectCommand);
298         
299         //Build the (optional) WHERE part.
300         boolean whereIsStated = false;
301         
302         // include qualifers in xternal qualifiers if any.
303         for (int i = 0; i < xTernalQualifiers.length; i++)
304         {
305             if(i==0)
306             {
307                 sqlSelectCommand.append(WHERE);
308                 whereIsStated = true;
309             }
310             else
311                 sqlSelectCommand.append(AND);
312             IEntityRelationPath path2Qualifer =  xTernalQualifiers[i].getEntityRelationPath();
313             String alias = sqlSelectCommand.getAliasArray(path2Qualifer)[0];
314             sqlSelectCommand.append( this.buildWhereExpression(xTernalQualifiers[i].getQualifer(), alias) );
315             sqlSelectCommand.append(' ');
316         }
317         // include the default qualifier for composite if any.
318         if (composite)
319         {
320             Qualifier defaultQualifier = ((ICompositeEntityDescriptor)entityDescriptor).getDefaultQualifier();
321             if(defaultQualifier!=null)
322             {
323                 if(whereIsStated)
324                     sqlSelectCommand.append(AND);
325                 else
326                 {
327                     sqlSelectCommand.append(WHERE);
328                     whereIsStated = true;
329                 }
330                 sqlSelectCommand.append(this.buildWhereExpression(defaultQualifier, null));
331                 sqlSelectCommand.append(' ');
332             }
333         }
334         // include the search qualifier if any.
335         Qualifier qualifier = dataQuery.getQualifier();
336         if(qualifier!=null)
337         {
338             if(whereIsStated)
339                 sqlSelectCommand.append(AND);
340             else
341                 sqlSelectCommand.append(WHERE);
342             sqlSelectCommand.append(this.buildWhereExpression(qualifier, null));
343         }
344         
345         return sqlSelectCommand.toString();//with minimal numbers of alias!
346     }
347     
348 
349     /*** Appends a String a'la:</br>
350      * "code-name"."EmployeeID", "code-name"."LastName, ..."
351      * 
352      * @param entityDescriptor supplies table name's.
353      * @param sqlSelectCommand appendeding are done to this.
354      */
355     private void appendSelectExpressionForEntityDescriptor(IEntityDescriptor entityDescriptor, SQLSelectCommand sqlSelectCommand)
356     {
357         // Only fetch persistent (non calculated) data-fields from DB.
358         String alias = sqlSelectCommand.getRootAlias(); //==baseEntityDescriptor.getCodeName();
359         
360         for(int j=0; j<entityDescriptor.getDataFieldCount(); j++)
361         {            
362             sqlSelectCommand.append(this.formatIdentifier(alias));
363             sqlSelectCommand.append('.');
364             sqlSelectCommand.append(this.formatIdentifier(entityDescriptor.getFieldDescriptor(j).getSourceName()));
365             sqlSelectCommand.append(',');//obs add's one extra remove this one later
366             sqlSelectCommand.append(' ');
367         }
368         
369         sqlSelectCommand.shortenCommandWith(2);//exclude last ','- & ' '-char.
370     }
371     
372     /*** Appends a string with the source name and code name
373      * of the one and only referenced base entity descriptors a'la:</br>
374      * "Employees Employee"
375      * 
376      * @param baseEntityDescriptor
377      * @param sqlSelectCommand string-appending are done to this.
378      */
379     private void appendFromExpressionForEntityDescriptor(IEntityDescriptor entityDescriptor, SQLSelectCommand sqlSelectCommand)
380     {
381         sqlSelectCommand.append( this.formatIdentifier( entityDescriptor.getSourceName() ) );
382         sqlSelectCommand.append(' ');
383         //sqlSelectCommand.append( this.formatIdentifier( entityDescriptor.getCodeName() ) );
384         //==
385         sqlSelectCommand.append( this.formatIdentifier( sqlSelectCommand.getRootAlias() ) );
386         //mark alias as declared
387         sqlSelectCommand.markAliasAsDeclared(sqlSelectCommand.getRootAlias());
388     }
389         
390      
391     // SELECT HELP METHODS FOR ICompositeEntityDescriptor ----------------------
392     //
393 
394     /*** Appends a String a'la:</br>
395      * "Employee"."ID", "Regions"."Name".
396      * 
397      * @param compositeEntityDescriptor all fields 
398      */
399     private void appendSelectExpressionForCompositeEntityDescriptor(ICompositeEntityDescriptor compositeEntityDescriptor, SQLSelectCommand sqlSelectCommand)
400     {
401         //make call to recursive method
402         this.appendSelectExpressionForCompositeEntityDescriptor(compositeEntityDescriptor.getRootFieldGroup(), sqlSelectCommand);
403         sqlSelectCommand.shortenCommandWith(2);//exclude last ','- & ' '-chars
404     }
405     
406     /*** Help-method, build the select expression RECURSIVELY in bread first 
407      * order in the sub field group tree.
408      */
409     private void appendSelectExpressionForCompositeEntityDescriptor(ICompositeEntityDescriptor.ICompositeFieldGroup fieldGroup, SQLSelectCommand sqlSelectCommand)
410     {
411         for (int i=0; i<fieldGroup.getFieldCount();i++)
412         {
413             sqlSelectCommand.append( this.formatIdentifier(fieldGroup.getEntityIdentity()) );
414             sqlSelectCommand.append('.');
415             sqlSelectCommand.append( this.formatIdentifier(fieldGroup.getFieldDescriptor(i).getSourceName()) );
416             sqlSelectCommand.append(',');//obs add's one extra remove this one later
417             sqlSelectCommand.append(' ');
418         }
419         //make recursive call
420         for(int j=0; j<fieldGroup.getChildFieldGroupCount(); j++)
421             this.appendSelectExpressionForCompositeEntityDescriptor(fieldGroup.getChildFieldGroup(j), sqlSelectCommand);
422     }
423     
424     /*** Appends a String a'la:</br>
425      * "Employees Employee INNER JOIN Region Regions
426      * ON Employee.ID = Regions.EmployeeID FULL OUTER ... ON ..."
427      * 
428      * @param compositeEntityDescriptor supplies a field group tree that contain
429      * data for table name(s) and table name alias(es) to make 
430      * (FULL/LEFT/RIGHT OUTER or INNER) JOIN(s) with belonging ON-clause.
431      */
432     private void appendFromExpressionForCompositeEntityDescriptor(ICompositeEntityDescriptor compositeEntityDescriptor, SQLSelectCommand sqlSelectCommand)
433     {   
434         ICompositeEntityDescriptor.ICompositeFieldGroup rootGroup = compositeEntityDescriptor.getRootFieldGroup();
435         
436         sqlSelectCommand.append( this.formatIdentifier(rootGroup.getBaseEntityDescriptor().getSourceName()) );
437         sqlSelectCommand.append(' ');
438         //sqlSelectCommand.append( this.formatIdentifier(rootGroup.getEntityIdentity()) );
439         //==
440         sqlSelectCommand.append( this.formatIdentifier( sqlSelectCommand.getRootAlias() ) );
441         sqlSelectCommand.append(' ');
442         //mark alias as declared
443         sqlSelectCommand.markAliasAsDeclared(sqlSelectCommand.getRootAlias());
444         //make call to recursive method
445         this.appendJoinClausesForCompositeEntityDescriptor(rootGroup, null, sqlSelectCommand);//start to send a null path
446         sqlSelectCommand.shortenCommandWith(1);//exclude last ' '
447     }
448     
449     /*** Appends the JOIN clause(s) RECURSIVELY in bread first order.
450      * @param parent the field group that are to be joined
451      * with it's children.
452      * @param entityRelationPathInJoinTree the entity relation path that goes
453      * from the ROOT field group to the field group (the parameter parent).
454      * @param sqlSelectCommand string-appending of the join clause(s) are done to this.
455      */
456     private void appendJoinClausesForCompositeEntityDescriptor(ICompositeEntityDescriptor.ICompositeFieldGroup parent,
457                                     EntityRelationPath entityRelationPathInJoinTree,
458                                     SQLSelectCommand sqlSelectCommand)
459     {
460         for(int j=0; j<parent.getChildFieldGroupCount(); j++)
461         {
462             ICompositeEntityDescriptor.ICompositeFieldGroup child = parent.getChildFieldGroup(j);
463             EntityRelationPath entityRelationPath2Append2;
464             if(entityRelationPathInJoinTree ==null)
465                 entityRelationPath2Append2 = new EntityRelationPath(child.getParentRelationPath());
466             else
467             {
468                 //make a copy and append to this copy and send it further
469                 entityRelationPath2Append2 = new EntityRelationPath(entityRelationPathInJoinTree);
470                 entityRelationPath2Append2.appendRelationPath(child.getParentRelationPath());
471             }
472             //appends the join clause that connects the parent and the child
473             this.appendJoinClauseForCompositeEntityDescriptor(parent, child, entityRelationPath2Append2, sqlSelectCommand);
474             //make recursive call
475             this.appendJoinClausesForCompositeEntityDescriptor(child, entityRelationPath2Append2, sqlSelectCommand);
476         }
477     }
478     
479     /*** Appends one JOIN clause (of oftenly several) with belonging ON-clause.
480      * 
481      * @param parentFieldGroup first "table" in the join.
482      * @param childFieldGroup second "table" in the join.
483      * @param entityRelationPathInJoinTree the entity relation path that goes
484      * from the ROOT field group to the child field group (the parameter fieldGroupChild).
485      * @param sqlSelectCommand string-appending of the join clause are done to this.
486      */
487     private void appendJoinClauseForCompositeEntityDescriptor(ICompositeEntityDescriptor.ICompositeFieldGroup parentFieldGroup,
488             ICompositeEntityDescriptor.ICompositeFieldGroup childFieldGroup,
489             EntityRelationPath entityRelationPathInJoinTree,
490             SQLSelectCommand sqlSelectCommand)
491     {
492         IEntityRelationPath parentRelationPath = childFieldGroup.getParentRelationPath();
493         String[] aliases = sqlSelectCommand.getAliasArray(entityRelationPathInJoinTree);
494         
495         int relationCount = parentRelationPath.getRelationCount();
496         if (relationCount == 0)
497             throw new RuntimeException("RelationCount in a \"IEntityRelationPath\" should always be larger than ZERO!");
498         
499         String previousAlias = parentFieldGroup.getEntityIdentity();
500         String nextAlias = "";
501         boolean childTableRequired = false;
502         boolean previousTableRequired = false;
503         
504         //IBaseEntityDescriptor nextBaseEntityDescriptor = (IBaseEntityDescriptor)parentRelationPath.getFirstEntityDescriptor();
505         IEntityDescriptor nextEntityDescriptor = parentRelationPath.getFirstEntityDescriptor();
506         
507         //ALWAYS WALK FORWARD ALONG PATH
508         for (int relationNbr=0; relationNbr<relationCount;relationNbr++)
509         {
510             if (relationNbr == 0)
511             {
512                 // first join and maybe the only one
513                 previousAlias = parentFieldGroup.getEntityIdentity();
514                 if (relationCount == 1)// table1 name1 -rel- table1 name2 or table1 -rel- table2 both are fieldGroups
515                 {
516                     //The one and only join
517                     // left/right/full outer or inner join
518                     previousTableRequired = parentFieldGroup.getRequired();
519                     childTableRequired = childFieldGroup.getRequired();
520                 }
521                 else// table1 -rel- table2 -rel- table3 -rel- table4, first and last are fieldGroups
522                 {
523                     //There are more to come
524                     previousTableRequired = parentFieldGroup.getRequired();
525                     childTableRequired = true; //"middleTable's" always required: => left outer join /inner join
526                 }
527             }
528             else if ( relationNbr > 0 && relationNbr < relationCount-1 )
529             {
530                 //middle join's in the middle of the path
531                 //always INNER JOIN in the middle of the path
532                 previousTableRequired = true;
533                 childTableRequired = true;
534             }
535             else if (relationNbr == relationCount-1 )
536             {
537                 //last join if any
538                 previousTableRequired = true;//"middleTable" always required: => right outer join /inner join
539                 childTableRequired = childFieldGroup.getRequired();
540             }
541             
542             nextEntityDescriptor = getBaseEntityDescriptor2Join2(parentRelationPath, relationNbr, nextEntityDescriptor);
543             String nextTableName = nextEntityDescriptor.getSourceName();
544             int localAliasOffset = aliases.length-parentRelationPath.getRelationCount();
545             nextAlias = aliases[relationNbr+localAliasOffset];
546             
547   
548             //mark alias as declared
549             if( !sqlSelectCommand.isAliasDeclared(nextAlias) )
550             {
551                 sqlSelectCommand.append(getJoinType(previousTableRequired, childTableRequired));//FULL OUTER JOIN | INNER JOIN | ... 
552                 sqlSelectCommand.append( this.formatIdentifier(nextTableName) + " ");//"Employees"
553                 sqlSelectCommand.append( this.formatIdentifier(nextAlias) + " ");//"reportsTo"
554                 String onClause =  getOnClauseSearchCondition(parentRelationPath, relationNbr, previousAlias, nextAlias, nextEntityDescriptor);//ON "Employee"."ReportsTo"="reportsTo"."EmployeeID"
555                 sqlSelectCommand.append(onClause);
556                 //mark alias as declared
557                 sqlSelectCommand.markAliasAsDeclared(nextAlias);
558             }
559             
560             previousAlias = nextAlias;
561         }
562 
563     }
564     
565     /*** Help method to determine the join-clauses JOIN-string.
566      * @return one of the four Strings:</br>
567      * "INNER JOIN "</br>
568      * "RIGHT OUTER JOIN "</br>
569      * "LEFT OUTER JOIN "</br>
570      * "FULL OUTER JOIN ".
571      */
572     private static String getJoinType(boolean firstTableRequired, boolean secondTableRequired)
573     {
574         int parentReq = 00;
575         if (firstTableRequired) parentReq = 10;
576         int childReq = 00;
577         if (secondTableRequired) childReq = 01;
578         int combinationReq = parentReq + childReq;
579         switch (combinationReq){
580             case 11: return "INNER JOIN ";
581             case 10: return "RIGHT OUTER JOIN ";
582             case 01: return "LEFT OUTER JOIN ";
583             case 00: return "FULL OUTER JOIN ";
584         }
585         throw new RuntimeException("Logically unreachable code, just here to satisfy the compiler!");
586     }
587     
588     // END COMPOSITE HELP METHODS ----------------------------------------------
589     
590     
591     
592     // HELP METHODS FOR dealing with JOIN-clauses for external qualifier and
593     // composite entity descriptors --------------------------------------------
594     //
595     
596     /*** Appends an INNER-JOIN string that spans over the entity relation path
597      * in an instance of a DataQuery.ExternalQualifier:</br>
598      * "INNER JOIN Employees theBoss ON Employee.ReportsTo = theBoss.EmployeeID".
599      * 
600      * @param xternalQualifier contains the relation path resulting in an inner
601      * join which connects the "root" (the selected entity descriptor) --- the
602      * last --- and another entity descriptor --- the first --- that is qualified.
603      * @param sqlSelectCommand string-appending of the JOIN-clause are done to this.
604      */
605     private void appendInnerJoinClauseForXternalQualifiers(DataQuery.ExternalQualifier xternalQualifier, SQLSelectCommand sqlSelectCommand)
606     {
607         IEntityRelationPath path2Qualifer =  xternalQualifier.getEntityRelationPath();
608         String[] aliases = sqlSelectCommand.getAliasArray(path2Qualifer);
609         String previousAlias = aliases[aliases.length-1];//Employee
610         //IBaseEntityDescriptor nextBaseEntityDescriptor = (IBaseEntityDescriptor)path2Qualifer.getLastEntityDescriptor();
611         IEntityDescriptor nextEntityDescriptor = path2Qualifer.getLastEntityDescriptor();
612         
613         //Always walk backwards in the path from the "root" out to the qualied entity descriptor 
614         for(int relationNbr = path2Qualifer.getRelationCount()-1; relationNbr>=0;relationNbr--)
615         {
616             nextEntityDescriptor = getBaseEntityDescriptor2Join2(path2Qualifer, relationNbr, nextEntityDescriptor);
617             String nextTableName = nextEntityDescriptor.getSourceName();
618             String nextTableNameAlias = aliases[relationNbr];
619             String onClause =  getOnClauseSearchCondition(path2Qualifer, relationNbr, previousAlias, nextTableNameAlias, nextEntityDescriptor);//ON "Employee"."ReportsTo"="Employee1"."EmployeeID"
620             
621             //nno-todo more to do?
622             sqlSelectCommand.append("INNER JOIN ");
623             if( !sqlSelectCommand.isAliasDeclared(nextTableNameAlias) )
624             {
625                 sqlSelectCommand.append( this.formatIdentifier(nextTableName) );//"Employees"
626                 sqlSelectCommand.append(' ');
627             }
628             else
629             {
630                 //mark alias as declared
631                 sqlSelectCommand.markAliasAsDeclared(nextTableNameAlias);
632             }
633             sqlSelectCommand.append( this.formatIdentifier(nextTableNameAlias) );//"Employee1" (e.g. == "theBoss")
634             sqlSelectCommand.append(' ');
635             sqlSelectCommand.append(onClause);//ON "Employee"."ReportsTo" = "Employee1"."EmployeeID"
636             
637             previousAlias = nextTableNameAlias;
638         }
639     }
640     
641     /*** Help-method that returns the related entity descriptor's 
642      * base entity descriptor.
643      * If the related entity descriptor is an instance of an
644      * IBaseEntityDescriptor the descriptor is just cast and returned.
645      * If the related entity descriptor is an instance of an
646      * ICompositeEntityDescriptor the composite field descriptors field group's
647      * base entity descriptor will be returned.
648      * 
649      * @param entityRelationPath the entity relation path to pick the related
650      * base entity descriptor from.
651      * @param relationNbr determines which relation in the path that is choosen.
652      * @param entityDescriptor2JoinFrom the provided entity descriptor that will
653      * be related.
654      * @return the related base entity descriptor.
655      */
656     private static IEntityDescriptor getBaseEntityDescriptor2Join2(IEntityRelationPath entityRelationPath, int relationNbr, IEntityDescriptor entityDescriptor2JoinFrom)
657     //private static IBaseEntityDescriptor getBaseEntityDescriptor2Join2(IEntityRelationPath entityRelationPath, int relationNbr, IEntityDescriptor entityDescriptor2JoinFrom)
658     {
659         IEntityRelation entityRelation = entityRelationPath.getRelation(relationNbr);
660         IEntityDescriptor related = entityRelation.getRelatedEntityDescriptor(entityDescriptor2JoinFrom);
661         if(related instanceof ICompositeEntityDescriptor)
662         {
663             IFieldDescriptor relatedFieldDescriptor;
664             if( related == entityRelation.getTargetEntityDescriptor() )
665                 relatedFieldDescriptor = entityRelation.getFieldRelation(0).getTargetField();
666             else
667                 relatedFieldDescriptor = entityRelation.getFieldRelation(0).getReferenceField();
668             //if (relatedFieldDescriptor instanceof ICompositeEntityDescriptor.ICompositeFieldDescriptor)
669             return (IBaseEntityDescriptor) ((ICompositeEntityDescriptor.ICompositeFieldDescriptor)relatedFieldDescriptor).getFieldGroup().getBaseEntityDescriptor();
670         }
671         else if (related instanceof IBaseEntityDescriptor)
672             return (IBaseEntityDescriptor)related;
673         else if (related instanceof IEntityDescriptor)
674                 return related;
675         else//this code is never used with this if-else statements
676             throw new InvalidDescriptorException("An entity descriptor of the "+
677                     "type "+related.getClass().getName()+" isn't supported!");
678     }
679     
680     /*** @returns a String that represents what to join ON e.g.:</br>
681      * "ON t1.foreignKey = t2.primaryKey" i.e. reference fields == target fields
682      */
683     private String getOnClauseSearchCondition(IEntityRelationPath iEntityRelationPath, int relationNbr, String firstAlias, String secondAlias, IEntityDescriptor  entityDescriptorForSecondAlias)
684     {
685         StringBuffer onClause = new StringBuffer(100);//buf as parameter and returned?
686         onClause.append(ON);
687         
688         java.util.Iterator iteratorRefColumns;
689         java.util.Iterator iteratorTargetColumns;
690         if (iEntityRelationPath.getRelationCount()>0)
691         {
692             IEntityRelation iEntityRelation = iEntityRelationPath.getRelation(relationNbr);
693             //System.out.println("\n\n"+iEntityRelation.getRelationQualifier()+"\n\n");//dbg
694             iteratorRefColumns = iEntityRelation.getReferenceFieldDescriptors();
695             iteratorTargetColumns = iEntityRelation.getTargetFieldDescriptors();
696         }
697         else
698         {//relation is a nullpath //nno-todo do we need this?
699             IFieldDescriptor[] idFieldsPresent = Relations.getIdentityFields(iEntityRelationPath.getFirstEntityDescriptor());
700             iteratorRefColumns    = org.caleigo.toolkit.util.Iterators.iterate(idFieldsPresent);
701             iteratorTargetColumns = org.caleigo.toolkit.util.Iterators.iterate(idFieldsPresent);
702         }
703         
704         java.util.Iterator iteratorFirstColumns;
705         java.util.Iterator iteratorSecondColumns;
706         if ( iEntityRelationPath.getRelation(relationNbr).getTargetEntityDescriptor() ==  entityDescriptorForSecondAlias)
707         {
708             iteratorFirstColumns = iteratorRefColumns;
709             iteratorSecondColumns = iteratorTargetColumns;
710         }
711         else
712         {
713             iteratorFirstColumns = iteratorTargetColumns;
714             iteratorSecondColumns = iteratorRefColumns;
715         }
716         
717         boolean moreThanOneColumn = false; 
718         //The length for these 2 should be same, otherwise something is fishy!
719         while (iteratorFirstColumns.hasNext() & iteratorSecondColumns.hasNext())
720         {
721             String firstColumnName  = ( (IFieldDescriptor)iteratorFirstColumns.next() ).getSourceName();
722             String secondColumnName = ( (IFieldDescriptor)iteratorSecondColumns.next() ).getSourceName();
723             onClause.append(this.formatIdentifier(firstAlias));
724             onClause.append(".");
725             onClause.append(this.formatIdentifier(firstColumnName));
726             onClause.append("=");
727             onClause.append(this.formatIdentifier(secondAlias));
728             onClause.append(".");
729             onClause.append(this.formatIdentifier(secondColumnName));
730             onClause.append(" ");
731             if (moreThanOneColumn)
732             {//this is fast enough since we oftently only have one column.
733                 onClause.append(',');
734                 onClause.append(' ');
735             }
736             else   
737                 moreThanOneColumn = true;
738         }
739         
740         return onClause.toString();
741     }
742     
743     
744     // END SELECT METHODS
745     
746     
747     
748     
749     
750     // OTHER METHODS THAT BUILDS SQL-COMMANDS ----------------------------------
751     
752     
753     /*** Builds the SQL UPDATE-command.
754      * 
755      * @param iEntity descriptor must be an instance of IBaseEntityDescriptor
756      * since code names i.e aliases can't be used in an SQL-UPDATE-statement.
757      * @param qualifier should uniqally qualify the entities to be updated.
758      * @return a String a'la:</br>
759      * "UPDATE Employees SET firstname=Niklas WHERE Employees.ID = 17"
760      */
761     public String buildUpdateCommand(IEntity entity, Qualifier qualifier)
762     {
763         //Validate descriptor and qualifier for update.
764         /*IBaseEntityDescriptor baseEntityDescriptor = null;
765         try
766         {
767             baseEntityDescriptor = (IBaseEntityDescriptor)entity.getEntityDescriptor();
768         }
769         catch (ClassCastException e)
770         {
771             throw new InvalidDescriptorException("The entity descriptor must" +
772                 "be an instance of IBaseEntityDescriptor.", e);
773         }*/
774         IEntityDescriptor baseEntityDescriptor = entity.getEntityDescriptor();
775         
776         if(entity.getEntityDescriptor() instanceof ICompositeEntityDescriptor )
777             throw new SQLToolKitException("Can't update composite entities.");
778         
779         if(qualifier==null || !qualifier.canUniquelyQualify(entity.getEntityDescriptor()))
780             throw new InvalidQualifierException("Update command requires an identity qualifier.");
781         
782         // Build the declaration part. 
783         StringBuffer command = new StringBuffer(1000);
784         command.append(UPDATE);
785         command.append(formatIdentifier(baseEntityDescriptor.getSourceName()));
786         command.append(' ');
787         
788         // Build the value part.
789         command.append(SET);
790         for(int j=0; j<baseEntityDescriptor.getDataFieldCount(); j++)
791         {
792             if(!baseEntityDescriptor.getFieldDescriptor(j).isAutoGenerated() &&
793                     entity.isFieldDirty(baseEntityDescriptor.getFieldDescriptor(j)))
794             {   
795                 command.append(formatIdentifier(baseEntityDescriptor.getFieldDescriptor(j).getSourceName()));
796                 command.append('=');
797                 command.append(this.buildDataString(baseEntityDescriptor.getFieldDescriptor(j).getDataType(), entity.getData(baseEntityDescriptor.getFieldDescriptor(j))));
798                 command.append(',');
799                 command.append(' ');
800             }
801         }
802         command.setLength(command.length()-2);//exclude last ','- & ' '-chars
803         command.append(' ');
804         
805         // Build the WHERE part. 
806         if(qualifier==null)
807             qualifier = entity.getOriginQualifier();
808         command.append(WHERE);
809         command.append(this.buildWhereExpression(qualifier, baseEntityDescriptor.getSourceName()));
810         
811         return command.toString();
812     }
813     
814     public String buildCreateTableCommand(IEntityDescriptor entityDescriptor) 
815     {
816         //Validate entity descriptor
817         /*try
818         {
819             IBaseEntityDescriptor iBaseEntityDescriptor = (IBaseEntityDescriptor)entityDescriptor;
820         }
821         catch (ClassCastException e)
822         {
823             throw new InvalidDescriptorException("The entity descriptor must" +
824                 "be an instance of IBaseEntityDescriptor.", e);
825         }    */        
826 
827         
828         StringBuffer createString = new StringBuffer(1000);
829         StringBuffer pkString = new StringBuffer(100);
830         createString.append(CREATE);
831         
832         createString.append(this.formatIdentifier(entityDescriptor.getSourceName()));
833 
834         createString.append(" (");
835         
836         for(int j=0; j<entityDescriptor.getDataFieldCount(); j++)
837         {
838             createString.append(this.formatIdentifier(entityDescriptor.getFieldDescriptor(j).getSourceName()));
839             createString.append(" ");
840             createString.append(mSchema.convertDataTypeToSQL(entityDescriptor.getFieldDescriptor(j)));
841             if (j < (entityDescriptor.getDataFieldCount() - 1))
842             {
843                 createString.append(", ");
844             }
845             else 
846             {
847                 createString.append(" ");
848             }
849             
850             // TODO Maybe not general. Move to Database implementation
851             if (entityDescriptor.getFieldDescriptor(j).isIdentityField() && !entityDescriptor.getFieldDescriptor(j).isAutoGenerated()) 
852             {
853                 if (pkString.length() == 0)
854                 {
855                     pkString.append(", PRIMARY KEY (" + this.formatIdentifier(entityDescriptor.getFieldDescriptor(j).getSourceName()));
856                 }
857                 else 
858                 {
859                     pkString.append(", ");
860                     pkString.append(this.formatIdentifier(entityDescriptor.getFieldDescriptor(j).getSourceName()));
861                 }
862             } 
863         }
864         
865         if (pkString.length() > 0)
866         {
867             createString.append(pkString);
868             createString.append(")");
869         }
870         
871         createString.append(")");
872         System.out.print(createString.toString());
873         return createString.toString();
874     }
875     
876     public String buildCreateRelationCommand(IEntityRelation relation)
877     {
878         StringBuffer createString = new StringBuffer(1000);
879         createString.append(ALTER_TABLE);
880         createString.append(this.formatIdentifier(relation.getReferenceEntityDescriptor().getSourceName()));
881         createString.append(" ADD CONSTRAINT ");
882 //        createString.append(relation.getReferenceEntityDescriptor().getSourceName() + 
883 //            "_" + 
884 //            relation.getFieldRelation(0).getReferenceField().getSourceName() + 
885 //            "_" + 
886 //            relation.getTargetEntityDescriptor().getSourceName());
887 //        createString.append("Relation_" + rnd.nextInt(999999999));
888         createString.append(this.formatIdentifier(relation.getCodeName()));
889         createString.append(" FOREIGN KEY ("); 
890         
891         StringBuffer referenceFields = new StringBuffer(100);
892         StringBuffer targetFields = new StringBuffer(100);
893         for (int i=0; i < relation.getFieldCount(); i++) 
894         {
895             IFieldRelation rel = relation.getFieldRelation(i);
896             if (referenceFields.length() > 0)
897             {
898                 referenceFields.append(", ");
899             }
900             referenceFields.append(this.formatIdentifier(rel.getReferenceField().getSourceName()));
901             if (targetFields.length() > 0)
902             {
903                 targetFields.append(", ");
904             }
905             targetFields.append(this.formatIdentifier(rel.getTargetField().getSourceName()));
906         }
907         
908         createString.append(referenceFields);
909         createString.append(") REFERENCES ");
910         createString.append(this.formatIdentifier(relation.getTargetEntityDescriptor().getSourceName()));
911         createString.append(" (");
912         createString.append(targetFields);
913         createString.append(")");
914         System.out.println(createString.toString());
915         return createString.toString();
916     }
917     
918     public String buildInsertCommand(IEntity entity)
919     {
920         // INSERT INTO employees (employeeid, lastname, firstname) VALUES (10, 'Smith', 'John');
921         //Validate entity descriptor
922         /*IBaseEntityDescriptor iBaseEntityDescriptor = null;
923         try
924         {
925             iBaseEntityDescriptor = (IBaseEntityDescriptor)entity.getEntityDescriptor();
926         }
927         catch (ClassCastException e)
928         {
929             throw new InvalidDescriptorException("The entity descriptor must" +
930                 "be an instance of IBaseEntityDescriptor.", e);
931         }*/
932         
933         // Initialize declaration and value parts of the command. 
934         StringBuffer declarationPart = new StringBuffer(1000);
935         StringBuffer valuePart = new StringBuffer(1000);
936         int addedCount = 0;
937 
938         declarationPart.append(INSERT);
939         declarationPart.append(this.formatIdentifier(entity.getEntityDescriptor().getSourceName()));
940         declarationPart.append(" (");
941         
942         valuePart.append(" VALUES (");
943                 
944         for(int j=0; j<entity.getEntityDescriptor().getDataFieldCount(); j++)
945         {
946             // Do not include autogenerated fields.
947             if(!entity.getEntityDescriptor().getFieldDescriptor(j).isAutoGenerated() || entity.getData(entity.getEntityDescriptor().getFieldDescriptor(j)) != null)
948             //if(!entity.getEntityDescriptor().getFieldDescriptor(j).isAutoGenerated() || entity.getData(entity.getEntityDescriptor().getFieldDescriptor(j)) != null)
949             //    isIdentityField
950             {
951                 Object data = entity.getData(entity.getEntityDescriptor().getFieldDescriptor(j));
952                 
953                 // Do not include null data.
954                 if(data!=null)
955                 {
956                     if(addedCount>0)
957                     {
958                         declarationPart.append(", ");
959                         valuePart.append(", ");
960                     }
961                     addedCount++;
962 
963                     /*declarationPart.append("\"" + entity.getEntityDescriptor().getFieldDescriptor(j).getEntityDescriptor().getSourceName() + "\"");
964                     declarationPart.append('.');*/
965                     declarationPart.append(this.formatIdentifier(entity.getEntityDescriptor().getFieldDescriptor(j).getSourceName()));
966 
967                     valuePart.append(this.buildDataString(entity.getEntityDescriptor().getFieldDescriptor(j).getDataType(), data));
968                 }
969             }
970         }
971         declarationPart.append(')');
972         valuePart.append(')');
973         
974         // Handle the case when no column data is specified
975         if (declarationPart.toString().endsWith("()") && valuePart.toString().endsWith("()"))
976         {
977             declarationPart.delete(declarationPart.indexOf("()"), declarationPart.length());
978             valuePart.replace(0, valuePart.length(), "DEFAULT VALUES");
979         }
980         
981         // Join and return command parts.
982         declarationPart.append(valuePart);
983         return declarationPart.toString();
984         //return declarationPart.toString().toLowerCase();
985     }
986     
987     public String buildDeleteCommand(IEntityDescriptor entityDescriptor, Qualifier qualifier)
988     {
989         // Validate descriptor and qualifier for delete.
990         IBaseEntityDescriptor baseEntityDescriptor = null;
991         try
992         {
993             baseEntityDescriptor = (IBaseEntityDescriptor)entityDescriptor;
994         }
995         catch (ClassCastException e)
996         {
997             throw new InvalidDescriptorException("The iEntity descriptor must" +
998               " be an instance of IBaseEntityDescriptor.", e);
999         }
1000         if(qualifier==null || !qualifier.canUniquelyQualify(entityDescriptor))
1001             throw new InvalidQualifierException("Delete command requires an identity qualifier.");
1002         
1003 
1004         // Build the declaration part.
1005         StringBuffer command = new StringBuffer(1000);
1006         command.append(DELETE);
1007         command.append(formatIdentifier(entityDescriptor.getSourceName()));
1008         command.append(' ');
1009         
1010         // Build the WHERE part. 
1011         command.append(WHERE);
1012         command.append(this.buildWhereExpression(qualifier, entityDescriptor.getSourceName()));
1013 
1014         return command.toString();
1015     }
1016     
1017     /*** Builds the SQL WHERE-part used in a SQL-command.
1018      * 
1019      * The type of the qualifer's field descriptors determines if
1020      * alias (for composites) are to be used in the WHERE-part. 
1021      * @param qualifier should uniqally qualify entities (one or several)
1022      * @param alias if this parameter isn't null this alias will be used in
1023      * the where clause.
1024      * @return a String a'la:</br>
1025      * "WHERE Employee.ID = 17"
1026      */
1027     public String buildWhereExpression(Qualifier qualifier, String alias)
1028     {
1029         if(qualifier==null)
1030             return null;
1031         else if(mQualifierParserMap.containsKey(qualifier.getClass())) 
1032             return ((IQualifierParser)mQualifierParserMap.get(qualifier.getClass())).process(qualifier, this, alias);
1033         else
1034             throw new SQLToolKitException("Failed to process unknown Qualifier class: "+qualifier.getClass().getName());
1035     }   
1036     
1037     public String buildDataString(DataType type, Object data)
1038     {
1039         if(mDataTypeConverterMap.containsKey(type))
1040             return (String)((IDataTypeConverter)mDataTypeConverterMap.get(type)).convertToDB(data);
1041         else
1042             return type.convertToString(data);
1043     }    
1044     
1045     public String prepareWildcards(String dataString)
1046     {
1047         StringBuffer buf = new StringBuffer(dataString);
1048         for(int j=0; j<dataString.length(); j++)
1049         {
1050             if(buf.charAt(j)=='*')
1051                 buf.setCharAt(j, '%');
1052             else if(buf.charAt(j)=='?')
1053                 buf.setCharAt(j, '_');
1054         }
1055         return buf.toString();
1056     }
1057         
1058     /*** Returns a Set containing the entity descriptors meaded for the WHERE
1059      * statement built by the submited qualifier. Can be used to build the
1060      * FROM statement of the SQL command. May return null
1061      */
1062     protected Set getQualifierDescriptorSet(Qualifier qualifier)
1063     {
1064         if(qualifier==null)
1065             return null;
1066         else if(mQualifierParserMap.containsKey(qualifier.getClass())) 
1067             return ((IQualifierParser)mQualifierParserMap.get(qualifier.getClass())).stateDescriptorRequirement(qualifier, this);
1068         else
1069             throw new SQLToolKitException("Failed to process unknown Qualifier class: "+qualifier.getClass().getName());        
1070     }
1071     
1072     public String formatIdentifier(String indentifier)
1073     {
1074         if(mIsUsingUppercaseIdentifiers)
1075             indentifier = indentifier.toUpperCase();
1076         //nno-code tmp-postgres fix:
1077         //indentifier = indentifier.toLowerCase();
1078         if(mIsQuotingIdentifiers || indentifier.indexOf(' ')>=0)
1079             return mQuotingString+indentifier+mQuotingString;
1080         else
1081             return indentifier;
1082     }
1083  
1084     public void setDatabaseSchema(IDatabaseSchema schema)
1085     {
1086         this.mSchema = schema;   
1087     }
1088     
1089     /*** Verify that the qualifer in the external qualifier only targets the
1090      * first entity descriptor in the external qualifier's entity relation path.
1091      * If the qualifer targets ICompositeFieldDescriptors they are only allowed
1092      * to belong to the root field group in the wrapping 
1093      * ICompositeEntityDescriptor.
1094      * 
1095      * @param xternalQualifier to be verified.
1096      * @throws an InvalidQualifierException if the qualifer target's several
1097      * entity descriptor's or another one than the first one.
1098      */
1099     public void  validateXternalQualifier(DataQuery.ExternalQualifier xternalQualifier, IEntityDescriptor relatedEntityDescriptor)
1100     {
1101         IEntityRelationPath path2Qualifer =  xternalQualifier.getEntityRelationPath();
1102         IEntityDescriptor firstEntityDescriptor = path2Qualifer.getFirstEntityDescriptor();
1103         
1104         if( path2Qualifer.getLastEntityDescriptor() != relatedEntityDescriptor )
1105             throw new InvalidRelationException("The last entity descriptor (" +
1106                     path2Qualifer.getLastEntityDescriptor() +
1107                     ") in the external qualifiers entity relation path" +
1108                     " should be same as the \"related\" (" +
1109                     relatedEntityDescriptor +
1110                     ") for the SQL command!");
1111         
1112         //Walk path and check that all but first is base entity decriptors
1113         IEntityDescriptor nextEntityDescriptor = firstEntityDescriptor;
1114         for (int i = 0; i < path2Qualifer.getRelationCount(); i++)
1115         {
1116             try
1117             {
1118                 //IBaseEntityDescriptor baseEntityDescriptor = (IBaseEntityDescriptor)path2Qualifer.getRelation(i).getRelatedEntityDescriptor(nextEntityDescriptor);
1119                 IEntityDescriptor entityDescriptor = path2Qualifer.getRelation(i).getRelatedEntityDescriptor(nextEntityDescriptor);
1120                 nextEntityDescriptor = entityDescriptor;
1121             }
1122             catch (ClassCastException e)
1123             {
1124                 throw new InvalidDescriptorException("All entity descriptors " +
1125                         "(except the first one) in an external qualifers path "+
1126                         "must be an instance of IBaseEntityDescriptor.", e);
1127             } 
1128         }
1129         
1130         //Check that the qualifier only qualifies the first entity descriptor 
1131         Qualifier qualifier = xternalQualifier.getQualifer();
1132         //Scan the qualifier for entity descriptor(s).
1133         Set descriptorSet = this.getQualifierDescriptorSet(qualifier);
1134         
1135         //Check targeteting
1136         Iterator it = descriptorSet.iterator();
1137         IEntityDescriptor entityDescriptorInSet;
1138         IEntityDescriptor baseEntityDescriptor2Qualify = null;
1139         int descriptorCounter = 0;
1140         boolean errors = false;
1141         String toManyTargets = null;
1142         String entityDescripors = null;
1143         List errorList = new LinkedList();
1144         while(it.hasNext())
1145         {
1146             ++descriptorCounter;
1147             entityDescriptorInSet = (IEntityDescriptor)it.next();
1148             
1149             if( firstEntityDescriptor instanceof ICompositeEntityDescriptor &&
1150                     baseEntityDescriptor2Qualify == null )
1151             {
1152                 //just set baseEntityDescriptor2Qualify
1153                 baseEntityDescriptor2Qualify = entityDescriptorInSet;
1154                 continue;
1155             }
1156             
1157             if( firstEntityDescriptor instanceof ICompositeEntityDescriptor && 
1158                     entityDescriptorInSet != baseEntityDescriptor2Qualify )
1159                 
1160             {
1161                 errors = true;
1162                 String error = "When the entity relation path in an external " +
1163                         "qualifier starts with a composite entity descriptor " +
1164                         "the qualifier's (in the external qualifier) all " +
1165                         "composite field descriptors must belong to the " +
1166                         "same field group in the composite entity descriptor!\n"+
1167                         "However, the qualifer: "+qualifier+" have a composite "+
1168                         "field descriptor that belongs to a field group which " +
1169                         "base entity descriptor is: " + entityDescriptorInSet +
1170                         ". The first composite field descriptor's field group's "+
1171                         "base entity descriptor is:"+baseEntityDescriptor2Qualify+"!\n";
1172                 errorList.add(error);
1173             }
1174             else if( (firstEntityDescriptor instanceof IBaseEntityDescriptor ||
1175                     firstEntityDescriptor instanceof IEntityDescriptor)  &&
1176                     entityDescriptorInSet != firstEntityDescriptor )
1177             {
1178                 errors = true;
1179                 String error = "When the entity relation path in an external " +
1180                         "qualifier starts with a base entity descriptor the " +
1181                         "qualifier's (in the external qualifier) all field " +
1182                         "descriptors must belong to this base entity descriptor!\n" +
1183                         "However, the qualifer: "+qualifier+" have a field " +
1184                         "descriptor belonging to the entity descriptor: " +
1185                         entityDescriptorInSet + "\n";
1186                 errorList.add(error);
1187             }
1188             else if ( !(firstEntityDescriptor instanceof ICompositeEntityDescriptor) &&
1189                       !(firstEntityDescriptor instanceof IBaseEntityDescriptor) &&
1190                       !(firstEntityDescriptor instanceof IEntityDescriptor) )
1191             {
1192                 errors = true;
1193                 String error = "Entity relation path in an external " +
1194                         "qualifier that starts with a entity descriptor of " +
1195                         "the type: " + entityDescriptorInSet.getClass().toString() +
1196                         " isn't supported!\n";
1197                 errorList.add(error);
1198                 break;
1199             }
1200             
1201             if(descriptorCounter > 1)
1202             {
1203                 errors = true;
1204                 entityDescripors += entityDescriptorInSet.toString() + ", ";
1205                 toManyTargets = "A qualifier in an external qualifier are " +
1206                         "only allowed to target one and only one entity " +
1207                         "descriptor namely the first one in the path which " +
1208                         "for this external qualifier's entity relation path " +
1209                         "is: " + firstEntityDescriptor + "!\n" +
1210                         "The qualifer in this external qualifier targets: " +
1211                         descriptorCounter + " entity descriptors: ";
1212             }
1213         }
1214         
1215         if (errors)
1216         {
1217             if (toManyTargets != null)
1218             {
1219                 entityDescripors = entityDescripors.substring(0, entityDescripors.length()-3 );//exculde last ", "
1220                 toManyTargets = toManyTargets + entityDescripors + "\n";
1221                 errorList.add(0, toManyTargets);
1222             }
1223             String exceptionMSG = "";
1224             it = errorList.iterator();
1225             while(it.hasNext())
1226                 exceptionMSG += (String)it.next();
1227             throw new InvalidQualifierException(exceptionMSG);
1228         }
1229     }
1230     
1231     
1232  
1233     
1234     // Nested classes ----------------------------------------------------------
1235     
1236     
1237     
1238     public static interface IDataTypeConverter
1239     {
1240         public Object convertToDB(Object data);
1241         public Object convertFromDB(Object data);
1242     }    
1243     
1244     public static class StringTypeConverter implements IDataTypeConverter
1245     {
1246         // Data members --------------------------------------------------------
1247         private boolean mDoTrim;
1248         
1249         // Constructors --------------------------------------------------------
1250         public StringTypeConverter()
1251         {
1252             this(true);
1253         }
1254         
1255         public StringTypeConverter(boolean trim)
1256         {
1257             mDoTrim = trim;
1258         }
1259         
1260         // IDataTypeConverter implementation -----------------------------------
1261         public Object convertToDB(Object data)
1262         {
1263             String text = (String)data;
1264             StringBuffer buf = new StringBuffer(text.length()+20);
1265             buf.append('\'');
1266             
1267             int prevIndex = 0;
1268             int index = text.indexOf('\'');
1269             while(index>=0)
1270             {
1271                 buf.append(text.substring(prevIndex, index));
1272                 buf.append('\'');
1273                 prevIndex = index;
1274                 index = text.indexOf('\'', index+1);
1275             }
1276             buf.append(text.substring(prevIndex));
1277 
1278             buf.append('\'');
1279             return buf.toString();
1280         }
1281         
1282         public Object convertFromDB(Object data)
1283         {
1284             if(data==null)
1285                 return null;
1286             else if(!(data instanceof String))
1287                 data = data.toString();
1288             
1289             if(mDoTrim)
1290                 data = ((String)data).trim();         
1291             
1292             return data;
1293         }
1294     }
1295     
1296     public static class BooleanTypeConverter implements IDataTypeConverter
1297     {
1298         public Object convertToDB(Object data)
1299         {
1300             if(!(data instanceof Boolean))
1301                 data = DataType.BOOLEAN.convertFrom(data);
1302             if(data==null)
1303                 return null;
1304             else
1305                 return ((Boolean)data).booleanValue() ? "1" : "0";
1306         }
1307         
1308         public Object convertFromDB(Object data)
1309         {
1310             if(!(data instanceof Boolean))
1311                 data = DataType.BOOLEAN.convertFrom(data);
1312             return data;
1313         }
1314     }
1315     
1316     public static class BooleanCharTypeConverter implements IDataTypeConverter
1317     {
1318         private static final Character DEFAULT_TRUE_CHARACTER   = new Character('t');
1319         private static final Character DEFAULT_FALSE_CHARACTER  = new Character('f');
1320         private static final String DEFAULT_ACCEPTED_TRUE_CHARS = "tT1yY";
1321         
1322         private Character mTrueCharacter;
1323         private Character mFalseCharacter;
1324         private String mAcceptedTrueChars;
1325         
1326         public BooleanCharTypeConverter()
1327         {
1328             mTrueCharacter  = DEFAULT_TRUE_CHARACTER;
1329             mFalseCharacter = DEFAULT_FALSE_CHARACTER;
1330             mAcceptedTrueChars = DEFAULT_ACCEPTED_TRUE_CHARS;
1331         }
1332         
1333         public BooleanCharTypeConverter(char trueChar, char falseChar)
1334         {
1335             this(trueChar, falseChar, DEFAULT_ACCEPTED_TRUE_CHARS);
1336         }
1337             
1338         public BooleanCharTypeConverter(char trueChar, char falseChar,
1339                                             String acceptedTrueChars)
1340         {
1341             mTrueCharacter = new Character(trueChar);
1342             mFalseCharacter = new Character(falseChar);
1343             
1344             if ( acceptedTrueChars != null )
1345                 mAcceptedTrueChars = acceptedTrueChars;
1346             else
1347                 mAcceptedTrueChars = "" + mTrueCharacter;
1348 
1349             if ( acceptedTrueChars.indexOf(mTrueCharacter.charValue()) < 0 )
1350                 acceptedTrueChars = mTrueCharacter + acceptedTrueChars;
1351         }
1352 
1353         public Object convertToDB(Object data)
1354         {
1355             if(!(data instanceof Boolean))
1356                 data = DataType.BOOLEAN.convertFrom(data);
1357             if(data==null)
1358                 return null;
1359             else
1360                 return ((Boolean)data).booleanValue() ? "\'" + mTrueCharacter + "\'": "\'" + mFalseCharacter + "\'";
1361         }
1362         
1363         public Object convertFromDB(Object data)
1364         {
1365             if (data instanceof String && ((String) data).length()==1)
1366                 data = new Character(((String) data).charAt(0));
1367             if (data instanceof Character)
1368             {
1369                 Character characterData = (Character) data;
1370                 if (characterData == mFalseCharacter)
1371                 {
1372                     data = new Boolean(false);
1373                 }
1374                 else
1375                 {
1376                     data = new Boolean(mAcceptedTrueChars.indexOf((characterData).charValue()) >= 0);
1377                 }
1378             }
1379             else if(!(data instanceof Boolean))
1380             {
1381                 data = DataType.BOOLEAN.convertFrom(data);
1382             }
1383 
1384             return data;
1385         }
1386     }
1387     
1388     public static class DateTypeConverter implements IDataTypeConverter
1389     {
1390 //        private DateFormat mDateFormat = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''");
1391         private DateFormat mDateFormat = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''");
1392         public Object convertToDB(Object data)
1393         {
1394             if(!(data instanceof Date))
1395                 data = DataType.DATE.convertFrom(data);
1396             if(data==null)
1397                 return null;
1398             else
1399 //                return "TO_DATE(" + mDateFormat.format((Date)data) + ", 'YYYY-MM-DD HH24:MI:SS')";
1400             return mDateFormat.format((Date)data);  
1401         }
1402         
1403         public Object convertFromDB(Object data)
1404         {
1405             if(data instanceof Timestamp)
1406                 data = new Date(((Timestamp)data).getTime());
1407             if(!(data instanceof Date))
1408                 data = DataType.DATE.convertFrom(data);
1409             return data;
1410         }
1411     }
1412     
1413     
1414     public static interface IQualifierParser
1415     {
1416         /*** Should produce a valid logic "term" to iclude in an sql where-
1417          * statement.
1418          */ 
1419         public String process(Qualifier qualifier, SQLToolKit sql, String alias);
1420         
1421         /*** Should produce a set of entity descriptors that are adressed by
1422          * the qualifier.
1423          */
1424         public Set stateDescriptorRequirement(Qualifier qualifier, SQLToolKit sql);
1425     }   
1426     
1427     
1428     public class RelationQualifierParser implements IQualifierParser
1429     {
1430         public String process(Qualifier qualifier, SQLToolKit sql, String alias)
1431         {
1432             if(qualifier==null)
1433                 return null;
1434             
1435             StringBuffer buf = new StringBuffer(100);
1436             RelationQualifier qual = (RelationQualifier)qualifier;
1437             
1438             // Process the field descriptor.
1439             IFieldDescriptor field = qual.getFieldDescriptor();
1440             
1441             String quotedAlias = null;
1442             if(alias!=null)
1443                 quotedAlias = SQLToolKit.this.formatIdentifier(alias);
1444             else
1445                 if (field instanceof ICompositeEntityDescriptor.ICompositeFieldDescriptor)
1446                     quotedAlias = SQLToolKit.this.formatIdentifier(((ICompositeEntityDescriptor.ICompositeFieldDescriptor)field).getFieldGroup().getEntityIdentity());
1447                 else
1448                     quotedAlias = SQLToolKit.this.formatIdentifier(field.getEntityDescriptor().getCodeName());
1449             
1450             buf.append(quotedAlias);
1451             buf.append('.');
1452             buf.append(SQLToolKit.this.formatIdentifier(field.getSourceName()));
1453             
1454             
1455             // Process the relation operator.
1456             if(qual.getRelationType()==RelationType.EQUAL)
1457             {
1458                 if(qual.getRelationValue()==null)
1459                     buf.append(" IS NULL");
1460                 else
1461                     buf.append('=');
1462             }
1463             else if(qual.getRelationType()==RelationType.NOT_EQUAL)
1464             {
1465                 if(qual.getRelationValue()==null)
1466                     buf.append(" IS NOT NULL");
1467                 else
1468                     buf.append("<>");
1469             }
1470             else if(qual.getRelationType()==RelationType.LESS)
1471                 buf.append('<');
1472             else if(qual.getRelationType()==RelationType.LESS_EQUAL)
1473                 buf.append("<=");
1474             else if(qual.getRelationType()==RelationType.LARGER)
1475                 buf.append('>');
1476             else if(qual.getRelationType()==RelationType.LARGER_EQUAL)
1477                 buf.append(">=");
1478             else if(qual.getRelationType()==RelationType.SIMULAR)
1479                 buf.append(" LIKE ");
1480             else
1481                 buf.append(qual.getRelationType().toString());
1482             
1483             // Process the relations data value.
1484             if(qual.getRelationValue() instanceof IFieldDescriptor)
1485             {
1486                 if(alias == null)
1487                     throw new SQLToolKitException("Setting alias explicitly as: " +
1488                             "\""+alias+"\" aren't allowed when relating to a field descriptor.");
1489                 field = (IFieldDescriptor)qual.getRelationValue();
1490                 if (field instanceof ICompositeEntityDescriptor.ICompositeFieldDescriptor)
1491                     quotedAlias = SQLToolKit.this.formatIdentifier(((ICompositeEntityDescriptor.ICompositeFieldDescriptor)field).getFieldGroup().getEntityIdentity());
1492                 else
1493                     quotedAlias = SQLToolKit.this.formatIdentifier(field.getEntityDescriptor().getCodeName());
1494                 
1495                 buf.append(quotedAlias);
1496                 buf.append('.');
1497                 buf.append(SQLToolKit.this.formatIdentifier(field.getSourceName()));
1498 
1499             }
1500             else if(qual.getRelationValue()!=null)
1501             {
1502                 if(qual.getRelationType()==RelationType.SIMULAR)
1503                     buf.append(sql.prepareWildcards(sql.buildDataString(qual.getFieldDescriptor().getDataType(), qual.getRelationValue())));
1504                 else
1505                     buf.append(sql.buildDataString(qual.getFieldDescriptor().getDataType(), qual.getRelationValue()));
1506             }
1507             else if(!(qual.getRelationType()==RelationType.EQUAL || qual.getRelationType()==RelationType.NOT_EQUAL))
1508                 throw new SQLToolKitException("Relation using \""+qual.getRelationType()+"\" can not relate to NULL.");
1509             
1510             return buf.toString();
1511               
1512         }
1513         
1514         public Set stateDescriptorRequirement(Qualifier qualifier, SQLToolKit sql)
1515         {            
1516             Set descriptorSet = new HashSet();
1517             
1518             IFieldDescriptor iFieldDescriptor = ((RelationQualifier)qualifier).getFieldDescriptor();
1519             if (iFieldDescriptor instanceof ICompositeEntityDescriptor.ICompositeFieldDescriptor)
1520                 iFieldDescriptor = ((ICompositeEntityDescriptor.ICompositeFieldDescriptor)iFieldDescriptor).getSourceFieldDescriptor();
1521             descriptorSet.add(iFieldDescriptor.getEntityDescriptor());
1522             
1523             if(((RelationQualifier)qualifier).getRelationValue() instanceof IFieldDescriptor)
1524                 if (iFieldDescriptor instanceof ICompositeEntityDescriptor.ICompositeFieldDescriptor)
1525                     throw new InvalidQualifierException("A relation qualifier " +
1526                             "with an ICompositeFieldDescriptor-object as " +
1527                             "relation value isn't supported!");
1528                 else
1529                     descriptorSet.add(((IFieldDescriptor)((RelationQualifier)qualifier).getRelationValue()).getEntityDescriptor());
1530             
1531             return descriptorSet;  
1532         }
1533     }       
1534     
1535     public static class CompositeQualifierParser implements IQualifierParser
1536     {
1537         public String process(Qualifier qualifier, SQLToolKit sql, String alias)
1538         {
1539             if(qualifier==null || ((CompositeQualifier)qualifier).getQualifierCount()<=0)
1540                 return null;
1541             
1542             StringBuffer buf = new StringBuffer(500);
1543             buf.append('(');
1544             buf.append( sql.buildWhereExpression(((CompositeQualifier)qualifier).getQualifier(0), alias) );
1545             for(int j=1; j<((CompositeQualifier)qualifier).getQualifierCount(); j++)
1546             {
1547                 if(((CompositeQualifier)qualifier).getUnionType()==CompositeQualifier.INTERSECTION)
1548                     buf.append(" AND ");
1549                 else
1550                     buf.append(" OR ");
1551                 buf.append( sql.buildWhereExpression(((CompositeQualifier)qualifier).getQualifier(j), alias) );
1552             }
1553             buf.append(')');
1554             
1555             return buf.toString();
1556         }
1557         
1558         public Set stateDescriptorRequirement(Qualifier qualifier, SQLToolKit sql)
1559         {
1560             Set descriptorSet = new HashSet();
1561             for(int j=0; j<((CompositeQualifier)qualifier).getQualifierCount(); j++)
1562                 descriptorSet.addAll(sql.getQualifierDescriptorSet(((CompositeQualifier)qualifier).getQualifier(j)));
1563             return descriptorSet;
1564         }
1565     }    
1566     
1567     
1568     public static class NegateQualifierParser
1569     {
1570         public String process(Qualifier qualifier, SQLToolKit sql, String alias)
1571         {
1572             if(((NegateQualifier)qualifier).getNegatedQualifier()==null)
1573                 return null;
1574             else
1575                 return "NOT "+sql.buildWhereExpression(((NegateQualifier)qualifier).getNegatedQualifier(), alias);
1576         }
1577         
1578         public Set stateDescriptorRequirement(Qualifier qualifier, SQLToolKit sql)
1579         {
1580             if(((NegateQualifier)qualifier).getNegatedQualifier()!=null)
1581                 return sql.getQualifierDescriptorSet(((NegateQualifier)qualifier).getNegatedQualifier());
1582             else
1583                 return null;
1584         }
1585     } 
1586     
1587     public static interface IDatabaseSchema
1588     {
1589         public String convertDataTypeToSQL(IFieldDescriptor fieldDescriptor);
1590     }
1591     
1592     public static class HsqldbDatabaseSchema implements IDatabaseSchema
1593     {
1594         public String convertDataTypeToSQL(IFieldDescriptor fieldDescriptor) 
1595         {
1596             StringBuffer returnString = new StringBuffer(100);
1597             StringBuffer pkString = new StringBuffer(100);
1598             if (fieldDescriptor.getDataType().equals(DataType.STRING))
1599             {
1600                 returnString.append("VARCHAR(");
1601                 returnString.append(fieldDescriptor.getLength());
1602                 returnString.append(")");
1603             } else if (fieldDescriptor.getDataType().equals(DataType.BIG_DECIMAL))
1604             {
1605                 returnString.append("DECIMAL");
1606             } else if (fieldDescriptor.getDataType().equals(DataType.BOOLEAN))
1607             {
1608                 returnString.append("INTEGER");
1609             } else if (fieldDescriptor.getDataType().equals(DataType.BYTE))
1610             {
1611                 returnString.append("BINARY");
1612             } else if (fieldDescriptor.getDataType().equals(DataType.DATE))
1613             {
1614                 returnString.append("DATE");
1615             } else if (fieldDescriptor.getDataType().equals(DataType.DOUBLE))
1616             {
1617                 returnString.append("DOUBLE");            
1618             } else if (fieldDescriptor.getDataType().equals(DataType.FLOAT))
1619             {
1620                 returnString.append("DOUBLE");
1621             
1622             } else if (fieldDescriptor.getDataType().equals(DataType.IMAGE))
1623             {
1624                 returnString.append("BINARY");
1625             
1626             } else if (fieldDescriptor.getDataType().equals(DataType.INTEGER))
1627             {
1628                 returnString.append("INTEGER");
1629             
1630             } else if (fieldDescriptor.getDataType().equals(DataType.LONG))
1631             {
1632                 returnString.append("BIGINT");
1633             } else if (fieldDescriptor.getDataType().equals(DataType.SHORT))
1634             {
1635                 returnString.append("SMALLINT");
1636             } else if (fieldDescriptor.getDataType().equals(DataType.LONG))
1637             {
1638                 returnString.append("BIGINT");
1639             } else if (fieldDescriptor.getDataType().equals(DataType.UNKNOWN))
1640             {
1641                 returnString.append("OBJECT");
1642             }
1643                 
1644             returnString.append(" ");
1645             
1646             if (fieldDescriptor.isAutoGenerated())
1647             {
1648                 returnString.append("IDENTITY ");
1649             } else if (fieldDescriptor.isRequired())
1650             {
1651                 returnString.append("NOT NULL ");
1652             }
1653             
1654             
1655             
1656             return returnString.toString();
1657         }
1658     }
1659     
1660     public static class OracleDatabaseSchema implements IDatabaseSchema
1661     {
1662         public String convertDataTypeToSQL(IFieldDescriptor fieldDescriptor) 
1663         {
1664             StringBuffer returnString = new StringBuffer(100);
1665             StringBuffer pkString = new StringBuffer(100);
1666             if (fieldDescriptor.getDataType().equals(DataType.STRING))
1667             {
1668                 returnString.append("VARCHAR(");
1669                 returnString.append(fieldDescriptor.getLength());
1670                 returnString.append(")");
1671             } else if (fieldDescriptor.getDataType().equals(DataType.BIG_DECIMAL))
1672             {
1673                 returnString.append("DECIMAL");
1674             } else if (fieldDescriptor.getDataType().equals(DataType.BOOLEAN))
1675             {
1676                 returnString.append("INTEGER");
1677             } else if (fieldDescriptor.getDataType().equals(DataType.BYTE))
1678             {
1679                 returnString.append("BINARY");
1680             } else if (fieldDescriptor.getDataType().equals(DataType.DATE))
1681             {
1682                 returnString.append("DATE");
1683             } else if (fieldDescriptor.getDataType().equals(DataType.DOUBLE))
1684             {
1685                 returnString.append("DOUBLE");            
1686             } else if (fieldDescriptor.getDataType().equals(DataType.FLOAT))
1687             {
1688                 returnString.append("DOUBLE");
1689         
1690             } else if (fieldDescriptor.getDataType().equals(DataType.IMAGE))
1691             {
1692                 returnString.append("BINARY");
1693         
1694             } else if (fieldDescriptor.getDataType().equals(DataType.INTEGER))
1695             {
1696                 returnString.append("INTEGER");
1697         
1698             } else if (fieldDescriptor.getDataType().equals(DataType.LONG))
1699             {
1700                 returnString.append("BIGINT");
1701             } else if (fieldDescriptor.getDataType().equals(DataType.SHORT))
1702             {
1703                 returnString.append("SMALLINT");
1704             } else if (fieldDescriptor.getDataType().equals(DataType.LONG))
1705             {
1706                 returnString.append("BIGINT");
1707             } else if (fieldDescriptor.getDataType().equals(DataType.UNKNOWN))
1708             {
1709                 returnString.append("OBJECT");
1710             }
1711             
1712             returnString.append(" ");
1713         
1714             /*if (fieldDescriptor.isAutoGenerated())
1715             {
1716                 returnString.append("IDENTITY ");
1717             } else if (fieldDescriptor.isRequired())
1718             {
1719                 returnString.append("NOT NULL ");
1720             }*/
1721         
1722         
1723         
1724             return returnString.toString();
1725         }
1726     }
1727         
1728 }