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}