HTTP Server
 
Previous Chapter: SQLAPP4 - A Client Application
Next Chapter: Database Maintenance

The HTTP Server is an integrated part of Ovrimos, offering users the option to communicate with databases through any Web browser. Since the HTTP Server doubles as a Web server, all other activities requiring a Web server may be conducted seamlessly through the Ovrimos HTTP Server, at the same time the database applications are running. This way a more powerful working environment is at hand.

Among others, the HTTP server allows users to download BLOB data, using the HTTP protocol. The Ovrimos HTTP server uses the GET method on HTTP 1.0 level.

Since the HTTP protocol is stateless, persistent connections are implemented separately and are supported via a non-intrusive mechanism that does not require user-side cookies. See also Persistent Sessions.
Besides all other, the HTTP Server has an embedded Scheme interpreter and is thus capable of  running Scheme scripts.

A few words about the parameterization of the HTTP server:

The parameters required for proper function of the HTTP server are the following:

General SQL Server parameters:  (See also, Global Parameters)

MIMETYPES

HTTPSHAREDDIR SCHEMEINIT Database specific parameters: (See also, Per-database Parameters)

HTTPPORT

HTTPROOTDIR HTTPTHREADS HTTPSESSIONTIMEOUT SCHEMEHANDLERS Special URL Addresses

Some links appearing on the pages of the HTTP Server have URL addresses that do not correspond to files in the disk. They are Web space addresses, having a specific handler (i.e. sqlcore code or a pertinent script) dedicated to their serving. These special URL addresses are:

/roadmap

It it a graphical tool displaying database information. The handle of this URL is sqlcore code, that displays in a tree-like fashion information about users, tables and views of a database. See also The Roadmap.

/sqlterminal

It is the part of sqlcore code that allows the interaction to a chosen database through a browser window. See also The SQL Terminal.

/console

It is the part of sqlcore code that allows database administrators an easy database activity monitoring. See also The Administrator Console.

/cgi-bin
Used for running various CGI programs. CGI files must be in cgi-bin directory under the database document root. To run sqlapp4 as a CGI ACTION, the application program must be placed under the cgi-bin directory.

 
SQL scripts

SQL script files, i.e. files of SQL commands, may be executed directly from the HTTP Server. The name of the script file is typed in the URL address of the Web browser, the script is executed, and the results appear in the browser, in a form depending on the directives of the SQL script.
The SQL script must be located under the database docs directory. For script execution, the associated database must, of course, be running.
This feature is different from the Script Uploading option of the SQL Terminal because it does not require an SQL Terminal page for script execution. On the other hand, scripts must be placed under the database docs directory, i.e. in a particular place of the server (database) side, while Script Uploading allows execution of scripts that are anywhere the user has access to.
SQL scripts may combine HTML commands (see also the SQL script examples) to format the output. Any formatting possible in HTML is also possible to the script output.
For the form of the output see also the available directives.

Scheme - Script Mechanisms

A Scheme interpreter is embedded in the Ovrimos core. This interpreter allows the execution of Scheme scripts that may be used to produce HTML or other output. Actions, which cannot be performed otherwise, through either SQL or HTML, become possible via Scheme scripts. Scheme is a LISP dialect.
The entire script is a stored procedure, written in script language for HTML. The sqlcore part of the server can support a persistent connection if required.

The SCHEMEHANDLERS parameter for every database is a file where a URL address and a corresponding Scheme script file are written in each line. The HTTP server uses this file to find and execute the appropriate scheme script every time this URL is requested. Optionally, authentication may be required for running a Scheme script, in which case, this piece of information is also written in the file after the URL address and the script path.

The HTTP Server can execute Scheme scripts directly, but these scripts should not contain SQL statements. SQL statements can only be run from Scheme when the script is executed as a URL handler.

The user may define more URL handlers through the SCHEMEHANDLERS file. See also Parameters.

Necessary elements for any script expected to handle a special URL are some environment variables:
*form-elements*  It is a list of symbols, which are the names of the form fields. The form field contents are the values of each symbol.
*request-uri* It is a string showing what the HTTP request was.
*request-method* A string containing one of GET, POST, HEAD etc.

For every HTTP request, the main function of the script is invoked. The elements described above are global values, used when main is run.

Scheme scripts can use Ovrimos through a small and expressive interface. Scripts can open a number of simultaneous Statements, each of which is identified by a small integer handle, assigned by the Server. Scripts run by default under the privileges of the calling user, when in authenticated mode. A procedure is supplied to allow them to authenticate as another user. This is mandatory when running in non-authenticated mode. Since Scheme does not support all SQL types, there is a mapping to Scheme types:

Procedures

