Prettify SQL for Resource Queries
- Dominant language
- No language data
- Stars
- 39
- Forks
- 1
- PR merge metrics
- No merged PRs in 30d
Description
[Resource Queries](https://github.com/ckan/ckan/pull/3816) are pretty powerful, as the data publisher has access to the full expressive power of PostgreSQL - not just a SQL dialect that cannot do joins, computed columns, aggregations, spatial queries, etc.
For example, this complex SQL to do aggregations for a viz was handled without problems using the Resource Query feature:
```sql
-- first, get total number of cases
WITH "TotalCases" AS
( SELECT COUNT(*)::NUMERIC AS "TotalCases"
FROM "b9517753-b821-4786-81eb-c94ca9be6b2d"),
-- get all closed cases
"ClosedCases" AS
( SELECT "DaysToClose"::NUMERIC AS "nDaysToClose"
FROM "b9517753-b821-4786-81eb-c94ca9be6b2d"
WHERE "DaysToClose" != 'NULL' ),
-- count all closed cases
"TotalClosedCases" AS
( SELECT COUNT(*)::NUMERIC AS "TotalClosed"
FROM "ClosedCases"),
-- count cases that were closed < 30 days
"TotalClosedCases30" AS
(SELECT COUNT(*)::NUMERIC AS "TotalClosed30"
FROM "ClosedCases"
WHERE "nDaysToClose" <= 30 ),
-- count cases that were closed between 30 and 60 days
"TotalClosedCases3060" AS
( SELECT COUNT(*)::NUMERIC AS "TotalClosed3060"
FROM "ClosedCases"
WHERE "nDaysToClose" > 30
AND "nDaysToClose" <= 60 ),
-- count cases that were closed between 60 and 90 days
"TotalClosedCases6090" AS
( SELECT COUNT(*)::NUMERIC AS "TotalClosed6090"
FROM "ClosedCases"
WHERE "nDaysToClose"> 60
AND "nDaysToClose" <= 90 ),
-- count cases that took longer than 90 days
"TotalClosedCases90" AS
( SELECT COUNT(*)::NUMERIC AS "TotalClosed90"
FROM "ClosedCases"
WHERE "nDaysToClose" > 90 )
-- compute the metrics
SELECT "TotalCases",
"TotalClosed",
("TotalCases"- "TotalClosed") AS "OpenCases",
ROUND((("TotalCases"- "TotalClosed")/"TotalCases") * 100.00, 2) AS "% Open",
"TotalClosed30",
ROUND(("TotalClosed30"/"TotalCases") * 100.00, 2) AS "% Closed < 30",
"TotalClosed3060",
ROUND(("TotalClosed3060"/"TotalCases") * 100.00, 2) AS "% Closed bet 30 and 60",
"TotalClosed6090",
ROUND(("TotalClosed6090"/"TotalCases") * 100.00, 2) AS "% Closed bet 60 and 90",
"TotalClosed90",
ROUND(("TotalClosed90"/"TotalCases") * 100.00, 2) AS "% Closed > 90"
FROM "TotalCases",
"TotalClosedCases",
"TotalClosedCases30",
"TotalClosedCases3060",
"TotalClosedCases6090",
"TotalClosedCases90"
```

In actuality, though, the SQL above was pretty-printed, and here is what it actually looks like when defining it in CKAN:

And that's after adding whitespace to make it more readable.
Would be nice if there's a FORMAT button to re-format the Resource Query, perhaps, by using something like [sqlparse](https://pypi.org/project/sqlparse/).
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by locating the CKAN Resource Query editor and reviewing how its SQL input is handled. Investigate whether sqlparse can format the query, then define the FORMAT button behavior and verify that it produces readable SQL without changing the query semantics.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sql
- Domain
- databases, frontend
- Issue type
- Feature
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100