Add REGEXP support

[IceBlizz6]

I'm trying to add the REGEXP function into jinq. It's a function from SQLite, has a similar syntax to LIKE.

It should generate: SELECT name FROM MyTable WHERE name REGEXP '^.....$'

I tried to add REGEXP to custom sql functions, this generated: "SELECT A FROM MyTable A WHERE function('REGEXP', A.name, :param0)"

Which is not recognized and gives following error: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: function (REGEXP)

I'm assuming that custom sql function may not be correct. Is it possible to add a new AST node for REGEXP function without having to edit the jinq source code?

[my2iu]

The custom function feature is mainly intended for stuff like stored procedures. The REGEXP function is a little weird because it’s not standard SQL, but some databases offer it as a built-in function. Because Jinq relies on JPA underneath, you would first need to register the built-in REGEXP function with Hibernate somehow so that Hibernate would know how to deal with it. Then, Jinq would need to be modified to allow REGEXP functions to pass through in a form that Hibernate could handle. Browsing around the web a bit, it looks like it’s potentially doable but a little messy. I don’t currently have the cycles to code on this, but I’ll try to have a deeper look later on.

[my2iu]

I guess I would need to know how you intend on exporting the MySQL REGEXP function so that it's available in Hibernate, then I could see how Jinq could be modified to generate that sort of output.

[IceBlizz6]

So for my specific case i'm using SQLite, this includes support for REGEXP, but does not include an implementation. In my case i supplied my own implementation.

1) Create implementation function that is added to the connection (code taken from online resource) public static void createRegexpFunction(java.sql.Connection connection) throws SQLException { // Create regexp() function to make the REGEXP operator available Function.create(connection, "REGEXP", new Function() { @Override protected void xFunc() throws SQLException { String expression = value_text(0); String value = value_text(1); if (value == null) value = ""; Pattern pattern = Pattern.compile(expression); result(pattern.matcher(value).find() ? 1 : 0); } }); }

2) Retrieve the actual connection object to register from step 1, then i register some dummy method (matchRegex) as target for Jinq lambdas, put this into custom sql function (which was not the way to go as we know now) public static void test(EntityManagerFactory entityManagerFactory, JinqJPAStreamProvider streamProvider) throws SQLException, NoSuchMethodException { var entityManager = entityManagerFactory.createEntityManager(); var sessionImpl = (org.hibernate.internal.SessionImpl)entityManager.unwrap(Session.class).getDelegate() var connection = sessionImpl.connection(); createRegexpFunction(connection); entityManager.close(); streamProvider.registerCustomSqlFunction(JinqHelper.class.getMethod("matchRegex"), "REGEXP"); }

3. using this... someJinqStream.where(e -> matchRegex(e.getName, myRegex))...

Results in: unexpected AST node: function (REGEXP) near line 1, column 55 [SELECT A FROM Table A WHERE function('REGEXP', A.name, :param0)]

Running the same through native query: SELECT name FROM MyTable WHERE name REGEXP '^.....$' Gives the expected output. From my end i would require a solution where the lambda generates the SQL query above, or the hibernate equivalent. I realize now that this should perhaps have been posted in the hibernate forums as if hibernate does not support it, then it may not be possible for you to do much about it.

[IceBlizz6]

If you want to look further into this then let me know if you need a working sample of the code above

[my2iu]

I think you need something like this to tell Hibernate about regexp before Jinq would be able to do anything (Jinq sits on top of Hibernate and is not able to directly generate any SQL).

Performance-wise, I'm not sure you'd be getting much benefit beyond just reading the data into Java, and then filtering by the regex there. I don't think SQLite can do any magic with REGEXes since it bypasses all the database indices and whatnot.

[IceBlizz6]

So based on that link it is possible to add REGEXP support to hibernate without editing the hibernate source code. Would it be possible to add similar functionality to the JINQ library where i could add support for the REGEXP through a method call myself? Doesn't have to be only for regexp, but rather a more general method that supports adding in new functions?

[my2iu]

Sure. Once you get the Hibernate side working, you can send me a working Hibernate query that uses REGEXP, and I can then modify Jinq to generate compatible queries.