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