ATerm Database Interface

Description

The SATerm Database Interface^? (aterm-dbi) offers an ATermServices look on a relational database. aterm-dbi uses Java Stratego.ATerm Servlets.

Interface

Currently there is just a query service. It is accessible on the URL aterm-dbi/query. The query service is an ATermService . It accepts a Query term in the body of a HTTP POST request and returns the result of the query in the body of the reponse.

For example this query:

  Query("SELECT ID, Name FROM Customer")

Might result in this ResultSet :

  ResultSet(
    MetaData(
      Columns(
        [ Column(Name("id"),   Type(Std("INTEGER"), Db("int4")))
        , Column(Name("name"), Type(Std("CHAR"),    Db("bpchar")))
        ]
      )
    )
  , Data(
     [ Row([160, "Meindert Kroese"])
     , Row([159, "Steven van Dijk"])
     , Row([158, "Martijn Schrage"])
     , Row([157, "Sandor Spruit"  ])
     ]
    )
  )

The Stratego.ATerm Database Interface rewrites values of SQL types to an Stratego.ATerm representation. The current implementation can just handle integer types (INTEGER, SMALLINT, and BIGINT) and string types (CHAR, VARCHAR, and LONGVARCHAR). This will be extended in the future to handle all SQL data types.

Invocation

From Stratego you can invoke the services of aterm-dbi with the http-transform strategy of StrategoNetworking.

There is one problem: the Java implementation of the ATermLibrary doesn't provide a reader for BAF, the Binary Stratego.ATerm Format^?. Because of this format is very efficient, it is used by XTC by default. You should thus invoke the services of aterm-dbi in TAF, the Textual Stratego.ATerm Format^?. You could do this with this strategy:

  http-text-transform(service-url) =
    xtc-temp-files(
      write-to-text
    ; xtc-http-transform(service-url)
    ; read-from
    )

Invocation of the query service of aterm-dbi:

  <http-text-transform(!URL("http://127.0.0.1:8080/aterm-dbi/query"))>
      Query("SELECT ID, Name FROM Customer")

Download

You can download the latest release at:

• http://losser.st-lab.cs.uu.nl/~mbravenb/software/aterm-dbi/

The war file is a Java Web Archive, a binary distribution. You can immediately put this file in your servlet container. The .tar.gz files are source distributions. You need Ant to build the sources.

You can also get the latest sources from the Subversion repository:

  svn checkout %SVNSTRATEGOXT%/trunk/experimental/aterm-dbi

See latest sources^? for more information on how to checkout packages from the StrategoXT subversion repository.

In the samples package of Stratego Networking^? you can find some samples in the xmpl/aterm-dbi directory (unreleased, use the Subversion repository).

Implementation

aterm-dbi is a J2EE application. It uses a JDBC DataSource that is made available by the application server it is hosted in via the JNDI. Advantages of this approach:

• by using techniques developed for database servers that should be able to handle a lot of requests, the Stratego.ATerm Database Interface scales very well.

• You don't need to modify any Java code to use a different database managament system, or one at a different location.

Installation

You should be able to run the Stratego.ATerm database interface on any J2EE Application Server and using any database system implementation (as long as there is a JDBC 3 driver available).

I will provide instructions for a configuration where I'm using Tomcat 5 and PostgreSQL. Please consult the documentation of the application and database server if you choose other software.

Basic applications

First of all you need a PostgreSQL database. You need version 7.2 or 7.3. Please refer to the documentation of PostgreSQL if you need to install the database system by yourself.

You need a Java 2 SDK to run Tomcat. It is wise to use a recent release (version 1.4.x). Make sure the environment variable JAVA_HOME points to the location where you installed your J2SDK.

Next, you need Tomcat 5. Tomcat 4 will not do the job! Installation of Tomcat 5 is very straightforward nowadays.

Configuration and extension

Now the basic components of Java based database application are available, now comes the Stratego.ATerm database interface specific part.

You need a JDBC3 driver for your DBMS, in this case the JDBC3 driver for PostgreSQL. Put it in the common/lib directory of your Tomcat installation.

Now it's finally time to install the web archive (.war) of aterm-dbi. You should put this war in the webapps directory of Tomcat.

We still have to configure Tomcat with the database you want to use. This is application server and database specific, so you will probably need to consult some other documentation.

In Tomcat you should add this to the Host element with the name attribute localhost:

  <Context path="/aterm-dbi" docBase="aterm-dbi.war" debug="5" reloadable="true">
    <Logger className="org.apache.catalina.logger.FileLogger"
               prefix="aterm-dbi-log."
               suffix=".txt"
            timestamp="true"/>

    <Resource name="jdbc/aterm-dbi"
              auth="Container"
              type="org.postgresql.jdbc3.Jdbc3PoolingDataSource"/>

    <ResourceParams name="jdbc/aterm-dbi">
      <parameter>
        <name>factory</name>
        <value>org.postgresql.jdbc3.Jdbc3ObjectFactory</value>
      </parameter>

      <parameter>
         <name>databaseName</name><value>.....</value>
      </parameter>

      <parameter>
         <name>serverName</name><value>127.0.0.1</value>
      </parameter>

      <parameter>
        <name>user</name><value>....</value>
      </parameter>

      <parameter>
       <name>password</name><value>.....</value>
      </parameter>
    </ResourceParams>
  </Context>

Of course you should fill in your database-, server-, and username and your password.

We've chosen the PoolingDataSource implementation of the PostgreSQL JDBC driver, but if your application server offers a connection pooling implementation which interfaces with a ConnectionPoolDataSource, you should choose that option.

Todo or might do

• Implement services for inserts, updates and deletes. Maybe use terms instead of concrete syntax in some cases.

• Convert more SQL data types like bit, boolean, dates etc.

• Conceptually it is much more attractive to send an AST representation of the SQL statements to the ATermService instead of concrete syntax for SQL in an Stratego.ATerm wrapper. This would be an excellent combination with the use of ConcreteSyntax for SQL in Stratego. This would at compile-time ensure syntactical correctness of the SQL statements. There are some problems:
  • Many SQL statements use database specific constructs. The practical use of the Stratego.ATerm Database Interface^? would decrease if we would enforce standard SQL.
  • We need a PrettyPrinter on the server-side, because the database system (well, JDBC) just accepts concrete syntax for SQL.
  • We need a SQL syntax definition (but a starting-point is already available)

-- MartinBravenboer - 10 Mar 2003