Activiti

Add SQL Query extensions

Details

  • Type: New Feature New Feature
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 5.11
  • Component/s: Engine
  • Labels:
    None
  • Number of attachments :
    0

Description

We cannot satisfy every usecase, there will always be usecases that are not covered by the Query API.

For all use cases which are "over the line" to be included in the Query API we want to offer an SQL extension mechanism, if the resultset ranges over an activiti-entity (Task, Execution, Job, ...; In that case there are already mybatis mappings we can reference).

I think it would be best to add the possibility for the user to specify a custom SQL query:
taskService.createTaskQuery()
.sql("SELECT * FROM ACT_TASK WHERE assignee = bernd OR assignee = daniel")
.listPage(10,20);

Details are currently discussed via email

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Bernd Ruecker (camunda) added a comment -

Open aspect:

  • How to deal with configured table prefixes
Show
Bernd Ruecker (camunda) added a comment - Open aspect: How to deal with configured table prefixes
Hide
Permalink
Bernd Ruecker (camunda) added a comment - - edited

Bernd: But the schema prefix should be added by the engine when executing the query in my opinion. Otherwise I see the Multi-Tenancy support somehow half baked, as a lot of code can be used independent of what engine/tenant you use, un some code not. And table prefix is configuration of the environment, this should not make its way into the Java code…

------
Daniel: Bernd, I don't agree. I have a bad feeling about "touching" the user's query. If I provide a custom query as a user I don't expect it to be manipulated behind the scenes.

And, it might also not be a 100% trivial thing to do. What do you do if the user joins custom domain tables? Do you prefix those as well?
What do you do if the user does not read the javadoc and provides the prefix himself? Can you always recognize this? Ignore it or throw an exception...? This could go on. I am sure that there are some more corner cases that are not trivial to handle.

But there might be different solutions to your problem. You could make the prefix available via the management service. Or you could add a

managementService.prepareCustomSqlStatement(...)

Method which does your replacements. This would make the user more aware of the magic you perform.

Bernd: For me the important part is, that you can work with the table prefixes without manually coding it, so a two-step approach would be OK for me.

Show
Bernd Ruecker (camunda) added a comment - - edited Bernd: But the schema prefix should be added by the engine when executing the query in my opinion. Otherwise I see the Multi-Tenancy support somehow half baked, as a lot of code can be used independent of what engine/tenant you use, un some code not. And table prefix is configuration of the environment, this should not make its way into the Java code… ------ Daniel: Bernd, I don't agree. I have a bad feeling about "touching" the user's query. If I provide a custom query as a user I don't expect it to be manipulated behind the scenes. And, it might also not be a 100% trivial thing to do. What do you do if the user joins custom domain tables? Do you prefix those as well? What do you do if the user does not read the javadoc and provides the prefix himself? Can you always recognize this? Ignore it or throw an exception...? This could go on. I am sure that there are some more corner cases that are not trivial to handle. But there might be different solutions to your problem. You could make the prefix available via the management service. Or you could add a managementService.prepareCustomSqlStatement(...) Method which does your replacements. This would make the user more aware of the magic you perform. Bernd: For me the important part is, that you can work with the table prefixes without manually coding it, so a two-step approach would be OK for me.
Hide
Permalink
Bernd Ruecker (camunda) added a comment -

Okay, first bunch committed, now looks like this:

taskService.createNativeTaskQuery().from(managementService.getTableName(Task.class)).list();
taskService.createNativeTaskQuery().from("ACT_RU_TASK T1, ACT_RU_TASK T1").count());
taskService.createNativeTaskQuery().from(managementService.getTableName(Task.class) + " T1, "+managementService.getTableName(VariableInstanceEntity.class)+" V1 WHERE V1.TASK_ID_ = T1.ID_");

taskService.createNativeTaskQuery().select("DISTINCT T1.*").from("ACT_RU_TASK T1").list();

taskService.createNativeTaskQuery().from(managementService.getTableName(Task.class) + " T WHERE T.NAME_ = #{taskName}").parameter("taskName", "gonzoTask").count();
Show
Bernd Ruecker (camunda) added a comment - Okay, first bunch committed, now looks like this: taskService.createNativeTaskQuery().from(managementService.getTableName(Task.class)).list(); taskService.createNativeTaskQuery().from( "ACT_RU_TASK T1, ACT_RU_TASK T1" ).count()); taskService.createNativeTaskQuery().from(managementService.getTableName(Task.class) + " T1, " +managementService.getTableName(VariableInstanceEntity.class)+ " V1 WHERE V1.TASK_ID_ = T1.ID_" ); taskService.createNativeTaskQuery().select( "DISTINCT T1.*" ).from( "ACT_RU_TASK T1" ).list(); taskService.createNativeTaskQuery().from(managementService.getTableName(Task.class) + " T WHERE T.NAME_ = #{taskName}" ).parameter( "taskName" , "gonzoTask" ).count();
Hide
Permalink
Bernd Ruecker (camunda) added a comment -

Done within the Hack Days (http://www.bpm-guide.de/2012/08/16/activiti-hack-days/)

Show
Bernd Ruecker (camunda) added a comment - Done within the Hack Days ( http://www.bpm-guide.de/2012/08/16/activiti-hack-days/ )

People

  • Assignee:
    Bernd Ruecker (camunda)
    Reporter:
    Bernd Ruecker (camunda)
Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved: