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