Prettify SQL for Resource Queries

[jqnatividad]

Resource Queries 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:

-- 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.

[wardi]

This should be 100% CSS and javascript built on something from https://en.wikipedia.org/wiki/Comparison_of_JavaScript-based_source_code_editors

[jqnatividad]

Perhaps, #78 can be revisited to not only enable this, but to improve the UX in general when entering freetext that requires formatting.

[wardi]

That's a great idea. No reason to have two rich JS text editors