%TOC% ---+++ 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 Stratego.StrategoNetworking. There is one problem: the Java implementation of the Stratego.ATermLibrary doesn't provide a reader for BAF, the [[Binary Stratego.ATerm Format]]. Because of this format is very efficient, it is used by Stratego.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: 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 [[http://ant.apache.org][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 Stratego.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 [[http://www.postgresql.org/][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 [[http://java.sun.com/j2se/][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 [[http://jakarta.apache.org/tomcat/][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 [[http://industry.java.sun.com/products/jdbc/drivers][JDBC3 driver for your DBMS]], in this case the [[http://jdbc.postgresql.org/][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=: factory org.postgresql.jdbc3.Jdbc3ObjectFactory databaseName..... serverName127.0.0.1 user.... password..... 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 Stratego.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 Stratego.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) -- Main.MartinBravenboer - 10 Mar 2003