[hibernate-dev] SQM and cast targets
Christian Beikov
christian.beikov at gmail.com
Wed May 31 10:49:38 EDT 2017
The naming problem and because people might be used to specify "managed
type names" with "treat" is actually why I suggested to reuse "treat".
I don't know how you'd like to implement the cast function, but allowing
to mix the type names/codes with actual DBMS specific type names doesn't
sound right to me. How about a syntax like the following
castTarget
// should allow either
// - named cast (IDENTIFIER)
//- JavaTypeDescriptorRegistry (imported) key
//- java.sql.Types field NAME (coded cast synonym - field's value)
//- coded cast (INTEGER_LITERAL)
//- SqlTypeDescriptorRegistry key
: INTEGER_LITERAL | IDENTIFIER ( '(' dbmsType=parenthesisBalancedText')')?
parenthesisBalancedText
: [^\(\)]* ('(' parenthesisBalancedText ')' )?
So every cast expression must define a "known type" which we use to
determine the expression type, but can optionally define in parenthesis
the concrete DBMS type. Valid examples would be something like
cast( x as String ), cast( x as String( varchar ) ), cast( x as String(
varchar2( 5000 ) ) )
What do you think about that?
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 31.05.2017 um 14:43 schrieb Steve Ebersole:
> We would not be able to. That is the trouble with any kind of
> "pass-through. We would need some form of hint/directive from the user.
>
> Also in thinking about it some more, I think that re-using treat is
> not appropriate. treat is specifically defined in regards to
> hierarchies, which mean this use would be counter-intuitive. But a
> "wrapper" of some sort (and really cast and treat are just specialized
> typing wrappers)seems like a good option. We'd just need a good name
> for it.
>
> On Wed, May 31, 2017 at 7:24 AM Christian Beikov
> <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>> wrote:
>
> Yeah the example you gave would reflect what I was thinking about.
>
> How would you determine the expression type if the castTarget is
> just "passed-through" then?
>
>
> Mit freundlichen Grüßen,
> ------------------------------------------------------------------------
> *Christian Beikov*
> Am 31.05.2017 um 13:44 schrieb Steve Ebersole:
>> Oh, I just saw your last statement. I'm not a fan of "only ever
>> [using cast function] for the pass-through case"
>>
>> On Wed, May 31, 2017 at 6:43 AM Steve Ebersole
>> <steve at hibernate.org <mailto:steve at hibernate.org>> wrote:
>>
>> You mean something like `treat( cast(x as some_db_type) as
>> String)`?
>>
>>
>> On Wed, May 31, 2017 at 1:12 AM Christian Beikov
>> <christian.beikov at gmail.com
>> <mailto:christian.beikov at gmail.com>> wrote:
>>
>> So during parsing you try to lookup the castTarget and if
>> it can't be found, just pass through? If you pass it
>> through, what would be the type of the expression?
>>
>> I'd like to present an idea I just had. How about we
>> reuse the "TREAT" function/operator for doing these
>> "casts" to named types. Applying the operator does not
>> necessarily cause a SQL "cast" i.e. if the expression is
>> a select item and the JDBC driver supports converting a
>> value to the desired type automatically, there is no need
>> for a cast. The main difference to a "cast" function
>> would be, that the expression type will be set to the
>> desired type, whereas the "cast" function will set the
>> type to "unknown" i.e. requiring the user to use the
>> treat operator around the cast. The cast function will
>> then only ever be used for the pass-through case. Wdyt?
>>
>>
>> Mit freundlichen Grüßen,
>> ------------------------------------------------------------------------
>> *Christian Beikov*
>> Am 30.05.2017 um 18:00 schrieb Steve Ebersole:
>>> How about this rule then?
>>>
>>> castTarget
>>> // should allow either
>>> // - named cast (IDENTIFIER)
>>> //- JavaTypeDescriptorRegistry (imported) key
>>> //- java.sql.Types field NAME (coded cast synonym -
>>> field's value)
>>> //- "pass through"
>>> //- coded cast (INTEGER_LITERAL)
>>> //- SqlTypeDescriptorRegistry key
>>> : IDENTIFIER | INTEGER_LITERAL
>>> ;
>>>
>>> On Mon, May 29, 2017 at 11:16 AM Steve Ebersole
>>> <steve at hibernate.org <mailto:steve at hibernate.org>> wrote:
>>>
>>> Yes, ultimately these need to resolve to
>>> SqlTypeDescriptor. So perhaps we allow both.
>>>
>>> What I just want to get out of is the
>>> open-ended-ness. Non-determinism is bad. E.g.,
>>> like what you just mentioned... how should the
>>> parser understand that "TEXT" `cast(x as TEXT)` is a
>>> database type name versus Java class name versus
>>> something else? Structurally we cannot - one String
>>> is syntactically the same as any other String.
>>>
>>> So do we just accept some policy of "well if we
>>> don't understand it we'll just pass it through to
>>> the database"? To me that's just a cop-out. Not to
>>> mention that it invariably leaves the door open to
>>> non-portability. If instead we limited this to just
>>> Java types (JavaTypeDescriptorRegistry keys) and
>>> JDBC type codes (SqlTypeDescriptorRegistry keys) we
>>> can fully support this in a portable manner. Now
>>> that does lead to a question for databases which
>>> make the silly decision (looking at you pgsql) to
>>> map multiple types to the same JDBC type code.
>>>
>>> As much as possible I think we ought to not be
>>> relying on the database to validate these kinds of
>>> things. An error from the database is going to be
>>> much less descriptive as to what exactly is wrong
>>> compared to a validation done by Hibernate.
>>>
>>> Not sure the correct answer, just some thoughts.
>>>
>>> An option is to allow 3 types of cast targets:
>>>
>>> 1. Java type name we can resolve against the
>>> JavaTypeDescriptorRegistry
>>> 2. A JDBC type (either by code or name) we can
>>> resolve against the SqlTypeDescriptorRegistry
>>> 3. Any other text we can resolve against the
>>> Dialect as a "valid SQL type"
>>>
>>> I'm kind of leery of (3), but if everyone else
>>> agrees it is important to allow that non-portability
>>> then I will consider it. And keep in mind that this
>>> is really only needed for databases like pgsql to
>>> handle the multiple types it maps to a single JDBC
>>> type code... all other cases can (and should) be
>>> handled by (1) and (2).
>>>
>>>
>>>
>>> On Mon, May 29, 2017 at 10:36 AM Christian Beikov
>>> <christian.beikov at gmail.com
>>> <mailto:christian.beikov at gmail.com>> wrote:
>>>
>>> Sounds good, although I guess there might be
>>> cases when ONLY this
>>> approach won't work that well.
>>>
>>> I am specifically thinking about casts to the
>>> various character types
>>> that are available in the different DBMS. A cast
>>> to "String" might work
>>> most of the time, but we should still have an
>>> option to cast to CLOB,
>>> TEXT or whatever other datatype a DBMS offers.
>>>
>>>
>>> Mit freundlichen Grüßen,
>>> ------------------------------------------------------------------------
>>> *Christian Beikov*
>>> Am 29.05.2017 um 16:17 schrieb Steve Ebersole:
>>> > Currently casting in HQL is under-defined and
>>> open-ended (and therefore
>>> > pretty inconsistent). What does that mean?
>>> Well, what is a valid cast
>>> > target in HQL? There really is not a defined
>>> > answer to that.
>>> >
>>> > I'd like to start formalizing the answer to this.
>>> >
>>> > Specifically, I am thinking this should be
>>> defined around
>>> > JavaTypeDescriptor. So that we'd understand
>>> any Java type registered with
>>> > with JavaTypeDescriptorRegistry, and
>>> specifically any that properly
>>> > implements `#getJdbcRecommendedSqlType` (using
>>> the Dialect to resolve the
>>> > cast target in the generated SQL).
>>> >
>>> > Anyone have objections to this? Thoughts?
>>> > _______________________________________________
>>> > hibernate-dev mailing list
>>> > hibernate-dev at lists.jboss.org
>>> <mailto:hibernate-dev at lists.jboss.org>
>>> >
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>> _______________________________________________
>>> hibernate-dev mailing list
>>> hibernate-dev at lists.jboss.org
>>> <mailto:hibernate-dev at lists.jboss.org>
>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>
>
More information about the hibernate-dev
mailing list