001/* 002 * Licensed to the Apache Software Foundation (ASF) under one or more 003 * contributor license agreements. See the NOTICE file distributed with 004 * this work for additional information regarding copyright ownership. 005 * The ASF licenses this file to You under the Apache License, Version 2.0 006 * (the "License"); you may not use this file except in compliance with 007 * the License. You may obtain a copy of the License at 008 * 009 * http://www.apache.org/licenses/LICENSE-2.0 010 * 011 * Unless required by applicable law or agreed to in writing, software 012 * distributed under the License is distributed on an "AS IS" BASIS, 013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 014 * See the License for the specific language governing permissions and 015 * limitations under the License. 016 */ 017package org.apache.juneau.beanquery.postgres; 018 019import static java.util.regex.Pattern.*; 020import static org.apache.juneau.commons.beanquery.SearchType.*; 021import static org.apache.juneau.commons.utils.Shorts.f; 022 023import java.util.*; 024import java.util.regex.*; 025 026import org.apache.juneau.commons.beanquery.*; 027import org.apache.juneau.beanquery.sql.*; 028 029/** 030 * The PostgreSQL {@link SqlDialect}: the one dialect Juneau ships for the shared JDBC {@link SqlBeanQueryContext}. 031 * 032 * <p> 033 * It renders the built-in {@code $}-operators (design §5.1) into Postgres predicates that mirror the in-memory 034 * semantics: exact, case-sensitive {@code $eq}/{@code $in}/{@code $ne} on text/id/enum columns, with the 035 * case-insensitive {@code $eqic} (and the case-insensitive {@code $contains}/{@code $prefix}) via {@code lower(...)} 036 * and {@code ILIKE}; full-string {@code $regex} via {@code ~}/{@code ~*} with the same {@code i}/{@code m}/{@code s} 037 * flag semantics as {@code java.util.regex}; dotted-tuple version comparison and {@code $prefix}; and typed binds for 038 * numeric/boolean/timestamp comparisons. A bare pattern maps {@code *} to {@code %} and {@code ?} to {@code _} for 039 * {@code ILIKE}, unless the literal was quoted in the search string, in which case it is matched literally. 040 * 041 * <p> 042 * Values arrive already typed (design #4 §4: {@link SearchType#parse(String)} runs during query resolution, before 043 * this dialect ever sees a value), and are always bound as {@code ?} parameters, never concatenated into SQL text. 044 * 045 * <ul class='spaced-list'> 046 * <li><b>ENUM columns</b> are compared as text: the column is cast ({@code col::text}) wherever this dialect applies a 047 * text function or operator to it, so a native Postgres {@code enum} column behaves like a {@code text} one 048 * (including bare {@code *}/{@code ?} wildcards, which match the whole constant name, case-insensitively). 049 * <li><b>VERSION columns</b> are stored as dotted text (for example {@code 10.0.1}) and compared as a tuple of integers 050 * ({@code 10.9} sorts before {@code 10.10}), like the in-memory engine. A stored value 051 * that is not a dotted sequence of integers never matches. Known, deliberate differences from the in-memory 052 * parser ({@code Integer.parseInt} over {@code split("\\.")}): each part is limited to 9 digits here (so it always 053 * fits a Postgres {@code int}; longer parts never match, where in-memory accepts up to the {@code int} range), and a 054 * trailing empty part ({@code 10.}) or a signed part ({@code +1}, {@code -1}) is rejected here but accepted in-memory. 055 * <li><b>Blank</b> ({@code $blank}, and the blank-matching {@code $ne}) means {@code NULL} or only ASCII whitespace 056 * (space, tab, line breaks, vertical tab, form feed and the C0 separators), matching {@code String.isBlank()} for 057 * ASCII; non-ASCII Unicode spaces are not trimmed. 058 * </ul> 059 * 060 * <h2 class='section'>Example:</h2> 061 * <p class='bjava'> 062 * <jc>// Register Postgres as the dialect for a JDBC-backed context.</jc> 063 * SqlBeanQueryContext<Task> <jv>ctx</jv> = SqlBeanQueryContext.<jsm>create</jsm>(Task.<jk>class</jk>) 064 * .dialect(PostgresDialect.<jsf>INSTANCE</jsf>) 065 * .table(<js>"tasks"</js>) 066 * .column(<js>"name"</js>, SearchType.<jsf>TEXT</jsf>) 067 * .column(<js>"status"</js>, SearchType.<jsf>ENUM</jsf>) 068 * .connectionSupplier(<jv>supplier</jv>) 069 * .build(); 070 * 071 * <jc>// Render a bare value directly: the value is already typed, so a NUMERIC column gets a BigDecimal bind.</jc> 072 * SqlFragment <jv>frag</jv> = PostgresDialect.<jsf>INSTANCE</jsf>.renderBare(<js>"\"age\""</js>, SearchType.<jsf>NUMERIC</jsf>, <jk>new</jk> BigDecimal(<js>"30"</js>), <jk>false</jk>); 073 * <jc>// frag.sql() == "\"age\" = ?", frag.binds() == [30]</jc> 074 * </p> 075 * 076 * @since 10.0.0 077 */ 078@SuppressWarnings({ 079 "java:S1192" // Duplicated SQL fragments read more clearly inline than as constants. 080}) 081public final class PostgresDialect implements SqlDialect { 082 083 /** The singleton instance. */ 084 public static final PostgresDialect INSTANCE = new PostgresDialect(); 085 086 private final Map<String,SearchSqlRenderer> builtins = new HashMap<>(); 087 088 private PostgresDialect() { 089 builtins.put("$eq", (d, col, type, args) -> eq(col, type, args.get(0))); 090 builtins.put("$eqic", (d, col, type, args) -> eqicFrag(col, type, args.get(0))); 091 builtins.put("$ne", (d, col, type, args) -> ne(col, type, args)); 092 builtins.put("$in", (d, col, type, args) -> in(col, type, args)); 093 builtins.put("$contains", (d, col, type, args) -> frag(col + " ILIKE ? ESCAPE '\\'", like("%", text(args.get(0)), "%"))); 094 builtins.put("$prefix", (d, col, type, args) -> prefix(col, type, args.get(0))); 095 builtins.put("$regex", (d, col, type, args) -> regex(col, args)); 096 builtins.put("$blank", (d, col, type, args) -> SqlFragment.of(blank(col))); 097 builtins.put("$gt", (d, col, type, args) -> cmp(col, " > ", type, args.get(0))); 098 builtins.put("$gte", (d, col, type, args) -> cmp(col, " >= ", type, args.get(0))); 099 builtins.put("$lt", (d, col, type, args) -> cmp(col, " < ", type, args.get(0))); 100 builtins.put("$lte", (d, col, type, args) -> cmp(col, " <= ", type, args.get(0))); 101 builtins.put("$between", (d, col, type, args) -> between(col, type, args.get(0), args.get(1))); 102 } 103 104 @Override /* SqlDialect */ 105 public String id() { 106 return "postgres"; 107 } 108 109 /** Postgres compares versions as {@code int[]} tuples (see {@link #versionArray}), matching the in-memory order. */ 110 @Override /* SqlDialect */ 111 public boolean supportsVersionOrdering() { 112 return true; 113 } 114 115 @Override /* SqlDialect */ 116 public String quote(String identifier) { 117 return '"' + identifier.replace("\"", "\"\"") + '"'; 118 } 119 120 @Override /* SqlDialect */ 121 public SearchSqlRenderer builtinRenderer(String operatorName) { 122 return builtins.get(operatorName); 123 } 124 125 @Override /* SqlDialect */ 126 public String renderLimitOffset(Long limit, Long offset) { 127 var sb = new StringBuilder(); 128 if (limit != null) 129 sb.append(" LIMIT ").append(limit.longValue()); 130 if (offset != null && offset != 0L) 131 sb.append(" OFFSET ").append(offset.longValue()); 132 return sb.toString(); 133 } 134 135 @Override /* SqlDialect */ 136 public SqlFragment renderBare(String columnSql, SearchType type, Object value, boolean quoted) { 137 switch (type) { 138 case TEXT, ID: { 139 var token = text(value); 140 if (quoted || ! hasWildcard(token)) 141 return frag(columnSql + " ILIKE ? ESCAPE '\\'", like("%", token, "%")); 142 return frag(columnSql + " ILIKE ? ESCAPE '\\'", glob(token)); 143 } 144 case ENUM: { 145 // Same rule as the in-memory engine: a wildcard pattern is a case-insensitive whole-string glob over the 146 // constant name; anything else (including a quoted literal) is case-insensitive equality. 147 var token = text(value); 148 if (quoted || ! hasWildcard(token)) 149 return eqicFrag(columnSql, type, token); 150 return frag(textOf(columnSql, type) + " ILIKE ? ESCAPE '\\'", glob(token)); 151 } 152 case VERSION: 153 return prefix(columnSql, type, value); 154 default: 155 return frag(columnSql + " = ?", value); // NUMERIC / BOOLEAN / TIMESTAMP: typed-exact; quoting is a no-op. 156 } 157 } 158 159 // ----------------------------------------------------------------------------------------------------------------- 160 // Built-in renderers 161 // ----------------------------------------------------------------------------------------------------------------- 162 163 /** Exact, case-sensitive equality (typed bind); used by {@code $eq}. */ 164 private static SqlFragment eq(String col, SearchType type, Object arg) { 165 if (type == VERSION) 166 return versionIn(col, List.of(arg)); 167 return frag(textOf(col, type) + " = ?", bind(type, arg)); 168 } 169 170 /** 171 * Case-insensitive equality; used by {@code $eqic} and the bare-token default for enum. 172 * 173 * <p> 174 * Named {@code eqicFrag} (rather than {@code eqic}) to make clear that it renders a SQL fragment, as opposed to 175 * evaluating a boolean comparison like the in-memory engine's {@code eqic} helper. 176 */ 177 private static SqlFragment eqicFrag(String col, SearchType type, Object arg) { 178 return frag("lower(" + textOf(col, type) + ") = lower(?)", text(arg)); 179 } 180 181 private static SqlFragment ne(String col, SearchType type, List<Object> args) { 182 if (type == VERSION) { 183 // Mirrors InMemoryMatch: a blank cell matches; otherwise no listed version may equal the (parseable) cell. 184 var in = versionIn(col, args); 185 var sb = new StringBuilder(blankOr(col, col)); 186 if (in.binds().isEmpty()) 187 return SqlFragment.of(sb.append(" OR 1 = 1)").toString()); 188 sb.append(" OR NOT COALESCE(").append(in.sql()).append(", FALSE))"); 189 return SqlFragment.of(sb.toString(), in.binds()); 190 } 191 var c = textOf(col, type); 192 var sb = new StringBuilder(isCiText(type) ? blankOr(col, c) : "(" + col + " IS NULL"); 193 sb.append(" OR ").append(c).append(" NOT IN ("); 194 appendPlaceholders(sb, args.size()); 195 sb.append("))"); 196 return SqlFragment.of(sb.toString(), binds(type, args)); 197 } 198 199 private static SqlFragment in(String col, SearchType type, List<Object> args) { 200 if (type == VERSION) 201 return versionIn(col, args); 202 var sb = new StringBuilder(textOf(col, type)).append(" IN ("); 203 appendPlaceholders(sb, args.size()); 204 sb.append(')'); 205 return SqlFragment.of(sb.toString(), binds(type, args)); 206 } 207 208 private static SqlFragment prefix(String col, SearchType type, Object arg) { 209 if (type == VERSION) { 210 var v = versionArg(arg); 211 if (v == null) 212 return NEVER; 213 var n = v.split("\\.").length; 214 // Array slice: a stored version with fewer parts than the prefix yields a shorter array, which never equals it. 215 return frag(versionArray(col) + "[1:" + n + "] = " + "string_to_array(?, '.')::int[]", v); 216 } 217 return frag(col + " ILIKE ? ESCAPE '\\'", like("", text(arg), "%")); 218 } 219 220 /** 221 * {@code $regex}: a full-string match with the flags of {@code InMemoryMatch}/{@code java.util.regex}. 222 * 223 * <p> 224 * Case-insensitive unless a {@code flags=} argument is given without {@code i} (then {@code ~} instead of {@code ~*}). 225 * Java's {@code m} (multiline) and {@code s} (dotall) are mapped onto Postgres's embedded regex options, which bundle 226 * newline handling differently: neither flag → {@code (?p)} ({@code .} does not match a newline, {@code ^}/{@code $} 227 * only at the string ends), {@code s} → the default, {@code m} → {@code (?n)} and both → {@code (?w)}. 228 * The pattern is anchored with {@code \A...\Z} (string boundaries, unaffected by those options) rather than 229 * {@code ^...$}, so {@code m} cannot turn the full-string match into a per-line one. 230 * 231 * <p> 232 * The pattern is first compiled with {@link java.util.regex.Pattern} using the mapped flags; a pattern that is not a valid 233 * Java regex never matches (renders as an always-false predicate), exactly like the in-memory engine, and can 234 * therefore never break out of the surrounding {@code \A(?:...)\Z} anchors (for example {@code x)|(y}). 235 * 236 * <p> 237 * <b>Flavor differences:</b> this uses PostgreSQL's POSIX {@code ~}/{@code ~*} operators (Advanced Regular 238 * Expressions), which are not Java regex. Java and PostgreSQL (ARE) regex syntax are similar but not identical (for example Java 239 * possessive quantifiers and {@code \p{L}} classes are not supported by PostgreSQL, and a Java-valid pattern may be 240 * rejected by the server, failing the query). A pathological pattern can also be slow on the server, so configure a 241 * {@code statement_timeout} for connections that run user-supplied patterns. A pattern that uses the {@code (?x)} 242 * comment flag with a trailing {@code #} comment is passed through unchanged and will be rejected by the server. 243 */ 244 private static SqlFragment regex(String col, List<Object> args) { 245 var ci = true; // Case-insensitive by default (design §5.1). 246 var multiline = false; 247 var dotall = false; 248 for (var i = 1; i < args.size(); i++) { 249 var a = text(args.get(i)).strip(); 250 if (a.startsWith("flags=")) { 251 var f = a.substring(6); // The last flags= argument wins, as in-memory. 252 ci = f.indexOf('i') >= 0; 253 multiline = f.indexOf('m') >= 0; 254 dotall = f.indexOf('s') >= 0; 255 } 256 } 257 var pattern = text(args.get(0)); 258 var javaFlags = 0; 259 if (ci) 260 javaFlags |= CASE_INSENSITIVE; 261 if (multiline) 262 javaFlags |= MULTILINE; 263 if (dotall) 264 javaFlags |= DOTALL; 265 try { 266 Pattern.compile(pattern, javaFlags); 267 if (! isPortable(pattern)) 268 return NEVER; 269 } catch (PatternSyntaxException e) { 270 return NEVER; // Invalid pattern = no match, like InMemoryMatch/RegexBudget; also keeps it from escaping the \A(?:...)\Z anchors. 271 } 272 String mods; 273 if (multiline) 274 mods = dotall ? "(?w)" : "(?n)"; 275 else 276 mods = dotall ? "" : "(?p)"; 277 return frag(col + (ci ? " ~* ?" : " ~ ?"), mods + "\\A(?:" + pattern + ")\\Z"); 278 } 279 280 private static SqlFragment cmp(String col, String operator, SearchType type, Object arg) { 281 if (type == VERSION) { 282 var v = versionArg(arg); 283 return v == null ? NEVER : frag(versionArray(col) + operator + "string_to_array(?, '.')::int[]", v); 284 } 285 return frag(col + operator + "?", arg); 286 } 287 288 private static SqlFragment between(String col, SearchType type, Object lo, Object hi) { 289 if (type == VERSION) { 290 var l = versionArg(lo); 291 var h = versionArg(hi); 292 return (l == null || h == null) ? NEVER : frag(versionArray(col) + " BETWEEN " + "string_to_array(?, '.')::int[]" + " AND " + "string_to_array(?, '.')::int[]", l, h); 293 } 294 return frag(col + " BETWEEN ? AND ?", lo, hi); 295 } 296 297 // ----------------------------------------------------------------------------------------------------------------- 298 // VERSION (stored as dotted text; compared as a tuple of integers, like InMemoryMatch.cmpVersion) 299 // ----------------------------------------------------------------------------------------------------------------- 300 301 /** The predicate for a version that can never match (an unparseable argument, like in-memory). */ 302 private static final SqlFragment NEVER = SqlFragment.of("(1 = 0)"); 303 304 /** 305 * The column as an {@code int[]}, or {@code NULL} when the stored text is not a dotted sequence of integers. 306 * Postgres compares arrays element by element and then by length, which is exactly the in-memory dotted-tuple order. 307 * 308 * <p> 309 * The guard literal is an {@code E'...'} string with doubled backslashes, so its meaning does not depend on 310 * {@code standard_conforming_strings}, and it contains no {@code ?} that pgjdbc could mistake for a placeholder. 311 */ 312 @SuppressWarnings({ 313 "java:S6353" // Deliberately ASCII [0-9]: Postgres \\d may match non-ASCII digits in some locales, which would then fail the int cast. 314 }) 315 private static String versionArray(String col) { 316 return "(CASE WHEN " + col + " ~ E'^\\\\s*[0-9]{1,9}(\\\\s*\\\\.\\\\s*[0-9]{1,9})*\\\\s*$' THEN string_to_array(" + col + ", '.')::int[] END)"; 317 } 318 319 /** A version argument normalized for {@link #VERSION_BIND}, or <jk>null</jk> if it is not a dotted sequence of integers. */ 320 private static String versionArg(Object arg) { 321 // Hand-rolled scan of ws* d{1,9} (ws* '.' ws* d{1,9})* ws* (ASCII digits/whitespace), equivalent to that regex but 322 // without a backtracking group. At most 9 digits per part, so every part fits a Postgres int (no 22003 overflow). 323 var s = text(arg); 324 var n = s.length(); 325 var parts = new ArrayList<String>(); 326 var i = skipWs(s, 0); 327 while (true) { 328 var start = i; 329 while (i < n && s.charAt(i) >= '0' && s.charAt(i) <= '9') 330 i++; 331 if (i == start || i - start > 9) 332 return null; 333 parts.add(String.valueOf(Integer.parseInt(s.substring(start, i)))); 334 i = skipWs(s, i); 335 if (i == n) 336 return String.join(".", parts); 337 if (s.charAt(i) != '.') 338 return null; 339 i = skipWs(s, i + 1); 340 } 341 } 342 343 /** Index of the first non-whitespace char at or after {@code i}; whitespace is the regex {@code \s} set (ASCII). */ 344 private static int skipWs(String s, int i) { 345 while (i < s.length() && " \t\n\u000B\f\r".indexOf(s.charAt(i)) >= 0) 346 i++; 347 return i; 348 } 349 350 /** Equality against any of the (parseable) versions; renders as never-true when none parses. */ 351 private static SqlFragment versionIn(String col, List<Object> args) { 352 var sb = new StringBuilder(); 353 var binds = new ArrayList<>(); 354 for (var a : args) { 355 var v = versionArg(a); 356 if (v == null) 357 continue; 358 sb.append(binds.isEmpty() ? "" : " OR ").append(versionArray(col)).append(" = ").append("string_to_array(?, '.')::int[]"); 359 binds.add(v); 360 } 361 if (binds.isEmpty()) 362 return NEVER; 363 return SqlFragment.of(binds.size() > 1 ? "(" + sb + ")" : sb.toString(), binds); 364 } 365 366 // ----------------------------------------------------------------------------------------------------------------- 367 // Helpers 368 // ----------------------------------------------------------------------------------------------------------------- 369 370 /** The cell is {@code NULL} or ASCII-whitespace only, like the in-memory blank test. */ 371 private static String blank(String col) { 372 return blankOr(col, col) + ")"; 373 } 374 375 /** Opens a parenthesized disjunction with the blank test for {@code textExpr}; the caller appends the rest and closes it. */ 376 private static String blankOr(String col, String textExpr) { 377 return "(" + col + " IS NULL OR btrim(" + textExpr + ", " + "E' \\t\\n\\x0B\\f\\r\\x1C\\x1D\\x1E\\x1F'" + ") = ''"; 378 } 379 380 private static boolean isCiText(SearchType type) { 381 return type == TEXT || type == ID || type == ENUM; 382 } 383 384 private static boolean hasWildcard(String token) { 385 return token.indexOf('*') >= 0 || token.indexOf('?') >= 0; 386 } 387 388 /** A bare glob ({@code *} any run, {@code ?} one character) as a LIKE pattern, with LIKE metacharacters escaped first. */ 389 private static String glob(String token) { 390 return escapeLike(token).replace("*", "%").replace("?", "_"); 391 } 392 393 /** The column as text: an ENUM column may be a native Postgres enum, which has no text operators of its own. */ 394 private static String textOf(String col, SearchType type) { 395 return type == ENUM ? col + "::text" : col; 396 } 397 398 /** Text-like columns bind strings; everything else binds the already-typed value. */ 399 private static Object bind(SearchType type, Object arg) { 400 return isCiText(type) ? text(arg) : arg; 401 } 402 403 private static List<Object> binds(SearchType type, List<Object> args) { 404 return args.stream().map(a -> bind(type, a)).toList(); 405 } 406 407 private static void appendPlaceholders(StringBuilder sb, int count) { 408 for (var i = 0; i < count; i++) { 409 if (i > 0) 410 sb.append(", "); 411 sb.append('?'); 412 } 413 } 414 415 /** 416 * Returns whether a regex uses only constructs with the same meaning in Java and PostgreSQL (ARE): literals, classes, 417 * groups, alternation, greedy/lazy quantifiers, anchors, back-references and the {@code \d \w \s} shorthands. 418 * Anything that differs between the flavors (possessive quantifiers, inline flags, lookbehind, named or atomic groups, 419 * {@code \p} classes, {@code \b}, {@code \Q...\E}, class intersections and POSIX bracket expressions) makes the 420 * pattern non-portable, so it matches nothing instead of being interpreted differently by the server. 421 */ 422 @SuppressWarnings({ 423 "java:S3776" // Branching is inherent to the regex scanner. 424 }) 425 static boolean isPortable(String p) { 426 var inClass = false; 427 var n = p.length(); 428 var i = 0; 429 while (i < n) { 430 var c = p.charAt(i); 431 if (c == '\\') { 432 i++; 433 if (i >= n || "pPbBQEhHRXGzZAkNvVuxc0".indexOf(p.charAt(i)) >= 0) 434 return false; 435 } else if (inClass) { 436 if (c == ']') 437 inClass = false; 438 else if (c == '[' || (c == '&' && i + 1 < n && p.charAt(i + 1) == '&')) 439 return false; 440 } else if (c == '[') { 441 inClass = true; 442 if (i + 1 < n && p.charAt(i + 1) == '^') 443 i++; 444 if (i + 1 < n && p.charAt(i + 1) == ']') 445 i++; 446 } else if (c == '(' && i + 1 < n && p.charAt(i + 1) == '?') { 447 if (i + 2 >= n || ":=!".indexOf(p.charAt(i + 2)) < 0) 448 return false; 449 } else if ("*+?}".indexOf(c) >= 0 && i + 1 < n && p.charAt(i + 1) == '+') { 450 return false; 451 } 452 i++; 453 } 454 return ! inClass; 455 } 456 457 /** Escapes LIKE/ILIKE metacharacters (backslash is the default Postgres LIKE escape). */ 458 private static String escapeLike(String s) { 459 return s.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_"); 460 } 461 462 private static String like(String pre, String s, String post) { 463 return pre + escapeLike(s) + post; 464 } 465 466 /** 467 * Asserts that a value on a text-only path is a {@link String}. 468 * 469 * <p> 470 * Values for TEXT / ID / ENUM / VERSION columns are always {@link String}s (design #4 §4: {@link SearchType#parse} 471 * leaves them unchanged), so a non-string here is an engine bug, not a user error. 472 * 473 * @param o The value. 474 * @return The value as a string. 475 * @throws IllegalStateException If the value is not a {@link String}. 476 */ 477 private static String text(Object o) { 478 if (o instanceof String s) 479 return s; 480 throw new IllegalStateException(f("Expected a String value for a text comparison but got '%s'.", o == null ? null : o.getClass().getName())); // I believe we have a short for getting class name? 481 } 482 483 private static SqlFragment frag(String sql, Object...binds) { 484 return SqlFragment.of(sql, binds); 485 } 486}