VOSGAMES

Currently this software is still in development and a lot of features have not yet been tested. Untested features are PUT and DELETE methods.

mod_okioki

mod_okioki is an Apache module which provides access to your database with a RESTful data service interface.

There is a lot of infrastructure build for webservices, such as load balances, caching/proxy servers, compression, encryption, authentication, auhtorization and logging. By having this Okioki module between the application server and its database as a shim will give you access to all this infrastructure. Okioki was designed to make it really easy to make such a RESTful data service.

The design constraints for this module are:

  • Correctness, i.e. everything is checked and don't allow sql injection.
  • KISS, Keep it simple and stupid, provide as little functionality as possible, but do it well.
  • Performance, the primary reason to make this in C.
  • Simple to use/configure.

Anatomy of a RESTful data service

Database connection

The database connection for mod_okioki is managed by the standard mod_dbd Apache module. mod_dbd manages a pool of database connections, which improves performance compared to one-on-one connections esteblished on each request. It also prepares SQL statements with the database to reduce communication and SQL parsing overhead, it also reduces the change of SQL Injection (FreeTDS requires special precautions, check the mod_dbd documentation).

mod_dbd, through apr-util, can work with these database connections:

  • MySQL,
  • PostgreSQL,
  • SQLite,
  • FreeTDS and
  • ODBC.

In our example we will use the default connection pool settings of mod_dbd and we will be connecting to a PostgreSQL database. We define two SQL statements which qeury the same table, once for an overview and once for detail of a single object. These Apache configuration statements are made at the VirtualHost level.

DBDriver pgsql
DBDParams "host=localhost dbname=tautoru user=tautoru password=xxxxxx"

DBDPrepareSQL "select id, name from otp_algorithm;" sql_test
DBDPrepareSQL "select name from otp_algorithm where id = %hhd;" sql_test_id

Rewrite for RESTful urls

mod_okioki can only extract parameters for SQL statements from the query string or from the POST/PUT data. The extract parameters from a RESTful url we use mod_rewrite.

In our example we rewrite our RESTful url /tautory/test/5 to a simple url with a query string /tautoru/test_id?id=5. These Apache configuration statementes are also made at VirtualHost level, although it may be possible to define these at Location level as well.

RewriteEngine on
RewriteRule ^/tautoru/test/(.*) /tautoru/test_id?id=$1

Executing SQL statments

The last small part is handled by mod_okioki, the actual execution of the SQL statement and the handling of its input and output. Each SQL statement is linked to an url through the use of the OkiokiCommand configuration statement. The OkiokiCommand has the following arguments:

  • The http method to listen to, may be: GET, POST, PUT or DELETE.
  • The part of the url to listen to.
  • The format of the output, maybe be: CSV.
  • A label from mod_dbd to the prepared sql statement.
  • Optional parameters in the order to use them in the prepared statement.

As you see there are two output formats, the first CSV is a comma seperated values format. CSV will output a header with the column names and order as defined in the SELECT part of the SQL statement, followed by rows of data.

In our example we link the two SQL statements to the urls /tautoru/test and /tautoru/test/5 (before it is rewritten). These configuration directives must be declared inside the Location or Directory level.

<Location /tautoru>
    SetHandler okioki-handler
    OkiokiCommand GET /test CSV sql_test
    OkiokiCommand GET /test_id CSV sql_test_id id
</Location>