Project

General

Profile

Actions
Copy link

Task #3054

closed

Implement generic SQL report

Added by Tomáš Doischer almost 3 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Roman Kučera
Target version:
3.0.0
Start date:
02/11/2022
Due date:
% Done:

100%

Estimated time:
Owner:

Description

Allow the user to get result of an SQL querry. Needs to be sanitized and only the admin can run this report.

Actions
Copy link
 #1

Updated by Tomáš Doischer almost 3 years ago

  • Sprint set to Sprint 12.2-1 (úno 18 - bře 02)
Actions
Copy link
 #2

Updated by Tomáš Doischer almost 3 years ago

  • Assignee set to Tomáš Doischer
Actions
Copy link
 #3

Updated by Tomáš Doischer almost 3 years ago

  • Sprint changed from Sprint 12.2-1 (úno 18 - bře 02) to Sprint 12.2-2 (bře 02 - bře 16)
Actions
Copy link
 #4

Updated by Roman Kučera almost 3 years ago

  • Assignee changed from Tomáš Doischer to Roman Kučera
Actions
Copy link
 #5

Updated by Roman Kučera almost 3 years ago

  • Status changed from New to In Progress
Actions
Copy link
 #6

Updated by Roman Kučera almost 3 years ago

  • % Done changed from 0 to 60

First implementation is done.
Report is working.
You can fill in table/view name and then you will get data based on that table/view
you can fill some sql code in so then you get data based on that sql code.

Next thing which I want to improve is sql syntax highlight. I will look into if is something simple or not.
implemented in https://git.bcvsolutions.eu/modules/reports/-/commits/kucerar/3054-generic-sql-report

Actions
Copy link
 #7

Updated by Roman Kučera over 2 years ago

  • Status changed from In Progress to Needs feedback
  • Assignee changed from Roman Kučera to Tomáš Doischer
  • % Done changed from 60 to 90

Syntax highlight will not be support right now.
I added description to params, so now it's obvious how to use this report even without documentation or without trying it out.

PR https://git.bcvsolutions.eu/modules/reports/-/merge_requests/3
@doischert can you do a review please?

Actions
Copy link
 #8

Updated by Roman Kučera over 2 years ago

  • Status changed from Needs feedback to In Progress
  • Assignee changed from Tomáš Doischer to Roman Kučera

We are waiting for multiple datasource support in product

Actions
Copy link
 #9

Updated by Roman Kučera over 2 years ago

added check, so the report can be executed only uf you are super admin

Actions
Copy link
 #10

Updated by Roman Kučera over 2 years ago

Connection was not closed, so I fixed it.
Added configuration for datasource

Add this to IdM property file

spring.reports-datasource.jdbcUrl=jdbc:postgresql://localhost:5432/hr
spring.reports-datasource.username=postgres
spring.reports-datasource.password=pass
spring.reports-datasource.driver-class-name=org.postgresql.Driver
spring.reports-datasource.testOnBorrow=true
spring.reports-datasource.validationQuery=SELECT 1
spring.reports-datasource.maximum-pool-size=1
spring.reports-datasource.read-only=true

Actions
Copy link
 #11

Updated by Tomáš Doischer over 2 years ago

  • % Done changed from 90 to 70

I reviewed the feature, the selects work well, well done.

I have a couple of notes in the gitlab review (they're all minor). However, during testing, I found that you can run delete and update queries as well. The report will fail because no data was returned but the query is executed anyway.

Also, documentation is needed.

Actions
Copy link
 #12

Updated by Roman Kučera over 2 years ago

  • % Done changed from 70 to 80

I improved the mentioned things.

Next I will write documentation.

Actions
Copy link
 #14

Updated by Tomáš Doischer over 2 years ago

  • Status changed from In Progress to Resolved
  • Assignee changed from Roman Kučera to Tomáš Doischer
  • % Done changed from 80 to 100

Thank you, LGTM, merged to develop. I slightly touched the documentation, added some warnings and some explanation to the configuration properties.

Actions
Copy link
 #15

Updated by Tomáš Doischer over 2 years ago

  • Status changed from Resolved to Closed
  • Assignee changed from Tomáš Doischer to Roman Kučera
Actions
Copy link

Also available in: Atom PDF