Source code

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