[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-31 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1092490830


##
core/src/main/java/org/apache/calcite/util/format/FormatModelUtil.java:
##
@@ -0,0 +1,66 @@
+/*
+ * 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.calcite.util.format;
+
+import com.google.common.collect.ImmutableMap;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+/**
+ * Utility class used to convert format strings into {@link 
FormatModelElement}s.
+ */
+public class FormatModelUtil {
+
+  private FormatModelUtil() {}
+
+  /**
+   * Parses the {@code fmtString} using element identifiers supplied by {@code 
fmtModel}.
+   */
+  public static List parse(String format,
+  ImmutableMap fmtModelMap) {
+List elements = new ArrayList<>();
+// TODO(CALCITE-2980): make these regex patterns static and tied to a 
library or dialect.
+StringBuilder regex = new StringBuilder();
+for (String key : fmtModelMap.keySet()) {
+  regex.append("(").append(Pattern.quote(key)).append(")|");
+}
+// remove the last '|'
+regex.setLength(regex.length() - 1);
+Matcher matcher = Pattern.compile(regex.toString()).matcher(format);

Review Comment:
   Formatting functions should be able to create a `FormatModels` instance that 
will compile the regex from the parse map during initialization. Calls to 
`parse` should now use the precomputed regex.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-31 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1092488598


##
core/src/main/java/org/apache/calcite/sql/FormatModel.java:
##
@@ -0,0 +1,154 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.fun.SqlLibrary;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeTransforms;
+import org.apache.calcite.util.format.FormatElementEnum;
+import org.apache.calcite.util.format.FormatModelElement;
+import org.apache.calcite.util.format.FormatModelElementAlias;
+import org.apache.calcite.util.format.FormatModelElementLiteral;
+import org.apache.calcite.util.format.FormatModelUtil;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.Arrays;
+import java.util.List;
+
+import static org.apache.calcite.util.format.FormatElementEnum.D;
+import static org.apache.calcite.util.format.FormatElementEnum.DAY;
+import static org.apache.calcite.util.format.FormatElementEnum.DD;
+import static org.apache.calcite.util.format.FormatElementEnum.DDD;
+import static org.apache.calcite.util.format.FormatElementEnum.DY;
+import static org.apache.calcite.util.format.FormatElementEnum.HH24;
+import static org.apache.calcite.util.format.FormatElementEnum.IW;
+import static org.apache.calcite.util.format.FormatElementEnum.MI;
+import static org.apache.calcite.util.format.FormatElementEnum.MM;
+import static org.apache.calcite.util.format.FormatElementEnum.MON;
+import static org.apache.calcite.util.format.FormatElementEnum.MONTH;
+import static org.apache.calcite.util.format.FormatElementEnum.Q;
+import static org.apache.calcite.util.format.FormatElementEnum.SS;
+import static org.apache.calcite.util.format.FormatElementEnum.TZR;
+import static org.apache.calcite.util.format.FormatElementEnum.WW;
+import static org.apache.calcite.util.format.FormatElementEnum.;
+
+/** A https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlqr/Format-Models.html;>
+ * format model is a character literal that describes the format of {@code 
DATETIME} or {@code
+ * NUMBER} data stored in a character string.
+ *
+ * {@link #unparse(SqlWriter, SqlCall, int, int)} calls
+ * {@link SqlDialect#getFormatElement(FormatElementEnum)} for known elements 
and aliases. Consider
+ * overriding this method if a dialect's format elements differs from those in 
{@link
+ * FormatElementEnum}
+ */
+public class FormatModel extends SqlInternalOperator {

Review Comment:
   I removed this operator class and moved most of the classes into the 
`FormatModels` utility as you had suggested elsewhere. It makes sense for them 
to be in a single place and we no longer try to string a string argument as 
something other than a string (i.e. no more premature optimization to convert a 
string into a `FormatModel`) 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-31 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1092488598


##
core/src/main/java/org/apache/calcite/sql/FormatModel.java:
##
@@ -0,0 +1,154 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.fun.SqlLibrary;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeTransforms;
+import org.apache.calcite.util.format.FormatElementEnum;
+import org.apache.calcite.util.format.FormatModelElement;
+import org.apache.calcite.util.format.FormatModelElementAlias;
+import org.apache.calcite.util.format.FormatModelElementLiteral;
+import org.apache.calcite.util.format.FormatModelUtil;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.Arrays;
+import java.util.List;
+
+import static org.apache.calcite.util.format.FormatElementEnum.D;
+import static org.apache.calcite.util.format.FormatElementEnum.DAY;
+import static org.apache.calcite.util.format.FormatElementEnum.DD;
+import static org.apache.calcite.util.format.FormatElementEnum.DDD;
+import static org.apache.calcite.util.format.FormatElementEnum.DY;
+import static org.apache.calcite.util.format.FormatElementEnum.HH24;
+import static org.apache.calcite.util.format.FormatElementEnum.IW;
+import static org.apache.calcite.util.format.FormatElementEnum.MI;
+import static org.apache.calcite.util.format.FormatElementEnum.MM;
+import static org.apache.calcite.util.format.FormatElementEnum.MON;
+import static org.apache.calcite.util.format.FormatElementEnum.MONTH;
+import static org.apache.calcite.util.format.FormatElementEnum.Q;
+import static org.apache.calcite.util.format.FormatElementEnum.SS;
+import static org.apache.calcite.util.format.FormatElementEnum.TZR;
+import static org.apache.calcite.util.format.FormatElementEnum.WW;
+import static org.apache.calcite.util.format.FormatElementEnum.;
+
+/** A https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlqr/Format-Models.html;>
+ * format model is a character literal that describes the format of {@code 
DATETIME} or {@code
+ * NUMBER} data stored in a character string.
+ *
+ * {@link #unparse(SqlWriter, SqlCall, int, int)} calls
+ * {@link SqlDialect#getFormatElement(FormatElementEnum)} for known elements 
and aliases. Consider
+ * overriding this method if a dialect's format elements differs from those in 
{@link
+ * FormatElementEnum}
+ */
+public class FormatModel extends SqlInternalOperator {

Review Comment:
   I removed this operator class. Instead, I moved most of the classes into the 
`FormatModels` utility class as you had suggested elsewhere. It makes sense for 
them to be in a single place and we no longer try to string a string argument 
as something other than a string (i.e. no more premature optimization to 
convert a string into a `FormatModel`) 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-31 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1092484458


##
core/src/main/java/org/apache/calcite/sql/SqlDialect.java:
##
@@ -1002,6 +1004,18 @@ protected static void unparseOffset(SqlWriter writer, 
@Nullable SqlNode offset)
 }
   }
 
+  /**

Review Comment:
   In addressing this comment I tried to do both things -- formatting/parse 
functions are given a static `FormatModels` instance for parsing strings. This 
`FormatModels` receives its element mapping from the `BigQuerySqlDialect` 
class. 
   
   We could reverse the hierarchy if needed so that the format function holds 
the element mapping and the SqlDialect refers to that for unparsing.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-31 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1092479733


##
core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java:
##
@@ -271,6 +272,50 @@ private static TimeUnit validate(TimeUnit timeUnit) {
 }
   }
 
+  /** {@inheritDoc}
+   *
+   * BigQuery format element reference:
+   * https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements;>
+   * BigQuery Standard SQL Format Elements.
+   */
+  @Override public String getFormatElement(FormatElementEnum fmtElement) {

Review Comment:
   As a followup to this PR, we can define non-standard element maps per 
dialect. Then, when we unparse a formatting function we would use the map to 
find the appropriate element string for the "standard" element. The map would 
be bi-directional as suggested so that it can be used as both the parse map for 
functions and for converting elements from one dialect to another.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-24 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1086039023


##
core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java:
##
@@ -271,6 +272,50 @@ private static TimeUnit validate(TimeUnit timeUnit) {
 }
   }
 
+  /** {@inheritDoc}
+   *
+   * BigQuery format element reference:
+   * https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements;>
+   * BigQuery Standard SQL Format Elements.
+   */
+  @Override public String getFormatElement(FormatElementEnum fmtElement) {

Review Comment:
   This doesn't mean we should get rid of the `getFormatElement` method (or at 
least something like it) entirely right? 
   
   A `SqlDialect` would still need to know how to get an appropriate format 
string from a BigQuery style one while unparsing. For example, 
`FORMAT_TIMESTAMP(TIMESTAMP '2012-12-02 12:05:00', '%R')` would need to unparse 
as`TO_CHAR( TIMESTAMP '2012-12-02 12:05:00', 'HH24:MI')` for Postgres based 
dialects.
   
   As you mentioned elsewhere,  this translation should not happen in a 
`FormatModel` _operator_ but rather the formatting function itself. That is, we 
should reach into the `SqlDialect` for this element mapping while in 
`SqlBigQueryFormatDatetimeFunction#unparse`
   
   For library specific functions this mapping could also be used as the parse 
map.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-22 Thread via GitHub 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1083554622


##
core/src/main/java/org/apache/calcite/util/format/FormatModelUtil.java:
##
@@ -0,0 +1,74 @@
+/*
+ * 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.calcite.util.format;
+
+import com.google.common.collect.ImmutableMap;
+
+import java.util.List;
+import java.util.Stack;
+
+/**
+ * Utility class used to convert format strings into {@link 
FormatModelElement}s.
+ */
+public class FormatModelUtil {
+
+  private FormatModelUtil() {}
+
+  /**
+   * Parses the {@code fmtString} using element identifiers supplied by {@code 
fmtModel}.
+   *
+   * TODO(CALCITE-2980): make this configurable for multiple parse maps. 
Currently this only works
+   * for BigQuery and MySQL style format strings where elements begin with '%'
+   */
+  public static List parse(String fmtString,

Review Comment:
   Took a stab at this in most recent commit.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-18 Thread GitBox 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1080688763


##
core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java:
##
@@ -240,6 +281,17 @@ public MysqlSqlDialect(Context context) {
   unparseListAggCall(writer, call, null, leftPrec, rightPrec);
   break;
 
+case FORMAT_DATE:
+case FORMAT_TIME:
+case FORMAT_TIMESTAMP:
+case FORMAT_DATETIME:
+  writer.print("DATE_FORMAT(");

Review Comment:
   Or better yet convert these BQ format functions into the standard `CAST(... 
as FORMAT )` and unparse the `CAST` to `DATE_FORMAT`, `TO_CHAR` 
accordingly.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-18 Thread GitBox 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1080677876


##
core/src/main/java/org/apache/calcite/util/format/FormatModelElementAlias.java:
##
@@ -0,0 +1,76 @@
+/*
+ * 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.calcite.util.format;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.Objects;
+import java.util.StringJoiner;
+
+/**
+ * Represents a format element comprised of one or more {@link 
FormatElementEnum} entries.
+ */
+public class FormatModelElementAlias implements FormatModelElement {

Review Comment:
   Yeah exactly - it maps to one or more FormatModelElementEnum entries.
   
   For example in Google SQL `%R` is the same as `HH24:MI`. I'll add more 
context/examples in the javadoc 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-18 Thread GitBox 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1080614602


##
core/src/main/java/org/apache/calcite/sql/FormatModel.java:
##
@@ -0,0 +1,154 @@
+/*
+ * 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.calcite.sql;
+
+import static org.apache.calcite.util.format.FormatElementEnum.D;
+import static org.apache.calcite.util.format.FormatElementEnum.DAY;
+import static org.apache.calcite.util.format.FormatElementEnum.DD;
+import static org.apache.calcite.util.format.FormatElementEnum.DDD;
+import static org.apache.calcite.util.format.FormatElementEnum.DY;
+import static org.apache.calcite.util.format.FormatElementEnum.HH24;
+import static org.apache.calcite.util.format.FormatElementEnum.IW;
+import static org.apache.calcite.util.format.FormatElementEnum.MI;
+import static org.apache.calcite.util.format.FormatElementEnum.MM;
+import static org.apache.calcite.util.format.FormatElementEnum.MON;
+import static org.apache.calcite.util.format.FormatElementEnum.MONTH;
+import static org.apache.calcite.util.format.FormatElementEnum.Q;
+import static org.apache.calcite.util.format.FormatElementEnum.SS;
+import static org.apache.calcite.util.format.FormatElementEnum.TZR;
+import static org.apache.calcite.util.format.FormatElementEnum.WW;
+import static org.apache.calcite.util.format.FormatElementEnum.;
+
+import org.apache.calcite.sql.fun.SqlLibrary;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeTransforms;
+import org.apache.calcite.util.format.FormatElementEnum;
+import org.apache.calcite.util.format.FormatModelElement;
+import org.apache.calcite.util.format.FormatModelElementAlias;
+import org.apache.calcite.util.format.FormatModelElementLiteral;
+import org.apache.calcite.util.format.FormatModelUtil;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.Arrays;
+import java.util.List;
+
+
+/** A https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlqr/Format-Models.html;>
+ * format model is a character literal that describes the format of {@code 
DATETIME} or {@code
+ * NUMBER} data stored in a character string.
+ *
+ * {@link #unparse(SqlWriter, SqlCall, int, int)} calls
+ * {@link SqlDialect#getFormatElement(FormatElementEnum)} for known elements 
and aliases. Consider
+ * overriding this method if a dialect's format elements differs from those in 
{@link
+ * FormatElementEnum}
+ */
+public class FormatModel extends SqlInternalOperator {
+
+  private List elements;
+  private ImmutableMap fmtModelParseMap;
+
+  /**
+   * TODO(CALCITE-2980): This should live elsewhere and be associated with 
{@link SqlLibrary}
+   * or {@link org.apache.calcite.config.Lex}.
+   */
+  public static final ImmutableMap 
BIG_QUERY_FORMAT_ELEMENT_PARSE_MAP =

Review Comment:
   For sure, I can open a Jira case to swap `GOOGLE_SQL` or `GSQL` with 
`BIG_QUERY` references -- would have to a non-breaking change for config 
settings etc.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-18 Thread GitBox 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1080608342


##
core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java:
##
@@ -240,6 +281,17 @@ public MysqlSqlDialect(Context context) {
   unparseListAggCall(writer, call, null, leftPrec, rightPrec);
   break;
 
+case FORMAT_DATE:
+case FORMAT_TIME:
+case FORMAT_TIMESTAMP:
+case FORMAT_DATETIME:
+  writer.print("DATE_FORMAT(");

Review Comment:
   Yeah that's open for discussion -- how to best swap a `FORMAT_TIMESTAMP` 
Google SQL style function call to any other dialect. Snowflake for example uses 
`TO_CHAR` or `TO_VARCHAR`. 
   
   Maybe a generic entry in `SqlKind` called `GOOGLE_SQL_DATETIME_FORMAT`?
   Could even be more generic like `DATETIME_FORMAT` and then each dialect 
would know how to unparse.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org

[GitHub] [calcite] tjbanghart commented on a diff in pull request #3034: [CALCITE-5357] Implement BigQuery FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME, and FORMAT_DATETIME

2023-01-18 Thread GitBox 


tjbanghart commented on code in PR #3034:
URL: https://github.com/apache/calcite/pull/3034#discussion_r1073950252


##
core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java:
##
@@ -184,6 +185,46 @@ public MysqlSqlDialect(Context context) {
 return super.getCastSpec(type);
   }
 
+  /** {@inheritDoc}
+   *
+   * MySQL format element reference:
+   * https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format;>
+   * MySQL Date and Time Functions.
+   */
+  @Override public String getFormatElement(FormatElementEnum fmtElement) {

Review Comment:
   Each dialect might have a different set of elements to represent the same 
type of datetime unit. 
   
   For example an abbreviated month name like `Jan` is `%b` in MySQL and `MON` 
in Snowflake. Each `SqlDialect` will have to override this method to supply the 
correct string for the element type. I only did MySQL, BigQuery, and Snowflake 
in the first commit because I wanted to get some feedback before working on 
each dialect.
   
   Oracle's format elements were chosen as the "base" set since they map 
closely to the SQL:2016 standard as described in 
[CALCITE-2980](https://issues.apache.org/jira/browse/CALCITE-2980).



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org