December 2011 Edition

Using SQL Anywhere as a Generic HTTP Web Server

Since the introduction of SQL Anywhere 10, SQL Anywhere has been able to retrieve a generic file listing from the file system using the newly-introduced Directory Access Remote Server ability. Combining this feature with the ‘RAW SERVICE’ ability, we can turn the database server into a rudimentary web server, to host generic text/binary files off a file system (similar to other web servers that customers are used to, like IIS/Apache).
Here is a quick example on how to set such system up.

1. Create and start SQL Anywhere database with a web server listening port 80

Assuming SQL Anywhere 12 has been installed from the command prompt execute:

dbinit webserver.db
dbsrv12 -xs http(port=80) webserver.db

2. Set up the file service

Connect to the webserver database with dbisql e.g.:

dbisql –c “uid=dba;pwd=sql;Servername=webserver”

and execute:

--
-- Create File Service
--
CREATE SERVER www_filehost
CLASS 'directory'
USING 'root=c:\\inetpub\\wwwroot;SUBDIRS=10';
CREATE EXTERNLOGIN DBA TO www_filehost;
CREATE EXISTING TABLE www_files AT 'www_filehost;;;.';

In this command, we use 'c:\inetpub\wwwroot' as our main web directory (the default IIS path), and we allow 10 levels of sub-directory indexing as well.

3. Set up the Web (RAW) service

Continue using dbisql and create a new web service with:

--
-- Create WWW Service
--
CREATE SERVICE root
TYPE 'RAW'
AUTHORIZATION OFF
SECURE OFF
URL ON
USER DBA
AS CALL www_get_page(:url);

Note: a 'simple' security scheme will be used for this test - if advanced security is required, it is possible to be defined here.

4. Set Up the supported MIME types

When a browser requests a resource from a web server, it has no expectation on what the format of the response should be (e.g. the request for an HTML document looks exactly the same as a request for an image). Unfortunately, the browser needs a "hint" when it is reading the response data to help it figure out what kind of binary file was returned. This behaviour is controlled by the Content-Type HTML header which is set by the web server and given a value of a registered media type.

When setting up a SQL Anywhere RAW service, all files binary and text will be returned with a generic content-type header - this means that any binary file sent via this service without an appropriate "Content-Type" HTML header will instead always be displayed as text.

SQL Anywhere supports changing the Content-Type header value via sa_set_http_header() - however, we still need a mechanism to detect what kind of file we're about to return to the client and set the Content-Type header's value appropriately. We can do this fairly easily by setting up a 'mapping table' of file extensions to media types, using the existing lists that are already published for other products (e.g. Apache).

Here is the latest MIME mapping for Apache servers: http://svn.apache.org/repos/asf/httpd/httpd/trunk/docs/conf/mime.types

To import the mime types into SQL Anywhere, you can just copy mine.types from Apache site into a file and then using simple text editor search/replace all double tabs with single tabs until there is no more double tabs. This is needed for importing this data into a database table.

(on Unix or Linux this can be simply done with sed e.g.:
sed -e "s/\t\t\t\t\t/\t/g" -e "s/\t\t\t\t/\t/g" -e "s/\t\t\t/\t/g" -e "s/\t\t/\t/g" mime.types > mime.types2 )

Then from dbisql create www_mine_types table and load the data:

CREATE TABLE www_mime_types (
mimetype VARCHAR(256) UNIQUE,
extensions VARCHAR(1024)
);

LOAD TABLE www_mime_types (mimetype,extensions)
FROM 'mime.types2'
DELIMITED BY '\x09'
COMMENTS INTRODUCED BY '#';

Next, create a stored procedure to return pages and set HTTP headers

CREATE OR REPLACE PROCEDURE www_get_page( @url LONG VARCHAR )
BEGIN
DECLARE @url_file_suffix LONG VARCHAR;
DECLARE @url_suffix LONG VARCHAR;
DECLARE @mime_target VARCHAR(256);
SET @url_file_suffix = REGEXP_SUBSTR(@url,'\..+$'); -- Extract extension
SET @url_suffix = RIGHT(@url_file_suffix, LENGTH(@url_file_suffix) - 1);
-- Exact match
SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions = @url_suffix;
IF @mime_target IS NULL THEN
-- First match
SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions LIKE @url_suffix || ' %';
IF @mime_target IS NULL THEN
-- Middle match
SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions LIKE '% ' || @url_suffix || ' %';
IF @mime_target IS NULL THEN
-- End match
SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions LIKE '% ' || @url_suffix;
IF @mime_target IS NULL THEN
SET @mime_target = 'text/html'; -- Default
END IF;
END IF;
END IF;
END IF;
CALL sa_set_http_header( 'Content-Type', @mime_target );
-- Cast binary data as LONG VARCHAR in 'text' situations, otherwise return LONG BINARY
IF ( LEFT(@mime_target, 4) = 'text' ) THEN
SELECT CAST(contents AS LONG VARCHAR) FROM www_files WHERE file_name = @url;
ELSE
SELECT contents FROM www_files WHERE file_name = @url;
END IF;
END;

The stored procedure is basically trying extract out the file extension of the web request, look up that value in the www_mime_types table, and then set that value as the 'Content-Type' field. It then grabs the file off of the file server and returns it to the HTTP client.

5. Test your new webserver

You should now be able to test your web client.
Create a simple hello.htm file :

<html>
<body bgcolor = "#ffffcc" text = "#000000" align = center>
<h1>Using SQL Anywhere as a simple webserver</h1>
</body>
</html>
Copy hello.htm to your root directory (c:\\inetpub\\wwwroot) defined in your file service, and from any browser test your page with :
http://localhost:80/hello.htm