001package gudusoft.gsqlparser.dlineage.dataflow.metadata.sqldep;
002
003import java.util.Collections;
004import java.util.Comparator;
005import java.util.LinkedHashMap;
006import java.util.List;
007import java.util.Map;
008import gudusoft.gsqlparser.EDbVendor;
009import gudusoft.gsqlparser.dlineage.dataflow.metadata.MetadataAnalyzer;
010import gudusoft.gsqlparser.dlineage.dataflow.metadata.MetadataReader;
011import gudusoft.gsqlparser.dlineage.dataflow.metadata.model.MetadataRelation;
012import gudusoft.gsqlparser.dlineage.dataflow.model.ModelBindingManager;
013import gudusoft.gsqlparser.dlineage.dataflow.model.RelationshipType;
014import gudusoft.gsqlparser.dlineage.dataflow.model.xml.column;
015import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;
016import gudusoft.gsqlparser.dlineage.dataflow.model.xml.procedure;
017import gudusoft.gsqlparser.dlineage.dataflow.model.xml.relationship;
018import gudusoft.gsqlparser.dlineage.dataflow.model.xml.sourceColumn;
019import gudusoft.gsqlparser.dlineage.dataflow.model.xml.table;
020import gudusoft.gsqlparser.dlineage.dataflow.model.xml.targetColumn;
021import gudusoft.gsqlparser.dlineage.util.Pair3;
022import gudusoft.gsqlparser.dlineage.util.DlineageUtil;
023import gudusoft.gsqlparser.sqlenv.TSQLEnv;
024import gudusoft.gsqlparser.util.SQLUtil;
025import gudusoft.gsqlparser.dlineage.util.XML2Model;
026
027public class SQLDepMetadataAnalyzer implements MetadataAnalyzer<String> {
028
029        private Map<String, procedure> procedureMap = new LinkedHashMap<String, procedure>();
030        private Map<String, table> tableMap = new LinkedHashMap<String, table>();
031        private Map<String, column> columnMap = new LinkedHashMap<String, column>();
032        private EDbVendor vendor;
033
034        @Override
035        public synchronized dataflow analyzeMetadata(EDbVendor vendor, String metadata) {
036                init(vendor);
037                List<MetadataRelation> relations = MetadataReader.read(metadata);
038                dataflow dataflow = new dataflow();
039                appendProcedures(dataflow, relations);
040                appendTables(dataflow, relations);
041                sortTableColumns(dataflow);
042                appendRelations(dataflow, relations);
043                return dataflow;
044        }
045
046        private void init(EDbVendor vendor) {
047                this.vendor = vendor;
048                procedureMap.clear();
049                tableMap.clear();
050                columnMap.clear();
051                if (ModelBindingManager.get() == null) {
052                        ModelBindingManager.set(new ModelBindingManager());
053                }
054        }
055
056        private void sortTableColumns(dataflow dataflow) {
057                if (dataflow.getTables() != null) {
058                        for (table table : dataflow.getTables()) {
059                                Collections.sort(table.getColumns(), new Comparator<column>() {
060                                        public int compare(column t1, column t2) {
061                                                if (t1.getName().equalsIgnoreCase("RelationRows"))
062                                                        return 1;
063                                                if (t2.getName().equalsIgnoreCase("RelationRows"))
064                                                        return -1;
065                                                return 0;
066                                        }
067                                });
068                        }
069                }
070
071                if (dataflow.getResultsets() != null) {
072                        for (table table : dataflow.getResultsets()) {
073                                Collections.sort(table.getColumns(), new Comparator<column>() {
074                                        public int compare(column t1, column t2) {
075                                                if (t1.getName().equalsIgnoreCase("RelationRows"))
076                                                        return 1;
077                                                if (t2.getName().equalsIgnoreCase("RelationRows"))
078                                                        return -1;
079                                                return 0;
080                                        }
081                                });
082                        }
083                }
084        }
085
086        private void appendRelations(dataflow dataflow, List<MetadataRelation> relations) {
087                for (MetadataRelation metadataRelation : relations) {
088                        relationship relation = new relationship();
089                        relation.setId(String.valueOf(++ModelBindingManager.get().RELATION_ID));
090                        if ("SQLDEP-INDIRECT".equalsIgnoreCase(metadataRelation.getSourceColumn())
091                                        || "SQLDEP-INDIRECT".equalsIgnoreCase(metadataRelation.getTargetColumn())) {
092                                relation.setType(RelationshipType.frd.name());
093                        } else {
094                                relation.setType(metadataRelation.getRelationType());
095                        }
096
097                        sourceColumn sourceColumn = new sourceColumn();
098
099                        String sourceColumnName = metadataRelation.getSourceColumn();
100                        if ("SQLDEP-INDIRECT".equalsIgnoreCase(sourceColumnName)) {
101                                sourceColumnName = "RelationRows";
102                        }
103                        sourceColumn.setCoordinate(new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash())
104                                        + "," + new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()));
105                        sourceColumn.setColumn(sourceColumnName);
106                        String sourceParentFullName = getFullName(checkDefault(metadataRelation.getSourceDb()),
107                                        checkDefault(metadataRelation.getSourceSchema()), getTableName(metadataRelation.getSourceTable()));
108                        String sourceColumnFullName = getFullName(checkDefault(metadataRelation.getSourceDb()),
109                                        checkDefault(metadataRelation.getSourceSchema()), getTableName(metadataRelation.getSourceTable()),
110                                        sourceColumnName);
111                        String sourceColumnKey = sourceColumnFullName;
112                        if(vendor != null){
113                                sourceColumnKey = SQLUtil.getIdentifierNormalColumnName(vendor, sourceColumnFullName);
114                        }
115                        String sourceParentKey = sourceParentFullName;
116                        if(vendor != null){
117                                sourceParentKey = SQLUtil.getIdentifierNormalTableName(vendor, sourceParentFullName);
118                        }
119                        if ("SQLDEP-CONSTANT".equalsIgnoreCase(metadataRelation.getSourceColumn())) {
120                                sourceColumn.setColumn_type("constant");
121                                sourceColumn.setId(String.valueOf(++ModelBindingManager.get().TABLE_COLUMN_ID));
122                        } else {
123                                sourceColumn.setId(columnMap.get(sourceColumnKey).getId());
124                                sourceColumn.setParent_name(sourceParentFullName);
125                                sourceColumn.setParent_id(tableMap.get(sourceParentKey).getId());
126                        }
127
128                        relation.getSources().add(sourceColumn);
129                        targetColumn targetColumn = new targetColumn();
130
131                        String targetColumnName = metadataRelation.getTargetColumn();
132                        if ("SQLDEP-INDIRECT".equalsIgnoreCase(targetColumnName)) {
133                                targetColumnName = "RelationRows";
134                        }
135
136                        targetColumn.setColumn(targetColumnName);
137                        targetColumn.setCoordinate(new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash())
138                                        + "," + new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()));
139                        String targetParentFullName = getFullName(checkDefault(metadataRelation.getTargetDb()),
140                                        checkDefault(metadataRelation.getTargetSchema()), getTableName(metadataRelation.getTargetTable()));
141                        String targetColumnFullName = getFullName(checkDefault(metadataRelation.getTargetDb()),
142                                        checkDefault(metadataRelation.getTargetSchema()), getTableName(metadataRelation.getTargetTable()),
143                                        targetColumnName);
144                        String targetColumnKey = targetColumnFullName;
145                        if(vendor != null){
146                                targetColumnKey = SQLUtil.getIdentifierNormalColumnName(vendor, targetColumnFullName);
147                        }
148                        String targetParentKey = targetParentFullName;
149                        if(vendor != null){
150                                targetParentKey = SQLUtil.getIdentifierNormalTableName(vendor, targetParentFullName);
151                        }
152
153                        if ("SQLDEP-CONSTANT".equalsIgnoreCase(metadataRelation.getTargetColumn())) {
154                                targetColumn.setId(String.valueOf(++ModelBindingManager.get().TABLE_COLUMN_ID));
155                        } else {
156                                targetColumn.setId(columnMap.get(targetColumnKey).getId());
157                                targetColumn.setParent_name(targetParentFullName);
158                                targetColumn.setParent_id(tableMap.get(targetParentKey).getId());
159                        }
160
161                        relation.setTarget(targetColumn);
162                        dataflow.getRelationships().add(relation);
163                }
164        }
165
166        private void appendTables(dataflow dataflow, List<MetadataRelation> relations) {
167                for (MetadataRelation relation : relations) {
168                        if (!"SQLDEP-CONSTANT".equalsIgnoreCase(relation.getSourceColumn())) {
169                                appendTable(dataflow, checkDefault(relation.getSourceDb()), checkDefault(relation.getSourceSchema()),
170                                                getTableName(relation.getSourceTable()), relation.getSourceColumn());
171                        }
172
173                        if (!"SQLDEP-CONSTANT".equalsIgnoreCase(relation.getTargetColumn())) {
174                                appendTable(dataflow, checkDefault(relation.getTargetDb()), checkDefault(relation.getTargetSchema()),
175                                                getTableName(relation.getTargetTable()), relation.getTargetColumn());
176                        }
177                }
178        }
179
180        private String getTableName(String tableName) {
181                return tableName.replaceAll("//s*//(.+//)", "");
182        }
183
184        private void appendTable(dataflow dataflow, String databaseName, String schemaName, String tableName,
185                        String columnName) {
186                String tableKey = getFullName(databaseName, schemaName, tableName);
187                if(vendor != null){
188                        tableKey = SQLUtil.getIdentifierNormalTableName(vendor, tableKey);
189                }
190                if (!tableMap.containsKey(tableKey)) {
191                        table table = new table();
192                        table.setDatabase(databaseName);
193                        table.setSchema(schemaName);
194                        table.setName(tableName);
195                        if (!SQLUtil.isEmpty(table.getSchema()) && !TSQLEnv.DEFAULT_SCHEMA_NAME.equalsIgnoreCase(table.getSchema())) {
196                                table.setName(table.getSchema() + "." + table.getName());
197                        }
198                        if (!SQLUtil.isEmpty(table.getDatabase()) && !TSQLEnv.DEFAULT_DB_NAME.equalsIgnoreCase(table.getDatabase())) {
199                                table.setName(table.getDatabase() + "." + table.getName());
200                        }
201                        table.setId(String.valueOf(++ModelBindingManager.get().TABLE_COLUMN_ID));
202                        table.setCoordinate(new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()) + ","
203                                        + new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()));
204                        if (tableName.toUpperCase().indexOf("-RES") == -1) {
205                                table.setType("table");
206                                dataflow.getTables().add(table);
207                        } else {
208                                table.setType("select_list");
209                                dataflow.getResultsets().add(table);
210                        }
211                        tableMap.put(tableKey, table);
212                }
213
214                table table = tableMap.get(tableKey);
215
216                if ("SQLDEP-INDIRECT".equalsIgnoreCase(columnName)) {
217                        columnName = "RelationRows";
218                }
219                String sourceColumnKey = getFullName(databaseName, schemaName, tableName, columnName);
220                if(vendor != null){
221                        sourceColumnKey = SQLUtil.getIdentifierNormalColumnName(vendor, sourceColumnKey);
222                }
223
224                if (!columnMap.containsKey(sourceColumnKey)) {
225                        column column = new column();
226                        column.setId(String.valueOf(++ModelBindingManager.get().TABLE_COLUMN_ID));
227                        column.setName(columnName);
228                        if ("RelationRows".equalsIgnoreCase(columnName)) {
229                                column.setSource("system");
230                        }
231                        column.setCoordinate(new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()) + ","
232                                        + new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()));
233                        table.getColumns().add(column);
234                        columnMap.put(sourceColumnKey, column);
235                }
236        }
237
238        private String checkDefault(String value) {
239                if (value.toUpperCase().indexOf("DEFAULT") != -1) {
240                        value = value.replaceAll("(?i)DEFAULT\\.", "").replaceAll("(?i)DEFAULT", "");
241                }
242                if (SQLUtil.isEmpty(value))
243                        return null;
244                return value;
245        }
246
247        private String getFullName(String... segments) {
248                StringBuilder builder = new StringBuilder();
249                for (int i = 0; i < segments.length; i++) {
250                        builder.append(segments[i]);
251                        if (i < segments.length - 1) {
252                                builder.append(".");
253                        }
254                }
255                return builder.toString().replace("null.", "");
256        }
257
258        private void appendProcedures(dataflow dataflow, List<MetadataRelation> relations) {
259                for (MetadataRelation relation : relations) {
260                        if (!SQLUtil.isEmpty(relation.getProcedureName())) {
261                                String procedureName = relation.getProcedureName();
262                                if (DlineageUtil.isProcedureExcluded(procedureName)) {
263                                        continue;
264                                }
265                                String produceName = procedureName;
266                                if(vendor != null){
267                                        produceName = SQLUtil.getIdentifierNormalTableName(vendor, produceName);
268                                }
269                                if (!procedureMap.containsKey(procedureName)) {
270                                        procedure procedure = new procedure();
271                                        procedure.setName(procedureName);
272                                        procedure.setId(String.valueOf(++ModelBindingManager.get().TABLE_COLUMN_ID));
273                                        procedure.setCoordinate(new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash())
274                                                        + "," + new Pair3<Long, Long, String>(-1L, -1L, ModelBindingManager.getGlobalHash()));
275                                        procedureMap.put(procedureName, procedure);
276                                        dataflow.getProcedures().add(procedure);
277                                }
278                        }
279                }
280        }
281
282        public static void main(String[] args) throws Exception {
283                dataflow dataflow = new SQLDepMetadataAnalyzer().analyzeMetadata(EDbVendor.dbvmssql,
284                                SQLUtil.getFileContent("D:\\5.txt"));
285                System.out.println(XML2Model.saveXML(dataflow));
286        }
287
288}