/**
* Copyright 2007 ATG DUST Project
*
* 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.adapter.gsa;
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.Iterator;
import java.util.List;
import org.apache.log4j.Logger;
/**
* 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 Logger log = Logger.getLogger(SQLFileParser.class);
// ****************************************
// 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(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().indexOf(sKeywordList[i]) > -1)
{
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.indexOf(";") > -1)
{
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.indexOf("/") > -1) && (sCurrentLine.indexOf("/") >= iLength))
{
sCurrentLine = sCurrentLine.substring (0, sCurrentLine.indexOf("/"));
}
else if ((sCurrentLine.indexOf("/") > -1) && (sCurrentLine.indexOf("/") == 0))
{
sCurrentLine = "";
}
sCurrentLine = sCurrentLine + " ";
return (sCurrentLine);
}
private List<String> readFileAndLoadData ()
{
// ****************************************
// 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
{
//log.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
* @exception 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
* @exception 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();
String s = "";
for (int i=0;i<v.size();i++)
{
s = v.get(i).toString();
s = trimDebuggingCharacters (s);
if ( logToSystemOut ) {
//log.info("\n\n" + s );
} else {
// if (isLoggingInfo ()) logInfo(s);
}
}
return v;
}
public Collection<String> parseSQLFile( String pFile )
{
String[] files = { pFile };
return parseSQLFiles( files );
}
// This is useful for debugging this application
public boolean logToSystemOut = false;
public static void main (String[] args)
{
SQLFileParser t = new SQLFileParser();
t.logToSystemOut = true;
Iterator<String> cmds = t.parseSQLFiles( args ).iterator();
while ( cmds.hasNext() ) {
log.info("\n\n" + cmds.next() );
}
}
}