ckan / ckan/ideas

Prettify SQL for Resource Queries

Open
#222 3 comments 0 reactions 0 assignees View on GitHub
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"
```
![image](https://user-images.githubusercontent.com/1980690/45514284-f33fce80-b772-11e8-9d6c-13c771a555bf.png)

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

![image](https://user-images.githubusercontent.com/1980690/45514425-4ade3a00-b773-11e8-8d8d-7db0765d8b62.png)

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.