| Table of Contents |
|---|
Overview
Identified by type="ODBC", this storage option stores data using an ODBC-compliant database. This allows for persistence across software restarts and supports shared access across the nodes of a cluster, provided the ODBC driver and the database support transactions. No explicit database locking is performed inside the plugin itself.
| Info |
|---|
Use of this plugin requires that its extension library |
...
Settings specific to this type include:
Name | Type | Default | Description |
|---|
cleanupInterval cleanupInterval | time in seconds | 900 | Interval in seconds between cleanup runs, when expired entries are swept by a background thread. Set to 0 to disable. |
isolationLevel isolationLevel | "SERIALIZABLE", "REPEATABLE_READ", "READ_COMMITTED" "READ_UNCOMMITTED" | Transaction isolation level for database access. Defaults to SERIALIZABLE to prevent race conditions while updating records. Reduce at your own risk, as the code is written to assume the guarantees that level provides. |
| Include Page | ||||
|---|---|---|---|---|
|
Child Elements
Name | Cardinality | Type | Description |
|---|---|---|---|
<ConnectionString> | 1 | string | Required element containing an ODBC connection string. Exact contents differ across drivers. Note that you may wish to use a so-called "named" connection defined in odbc.ini (or for Windows inside the ODBC control panel applet or registry) to keep the database password out of this file, since it will be readable by the web server account/user. |
<RetryOnError> | 0 or 1 | whitespace-delimited list of strings | Instructs the plugin to retry certain operations if a matching ODBC status code is returned from the operation. This is used to detect rolled back transactions caused by optimistic locking and retry them, by including the driver-specific status code that indicates the condition. Under load, you'll typically see problems on most databases that have to be worked around using this setting. |
Examples
The below is excerpted from the configuration file to illustrate the key pieces involved.
...
This plugin obviously requires a database to store its data. The plugin does not automatically create any tables for itself, so this has to be done ahead of time. The rough SQL DDL for the database is the following:
...
...
Rough DDL for the database
| Code Block | ||
|---|---|---|
| ||
CREATE TABLE version (
major int NOT NULL,
minor int NOT NULL
)
CREATE TABLE strings (
context varchar(255) not null,
id varchar(255) not null,
expires datetime not null,
version smallint not null,
value varchar(255) not null,
PRIMARY KEY (context, id)
)
CREATE TABLE texts (
context varchar(255) not null,
id varchar(255) not null,
expires datetime not null,
version smallint not null,
value text not null,
PRIMARY KEY (context, id)
) |
...
In PostgreSQL the datetime data type is called timestamp which requires a small change to the reference SQL from above:
DDL
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
CREATE TABLE version (
major int NOT NULL,
minor int NOT NULL
);
INSERT INTO version VALUES (1,0);
CREATE TABLE strings (
context varchar(255) NOT NULL,
id varchar(255) NOT NULL,
expires timestamp NOT NULL,
version smallint NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (context, id)
);
CREATE TABLE texts (
context varchar(255) NOT NULL,
id varchar(255) NOT NULL,
expires timestamp NOT NULL,
version smallint NOT NULL,
value text NOT NULL,
PRIMARY KEY (context, id)
); |
GOTCHA: Be sure that your pg_hba.conf file is set up to allow IPv4 MD5 authentication from the network location of your SP host:
| Code Block | ||||
|---|---|---|---|---|
| ||||
local all postgres peer host all all 127.0.0.1/32 md5 host all all 10.0.0.0/8 md5 |
On RHEL/CentOS Linux, the ODBC driver links itself to an unversioned library, /usr/lib64/libpq.so, but the underlying postgresql package fails to create that symlink, so after installing the postgresql-libs package, you'll need to do so:
...
...
Install postgresql-libs and create symlink
| Code Block | ||
|---|---|---|
| ||
yum install -y postgresql-libs ln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so |
Finally, an example connection string:
shibboleth3.xml
| Code Block | ||||
|---|---|---|---|---|
| ||||
<StorageService type="ODBC" id="db" cleanupInterval="900">
<ConnectionString><![CDATA[
Driver=PostgreSQL;Server=127.0.0.1;Port=5432;Database=shibboleth-sp;Uid=shibboleth-sp;Password=shibboleth-sp-password
]]></ConnectionString>
</StorageService> |
...
"Quiet in here, isn't it Sarge?" "Too quiet, son."