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 (Statement stmt = connection.createStatement()) {
099
100            for (Column column : getMissingColumns(false)) {
101                String alter = table.getAddColumnSql(column);
102                if (logger.isLogEnabled()) {
103                    logger.log(alter);
104                }
105                stmt.execute(alter);
106            }
107        } catch (SQLException e) {
108            throw new DirectoryException(String.format("Table '%s' alteration failed: %s", table, e.getMessage()), e);
109        }
110    }
111
112    private void createTable(boolean drop) throws DirectoryException {
113        try (Statement stmt = connection.createStatement()) {
114            if (drop) {
115                // drop table
116                String dropSql = table.getDropSql();
117                if (logger.isLogEnabled()) {
118                    logger.log(dropSql);
119                }
120                stmt.execute(dropSql);
121            }
122
123            String createSql = table.getCreateSql();
124            if (logger.isLogEnabled()) {
125                logger.log(createSql);
126            }
127            stmt.execute(createSql);
128            for (String sql : table.getPostCreateSqls(null)) {
129                if (logger.isLogEnabled()) {
130                    logger.log(sql);
131                }
132                stmt.execute(sql);
133            }
134        } catch (SQLException e) {
135            throw new DirectoryException(String.format("Table '%s' creation failed: %s", table, e.getMessage()), e);
136        }
137    }
138
139    public boolean hasMatchingColumns() {
140        Set<Column> missingColumns = getMissingColumns(true);
141        if (missingColumns == null || missingColumns.size() > 0) {
142            return false;
143        } else {
144            // all fields have a matching column, this looks not that bad
145            log.debug(String.format("all fields matched for table '%s'", tableName));
146            return true;
147        }
148    }
149
150    public Set<Column> getMissingColumns(Boolean breakAtFirstMissing) {
151        try {
152            Set<Column> missingColumns = new HashSet<>();
153
154            // Test whether there are new fields added in the schema that are
155            // not present in the table schema. If so it is advised to
156            // reinitialise the database.
157
158            Set<String> columnNames = getPhysicalColumns();
159
160            // check the field names match the column names (case-insensitive)
161            for (Column column : table.getColumns()) {
162                // TODO: check types as well
163                String fieldName = column.getPhysicalName();
164                if (!columnNames.contains(fieldName)) {
165                    log.debug(String.format("required field: %s is missing", fieldName));
166                    missingColumns.add(column);
167
168                    if (breakAtFirstMissing) {
169                        return null;
170                    }
171                }
172            }
173
174            return missingColumns;
175        } catch (SQLException e) {
176            log.warn("error while introspecting table: " + tableName, e);
177            return null;
178        }
179    }
180
181    private Set<String> getPhysicalColumns() throws SQLException {
182        ResultSet rs = null;
183        Set<String> columnNames = new HashSet<>();
184        try {
185            // fetch the database columns definitions
186            DatabaseMetaData metadata = connection.getMetaData();
187            rs = metadata.getColumns(null, "%", tableName, "%");
188
189            while (rs.next()) {
190                columnNames.add(rs.getString("COLUMN_NAME"));
191            }
192        } finally {
193            if (rs != null) {
194                try {
195                    rs.close();
196                } catch (SQLException e) {
197                    log.warn("Error while trying to close result set", e);
198                }
199            }
200        }
201        return columnNames;
202    }
203
204    private boolean tableExists() throws DirectoryException {
205        try {
206            // Check if table exists using metadata
207            DatabaseMetaData metaData = connection.getMetaData();
208            String schemaName = null;
209            String productName = metaData.getDatabaseProductName();
210            if ("Oracle".equals(productName)) {
211                try (Statement st = connection.createStatement()) {
212                    String sql = "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL";
213                    log.trace("SQL: " + sql);
214                    try (ResultSet rs = st.executeQuery(sql)) {
215                        rs.next();
216                        schemaName = rs.getString(1);
217                        log.trace("checking existing tables for oracle database, schema: " + schemaName);
218                    }
219                }
220            }
221            try (ResultSet rs = metaData.getTables(null, schemaName, table.getPhysicalName(), new String[] { "TABLE" })) {
222                boolean exists = rs.next();
223                log.debug(String.format("checking if table %s exists: %s", table.getPhysicalName(), Boolean.valueOf(exists)));
224                return exists;
225            }
226        } catch (SQLException e) {
227            throw new DirectoryException(e);
228        }
229    }
230
231    public static Table addTable(String name, Dialect dialect, boolean nativeCase) {
232        String physicalName = dialect.getTableName(name);
233        if (!nativeCase && name.length() == physicalName.length()) {
234            // we can keep the name specified in the config
235            physicalName = name;
236        }
237        return new TableImpl(dialect, physicalName, physicalName);
238    }
239
240    public static Column addColumn(Table table, String fieldName, ColumnType type, boolean nativeCase) {
241        String physicalName = table.getDialect().getColumnName(fieldName);
242        if (!nativeCase && fieldName.length() == physicalName.length()) {
243            // we can keep the name specified in the config
244            physicalName = fieldName;
245        }
246        Column column = new Column(table, physicalName, type, fieldName);
247        return ((TableImpl) table).addColumn(fieldName, column);
248    }
249
250}