001/* 002 * (C) Copyright 2006-2011 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 * Florent Guillaume 018 */ 019package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 020 021import java.io.BufferedReader; 022import java.io.IOException; 023import java.io.InputStream; 024import java.io.InputStreamReader; 025import java.io.Serializable; 026import java.sql.Connection; 027import java.sql.ResultSet; 028import java.sql.SQLException; 029import java.sql.Statement; 030import java.util.ArrayList; 031import java.util.Collections; 032import java.util.HashSet; 033import java.util.LinkedList; 034import java.util.List; 035import java.util.Map; 036import java.util.Map.Entry; 037import java.util.Set; 038import java.util.regex.Matcher; 039import java.util.regex.Pattern; 040 041import org.nuxeo.ecm.core.storage.sql.Activator; 042import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 043 044/** 045 * A SQL statement and some optional tags that condition execution. 046 */ 047public class SQLStatement { 048 049 // for derby... 050 public static final String DIALECT_WITH_NO_SEMICOLON = "noSemicolon"; 051 052 /** Category pseudo-tag */ 053 public static final String CATEGORY = "#CATEGORY:"; 054 055 /** 056 * Tags that may condition execution of the statement. 057 */ 058 public static class Tag { 059 060 /** 061 * Tag for a SELECT statement whose number of rows must be counted. Var "emptyResult" is set accordingly. 062 */ 063 public static final String TAG_TEST = "#TEST:"; 064 065 /** 066 * Tag to only execute statement if a var is true. Var may be preceded by ! inverse the test. 067 */ 068 public static final String TAG_IF = "#IF:"; 069 070 /** 071 * Tag to define a stored procedure / function / type / trigger. Followed by its name. Use by 072 * {@link Dialect#checkStoredProcedure}. 073 */ 074 public static final String TAG_PROC = "#PROC:"; 075 076 /** 077 * Tag to set a var to true if the result if the statement is empty. 078 */ 079 public static final String TAG_SET_IF_EMPTY = "#SET_IF_EMPTY:"; 080 081 /** 082 * Tag to set a var to true if the result if the statement is not empty. 083 */ 084 public static final String TAG_SET_IF_NOT_EMPTY = "#SET_IF_NOT_EMPTY:"; 085 086 public static final String VAR_EMPTY_RESULT = "emptyResult"; 087 088 /** The tag key. */ 089 public final String key; 090 091 /** 092 * The value behind a tag, used for {@link #TAG_IF}, {@link #TAG_PROC}, {@link #TAG_SET_IF_EMPTY}, 093 * {@link #TAG_SET_IF_NOT_EMPTY} 094 */ 095 public final String value; 096 097 public Tag(String key, String value) { 098 this.key = key; 099 this.value = value; 100 } 101 } 102 103 /** 104 * Collects a list of strings. 105 * 106 * @since 6.0-HF24, 7.10-HF01, 8.1 107 */ 108 public static class ListCollector { 109 110 private final List<String> list = new ArrayList<>(); 111 112 /** Collects one string. */ 113 public void add(String string) { 114 list.add(string); 115 } 116 117 /** Collects several strings. */ 118 public void addAll(List<String> strings) { 119 list.addAll(strings); 120 } 121 122 /** Gets the collected strings. */ 123 public List<String> getStrings() { 124 return list; 125 } 126 } 127 128 /** SQL statement */ 129 public final String sql; 130 131 /** Tags on the statement */ 132 public final List<Tag> tags; 133 134 public SQLStatement(String sql, List<Tag> tags) { 135 this.sql = sql; 136 this.tags = tags == null ? Collections.<Tag> emptyList() : tags; 137 } 138 139 @Override 140 public String toString() { 141 StringBuilder buf = new StringBuilder(); 142 buf.append("SQLStatement("); 143 for (Tag tag : tags) { 144 buf.append(tag.key); 145 String value = tag.value; 146 if (value != null) { 147 buf.append(' '); 148 buf.append(value); 149 } 150 buf.append(", "); 151 } 152 buf.append(sql); 153 buf.append(')'); 154 return buf.toString(); 155 } 156 157 /** 158 * Reads SQL statements from a text file. 159 * <p> 160 * Statements have a category, and optional tags (that may condition execution). 161 * 162 * <pre> 163 * #CATEGORY: mycat 164 * #TEST: 165 * SELECT foo 166 * from bar; 167 * </pre> 168 * 169 * <pre> 170 * #CATEGORY: mycat 171 * #IF: emptyResult 172 * #IF: somethingEnabled 173 * INSERT INTO ...; 174 * </pre> 175 * 176 * An empty line terminates a statement. 177 */ 178 public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements) 179 throws IOException { 180 return read(filename, statements, false); 181 } 182 183 public static Map<String, List<SQLStatement>> read(String filename, Map<String, List<SQLStatement>> statements, 184 boolean allDDL) throws IOException { 185 InputStream is = Activator.getResourceAsStream(filename); 186 if (is == null) { 187 throw new IOException("Cannot open: " + filename); 188 } 189 BufferedReader reader = new BufferedReader(new InputStreamReader(is, "UTF-8")); 190 String line; 191 String category = null; 192 List<Tag> tags = new LinkedList<>(); 193 try { 194 while ((line = reader.readLine()) != null) { 195 int colonPos = line.indexOf(':'); 196 String key = colonPos < 0 ? "" : line.substring(0, colonPos + 1); 197 String value = colonPos < 0 ? "" : line.substring(colonPos + 1).trim(); 198 switch (key) { 199 case SQLStatement.CATEGORY: 200 category = value; 201 continue; 202 case Tag.TAG_TEST: 203 case Tag.TAG_IF: 204 case Tag.TAG_PROC: 205 case Tag.TAG_SET_IF_EMPTY: 206 case Tag.TAG_SET_IF_NOT_EMPTY: 207 if (value.length() == 0) { 208 value = null; 209 } 210 tags.add(new Tag(key, value)); 211 continue; 212 } 213 if (line.startsWith("#")) { 214 continue; 215 } 216 StringBuilder buf = new StringBuilder(); 217 boolean read = false; 218 while (true) { 219 if (read) { 220 line = reader.readLine(); 221 } else { 222 read = true; 223 } 224 if (line == null || line.trim().equals("")) { 225 if (buf.length() == 0) { 226 break; 227 } 228 String sql = buf.toString().trim(); 229 SQLStatement statement = new SQLStatement(sql, tags); 230 List<SQLStatement> catStatements = statements.get(category); 231 if (catStatements == null) { 232 statements.put(category, catStatements = new LinkedList<SQLStatement>()); 233 } 234 catStatements.add(statement); 235 break; 236 } else if (line.startsWith("#")) { 237 continue; 238 } else { 239 buf.append(line); 240 buf.append('\n'); 241 } 242 } 243 tags = new LinkedList<>(); 244 if (line == null) { 245 break; 246 } 247 } 248 } finally { 249 reader.close(); 250 } 251 return statements; 252 } 253 254 protected static String replaceVars(String sql, Map<String, Serializable> properties) { 255 if (properties != null) { 256 for (Entry<String, Serializable> en : properties.entrySet()) { 257 String key = "${" + en.getKey() + "}"; 258 String value = String.valueOf(en.getValue()); 259 sql = sql.replaceAll(Pattern.quote(key), Matcher.quoteReplacement(value)); 260 } 261 } 262 return sql; 263 } 264 265 /** 266 * Executes a list of SQL statements, following the tags. 267 */ 268 public static void execute(List<SQLStatement> statements, String ddlMode, Map<String, Serializable> properties, 269 Dialect dialect, Connection connection, JDBCLogger logger, ListCollector ddlCollector) throws SQLException { 270 try (Statement st = connection.createStatement()) { 271 STATEMENT: // 272 for (SQLStatement statement : statements) { 273 boolean test = false; 274 String proc = null; 275 Set<String> setIfEmpty = new HashSet<>(); 276 Set<String> setIfNotEmpty = new HashSet<>(); 277 for (Tag tag : statement.tags) { 278 switch (tag.key) { 279 case Tag.TAG_TEST: 280 test = true; 281 break; 282 case Tag.TAG_PROC: 283 proc = tag.value; 284 break; 285 case Tag.TAG_IF: 286 String expr = tag.value; 287 boolean res = false; 288 for (String key : expr.split(" OR: ")) { 289 boolean neg = key.startsWith("!"); 290 if (neg) { 291 key = key.substring(1).trim(); 292 } 293 Serializable value = properties.get(key); 294 if (value == null) { 295 logger.log("Defaulting to false: " + key); 296 value = Boolean.FALSE; 297 } 298 if (!(value instanceof Boolean)) { 299 logger.error("Not a boolean condition: " + key); 300 continue STATEMENT; 301 } 302 if (((Boolean) value).booleanValue() != neg) { 303 res = true; 304 break; 305 } 306 } 307 if (!res) { 308 continue STATEMENT; 309 } 310 break; 311 case Tag.TAG_SET_IF_EMPTY: 312 setIfEmpty.add(tag.value); 313 break; 314 case Tag.TAG_SET_IF_NOT_EMPTY: 315 setIfNotEmpty.add(tag.value); 316 break; 317 } 318 } 319 String sql = statement.sql; 320 sql = replaceVars(sql, properties); 321 if (sql.startsWith("LOG.DEBUG")) { 322 String msg = sql.substring("LOG.DEBUG".length()).trim(); 323 logger.log(msg); 324 continue; 325 } else if (sql.startsWith("LOG.INFO")) { 326 String msg = sql.substring("LOG.INFO".length()).trim(); 327 logger.info(msg); 328 continue; 329 } else if (sql.startsWith("LOG.ERROR")) { 330 String msg = sql.substring("LOG.ERROR".length()).trim(); 331 logger.error(msg); 332 continue; 333 } else if (sql.startsWith("LOG.FATAL")) { 334 String msg = sql.substring("LOG.FATAL".length()).trim(); 335 logger.error(msg); 336 throw new SQLException("Fatal error: " + msg); 337 } 338 339 if (sql.endsWith(";") && properties.containsKey(DIALECT_WITH_NO_SEMICOLON)) { 340 // derby at least doesn't allow a terminating semicolon 341 sql = sql.substring(0, sql.length() - 1); 342 } 343 344 try { 345 if (test) { 346 logger.log(sql.replace("\n", "\n ")); // indented 347 try (ResultSet rs = st.executeQuery(sql)) { 348 boolean empty = !rs.next(); 349 properties.put(Tag.VAR_EMPTY_RESULT, Boolean.valueOf(empty)); 350 logger.log(" -> emptyResult = " + empty); 351 if (empty) { 352 for (String prop : setIfEmpty) { 353 properties.put(prop, Boolean.TRUE); 354 logger.log(" -> " + prop + " = true"); 355 } 356 } else { 357 for (String prop : setIfNotEmpty) { 358 properties.put(prop, Boolean.TRUE); 359 logger.log(" -> " + prop + " = true"); 360 } 361 } 362 } 363 } else if (proc != null) { 364 ddlCollector.addAll( 365 dialect.checkStoredProcedure(proc, sql, ddlMode, connection, logger, properties)); 366 } else if (ddlCollector != null) { 367 ddlCollector.add(sql); 368 } else { 369 // upgrade stuff, execute immediately 370 logger.log(sql.replace("\n", "\n ")); // indented 371 st.execute(sql); 372 } 373 } catch (SQLException e) { 374 throw new SQLException("Error executing: " + sql + " : " + e.getMessage(), e); 375 } 376 } 377 } 378 } 379 380}