opengamma / elsql Goto Github PK

My test case is trying to select from a sequence, but this is a more general issue. When I try the following using HSQLDB:

  SELECT NEXT VALUE FOR @VALUE(:sequenceName) AS next_id
  FROM (VALUES (0))

and pass my_seq as the sequence name variable, the SQL gets translated to:

  SELECT NEXT VALUE FOR MY_SEQAS next_id
  FROM (VALUES (0))

rather than:

  SELECT NEXT VALUE FOR MY_SEQ AS next_id
  FROM (VALUES (0))

i.e. the whitespace is gobbled and the statement therefore fails

Currently, ElsqlBundle is a final class. This becomes problematic when trying to mock an ElsqlBundle using Mockito. Mockito cannot mock final classes.

Use case:
In my Spring application, ElsqlBundle is a bean that is injected into Repository classes. When testing my repository classes, I cannot mock the injected ElsqlBundle because it is a final class.

The @PAGING tag currently does not allow literal values to be passed in. While this behaviour is correct according to the documentation (@PAGING(offsetVariable,fetchVariable)), allowing so could be useful in some cases.

For example when I always want to fetch the top x results, I'll have to add an hardcoded offsetVariable = 0 param next to the variable fetchVariable = x param.

I could try to create a PR for this, if desired.

Hi

As I developed the camel-elsql [1] component its based on an existing camel-sql component. In the latter we also support named parameters but the syntax is slightly different, we use :# as the marker for a named parameter. However we allow the named parameter to present a function that we evaluate at runtime to fetch the actual value.

for example

:#${body.user.firstName}

Where the function is represented using ${xxxx}

When using ElSql that does not work, as I cannot do in

SELECT *
FROM FOO
WHERE name = :${body.user.firstName}

At runtime that gives me a parameter name in the hasParameter callback with the value of :${body.user.firstName. Notice the last } is missing. I also noticed that in other combos that the value was empty.

I just logged this ticket here in the morning to ask whether ElSql would consider some kind of support for named parameters as functions. I guess the problem could be parsing the syntax.

Though I am considering in the next Camel release to consider the parameter name as a function if there is no direct hit, so you can do

SELECT *
FROM FOO
WHERE name = :body.user.firstName

Anyway just wanted to say thanks as well for this great SQL library. I love that it allows me to work with SQL externally and that they resemble actual SQL. That is why I also like MyBatis etc. But this library is even lighter.

• [1] http://camel.apache.org/elsql

It would be good to have a simple example that is shipped as part of elsql and not refer to that big OpenGamma project.

Essentially, this is my code:

ElSql elSql = ElSql.parse(ElSqlConfig.DEFAULT, Main.class.getResource("/sql/queries.elsql"));
Map<String, String> params = Collections.singletonMap("key", "value");
String sql = elSql.getSql("SomeQuery", params);

That won't compile, since ElSql#getSql explicitly takes a Map<String, Object>.
In this case making it

Map<String, Object> params = Collections.singletonMap("key", "value");

would be an obvious fix, but that Map might come from somewhere else and casting is somewhat ugly.

Changing the signature of ElSql#getSql to take a Map<String, ?> instead would fix this minor issue in a backward compatible way, or am I missing something?

Oh, and many thanks for this awesome library, it really fills a niche.
I'll try to use it together with fluent-jdbc, that seems to use the same syntax for named parameters as the Spring JdbcTemplate.

The aim of elsql is to keep complex SQL in the code, however a simple looping facility can occasionally be useful in the elsql file.

@NAME(Test1)
  SELECT * FROM foo WHERE
  @LOOP(:size)
    (a = :a@LOOPINDEX AND b = :b@LOOPINDEX)
    @LOOPJOIN OR

Using loops in a nested way seems to be impossible, am I right? This could be very useful, however. For example when I have x columns which I want to match against y values.

Something like this: (This obviously won't work, because of the duplicate @LOOPINDEX).

@NAME(Find)
    SELECT
        id
    FROM
        tablename
    WHERE
        @LOOP(:numberOfSearchTerms)
            (
            @LOOP(:numberOfSearchFields)
               :searchField@LOOPINDEX @LIKE :searchTerm@LOOPINDEX)
               @LOOPJOIN OR
            )
            @LOOPJOIN AND

Cannot put both tags on the same line.

When I use ElSqlBundle to read external sql file. An multiline SQL changed to one line sql. For example:

@name(example)
SELECT
COUNT(*)
FROM
Employee

this is changed to
SELECT COUNT(*) FROM Employee

I would like to preserve multi line structure of SQL if it is possible.

Add a new tag to simply output the value of a parameter with no surrounding space.

Previously this could be achieved with @INCLUDE(:variable) but that was really a bug.

@INCLUDE(:variable) should use variable as name to include, not as the inclusion of the variable itself.

Added @VALUE in #3 to achieve that. Note that @VALUE does not add a trailing space, whereas @INCLUDE(:variable) did.

