apache > cocoon

- Home

# DatabaseReader

NAME | databasereader
WHAT | The `DatabaseReader` component is used to serve data from a database
TYPE | Reader, Sitemap Component
BLOCK | Database
CLASS | org.apache.cocoon.reading.DatabaseReader
SINCE | Cocoon 2.1
CACHEABLE | yes

## Description

This Reader pulls a resource from a database. It is configured with the Connection to use, parameters specify the table and column to pull the image from, and source specifies the source key information.

## Usage

### Sitemap pipeline examples

The following pipeline snippet uses a `Database Reader` for serving PNG images from a database.

    <map:match pattern="images/*.png">
      <map:read type="databasereader"
        src="{1}"
        mime-type="image/png">
        <!-- option sitemap parameters -->
        <map:parameter name="table" value="images"/>
        <map:parameter name="image" value="image"/>
        <map:parameter name="key" value="name"/>
        <map:parameter name="where" value="publishing = 1"/>
        <map:parameter name="order-by" value="created"/>
        <map:parameter name="last-modified" value="last-modified"/>
      </map:read>
    </map:match>

The snippet above make following assumption about the database

- A database table `images` holds the PNG image data.
- The database table has `image` column storing the image data as `BLOB`.
- The database table has `key` column which must match the value of of `{1}`.
- The database table has `publishing` column indicating by value `1` that the image data is allowed to get published.
- The database table has `created` column, indicating the creation date of the image data, and used if the `key` is not a primary key, serving images in a LIFO fashion.
- The database table has `last-modified` column of type `TIMESTAMP` indicating the last modification date of the image data.

### Sitemap component configuration example

    <map:readers...
      <map:reader name="databasereader"
        src="org.apache.cocoon.reading.DatabaseReader"
        logger="sitemap.reader.databasereader"
        pool-max="32"/>
        <!-- optional reader configuration -->
        ...
      </map:readers>
    ...

### Configuration

In the `Database Reader` declaration section following configuration options are available

Configurationname | Type | Comment
--- | --- | ---
use-connection | Data source name | The name of a database selector, configured in the `cocoon.xconf` file.
invalidate | never | always | This option configures the caching behaviour if lastModifed has value of `-1`.

### Setup

The `DatabaseReader` accepts following setup parameters

Parametername | Type | Comment
--- | --- | ---
table | database table name | The database table name
image | database column name | The column name of the image data
key | database key column name | The key column name of the image data matching the src attribute of the <map:read> sitemap usage.
where | database where expression | Optional parameter specifying SQL where expression.
order-by | database order-by expression | Optional parameter specifying an SQL order-by expression.
last-modified | timestamp column name | Optional parameter a `TIMESTAMP` column name, added to the `SELECT` clause of the SQL query.
content-type | database column name | Optional parameter a column name, if specified the column value overrides the `mime-type` attribute of the <map:read> sitemap usage.

The key value is derived from the `src` attribute of the `Database Reader` usage..

The `Database Reader` builds internally following SQL query:

    SELECT {image} [, last-modified] [, {order-by-column} ] from {table}
      WHERE {key} = {src} [ AND {where} ]
      [ORDER BY {order-by}]

### Effect on Object Model and Sitemap Parameters

## Bugs/Caveats

The `Database Reader` needs a datasource name, it is referenced by the configuration element `use-connection`. The datasource name has to be configured in the Cocoon database configuration `cocoon.xconf`.

If the parameter `last-modified` ends with `" DESC"` this suffix is truncated as it is appended to the SQL clause, noted as {order-by-column} in the SQL query snippet above.

## History

12-25-02: created initial version by Bernhard Huber

## See also

Errors and Improvements? If you see any errors or potential improvements in this document please help us: View, Edit or comment on the latest development version (registration required).