لطفا منتظر باشید ...
Adding the Portal Functionality
The characteristic that separates a portal site from a normal web site is its ability to be personalized for the specific user. This can be done by creating a login area that, once successfully accessed, retrieves a profile that results in a version of the site that is customized to the user. In this section, we will define that customization based on roles.
To set up this personalization feature, you need to first define a database schema for the user information, create a form to sign up new users, create an administrative page to approve users and assign roles, and create a login area for users to sign in.
Creating the User Schema
To define the database schema, you first must determine what information is required to identify the user uniquely and what information that you want to maintain for each user. For this application, you will store the following information for each user:
user_id Generated primary key
uname User login name
first_name User’s first name
last_name User’s last name
email User’s e-mail address
password User’s password
role User’s role (user, admin, manager, developer)
status User’s approval status
register_date User’s registration date
last_visit Date of user’s last visit
comments Notes regarding the user
All of these values can be stored in a single USERS table because there is no data that requires nesting. You can create this table by using the following SQL script:
CREATE TABLE USERS(
user_id VARCHAR2(20) PRIMARY KEY,
uname varchar2(20),
first_name VARCHAR2(100),
last_name VARCHAR2(100),
email VARCHAR2(200),
password varchar2(20),
role VARCHAR2(10),
status varchar2(20),
register_date DATE,
last_visit date,
comments varchar2(4000)
);
ALTER TABLE USERS
ADD CONSTRAINT CK_ROLE
CHECK (role IN ('user', 'admin', 'manager', 'developer'));
CREATE SEQUENCE users_seq START WITH 100;
CREATE or replace TRIGGER users_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
select users_seq.nextval into :new.user_id from dual;
select sysdate into :new.register_date from dual;
select 'pending' into :new.status from dual;
END;
/
show errors;
Note that you need to define a trigger to handle the following:
Inserting the user_id from a sequence
The registration date and time from the database current date and time
Adding pending into the status column as an initial value
Creating the Login Area
Before discussing how to create the user application form, we will discuss how to add the login area to the main page. Referring to this page, you see the following section that adds the area:
<login>
<xsql:include-xsql reparse="yes" href="/>
</login>
This element calls to create the box and provide the functionality. This is the most sophisticated XSQL file presented, so we will examine it in detail:
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="msie" href="?>
<?xml-stylesheet type="text/xsl" media="mozilla" href="?>
<page connection="xdkus" xmlns:xsql="urn:oracle-xsql">
<xsql:if-param name="action" equals="login">
<xsql:set-session-param name="usr" ignore-empty-value="no">select uname
from users
where uname='{@logusr}' and password='{@passwd}'</xsql:set-session-param>
</xsql:if-param>
<xsql:if-param name="action" equals="logout">
<xsql:set-session-param name="usr" value=" "/>
</xsql:if-param>
<xsql:if-param name="usr" not-equals=" ">
<xsql:query>
select first_name ||', '|| last_name as name, role
from users
where uname='{@usr}'</xsql:query>
<xsql:dml commit="yes">
update users set last_visit=sysdate where uname='{@usr}'
</xsql:dml>
<xsql:set-session-param name="role" ignore-empty-value="no">
select role
from users
where uname='{@usr}'</xsql:set-session-param>
</xsql:if-param>
<xsql:if-param name="usr" exists="no">
<backtohome/>
</xsql:if-param>
<xsql:include-param name="usr"/>
<xsql:include-param name="action"/>
<xsql:include-xml href="/>
</page>
This page has a number of conditional sections based upon both the way it is called and the parameters passed. The first set of conditions defines a Login and Logout action. Intrinsic in handling a Login action is the ability to set up a session. This is accomplished using the <xsql:set-session-param> element and insuring that a uname is return by setting the attribute, ignore-empty-value, equal to no. This initializes usr, which then satisfies the condition not-equals=" " permitting the query inside <xsql:query> to be executed as well as inserting the current date using the <xsql:dml> element content. Finally, the nav_login.xml page is included as follows:
<nav_bar>
<category name="admin">
<link uri="/xdkus/admin/index.xsql?pagename=news">Administration</link>
<link uri="/xdkus/app_document/doc.xsql?pagename=xdk_perf">Performance
test</link>
<link uri="/xdkus/app_customer/index.xsql">Customer Reference</link>
<link status="new" uri="/xdkus/app_document/doc.xsql?
pagename=schemamapping">SchemaMapping</link>
</category>
<category name="manager">
<link uri="/xdkus/app_document/doc.xsql?pagename=xdk_perf">Performance
Test</link>
<link uri="/xdkus/app_customer/index.xsql">Customer Reference</link>
<link status="new" uri="/xdkus/app_document/doc.xsql?
pagename=schemamapping">SchemaMapping</link>
</category>
<category name="user">
<link uri="/xdkus/app_customer/index.xsql">Customer Reference</link>
<link uri="/xdkus/app_document/doc.xsql?pagename=xdk_perf">Performance
Test</link>
</category>
<category name="developer">
<link uri="/xdkus/app_document/doc.xsql?pagename=xdk_perf">Performance
Test</link>
<link uri="/xdkus/app_customer/index.xsql">Customer Reference</link>
</category>
</nav_bar>
The stylesheet declared at the top of the Login page then selects the proper category based upon the role specified in the name attribute of the <category> element. This illustrates how you can mix file-based dynamic content with database content. Obviously, these roles and links could also be retrieved from the database.
Finally, the Login XSQL page handles the conditions of Logout by nulling usr, which returns the user to the home page, as well as conditions where parts of the login are missing.
Creating the User Application Form
The XSQL Servlet makes it easy to create web forms that are designed for submitting data into a database. The full range of input types is available. Figure 15-3 shows the finished form displayed within the center window.
Figure 15-3: User application form
This form is created by passing the name of the XSQL page to index.xsql and refreshing the page. In this case, the URL is http://localhost:8988/xdkus/index.xsql?pagename=register.
Referring to the code in the index.xsql page, the existence of the pagename parameter causes its value to be used to identify the XSQL page that is to be loaded. In this case, it is register.xsql, which is as follows:
<?xml version="1.0"?>
<page id="1" title="XDK User Registration" connection="xdkus"
xmlns:xsql="urn:oracle-xsql">
<xsql:include-xml href="/>
</page>
This page includes an XHTML fragment—located in the document named register.xml—that adds the form and its associated fields. This form uses the POST method and another XSQL page to submit the data as follows:
<form method="post" action="registerAction.xsql" name="adduser">
<table width="60%" border="0">
<tr> <td>First Name:</td>
<td><input type="text" name="FIRST_NAME" size="70"/></td>
</tr>
<tr><td>Last Name:</td>
<td><input type="text" name="LAST_NAME" size="70"/></td>
</tr>
<tr><td>Role:</td>
<td><select name="ROLE">
<option value="user" selected="selected">XDK User</option>
<option value="manager">XDK Management User</option>
<option value="admin">XDK Administrator</option>
<option value="developer">XDK Development User</option>
</select>
</td>
</tr>
<tr><td>Email:</td>
<td><input type="text" name="EMAIL" size="70"/></td>
</tr>
<tr><td valign="top">Comments:</td>
<td><textarea name="COMMENTS" cols="55" rows="5">Please tell us more
about how you use XML and the Oracle XDK...</textarea>
</td>
</tr>
<tr><td valign="top" height="8"> </td>
<td height="8"><input type="submit" name="Submit" value="Submit"/></td>
</tr>
</table>
</form>
This form uses a combination of <input> and <select> elements to build a set of parameters to pass to the registerAction.xsql page. This page handles the insert into the database as follows:
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="mozilla" href="?>
<page title="Add new XDK User" connection="xdkus" xmlns:xsql="urn:oracle-xsql">
<xsql:insert-request date-format="MM/dd/yyyy" table="USERS"
transform="xsl/generateXML.xsl"/>
</page>
This XSQL page has two stylesheet references. The registerAction.xsl stylesheet simply transforms the output as has been done previously. However, the following generateXML.xsl stylesheet performs a different function. When used as part of the <xsql:insert-request> element, it creates the XML document from the form’s data passed as parameters and passes it into a SQL query as follows:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>
<xsl:template match="/">
<ROWSET>
<ROW>
<xsl:copy-of select="request/parameters//*[name() !='Submit' and name()
!='table' and name() !='keycolmn']"/>
</ROW>
</ROWSET>
</xsl:template>
<xsl:template match="node()|@*">
<!– Copy the current node –>
<xsl:copy>
<!– Including any attributes it has and any child nodes –>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
After an application is successfully submitted, it is useful to send an e-mail acknowledgement. Since this depends upon a successful database insertion, you can use an INSERT TRIGGER command to generate not only a notification to the user but also a copy to the administrator to act upon. The Oracle database provides the UTL_SMTP package, which can be used to write PL/SQL procedures to send e-mails inside a PL/SQL trigger. The procedure is included in the provided sample code for this chapter. The following is the SQL that can be added to the previously defined users_insert trigger:
sendEmail_pkg.send_email('smtp.foo.com',
'xdkadmin@foo.com', 'XDK Product Management<xdkadmin@foo.com>',
:new.email,
'John.Smith@foo.com', null, 'XDK.US Registration',
'Thanks for your registration. We will review the request and
get back to you soon.'); Once the administrator receives the mail, there must be a way to approve the user’s application. This is done by creating a new form that is populated by the inserted data. This will be described in the next section.
