package com.jamonapi.proxy;


import java.util.*;
import com.jamonapi.utils.*;
/** SQLDeArger takes a sql statement and 1) replaces argument values ('souza', "souza", 'souza''s', 100, 100.5, 0xff, 10e9) with question marks
 *  It makes full sql statements look more like a prepared statement.  2) Returns a sql type which is simply the first word of the command 
 *  (typically 'select', 'update' etc. 3) Returns any specified keywords that are in the parsed sql.  This is a good way to return table names.
 *  
 *  A normal sql statement with argument values would generate too much data for JAMon and wouldn't be very good for understanding how your query
 *  performed.  Coneceptually the following queries are the same: 1) select * from table where name='steve', 2) select * from table where name='mindy'. 
 *  However, if you passed both strings to jamon the 'sameness' wouldn't show up in the stats as each is a different string.  However by putting 
 *  question marks in place of the values this problem can be resolved (i.e. select * from table where name=?).  One issue with the way this is done 
 *  at this point is numbers or strings in other places can be replaced too. This shouldn't affect monitoring however.  For example
 *  This "select abs(200) from table", would be parsed to "select abs(?) from table".
 *  
 *   However, numbers of the format 100.00, really are multiple tokens. And will appear in the returned 
 *   strings as ?.?.   

 *  
 *  The class name SQLDeArger refers to the fact that argument values are removed from SQL statements.
 *  
 *  This class is also useful for logging sql statements.
 * @author steve souza
 *
 */

public class SQLDeArger {

	            private int parseSize;// size of the sql statement to parse
	            private StringBuffer parsedSQL=new StringBuffer();// where the resulting sql statement will reside
	            private char[] sqlChars;// characters in sql string
	            private String sqlToParse;// original sql
	            private int delimCounter=0;//character counter
	            private int currentCharNum=0;// current character in sql that is being proceesed
	            private boolean isInString=false;// indicates whether or not the parser is currently in a string i.e. 'steve'
	            private char delim; // string delimeter which will be either ' or "
	            private int totalDelims;// a counter of how many times delimeter appears in a string.
	            private boolean firstToken=true;// the first token processed.  usually will be select, update, insert etc.
	            private String sqlType;// the first token such as select, update, delete, insert
	            private List matchStrings;// a list of strings to match in the sql statement.  table names are a good example
	            private String[] matches;// the list of returned matches.
	            
	            private static Map sqlTypes=AppMap.createInstance();// case insensitive map.
	            static {
	            	// These values are just checked for existance so the value can be null.  These indicate the type of sql statement.
	            	sqlTypes.put("select","select");
	            	sqlTypes.put("update","update");
	            	sqlTypes.put("delete","delete");
	            	sqlTypes.put("insert","insert");
	            	sqlTypes.put("truncate","truncate");
	 
	            	sqlTypes.put("exec","exec");
	            	sqlTypes.put("create","create");
	            	sqlTypes.put("drop","drop");
	            	sqlTypes.put("alter","alter");

	            	sqlTypes.put("commit","commit");
	            	sqlTypes.put("rollback","rollback");
	            	sqlTypes.put("save","save");
	            	
	            	sqlTypes.put("grant","grant");
	            	sqlTypes.put("revoke","revoke");

	            	
	            }
	                        
	            /** Accepts string to parse */
	            public SQLDeArger(String sql) {
	            	this(sql,null);

	            }
	            
                /** Accepts strings to parse and a List of strings to check to see if they are in the sql statement.  A good use for this is to
                 * pass table names into the constructor.  After the constructor is called the sql will already have been parsed
                 * @param sql
                 * @param matchStrings
                 */
	            public SQLDeArger(String sql, List matchStrings) {
	            	this.sqlToParse=sql.trim()+" ";// ensures that the last character is always a space and not part of the query.
	             	parseSize=sqlToParse.length()-1; // exclude space which lets me not have to worry about next char.
	 	           	sqlChars=sqlToParse.toCharArray();// characters that will be parsed
	            	setMatchStrings(matchStrings);
	 	           	parse();
	            }
	            
