//import com.alibaba.fastjson.JSON;
//import com.alibaba.fastjson.JSONArray;
//import com.alibaba.fastjson.JSONObject;
//import com.alibaba.fastjson.JSON;
//import com.alibaba.fastjson.JSONArray;
//import com.alibaba.fastjson.JSONObject;
//import demos.traceColumn.TTraceColumn;

//import test.SqlFileList;
import java.io.*;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.compiler.TAnnotatedTree;
import gudusoft.gsqlparser.compiler.TTypeAndScopeScanner;
import gudusoft.gsqlparser.compiler.util.TSimpleEvaluationContext;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.sqlenv.TSQLCatalog;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.sqlenv.TSQLSchema;
import gudusoft.gsqlparser.sqlenv.TSQLTable;
import gudusoft.gsqlparser.stmt.*;
import gudusoft.gsqlparser.stmt.mssql.TMssqlBlock;
import gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure;
import gudusoft.gsqlparser.stmt.oracle.TBasicStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateTrigger;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateType;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateTypeBody;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateType_Placeholder;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlDummyStmt;
import gudusoft.gsqlparser.stmt.TExecImmeStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlForallStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlGotoStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlNullStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlPipeRowStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlPragmaDeclStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlProcedureSpecStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlRecordTypeDefStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlSqlStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlSubProgram;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlTableTypeDefStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlVarrayTypeDefStmt;
import gudusoft.gsqlparser.stmt.oracle.TSqlplusCmdStatement;
import gudusoft.gsqlparser.stmt.teradata.TTeradataNotImplement;
import gudusoft.gsqlparser.util.VariableSubstitutor;



class SqlFileList {

        public final List<String> sqlfiles = new ArrayList<String>();
        private String suffix = ".sql";
        private boolean listAllFiles = false;

        public SqlFileList(String dir, boolean includeSubDir) {
                File parent = new File(dir);
                if (parent.exists() && parent.isDirectory())
                        traverseSqlFiles(parent, includeSubDir);
        }

        public SqlFileList(String dir, boolean includeSubDir, boolean listAllFiles) {
                this.listAllFiles = listAllFiles;
                File parent = new File(dir);
                if (parent.exists() && parent.isDirectory())
                        traverseSqlFiles(parent, includeSubDir);
        }

        public SqlFileList(String dir) {
                this(dir, false);
        }

        private void traverseSqlFiles(File parent, boolean includeSubDir) {
                File[] files = parent.listFiles(new FileFilter() {

                        public boolean accept(File file) {
                                if (listAllFiles){
                                        sqlfiles.add(file.getAbsolutePath());
                                }else if (file.getName().toLowerCase().endsWith(suffix)) {
                                        sqlfiles.add(file.getAbsolutePath());
                                }

                                if (file.isDirectory())
                                        return true;

                                return false;
                        }
                });

                if (includeSubDir) {
                        if (files != null && files.length > 0) {
                                for (File file : files) {
                                        traverseSqlFiles(file, includeSubDir);
                                }
                        }
                }
        }

}

class getObject {
        public final static int compareMode = 1;
        public final static int showMode = 2;

        String sqlfile;
        String[] desiredTables = new String[100];
        String[] desiredColumns = new String[100];
        int desiredTableCount = 0, desiredColumnCount = 0;

        String oracle_sqldir = "D:/prg/gsqlparser/Test/TestCases/java/oracle/dbobject/";
        String[] oracle_sqlfiles = { "berger_example_01", "berger_sqltest_01", "berger_sqltest_02", "berger_sqltest_03",
                        "berger_sqltest_04", "berger_sqltest_05", "createtrigger", "plsql_block_correlated_subquery",
                        "createfunction", "createpackagebody" };

        String sqlserver_sqldir = "D:/prg/gsqlparser/Test/TestCases/fetchdbobject/mssql/java/";
        String[] sqlserver_sqlfiles = { "bigjoin1", "shurleyjoin", "delete1", "delete2", "delete3", "delete4", "update1",
                        "update2", "update3", "insert1", "createfunction1", "createprocedure1", "createtrigger1", "createview1",
                        "while1"

        };

        String sqldir;
        String[] sqlfiles;
        int files_count = 0;

        public String showModeFile;

        String[] foundTables = new String[1000];
        String[] foundColumns = new String[1000];
        int foundTableCount = 0;
        int foundColumnsCount = 0;

        EDbVendor dbvendor;

        getObject(EDbVendor db) {
                this.dbvendor = db;
                if (db == EDbVendor.dbvoracle) {
                        sqldir = oracle_sqldir;
                        sqlfiles = oracle_sqlfiles;
                        files_count = sqlfiles.length;
                } else {
                        sqldir = sqlserver_sqldir;
                        sqlfiles = sqlserver_sqlfiles;
                        files_count = sqlfiles.length;
                }
        }

        void getDesiredTablesColumns(String sqlfile, String[] pTables, String[] pColumns) {
                String line;
                desiredTableCount = 0;
                desiredColumnCount = 0;
                boolean isTable = false, isColumn = false;
                try {
                        BufferedReader br = new BufferedReader(new FileReader(sqlfile));

                        try {
                                while ((line = br.readLine()) != null) {

                                        if (line.toLowerCase().indexOf("tables:") >= 0) {
                                                isTable = true;
                                                isColumn = false;
                                                continue;
                                        }

                                        if (line.toLowerCase().indexOf("fields:") >= 0) {
                                                isTable = false;
                                                isColumn = true;
                                                continue;
                                        }

                                        if (line.toLowerCase().indexOf("functions:") >= 0) {
                                                isTable = false;
                                                isColumn = false;
                                                continue;
                                        }

                                        if (line.toLowerCase().indexOf("schema:") >= 0) {
                                                isTable = false;
                                                isColumn = false;
                                                continue;
                                        }

                                        if (isTable) {
                                                pTables[desiredTableCount] = line.trim();
                                                desiredTableCount++;
                                        }

                                        if (isColumn) {
                                                pColumns[desiredColumnCount] = line.trim();
                                                desiredColumnCount++;
                                        }
                                }
                                br.close();
                        } catch (IOException e) {
                                System.out.println(e.toString());
                        }

                } catch (FileNotFoundException e) {
                        System.out.println(e.toString());
                }

        }

        void run(int pmode) {
                // 1: compare, compare found table/column with desired results in a file
                // 2: show result, don't compare

                TGSqlParser sqlparser = new TGSqlParser(this.dbvendor);
                if (pmode == showMode) {
                        files_count = 1;
                }
                for (int k = 0; k < files_count; k++) {
                        foundColumnsCount = 0;
                        foundTableCount = 0;
                        if (pmode == showMode) {
                                sqlparser.sqlfilename = showModeFile;
                        } else {
                                sqlparser.sqlfilename = sqldir + sqlfiles[k] + ".sql";
                        }

                        int ret = sqlparser.parse();
                        if (ret == 0) {
                                if (pmode == compareMode) {
                                        getDesiredTablesColumns(sqldir + sqlfiles[k] + ".out", desiredTables, desiredColumns);
                                }

                                TCustomSqlStatement stmt = null;
                                for (int i = 0; i < sqlparser.sqlstatements.size(); i++) {
                                        analyzeStmt(sqlparser.sqlstatements.get(i));
                                }

                                String[] foundTables2 = new String[foundTableCount];
                                for (int k1 = 0; k1 < foundTableCount; k1++) {
                                        foundTables[k1] = foundTables[k1].toLowerCase();
                                }
                                System.arraycopy(foundTables, 0, foundTables2, 0, foundTableCount);
                                Set set = new HashSet(Arrays.asList(foundTables2));
                                Object[] foundTables3 = set.toArray();
                                Arrays.sort(foundTables3);

                                String[] foundColumns2 = new String[foundColumnsCount];
                                for (int k1 = 0; k1 < foundColumnsCount; k1++) {
                                        foundColumns[k1] = foundColumns[k1].toLowerCase();
                                }
                                System.arraycopy(foundColumns, 0, foundColumns2, 0, foundColumnsCount);
                                Set set2 = new HashSet(Arrays.asList(foundColumns2));
                                Object[] foundColumns3 = set2.toArray();
                                Arrays.sort(foundColumns3);

                                if (pmode == compareMode) {
                                        System.out.println("process: " + sqlfiles[k] + ", tables desired: " + desiredTableCount
                                                        + ", found: " + foundTables3.length + ", columns desired: " + desiredColumnCount
                                                        + ", found columns: " + foundColumns3.length);
                                }

                                if (pmode == showMode) {
                                        System.out.println("Tables:");
                                        for (int j = 0; j < foundTables3.length; j++) {
                                                System.out.println(foundTables3[j]);
                                        }

                                        System.out.println("Fields:");
                                        for (int j = 0; j < foundColumns3.length; j++) {
                                                System.out.println(foundColumns3[j]);
                                        }

                                        return;
                                }

                                boolean isfound = false;
                                for (int i = 0; i < desiredColumnCount; i++) {
                                        isfound = false;
                                        for (int j = 0; j < foundColumns3.length; j++) {
                                                if (desiredColumns[i].toString().equalsIgnoreCase(foundColumns3[j].toString())) {
                                                        isfound = true;
                                                        break;
                                                }
                                        }

                                        if (!isfound) {
                                                System.out.println("desired column not found:" + desiredColumns[i]);
                                        }
                                }

                                for (int i = 0; i < foundColumns3.length; i++) {
                                        isfound = false;
                                        for (int j = 0; j < desiredColumnCount; j++) {
                                                if (foundColumns3[i].toString().equalsIgnoreCase(desiredColumns[j].toString())) {
                                                        isfound = true;
                                                        break;
                                                }
                                        }

                                        if (!isfound) {
                                                System.out.println("not in desired column:" + foundColumns3[i]);
                                        }
                                }

                        } else {
                                System.out.println(sqlparser.getErrormessage());
                        }
                }
        }

        protected void analyzeStmt(TCustomSqlStatement stmt) {
                for (int i = 0; i < stmt.tables.size(); i++) {
                        if (stmt.tables.getTable(i).isBaseTable()) {
                                if ((stmt.dbvendor == EDbVendor.dbvmssql)
                                                && ((stmt.tables.getTable(i).getFullName().equalsIgnoreCase("deleted"))
                                                                || (stmt.tables.getTable(i).getFullName().equalsIgnoreCase("inserted")))) {
                                        continue;
                                }

                                foundTables[foundTableCount] = stmt.tables.getTable(i).getFullName();
                                foundTableCount++;
                                for (int j = 0; j < stmt.tables.getTable(i).getObjectNameReferences().size(); j++) {
                                        foundColumns[foundColumnsCount] = stmt.tables.getTable(i).getFullName() + "."
                                                        + stmt.tables.getTable(i).getObjectNameReferences().getObjectName(j).getColumnNameOnly();
                                        foundColumnsCount++;
                                }
                        }
                        // System.out.println(stmt.tables.getTable(i).getFullName());
                }

                for (int i = 0; i < stmt.getStatements().size(); i++) {
                        analyzeStmt(stmt.getStatements().get(i));
                }

        }

}

class tableEffected {
        public final static int select = 0;
        public final static int createtable = 1;
        public final static int delete = 2;
        public final static int insert = 3;
        public final static int update = 4;
        public final static String[] stmtstr = { "s", "c", "d", "i", "u" };

        TObjectName tablename;
        int[] statetments;

        public tableEffected() {
                statetments = new int[5];
        }

}

class ProcessFile {
        String sqlfilename;
        FileInputStream streamFromSqlFile = null;

        public ProcessFile(String filename) {
                sqlfilename = filename;
        }

        public void openIt() {
                try {

                        streamFromSqlFile = new FileInputStream(sqlfilename);
                        InputStreamReader isr = new InputStreamReader(streamFromSqlFile, Charset.defaultCharset().name());

                        BufferedReader finputstream = new BufferedReader(isr);

                        String thisLine;
                        while ((thisLine = finputstream.readLine()) != null) {
                                System.out.println(thisLine);
                        }

                } catch (Exception e) {
                        System.out.println(e.toString());
                } finally {
                }

                System.out.println("Process: " + sqlfilename);
        }

        public void closeIt() {
                try {
                        streamFromSqlFile.close();
                } catch (IOException e) {
                        e.printStackTrace();
                }
        }

}

class crud {
        public final static int max_tables = 100;
        tableEffected[] tables;
        int total_tables;
        EDbVendor dbvendor;
        String sqlfile;

        public crud(EDbVendor db, String sqlfile) {
                this.dbvendor = db;
                this.sqlfile = sqlfile;
                tables = new tableEffected[max_tables];
                total_tables = 0;
        }

        public int run() {
                TGSqlParser sqlparser = new TGSqlParser(this.dbvendor);
                sqlparser.sqlfilename = sqlfile;
                int ret = sqlparser.parse();
                if (ret != 0) {
                        System.out.println(sqlparser.getErrormessage());
                        return ret;
                }

                for (int i = 0; i < sqlparser.sqlstatements.size(); i++) {
                        analyzeStmt(sqlparser.sqlstatements.get(i));
                }

                showResult();
                return ret;
        }

        void showResult() {
                String str = "";

                for (int i = 0; i < total_tables; i++) {
                        for (int j = 0; j < 5; j++) {
                                str = str + "\t\t\t\t" + tables[i].statetments[j] + "(" + tableEffected.stmtstr[j] + ")";
                        }
                        System.out.println(tables[i].tablename.toString() + str);
                        str = "";
                }

        }

