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}