	            /** Parse the passed in where clause and break it along token lines.   */
		        private SQLDeArger parse() {
		            	
                   // tokens are strings broken on word boundaries like spaces
 	               while (hasTokens()) {
		               String token=getNextToken();
		               if (firstToken) {
		              	 setSQLType(token); 
		               	 firstToken=false;
		               }
		           
                       // if the token is a number such as 100, 100.5 0xff, 10E9 or a quoted string such as 'steve'
                       // the replace it with a '?'
		               token=(isFloatString(token) || isQuotedString(token)) ? "?" : token;
		               parsedSQL.append(token);
		            }
		            
		           	parseMatches();
		           	
		           	return this;
		        }
		  
               /** Return sql with original argument values replaced with '?'.  For example:  select * from table where name=? */
		       public String getParsedSQL() {
		           	return parsedSQL.toString();
		       }
		       
		       /** 
		        * Get sql that was passed in to parse.
		        */
		       public String getSQLToParse() {
		    	   return sqlToParse;
		       }
		       
               /** Return the first word from the sql command.  These would include:  select, update, delete, create, insert, commit,...
                * If the word is not recognized then 'other' is returned.
                */
		       public String getSQLType() {
		    	   return sqlType;
		       }
		       
		       
		       /** Returns an array of Strings that matched the Strings specified in 
		        *  the matches arraylist.   Note that the matches are performed after arg
		        *  values have been replaced on the sql with '?'.  
		        */
		       public String[] getMatches() {
		    	   return matches;
		       }
		       
		       /** Returns true if there were any matches against the match Strings */
		       public boolean hasMatches() {
		    	   return (matches==null || matches.length==0) ? false : true;
		       }
		       
               /** Returns the number of matches or 0 if there were none */
		       public int getNumMatches() {
		    	   return (hasMatches()) ? matches.length : 0;
		       }
		       
               /** One for the statement, one for the keyword type of the statment, and the other numbers are for the matches */
		       int getNumAll() {
		    	   return 3+getNumMatches();
		       }
		       
		       /** Return an array that has 1) all sql, 2) the sql type, 3) the parsed sql, 4) any matched strings if they exist.  The array will be at least
		        *  2 long.   This is useful to pass all the strings in the array to jamon to track stats associated with the query.
		        */ 
		       String[][] getAll() {
		    	   
	    	     int size=getNumAll(); // 2 represents the sql and sqltype values, so that is always there.
	    	     String[][] allData=new String[size][];
			    	   
    		       // start at position 2 assigning any matches to the array.
	    		   for (int i=0, j=0;i<size;i++) {
	    			   allData[i]=new String[3];// ms., and value
	    			   allData[i][1]=getSQLToParse();
	    			   allData[i][2]="ms.";
                       // note this loop matches one in SQLDeArgMon constructor and positions are important
                       // The constructor must be changed if this method changes - kind of ugly...
                       if (i==0)  // All
                         allData[i][0]="All";                       
                       else if (i==1) // SQL Type
		    			 allData[i][0]=getSQLType();
		    		   else if (i==2) // parsed SQL
		    		     allData[i][0]=getParsedSQL();
		    		   else // Matches in sql such as table names.
			    		 allData[i][0]=matches[j++];
		    	     }

		    			  
		    	   return allData; 
		       }
		       
               /** Add string to see if it matches in the query */
		       public void addMatchString(String matchString) {
		    	   matchStrings.add(matchString);
		    	   
		       }
		       
               /** Determine if the matches strings are in the parsed sql */
		       private void parseMatches() {
		    	   
		    	   if (matchStrings!=null) {
		    	     String sql=getParsedSQL();
			         List matchesList=new ArrayList();
		    	   
		    	     Iterator iter=matchStrings.iterator();
		    	     while (iter.hasNext()) {
		    		   Object matchObj = iter.next(); // passed in matches such as table names
		    		   String matchStr = (matchObj==null) ? null : matchObj.toString();
		    		   if (sql.indexOf(matchStr)>0)// i.e. a match found
		    			   matchesList.add(matchStr);

	    		   
		    	     }
		    	     
		    	     matches=(String[]) matchesList.toArray(new String[0]);// convert matches into an array
		    	   }
		    		 
		       }

		       
		       