        protected void analyzeStmt(TCustomSqlStatement stmt) {
                for (int i = 0; i < stmt.tables.size(); i++) {
                        if (stmt.tables.getTable(i).isBaseTable()) {
                                if ((stmt.dbvendor == EDbVendor.dbvmssql)
                                                && ((stmt.tables.getTable(i).getFullName().equalsIgnoreCase("deleted"))
                                                                || (stmt.tables.getTable(i).getFullName().equalsIgnoreCase("inserted")))) {
                                        continue;
                                }

                                switch (stmt.sqlstatementtype) {
                                case sstselect:
                                        addTable(stmt.tables.getTable(i).getTableName(), tableEffected.select);
                                        break;
                                case sstcreatetable:
                                        addTable(stmt.tables.getTable(i).getTableName(), tableEffected.createtable);
                                        break;
                                case sstdelete:
                                        if (i == 0) {
                                                addTable(stmt.tables.getTable(i).getTableName(), tableEffected.delete);
                                        } else {
                                                addTable(stmt.tables.getTable(i).getTableName(), tableEffected.select);
                                        }
                                        break;
                                case sstinsert:
                                        addTable(stmt.tables.getTable(i).getTableName(), tableEffected.insert);
                                        break;
                                case sstupdate:
                                        if (i == 0) {
                                                addTable(stmt.tables.getTable(i).getTableName(), tableEffected.update);
                                        } else {
                                                addTable(stmt.tables.getTable(i).getTableName(), tableEffected.select);
                                        }
                                        break;
                                default:
                                        System.out.println(stmt.sqlstatementtype.toString()
                                                        + " was not supported in current version, this version able to get table information from select/create table/delete/insert/update");
                                        break;
                                }

                        }
                        // System.out.println(stmt.tables.getTable(i).getFullName());
                }

                for (int i = 0; i < stmt.getStatements().size(); i++) {
                        analyzeStmt(stmt.getStatements().get(i));
                }
        }

        void addTable(TObjectName tablename, int kind) {
                // check is this table already in tables
                boolean isFound = false;

                for (int i = 0; i < total_tables; i++) {
                        if (tables[i].tablename.toString().compareToIgnoreCase(tablename.toString()) == 0) {
                                tables[i].statetments[kind]++;
                                isFound = true;
                                break;
                        }
                }

                if (!isFound) {
                        tables[total_tables] = new tableEffected();
                        tables[total_tables].tablename = tablename;
                        tables[total_tables].statetments[kind]++;
                        total_tables++;
                }

        }
}

class TtextVisitor extends TParseTreeVisitor {
        void show(TCustomSqlStatement stmt) {
                System.out.println("sql type: " + stmt.sqlstatementtype);
                // System.out.println("sql text: "+stmt.toString());
        }

        void showNode(TParseTreeNode node) {
                System.out.println("node type: " + node.getNodeType());
        }

        public void preVisit(TSelectSqlStatement stmt) {
                show(stmt);
        }

        public void postVisit(TStatementList node) {
                System.out.println("stmt list: " + node.size());
        }

        public void postVisit(TCommonBlock node) {
                show(node);
        }

        public void postVisit(TExceptionClause node) {
                showNode(node);
        }

        public void postVisit(TExceptionHandler node) {
                showNode(node);
        }

        public void postVisit(TCreateSequenceStmt node) {
                show(node);
        }

        public void postVisit(TAssignStmt node) {
                show(node);
        }

        public void postVisit(TBasicStmt node) {
                show(node);
        }

        public void postVisit(TCaseStmt node) {
                show(node);
        }

        public void postVisit(TCloseStmt node) {
                show(node);
        }

        public void postVisit(TPlsqlCreateFunction node) {
                show(node);
        }

        public void postVisit(TPlsqlCreatePackage node) {
                show(node);
        }

        public void postVisit(TPlsqlCreateProcedure node) {
                show(node);
        }

        public void postVisit(TPlsqlCreateTrigger node) {
                show(node);
        }

        public void postVisit(TPlsqlCreateType node) {
                show(node);
        }

        public void postVisit(TPlsqlCreateType_Placeholder node) {
                show(node);
        }

        public void postVisit(TPlsqlCreateTypeBody node) {
                show(node);
        }

        public void visit(TCursorDeclStmt node) {
                show(node);
        }

        public void visit(TPlsqlDummyStmt node) {
                show(node);
        }

        public void visit(TElsifStmt node) {
                show(node);
        }

        public void visit(TExecImmeStmt node) {
                show(node);
        }

        public void visit(TExitStmt node) {
                show(node);
        }

        public void visit(TFetchStmt node) {
                show(node);
        }

        public void visit(TPlsqlForallStmt node) {
                show(node);
        }

        public void visit(TPlsqlGotoStmt node) {
                show(node);
        }

        public void visit(TIfStmt node) {
                show(node);
        }

        public void visit(TLoopStmt node) {
                show(node);
        }

        public void visit(TPlsqlNullStmt node) {
                show(node);
        }

        public void visit(TOpenforStmt node) {
                show(node);
        }

        public void visit(TOpenStmt node) {
                show(node);
        }

        public void visit(TPlsqlPipeRowStmt node) {
                show(node);
        }

        public void visit(TPlsqlPragmaDeclStmt node) {
                show(node);
        }

        public void visit(TPlsqlProcedureSpecStmt node) {
                show(node);
        }

        public void visit(TRaiseStmt node) {
                show(node);
        }

        public void visit(TPlsqlRecordTypeDefStmt node) {
                show(node);
        }

        public void visit(TReturnStmt node) {
                show(node);
        }

        public void visit(TPlsqlSqlStmt node) {
                show(node);
        }

        public void visit(TPlsqlSubProgram node) {
                show(node);
        }

        public void visit(TPlsqlTableTypeDefStmt node) {
                show(node);
        }

        public void visit(TVarDeclStmt node) {
                show(node);
        }

        public void visit(TPlsqlVarrayTypeDefStmt node) {
                show(node);
        }

        public void visit(TSqlplusCmdStatement node) {
                show(node);
        }

}


public class demo {

        // public static YYTrec yyt[] = {new YYTrec("1".toCharArray(),1)};

        static String getQueryFromFile(String filename) {

                File file = new File(filename);
                StringBuffer contents = new StringBuffer();
                BufferedReader reader = null;

                try {
                        reader = new BufferedReader(new FileReader(file));
                        String text = null;

                        // repeat until all lines is read
                        while ((text = reader.readLine()) != null) {
                                contents.append(text).append(System.getProperty("line.separator"));
                        }
                } catch (FileNotFoundException e) {
                        e.printStackTrace();
                } catch (IOException e) {
                        e.printStackTrace();
                } finally {
                        try {
                                if (reader != null) {
                                        reader.close();
                                }
                        } catch (IOException e) {
                                e.printStackTrace();
                        }
                }

                // show file contents here
                return contents.toString();

        }

        public static void writeToFile(String outContent, String outfile,String charset) throws IOException {
                Writer out = new BufferedWriter(new OutputStreamWriter(
                                new FileOutputStream(outfile), charset));
                try {
                        out.write(outContent);
                } finally {
                        out.close();
                }
        }
        public static void readfile1(String filename,String charset) {
                FileInputStream streamFromSqlFile = null;
                InputStreamReader sqlStreamReader = null;
                BufferedReader finputstream = null;
                StringBuilder s = new StringBuilder(80);
                int c;

                //String charset = Charset.defaultCharset().name();//"UTF-8";
                //System.out.println(charset);
       // String charset = "gb2312";//"gb2312" ""UTF-8";

                //System.out.println(Charset.defaultCharset().name());
                try {
                        streamFromSqlFile =   new FileInputStream(filename);
                        sqlStreamReader = new InputStreamReader(streamFromSqlFile,charset);
                        finputstream =  new BufferedReader(sqlStreamReader);

                        while ( (c = finputstream.read()) != -1){
                                //System.out.println(String.format("0x%08X", c)+", "+(char)c);
                                if (c == 0xA0){
                                //      System.out.println("Find "+String.format("0x%08X", c));
                                }
                                s.append((char)c);
                                //parseIntToHex(c);
                                //String s1 = new String( intToByteArray(c) , Charset.forName(charset));
                                System.out.println("\\u"+Integer.toHexString(c).toUpperCase()+", integer="+c);

                                char[] unicodeChar = Character.toChars(c);
                                if (c > 255){
                                        System.out.println(new String(unicodeChar));
                                }

                                //System.out.println(s1);
                                //s.append(s1);

                        }
                        //System.out.println( TBaseType.toHex(s.toString(),charset));
                        System.out.println( "\n"+s.toString());


                        writeToFile(s.toString(),"c:\\prg\\tmp\\demo.out.txt", charset);

                }catch(FileNotFoundException e){

                } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                } catch (IOException e) {
                        e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
        }

        public static void readbigfile(String filename) {
                try {

                        FileInputStream fstream = new FileInputStream(filename);
                        BufferedReader br = new BufferedReader(new InputStreamReader(fstream));

                        String strLine;
                        int lineNo = 0;
                        try {
                                while ((strLine = br.readLine()) != null) {
                                        // Print the content on the console
                                        System.out.println(strLine);
                                        if (lineNo++ > 1000)
                                                break;
                                }
                                br.close();
                        } catch (IOException o) {
                                o.printStackTrace();
                        }
                } catch (FileNotFoundException e) {
                        e.printStackTrace(); // To change body of catch statement use File |
                                                                        // Settings | File Templates.
                }
        }

        enum Tax {
                        one(1),two(2), three(3);
                private final int value;
                private Tax(int value) {
                        this.value = value;
                }

                public int getValue() {
                        return value;
                }
        };

        public static void parseIntToHex(int value){
                // turn int into 4 bytes
                byte byte0 = (byte)(value & 0xF);
                byte byte1 = (byte)((value >> 4) & 0xF);
                byte byte2 = (byte)((value >> 8) & 0xF);
                byte byte3 = (byte)((value >> 12) & 0xF);

                System.out.println(String.format("%01X%01X%01X%01X ", byte3, byte2, byte1, byte0));
//              System.out.println(String.format("%01X ", byte1));
//              System.out.println(String.format("%01X ", byte2));
//              System.out.println(String.format("%01X ", byte3));
        }

        public static byte[] intToByteArray(int num){
                byte[] intBytes = new byte[4];
                intBytes[0] = (byte) (num >>> 24);
                intBytes[1] = (byte) (num >>> 16);
                intBytes[2] = (byte) (num >>> 8);
                intBytes[3] = (byte) num;

//              intBytes[3] = (byte)(num & 0xF);
//              intBytes[2] = (byte)((num >> 4) & 0xF);
//              intBytes[1] = (byte)((num >> 8) & 0xF);
//              intBytes[0] = (byte)((num >> 12) & 0xF);

                return intBytes;
        }

