Closed richb-hanover closed 3 weeks ago
ryanhamilton
commented
3 weeks ago On my qStudio this compiles but then sqlite fails as it doesn't support REGEXP: https://github.com/xerial/sqlite-jdbc/issues/60 I'm not sure how to fix that REGEXP issue as it really seems like the JDBC driver should handle that?
Can you try pasting the PRQL into your specific prqlc that you use for qStudio?
richb-hanover
commented
3 weeks ago Good troubleshooting tip. I'll let you know how it turns out.
richb-hanover
commented
3 weeks ago Here's the output from prqlc on my computer. A couple thoughts:
target statement at the top. So the prql target:sql.sqlite in the _Non-ConfBuildings.prql should handle SQLite's lack of a builtin regex. I did paste the code below into DB4S and it worked fine.prqlc always includes a comment as the final line showing the version of the compiler. In the debugging output of the java -jar qstudio.jar command, I don't think I've seen that line in the SQL that was sent to the database. Thanks for all this good work!
√ PRQL_Queries % which prqlc
/usr/local/bin/prqlc
√ PRQL_Queries % prqlc --version
prqlc 0.11.3
√ PRQL_Queries % prqlc < Non-conf_Buildings.prql compile
WITH table_3 AS (
SELECT
COUNT(*) AS "NumBldgs",
MAX(("BL_Gross_Floor_Area" * 1.0 / "BL_Stories")) AS "BiggestBldgFprt",
COALESCE(
SUM(("BL_Gross_Floor_Area" * 1.0 / "BL_Stories")),
0
) AS "TotalBldgFprt",
COALESCE(SUM("BL_Gross_Floor_Area"), 0) AS "TotalGFA",
"BL_PID"
FROM
buildings
GROUP BY
"BL_PID"
),
table_2 AS (
SELECT
table_3."NumBldgs",
table_3."BiggestBldgFprt",
table_3."TotalBldgFprt",
table_3."TotalGFA",
sd."SD_Lot_Size",
sd."SD_Street_Address",
sd."SD_PID",
sd."SD_Zoning_District"
FROM
table_3
JOIN "ScrapedData" AS sd ON table_3."BL_PID" = sd."SD_PID"
WHERE
sd."SD_Version" = 14
AND sd."SD_Lot_Size" <> 0
AND NOT sd."SD_Description" REGEXP 'CONDO'
AND table_3."BiggestBldgFprt" IS NOT NULL
),
table_1 AS (
SELECT
table_2."NumBldgs",
table_2."BiggestBldgFprt",
table_2."TotalBldgFprt",
table_2."TotalGFA",
table_2."SD_Lot_Size",
table_2."SD_Street_Address",
table_2."SD_PID",
CASE
WHEN table_2."SD_Street_Address" REGEXP 'ORFORD ' THEN 1
WHEN table_2."SD_Street_Address" REGEXP 'DARTMOUTH COLLEGE' THEN 1
WHEN table_2."SD_Street_Address" REGEXP 'DORCHESTER' THEN 1
WHEN table_2."SD_Street_Address" REGEXP 'EAST THETFORD' THEN 1
WHEN table_2."SD_Street_Address" REGEXP 'NORTH THETFORD' THEN 1
ELSE 0
END AS _expr_2,
CASE
WHEN table_2."SD_Zoning_District" = 'ES' THEN 'RD'
WHEN table_2."SD_Zoning_District" = 'R' THEN 'RD'
WHEN table_2."SD_Zoning_District" = 'SFR' THEN 'RD'
WHEN table_2."SD_Zoning_District" = 'URD' THEN 'RD'
WHEN table_2."SD_Zoning_District" = 'LCD' THEN 'LCD'
WHEN table_2."SD_Zoning_District" = 'CD' THEN 'LCD'
WHEN table_2."SD_Zoning_District" = 'LDC' THEN 'LCD'
WHEN table_2."SD_Zoning_District" = 'SD' THEN 'RD'
WHEN table_2."SD_Zoning_District" = '' THEN NULL
ELSE table_2."SD_Zoning_District"
END AS _expr_3,
czd."CZD_Actual",
table_2."SD_Zoning_District"
FROM
table_2
LEFT JOIN "CorrectedZoningDistrict" AS czd ON table_2."SD_PID" = czd."CZD_PID"
),
table_0 AS (
SELECT
"NumBldgs",
"BiggestBldgFprt",
"TotalBldgFprt",
"TotalGFA",
CASE
WHEN COALESCE(_expr_3, "CZD_Actual") = 'RD'
AND _expr_2 = 1 THEN 'Rural-State Road'
WHEN COALESCE(_expr_3, "CZD_Actual") = 'RD'
AND _expr_2 = 0 THEN 'Rural-Town Road'
WHEN COALESCE(_expr_3, "CZD_Actual") = 'BD' THEN 'Commercial'
WHEN COALESCE(_expr_3, "CZD_Actual") = 'LCD' THEN 'LymeCommon/Ctr'
WHEN COALESCE(_expr_3, "CZD_Actual") = 'ELD' THEN 'EastLyme'
WHEN COALESCE(_expr_3, "CZD_Actual") = 'SKIWAY' THEN 'Skiway'
WHEN COALESCE(_expr_3, "CZD_Actual") = 'MFD' THEN 'MtnForest'
ELSE COALESCE(_expr_3, "CZD_Actual")
END AS _expr_0,
"SD_Lot_Size",
"SD_Street_Address",
"SD_PID",
COALESCE(_expr_3, "CZD_Actual") AS _expr_1,
_expr_2
FROM
table_1
)
SELECT
"SD_PID" AS "PID",
"SD_Street_Address" AS "Street Address",
_expr_0 AS "ZoningDistr",
"SD_Lot_Size" AS "LotSize",
"NumBldgs",
CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6.0
WHEN _expr_0 = 'Commercial' THEN 10.0
WHEN _expr_0 = 'Rural-State Road' THEN 2.0
WHEN _expr_0 = 'Rural-Town Road' THEN 2.0
WHEN _expr_0 = 'EastLyme' THEN 1.0
WHEN _expr_0 = 'Skiway' THEN 1.0
WHEN _expr_0 = 'MtnForest' THEN 1.0
ELSE '?'
END AS "MaxAllowedFprtPct",
"BiggestBldgFprt",
(
"SD_Lot_Size" * 43560 * CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6.0
WHEN _expr_0 = 'Commercial' THEN 10.0
WHEN _expr_0 = 'Rural-State Road' THEN 2.0
WHEN _expr_0 = 'Rural-Town Road' THEN 2.0
WHEN _expr_0 = 'EastLyme' THEN 1.0
WHEN _expr_0 = 'Skiway' THEN 1.0
WHEN _expr_0 = 'MtnForest' THEN 1.0
ELSE '?'
END * 1.0 / 100.0
) AS "MaxAllowedBldg",
"BiggestBldgFprt" > (
"SD_Lot_Size" * 43560 * CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6.0
WHEN _expr_0 = 'Commercial' THEN 10.0
WHEN _expr_0 = 'Rural-State Road' THEN 2.0
WHEN _expr_0 = 'Rural-Town Road' THEN 2.0
WHEN _expr_0 = 'EastLyme' THEN 1.0
WHEN _expr_0 = 'Skiway' THEN 1.0
WHEN _expr_0 = 'MtnForest' THEN 1.0
ELSE '?'
END * 1.0 / 100.0
) AS "BldgNonConforming",
"TotalBldgFprt",
CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
ELSE 0
END AS "DrivewayFprt",
"TotalBldgFprt" + CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
ELSE 0
END AS "LotCoverageSF",
(
(
"TotalBldgFprt" + CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
ELSE 0
END
) * 100.0 * 1.0 / (43560 * "SD_Lot_Size")
) AS "LotCoveragePct",
CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 12.0
WHEN _expr_0 = 'Commercial' THEN 20.0
WHEN _expr_0 = 'Rural-State Road' THEN 12.0
WHEN _expr_0 = 'Rural-Town Road' THEN 12.0
WHEN _expr_0 = 'EastLyme' THEN 12.0
WHEN _expr_0 = 'Skiway' THEN 2.0
WHEN _expr_0 = 'MtnForest' THEN 2.0
ELSE '?'
END AS "MaxAllowedLotCoverage",
(
(
"TotalBldgFprt" + CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
ELSE 0
END
) * 100.0 * 1.0 / (43560 * "SD_Lot_Size")
) > CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 12.0
WHEN _expr_0 = 'Commercial' THEN 20.0
WHEN _expr_0 = 'Rural-State Road' THEN 12.0
WHEN _expr_0 = 'Rural-Town Road' THEN 12.0
WHEN _expr_0 = 'EastLyme' THEN 12.0
WHEN _expr_0 = 'Skiway' THEN 2.0
WHEN _expr_0 = 'MtnForest' THEN 2.0
ELSE '?'
END AS "CoverageNonConforming",
"TotalGFA",
CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6000.0
WHEN _expr_0 = 'Commercial' THEN 14000.0
WHEN _expr_0 = 'Rural-State Road' THEN 14000.0
WHEN _expr_0 = 'Rural-Town Road' THEN 14000.0
WHEN _expr_0 = 'EastLyme' THEN 14000.0
WHEN _expr_0 = 'Skiway' THEN 14000.0
WHEN _expr_0 = 'MtnForest' THEN 14000.0
ELSE '?'
END AS "MaxAllowedGFA",
"TotalGFA" > CASE
WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6000.0
WHEN _expr_0 = 'Commercial' THEN 14000.0
WHEN _expr_0 = 'Rural-State Road' THEN 14000.0
WHEN _expr_0 = 'Rural-Town Road' THEN 14000.0
WHEN _expr_0 = 'EastLyme' THEN 14000.0
WHEN _expr_0 = 'Skiway' THEN 14000.0
WHEN _expr_0 = 'MtnForest' THEN 14000.0
ELSE '?'
END AS "GFANonConforming"
FROM
table_0
ORDER BY
"PID"
-- Generated by PRQL compiler version:0.11.3 (https://prql-lang.org)
√ PRQL_Queries %Ahah! I know more now... The query is gagging on the REGEXP call.
SQLite doesn't bundle the REGEXP facility by default. The docs at https://www.sqlite.org/lang_expr.html explain:
The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".
https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query gives many hints. Also check out https://github.com/nalgeon/sqlean/tree/main
ryanhamilton
commented
3 weeks ago That's frustrating. It seems either:
Either way I'm not sure there's much qStudio should do. I have added logic to detct "prql target:" and to avoid qStudio sending a target if it's in the code. The logic / command is now:
String target = getPrqlTarget(jdbcTypes);
String[] commands = new String[] { "prqlc","compile", "--hide-signature-comment", "--color", "never" };
if(target != null && !qry.contains("prql target:")) {
commands = new String[] { "prqlc","compile", "--hide-signature-comment", "--color", "never", "--target", target };
}Frustrating, indeed. Another thought (although I confess that I don't really understand all the layers of this...) Googling "sqlite jdbc regexp" brings up this StackOverflow article: https://stackoverflow.com/a/28587200/1827982
Does this give you any useful info? Many thanks...
ryanhamilton
commented
3 weeks ago Oh I kind of see how it could be done, I'm just super reluctant to start owning that code. qStudio tries to support 30+ databases, the only hope of doing that is by pushing most the work to them. :( How about we raise an issue on sqlean asking for a JDBC driver?
richb-hanover
commented
3 weeks ago Would that make your life straightforward? (I totally understand not owning all kinds of code...)
Is raising an issue over there something you could do? Thanks.
ryanhamilton
commented
3 weeks ago Raised in both places that I think it could or should be fixed. Closing here.
richb-hanover
commented
3 weeks ago Workaround: It's not perfect, but for many situations, all you need is a LIKE %string% feature. Here's an implementation of like that is a bit of a crock (it uses S-strings) but it works:
# implement a "like" function in lieu of a regex
let like = fld str -> s"{fld} like '%' || {str} || '%' "
from foo
select bar
filter (like bar "stuff")Changing all the ~= operators/comparisons to use like allows the query to run correctly.
It would still be terrific to bundle in a SQLite version that supports REGEXP...
Workaround found: https://github.com/timeseries/qstudio/issues/50#issuecomment-2151249068
Using qStudio 3.03 on macOS 12.7.5 with Java 16.0.1. I can open the Property in Lyme.sqlite database and use short PRQL queries. For example this query returns six substantially identical rows with different dates:
But if I try to execute a longer query (for example, Non-conf_Buildings.prql) I get the following error.
I will note that all three PRQL reference implementations (PRQL Playground 0.11.3, PRQL VSCode extension 0.11.3, and PRQL DevContainer 0.11.5) do not give this error and generate identical SQL code for this query.
This might be a
prqlcproblem - but I'm reporting it here. What other information could I provide? Thanks.