[
https://issues.apache.org/jira/browse/KARAF-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16607074#comment-16607074
]
ASF GitHub Bot commented on KARAF-5802:
---------------------------------------
jbonofre closed pull request #50: [KARAF-5802] Add JDBC collector
URL: https://github.com/apache/karaf-decanter/pull/50
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git a/assembly/src/main/feature/feature.xml
b/assembly/src/main/feature/feature.xml
index e6a92db..0d3fba9 100644
--- a/assembly/src/main/feature/feature.xml
+++ b/assembly/src/main/feature/feature.xml
@@ -170,6 +170,17 @@
<configfile
finalname="/etc/org.apache.karaf.decanter.collector.mqtt.cfg">mvn:org.apache.karaf.decanter.collector/org.apache.karaf.decanter.collector.mqtt/${project.version}/cfg</configfile>
</feature>
+ <feature name="decanter-collector-jdbc-core" version="${project.version}"
description="Karaf Decanter JDBC Collector core">
+ <feature>decanter-common</feature>
+ <feature>scheduler</feature>
+
<bundle>mvn:org.apache.karaf.decanter.collector/org.apache.karaf.decanter.collector.jdbc/${project.version}</bundle>
+ </feature>
+
+ <feature name="decanter-collector-jdbc" version="${project.version}"
description="Karaf Decanter JDBC Collector">
+ <feature>decanter-collector-jdbc-core</feature>
+ <configfile
finalname="/etc/org.apache.karaf.decanter.collector.jdbc.cfg">mvn:org.apache.karaf.decanter.collector/org.apache.karaf.decanter.collector.jdbc/${project.version}/cfg</configfile>
+ </feature>
+
<feature name="decanter-appender-log" version="${project.version}"
description="Karaf Decanter Log Appender">
<feature>decanter-common</feature>
<bundle>mvn:org.apache.karaf.decanter.appender/org.apache.karaf.decanter.appender.log/${project.version}</bundle>
diff --git a/collector/jdbc/pom.xml b/collector/jdbc/pom.xml
new file mode 100644
index 0000000..bf30dd2
--- /dev/null
+++ b/collector/jdbc/pom.xml
@@ -0,0 +1,100 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project xmlns="http://maven.apache.org/POM/4.0.0";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd";>
+
+ <!--
+
+ 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.
+ -->
+
+ <modelVersion>4.0.0</modelVersion>
+
+ <parent>
+ <groupId>org.apache.karaf.decanter</groupId>
+ <artifactId>collector</artifactId>
+ <version>2.1.0-SNAPSHOT</version>
+ <relativePath>../pom.xml</relativePath>
+ </parent>
+
+ <groupId>org.apache.karaf.decanter.collector</groupId>
+ <artifactId>org.apache.karaf.decanter.collector.jdbc</artifactId>
+ <packaging>bundle</packaging>
+ <name>Apache Karaf :: Decanter :: Collector :: JDBC</name>
+
+ <dependencies>
+ <dependency>
+ <groupId>org.apache.karaf.decanter</groupId>
+ <artifactId>org.apache.karaf.decanter.api</artifactId>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.karaf.decanter.collector</groupId>
+ <artifactId>org.apache.karaf.decanter.collector.utils</artifactId>
+ </dependency>
+
+ <!-- test -->
+ <dependency>
+ <groupId>org.apache.derby</groupId>
+ <artifactId>derby</artifactId>
+ <scope>test</scope>
+ </dependency>
+ </dependencies>
+
+ <build>
+ <plugins>
+ <plugin>
+ <groupId>org.apache.felix</groupId>
+ <artifactId>maven-bundle-plugin</artifactId>
+ <inherited>true</inherited>
+ <extensions>true</extensions>
+ <configuration>
+ <obrRepository>NONE</obrRepository>
+ <instructions>
+
<Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>
+ <Export-Package>!*</Export-Package>
+ <Import-Package>
+ *
+ </Import-Package>
+ <Private-Package>
+ org.apache.karaf.decanter.collector.jdbc,
+ org.apache.karaf.decanter.collector.utils
+ </Private-Package>
+ <_dsannotations>*</_dsannotations>
+ </instructions>
+ </configuration>
+ </plugin>
+ <plugin>
+ <groupId>org.codehaus.mojo</groupId>
+ <artifactId>build-helper-maven-plugin</artifactId>
+ <executions>
+ <execution>
+ <phase>package</phase>
+ <goals>
+ <goal>attach-artifact</goal>
+ </goals>
+ <configuration>
+ <artifacts>
+ <artifact>
+
<file>src/main/cfg/org.apache.karaf.decanter.collector.jdbc.cfg</file>
+ <type>cfg</type>
+ </artifact>
+ </artifacts>
+ </configuration>
+ </execution>
+ </executions>
+ </plugin>
+ </plugins>
+ </build>
+
+</project>
\ No newline at end of file
diff --git
a/collector/jdbc/src/main/cfg/org.apache.karaf.decanter.collector.jdbc.cfg
b/collector/jdbc/src/main/cfg/org.apache.karaf.decanter.collector.jdbc.cfg
new file mode 100644
index 0000000..aa7385b
--- /dev/null
+++ b/collector/jdbc/src/main/cfg/org.apache.karaf.decanter.collector.jdbc.cfg
@@ -0,0 +1,28 @@
+################################################################################
+#
+# 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.
+#
+################################################################################
+
+#######################################
+# Decanter JDBC Collector Configuration
+#######################################
+
+# DataSource to use
+dataSource.target=(osgi.jndi.service.name=jdbc/decanter)
+
+# SQL Query to retrieve data
+query=select * from TABLE
\ No newline at end of file
diff --git
a/collector/jdbc/src/main/java/org/apache/karaf/decanter/collector/jdbc/JdbcCollector.java
b/collector/jdbc/src/main/java/org/apache/karaf/decanter/collector/jdbc/JdbcCollector.java
new file mode 100644
index 0000000..a7e8807
--- /dev/null
+++
b/collector/jdbc/src/main/java/org/apache/karaf/decanter/collector/jdbc/JdbcCollector.java
@@ -0,0 +1,192 @@
+/*
+ * 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.
+ */
+package org.apache.karaf.decanter.collector.jdbc;
+
+import org.apache.karaf.decanter.collector.utils.PropertiesPreparator;
+import org.osgi.service.component.ComponentContext;
+import org.osgi.service.component.annotations.Activate;
+import org.osgi.service.component.annotations.Component;
+import org.osgi.service.component.annotations.ConfigurationPolicy;
+import org.osgi.service.component.annotations.Deactivate;
+import org.osgi.service.component.annotations.Reference;
+import org.osgi.service.event.Event;
+import org.osgi.service.event.EventAdmin;
+import org.osgi.service.event.EventConstants;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.sql.DataSource;
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.Dictionary;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+@Component(
+ name = "org.apache.karaf.decanter.collector.jdbc",
+ immediate = true,
+ configurationPolicy = ConfigurationPolicy.REQUIRE,
+ property = { "decanter.collector.name=jdbc",
+ "scheduler.period:Long=60",
+ "scheduler.concurrent:Boolean=false",
+ "scheduler.name=decanter-collector-jdbc"}
+)
+public class JdbcCollector implements Runnable {
+
+ @Reference
+ public EventAdmin dispatcher;
+
+ @Reference
+ public DataSource dataSource;
+
+ private final static Logger LOGGER =
LoggerFactory.getLogger(JdbcCollector.class);
+
+ private String query;
+ private String dispatcherTopic;
+ private Dictionary<String, Object> properties;
+ private Connection connection;
+ private PreparedStatement preparedStatement;
+
+ @Activate
+ public void activate(ComponentContext context) throws Exception {
+ properties = context.getProperties();
+ open(properties);
+ }
+
+ public void open(Dictionary<String, Object> config) throws Exception {
+ query = getProperty(config, "query", null);
+ if (query == null) {
+ throw new IllegalStateException("Query is mandatory");
+ }
+ dispatcherTopic = getProperty(config, EventConstants.EVENT_TOPIC,
"decanter/collect/jdbc");
+
+ connection = dataSource.getConnection();
+ preparedStatement = connection.prepareStatement(query);
+ }
+
+ @Deactivate
+ public void deactivate() throws Exception {
+ if (preparedStatement != null) {
+ preparedStatement.close();
+ }
+ if (connection != null) {
+ connection.close();
+ }
+ }
+
+ private String getProperty(Dictionary<String, Object> properties, String
key, String defaultValue) {
+ return (properties.get(key) != null) ? (String) properties.get(key) :
defaultValue;
+ }
+
+ @Override
+ public void run() {
+ LOGGER.debug("Karaf Decanter JDBC collector exectutes query {}",
query);
+
+ List<Map<String, Object>> dataRows = query();
+
+ for (Map<String, Object> data : dataRows) {
+ Event event = new Event(dispatcherTopic, data);
+ dispatcher.postEvent(event);
+ }
+ }
+
+ public List<Map<String, Object>> query() {
+ List<Map<String, Object>> dataRows = new ArrayList<>();
+
+ try {
+ ResultSet resultSet = preparedStatement.executeQuery();
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ int columnCount = resultSetMetaData.getColumnCount();
+ int rowId = 1;
+ while (resultSet.next()) {
+ Map<String, Object> data = new HashMap<>();
+ data.put("type", "jdbc");
+ // unfortunately, getRow() is not fully supported by all JDBC
driver, like Derby
+ // data.put("row", resultSet.getRow());
+ data.put("rowId", rowId);
+ rowId++;
+
+ for (int i = 1; i <= columnCount; i++) {
+ String columnName = resultSetMetaData.getColumnName(i);
+ int columnType = resultSetMetaData.getColumnType(i);
+ if (columnType == Types.CHAR || columnType ==
Types.VARCHAR) {
+ String value = resultSet.getString(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.BOOLEAN) {
+ Boolean value = resultSet.getBoolean(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.INTEGER) {
+ Integer value = resultSet.getInt(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.CLOB) {
+ Clob value = resultSet.getClob(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.ARRAY) {
+ Array value = resultSet.getArray(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.FLOAT) {
+ Float value = resultSet.getFloat(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.BLOB) {
+ Blob value = resultSet.getBlob(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.DATE) {
+ Date value = resultSet.getDate(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.DOUBLE) {
+ Double value = resultSet.getDouble(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.BIGINT) {
+ Long value = resultSet.getLong(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.TIME) {
+ Time value = resultSet.getTime(i);
+ data.put(columnName, value);
+ }
+ if (columnType == Types.TIMESTAMP) {
+ Timestamp value = resultSet.getTimestamp(i);
+ data.put(columnName, value);
+ }
+ }
+
+ try {
+ PropertiesPreparator.prepare(data, properties);
+ } catch (Exception e) {
+ LOGGER.warn("Can't prepare data for the dispatcher", e);
+ }
+
+ dataRows.add(data);
+ }
+ } catch (Exception e) {
+ LOGGER.warn("Can't get data from database", e);
+ }
+
+ return dataRows;
+ }
+
+}
diff --git
a/collector/jdbc/src/test/java/org/apache/karaf/decanter/collector/jdbc/TestJdbcCollector.java
b/collector/jdbc/src/test/java/org/apache/karaf/decanter/collector/jdbc/TestJdbcCollector.java
new file mode 100644
index 0000000..1d2ecb6
--- /dev/null
+++
b/collector/jdbc/src/test/java/org/apache/karaf/decanter/collector/jdbc/TestJdbcCollector.java
@@ -0,0 +1,81 @@
+/*
+ * 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.
+ */
+package org.apache.karaf.decanter.collector.jdbc;
+
+import org.apache.derby.jdbc.EmbeddedDataSource;
+import org.junit.After;
+import org.junit.Assert;
+import org.junit.Before;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.Statement;
+import java.util.Dictionary;
+import java.util.Hashtable;
+import java.util.List;
+import java.util.Map;
+
+public class TestJdbcCollector {
+
+ private EmbeddedDataSource dataSource;
+
+ @Before
+ public void prepareBase() throws Exception {
+ System.setProperty("derby.stream.error.file", "target/derby.log");
+ dataSource = new EmbeddedDataSource();
+ dataSource.setDatabaseName("target/testDB");
+ dataSource.setCreateDatabase("create");
+
+ try (Connection connection = dataSource.getConnection(); Statement
statement = connection.createStatement();) {
+ statement.executeUpdate("create table TEST(id integer not null,
name varchar(26))");
+ statement.executeUpdate("insert into TEST(id, name) values(1,
'TEST1')");
+ statement.executeUpdate("insert into TEST(id, name) values(2,
'TEST2')");
+ }
+ }
+
+ @After
+ public void cleanup() throws Exception {
+ try (Connection connection = dataSource.getConnection(); Statement
statement = connection.createStatement();) {
+ statement.execute("drop table TEST");
+ } catch (Exception e) {
+ // Ignore
+ }
+ }
+
+ @Test
+ public void testCollector() throws Exception {
+ JdbcCollector collector = new JdbcCollector();
+ collector.dataSource = dataSource;
+ Dictionary<String, Object> config = new Hashtable<>();
+ config.put("query", "select * from TEST");
+ collector.open(config);
+
+ List<Map<String, Object>> dataRows = collector.query();
+ Assert.assertEquals(2, dataRows.size());
+
+ Assert.assertEquals(1, dataRows.get(0).get("ID"));
+ Assert.assertEquals("TEST1", dataRows.get(0).get("NAME"));
+ Assert.assertEquals(1, dataRows.get(0).get("rowId"));
+ Assert.assertEquals("jdbc", dataRows.get(0).get("type"));
+
+ Assert.assertEquals(2, dataRows.get(1).get("ID"));
+ Assert.assertEquals("TEST2", dataRows.get(1).get("NAME"));
+ Assert.assertEquals(2, dataRows.get(1).get("rowId"));
+ Assert.assertEquals("jdbc", dataRows.get(1).get("type"));
+ }
+
+}
diff --git a/collector/pom.xml b/collector/pom.xml
index cef98c1..245bd50 100644
--- a/collector/pom.xml
+++ b/collector/pom.xml
@@ -50,6 +50,7 @@
<module>rest-servlet</module>
<module>system</module>
<module>socket</module>
+ <module>jdbc</module>
</modules>
</project>
diff --git a/manual/src/main/asciidoc/user-guide/collectors.adoc
b/manual/src/main/asciidoc/user-guide/collectors.adoc
index 81304ba..2dbe3ae 100644
--- a/manual/src/main/asciidoc/user-guide/collectors.adoc
+++ b/manual/src/main/asciidoc/user-guide/collectors.adoc
@@ -725,6 +725,38 @@ karaf@root()> feature:install decanter-collector-dropwizard
As soon as a `MetricSet` (like `MetricRegistry`) service will be available,
the collector will get the metrics and
send to the Decanter dispatcher.
+==== JDBC
+
+The Decanter JDBC collector periodically executes a query on a database and
send the query result to the dispatcher.
+
+The `decanter-collector-jdbc` installs the JDBC collector:
+
+----
+karaf@root()> feature:install decanter-collector-jdbc
+----
+
+The feature also installs the
`etc/org.apache.karaf.decanter.collector.jdbc.cfg` configuration file:
+
+----
+#######################################
+# Decanter JDBC Collector Configuration
+#######################################
+
+# DataSource to use
+dataSource.target=(osgi.jndi.service.name=jdbc/decanter)
+
+# SQL Query to retrieve data
+query=select * from TABLE
+----
+
+This configuration file allows you to configure the datasource to use and the
SQL query to perform:
+
+* the `datasource.name` property contains the name of the JDBC datasource to
use to connect to the database. You can
+ create this datasource using the Karaf `jdbc:create` command (provided by
the `jdbc` feature).
+* the `query` property contains the SQL query to perform on the database and
retrieve data.
+
+This collector is periodically executed by the Karaf scheduler.
+
==== Customizing properties in collectors
You can add, rename or remove properties collected by the collectors before
sending it to the dispatcher.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
> Add JDBC collector
> ------------------
>
> Key: KARAF-5802
> URL: https://issues.apache.org/jira/browse/KARAF-5802
> Project: Karaf
> Issue Type: New Feature
> Components: decanter
> Reporter: Jean-Baptiste Onofré
> Assignee: Jean-Baptiste Onofré
> Priority: Major
> Fix For: decanter-2.1.0
>
>
> Decanter could provide a JDBC collector using a ManagedService. Each JDBC
> cfg file would contain a datasource name and a SQL query. It's a scheduled
> collector that executes the query periodically and send the result in the
> dispatcher (each row as an event, each colon as event property).
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