        public static void main(String args[]) throws IOException {
                long t;
                String sqlfile = "c:/prg/tmp/demo.sql";
                String outfile = "c:/prg/tmp/demo.out.txt";
                // int i;
                t = System.currentTimeMillis();
                //parseIntToHex(0x1A2B);
                // readfile1(sqlfile,"UTF-8"); //"gb2312";//"gb2312" ""UTF-8";


                // TGetTableColumn getTableColumn = new
                // TGetTableColumn(EDbVendor.dbvmysql);
                // getTableColumn.listStarColumn = true;
                // getTableColumn.showTreeStructure = true;
                // getTableColumn.runFile(sqlfile);

                // getObject go = new getObject(EDbVendor.dbvmssql);
                // go.showModeFile = "c:/prg/tmp/demo.sql";
                // go.run(getObject.showMode);

                // readbigfile("d:/tmp/all_requests_uniq_cleared.dat");
                // StringBuffer literalbuf = new StringBuffer(1024);
                //
                // literalbuf.append("q'<'So,' she said, 'It's finished.'>'");
                //
                /*
                 * TJoinList jl = new TJoinList(); if (jl instanceof TJoinList){
                 * System.out.println("TJoinList"); } if (jl instanceof
                 * TParseTreeNodeList){ System.out.println("TParseTreeNodeList"); } if
                 * (jl instanceof TParseTreeNode){ System.out.println("TParseTreeNode");
                 * }
                 */

                // TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
                // OnlySql o = new OnlySql("sql");
                // getrawsqlstatements(EDbVendor.dbvmysql,"D:/prg/gsqlparser/Test\\TestCases\\undertest\\mysql/rawsqlstatements.sql");
                // String[] desiredTables =
                // {"CDS_H_KONTOKORRENT","CDS_H_KUNDEN_OBJEKT","CDS_H_ZINSEN","CDS_VERARBEITUNG","cds_vw_prm_waehrung_transl","CDS.CDS_APP"};

                // go.showModeFile =
                // "D:/prg/gsqlparser/Test/TestCases/java/oracle/dbobject/createpackagebody.sql";
                // go.showModeFile =
                // "D:/prg/gsqlparser/Test/TestCases/fetchdbobject/mssql/java/while1.sql";
                // go.run(getObject.compareMode);

                // mysqlPerformanceText();

                // parseallfiles();




                 //listSupportedSQLs(EDbVendor.dbvsparksql,true);

                // TBaseType.c_createTableStrictParsing = true;

                //EDbVendor dbVendor = EDbVendor.dbvpostgresql;
                String logfile = "23T14_02";
                //extractSQL("f:\\tmp\\syngenta\\useractivitylog_2021-11-23\\1\\useractivitylog_2021-11-"+logfile);
                // extractSQLFromDir("f:\\tmp\\syngenta\\redshift-logs\\2021-12-09\\");
//              try {
//                      sleep(10000);
//              } catch (InterruptedException e) {
//                      e.printStackTrace();
//              }

                //parseInt(1234);
                //testToScript();

                 sqlfile = "c:\\prg\\tmp\\demo.sql";

                 //TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE = true;

                // tokenizeSqltext(EDbVendor.dbvdb2,"c:/prg/tmp/demo.sql");
                // tokenizeSqltext(EDbVendor.dbvhive,"c:/prg/tmp/demo.sql");
                // tokenizeSqltext(EDbVendor.dbvmssql,"c:/prg/tmp/demo.sql");

                // getrawsqlstatements(EDbVendor.dbvmysql,sqlfile);

                //  TStatementList statements = parse(EDbVendor.dbvteradata, sqlfile, false);
                  toScript(EDbVendor.dbvpostgresql,sqlfile);

//              String [] dbs = EDbVendor.getAllVendorAliases(true);
//              System.out.println(dbs.length+":"+ String.join(", ", dbs));


                // parsefiles(EDbVendor.dbvpostgresql, "g:\\tmp\\sql\\");

                //testVariableSubstitution();

                // System.out.println(TLexerAnsi.getKeywordType("WORK"));


//              testSQLEnv();

//              if (TBaseType.logSize() > 0){
//                      TBaseType.log("Above logs is from sql file: "+sqlfile,TLog.DEBUG);
//                      TBaseType.dumpLogsToFile("c:\\prg\\tmp\\column_reslover_debug.log");
//                      TBaseType.clearLogs();
//              }

        //       getrawsqlstatements(EDbVendor.dbvteradata,sqlfile);
        //       tokenizeSqltext(EDbVendor.dbvpostgresql,sqlfile);


                //  tokenizeSqltext(EDbVendor.dbvnetezza,"c:/prg/tmp/demo.sql");
                // asCanonical(EDbVendor.dbvsnowflake, sqlfile);

                // getrawsqlstatements(EDbVendor.dbvgreenplum,sqlfile);
                //tokenizeSqltext(EDbVendor.dbvredshift,"c:/prg/tmp/demo.sql");

                // evaluate(EDbVendor.dbvpostgresql,sqlfile);


                //String fmt = String.format("Hello %s", "World");
                //String fmt = String.format("Testing %s, %s, %s, %%", "one", "two", "three");
                //String fmt = String.format("INSERT INTO \"%s\" VALUES('%s')", "Foo bar", "O\\'Reilly");
                //String fmt = String.format("'|%10s|","foo");
        //      System.out.println(fmt);

//               readfile1("f:\\tmp\\alexsolutions\\breaking_sql.sql");

        //      tokenizeSqltext(EDbVendor.dbvhive,sqlfile);

//               EDbVendor[] dbs = EDbVendor.values();
//               int l = dbs.length;
//               EDbVendor oracle = EDbVendor.dbvoracle;
//               int[] aaa = {
//                               1,2,3
//
//
//                      };
//               System.out.println( aaa[oracle.ordinal()] );


//               if (statements != null){
//                       TCompiler compiler  = new TCompiler();
//                       compiler.compile(statements,true,false);
//               }


                // TBaseType.db_version_num = 12;
                // parse(EDbVendor.dbvdb2,"C:\\Users\\Tako\\Downloads\\craddl.ddl");


                // iterateStatements(EDbVendor.dbvoracle,"D:/prg/tmp/demo.sql") ;
                // parseAndFetch(EDbVendor.dbvoracle,"D:/prg/tmp/demo.sql") ;

                // crud go = new crud(EDbVendor.dbvmssql,"D:/prg/tmp/demo.sql");
                // go.run();
                // textVisitor(EDbVendor.dbvoracle,"D:/prg/tmp/demo.sql");
                // modifySql("D:/prg/tmp/demo.sql");

                // selecttoString();
                // teradataTest();
                // parseSQLInputStream(EDbVendor.dbvoracle,sqlfile);
                // parseSQLInputStream(EDbVendor.dbvoracle,"c:\\prg\\gsqlparser\\Test\\TestCases\\unicode\\chinese_utf16_be_with_bom.sql");
                // System.out.println("[Dim].[vwBorrower]".replaceAll("\\[","").replaceAll("]",""));
                // System.out.println("\"Dim\".\"vwBorrower\"".replaceAll("\"",""));
                // System.out.println(compareIdentifier(EDbVendor.dbvdb2,"\"DIM.VWBORROWEr\"","Dim.vwBorrower"));
                // getQuotedString("'select $1 + $2;'");
                // teradataCmds();
                // mssqlTest();

                // int iterateNum = 50;
                // TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
                // perfTest(sqlparser,sqlfile,iterateNum);
                // System.out.println("Time Escaped: " + (System.currentTimeMillis() -
                // t)+", each sql: "+(System.currentTimeMillis() - t)/iterateNum);
                // listSupportedSQLs(EDbVendor.dbvteradata,true);
                // example1();
                // printMemoryUsage();
                // System.out.println("Default Charset=" + Charset.defaultCharset());
                // System.out.println(keywordChecker.getAvailableDbVersions(EDbVendor.dbvimpala));
                // System.out.println(keywordChecker.isKeyword( "identifier",
                // EDbVendor.dbvimpala,
                // "6.1",
                // true ));

                // generateKeywordList("c:\\prg\\tmp\\keyword.txt","c:\\prg\\tmp\\out.txt");
                // example1();
                // testSQLPlus();
                // testUseFile(sqlfile);
                // System.in.read();
                // testReplace();
                // testSQLEnv();
                // functionLoader.getFunctionList(EDbVendor.dbvhana);
                // functionChecker.getBuiltInFunctionList(EDbVendor.dbvhana,"7");
                // System.out.println(functionChecker.isBuiltInFunction("CURRENT_TIMESTAMP",EDbVendor.dbvmysql,"6.0"));
                // System.out.println("where recording_date >
                // ''1/1/2015'".replaceAll("''","'"));

                // parseJsonFile(EDbVendor.dbvmssql,"c:\\prg\\tmp\\DBexport20191119.txt")
                // ;
                // testSetNodeText();
                //String[] a = "a*".split("[.]");
                // System.out.println(a.length);
                // System.out.println(TSQLEnv.getSchemaName("table"));


                // TParseTreeNode.doubleLinkedTokenListToString = true;
                // testSetNodeText();
                // textIterator(EDbVendor.dbvmssql,"c:/prg/tmp/demo.sql");
                // testModifySQL();
                // testExprRemoveResultOrderBy();
                // showNodeOfToken();
                // testRenameTable();
                // testRemoveTableList();


                System.out.println("Time Escaped: " + (System.currentTimeMillis() - t) + ", version:" + TBaseType.versionid);

        }

        public static void  testVariableSubstitution(){
                String sql = "SELECT * FROM DWH_META_${DWHENV}.LOADPROTOCOL_GTMP WHERE env = ${ENV}";

                // Define variables and their values
                Map<String, String> variables = new HashMap<>();
                variables.put("DWHENV", "PROD");
                variables.put("ENV", "production");

                // Perform substitution
                String result = VariableSubstitutor.substituteVariables(sql, variables);
                System.out.println(result);
        }

        public static void testSQLEnv(){
                String declareSQL = "DECLARE\n" +
                                "\tz_empid employees.employee_id%TYPE;\n" +
                                "\tz_depid employees.department_id%TYPE;\n" +
                                "\tz_firstname employees.first_name%TYPE;\n" +
                                "\tz_lastname employees.last_name%TYPE;\n" +
                                "\n" +
                                "\tCURSOR cur_stclerk IS\n" +
                                "\t\tSELECT employee_id,\n" +
                                "\t\tdepartment_id,\n" +
                                "\t\tfirst_name,\n" +
                                "\t\tlast_name\n" +
                                "\t\tFROM employees\n" +
                                "\t\tWHERE job_id = 'ST_CLERK';\n" +
                                "BEGIN\n" +
                                "OPEN cur_stclerk;\n" +
                                "LOOP\n" +
                                "\tFETCH cur_stclerk INTO z_empid,z_depid,z_firstname,\n" +
                                "\tz_lastname;\n" +
                                "\tEXIT WHEN cur_stclerk%NOTFOUND;\n" +
                                "\n" +
                                "\tINSERT INTO emp_temp\n" +
                                "\t(employee_id,\n" +
                                "\tdepartment_id,\n" +
                                "\tjob_id)\n" +
                                "\tVALUES (z_empid,\n" +
                                "\tz_depid,\n" +
                                "\t'ST_CLERK');\n" +
                                "\n" +
                                "\tINSERT INTO emp_detls_temp\n" +
                                "\t(employee_id,\n" +
                                "\tempname)\n" +
                                "\tVALUES (z_empid,\n" +
                                "\tz_firstname\n" +
                                "\t|| ' '\n" +
                                "\t|| z_lastname);\n" +
                                "END LOOP;\n" +
                                "\n" +
                                "CLOSE cur_stclerk;\n" +
                                "COMMIT;\n" +
                                "END;";
                TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);

                String tableName = "ot.persons";
                int maxTables = 1000*10;
                for(int i=0;i<maxTables;i++){
                        sqlParser.sqltext = "CREATE TABLE "+ tableName+i+"(\n" +
                                        "    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,\n" +
                                        "    first_name VARCHAR2(50) NOT NULL,\n" +
                                        "    last_name VARCHAR2(50) NOT NULL,\n" +
                                        "    PRIMARY KEY(person_id)\n" +
                                        ");";
                        sqlParser.parse();
                        if (i%1000 == 0) System.out.printf("\r%d",i) ;
                }

                System.out.println();
                sqlParser.sqltext = declareSQL;
                sqlParser.parse();

