/*
* Copyright 2013 Matt Sicker and Contributors
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package atg.tools.dynunit.adapter.gsa;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.jetbrains.annotations.Nullable;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
/**
* THIS IS A REALLY SIMPLE APPLICATION THAT
* TAKES A STRING ARRAY OF SQL FILES AND
* RETURNS ALL OF THE SQL STATEMENTS THAT
* MUST BE EXECUTED WITHIN THE FILES
* DATE: MAY 23, 2000
*
* @author jb
* 7/14/2002
*/
public class SQLFileParser {
private static final Logger logger = LogManager.getLogger();
// ****************************************
// THERE IS ONLY ONE CLASS VARIABLE
// FOR THIS CLASS. THAT VARIABLE IS TO
// STORE THE FILE NAMES.
// ****************************************
private String[] sFileNameArray = { "" };
/**
* sets the names of files to be parsed
*/
private void setFileNameArray(String[] pFileNameArray) {
sFileNameArray = pFileNameArray;
}
// /** returns the names of files to be parsed */
// private String[] getFileNameArray()
// {
// return (sFileNameArray);
// }
// ============== CONSTRUCTORS =================
/**
* no arg constructor
*/
public SQLFileParser() {
}
// ================= PUBLIC METHODS =================
private String trimDebuggingCharacters(@Nullable String sText) {
if ( sText == null ) {
return sText;
}
return sText.trim();
/*
char[] ch = new char [sText.length()];
ch = sText.toCharArray();
String sTemp = "";
for (int i=0; i < sText.length(); i++)
{
if ((ch[i] == '\n') || (ch[i] == '\r') || (ch[i] == '\t') || (ch[i] == ' '))
{
// Keep going
sTemp = sTemp + " ";
}
else
{
sTemp = sTemp + ch[i];
}
}
if (sTemp.length() > DEBUGGING_LENGTH)
{
return (sTemp.substring(0,DEBUGGING_LENGTH));
}
else
{
return (sTemp);
}
*/
}
private String RemoveWhiteSpaceInFront(String sText) {
// **********************************************************************************
// THIS FUNCTION REMOVES WHITESPACE IN THE FRONT OF A STRING. FOR INSTANCE
// IF A STRING IS PASSED TO IT AS ' SPACE', THEN IT WILL RETURN 'SPACE.
// **********************************************************************************
char[] ch = new char[sText.length()];
ch = sText.toCharArray();
//String sTemp;
for ( int i = 0; i < sText.length(); i++ ) {
if ( (ch[i] == '\n') || (ch[i] == '\r') || (ch[i] == '\t') || (ch[i] == ' ') || (ch[i]
== ' ') ) {
// Keep going
} else {
return (sText.substring(i, sText.length()));
}
}
return (sText);
}
// private String RemoveWhiteSpaceInEnd (String sText)
// {
//// **********************************************************************************
//// THIS FUNCTION REMOVES WHITESPACE IN THE END OF A STRING. FOR INSTANCE
//// IF A STRING IS PASSED TO IT AS 'SPACE ', THEN IT WILL RETURN 'SPACE.
//// **********************************************************************************
// char[] ch = new char [sText.length()];
// ch = sText.toCharArray();
// String sTemp;
// for (int i= sText.length() - 1; i > 0; i--)
// {
// if ((ch[i] == '\n') || (ch[i] == '\r') || (ch[i] == '\t')|| (ch[i] == ' ') || (ch[i] == ' '))
// {
// // Keep going
// }
// else
// {
// return (sText.substring (0, i +1));
// }
// }
// return (sText);
// }
private String RemoveWhiteSpaceFromString(String sText) {
if ( sText == null ) {
return null;
}
return sText.trim();
// **********************************************************************************
// THIS FUNCTION REMOVES WHITESPACE IN THE FRONT AND END OF A STRING. FOR INSTANCE
// IF A STRING IS PASSED TO IT AS ' SPACE ', THEN IT WILL RETURN 'SPACE.
// **********************************************************************************
/*
sText = RemoveWhiteSpaceInFront (sText);
sText = RemoveWhiteSpaceInEnd (sText);
return (sText);
*/
}
private int countFileArraySize(String[] sArray) {
// ****************************************
// THIS IS A USEFUL FUNCTION TO
// COUNT THE SIZE OF AN ARRAY. IT
// TAKES AN ARRAY AS A PARAMETER
// AND IT RETURNS AN INTEGER AS
// THE COUNT. IN THIS CASE WE
// DO NOT NEED TO PASS THE ARRAY
// SINCE IT IS A CLASS VARIABLE
// ****************************************
List<String> newList = Arrays.asList(sArray);
return (newList.size());
}
private boolean checkForKeywords(String sLineRead) {
// ****************************************
// THIS IS ONE OF THE MOST CRITICAL
// FUNCTIONS IN THIS PROGRAM. IT
// TAKES THE RESERVED WORDS FROM
// SQL INSTALL SCRIPTS AND RESERVES
// THEM WITHIN THIS APPLICATION.
// EACH WORD LISTED BELOW STARTS
// A SQL STATEMENT.
// ****************************************
String[] sKeywordList = {
"BEGIN ",
"CREATE ",
"INSERT ",
"UPDATE ",
"DELETE ",
"COMMIT ",
"COMMIT",
"ALTER ",
"DROP ",
"GRANT ",
"GO",
"IF OBJECT_ID"
};
List<String> lList = Arrays.asList(sKeywordList);
int iArraySize = lList.size();
sLineRead = RemoveWhiteSpaceInFront(sLineRead);
//sLineRead = sLineRead.trim();
// something about using trim() doesn't work with the overall logic of the program.
for ( int i = 0; i < iArraySize; i++ ) {
if ( sLineRead.toUpperCase().startsWith(sKeywordList[i].toUpperCase()) ) {
// we need to check more than just whether the line starts
// with the keyword. we also have to try to determine that
// the keyword we found is actually NOT just the beginning of a
// bigger word. for example, Google.
// so...
if ( sLineRead.length() == sKeywordList[i].trim().length() ) {
// if the whole line is just the keyword...
return true;
} else if ( sLineRead.charAt(sKeywordList[i].trim().length()) == ' ' ) {
// if the char after the word is a space
return true;
} else if ( sLineRead.charAt(sKeywordList[i].trim().length()) == '(' ) {
// if the char after the word is a right paren "("
return true;
}
}
}
return (false);
}
private String checkForComments(String sLineRead) {
// ****************************************
// THIS FUNCTION HAS ONE DUTY. IT CHECKS
// FOR LINES IN SQL SCRIPT THAT HAVE
// COMMENTS IN THEM. IF IT FINDS A LINE
// IN A SQL SCRIPT THAT WOULD BE RESERVED
// FOR A COMMENT IT WILL SKIP THAT LINE.
// ****************************************
String[] sKeywordList = { "--", "//" };
List<String> lList = Arrays.asList(sKeywordList);
int iArraySize = lList.size();
//sLineRead = sLineRead;
for ( int i = 0; i < iArraySize; i++ ) {
if ( sLineRead.toUpperCase().contains(sKeywordList[i]) ) {
return (sLineRead.substring(0, sLineRead.indexOf(sKeywordList[i])));
}
}
return (sLineRead);
}
private String checkForSemiColons(String sCurrentLine) {
// ****************************************
// THIS FUNCTION HAS ONE DUTY. IT CHECKS
// FOR SQL STATEMENTS THAT END IN THE
// SEMICOLON PUNCTUATION AND REMOVES
// THE SEMICOLON.
// ****************************************
if ( sCurrentLine.contains(";") ) {
sCurrentLine = sCurrentLine.substring(0, sCurrentLine.indexOf(";"));
}
return (sCurrentLine);
}
private String checkForEndLines(String sCurrentLine) {
// ****************************************
// THIS FUNCTION HAS ONE DUTY. IT CHECKS
// FOR SQL STATEMENTS THAT END IN THE
// SEMICOLON PUNCTUATION AND REMOVES
// THE SEMICOLON. Sometimes if there
// is division in a view that is created
// the end line will be used, and sometimes
// it is used to execute a statement.
// ****************************************
sCurrentLine = RemoveWhiteSpaceFromString(sCurrentLine);
int iLength = sCurrentLine.length() - 2;
//int iIndex = sCurrentLine.indexOf("/");
if ( (sCurrentLine.contains("/")) && (sCurrentLine.indexOf("/") >= iLength) ) {
sCurrentLine = sCurrentLine.substring(0, sCurrentLine.indexOf("/"));
} else if ( (sCurrentLine.contains("/")) && (sCurrentLine.indexOf("/") == 0) ) {
sCurrentLine = "";
}
sCurrentLine = sCurrentLine + " ";
return (sCurrentLine);
}
private List<String> readFileAndLoadData() {
// XXX: disregard note below
// ****************************************
// THIS FUNCTION IS THE ENGINE FOR THIS
// APPLICATION. THIS FUNCTION OPENS
// THE FIRST FILE FROM THE FILE ARRAY
// LIST. THEN IT READS THROUGH THE
// FILE LINE BY LINE BUILDING A VECTOR
// OF SQL SCRIPT COMMANDS. WHEN IT IS
// DONE PROCESSING IT PASSES A NEAT
// VECTOR OF SQL COMMANDS TO THE
// PROGRAM THAT CALLED THIS FUNCTION
// ****************************************
// THIS PARTICULAR METHOD IS LONG AND
// TERRIBLE TO READ BECAUSE IT WAS WRITTEN
// IN ONE SITTING. AND SINCE IT SEEMED
// LIKE THE TYPE OF FUNCTION THAT LOOKS
// SO BAD TO A CODE REVIEWER, BY LEAVING
// IT IN THE CODE IT PROVIDES GOOD
// HUMOR RELIEF FOR THE DEVELOPER GIVING
// A CODE REVIEW TO THIS PROGRAM.
// ****************************************
// ****************************************
// VARIABLE DECLARATIONS
String sFileName = "";
String line = "";
String lineTemp = "";
String sSqlBuffer = "";
//String sFindSemiColon = "";
boolean bKeywordAlreadyFound = false;
boolean bKeywordInString = false;
List<String> vSql = new ArrayList<String>();
BufferedReader in = null;
File fFile = null;
FileInputStream fis = null;
InputStreamReader Fin = null;
//Object obj = null;
int iSizeOfVector = countFileArraySize(sFileNameArray);
// END VARIABLE DECLARATIONS
// ****************************************
// ****************************************
// LOOPING THROUGH THE VECTOR OF FILE NAMES
for ( int iFile = 0; iFile < iSizeOfVector; iFile++ ) {
// GETTING THE FIRST FILE NAME FROM THE VECTOR
sFileName = sFileNameArray[iFile];
try // Try to open the file
{
//File fFile = new File (sDirectory, sFilename);
fFile = new File(sFileName);
fis = new FileInputStream(fFile);
Fin = new InputStreamReader(fis);
in = new BufferedReader(Fin);
//if (isLoggingDebug()) logDebug ("Able to open file: " + sFileName);
} catch ( Exception e ) {
// logError("* Error opening file :" + sFileName + ": *");
return (vSql);
} // Unable to open the file
try // Try to read the file line by line
{
//String sSqlStatement = "";
sSqlBuffer = "";
// Open file and read first line
line = in.readLine();
lineTemp = line;
while ( line != null ) // It is not the end of the file, LOOK FOR KEY WORDS
{
//logger.info ("DEBUG:" + line);
lineTemp = line;
lineTemp = checkForComments(lineTemp);
bKeywordInString = checkForKeywords(lineTemp);
// if this line is starting a stored procedure declaration, parse it
// specially. added this block to correctly parse stored procedures in oracle.
// marty - 7/11/2002
if ( bKeywordInString && isStartOfStoredProcedure(lineTemp) ) {
String command = parseStoredProcedure(lineTemp, in);
vSql.add(command);
line = in.readLine();
continue;
}
// if this line is starting an 'if exists...' statement for MS SQL Server, parse
// if specially. added to work around bug 64622
if ( bKeywordInString && isStartOfIfExistsCommand(lineTemp) ) {
String command = parseIfExistsCommand(lineTemp, in);
vSql.add(command);
line = in.readLine();
continue;
}
if ( bKeywordAlreadyFound ) {
if ( bKeywordInString ) {
sSqlBuffer = checkForSemiColons(sSqlBuffer);
sSqlBuffer = checkForEndLines(sSqlBuffer);
vSql.add(sSqlBuffer);
sSqlBuffer = lineTemp;
} else // (!bKeywordInString}
{
sSqlBuffer = sSqlBuffer + " " + lineTemp;
}
} else {
if ( bKeywordInString ) {
bKeywordAlreadyFound = bKeywordInString;
sSqlBuffer = sSqlBuffer + lineTemp;
}
}
//Get the next line
line = in.readLine();
} // End while
try {
sSqlBuffer = checkForSemiColons(sSqlBuffer);
sSqlBuffer = checkForEndLines(sSqlBuffer);
vSql.add(sSqlBuffer);
} catch ( Exception ex ) {
//logError ("No element to add" + ex);
}
} catch ( Exception e ) {
//logError("Could not read one line in file: " + sFileName + "\n");
return (vSql);
} // Unable to read one of the lines in the file
try // To close the file streams
{
Fin.close();
fis.close();
//fFile.close();
} catch ( Exception ex ) {
//logError ("Unable to close files\n");
return (vSql);
} // If the files could not be closed
}
return (vSql);
}
/**
* this method returns true if this line is the start of a check for whether an object
* exists
* in MS SQL Server. otherwise it returns false.
*/
private boolean isStartOfIfExistsCommand(String pLine) {
// i think this is only on MS SQL Server. hard-coding the logic.
return pLine.trim().toUpperCase().startsWith("IF OBJECT_ID");
}
/**
* used to parse an if-exists method. the parsing logic is different than our usual
* behavior
* so it has been special cased.
*
* @return String the parsed SQL command
* @throws Exception if an error occurs
*/
private String parseIfExistsCommand(String pCommand, BufferedReader pIn)
throws Exception {
// a pretty big hack, but just parse until we find the next "go"
String line = pIn.readLine();
if ( line != null ) {
line = line.trim();
}
while ( line != null && (!line.toUpperCase().startsWith("GO")) ) {
pCommand = pCommand + " " + line;
line = pIn.readLine();
if ( line != null ) {
line = line.trim();
}
}
return pCommand;
}
/**
* this method returns true if this line is the start of a definition for a stored procedure.
* otherwise, it returns false.
*/
private boolean isStartOfStoredProcedure(String pLine) {
// i only know of this on oracle, so i'm temporarily coding it to work specifically
// with oracle.
return pLine.trim().toUpperCase().startsWith("CREATE OR REPLACE PROCEDURE");
}
/**
* this method is used to parse a stored procedure. since stored procedures may contain
* sub-commands within them, we use a special process to parse them.
*
* @return String the parsed SQL command
* @throws Exception if an error occurs
*/
private String parseStoredProcedure(String pCommand, BufferedReader pIn)
throws Exception {
// this is pretty much a total hack, but i just want to get it working for now
// ASSUME we just parse until we find a line that starts with "/"
String line = pIn.readLine();
if ( line != null ) {
line = line.trim();
}
while ( line != null && (!line.startsWith("/")) ) {
pCommand = pCommand + " " + line;
line = pIn.readLine();
if ( line != null ) {
line = line.trim();
}
}
return pCommand;
}
public synchronized Collection<String> parseSQLFiles(String[] pFiles) {
setFileNameArray(pFiles);
// ****************************************
// THIS FUNCTION CALLS THE FUNCTION THAT
// READS THROUGH THE ARRAY OF FILES PASSED
// TO IT AND IT RETURNS A VECTOR OF SQL
// STATEMENTS. MOST OF THESE WILL TEND
// TO BE CREATE AND DROP STATEMENTS.
// ****************************************
List<String> v = new ArrayList<String>();
v = readFileAndLoadData();
for ( String s : v ) {
s = trimDebuggingCharacters(s);
logger.info(s);
}
return v;
}
public Collection<String> parseSQLFile(String pFile) {
String[] files = { pFile };
return parseSQLFiles(files);
}
// This is useful for debugging this application
private boolean logToSystemOut = false;
public static void main(String[] args) {
SQLFileParser t = new SQLFileParser();
t.logToSystemOut = true;
for ( String s : t.parseSQLFiles(args) ) {
logger.info("\n\n" + s);
}
}
}