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 */ 019package org.nuxeo.ecm.core.storage.sql.jdbc; 020 021import java.io.IOException; 022import java.io.Serializable; 023import java.sql.Connection; 024import java.sql.ResultSet; 025import java.sql.SQLException; 026import java.util.ArrayList; 027import java.util.Arrays; 028import java.util.Collection; 029import java.util.Collections; 030import java.util.HashMap; 031import java.util.HashSet; 032import java.util.LinkedList; 033import java.util.List; 034import java.util.Map; 035import java.util.Map.Entry; 036import java.util.Set; 037import java.util.stream.Collectors; 038 039import org.nuxeo.ecm.core.api.NuxeoException; 040import org.nuxeo.ecm.core.api.model.Delta; 041import org.nuxeo.ecm.core.api.repository.FulltextConfiguration; 042import org.nuxeo.ecm.core.query.sql.NXQL; 043import org.nuxeo.ecm.core.schema.SchemaManager; 044import org.nuxeo.ecm.core.storage.sql.ColumnType; 045import org.nuxeo.ecm.core.storage.sql.Mapper; 046import org.nuxeo.ecm.core.storage.sql.Model; 047import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 048import org.nuxeo.ecm.core.storage.sql.RepositoryImpl; 049import org.nuxeo.ecm.core.storage.sql.RowMapper.RowUpdate; 050import org.nuxeo.ecm.core.storage.sql.Selection; 051import org.nuxeo.ecm.core.storage.sql.SelectionType; 052import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 053import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 054import org.nuxeo.ecm.core.storage.sql.jdbc.db.Delete; 055import org.nuxeo.ecm.core.storage.sql.jdbc.db.Insert; 056import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 057import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select; 058import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 059import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table.IndexType; 060import org.nuxeo.ecm.core.storage.sql.jdbc.db.Update; 061import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect; 062import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.SQLStatement; 063import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.SQLStatement.ListCollector; 064import org.nuxeo.runtime.api.Framework; 065 066/** 067 * This singleton generates and holds the actual SQL DDL and DML statements for the operations needed by the 068 * {@link Mapper}, given a {@link Model}. 069 * <p> 070 * It is specific to one SQL dialect. 071 */ 072public class SQLInfo { 073 074 private static final String ORDER_DESC = "DESC"; 075 076 private static final String ORDER_ASC = "ASC"; 077 078 public final Database database; 079 080 public final Dialect dialect; 081 082 public final boolean softDeleteEnabled; 083 084 public final boolean proxiesEnabled; 085 086 private final Model model; 087 088 private String selectRootIdSql; 089 090 private Column selectRootIdWhatColumn; 091 092 private final Map<String, String> insertSqlMap; // statement 093 094 private final Map<String, List<Column>> insertColumnsMap; 095 096 private final Map<String, String> deleteSqlMap; // statement 097 098 private Map<SelectionType, SQLInfoSelection> selections; 099 100 private String selectChildrenIdsAndTypesSql; 101 102 private String selectChildrenIdsAndTypesSqlExcludeSpecialChildren; 103 104 private String selectComplexChildrenIdsAndTypesSql; 105 106 private String selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren; 107 108 private List<Column> selectChildrenIdsAndTypesWhatColumns; 109 110 private String selectDescendantsInfoSql; 111 112 private List<Column> selectDescendantsInfoWhatColumns; 113 114 private final Map<String, String> copySqlMap; 115 116 private final Map<String, Column> copyIdColumnMap; 117 118 public final Map<String, SQLInfoSelect> selectFragmentById; 119 120 protected String createClusterNodeSql; 121 122 protected List<Column> createClusterNodeColumns; 123 124 protected String deleteClusterNodeSql; 125 126 protected Column deleteClusterNodeColumn; 127 128 protected String deleteClusterInvalsSql; 129 130 protected Column deleteClusterInvalsColumn; 131 132 protected List<Column> clusterInvalidationsColumns; 133 134 protected Map<String, List<SQLStatement>> sqlStatements; 135 136 public Map<String, Serializable> sqlStatementsProperties; 137 138 protected List<String> getBinariesSql; 139 140 protected List<Column> getBinariesColumns; 141 142 /** 143 * Generates and holds the needed SQL statements given a {@link Model} and a {@link Dialect}. 144 * 145 * @param model the model 146 * @param dialect the SQL dialect 147 */ 148 public SQLInfo(Model model, Dialect dialect, boolean requiresClusterSQL) { 149 this.model = model; 150 this.dialect = dialect; 151 RepositoryDescriptor repositoryDescriptor = model.getRepositoryDescriptor(); 152 softDeleteEnabled = repositoryDescriptor.getSoftDeleteEnabled(); 153 proxiesEnabled = repositoryDescriptor.getProxiesEnabled(); 154 155 database = new Database(dialect); 156 157 selectRootIdSql = null; 158 selectRootIdWhatColumn = null; 159 160 selectFragmentById = new HashMap<>(); 161 162 selections = new HashMap<>(); 163 164 selectChildrenIdsAndTypesSql = null; 165 selectChildrenIdsAndTypesSqlExcludeSpecialChildren = null; 166 selectChildrenIdsAndTypesWhatColumns = null; 167 selectComplexChildrenIdsAndTypesSql = null; 168 selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren = null; 169 170 insertSqlMap = new HashMap<>(); 171 insertColumnsMap = new HashMap<>(); 172 173 deleteSqlMap = new HashMap<>(); 174 175 copySqlMap = new HashMap<>(); 176 copyIdColumnMap = new HashMap<>(); 177 178 getBinariesSql = new ArrayList<>(1); 179 getBinariesColumns = new ArrayList<>(1); 180 181 initSQL(requiresClusterSQL); 182 initSelections(); 183 184 try { 185 initSQLStatements(RepositoryImpl.testProps, repositoryDescriptor.sqlInitFiles); 186 } catch (IOException e) { 187 throw new NuxeoException(e); 188 } 189 } 190 191 public Database getDatabase() { 192 return database; 193 } 194 195 // ----- select ----- 196 197 public String getSelectRootIdSql() { 198 return selectRootIdSql; 199 } 200 201 public Column getSelectRootIdWhatColumn() { 202 return selectRootIdWhatColumn; 203 } 204 205 public String getInsertRootIdSql() { 206 return insertSqlMap.get(Model.REPOINFO_TABLE_NAME); 207 } 208 209 public List<Column> getInsertRootIdColumns() { 210 return insertColumnsMap.get(Model.REPOINFO_TABLE_NAME); 211 } 212 213 public SQLInfoSelection getSelection(SelectionType type) { 214 return selections.get(type); 215 216 } 217 218 public String getSelectChildrenIdsAndTypesSql(boolean excludeSpecialChildren, boolean excludeRegularChildren) { 219 if (excludeRegularChildren) { 220 return excludeSpecialChildren ? selectComplexChildrenIdsAndTypesSql 221 : selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren; 222 } else { 223 return excludeSpecialChildren ? selectChildrenIdsAndTypesSqlExcludeSpecialChildren 224 : selectChildrenIdsAndTypesSql; 225 } 226 } 227 228 public List<Column> getSelectChildrenIdsAndTypesWhatColumns() { 229 return selectChildrenIdsAndTypesWhatColumns; 230 } 231 232 public String getSelectDescendantsInfoSql() { 233 return selectDescendantsInfoSql; 234 } 235 236 public List<Column> getSelectDescendantsInfoWhatColumns() { 237 return selectDescendantsInfoWhatColumns; 238 } 239 240 // ----- cluster ----- 241 242 public String getCreateClusterNodeSql() { 243 return createClusterNodeSql; 244 } 245 246 public List<Column> getCreateClusterNodeColumns() { 247 return createClusterNodeColumns; 248 } 249 250 public String getDeleteClusterNodeSql() { 251 return deleteClusterNodeSql; 252 } 253 254 public Column getDeleteClusterNodeColumn() { 255 return deleteClusterNodeColumn; 256 } 257 258 public String getDeleteClusterInvalsSql() { 259 return deleteClusterInvalsSql; 260 } 261 262 public Column getDeleteClusterInvalsColumn() { 263 return deleteClusterInvalsColumn; 264 } 265 266 public int getClusterNodeIdType() { 267 return dialect.getJDBCTypeAndString(ColumnType.CLUSTERNODE).jdbcType; 268 } 269 270 public List<Column> getClusterInvalidationsColumns() { 271 return clusterInvalidationsColumns; 272 } 273 274 // ----- insert ----- 275 276 /** 277 * Returns the SQL {@code INSERT} to add a row. The columns that represent sequences that are implicitly 278 * auto-incremented aren't included. 279 * 280 * @param tableName the table name 281 * @return the SQL {@code INSERT} statement 282 */ 283 public String getInsertSql(String tableName) { 284 return insertSqlMap.get(tableName); 285 } 286 287 /** 288 * Returns the list of columns to use for an {@code INSERT} statement {@link #getInsertSql}. 289 * 290 * @param tableName the table name 291 * @return the list of columns 292 */ 293 public List<Column> getInsertColumns(String tableName) { 294 return insertColumnsMap.get(tableName); 295 } 296 297 // ----- 298 299 /** 300 * Returns the clause used to match a given row by id in the given table. 301 * <p> 302 * Takes into account soft deletes. 303 * 304 * @param tableName the table name 305 * @return the clause, like {@code table.id = ?} 306 */ 307 public String getIdEqualsClause(String tableName) { 308 return database.getTable(tableName).getColumn(Model.MAIN_KEY).getQuotedName() + " = ?" 309 + getSoftDeleteClause(tableName); 310 } 311 312 /** 313 * Returns {@code AND isdeleted IS NULL} if this is the hierarchy table and soft delete is activated. 314 * 315 * @param tableName the table name 316 * @return the clause 317 */ 318 public String getSoftDeleteClause(String tableName) { 319 if (Model.HIER_TABLE_NAME.equals(tableName) && softDeleteEnabled) { 320 return " AND " + getSoftDeleteClause(); 321 } else { 322 return ""; 323 } 324 } 325 326 /** 327 * Returns null or {@code AND isdeleted IS NULL} if soft delete is activated. 328 * 329 * @return the clause, or null 330 */ 331 public String getSoftDeleteClause() { 332 if (softDeleteEnabled) { 333 return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_IS_DELETED_KEY).getFullQuotedName() 334 + " IS NULL"; 335 } else { 336 return null; 337 } 338 } 339 340 // ----- update ----- 341 342 // TODO these two methods are redundant with one another 343 344 /** 345 * <pre> 346 * UPDATE tableName SET key1 = ?, key2 = ?, ... WHERE id = ? AND condition1 = ? AND condition2 IS NULL ... 347 * </pre> 348 */ 349 public SQLInfoSelect getUpdateById(String tableName, RowUpdate rowu) { 350 Table table = database.getTable(tableName); 351 Update update = new Update(table); 352 353 List<Column> whatColumns = new ArrayList<>(); 354 Set<String> deltas = new HashSet<>(); 355 for (String key : rowu.keys) { 356 whatColumns.add(table.getColumn(key)); 357 Serializable value = rowu.row.get(key); 358 if (value instanceof Delta && ((Delta) value).getBase() != null) { 359 deltas.add(key); 360 } 361 } 362 update.setUpdatedColumns(whatColumns, deltas); 363 364 List<Column> whereColumns = new ArrayList<>(2); 365 String where = getIdEqualsClause(tableName); 366 whereColumns.add(table.getColumn(Model.MAIN_KEY)); 367 if (rowu.conditions != null) { 368 for (Entry<String, Serializable> es : rowu.conditions.entrySet()) { 369 String key = es.getKey(); 370 boolean isNull = es.getValue() == null; 371 Column column = table.getColumn(key); 372 String columnName = column.getQuotedName(); 373 if (isNull) { 374 where += " AND " + columnName + " IS NULL"; 375 } else { 376 where += " AND " + columnName + " = ?"; 377 whereColumns.add(column); 378 } 379 } 380 } 381 update.setWhere(where); 382 return new SQLInfoSelect(update.getStatement(), whatColumns, whereColumns, null); 383 } 384 385 public Update getUpdateByIdForKeys(String tableName, List<String> keys) { 386 Table table = database.getTable(tableName); 387 List<Column> columns = new LinkedList<>(); 388 for (String key : keys) { 389 columns.add(table.getColumn(key)); 390 } 391 Update update = new Update(table); 392 update.setUpdatedColumns(columns); 393 update.setWhere(getIdEqualsClause(tableName)); 394 return update; 395 } 396 397 /** 398 * Select by ids for all values of several fragments. 399 */ 400 public SQLInfoSelect getSelectFragmentsByIds(String tableName, int nids) { 401 return getSelectFragmentsByIds(tableName, nids, null, null); 402 } 403 404 /** 405 * Select by ids for all values of several fragments (maybe ordered along columns -- for collection fragments 406 * retrieval). 407 */ 408 public SQLInfoSelect getSelectFragmentsByIds(String tableName, int nids, String[] orderBys, 409 Set<String> skipColumns) { 410 Table table = database.getTable(tableName); 411 List<Column> whatColumns = new LinkedList<>(); 412 List<String> whats = new LinkedList<>(); 413 List<Column> opaqueColumns = new LinkedList<>(); 414 for (Column column : table.getColumns()) { 415 if (column.isOpaque()) { 416 opaqueColumns.add(column); 417 } else if (skipColumns == null || !skipColumns.contains(column.getKey())) { 418 whatColumns.add(column); 419 whats.add(column.getQuotedName()); 420 } 421 } 422 Column whereColumn = table.getColumn(Model.MAIN_KEY); 423 StringBuilder sb = new StringBuilder(whereColumn.getQuotedName()); 424 sb.append(" IN ("); 425 for (int i = 0; i < nids; i++) { 426 if (i != 0) { 427 sb.append(", "); 428 } 429 sb.append('?'); 430 } 431 sb.append(')'); 432 sb.append(getSoftDeleteClause(tableName)); 433 Select select = new Select(table); 434 select.setWhat(String.join(", ", whats)); 435 select.setFrom(table.getQuotedName()); 436 select.setWhere(sb.toString()); 437 if (orderBys != null) { 438 List<String> orders = new LinkedList<>(); 439 for (String orderBy : orderBys) { 440 orders.add(table.getColumn(orderBy).getQuotedName()); 441 } 442 select.setOrderBy(String.join(", ", orders)); 443 } 444 return new SQLInfoSelect(select.getStatement(), whatColumns, Collections.singletonList(whereColumn), 445 opaqueColumns.isEmpty() ? null : opaqueColumns); 446 } 447 448 /** 449 * Select all ancestors ids for several fragments. 450 * <p> 451 * Fast alternative to the slowest iterative {@link #getSelectParentIds}. 452 * 453 * @return null if it's not possible in one call in this dialect 454 */ 455 public SQLInfoSelect getSelectAncestorsIds() { 456 String sql = dialect.getAncestorsIdsSql(); 457 if (sql == null) { 458 return null; 459 } 460 Table table = database.getTable(Model.HIER_TABLE_NAME); 461 Column mainColumn = table.getColumn(Model.MAIN_KEY); 462 // no soft-delete check needed, as ancestors of a non-deleted doc 463 // aren't deleted either 464 return new SQLInfoSelect(sql, Collections.singletonList(mainColumn), null, null); 465 } 466 467 /** 468 * Select parentid by ids for all values of several fragments. 469 */ 470 public SQLInfoSelect getSelectParentIds(int nids) { 471 Table table = database.getTable(Model.HIER_TABLE_NAME); 472 Column whatColumn = table.getColumn(Model.HIER_PARENT_KEY); 473 Column whereColumn = table.getColumn(Model.MAIN_KEY); 474 StringBuilder sb = new StringBuilder(whereColumn.getQuotedName()); 475 sb.append(" IN ("); 476 for (int i = 0; i < nids; i++) { 477 if (i != 0) { 478 sb.append(", "); 479 } 480 sb.append('?'); 481 } 482 sb.append(')'); 483 sb.append(getSoftDeleteClause(Model.HIER_TABLE_NAME)); 484 Select select = new Select(table); 485 select.setWhat("DISTINCT " + whatColumn.getQuotedName()); 486 select.setFrom(table.getQuotedName()); 487 select.setWhere(sb.toString()); 488 return new SQLInfoSelect(select.getStatement(), Collections.singletonList(whatColumn), 489 Collections.singletonList(whereColumn), null); 490 } 491 492 /** 493 * Selects all children (not complex) for several parent ids. 494 */ 495 public SQLInfoSelect getSelectChildrenNodeInfos(int nids) { 496 Table hierTable = database.getTable(Model.HIER_TABLE_NAME); 497 Column mainColumn = hierTable.getColumn(Model.MAIN_KEY); 498 List<Column> whatColumns = new ArrayList<>(); 499 whatColumns.add(mainColumn); 500 whatColumns.add(hierTable.getColumn(Model.HIER_PARENT_KEY)); 501 whatColumns.add(hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY)); 502 whatColumns.add(hierTable.getColumn(Model.MAIN_RETAIN_UNTIL_KEY)); 503 whatColumns.add(hierTable.getColumn(Model.MAIN_HAS_LEGAL_HOLD_KEY)); 504 whatColumns.add(hierTable.getColumn(Model.MAIN_IS_RETENTION_ACTIVE_KEY)); 505 Table proxyTable = null; 506 if (proxiesEnabled) { 507 proxyTable = database.getTable(Model.PROXY_TABLE_NAME); 508 whatColumns.add(proxyTable.getColumn(Model.PROXY_TARGET_KEY)); 509 whatColumns.add(proxyTable.getColumn(Model.PROXY_VERSIONABLE_KEY)); 510 } 511 String selectWhats = whatColumns.stream().map(Column::getFullQuotedName).collect(Collectors.joining(", ")); 512 Select select = new Select(null); 513 select.setWhat(selectWhats); 514 String from = hierTable.getQuotedName(); 515 if (proxiesEnabled) { 516 from += " LEFT JOIN " + proxyTable.getQuotedName() + " ON " + mainColumn.getFullQuotedName() + " = " 517 + proxyTable.getColumn(Model.MAIN_KEY).getFullQuotedName(); 518 } 519 select.setFrom(from); 520 Column whereColumn = hierTable.getColumn(Model.HIER_PARENT_KEY); 521 StringBuilder sb = new StringBuilder(whereColumn.getFullQuotedName()); 522 if (nids == 1) { 523 sb.append(" = ?"); 524 } else { 525 sb.append(" IN ("); 526 for (int i = 0; i < nids; i++) { 527 if (i != 0) { 528 sb.append(", "); 529 } 530 sb.append('?'); 531 } 532 sb.append(')'); 533 } 534 sb.append(" AND "); 535 sb.append(hierTable.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getFullQuotedName()); 536 sb.append(" = ").append(dialect.toBooleanValueString(false)); // not complex 537 sb.append(getSoftDeleteClause(Model.HIER_TABLE_NAME)); 538 select.setWhere(sb.toString()); 539 return new SQLInfoSelect(select.getStatement(), whatColumns, Collections.singletonList(whereColumn), null); 540 } 541 542 // ----- delete ----- 543 544 /** 545 * Returns the SQL {@code DELETE} to delete a row. The primary key columns are free parameters. 546 * 547 * @param tableName the table name 548 * @return the SQL {@code DELETE} statement 549 */ 550 public String getDeleteSql(String tableName) { 551 return deleteSqlMap.get(tableName); 552 } 553 554 /** 555 * Returns the SQL {@code DELETE} to delete several rows. The primary key columns are free parameters. 556 * 557 * @param tableName the table name 558 * @param n the number of rows to delete 559 * @return the SQL {@code DELETE} statement with a {@code IN} for the keys 560 */ 561 public String getDeleteSql(String tableName, int n) { 562 Table table = database.getTable(tableName); 563 Delete delete = new Delete(table); 564 String where = null; 565 for (Column column : table.getColumns()) { 566 if (column.getKey().equals(Model.MAIN_KEY)) { 567 StringBuilder sb = new StringBuilder(); 568 sb.append(column.getQuotedName()); 569 if (n == 1) { 570 sb.append(" = ?"); 571 } else { 572 sb.append(" IN ("); 573 for (int i = 0; i < n; i++) { 574 if (i > 0) { 575 sb.append(", "); 576 } 577 sb.append("?"); 578 } 579 sb.append(")"); 580 } 581 where = sb.toString(); 582 } 583 } 584 delete.setWhere(where); 585 return delete.getStatement(); 586 } 587 588 /** 589 * Returns the SQL to soft-delete several rows. The array of ids and the time are free parameters. 590 * 591 * @return the SQL statement 592 */ 593 public String getSoftDeleteSql() { 594 return dialect.getSoftDeleteSql(); 595 } 596 597 /** 598 * Returns the SQL to clean (hard-delete) soft-deleted rows. The max and beforeTime are free parameters. 599 * 600 * @return the SQL statement 601 */ 602 public String getSoftDeleteCleanupSql() { 603 return dialect.getSoftDeleteCleanupSql(); 604 } 605 606 // ----- copy ----- 607 608 public SQLInfoSelect getCopyHier(boolean explicitName, boolean resetVersion) { 609 Table table = database.getTable(Model.HIER_TABLE_NAME); 610 Collection<Column> columns = table.getColumns(); 611 List<String> selectWhats = new ArrayList<>(columns.size()); 612 List<Column> selectWhatColumns = new ArrayList<>(5); 613 Insert insert = new Insert(table); 614 for (Column column : columns) { 615 if (column.isIdentity()) { 616 // identity column is never copied 617 continue; 618 } 619 insert.addColumn(column); 620 String quotedName = column.getQuotedName(); 621 String key = column.getKey(); 622 if (key.equals(Model.MAIN_KEY) // 623 || key.equals(Model.HIER_PARENT_KEY) // 624 || key.equals(Model.MAIN_BASE_VERSION_KEY) // 625 || key.equals(Model.MAIN_CHECKED_IN_KEY) // 626 || key.equals(Model.MAIN_IS_RECORD_KEY) // 627 || (key.equals(Model.MAIN_MINOR_VERSION_KEY) && resetVersion) // 628 || (key.equals(Model.MAIN_MAJOR_VERSION_KEY) && resetVersion) // 629 || (key.equals(Model.HIER_CHILD_NAME_KEY) && explicitName)) { 630 // explicit value set 631 selectWhats.add("?"); 632 selectWhatColumns.add(column); 633 } else { 634 // otherwise copy value 635 selectWhats.add(quotedName); 636 } 637 } 638 Column whereColumn = table.getColumn(Model.MAIN_KEY); 639 Select select = new Select(null); 640 select.setFrom(table.getQuotedName()); 641 select.setWhat(String.join(", ", selectWhats)); 642 select.setWhere(whereColumn.getQuotedName() + " = ?"); 643 insert.setValues(select.getStatement()); 644 String sql = insert.getStatement(); 645 return new SQLInfoSelect(sql, selectWhatColumns, Collections.singletonList(whereColumn), null); 646 } 647 648 public String getCopySql(String tableName) { 649 return copySqlMap.get(tableName); 650 } 651 652 public Column getCopyIdColumn(String tableName) { 653 return copyIdColumnMap.get(tableName); 654 } 655 656 // ----- prepare everything ----- 657 658 /** 659 * Creates all the sql from the models. 660 */ 661 protected void initSQL(boolean requiresClusterSQL) { 662 663 // structural tables 664 if (requiresClusterSQL) { 665 if (!dialect.isClusteringSupported()) { 666 throw new NuxeoException("Clustering not supported for " + dialect.getClass().getSimpleName()); 667 } 668 initClusterSQL(); 669 } 670 initHierarchySQL(); 671 initRepositorySQL(); 672 if (dialect.supportsAncestorsTable()) { 673 initAncestorsSQL(); 674 } 675 676 for (String tableName : model.getFragmentNames()) { 677 if (tableName.equals(Model.HIER_TABLE_NAME)) { 678 continue; 679 } 680 initFragmentSQL(tableName); 681 } 682 683 /* 684 * versions 685 */ 686 687 Table hierTable = database.getTable(Model.HIER_TABLE_NAME); 688 Table versionTable = database.getTable(Model.VERSION_TABLE_NAME); 689 hierTable.addIndex(Model.MAIN_IS_VERSION_KEY); 690 versionTable.addIndex(Model.VERSION_VERSIONABLE_KEY); 691 // don't index series+label, a simple label scan will suffice 692 693 /* 694 * proxies 695 */ 696 697 if (proxiesEnabled) { 698 Table proxyTable = database.getTable(Model.PROXY_TABLE_NAME); 699 proxyTable.addIndex(Model.PROXY_VERSIONABLE_KEY); 700 proxyTable.addIndex(Model.PROXY_TARGET_KEY); 701 } 702 703 initSelectDescendantsSQL(); 704 705 /* 706 * fulltext 707 */ 708 if (!model.getRepositoryDescriptor().getFulltextDescriptor().getFulltextSearchDisabled()) { 709 Table table = database.getTable(Model.FULLTEXT_TABLE_NAME); 710 FulltextConfiguration fulltextConfiguration = model.getFulltextConfiguration(); 711 if (fulltextConfiguration.indexNames.size() > 1 && !dialect.supportsMultipleFulltextIndexes()) { 712 String msg = String.format("SQL database supports only one fulltext index, but %d are configured: %s", 713 fulltextConfiguration.indexNames.size(), fulltextConfiguration.indexNames); 714 throw new NuxeoException(msg); 715 } 716 for (String indexName : fulltextConfiguration.indexNames) { 717 String suffix = model.getFulltextIndexSuffix(indexName); 718 int ftic = dialect.getFulltextIndexedColumns(); 719 if (ftic == 1) { 720 table.addIndex(indexName, IndexType.FULLTEXT, Model.FULLTEXT_FULLTEXT_KEY + suffix); 721 } else if (ftic == 2) { 722 table.addIndex(indexName, IndexType.FULLTEXT, Model.FULLTEXT_SIMPLETEXT_KEY + suffix, 723 Model.FULLTEXT_BINARYTEXT_KEY + suffix); 724 } 725 } 726 } 727 728 /* 729 * binary columns for GC 730 */ 731 for (Entry<String, List<String>> e : model.getBinaryPropertyInfos().entrySet()) { 732 String tableName = e.getKey(); 733 Table table = database.getTable(tableName); 734 for (String key : e.getValue()) { 735 Select select = new Select(table); 736 Column col = table.getColumn(key); // key = name for now 737 select.setWhat("DISTINCT " + col.getQuotedName()); 738 select.setFrom(table.getQuotedName()); 739 getBinariesSql.add(select.getStatement()); 740 // in the result column we want the digest, not the binary 741 Column resCol = new Column(table, null, ColumnType.STRING, null); 742 getBinariesColumns.add(resCol); 743 } 744 } 745 } 746 747 protected void initClusterSQL() { 748 TableMaker maker = new TableMaker(Model.CLUSTER_NODES_TABLE_NAME); 749 maker.newColumn(Model.CLUSTER_NODES_NODEID_KEY, ColumnType.CLUSTERNODE); 750 maker.newColumn(Model.CLUSTER_NODES_CREATED_KEY, ColumnType.TIMESTAMP); 751 maker.table.addIndex(null, IndexType.UNIQUE, Model.CLUSTER_NODES_NODEID_KEY); 752 maker.postProcessClusterNodes(); 753 754 maker = new TableMaker(Model.CLUSTER_INVALS_TABLE_NAME); 755 maker.newColumn(Model.CLUSTER_INVALS_NODEID_KEY, ColumnType.CLUSTERNODE); 756 maker.newColumn(Model.CLUSTER_INVALS_ID_KEY, ColumnType.NODEVAL); 757 maker.newColumn(Model.CLUSTER_INVALS_FRAGMENTS_KEY, ColumnType.CLUSTERFRAGS); 758 maker.newColumn(Model.CLUSTER_INVALS_KIND_KEY, ColumnType.TINYINT); 759 maker.table.addIndex(Model.CLUSTER_INVALS_NODEID_KEY); 760 maker.postProcessClusterInvalidations(); 761 } 762 763 /** 764 * Creates the SQL for the table holding global repository information. This includes the id of the hierarchy root 765 * node. 766 */ 767 protected void initRepositorySQL() { 768 TableMaker maker = new TableMaker(Model.REPOINFO_TABLE_NAME); 769 maker.newColumn(Model.MAIN_KEY, ColumnType.NODEIDFK); 770 maker.newColumn(Model.REPOINFO_REPONAME_KEY, ColumnType.SYSNAME); 771 maker.table.addIndex(null, IndexType.UNIQUE, Model.REPOINFO_REPONAME_KEY); 772 maker.postProcessRepository(); 773 } 774 775 /** 776 * Creates the SQL for the table holding hierarchy information. 777 */ 778 protected void initHierarchySQL() { 779 TableMaker maker = new TableMaker(Model.HIER_TABLE_NAME); 780 // if (separateMainTable) 781 // maker.newColumn(model.MAIN_KEY, ColumnType.NODEIDFK); 782 maker.newColumn(Model.MAIN_KEY, ColumnType.NODEID); 783 maker.newColumn(Model.HIER_PARENT_KEY, ColumnType.NODEIDFKNULL); 784 maker.newColumn(Model.HIER_CHILD_POS_KEY, ColumnType.INTEGER); 785 maker.newColumn(Model.HIER_CHILD_NAME_KEY, ColumnType.STRING); 786 maker.newColumn(Model.HIER_CHILD_ISPROPERTY_KEY, ColumnType.BOOLEAN); // notnull 787 // if (!separateMainTable) 788 maker.newFragmentFields(); 789 maker.postProcess(); 790 maker.postProcessHierarchy(); 791 // if (!separateMainTable) 792 // maker.postProcessIdGeneration(); 793 794 maker.table.addIndex(Model.HIER_PARENT_KEY); 795 maker.table.addIndex(Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY); 796 // don't index parent+name+isprop, a simple isprop scan will suffice 797 maker.table.addIndex(Model.MAIN_PRIMARY_TYPE_KEY); 798 maker.table.addIndex(Model.MAIN_RETAIN_UNTIL_KEY); 799 800 if (model.getRepositoryDescriptor().getSoftDeleteEnabled()) { 801 maker.table.addIndex(Model.MAIN_IS_DELETED_KEY); 802 } 803 } 804 805 protected void initSelectDescendantsSQL() { 806 Table hierTable = database.getTable(Model.HIER_TABLE_NAME); 807 Table proxyTable = null; 808 if (proxiesEnabled) { 809 proxyTable = database.getTable(Model.PROXY_TABLE_NAME); 810 } 811 Column mainColumn = hierTable.getColumn(Model.MAIN_KEY); 812 List<Column> whatCols = new ArrayList<>(Arrays.asList(mainColumn, // 813 hierTable.getColumn(Model.HIER_PARENT_KEY), // 814 hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY), // 815 hierTable.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY), // 816 hierTable.getColumn(Model.MAIN_RETAIN_UNTIL_KEY), // 817 hierTable.getColumn(Model.MAIN_HAS_LEGAL_HOLD_KEY), // 818 hierTable.getColumn(Model.MAIN_IS_RETENTION_ACTIVE_KEY))); 819 if (proxiesEnabled) { 820 whatCols.add(proxyTable.getColumn(Model.PROXY_VERSIONABLE_KEY)); 821 whatCols.add(proxyTable.getColumn(Model.PROXY_TARGET_KEY)); 822 } 823 // no mixins, not used to decide if we have a version or proxy 824 String whats = whatCols.stream().map(Column::getFullQuotedName).collect(Collectors.joining(", ")); 825 Select select = new Select(null); 826 select.setWhat(whats); 827 String from = hierTable.getQuotedName(); 828 if (proxiesEnabled) { 829 from += " LEFT JOIN " + proxyTable.getQuotedName() + " ON " + mainColumn.getFullQuotedName() + " = " 830 + proxyTable.getColumn(Model.MAIN_KEY).getFullQuotedName(); 831 } 832 select.setFrom(from); 833 String where = dialect.getInTreeSql(mainColumn.getFullQuotedName(), null); 834 where += getSoftDeleteClause(Model.HIER_TABLE_NAME); 835 select.setWhere(where); 836 selectDescendantsInfoSql = select.getStatement(); 837 selectDescendantsInfoWhatColumns = whatCols; 838 } 839 840 /** 841 * Creates the SQL for the table holding ancestors information. 842 * <p> 843 * This table holds trigger-updated information extracted from the recursive parent-child relationship in the 844 * hierarchy table. 845 */ 846 protected void initAncestorsSQL() { 847 TableMaker maker = new TableMaker(Model.ANCESTORS_TABLE_NAME); 848 maker.newColumn(Model.MAIN_KEY, ColumnType.NODEIDFKMUL); 849 maker.newColumn(Model.ANCESTORS_ANCESTOR_KEY, ColumnType.NODEARRAY); 850 } 851 852 /** 853 * Creates the SQL for one fragment (simple or collection). 854 */ 855 protected void initFragmentSQL(String tableName) { 856 TableMaker maker = new TableMaker(tableName); 857 ColumnType type; 858 if (tableName.equals(Model.HIER_TABLE_NAME)) { 859 type = ColumnType.NODEID; 860 } else if (tableName.equals(Model.LOCK_TABLE_NAME)) { 861 type = ColumnType.NODEIDPK; // no foreign key to hierarchy 862 } else if (model.isCollectionFragment(tableName)) { 863 type = ColumnType.NODEIDFKMUL; 864 } else { 865 type = ColumnType.NODEIDFK; 866 } 867 maker.newColumn(Model.MAIN_KEY, type); 868 maker.newFragmentFields(); 869 maker.postProcess(); 870 // if (isMain) 871 // maker.postProcessIdGeneration(); 872 } 873 874 protected void initSelections() { 875 for (SelectionType selType : SelectionType.values()) { 876 if (!proxiesEnabled && selType.tableName.equals(Model.PROXY_TABLE_NAME)) { 877 continue; 878 } 879 selections.put(selType, new SQLInfoSelection(selType)); 880 } 881 } 882 883 // ----- prepare one table ----- 884 885 protected class TableMaker { 886 887 private final String tableName; 888 889 private final Table table; 890 891 private final String orderBy; 892 893 protected TableMaker(String tableName) { 894 this.tableName = tableName; 895 table = database.addTable(tableName); 896 orderBy = model.getCollectionOrderBy(tableName); 897 } 898 899 protected void newFragmentFields() { 900 Map<String, ColumnType> keysType = model.getFragmentKeysType(tableName); 901 for (Entry<String, ColumnType> entry : keysType.entrySet()) { 902 newColumn(entry.getKey(), entry.getValue()); 903 } 904 } 905 906 protected Column newColumn(String columnName, ColumnType type) { 907 Column column = table.addColumn(columnName, type, columnName, model); 908 if (type == ColumnType.NODEID) { 909 // column.setIdentity(true); if idGenPolicy identity 910 column.setNullable(false); 911 column.setPrimary(true); 912 } 913 if (type == ColumnType.NODEIDFK || type == ColumnType.NODEIDPK) { 914 column.setNullable(false); 915 column.setPrimary(true); 916 } 917 if (type == ColumnType.NODEIDFKMUL) { 918 column.setNullable(false); 919 table.addIndex(columnName); 920 } 921 if (type == ColumnType.NODEIDFK || type == ColumnType.NODEIDFKNP || type == ColumnType.NODEIDFKNULL 922 || type == ColumnType.NODEIDFKMUL) { 923 column.setReferences(database.getTable(Model.HIER_TABLE_NAME), Model.MAIN_KEY); 924 } 925 return column; 926 } 927 928 // ----------------------- post processing ----------------------- 929 930 protected void postProcessClusterNodes() { 931 Collection<Column> columns = table.getColumns(); 932 Insert insert = new Insert(table); 933 for (Column column : columns) { 934 insert.addColumn(column); 935 } 936 createClusterNodeSql = insert.getStatement(); 937 createClusterNodeColumns = new ArrayList<>(columns); 938 939 Delete delete = new Delete(table); 940 Column column = table.getColumn(Model.CLUSTER_NODES_NODEID_KEY); 941 delete.setWhere(column.getQuotedName() + " = ?"); 942 deleteClusterNodeSql = delete.getStatement(); 943 deleteClusterNodeColumn = column; 944 } 945 946 protected void postProcessClusterInvalidations() { 947 clusterInvalidationsColumns = Arrays.asList(table.getColumn(Model.CLUSTER_INVALS_NODEID_KEY), 948 table.getColumn(Model.CLUSTER_INVALS_ID_KEY), table.getColumn(Model.CLUSTER_INVALS_FRAGMENTS_KEY), 949 table.getColumn(Model.CLUSTER_INVALS_KIND_KEY)); 950 951 Delete delete = new Delete(table); 952 Column column = table.getColumn(Model.CLUSTER_INVALS_NODEID_KEY); 953 delete.setWhere(column.getQuotedName() + " = ?"); 954 deleteClusterInvalsSql = delete.getStatement(); 955 deleteClusterInvalsColumn = column; 956 } 957 958 protected void postProcessRepository() { 959 postProcessRootIdSelect(); 960 postProcessInsert(); 961 } 962 963 protected void postProcessRootIdSelect() { 964 String what = null; 965 String where = null; 966 for (Column column : table.getColumns()) { 967 String key = column.getKey(); 968 String qname = column.getQuotedName(); 969 if (key.equals(Model.MAIN_KEY)) { 970 what = qname; 971 selectRootIdWhatColumn = column; 972 } else if (key.equals(Model.REPOINFO_REPONAME_KEY)) { 973 where = qname + " = ?"; 974 } else { 975 throw new RuntimeException(column.toString()); 976 } 977 } 978 Select select = new Select(table); 979 select.setWhat(what); 980 select.setFrom(table.getQuotedName()); 981 select.setWhere(where); 982 selectRootIdSql = select.getStatement(); 983 } 984 985 /** 986 * Precompute what we can from the information available for a regular schema table, or a collection table. 987 */ 988 protected void postProcess() { 989 postProcessSelectById(); 990 postProcessInsert(); 991 postProcessDelete(); 992 postProcessCopy(); 993 } 994 995 /** 996 * Additional SQL for the hierarchy table. 997 */ 998 protected void postProcessHierarchy() { 999 postProcessSelectChildrenIdsAndTypes(); 1000 } 1001 1002 protected void postProcessSelectById() { 1003 String[] orderBys = orderBy == null ? NO_ORDER_BY : new String[] { orderBy, ORDER_ASC }; 1004 SQLInfoSelect select = makeSelect(table, orderBys, Model.MAIN_KEY); 1005 selectFragmentById.put(tableName, select); 1006 } 1007 1008 protected void postProcessSelectChildrenIdsAndTypes() { 1009 List<Column> whatColumns = new ArrayList<>(2); 1010 List<String> whats = new ArrayList<>(2); 1011 Column column = table.getColumn(Model.MAIN_KEY); 1012 whatColumns.add(column); 1013 whats.add(column.getQuotedName()); 1014 column = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY); 1015 whatColumns.add(column); 1016 whats.add(column.getQuotedName()); 1017 column = table.getColumn(Model.MAIN_MIXIN_TYPES_KEY); 1018 whatColumns.add(column); 1019 whats.add(column.getQuotedName()); 1020 column = table.getColumn(Model.MAIN_IS_RECORD_KEY); 1021 whatColumns.add(column); 1022 whats.add(column.getQuotedName()); 1023 Select select = new Select(table); 1024 select.setWhat(String.join(", ", whats)); 1025 select.setFrom(table.getQuotedName()); 1026 String where = table.getColumn(Model.HIER_PARENT_KEY).getQuotedName() + " = ?" 1027 + getSoftDeleteClause(tableName); 1028 select.setWhere(where); 1029 selectChildrenIdsAndTypesSql = select.getStatement(); 1030 1031 // Build the exclude from copy part of the where clause 1032 SchemaManager schemaManager = Framework.getService(SchemaManager.class); 1033 String excludedTypes = schemaManager.getSpecialDocumentTypes() 1034 .stream() 1035 .collect(Collectors.joining("', '")); 1036 if (!excludedTypes.isEmpty()) { 1037 String excludeSpecialChildrenClause = " AND " 1038 + table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getQuotedName() + " NOT IN ('" + excludedTypes 1039 + "')"; 1040 select.setWhere(where + excludeSpecialChildrenClause); 1041 } 1042 selectChildrenIdsAndTypesSqlExcludeSpecialChildren = select.getStatement(); 1043 1044 selectChildrenIdsAndTypesWhatColumns = whatColumns; 1045 // now only complex properties 1046 String isComplexClause = table.getColumn(Model.HIER_CHILD_ISPROPERTY_KEY).getQuotedName() + " = " 1047 + dialect.toBooleanValueString(true); 1048 String whereAnd = where + " AND " + isComplexClause; 1049 select.setWhere(whereAnd); 1050 selectComplexChildrenIdsAndTypesSql = select.getStatement(); 1051 // now only complex properties include special children 1052 if (!excludedTypes.isEmpty()) { 1053 String whereAndOr = where + " AND ( " + isComplexClause + " OR " 1054 + table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getQuotedName() + " IN ('" 1055 + excludedTypes + "') )"; 1056 select.setWhere(whereAndOr); 1057 } 1058 selectComplexChildrenIdsAndTypesSqlIncludeSpecialChildren = select.getStatement(); 1059 } 1060 1061 // TODO optimize multiple inserts into one statement for collections 1062 protected void postProcessInsert() { 1063 // insert (implicitly auto-generated sequences not included) 1064 Collection<Column> columns = table.getColumns(); 1065 List<Column> insertColumns = new ArrayList<>(columns.size()); 1066 Insert insert = new Insert(table); 1067 for (Column column : columns) { 1068 if (column.isIdentity()) { 1069 // identity column is never inserted 1070 continue; 1071 } 1072 insertColumns.add(column); 1073 insert.addColumn(column); 1074 } 1075 insertSqlMap.put(tableName, insert.getStatement()); 1076 insertColumnsMap.put(tableName, insertColumns); 1077 } 1078 1079 protected void postProcessDelete() { 1080 Delete delete = new Delete(table); 1081 String wheres = table.getColumns() 1082 .stream() 1083 .filter(col -> Model.MAIN_KEY.equals(col.getKey())) 1084 .map(col -> col.getQuotedName() + " = ?") 1085 .collect(Collectors.joining(" AND ")); 1086 delete.setWhere(wheres); 1087 deleteSqlMap.put(tableName, delete.getStatement()); 1088 } 1089 1090 // copy of a fragment 1091 // INSERT INTO foo (id, x, y) SELECT ?, x, y FROM foo WHERE id = ? 1092 protected void postProcessCopy() { 1093 Collection<Column> columns = table.getColumns(); 1094 List<String> selectWhats = new ArrayList<>(columns.size()); 1095 Column copyIdColumn = table.getColumn(Model.MAIN_KEY); 1096 Insert insert = new Insert(table); 1097 for (Column column : columns) { 1098 if (column.isIdentity()) { 1099 // identity column is never copied 1100 continue; 1101 } 1102 insert.addColumn(column); 1103 if (column == copyIdColumn) { 1104 // explicit value 1105 selectWhats.add("?"); 1106 } else { 1107 // otherwise copy value 1108 selectWhats.add(column.getQuotedName()); 1109 } 1110 } 1111 Select select = new Select(table); 1112 select.setWhat(String.join(", ", selectWhats)); 1113 select.setFrom(table.getQuotedName()); 1114 select.setWhere(copyIdColumn.getQuotedName() + " = ?"); 1115 insert.setValues(select.getStatement()); 1116 copySqlMap.put(tableName, insert.getStatement()); 1117 copyIdColumnMap.put(tableName, copyIdColumn); 1118 } 1119 1120 } 1121 1122 public static class SQLInfoSelect { 1123 1124 public final String sql; 1125 1126 public final List<Column> whatColumns; 1127 1128 public final MapMaker mapMaker; 1129 1130 public final List<Column> whereColumns; 1131 1132 public final List<Column> opaqueColumns; 1133 1134 /** 1135 * Standard select for given columns. 1136 */ 1137 public SQLInfoSelect(String sql, List<Column> whatColumns, List<Column> whereColumns, 1138 List<Column> opaqueColumns) { 1139 this(sql, whatColumns, null, whereColumns, opaqueColumns); 1140 } 1141 1142 /** 1143 * Select where some column keys may be aliased, and some columns may be computed. The {@link MapMaker} is used 1144 * by the queryAndFetch() method. 1145 */ 1146 public SQLInfoSelect(String sql, MapMaker mapMaker) { 1147 this(sql, null, mapMaker, null, null); 1148 } 1149 1150 public SQLInfoSelect(String sql, List<Column> whatColumns, MapMaker mapMaker, List<Column> whereColumns, 1151 List<Column> opaqueColumns) { 1152 this.sql = sql; 1153 this.whatColumns = whatColumns; 1154 this.mapMaker = mapMaker; 1155 this.whereColumns = whereColumns == null ? null : new ArrayList<>(whereColumns); 1156 this.opaqueColumns = opaqueColumns == null ? null : new ArrayList<>(opaqueColumns); 1157 } 1158 } 1159 1160 /** 1161 * Info about how to do the query to get a {@link Selection}. 1162 */ 1163 public class SQLInfoSelection { 1164 1165 public final SelectionType type; 1166 1167 public final SQLInfoSelect selectAll; 1168 1169 public final SQLInfoSelect selectFiltered; 1170 1171 public SQLInfoSelection(SelectionType selType) { 1172 this.type = selType; 1173 Table table = database.getTable(selType.tableName); 1174 SQLInfoSelect selectAll; 1175 SQLInfoSelect selectFiltered; 1176 String from = table.getQuotedName(); 1177 List<String> clauses; 1178 if (selType.tableName.equals(Model.HIER_TABLE_NAME)) { 1179 // clause already added by makeSelect 1180 clauses = null; 1181 } else { 1182 Table hierTable = database.getTable(Model.HIER_TABLE_NAME); 1183 Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null, 1184 hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY)); 1185 from += join.toSql(dialect); 1186 String clause = getSoftDeleteClause(); 1187 clauses = clause == null ? null : Collections.singletonList(clause); 1188 } 1189 if (selType.criterionKey == null) { 1190 selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey); 1191 selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey); 1192 } else { 1193 selectAll = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.criterionKey); 1194 selectFiltered = makeSelect(table, from, clauses, NO_ORDER_BY, selType.selKey, selType.filterKey, 1195 selType.criterionKey); 1196 } 1197 this.selectAll = selectAll; 1198 this.selectFiltered = selectFiltered; 1199 } 1200 1201 /** 1202 * Select selection ids for multiple values. 1203 */ 1204 public SQLInfoSelect getSelectSelectionIds(int nids) { 1205 Table table = database.getTable(type.tableName); 1206 String from = table.getQuotedName(); 1207 Table hierTable = database.getTable(Model.HIER_TABLE_NAME); 1208 Join join = new Join(Join.INNER, hierTable.getQuotedName(), null, null, 1209 hierTable.getColumn(Model.MAIN_KEY), table.getColumn(Model.MAIN_KEY)); 1210 from += join.toSql(dialect); 1211 1212 Column whatColumn = table.getColumn(Model.MAIN_KEY); 1213 Column whereColumn = table.getColumn(type.selKey); 1214 StringBuilder sb = new StringBuilder(whereColumn.getQuotedName()); 1215 sb.append(" IN ("); 1216 for (int i = 0; i < nids; i++) { 1217 if (i != 0) { 1218 sb.append(", "); 1219 } 1220 sb.append('?'); 1221 } 1222 sb.append(')'); 1223 sb.append(getSoftDeleteClause(Model.HIER_TABLE_NAME)); 1224 Select select = new Select(table); 1225 select.setWhat(whatColumn.getFullQuotedName()); 1226 select.setFrom(from); 1227 select.setWhere(sb.toString()); 1228 return new SQLInfoSelect(select.getStatement(), Collections.singletonList(whatColumn), 1229 Collections.singletonList(whereColumn), null); 1230 } 1231 } 1232 1233 /** 1234 * Knows how to build a result map for a row given a {@link ResultSet}. This abstraction may be used to compute some 1235 * values on the fly. 1236 */ 1237 public interface MapMaker { 1238 Map<String, Serializable> makeMap(ResultSet rs) throws SQLException; 1239 } 1240 1241 /** 1242 * Builds the map from a result set given a list of columns and column keys. 1243 */ 1244 public static class ColumnMapMaker implements MapMaker { 1245 public final List<Column> columns; 1246 1247 public final List<String> keys; 1248 1249 public ColumnMapMaker(List<Column> columns) { 1250 this.columns = columns; 1251 this.keys = columns.stream().map(Column::getKey).collect(Collectors.toList()); 1252 } 1253 1254 public ColumnMapMaker(List<Column> columns, List<String> keys) { 1255 this.columns = columns; 1256 this.keys = keys; 1257 } 1258 1259 @Override 1260 public Map<String, Serializable> makeMap(ResultSet rs) throws SQLException { 1261 Map<String, Serializable> map = new HashMap<>(); 1262 int i = 1; 1263 for (Column column : columns) { 1264 String key = keys.get(i - 1); 1265 Serializable value = column.getFromResultSet(rs, i++); 1266 if (NXQL.ECM_UUID.equals(key) || NXQL.ECM_PARENTID.equals(key)) { 1267 value = String.valueOf(value); // idToString 1268 } 1269 map.put(key, value); 1270 } 1271 return map; 1272 } 1273 } 1274 1275 private static String[] NO_ORDER_BY = new String[0]; 1276 1277 /** 1278 * Basic SELECT x, y, z FROM table WHERE a = ? AND b = ? 1279 * <p> 1280 * with optional ORDER BY x, y DESC 1281 */ 1282 public SQLInfoSelect makeSelect(Table table, String[] orderBys, String... freeColumns) { 1283 return makeSelect(table, null, null, orderBys, freeColumns); 1284 } 1285 1286 /** 1287 * Same as above but the FROM can be passed in, to allow JOINs. 1288 */ 1289 public SQLInfoSelect makeSelect(Table table, String from, List<String> clauses, String[] orderBys, 1290 String... freeColumns) { 1291 boolean fullQuotedName = from != null; 1292 List<String> freeColumnsList = Arrays.asList(freeColumns); 1293 List<Column> whatColumns = new LinkedList<>(); 1294 List<Column> whereColumns = new LinkedList<>(); 1295 List<Column> opaqueColumns = new LinkedList<>(); 1296 List<String> whats = new LinkedList<>(); 1297 List<String> wheres = new LinkedList<>(); 1298 for (Column column : table.getColumns()) { 1299 String qname = fullQuotedName ? column.getFullQuotedName() : column.getQuotedName(); 1300 if (freeColumnsList.contains(column.getKey())) { 1301 whereColumns.add(column); 1302 wheres.add(qname + " = ?"); 1303 } else if (column.isOpaque()) { 1304 opaqueColumns.add(column); 1305 } else { 1306 whatColumns.add(column); 1307 whats.add(qname); 1308 } 1309 } 1310 if (whats.isEmpty()) { 1311 // only opaque columns, don't generate an illegal SELECT 1312 whats.add(table.getColumn(Model.MAIN_KEY).getQuotedName()); 1313 } 1314 if (clauses != null) { 1315 wheres.addAll(clauses); 1316 } 1317 Select select = new Select(table); 1318 select.setWhat(String.join(", ", whats)); 1319 if (from == null) { 1320 from = table.getQuotedName(); 1321 } 1322 select.setFrom(from); 1323 String where = String.join(" AND ", wheres) + getSoftDeleteClause(table.getKey()); 1324 select.setWhere(where); 1325 List<String> orders = new LinkedList<>(); 1326 for (int i = 0; i < orderBys.length; i++) { 1327 String name = orderBys[i++]; 1328 String ascdesc = orderBys[i].equals(ORDER_DESC) ? " " + ORDER_DESC : ""; 1329 Column col = table.getColumn(name); 1330 String qcol = fullQuotedName ? col.getFullQuotedName() : col.getQuotedName(); 1331 orders.add(qcol + ascdesc); 1332 } 1333 select.setOrderBy(String.join(", ", orders)); 1334 return new SQLInfoSelect(select.getStatement(), whatColumns, whereColumns, 1335 opaqueColumns.isEmpty() ? null : opaqueColumns); 1336 } 1337 1338 public void initSQLStatements(Map<String, Serializable> testProps, List<String> sqlInitFiles) throws IOException { 1339 sqlStatements = new HashMap<>(); 1340 SQLStatement.read(dialect.getSQLStatementsFilename(), sqlStatements); 1341 if (sqlInitFiles != null) { 1342 for (String filename : sqlInitFiles) { 1343 SQLStatement.read(filename, sqlStatements); 1344 } 1345 } 1346 if (!testProps.isEmpty()) { 1347 SQLStatement.read(dialect.getTestSQLStatementsFilename(), sqlStatements, true); // DDL time 1348 } 1349 sqlStatementsProperties = dialect.getSQLStatementsProperties(model, database); 1350 if (!testProps.isEmpty()) { 1351 sqlStatementsProperties.putAll(testProps); 1352 } 1353 } 1354 1355 /** 1356 * Executes the SQL statements for the given category. 1357 */ 1358 public void executeSQLStatements(String category, String ddlMode, Connection connection, JDBCLogger logger, 1359 ListCollector ddlCollector) throws SQLException { 1360 List<SQLStatement> statements = sqlStatements.get(category); 1361 if (statements != null) { 1362 SQLStatement.execute(statements, ddlMode, sqlStatementsProperties, dialect, connection, logger, 1363 ddlCollector); 1364 } 1365 } 1366 1367 public int getMaximumArgsForIn() { 1368 return dialect.getMaximumArgsForIn(); 1369 } 1370 1371}