                System.out.println("Number of tables in SQL Env: "+sqlParser.getSqlEnv().getNumberOfTables());
        }

        public static String extractSQL(String filePath) throws IOException {
                if (null == filePath || "".equals(filePath)) {
                        throw new RuntimeException("文件地址不能为空");
                }

                File file = new File(filePath);
                if (!file.exists()) {
                        throw new RuntimeException("文件不存在");
                }

                //read
                BufferedReader reader = new BufferedReader(new FileReader(file));
                StringBuilder sql = new StringBuilder();
                StringBuilder sqls = new StringBuilder();
                String temp;
                boolean flag = false;
                while ((temp = reader.readLine()) != null) {
                        String line = new String(temp.getBytes(), StandardCharsets.UTF_8);
                        if (line.contains("LOG")) {
                                if (flag) {
                                        String sqlStr = sql.toString().trim();
                                        if ("".equals(sqlStr) || sqlStr.startsWith("fetch")) {
                                                continue;
                                        }
                                        String[] sqlStrs = sqlStr.split("\n");
                                        String endSql = sqlStrs[sqlStrs.length - 1];

                                        String[] vars = endSql.split("--");
                                        if (vars.length > 1) {
                                                String var1 = vars[0];
                                                String trim = var1.trim();
                                                if (!trim.endsWith("\"") && !trim.endsWith(";")) {
                                                        if (sqlStrs.length > 1) {
                                                                if (!sqlStrs[sqlStrs.length - 2].trim().endsWith(";")) {
                                                                        sqlStr += "\n;\n";
                                                                }
                                                        } else {
                                                                sqlStr += "\n;\n";
                                                        }
                                                }
                                        } else if (!sqlStr.endsWith(";")) {
                                                sqlStr += ";";
                                        }
                                        sqls.append(sqlStr).append("\n").append("\n");
                                        sql = new StringBuilder();
                                        flag = false;
                                }
                                String[] logs = line.split("LOG:");
                                if (logs.length > 1 && !logs[1].trim().startsWith("fetch")) {
                                        sql.append(logs[1]).append("\n");
                                        flag = true;
                                }
                        } else if (flag && !line.trim().startsWith("fetch")) {
                                sql.append(line).append("\n");
                        }
                }
                reader.close();

                //write
                String fileName = file.getName();
                String resultFilePath;
                if (fileName.contains(".")) {
                        resultFilePath = file.getPath().split(fileName)[0] + fileName.substring(0, fileName.lastIndexOf(".")) + "_sql.sql";
                } else {
                        resultFilePath = file.getPath() + "_sql.sql";
                }
                BufferedWriter writer = new BufferedWriter(new FileWriter(resultFilePath));
                writer.write(sqls.toString());
                writer.flush();
                writer.close();

                return resultFilePath;
        }

        // static public void testTraceColumn(){
        // String sqltext = "SELECT a.deptno \"Department\", \n" +
        // " a.num_emp/b.total_count \"Employees\", \n" +
        // " a.sal_sum/b.total_sal \"Salary\"\n" +
        // " FROM\n" +
        // "(SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum\n" +
        // " FROM scott.emp\n" +
        // " GROUP BY deptno) a,\n" +
        // "(SELECT COUNT(*) total_count, SUM(sal) total_sal\n" +
        // " FROM scott.emp) b";
        //
        // //System.out.println(sqltext);
        // TTraceColumn traceColumn = new TTraceColumn(EDbVendor.dbvoracle);
        // traceColumn.runText(sqltext);
        // //System.out.print(traceColumn.getInfos().toString().trim());
        // String actualStr = traceColumn.getInfos().toString().trim();
        // }

        static public void testRemoveTableList() {
                TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
                parser.sqltext = "select f1,f2,f3\n" + "from table1 pal, table2 pualr, table3 pu\n"
                                + "WHERE  pal.application_location_id in (1,2,3,4)";
                int ret = parser.parse();

                TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement) parser.sqlstatements.get(0);
                selectSqlStatement.getResultColumnList().removeItem(0);
                selectSqlStatement.getJoins().removeItem(2);
                selectSqlStatement.getJoins().removeItem(0);
                selectSqlStatement.getWhereClause().getCondition().getRightOperand().getExprList().removeItem(1);
                System.out.println(selectSqlStatement.toString());

        }

        static public void testRenameTable() {
                TGSqlParser lcparser = new TGSqlParser(EDbVendor.dbvnetezza);
                lcparser.sqltext = "select * from \"emp\"";
                lcparser.parse();
                TSelectSqlStatement select = (TSelectSqlStatement) lcparser.sqlstatements.get(0);
                TTable table = select.tables.getTable(0);
                table.getTableName().setString(table.getTableName().toString().substring(1, 4));
                System.out.println(select.toString());
        }

        static public void testModifyExpr() {
                TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
                parser.sqltext = "SELECT SUM (d.amt) \n" + "FROM   summit.cntrb_detail d \n"
                                + "WHERE d.cntrb_date1 >= '$From_Date$' \n" + "AND d.cntrb_date2 <= '$Thru_Date$' \n"
                                + "GROUP  BY d.id;";
                parser.parse();

                TSelectSqlStatement select = (TSelectSqlStatement) parser.sqlstatements.get(0);
                TExpression expression = select.getWhereClause().getCondition();
                // expression.getLeftOperand().remove2();
                // expression.getRightOperand().remove2();
                // System.out.println(expression.toString());
                // System.out.println(select.getWhereClause().toString());
                TExpression expr_From_Date = expression.getLeftOperand().getRightOperand();
                TExpression expr_cntrb_date1 = expression.getLeftOperand().getLeftOperand();
                TExpression expr_Thru_Date = expression.getRightOperand().getRightOperand();
                TExpression expr_cntrb_date2 = expression.getRightOperand().getLeftOperand();
                // System.out.println("From_Date:"+expr_From_Date);
                // System.out.println("cntrb_date1:"+expr_cntrb_date1);
                // System.out.println("Thru_Date:"+expr_Thru_Date);
                // System.out.println("cntrb_date2:"+expr_cntrb_date2);

                // printNodeEndWithThisToken(expression.getLeftOperand().getEndToken());

                expr_cntrb_date1.removeMe();
                expr_From_Date.removeMe();
                expr_cntrb_date2.removeMe();
                expr_Thru_Date.removeMe();
                // removeExprFromParent(expr_cntrb_date1);
                // removeExprFromParent(expr_From_Date);
                // removeExprFromParent(expr_cntrb_date2);
                // removeExprFromParent(expr_Thru_Date);

                // removeExprFromParent(expr_cntrb_date2);
                // removeExprFromParent(expr_Thru_Date);
                // removeExprFromParent(expr_cntrb_date1);
                // removeExprFromParent(expr_From_Date);

                // System.out.println(expr_cntrb_date1.toString());
                // System.out.println(expression.getLeftOperand().toString());
                // System.out.println(expression.getLeftOperand().getEndToken().toString());

                // System.out.println("\n"+expression.toString());
                if (select.getWhereClause().getCondition().getNodeStatus() == ENodeStatus.nsRemoved) {
                        select.setWhereClause(null);
                }
                System.out.println("\n" + select.toString());
        }

        static void testModifyExpr3() {
                String sql = "SELECT SUM (d.amt)\n" + "FROM summit.cntrb_detail d\n"
                                + "WHERE ((d.fund_coll_attrb IN ( '$Institute$' )\n" + "AND d.fund_acct IN ( '$Fund$' )\n"
                                + "AND d.cntrb_date >= '$From_Date$'\n" + "AND d.cntrb_date <= '$Thru_Date$'))\n" + "GROUP BY d.id;";
                TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
                sqlparser.setSqltext(sql);
                sqlparser.parse();
                TSelectSqlStatement stmt = (TSelectSqlStatement) sqlparser.getSqlstatements().get(0);
                TExpression whereExpression = stmt.getWhereClause().getCondition();

                whereExpression.postOrderTraverse(new IExpressionVisitor() {
                        public boolean exprVisit(TParseTreeNode pnode, boolean pIsLeafNode) {
                                TExpression expression = (TExpression) pnode;
                                if (expression.getNodeStatus() != ENodeStatus.nsRemoved && expression != null
                                                && expression.getLeftOperand() == null && expression.getRightOperand() == null
                                                && expression.toString().indexOf("$") != -1) {
                                        // System.out.println(expression.toString());
                                        expression.removeMe();
                                } else {
                                        if (expression.getExpressionType() == EExpressionType.simple_comparison_t
                                                        || expression.getExpressionType() == EExpressionType.in_t) {
                                                if (expression.getRightOperand() == null && expression.getLeftOperand() != null) {
                                                        expression.getLeftOperand().removeMe();
                                                }
                                                if (expression.getRightOperand() != null && expression.getLeftOperand() == null) {
                                                        expression.getRightOperand().removeMe();
                                                }
                                                if (expression.getLeftOperand() == null && expression.getRightOperand() == null) {
                                                        expression.removeMe();
                                                }
                                        } else if (expression.getExpressionType() == EExpressionType.logical_and_t
                                                        || expression.getExpressionType() == EExpressionType.logical_or_t) {
                                                if (expression.getLeftOperand() == null && expression.getRightOperand() == null) {
                                                        expression.removeMe();
                                                }
                                        }
                                }
                                return true;
                        }

                });

                if (stmt.getWhereClause().getCondition().getNodeStatus() == ENodeStatus.nsRemoved) {
                        stmt.setWhereClause(null);
                }

                System.out.println("stmt:\n" + stmt.toString() + "\nend");

        }

        static void testModifyExpr2() {
                String sql = "SELECT SUM (d.amt)\r\n" + "FROM summit.cntrb_detail d\r\n"
                                + "WHERE d.fund_coll_attrb IN ( '$Institute$' )\r\n" + "AND d.fund_acct IN ( '$Fund$' )\r\n"
                                + "AND d.cntrb_date >= '$From_Date$'\r\n" + "AND d.cntrb_date <= '$Thru_Date$'\r\n" + "GROUP BY d.id;";
                TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
                sqlparser.setSqltext(sql);
                sqlparser.parse();
                TSelectSqlStatement stmt = (TSelectSqlStatement) sqlparser.getSqlstatements().get(0);
                TExpression whereExpression = stmt.getWhereClause().getCondition();

                whereExpression.postOrderTraverse(new IExpressionVisitor() {
                        public boolean exprVisit(TParseTreeNode pnode, boolean pIsLeafNode) {
                                TExpression expression = (TExpression) pnode;
                                if (expression.getNodeStatus() != ENodeStatus.nsRemoved && expression != null
                                                && expression.getLeftOperand() == null && expression.getRightOperand() == null
                                                && expression.toString().indexOf("$") != -1) {
                                        // System.out.println(expression.toString());
                                        removeExpression(expression);
                                } else {
                                        if (expression.getExpressionType() == EExpressionType.simple_comparison_t
                                                        || expression.getExpressionType() == EExpressionType.in_t) {
                                                if (expression.getRightOperand() == null && expression.getLeftOperand() != null) {
                                                        removeExpression(expression.getLeftOperand());
                                                }
                                                if (expression.getRightOperand() != null && expression.getLeftOperand() == null) {
                                                        removeExpression(expression.getRightOperand());
                                                }
                                                if (expression.getLeftOperand() == null && expression.getRightOperand() == null) {
                                                        removeExpression(expression);
                                                }
                                        } else if (expression.getExpressionType() == EExpressionType.logical_and_t
                                                        || expression.getExpressionType() == EExpressionType.logical_or_t) {
                                                if (expression.getLeftOperand() == null && expression.getRightOperand() == null) {
                                                        removeExpression(expression);
                                                }
                                        }
                                }
                                return true;
                        }

                        private void removeExpression(TExpression expression) {
                                if (expression.getParentExpr() != null) {
                                        TExpression parentExpr = expression.getParentExpr();
                                        if (parentExpr.getLeftOperand() == expression) {
                                                parentExpr.setLeftOperand(null);
                                        }
                                        if (parentExpr.getRightOperand() == expression) {
                                                parentExpr.setRightOperand(null);
                                        }
                                }
                        }
                });

                if (stmt.getWhereClause().getCondition().getNodeStatus() == ENodeStatus.nsRemoved) {
                        stmt.setWhereClause(null);
                }

                System.out.println(stmt.toString().trim());
        }

        static public void printNodeStartWithThisToken(TSourceToken st) {
                System.out.println("\nNode start from this token: " + st.toString());
                for (int i = 0; i < st.getNodesStartFromThisToken().size(); i++) {
                        TParseTreeNode node = st.getNodesStartFromThisToken().get(i);
                        System.out.println(i + ": Node type:" + node.getClass().getName() + ", \tNode text:" + node.toString());
                }
        }

        static public void printNodeEndWithThisToken(TSourceToken st) {
                System.out.println("\nNode end with this token: " + st.toString());
                for (int i = 0; i < st.getNodesEndWithThisToken().size(); i++) {
                        TParseTreeNode node = st.getNodesEndWithThisToken().get(i);
                        System.out.println(i + ": Node type:" + node.getClass().getName() + ", \tNode text:" + node.toString());
                }
        }

        static public void showNodeOfToken() {
                TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);

                parser.sqltext = "SELECT emp_id,salary+100 FROM emp";
                parser.parse();
                while (parser.sourcetokenlist.hasNext()) {
                        TSourceToken st = parser.sourcetokenlist.next();
                        if (st.toString().equalsIgnoreCase("emp")) {
                                printNodeStartWithThisToken(st);
                                printNodeEndWithThisToken(st);
                        }
                }
        }

        static public void testModifySQL() {
                String sql = "SELECT * FROM TABLE_X";

                TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
                parser.sqltext = sql;
                parser.parse();
                TSelectSqlStatement select = (TSelectSqlStatement) parser.sqlstatements.get(0);

                TWhereClause whereClause = new TWhereClause("where f > 0");
                // whereClause.setText("where f > 0");
                select.setAnchorNode(select.joins);
                select.setWhereClause(whereClause);

                System.out.println(select.toString());
                // assertTrue(select.toString().equalsIgnoreCase("SELECT * FROM TABLE_X
                // order by b asc,c desc"));

        }

        static public void testSetNodeText() {
                // TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvimpala);
                // sqlparser.sqltext = "create table if not exists campaign_1 ( id int,
                // name string )";
                // int ret = sqlparser.parse();
                // TCustomSqlStatement stmt = sqlparser.sqlstatements.get(0);
                // TTable table = stmt.tables.getTable(0);
                // table.setString("prefix_." + table.toString() + " " +
                // table.getAliasName());
                // System.out.println(table.toString());

                TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
                sqlparser.sqltext = "SELECT *\n" + "FROM scott.employee\n" + "WHERE fx(e.job_id) = 1";
                sqlparser.parse();
                TSelectSqlStatement select = (TSelectSqlStatement) sqlparser.sqlstatements.get(0);
                // Iterator<TSourceToken> iterator = select.iterator();
                while (select.hasNext()) {
                        System.out.print(select.next().toString());
                }
                System.out.println("\n");
                TWhereClause whereClause = select.getWhereClause();
                TExpression condition = whereClause.getCondition();
                TFunctionCall f = condition.getLeftOperand().getFunctionCall();
                // for(int
                // i=0;i<f.getStartToken().getNodesStartFromThisToken().size();i++){
                // TParseTreeNode node =
                // f.getStartToken().getNodesStartFromThisToken().getElement(i);
                // System.out.println(node.toString()+", "+node.getClass().getName());
                // if (node == f){
                // System.out.println("\t this is me");
                // }
                // }
                // f.setString("gx(e.job)");
                // System.out.println(f.toString());
                // System.out.println(condition.toString());
                whereClause.getCondition().setString("e.salary > 1000");
                // whereClause.setString("where a+b>c");
                System.out.println(select.toString());
        }

        static public void parseJsonFile(EDbVendor db, String jsonfile) {
                // int ret = 0;
                // TGSqlParser sqlparser = new TGSqlParser(db);
                // TSQLServerEnv tsqlServerEnv = new TSQLServerEnv();
                // // tsqlServerEnv.setEnableGetMetadataFromDDL(false);
                // sqlparser.setSqlEnv(tsqlServerEnv);
                // //sqlparser.sqlfilename = file;
                // String targetDir = "c:\\prg\\tmp\\sql\\";
                //
                // String json = SQLUtil.getFileContent(jsonfile);
                //
                // if (json != null && json.trim().startsWith("{")) {
                // JSONObject queryObject = JSON.parseObject(json);
                // JSONArray querys = queryObject.getJSONArray("queries");
                // if (querys != null) {
                // for (int i = 0; i < querys.size(); i++) {
                // String database = querys.getJSONObject(i).getString("database");
                // String schema = querys.getJSONObject(i).getString("schema");
                // tsqlServerEnv.setDefaultCatalogName(database);
                // tsqlServerEnv.setDefaultSchemaName(schema);
                // String sql = querys.getJSONObject(i).getString("sourceCode");
                // sqlparser.sqltext = sql;
                // sqlparser.parse();
                // SQLUtil.writeFileContent(new File(targetDir, (i + 1) + ".sql"), sql);
                // }
                // }
                // } else {
                // System.out.println(jsonfile + " is not a valid json file.");
                // }
                //
                // System.out.println(tsqlServerEnv.toString());
        }

        static public String qualifySchemaObjectName(String schemaObjectName) {
                String lcResult = schemaObjectName;
                String[] names = schemaObjectName.split("[.]");
                if (names.length == 1) {
                        lcResult = ".." + names[0];
                } else if (names.length == 2) {
                        lcResult = "." + names[0] + "." + names[1];
                } else if (names.length == 3) {
                        lcResult = names[0] + "." + names[1] + "." + names[2];
                }
                return lcResult;
        }

        static boolean compareIdentifier(EDbVendor dbVendor, String source, String target) {
                return compareIdentifier(dbVendor, EDbObjectType.table, source, target);
        }

        static boolean compareIdentifier(EDbVendor dbVendor, EDbObjectType objectType, String source, String target) {
                boolean ret = false;
                String normalTarget, normalSource;

                switch (dbVendor) {
                case dbvbigquery:
                        if (target.indexOf('`') >= 0) {
                                normalTarget = target.replaceAll("[`]", "");
                        } else {
                                if (objectType == EDbObjectType.table) { // table name is case
                                                                                                                        // sensitive
                                        normalTarget = target;
                                } else {
                                        normalTarget = target.toUpperCase();
                                }
                        }

                        if (source.indexOf('`') >= 0) {
                                normalSource = source.replaceAll("[`]", "");
                        } else {
                                if (objectType == EDbObjectType.table) {
                                        normalSource = source;
                                } else {
                                        normalSource = source.toUpperCase();
                                }
                        }

                        ret = normalTarget.compareTo(normalSource) == 0;
                        break;
                case dbvcouchbase:
                case dbvhive:
                case dbvimpala:
                        if (target.indexOf('`') >= 0) {
                                normalTarget = target.replaceAll("[`]", "");
                        } else {
                                normalTarget = target;
                        }

                        if (source.indexOf('`') >= 0) {
                                normalSource = source.replaceAll("[`]", "");
                        } else {
                                normalSource = source;
                        }

                        ret = normalTarget.equalsIgnoreCase(normalSource);
                        break;
                case dbvmysql:
                        if (target.indexOf('`') >= 0) {
                                normalTarget = target.replaceAll("[`]", "");
                        } else {
                                normalTarget = target;
                        }

                        if (source.indexOf('`') >= 0) {
                                normalSource = source.replaceAll("[`]", "");
                        } else {
                                normalSource = source;
                        }

                        ret = normalTarget.equalsIgnoreCase(normalSource);
                        break;
                case dbvdax:
                        if (target.indexOf('\'') >= 0) {
                                normalTarget = target.replaceAll("[']", "");
                        } else {
                                normalTarget = target;
                        }

                        if (source.indexOf('\'') >= 0) {
                                normalSource = source.replaceAll("[']", "");
                        } else {
                                normalSource = source;
                        }

                        ret = normalTarget.equalsIgnoreCase(normalSource);
                        break;
                case dbvdb2:
                case dbvhana:
                case dbvinformix:
                case dbvnetezza:
                case dbvoracle:
                case dbvredshift:
                case dbvsnowflake:
                case dbvsybase:
                case dbvteradata:
                case dbvvertica:
                        if (target.indexOf('"') >= 0) {
                                normalTarget = target.replaceAll("\"", "");
                        } else {
                                normalTarget = target.toUpperCase();
                        }

                        if (source.indexOf('"') >= 0) {
                                normalSource = source.replaceAll("\"", "");
                        } else {
                                normalSource = source.toUpperCase();
                        }
                        ret = normalTarget.compareTo(normalSource) == 0;

                        break;
                case dbvpostgresql:
                        if (target.indexOf('"') >= 0) {
                                normalTarget = target.replaceAll("\"", "");
                        } else {
                                normalTarget = target.toLowerCase();
                        }

                        if (source.indexOf('"') >= 0) {
                                normalSource = source.replaceAll("\"", "");
                        } else {
                                normalSource = source.toLowerCase();
                        }
                        ret = normalTarget.compareTo(normalSource) == 0;

                        break;
                case dbvmssql:
                        if (target.indexOf('"') >= 0) {
                                normalTarget = target.replaceAll("\"", "");
                        } else if (target.indexOf("[") >= 0) {
                                normalTarget = target.replaceAll("\\[", "").replaceAll("]", "");
                        } else {
                                normalTarget = target;
                        }

                        if (source.indexOf('"') >= 0) {
                                normalSource = source.replaceAll("\"", "");
                        } else if (source.indexOf("[") >= 0) {
                                normalSource = source.replaceAll("\\[", "").replaceAll("]", "");
                        } else {
                                normalSource = source;
                        }

                        // depends on the sql server case sensitive setting, need to add an
                        // option to control the comparision
                        ret = normalTarget.equalsIgnoreCase(normalSource);

                        break;
                default:
                        if (target.indexOf('"') >= 0) {
                                normalTarget = target.replaceAll("\"", "");
                        } else {
                                normalTarget = target.toUpperCase();
                        }

                        if (source.indexOf('"') >= 0) {
                                normalSource = source.replaceAll("\"", "");
                        } else {
                                normalSource = source.toUpperCase();
                        }
                        ret = normalTarget.compareTo(normalSource) == 0;

                        break;
                }

                return ret;
        }

        static void testUseFile(String sqlfilename) {
                ProcessFile processFile = new ProcessFile(sqlfilename);
                processFile.openIt();
                processFile.closeIt();
                // FileInputStream streamFromSqlFile = null;
                // try{
                //
                // streamFromSqlFile = new FileInputStream(sqlfilename);
                // InputStreamReader isr = new
                // InputStreamReader(streamFromSqlFile,Charset.defaultCharset().name());
                //
                // BufferedReader finputstream = new BufferedReader(isr);
                //
                // String thisLine;
                // while ((thisLine = finputstream.readLine()) != null) {
                // System.out.println(thisLine);
                // }
                // // streamFromSqlFile.close();
                // }catch (Exception e){
                // System.out.println(e.toString());
                // }finally {
                // }
                //
                // System.out.println("Process: "+sqlfilename);

        }

        static void testReplace() {
                String s = "DECLARE inString alias for $1; begin return ''bob''; end;";
                System.out.print(s.replaceAll("''", "'"));
        }

        static void testSQLPlus() {
                System.out.println(TSqlplusCmdStatement.searchCmd("rem"));
                System.out.println(TSqlplusCmdStatement.searchCmd("rem", null));
        }

        static void printMemoryUsage() {
                Runtime runtime = Runtime.getRuntime();

                NumberFormat format = NumberFormat.getInstance();

                StringBuilder sb = new StringBuilder();
                long maxMemory = runtime.maxMemory();
                long allocatedMemory = runtime.totalMemory();
                long freeMemory = runtime.freeMemory();

                sb.append("free memory: " + format.format(freeMemory / (1024 * 1024)) + "\n");
                sb.append("allocated memory: " + format.format(allocatedMemory / (1024 * 1024)) + "\n");
                sb.append("used memory: " + format.format((allocatedMemory - freeMemory) / (1024 * 1024)) + "\n");
                sb.append("max memory: " + format.format(maxMemory / (1024 * 1024)) + "\n");
                sb.append("total free memory: " + format.format((freeMemory + (maxMemory - allocatedMemory)) / (1024 * 1024))
                                + "\n");
                System.out.println(sb.toString());
        }

        static void example1() {
                String query = "CREATE TABLE \"QATEST\".\"BANK_TRANSACTION4\"\n" +

                                "   (    \"BANK_TRANSACTION_ID\" NUMBER(11,0) NOT NULL ENABLE NOVALIDATE,\n" +

                                "        \"TRANSACTION_ID\" VARCHAR2(800),\n" +

                                "        \"TRANSACTION_AMOUNT\" NUMBER(15,4),"

                                + "CONSTRAINT \"BANK_TRANS_PK3\" PRIMARY KEY (\"BANK_TRANSACTION_ID\"))";

                TGSqlParser sqlparser;

                sqlparser = new TGSqlParser(EDbVendor.dbvoracle);

                sqlparser.sqltext = query;
                System.out.println(query);

                if (sqlparser.parse() == 0)

                {

                        System.out.println("parsing successfull");

                        TCustomSqlStatement stmt = sqlparser.sqlstatements.get(0);

                        TCreateTableSqlStatement createTable = (TCreateTableSqlStatement) stmt;

                        System.out.println(createTable.getTableConstraints().getConstraint(0)); // prints
                                                                                                                                                                        // CONSTRAINT
                                                                                                                                                                        // "BANK_TRANS_PK3"
                                                                                                                                                                        // PRIMARY
                                                                                                                                                                        // KEY
                                                                                                                                                                        // ("BANK_TRANSACTION_ID")

                        TConstraint constraint = createTable.getTableConstraints().getConstraint(0);

                        System.out.println(constraint.getConstraint_type()); // prints
                                                                                                                                        // primary_key

                        TPTNodeList<TColumnWithSortOrder> constrColList = constraint.getColumnList(); // returns
                                                                                                                                                                                        // null
                                                                                                                                                                                        // but
                                                                                                                                                                                        // expected
                                                                                                                                                                                        // is
                                                                                                                                                                                        // "BANK_TRANSACTION_ID"

                        for (int constrColIndex = 0; constrColIndex < constrColList.size(); constrColIndex++)

                        {

                                String colName = constrColList.getElement(constrColIndex).getColumnName().toString();

                                System.out.println(colName); // prints null but expected is
                                                                                                // "BANK_TRANSACTION_ID"

                        }

                }
        }

        static void listSupportedSQLs(EDbVendor dbVendor, boolean html) {
                TSqlCmds sqlCmds = new TSqlCmds();
                TSqlCmds.TSqlCmdList cmdList = sqlCmds.getSqlCmdList(dbVendor);
                TGSqlParser sqlParser = new TGSqlParser(dbVendor);
                boolean isSupported = false;
                int supportSqlNum = 0, skippedNum = 0, processedNum = 0, errorNum = 0;
                ArrayList<ESqlStatementType> sqltypes = new ArrayList<ESqlStatementType>();

                String summaryStr = "<p>Level 1 statements: %d, Level 2 statements: %d, generated by %s</p>\n";

                String rowStr = " <tr height=21 style='height:15.75pt'>\n"
                                + "  <td height=21 class=xl2427189 style='height:15.75pt;border-top:none'>%s</td>\n"
                                + "  <td class=xl2427189 style='border-top:none;border-left:none'>%s</td>\n"
                                + "  <td class=xl2527189 style='border-top:none;border-left:none'>%s</td>\n" + " </tr>";

                StringBuilder sb = new StringBuilder();

                for (int i = 0; i < cmdList.size(); i++) {
                        isSupported = false;
                        TSqlCmds.TSqlCmd sqlCmd = (TSqlCmds.TSqlCmd) cmdList.get(i);
                        if (sqltypes.contains(sqlCmd.sqlstatementtype)) {
                                skippedNum++;
                                continue;
                        }
                        processedNum++;
                        sqltypes.add(sqlCmd.sqlstatementtype);
                        if (sqlCmd.toString().length() > 0) {
                                sqlParser.sqltext = sqlCmd.toString();
                                sqlParser.tokenizeSqltext();
                                TCustomSqlStatement stmt = sqlCmds.issql(sqlParser.getSourcetokenlist().get(0), dbVendor,
                                                EFindSqlStateType.stnormal, null);
                                if (stmt != null) {
                                        isSupported = !((stmt instanceof TUnknownSqlStatement) || (stmt instanceof TTeradataNotImplement));
                                }
                                if (isSupported)
                                        supportSqlNum++;
                        }
                        if (!html) {
                                System.out.println(processedNum + "\t" + isSupported + "\t" + sqlCmd.toString());
                        }

                        if (sqlCmd.toString().contains("*"))
                                errorNum++;

                        // System.out.println(String.format(rowStr,sqlCmd.toString(),"Y",isSupported?"Y":"
                        // "));
                        sb.append(String.format(rowStr, sqlCmd.toString(), "Y", isSupported ? "Y" : " "));
                }

                sb.insert(0, String.format(summaryStr, processedNum, supportSqlNum,
                                TBaseType.version + ',' + new SimpleDateFormat("yyyy/MM/dd").format(Calendar.getInstance().getTime())));
                if (html)
                        System.out.println(sb.toString());
                System.out.println("total:" + cmdList.size() + ",processed:" + processedNum + ",skipped:" + skippedNum
                                + ",error:" + errorNum + ",supported:" + supportSqlNum);

        }

        static void perfTest(TGSqlParser sqlparser, String filename, int iterateNum) {
                sqlparser.sqlfilename = filename;
                for (int i = 0; i < iterateNum; i++) {
                        sqlparser.parse();
                }
        }

        static void mssqlTest() {
                TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
                sqlparser.sqltext = "CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]\n"
                                + "@BusinessEntityID [int]," + "@JobTitle [nvarchar](50)," + "@HireDate [datetime],"
                                + "@RateChangeDate [datetime]," + "@Rate [money], " + "@PayFrequency [tinyint],"
                                + "@CurrentFlag [dbo].[Flag]" + "WITH EXECUTE AS CALLER\n" + "AS\n" + "\n" + "BEGIN\n" + "\n" +
                                /*
                                 * "INSERT INTO PRODUCT_DESCRIPTIONS VALUES ( PROD_ID , LANGID,  TR_NAME,   TR_DESC   );\n"
                                 * + "SELECT ename, empno FROM emp;\n"+ "COMMIT;\n"+
                                 */
                                "SET NOCOUNT ON;\n" + "BEGIN TRY\n" + "BEGIN TRANSACTION;\n" + "UPDATE [HumanResources].[Employee] \n"
                                + "SET [JobTitle] = @JobTitle\n" + ",[HireDate] = @HireDate\n" + ",[CurrentFlag] = @CurrentFlag\n"
                                + "WHERE [BusinessEntityID] = @BusinessEntityID;\n"
                                + "INSERT INTO [HumanResources].[EmployeePayHistory] \n" + "([BusinessEntityID]" + ",[RateChangeDate]"
                                + ",[Rate]" + ",[PayFrequency])\n"
                                + "VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);\n" + "COMMIT TRANSACTION;\n"
                                + "END TRY\n" + "BEGIN CATCH\n" + "-- Rollback any active or uncommittable transactions before\n"
                                + "-- inserting information in the ErrorLog\n" + "IF @@TRANCOUNT > 0\n" + "BEGIN\n"
                                + "ROLLBACK TRANSACTION;\n" + "END\n" + "EXECUTE [dbo].[uspLogError];\n" + "END CATCH;\n" + "END;";

                int ret = sqlparser.parse();
                if (ret != 0) {
                        System.out.println(sqlparser.getErrormessage());
                        return;
                }

                TCustomSqlStatement sql = sqlparser.sqlstatements.get(0);
                System.out.println("SQL Statement: " + sql.sqlstatementtype);

                TMssqlCreateProcedure procedure = (TMssqlCreateProcedure) sql;

                // TPlsqlCreateProcedure procedure = (TPlsqlCreateProcedure)sql;
                System.out.println("Procedure name: " + procedure.getProcedureName().toString());
                if (procedure.getParameterDeclarations() != null) {
                        System.out.println("Parameters:" + procedure.getParameterDeclarations());

                        TParameterDeclaration param = null;
                        for (int i = 0; i < procedure.getParameterDeclarations().size(); i++) {
                                param = procedure.getParameterDeclarations().getParameterDeclarationItem(i);
                                System.out.println("\tName:" + param.getParameterName().toString());
                                System.out.println("\tDatatype:" + param.getDataType().toString());
                                System.out.println("\tIN/OUT:" + param.getMode());
                        }
                }

                // Aniket
                if (procedure.getDeclareStatements().size() != 0) {
                        TStatementList list = procedure.getDeclareStatements();
                        System.out.println("List : " + list);
                        System.out.println("List: " + list.size());
                        /*
                         * for(int k=0; k<iList.size();k++){
                         * 
                         * }
                         */
                        for (int k = 0; k < list.size(); k++) {
                                // Written by Aniket to parse the cursor and usage of various
                                // methods of it
                                if (list.get(k).toString().toUpperCase().contains("CURSOR")) {
                                        TCursorDeclStmt cur = (TCursorDeclStmt) list.get(k);
                                        System.out.println("Cursor Type : " + cur.sqlstatementtype);
                                        System.out.println("Cursor name is : " + cur.getCursorName());
                                        System.out.println("Cursor Subquery : " + cur.getSubquery());
                                        //
                                        /*
                                         * cursorName=list.get(k).toString();
                                         * System.out.println("Cursor Name: "+cursorName);
                                         */
                                }
                        }

                        TStatementList declareStatements = procedure.getDeclareStatements();
                        System.out.println("declare statements: " + declareStatements.size());
                        TCustomSqlStatement declareStatement = null;
                        TVarDeclStmt variableDelcare = null;
                        for (int i = 0; i < declareStatements.size(); i++) {
                                declareStatement = declareStatements.get(i);
                                System.out.println("SQL Statement: " + declareStatement.sqlstatementtype);
                                if (declareStatement.toString().toLowerCase().contains("cursor")) {
                                        TCursorDeclStmt cur = (TCursorDeclStmt) declareStatement;
                                        System.out.println("Cursor Name:" + cur.getCursorName());
                                } else {
                                        variableDelcare = (TVarDeclStmt) declareStatement;

                                        switch (variableDelcare.getWhatDeclared()) {
                                        case TVarDeclStmt.whatDeclared_variable:
                                                System.out.println("\tVariable Name:" + variableDelcare.getElementName().toString());
                                                System.out.println("\tVariable Datatype:" + variableDelcare.getDataType().toString());
                                                break;
                                        case TVarDeclStmt.whatDeclared_constant:
                                                break;
                                        case TVarDeclStmt.whatDeclared_exception:
                                                System.out.println("\tException:" + variableDelcare.getElementName().toString());
                                                break;
                                        case TVarDeclStmt.whatDeclared_subtype:
                                                break;
                                        case TVarDeclStmt.whatDeclared_pragma_autonomous_transaction:
                                                break;
                                        case TVarDeclStmt.whatDeclared_pragma_exception_init:
                                                break;
                                        case TVarDeclStmt.whatDeclared_pragma_serially_reusable:
                                                break;
                                        case TVarDeclStmt.whatDeclared_pragma_restrict_references:
                                                break;
                                        case TVarDeclStmt.whatDeclared_pragma_timestamp:
                                                break;
                                        }
                                }
                        }
                }

                TStatementList bodyStatements1 = procedure.getBodyStatements();
                // TUpdateSqlStatement tUpdate =(TUpdateSqlStatement)
                // bodyStatements1.get(0);
                for (int j = 0; j <= bodyStatements1.size(); j++) {
                        System.out.println("SQL Body Statement" + bodyStatements1.getNodeType());
                }
                System.out.println("body statements: " + bodyStatements1.size());
                System.out.println("body statements: " + bodyStatements1);
                if (procedure.getBodyStatements().size() != 0) {
                        TStatementList bodyStatements = procedure.getBodyStatements();//
                        System.out.println("body statements: " + bodyStatements.size());
                        TCustomSqlStatement bodyStatement = null;
                        for (int i = 0; i < bodyStatements.size(); i++) {
                                bodyStatement = bodyStatements.get(i);
                                System.out.println("SQL Statement: " + bodyStatement.sqlstatementtype);
                                System.out.println("Body Statement : " + bodyStatement);
                                switch (bodyStatement.sqlstatementtype) {
                                case sstmssqlblock:
                                        TMssqlBlock block = (TMssqlBlock) bodyStatement;
                                        processBlock(block, 0);
                                        break;
                                case sst_assignstmt:
                                        TAssignStmt assign = (TAssignStmt) bodyStatement;
                                        System.out.println("left: " + assign.getLeft().toString());
                                        System.out.println("right: " + assign.getExpression().toString());
                                        break;
                                case sst_ifstmt:
                                        TIfStmt ifstmt = (TIfStmt) bodyStatement;
                                        System.out.println("condition: " + ifstmt.getCondition().toString());

                                        if (ifstmt.getThenStatements().size() > 0) {
                                                System.out.println("then statement:");
                                                for (int j = 0; j < ifstmt.getThenStatements().size(); j++) {
                                                        System.out
                                                                        .println("\tStatement type: " + ifstmt.getThenStatements().get(j).sqlstatementtype);
                                                        System.out.println("\tStatement text: " + ifstmt.getThenStatements().get(j).toString());
                                                }
                                        }

                                        if (ifstmt.getElseifStatements().size() > 0) {
                                                System.out.println("else if statement:");
                                                for (int j = 0; j < ifstmt.getElseifStatements().size(); j++) {
                                                        System.out.println(
                                                                        "\tStatement type: " + ifstmt.getElseifStatements().get(j).sqlstatementtype);
                                                        System.out.println("\tStatement text: " + ifstmt.getElseifStatements().get(j).toString());
                                                }
                                        }

                                        if (ifstmt.getElseStatements().size() > 0) {
                                                System.out.println("else statement:");
                                                for (int j = 0; j < ifstmt.getElseStatements().size(); j++) {
                                                        System.out
                                                                        .println("\tStatement type: " + ifstmt.getElseStatements().get(j).sqlstatementtype);
                                                        System.out.println("\tStatement text: " + ifstmt.getElseStatements().get(j).toString());
                                                }
                                        }

                                        break;
                                default:
                                        break;
                                }
                        }
                }

        }

        static void processBlock(TMssqlBlock block, int level) {
                for (int k = 0; k < block.getBodyStatements().size(); k++) {
                        for (int i = 0; i < level; i++)
                                System.out.print("\t");
                        System.out.println("Inside block: " + block.getBodyStatements().get(k).sqlstatementtype);
                        if (block.getBodyStatements().get(k).sqlstatementtype == ESqlStatementType.sstmssqlblock) {
                                processBlock((TMssqlBlock) block.getBodyStatements().get(k), ++level);
                        }
                }
        }

        static void iterateStatements(EDbVendor db, String file) {
                TGSqlParser parser = new TGSqlParser(db);
                parser.sqlfilename = file;
                int iret = parser.parse();
                if (iret != 0) {
                        System.out.println(parser.getErrormessage());
                        return;
                }

                TStatementList stmtList = parser.sqlstatements;
                System.out.println(stmtList.size());
                System.out.println(stmtList.hasNext());
                // TCustomSqlStatement topLevelStmt = stmtList.next();
                Iterator<TCustomSqlStatement> iterator = stmtList.iterator();
                while (iterator.hasNext()) {
                        System.out.println(iterator.next().toString());
                }

                // System.out.println(topLevelStmt.toString());
                //
                // for (int i = 0; i < parser.sqlstatements.size(); i++) {
                // TCustomSqlStatement s = parser.sqlstatements.get(i);
                // //System.out.println(s.toString());
                // }
        }

        static void parseSQLInputStream(EDbVendor db, String file) {
                TGSqlParser sqlparser = new TGSqlParser(db);

                // sqlparser.sqlfilename = file;
                String fullSql = "select * from foo";

                try {
                        FileInputStream inputStream = new FileInputStream(file);

                        sqlparser.setSqlInputStream(inputStream);

                        int ret = sqlparser.parse();

                        if (ret == 0) {
                                System.out.println("parse ok, statements:" + sqlparser.sqlstatements.size());
                        } else {
                                System.out.println("error:" + sqlparser.getErrormessage());
                        }

                } catch (FileNotFoundException e) {
                        e.printStackTrace(); // To change body of catch statement use File |
                                                                        // Settings | File Templates.
                }

        }

        static void mysqlPerformanceText() {
                int num = 1000;
                long t;
                t = System.currentTimeMillis();

                for (int i = 0; i < num; i++) {
                        TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvmysql);
                        sqlParser.sqltext = "select * from foo" + i;
                        int ret = sqlParser.parse();
                }

                System.out.println("Time Escaped: " + (System.currentTimeMillis() - t) + " for " + num);

        }

        static void teradataTest() {
                TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvteradata);

                String sql = "select TIMESTAMP '1999-07-01 15:00:00-08:00'\n" + "from dual;";

                sqlParser.sqltext = sql;

                int ret = sqlParser.parse();
                if (ret != 0) {
                        System.out.println(sqlParser.getErrormessage());
                        return;
                }

                TSelectSqlStatement select = (TSelectSqlStatement) sqlParser.sqlstatements.get(0);
                TResultColumnList columnList = select.getResultColumnList();
                TResultColumn column = null;
                for (int i = 0; i < columnList.size(); i++) {
                        column = columnList.getResultColumn(i);

                        System.out.println(column.toString());
                }

        }

        static void selecttoString() {
                TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);

                String sql = "SELECT R.* FROM REGIONS R";

                sqlParser.sqltext = sql;

                sqlParser.parse();

                TSelectSqlStatement select = (TSelectSqlStatement) sqlParser.sqlstatements.get(0);
                System.out.println(select.toString());

        }

        static void tokenizeSqltext(EDbVendor db, String filename) {
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.setSqlCharset("UTF-8");
                sqlparser.sqlfilename = filename;
                sqlparser.tokenizeSqltext();

                for (int i = 0; i < sqlparser.sourcetokenlist.size(); i++) {
                        TSourceToken st = sqlparser.sourcetokenlist.get(i);
//                      if (st.lineNo < 3704) continue;
//                      if (st.lineNo > 3704+200) break;
                        System.out.printf("line:%d,Column:%d,%s,type:%s,code:%d, pos in list:%d\n", st.lineNo, st.columnNo,
                                        st.tokentype == ETokenType.ttreturn ? "linebreak" : st.toString()
                                        // , st.tokentype == ETokenType.ttreturn ? "linebreak" :
                                        // TBaseType.toHex(st.toString(),"UTF-8")
                                        , st.tokentype, st.tokencode, st.posinlist);
                }

        }

        static void textIterator(EDbVendor db, String filename) {
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.sqlfilename = filename;
                int ret = sqlparser.parse();
                if (ret == 0) {
                        for (TCustomSqlStatement sqlStatement : sqlparser.sqlstatements) {
                                System.out.println(sqlStatement.sqlstatementtype);
                                // while(sqlStatement.hasNext()){
                                // //System.out.println(sqlStatement.next().toString());
                                // }
                                if (sqlStatement.sqlstatementtype == ESqlStatementType.sstselect) {
                                        TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement) sqlStatement;
                                        for (TJoin join : sqlStatement.joins) {
                                                // System.out.println(join.getKind());
                                        }
                                        for (TTable table : sqlStatement.tables) {
                                                System.out.println(table.getTableType());
                                        }
                                        while (sqlStatement.tables.hasNext()) {
                                                System.out.println(sqlStatement.tables.next().toString());
                                        }
                                }
                        }
                } else {
                        System.out.println(sqlparser.getErrormessage());
                }

        }

        static void textVisitor(EDbVendor db, String filename) {
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.sqlfilename = filename;
                int ret = sqlparser.parse();
                if (ret == 0) {
                        TtextVisitor tv = new TtextVisitor();
                        for (int i = 0; i < sqlparser.sqlstatements.size(); i++) {
                                sqlparser.sqlstatements.get(i).accept(tv);
                        }
                } else {
                        System.out.println(sqlparser.getErrormessage());
                }

        }

        static void modifySql(String sqlfile) {
                TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
                sqlparser.sqlfilename = sqlfile;
                int ret = sqlparser.parse();
                if (ret == 0) {
                        TCustomSqlStatement s = sqlparser.sqlstatements.get(0);
                        TTable t;
                        for (int i = 0; i < s.tables.size(); i++) {
                                t = s.tables.getTable(i);
                                if (t.toString().compareToIgnoreCase("table2") == 0) {
                                        for (int j = 0; j < t.getObjectNameReferences().size(); j++) {
                                                System.out.println(t.getObjectNameReferences().getObjectName(j).toString());
                                        }
                                        t.setString("(tableX join tableY using (id)) as table2");
                                }
                        }
                        System.out.println(s.toString());
                } else {
                        System.out.println(sqlparser.getErrormessage());
                }
        }

        static void parsetext(EDbVendor db, String sql) {
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.sqltext = sql;
                int ret = sqlparser.parse();
                if (ret == 0) {
                        System.out.println("Successfully check syntax!");
                        /*
                         * for(int j=0;j<sqlparser.sourcetokenlist.size();j++){ TSourceToken
                         * st = sqlparser.sourcetokenlist.get(j);
                         * System.out.println(st.tokentype+" "+st.astext); } for(int i=0;
                         * i<sqlparser.sqlstatements.size();i++){ TCustomSqlStatement stmt =
                         * sqlparser.sqlstatements.get(i);
                         * System.out.println(stmt.sqlstatementtype.toString()); for(int
                         * j=0;j<stmt.sourcetokenlist.size();j++){ TSourceToken st =
                         * stmt.sourcetokenlist.get(j); System.out.println(st.tokentype); }
                         * }
                         */
                } else {
                        System.out.println(sqlparser.getErrormessage());
                }

        }

        static void parseallfiles() {
                // parsefiles(EDbVendor.dbvaccess,
                // "D:/prg/gsqlparser/Test/TestCases/access");
                // parsefiles(EDbVendor.dbvdb2, "D:/prg/gsqlparser/Test/TestCases/db2");
                // parsefiles(EDbVendor.dbvmysql,
                // "D:/prg/gsqlparser/Test/TestCases/mysql");
                // parsefiles(EDbVendor.dbvmssql, "f:\\tmp\\1021\\sql\\");
                // parsefiles(EDbVendor.dbvoracle,
                // "D:/prg/gsqlparser/Test/TestCases/oracle");
        }

        // static void toScript2(EDbVendor db, String file) {
        // int ret = 0;
        // TScriptGenerator scriptGenerator = new TScriptGenerator();
        // TGSqlParser sqlparser = new TGSqlParser(db);
        //
        // System.out.println("Parsing with " + db + " ...");
        // sqlparser.sqlfilename = file;
        // ret = sqlparser.parse();
        //
        // for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
        // System.out.println("sql type: " +
        // sqlparser.sqlstatements.get(j).sqlstatementtype + "(" +
        // sqlparser.sqlstatements.get(j).getClass().toString() + ")" + ", " +
        // sqlparser.sqlstatements.get(j).getStartToken().toString() + "(" +
        // sqlparser.sqlstatements.get(j).getStartToken().lineNo + "," +
        // sqlparser.sqlstatements.get(j).getStartToken().columnNo + ")");
        // System.out.println(
        // scriptGenerator.generateScript(sqlparser.sqlstatements.get(j)));
        // //System.out.println(sqlparser.sqlstatements.get(j).toScript());
        // }
        //
        // if (ret == 0) {
        // System.out.println("sqls: " + sqlparser.sqlstatements.size());
        // } else {
        // System.out.println("error: " + sqlparser.getErrormessage());
        // }
        //
        // }

        static void toScript(EDbVendor db, String file) {
                int ret = 0;
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.setSqlCharset("UTF-8");

                System.out.println("Parsing with " + db + " ...");
                sqlparser.sqlfilename = file;
                ret = sqlparser.parse();

                for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
                        System.out.println("sql type: " + sqlparser.sqlstatements.get(j).sqlstatementtype + "("
                                        + sqlparser.sqlstatements.get(j).getClass().toString() + ")" + ", "
                                        + sqlparser.sqlstatements.get(j).getStartToken().toString() + "("
                                        + sqlparser.sqlstatements.get(j).getStartToken().lineNo + ","
                                        + sqlparser.sqlstatements.get(j).getStartToken().columnNo + ")");

                        System.out.println("=========== toString() ==============");
                        System.out.println(sqlparser.sqlstatements.get(j).toString());

                        System.out.println("=========== toScript() ==============");
                        System.out.println(sqlparser.sqlstatements.get(j).toScript());

                }

                if (ret == 0) {
                        System.out.println("sqls: " + sqlparser.sqlstatements.size());
                } else {
                        System.out.println("error: " + sqlparser.getErrormessage());
                }

        }

        static void evaluate(EDbVendor db, String file) {
                int ret = 0;
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.sqlfilename = file;
                ret = sqlparser.parse();

                if (ret != 0) {
                        System.out.println("error: " + sqlparser.getErrormessage());
                        System.out.println("syntax arrays: "+sqlparser.getSyntaxErrors().size());
                        return ;
                } else {
                        System.out.println("sqls: " + sqlparser.sqlstatements.size());
                }

                TSimpleEvaluationContext context = new TSimpleEvaluationContext();
                TAnnotatedTree annotatedTree = new TAnnotatedTree();
                annotatedTree.setAst(sqlparser.sqlstatements);

                TTypeAndScopeScanner scopeScanner = new TTypeAndScopeScanner(context,annotatedTree);
                sqlparser.sqlstatements.acceptChildren(scopeScanner);

//              TSqlStringEvaluator evaluator = new TSqlStringEvaluator(context,annotatedTree);
//              sqlparser.sqlstatements.acceptChildren(evaluator);

                System.out.println(annotatedTree.getScopeTreeString());
        }

        static TStatementList parse1(EDbVendor db, String file) {
                int ret = 0;
                TGSqlParser sqlparser = new TGSqlParser(db);
                System.out.println("Parsing with " + db + " ...");
                sqlparser.sqlfilename = file;
                ret = sqlparser.parse();

                int totalError = 0;
                for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {

                        String syntaxSign = "Success\t";
                        if (sqlparser.sqlstatements.get(j).getSyntaxErrors().size() > 0){
                                syntaxSign = "Failed\t";
                                totalError++;
                        }

                        System.out.print(syntaxSign+"sql type: " + sqlparser.sqlstatements.get(j).sqlstatementtype + "("
                                        + sqlparser.sqlstatements.get(j).getClass().toString() + ")" + ", "
                                        + sqlparser.sqlstatements.get(j).getStartToken().toString() + "("
                                        + sqlparser.sqlstatements.get(j).getStartToken().lineNo + ","
                                        + sqlparser.sqlstatements.get(j).getStartToken().columnNo + ")");


                        System.out.print("\n");
                        for(int i=0;i<sqlparser.sqlstatements.get(j).getSyntaxHints().size();i++){
                                System.out.println( String.format("syntax hint:%s(%d,%d)"
                                                ,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).tokentext
                                                ,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).lineNo
                                                ,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).columnNo)
                                                +sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).hint);
                        }
                }


                if (ret == 0) {
                        System.out.println("sqls: " + sqlparser.sqlstatements.size());
                        return sqlparser.sqlstatements;
                } else {
                        System.out.println("error: " + sqlparser.getErrormessage());
                        System.out.println("syntax arrays: "+sqlparser.getSyntaxErrors().size());
                        System.out.println("total errors: "+totalError+"/"+sqlparser.sqlstatements.size());
                        return  null;
                }

        }

        static void asCanonical (EDbVendor db, String file) {
                int ret = 0;
                TGSqlParser sqlparser = new TGSqlParser(db);

                System.out.println("Parsing with " + db + " ...");
                sqlparser.sqlfilename = file;
                TBaseType.as_canonical_f_decrypt_replace_password = true;
                ret = sqlparser.parse();


                int totalError = 0;
                for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
                        System.out.println(sqlparser.sqlstatements.get(j).asCanonical());
                }


                if (ret == 0) {
                        System.out.println("sqls: " + sqlparser.sqlstatements.size());
                        return ;
                } else {
                        System.out.println("error: " + sqlparser.getErrormessage());
                        System.out.println("syntax arrays: "+sqlparser.getSyntaxErrors().size());
                        System.out.println("total errors: "+totalError+"/"+sqlparser.sqlstatements.size());
                        return ;
                }

        }

        static TStatementList parse(EDbVendor db, String file, boolean showTokens) {
                int ret = 0;
                TLog.clearLogs();

                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.setEnableTimeLogging(true);

//              sqlparser.setTokenListHandle(new myTokenListHandle());
                //sqlparser.setSqlCharset("UTF-8");
//              TBaseType.bigquery_legacysql_compatible = true;
                //sqlparser.setSqlCharset("GB18030");
//              tsqlServerEnv.setEnableGetMetadataFromDDL(true);
//              TSQLServerEnv tsqlServerEnv = new TSQLServerEnv();
                //sqlparser.setSqlEnv(new THiveEnv());

                System.out.println("Parsing with " + db + " ...");
                // sqlparser.sqlfilename =
                // "D:\\prg\\gsqlparser\\Test\\TestCases\\oracle\\substituation.sql";
                // sqlparser.sqlfilename = "D:\\tmp\\addextent.sql";
                sqlparser.sqlfilename = file;
                ret = sqlparser.parse();

                //File file1 = new File("c:\\prg\\tmp\\demo1.sql");
                //SQLUtil.writeFileContent(file1,sqlparser.sqlstatements.get(0).toString(),"GB18030" );
                 System.out.println();
                // System.out.println(sqlparser.getSqlEnv().toString());

                //
                if (showTokens){
                        for(int i=0;i<sqlparser.sourcetokenlist.size();i++){
                                TSourceToken st = sqlparser.sourcetokenlist.get(i);
                                System.out.println(st.tokentype.toString()+
                                                +st.tokencode+"("+st.lineNo+","+st.columnNo+ ")"+st.toString()
                                                +", type:"+st.tokentype
                                                +", status:"+st.tokenstatus
                                                +", hashcode:"+st.hashCode());
                        }
                }


                int totalError = 0;
                StringBuilder sb = new StringBuilder();
                String logInfo;
                System.out.println("Total");

                for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
                        TCustomSqlStatement sqlStatement = sqlparser.sqlstatements.get(j);

//                      if (!((sqlparser.sqlstatements.get(j).sqlstatementtype == ESqlStatementType.sstcreatefunction)
//                      ||(sqlparser.sqlstatements.get(j).sqlstatementtype == ESqlStatementType.sstcreateprocedure)
//            ))
//                              continue;


                        //System.out.println(sqlparser.getSqlstatements().get(j).toString());

                        String syntaxSign = "Success\t";
                        if (sqlStatement.getSyntaxErrors().size() > 0){
                                syntaxSign = "Failed\t";
                                totalError++;
                        }

                        logInfo = //String.format(
                                        "["+j+"] "+syntaxSign+"sql type: " + sqlStatement.sqlstatementtype + "("
                                        + sqlStatement.getClass().toString() + ")" + ", "
                                        + sqlStatement.getStartToken().toString() + "("
                                        + sqlStatement.getStartToken().lineNo + ","
                                        + sqlStatement.getStartToken().columnNo + ")";
                                //);

                        sb.append(logInfo);

                        System.out.println(logInfo);

//                      System.out.print(syntaxSign+"sql type: " + sqlparser.sqlstatements.get(j).sqlstatementtype + "("
//                                      + sqlparser.sqlstatements.get(j).getClass().toString() + ")" + ", "
//                                      + sqlparser.sqlstatements.get(j).getStartToken().toString() + "("
//                                      + sqlparser.sqlstatements.get(j).getStartToken().lineNo + ","
//                                      + sqlparser.sqlstatements.get(j).getStartToken().columnNo + ")");

//                      System.out.print(sqlparser.sqlstatements.get(j).asCanonical());
//            System.out.print("\n\n"+sqlparser.sqlstatements.get(j).toString()+"\n\n");

                        System.out.print("\n");
                        for(int i=0;i<sqlparser.sqlstatements.get(j).getSyntaxHints().size();i++){

                                System.out.println( String.format("syntax hint:%s(%d,%d)"
                                                ,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).tokentext
                                                ,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).lineNo
                                                ,sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).columnNo)
                                                +sqlparser.sqlstatements.get(j).getSyntaxHints().get(i).hint);
                        }

                        if (sqlparser.sqlstatements.get(j).getCommentBeforeNode() != null){
//                              System.out.println("\nComment before this stmt:");
//                              System.out.println(sqlparser.sqlstatements.get(j).getCommentBeforeNode());
                        }else{
                        //      System.out.println("Comment before this stmt is null");
                        }

                        // print out all tokens in this statement

