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