You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by se...@apache.org on 2019/07/04 11:28:14 UTC
[calcite] branch master updated: [CALCITE-2460] [CALCITE-2459] Add
TO_BASE64 / FROM_BASE64 SQL functions (Wenhui Tang)
This is an automated email from the ASF dual-hosted git repository.
sereda pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 1748f05 [CALCITE-2460] [CALCITE-2459] Add TO_BASE64 / FROM_BASE64 SQL functions (Wenhui Tang)
1748f05 is described below
commit 1748f0503e7b626a8d0165f1698adb8b61bbc31e
Author: winifredtamg <we...@yeah.net>
AuthorDate: Mon May 27 18:25:53 2019 +0800
[CALCITE-2460] [CALCITE-2459] Add TO_BASE64 / FROM_BASE64 SQL functions (Wenhui Tang)
---
.../calcite/adapter/enumerable/RexImpTable.java | 4 ++
.../org/apache/calcite/runtime/SqlFunctions.java | 36 +++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 23 +++++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 73 ++++++++++++++++++++++
.../org/apache/calcite/test/SqlFunctionsTest.java | 37 +++++++++++
site/_docs/reference.md | 2 +
7 files changed, 177 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 5178a5c..aaafe6a 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -92,6 +92,7 @@ import static org.apache.calcite.linq4j.tree.ExpressionType.OrElse;
import static org.apache.calcite.linq4j.tree.ExpressionType.Subtract;
import static org.apache.calcite.linq4j.tree.ExpressionType.UnaryPlus;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DIFFERENCE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_DEPTH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_KEYS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_LENGTH;
@@ -105,6 +106,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ABS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ACOS;
@@ -282,6 +284,8 @@ public class RexImpTable {
defineMethod(UPPER, BuiltInMethod.UPPER.method, NullPolicy.STRICT);
defineMethod(LOWER, BuiltInMethod.LOWER.method, NullPolicy.STRICT);
defineMethod(INITCAP, BuiltInMethod.INITCAP.method, NullPolicy.STRICT);
+ defineMethod(TO_BASE64, BuiltInMethod.TO_BASE64.method, NullPolicy.STRICT);
+ defineMethod(FROM_BASE64, BuiltInMethod.FROM_BASE64.method, NullPolicy.STRICT);
defineMethod(SUBSTRING, BuiltInMethod.SUBSTRING.method, NullPolicy.STRICT);
defineMethod(LEFT, BuiltInMethod.LEFT.method, NullPolicy.ANY);
defineMethod(RIGHT, BuiltInMethod.RIGHT.method, NullPolicy.ANY);
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index bea0558..fb9e32c 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -41,6 +41,7 @@ import org.apache.calcite.util.Util;
import org.apache.commons.codec.language.Soundex;
+import com.google.common.base.Splitter;
import com.google.common.base.Strings;
import java.lang.reflect.Field;
@@ -53,6 +54,7 @@ import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Base64;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
@@ -71,6 +73,8 @@ import javax.annotation.Nonnull;
import static org.apache.calcite.util.Static.RESOURCE;
+import static java.nio.charset.StandardCharsets.UTF_8;
+
/**
* Helper methods to implement SQL functions in generated code.
*
@@ -94,6 +98,8 @@ public class SqlFunctions {
private static final int SOUNDEX_LENGTH = 4;
+ private static final Pattern FROM_BASE64_REGEXP = Pattern.compile("[\\t\\n\\r\\s]");
+
private static final Function1<List<Object>, Enumerable<Object>> LIST_AS_ENUMERABLE =
Linq4j::asEnumerable;
@@ -128,6 +134,36 @@ public class SqlFunctions {
private SqlFunctions() {
}
+ /** SQL TO_BASE64(string) function. */
+ public static String toBase64(String string) {
+ return toBase64_(string.getBytes(UTF_8));
+ }
+
+ /** SQL TO_BASE64(string) function for binary string. */
+ public static String toBase64(ByteString string) {
+ return toBase64_(string.getBytes());
+ }
+
+ private static String toBase64_(byte[] bytes) {
+ String base64 = Base64.getEncoder().encodeToString(bytes);
+ StringBuilder str = new StringBuilder(base64.length() + base64.length() / 76);
+ Splitter.fixedLength(76).split(base64).iterator().forEachRemaining(s -> {
+ str.append(s);
+ str.append("\n");
+ });
+ return str.substring(0, str.length() - 1);
+ }
+
+ /** SQL FROM_BASE64(string) function. */
+ public static ByteString fromBase64(String base64) {
+ try {
+ base64 = FROM_BASE64_REGEXP.matcher(base64).replaceAll("");
+ return new ByteString(Base64.getDecoder().decode(base64));
+ } catch (IllegalArgumentException e) {
+ return null;
+ }
+ }
+
/** SQL SUBSTRING(string FROM ... FOR ...) function. */
public static String substring(String c, int s, int l) {
int lc = c.length();
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index ff27685..94afbbe 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -25,6 +25,7 @@ import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlReturnTypeInference;
+import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeTransforms;
import java.util.ArrayList;
@@ -214,6 +215,28 @@ public abstract class SqlLibraryOperators {
null,
OperandTypes.CHARACTER,
SqlFunctionCategory.STRING);
+
+ @LibraryOperator(libraries = {MYSQL})
+ public static final SqlFunction FROM_BASE64 =
+ new SqlFunction(
+ "FROM_BASE64",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.cascade(
+ ReturnTypes.explicit(SqlTypeName.VARBINARY), SqlTypeTransforms.TO_NULLABLE),
+ null,
+ OperandTypes.STRING,
+ SqlFunctionCategory.STRING);
+
+ @LibraryOperator(libraries = {MYSQL})
+ public static final SqlFunction TO_BASE64 =
+ new SqlFunction(
+ "TO_BASE64",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.cascade(
+ ReturnTypes.explicit(SqlTypeName.VARCHAR), SqlTypeTransforms.TO_NULLABLE),
+ null,
+ OperandTypes.or(OperandTypes.STRING, OperandTypes.BINARY),
+ SqlFunctionCategory.STRING);
}
// End SqlLibraryOperators.java
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 4e9c242..30d91cd 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -280,6 +280,8 @@ public enum BuiltInMethod {
REVERSE(SqlFunctions.class, "reverse", String.class),
LEFT(SqlFunctions.class, "left", String.class, int.class),
RIGHT(SqlFunctions.class, "right", String.class, int.class),
+ TO_BASE64(SqlFunctions.class, "toBase64", String.class),
+ FROM_BASE64(SqlFunctions.class, "fromBase64", String.class),
JSONIZE(JsonFunctions.class, "jsonize", Object.class),
DEJSONIZE(JsonFunctions.class, "dejsonize", String.class),
JSON_VALUE_EXPRESSION(JsonFunctions.class, "jsonValueExpression",
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index 0edf895..eee2dc0 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -4277,6 +4277,79 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("ASCII(cast(null as varchar(1)))");
}
+ @Test public void testToBase64() {
+ final SqlTester tester1 = tester(SqlLibrary.MYSQL);
+ tester1.setFor(SqlLibraryOperators.TO_BASE64);
+ tester1.checkString("to_base64(x'546869732069732061207465737420537472696e672e')",
+ "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==",
+ "VARCHAR NOT NULL");
+ tester1.checkString("to_base64(x'546869732069732061207465737420537472696e672e20636865"
+ + "636b20726573756c7465206f7574206f66203736546869732069732061207465737420537472696e"
+ + "672e546869732069732061207465737420537472696e672e54686973206973206120746573742053"
+ + "7472696e672e546869732069732061207465737420537472696e672e546869732069732061207465"
+ + "737420537472696e672e20546869732069732061207465737420537472696e672e20636865636b20"
+ + "726573756c7465206f7574206f66203736546869732069732061207465737420537472696e672e54"
+ + "6869732069732061207465737420537472696e672e54686973206973206120746573742053747269"
+ + "6e672e546869732069732061207465737420537472696e672e546869732069732061207465737420"
+ + "537472696e672e20546869732069732061207465737420537472696e672e20636865636b20726573"
+ + "756c7465206f7574206f66203736546869732069732061207465737420537472696e672e54686973"
+ + "2069732061207465737420537472696e672e546869732069732061207465737420537472696e672e"
+ + "546869732069732061207465737420537472696e672e546869732069732061207465737420537472"
+ + "696e672e')",
+ "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBjaGVjayByZXN1bHRlIG91dCBvZiA3NlRoaXMgaXMgYSB0\n"
+ + "ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRo\n"
+ + "aXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuIFRoaXMgaXMgYSB0ZXN0\n"
+ + "IFN0cmluZy4gY2hlY2sgcmVzdWx0ZSBvdXQgb2YgNzZUaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhp\n"
+ + "cyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBT\n"
+ + "dHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBUaGlzIGlzIGEgdGVzdCBTdHJpbmcuIGNoZWNr\n"
+ + "IHJlc3VsdGUgb3V0IG9mIDc2VGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0\n"
+ + "cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMg\n"
+ + "YSB0ZXN0IFN0cmluZy4=",
+ "VARCHAR NOT NULL");
+ tester1.checkString("to_base64('This is a test String.')",
+ "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==",
+ "VARCHAR NOT NULL");
+ tester1.checkString("to_base64('This is a test String. check resulte out of 76T"
+ + "his is a test String.This is a test String.This is a test String.This is a "
+ + "test String.This is a test String. This is a test String. check resulte out "
+ + "of 76This is a test String.This is a test String.This is a test String.This "
+ + "is a test String.This is a test String. This is a test String. check resulte "
+ + "out of 76This is a test String.This is a test String.This is a test String."
+ + "This is a test String.This is a test String.')",
+ "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBjaGVjayByZXN1bHRlIG91dCBvZiA3NlRoaXMgaXMgYSB0\n"
+ + "ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRo\n"
+ + "aXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuIFRoaXMgaXMgYSB0ZXN0\n"
+ + "IFN0cmluZy4gY2hlY2sgcmVzdWx0ZSBvdXQgb2YgNzZUaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhp\n"
+ + "cyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBT\n"
+ + "dHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBUaGlzIGlzIGEgdGVzdCBTdHJpbmcuIGNoZWNr\n"
+ + "IHJlc3VsdGUgb3V0IG9mIDc2VGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0\n"
+ + "cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMg\n"
+ + "YSB0ZXN0IFN0cmluZy4=",
+ "VARCHAR NOT NULL");
+ tester1.checkString("to_base64('')", "", "VARCHAR NOT NULL");
+ tester1.checkString("to_base64('a')", "YQ==", "VARCHAR NOT NULL");
+ tester1.checkString("to_base64(x'61')", "YQ==", "VARCHAR NOT NULL");
+ }
+
+ @Test public void testFromBase64() {
+ final SqlTester tester1 = tester(SqlLibrary.MYSQL);
+ tester1.setFor(SqlLibraryOperators.FROM_BASE64);
+ tester1.checkString("from_base64('VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY NOT NULL");
+ tester1.checkString("from_base64('VGhpcyBpcyBhIHRlc\t3QgU3RyaW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY NOT NULL");
+ tester1.checkString("from_base64('VGhpcyBpcyBhIHRlc\t3QgU3\nRyaW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY NOT NULL");
+ tester1.checkString("from_base64('VGhpcyB pcyBhIHRlc3Qg\tU3Ry\naW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY NOT NULL");
+ tester1.checkNull("from_base64('-1')");
+ tester1.checkNull("from_base64('-100')");
+ }
+
@Test public void testRepeatFunc() {
final SqlTester tester1 = tester(SqlLibrary.MYSQL);
tester1.setFor(SqlLibraryOperators.REPEAT);
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index fa91f34..7d2840b 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -34,6 +34,7 @@ import static org.apache.calcite.avatica.util.DateTimeUtils.ymdToUnixDate;
import static org.apache.calcite.runtime.SqlFunctions.addMonths;
import static org.apache.calcite.runtime.SqlFunctions.charLength;
import static org.apache.calcite.runtime.SqlFunctions.concat;
+import static org.apache.calcite.runtime.SqlFunctions.fromBase64;
import static org.apache.calcite.runtime.SqlFunctions.greater;
import static org.apache.calcite.runtime.SqlFunctions.initcap;
import static org.apache.calcite.runtime.SqlFunctions.lesser;
@@ -42,6 +43,7 @@ import static org.apache.calcite.runtime.SqlFunctions.ltrim;
import static org.apache.calcite.runtime.SqlFunctions.posixRegex;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.subtractMonths;
+import static org.apache.calcite.runtime.SqlFunctions.toBase64;
import static org.apache.calcite.runtime.SqlFunctions.trim;
import static org.apache.calcite.runtime.SqlFunctions.upper;
@@ -54,6 +56,8 @@ import static org.junit.Assert.assertSame;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.fail;
+import static java.nio.charset.StandardCharsets.UTF_8;
+
/**
* Unit test for the methods in {@link SqlFunctions} that implement SQL
* functions.
@@ -97,6 +101,39 @@ public class SqlFunctionsTest {
assertEquals("a bcd iijk", lower("A bCd Iijk"));
}
+ @Test public void testFromBase64() {
+ final List<String> expectedList =
+ Arrays.asList("", "\0", "0", "a", " ", "\n", "\r\n", "\u03C0", "hello\tword");
+ for (String expected: expectedList) {
+ assertEquals(new ByteString(expected.getBytes(UTF_8)), fromBase64(toBase64(expected)));
+ }
+ assertThat("546869732069732061207465737420537472696e672e",
+ is(fromBase64("VGhpcyB pcyBh\rIHRlc3Qg\tU3Ry\naW5nLg==").toString()));
+ assertNull(null, fromBase64("-1"));
+ }
+
+ @Test public void testToBase64() {
+ assertThat(
+ "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBjaGVjayByZXN1bHRlIG91dCBvZiA3NlRoaXMgaXMgYSB0\n"
+ + "ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRo\n"
+ + "aXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuIFRoaXMgaXMgYSB0ZXN0\n"
+ + "IFN0cmluZy4gY2hlY2sgcmVzdWx0ZSBvdXQgb2YgNzZUaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhp\n"
+ + "cyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBT\n"
+ + "dHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBUaGlzIGlzIGEgdGVzdCBTdHJpbmcuIGNoZWNr\n"
+ + "IHJlc3VsdGUgb3V0IG9mIDc2VGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0\n"
+ + "cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMg\n"
+ + "YSB0ZXN0IFN0cmluZy4=",
+ is(
+ toBase64("This is a test String. check resulte out of 76This is a test String."
+ + "This is a test String.This is a test String.This is a test String."
+ + "This is a test String. This is a test String. check resulte out of 76"
+ + "This is a test String.This is a test String.This is a test String."
+ + "This is a test String.This is a test String. This is a test String. "
+ + "check resulte out of 76This is a test String.This is a test String."
+ + "This is a test String.This is a test String.This is a test String.")));
+ assertThat("", is(toBase64("")));
+ }
+
@Test public void testUpper() {
assertEquals("A BCD IIJK", upper("A bCd iIjk"));
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 4a4505b..8f01cdc 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2104,6 +2104,8 @@ semantics.
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of bytes used to store the binary representation of a *jsonValue*
| o | LEAST(expr [, expr ]* ) | Returns the least of the expressions
| m p | LEFT(string, length) | Returns the leftmost *length* characters from the *string*
+| m | TO_BASE64(string) | Converts the *string* to base-64 encoded form and returns a encoded string
+| m | FROM_BASE64(string) | Returns the decoded result of a base-64 *string* as a string
| o | LTRIM(string) | Returns *string* with all blanks removed from the start
| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
| m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1