[[library-sql,SQL Library]]
= SQL =

[devstatus]
--------------
source=libraries/sql/dev-status.xml
--------------


The SQL Library provides facilities for working with SQL databases.

The center piece is the DataSource support that comes with
<<library-circuitbreaker>> and <<library-jmx>> support.
Moreover, this library helps dealing with schema migrations and doing SQL I/O
with the <<core-io>>.


== DataSource and connection pools ==

DataSource support comes in three flavors:

- using the http://sourceforge.net/projects/c3p0/[C3P0] connection pool
- using the http://commons.apache.org/dbcp/[Apache DBCP] connection pool
- importing an existing DataSource provided at assembly time


=== Connection Pools ===

*C3P0*

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/DocumentationSupport.java
tag=c3p0
----

*Apache DBCP*

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/DocumentationSupport.java
tag=dbcp
----


=== DataSource ===

*Assembly*

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/DocumentationSupport.java
tag=datasource
----

Assembled DataSources must be visible from the connection pool importer service.

*Configuration*

You need to provide a DataSource Configuration Entity per assembled DataSource.
See <<howto-configure-service>>.

[snippet,java]
----
source=libraries/sql/src/main/java/org/qi4j/library/sql/datasource/DataSourceConfigurationState.java
tag=config
----


=== Importing an existing DataSource ===

Importing an existing DataSource at assembly time is usefull when your Qi4j
Application runs in an environment where DataSource are already provided.

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/datasource/ExternalDataSourceTest.java
tag=assembly
----

This mechanism is provided as an integration convenience and using the embedded
connection pools described above is recommended.


== Circuit Breaker ==

Assemblers for managed and external DataSource takes an optional
CircuitBreaker and set it as <<def-metainfo>> of the DataSource.

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/DocumentationSupport.java
tag=cb-assembly
----

Then, when you gets injected or lookup a DataSource it will be automatically wrapped
by a CircuitBreaker proxy.

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/DocumentationSupport.java
tag=cb-datasource
----


== I/O ==

Here is a simple example:

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/liquibase/LiquibaseServiceTest.java
tag=io
----


== Liquibase ==

Database schema migration can be delegated to http://www.liquibase.org/[Liquibase].

*Assembly*

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/liquibase/LiquibaseServiceTest.java
tag=assembly
----

The LiquibaseService is activated on Application startup and if enabled it
applies the configured changelog.

*Configuration*

[snippet,java]
----
source=libraries/sql/src/main/java/org/qi4j/library/sql/liquibase/LiquibaseConfiguration.java
tag=config
----

For the Liquibase service to be enabled you must set it's Configuration
+enabled+ Property to TRUE. *contexts* and *changeLog* are optional.


== JMX ==

Thanks to the <<library-jmx>> the Configuration of DataSources is exposed
through JMX.

[snippet,java]
----
source=libraries/sql/src/test/java/org/qi4j/library/sql/jmx/DataSourceConfigurationManagerServiceTest.java
tag=jmx
----

Every DataSource visible from the DataSourceConfigurationManager Service
will get its Configuration available using a JMX client.

Note that the JMX support does not apply to existing DataSource imported as
described above.