//                      if (showTokens){
//                              System.out.println("Tokens in statement: "+j);
//
//                              for(int i=0;i<sqlparser.sqlstatements.get(j).sourcetokenlist.size();i++){
//                                      TSourceToken st = sqlparser.sqlstatements.get(j).sourcetokenlist.get(i);
//                                      System.out.println(st.tokentype.toString()+
//                                                      +st.tokencode+"("+st.lineNo+","+st.columnNo+ ")"+st.toString()
//                                                      +", type:"+st.tokentype
//                                                      +", status:"+st.tokenstatus
//                                                      +", hashcode:"+st.hashCode());
//                              }
//                      }

                }

//              sqlparser.getSqlEnv().setDefaultCatalogName("default1");
//              sqlparser.getSqlEnv().setDefaultSchemaName("default2");
//              System.out.println(sqlparser.getSqlEnv().toString());
//
//              System.out.println("===== Start validating...");
//              sqlparser.validate();

//              sqlparser.collectMetadata();
//              sqlparser.resolveRelation();
//              sqlparser.resolveAttribute();

//              try {
//                      FileWriter myWriter = new FileWriter("c:\\baidudisk\\works\\tech_support\\eoncollective\\aa.txt");
//                      myWriter.write(sb.toString());
//                      myWriter.close();
//                      System.out.println("Successfully wrote to the file.");
//              } catch (IOException e) {
//                      System.out.println("An error occurred.");
//                      e.printStackTrace();
//              }


                if (ret == 0) {
                        System.out.println("sqls: " + sqlparser.sqlstatements.size());


                } else {
                        System.out.println("error: " + sqlparser.getErrormessage());
                        System.out.println("syntax arrays: "+sqlparser.getSyntaxErrors().size());
                        System.out.println("total errors/total sql num: "+totalError+"/"+sqlparser.sqlstatements.size());
                        return  null;
                }

                //TBaseType.dumpLogs();

                System.out.println(sqlparser.getTimeStatistics());
                return sqlparser.sqlstatements;

        }

        static void parseAndFetch(EDbVendor db, String file) {
                TGSqlParser sqlparser = new TGSqlParser(db);

                sqlparser.sqlfilename = file;

                int ret = sqlparser.parse(); // ; getrawsqlstatements()

                for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
                        System.out.println("sql type: " + sqlparser.sqlstatements.get(j).sqlstatementtype);
                        TCustomSqlStatement st = sqlparser.sqlstatements.get(j);
                        analyzeStmt(st, 1);
                        // System.out.println("last tokentext:
                        // "+sqlparser.sqlstatements.get(2).sourcetokenlist.get(sqlparser.sqlstatements.get(2).sourcetokenlist.size()-7));
                }

                if (ret == 0) {
                        System.out.println("sqls: " + sqlparser.sqlstatements.size());
                } else {
                        System.out.println("error: " + sqlparser.getErrormessage());
                }

        }

        private static void analyzeStmt(TCustomSqlStatement st, int level) {

                System.out.println("/----------------- " + level + " ----------------- ");
                System.out.println(st.getClass());
                System.out.println(st.toString());
                System.out.println("result columns:" + st.getResultColumnList().size());
                for (int i = 0; i < st.getResultColumnList().size(); i++) {
                        System.out.println("Column:" + st.getResultColumnList().getResultColumn(i).toString());
                }
                System.out.println("join tables:" + st.joins.size());
                for (int i = 0; i < st.joins.size(); i++) {
                        System.out.println("Table:" + st.joins.getJoin(i).toString());
                }
                for (int i = 0; i < st.getStatements().size(); i++) {
                        TCustomSqlStatement st1 = st.getStatements().get(i);
                        analyzeStmt(st1, level + 1);
                }
                System.out.println("----------------- " + level + " ----------------- /");

        }

        static void getrawsqlstatements(EDbVendor db, String file) {
                TGSqlParser sqlparser = new TGSqlParser(db);
                sqlparser.setSqlCharset("UTF-8");
                sqlparser.sqlfilename = file;

                int ret = -1;
                ret = sqlparser.getrawsqlstatements(); // ;

                // sqlparser.tokenizeSqltext();

//               for(int i=0;i<sqlparser.sourcetokenlist.size();i++){
//               TSourceToken st = sqlparser.sourcetokenlist.get(i);
//                       System.out.println(st.tokentype.toString()+" "+st.tokencode+" "+st.toString());
//               }

                if (ret == 0) {
                        for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
                                TCustomSqlStatement stmt = sqlparser.sqlstatements.get(j);
                                System.out.printf("sql type: %d: %s, %s(%d,%d) to %s(%d,%d)\n", j,stmt.sqlstatementtype,
                                                stmt.getStartToken().toString(), stmt.getStartToken().lineNo, stmt.getStartToken().columnNo,
                                                stmt.getEndToken().tokentype == ETokenType.ttreturn ? "linebreak"
                                                                : stmt.getEndToken().toString(),
                                                stmt.getEndToken().lineNo, stmt.getEndToken().columnNo);

                                // System.out.println("last tokentext:
                                // "+sqlparser.sqlstatements.get(2).sourcetokenlist.get(sqlparser.sqlstatements.get(2).sourcetokenlist.size()-7));
                        }
                } else {
                        System.out.println("error: " + sqlparser.getErrormessage());
                }

                StringBuilder sb = new StringBuilder();
                String sqlInfo;
                for (int j = 0; j < sqlparser.sqlstatements.size(); j++) {
                        TCustomSqlStatement stmt = sqlparser.sqlstatements.get(j);
                        sqlInfo = String.format("sql type: %d: %s, %s(%d,%d) to %s(%d,%d)\n", j,stmt.sqlstatementtype,
                                        stmt.getStartToken().toString(), stmt.getStartToken().lineNo, stmt.getStartToken().columnNo,
                                        stmt.getEndToken().tokentype == ETokenType.ttreturn ? "linebreak"
                                                        : stmt.getEndToken().toString(),
                                        stmt.getEndToken().lineNo, stmt.getEndToken().columnNo);

                        //System.out.printf(sqlInfo);
                        sb.append(sqlInfo);
                        // System.out.println("last tokentext:
                        // "+sqlparser.sqlstatements.get(2).sourcetokenlist.get(sqlparser.sqlstatements.get(2).sourcetokenlist.size()-7));
                }