		       /** Note matchStrings should contain Strings.  If it doesn't toString() will be called on the objects */
		       void setMatchStrings(List matchStrings) {
		    	   this.matchStrings=matchStrings;
		       }
		       
		       
		       
		       
		       /** SQL types are the first word that is in a sql statement.  Examples are
		        * insert, delete, update, and select.  However, any word that you add by calling this
		        * method will be detected as a sql type.  Note the JDBCMonProxy uses this info
		        * to add a monitor for whenever a select, insert etc are executed.  This gives the number
		        * of times and performances of the sql types.  A list of all the default 
		        * sql types follows: select, update, delete, insert, truncate, exec, create, drop, alter
		        * commit, rollback, grant, revoke, save.  Any value that isn't on the list will return
		        * 'other'.	The getSQLType method returns the SQL type value in the sql statement passed to the 
		        * constructor.
		        * 	        * 
		        * @param type
		        */
		       public static void putSQLType(String type) {
		    	   sqlTypes.put(type,type);
		       }
		       
		       private void setSQLType(String type) {
		    	  sqlType = (String)sqlTypes.get(type);
		    	  if (sqlType==null)
		    		sqlType="other";
		    		
		       }

               // returns true as long as there are characters to process
	            private boolean hasTokens() {
	            	return (currentCharNum<parseSize) ? true : false;
	            }
	            
                // Returns String tokens such as: select, from, where, table, etc. 
	            private String getNextToken() {
	            
	              int start=currentCharNum;
	              int end=0;
	            
                  // loop until word boundary
	              while (end==0 && currentCharNum<parseSize) {
	            	 setStringDelim();	  
	            	 
                     // an example of a word boundary would be next char of space while not in a string, or a puctuation mark
	            	 if (isWordBoundary(getCurrentChar(), getNextChar()) ) 
	            	    end=currentCharNum+1;

	            	 currentCharNum++;
	              }  
                  
	              // return the word as a token if one was found
	              if (end>0) 
	             		return sqlToParse.substring(start,end);
	              else 
	                return null;
         	  

	            }
                
	           // determine if the character is punctuation or not.
	           private boolean isPunctuation(char ch) {
	        	   return (!isInString() && !Character.isLetterOrDigit(ch));
	           }
	            
	  	       private boolean isWordBoundary(char currentCh, char nextCh) {
		    	  // word boundaries are special puncutation, when not in a string.
		    	  // select * from table where key=100
                  // first part of conditional would be triggered with convert(200. if currentchar was '('
                  // 2nd part:  select *, convert(char(20)... from.  if characther is 't' of select or 't' 
                  // of 'convert'
	  	    	  return (isPunctuation(currentCh) && Character.isLetterOrDigit(nextCh)) ||
	  	    	     (!isInString() && (Character.isWhitespace(nextCh) || !Character.isLetterOrDigit(nextCh)));
		      }
	            
	            // Indicates if the character is embedded in a where clause string surrounded by ' or ".
	            // i.e. this would return true for any characters in between double quotes "steve's"
	            private boolean isInString() {
	                return isInString;
	            }
	            
	            private void setIsInString(boolean isInString) {
	                this.isInString=isInString;
	            }
	            
                // can either be ' or "
	            private void setStringDelim(char delim) {
	                this.delim=delim;
	            }
	            
	            private char getStringDelim() {
	                return delim;
	            }
	            
	
	            // Get the current character of processing
	            private char getCurrentChar() {
	                return sqlChars[currentCharNum];
	            }
	            
                // get next charater of processing.
	            private char getNextChar() {
	                return sqlChars[currentCharNum+1];
	            }
	            

	            

