解析Oracle sql,生成字段关系:
package org.example;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.util.XML2Model;
public class OracleTest {
public static void main(String[] args) throws Exception {
String sqltext = "-- oracle\nINSERT INTO deptsal\n" +
" (dept_no,\n" +
" dept_name,\n" +
" salary)\n" +
"SELECT d.deptno,\n" +
" d.dname,\n" +
" SUM(e.sal + Nvl(e.comm, 0)) AS sal\n" +
"FROM dept d\n" +
" left join (SELECT *\n" +
" FROM emp\n" +
" WHERE hiredate > DATE '1980-01-01') e\n" +
" ON e.deptno = d.deptno\n" +
"GROUP BY d.deptno,\n" +
" d.dname; ";
System.out.println(sqltext);
System.out.println();
System.out.println();
System.out.println("-------------------------gen lineage--------------------------------------");
DataFlowAnalyzer dlineage = new DataFlowAnalyzer(sqltext, EDbVendor.dbvoracle, false);
dlineage.generateDataFlow();
String result = XML2Model.saveXML(dlineage.getDataFlow());
System.out.println(result);
}
}
执行结果:
-- oracle
INSERT INTO deptsal
(dept_no,
dept_name,
salary)
SELECT d.deptno,
d.dname,
SUM(e.sal + Nvl(e.comm, 0)) AS sal
FROM dept d
left join (SELECT *
FROM emp
WHERE hiredate > DATE '1980-01-01') e
ON e.deptno = d.deptno
GROUP BY d.deptno,
d.dname;
-------------------------gen lineage--------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dlineage>
<process id="8" name="Query Insert-1" procedureName="batchQueries" queryHashId="418d20458fa9befd48800a0662605e43" type="sstinsert" coordinate="[2,1,0],[15,19,0]"/>
<table id="4" name="deptsal" type="table" processIds="8" coordinate="[2,13,0],[2,20,0]">
<column id="5" name="dept_no" coordinate="[3,14,0],[3,21,0]"/>
<column id="6" name="dept_name" coordinate="[4,14,0],[4,23,0]"/>
<column id="7" name="salary" coordinate="[5,14,0],[5,20,0]"/>
<column id="3" name="RelationRows" coordinate="[2,13,0],[2,20,0]" source="system"/>
</table>
<table id="12" name="dept" alias="d" type="table" coordinate="[9,8,0],[9,14,0]">
<column id="13" name="deptno" coordinate="[13,29,0],[13,37,0]"/>
<column id="14" name="dname" coordinate="[7,8,0],[7,15,0]"/>
</table>
<table id="20" name="emp" type="table" coordinate="[11,26,0],[11,29,0]">
<column id="21" name="hiredate" coordinate="[12,26,0],[12,34,0]"/>
<column id="22" name="deptno" coordinate="[13,18,0],[13,26,0]"/>
<column id="23" name="sal" coordinate="[8,12,0],[8,17,0]"/>
<column id="24" name="comm" coordinate="[8,24,0],[8,30,0]"/>
<column id="26" name="*" coordinate="[10,26,0],[10,27,0]"/>
</table>
<resultset id="16" name="RESULT_OF_E-1" type="select_list" coordinate="[12,56,0],[12,57,0]">
<column id="25" name="*" coordinate="[10,26,0],[10,27,0]"/>
<column id="39" name="deptno" coordinate="[13,18,0],[13,26,0]"/>
<column id="15" name="RelationRows" coordinate="[12,56,0],[12,57,0]" source="system"/>
<column id="25_0" name="SAL" coordinate="[10,26,0],[10,27,0]"/>
<column id="25_1" name="COMM" coordinate="[10,26,0],[10,27,0]"/>
<column id="25_2" name="HIREDATE" coordinate="[10,26,0],[10,27,0]"/>
</resultset>
<resultset id="28" name="INSERT-SELECT-1" type="insert-select" coordinate="[6,8,0],[8,42,0]">
<column id="29" name="deptno" coordinate="[6,8,0],[6,16,0]"/>
<column id="30" name="dname" coordinate="[7,8,0],[7,15,0]"/>
<column id="31" name="sal" coordinate="[8,8,0],[8,42,0]"/>
<column id="27" name="RelationRows" coordinate="[6,8,0],[8,42,0]" source="system"/>
</resultset>
<resultset id="33" name="SUM" type="function" coordinate="[8,8,0],[8,35,0]">
<column id="34" name="SUM" coordinate="[8,8,0],[8,11,0]"/>
<column id="32" name="RelationRows" coordinate="[8,8,0],[8,35,0]" source="system"/>
</resultset>
<resultset id="36" name="Nvl" type="function" coordinate="[8,20,0],[8,34,0]">
<column id="37" name="Nvl" coordinate="[8,20,0],[8,23,0]"/>
<column id="35" name="RelationRows" coordinate="[8,20,0],[8,34,0]" source="system"/>
</resultset>
<relationship id="1_0" type="fdd" effectType="select">
<target id="25_0" column="SAL" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[10,26,0],[10,27,0]"/>
<source id="23" column="sal" parent_id="20" parent_name="emp" coordinate="[8,12,0],[8,17,0]"/>
</relationship>
<relationship id="1_1" type="fdd" effectType="select">
<target id="25_1" column="COMM" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[10,26,0],[10,27,0]"/>
<source id="24" column="comm" parent_id="20" parent_name="emp" coordinate="[8,24,0],[8,30,0]"/>
</relationship>
<relationship id="1_2" type="fdd" effectType="select">
<target id="25_2" column="HIREDATE" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[10,26,0],[10,27,0]"/>
<source id="21" column="hiredate" parent_id="20" parent_name="emp" coordinate="[12,26,0],[12,34,0]"/>
</relationship>
<relationship id="1_3" type="fdd" effectType="select">
<target id="39" column="DEPTNO" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[13,18,0],[13,26,0]"/>
<source id="22" column="deptno" parent_id="20" parent_name="emp" coordinate="[13,18,0],[13,26,0]"/>
</relationship>
<relationship id="1" type="fdd" effectType="select">
<target id="25" column="*" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[10,26,0],[10,27,0]"/>
<source id="21" column="hiredate" parent_id="20" parent_name="emp" coordinate="[12,26,0],[12,34,0]"/>
<source id="22" column="deptno" parent_id="20" parent_name="emp" coordinate="[13,18,0],[13,26,0]"/>
<source id="23" column="sal" parent_id="20" parent_name="emp" coordinate="[8,12,0],[8,17,0]"/>
<source id="24" column="comm" parent_id="20" parent_name="emp" coordinate="[8,24,0],[8,30,0]"/>
<source id="26" column="*" parent_id="20" parent_name="emp" coordinate="[10,26,0],[10,27,0]"/>
</relationship>
<relationship id="4" type="fdd" effectType="select">
<target id="29" column="deptno" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[6,8,0],[6,16,0]"/>
<source id="13" column="deptno" parent_id="12" parent_name="dept" parent_alias="d" coordinate="[13,29,0],[13,37,0]"/>
</relationship>
<relationship id="5" type="fdd" effectType="select">
<target id="30" column="dname" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[7,8,0],[7,15,0]"/>
<source id="14" column="dname" parent_id="12" parent_name="dept" parent_alias="d" coordinate="[7,8,0],[7,15,0]"/>
</relationship>
<relationship id="6" type="fdd" effectType="select">
<target id="31" column="sal" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[8,8,0],[8,42,0]"/>
<source id="34" column="SUM" parent_id="33" parent_name="SUM" coordinate="[8,8,0],[8,11,0]"/>
</relationship>
<relationship id="7" type="fdd" effectType="function">
<target id="34" column="SUM" parent_id="33" parent_name="SUM" coordinate="[8,8,0],[8,11,0]"/>
<source id="37" column="Nvl" parent_id="36" parent_name="Nvl" coordinate="[8,20,0],[8,23,0]"/>
</relationship>
<relationship id="8" type="fdd" effectType="function">
<target id="37" column="Nvl" parent_id="36" parent_name="Nvl" coordinate="[8,20,0],[8,23,0]"/>
<source id="25_1" column="COMM" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[10,26,0],[10,27,0]"/>
</relationship>
<relationship id="11" type="fdd" function="Nvl" effectType="function">
<target id="34" column="SUM" parent_id="33" parent_name="SUM" coordinate="[8,8,0],[8,11,0]"/>
<source id="25_0" column="SAL" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[10,26,0],[10,27,0]"/>
</relationship>
<relationship id="17" type="fdd" effectType="insert" processId="8" processType="sstinsert">
<target id="5" column="dept_no" parent_id="4" parent_name="deptsal" coordinate="[3,14,0],[3,21,0]"/>
<source id="29" column="deptno" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[6,8,0],[6,16,0]"/>
</relationship>
<relationship id="18" type="fdd" effectType="insert" processId="8" processType="sstinsert">
<target id="6" column="dept_name" parent_id="4" parent_name="deptsal" coordinate="[4,14,0],[4,23,0]"/>
<source id="30" column="dname" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[7,8,0],[7,15,0]"/>
</relationship>
<relationship id="19" type="fdd" effectType="insert" processId="8" processType="sstinsert">
<target id="7" column="salary" parent_id="4" parent_name="deptsal" coordinate="[5,14,0],[5,20,0]"/>
<source id="31" column="sal" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[8,8,0],[8,42,0]"/>
</relationship>
<relationship id="13" type="fdr" function="SUM" effectType="function">
<target id="34" column="SUM" parent_id="33" parent_name="SUM" coordinate="[8,8,0],[8,11,0]"/>
<source id="13" column="deptno" parent_id="12" parent_name="dept" parent_alias="d" coordinate="[13,29,0],[13,37,0]"/>
</relationship>
<relationship id="14" type="fdr" function="SUM" effectType="function">
<target id="34" column="SUM" parent_id="33" parent_name="SUM" coordinate="[8,8,0],[8,11,0]"/>
<source id="14" column="dname" parent_id="12" parent_name="dept" parent_alias="d" coordinate="[7,8,0],[7,15,0]"/>
</relationship>
<relationship id="2" type="fdr" effectType="select" clause="where">
<target id="15" column="RelationRows" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[12,56,0],[12,57,0]" source="system"/>
<source id="21" column="hiredate" parent_id="20" parent_name="emp" coordinate="[12,26,0],[12,34,0]" clauseType="where"/>
</relationship>
<relationship id="3" type="fdr" effectType="select">
<target id="27" column="RelationRows" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[6,8,0],[8,42,0]" source="system"/>
<source id="15" column="RelationRows" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[12,56,0],[12,57,0]" source="system"/>
</relationship>
<relationship id="9" type="fdr">
<target id="35" column="RelationRows" parent_id="36" parent_name="Nvl" coordinate="[8,20,0],[8,34,0]" source="system"/>
<source id="15" column="RelationRows" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[12,56,0],[12,57,0]" source="system"/>
</relationship>
<relationship id="12" type="fdr">
<target id="32" column="RelationRows" parent_id="33" parent_name="SUM" coordinate="[8,8,0],[8,35,0]" source="system"/>
<source id="15" column="RelationRows" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[12,56,0],[12,57,0]" source="system"/>
</relationship>
<relationship id="15" type="fdr" effectType="select" clause="on">
<target id="27" column="RelationRows" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[6,8,0],[8,42,0]" source="system"/>
<source id="39" column="deptno" parent_id="16" parent_name="RESULT_OF_E-1" coordinate="[13,18,0],[13,26,0]"/>
<source id="13" column="deptno" parent_id="12" parent_name="dept" parent_alias="d" coordinate="[13,29,0],[13,37,0]" clauseType="joinCondition"/>
</relationship>
<relationship id="16" type="fdr" effectType="insert">
<target id="3" column="RelationRows" parent_id="4" parent_name="deptsal" coordinate="[2,13,0],[2,20,0]" source="system"/>
<source id="27" column="RelationRows" parent_id="28" parent_name="INSERT-SELECT-1" coordinate="[6,8,0],[8,42,0]" source="system"/>
</relationship>
</dlineage>