DALgate -- a CGI program for WebSTAR and Butler

Michael BJÖRN
University of Tsukuba
Doctoral Program of Socio-Economic Planning
1-1-1 Tennodai, Tsukuba, Ibaraki 305, JAPAN
Phone +81 298 53 5424, Fax +81 298 53 5070
E-mail:

Figure 1.1. Static and dynamic HTML

2. Installation

You have to make settings in a few different software components, so we'll take them one at a time.

DALgate

Just drop it in the same folder as WebSTAR, or in one of WebSTARs subfolders. In the installation shown here, DALgate was put in the same folder as WebSTAR. You can put login.html anywhere you want, but it makes sense to put it together with WebSTAR.

Butler

On the machine running WebSTAR, use ButlerHosts and add a host which specifies the path from the WebSTAR folder to DALgate. Since we have put DALgate in the same folder as WebSTAR, we add a host as follows:

Figure 2.1. A host which specifies the path to DALgate.

Edit the host so that it points to your Butler system, as follows:

Figure 2.2. The host points to Butler on a server called CyberBase, using program linking.

On the machine running Butler, you must first of all start program linking in the Sharing Setup control panel.

Then, you must set the access privileges for your users, by using Butler Tools. First you choose Show Access from the Access menu, and add a user by choosing New User from the same menu. We will add a usser with name anonymous and password anonymous, who has the minimum access privileges for using DALgate. If you want your users to have full access, you must give them higher access privileges than we show here. We set up our anonymous user as follows:

Figure 2.3. An anonymous user with password anonymous.

Note that even our anonymous user can create tables!!

We then mark the user and give him full access privileges for the database we want him/her to access:

Figure 2.4. User with full access privileges to the database company.

Note that you always should give full access privileges as default!!

But since we actually do not want the anonymous user to have full access, we then select the database (in this case company) and limit access to existing tables to Protected and Select, as follows:

Figure 2.5. User with limited access privileges to the tables of database company.

Now, add one more user yourself, this time with no access privilege limitations, and continue to the next section.

3. A simple example

Let us take a look at a simple example where a we creates a table called suppliers in the database company, and then populates the table with data.

Figure 3.1. We choose a database (company) and an action (Insert a table). Figure 3.2. We fill out a table definition form.

In figures 3.1 and 3.2 the first HTTP transaction is used for providing data dictionary information, and the second transaction is used for inserting the user specified table. When we click Insert Table, a result message (not shown) is returned.

Next, we might want to populate the table with some data. We select Insert values in figure 3.1, and press the Prepare Transaction button again, whereupon we are presented with the following screens:

Fig. 3.3. We choose a table (suppliers) to insert values into. Fig. 3.4. We insert data values (5090, Acme Suppliers Co., Littletown).

Again, the first HTTP transaction is used for providing data dictionary information. The second transaction is used for adding the data that the user has entered in figure 3.4 to the database. When Insert Values is clicked, a result message (not shown) is returned.

Then, we want to query the table that we have just constructed. The user selects Query in figure 3.1, and clicks the Prepare Transaction button yet again, whereupon he/she is presented with the screen in figure 3.5.

As in the two previous cases, the first HTTP transaction is used for providing data dictionary information so that we can build the query from existing tables and columns by choosing them in pop-up menus or selecting in scrolling list panes. We also select match conditions and matching values in the same screen. The second transaction executes the query and presents us with the results:

Figure 3.5. We make a query and joins the suppliers and customers tables. Figure 3.6. The query results are returned.

As can be seen, we can do fairly much within the confines of a single HTTP transaction. However, no interaction with the user is allowed. Let's say that we would like to use the result of the above query to further query the database. This is exactly what we will discuss in the next section.

4. A little more interesting example

DALgate maintains a connection with the database system for each user, so that the user does not need to provide login information for every single HTTP transaction. When the user first logs in to the system, a unique session number is generated. The connection between the server and the browser is broken several times, but each time it is reestablished, the session number allows the relational gateway to verify the user. The gateway each time controls that the session number was resent from the same IP address, so that some security is achieved even when no encryption is used. Neither user name nor password is passed after login, so there is no additional security risk involved.

At login, a private table is created which preserves state information for the user. We call this table a VIEW since it will invariably contain derived data: If the user updates the database (by inserting or deleting tables or data) the global state of the database is changed and thus state is preserved anyway. The private VIEWs are transitional and are automatically deleted when the user logs out from the database or if he/she does not contact the database again within a preset time period (typically 10 minutes).

Since VIEWs contain derived data, their most common use is to query the database based on results from previous results (as shown below) but VIEW data can equally well be used for conditionally inserting data into or deleting data from the database.

In a database system which preserves user specific state information, we can allow the user to repeatedly query the database using intermediate results or combinations of intermediate results with other parts of the database. This use of repeated queries is an attempt to somewhat capture the power of nested SELECTs.

Each time the user queries the database, a personal view is saved in the database. To use results of a previous query in a new query, we can simply join the VIEW table with other tables of the database using SELECT statements. To illustrate, we continue the example from section 3.

In section 3, we created a new table called suppliers, which has the columns supp_num, supp_name and supp_city. We then inserted a single tuple and made a SELECT. We thus have a personal VIEW, which can be accessed from all menus listing the tables of the current database. We directly continue the example and perform the following query:

Figure 4.1. Query involving a join with a VIEW table. Figure 4.2. Results of the query, linking previous results to tables in the database.

We select the salesrep from the table orders, and join it with supp_name and cust_name from the VIEW that was generated in section 3, where orders.cust_nr and VIEW.supp_num are equal. When we click Do Query in figure 4.1 the VIEW is updated and the results (in this case that the supplier Acme and the customer Nicolo have been involved in two orders together, with two different sales representatives) are automatically displayed on-screen. We can now in figure 4.2 choose to continue working with the database, or log off, since DALgate is still maintaining an open connection with the database for us.