001import java.io.File;
002import java.nio.charset.StandardCharsets;
003import java.nio.file.Files;
004import java.nio.file.Path;
005import java.nio.file.Paths;
006
007import gudusoft.gsqlparser.EDbObjectType;
008import gudusoft.gsqlparser.EDbVendor;
009import gudusoft.gsqlparser.TBaseType;
010import gudusoft.gsqlparser.TCustomSqlStatement;
011import gudusoft.gsqlparser.TGSqlParser;
012import gudusoft.gsqlparser.nodes.TAliasClause;
013import gudusoft.gsqlparser.nodes.TExpression;
014import gudusoft.gsqlparser.nodes.TObjectName;
015import gudusoft.gsqlparser.nodes.TResultColumn;
016import gudusoft.gsqlparser.nodes.TResultColumnList;
017import gudusoft.gsqlparser.pp.para.GFmtOpt;
018import gudusoft.gsqlparser.pp.para.GFmtOptFactory;
019import gudusoft.gsqlparser.pp.stmtformatter.FormatterFactory;
020import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
021
022public class T123 {
023    public static final EDbVendor DIALECT = EDbVendor.dbvpostgresql;
024    public static final String filename = "C:\\prg\\tmp\\demo.sql";
025
026    public static void main(String[] args) throws Exception {
027        System.out.println(String.format("Version: %s, Release date: %s, Full version:%s",TBaseType.versionid, TBaseType.releaseDate, String.valueOf(TBaseType.full_edition)));
028
029        File file=new File(filename);
030        if (!file.exists()){
031            System.out.println("File not exists:"+filename);
032            return;
033        }
034
035        execute();
036
037        System.out.println("--------------------------");
038        System.out.println("Done!");
039    }
040
041    private static String getResource(String fileName) throws Exception
042    {
043        System.out.println("Reading from "+fileName);
044        Path path = Paths.get(fileName);
045        byte[] bytes = Files.readAllBytes(path);
046        return new String(bytes,StandardCharsets.UTF_8);
047    }
048
049    public static void execute() throws Exception {
050        long t = System.currentTimeMillis();
051
052        String sqltext = getResource(filename);
053        System.out.println("--------------------------");
054        System.out.println("Dialect : " + DIALECT.toString());
055        System.out.println("SQL ("+sqltext.length()+"): \r\n" + sqltext);
056        System.out.println("--------------------------");
057        System.out.println();
058
059        TGSqlParser sqlparser = new TGSqlParser(DIALECT);
060        sqlparser.sqltext = sqltext;
061        int ret = sqlparser.parse();
062
063        if (ret == 0) {
064            GFmtOpt option = GFmtOptFactory.newInstance();
065            String result = FormatterFactory.pp(sqlparser, option);
066            System.out.println("formatted: \r\n" + result);
067            System.out.println("--------------------------");
068            System.out.println();
069
070            String finalsql = "";
071            for (int k = 0; k < sqlparser.sqlstatements.size(); k++) {
072                TCustomSqlStatement stmt = sqlparser.sqlstatements.get(k);
073
074                if (stmt instanceof TSelectSqlStatement) {
075                    TSelectSqlStatement select = (TSelectSqlStatement) stmt;
076
077                    TResultColumnList resultList = select.getResultColumnList();
078                    for (int listIndex = resultList.size()-1; listIndex >= 0; listIndex--) {
079                        System.out.println("col-"+listIndex+" : ("+resultList.getResultColumn(listIndex).getExpr().getExpressionType().toString()+") "+resultList.getResultColumn(listIndex));
080                        resultList.removeResultColumn(listIndex);
081                    }
082
083                    TResultColumn resultColumn = new TResultColumn();
084
085                    String finalAlias = "xxx";
086                    TObjectName aliasName = TObjectName.createObjectName(sqlparser.getDbVendor(),EDbObjectType.column_alias,finalAlias);
087                    if (aliasName!=null) {
088                        TAliasClause aliasClause = new TAliasClause();
089                        aliasClause.setAliasName(aliasName);
090                        resultColumn.setAliasClause(aliasClause);
091                    }
092                    System.out.println("--------------------------");
093                    System.out.println();
094
095                    String finalColm = "(select pg_catalog.array_agg(inhparent::bigint order by inhseqno)::varchar from pg_catalog.pg_inherits where T.oid = inhrelid)";
096
097                    TSelectSqlStatement colSelect = TGSqlParser.parseSubquery(sqlparser.getDbVendor(),finalColm);
098                    String colSelectStr = colSelect.toScript();
099                    System.out.println();
100                    System.out.println("subquery ("+colSelectStr.length()+"): \r\n" + colSelectStr);
101
102                    TObjectName exprName = TObjectName.createObjectName(sqlparser.getDbVendor(),EDbObjectType.unknown,colSelectStr);
103                    if (exprName!=null) {
104                        TExpression expr = new TExpression(exprName);
105                        resultColumn.setExpr(expr);
106                        resultList.addResultColumn(resultColumn);
107                    }
108
109                    String temp = select.toScript().trim().replaceAll("(?:\\n|\\r|\\t)", " ").replaceAll(" +"," ");;
110                    System.out.println();
111                    System.out.println("selectX ("+temp.length()+"): \r\n" + temp);
112
113                    finalsql += " "+temp;
114                }
115                else {
116                    String temp = stmt.toString().trim();
117                    System.out.println();
118                    System.out.println("selectA: \r\n" + temp);
119
120                    finalsql += " "+temp;
121                }
122            }
123
124            finalsql = finalsql.trim();
125            System.out.println("--------------------------");
126            System.out.println();
127            System.out.println("FINAL ("+finalsql.length()+"): \r\n" + finalsql);
128
129            TGSqlParser sqlparserX = new TGSqlParser(DIALECT);
130            sqlparserX.sqltext = finalsql;
131            ret = sqlparserX.parse();
132            if (ret != 0) {
133                System.out.println();
134                System.out.println("---> "+sqlparserX.getErrormessage());
135            }
136        } else {
137            System.out.println(sqlparser.getErrormessage());
138        }
139
140        System.out.println();
141        System.out.println("--------------------------");
142        System.out.println("Time Escaped: " + (System.currentTimeMillis() - t));
143    }
144
145}