A typical database enabled web application consists up of one or more servlets. When an HTTP request comes in, these servlets generally carry out the following sequential steps:
Web applications also need to send email, ensure consistent look and feel across all the servlets of a web application, and propagate any runtime error messages to the user. In reality, a lot of effort in writing web applications is spend in taking care of programming complexities rather than actually coding the user interface and business logic. The solution to this problem is to write web applications using web application development frameworks.
There are many web application frameworks available today. While SAF shares a lot of features with these frameworks(such as HTML templates) there are some significant differences as well. One of the main differences being how SAF generates queries based on user input. Unlike many other frameworks which completely ignore the issue, SAF has the concept of using tags within SQL queries. These tags take care of many complexities that would otherwise need to be coded into each web application.SAF was designed to be a small, simple, and easy to use web application development framework and is specially suitable for developing database enabled web applications.
Now that you have some familiarity with SAF and have had a chance to play around with some of the sample application, we are going to discuss how SAF based applications are called. In general, all HTTP requests first go to SkyServlet controller which must then redirect the request to an appropriate Form class. In order to allow SkyServlet to properly redirect requests, each HTTP request must have information that will allow SkyServlet to determine which form to redirect the request to.
SAF currently implements three calling convention that allow SkyServlet to redirect request to appropriate Form classes. These three calling conventions are:
The general format of a URL that can be used to invoke a specific Form class is:
When such a link is activated a request will be send to http(s)://some.domain.com/SkyWebApp/servlet/SkyServlet. The SkyServlet contoller will then read the value of parameter handler (FormHandlerClassName) and then forward the request to the Form class with the same name as the value of handler parameter.
The URL given below invokes a SAF based application using the URL calling convention. Try making changes to the parameter values to see how SAF automatically handles errors.
http://localhost:8080/SAF/servlet/SkyServlet?handler=MessageForm&name=SAFProgrammer
In submit button calling convention, HTTP GET or POST request is send to the controller when a user agent submits a form by clicking on the submit button. The HTML code for a simple form that can invoke a sky-based web application using submit-button calling convention is shown below.
Unlike the URL calling convention, the value of the Submit button identifies the Form class where SkyServlet controller will redirect this HTTP request.
<html>
<head>
<title>Simple HTML Form</title>
</head>
<body>
<form action="http://localhost:8080/SAF/servlet/SkyServlet" method="POST">
<input type="TEXT" name="name" size="20" maxlength="60">
<input type="SUBMIT" name="submit" value="MessageForm">
<input type="RESET" name="Reset" value="Reset">
</form>
</body>
</html>
The Hidden Field calling convention is somewhat of a hybrid between URL calling convention and the Submit button calling convention. In this case a hidden parameter is used in HTML form to identify the Form class where SkyServlet controller will redirect this HTTP request.
<html>
<head>
<title>Simple HTML Form</title>
</head>
<body>
<form action="http://localhost:8080/SAF/servlet/SkyServlet" method="POST">
<input type="TEXT" name="name" size="20" maxlength="60">
<input type="HIDDEN" name="handler" value="MessageForm">
<input type="SUBMIT" name="submit">
<input type="RESET" name="Reset" value="Reset">
</form>
</body>
</html>
In the last section, we discussed how client-side URLs and HTML forms can be written to invoke server-side SAF based applications. In this section, we will dig into implementation details of server-side SAF based application through the use of examples.
There are only two types of SAF forms: 1) forms that generate dynamic HTML output and 2) forms that generate dynamic image output. If you are writing a form that generates dynamic HTML output, then derive that form from sky.engine.form.HttpForm class. If you are implementing a form that generates dynamic images, then derive that form from sky.engine.form.HttpImageForm class.
In either cases, your form must have an explicit constructor and an execute method. The explicit constructor must have a specific signature and it must also invoke the constructor of super class (HttpForm or HttpImageForm). A basic class definition for any SAF based application that generate dynamic HTML output is shown below.
BasicForm.java
01 import sky.common.SkyContext;
02 import sky.message.Message;
03 import sky.engine.form.HttpForm;
04
05 public class BasicForm extends HttpForm
06 {
07 public BasicForm(SkyContext context) throws Message
08 {
09 super(context);
10 }
11
12 public void execute() throws Message
13 {
14
15 }
16 }
Now, we add a few lines to the code above to get the SimpleForm application:
MessageForm.java
01 import sky.common.SkyContext;
02 import sky.message.Message;
03 import sky.message.SuccessMessage; 04 05 import sky.engine.field.CharField; 06 import sky.engine.form.HttpForm; 07 08 public class MessageForm extends HttpForm 09 { 10 public MessageForm(SkyContext context) throws Message 11 { 12 super(context); 13 } 14 15 public void execute() throws Message 16 { 17 addField(new CharField("name", true, -1, -1));
18 throw new SuccessMessage("Hello " + this.getField("name").getStringData(0) + ", I am MessageForm."); 19 } 20 }
In line 17, we added a character field called "name". The second parameter is "true" which means that the field must be filled out by the user, otherwise SAF will automatically generate a message asking the user to go back and fill the field. The third and fourth parameter are -1 which means that the character field does not have any minimum or maximum length restrictions.
In line 18, we throw a SuccessMessage using the data that the user specified for name field either in an HTML form or in an HTML URL.
Note that there is more here than what meets the eye. Lines 10-13 initialize the Sky Framework so that the framework can take care of various complexities such as session tracking, database connection pooling (if database support is needed), email handling (if email support is needed), dynamic HTML generation, user-data error checking, etc. Line 17, binds the HTML form field "name", with a SAF Character field. Note that both the HTML form field and the SAF field must have the same names in order for binding to take place. Besides bindings, line 17 also specifies restrictions (whether the field is required or not, the maximum and minimum length of data) that are used for error checking user-specified data. Finally, line 18 creates a dynamic "Hello" message that you see on your web browser. This message like other SAF messages are based on the message.html template file that is present in your sample application project.
SAF Messages are similar to dialog boxes that you find in traditional OS based applications. They are useful for sending brief information to the user. If you need to send detailed information back to the user, use HTML templates.
Using HTML templates in forms is suitable for situations where you have a large HTML document in which certain fields (or sections of text) need to be changed depending upon the user-supplied data. In this case, 1) we first create an HTML document, 2) place SAF tags within the HTML document at appropriate place, and then 3) add a link to the HTML document in a SAF form.
Once again, we modify our basic form to illustrate this concept. However, this time, the file that contains the code for this form will be named TemplateForm.sky instead of TemplateForm.java. Any java class that contains a template must have file extension 'sky'.
TemplateForm.sky
01 import sky.common.SkyContext;
02 import sky.message.Message;
03
04 import sky.engine.field.CharField;
05 import sky.engine.field.IntField;
06 import sky.engine.form.HttpForm;
07
08 public class TemplateForm extends HttpForm
09 {
10 public TemplateForm(SkyContext context) throws Message
11 {
12 super(context);
13 }
14
15 public void execute() throws Message
16 {
17 addField(new IntField("count", true));
18 addField(new CharField("name", true, -1, -1));
19 /* <include "TemplateForm.html"> */
20 }
21 }
Note that this form has two fields count and name. with the restriction that count field will only contain integer values where as CharField will contain character value. Moreover, both the fields are required (because their second variables are set to true) and CharField must have a length of at least 2 characters and at most 20 characters. Additionally, line 19 links this form with HTML template TemplateForm.html which is shown below.
TemplateForm.html
<html>
<head><title>Template for <!--: getField("name").getStringData(0) :--></title></head>
<body>
<h1>Template for <!--: getField("name").getStringData(0) :-->
<table>
<!--%
int count = ((IntField)getField("count")).getIntData(0);
for (int i = 0; i < count; i++)
{
%--> <tr> <td><!--: i :--></td>
<td>Hello <!--: getField("name").getStringData(0) :--></td>
</tr>
<!--%
}
%--> </table> </body> </html>
Given TemplateForm.sky and TemplateForm.html in your SAF project, when you run ant war, the SAF preprocessor will take the contents of TemplateForm.html and put it into TemplateForm.sky at the location where the tag (/* <include "TemplateForm.html"> */) is present in TemplateForm.sky.
TemplateForm.java
import sky.common.SkyContext;
import sky.message.Message;
import sky.engine.field.CharField;
import sky.engine.field.IntField;
import sky.engine.form.HttpForm;
public class TemplateForm extends HttpForm
{
public TemplateForm(SkyContext context) throws Message
{
super(context);
}
public void execute() throws Message
{
addField(new IntField("count", true));
addField(new CharField("name", true, -1, -1));
_writer.write("<html>");
_writer.write(" <head><title>Template for " + getField("name").getStringData(0) + "</title></head>");
_writer.write(" <body>");
_writer.write(" <h1>Template for " + getField("name").getStringData(0) + "");
_writer.write(" <table>");
int count = ((IntField)getField("count")).getIntData(0);
for (int i = 0; i < count; i++)
{
_writer.write("");
_writer.write(" <tr>");
_writer.write(" <td>" + i + "</td>");
_writer.write(" <td>Hello " + getField("name").getStringData(0) + "</td>");
_writer.write(" </tr> ");
}
_writer.write("");
_writer.write(" </table>");
_writer.write(" </body>");
_writer.write("</html>");
_writer.write("</HTML>");
_writer.flush();
}
}
The resulting TemplateForm.java is then compiled into a SAF based web application. Again, note that, besides SAF does a lot of things here behind the scene. Try entering a string not representing a number into the count field and you will see.
In general, forms generate dynamic HTML content in a two-step procedure: First, the form receives an HTTP request and then the form responds to the request with HTML content. Hence, only one form is required to create dynamic HTML documents.
However, this two-step procedure does not work with Forms that generate images. This is mainly because of the fact that the server-side code must not only generate the dynamic image (mime type image/gif), but also generate the HTML code that contains the image (mime type: text/html). Moreover, there also needs to be a separate form where the user will fill out all the data which will be used to create the dynamic image. Therefore, generating dynamic images involves multiple steps. We illustrate these steps using the ChartCreator sample application.
<img src="/SAF/servlet/SkyServlet?handler=DrawChart&height=<!--: height :-->&width=<!--: width :-->"
alt=""
height="<!--: height :-->"
width="<!--: width :-->"
border="0">
At this point, I only know this method for generating and displaying dynamically generated images. There is some talk on the Internet about mime type multipart that may allow for reducing the number of steps involved in generating dynamic images as it may allow sending both HTML and image data back to the web browser at the same time. However, I think there are limitations on the browser side that do not allow usage of multipart mime type for creating dynamic images. If you have a concrete example to show how dynamic images and HTML code can be generated at the same time, do drop me an email.
In this section, we discussed SAF forms and demonstrated how one can create forms that generate dynamic HTML documents and dynamic images using SAF based forms. We recognize that your web application may need to dynamically generate a bar-code or an adobe pdf document. In this case you can create your derive your own Form class, by extending the current Form functionality. SAF has complete support for creating new forms in this way.
At this point, we have covered a lot of SAF usage, implementation, and concepts. However, we have still not talked anything about how SAF makes it easier for developers to create database and email enabled web applications. But before, we get into database and email, we need to discuss SAF fields.
In the last section, we discussed Forms and also saw examples of how Forms declare fields which automatically bind to the data that a user fills out on a web form or a url. In this section, we are going to delve deeper into the functionality provided by fields and also discuss various types of fields that are available in SAF.
Fields in general 1) contain one or more values of a specific data type, 2) automatically error check the values for syntactic errors such as entering a name where a number was required, 3) have data that is either optional or required, 4) aid in the automatic construction of SQL queries (which we will discuss in a later section).
CharField
Binds to any string or character data that a user may enter into an
HTML form field or text area. A CharField may be declared as follows
within a SAF based form:
CharField c = new CharField("city", true, 10, 20);
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
// 3rd argument : Minimum acceptable length of character data (number of characters). set to -1 if there is no minimum.
// 4th argument : Maximum acceptable length of character data (number of characters). set to -1 if there is no maximum.
CharListField
Derived from CharField and binds to HTML form or URL field that can
contain a list of strings where each string is separated from the next
string by a delimiter. A CharListField can be declared as follows:
CharListField c = new CharListField("cities", false, 2, 30, ";");
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
// 3rd argument : Minimum acceptable length of character data (number of characters). set to -1 if there is no minimum.
// 4th argument : Maximum acceptable length of character data (number of characters). set to -1 if there is no maximum.
// 5th argument : String delmiter seperating strings entered by the user in the HTML field.
EmailField
Binds to an HTML field or URL field that can have an email address as
its value. An EmailField can be declared as follows:
EmailField c = new EmailField("email", true);
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
IntField
Binds to an HTML field or URL field that can have an integer as its
value. An IntField can be declared as follows:
IntField c = new IntField("count", true);
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
RangeField
Binds to an HTML field or a URL field that declares a range of some
arbitrary values. A RangeField can be declared as follows:
RangeField c = new RangeField("range", true, "to");
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
// 3rd argument : Seperator between two values, i.e in (5 to 10), to is the separator.
SelectionField
Derived from IntField and binds to a Combo box or Selection field in an
HTML form. A SelectionField cannot be declared directly in a form since
each selection field can have different contents (selection of fruits,
car models, etc). You must derive your own class from the
SelectionField which represents the Combo box or Selection field in
your HTML form. A SelectionField can be declared as follows:
CarSelectionField c = new CarSelectionField("item", true);
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
SessionField
The servlet HttpSession object can contain name value pair type of
parameters. This field corresponds to a particular name value pair in
the servlet's HttpSession field and can be declared as follows:
SessionField c = new SessionField("chart");
// 1st argument : Exact name of the parameter in HttpSession object.
UrlField
Derived from CharField and binds to an HTML field that can contain URL
type strings. A URL field is declared as follows:
UrlField c = new UrlField("location", true);
// 1st argument : Exact name of the field in the HTML form or URL.
// 2nd argument : If the user must provide data for this field, then true, else false.
SAF built-in fields types are limited; however, these field types can be extended to create new fields. For example, you may wish to create a DateField, ZipCodeField, or PhoneNumberField.
In order to carry out database operations, web applications must execute pre-constructed sql queries against a database. Web applications either call database stored procedure to interact with databases or construct a query by concatenating strings of static sql fragments with user specified input data. Although stored procedures can offer better performance their use in web applications can make web applications highly dependent on specific database products with the implication that significant rework will be needed if the web application needs to be redeployed or distributed for some other database product. So, the SAF preferred method for interacting with databases is to dynamically construct queries which can then be executed on your database of choice. But you can still invoke database stored procedures using SAF.
As an example, consider an HTML login form with three fields: yourname, yourid, and password. The form has a constraint that the user must always enter a value for field password and a value for ATLEAST one of the fields yourname and yourid.
Depending upon whether the user has specified values for one or both of the fields yourname and yourid, the web application will generate one of the following three queries:
When 'yourname' and 'password' are filled with values 'klingon' and 'kronos' respectively and the field 'yourid' left blank:
Q1: SELECT p.id, p.name, p.location FROM person WHERE p.name = 'klingon' AND p.pass = 'kronos' When 'yourid' and 'password' are filled with values '54' and 'kronos' respectively and the field 'yourname' is left blank: Q2: SELECT p.id, p.name, p.location FROM person WHERE p.id = 54 AND p.pass = 'kronos' When both 'yourid' and 'yourname' are filled with '54' and 'klingon' respectively and 'password' is filled with 'kronos': Q3: SELECT p.id, p.name, p.location FROM person WHERE p.id = 54 AND p.name = 'klingon' AND p.pass = 'kronos'
At first glance, creating dynamic SQL queries based on user input appears to be simple, but its not since the following programming complexities must be dealt with:
Dealing with such programming complexities usually involves around 50 to 100 lines of code in the simplest of cases. Such code is also relatively error prone and boring to write. In order to take care of these complexities automatically the sky framework has introduced the concept of tags embedded within an SQL queries. Tags are rules that specify how a given HTML field and its value(s) can be expanded into an SQL statement.
For the case discussed above, 50 to 100 lines of programming can be reduced to a single SQL statement declaration with embedded tags as shown below. Once the programmer has written such an SQL statement, the rest of the tasks involved in generating an appropriate SQL query based on user input is carried out automatically by SAF. Binding of actual field data with tags present in the sql statement takes place automatically.
Example 1
A SAF based form may have the following sql query declared in it:
S1: SELECT p.id, p.name, p.location
FROM person
WHERE [FIELD yourname AS (p.name) QUOTED SOP '='] -- TAG T1
[FIELD yourid AS (p.id) SOP '=' COMBINE1 'AND'] -- TAG T2
AND p.pass = [FIELD password QUOTED] -- TAG T3
The query above contains three tags which are highlighted in red color. Note that tag T1 operates on HTML field yourname, tag T2 operates on HTML field yourid, and tag T3 operates on HTML field password. Given that the HTTP request has data for all three of these HTML field, tag T1, T2, and T3 will be expanded into sql fragments:
E1: p.name = 'klingon'
E2: AND p.id = '54'
E3: 'kronos'
However, if the user does not specify a value for yourname, then tag T1 will be expanded into an empty string. Similarly, if the user does not specify a value for yourid, then tag T2 will be expanded into empty string. Depending on such situations, one of the following three SQL queries will be generated by replacing tags in query S1
When 'yourname' and 'password' are filled with values 'klingon' and 'kronos' respectively:
Q1: SELECT p.id, p.name, p.location FROM person WHERE p.name = 'klingon' AND p.pass = 'kronos' |<------E1------>| |<-E3->| When 'yourid' and 'password' are filled with values '54' and 'kronos' respectively: Q2: SELECT p.id, p.name, p.location FROM person WHERE p.id = 54 AND p.pass = 'kronos' |<--E2-->| |<-E3->| When both 'yourid' and 'yourname' are filled with 54 and 'klingon' respectively and 'password' is filled with 'kronos': Q3: SELECT p.id, p.name, p.location FROM person WHERE p.id = 54 AND p.name = 'klingon' AND p.pass = 'kronos' |<--E2-->| |<------E1------>| |<-E3->|
We will dig into the details of how tags are expanded into sql fragments. For now, just keep in mind that if you write queries using tags, SAF will automatically bind the query with actual user supplied data values and then generate a valid sql query depending upon whether the user has filled out a specific field or not. SAF will not only generate the appropriate query, but also execute the query against the back-end database and then make the results for the query available to the SAF based form that is processing the HTTP request.
Syntax of tags embedded with SQL queries is represented by the diagram shown below. This diagram shows that tags can contain keywords (highlighted in blue) which operate on certain objects (highlighted in red).
-[-+---FIELD--+--name--+----------------------+--+----------+--+--------------+--+-------------+--+-----------------+--]-
| | | | | | | | | | | |
+--SESSION-+ +--(--AS-+-alias-+--)--+ +--QUOTED--+ +--AOP--+--op--+ +--DELM--del--+ +--COMBINE--comb--+
| | | | | |
+--HEADER--+ +-- , --+ +--SOP--+
| |
+--ROP--+
|<---------------->|<------------------------------------------------------------------------>|<---------------------->|
specifier expansion combination
Additionally, a tag can be logically split into three transformation stages: specifier, expansion, and combination (as shown in the syntax diagram above). The output of each stage serves as input of the next stage where it is further transformed based on how the tag has been written. As shown in the syntax diagram, only the specifier stage is required in a tag. The expansion and combination stages are optional.
Example 2
Let's take a second look at Tag T2 from query S1 given in example 1 earlier. In this query, the Specifier is shown in blue, the Expansion is shown in red, and the Combination is shown in green.
T2: [FIELD yourid AS (p.id) SOP '=' COMBINE 'AND']
Specifier indicates that this tag operates over the HTML field 'yourid'. Specifier simply replaces the field name with its value; hence, the sql fragment produced after the specifier stage will be:
54
where 54 is simply the value of the HTML form field yourid. The expansion stage will then map this value to the sql column name p.id using scalar operator '=' to produce the following sql fragment as output:
p.id = 54
The combination stage is responsible for combining the sql fragment produced by expansion stage with any sql fragment that preceded the tag. So if the tag did not appear in the beginning of the sql string, the combination stage will produce the following sql fragment:
E2: AND p.id = 54
So the basic thing to remember here is that each stage transforms the sql fragment that was produced by the previous stage into another sql fragment.
Example 3
Now we take a deeper look at tag T3 from query S1 given in example 1 earlier.
T3: [FIELD password QUOTED]
Tag T3 contains only specifier and an expansion. In does not contain a combination phase. Specifier indicates that the tag operates over the HTML field password and hence produces the value of field password as its output. Expansion only has the keyword QUOTED, which is responsible for putting any field values that are produced by the specifier stage within quotes. So the result is field value surrounded by quotes as shown below:
E3: 'kronos'
In the next few sections, we will take a detailed look at specifier, expansion, and combination stages of the tag.
-[-+---FIELD--+--name--+ ...
| |
+--SESSION-+
| |
+--HEADER--+
|<----------------->|
specifier
In web applications, SQL queries can use data from three different sources: HTML fields, Servlet Session parameters, and HTTP header parameters. SAF based tags have the functionality for using data from these three sources in a query. As you can see from the syntax diagram, the specifier part contains a keyword and a literal. The keyword, which can either FIELD, SESSION, or HEADER identifies the source of data; while as, the literal identifies the name of the parameter whose value is to be used in the sql query.
Example 4
[ FIELD yourname ...
Produce the value of the HTML field yourname as output.
[ SESSION userid ...
Produce the value of HttpSession parameter userid as output.
[ HEADER http-remote-host ...
Produce values from an HTTP header HTTP-REMOTE-HOST as output.
A tag must always contain a specifier. However, a tag may or may not contain an expansion or a combination. The smallest tag is one that contains only a specifier. For example, consider an HTML form field 'id' with one or more values.
Example 5
If field 'id' has a value of 54, then the query:
S2: SELECT p.name, p.email FROM person p WHERE p.id = [FIELD 'id']
will be expaded into:
Q4: SELECT p.name, p.email FROM person p WHERE p.id = 54
Since the tag used in query S2 above only had the specifier part, it simply resulted into the value of field id. However, if the HTML field id did not contain any value, then the tag would have expanded into an empty string and hence query S2 would have been transformed into:
Q5: SELECT p.name, p.email FROM person p WHERE p.id =
Q5 is an incomplete SQL query and if Q5 is executed a syntax error will be returned by the database. However, if you declared the field id as a required field in your SAF based form, then this query will never get generated since the user will automatically get a response asking them to fill out the required field. However, for the scope of this example, we rewrite query S2 to include an expansion section in the tag:
S3: SELECT p.name, p.email FROM person p [FIELD id AS (p.id) SOP '=']
When field 'id' has a value, query S3 will be expanded into query Q4 shown above. However, when field 'id' does not have any value, then query S3 will be expanded into:
Q5: SELECT p.name, p.email FROM person p
In your own web application, you may either use S2 or S3 depending upon the results desired.
So far we have given examples of tags that operate over HTML field. An example using header field http-remote-host is given below.
Example 6
If a client browser has initiated a connection from IP Address 131.123.22.144, then the query:
S4: SELECT count(*) FROM log WHERE client_ip = '[HEADER http-remote-host]'
will get expanded into
Q6: SELECT count(*) FROM log WHERE client_ip = '131.123.22.144'
Query S4 can also be rewritten as:
S5: SELECT count(*) FROM log WHERE [HEADER http-remote-host QUOTED]
to produce the same resultant query Q6. Note that, query S4 has been modified into query S5 in two ways. First, we removed the quotes around the tag in query S4 and second, we introduced the QUOTED keyword inside the tag. The QUOTED keyword is part of the expansion section of the tag and simply quotes any values produced by the specifier section.
As you can see a query can be written in different ways with different tags to produce the same resulting sql statement as output.
---+----------------------+--+----------+--+--------------+--+-------------+--
| | | | | | | |
+--(--AS-+-alias-+--)--+ +--QUOTED--+ +--AOP--+--op--+ +--DELM--del--+
| | | |
+-- , --+ +--SOP--+
| |
+--ROP--+
|<---------------------->|<----------->|<--------------->|<------------->|
name mapping quote operator delimiter
|<---------------------------------------------------------------------->|
expansion
The expansion part of the tag can be further subdivided into four parts: name mapping, quote, operator, delimiter. The name mapping part allows us to map the HTML (or SESSION or HEADER) field names to sql column names. Whenever a tag has name mapping part, it must also have an operator part, since mapping generates SQL expressions through the use of operators. If the operator is missing, then the default value of operator is =.
The examples below will show that name mapping actually carry out a cross product (or join in database terminology) between field names and sql column names to produce sql expressions. A single (or multiple) field name(s) can be mapped to single (or multiple) sql column name(s). As a result of this mapping, we are able to accurately map one or more fields in the HTML form to one or more fields in database tables or vice versa. This is an extremely useful feature when it comes to quickly developing database intensive web applications.
Example 7
Now assume that a user fills out an email address in an HTML form and clicks the submit button. The expected result is a name corresponding to the email address from the company database. In this case, the SAF based form that will process the request may have the following query declared in it:
S6: SELECT name FROM person p WHERE [FIELD email AS (p.personal_email_address) QUOTED]
Let's take a closer look at the tag from the query above:
T4: [FIELD email AS (p.personal_email_address) QUOTED ]
|<--------------------------->|<------>
name mapping quote
In this tag the name mapping part of the expansion is shown in blue and the quote part of the expansion is shown in red. Since the tag has name mapping, but no operator, the default value of operator is set to = . Since the QUOTE keyword is present, all the values of the field must be quoted. The name mapping using the = operator and quoting of field values will result in the following sql expression:
E4: p.personal_email = 'albert@pinto.com'
As you can see, the value of HTML form field email is mapped to sql column name p.personal_email using default operator =. So query S6 will be transformed into:
Q7: SELECT name FROM person p WHERE p.personal_email = 'albert@pinto.com'
Example 8
Now lets assume that the user wanted the names of all the people whose email address was lexically greater than the email address that the user filled out on the web form. In this case, the SAF based form that handles the query request will need to decare the following query (which is a slight modification of query S6):
S7: SELECT name FROM person p WHERE [FIELD email AS (p.personal_email) QUOTED SOP '>']
In query S7, the web developer has specified mapping through operator > rather than letting the operator default to =. Hence the query will expand to:
Q8: SELECT name FROM person p WHERE p.personal_email > 'albert@pinto.com'
Note the difference between queries S6 and S7 and how they were expanded into queries Q7 and Q8 through the use of operators.
Name mapping can be very flexible. You don't have to just use scalar operators (SOP) such as =, <, >, etc, but can also use aggregate operators (AOP) such as the sql IN operator. You can also map a single HTML field to multiple sql columns where the HTML field may contain one or more values.
Example 9
For a more realistic example, consider an HTML form containing a selection list, where a user can select multiple email addresses and then submit the form to request the actual name of the people who own those email addresses. In this case, the SAF based form will contain the following query:
S8: SELECT name,email FROM person p WHERE [FIELD email AS (p.personal_email) QUOTED AOP 'IN']
Note that the tag in the query above contains a name mapping part that is mapping one field (email) to one sql column name (p.persnal_email). Furthermore, this query is written with the expectation that field email can have multiple values. Therfore, we use the SQL operator IN to combine these multiple values into a single sql expression. Assuming that HTML field email contains the values a@b.com, b@b.com, and c@b.com, the query S8 will be transformed into:
Q9: SELECT name, email FROM person p WHERE p.personal_email IN ('a@b.com', 'b@b.com', 'c@b.com')
Note that the tag (shown in query S8) was transformed into a single SQL expression (shown in query Q9) based on the sql column name (p.personal_email) and values of field email. Multiple values were combined using the SQL IN operator. Since we were mapping only one field name to only one sql column name, only one SQL expression was generated.
Example 10
To make our realistic example a bit more complicated, assume that each person in the person table that we are using in our queries had a company_email address also along with a personal_email address. Moreover, the web developer wants to write a form where a user will select multiple email addresses in a selection field on the form and those email addresses will then be searched against both personal_email and company_email columns. How would you do this?
In SAF, we once again use name mapping to accomplish this task using the query given below:
S9: SELECT name, email
FROM person p
WHERE [FIELD email AS(p.personal_email, p.company_email) QUOTED AOP 'IN' DELM 'OR'] |<-specifier->|<---------- name mapping ----------->|<- quote ->|<- operator ->|<- delimiter ->|
The tag shown in query above specifies that field email should be mapped to two sql column names (p.personal_email and p.company_email). This mapping will lead to generation of two SQL expressions which then need to be combined into a single sql expression. So for combining multiple sql expressions into a single SQL expression we use the keyword DELM. The delimiter in the above query specifies that all the sql expression generated through name mapping should be combined with each other using 'OR'. Assuming that HTML field email contains the values a@b.com, b@b.com, and c@b.com, the query S8 will be transformed into:
Q10: SELECT name, email
FROM person p
WHERE p.personal_email IN ('a@b.com', 'b@b.com', 'c@b.com') OR p.company_email IN('a@b.com', 'b@b.com', 'c@b.com') |<----------------- expression #1 ----------------->| |<--------------- expression #2 ----------------->|
Query Q10 was generated by transforming the tag in query S9. As you can see the tag produced two expressions which were combined using the delimiter 'OR'. Query Q10 basically queries the database to find one or more email addresses that may either be in p.personal_email or p.company_email columns.
Besides mapping a single HTML field to multiple sql column names, tags can also be used to map mutiple HTML fields to a single sql column. This pattern is useful when you need to query data in a single sql column using the information that the user has filled out in multiple fields in an HTML form.
Example 11
In this example we consider a job search form which contains three fields. In each of these three fields, user can select a state and then submit the form to get all the jobs for the selected states. The sql query for such a search may look like this:
S10: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE [FIELD state1 AS(j.location) state2 AS (j.location) state3 AS(j.location) QUOTED SOP '=' DELM 'OR']
In the query above, we are mapping three different fields (state1, state2, state3) to the same sql column name (j.location). This means that the tag will produce three sql expressions (3x1) which will then be combined into a single sql expression using the delimiter 'OR'. Assuming that state1 contains the value 'CA', state2 contains the value 'OH', and state3 contains the value 'TX', the query generated is:
Q11: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE j.location = 'CA' OR j.location = 'OH' OR j.location = 'TX'
-+-----------------+-]-
| |
+--COMBINE--comb--+
|<---------------->|
combination
So far, we have shown how tagged sql queries can be used to generate actual sql queries with field data using sql query examples with one tag. In real web applications however, it is common to see SQL queries with multiple tags. In such queries, depending upon whether a field has zero, one, or more values, tags may be expanded into an empty string or a string that represents an sql expression. Often, the use of multiple tags in an sql query introduces complexities regarding how the expressions generated by these multiple tags can be combined. This issue is addressed by the combination part of the tag. When a tag has combination part in it, the combination part dictates how the expression generated by the tag will be combined with any string that appears before the tag.
Example 12
S11: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE [FIELD state AS (j.location) QUOTED SOP '='] AND [FIELD comp AS (j.company) QUOTED SOP '=']
Query S11 contains two tags: one operating on field state and the other on field comp. If state has the value 'NJ' and comp has the value 'Web Applications, Inc.'. Then the following sql expression will be generated:
Q11: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE j.location = 'NJ' AND j.company = 'Web Applications, Inc.'
As you can see, in Q11, the two expressions that were generated by the two tags are combined by 'AND' since 'AND' was already present in query S11 between the two tags. However, if HTML field comp did not contain any values, then the following query would have been generated:
Q12: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE j.location = 'NJ' AND
Query Q12 is obviously invalid becuase it has a syntax error (the query ends with AND keyword). So noticing this problem, we realize that 'AND' should be present only when the second tag evaluates to an sql expression. Therefore we introduce the COMBINE keyword in the second tag as shown below.
S11: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE [FIELD state AS (j.location) QUOTED SOP '='] [FIELD comp AS (j.company) QUOTED SOP '=' COMBINE 'AND']
When values for both the fields state and comp are present, this query will be transformed into query Q11 shown above. However, when comp does not have any values, the following query will be generated.
Q13: SELECT j.id, j.title, j.company, j.date_posted
FROM jobs j
WHERE j.location = 'NJ'
As you can see, query Q13 does not end with 'AND' as in query Q12. Query Q13 is valid becuase we used combination part in the second tag.