//              try {
//                      FileWriter myWriter = new FileWriter("c:\\baidudisk\\works\\tech_support\\eoncollective\\aa.txt");
//                      myWriter.write(sb.toString());
//                      myWriter.close();
//                      System.out.println("Successfully wrote to the file.");
//              } catch (IOException e) {
//                      System.out.println("An error occurred.");
//                      e.printStackTrace();
//              }

                System.out.println("Total sqls:"+sqlparser.sqlstatements.size());

        }

        static public boolean moveFile(String sourcePath, String targetPath) {

                File fileToMove = new File(sourcePath);

                return fileToMove.renameTo(new File(targetPath));
        }

        static void extractSQLFromDir(String dir) {
                SqlFileList sqlfiles = new SqlFileList(dir, true,true);
                for (int k = 0; k < sqlfiles.sqlfiles.size(); k++) {
                         System.out.println(sqlfiles.sqlfiles.get(k).toString());
                        try {
                                extractSQL(sqlfiles.sqlfiles.get(k).toString());
                        } catch (IOException e) {
                                e.printStackTrace();
                        }
                }

        }
        static void parsefiles(EDbVendor db, String dir) {

                int i, e = 0, m = 0;
                TGSqlParser sqlparser = new TGSqlParser(db);
                SqlFileList sqlfiles = new SqlFileList(dir, true);
                for (int k = 0; k < sqlfiles.sqlfiles.size(); k++) {
                        sqlparser.sqlfilename = sqlfiles.sqlfiles.get(k).toString();
                        // System.out.println(sqlfiles.sqlfiles.get(k));
                        long t = System.currentTimeMillis();
                        i = sqlparser.parse();// parse();
                        System.out.println("Time Escaped: " + (System.currentTimeMillis() - t));

                        //java.lang.System.gc();

                        if (i == 0) {
                                // System.out.println(sqlfiles.sqlfiles.get(k));
                                // remove this file
                                // File f = new File(sqlfiles.sqlfiles.get(k));
                                // String destFile =
                                // "c:\\prg\\gsqlparser\\Test\\TestCases\\java\\dag\\teradata\\"
                                // + f.getName();
                                // System.out.println(destFile);

                                // if (moveFile(sqlfiles.sqlfiles.get(k),destFile)) m++;

                                // File file = new File(sqlfiles.sqlfiles.get(k));
                                //
                                // // renaming the file and moving it to a new location
                                // if (file.renameTo
                                // (new File(destFile))) {
                                // // if file copied successfully then delete the original file
                                // file.delete();
                                // System.out.println("File moved successfully:" +
                                // file.getName());
                                // } else {
                                // System.out.println("Failed to move the file");
                                // }
                        } else {
                                e++;
                                System.out.println("syntax error: " + sqlfiles.sqlfiles.get(k));
                        }
                }

                System.out.println(db.toString() + " total sql files: " + sqlfiles.sqlfiles.size() + ", error files: " + e);

        }

        static public void generateKeywordList(String keywordFile, String outFile) throws IOException {

                BufferedReader reader = null, reader2 = null;
                PrintWriter outputStream = null;
                ArrayList<String> rows = new ArrayList<String>();
                ArrayList<String> oldkeys = new ArrayList<String>();

                try {
                        reader = new BufferedReader(new FileReader("c:\\prg\\tmp\\demo.sql"));
                        reader2 = new BufferedReader(new FileReader(keywordFile));
                        outputStream = new PrintWriter(new FileWriter(outFile));

                        String file;

                        while ((file = reader2.readLine()) != null) {
                                oldkeys.add(file);
                                // String[] words = file.split("RW_");
                                // for(String str : words){
                                // if (str.trim().length() == 0) continue;
                                // oldkeys.add(str);
                                // }
                        }
                        Collections.sort(oldkeys);

                        String[] strArr = oldkeys.toArray(new String[0]);
                        for (String cur : strArr) {
                                outputStream.printf("addkeyword('%s',\tRW_%s,\tttkeyword);\n", cur.trim(), cur.trim());
                        }

                } finally {
                        if (reader != null) {
                                reader.close();
                        }
                        if (reader2 != null) {
                                reader2.close();
                        }
                        if (outputStream != null) {
                                outputStream.close();
                        }
                }
        }

}

