001/*
002 * (C) Copyright 2006-2014 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 *     George Lefter
018 *     Florent Guillaume
019 *     Julien Carsique
020 */
021package org.nuxeo.ecm.directory.sql;
022
023import static org.nuxeo.ecm.directory.BaseDirectoryDescriptor.CREATE_TABLE_POLICY_NEVER;
024import static org.nuxeo.ecm.directory.BaseDirectoryDescriptor.CREATE_TABLE_POLICY_ON_MISSING_COLUMNS;
025
026import java.sql.Connection;
027import java.sql.DatabaseMetaData;
028import java.sql.ResultSet;
029import java.sql.SQLException;
030import java.sql.Statement;
031import java.util.HashSet;
032import java.util.Set;
033
034import org.apache.commons.logging.Log;
035import org.apache.commons.logging.LogFactory;
036import org.nuxeo.ecm.core.storage.sql.ColumnType;
037import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
038import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
039import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
040import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableImpl;
041import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect;
042import org.nuxeo.ecm.directory.DirectoryException;
043
044public class SQLHelper {
045
046    private static final Log log = LogFactory.getLog(SQLHelper.class);
047
048    private static final Object DIRECTORY_INIT_LOCK = new Object();
049
050    private final Table table;
051
052    private final String tableName;
053
054    private final Connection connection;
055
056    private final String policy;
057
058    private JDBCLogger logger = new JDBCLogger("SQLDirectory");
059
060    public SQLHelper(Connection connection, Table table, String policy) {
061        this.table = table;
062        this.connection = connection;
063        this.policy = policy;
064        tableName = table.getPhysicalName();
065    }
066
067    /**
068     * Sets up the table without loading the data in it.
069     *
070     * @return {@code true} if CSV data should be loaded
071     */
072    public boolean setupTable() throws DirectoryException {
073        log.debug(String.format("setting up table '%s', policy='%s'", tableName, policy));
074        if (policy.equals(CREATE_TABLE_POLICY_NEVER)) {
075            log.debug("policy='" + CREATE_TABLE_POLICY_NEVER + "', skipping setup");
076            return false;
077        }
078        synchronized (DIRECTORY_INIT_LOCK) {
079            boolean tableExists = tableExists();
080            // check the field names match the column names
081            if (policy.equals(CREATE_TABLE_POLICY_ON_MISSING_COLUMNS) && tableExists) {
082                if (hasMatchingColumns()) {
083                    // all required columns were found
084                    log.debug("policy='" + CREATE_TABLE_POLICY_ON_MISSING_COLUMNS
085                            + "' and all column matched, skipping data load");
086                } else {
087                    log.debug("policy='" + CREATE_TABLE_POLICY_ON_MISSING_COLUMNS + "' and some columns are missing");
088                    addMissingColumns();
089                }
090                return false;
091            } // else policy=always or table doesn't exist
092            createTable(tableExists);
093            return true; // load data
094        }
095    }
096
097    private void addMissingColumns() throws DirectoryException {
098        try {
099            Statement stmt = connection.createStatement();
100
101            for (Column column : getMissingColumns(false)) {
102                String alter = table.getAddColumnSql(column);
103                if (logger.isLogEnabled()) {
104                    logger.log(alter);
105                }
106                stmt.execute(alter);
107            }
108        } catch (SQLException e) {
109            throw new DirectoryException(String.format("Table '%s' alteration failed: %s", table, e.getMessage()), e);
110        }
111    }
112
113    private void createTable(boolean drop) throws DirectoryException {
114        try (Statement stmt = connection.createStatement()) {
115            if (drop) {
116                // drop table
117                String dropSql = table.getDropSql();
118                if (logger.isLogEnabled()) {
119                    logger.log(dropSql);
120                }
121                stmt.execute(dropSql);
122            }
123
124            String createSql = table.getCreateSql();
125            if (logger.isLogEnabled()) {
126                logger.log(createSql);
127            }
128            stmt.execute(createSql);
129            for (String sql : table.getPostCreateSqls(null)) {
130                if (logger.isLogEnabled()) {
131                    logger.log(sql);
132                }
133                stmt.execute(sql);
134            }
135        } catch (SQLException e) {
136            throw new DirectoryException(String.format("Table '%s' creation failed: %s", table, e.getMessage()), e);
137        }
138    }
139
140    public boolean hasMatchingColumns() {
141        Set<Column> missingColumns = getMissingColumns(true);
142        if (missingColumns == null || missingColumns.size() > 0) {
143            return false;
144        } else {
145            // all fields have a matching column, this looks not that bad
146            log.debug(String.format("all fields matched for table '%s'", tableName));
147            return true;
148        }
149    }
150
151    public Set<Column> getMissingColumns(Boolean breakAtFirstMissing) {
152        try {
153            Set<Column> missingColumns = new HashSet<>();
154
155            // Test whether there are new fields added in the schema that are
156            // not present in the table schema. If so it is advised to
157            // reinitialise the database.
158
159            Set<String> columnNames = getPhysicalColumns();
160
161            // check the field names match the column names (case-insensitive)
162            for (Column column : table.getColumns()) {
163                // TODO: check types as well
164                String fieldName = column.getPhysicalName();
165                if (!columnNames.contains(fieldName)) {
166                    log.debug(String.format("required field: %s is missing", fieldName));
167                    missingColumns.add(column);
168
169                    if (breakAtFirstMissing) {
170                        return null;
171                    }
172                }
173            }
174
175            return missingColumns;
176        } catch (SQLException e) {
177            log.warn("error while introspecting table: " + tableName, e);
178            return null;
179        }
180    }
181
182    private Set<String> getPhysicalColumns() throws SQLException {
183        ResultSet rs = null;
184        Set<String> columnNames = new HashSet<>();
185        try {
186            // fetch the database columns definitions
187            DatabaseMetaData metadata = connection.getMetaData();
188            rs = metadata.getColumns(null, "%", tableName, "%");
189
190            while (rs.next()) {
191                columnNames.add(rs.getString("COLUMN_NAME"));
192            }
193        } finally {
194            if (rs != null) {
195                try {
196                    rs.close();
197                } catch (SQLException e) {
198                    log.warn("Error while trying to close result set", e);
199                }
200            }
201        }
202        return columnNames;
203    }
204
205    private boolean tableExists() throws DirectoryException {
206        try {
207            // Check if table exists using metadata
208            DatabaseMetaData metaData = connection.getMetaData();
209            String schemaName = null;
210            String productName = metaData.getDatabaseProductName();
211            if ("Oracle".equals(productName)) {
212                Statement st = connection.createStatement();
213                String sql = "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL";
214                log.trace("SQL: " + sql);
215                ResultSet rs = st.executeQuery(sql);
216                rs.next();
217                schemaName = rs.getString(1);
218                log.trace("checking existing tables for oracle database, schema: " + schemaName);
219                rs.close();
220                st.close();
221            }
222            ResultSet rs = metaData.getTables(null, schemaName, table.getPhysicalName(), new String[] { "TABLE" });
223            boolean exists = rs.next();
224            rs.close();
225            log.debug(String.format("checking if table %s exists: %s", table.getPhysicalName(), Boolean.valueOf(exists)));
226            return exists;
227        } catch (SQLException e) {
228            throw new DirectoryException(e);
229        }
230    }
231
232    public static Table addTable(String name, Dialect dialect, boolean nativeCase) {
233        String physicalName = dialect.getTableName(name);
234        if (!nativeCase && name.length() == physicalName.length()) {
235            // we can keep the name specified in the config
236            physicalName = name;
237        }
238        return new TableImpl(dialect, physicalName, physicalName);
239    }
240
241    public static Column addColumn(Table table, String fieldName, ColumnType type, boolean nativeCase) {
242        String physicalName = table.getDialect().getColumnName(fieldName);
243        if (!nativeCase && fieldName.length() == physicalName.length()) {
244            // we can keep the name specified in the config
245            physicalName = fieldName;
246        }
247        Column column = new Column(table, physicalName, type, fieldName);
248        return ((TableImpl) table).addColumn(fieldName, column);
249    }
250
251}