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