001/* 002MIT License 003 004Copyright (c) 2020 FBSQL Team 005 006Permission is hereby granted, free of charge, to any person obtaining a copy 007of this software and associated documentation files (the "Software"), to deal 008in the Software without restriction, including without limitation the rights 009to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 010copies of the Software, and to permit persons to whom the Software is 011furnished to do so, subject to the following conditions: 012 013The above copyright notice and this permission notice shall be included in all 014copies or substantial portions of the Software. 015 016THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 017IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 018FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 019AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 020LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 021OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE 022SOFTWARE. 023 024Home: https://fbsql.github.io 025E-Mail: fbsql.team@gmail.com 026*/ 027 028package org.fbsql.servlet; 029 030import static org.fbsql.servlet.SqlParseUtils.parseConnectStatement; 031import static org.fbsql.servlet.SqlParseUtils.parseExposeStatement; 032import static org.fbsql.servlet.SqlParseUtils.parseNamedPreparedStatement; 033import static org.fbsql.servlet.StringUtils.q; 034 035import java.io.File; 036import java.io.FileInputStream; 037import java.io.IOException; 038import java.io.InputStream; 039import java.io.OutputStream; 040import java.io.Writer; 041import java.lang.reflect.Method; 042import java.nio.charset.StandardCharsets; 043import java.nio.file.Files; 044import java.nio.file.Path; 045import java.nio.file.Paths; 046import java.sql.CallableStatement; 047import java.sql.Connection; 048import java.sql.Driver; 049import java.sql.DriverManager; 050import java.sql.ParameterMetaData; 051import java.sql.PreparedStatement; 052import java.sql.ResultSet; 053import java.sql.SQLException; 054import java.sql.Statement; 055import java.text.MessageFormat; 056import java.util.ArrayList; 057import java.util.Arrays; 058import java.util.Base64; 059import java.util.Base64.Decoder; 060import java.util.Base64.Encoder; 061import java.util.Collection; 062import java.util.HashMap; 063import java.util.Iterator; 064import java.util.LinkedHashMap; 065import java.util.List; 066import java.util.Locale; 067import java.util.Map; 068import java.util.Properties; 069import java.util.Queue; 070import java.util.ServiceLoader; 071import java.util.concurrent.ConcurrentLinkedQueue; 072 073import javax.servlet.AsyncContext; 074import javax.servlet.AsyncEvent; 075import javax.servlet.AsyncListener; 076import javax.servlet.ServletConfig; 077import javax.servlet.ServletException; 078import javax.servlet.annotation.WebServlet; 079import javax.servlet.http.HttpServlet; 080import javax.servlet.http.HttpServletRequest; 081import javax.servlet.http.HttpServletResponse; 082 083import org.fbsql.antlr4.parser.ParseNativeStmt; 084import org.fbsql.antlr4.parser.ParseNativeStmt.Procedure; 085import org.fbsql.antlr4.parser.ParseStmtConnectTo; 086import org.fbsql.antlr4.parser.ParseStmtConnectTo.StmtConnectTo; 087import org.fbsql.antlr4.parser.ParseStmtDeclareStatement.StmtDeclareStatement; 088import org.fbsql.connection_pool.ConnectionPoolManager; 089import org.fbsql.connection_pool.DbConnection; 090import org.fbsql.servlet.Logger.Severity; 091import org.mozilla.javascript.Context; 092import org.mozilla.javascript.Function; 093import org.mozilla.javascript.Scriptable; 094import org.quartz.CronScheduleBuilder; 095import org.quartz.JobBuilder; 096import org.quartz.JobDataMap; 097import org.quartz.JobDetail; 098import org.quartz.Scheduler; 099import org.quartz.Trigger; 100import org.quartz.TriggerBuilder; 101import org.quartz.impl.StdSchedulerFactory; 102 103@WebServlet(asyncSupported = true) 104public class DbServlet extends HttpServlet { 105 106 /** 107 * serialVersionUID 108 */ 109 private static final long serialVersionUID = 1L; 110 111 /** 112 * Debug mode flag 113 * 114 * true in debug mode 115 * false in production mode 116 */ 117 //public static final boolean DEBUG = "true".equals(System.getenv("FBSQL_DEBUG")); 118 public static final boolean DEBUG = true; //!!!!!!!!!!! 119 120 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Cache-Control */ 121 private static final String HTTP_HEADER_CACHE_CONTROL = "Cache-Control"; 122 123 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Authorization */ 124 private static final String HTTP_HEADER_AUTHORIZATION = "Authorization"; 125 126 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Origin */ 127 private static final String HTTP_HEADER_ORIGIN = "Origin"; 128 129 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Access-Control-Allow-Headers */ 130 private static final String HTTP_HEADER_ACCESS_CONTROL_ALLOW_HEADERS = "Access-Control-Allow-Headers"; 131 132 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Access-Control-Allow-Origin */ 133 private static final String HTTP_HEADER_ACCESS_CONTROL_ALLOW_ORIGIN = "Access-Control-Allow-Origin"; 134 135 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Access-Control-Allow-Credentials */ 136 private static final String HTTP_HEADER_ACCESS_CONTROL_ALLOW_CREDENTIALS = "Access-Control-Allow-Credentials"; 137 138 /* https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Access-Control-Allow-Methods */ 139 private static final String HTTP_HEADER_ACCESS_CONTROL_ALLOW_METHODS = "Access-Control-Allow-Methods"; 140 141 /** 142 * Custom HTTP header to send SQL statement with GET method 143 * WARNING! This constant used also in JavaScript part 144 */ 145 public static final String CUSTOM_HTTP_HEADER_CLIENT_INFO = "X-FB-SQL-Client-Info"; 146 147 /** 148 * Custom HTTP header to send SQL statement with GET method 149 * WARNING! This constant used also in JavaScript part 150 */ 151 public static final String CUSTOM_HTTP_HEADER_STATEMENT = "X-FB-SQL-Statement"; 152 153 /** 154 * Custom HTTP header to send user role 155 * WARNING! This constant used also in JavaScript part 156 */ 157 public static final String CUSTOM_HTTP_HEADER_ROLE = "X-FB-SQL-Role"; 158 159 /** 160 * Custom HttpServletRequest attribute name to store user name 161 */ 162 public static final String REQUEST_ATTRIBUTE_USER = "REQUEST_ATTRIBUTE_USER"; 163 164 /** 165 * Custom HttpServletRequest attribute name to store session ID 166 */ 167 public static final String REQUEST_ATTRIBUTE_SESSION_ID = "REQUEST_ATTRIBUTE_SESSION_ID"; 168 169 public static final String CORS_ALLOW_ORIGIN = "CORS_ALLOW_ORIGIN"; 170 171 public static final String FBSQL_HOME_DIR_ENV_NAME = "FBSQL_HOME_DIR"; 172 private String fbsql_home_dir; 173 174 private Map<String /* instance name */, StmtConnectTo> connectionInfoMap; 175 private Map<String /* instance name */, ConnectionPoolManager> connectionPoolManagerMap; 176 private Map<String /* instance name */, Map<String /* SQL statement name */, StmtDeclareStatement>> instancesDeclaredStatementsMap; 177 private Map<String /* instance name */, Map<StaticStatement, ReadyResult>> staticJsonsMap; 178 private Map<String /* instance name */, Queue<AsyncContext>> ongoingRequestsMap; 179 private Map<String /* instance name */, Connection> connectionMap; 180 private Map<String /* instance name */, Map<String /* stored procedure name */, NonNativeProcedure>> instancesProceduresMap; 181 private Map<String /* instance name */, Map<String /* js file name */, Scriptable>> instancesScopesMap; 182 private Map<String /* instance name */, Map<String /* js file name */, Map<String /* function name */, Function>>> instancesFunctionsMap; 183 private Map<String /* instance name */, ParseNativeStmt> instancesParseNativeStmtMap; 184 private Map<String /* connection name */, String /* parent directory */> instancesDirectoryMap; 185 186 private Collection<String> instances; 187 188 /** 189 * Transfer encoding/decoding functionality to request processor 190 * Instances of Base64.Encoder/Base64.Decoder class are safe for use by multiple concurrent threads. 191 */ 192 class SharedCoder { 193 /** 194 * Base64.Encoder instance 195 */ 196 Encoder encoder; 197 198 /** 199 * Base64.Decoder instance 200 */ 201 Decoder decoder; 202 } 203 204 private ServletConfig servletConfig; 205 private SharedCoder sharedCoder; 206 207 private ServiceLoader<Driver> loadedDrivers; 208 209 /** 210 * Called by the servlet container to indicate to a servlet that the 211 * servlet is being placed into service. 212 * 213 * The servlet container calls the init 214 * method exactly once after instantiating the servlet. 215 * The init method must complete successfully 216 * before the servlet can receive any requests. 217 * 218 * 219 * The servlet container cannot place the servlet into service 220 * if the init method 221 * 222 * @param servletConfig a servlet configuration object used by a servlet container to pass information to a servlet during initialization. 223 * 224 * Throws a ServletException 225 * Does not return within a time period defined by the Web server 226 */ 227 @Override 228 public void init(ServletConfig servletConfig) throws ServletException { 229 this.servletConfig = servletConfig; 230 231 fbsql_home_dir = System.getenv(FBSQL_HOME_DIR_ENV_NAME); 232 if (fbsql_home_dir == null) 233 fbsql_home_dir = System.getProperty("user.home"); 234 new File(fbsql_home_dir).mkdirs(); 235 236 loadedDrivers = ServiceLoader.load(Driver.class); 237 238 try { 239 for (Driver driver : loadedDrivers) { 240 DriverManager.registerDriver(driver); 241 Logger.out(Severity.INFO, MessageFormat.format("JDBC driver registered: \"{0}\"", driver.getClass().getName())); 242 } 243 244 String dbConnectorsDir = fbsql_home_dir + "/fbsql/config/init"; 245 Path dbConnectorsDirPath = Paths.get(dbConnectorsDir); 246 Files.createDirectories(dbConnectorsDirPath); 247 Logger.out(Severity.INFO, MessageFormat.format("Database connectors directory is: \"{0}\"", dbConnectorsDir)); 248 249 /* 250 * Initialize SharedCoder 251 */ 252 sharedCoder = new SharedCoder(); 253 sharedCoder.encoder = Base64.getEncoder().withoutPadding(); 254 sharedCoder.decoder = Base64.getDecoder(); 255 256 Map<String /* connection name */, List<String /* SQL statements */>> initSqlMap = new HashMap<>(); 257 instancesDirectoryMap = new HashMap<>(); 258 SqlParseUtils.processInitSqlFiles(new File(dbConnectorsDir), initSqlMap, instancesDirectoryMap); 259 int instancesCount = initSqlMap.size(); 260 261 if (instancesCount == 0) 262 Logger.out(Severity.WARN, "No connection instances found"); 263 else 264 Logger.out(Severity.INFO, MessageFormat.format("{0} connection instance(s) found", instancesCount)); 265 266 connectionPoolManagerMap = new HashMap<>(instancesCount); 267 instancesDeclaredStatementsMap = new HashMap<>(instancesCount); 268 staticJsonsMap = new HashMap<>(instancesCount); 269 connectionInfoMap = new HashMap<>(instancesCount); 270 ongoingRequestsMap = new HashMap<>(instancesCount); 271 connectionMap = new HashMap<>(instancesCount); 272 instancesProceduresMap = new HashMap<>(instancesCount); 273 instancesScopesMap = new HashMap<>(instancesCount); 274 instancesFunctionsMap = new HashMap<>(instancesCount); 275 instancesParseNativeStmtMap = new HashMap<>(instancesCount); 276 277 instances = new ArrayList<>(instancesCount); 278 279 for (Map.Entry<String /* connection name */, List<String /* SQL statements */>> entry : initSqlMap.entrySet()) { 280 String instanceName = entry.getKey(); 281 List<String /* SQL statements */> initList = entry.getValue(); 282 try { 283 openInstance(instanceName, initList); 284 instances.add(instanceName); 285 } catch (Exception e) { 286 e.printStackTrace(); 287 } 288 } 289 System.out.println("*** FBSQL started ***"); 290 } catch (Throwable e) { 291 throw new ServletException(e); 292 } 293 } 294 295 /** 296 * Open database connection instance 297 * 298 * @param instanceDir 299 * @return 300 * @throws Exception 301 */ 302 private void openInstance(String instanceName, List<String /* SQL statements */> initList) throws Exception { 303 if (initList.isEmpty()) 304 return; 305 String instanceDirectory = instancesDirectoryMap.get(instanceName); 306 307 Logger.out(Severity.INFO, MessageFormat.format("Instance found: ''{0}''", instanceName)); 308 StmtConnectTo info = null; 309 for (String statement : initList) { 310 String text = SqlParseUtils.canonizeSql(statement); 311 if (text.startsWith(SqlParseUtils.SPECIAL_STATEMENT_CONNECT_TO)) { 312 info = parseConnectStatement(servletConfig, statement); 313 if (info.jdbcUrl == null) { 314 Logger.out(Severity.INFO, MessageFormat.format("Can't connect to ''{0}''. Bad 'CONNECT TO' statement: ''{1}''. No JDBC URL provided.", instanceName, statement)); 315 return; 316 } 317 } 318 } 319 320 if (DEBUG) 321 System.out.println(info); 322 323 connectionInfoMap.put(instanceName, info); 324 Queue<AsyncContext> ongoingRequests = new ConcurrentLinkedQueue<>(); 325 326 ongoingRequestsMap.put(instanceName, ongoingRequests); 327 328 // Try to register JDBC driver declared into CONNECT TO statement 329 ClassLoaderUtils.registerJdbcDriver(info.driverClassName, info.driverJars); 330 331 String jdbcUrl = StringUtils.putVars(info.jdbcUrl); 332 Properties jdbcProperties = new Properties(); 333 if (info.jdbcPropertiesFile != null) { 334 Path propertiesFilePath = Paths.get(info.jdbcPropertiesFile); 335 if (Files.exists(propertiesFilePath) && !Files.isDirectory(propertiesFilePath) && Files.isReadable(propertiesFilePath)) 336 try (InputStream is = new FileInputStream(info.jdbcPropertiesFile)) { 337 jdbcProperties.load(is); 338 } 339 } 340 341 ConnectionPoolManager connectionPoolManager = new ConnectionPoolManager(jdbcUrl, info.user, info.password, jdbcProperties, info.connectionPoolSizeMin, info.connectionPoolSizeMax); 342 try { 343 connectionPoolManager.init(); 344 connectionPoolManagerMap.put(instanceName, connectionPoolManager); 345 } catch (SQLException e) { 346 Logger.out(Severity.FATAL, MessageFormat.format("WARNING Connection filed: ''{0}''", jdbcUrl)); 347 } 348 // 349 Map<StaticStatement, ReadyResult> mapReadyResults = new HashMap<>(); 350 staticJsonsMap.put(instanceName, mapReadyResults); 351 // 352 Connection connection; 353 if (!jdbcProperties.isEmpty()) 354 connection = DriverManager.getConnection(jdbcUrl, jdbcProperties); 355 else if (info.user != null) 356 connection = DriverManager.getConnection(jdbcUrl, info.user, info.password); 357 else 358 connection = DriverManager.getConnection(jdbcUrl); 359 360 connectionMap.put(instanceName, connection); 361 362 Map<String /* js file name */, Scriptable> mapScopes = new HashMap<>(); 363 Map<String /* js file name */, Map<String /* function name */, Function>> mapFunctions = new HashMap<>(); 364 Map<String /* stored procedure name */, NonNativeProcedure> proceduresMap = new HashMap<>(); 365 Map<String /* Cron expression */, List<String /* SQL statement name */>> schedulersMap = new HashMap<>(); 366 Map<String /* SQL statement name */, StmtDeclareStatement> declareStatementStatementsMap = new HashMap<>(); 367 368 instancesDeclaredStatementsMap.put(instanceName, declareStatementStatementsMap); 369 try (Statement st = connection.createStatement()) { 370 // 371 // Execute all statements from 'init.sql' script 372 // "CONNECT TO" statement is ignored 373 // 374 375 instancesProceduresMap.put(instanceName, proceduresMap); 376 instancesScopesMap.put(instanceName, mapScopes); 377 instancesFunctionsMap.put(instanceName, mapFunctions); 378 379 for (String statement : initList) { 380 String text = SqlParseUtils.canonizeSql(statement); 381 if (text.startsWith(SqlParseUtils.SPECIAL_STATEMENT_DECLARE_PROCEDURE)) // Process DECLARE PROCEDURE statement 382 SqlParseUtils.parseDeclareProcedureStatement(servletConfig, statement, proceduresMap); 383 } 384 385 ParseNativeStmt parseNativeStmt = new ParseNativeStmt(proceduresMap.keySet()); 386 instancesParseNativeStmtMap.put(instanceName, parseNativeStmt); 387 388 for (String statement : initList) { 389 if (DEBUG) 390 System.out.println("init.sql: -->" + statement + "<--"); 391 String text = SqlParseUtils.canonizeSql(statement); 392 NonNativeProcedure nonNativeProcedure = CallUtils.getCallStatementNonNativeProcedure(statement, proceduresMap); 393 if (nonNativeProcedure != null) { // Java or JavaScript 394 if (nonNativeProcedure.procedureType == ProcedureType.JAVA || nonNativeProcedure.procedureType == ProcedureType.JS) { // Java or JavaScript 395 List<Object> parameterValues = new ArrayList<>(); 396 parameterValues.add(connection); 397 parameterValues.add(instanceName); 398 399 CallUtils.parseSqlParameters(parseNativeStmt, statement, parameterValues); 400 Object[] parametersArray = parameterValues.toArray(new Object[parameterValues.size()]); 401 402 if (nonNativeProcedure.procedureType == ProcedureType.JAVA) { // Java 403 Method method = CallUtils.getMethod(nonNativeProcedure.optionsJson); 404 method.invoke(null, parametersArray); 405 } else if (nonNativeProcedure.procedureType == ProcedureType.JS) { // JavaScript 406 // 407 // initize Rhino 408 // https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino 409 // 410 Context ctx = Context.enter(); 411 try { 412 ctx.setLanguageVersion(Context.VERSION_1_7); 413 ctx.setOptimizationLevel(9); // Rhino optimization: https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino/Optimization 414 JsFunction jsFunction = CallUtils.getFunction(instanceDirectory, nonNativeProcedure.optionsJson, mapScopes, mapFunctions); 415 jsFunction.function.call(ctx, jsFunction.scope, null, parametersArray); 416 } catch (Exception e) { 417 e.printStackTrace(); 418 } finally { 419 ctx.exit(); 420 } 421 } 422 } else if (nonNativeProcedure.procedureType == ProcedureType.EXEC) { // OS 423 List<Object> parameterValues = new ArrayList<>(); 424 parameterValues.add(instanceName); 425 CallUtils.parseSqlParameters(parseNativeStmt, statement, parameterValues); 426 String[] parametersArray = parameterValues.toArray(new String[parameterValues.size()]); 427 CallUtils.executeOsProgramm(instanceDirectory, nonNativeProcedure.optionsJson, parametersArray); 428 } else if (nonNativeProcedure.procedureType == ProcedureType.URL) { // URL 429 List<Object> parameterValues = new ArrayList<>(); 430 parameterValues.add(instanceName); 431 CallUtils.parseSqlParameters(parseNativeStmt, statement, parameterValues); 432 String[] parametersArray = parameterValues.toArray(new String[parameterValues.size()]); 433 434 Map<String, Object> parametersMap = new LinkedHashMap<>(); 435 parametersMap.put("parameters", Arrays.toString(parametersArray)); 436 437 CallUtils.executeUrl(instanceDirectory, nonNativeProcedure.optionsJson, parametersMap); 438 } 439 } // 440 else if (text.startsWith(SqlParseUtils.SPECIAL_STATEMENT_SCHEDULE)) 441 SqlParseUtils.parseScheduleStatement(servletConfig, statement, schedulersMap); 442 else if (text.startsWith(SqlParseUtils.SPECIAL_STATEMENT_DECLARE_STATEMENT)) { 443 StmtDeclareStatement stmtDeclareStatement = parseExposeStatement(servletConfig, statement); 444 declareStatementStatementsMap.put(stmtDeclareStatement.alias, stmtDeclareStatement); 445 } // 446 else if (text.startsWith(SqlParseUtils.SPECIAL_STATEMENT_DECLARE_PROCEDURE)) 447 ; // ignore 448 else if (text.startsWith(SqlParseUtils.SPECIAL_STATEMENT_CONNECT_TO)) 449 ; // ignore 450 else // Not a special statements => native SQL 451 try { 452 st.execute(statement); 453 } catch (Exception e) { 454 e.printStackTrace(); 455 } 456 457 } 458 459 for (StmtDeclareStatement stmtExpose : declareStatementStatementsMap.values()) { 460 if (!stmtExpose.prefetch) 461 continue; 462 463 String sql = stmtExpose.statement; 464 // 465 // prefetch: 466 // «warmed up» static queries with no interaction with underlying database 467 // 468 ResultSet rs = st.executeQuery(sql); 469 List<Map<String /* column name */, String /* JSON value */>> list = QueryUtils.resutlSetToListOfMapsJsonValues(rs, sharedCoder.encoder); 470 471 // 472 // cover all possible result set output formats 473 // 474 475 // In this case we ignore user defined compression level and use maximal compression level 476 // because we prepare result offline. 477 478 // array of objects 479 StaticStatement staticStatement = new StaticStatement(sql, QueryUtils.RESULT_FORMAT_ARRAY_OF_OBJECTS); 480 ReadyResult readyResult = QueryUtils.createReadyResult(list, staticStatement.resultSetFormat, CompressionLevel.BEST_COMPRESSION, sharedCoder.encoder); 481 mapReadyResults.put(staticStatement, readyResult); 482 483 // array of arrays 484 staticStatement = new StaticStatement(sql, QueryUtils.RESULT_FORMAT_ARRAY_OF_ARRAYS); 485 readyResult = QueryUtils.createReadyResult(list, staticStatement.resultSetFormat, CompressionLevel.BEST_COMPRESSION, sharedCoder.encoder); 486 mapReadyResults.put(staticStatement, readyResult); 487 } 488 } 489 490 // 491 // Schedule jobs 492 // 493 for (Map.Entry<String /* Cron expression */, List<String /* Scheduled stored procedure name */>> entry : schedulersMap.entrySet()) { 494 String cronExpression = entry.getKey(); 495 List<String> storedProcedureNames = entry.getValue(); 496 497 Runnable runnable = new Runnable() { 498 499 @Override 500 public void run() { 501 try { 502 for (String storedProcedureName : storedProcedureNames) { 503 DbConnection dbConnection0 = null; 504 try { 505 dbConnection0 = connectionPoolManager.getConnection(); 506 507 String outEvent = null; 508 509 NonNativeProcedure nonNativeProcedure = proceduresMap.get(storedProcedureName); 510 if (nonNativeProcedure != null) { 511 if (nonNativeProcedure.procedureType == ProcedureType.JAVA || nonNativeProcedure.procedureType == ProcedureType.JS) { // Java or JavaScript 512 List<Object> parameterValues = new ArrayList<>(); 513 parameterValues.add(dbConnection0.getConnection()); 514 parameterValues.add(instanceName); 515 parameterValues.add(cronExpression); 516 Object[] parametersArray = parameterValues.toArray(new Object[parameterValues.size()]); 517 518 Object obj = null; 519 if (nonNativeProcedure.procedureType == ProcedureType.JAVA) {// Java 520 Method method = CallUtils.getMethod(nonNativeProcedure.optionsJson); 521 obj = method.invoke(null, parametersArray); 522 } else if (nonNativeProcedure.procedureType == ProcedureType.JS) { // JavaScript 523 // 524 // initize Rhino 525 // https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino 526 // 527 Context ctx = Context.enter(); 528 try { 529 ctx.setLanguageVersion(Context.VERSION_1_7); 530 ctx.setOptimizationLevel(9); // Rhino optimization: https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino/Optimization 531 JsFunction jsFunction = CallUtils.getFunction(instanceDirectory, nonNativeProcedure.optionsJson, mapScopes, mapFunctions); 532 obj = jsFunction.function.call(ctx, jsFunction.scope, null, parametersArray); 533 } catch (Exception e) { 534 e.printStackTrace(); 535 } finally { 536 ctx.exit(); 537 } 538 } 539 if (obj instanceof String) 540 outEvent = (String) obj; 541 else if (obj instanceof ResultSet) 542 try (ResultSet rs = (ResultSet) obj) { 543 if (rs.next()) 544 outEvent = rs.getString(1); 545 } 546 } else if (nonNativeProcedure.procedureType == ProcedureType.EXEC) { // OS 547 List<Object> parameterValues = new ArrayList<>(); 548 parameterValues.add(instanceName); 549 parameterValues.add(cronExpression); 550 Object[] parametersArray = parameterValues.toArray(new Object[parameterValues.size()]); 551 552 outEvent = CallUtils.executeOsProgramm(instanceDirectory, nonNativeProcedure.optionsJson, parametersArray); 553 } else if (nonNativeProcedure.procedureType == ProcedureType.URL) { // URL 554 Map<String, Object> parametersMap = new LinkedHashMap<>(); 555 parametersMap.put("instanceName", instanceName); 556 parametersMap.put("cronExpression", cronExpression); 557 558 outEvent = CallUtils.executeUrl(instanceDirectory, nonNativeProcedure.optionsJson, parametersMap); 559 } 560 } else { // Native 561 CallableStatement cs = dbConnection0.getCallableStatement("{call " + storedProcedureName + "(?,?)}"); 562 cs.setString(1, instanceName); 563 cs.setString(2, cronExpression); 564 boolean b = cs.execute(); 565 if (b) { // first result is a ResultSet object 566 try (ResultSet rs = cs.getResultSet()) { 567 if (rs.next()) 568 outEvent = rs.getString(1); 569 } 570 } 571 } 572 for (AsyncContext ac : ongoingRequests) { 573 if (outEvent != null) { // send if outEvent is not null 574 Writer writer = ac.getResponse().getWriter(); 575 writer.append(outEvent + '\n'); 576 writer.flush(); 577 if (DEBUG) { 578 System.out.println("Background job event was delivered to client:"); 579 System.out.println(outEvent); 580 } 581 } 582 } 583 } finally { 584 if (dbConnection0 != null) 585 connectionPoolManager.releaseConnection(dbConnection0); 586 } 587 } 588 } catch (Exception e) { 589 e.printStackTrace(); 590 } 591 } 592 }; 593 594 JobDataMap jobDataMap = new JobDataMap(); 595 jobDataMap.put(RunnableJob.KEY_RUNNABLE, runnable); 596 597 JobDetail jobDetail = JobBuilder.newJob(RunnableJob.class) // 598 .usingJobData(jobDataMap) // 599 .build(); 600 601 Trigger trigger = TriggerBuilder.newTrigger() // 602 .startNow() // 603 .withSchedule(CronScheduleBuilder.cronSchedule(cronExpression)) // 604 .build(); // 605 606 Scheduler scheduler = new StdSchedulerFactory().getScheduler(); 607 scheduler.scheduleJob(jobDetail, trigger); 608 scheduler.start(); 609 } 610 } 611 612 /** 613 * Called by the servlet container to indicate to a servlet that the 614 * servlet is being taken out of service. This method is 615 * only called once all threads within the servlet's 616 * service method have exited or after a timeout 617 * period has passed. After the servlet container calls this 618 * method, it will not call the service method again 619 * on this servlet. 620 * 621 * This method gives the servlet an opportunity 622 * to clean up any resources that are being held (for example, memory, 623 * file handles, threads) and make sure that any persistent state is 624 * synchronized with the servlet's current state in memory. 625 */ 626 @Override 627 public void destroy() { 628 Logger.out(Severity.INFO, "Servlet destroy() method called by container."); 629 List<String> instanceNames = new ArrayList<>(connectionInfoMap.keySet()); 630 for (String instanceName : instanceNames) { 631 Queue<AsyncContext> ongoingRequests = ongoingRequestsMap.remove(instanceName); 632 for (Iterator<AsyncContext> iterator = ongoingRequests.iterator(); iterator.hasNext();) { 633 AsyncContext asyncContext = iterator.next(); 634 asyncContext.complete(); 635 iterator.remove(); 636 } 637 connectionInfoMap.remove(instanceName); 638 instancesDeclaredStatementsMap.remove(instanceName); 639 staticJsonsMap.remove(instanceName); 640 Connection connection = connectionMap.remove(instanceName); 641 if (connection != null) 642 try { 643 connection.close(); 644 } catch (SQLException e) { 645 e.printStackTrace(); 646 } 647 ConnectionPoolManager connectionPoolManager = connectionPoolManagerMap.remove(instanceName); 648 if (connectionPoolManager != null) 649 try { 650 connectionPoolManager.close(); 651 } catch (SQLException e) { 652 e.printStackTrace(); 653 } 654 } 655 for (Iterator<Driver> iterator = loadedDrivers.iterator(); iterator.hasNext();) { 656 Driver driver = iterator.next(); 657 try { 658 DriverManager.deregisterDriver(driver); 659 Logger.out(Severity.INFO, MessageFormat.format("JDBC driver deregistered: \"{0}\"", driver.getClass().getName())); 660 } catch (SQLException e) { 661 e.printStackTrace(); 662 } finally { 663 iterator.remove(); 664 } 665 } 666 } 667 668 @Override 669 protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 670 String[] inst_and_oper = getInstanceAndOperation(request); 671 if (inst_and_oper.length == 0) { // No instance specified in URL 672 response.sendError(HttpServletResponse.SC_BAD_REQUEST, "No instance specified in URL"); 673 return; 674 } 675 676 String instanceName = inst_and_oper[0]; 677 678 if (instanceName.startsWith(ParseStmtConnectTo.NONEXPOSABLE_NAME_PREFIX) || !connectionInfoMap.containsKey(instanceName)) { 679 response.sendError(HttpServletResponse.SC_BAD_REQUEST, StringUtils.formatMessage("Instance Not Found: \"{0}\"", instanceName)); 680 return; 681 } 682 683 request.setAttribute(REQUEST_ATTRIBUTE_USER, request.getRemoteUser()); 684 685 // 686 // CORS 687 // https://developer.mozilla.org/en-US/docs/Glossary/CORS 688 // 689 690 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_CREDENTIALS, "true"); 691 692 String origin = request.getHeader(HTTP_HEADER_ORIGIN); 693 String corsAllowOrigin = servletConfig.getInitParameter(CORS_ALLOW_ORIGIN); 694 695 if (origin == null) 696 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_ORIGIN, "*"); 697 else if (origin.equals("null") || origin.startsWith("file:")) 698 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_ORIGIN, origin); 699 else if (corsAllowOrigin != null && !corsAllowOrigin.trim().isEmpty()) 700 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_ORIGIN, corsAllowOrigin); 701 else 702 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_ORIGIN, origin); 703 704 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_METHODS, "GET,POST"); 705 706 response.addHeader(HTTP_HEADER_ACCESS_CONTROL_ALLOW_HEADERS, // 707 HTTP_HEADER_CACHE_CONTROL + ',' + // 708 HTTP_HEADER_AUTHORIZATION + ',' + // 709 CUSTOM_HTTP_HEADER_CLIENT_INFO + ',' + // 710 CUSTOM_HTTP_HEADER_STATEMENT + ',' + // 711 CUSTOM_HTTP_HEADER_ROLE // 712 ); 713 714 StmtConnectTo stmtConnectTo = connectionInfoMap.get(instanceName); 715 if (stmtConnectTo == null) { 716 authorizationError(response, "Unknown instance name: " + instanceName); 717 return; 718 } 719 if (!stmtConnectTo.allowConnections && stmtConnectTo.authenticationQuery == null) { 720 authorizationError(response, "Unknown instance name: " + instanceName); 721 return; 722 } 723 if (stmtConnectTo.authenticationQuery != null) { 724 String instanceDirectory = instancesDirectoryMap.get(instanceName); 725 726 String authorization = request.getHeader(HTTP_HEADER_AUTHORIZATION); 727 if (authorization != null && authorization.toLowerCase(Locale.ENGLISH).startsWith("basic")) { 728 String base64Credentials = authorization.substring("Basic".length()).trim(); 729 String credentials = new String(sharedCoder.decoder.decode(base64Credentials), StandardCharsets.UTF_8); 730 731 /* parse username:password */ 732 String[] userAndPassword = credentials.split(":", 2); 733 734 String user = userAndPassword[0]; 735 String password = userAndPassword[1]; 736 String role = request.getHeader(CUSTOM_HTTP_HEADER_ROLE); 737 738 Connection connection = connectionMap.get(instanceName); 739 740 Map<String /* stored procedure name */, NonNativeProcedure> proceduresMap = instancesProceduresMap.get(instanceName); 741 NonNativeProcedure nonNativeProcedure = CallUtils.getCallStatementNonNativeProcedure(stmtConnectTo.authenticationQuery, proceduresMap); 742 try { 743 boolean authorized = false; 744 if (nonNativeProcedure != null) { 745 ParseNativeStmt parseNativeStmt = instancesParseNativeStmtMap.get(instanceName); 746 Procedure procedure = parseNativeStmt.parse(stmtConnectTo.authenticationQuery); 747 748 Map<String /* js file name */, Scriptable> mapScopes = instancesScopesMap.get(instanceName); 749 Map<String /* js file name */, Map<String /* function name */, Function>> mapFunctions = instancesFunctionsMap.get(instanceName); 750 751 if (nonNativeProcedure.procedureType == ProcedureType.JAVA || nonNativeProcedure.procedureType == ProcedureType.JS) { // Java or JavaScript 752 753 List<Object> parameterValues = new ArrayList<>(); 754 parameterValues.add(request); 755 parameterValues.add(response); 756 parameterValues.add(connection); 757 parameterValues.add(instanceName); 758 759 for (Object param : procedure.parameters) { 760 if (param.equals(":user")) 761 parameterValues.add(user); 762 else if (param.equals(":password")) 763 parameterValues.add(password); 764 else if (param.equals(":role")) 765 parameterValues.add(role); 766 } 767 768 Object[] parametersArray = parameterValues.toArray(new Object[parameterValues.size()]); 769 770 Object obj = null; 771 if (nonNativeProcedure.procedureType == ProcedureType.JAVA) { // Java 772 Method method = CallUtils.getMethod(nonNativeProcedure.optionsJson); 773 obj = method.invoke(null, parametersArray); 774 try (ResultSet rs = (ResultSet) obj;) { 775 authorized = rs.next(); 776 } 777 } else if (nonNativeProcedure.procedureType == ProcedureType.JS) { // JavaScript 778 // 779 // initize Rhino 780 // https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino 781 // 782 Context ctx = Context.enter(); 783 try { 784 ctx.setLanguageVersion(Context.VERSION_1_7); 785 ctx.setOptimizationLevel(9); // Rhino optimization: https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino/Optimization 786 JsFunction jsFunction = CallUtils.getFunction(instanceDirectory, nonNativeProcedure.optionsJson, mapScopes, mapFunctions); 787 obj = jsFunction.function.call(ctx, jsFunction.scope, null, parametersArray); 788 } catch (Exception e) { 789 e.printStackTrace(); 790 } finally { 791 ctx.exit(); 792 } 793 try (ResultSet rs = (ResultSet) obj;) { 794 authorized = rs.next(); 795 } 796 797 } 798 } else if (nonNativeProcedure.procedureType == ProcedureType.EXEC) { // OS 799 List<Object> parameterValues = new ArrayList<>(); 800 parameterValues.add(instanceName); 801 802 for (Object param : procedure.parameters) { 803 if (param.equals(":user")) 804 parameterValues.add(user); 805 else if (param.equals(":password")) 806 parameterValues.add(password); 807 else if (param.equals(":role")) 808 parameterValues.add(role); 809 } 810 811 Object[] parametersArray = parameterValues.toArray(new Object[parameterValues.size()]); 812 String jsonArray = CallUtils.executeOsProgramm(instanceDirectory, nonNativeProcedure.optionsJson, parametersArray); 813 jsonArray = jsonArray.trim().replace(" ", "").replace("\n", "").replace("\t", ""); 814 authorized = jsonArray.equals("[]"); 815 } else if (nonNativeProcedure.procedureType == ProcedureType.URL) { // URL 816 Map<String, Object> parametersMap = new LinkedHashMap<>(); 817 parametersMap.put("instanceName", instanceName); 818 819 for (Object param : procedure.parameters) { 820 if (param.equals(":user")) 821 parametersMap.put("user", user); 822 else if (param.equals(":password")) 823 parametersMap.put("password", password); 824 else if (param.equals(":role")) 825 parametersMap.put("role", role); 826 } 827 828 String jsonArray = CallUtils.executeUrl(instanceDirectory, nonNativeProcedure.optionsJson, parametersMap); 829 jsonArray = jsonArray.trim().replace(" ", "").replace("\n", "").replace("\t", ""); 830 authorized = jsonArray.equals("[]"); 831 } 832 833 if (!stmtConnectTo.allowConnections) 834 authorized = !authorized; 835 } else { // Native SQL statemment 836 StringBuilder preparedStatementSb = new StringBuilder(); 837 838 Map<String /* name */, List<Integer /* index */>> paramsIndexes = parseNamedPreparedStatement(stmtConnectTo.authenticationQuery, preparedStatementSb); 839 PreparedStatement ps = connection.prepareStatement(preparedStatementSb.toString()); 840 ParameterMetaData pmd = ps.getParameterMetaData(); 841 842 // 843 // parameters of custom authentication SQL statement 844 // 845 Map<String, Object> paramsMap = new HashMap<>(); 846 paramsMap.put("user", user); // :user parameter 847 paramsMap.put("password", password); // :password parameter 848 paramsMap.put("role", role); // :role parameter 849 850 for (Map.Entry<String, Object> entry : paramsMap.entrySet()) { 851 String name = entry.getKey(); 852 Object value = entry.getValue(); 853 List<Integer /* index */> indexes = paramsIndexes.get(name); 854 if (indexes != null) 855 for (int n : indexes) { 856 int parameterType = pmd.getParameterType(n); 857 if (value == null) 858 ps.setNull(n, parameterType); 859 else 860 ps.setString(n, value.toString()); 861 } 862 } 863 try (ResultSet rs = ps.executeQuery();) { 864 authorized = rs.next(); 865 } 866 } 867 if (!authorized) { 868 authorizationError(response, "Bad combination of user, password, role. Authorization Failed."); 869 return; 870 } 871 request.setAttribute(REQUEST_ATTRIBUTE_USER, user); 872 } catch (Exception e) { 873 e.printStackTrace(); 874 authorizationError(response, "Authorization query error. Authorization Failed."); 875 } 876 } else { 877 if (!request.getMethod().trim().toUpperCase(Locale.ENGLISH).equals("OPTIONS")) { 878 authorizationError(response, "Authorization Required"); 879 return; 880 } 881 } 882 } 883 884 String remoteUser = request.getRemoteUser(); 885 if (remoteUser != null) { 886 String role = request.getHeader(CUSTOM_HTTP_HEADER_ROLE); 887 if (remoteUser != null && role != null && !request.isUserInRole(role)) { 888 authorizationError(response, "User is not in role"); 889 return; 890 } 891 } 892 super.service(request, response); 893 } 894 895 @Override 896 protected void doOptions(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 897 response.setStatus(HttpServletResponse.SC_ACCEPTED); // 202 Accepted. (https://tools.ietf.org/html/rfc7231#section-6.3.3) 898 } 899 900 @Override 901 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 902 String[] inst_and_oper = getInstanceAndOperation(request); 903 if (inst_and_oper.length == 2) { 904 String instanceName = inst_and_oper[0]; 905 String operName = inst_and_oper[1]; 906 907 if (operName.equals("events")) { 908 Queue<AsyncContext> ongoingRequests = ongoingRequestsMap.get(instanceName); 909 910 response.setHeader("Content-Type", "text/plain; charset=UTF-8"); 911 response.setHeader(HTTP_HEADER_CACHE_CONTROL, "no-cache, no-store"); 912 response.setHeader("Connection", "keep-alive"); 913 //response.setHeader("Transfer-Encoding", "chunked"); 914 915 /** 916 * https://tomcat.apache.org/tomcat-9.0-doc/api/org/apache/catalina/Globals.html#ASYNC_SUPPORTED_ATTR 917 */ 918 //request.setAttribute("org.apache.catalina.ASYNC_SUPPORTED", true); 919 AsyncContext asyncCtx = request.startAsync(request, response); 920 asyncCtx.setTimeout(0); // A timeout value of zero or less indicates no timeout. (https://docs.oracle.com/javaee/6/api/javax/servlet/AsyncContext.html#setTimeout(long)) 921 asyncCtx.addListener(new AsyncListener() { 922 923 @Override 924 public void onComplete(AsyncEvent ae) { 925 AsyncContext asyncContext = ae.getAsyncContext(); 926 ongoingRequests.remove(asyncContext); 927 } 928 929 @Override 930 public void onTimeout(AsyncEvent ae) { 931 AsyncContext asyncContext = null; 932 try { 933 asyncContext = ae.getAsyncContext(); 934 asyncContext.complete(); 935 } catch (Throwable e) { 936 e.printStackTrace(); 937 } finally { 938 ongoingRequests.remove(asyncContext); 939 } 940 } 941 942 @Override 943 public void onError(AsyncEvent ae) { 944 AsyncContext asyncContext = null; 945 try { 946 asyncContext = ae.getAsyncContext(); 947 asyncContext.complete(); 948 } catch (Throwable e) { 949 e.printStackTrace(); 950 } finally { 951 ongoingRequests.remove(asyncContext); 952 } 953 } 954 955 @Override 956 public void onStartAsync(AsyncEvent ae) throws IOException { 957 } 958 }, request, response); 959 ongoingRequests.add(asyncCtx); 960 } 961 } else 962 doDbRequest(request, response); 963 } 964 965 @Override 966 public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 967 doDbRequest(request, response); 968 } 969 970 private void doDbRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 971 String[] inst_and_oper = getInstanceAndOperation(request); 972 973 String instanceName = inst_and_oper[0]; 974 StmtConnectTo stmtConnectTo = connectionInfoMap.get(instanceName); 975 ConnectionPoolManager connectionPoolManager = connectionPoolManagerMap.get(instanceName); 976 Map<String /* SQL statement name */, StmtDeclareStatement> declaredStatementsMap = instancesDeclaredStatementsMap.get(instanceName); 977 Map<String /* stored procedure name */, NonNativeProcedure> proceduresMap = instancesProceduresMap.get(instanceName); 978 Map<String /* js file name */, Scriptable> mapScopes = instancesScopesMap.get(instanceName); 979 Map<String /* js file name */, Map<String /* function name */, Function>> mapFunctions = instancesFunctionsMap.get(instanceName); 980 ParseNativeStmt parseNativeStmt = instancesParseNativeStmtMap.get(instanceName); 981 String instanceDirectory = instancesDirectoryMap.get(instanceName); 982 983 Queue<AsyncContext> ongoingRequests = ongoingRequestsMap.get(instanceName); 984 985 Map<StaticStatement, ReadyResult> mapJson = staticJsonsMap.get(instanceName); 986 /** 987 * https://tomcat.apache.org/tomcat-9.0-doc/api/org/apache/catalina/Globals.html#ASYNC_SUPPORTED_ATTR 988 */ 989 request.setAttribute("org.apache.catalina.ASYNC_SUPPORTED", true); 990 AsyncContext asyncCtx = request.startAsync(); 991 asyncCtx.setTimeout(0); // A timeout value of zero or less indicates no timeout. (https://docs.oracle.com/javaee/6/api/javax/servlet/AsyncContext.html#setTimeout(long)) 992 asyncCtx.addListener(new AsyncListener() { 993 994 @Override 995 public void onComplete(AsyncEvent ae) { 996 } 997 998 @Override 999 public void onTimeout(AsyncEvent ae) { 1000 AsyncContext asyncContext = null; 1001 try { 1002 asyncContext = ae.getAsyncContext(); 1003 asyncContext.complete(); 1004 } catch (Throwable e) { 1005 e.printStackTrace(); 1006 } 1007 } 1008 1009 @Override 1010 public void onError(AsyncEvent ae) { 1011 AsyncContext asyncContext = null; 1012 try { 1013 asyncContext = ae.getAsyncContext(); 1014 asyncContext.complete(); 1015 } catch (Throwable e) { 1016 e.printStackTrace(); 1017 } 1018 } 1019 1020 @Override 1021 public void onStartAsync(AsyncEvent ae) throws IOException { 1022 } 1023 }, request, response); 1024 new DbRequestProcessor( // 1025 instanceName, // 1026 instanceDirectory, // 1027 stmtConnectTo, // 1028 asyncCtx, // 1029 DEBUG, // 1030 connectionPoolManager, // 1031 declaredStatementsMap, // 1032 proceduresMap, // 1033 mapScopes, // 1034 mapFunctions, // 1035 mapJson, // 1036 ongoingRequests, // 1037 parseNativeStmt, // 1038 sharedCoder // 1039 ).run(); 1040 1041 } 1042 1043 /** 1044 * Extract instance name from request 1045 * 1046 * @param request - HttpServletRequest request 1047 * @return - empty array if instance was not specified in URL 1048 * - array with single element (instance name) if operation was not specified in URL 1049 * - array with two elements (instance name and operation) if instance name and operation was specified in URL 1050 */ 1051 private static String[] getInstanceAndOperation(HttpServletRequest request) { 1052 String path = request.getPathInfo(); 1053 if (path == null) // case when URL come without slash at the end (E.g. http://localhost:8080/app). 1054 return new String[0]; 1055 return path.substring(1).split("/"); 1056 } 1057 1058 /** 1059 * 1060 * @param response 1061 * @param message 1062 * @throws IOException 1063 */ 1064 private static void authorizationError(HttpServletResponse response, String message) throws IOException { 1065 response.setStatus(HttpServletResponse.SC_UNAUTHORIZED); 1066 try (OutputStream os = response.getOutputStream()) { 1067 os.write(("{" + q("message") + ":" + q(message) + "}").getBytes(StandardCharsets.UTF_8)); 1068 os.flush(); 1069 } 1070 } 1071} 1072 1073/* 1074Please contact FBSQL Team by E-Mail fbsql.team@gmail.com 1075or visit https://fbsql.github.io if you need additional 1076information or have any questions. 1077*/ 1078 1079/* EOF */