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