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