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