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