IDRS Tutorial
The aim of this tutorial is to explain how to setup and implement the IDRS using a sample setup. This tutorial will cover the steps necessary to add the IDRS to a servlet engine and how to connect it to a database. This tutorial DOES NOT cover setting up of a web server, jdk, rdbms or any other component of a web site infrastructure. These are what needs to be setup BEFORE you begin installing the IDRS:
For information as to which combinations of the above systems
that have
been certified to worth with the IDRS, please visit the
IDRS Compatibility
Page.
The IDRS is made up of these packages
Number | Class Name | Description |
1 | net.sourceforge.idrs.core |
Contains the core pieces of the IDRS including the RML parser and
the servlet |
2 | net.sourceforge.idrs.utils |
Contains the classes that include database access and calling object
dynamicly |
3 | net.sourceforge.idrs.utils.pool |
Used as the base for all pools in the IDRS |
4 | net.sourceforge.idrs.jdbc |
Includes classes for a database pool |
5 |
net.sourceforge.idrs.script |
Classes for scripting the IDRS |
6 |
net.sourceforge.idrs.embedable |
Classes for embedding scripts into RML
reports |
Either the classes or idrs.jar now need to be inserted into a web application directory structure. The location of this structure is defined by the servlet executor, so consult your documentation. The web application tree is as follows
In this tree the {Web App Directory} is where the web application will be. This directory will include any static web pages or JSP pages that relate to the web application. The WEB-INF directory holds the web application's configuration file called web.xml, this document will be explained in the next section. The final directory is the classes directory, this is where the IDRS class files described above will go.
WEB.XML
The web.xml file is an xml file that tells the servlet runner where the web server can find the necessary classes for this web application and what initialization arguments are given to the servlet on startup and restart. The parameters are defined and explained by the IDRS Initialization Arguments page that is part of the IDRS Documentation Site. This document explains every piece of the IDRS' web.xml file and gives a sample web.xml file. A sample web.xml file is also included in the download of the IDRS.
IDRS DATA MODEL
The IDRS data model facilitates a dynamic model for report generation. The model is explained in the IDRS Data Model as part of the IDRS Documentation. to create the data model simply execute the statements in the IDRS Data Model. If you are running SQL Server you must change the type for the IsFile field in the IDRS' tblDoc table from boolean to int. Once the model is created the next step is to add users and groups to the model. Users may belong to multiple groups as explained in the IDRS Data Model, and reports may allow multiple groups to access the report.
CREATING A REPORT
Now we will create a sample report. The data for this report
will just
happen to sit in a PostgreSQL and all examples will
pertain to PostgreSQL,
but the type of database that is used
doesn't matter. When finished our
report will look like this :
This is my first IDRS Generated Report
John's Time Card
Hourly Rate : $25.00 / hour
Line Number | Date | Time In | Time Out |
1 | 22-May-2000 | 10:00 AM | 3:00 PM |
2 | 23-May-2000 | 11:30 AM | 2:00 PM |
Jack's Time Card
Hourly Rate : $10.00 / hour
Line Number | Date | Time In | Time Out |
3 | 22-May-2000 | 8:30 AM | 5:00 PM |
4 | 23-May-2000 | 8:00 AM | 5:30 PM |
Jen's Time Card
Hourly Rate: $8.50 / hour
Line Number | Date | Time In | Time Out |
5 | 22-May-2000 | 8:30 AM | 4:30 PM |
6 | 23-May-2000 | 8:22 AM | 3:00 PM |
John is the boss and you always listen to him/her.
The Data that supplies this report is in the employee database and contains the emp table :
CREATE TABLE emp (
ID int,
name varchar(10),
date date,
timein time,
timeout time,
payrate float);
id | name | date | timein | timeout | payrate |
1 | John | 22-May-2002 | 10:00 AM | 3:00 PM | 25.00 |
1 | John | 23-May-2002 | 11:30 AM | 2:00 PM | 25.00 |
2 | Jack | 22-May-2000 | 8:30 AM | 5:00 PM | 10.00 |
2 | Jack | 23-May-2000 | 8:00 AM | 5:30 PM | 10.00 |
3 | Jen | 22-May-2000 | 8:30 AM | 4:30 PM | 8.50 |
3 | Jen | 23-May-2000 | 8:22 AM | 3:00 PM | 8.50 |
This table is not a good example of a relational database, but for our purposes it will do just fine.
The final product has almost all of the pieces of the IDRS. It has tables that break when a data field changes, repeating colors in tables and it uses two datasets. While having two db's may seem like over kill, it will display how to use multiple DBs.
You can download the srource for this report by clicking here.
First we will look at how an RML template is structured. There are two parts
to an RML document, the head and the
body. They are defined by the <HEAD></HEAD>and
<BODY></BODY>tags
just as in HTML. All of the
information that defines the datasets to be
used is placed in the
head of the document and the placement of data is
defined in the
body of the document. First, lets look at the head of the
document:
<rnl>
<ishtml>true</ishtml>
<HEAD>
<DB ID="emp">
<DBDriver>dbType.Driver</DBDriver>
<UserName>admin</UserName>
<Password>asdf123</Password>
<DBName>jdbc:dbType:employee</DBName>
<SQL>
<SRC>
SELECT * FROM emp;
</SRC>
</SQL>
</DB>
<DB ID="boss">
<useDB>emp</useDB>
<SQL>
<SRC>
SELECT name FROM emp
WHERE id = ?;
</SRC>
<varType>int</varType>
</SQL>
</DB>
</HEAD>
Preceding the <head>section there is an <rml>tag followed by an <ishtml>tag. The <rml>tag tells the IDRS that there is RML in the page. The <ishtml>tag tells the IDRS wether or not to print the <head>and <body>tags.
This head section defines two <DB>tags. The frist DB has "emp" for an ID. The ID attribute is used to identify each separate dataset. This ID is used when referencing and inserting data into the body of the RML template. The next line tells the IDRS what driver to use to connect to the database where the the data is stored. Check your Database and JDBC Driver documentation for details on what this line should be. The next two lines tells the IDRS what username and password should be used to connect to the DB. These lines must be placed BEFORE the <DBName>tag, which appears next. This line has the JDBC connection string that will be used to connect to the DB. Again, check your documentation for correct syntax for this line.
The next line is the beginning of a SQL statement. There are two parts to an SQL statement, the source and the declaration of variable types. The first part of the SQL block is required, the second may not be. The actual SQL statement to be used is placed here. If there are no arguments to be used in the statement, then there is no need to declare what types are used. The rest of the DB block is simply closing tags.
The next DB block is both simpler and more complicated. The block starts out the same, but this time the ID is 'boss'. The next line, <useDB>, tells the IDRS to share that database connection created by the first DB block, 'emp'. This is preferable to creating an new connection because it is faster and saves on system recourses.
Next comes an SQL block. First comes the <src>block which is used
to extract the needed data from the
database. In this statement there is
an argument to be used
though. In the WHERE clause of the SQL statement
is a
'?'. This means that the IDRS must be supplied the criteria
to be
used for this statement. This criteria will be
supplied by the form that
calls the IDRS and will be covered more
later. If an there are arguments
in the SQL statement
to be used, their types must be declared in the order
which they
occur in that statement. The types are defined in the RML
specification
under the <varType>tag. The type
declared here is 'int'. This means
that the calling form
must supply an integer for the WHERE clause to use
in this
statement.
<BODY>
This is my first IDRS Generated Report<p>
<repeat id="emp" color1="white"
color2="#808080">
<ifchange field="emp.id">
</table>
<field>emp.name</field>'s Time
Card<p>
Hourly Rate : <field
format="number,currency">emp.payrate</field>/hour<p>
<table>
<tr>
<td><b>Line
Number</b></td>
<td><b>Date</b></td>
<td><b>Time
In</b></td>
<td><b>Time
Out</b></td>
</tr>
</ifchange>
<tr
bgcolor="<field><backcolor></field>">
<td><font
color="<field><forecolor></field>"><field><linenum></field></td>
<td><td><font
color="<field><forecolor></field>"><field
format="date,medium">emp.date</field></td>
<td><font
color="<field><forecolor></field>"><field
format
=
"time,short">emp.timein</field></font></td>
<td><font
color="<field><forecolor></field>"><field
format
=
"time,short">emp.timeout</field></font></td>
</tr>
</repeat>
</table><p>
<field>boss.name</field>is the boss and you should
always listen
to him/her.
</BODY>
</rml>
Now lets take a look at the body of the RML document. The first line is standard HTML. Remember that RML documents output standard HTML. The next line is the beginning of a <repeat>block. <Repeat>tags require at least one parameter, id. This tells the IDRS which db it is iterating through. In this instance we are iterating through the 'emp' data set. The IDRS will process every line inside this <repeat>block for every record in the 'emp' dataset. The next two parameters are optional, but either must both be included or excluded. These colors are used to set the <forecolor>and <backcolor>fields of the IDRS. These special fields alternate between the two specified colors depending what record the IDRS is currently on. This allows for the alternating colors in the resulting tables tat makes the data easier to read.
The next line begins an <ifchange>block. An <ifchange>block is used when you need to only print lines when a certain field in a db changes. In this example we use the <ifchange>to break the last printed table, print employee information and begin a new table. The <ifchange>block has one parameter, field. This parameter is set to the db and field that will be tracked for changes. In this case we are checking the id field of the 'emp' dataset. This is done by setting field="emp.id". The next line is terminates the preceding table. Don't worry because there will be an extra </table>tag, the browser will ignore it. the next few lines print employee specific info that doesn't need to be printed in each line of the record and makes the final report much nicer looking. The key to inserting data into a final report is the <field>tag. This tag is described as part of the RML specification. Simply put the name of the dataset to be used, a period and then the name of the field to be used is placed between field tags. The next <field>tag contains a 'format' parameter. This parameter is used when formatting the output from the IDRS. This is further explained in the RML specification.
The following lines are all similar. These lines are processed ONLY when the emp.id field is changed.
The rest of the <repeat>block is processed for every record in the 'emp' dataset. Look closely at the line immediately following the end of the <ifchange>block. It has a special value inside the <field></field>block. The value is surrounded by a '<' &'>'. This means that the field to be used is an IDRS field. The one that is used is the <backcolor>field. This tells the IDRS to print the current back color as defined in the <repeat>tag. The next line shows the <linenum>field. This field prints the current record number the IDRS is processing. The rest of the <repeat>block is all very similar and straight forward.
After the <repeat>block the last table is closed off and a new paragraph is begun. There is one final <field>tag, this one uses the 'boss' dataset to retrieve data. This <field>tag is outside of a <repeat>tag. If a field is before a <repeat>tag that it's dataset is used in, the first line of the record is used for output. If it is after it's corresponding <repeat>block, the last record is used. There doesn't have to be a <repeat>block for any given dataset.
Now that the RML document is constructed, it must be deployed within the IDRS database. For our purposes the IDRS tblGroups and tblUsers tables will look like this:
tblGroups
GroupID | GroupName |
1 | Boss |
2 | Employee |
tblUsers
UserID | UserName | Password | Groups |
1 | John | asd12 | 1,2, |
2 | Jen | def34 | 2, |
3 | Jack | qwe87 | 2, |
Lets look at this security model. There are two groups, Boss and Employee. For users, John is both a Boss and an Employee, this makes sense, while Jen and Jack are just Employees.
Now that we have our security model setup, it's tie to figure
out what
information is needed to deploy our RML document. This
is the information
needed to deploy our RML document:
DocID | DocName | DocSrc | DocVarSrc | DocParams | DocGroups | isFile | DocConns |
The number of our RML Document | The name of our RML Document | Either the filename of or the actuall source the RML Document | The source of the input form needed for the RML Document | The names of the input fields from our input form i the order they appear in the RML document | The groups allowed to view this document | wether or not the contents of DocSrc is a file | the names of DB connections needed by the IDRS |
We will go through each of these parts and show what data is needed for each field.
DocID: This is a unique number used to identify the RML document.
DocName: A unique name used to identify the RML document
DocSrc: This is either the source or the filename of the RML document. The source should be used for maintenance reasons, in our example we will be using the source.
DocVarSrc: This is the source for the input form that
will suply our
RML document with the data it needs. If we
look at our RML document's <HEAD>block,
we see that our
'boss' <DB>block is the only one that requires a
parameter.
To tell the IDRS what information it needs, we
need to us a special name
system. That system is as follows
DBName_inputName. In this case we will
name our only input
box boss_BossID because have to use the DB's name followed
by an
underscore and a descriptive name. we also need to include
the submit
button and reset buttons. We don't include the
<form>tags that define
how to handle the data. The
source will look like this, you can download
it by clicking here:
Enter The Boss's ID : <input type="text"
name="boss_BossID"><br>
<input type="submit"><input
type="reset">
Type this information into your favorite text editor and save it to the same directory as our sample RML document.
DocParams: This field is used to tell the IDRS the names of
the input
tags used in the DocVarSrc field. You must name
each input tag used in
the DocVarSrc field in order which they
are required in the RML document.
The submit and reset
buttons are not included in this list. The field looks
like
this:
boss_BossID:
We used only one parameter, boss_BossID. This list is
seperate and terminate
by colons.
DocGroups: This is a list of the groups that will have
access to the
RML document separated and terminated by
commas. In this case we wouldn't
want all of the employees
to see the other employee's data, so we'll only
allow the boss to
look at this report. This is what the field will look
like:
1,
isFile: This field tells the IDRS wether or not the contents of DocSrc is a file name. The value for this will be false.
DocConns: This field tells the IDRS what DB connections
are used by the
RML document. This field contains the
information in the <DBName>tag
of each <DB>block. If a connection is shared by two <DB>block,
it
is only mentioned once. This field is separated and
terminated by colons
and will look like this:
jdbc:dbType:employee,
Remember to consult your db and jdbc driver documentation for how to connect to the DB.
Now that we have decided what information we need, we have to
enter it
into the IDRS database. We can do this with a
utility called InsertToIDRS.
This utility allows us to
insert all of the data we need into the tblDoc
table of the
IDRS. It's a command line utility written in java.
The use
of this utility is simple. It first requires the
driver, username, password,
and name of the idrs db. Next
comes the option string. This string starts
with a '-' and
continues with several characters that tell the utility what
data
to insert and where. By typing 'java InsertToIDRS -h' you
can see
a full description of these arguments. There is one
argument character
that might be confusing, The 'u'
argument option means that it will update
a current record in the
IDRS db, not including this character means that
a new record
will be created. If this character is used then either the
'i'
option or the 'n' option and it's corresponding values must
be given to
tell the utility what record should be updated.
We will use every argument
except 'u' in this command.
java -insgvfpc jdbcdriver.Driver jdbc:db:idrs user pass 1 TestDoc
/IDRSSrc/IDRSSample.rml
1, /IDRSSrc/IDRSSampleParams.htm
boss_BosID: false jdbc:dbType:employee,
You'll notice that all of these parameters were what we went over
in the
section prior to this. This will insert an IDRS
document into the tblDoc
table.
There is just one more step until We can look at our sample report. We need to change the web.xml file to reflect the new report we just created by adding a database to be pooled. This is a simple and straight forward process, review the IDRS Initialization Arguments web page in the IDRS documentation. change the <numdbs>to 1 and fill in all of the db information needed.
Once the servlet runner and the web server are started, you can go ahead and access the report we created.
To do this, fire up your web browser and point it to the
IDRSParams servlet
like this:
http://www.yoursever.com/IDRS/IDRSParams?docID=1
This will bring up a form that we give the username, password and
the boss'
ID. If we enter john and asdf123 for the username
and password and 1 for
the ID, then you should see the same page
as shown in the sample above.
If you type in the above web
address again you will see a difference, there
is no username or
password. For as long as the browser is open the IDRS
will
not ask or accept any other usernames and passwords.
Congratulations! You have just created and deployed your first IDRS Report! There is much more that can be done in RML, but this was a good beginning.