All procedures that return results other than boolean, can also return #f if they fail.
sql-login user password
Attempts to authenticate as specified user. Returns boolean.

 
sql-logout
Abandons the SQL connection and frees the resources. Returns boolean.

 
sql-start-session
Allocates a Statement and returns an integer handle for it, or #f.

 
sql-end-session integer-handle
Deallocates the Statement.

 
sql-exec-direct integer-handle string
Compiles and executes the SQL command specified. This must not contain any parameters. Returns boolean.

 
sql-result-columns integer-handle
For select statements, returns a list with the result columns' names. Names missing are represented by '()

 
sql-row-count integer-handle
For update statements, returns the number of rows affected

 
sql-cursor integer-handle direction
Depending on the parameter direction, fetches a row from the result set. For simplicity, there is no provision to specify an offset other than 1, or to fetch rowsets of more than 1 row. The parameter direction can be one of the symbols:
The row is returned as a list of values. Returns #f when not available.
sql-commit
Commits the transaction. This is necessary to make the updates permanent. Returns boolean.

 
sql-rollback
Rolls back the transaction. Returns boolean.

 
 

 
An example of a Scheme script, executing SQL via the HTTP Server follows.
 

SCHEME - SQL Examples:

The following is a Scheme script, which may be run by the HTTP server. The script runs on testbase. An SQL script, called books.sql may be run to generate the necessary data for the running of the example (i.e. a table called books and some sample data).
First time users may click on Create a sample bookstore to run the database filling SQL script. Some warnings, which may be issued, are not to worry the user. Authentication may be required. A valid userid and password should be provided. (i.e. initial userid is admin and initial password is  pegasus).
The URL for running this application is /demo/bookstore. Users are advised to run in a separate browser window (if possible).

Any Scheme script for Ovrimos may be considered as having a double interface: one as an SQL client to store and retrieve data from the database and one to the HTTP Server, i.e. a part that deals with HTTP data collection and HTTP data display.
The program presented here is a simple bookstore application, with a demand-driven main loop. A number of books are stored in a database table called books. Users may search for a book, using part of its title or author name as a keyword. Displayed information about books consists of ISBN, Author, Price and Cover. A check list allows the user to add displayed books to the shopping basket. It is possible to display the contents of the basket as well.
Finally, Filing an order is not implemented. It is left as an exercise to the programmer, since this a good starting point for further exploration of Ovrimos. Users may also extend this script by adding a Delete from basket option.
All Scheme scripts require a main function, called main, which makes use of global arguments *request-uri*, *form-elements* and *request-method*.
When a *form-element* is required, it is extracted from the global element list by the Scheme function member.
The *request-uri* is a string containing the address requested.
Finally the *request-method* refers to the HTTP method requested.
The Scheme example provided here is as modular as possible, clearly separating the HTTP part from the SQL part of the application.

The first part of function definitions (before the main function) are display formatting commands, using mostly HTML tables for formatting data and selection forms.
The main function requests a connection to the database and a Statement handle. If the above actions are successful, the bookstore application is started. As mentioned before, the body of main checks the user request and takes appropriate action. Looking at the code, the user will notice that all *request-uri* (i.e. requests issued) are URL addresses. Scheme functions are defined to handle these URL addresses.

The welcome-page is used to display application information and receive the user's request. The other functions called by main are:
search, which executes an SQL  select statement matching a given keyword, when a search with a keyword is issued, or matching part of the author name when search by author name is indicated,
basket, which displays the shopping basket contents,
add-book, when a book is checked from the book list to be added to the shopping basket and order (left as an exercise to the programmer), where books added to the shopping basket will be inserted into a table orders.

(define (http-header)
     (display "HTTP/1.0 200 OK\n")
     (display "Pragma: no-cache\n")
     (display "Content-type: text/html\n")
     (newline))

(define (http-body-start)
     (display "<html><head><center><img src=\"/ovrimos_small.gif\"><h1>On-line bookstore</h1></center><hr></head><body>"))

(define (http-body-end)
     (display "\n<br><a href=\"/demo/bookstore\">Home</a>")
     (display "&nbsp;<a href=\"/demo/bookstore/basket\">Shopping basket</a>")
     (display "</body></html>"))

(define (display-all . list)
     (for-each display list))

(define (display-in-tr list)
     (display "<tr>")
     (for-each (lambda (x) (display-all (if (number? x) 
                "<td align=right>" "<td>") x "</td>\n")) list)
     (display "</tr>\n"))

(define (display-in-tr-with-checkbox list)
     (display "<tr>")
     (display-all "<td><input type=checkbox name=\"" (car list)  "\"></td>")
     (for-each (lambda (x) (display-all (if (number? x) 
                "<td align=right>" "<td>") x "</td>\n")) list)
     (display "</tr>\n"))

