package com.jdk8new.stream;

import java.io.*;
import java.util.Arrays;

public class ReadDb {
    public static void main(String[] args) throws Exception {
        createmssql("E:\\viewhigh\\hosp_product\\DIP\\1.dip_oracle.sql","E:\\viewhigh\\hosp_product\\DIP\\1.dip_mssql_out.sql");
        //createOracleSql("E:\\viewhigh\\hosp_product\\DIP\\1.dip_oracle.sql","E:\\viewhigh\\hosp_product\\DIP\\1.dip_oracle_out.sql");
    }

    private static void createmssql(String inPath,String outPath) throws Exception{
        //读取文件(字符流)
        BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(inPath),"UTF-8"));
        //BufferedReader in = new BufferedReader(new FileReader("d:\\1.txt")));
        //写入相应的文件
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outPath),"UTF-8"));
        //BufferedWriter out = new BufferedWriter(new FileWriter("d:\\2.txt"))；
        //读取数据
        //循环取出数据
        String str = null;
        while ((str = in.readLine()) != null) {
            str=str.replace("VARCHAR2","VARCHAR").replace("NUMBER","NUMERIC").replace("TIMESTAMP(6)","DATETIME").replace("RAW(16)","replace(newid(),'-','')");
            String[] split = str.split("\\s+");
            System.out.println(Arrays.toString(split));
            if(str.startsWith("DROP")){
                out.write("END;");
                out.newLine();
                out.write("GO");
                out.newLine();
                out.write("IF NOT EXISTS(SELECT * FROM sysobjects WHERE id =  object_id(N'["+split[2].replace(";","")+"]') AND xtype = 'U') begin");
                out.newLine();
            }else if(str.startsWith("COMMENT ON TABLE")){
                out.write("execute sp_addextendedproperty 'MS_Description',"+split[5].replace(";","")+",'user','dbo','table','"+split[3]+"',null,null;");
                out.newLine();
            }else if(str.startsWith("COMMENT ON COLUMN")){
                String[] t_c=split[3].split("\\.");
                System.out.println("tc"+Arrays.toString(t_c));
                out.write("execute sp_addextendedproperty 'MS_Description',"+split[5].replace(";","")+",'user','dbo','table','"+t_c[0]+"','column','"+t_c[1]+"';");
                out.newLine();
            }else{
                out.write(str);
                out.newLine();
            }

        }
        //清楚缓存
        out.flush();
        //关闭流
        in.close();
        out.close();
    }

    private static void createOracleSql(String inPath,String outPath) throws Exception{
        //读取文件(字符流)
        BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(inPath),"UTF-8"));
        //BufferedReader in = new BufferedReader(new FileReader("d:\\1.txt")));
        //写入相应的文件
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outPath),"UTF-8"));
        //BufferedWriter out = new BufferedWriter(new FileWriter("d:\\2.txt"))；
        //读取数据
        //循环取出数据
        String str = null;
        boolean tbbody=false;
        while ((str = in.readLine()) != null) {

            String[] split = str.split("\\s+");
            System.out.println(Arrays.toString(split));
            if(str.startsWith("DROP")){
                tbbody=true;
                out.write("END IF;");
                out.newLine();
                out.write("END;");
                out.newLine();
                out.write("/");
                out.newLine();
                out.write("DECLARE num NUMBER;");
                out.newLine();
                out.write("BEGIN");
                out.newLine();
                out.write("select count(1) into num from user_tables t where t.TABLE_NAME = UPPER('"+split[2].replace(";","")+"');");
                out.newLine();
                out.write("if num<1 THEN");
                out.newLine();
                out.write("EXECUTE IMMEDIATE '");
                out.newLine();
                //正在写入tbbody
            }else if(tbbody){
                if(str.trim().endsWith(";")){
                    out.write(str.replace(";","")+"';");
                    tbbody=false;
                }else{
                    out.write(str);
                }
                out.newLine();
            }else{
                if(str.trim().equals("")){
                    continue;
                }
                out.write("EXECUTE IMMEDIATE '"+str.replace(";","").replace("'","''")+"';");
                out.newLine();
            }
        }
        //清楚缓存
        out.flush();
        //关闭流
        in.close();
        out.close();
    }
}