package com.smartgwt.client.docs;
/**
* <h3>Custom Querying Overview</h3>
* <b>This feature, called "SQL Templating", is available with Power or better licenses
* only.</b> See <a href="http://smartclient.com/product">smartclient.com/product</a> for details.
* <p>
* The Smart GWT server provides a number of ways to let you customize the SQL or Hibernate
* query it generates to fetch data from or update your database. You can provide full
* custom queries in either {@link com.smartgwt.client.docs.serverds.OperationBinding#customSQL SQL} or
* {@link com.smartgwt.client.docs.serverds.OperationBinding#customHQL HQL}, or you can replace individual parts of the
* query
* ({@link com.smartgwt.client.docs.serverds.OperationBinding#whereClause the WHERE clause}, for example) while letting
* Smart GWT generate the rest.
* <P>
* Full custom queries specified via <customSQL> provide complete flexibility, but
* they cannot be used for automatic data paging; if you use a full custom query, all data
* returned by the query will be delivered to the client, which may be inefficient.
* To retain automatic data paging, implement your customizations by replacing just specific
* clauses of the query, via {@link com.smartgwt.client.docs.serverds.OperationBinding#whereClause <whereClause>},
* {@link com.smartgwt.client.docs.serverds.OperationBinding#selectClause <selectClause>}, and the other
* clause-by-clause
* replacement features.
* <p>
* Query customization is done per {@link com.smartgwt.client.data.OperationBinding}, so you can create multiple
* customized queries per DataSource or even for the same
* {@link com.smartgwt.client.data.OperationBinding#getOperationType operation type}.
*
* <h4>Using criteria and values</h4>
* Whether using full custom queries or individual clauses, your code has access to the
* criteria values submitted with the operation; if this is an "add" or "update" operation,
* it also has access to the new field values sent from the client.
* <p>
* Fields are accessed in your SQL or HQL code using the Velocity template language. You
* can refer to container variables <b>$criteria</b> and <b>$values</b> in your queries or
* clause snippets, and Smart GWT will insert the appropriate values. A simple
* {@link com.smartgwt.client.docs.serverds.OperationBinding#whereClause whereClause} example:
* <p>
* <pre><code>
* <operationBinding operationType="fetch">
* <whereClause>
* continent = $criteria.continent AND population > $criteria.minPop
* </whereClause>
* </operationBinding>
* </code></pre>
* In addition to the $criteria and $values Velocity template variables described above, we
* also provide a number of template variables containing generally-useful values. Please see
* {@link com.smartgwt.client.docs.VelocitySupport} for details.
*
* <h4>Using the default clauses</h4>
* You also have access to the default subclauses generated by
* Smart GWT. You can use these in full custom queries to allow a certain part of the query
* code to be generated:<p>
* <pre>
* <customSQL>
* SELECT foo, bar FROM $defaultTableClause
* WHERE baz > $criteria.baz
* </customSQL>
* </pre>
* <p>
* You can also use them within individual clauses in order to customize a clause without
* losing default SQL generation:
* <pre>
* <whereClause>
* ($defaultWhereClause) AND foo > 5
* </whereClause>
* </pre>
*
* <h4>Mixing SQL Templating and custom Java Logic</h4>
* You can use both SQL Templating and custom Java logic added via DMI in the same
* operationBinding. Your DMI method is called before SQL is generated, and the SQL template
* will be evalauted and the actual SQL operation performed only when you call
* dsRequest.execute().
* <P>
* This allows you to modify the criteria or values on the DSRequest, which will change the
* values retrieved by $criteria and $values when the SQL Template is evaluated. You can also
* add entirely new information to the Velocity context used to evaluate the template, via
* the server-side API DSRequest.addToTemplateContext().
*
* <h4>Excluding fields from SQL Generation</h4>
* In some cases you pass a value to the server which is intended to be used by custom Java
* logic and should not cause SQL to be generated. To prevent all SQL from being generated for
* a particular field, set {@link com.smartgwt.client.data.DataSourceField#getCustomSQL customSQL="true"} on that field.
* <P>
* Any field for which SQL will ever be generated must be declared in a DataSource. It's
* common to have a field which is only used in one or two operationBindings - in this case,
* set customSQL="true" on the field, and use {@link com.smartgwt.client.docs.serverds.OperationBinding#customFields
* customFields} to cause
* specific operationBindings to generate SQL for the field, while all others ignore it.
* <P>
* In other cases you want to hand-write SQL for a particular field for a specific
* operationBinding. You can set {@link com.smartgwt.client.docs.serverds.OperationBinding#excludeCriteriaFields
* excludeCriteriaFields} to
* exclude fields from SQL generation for the whereClause of a specific operationBinding.
*
* <h4>Field-level SQL Customization</h4>
* An individual field can configured with custom expressions to be used in different SQL
* statements for all operationBindings - see {@link com.smartgwt.client.data.DataSourceField#getCustomSQL customSQL} for
* an overview.
*
* <h4>Using AdvancedCriteria</h4>
* The above examples involving $criteria assume that the submitted criteria are simple
* Criteria and not {@link com.smartgwt.client.data.AdvancedCriteria}, a more sophisticated criteria format in which
* different search operators can be specified per field and criteria can be nested.
* <P>
* The special variable $advancedCriteria provides simplified access to the AdvancedCriteria
* structure: $advancedCriteria.<i>fieldName</i> will return the criteria value specified for a
* given fieldName, regardless of where it's present in the AdvancedCriteria.
* <P>
* This makes it straightforward to add an additional criteria value to AdvancedCriteria that
* you want to use only in the SQL template:
* <ul>
* <li> make a simple Criteria object representing the fieldName and value name you need to have
* available in the SQL template
* <li> use {@link com.smartgwt.client.data.DataSource#combineCriteria DataSource.combineCriteria} to add your additional
* criteria to an existing
* AdvancedCriteria, wherever this is convenient
* <li> list the fieldName in {@link com.smartgwt.client.docs.serverds.OperationBinding#customCriteriaFields
* customCriteriaFields} to prevent the
* default SQL for this field from being generated
* <li> use $advancedCriteria in your customized SQL to access the value
* </ul>
* Java API dsRequest.getCriteriaValue() is equivalent to accessing $advancedCriteria in a SQL
* Template. Also note, if a given fieldName appears more than once in AdvancedCriteria,
* $advancedCriteria.<i>fieldName</i> will return the value for the first {@link com.smartgwt.client.data.Criterion} that
* uses the fieldName, as found by depth-first search.
* <p>
* NOTE: $advancedCriteria falls back to simple criteria values if the current criteria object
* is not an <code>AdvancedCriteria</code>. This means that you can safely use $advancedCriteria
* in circumstances where you cannot predict in advance whether your server code will be handed
* a simple criteria or an AdvancedCriteria.
*
* <h4>Stored procedures</h4>
* It is possible to include templated calls to SQL stored procedures in a
* {@link com.smartgwt.client.docs.serverds.OperationBinding#customSQL customSQL} clause, for the ultimate in flexibility.
* For
* example, the deletion of an order might require a number of actions: deletion of the order
* record itself, messages sent to other systems (data warehousing, maybe, or a central accounts
* system running on a mainframe), an event log written, and so on. You could write a stored
* procedure to do all this, and then invoke it with a customSQL clause:
* <pre>
* <operationBinding operationType="remove">
* <customSQL>call deleteOrder($criteria.orderNo)</customSQL>
* </operationBinding>
* </pre>
*
* <h4>Velocity Template Language conditional logic</h4>
* When writing a customized SQL clause for an operation, it is commonly desirable to be
* able to include conditional logic - for example only modifying a where clause if a
* certain criteria value is present. Velocity template language conditional statements
* can be embedded directly into your template code to achieve this. For example the following
* <code>whereClause</code> would produce different output depending on whether the
* request criteria included a value for the field <code><i>someField</i></code>:<p>
* <code><whereClause>$defaultWhereClause #if ($criteria.someField) AND someDatabaseField = $criteria.someField
* #end</whereClause></code>
* <p>
* If <code><i>criteria.someField</i></code> was not present in the request, the generated
* SQL statement would simply use the default where clause -- otherwise
* <code>AND someDatabaseField = <i>[some value]</i></code> would be appended to it (where
* <code><i>[some value]</i></code> was picked up from the value of <code>someField</code> on
* the request criteria object).
*
* <h4>Custom queries are safe</h4>
* Custom queries are protected from <a href=http://en.wikipedia.org/wiki/SQL_injection>
* SQL injection attacks</a>, because anything coming from the client is quoted and escaped
* in accordance with the syntax of the underlying database before use (though see the warning
* about using <code>$rawValue</code> in the article on {@link com.smartgwt.client.docs.VelocitySupport}).
* So, in a typical SQL injection attack an attacker might enter his User ID as <br>
* <code>123' OR '1' = '1</code><p>
* in the hope that this will generate a query
* with a where clause like this<br>
* <code>WHERE userID = '123' OR '1' = '1'</code><p>
* which would of course return every row. With Smart GWT custom queries, this does not happen;
* the client-provided string is escaped, and the resultant clause would look like this: <br>
* <code>WHERE userID = '123'' OR ''1'' = ''1'</code><p>
* This clause only returns those records where the userID column contains the literal value that
* the user typed: <br>
* <code>123' OR '1' = '1</code>
* <p>
* Further, custom queries can be protected from buggy or ad-hoc client requests because the
* query is specified on the server. For example you could add a custom where clause, as shown
* in the above section on default clauses, to ensure that certain records are never seen by
* the client. For instance: <p>
* <code><whereClause>($defaultWhereClause) AND confidential = '0'</whereClause></code>.
* <p>
* <h4>Column case-sensitivity issues</h4>
* Different database products have different rules concerning case-sensitivity in column
* names. Consider the following query:
* <br><br><code> SELECT orderNumber FROM Order</code>
* <ul>
* <li>MySQL and Microsoft SQL Server are not case-sensitive with regard to column names, so
* this query will work whether the column is called "orderNumber" or "ORDERNUMBER" or any
* other variation.</li>
* <li>Oracle, HSQLDB and DB2 default to upper-case column names. Therefore, this query will
* fail if the column is actually called "orderNumber"; it will only work if the underlying
* column name is "ORDERNUMBER"</li>
* <li>PostgreSQL defaults to lower-case column names, so this query will fail unless the
* underlying column name is actually "ordernumber"</li>
* </ul>
* Note that these differences only apply in a practical sense if the underlying database
* table was created with quoted column names. If you create your tables without quoting
* column names, the database creates the columns using its own preferred defaults, which
* is what it will also use when it encounters an unquoted column name in a query. Behind
* the scenes, the differences are still there - your column will be called "ORDERNUMBER"
* on Oracle and "ordernumber" on PostgreSQL - but that wouldn't be apparent unless you went
* looking for it: the example query would work unchanged with both products, and you would
* be able to use whatever mixture of case you like in your DataSource field names
* (Smart GWT will map DataSource field "orderNumber" to Oracle column "ORDERNUMBER"
* transparently). <b>This is the recommended approach.</b>
* <p>
* If you can't, or don't want to, accept the database default - if you are working with an
* existing schema, for example - then you will need to quote column names in your queries.
* Unfortunately, the way you do this also differs by database product, so quoting a column
* name correctly in one database's syntax may mean that the query cannot be ported to a
* different database without change.
* <p>
* To help with this case, we provide two extra container variables that you can use.
* <b>$fields</b> contains the names of all the fields in your DataSource, but quoted in
* accordance with the column-quoting rules of the target database. <b>$qfields</b> also
* contains a list of field names, but in this case each one is qualified with its table
* name.<p>
* As an example of how to use <b>$fields</b> and <b>$qfields</b>, consider a DataSource with
* a field called "itemID", bound to a column also called "itemID", and a tableName property
* of "orderItem". Here are three ways to write a {@link com.smartgwt.client.docs.serverds.OperationBinding#selectClause
* selectClause}
* for a custom SQL query that returns that field:<ul>
* <li><code>orderItem."itemID"</code>
* <li><code>orderItem.$fields.itemID</code>
* <li><code>$qfields.itemID</code>
* </ul>
* The first of these is not portable. It will work fine in HSQL and Oracle, but will fail
* with a syntax error in MySQL because you quote a field name with backticks in MySQL, not
* quote marks.
* <p>
* The usages via <b>$fields</b> and <b>$qfields</b> <em>are</em> portable. The second line,
* when targeting Oracle, will be translated to <code>orderItem."itemID"</code>; when targeting
* MySQL, it will be translated to <code>orderItem.itemID</code>, or <code>orderItem.`itemID`</code>
* if column quoting is enabled for that database (it generally isn't required, since MySQL
* preserves case by default).
* @see com.smartgwt.client.docs.serverds.OperationBinding#selectClause
* @see com.smartgwt.client.docs.serverds.OperationBinding#tableClause
* @see com.smartgwt.client.docs.serverds.OperationBinding#whereClause
* @see com.smartgwt.client.docs.serverds.OperationBinding#groupClause
* @see com.smartgwt.client.docs.serverds.OperationBinding#orderClause
* @see com.smartgwt.client.docs.serverds.OperationBinding#valuesClause
* @see com.smartgwt.client.docs.serverds.OperationBinding#customSQL
* @see com.smartgwt.client.docs.serverds.OperationBinding#customHQL
* @see com.smartgwt.client.docs.serverds.OperationBinding#customFields
* @see com.smartgwt.client.docs.serverds.OperationBinding#customValueFields
* @see com.smartgwt.client.docs.serverds.OperationBinding#customCriteriaFields
* @see com.smartgwt.client.docs.serverds.OperationBinding#excludeCriteriaFields
* @see com.smartgwt.client.docs.serverds.OperationBinding#useForCacheSync
* @see com.smartgwt.client.docs.serverds.OperationBinding#cacheSyncOperation
* @see com.smartgwt.client.docs.serverds.OperationBinding#canSyncCache
* @see com.smartgwt.client.docs.serverds.OperationBinding#sqlType
* @see com.smartgwt.client.types.SQLType
*/
public interface CustomQuerying {
}