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