class TSQLServerEnv extends TSQLEnv {

        public TSQLServerEnv() {
                super(EDbVendor.dbvmssql);
                initSQLEnv();
        }

        @Override
        public void initSQLEnv() {

        }
}

class THiveEnv extends TSQLEnv{

        public THiveEnv(){
                super(EDbVendor.dbvhive);
                initSQLEnv();
        }

        @Override
        public void initSQLEnv() {

                // add a new database: default database
                TSQLCatalog sqlCatalog = createSQLCatalog("pharos_business_vault");
                // hive don't have schema, we use database as a schema
                TSQLSchema sqlSchema = sqlCatalog.createSchema("default");

                //add a new table: cTab
                TSQLTable ExecutionLogStorage = sqlSchema.createTable("b_content_datamart_bv");
                ExecutionLogStorage.addColumn("a_beginn_pe");
                ExecutionLogStorage.addColumn("a_perspektive_verbrauch");
        }
}

class SQLUtil {

        public static String getFileContent(File file) {
                String charset = null;
                String sqlfilename = file.getAbsolutePath();
                int read = 0;
                try {
                        FileInputStream fr = new FileInputStream(sqlfilename);
                        byte[] bom = new byte[4];
                        fr.read(bom, 0, bom.length);

                        if ((bom[0] == (byte) 0x00) && (bom[1] == (byte) 0x00) && (bom[2] == (byte) 0xFE)
                                        && (bom[3] == (byte) 0xFF)) {
                                charset = "UTF-32BE";
                                read = 4;
                        } else if ((bom[0] == (byte) 0xFF) && (bom[1] == (byte) 0xFE) && (bom[2] == (byte) 0x00)
                                        && (bom[3] == (byte) 0x00)) {
                                charset = "UTF-32LE";
                                read = 4;
                        } else if ((bom[0] == (byte) 0xEF) && (bom[1] == (byte) 0xBB) && (bom[2] == (byte) 0xBF)) {
                                charset = "UTF-8";
                                read = 3;
                        } else if ((bom[0] == (byte) 0xFE) && (bom[1] == (byte) 0xFF)) {
                                charset = "UTF-16BE";
                                read = 2;
                        } else if ((bom[0] == (byte) 0xFF) && (bom[1] == (byte) 0xFE)) {
                                charset = "UTF-16LE";
                                read = 2;
                        } else {
                                charset = Charset.defaultCharset().name();
                                read = 0;
                        }

                        fr.close();
                } catch (IOException e) {
                        e.printStackTrace();
                }

                Long filelength = file.length();
                byte[] filecontent = new byte[filelength.intValue()];
                try {
                        InputStream in = new BufferedInputStream(new FileInputStream(sqlfilename));
                        in.read(filecontent);
                        in.close();
                } catch (IOException e) {
                        e.printStackTrace();
                }

                byte[] content = new byte[filelength.intValue() - read];
                System.arraycopy(filecontent, read, content, 0, content.length);

                try {
                        String fileContent = new String(content, charset == null ? Charset.defaultCharset().name() : charset)
                                        .trim();
                        return fileContent;
                } catch (UnsupportedEncodingException e) {
                        System.err
                                        .println("The OS does not support " + charset == null ? Charset.defaultCharset().name() : charset);
                        return null;
                }
        }

