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