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}