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 * Olivier Grisel 018 * Florent Guillaume 019 */ 020package org.nuxeo.ecm.directory.sql; 021 022import java.io.Serializable; 023import java.sql.PreparedStatement; 024import java.sql.ResultSet; 025import java.sql.SQLException; 026import java.util.Arrays; 027import java.util.Collections; 028import java.util.HashSet; 029import java.util.LinkedList; 030import java.util.List; 031import java.util.Set; 032 033import org.nuxeo.common.xmap.annotation.XNode; 034import org.nuxeo.common.xmap.annotation.XObject; 035import org.nuxeo.ecm.core.storage.sql.ColumnType; 036import org.nuxeo.ecm.core.storage.sql.jdbc.db.Delete; 037import org.nuxeo.ecm.core.storage.sql.jdbc.db.Insert; 038import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select; 039import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 040import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table.IndexType; 041import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect; 042import org.nuxeo.ecm.directory.AbstractReference; 043import org.nuxeo.ecm.directory.Directory; 044import org.nuxeo.ecm.directory.DirectoryException; 045 046@XObject(value = "tableReference") 047public class TableReference extends AbstractReference { 048 049 @XNode("@field") 050 public void setFieldName(String fieldName) { 051 this.fieldName = fieldName; 052 } 053 054 @Override 055 @XNode("@directory") 056 public void setTargetDirectoryName(String targetDirectoryName) { 057 this.targetDirectoryName = targetDirectoryName; 058 } 059 060 @XNode("@table") 061 protected String tableName; 062 063 @XNode("@sourceColumn") 064 protected String sourceColumn; 065 066 @XNode("@targetColumn") 067 protected String targetColumn; 068 069 @XNode("@schema") 070 protected String schemaName; 071 072 @XNode("@dataFile") 073 protected String dataFileName; 074 075 private Table table; 076 077 private Dialect dialect; 078 079 private boolean initialized = false; 080 081 private SQLDirectory getSQLSourceDirectory() throws DirectoryException { 082 Directory dir = getSourceDirectory(); 083 return (SQLDirectory) dir; 084 } 085 086 private void initialize(SQLSession sqlSession) throws DirectoryException { 087 SQLDirectory directory = getSQLSourceDirectory(); 088 String createTablePolicy = directory.getConfig().createTablePolicy; 089 Table table = getTable(); 090 SQLHelper helper = new SQLHelper(sqlSession.sqlConnection, table, dataFileName, createTablePolicy); 091 helper.setupTable(); 092 } 093 094 @Override 095 public void addLinks(String sourceId, List<String> targetIds) throws DirectoryException { 096 if (targetIds == null) { 097 return; 098 } 099 try (SQLSession session = getSQLSession()) { 100 addLinks(sourceId, targetIds, session); 101 } 102 } 103 104 @Override 105 public void addLinks(List<String> sourceIds, String targetId) throws DirectoryException { 106 if (sourceIds == null) { 107 return; 108 } 109 try (SQLSession session = getSQLSession()) { 110 addLinks(sourceIds, targetId, session); 111 } 112 } 113 114 public void addLinks(String sourceId, List<String> targetIds, SQLSession session) throws DirectoryException { 115 if (targetIds == null) { 116 return; 117 } 118 for (String targetId : targetIds) { 119 addLink(sourceId, targetId, session, true); 120 } 121 } 122 123 public void addLinks(List<String> sourceIds, String targetId, SQLSession session) throws DirectoryException { 124 if (sourceIds == null) { 125 return; 126 } 127 for (String sourceId : sourceIds) { 128 addLink(sourceId, targetId, session, true); 129 } 130 } 131 132 public boolean exists(String sourceId, String targetId, SQLSession session) throws DirectoryException { 133 // String selectSql = String.format( 134 // "SELECT COUNT(*) FROM %s WHERE %s = ? AND %s = ?", tableName, 135 // sourceColumn, targetColumn); 136 137 Table table = getTable(); 138 Select select = new Select(table); 139 select.setFrom(table.getQuotedName()); 140 select.setWhat("count(*)"); 141 String whereString = String.format("%s = ? and %s = ?", table.getColumn(sourceColumn).getQuotedName(), 142 table.getColumn(targetColumn).getQuotedName()); 143 144 select.setWhere(whereString); 145 146 String selectSql = select.getStatement(); 147 if (session.logger.isLogEnabled()) { 148 session.logger.logSQL(selectSql, Arrays.<Serializable> asList(sourceId, targetId)); 149 } 150 151 PreparedStatement ps = null; 152 ResultSet rs = null; 153 try { 154 ps = session.sqlConnection.prepareStatement(selectSql); 155 ps.setString(1, sourceId); 156 ps.setString(2, targetId); 157 rs = ps.executeQuery(); 158 rs.next(); 159 return rs.getInt(1) > 0; 160 } catch (SQLException e) { 161 throw new DirectoryException(String.format("error reading link from %s to %s", sourceId, targetId), e); 162 } finally { 163 try { 164 if (rs != null) { 165 rs.close(); 166 } 167 if (ps != null) { 168 ps.close(); 169 } 170 } catch (SQLException sqle) { 171 throw new DirectoryException(sqle); 172 } 173 } 174 } 175 176 public void addLink(String sourceId, String targetId, SQLSession session, boolean checkExisting) 177 throws DirectoryException { 178 // OG: the following query should have avoided the round trips but 179 // does not work for some reason that might be related to a bug in the 180 // JDBC driver: 181 // 182 // String sql = String.format( 183 // "INSERT INTO %s (%s, %s) (SELECT ?, ? FROM %s WHERE %s = ? AND %s = 184 // ? HAVING COUNT(*) = 0)", tableName, sourceColumn, targetColumn, 185 // tableName, sourceColumn, targetColumn); 186 187 // first step: check that this link does not exist yet 188 if (checkExisting && exists(sourceId, targetId, session)) { 189 return; 190 } 191 192 // second step: add the link 193 194 // String insertSql = String.format( 195 // "INSERT INTO %s (%s, %s) VALUES (?, ?)", tableName, 196 // sourceColumn, targetColumn); 197 Table table = getTable(); 198 Insert insert = new Insert(table); 199 insert.addColumn(table.getColumn(sourceColumn)); 200 insert.addColumn(table.getColumn(targetColumn)); 201 String insertSql = insert.getStatement(); 202 if (session.logger.isLogEnabled()) { 203 session.logger.logSQL(insertSql, Arrays.<Serializable> asList(sourceId, targetId)); 204 } 205 206 PreparedStatement ps = null; 207 try { 208 ps = session.sqlConnection.prepareStatement(insertSql); 209 ps.setString(1, sourceId); 210 ps.setString(2, targetId); 211 ps.execute(); 212 } catch (SQLException e) { 213 throw new DirectoryException(String.format("error adding link from %s to %s", sourceId, targetId), e); 214 } finally { 215 try { 216 if (ps != null) { 217 ps.close(); 218 } 219 } catch (SQLException sqle) { 220 throw new DirectoryException(sqle); 221 } 222 } 223 } 224 225 protected List<String> getIdsFor(String valueColumn, String filterColumn, String filterValue) 226 throws DirectoryException { 227 try (SQLSession session = getSQLSession()) { 228 // String sql = String.format("SELECT %s FROM %s WHERE %s = ?", 229 // table.getColumn(valueColumn), tableName, filterColumn); 230 Table table = getTable(); 231 Select select = new Select(table); 232 select.setWhat(table.getColumn(valueColumn).getQuotedName()); 233 select.setFrom(table.getQuotedName()); 234 select.setWhere(table.getColumn(filterColumn).getQuotedName() + " = ?"); 235 236 String sql = select.getStatement(); 237 if (session.logger.isLogEnabled()) { 238 session.logger.logSQL(sql, Collections.<Serializable> singleton(filterValue)); 239 } 240 241 List<String> ids = new LinkedList<String>(); 242 try (PreparedStatement ps = session.sqlConnection.prepareStatement(sql)) { 243 ps.setString(1, filterValue); 244 try (ResultSet rs = ps.executeQuery()) { 245 while (rs.next()) { 246 ids.add(rs.getString(valueColumn)); 247 } 248 return ids; 249 } 250 } catch (SQLException e) { 251 throw new DirectoryException("error fetching reference values: ", e); 252 } 253 } 254 } 255 256 @Override 257 public List<String> getSourceIdsForTarget(String targetId) throws DirectoryException { 258 return getIdsFor(sourceColumn, targetColumn, targetId); 259 } 260 261 @Override 262 public List<String> getTargetIdsForSource(String sourceId) throws DirectoryException { 263 return getIdsFor(targetColumn, sourceColumn, sourceId); 264 } 265 266 public void removeLinksFor(String column, String entryId, SQLSession session) throws DirectoryException { 267 Table table = getTable(); 268 String sql = String.format("DELETE FROM %s WHERE %s = ?", table.getQuotedName(), table.getColumn(column) 269 .getQuotedName()); 270 if (session.logger.isLogEnabled()) { 271 session.logger.logSQL(sql, Collections.<Serializable> singleton(entryId)); 272 } 273 PreparedStatement ps = null; 274 try { 275 ps = session.sqlConnection.prepareStatement(sql); 276 ps.setString(1, entryId); 277 ps.execute(); 278 } catch (SQLException e) { 279 throw new DirectoryException("error remove links to " + entryId, e); 280 } finally { 281 try { 282 if (ps != null) { 283 ps.close(); 284 } 285 } catch (SQLException sqle) { 286 throw new DirectoryException(sqle); 287 } 288 } 289 } 290 291 public void removeLinksForSource(String sourceId, SQLSession session) throws DirectoryException { 292 removeLinksFor(sourceColumn, sourceId, session); 293 } 294 295 public void removeLinksForTarget(String targetId, SQLSession session) throws DirectoryException { 296 removeLinksFor(targetColumn, targetId, session); 297 } 298 299 @Override 300 public void removeLinksForSource(String sourceId) throws DirectoryException { 301 try (SQLSession session = getSQLSession()) { 302 removeLinksForSource(sourceId, session); 303 } 304 } 305 306 @Override 307 public void removeLinksForTarget(String targetId) throws DirectoryException { 308 try (SQLSession session = getSQLSession()) { 309 removeLinksForTarget(targetId, session); 310 } 311 } 312 313 public void setIdsFor(String idsColumn, List<String> ids, String filterColumn, String filterValue, 314 SQLSession session) throws DirectoryException { 315 316 List<String> idsToDelete = new LinkedList<String>(); 317 Set<String> idsToAdd = new HashSet<String>(); 318 if (ids != null) { // ids may be null 319 idsToAdd.addAll(ids); 320 } 321 Table table = getTable(); 322 323 // iterate over existing links to find what to add and what to remove 324 String selectSql = String.format("SELECT %s FROM %s WHERE %s = ?", table.getColumn(idsColumn).getQuotedName(), 325 table.getQuotedName(), table.getColumn(filterColumn).getQuotedName()); 326 PreparedStatement ps = null; 327 try { 328 ps = session.sqlConnection.prepareStatement(selectSql); 329 ps.setString(1, filterValue); 330 ResultSet rs = ps.executeQuery(); 331 while (rs.next()) { 332 String existingId = rs.getString(1); 333 if (idsToAdd.contains(existingId)) { 334 // to not add already existing ids 335 idsToAdd.remove(existingId); 336 } else { 337 // delete unwanted existing ids 338 idsToDelete.add(existingId); 339 } 340 } 341 rs.close(); 342 } catch (SQLException e) { 343 throw new DirectoryException("failed to fetch existing links for " + filterValue, e); 344 } finally { 345 try { 346 if (ps != null) { 347 ps.close(); 348 } 349 } catch (SQLException sqle) { 350 throw new DirectoryException(sqle); 351 } 352 } 353 354 if (!idsToDelete.isEmpty()) { 355 // remove unwanted links 356 357 // String deleteSql = String.format( 358 // "DELETE FROM %s WHERE %s = ? AND %s = ?", tableName, 359 // filterColumn, idsColumn); 360 Delete delete = new Delete(table); 361 String whereString = String.format("%s = ? AND %s = ?", table.getColumn(filterColumn).getQuotedName(), 362 table.getColumn(idsColumn).getQuotedName()); 363 delete.setWhere(whereString); 364 String deleteSql = delete.getStatement(); 365 366 try { 367 ps = session.sqlConnection.prepareStatement(deleteSql); 368 for (String unwantedId : idsToDelete) { 369 if (session.logger.isLogEnabled()) { 370 session.logger.logSQL(deleteSql, Arrays.<Serializable> asList(filterValue, unwantedId)); 371 } 372 ps.setString(1, filterValue); 373 ps.setString(2, unwantedId); 374 ps.execute(); 375 } 376 } catch (SQLException e) { 377 throw new DirectoryException("failed to remove unwanted links for " + filterValue, e); 378 } finally { 379 try { 380 if (ps != null) { 381 ps.close(); 382 } 383 } catch (SQLException sqle) { 384 throw new DirectoryException(sqle); 385 } 386 } 387 } 388 389 if (!idsToAdd.isEmpty()) { 390 // add missing links 391 if (filterColumn.equals(sourceColumn)) { 392 for (String missingId : idsToAdd) { 393 addLink(filterValue, missingId, session, false); 394 } 395 } else { 396 for (String missingId : idsToAdd) { 397 addLink(missingId, filterValue, session, false); 398 } 399 } 400 } 401 } 402 403 public void setSourceIdsForTarget(String targetId, List<String> sourceIds, SQLSession session) 404 throws DirectoryException { 405 setIdsFor(sourceColumn, sourceIds, targetColumn, targetId, session); 406 } 407 408 public void setTargetIdsForSource(String sourceId, List<String> targetIds, SQLSession session) 409 throws DirectoryException { 410 setIdsFor(targetColumn, targetIds, sourceColumn, sourceId, session); 411 } 412 413 @Override 414 public void setSourceIdsForTarget(String targetId, List<String> sourceIds) throws DirectoryException { 415 try (SQLSession session = getSQLSession()) { 416 setSourceIdsForTarget(targetId, sourceIds, session); 417 } 418 } 419 420 @Override 421 public void setTargetIdsForSource(String sourceId, List<String> targetIds) throws DirectoryException { 422 try (SQLSession session = getSQLSession()) { 423 setTargetIdsForSource(sourceId, targetIds, session); 424 } 425 } 426 427 // TODO add support for the ListDiff type 428 429 protected SQLSession getSQLSession() throws DirectoryException { 430 if (!initialized) { 431 try (SQLSession sqlSession = (SQLSession) getSourceDirectory().getSession()) { 432 initialize(sqlSession); 433 initialized = true; 434 } 435 } 436 return (SQLSession) getSourceDirectory().getSession(); 437 } 438 439 /** 440 * Initialize if needed, using an existing session. 441 * 442 * @param sqlSession 443 * @throws DirectoryException 444 */ 445 protected void maybeInitialize(SQLSession sqlSession) throws DirectoryException { 446 if (!initialized) { 447 initialize(sqlSession); 448 initialized = true; 449 } 450 } 451 452 public Table getTable() throws DirectoryException { 453 if (table == null) { 454 boolean nativeCase = getSQLSourceDirectory().useNativeCase(); 455 table = SQLHelper.addTable(tableName, getDialect(), nativeCase); 456 SQLHelper.addColumn(table, sourceColumn, ColumnType.STRING, nativeCase); 457 SQLHelper.addColumn(table, targetColumn, ColumnType.STRING, nativeCase); 458 // index added for Azure 459 table.addIndex(null, IndexType.MAIN_NON_PRIMARY, sourceColumn); 460 } 461 return table; 462 } 463 464 private Dialect getDialect() throws DirectoryException { 465 if (dialect == null) { 466 dialect = getSQLSourceDirectory().getDialect(); 467 } 468 return dialect; 469 } 470 471 public String getSourceColumn() { 472 return sourceColumn; 473 } 474 475 public String getTargetColumn() { 476 return targetColumn; 477 } 478 479 public String getTargetDirectoryName() { 480 return targetDirectoryName; 481 } 482 483 public String getTableName() { 484 return tableName; 485 } 486 487 public String getSchemaName() { 488 return schemaName; 489 } 490 491 public String getDataFileName() { 492 return dataFileName; 493 } 494 495 /** 496 * @since 5.6 497 */ 498 @Override 499 protected AbstractReference newInstance() { 500 return new TableReference(); 501 } 502 503 /** 504 * @since 5.6 505 */ 506 @Override 507 public TableReference clone() { 508 TableReference clone = (TableReference) super.clone(); 509 clone.tableName = tableName; 510 clone.sourceColumn = sourceColumn; 511 clone.targetColumn = targetColumn; 512 clone.schemaName = schemaName; 513 clone.dataFileName = dataFileName; 514 return clone; 515 } 516 517}