001/* 002 * Copyright 2016 Anyware Services 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 */ 016package org.ametys.core.script; 017 018import java.io.IOException; 019import java.io.InputStream; 020import java.io.LineNumberReader; 021import java.io.StringReader; 022import java.sql.Connection; 023import java.sql.DatabaseMetaData; 024import java.sql.ResultSet; 025import java.sql.SQLException; 026import java.sql.Statement; 027import java.time.Instant; 028import java.util.Map; 029 030import org.apache.commons.io.IOUtils; 031import org.apache.excalibur.source.Source; 032import org.apache.excalibur.source.SourceResolver; 033import org.slf4j.Logger; 034import org.slf4j.LoggerFactory; 035 036import org.ametys.core.datasource.ConnectionHelper; 037import org.ametys.core.migration.MigrationException; 038import org.ametys.core.migration.MigrationExtensionPoint; 039import org.ametys.core.migration.configuration.impl.SqlVersionConfiguration; 040import org.ametys.core.migration.storage.impl.SqlVersionStorage; 041import org.ametys.core.migration.version.Version; 042import org.ametys.core.migration.version.impl.SqlVersion; 043 044/** 045 * Example of simple use: 046 * SQLScriptHelper.createTableIfNotExists(dataSourceId, "QRTZ_JOB_DETAILS", "plugin:core://scripts/%s/quartz.sql", _sourceResolver); 047 * Will test if table QRTZ_JOB_DETAILS exits in database from datasource dataSourceId. If not, the script plugin:core://scripts/%s/quartz.sql will be resolved and executed (where %s is replaced by the database type 'mysql', 'derby'...) 048 * 049 * Tools to run SQL scripts.<p> 050 * Default separator for isolating statements is the semi colon 051 * character: <code>;</code>.<br> 052 * It can be changed by using a comment like the following snippet 053 * for using the string <code>---</code>:<br> 054 * <code>-- _separator_=---<br> 055 * begin<br> 056 * execute immediate 'DROP TABLE MYTABLE';<br> 057 * Exception when others then null;<br> 058 * end;<br> 059 * ---<br> 060 * -- _separator_=;<br> 061 * CREATE TABLE MYTABLE;<br> 062 * ...</code><br> 063 * Note that the command must be placed at the end of the comment.<br><br> 064 * The runner can be configured to ignore SQLExceptions. This can be useful 065 * to execute DROP statements when it's unknown if the tables exist:<br> 066 * <code>--_ignore_exceptions_=on<br> 067 * DROP TABLE MYTABLE;<br> 068 * --_ignore_exceptions_=off</code> 069 */ 070public final class SQLScriptHelper 071{ 072 /** Default separator used for isolating statements. */ 073 public static final String DEFAULT_SEPARATOR = ";"; 074 /** Command to ignore sql exceptions. */ 075 public static final String IGNORE_EXCEPTIONS_COMMAND = "_ignore_exceptions_="; 076 /** Command to change the separator. */ 077 public static final String CHANGE_SEPARATOR_COMMAND = "_separator_="; 078 /** Logger available to subclasses. */ 079 protected static final Logger __LOGGER = LoggerFactory.getLogger(SQLScriptHelper.class); 080 081 private SQLScriptHelper() 082 { 083 // Nothing to do 084 } 085 086 /** 087 * This method will test if a table exists, and if not will execute a script to create it 088 * @param datasourceId The data source id to open a connection to the database 089 * @param tableNameToCheck The name of the table that will be checked 090 * @param location The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument. 091 * @param sourceResolver The source resolver 092 * @return true if the table was created, false otherwise 093 * @throws SQLException If an error occurred while executing SQL script, or while testing table existence 094 * @throws IOException If an error occurred while getting the script file, or if the url is malformed 095 */ 096 public static boolean createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver) throws SQLException, IOException 097 { 098 return createTableIfNotExists(datasourceId, tableNameToCheck, location, sourceResolver, null); 099 } 100 101 /** 102 * This method will test if a table exists, and if not will execute a script to create it 103 * @param datasourceId The data source id to open a connection to the database 104 * @param tableNameToCheck The name of the table that will be checked 105 * @param location The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument. 106 * @param sourceResolver The source resolver 107 * @param replace The map of string to replace. Key is the regexp to seek, value is the replacing string. 108 * @return true if the table was created, false otherwise 109 * @throws SQLException If an error occurred while executing SQL script, or while testing table existence 110 * @throws IOException If an error occurred while getting the script file, or if the url is malformed 111 */ 112 public static boolean createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String, String> replace) throws SQLException, IOException 113 { 114 Connection connection = null; 115 try 116 { 117 connection = ConnectionHelper.getConnection(datasourceId); 118 119 return createTableIfNotExists(connection, tableNameToCheck, location, sourceResolver, replace); 120 } 121 finally 122 { 123 ConnectionHelper.cleanup(connection); 124 } 125 } 126 127 /** 128 * This method will test if a table exists, and if not will execute a script to create it 129 * @param connection The database connection to use 130 * @param tableNameToCheck The name of the table that will be checked 131 * @param location The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument. 132 * @param sourceResolver The source resolver 133 * @return true if the table was created, false otherwise 134 * @throws SQLException If an error occurred while executing SQL script, or while testing table existence 135 * @throws IOException If an error occurred while getting the script file, or if the url is malformed 136 */ 137 public static boolean createTableIfNotExists(Connection connection, String tableNameToCheck, String location, SourceResolver sourceResolver) throws SQLException, IOException 138 { 139 return createTableIfNotExists(connection, tableNameToCheck, location, sourceResolver, null); 140 } 141 142 /** 143 * This method will test if a table exists, and if not will execute a script to create it 144 * @param connection The database connection to use 145 * @param tableNameToCheck The name of the table that will be checked 146 * @param location The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument. 147 * @param sourceResolver The source resolver 148 * @param replace The map of string to replace. Key is the regexp to seek, value is the replacing string. 149 * @return true if the table was created, false otherwise 150 * @throws SQLException If an error occurred while executing SQL script, or while testing table existence 151 * @throws IOException If an error occurred while getting the script file, or if the url is malformed 152 */ 153 public static boolean createTableIfNotExists(Connection connection, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String, String> replace) throws SQLException, IOException 154 { 155 if (tableExists(connection, tableNameToCheck)) 156 { 157 return false; 158 } 159 160 String finalLocation = String.format(location, ConnectionHelper.getDatabaseType(connection)); 161 162 Source source = null; 163 try 164 { 165 source = sourceResolver.resolveURI(finalLocation); 166 167 try (InputStream is = source.getInputStream()) 168 { 169 String script = IOUtils.toString(is, "UTF-8"); 170 171 if (replace != null) 172 { 173 for (String replaceKey : replace.keySet()) 174 { 175 script = script.replaceAll(replaceKey, replace.get(replaceKey)); 176 } 177 } 178 179 SQLScriptHelper.runScript(connection, script); 180 } 181 } 182 finally 183 { 184 sourceResolver.release(source); 185 } 186 187 return true; 188 } 189 190 /** 191 * This method will test if a table exists, and if not will execute a script to create it 192 * @param datasourceId The data source id to open a connection to the database 193 * @param tableNameToCheck The name of the table that will be checked 194 * @param location The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument. 195 * @param sourceResolver The source resolver 196 * @param replace The map of string to replace. Key is the regexp to seek, value is the replacing string. 197 * @param componentId component used to get the most recent version number 198 * @param versionId version id that will be stored in database (can be the same as componentId) 199 * @param migrationEP Migration Extension Point used to read the version 200 * @param sqlVersionStorage SQJ Version Storage used to store the version 201 * @return true if the table was created, false otherwise 202 * @throws SQLException If an error occurred while executing SQL script, or while testing table existence 203 * @throws IOException If an error occurred while getting the script file, or if the url is malformed 204 */ 205 public static boolean createTableIfNotExists(String datasourceId, String tableNameToCheck, String location, SourceResolver sourceResolver, Map<String, String> replace, 206 String componentId, String versionId, MigrationExtensionPoint migrationEP, SqlVersionStorage sqlVersionStorage) throws SQLException, IOException 207 { 208 Connection connection = null; 209 boolean created = false; 210 try 211 { 212 connection = ConnectionHelper.getConnection(datasourceId); 213 214 created = createTableIfNotExists(connection, tableNameToCheck, location, sourceResolver, replace); 215 SqlVersionConfiguration config = new SqlVersionConfiguration(datasourceId); 216 Version currentVersion = sqlVersionStorage.getCurrentVersion(versionId, config, "sql"); 217 String versionNumber = migrationEP.getExpectedVersionForComponent(componentId); 218 if (currentVersion == null || currentVersion.getVersionNumber() == null || !currentVersion.getVersionNumber().equals(versionNumber)) 219 { 220 String databaseType = ConnectionHelper.getDatabaseType(connection); 221 SqlVersion version = new SqlVersion("script", versionId, versionNumber, Instant.now(), "Initialization", datasourceId, databaseType); 222 sqlVersionStorage.addVersion(version); 223 } 224 } 225 catch (MigrationException e) 226 { 227 __LOGGER.error("The tables " + tableNameToCheck + " was created but the version table was not, you might restart the application to solve this.", e); 228 } 229 finally 230 { 231 ConnectionHelper.cleanup(connection); 232 } 233 234 return created; 235 } 236 237 /** 238 * Checks whether the given table exists in the database. 239 * @param connection The database connection 240 * @param tableName the name of the table 241 * @return true is the table exists 242 * @throws SQLException In an SQL exception occurs 243 */ 244 public static boolean tableExists(Connection connection, String tableName) throws SQLException 245 { 246 ResultSet rs = null; 247 248 DatabaseMetaData metaData = connection.getMetaData(); 249 250 // Test for non escaped table names 251 String name = tableName; 252 if (metaData.storesLowerCaseIdentifiers()) 253 { 254 name = tableName.toLowerCase(); 255 } 256 else if (metaData.storesUpperCaseIdentifiers()) 257 { 258 name = tableName.toUpperCase(); 259 } 260 261 try 262 { 263 rs = metaData.getTables(connection.getCatalog(), connection.getSchema(), name, null); 264 if (rs.next()) 265 { 266 return true; 267 } 268 } 269 finally 270 { 271 ConnectionHelper.cleanup(rs); 272 } 273 274 // Test for escaped table names 275 String quotedName = tableName; 276 if (metaData.storesLowerCaseQuotedIdentifiers()) 277 { 278 quotedName = tableName.toLowerCase(); 279 } 280 else if (metaData.storesUpperCaseQuotedIdentifiers()) 281 { 282 quotedName = tableName.toUpperCase(); 283 } 284 285 if (!quotedName.equals(name)) 286 { 287 try 288 { 289 rs = metaData.getTables(connection.getCatalog(), connection.getSchema(), quotedName, null); 290 if (rs.next()) 291 { 292 return true; 293 } 294 } 295 finally 296 { 297 ConnectionHelper.cleanup(rs); 298 } 299 } 300 301 return false; 302 } 303 304 /** 305 * Run a SQL script using the connection passed in. 306 * @param connection the connection to use for the script 307 * @param script the script data. 308 * @throws IOException if an error occurs while reading the script. 309 * @throws SQLException if an error occurs while executing the script. 310 */ 311 public static void runScript(Connection connection, String script) throws IOException, SQLException 312 { 313 ScriptContext scriptContext = new ScriptContext(); 314 StringBuilder command = new StringBuilder(); 315 316 try 317 { 318 LineNumberReader lineReader = new LineNumberReader(new StringReader(script)); 319 String line = null; 320 while ((line = lineReader.readLine()) != null) 321 { 322 if (__LOGGER.isDebugEnabled()) 323 { 324 __LOGGER.debug(String.format("Reading line: '%s'", line)); 325 } 326 327 boolean processCommand = false; 328 String trimmedLine = line.trim(); 329 330 if (trimmedLine.length() > 0) 331 { 332 processCommand = processScriptLine(trimmedLine, command, scriptContext); 333 334 if (processCommand) 335 { 336 _processCommand(connection, command, lineReader.getLineNumber(), scriptContext); 337 } 338 } 339 } 340 341 // If the entire file was processed and the command buffer is not empty, execute the current buffer. 342 if (command.length() > 0) 343 { 344 _processCommand(connection, command, lineReader.getLineNumber(), scriptContext); 345 } 346 347 if (!connection.getAutoCommit()) 348 { 349 connection.commit(); 350 } 351 } 352 finally 353 { 354 if (!connection.getAutoCommit()) 355 { 356 try 357 { 358 // Fermer la connexion à la base 359 connection.rollback(); 360 } 361 catch (SQLException s) 362 { 363 __LOGGER.error("Error while rollbacking connection", s); 364 } 365 } 366 } 367 } 368 369 /** 370 * Run a SQL script using the connection passed in. 371 * @param connection the connection to use for the script 372 * @param is the input stream containing the script data. 373 * @throws IOException if an error occurs while reading the script. 374 * @throws SQLException if an error occurs while executing the script. 375 */ 376 @SuppressWarnings("deprecation") 377 public static void runScript(Connection connection, InputStream is) throws IOException, SQLException 378 { 379 try 380 { 381 String script = IOUtils.toString(is, "UTF-8"); 382 runScript(connection, script); 383 } 384 finally 385 { 386 IOUtils.closeQuietly(is); 387 } 388 } 389 390 /** 391 * Process a script line. 392 * @param line the line to process. 393 * @param commandBuffer the command buffer. 394 * @param scriptContext the script execution context. 395 * @return true to immediately process the command (a separator was found), false to process it later. 396 */ 397 protected static boolean processScriptLine(String line, StringBuilder commandBuffer, ScriptContext scriptContext) 398 { 399 boolean processCommand = false; 400 401 if (line.startsWith("//") || line.startsWith("--")) 402 { 403 String currentSeparator = scriptContext.getSeparator(); 404 405 // Search if the separator needs to be changed 406 if (line.contains(CHANGE_SEPARATOR_COMMAND)) 407 { 408 // New separator 409 String newSeparator = line.substring(line.indexOf(CHANGE_SEPARATOR_COMMAND) 410 + CHANGE_SEPARATOR_COMMAND.length()).trim(); 411 412 scriptContext.setSeparator(newSeparator); 413 414 if (__LOGGER.isDebugEnabled()) 415 { 416 __LOGGER.debug(String.format("Changing separator to: '%s'", newSeparator)); 417 } 418 } 419 else if (line.contains(IGNORE_EXCEPTIONS_COMMAND)) 420 { 421 String ignoreStr = line.substring(line.indexOf(IGNORE_EXCEPTIONS_COMMAND) 422 + IGNORE_EXCEPTIONS_COMMAND.length()).trim(); 423 424 boolean ignoreExceptions = "on".equals(ignoreStr); 425 426 scriptContext.setIgnoreExceptions(ignoreExceptions); 427 428 if (__LOGGER.isDebugEnabled()) 429 { 430 __LOGGER.debug(String.format("Ignore exceptions: '%s'", ignoreExceptions ? "on" : "off")); 431 } 432 } 433 434 if (line.contains(currentSeparator)) 435 { 436 if (commandBuffer.length() > 0) 437 { 438 // End of command but do not use current line 439 processCommand = true; 440 } 441 } 442 } 443 else if (line.endsWith(scriptContext.getSeparator())) 444 { 445 // End of command and use current line 446 processCommand = true; 447 commandBuffer.append(line.substring(0, line.lastIndexOf(scriptContext.getSeparator()))); 448 } 449 else 450 { 451 // Append current command to the buffer 452 commandBuffer.append(line); 453 commandBuffer.append(" "); 454 } 455 456 return processCommand; 457 } 458 459 private static void _processCommand(Connection connection, StringBuilder command, int lineNumber, ScriptContext scriptContext) throws SQLException 460 { 461 if (__LOGGER.isInfoEnabled()) 462 { 463 __LOGGER.info(String.format("Executing SQL command: '%s'", command)); 464 } 465 466 _execute(connection, command.toString(), lineNumber, scriptContext); 467 468 // Clear command 469 command.setLength(0); 470 } 471 472 private static void _execute(Connection connection, String command, int lineNumber, ScriptContext scriptContext) throws SQLException 473 { 474 Statement statement = null; 475 try 476 { 477 statement = connection.createStatement(); 478 statement.execute(command); 479 } 480 catch (SQLException e) 481 { 482 if (!scriptContext.ignoreExceptions()) 483 { 484 String message = String.format("Unable to execute SQL: '%s' at line %d", command, lineNumber); 485 __LOGGER.error(message, e); 486 487 throw new SQLException(message, e); 488 } 489 } 490 finally 491 { 492 ConnectionHelper.cleanup(statement); 493 } 494 } 495 496 /** 497 * Script execution context. 498 */ 499 protected static class ScriptContext 500 { 501 502 /** The current script execution block separator. */ 503 protected String _separator; 504 505 /** True to ignore sql exceptions. */ 506 protected boolean _ignoreExceptions; 507 508 /** 509 * Default ScriptContext object. 510 */ 511 public ScriptContext() 512 { 513 this(DEFAULT_SEPARATOR, false); 514 } 515 516 /** 517 * Build a ScriptContext object. 518 * @param separator the separator 519 * @param ignoreExceptions true to ignore exceptions. 520 */ 521 public ScriptContext(String separator, boolean ignoreExceptions) 522 { 523 this._separator = separator; 524 this._ignoreExceptions = ignoreExceptions; 525 } 526 527 /** 528 * Get the separator. 529 * @return the separator 530 */ 531 public String getSeparator() 532 { 533 return _separator; 534 } 535 536 /** 537 * Set the separator. 538 * @param separator the separator to set 539 */ 540 public void setSeparator(String separator) 541 { 542 this._separator = separator; 543 } 544 545 /** 546 * Get the ignoreExceptions. 547 * @return the ignoreExceptions 548 */ 549 public boolean ignoreExceptions() 550 { 551 return _ignoreExceptions; 552 } 553 554 /** 555 * Set the ignoreExceptions. 556 * @param ignoreExceptions the ignoreExceptions to set 557 */ 558 public void setIgnoreExceptions(boolean ignoreExceptions) 559 { 560 this._ignoreExceptions = ignoreExceptions; 561 } 562 563 } 564 565}