001/* 002 * (C) Copyright 2006-2018 Nuxeo (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Florent Guillaume 018 * Benoit Delbosc 019 */ 020package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 021 022import java.io.Serializable; 023import java.sql.Array; 024import java.sql.Connection; 025import java.sql.DatabaseMetaData; 026import java.sql.PreparedStatement; 027import java.sql.ResultSet; 028import java.sql.SQLException; 029import java.sql.Statement; 030import java.sql.Timestamp; 031import java.sql.Types; 032import java.util.ArrayList; 033import java.util.Arrays; 034import java.util.Collections; 035import java.util.HashMap; 036import java.util.HashSet; 037import java.util.LinkedList; 038import java.util.List; 039import java.util.Map; 040import java.util.Map.Entry; 041import java.util.Set; 042import java.util.UUID; 043import java.util.regex.Matcher; 044import java.util.regex.Pattern; 045import java.util.stream.Collectors; 046 047import org.apache.commons.lang3.StringUtils; 048import org.apache.commons.logging.Log; 049import org.apache.commons.logging.LogFactory; 050import org.nuxeo.ecm.core.NXCore; 051import org.nuxeo.ecm.core.api.NuxeoException; 052import org.nuxeo.ecm.core.api.repository.FulltextConfiguration; 053import org.nuxeo.ecm.core.api.security.SecurityConstants; 054import org.nuxeo.ecm.core.query.QueryParseException; 055import org.nuxeo.ecm.core.security.SecurityService; 056import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer; 057import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery; 058import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.Op; 059import org.nuxeo.ecm.core.storage.sql.ColumnType; 060import org.nuxeo.ecm.core.storage.sql.Model; 061import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 062import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 063import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 064import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 065import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 066import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 067import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableAlias; 068 069/** 070 * PostgreSQL-specific dialect. 071 * 072 * @author Florent Guillaume 073 */ 074public class DialectPostgreSQL extends Dialect { 075 076 private static final Log log = LogFactory.getLog(DialectPostgreSQL.class); 077 078 private static final String DEFAULT_FULLTEXT_ANALYZER = "english"; 079 080 private static final String DEFAULT_USERS_SEPARATOR = ","; 081 082 private static final String PREFIX_SEARCH = ":*"; 083 084 // prefix search syntax foo* or foo% or foo:*-> foo:* 085 private static final Pattern PREFIX_PATTERN = Pattern.compile("(\\*|%|:\\*)( |\"|$)"); 086 087 private static final String PREFIX_REPL = PREFIX_SEARCH + "$2"; 088 089 private static final String[] RESERVED_COLUMN_NAMES = { "xmin", "xmax", "cmin", "cmax", "ctid", "oid", "tableoid" }; 090 091 private static final String UNLOGGED_KEYWORD = "UNLOGGED"; 092 093 protected final String fulltextAnalyzer; 094 095 protected final boolean supportsWith; 096 097 protected boolean hierarchyCreated; 098 099 protected boolean pathOptimizationsEnabled; 100 101 protected final boolean arrayColumnsEnabled; 102 103 protected final boolean childNameUniqueConstraintEnabled; 104 105 protected final boolean collectionUniqueConstraintEnabled; 106 107 protected String usersSeparator; 108 109 protected final DialectIdType idType; 110 111 protected boolean compatibilityFulltextTable; 112 113 protected final String unloggedKeyword; 114 115 protected String idSequenceName; 116 117 public DialectPostgreSQL(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 118 super(metadata, repositoryDescriptor); 119 fulltextAnalyzer = repositoryDescriptor == null ? null 120 : repositoryDescriptor.getFulltextAnalyzer() == null ? DEFAULT_FULLTEXT_ANALYZER 121 : repositoryDescriptor.getFulltextAnalyzer(); 122 pathOptimizationsEnabled = repositoryDescriptor != null && repositoryDescriptor.getPathOptimizationsEnabled(); 123 if (repositoryDescriptor != null) { 124 log.info("Path optimizations " + (pathOptimizationsEnabled ? "enabled" : "disabled")); 125 } 126 arrayColumnsEnabled = repositoryDescriptor != null && repositoryDescriptor.getArrayColumns(); 127 childNameUniqueConstraintEnabled = repositoryDescriptor != null 128 && repositoryDescriptor.getChildNameUniqueConstraintEnabled(); 129 collectionUniqueConstraintEnabled = repositoryDescriptor != null 130 && repositoryDescriptor.getCollectionUniqueConstraintEnabled(); 131 int major, minor; 132 try { 133 major = metadata.getDatabaseMajorVersion(); 134 minor = metadata.getDatabaseMinorVersion(); 135 } catch (SQLException e) { 136 throw new NuxeoException(e); 137 } 138 supportsWith = major > 8 || (major == 8 && minor >= 4); 139 if ((major == 9 && minor >= 1) || (major > 9)) { 140 unloggedKeyword = UNLOGGED_KEYWORD; 141 } else { 142 unloggedKeyword = ""; 143 } 144 usersSeparator = repositoryDescriptor == null ? null 145 : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR 146 : repositoryDescriptor.usersSeparatorKey; 147 String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType; 148 if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) { 149 idType = DialectIdType.VARCHAR; 150 } else if ("uuid".equalsIgnoreCase(idt)) { 151 idType = DialectIdType.UUID; 152 } else if (idt.toLowerCase().startsWith("sequence")) { 153 idType = DialectIdType.SEQUENCE; 154 if (idt.toLowerCase().startsWith("sequence:")) { 155 String[] split = idt.split(":"); 156 idSequenceName = split[1]; 157 } else { 158 idSequenceName = "hierarchy_seq"; 159 } 160 } else { 161 throw new NuxeoException("Unknown id type: '" + idt + "'"); 162 } 163 try { 164 compatibilityFulltextTable = getCompatibilityFulltextTable(metadata); 165 } catch (SQLException e) { 166 throw new NuxeoException(e); 167 } 168 } 169 170 protected boolean getCompatibilityFulltextTable(DatabaseMetaData metadata) throws SQLException { 171 ResultSet rs = metadata.getColumns(null, null, Model.FULLTEXT_TABLE_NAME, "%"); 172 while (rs.next()) { 173 // COLUMN_NAME=fulltext DATA_TYPE=1111 TYPE_NAME=tsvector 174 String columnName = rs.getString("COLUMN_NAME"); 175 if (Model.FULLTEXT_FULLTEXT_KEY.equals(columnName)) { 176 String typeName = rs.getString("TYPE_NAME"); 177 return "tsvector".equals(typeName); 178 } 179 } 180 return false; 181 } 182 183 @Override 184 public String toBooleanValueString(boolean bool) { 185 return bool ? "true" : "false"; 186 } 187 188 @Override 189 public String getNoColumnsInsertString(Column idColumn) { 190 return "DEFAULT VALUES"; 191 } 192 193 @Override 194 public String getCascadeDropConstraintsString() { 195 return "CASCADE"; 196 } 197 198 @Override 199 public JDBCInfo getJDBCTypeAndString(ColumnType type) { 200 switch (type.spec) { 201 case STRING: 202 if (type.isUnconstrained()) { 203 return jdbcInfo("varchar", Types.VARCHAR); 204 } else if (type.isClob()) { 205 return jdbcInfo("text", Types.CLOB); 206 } else { 207 return jdbcInfo("varchar(%d)", type.length, Types.VARCHAR); 208 } 209 case ARRAY_STRING: 210 if (type.isUnconstrained()) { 211 return jdbcInfo("varchar[]", Types.ARRAY, "varchar", Types.VARCHAR); 212 } else if (type.isClob()) { 213 return jdbcInfo("text[]", Types.ARRAY, "text", Types.CLOB); 214 } else { 215 return jdbcInfo("varchar(%d)[]", type.length, Types.ARRAY, "varchar", Types.VARCHAR); 216 } 217 case BOOLEAN: 218 return jdbcInfo("bool", Types.BIT); 219 case ARRAY_BOOLEAN: 220 return jdbcInfo("bool[]", Types.ARRAY, "bool", Types.BOOLEAN); 221 case LONG: 222 return jdbcInfo("int8", Types.BIGINT); 223 case ARRAY_LONG: 224 return jdbcInfo("int8[]", Types.ARRAY, "int8", Types.BIGINT); 225 case DOUBLE: 226 return jdbcInfo("float8", Types.DOUBLE); 227 case ARRAY_DOUBLE: 228 return jdbcInfo("float8[]", Types.ARRAY, "float8", Types.DOUBLE); 229 case TIMESTAMP: 230 return jdbcInfo("timestamp", Types.TIMESTAMP); 231 case ARRAY_TIMESTAMP: 232 return jdbcInfo("timestamp[]", Types.ARRAY, "timestamp", Types.TIMESTAMP); 233 case BLOBID: 234 return jdbcInfo("varchar(250)", Types.VARCHAR); 235 case ARRAY_BLOBID: 236 return jdbcInfo("varchar(250)[]", Types.ARRAY, "varchar", Types.VARCHAR); 237 case BLOB: 238 return jdbcInfo("bytea", Types.BINARY); 239 // ----- 240 case NODEID: 241 case NODEIDFK: 242 case NODEIDFKNP: 243 case NODEIDFKMUL: 244 case NODEIDFKNULL: 245 case NODEIDPK: 246 case NODEVAL: 247 switch (idType) { 248 case VARCHAR: 249 return jdbcInfo("varchar(36)", Types.VARCHAR); 250 case UUID: 251 return jdbcInfo("uuid", Types.OTHER); 252 case SEQUENCE: 253 return jdbcInfo("int8", Types.BIGINT); 254 } 255 throw new AssertionError("Unknown id type: " + idType); 256 case NODEARRAY: 257 switch (idType) { 258 case VARCHAR: 259 return jdbcInfo("varchar(36)[]", Types.ARRAY, "varchar", Types.VARCHAR); 260 case UUID: 261 return jdbcInfo("uuid[]", Types.ARRAY, "uuid", Types.OTHER); 262 case SEQUENCE: 263 return jdbcInfo("int8[]", Types.ARRAY, "int8", Types.BIGINT); 264 } 265 throw new AssertionError("Unknown id type: " + idType); 266 case SYSNAME: 267 return jdbcInfo("varchar(250)", Types.VARCHAR); 268 case SYSNAMEARRAY: 269 return jdbcInfo("varchar(250)[]", Types.ARRAY, "varchar", Types.VARCHAR); 270 case TINYINT: 271 return jdbcInfo("int2", Types.SMALLINT); 272 case INTEGER: 273 return jdbcInfo("int4", Types.INTEGER); 274 case ARRAY_INTEGER: 275 return jdbcInfo("int4[]", Types.ARRAY, "int4", Types.INTEGER); 276 case AUTOINC: 277 return jdbcInfo("serial", Types.INTEGER); 278 case FTINDEXED: 279 if (compatibilityFulltextTable) { 280 return jdbcInfo("tsvector", Types.OTHER); 281 } else { 282 return jdbcInfo("text", Types.CLOB); 283 } 284 case FTSTORED: 285 if (compatibilityFulltextTable) { 286 return jdbcInfo("tsvector", Types.OTHER); 287 } else { 288 return jdbcInfo("text", Types.CLOB); 289 } 290 case CLUSTERNODE: 291 return jdbcInfo("int4", Types.INTEGER); 292 case CLUSTERFRAGS: 293 return jdbcInfo("varchar[]", Types.ARRAY, "varchar", Types.VARCHAR); 294 } 295 throw new AssertionError(type); 296 } 297 298 @Override 299 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 300 // CLOB vs VARCHAR compatibility 301 if (expected == Types.VARCHAR && actual == Types.CLOB) { 302 return true; 303 } 304 if (expected == Types.CLOB && actual == Types.VARCHAR) { 305 return true; 306 } 307 // INTEGER vs BIGINT compatibility 308 if (expected == Types.BIGINT && actual == Types.INTEGER) { 309 return true; 310 } 311 if (expected == Types.INTEGER && actual == Types.BIGINT) { 312 return true; 313 } 314 // TSVECTOR vs CLOB compatibility during upgrade tests 315 // where column detection is done before upgrade test setup 316 if (expected == Types.CLOB && (actual == Types.OTHER && actualName.equals("tsvector"))) { 317 return true; 318 } 319 return false; 320 } 321 322 @Override 323 public Serializable getGeneratedId(Connection connection) throws SQLException { 324 if (idType != DialectIdType.SEQUENCE) { 325 return super.getGeneratedId(connection); 326 } 327 String sql = String.format("SELECT NEXTVAL('%s')", idSequenceName); 328 try (Statement s = connection.createStatement()) { 329 try (ResultSet rs = s.executeQuery(sql)) { 330 rs.next(); 331 return Long.valueOf(rs.getLong(1)); 332 } 333 } 334 } 335 336 @Override 337 public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException { 338 switch (idType) { 339 case VARCHAR: 340 ps.setObject(index, value); 341 break; 342 case UUID: 343 ps.setObject(index, value, Types.OTHER); 344 break; 345 case SEQUENCE: 346 setIdLong(ps, index, value); 347 break; 348 default: 349 throw new AssertionError(); 350 } 351 } 352 353 @SuppressWarnings("boxing") 354 public Serializable getId(ResultSet rs, int index) throws SQLException { 355 switch (idType) { 356 case VARCHAR: 357 case UUID: 358 return rs.getString(index); 359 case SEQUENCE: 360 return rs.getLong(index); 361 default: 362 throw new AssertionError(); 363 } 364 } 365 366 @Override 367 public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 368 throws SQLException { 369 switch (column.getJdbcType()) { 370 case Types.VARCHAR: 371 case Types.CLOB: 372 setToPreparedStatementString(ps, index, value, column); 373 return; 374 case Types.BIT: 375 ps.setBoolean(index, ((Boolean) value).booleanValue()); 376 return; 377 case Types.SMALLINT: 378 ps.setInt(index, ((Long) value).intValue()); 379 return; 380 case Types.INTEGER: 381 case Types.BIGINT: 382 ps.setLong(index, ((Number) value).longValue()); 383 return; 384 case Types.DOUBLE: 385 ps.setDouble(index, ((Double) value).doubleValue()); 386 return; 387 case Types.TIMESTAMP: 388 setToPreparedStatementTimestamp(ps, index, value, column); 389 return; 390 case Types.ARRAY: 391 int jdbcBaseType = column.getJdbcBaseType(); 392 String jdbcBaseTypeName = column.getSqlBaseTypeString(); 393 if (jdbcBaseType == Types.TIMESTAMP) { 394 value = getTimestampFromCalendar((Serializable[]) value); 395 } 396 Array array = ps.getConnection().createArrayOf(jdbcBaseTypeName, (Object[]) value); 397 ps.setArray(index, array); 398 return; 399 case Types.BINARY: 400 ps.setBytes(index, (byte[]) value); 401 return; 402 case Types.OTHER: 403 ColumnType type = column.getType(); 404 if (type.isId()) { 405 setId(ps, index, value); 406 return; 407 } else if (type == ColumnType.FTSTORED) { 408 ps.setString(index, (String) value); 409 return; 410 } 411 throw new SQLException("Unhandled type: " + column.getType()); 412 default: 413 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 414 } 415 } 416 417 @Override 418 @SuppressWarnings("boxing") 419 public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException { 420 int jdbcType = rs.getMetaData().getColumnType(index); 421 if (column.getJdbcType() == Types.ARRAY && jdbcType != Types.ARRAY) { 422 jdbcType = column.getJdbcBaseType(); 423 } else { 424 jdbcType = column.getJdbcType(); 425 } 426 switch (jdbcType) { 427 case Types.VARCHAR: 428 case Types.CLOB: 429 return getFromResultSetString(rs, index, column); 430 case Types.BIT: 431 return rs.getBoolean(index); 432 case Types.SMALLINT: 433 case Types.INTEGER: 434 case Types.BIGINT: 435 return rs.getLong(index); 436 case Types.DOUBLE: 437 return rs.getDouble(index); 438 case Types.TIMESTAMP: 439 return getFromResultSetTimestamp(rs, index, column); 440 case Types.ARRAY: 441 Array array = rs.getArray(index); 442 if (array == null) { 443 return null; 444 } 445 if (array.getBaseType() == Types.TIMESTAMP) { 446 return getCalendarFromTimestamp((Timestamp[]) array.getArray()); 447 } else { 448 return (Serializable) array.getArray(); 449 } 450 case Types.BINARY: 451 return rs.getBytes(index); 452 case Types.OTHER: 453 ColumnType type = column.getType(); 454 if (type.isId()) { 455 return getId(rs, index); 456 } 457 throw new SQLException("Unhandled type: " + column.getType()); 458 } 459 throw new SQLException( 460 "Unhandled JDBC type: " + column.getJdbcType() + " for type " + column.getType().toString()); 461 } 462 463 @Override 464 protected int getMaxNameSize() { 465 return 63; 466 } 467 468 @Override 469 public String getColumnName(String name) { 470 // ignore suffixed "_" when checking for reservedness 471 String n = name.replaceAll("_+$", ""); 472 for (String reserved : RESERVED_COLUMN_NAMES) { 473 if (n.equals(reserved)) { 474 // reserved, add one more suffix "_" 475 name += "_"; 476 break; 477 } 478 } 479 return super.getColumnName(name); 480 } 481 482 @Override 483 public List<String> getCustomPostCreateSqls(Table table, Model model) { 484 List<String> sqls = new ArrayList<>(); 485 String key = table.getKey(); 486 if (childNameUniqueConstraintEnabled && key.equals(Model.HIER_TABLE_NAME)) { 487 // CREATE UNIQUE INDEX hierarchy_unique_child ON hierarchy (parentid, name) 488 // WHERE isproperty = false 489 sqls.add(String.format( 490 "CREATE UNIQUE INDEX \"hierarchy_unique_child\" ON \"%s\" (\"%s\", \"%s\") WHERE \"%s\" = false", 491 Model.HIER_TABLE_NAME, Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY, 492 Model.HIER_CHILD_ISPROPERTY_KEY)); 493 // CREATE UNIQUE INDEX hierarchy_unique_child_complex ON hierarchy (parentid, name) 494 // WHERE isproperty = true AND pos IS NULL 495 sqls.add(String.format( 496 "CREATE UNIQUE INDEX \"hierarchy_unique_child_complex\" ON \"%s\" (\"%s\", \"%s\") WHERE \"%s\" = true AND \"%s\" IS NULL", 497 Model.HIER_TABLE_NAME, Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY, 498 Model.HIER_CHILD_ISPROPERTY_KEY, Model.HIER_CHILD_POS_KEY)); 499 // CREATE UNIQUE INDEX hierarchy_unique_child_complex_list ON hierarchy (parentid, name, pos) 500 // WHERE isproperty = true AND pos IS NOT NULL 501 sqls.add(String.format( 502 "CREATE UNIQUE INDEX \"hierarchy_unique_child_complex_list\" ON \"%s\" (\"%s\", \"%s\", \"%s\") WHERE \"%s\" = true AND \"%s\" IS NOT NULL", 503 Model.HIER_TABLE_NAME, Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY, Model.HIER_CHILD_POS_KEY, 504 Model.HIER_CHILD_ISPROPERTY_KEY, Model.HIER_CHILD_POS_KEY)); 505 } else if (collectionUniqueConstraintEnabled && model.isCollectionFragment(key)) { 506 // CREATE UNIQUE INDEX dc_contributors_unique_pos ON dc_contributors (id, pos) 507 String name = table.getPhysicalName(); 508 String indexName = makeName(name, "", "_unique_pos", getMaxIndexNameSize()); 509 sqls.add(String.format("CREATE UNIQUE INDEX \"%s\" ON \"%s\" (\"%s\", \"%s\")", indexName, name, 510 Model.MAIN_KEY, Model.COLL_TABLE_POS_KEY)); 511 } 512 return sqls; 513 } 514 515 @Override 516 public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, 517 Model model) { 518 String sql; 519 if (compatibilityFulltextTable) { 520 sql = "CREATE INDEX %s ON %s USING GIN(%s)"; 521 } else { 522 sql = "CREATE INDEX %s ON %s USING GIN(NX_TO_TSVECTOR(%s))"; 523 } 524 return String.format(sql, quotedIndexName.toLowerCase(), table.getQuotedName(), columns.get(0).getQuotedName()); 525 } 526 527 // must not be interpreted as a regexp, we split on it 528 protected static final String FT_LIKE_SEP = " @#AND#@ "; 529 530 protected static final String FT_LIKE_COL = "??"; 531 532 /** 533 * {@inheritDoc} 534 * <p> 535 * The result of this is passed to {@link #getFulltextScoredMatchInfo}. 536 */ 537 @Override 538 public String getDialectFulltextQuery(String query) { 539 query = query.replace(" & ", " "); // PostgreSQL compatibility BBB 540 query = PREFIX_PATTERN.matcher(query).replaceAll(PREFIX_REPL); 541 FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query); 542 if (ft == null) { 543 return ""; // won't match anything 544 } 545 if (!FulltextQueryAnalyzer.hasPhrase(ft)) { 546 return FulltextQueryAnalyzer.translateFulltext(ft, "|", "&", "& !", ""); 547 } 548 if (compatibilityFulltextTable) { 549 throw new QueryParseException("Cannot use phrase search in fulltext compatibilty mode. " 550 + "Please upgrade the fulltext table: " + query); 551 } 552 /* 553 * Phrase search. We have to do the phrase query using a LIKE, but for performance we pre-filter using as many 554 * fulltext matches as possible by breaking some of the phrases into words. We do an AND of the two. 1. 555 * pre-filter using fulltext on a query that is a superset of the original query, 556 */ 557 FulltextQuery broken = breakPhrases(ft); 558 String ftsql = FulltextQueryAnalyzer.translateFulltext(broken, "|", "&", "& !", ""); 559 /* 560 * 2. AND with a LIKE-based search for all terms, except those that are already exactly matched by the first 561 * part, i.e., toplevel ANDed non-phrases. 562 */ 563 FulltextQuery noand = removeToplevelAndedWords(ft); 564 if (noand != null) { 565 StringBuilder buf = new StringBuilder(); 566 generateLikeSql(noand, buf); 567 ftsql += FT_LIKE_SEP + buf.toString(); 568 569 } 570 return ftsql; 571 } 572 573 /** 574 * Returns a fulltext query that is a superset of the original one and does not have phrase searches. 575 * <p> 576 * Negative phrases (which are at AND level) are removed, positive phrases are split into ANDed words. 577 */ 578 protected static FulltextQuery breakPhrases(FulltextQuery ft) { 579 FulltextQuery newFt = new FulltextQuery(); 580 if (ft.op == Op.AND || ft.op == Op.OR) { 581 List<FulltextQuery> newTerms = new LinkedList<>(); 582 for (FulltextQuery term : ft.terms) { 583 FulltextQuery broken = breakPhrases(term); 584 if (broken == null) { 585 // remove negative phrase 586 } else if (ft.op == Op.AND && broken.op == Op.AND) { 587 // associativity (sub-AND hoisting) 588 newTerms.addAll(broken.terms); 589 } else { 590 newTerms.add(broken); 591 } 592 } 593 if (newTerms.size() == 1) { 594 // single-term parenthesis elimination 595 newFt = newTerms.get(0); 596 } else { 597 newFt.op = ft.op; 598 newFt.terms = newTerms; 599 } 600 } else { 601 boolean isPhrase = ft.isPhrase(); 602 if (!isPhrase) { 603 newFt = ft; 604 } else if (ft.op == Op.WORD) { 605 // positive phrase 606 // split it 607 List<FulltextQuery> newTerms = new LinkedList<>(); 608 for (String subword : ft.word.split(" ")) { 609 FulltextQuery sft = new FulltextQuery(); 610 sft.op = Op.WORD; 611 sft.word = subword; 612 newTerms.add(sft); 613 } 614 newFt.op = Op.AND; 615 newFt.terms = newTerms; 616 } else { 617 // negative phrase 618 // removed 619 newFt = null; 620 } 621 } 622 return newFt; 623 } 624 625 /** 626 * Removes toplevel ANDed simple words from the query. 627 */ 628 protected static FulltextQuery removeToplevelAndedWords(FulltextQuery ft) { 629 if (ft.op == Op.OR || ft.op == Op.NOTWORD) { 630 return ft; 631 } 632 if (ft.op == Op.WORD) { 633 if (ft.isPhrase()) { 634 return ft; 635 } 636 return null; 637 } 638 List<FulltextQuery> newTerms = new LinkedList<>(); 639 for (FulltextQuery term : ft.terms) { 640 if (term.op == Op.NOTWORD) { 641 newTerms.add(term); 642 } else { // Op.WORD 643 if (term.isPhrase()) { 644 newTerms.add(term); 645 } 646 } 647 } 648 if (newTerms.isEmpty()) { 649 return null; 650 } else if (newTerms.size() == 1) { 651 // single-term parenthesis elimination 652 return newTerms.get(0); 653 } else { 654 FulltextQuery newFt = new FulltextQuery(); 655 newFt.op = Op.AND; 656 newFt.terms = newTerms; 657 return newFt; 658 } 659 } 660 661 // turn non-toplevel ANDed single words into SQL 662 // abc "foo bar" -"gee man" 663 // -> ?? LIKE '% foo bar %' AND ?? NOT LIKE '% gee man %' 664 // ?? is a pseudo-parameter for the col 665 protected static void generateLikeSql(FulltextQuery ft, StringBuilder buf) { 666 if (ft.op == Op.AND || ft.op == Op.OR) { 667 buf.append('('); 668 boolean first = true; 669 for (FulltextQuery term : ft.terms) { 670 if (!first) { 671 if (ft.op == Op.AND) { 672 buf.append(" AND "); 673 } else { // Op.OR 674 buf.append(" OR "); 675 } 676 } 677 first = false; 678 generateLikeSql(term, buf); 679 } 680 buf.append(')'); 681 } else { 682 buf.append(FT_LIKE_COL); 683 if (ft.op == Op.NOTWORD) { 684 buf.append(" NOT"); 685 } 686 buf.append(" ILIKE '% "); 687 String word = ft.word.toLowerCase(); 688 // SQL escaping 689 word = word.replace("'", "''"); 690 word = word.replace("\\", ""); // don't take chances 691 word = word.replace(PREFIX_SEARCH, "%"); 692 buf.append(word); 693 if (!word.endsWith("%")) { 694 buf.append(" %"); 695 } 696 buf.append("'"); 697 } 698 } 699 700 // OLD having problems in pre-9.2 (NXP-9228) 701 // SELECT ..., 702 // TS_RANK_CD(NX_TO_TSVECTOR(fulltext), nxquery, 32) as nxscore 703 // FROM ... 704 // LEFT JOIN fulltext ON fulltext.id = hierarchy.id, 705 // TO_TSQUERY('french', ?) nxquery 706 // WHERE ... 707 // AND nxquery @@ NX_TO_TSVECTOR(fulltext) 708 // AND fulltext LIKE '% foo bar %' -- when phrase search 709 // ORDER BY nxscore DESC 710 711 // NEW 712 // SELECT ..., 713 // TS_RANK_CD(NX_TO_TSVECTOR(fulltext), TO_TSQUERY('french', ?), 32) as 714 // nxscore 715 // FROM ... 716 // LEFT JOIN fulltext ON fulltext.id = hierarchy.id 717 // WHERE ... 718 // AND TO_TSQUERY('french', ?) @@ NX_TO_TSVECTOR(fulltext) 719 // AND fulltext LIKE '% foo bar %' -- when phrase search 720 // ORDER BY nxscore DESC 721 @Override 722 public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 723 Column mainColumn, Model model, Database database) { 724 String indexSuffix = model.getFulltextIndexSuffix(indexName); 725 Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); 726 Column ftMain = ft.getColumn(Model.MAIN_KEY); 727 Column ftColumn = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + indexSuffix); 728 String ftColumnName = ftColumn.getFullQuotedName(); 729 String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); 730 FulltextMatchInfo info = new FulltextMatchInfo(); 731 info.joins = new ArrayList<>(); 732 if (nthMatch == 1) { 733 // Need only one JOIN involving the fulltext table 734 info.joins.add(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), 735 mainColumn.getFullQuotedName())); 736 } 737 /* 738 * for phrase search, fulltextQuery may contain a LIKE part 739 */ 740 String like; 741 if (fulltextQuery.contains(FT_LIKE_SEP)) { 742 String[] tmp = fulltextQuery.split(FT_LIKE_SEP, 2); 743 fulltextQuery = tmp[0]; 744 like = tmp[1].replace(FT_LIKE_COL, ftColumnName); 745 } else { 746 like = null; 747 } 748 String tsquery = String.format("TO_TSQUERY('%s', ?)", fulltextAnalyzer); 749 String tsvector; 750 if (compatibilityFulltextTable) { 751 tsvector = ftColumnName; 752 } else { 753 tsvector = String.format("NX_TO_TSVECTOR(%s)", ftColumnName); 754 } 755 String where = String.format("(%s @@ %s)", tsquery, tsvector); 756 if (like != null) { 757 where += " AND (" + like + ")"; 758 } 759 info.whereExpr = where; 760 info.whereExprParam = fulltextQuery; 761 info.scoreExpr = String.format("TS_RANK_CD(%s, %s, 32)", tsvector, tsquery); 762 info.scoreExprParam = fulltextQuery; 763 info.scoreAlias = "_nxscore" + nthSuffix; 764 info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); 765 return info; 766 } 767 768 @Override 769 public boolean getMaterializeFulltextSyntheticColumn() { 770 return true; 771 } 772 773 @Override 774 public int getFulltextIndexedColumns() { 775 return 1; 776 } 777 778 @Override 779 public String getFreeVariableSetterForType(ColumnType type) { 780 if (type == ColumnType.FTSTORED && compatibilityFulltextTable) { 781 return "NX_TO_TSVECTOR(?)"; 782 } 783 return "?"; 784 } 785 786 @Override 787 public boolean supportsUpdateFrom() { 788 return true; 789 } 790 791 @Override 792 public boolean doesUpdateFromRepeatSelf() { 793 return false; 794 } 795 796 @Override 797 public boolean needsAliasForDerivedTable() { 798 return true; 799 } 800 801 @Override 802 public boolean supportsIlike() { 803 return true; 804 } 805 806 @Override 807 public boolean supportsReadAcl() { 808 return aclOptimizationsEnabled; 809 } 810 811 @Override 812 public String getPrepareUserReadAclsSql() { 813 return "SELECT nx_prepare_user_read_acls(?)"; 814 } 815 816 @Override 817 public String getReadAclsCheckSql(String userIdCol) { 818 return String.format("%s = md5(array_to_string(?, '%s'))", userIdCol, getUsersSeparator()); 819 } 820 821 @Override 822 public String getUpdateReadAclsSql() { 823 return "SELECT nx_update_read_acls();"; 824 } 825 826 @Override 827 public String getRebuildReadAclsSql() { 828 return "SELECT nx_rebuild_read_acls();"; 829 } 830 831 @Override 832 public String getSecurityCheckSql(String idColumnName) { 833 return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName); 834 } 835 836 @Override 837 public boolean supportsAncestorsTable() { 838 return true; 839 } 840 841 @Override 842 public boolean supportsFastDescendants() { 843 return pathOptimizationsEnabled; 844 } 845 846 @Override 847 public String getInTreeSql(String idColumnName, String id) { 848 String cast; 849 try { 850 cast = getCastForId(id); 851 } catch (IllegalArgumentException e) { 852 // discard query with invalid id 853 return null; 854 } 855 if (pathOptimizationsEnabled) { 856 return String.format("EXISTS(SELECT 1 FROM ancestors WHERE id = %s AND ARRAY[?]%s <@ ancestors)", 857 idColumnName, getCastForArray(cast)); 858 } 859 return String.format("%s IN (SELECT * FROM nx_children(?%s))", idColumnName, cast); 860 } 861 862 protected String getCastForArray(String cast) { 863 if (cast.isEmpty()) { 864 return cast; 865 } 866 return cast + "[]"; 867 } 868 869 protected String getCastForId(String id) { 870 String ret; 871 switch (idType) { 872 case VARCHAR: 873 return ""; 874 case UUID: 875 // check that it's really a uuid 876 if (id != null) { 877 UUID.fromString(id); 878 } 879 ret = "::uuid"; 880 break; 881 case SEQUENCE: 882 // check that it's really an integer 883 if (id != null && !StringUtils.isNumeric(id)) { 884 throw new IllegalArgumentException("Invalid sequence id: " + id); 885 } 886 ret = "::bigint"; 887 break; 888 default: 889 throw new AssertionError("Unknown id type: " + idType); 890 } 891 return ret; 892 } 893 894 @Override 895 public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, 896 List<Serializable> outValues) { 897 Column keyColumn = columns.get(0); 898 Table table = keyColumn.getTable(); 899 StringBuilder sql = new StringBuilder(); 900 sql.append("INSERT INTO "); 901 sql.append(table.getQuotedName()); 902 sql.append(" ("); 903 for (int i = 0; i < columns.size(); i++) { 904 if (i != 0) { 905 sql.append(", "); 906 } 907 sql.append(columns.get(i).getQuotedName()); 908 } 909 sql.append(") VALUES ("); 910 for (int i = 0; i < columns.size(); i++) { 911 if (i != 0) { 912 sql.append(", "); 913 } 914 sql.append("?"); 915 outColumns.add(columns.get(i)); 916 outValues.add(values.get(i)); 917 } 918 sql.append(") ON CONFLICT ("); 919 sql.append(keyColumn.getQuotedName()); 920 sql.append(") DO UPDATE SET "); 921 for (int i = 1; i < columns.size(); i++) { 922 if (i != 1) { 923 sql.append(", "); 924 } 925 sql.append(columns.get(i).getQuotedName()); 926 sql.append(" = ?"); 927 outColumns.add(columns.get(i)); 928 outValues.add(values.get(i)); 929 } 930 return sql.toString(); 931 } 932 933 @Override 934 public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) { 935 returnParam[0] = mixin; 936 String sql = "ARRAY[?]::varchar[] <@ " + mixinsColumn.getFullQuotedName(); 937 return positive ? sql : "NOT(" + sql + ")"; 938 } 939 940 @Override 941 public boolean supportsSysNameArray() { 942 return true; 943 } 944 945 @Override 946 public boolean supportsArrays() { 947 return true; 948 } 949 950 @Override 951 public boolean supportsArrayColumns() { 952 return true; 953 } 954 955 public static class ArraySubQueryPostgreSQL extends ArraySubQuery { 956 957 protected Dialect dialect = null; 958 959 protected Table fakeSubqueryTableAlias = null; 960 961 public ArraySubQueryPostgreSQL(Column arrayColumn, String alias) { 962 super(arrayColumn, alias); 963 dialect = arrayColumn.getTable().getDialect(); 964 fakeSubqueryTableAlias = new TableAlias(arrayColumn.getTable(), alias); 965 } 966 967 @Override 968 public Column getSubQueryIdColumn() { 969 Column column = fakeSubqueryTableAlias.getColumn(Model.MAIN_KEY); 970 return new ArraySubQueryPostgreSQLColumn(column.getPhysicalName(), column.getType()); 971 } 972 973 @Override 974 public Column getSubQueryValueColumn() { 975 return new ArraySubQueryPostgreSQLColumn(Model.COLL_TABLE_VALUE_KEY, arrayColumn.getBaseType()); 976 } 977 978 public class ArraySubQueryPostgreSQLColumn extends Column { 979 private static final long serialVersionUID = 1L; 980 981 ArraySubQueryPostgreSQLColumn(String columnName, ColumnType columnType) { 982 super(fakeSubqueryTableAlias, columnName, columnType, columnName); 983 } 984 985 @Override 986 public String getFullQuotedName() { 987 return dialect.openQuote() + subQueryAlias + dialect.closeQuote() + '.' + getQuotedName(); 988 } 989 } 990 991 @Override 992 public String toSql() { 993 Table table = arrayColumn.getTable(); 994 return String.format("(SELECT %s, UNNEST(%s) AS %s, generate_subscripts(%s, 1) AS %s FROM %s) ", 995 table.getColumn(Model.MAIN_KEY).getQuotedName(), arrayColumn.getQuotedName(), 996 Model.COLL_TABLE_VALUE_KEY, arrayColumn.getQuotedName(), Model.COLL_TABLE_POS_KEY, 997 table.getRealTable().getQuotedName()); 998 } 999 } 1000 1001 @Override 1002 public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) { 1003 return new ArraySubQueryPostgreSQL(arrayColumn, subQueryAlias); 1004 } 1005 1006 @Override 1007 public String getArrayElementString(String arrayColumnName, int arrayElementIndex) { 1008 // PostgreSQL arrays index start at 1 1009 return arrayColumnName + "[" + (arrayElementIndex + 1) + "]"; 1010 } 1011 1012 @Override 1013 public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) { 1014 StringBuilder sql = new StringBuilder(); 1015 if (!positive) { 1016 sql.append("(NOT("); 1017 } 1018 if (params.size() == 1) { 1019 // ? = ANY(arrayColumn) 1020 sql.append("? = ANY("); 1021 sql.append(arrayColumn.getFullQuotedName()); 1022 if (cast != null) { 1023 // DATE cast 1024 sql.append("::"); 1025 sql.append(cast); 1026 sql.append("[]"); 1027 } 1028 sql.append(")"); 1029 } else { 1030 // arrayColumn && ARRAY[?, ?, ?] 1031 sql.append(arrayColumn.getFullQuotedName()); 1032 sql.append(" && "); 1033 sql.append("ARRAY["); 1034 for (int i = 0; i < params.size(); i++) { 1035 if (i != 0) { 1036 sql.append(", "); 1037 } 1038 sql.append('?'); 1039 } 1040 sql.append("]::"); 1041 sql.append(arrayColumn.getSqlTypeString()); 1042 } 1043 if (!positive) { 1044 sql.append(") OR "); 1045 sql.append(arrayColumn.getFullQuotedName()); 1046 sql.append(" IS NULL)"); 1047 } 1048 return sql.toString(); 1049 } 1050 1051 @Override 1052 public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 1053 return getArrayOpSql(arrayColumn, refName, positive, dataHierTable, "LIKE"); 1054 } 1055 1056 @Override 1057 public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 1058 return getArrayOpSql(arrayColumn, refName, positive, dataHierTable, "ILIKE"); 1059 } 1060 1061 protected String getArrayOpSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable, 1062 String op) { 1063 Table table = arrayColumn.getTable(); 1064 String tableAliasName = openQuote() + getTableName(refName) + closeQuote(); 1065 String sql = String.format("EXISTS (SELECT 1 FROM %s AS %s WHERE %s = %s AND %s %s ?)", 1066 getArraySubQuery(arrayColumn, tableAliasName).toSql(), tableAliasName, 1067 dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), 1068 tableAliasName + '.' + table.getColumn(Model.MAIN_KEY).getQuotedName(), 1069 tableAliasName + '.' + Model.COLL_TABLE_VALUE_KEY, op); 1070 if (!positive) { 1071 sql = "NOT(" + sql + ")"; 1072 } 1073 return sql; 1074 } 1075 1076 @Override 1077 public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException { 1078 if (elements == null || elements.length == 0) { 1079 return null; 1080 } 1081 String typeName; 1082 switch (type) { 1083 case Types.VARCHAR: 1084 typeName = "varchar"; 1085 break; 1086 case Types.CLOB: 1087 typeName = "text"; 1088 break; 1089 case Types.BIT: 1090 typeName = "bool"; 1091 break; 1092 case Types.BIGINT: 1093 typeName = "int8"; 1094 break; 1095 case Types.DOUBLE: 1096 typeName = "float8"; 1097 break; 1098 case Types.TIMESTAMP: 1099 typeName = "timestamp"; 1100 break; 1101 case Types.SMALLINT: 1102 typeName = "int2"; 1103 break; 1104 case Types.INTEGER: 1105 typeName = "int4"; 1106 break; 1107 case Types.OTHER: // id 1108 switch (idType) { 1109 case VARCHAR: 1110 typeName = "varchar"; 1111 break; 1112 case UUID: 1113 typeName = "uuid"; 1114 break; 1115 case SEQUENCE: 1116 typeName = "int8"; 1117 break; 1118 default: 1119 throw new AssertionError("Unknown id type: " + idType); 1120 } 1121 break; 1122 default: 1123 throw new AssertionError("Unknown type: " + type); 1124 } 1125 return connection.createArrayOf(typeName, elements); 1126 } 1127 1128 @Override 1129 public String getSQLStatementsFilename() { 1130 return "nuxeovcs/postgresql.sql.txt"; 1131 } 1132 1133 @Override 1134 public String getTestSQLStatementsFilename() { 1135 return "nuxeovcs/postgresql.test.sql.txt"; 1136 } 1137 1138 @Override 1139 public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) { 1140 Map<String, Serializable> properties = new HashMap<>(); 1141 switch (idType) { 1142 case VARCHAR: 1143 properties.put("idType", "varchar(36)"); 1144 properties.put("idTypeParam", "varchar"); 1145 properties.put("idNotPresent", "'-'"); 1146 properties.put("sequenceEnabled", Boolean.FALSE); 1147 break; 1148 case UUID: 1149 properties.put("idType", "uuid"); 1150 properties.put("idTypeParam", "uuid"); 1151 properties.put("idNotPresent", "'00000000-FFFF-FFFF-FFFF-FFFF00000000'"); 1152 properties.put("sequenceEnabled", Boolean.FALSE); 1153 break; 1154 case SEQUENCE: 1155 properties.put("idType", "int8"); 1156 properties.put("idTypeParam", "int8"); 1157 properties.put("idNotPresent", "-1"); 1158 properties.put("sequenceEnabled", Boolean.TRUE); 1159 properties.put("idSequenceName", idSequenceName); 1160 } 1161 properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled)); 1162 properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled)); 1163 properties.put("fulltextAnalyzer", fulltextAnalyzer); 1164 properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled)); 1165 properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled)); 1166 properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled)); 1167 properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled)); 1168 properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled)); 1169 properties.put("arrayColumnsEnabled", Boolean.valueOf(arrayColumnsEnabled)); 1170 if (!fulltextSearchDisabled) { 1171 Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); 1172 FulltextConfiguration fti = model.getFulltextConfiguration(); 1173 List<String> lines = new ArrayList<>(fti.indexNames.size()); 1174 for (String indexName : fti.indexNames) { 1175 String suffix = model.getFulltextIndexSuffix(indexName); 1176 Column ftft = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + suffix); 1177 Column ftst = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + suffix); 1178 Column ftbt = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + suffix); 1179 String concat; 1180 if (compatibilityFulltextTable) { 1181 // tsvector 1182 concat = " NEW.%s := COALESCE(NEW.%s, ''::TSVECTOR) || COALESCE(NEW.%s, ''::TSVECTOR);"; 1183 } else { 1184 // text with space at beginning and end 1185 concat = " NEW.%s := ' ' || COALESCE(NEW.%s, '') || ' ' || COALESCE(NEW.%s, '') || ' ';"; 1186 } 1187 String line = String.format(concat, ftft.getQuotedName(), ftst.getQuotedName(), ftbt.getQuotedName()); 1188 lines.add(line); 1189 } 1190 properties.put("fulltextTriggerStatements", String.join("\n", lines)); 1191 } 1192 String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE); 1193 List<String> permsList = new LinkedList<>(); 1194 for (String perm : permissions) { 1195 permsList.add("('" + perm + "')"); 1196 } 1197 properties.put("readPermissions", String.join(", ", permsList)); 1198 properties.put("usersSeparator", getUsersSeparator()); 1199 properties.put("everyone", SecurityConstants.EVERYONE); 1200 properties.put("readAclMaxSize", Integer.toString(readAclMaxSize)); 1201 properties.put("unlogged", unloggedKeyword); 1202 return properties; 1203 } 1204 1205 @Override 1206 public List<String> getStartupSqls(Model model, Database database) { 1207 if (aclOptimizationsEnabled) { 1208 log.info("Vacuuming tables used by optimized acls"); 1209 return Collections.singletonList("SELECT nx_vacuum_read_acls()"); 1210 } 1211 return Collections.emptyList(); 1212 } 1213 1214 @Override 1215 public boolean isClusteringSupported() { 1216 return true; 1217 } 1218 1219 @Override 1220 public String getClusterInsertInvalidations() { 1221 return "SELECT NX_CLUSTER_INVAL(?, ?, ?, ?)"; 1222 } 1223 1224 @Override 1225 public String getClusterGetInvalidations() { 1226 return "DELETE FROM cluster_invals WHERE nodeid = ? RETURNING id, fragments, kind"; 1227 } 1228 1229 @Override 1230 public boolean isConcurrentUpdateException(Throwable t) { 1231 while (t.getCause() != null) { 1232 t = t.getCause(); 1233 } 1234 if (t instanceof SQLException) { 1235 String sqlState = ((SQLException) t).getSQLState(); 1236 if ("23503".equals(sqlState)) { 1237 // insert or update on table ... violates foreign key constraint 1238 return true; 1239 } 1240 if ("23505".equals(sqlState)) { 1241 // duplicate key value violates unique constraint 1242 return true; 1243 } 1244 if ("40P01".equals(sqlState)) { 1245 // deadlock detected 1246 return true; 1247 } 1248 } 1249 return false; 1250 } 1251 1252 @Override 1253 public boolean supportsPaging() { 1254 return true; 1255 } 1256 1257 @Override 1258 public String addPagingClause(String sql, long limit, long offset) { 1259 return sql + String.format(" LIMIT %d OFFSET %d", limit, offset); 1260 } 1261 1262 @Override 1263 public boolean supportsWith() { 1264 return false; // don't activate until proven useful 1265 // return supportsWith; 1266 } 1267 1268 @Override 1269 public void performAdditionalStatements(Connection connection) throws SQLException { 1270 // Check if there is a pre-existing aclr_permission table 1271 boolean createAclrPermission; 1272 try (Statement s = connection.createStatement()) { 1273 String sql = "SELECT 1 FROM pg_tables WHERE tablename = 'aclr_permission'"; 1274 try (ResultSet rs = s.executeQuery(sql)) { 1275 createAclrPermission = !rs.next(); 1276 } 1277 } 1278 // If no table, it will be created and filled at DDL execution time 1279 if (createAclrPermission) { 1280 return; 1281 } 1282 // Warn user if BROWSE permissions has changed 1283 Set<String> dbPermissions = new HashSet<>(); 1284 try (Statement s = connection.createStatement()) { 1285 String sql = "SELECT * FROM aclr_permission"; 1286 try (ResultSet rs = s.executeQuery(sql)) { 1287 while (rs.next()) { 1288 dbPermissions.add(rs.getString(1)); 1289 } 1290 } 1291 } 1292 SecurityService securityService = NXCore.getSecurityService(); 1293 Set<String> confPermissions = new HashSet<>( 1294 Arrays.asList(securityService.getPermissionsToCheck(SecurityConstants.BROWSE))); 1295 if (!dbPermissions.equals(confPermissions)) { 1296 log.error("Security permission for BROWSE has changed, you need to rebuild the optimized read acls:" 1297 + "DROP TABLE aclr_permission; DROP TABLE aclr; then restart."); 1298 } 1299 } 1300 1301 public String getUsersSeparator() { 1302 if (usersSeparator == null) { 1303 return DEFAULT_USERS_SEPARATOR; 1304 } 1305 return usersSeparator; 1306 } 1307 1308 @Override 1309 public String getValidationQuery() { 1310 return ""; 1311 } 1312 1313 @Override 1314 public String getAncestorsIdsSql() { 1315 return "SELECT NX_ANCESTORS(?)"; 1316 } 1317 1318 @Override 1319 public boolean needsNullsLastOnDescSort() { 1320 return true; 1321 } 1322 1323 @Override 1324 public String getDateCast() { 1325 // this is more amenable to being indexed than a CAST 1326 return "DATE(%s)"; 1327 } 1328 1329 @Override 1330 public String castIdToVarchar(String expr) { 1331 switch (idType) { 1332 case VARCHAR: 1333 return expr; 1334 case UUID: 1335 return expr + "::varchar"; 1336 case SEQUENCE: 1337 return expr + "::varchar"; 1338 default: 1339 throw new AssertionError("Unknown id type: " + idType); 1340 } 1341 } 1342 1343 @Override 1344 public DialectIdType getIdType() { 1345 return idType; 1346 } 1347 1348 @Override 1349 public String getSoftDeleteSql() { 1350 return "SELECT NX_DELETE(?, ?)"; 1351 } 1352 1353 @Override 1354 public String getSoftDeleteCleanupSql() { 1355 return "SELECT NX_DELETE_PURGE(?, ?)"; 1356 } 1357 1358 @Override 1359 public String getBinaryFulltextSql(List<String> columns) { 1360 if (compatibilityFulltextTable) { 1361 // extract tokens from tsvector 1362 String columnsAs = columns.stream() 1363 .map(col -> "regexp_replace(" + col + "::text, $$'|'\\:[^']*'?$$, ' ', 'g')") 1364 .collect(Collectors.joining(", ")); 1365 return "SELECT " + columnsAs + " FROM fulltext WHERE id=?"; 1366 } 1367 return super.getBinaryFulltextSql(columns); 1368 } 1369 1370 // parenthesizes parameter part, with optional nested parentheses 1371 private static final Pattern SIG_MATCH = Pattern.compile("[^(]*\\((([^()]*|\\([^()]*\\))*)\\).*", Pattern.DOTALL); 1372 1373 @Override 1374 public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection, 1375 JDBCLogger logger, Map<String, Serializable> properties) throws SQLException { 1376 boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT); 1377 if (compatCheck) { 1378 procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length()); 1379 return Collections.singletonList(procCreate); 1380 } 1381 // extract signature from create statement 1382 Matcher m = SIG_MATCH.matcher(procCreate); 1383 if (!m.matches()) { 1384 throw new NuxeoException("Cannot parse arguments: " + procCreate); 1385 } 1386 String procArgs = normalizeArgs(m.group(1)); 1387 try (Statement st = connection.createStatement()) { 1388 // check if the stored procedure exists and its content 1389 String getBody = "SELECT prosrc, pg_get_function_identity_arguments(oid) FROM pg_proc WHERE proname = '" 1390 + procName + "'"; 1391 logger.log(getBody); 1392 try (ResultSet rs = st.executeQuery(getBody)) { 1393 while (rs.next()) { 1394 String body = rs.getString(1); 1395 String args = rs.getString(2); 1396 if (!args.equals(procArgs)) { 1397 // different signature 1398 continue; 1399 } 1400 // stored proc already exists 1401 if (normalizeString(procCreate).contains(normalizeString(body))) { 1402 logger.log(" -> exists, unchanged"); 1403 return Collections.emptyList(); 1404 } else { 1405 logger.log(" -> exists, old"); 1406 // we can't drop then recreate as for instance a function used by a trigger 1407 // would say "cannot drop function ... because other objects depend on it" 1408 // so we hack and do an do a replace 1409 if (!procCreate.toLowerCase().startsWith("create ")) { 1410 throw new NuxeoException("Should start with CREATE: " + procCreate); 1411 } 1412 procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length()); 1413 return Collections.singletonList(procCreate); 1414 } 1415 } 1416 } 1417 logger.log(" -> missing"); 1418 return Collections.singletonList(procCreate); 1419 } 1420 } 1421 1422 protected static String normalizeString(String string) { 1423 return string.replaceAll("[ \n\r\t]+", " ").trim(); 1424 } 1425 1426 /** The type aliases that we use for our stored procedure argument definitions. */ 1427 private static final Map<String, String> TYPE_ALIASES = new HashMap<>(); 1428 1429 static { 1430 TYPE_ALIASES.put("bool", "boolean"); 1431 TYPE_ALIASES.put("varchar", "character varying"); 1432 TYPE_ALIASES.put("int", "integer"); 1433 TYPE_ALIASES.put("int4", "integer"); 1434 TYPE_ALIASES.put("int8", "bigint"); 1435 TYPE_ALIASES.put("timestamp", "timestamp without time zone"); 1436 } 1437 1438 /** Normalize PostgreSQL type aliases. */ 1439 protected static String normalizeArgs(String args) { 1440 if (args.isEmpty()) { 1441 return args; 1442 } 1443 args = args.toLowerCase(); 1444 List<String> argList = Arrays.asList(args.split(",[ ]*")); 1445 List<String> newArgList = new ArrayList<>(argList.size()); 1446 for (String arg : argList) { 1447 // array or size spec 1448 int i = arg.indexOf('('); 1449 if (i == -1) { 1450 i = arg.indexOf('['); 1451 } 1452 String suffix = ""; 1453 if (i > 0) { 1454 suffix = arg.substring(i); 1455 arg = arg.substring(0, i); 1456 } 1457 for (Entry<String, String> es : TYPE_ALIASES.entrySet()) { 1458 String type = es.getKey(); 1459 if (arg.equals(type) || arg.endsWith(" " + type)) { 1460 arg = arg.substring(0, arg.length() - type.length()) + es.getValue(); 1461 break; 1462 } 1463 } 1464 newArgList.add(arg + suffix); 1465 } 1466 return String.join(", ", newArgList); 1467 } 1468 1469}