This is an automated email from the ASF dual-hosted git repository.
micklich pushed a commit to branch feature/jdbc_rewrite
in repository
https://gitbox.apache.org/repos/asf/incubator-streampipes-extensions.git
The following commit(s) were added to refs/heads/feature/jdbc_rewrite by this
push:
new 9d1258e adding beginning lockup table, sqlattributes referencing to
name
9d1258e is described below
commit 9d1258e50f7e0f22d07f61a89971a66136c2a943
Author: micklich <[email protected]>
AuthorDate: Thu Jul 2 19:35:03 2020 +0200
adding beginning lockup table, sqlattributes referencing to name
---
.../sinks/databases/jvm/jdbcclient/JdbcClient.java | 25 +++++--
.../databases/jvm/jdbcclient/SqlAttribute.java | 34 ++++++---
.../sinks/databases/jvm/jdbcclient/SqlLockup.java | 85 ++++++++++++++++++++++
.../sinks/databases/jvm/mysql/Mysql.java | 5 +-
.../jvm/postgresql/PostgresJdbcClient.java | 12 ++-
5 files changed, 132 insertions(+), 29 deletions(-)
diff --git
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/JdbcClient.java
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/JdbcClient.java
index ad2a917..efa9eda 100644
---
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/JdbcClient.java
+++
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/JdbcClient.java
@@ -30,6 +30,7 @@ import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import java.util.Optional;
public class JdbcClient {
@@ -40,7 +41,7 @@ public class JdbcClient {
protected String tableName;
protected String user;
protected String password;
- protected String urlName;
+ protected String subprotocol;
protected String host;
protected Integer port;
@@ -79,7 +80,7 @@ public class JdbcClient {
String password,
String allowedRegEx,
String driver,
- String urlName,
+ String subprotocol,
Logger logger) throws SpRuntimeException {
this.eventProperties = eventProperties;
this.databaseName = databaseName;
@@ -89,10 +90,10 @@ public class JdbcClient {
this.allowedRegEx = allowedRegEx;
this.logger = logger;
// needed for fallback
- this.urlName = urlName;
+ this.subprotocol = subprotocol;
this.host = host;
this.port = port;
- this.url = "jdbc:" + urlName + "://" + host + ":" + port + "/" +
databaseName;
+ this.url = "jdbc:" + subprotocol + "://" + host + ":" + port + "/" +
databaseName;
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
@@ -123,7 +124,7 @@ public class JdbcClient {
throw new SpRuntimeException("User authentication error. Check
username or password: \n" + e.getMessage());
} else if (e.getSQLState().substring(0, 2).equals("3D")){
try {
- String urlFallback= "jdbc:" + urlName + "://" + host + ":" + port +
"/";
+ String urlFallback= "jdbc:" + subprotocol + "://" + host + ":" +
port + "/";
c = DriverManager.getConnection(urlFallback, user, password);
ensureDatabaseExists();
} catch (SQLException e2) {
@@ -281,6 +282,12 @@ public class JdbcClient {
generatePreparedStatement(event);
}
Parameterinfo p = parameters.get(newKey);
+//// newKey;
+// Optional<EventProperty> result =
eventProperties.stream().filter(name -> name.equals(newKey)).findFirst();
+//
+//// SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType(), property.getDomainProperties().toString(),
subprotocol).sqlName
+//
+// SqlAttribute type = SqlAttribute.getFromUri(p.toString(), null,
subprotocol);
SqlAttribute.setValue(p, pair.getValue(), ps);
}
}
@@ -339,6 +346,7 @@ public class JdbcClient {
pair.getKey() + "_", pre);
} else {
checkRegEx(pair.getKey(), "Columnname");
+
parameters.put(pair.getKey(), new Parameterinfo(index,
SqlAttribute.getFromObject(pair.getValue())));
s1.append(pre).append("\"").append(preProperty).append(pair.getKey().toLowerCase()).append("\"");
s2.append(pre).append("?");
@@ -390,10 +398,11 @@ public class JdbcClient {
/**
* Creates a SQL-Query with the given Properties (SQL-Injection safe). For
nested properties it
* recursively extracts the information. EventPropertyList are getting
converted to a string (so
- * in SQL to a VARCHAR(255)). For each type it uses {@link
SqlAttribute#getFromUri(String)}
+ * in SQL to a VARCHAR(255)). For each type it uses {@link
SqlAttribute#getFromUri(String, String, String)}
* internally to identify the SQL-type from the runtimeType.
*
* @param properties The list of properties which should be included in the
query
+ *
* @param preProperty A string which gets prepended to all property
runtimeNames
* @return A StringBuilder with the query which needs to be executed in
order to create the table
* @throws SpRuntimeException If the runtimeName of any property is not
allowed
@@ -422,10 +431,10 @@ public class JdbcClient {
// adding the type of the property (e.g. "VARCHAR(255)")
if (property instanceof EventPropertyPrimitive) {
- s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType()).sqlName);
+ s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType(), property.getDomainProperties().toString(),
subprotocol).sqlName);
} else {
// Must be an EventPropertyList then
- s.append(SqlAttribute.getFromUri(XSD._string.toString()).sqlName);
+ s.append(SqlAttribute.getFromUri(XSD._string.toString(),
property.getDomainProperties().toString(), subprotocol).sqlName);
}
}
pre = ", ";
diff --git
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlAttribute.java
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlAttribute.java
index 7fa360a..2c1dc0b 100644
---
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlAttribute.java
+++
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlAttribute.java
@@ -57,7 +57,16 @@ public enum SqlAttribute {
*/
public static SqlAttribute getFromObject(final Object o) {
SqlAttribute r;
- if (o instanceof Integer) {
+
+ String subprotocol = "postgresql";
+ // check domain first
+ if (o.equals(SO.DateTime)){
+ r = SqlAttribute.TIMESTAMP;
+ } else if (o.equals("[http://www.opengis.net/ont/geosparql#Geometry]") &&
subprotocol.equals("postgresql")) {
+ r = SqlAttribute.GEOMETRY;
+ }
+ // default
+ else if (o instanceof Integer) {
r = SqlAttribute.INTEGER;
} else if (o instanceof Long) {
r = SqlAttribute.LONG;
@@ -73,22 +82,25 @@ public enum SqlAttribute {
return r;
}
- public static SqlAttribute getFromUri(final String s) {
+ public static SqlAttribute getFromUri(final String type, final String
domain, final String subprotocol) {
SqlAttribute r;
- if (s.equals(XSD._integer.toString())) {
+ // check domain first
+ if (domain.equals(SO.DateTime)){
+ r = SqlAttribute.TIMESTAMP;
+ } else if
(domain.equals("[http://www.opengis.net/ont/geosparql#Geometry]") &&
subprotocol.equals("postgresql")) {
+ r = SqlAttribute.GEOMETRY;
+ }
+ // and then types
+ else if (type.equals(XSD._integer.toString())) {
r = SqlAttribute.INTEGER;
- } else if (s.equals(XSD._long.toString())) {
+ } else if (type.equals(XSD._long.toString())) {
r = SqlAttribute.LONG;
- } else if (s.equals(XSD._float.toString())) {
+ } else if (type.equals(XSD._float.toString())) {
r = SqlAttribute.FLOAT;
- } else if (s.equals(XSD._double.toString())) {
+ } else if (type.equals(XSD._double.toString())) {
r = SqlAttribute.DOUBLE;
- } else if (s.equals(XSD._boolean.toString())) {
+ } else if (type.equals(XSD._boolean.toString())) {
r = SqlAttribute.BOOLEAN;
- } else if (s.equals(SO.DateTime)) {
- r = SqlAttribute.TIMESTAMP;
- } else if (s.equals("http://www.opengis.net/ont/geosparql#Geometry")) {
- r = SqlAttribute.GEOMETRY;
} else {
r = SqlAttribute.STRING;
}
diff --git
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlLockup.java
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlLockup.java
new file mode 100644
index 0000000..3a2dc86
--- /dev/null
+++
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/jdbcclient/SqlLockup.java
@@ -0,0 +1,85 @@
+package org.apache.streampipes.sinks.databases.jvm.jdbcclient;
+
+import java.util.HashMap;
+
+public class SqlLockup {
+
+ private HashMap<Integer, String> lockupTable;
+
+ public SqlLockup() {
+ lockupTable = new HashMap<>();
+ lockupTable.put(4, "INT");
+ lockupTable.put(6, "FLOAT");
+ lockupTable.put(8, "DOUBLE");
+ lockupTable.put(12, "TEXT");
+ lockupTable.put(16, "BOOLEAN");
+
+
+ lockupTable.put(93, "TIMESTAMP");
+ lockupTable.put(1111, "GEOMETRY");
+ }
+
+ public HashMap<Integer, String> getLockupTable() {
+ return lockupTable;
+ }
+
+
+// protected String getKeyFromValue(int value) {
+//
+//
+// }
+}
+
+
+
+
+
+
+
+ // ARRAY (2003),
+// BIG_INT(-5),
+// BINARY(-2),
+// BIT( -7),
+// BLOB(2004),
+// BOOLEAN( 16),
+// Char(1),
+// CLOB (2005),
+// DATE(91),
+// DATALINK( 70),
+// DECIMAl( 3),
+// DISTINCT(2001),
+// DOUBLE( 8),
+// FLOAT( 6),
+// INTEGER( 4),
+// JAVAOBJECT (2000),
+// LONG_VAR_CHAR (-16),
+// NCHAR (-15),
+// NCLOB (2011),
+// VARCHARR( 12),
+// VARBINARY( -3),
+// TINY_INT (-6),
+// TIMESTAMPTZ(2014),
+// TIMESTAMP(93),
+// TIME( 92),
+// STRUCT( 2002),
+// SQLXML(2009),
+// SMALLINT( 5),
+// ROWID (-8),
+// REFCURSOR(2012),
+// REF (2006),
+// REAL (7),
+// NVARCHAR (-9),
+// NUMERIC(2),
+// NULL(0),
+//
+// INTEGER("INT"),
+// LONG("BIGINT"),
+// FLOAT("FLOAT"),
+// DOUBLE("DOUBLE"),
+// STRING("VARCHAR(255)"),
+// BOOLEAN("BOOLEAN"),
+// //MYSQL
+// MYSQL_DATETIME("DATETIME"),
+// //POSTGRES / POSTGIS
+// PG_DOUBLE("NUMERIC");
+
diff --git
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/mysql/Mysql.java
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/mysql/Mysql.java
index 8a9a0dd..c83467c 100644
---
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/mysql/Mysql.java
+++
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/mysql/Mysql.java
@@ -26,7 +26,6 @@ import
org.apache.streampipes.model.schema.EventPropertyPrimitive;
import org.apache.streampipes.sinks.databases.jvm.jdbcclient.JdbcClient;
import org.apache.streampipes.sinks.databases.jvm.jdbcclient.SqlAttribute;
import org.apache.streampipes.vocabulary.SO;
-import org.apache.streampipes.vocabulary.XSD;
import org.apache.streampipes.wrapper.context.EventSinkRuntimeContext;
import org.apache.streampipes.wrapper.runtime.EventSink;
import org.apache.streampipes.model.schema.EventProperty;
@@ -252,11 +251,11 @@ public class Mysql extends JdbcClient implements
EventSink<MysqlParameters> {
s.append(SqlAttribute.TIMESTAMP);
this.timestampKeys.add(property.getRuntimeName());
} else {
-
s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType()));
+
s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType(), property.getDomainProperties().toString(),
subprotocol).sqlName);
}
} else {
// Must be an EventPropertyList then
- s.append(SqlAttribute.getFromUri(XSD._string.toString()));
+ s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType(), property.getDomainProperties().toString(),
subprotocol).sqlName);
}
}
pre = ", ";
diff --git
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/postgresql/PostgresJdbcClient.java
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/postgresql/PostgresJdbcClient.java
index 4ae1dc1..6f5343c 100644
---
a/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/postgresql/PostgresJdbcClient.java
+++
b/streampipes-sinks-databases-jvm/src/main/java/org/apache/streampipes/sinks/databases/jvm/postgresql/PostgresJdbcClient.java
@@ -26,8 +26,6 @@ import
org.apache.streampipes.model.schema.EventPropertyNested;
import org.apache.streampipes.model.schema.EventPropertyPrimitive;
import org.apache.streampipes.sinks.databases.jvm.jdbcclient.JdbcClient;
import org.apache.streampipes.sinks.databases.jvm.jdbcclient.SqlAttribute;
-import org.apache.streampipes.vocabulary.SO;
-import org.apache.streampipes.vocabulary.XSD;
import java.sql.*;
import java.util.List;
@@ -55,7 +53,7 @@ public class PostgresJdbcClient extends JdbcClient {
String password,
String allowedRegEx,
String driver,
- String urlName,
+ String subprotocol,
Logger logger,
String schemaName,
boolean isToDropTable) throws
SpRuntimeException {
@@ -67,10 +65,10 @@ public class PostgresJdbcClient extends JdbcClient {
super.allowedRegEx = allowedRegEx;
super.logger = logger;
// needed for fallback
- super.urlName = urlName;
+ super.subprotocol = subprotocol;
super.host = host;
super.port = port;
- super.url = "jdbc:" + urlName + "://" + host + ":" + port + "/" +
databaseName;
+ super.url = "jdbc:" + subprotocol + "://" + host + ":" + port + "/" +
databaseName;
this.schemaName = schemaName;
this.isToDropTable = isToDropTable;
@@ -315,11 +313,11 @@ public class PostgresJdbcClient extends JdbcClient {
// if ((((EventPropertyPrimitive)
property).getRuntimeType().equals(XSD._double.toString()))) {
// s.append(SqlAttribute.PG_DOUBLE.sqlName);
// } else {
- s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType()).sqlName);
+ s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType(), property.getDomainProperties().toString(),
subprotocol).sqlName);
// }
} else {
// Must be an EventPropertyList then
- s.append(SqlAttribute.getFromUri(XSD._string.toString()).sqlName);
+ s.append(SqlAttribute.getFromUri(((EventPropertyPrimitive)
property).getRuntimeType(), property.getDomainProperties().toString(),
subprotocol).sqlName);
}
}
pre = ", ";