[Module] Engine SQL

[ThuF]

Missing implementation for module: engine-sql

[g051051]

Are there any details on this request? What would this implement?

[ThuF]

I guess this was already migrated as database-sql module.

@delchev can you confirm that, so we could close this issue?

[delchev]

No, this one is different. The idea is to have SQL "engine" in terms of Dirigible engines:

https://github.com/eclipse/dirigible/blob/master/modules/engines/engine-api/src/main/java/org/eclipse/dirigible/engine/api/IEngineExecutor.java

and more precisely Script Engine:

https://github.com/eclipse/dirigible/blob/master/modules/engines/engine-api/src/main/java/org/eclipse/dirigible/engine/api/script/IScriptEngineExecutor.java

Simple steps from UX PoV:

1. Create a project sql_project
2. Create a file sample.sql
3. Enter SQL script in it e.g.:

SELECT * FROM DIRIGIBLE_EXTENSIONS

4. Save (Publish)
5. Access http://host:port/services/v4/sql/sql_project/sample.sql
6. The content is rendered as JSON to the response

---

Next thing is to support parameters:

SELECT * FROM DIRIGIBLE_EXTENSIONS WHERE EXTENSION_LOCATION LIKE :location

Then you can call it like: *http://host:port/services/v4/sql/sql_project/sample.sql?location=%2Fide%25

Underneath it has to check the parameters availability, replace with parameters, sort the values in the right order, set the parameters values and then call via JDBC.

It could be quite useful for fast prototyping of REST layer on top of RDBMS.

Inspiration from something similar yet simple enough can be found at:

https://github.com/eclipse/dirigible/tree/master/modules/engines/engine-command

[g051051]

I've been working on this, and after a lot of head scratching, have it to the "phase 1" level described by @delchev, where you can get the JSON result of a SQL call. It uses the DatabaseFacade for the DB access. Next step is to figure out how to pass and access parameters to allow selecting the datasource, then do parameter substitution.

[ThuF]

@g051051 What do you think about selecting the datasource through special query parameters like datasourceName/dirigibleDatasourceName and datasourceType/dirigibleDatasourceType, or through such custom headers? If no such parameters are passed, then the default datasource should be used.

[g051051]

@ThuF I'd prefer not to have magic query parameters that would prevent someone from using those names if they wanted. Custom headers would make using it a bit more difficult. No preview pane, and you'd need a real client of some kind like postman. So, I don't have a good answer here. Is there a way to specify the @Path annotation to support explicit datasource parameters?

[ThuF]

@g051051 Maybe using the path to determine the datasource type and name could be a good solution. For example:

• http://host:port/services/v4/sql/sql_project/sample.sql - using the default datasource
• http://host:port/services/v4/sql/custom/myCustomDatasourceName/sql_project/sample.sql - to use the custom datasource type with name myCustomDatasourceName
• http://host:port/services/v4/sql/local/myLocalDatasourceName/sql_project/sample.sql - to use the local datasource type with name myLocalDatasourceName
• http://host:port/services/v4/sql/managed/myManagedDatasourceName/sql_project/sample.sql - to use the managed datasource type with name myManagedDatasourceName
• http://host:port/services/v4/sql/dynamic/myDynamicDatasourceName/sql_project/sample.sql - to use the dynamic datasource type with name myDynamicDatasourceName

That way you can create 4 endpoints:

• @Path("/{path:.*}") - for the default one
• @Path("/custom/{datasourceName}/{path:.*}") - for the custom datasource type
• @Path("/local/{datasourceName}/{path:.*}") - for the local datasource type
• @Path("/managed/{datasourceName}/{path:.*}") - for the managed datasource type
• @Path("/dynamic/{datasourceName}/{path:.*}") - for the dynamic datasource type

Here you can find all supported database types:

• CustomDatabase
• H2Database - providing the local datasource type
• ManagedDatabase
• DynamicDatabase

[g051051]

@ThuF I was afraid that's how it would work. I'd prefer it to be more dynamic than hardcoding 5 distinct endpoints, but I can certainly do it that way, at least for now.

Another design question arises: do we want to support all those different HTTP methods? For instance, a PUT would imply that we use executeUpdate instead of query. Do we want to support different SQL capabilities, or confine it to queries and GET/POST?

[ThuF]

@g051051 Sticking to read queries with HTTP GET and modify queries with PUT or POST will be great for a start! I think that having support for all HTTP verbs will lead to overengineering and the added value won't be that much.