(define *handle* #f)

(define *basket* '())

(define *table-header* "<tr><td>ISBN</td><td>Title</td><td>Author</td><td align=right>Price $</td><td>Cover</td></tr>")

(define *table-header-chk* "<tr><td>Select</td><td>ISBN</td><td>Title</td><td>Author</td><td align=right>Price $</td><td>Cover</td></tr>")

(define (main)
     (http-header)
     (http-body-start)
     (unless *handle*
          (sql-login "admin" "pegasus")
          (set! *handle* (sql-start-session)))
     (cond
          ((not *handle*) (display "Authentication failed or license exceeded<br>"))
          ((string=? *request-uri* "/demo/bookstore")
               (welcome-page))
          ((string=? *request-uri* "/demo/bookstore/search")
               (search))
          ((string=? *request-uri* "/demo/bookstore/basket")
               (basket))
          ((string=? *request-uri* "/demo/bookstore/add-book")
               (add-book))
          ((string=? *request-uri* "/demo/bookstore/order")
               (order))
          (else (url-not-found)))
     (http-body-end))

(define (add-missing x y)
     (cond 
          ((null? x) y)
          ((null? y) x)
          (else (if (member (car y) x)
                    (add-missing x (cdr y))
                    (add-missing (cons (car y) x) (cdr y))))))

(define (add-book)
     (set!
          *basket*
          (add-missing *basket*
               (map symbol->string *form-elements*)))
     (basket))

(define (order)
     (display "Not implemented!"))

(define (add-commas lst)
     (cons (string-append "'" (car lst) "'") 
       (append (map (lambda (x) (string-append ",'" x "'")) (cdr lst)) '(")"))))

(define-with-return (basket)
     (if (null? *basket*)
          (begin (display "Your basket is empty<br>\n")
               (return #t)))
     (begin
          (unless
               (sql-exec-direct
                    *handle*
                    (apply string-append
                         (cons "select isbn,title,author,price,image(cover) 
                            from books where isbn in ("
                              (add-commas *basket*))))
               (display "Search failed!<br>\n"))
          (display-all "<h2>Your basket contains:</h2>\n<table border>" *table-header* "\n")
          (define total-amt 0.0)
          (define (loop)
               (let ((row (sql-cursor *handle* 'next)))
                    (if row
                         (begin 
                              (set! total-amt (+ total-amt (list-ref row 3)))
                              (display-in-tr row) 
                              (loop))
                         (display-all 
                              "</table><input type=submit value=\"Add to basket\"><br>\nTotal so far: $"
                              total-amt "\n"))))
          (loop)))

(define (url-not-found)
     (display-all "URL " *request-uri* " not found!\n")
     (http-body-end))

(define (welcome-page)
     (display "<h2>Search for books</h2>\n")
     (display "Constrain search by keyword in title, or author<br>\n")
     (display "<table><tr><td>Keyword</td><td><form method=get action=\"/demo/bookstore/search\"><input type=text name=keyword>\n")
     (display "<input type=submit value=\"Search\"></form></td></tr>\n")
     (display "<tr><td>Author</td><td><form method=get action=\"/demo/bookstore/search\"><input type=text name=author>\n")
     (display "<input type=submit value=\"Search\"></form></td></tr></table>\n"))

(define (search-keyword)
     (generic-search (string-append "where title like '%" keyword "%'")))
(define (search-author)
     (generic-search (string-append "where author like '%" author "%'")))
(define (generic-search where)
     (unless (sql-exec-direct
               *handle*
               (string-append "select isbn,title,author,price,image(cover) from books " where))
          (display "<h2>Search failed!</h2>\n"))
     (display "<h2>Your search results:</h2>\n")
     (display "<form method=get action=\"/demo/bookstore/add-book\">\n")
     (display-all "<table border>\n" *table-header-chk*)
     (define (loop)
          (let ((row (sql-cursor *handle* 'next)))
               (if row
                    (begin (display-in-tr-with-checkbox row) (loop))
                    (display "</table><input type=submit value=\"Add to basket\"></form>\n"))))
     (loop))

(define (search)
     (cond
          ((member 'keyword *form-elements*)
               (search-keyword))
          ((member 'author *form-elements*)
               (search-author))
          (else (display-all "els= " *form-elements*))))
Functions are often recursive (directly or indirectly) since recursion is one of the main features of Scheme (which is a dialect of LISP). Notice recursive function loop where the client sql-cursor function is called with argument 'next' to fetch and display in table form the next row of the result set. Loop is a local function defined both in generic-search and in basket. In both cases, it is run recursively as long as the cursor has something to return.
Search is conducted either on a keyword from the title or on part of the author name. If no keyword or name is specified, then all books must be displayed.

The above is only a very limited example of what may be done using Scheme with Ovrimos. Scheme scripts make the HTTP Server very powerful, since they add the option to have interactive and programmable pages, something neither HTML nor CGI alone can provide.
 
Previous Chapter: SQLAPP4 - A Client Application