//
// Licensed 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.
//

==== DataSources (JDBC)

The Apache Karaf DataSources (JDBC) is an optional enterprise feature.

You have to install the `jdbc` feature first:

----
karaf@root()> feature:install jdbc
----

This feature provides an OSGi service to create/delete JDBC datasources in the container and perform database operations (SQL queries).

This JDBC OSGi service can be manipulated programmatically (see the developer guide for details), using the `jdbc:*` commands, or using the JDBC MBean.

===== Commands

====== `jdbc:create`

The `jdbc:create` command automatically creates a datasource definition file in the Apache Karaf `deploy` folder.

The `jdbc:create` accepts a set of options and the name argument:

----
karaf@root()> jdbc:create --help
DESCRIPTION
        jdbc:create

        Create a JDBC datasource

SYNTAX
        jdbc:create [options] name

ARGUMENTS
        name
                The JDBC datasource name

OPTIONS
        -u, --username
                The database username
        -v, --version
                The version of the driver to use
        -t, --type
                The JDBC datasource type (generic, MySQL, Oracle, Postgres, H2, HSQL, Derby, MSSQL)
        -url
                The JDBC URL to use
        -p, --password
                The database password
        -i, --install-bundles
                Try to install the bundles providing the JDBC driver
        -d, --driver
                The classname of the JDBC driver to use. NB: this option is used only the type generic
        --help
                Display this help message

----

* the `name` argument is required. It's the name of the datasource. The name is used to identify the datasource, and to create the datasource definition file (`deploy/datasource-[name].xml`).
* the `-u` option is optional. It defines the database username.
* the `-v` option is optional. It "forces" a given JDBC driver version (only used with the `-i` option).
* the `-t` option is required. It defines the JDBC datasource type. Accepted values are: MySQL, Oracle, Postgres, Derby, H2, HSQL, MSSQL, Generic. Generic is a generic configuration file using DBCP to create a pooled datasource. When using generic, it's up to you to install the JDBC driver and configure the `deploy/datasource-[name].xml` datasource file.
* the `-url` option is optional. It defines the JDBC URL to access to the database.
* the `-p` option is optional. It defines the database password.
* the `-d` option is optional. It defines the JDBC driver classname to use (only used with the generic type).
* the `-i` option is optional. If specified, the command will try to automatically install the OSGi bundles providing the JDBC driver (depending of the datasource type specified by the `-t` option).

For instance, to create an embedded Apache Derby database in Apache Karaf, you can do:

----
karaf@root()> jdbc:create -t derby -u test -i test
----

We can note that the Derby bundle has been installed automatically, and the datasource has been created:

----
karaf@root()> la
...
87 | Active   |  80 | 10.8.2000002.1181258  | Apache Derby 10.8
88 | Active   |  80 | 0.0.0                 | datasource-test.xml
----

We can see the `deploy/datasource-test.xml` datasource file.

===== `jdbc:delete`

The `jdbc:delete` command deletes a datasource by removing the `deploy/datasource-[name].xml` datasource file:

----
karaf@root()> jdbc:delete test
----

[NOTE]
====
The `jdbc:delete` does not uninstall the JDBC driver bundles and does not remove the files created by the JDBC driver (or the database in case of embedded database).
It's up to you to remove it.
====

===== `jdbc:datasources`

The `jdbc:datasources` command lists the JDBC datasources:

----
karaf@root()> jdbc:datasources
Name       | Product      | Version              | URL
------------------------------------------------------------------
/jdbc/test | Apache Derby | 10.8.2.2 - (1181258) | jdbc:derby:test
----

===== `jdbc:info`

The `jdbc:info` command provides details about a JDBC datasource:

----
karaf@root()> jdbc:info /jdbc/test
Property       | Value
--------------------------------------------------
driver.version | 10.8.2.2 - (1181258)
username       | APP
db.version     | 10.8.2.2 - (1181258)
db.product     | Apache Derby
driver.name    | Apache Derby Embedded JDBC Driver
url            | jdbc:derby:test
----

===== `jdbc:execute`

The `jdbc:execute` command executes a SQL query that doesn't return any result on a given JDBC datasource.

Typically, you can use the `jdbc:execute` command to create tables, insert values into tables, etc.

For instance, we can create a `person` table on our `test` datasource:

----
karaf@root()> jdbc:execute /jdbc/test "create table person(name varchar(100), nick varchar(100))"
----

And we can insert some records in the `person` table:

----
karaf@root()> jdbc:execute /jdbc/test "insert into person(name, nick) values('foo','bar')"
karaf@root()> jdbc:execute /jdbc/test "insert into person(name, nick) values('test','test')"
----

===== `jdbc:query`

The `jdbc:query` command is similar to the `jdbc:execute` one but it displays the query result.

For instance, to display the content of the `person` table, we can do:

----
karaf@root()> jdbc:query /jdbc/test "select * from person"
NICK       | NAME
--------------------------------
bar        | foo
test       | test
----

===== `jdbc:tables`

The `jdbc:tables` command displays all tables available on a given JDBC datasource:

----
karaf@root()> jdbc:tables /jdbc/test
REF_GENERATION | TYPE_NAME | TABLE_NAME       | TYPE_CAT | REMARKS | TYPE_SCHEM | TABLE_TYPE   | TABLE_SCHEM | TABLE_CAT | SELF_REFERENCING_COL_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------
               |           | SYSALIASES       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCHECKS        |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCOLPERMS      |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCOLUMNS       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCONGLOMERATES |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSCONSTRAINTS   |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSDEPENDS       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSFILES         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSFOREIGNKEYS   |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSKEYS          |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSPERMS         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSROLES         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSROUTINEPERMS  |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSCHEMAS       |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSEQUENCES     |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSTATEMENTS    |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSSTATISTICS    |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSTABLEPERMS    |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSTABLES        |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSTRIGGERS      |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSVIEWS         |          |         |            | SYSTEM TABLE | SYS         |           |
               |           | SYSDUMMY1        |          |         |            | SYSTEM TABLE | SYSIBM      |           |
               |           | PERSON           |          |         |            | TABLE        | APP         |           |
----

===== JMX JDBC MBean

The JMX JDBC MBean provides the JDBC datasources, and the operations to manipulate datasources and database.

The object name to use is `org.apache.karaf:type=jdbc,name=*`.

====== Attributes

The `Datasources` attribute provides a tabular data of all JDBC datasource, containing:

* `name` is the JDBC datasource name
* `product` is the database product backend
* `url` is the JDBC URL used by the datasource
* `version` is the database version backend.

====== Operations

* `create(name, type, jdbcDriverClassName, version, url, user, password, installBundles)` creates a JDBC datasource (the arguments correspond to the options of the `jdbc:create` command).
* `delete(name)` deletes a JDBC datasource.
* `info(datasource)` returns a Map (String/String) of details about a JDBC `datasource`.
* `tables(datasource)` returns a tabular data containing the tables available on a JDBC `datasource`.
* `execute(datasource, command` executes a SQL command on the given JDBC `datasource`.
* `query(datasource, query` executes a SQL query on the given JDBC `datasource` and return the execution result as tabular data.

