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