package hirondelle.web4j.database;

import hirondelle.web4j.util.Util;
import static hirondelle.web4j.util.Consts.NEW_LINE;

/**
  Dynamic SQL statement created in code. The SQL statement can be either: 
  <ul>
   <li>a complete statement 
   <li>a fragment of a statement, to be appended to the end of a static base statement
  </ul>
  
  <P>This class is intended for two use cases: 
  <ul>
   <li>creating a statement entirely in code
   <li>creating <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses dynamically, by building sort and filter criteria from 
   user input
  </ul>
  
  <P><b>The creation of SQL in code is dangerous. 
  You have to exercise care that your code will not be subject to 
  <a href='http://en.wikipedia.org/wiki/SQL_injection'>SQL Injection attacks</a>. 
  If you don't know what such attacks are all about, then you are in danger of creating 
  very large, dangerous security flaws in your application.</b>
  
  <P>The main means of protecting yourself from these attacks is to ensure that the sql strings you pass to 
  this class never contain data that has come directly from the user, in an unescaped form.
  You achieve this by <tt>parameterizing</tt> user input, and proceeding in 2 steps:
  <ol>
   <li>create an SQL statement that always uses a <tt>?</tt> placeholder for data entered by the user 
   <li>pass all user-entered data as parameters to the above statement
  </ol>
  
  The above corresponds to the correct use of a <tt>PreparedStatement</tt>.
  After you have built your dynamic SQL, you will usually pass it to   
  {@link hirondelle.web4j.database.Db#search(Class, SqlId, DynamicSql, Object[])} to retrieve the data. 
  
  <h3>Entries in .sql Files</h3>
  <P>The SQL string you pass to this class is always <em>appended</em> (using {@link #toString}) to 
  a (possibly-empty) base SQL statement already defined (as usual), in your <tt>.sql</tt> file. 
  That entry can take several forms. The criteria on the entry are:
  <ul>
   <li>it can be precompiled by WEB4J upon startup, if desired.
   <li>it contains only <i>static</i> elements of the final SQL statement
  </ul> 

  <em>It's important to note that the static base SQL can be completely empty.</em>
  For example, the entry in your <tt>.sql file</tt> can look something like this: 
  <pre>MY_DYNAMIC_REPORT {
    -- this sql is generated in code
}</pre>
  As you can see, there's only a comment here; there's no real SQL.
  In this case, you will need to build the entire SQL statement in code. 
  (Even though the above entry is empty, it's still necessary, since it's where you specify any 
  non-default target database name. It also ensures that the same mechanism web4j applies 
  to processing <tt>SqlId</tt> objects will remain in effect, which is useful.)

  <P>You are encouraged to implement joins between tables using the <tt>JOIN</tt> syntax. 
  The alternative is to implement joins using expressions in the <tt>WHERE</tt> clause.
  This usually isn't desirable, since it mixes up two distinct items - joins and actual criteria. 
  Using <tt>JOIN</tt> allows these items to remain separate and distinct.  

  <h3>See Also</h3>
  Other items closely related to this class are :  
   <ul>
   <li> {@link hirondelle.web4j.action.ActionImpl#getOrderBy(hirondelle.web4j.request.RequestParameter,hirondelle.web4j.request.RequestParameter, String)} - 
   convenience method for constructing an <tt>ORDER BY</tt> clause from request parameters.
   <li> {@link hirondelle.web4j.database.Db#search(Class, SqlId, DynamicSql, Object[])} 
   <li> the {@link hirondelle.web4j.database.Report} class. 
   </ul>
   
  <h3>Constants</h3>
  The {@link #WHERE}, {@link #AND}, and other constants are included in this class as a simple 
  convenience. Note that each value includes a leading a trailing space, to avoid trivial spacing errors.
  
  <P>This class is non-final, and can be overridden, if desired. The reason is that some applications may wish to try to 
  validate that the SQL passed to this class has been properly parameterized. 
*/
public class DynamicSql {;
  
  /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause.*/
  public static final String WHERE = " WHERE ";
  
  /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause. */
  public static final String AND = " AND ";
  
  /** Value - {@value}, convenience value for building a <tt>WHERE</tt> clause. */
  public static final String OR = " OR ";
  
  /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
  public static final String ORDER_BY = " ORDER BY ";
  
  /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
  public static final String ASC = " ASC ";
  
  /** Value - {@value}, convenience value for building an <tt>ORDER BY</tt> clause. */
  public static final String DESC = " DESC ";

  /**
   Represents the absence of any criteria. The value of this item is simply <tt>null</tt>.
   <P>If a method allows a <tt>null</tt> object to indicate the absence of any criteria, 
   then it is recommended that this reference be used instead of <tt>null</tt>.
  */
  public static final DynamicSql NONE = null;
  
  /**
   Constructor.
   <P>This constructor will slightly modify the given parameter: it will trim it, and prepend a new line to the result.
   @param aSql must have content; it will be trimmed by this method.
  */
  public DynamicSql(String aSql){
    if( ! Util.textHasContent(aSql) ){
      throw new IllegalArgumentException("The SQL text has no content.");
    }
    fSql = NEW_LINE + aSql.trim();
  }
  
  
  /** Convenience constructor, forwards to {@link #DynamicSql(String)}.  */
  public DynamicSql(StringBuilder aSql){
    this(aSql.toString());
  }
  
  /**
   Return the String passed to the constructor, trimmed.
   
  <P>The returned value is appended by the framework to an existing (possibly empty) entry in an <tt>.sql</tt> file.
  */
  @Override final public String toString(){
    return fSql;
  }

  // PRIVATE 
  
  private String fSql = "";
  
}