Consider the following code:

import com.opengamma.elsql.ElSql;
import com.opengamma.elsql.ElSqlConfig;

public class Test {

    public static void main(String[] args) {
        ElSql bundle = ElSql.of(ElSqlConfig.DEFAULT, Test.class);
        bundle.getSql("test");
    }
}

There is no matching .elsql resource and an exception will occur when when retrieving the 'test' fragment:

Exception in thread "main" java.lang.IllegalArgumentException: Unknown fragment name: test
    at com.opengamma.elsql.SqlFragments.getFragment(SqlFragments.java:172)
    at com.opengamma.elsql.SqlFragments.getSql(SqlFragments.java:153)
    at com.opengamma.elsql.ElSql.getSql(ElSql.java:141)
    at Test.main(Test.java:8)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)

Process finished with exit code 1

My suggestion is to fail fast when there is no matching resource at all and provide a clear error message in that case. This will make debugging a little bit easier when you're just getting to know this library.

Would it be possible to build and release the JAR as an OSGi compliant bundle?

As there is no external dependencies then the OSGi bundle is simple to do. There is a maven-bundle-plugin that can do this automatic:
http://felix.apache.org/documentation/subprojects/apache-felix-maven-bundle-plugin-bnd.html

All that would be needed is to declare the Export-Package so something alike

 <plugin>    
        <groupId>org.apache.felix</groupId>
        <artifactId>maven-bundle-plugin</artifactId>
        <extensions>true</extensions>
        <configuration>
          <instructions>
            <Export-Package>com.opengamma.elsql</Export-Package>
          </instructions>
        </configuration>
      </plugin>    

Is there anything in the SqlServer2008 configuration that is known to be specific to the 2008 version of SQL Server?
If not, it would be preferable to have a generic SqlServer config, and SqlServer2008 could be an alias for that (for anyone who is already using it).

I'm willing to put in a pull request if it sounds like a reasonable change.
Thanks,
Andrew

Match behaviour of OFFSET/FETCH tags.

@LOOP(2)

Hi folks,
I want to use the JPA and ElSql together, but I don't know if that is possible. The goal is to use JPA configuration and export my queries to .elsql file.

Thank you for your attention.

There is no good reason why ElSql should depend on Spring. However, the change will have to be made in a backwards compatible way.

Perhaps I've missed it, but it would be useful to have a way to escape @ symbols that appear in the Query body and aren't ElSql tags.
Looking at the parser it appears that any line starting with @ that isn't a recognized tag will throw an exception.

It would be nice if there was a way (there doesn't appear to be one that I can see) to either match on a column or add an IS NULL if there is no parameter (or the parameter value is null).

Perhaps something like this?

@AND(:name)
     a.name = :name
@NOT @AND(:name)
     a.name IS NULL

Trying to do @AND(:name = null) currently gives a NPE, and changing it so that a null value matches a string of "null" (e.g. using Objects.toString(value) instead of value.toString() in ConditionalSqlFragment#isMatch) might be bad if you actually had strings with value "null" in your DB - however that would be a simpler solution.

I'm willing to work on implementing something if I can get some advice on whether this type of syntax would be good or if something else would be more in keeping with the rest of the library.

I'm finding ElSql to be a useful library to externalize SQL and support multiple databases.

I have a folder structure:

- src/test/java
  -- TestC.java
  -- TestC.elsql

My code is:

    ElSqlBundle bundle = ElSqlBundle.of(ElSqlConfig.POSTGRES, TestC.class);
    String sql = bundle.getSql("SelectTest");
    System.out.println(sql);

And it works!

However with the scructure:

- src/test/java
  -- TestC.java
- src/test/resources
 -- META-INF
  ---- TestC.elsql

I got an error:

    java.lang.IllegalArgumentException: Unknown fragment name: SelectTest
        at com.opengamma.elsql.ElSqlBundle.getFragment(ElSqlBundle.java:233)    

If a trailing line comment is used it is retained, which breaks the resulting SQL. See #1.

It seems the @LIKE processing, where it generates an = comparison instead of a LIKE when possible, is broken in the presence of escaped wildcard characters.

If you provide test\_test as a parameter, ElSqlConfig.isLikeWildcard(value) will see the escaped wildcard, and return that it is not a 'like wildcard'. Consequently, LikeSqlFragment generates a comparison using the = operator. So you SQL ends up as:

SELECT * FROM x WHERE a = 'test\_test'

This searches for test\_test and not for test_test.

If you specify test_test, it will see the wildcard and generate:

SELECT * FROM x WHERE a LIKE 'test_test'

which matches more than it should. So whether you pass escaped or unescaped parameters, the end result is never what you want.

I am not sure what a good way to fix this would be. The only possible way seems to be to always generate a LIKE, or to let the LikeSqlFragment unescape the parameter if it does not generate a LIKE, but that feels like data it should not be touching.