8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * Copyright (c) 2006 Sun Microsystems Inc. All Rights Reserved
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * The contents of this file are subject to the terms
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * of the Common Development and Distribution License
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * (the License). You may not use this file except in
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * compliance with the License.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * You can obtain a copy of the License at
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * https://opensso.dev.java.net/public/CDDLv1.0.html or
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * See the License for the specific language governing
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * permission and limitations under the License.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * When distributing Covered Code, include this CDDL
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * Header Notice in each file and include the License file
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * If applicable, add the following below the CDDL Header,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * with the fields enclosed by brackets [] replaced by
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * your own identifying information:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * "Portions Copyrighted [year] [name of copyright owner]"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * $Id: LogReadDBHandler.java,v 1.4 2008/06/25 05:43:36 qcheng Exp $
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * Portions Copyrighted [2011] [ForgeRock AS]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Fosterimport com.sun.identity.log.util.LogRecordSorter;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster/**LogReadDBHandler class implements ReadDBHandler interface.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * This class name will be stored as a configuration parameter.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * LogReader will instantiate it at run time (when messages are logged
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * into file). This class reads the DB table, applies query if any,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * sorts records on field name when required, collects most recent records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * (default option) or all records. It returns result 2D String to the
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * caller (LogReader).
8af80418ba1ec431c8027fa9668e5678658d3611Allan Fosterpublic class LogReadDBHandler implements ReadDBHandler {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // private attributes
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private String [][] queryResult; // will hold the return value
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // internal storage for records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private ArrayList listOfValidRecords = new ArrayList();
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * constructor does nothing
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * LogReader calls this method. It collects header, records,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * applies query (if any), sorts (if asked) the records on field, checks
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * the max records to return, collects all the recods and returns.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param tableName db table name
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param logQuery is user specified qury chriteria with sorting requirement
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param logMgr the log manager associated with this handler
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param sourceData it specifies whether return data should be original
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * data received by logger (source) or formatted data as in file.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @return all the matched records with query
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws IOException if it fails to read log records.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws NoSuchFieldException if it fails to retrieve the name of field.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws IllegalArgumentException if query has wrong value.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws RuntimeException if it fails to retrieve log record.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws SQLException if it fails to process sql query.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws Exception if it fails any of operation.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if the object is persistence use it otherwise don't
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.databaseURL = logMgr.getProperty(LogConstants.LOG_LOCATION);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbDriver = logMgr.getProperty(LogConstants.DB_DRIVER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbUserName = logMgr.getProperty(LogConstants.DB_USER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbPassWord = logMgr.getProperty(LogConstants.DB_PASSWORD);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecordsStr = logMgr.getProperty(LogConstants.MAX_RECORDS);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // rethrow the exception
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (this.dbDriver.toLowerCase().indexOf("oracle") != -1) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } else if (this.dbDriver.toLowerCase().indexOf("mysql") != -1) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.warning("DBlogRecRead:assuming driver: '" + this.dbDriver +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "' is Oracle-compatible.");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (sourceData == true) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectStr = lq2Select (tableName, columns, logQuery);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("logRecRead/4:selectStr = " + selectStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("logRecRead/4.2:selectStr = " + selectStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // got the LogQuery part converted to SQL. the "order by"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (sortBy) and limit/rownum parts are different for oracle
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // and mysql.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecords = Integer.parseInt(maxRecordsStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "DBlogRecRead: maxRecords error (" + maxRecordsStr +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "), set to MAX");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecords = LogConstants.MAX_RECORDS_DEFAULT_INT;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBlogRecRead:about to execute: " + selStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // fetchsize appears to be 10 from rs.getFetchSize();
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBlogRecRead:#columns = " + numberOfColumns);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // these are the column (field) names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String [] spltHdrStr = new String[numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // have to figure out #rows
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBlogRecRead:#rows = " + numberOfRows);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error ("DBlogRecRead:rows=0:conn.close (" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // should be at least the column names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // checks whether it has got any record or not
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // weird if it's 0...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if no record found return null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster queryResult[i] = (String [])listOfValidRecords.get(i);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // don't care about this too much...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw new AMLogException(AMLogException.LOG_DB_TOOMANYRECORDS);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // reset to the beginning
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (isFirst == false) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // remember it's one for the column names, too
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster tableResults = new String[rowsToAlloc][numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if LogQuery.MOST_RECENT_MAX_RECORDS selected,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // then we just have to get the "this.maxRecords" records.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster skipThisManyRecords = numberOfRows - this.maxRecords;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // always do the first one... the column names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // checks whether it has got any record or not
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if no record found return null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // sorting already done by DB
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error ("DBlogRecRead:conn.close (" + ex.getErrorCode() +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster queryResult[i] = (String [])listOfValidRecords.get(i);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * LogReader calls this method. It collects header, records,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * applies query (if any), sorts (if asked) the records on field, checks
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * the max records to return, collects all the recods and returns.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param tableNames db table names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param logQuery is user specified qury chriteria with sorting requirement
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param logMgr the log manager associated with this handler
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param sourceData it specifies whether return data should be original
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * data received by logger (source) or formatted data as in file.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @return all the matched records with query
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws IOException if it fails to read log records.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws NoSuchFieldException if it fails to retrieve the name of field.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws IllegalArgumentException if query has wrong value.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws RuntimeException if it fails to retrieve log record.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws SQLException if it fails to process sql query.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @throws Exception if it fails any of operation.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster ) throws IOException, NoSuchFieldException, IllegalArgumentException,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if the object is persistence use it otherwise don't
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // tblNames is needed for the guaranteed "underscore" form
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (e.g., "amAuthentication_access") of the table names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster StringBuilder allTablesSB = new StringBuilder("");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tableNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // allTablesSB contains the list of tables for use in the
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select statement.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.databaseURL = logMgr.getProperty(LogConstants.LOG_LOCATION);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbDriver = logMgr.getProperty(LogConstants.DB_DRIVER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbUserName = logMgr.getProperty(LogConstants.DB_USER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbPassWord = logMgr.getProperty(LogConstants.DB_PASSWORD);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecordsStr = logMgr.getProperty(LogConstants.MAX_RECORDS);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // rethrow the exception
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // see if we're using Oracle or MySQL, as there are
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // some differences between SQL for Oracle and MySQL.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (this.dbDriver.toLowerCase().indexOf("oracle") != -1) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } else if (this.dbDriver.toLowerCase().indexOf("mysql") != -1) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.warning("DBlogRecRead:assuming driver: '" + this.dbDriver +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "' is Oracle-compatible.");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecords = Integer.parseInt(maxRecordsStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "DBlogRecRead(s): maxRecords error (" + maxRecordsStr +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "), set to MAX");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecords = LogConstants.MAX_RECORDS_DEFAULT_INT;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // kind of a bad situation here between Oracle and MySQL.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // pre-v4 MySQL doesn't support the "union" operator, so multi-
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // table selects has to be affected as multiple single-table
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // selects and combining their results.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the Oracle case is more straightforward, and if we decide
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // to only support >= V4 MySQL, can just use the !isMySQL
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (sourceData == true) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectStr = lq2Select (tblNames, columns, logQuery);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("logRecRead/4:selectStr = " + selectStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectStr = lq2Select (tblNames, columns, null);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("logRecRead/4.2:selectStr = " + selectStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBlogRecRead:about to execute: " + selStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // fetchsize appears to be 10 from rs.getFetchSize();
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // these are the column (field) names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String [] spltHdrStr = new String[numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // have to figure out #rows
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBlogRecRead:#rows = " + numberOfRows);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // possible to have no records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBlogRecRead:rows=0:conn.close (" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // should be at least the column names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // weird if it's 0...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster queryResult[i] = (String [])listOfValidRecords.get(i);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // don't care about this too much...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // reset to the beginning
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (isFirst == false) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // think we're not going to allow MOST_RECENT_MAX_RECORDS
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // with multi-table query...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster tableResults = new String[rowsToAlloc][numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // always do the first one... the column names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // checks whether it has got any record or not
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if no record found return null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // sorting already done by DB
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error ("DBlogRecRead:conn.close (" + ex.getErrorCode() +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster queryResult[i] = (String [])listOfValidRecords.get(i);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } else { // else (isMySQL case)
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // Multi-table select for <V4 MySQL is essentially
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // looping through the tables and combining the results.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (sourceData == true) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // do same select on each table
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster boolean isFirstTable = true;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tblNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (sourceData == true) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectStr = lq2Select (thisTable, columns, logQuery);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("logRecRead/5:selectStr = " + selectStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectStr = lq2Select (thisTable, columns, null);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // send the select statement
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBlogRecRead:about to execute: " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // fetchsize appears to be 10 from rs.getFetchSize();
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // get the column (field) names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // put them into the listOfValidRecords, but only
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the for the first table... don't need or want
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // them scattered about later. and they should be
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the same for all the tables...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String [] spltHdrStr = new String[numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // have to figure out #rows
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // don't care about this too much...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // reset to the "beginning"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (isFirst == false) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // think we're not going to allow
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // MOST_RECENT_MAX_RECORDS with multi-table query...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // print the actual results in the debug log
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // catch & rethrow it
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } // the for loop for the set of tables
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // probably have to sort again
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String sortByField = logQuery.getSortingField();
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // checks whether it has got any record or not
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if no record found return null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // can we use the toArray() converter?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster queryResult[i] = (String [])listOfValidRecords.get(i);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * Return table names for each logger
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param logMgr Log Manager that is maintaing table names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @return table names for each logger
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster public String [][] getTableNames(java.util.logging.LogManager logMgr) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.databaseURL = logMgr.getProperty(LogConstants.LOG_LOCATION);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbDriver = logMgr.getProperty(LogConstants.DB_DRIVER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbUserName = logMgr.getProperty(LogConstants.DB_USER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbPassWord = logMgr.getProperty(LogConstants.DB_PASSWORD);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecordsStr = logMgr.getProperty(LogConstants.MAX_RECORDS);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetTableNames:connect:SQE:code=" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (this.dbDriver.toLowerCase().indexOf("oracle") != -1) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // gonna be something like:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select table_name from dba_all_tables where owner = 'AMADMIN';
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster "select table_name from dba_all_tables where owner = '" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } else if (this.dbDriver.toLowerCase().indexOf("mysql") != -1) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // gonna be:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // show tables
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBgetTableNames:about to execute: " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBgetTableNames:#columns = " + numberOfColumns);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String [] spltHdrStr = new String[numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // have to figure out #rows
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBgetTableNames:#rows = " + numberOfRows);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // reset to the beginning
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if (isFirst == false) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetTableNames:first() is false!");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster tableResults = new String[numberOfRows][numberOfColumns];
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetTableNames:query:SQE:code=" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error ("DBgetTableNames:conn.close (" + ex.getErrorCode() +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * Return number of records in each table
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @param logMgr Log Manager that is maintaing table names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster * @return number of records in each table
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster public long getNumberOfRows(java.util.logging.LogManager logMgr,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.databaseURL = logMgr.getProperty(LogConstants.LOG_LOCATION);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbDriver = logMgr.getProperty(LogConstants.DB_DRIVER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbUserName = logMgr.getProperty(LogConstants.DB_USER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.dbPassWord = logMgr.getProperty(LogConstants.DB_PASSWORD);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster this.maxRecordsStr = logMgr.getProperty(LogConstants.MAX_RECORDS);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetNumberOfRows:connect:SQE:code=" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetgetNumberOfRows:connect:CNFE: " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String queryString = "select count(*) from " + fName;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBgetgetNumberOfRows:about to execute: " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("DBgetNumberOfRows:#columns = " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetNumberOfRows:query:SQE:code=" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // might not care about this too much...?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error ("DBgetNumberOfRows:conn.close (" +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBgetNumberOfRows:got " + result +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster " as number of rows, returning 0.");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // private, mostly debugging method to display results
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // should be called after the ResultSet has been gone
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // through already, as you can't point the cursor to before
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the beginning once you've made it move.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private void displayResultSet (ResultSet myrs) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("displayRS:#columns = " + numberOfColumns);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("displayRS:columns =\n" + sbtemp.toString());
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("displayRS:row #" + rowNum + " = " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // put cursor back at beginning
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error ("displayRS:got SQLException: " + ex.getMessage());
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ELF version reads file, splits into fields, validates
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // field values and collects it.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // DB (this) version already has received the rows and put
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // them into tableResults, a 2D String array, which is passed
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private boolean getRecords(String [][] tblResults, boolean isSourceData)
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // could pass in the number of rows, or make it global,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // but for now, just figure it out.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // process the rows of columns
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // query to DB already executed the "query"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster return true;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // below method reset previous readings and prepare for new one.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // userName = lmanager.getProperty(LogConstants.DB_USER);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // password = lmanager.getProperty(LogConstants.DB_PASSWORD);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private void connectToDatabase(String userName, String password)
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster DriverManager.getConnection(databaseURL, userName, password);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.error("DBlogRecRead:connect:ClassNotFoundException: " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster throw e; // rethrow
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if start up with Oracle DB down, can get sqle.getErrorCode()
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // == 1034, "ORA-01034: ORACLE not available"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // MySQL returns error code 0, message:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // "unable to connect to any hosts due to
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // exception: java.net.ConnectException: Connection refused
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster sqle.getErrorCode() + "): " + sqle.getMessage());
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // LogQuery-to-SQL-Select converter
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // tblName is the table to select on
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // columns contains the comma-separated column names to return...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // e.g., "time, data, LoginID"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // lq is the LogQuery specified. if no logquery, then
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // lq2Select returns "select <columns> from <tblName>;"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private String lq2Select (String tblName, String columns, LogQuery lq) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String whatSBuf = "*"; // select what from tblName?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String opStr = " and "; // default to "and"; could be "or"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster int lqMatchAny = com.sun.identity.log.LogQuery.MATCH_ANY_CONDITION;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster boolean getAllRecs = false;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // check if query
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((columns != null) && (columns.length() > 0)) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if LogQuery.MOST_RECENT_MAX_RECORDS or
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // LogQuery.ALL_RECORDS, need to retrieve all records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((numRecs == LogQuery.MOST_RECENT_MAX_RECORDS) ||
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("lq2Select:getAllRecs = " + getAllRecs +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster ", numRecs = " + numRecs + ", sortByField = " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((queries == null) || (qrySize = queries.size()) == 0) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(" limit ").append(numRecsStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(whatSBuf).append(" from ").append
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // limiting for oracle comes first after "where"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append("rownum < ").append(numRecsStr).append
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // get the columns/values to search on
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster boolean moreThanOneQuery = false;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if more than one query, then they all should be with
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // a set of parens, and each should be within parens, e.g.:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ((loginid like 'uid=user2%') or (loginid like 'uid=amAdmin%'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // both have to be non-null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((fldName != null) && (fldName.length() > 0) &&
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '%").append(fldValue).append("%'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '").append(fldValue).append("%'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '%").append(fldValue).append("'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '").append(fldValue).append("'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if not last element, then
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // append operator in prep for next element
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // add the "order by" part
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((sortStr != null) && (sortStr.length() > 0)) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(" order by ").append(sortStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // for MySQL, the limit comes last
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(" limit ").append(numRecsStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("lq2Select:select = " + selectSBuf.toString());
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // LogQuery-to-SQL-Select converter for multiple tables
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // tblNames is the Set of tables to select on
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // columns contains the comma-separated column names to return...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // e.g., "time, data, LoginID", or "*"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // lq is the LogQuery specified. if no logquery, then
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // lq2Select returns "select <columns> from <tblName>;"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private String lq2Select (Set tblNames, String columns, LogQuery lq) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String whatSBuf = "*"; // select what from tblName?
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String opStr = " and "; // default to "and"; could be "or"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster int lqMatchAny = com.sun.identity.log.LogQuery.MATCH_ANY_CONDITION;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster boolean getAllRecs = false;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // columns is at least "*"...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if LogQuery.MOST_RECENT_MAX_RECORDS or
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // LogQuery.ALL_RECORDS, need to retrieve all records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if no LogQuery, assume all records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((numRecs == LogQuery.MOST_RECENT_MAX_RECORDS) ||
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // sortByField has to explicitly be in the the
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // columns requested ("columns"); "columns" can't just
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // found sorting field in columns specified
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("lq2Select:getAllRecs = " + getAllRecs +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster ", numRecs = " + numRecs + ", sortByField = " +
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // no LogQuery
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // for Oracle, which supports the union directive:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // "select <whatSBuf> from tbl1
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select <whatSBuf> from tbl2
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select <whatSBuf> from tblx
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // [order by sortByField]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // no "order by" or maxrecords if no LogQuery
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // this is the Oracle version (using "union")
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // no query, so the select should look like:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // selectSBuf currently contains "select "
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String baseSelect = "select " + whatSBuf + " from ";
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tblNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(baseSelect).append(it.next());
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // LogQuery, but no QueryElement(s).
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // tempSBuf contains the "select <columns> from " part, which will
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // be the beginning of each select.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // oracle can use "union", but mysql < v4 cannot.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((queries == null) || (qrySize = queries.size()) == 0) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if no query elements, then just the number of records
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // to return is applied. select should look something like
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (for oracle):
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // "select * from tbl1 where rownum < [numRecsStr]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tbl2 where rownum < [numRecsStr]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tblx where rownum < [numRecsStr]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // can add the " order by <sortByField>" to the end,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if there is a <sortByField> value.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // for mysql, can't do:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // "select * from tbl1,tb2,...,tblN"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // as you get N*(#cols)-wide rows, and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // #rows(tbl1)*#rows(tbl2)*...*#rows(tblN) rows
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // returned.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tblNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // add tablename to "select * from "
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(CR).append("union").append(CR);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // got table(s), LogQuery, and QueryElement(s).
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select (for oracle) will look something like:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select <columns> from tbl1 where [rownum < <numRecsStr> and ]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ((fld1 = 'val1') <opStr>
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fld2 = 'val2') <opStr>
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fldx = 'valx'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select <columns> from tbl2 where [rownum < <numRecsStr> and ]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ((fld1 = 'val1') <opStr>
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fld2 = 'val2') <opStr>
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fldx = 'valx'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select <columns> from tblN where [rownum < <numRecsStr> and ]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ((fld1 = 'val1') <opStr>
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fld2 = 'val2') <opStr>
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fldx = 'valx'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // [order by <sortByField>]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // for mysql (<v4 doesn't support union), this is incorrect:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tbl1, tbl2, ..., tbln where ((tbl1.fld1 = 'val1')
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // and (tbl1.fldx = 'valx')) or ((tbl2.fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (tbl2.fld2 = 'val2')) or ... ((tbln.fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (tbln.fld2 = 'val2))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the operator was changed from "or" to "and" in the mysql
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // example to illustrate that the "or" between the tables'
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // clauses will be fixed (not specifiable).
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // differences between oracle and mysql to be observed here...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the structure of the select with all the tables' names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // first, then the where clauses needing the tables' names
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // sequentially doesn't make for efficient coding...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // tempSBuf has the "select <columns> from " part.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tblNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tblNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String wherePart = doMySQLQueryElement(tblStr, queries, opStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // for MySQL, the limit comes last
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(" limit ").append(numRecsStr);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } else { // Oracle
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // get the part that goes after "select * from "
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // use sortByField, not lq.getSortingField()...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // checked above that it's explicitly in the columns
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // requested.
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String wherePart = doOracleQueryElements (queries, numRecsStr,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // tempSBuf has the "select <columns> from " part
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster for (Iterator it = tblNames.iterator(); it.hasNext(); ) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(sStr).append(it.next()).append(" where ").
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster selectSBuf.append(CR).append("union").append(CR);
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // this is the attempted fix for order by
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster } // !isMySQL
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster Debug.message("lq2Select:select = " + selectSBuf.toString());
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // create the "where..." clause from the QueryElements...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the part that comes after "where ".
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select (for oracle) will look something like:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tbl1 where [rownum < n] and ((fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fldx = 'valx')) union
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tbl2 where [rownum < n] and ((fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fldx = 'valx')) union
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tbln where [rownum < n] and ((fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (fldx = 'valx'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private String doOracleQueryElements (ArrayList qes, String numRecsStr,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String opStr, boolean getAllRecs, String sortStr)
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // get the columns/values to search on
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster boolean moreThanOneQuery = false;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // limiting for oracle comes first after "where"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // numRecStr is null if getAllRecs is true. could have
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // passed that, but...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((numRecsStr != null) && (numRecsStr.length() > 0)) {
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append("rownum < ").append(numRecsStr).append(" and ");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if more than one query, then they all should be with
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // a set of parens, and each should be within parens, e.g.:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ((loginid like 'uid=user2%') or (loginid like 'uid=amAdmin%'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // both have to be non-null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((fldName != null) && (fldName.length() > 0) &&
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '%").append(fldValue).append("%'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '").append(fldValue).append("%'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '%").append(fldValue).append("'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '").append(fldValue).append("'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if not last element, then
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // append operator in prep for next element
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // add the "order by" part, if there should be one
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // create the "where..." clause from the QueryElements...
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // the part that comes after "where ".
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // for mysql (<v4 doesn't support union):
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // select * from tbl1, tbl2, ..., tbln where ((tbl1.fld1 = 'val1')
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // and (tbl1.fldx = 'valx')) or ((tbl2.fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (tbl2.fld2 = 'val2')) or ... ((tbln.fld1 = 'val1') and
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // (tbln.fld2 = 'val2)) [limit n]
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster private String doMySQLQueryElement(String tblname, ArrayList qes,
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // get the columns/values to search on
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster boolean moreThanOneQuery = false;
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if more than one query element, then they all should be with
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // a set of parens, and each should be within parens, e.g.:
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // ((loginid like 'uid=user2%') or (loginid like 'uid=amAdmin%'))
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // now for the "(tblname.col <relation> val) <opStr> (tblname.col..)"
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster String fldName = tblname + "." + qe.getFieldName();
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // both have to be non-null
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster if ((fldName != null) && (fldName.length() > 0) &&
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '%").append(fldValue).append("%'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '").append(fldValue).append("%'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '%").append(fldValue).append("'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster whereSBuf.append(" '").append(fldValue).append("'");
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // if not last element, then
8af80418ba1ec431c8027fa9668e5678658d3611Allan Foster // append operator in prep for next element