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 Circuit 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 74. Artifact

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

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, 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.module ).
    withConfig( config, Visibility.layer ).
    assemble( mainModule );

Sample DataSource configuration defaults:

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

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