You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@openjpa.apache.org by "Patrick Linskey (JIRA)" <ji...@apache.org> on 2007/07/01 22:01:04 UTC

[jira] Created: (OPENJPA-273) Native functions in JPQL; functions available in more clauses

Native functions in JPQL; functions available in more clauses
-------------------------------------------------------------

                 Key: OPENJPA-273
                 URL: https://issues.apache.org/jira/browse/OPENJPA-273
             Project: OpenJPA
          Issue Type: New Feature
          Components: jdbc, kernel, query
    Affects Versions: 0.9.7, 0.9.6, 0.9.0
            Reporter: Patrick Linskey


JPQL has rather limited support for functions, in two ways. First, only a limited number of commonly-available functions are available in the language, and second, functions can only be used in a limited number of places in the grammar. This can be, well, limiting.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[jira] Commented: (OPENJPA-273) Native functions in JPQL; functions available in more clauses

Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.

    [ https://issues.apache.org/jira/browse/OPENJPA-273?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12510199 ] 

Patrick Linskey commented on OPENJPA-273:
-----------------------------------------

Another potential approach: create some syntax for escaping a part of a query to SQL, and then permit nested escapes back to JPQL (or at least JPQL parameters and path expressions)  from within the SQL bits. The query that I mentioned above might look like so:

    select :sql{stdev(:jpql{emp.salary})} from Employee emp

This has the advantage of permitting arbitrary SQL (excepting the escape sequence, which is ':jpql{' here) to be used. So, we can do an EXTRACT function like so:

    select distinct :sql{EXTRACT(year FROM :jpql{emp.hireDate})} from Employee emp

> Native functions in JPQL; functions available in more clauses
> -------------------------------------------------------------
>
>                 Key: OPENJPA-273
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-273
>             Project: OpenJPA
>          Issue Type: New Feature
>          Components: jdbc, kernel, query
>    Affects Versions: 0.9.0, 0.9.6, 0.9.7
>            Reporter: Patrick Linskey
>         Attachments: OPENJPA-273.patch
>
>
> JPQL has rather limited support for functions, in two ways. First, only a limited number of commonly-available functions are available in the language, and second, functions can only be used in a limited number of places in the grammar. This can be, well, limiting.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[jira] Updated: (OPENJPA-273) Native functions in JPQL; functions available in more clauses

Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.

     [ https://issues.apache.org/jira/browse/OPENJPA-273?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Patrick Linskey updated OPENJPA-273:
------------------------------------

    Attachment: OPENJPA-273.patch

Attached is a patch that allows native functions to be evaluated directly against the database using syntax like so:

    select :(stdev(emp.salary)) from Employee emp

I'm interested in hearing feedback about both the goals of this patch and the implementation strategy, mainly from an API standpoint.

This patch has a number of relatively-easily-resolvable limitations:

- No support for casting arguments, or any other SQL syntax in argument list. As a result, when native functions are used with the JPQL "SELECT NEW" syntax, the constructor / setters / fields must be of type Object. Additionally, this does not allow support for the Oracle EXTRACT function as used with dates.

- No support for nested function calls.

- Cannot use native functions as parameters to JPQL functions


Another approach could be to add support for OpenJPA query extensions (an already-existing feature not exposed in our JPQL parser). The advantage to this is more power; the disadvantage is that the syntax would be a bit more awkward / more of a black box. For example, the following queries would work with the existing query extension facilities:

    select emp.salary.ext:function('stdev') from Employee emp

    select e from Employee e where e.company.address.ext:getColumn ('ID') = 5

    select p from Product p where p.price < ext:sql ('(SELECT AVG(PRICE) FROM PRODUCTS)')

All of these query extensions expose things that my proposed function syntax would not, but they suffer three key weaknesses: 

1. They can only have a single argument (this is probably easily addressable).

2. For general-purpose use, the syntax gets a bit awkward, since the extension name takes up the natural function name position, so the name of the function would need to become the first argument

> Native functions in JPQL; functions available in more clauses
> -------------------------------------------------------------
>
>                 Key: OPENJPA-273
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-273
>             Project: OpenJPA
>          Issue Type: New Feature
>          Components: jdbc, kernel, query
>    Affects Versions: 0.9.0, 0.9.6, 0.9.7
>            Reporter: Patrick Linskey
>         Attachments: OPENJPA-273.patch
>
>
> JPQL has rather limited support for functions, in two ways. First, only a limited number of commonly-available functions are available in the language, and second, functions can only be used in a limited number of places in the grammar. This can be, well, limiting.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.