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() { 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() { 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) { 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() { 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}