Extracting and presenting data from a relational database is one of the most common tasks in web development. The usual approach splits this task into several tiers:
While powerful and generic, this approach is quite heavyweight. HTSQL and HTRAF radically simplify it by eliminating the middleware tier; instead you embed the data from the database directly to an HTML page.
Take the following use case: allow a user to select a school from a drop-down list, then, for the selected school, display associated departments together with the number of courses offered by each department.
Note
In all examples below, we use a sample database used for HTSQL regression testing. The database contains schools, programs administered by a school, departments associated with a school, and courses offered by a department.
This is how to implement this use case with HTRAF:
<select id="school_code"
data-htsql="/school{code, name}
?exists(department)">
</select>
<table
data-htsql="/department{name, count(course)}
?school.code=$school_code"
data-ref="school_code">
</table>
This HTML fragment contains two elements: <select> and <table> which display a drop-down list of schools and a list of associated departments respectively. The elements (we call them widgets) are empty, but have some extra attributes.
The data-htsql attribute contains an HTSQL query; it instructs HTRAF to execute the query and use the result to populate the content of the widget. Take a look at the output of the query:
| code | name |
|---|---|
| art | School of Art & Design |
| bus | School of Business |
| edu | College of Education |
| eng | School of Engineering |
HTRAF renders this output into the following HTML code:
<select id="school_code">
<option value="art">School of Art and Design</option>
<option value="bus">School of Business</option>
<option value="edu">College of Education</option>
<option value="eng">School of Engineering</option>
...
</select>
The <select> widget is also assigned an anchor school_code, which allows us to refer to it from other widgets.
The <table> widget is more interesting as it must refresh each time the user selects a school from the drop-down list. To indicate this dependency, we add data-ref="school_code" attribute. Now the widget will be updated any time the school_code element signals a change. The value of the selected row is available in the HTSQL query under the name $school_code.
For example, if the user selects School of Engineering in the drop-down list, then to update the linked table, HTRAF will execute the query:
| name | count(course) |
|---|---|
| Bioengineering | 17 |
| Computer Science | 21 |
| Electrical Engineering | 14 |
| Mechanical Engineering | 19 |
For more information on HTSQL, see http://htsql.org/; this document describes how to use HTRAF toolkit to embed results of HTSQL queries into HTML pages.
HTRAF uses an HTSQL service to retrieve data from a relational database; therefore, in order to use HTRAF, you need to install HTSQL and deploy it as a web service against your database. See HTSQL Installation Guide for more details.
It is strongly recommended to configure the HTTP server to serve both the HTSQL service and HTML pages from the same domain; otherwise browser security settings would prevent HTRAF from accessing HTSQL service. That could be circumvented by using Cross-Origin Resource Sharing (CORS) on the HTSQL service, but note that not all browsers support CORS.
The latest HTRAF package is available at http://dist.htsql.org/source/HTRAF-latest.zip. Unpack the archive and copy the content of htraf directory to where you keep static data for your HTML pages.
HTRAF is a pure-Javascript framework that depends on JQuery and a number of JQuery plugins. The HTRAF package includes all the dependencies so you don’t need to install them separately.
You could also download HTRAF directly from the source repository:
$ hg clone http://bitbucket.org/prometheus/htraf
The source repository does not include any dependencies. To build a packaged version of HTRAF, go to the htraf directory and type:
$ make
The generated package will be placed into build directory.
To start using HTRAF, include the script htraf.js to your HTML pages:
<script type="text/javascript"
src="/htraf/htraf.js"
data-htsql-version="2"
data-htsql-prefix="/@demo">
</script>
To include htraf.js, we use the regular <script> element, but with two non-standard attributes:
This specifies the root of the HTSQL service; in the example above, HTSQL service is located at http://htsql.org/@demo or https://demo.htsql.org.
Note that the URL should not include a trailing slash.
HTML elements controlled by HTRAF are called widgets. HTRAF supports a number of widgets: drop-down and regular lists, tables, charts, and also prodives an API for adding new widget types.
HTRAF recognizes widgets by presense of attribute data-htsql. This attribute specifies an HTSQL query used to populate the content of the widget.
The type of the widget is specified by attribute data-widget; when the attribute is not set, the type is determined by the element tag.
Widgets are controlled and populated automatically by HTRAF. HTRAF takes over the widget content, preserving any styles and attibutes of the element itself, but replacing the content of the element with generated data.
Some widgets (in particular, lists and tables) allow the user to select a row from the list. It is possible to bind two widgets together so that selecting a row in one widget updates the content in the other.
To establish a link between two widgets, assign attribute data-ref to the dependent widget — it should contain the id of the parent widget. When the HTSQL query of the dependent widget is evaluated, the selected value of the parent widget is passed to the query as a reference.
In addition to existing classes, HTRAF automatically assigns some custom CSS classes to controlled elements:
Assigned to a selectable row when the mouse hovers over it. The default style associated with this class is:
background: #888888;
Assigned to the currently selected row. The default style associated with this class is:
background: #DDDDDD;
HTRAF provides no default styling for widgets, but demo/css directory contains several sample stylesheets which could be used as a starting point.
<h3>Select a School</h3>
<select id="school"
data-htsql="/school{code, name}"></select>
<div style="width: 500px; height: 350px;"
data-htsql="/program{title, count(student)}
?school.code=$school
&exists(student)"
data-ref="school"
data-widget="chart"
data-type="pie"
data-title="Percent of Students by Program">
</div>
<h3>Departments</h3>
<p>Filter by name: <input id="department_name"/></p>
<table id="department"
data-htsql="/department{code, name}
?school.code=$school
&name~$department_name"
data-ref="school department_name"
data-hide-column-0="true">
</table>
<p>
The selected department:
<em data-htsql="/department{name}?code=$department"
data-ref="department"></em> <br/>
The number of courses in the selected department:
<strong
data-htsql="/department{count(course)}
?code=$department"
data-ref="department"></strong>
</p>
<h3>Courses</h3>
<table id="course"
data-htsql="/course?department_code=$department"
data-ref="department">
</table>
Filter by name:
The selected department:
The number of courses in the selected department:
More sample dashboards are available at HTSQL Gallery.