SQL Index/Query

code

docs

tests

This extension fully leverage the SQL Library meaning that you must use it to assemble your DataSource and that you get Cricuit Breaker and JMX integration for free.

Tip

See the SQL Support Sample that demonstrate combined use of SQL Library, SQL EntityStore and SQL Index/Query.

The following SQL databases are supported:

PostgreSQL

Implementations per database Vendor share a generic codebase but can override about everything SQL. As a consequence they can have strong differences in terms of performance if they use vendor specific extensions.

Table 83. Artifact

Group ID	Artifact ID	Version
org.qi4j.extension	org.qi4j.extension.indexing-sql	2.0

Configuration

SQL Index/Query Configuration is optional and provides only one configuration property: schemaName defaulted to qi4j_es. On SQL databases that don’t support schemas this configuration property is simply ignored.

The assembly snippets below show the DataSource assembly alongside the SQL Index/Query assembly. Remember to configure the DataSource properly, see SQL Library and Configure a Service.

PostgreSQL

Assembly is done using the provided Assembler:

// DataSourceService
new DBCPDataSourceServiceAssembler().
        identifiedBy( "postgres-datasource-service" ).
        visibleIn( Visibility.module ).
        withConfig( config ).
        withConfigVisibility( Visibility.layer ).
        assemble( mainModule );

// DataSource
new DataSourceAssembler().
        withDataSourceServiceIdentity( "postgres-datasource-service" ).
        identifiedBy( "postgres-datasource" ).
        visibleIn( Visibility.module ).
        withCircuitBreaker().
        assemble( mainModule );

// SQL Index/Query
new PostgreSQLIndexQueryAssembler().
        visibleIn( Visibility.application ).
        withConfig( config ).
        withConfigVisibility( Visibility.layer ).
        assemble( mainModule );

Sample DataSource configuration defaults:

enabled=true
url=jdbc:postgresql://localhost:5432/jdbc_test_db
driver=org.postgresql.Driver
username=jdbc_test_login
password=password

Important

The PostgreSQL ltree extension is needed on the used database, see below how to install it on your database.

Installing ltree on PostgreSQL >= 9.1 ==

It’s bundled with PostgreSQL but you need to activate it on your database:

CREATE EXTENSION ltree;

Installing ltree on PostgreSQL ⇐ 9.0

You need to install postgresql-contrib and import the module in your database. The following applies to Debian based distributions, adapt it to yours:

Install the contrib package: sudo apt-get install postgresql-contrib
Restart the database: sudo /etc/init.d/postgresql-8.4 restart
Change to the database owner account (e.g., postgres).
Change to the contrib modules' directory: /usr/share/postgresql/8.4/contrib/
Load the SQL files using: psql -U user_name -d database_name -f module_name.sql

For example to install the needed ltree module:

psql -U postgres -d database_name -f ltree.sql