        public static String getInputStreamContent(InputStream is, boolean close) {
                try {
                        ByteArrayOutputStream out = new ByteArrayOutputStream(4096);
                        byte[] tmp = new byte[4096];
                        while (true) {
                                int r = is.read(tmp);
                                if (r == -1)
                                        break;
                                out.write(tmp, 0, r);
                        }
                        byte[] bytes = out.toByteArray();
                        if (close) {
                                is.close();
                        }
                        out.close();
                        String content = new String(bytes);
                        return content.trim();
                } catch (IOException e) {
                        e.printStackTrace();
                }
                return null;
        }

        public static String getFileContent(String filePath) {
                if (filePath == null)
                        return "";
                File file = new File(filePath);
                if (!file.exists() || file.isDirectory())
                        return "";
                return getFileContent(file);
        }

        /**
         * @param file
         *            the filePath
         *
         *            the default encoding
         * @return the UTFFileHandler.UnicodeInputStream
         * @throws Exception
         */
        public static InputStream getInputStreamWithoutBom(String file) throws IOException {
                UnicodeInputStream stream = null;
                FileInputStream fis = new FileInputStream(file);
                stream = new UnicodeInputStream(fis, null);
                return stream;
        }

        /**
         * This inputstream will recognize unicode BOM marks and will skip bytes if
         * getEncoding() method is called before any of the read(...) methods.
         * <p>
         * Usage pattern: String enc = "ISO-8859-1"; // or NULL to use systemdefault
         * FileInputStream fis = new FileInputStream(file); UnicodeInputStream uin =
         * new UnicodeInputStream(fis, enc); enc = uin.getEncoding(); // check and
         * skip possible BOM bytes InputStreamReader in; if (enc == null) in = new
         * InputStreamReader(uin); else in = new InputStreamReader(uin, enc);
         */
        public static class UnicodeInputStream extends InputStream {
                PushbackInputStream internalIn;
                boolean isInited = false;
                String defaultEnc;
                String encoding;

                private static final int BOM_SIZE = 4;

                public UnicodeInputStream(InputStream in, String defaultEnc) {
                        internalIn = new PushbackInputStream(in, BOM_SIZE);
                        this.defaultEnc = defaultEnc;
                }

                public String getDefaultEncoding() {
                        return defaultEnc;
                }

                public String getEncoding() {
                        if (!isInited) {
                                try {
                                        init();
                                } catch (IOException ex) {
                                        IllegalStateException ise = new IllegalStateException("Init method failed.");
                                        ise.initCause(ise);
                                        throw ise;
                                }
                        }
                        return encoding;
                }

                /**
                 * Read-ahead four bytes and check for BOM marks. Extra bytes are unread
                 * back to the stream, only BOM bytes are skipped.
                 */
                protected void init() throws IOException {
                        if (isInited)
                                return;

                        byte bom[] = new byte[BOM_SIZE];
                        int n, unread;
                        n = internalIn.read(bom, 0, bom.length);

                        if ((bom[0] == (byte) 0x00) && (bom[1] == (byte) 0x00) && (bom[2] == (byte) 0xFE)
                                        && (bom[3] == (byte) 0xFF)) {
                                encoding = "UTF-32BE";
                                unread = n - 4;
                        } else if ((bom[0] == (byte) 0xFF) && (bom[1] == (byte) 0xFE) && (bom[2] == (byte) 0x00)
                                        && (bom[3] == (byte) 0x00)) {
                                encoding = "UTF-32LE";
                                unread = n - 4;
                        } else if ((bom[0] == (byte) 0xEF) && (bom[1] == (byte) 0xBB) && (bom[2] == (byte) 0xBF)) {
                                encoding = "UTF-8";
                                unread = n - 3;
                        } else if ((bom[0] == (byte) 0xFE) && (bom[1] == (byte) 0xFF)) {
                                encoding = "UTF-16BE";
                                unread = n - 2;
                        } else if ((bom[0] == (byte) 0xFF) && (bom[1] == (byte) 0xFE)) {
                                encoding = "UTF-16LE";
                                unread = n - 2;
                        } else {
                                encoding = defaultEnc;
                                unread = n;
                        }

                        if (unread > 0)
                                internalIn.unread(bom, (n - unread), unread);

                        isInited = true;
                }

                public void close() throws IOException {
                        internalIn.close();
                }

                public int read() throws IOException {
                        return internalIn.read();
                }
        }

        public static void writeFileContent(File file, String sql, String charset) {
                PrintWriter pw = null;
                try {
                        //charset  = "UTF-8";
                        pw = new PrintWriter(new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), charset)));
                        pw.write(sql);
                } catch (Exception e) {
                        e.printStackTrace();
                } finally {
                        if (pw != null) {
                                pw.close();
                        }
                }

        }

}

class myTokenListHandle implements ITokenListHandle {
        // 把 ${tx_date_yyyymm} 合并为一个token，token code为 TBasetype.ident
        public boolean processTokenList(TSourceTokenList sourceTokenList){
                int startIndex = -1;
                int endIndex = -1;

                for(int i=0;i< sourceTokenList.size();i++) {
                        TSourceToken token = sourceTokenList.get(i);

                        // Check for '$' followed immediately by '{'
                        if (token.tokencode == 36) { // Check for '$'
                                if (i + 1 < sourceTokenList.size() && sourceTokenList.get(i + 1).tokencode == 123) { // Check for '{' immediately after '$'
                                        startIndex = i;
                                }
                        } else if (token.tokencode == 125 && startIndex != -1) { // Check for '}'
                                endIndex = i;

                        }


                        if (startIndex != -1 && endIndex != -1) {
                                TSourceToken firstToken = sourceTokenList.get(startIndex);
                                firstToken.tokencode = TBaseType.ident;
                                for (int j = startIndex + 1; j <= endIndex; j++) {
                                        TSourceToken st = sourceTokenList.get(j);
                                        st.tokenstatus = ETokenStatus.tsdeleted;
                                        firstToken.setString(firstToken.astext + st.astext);
                                }

                                //System.out.println("Found variable token: " + firstToken.toStringDebug());

                                startIndex = -1;
                                endIndex = -1;
                        }
                }
                return true;
        }
}

// static void teradataCmds(){
// TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvteradata);
// sqlparser.teradataCmds();
// }