	            // Determine if the delimeter is " or '
	            private void setStringDelim() {
	                // if first delimiter in a string like 'steve' then prepare for a string to be
	                // processed
	            
	            	// if in the string count delimeters to know when to end.
	            	boolean isDelim=(isInString() && getCurrentChar()==getStringDelim());
	            	if (isDelim)
	            	  totalDelims++;
	            	
	                if (!isInString() && (getCurrentChar()=='\'' || getCurrentChar()=='"')) {
	                	delimCounter++;
	                	totalDelims++;
	                } // else it is the last ' or " in 'steve' , 'mindy''s'''
	                else if (isInString() && getCurrentChar()==getStringDelim() && getNextChar()!=getStringDelim() && totalDelims%2==0) {
	                	delimCounter--;
	                }            

	                
	                if (!isInString() && delimCounter==1) {
	                	setStringDelim(getCurrentChar());
	                    setIsInString(true);	
	                } else if (isInString() && delimCounter==0)
	                    setIsInString(false);	
	                	

	            }
	            
      
	   // Any token that starts with a 0 returns true.  i.e. 1000 0xFF, 10E9, would return true.
       private  boolean isFloatString(String str){
          // if null string or the first character is not a digit then this is not a number
          if (str==null || !Character.isDigit(str.charAt(0)))
            return false;
          else // else it is a number
            return true;
     }
     
     

    
     private  boolean isQuotedString(String str) {
         if (str==null || "".equals(str.trim()))
           return false;
         
         char first=str.charAt(0);
         char last=str.charAt(str.length()-1);
         
         // either 'souza' or "souza" is a good quoted string.
         if ((first=='\'' || first=='"') && first==last)
           return true;
         else 
           return false;
     }
     

     private static void printDebugInfo(String sql) {
    	 List matches=new ArrayList();
    	 matches.add("employees");
    	 matches.add("dependents");
       	 matches.add("orders");
       	 SQLDeArger s=new SQLDeArger(sql, matches);
         System.out.println("\nSQL="+sql);
         System.out.println("ParsedSQL="+s.getParsedSQL());
         System.out.println("SQLType="+s.getSQLType());
         if (s.hasMatches()) {
           System.out.println("String Matches=");
           String[][] m=s.getAll();
           for (int i=0;i<m.length;i++) {
        	System.out.println("all array["+i+"]="+m[i][0]);
        	System.out.println("all array original ["+i+"]="+m[i][1]);
 
           }
         }
      }
	       
	   /** Method that has test code for this class.  Click 'View Code' above to view the code */
	   public static void main(String[] args) {
	          printDebugInfo("select col0, 'mindy', 'mindy''s', 'mindy''s''''', col2, convert('steve',100, 0xff, 100.0),* from table where salary>=100000 or salary<=200000 or name like 'steve%' and name in ('mindy','steve') and short= 20 or sand='no sand' or short=30 and sand='no sand' and sand='no sand' or salary in (select max(salary/2),100 from emps group by name having count(*)>5)"); 
	          printDebugInfo("select ?,*,? from table where name like ?"); 
	          printDebugInfo("select 'mindy', \"mindy\" from table where name like 'mindy'"); 
	          printDebugInfo("     delete from table where  key name like          'mindy%'   "); 
	          printDebugInfo("myproc"); 
	          printDebugInfo("exec myproc");     
	          printDebugInfo("select * from employees as e, customers as c, dependents as d where e.id=c.id and c.id=d.id and e.name in (select * from favorite where name like 'j%' and salary > 50000)"); 
              printDebugInfo("select * from employees as e, customers as c, dependents as d where e.id=c.id and c.id=d.id and e.name not in ('steve','souza','jeff','beck') and salary in (100000,20000, 50000) and age!=50"); 
              printDebugInfo("select 10_name_10, name_10_name,* from employees where age<50 and age!=10.0 and age!=-50.0 and age < 0xFF and age < 0x0123456789aAbBcCdDeEfF and age<+10E09");
              // valid java variables can begin with a letter, $, or _ 
              printDebugInfo("select 10/22/06, date102206, m10/d22/y06, 10name, $10_name, _10_name, 10_name_10, name_10_na1010me,* from employees where age<50 and age!=10.0 and age!=-50.0 and age < 0xFF and age < 0x0123456789aAbBcCdDeEfF and age<+10E09 and age<1.72E3F"); 
              printDebugInfo("pageHits 'ssouza' jamon mb 100.5:pageHits jamon:pagehits.ssouza"); 
	      }   
	      
	      
	      
	      

}