<commit_before>/*
 * Copyright 2007 - 2019 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package net.sf.jailer.ui.associationproposer;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.IdentityHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.Stack;
import java.util.UUID;

import net.sf.jailer.datamodel.Association;
import net.sf.jailer.datamodel.DataModel;
import net.sf.jailer.util.Pair;
import net.sf.jailer.util.Quoting;
import net.sf.jailer.util.SqlUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.AllComparisonExpression;
import net.sf.jsqlparser.expression.AnalyticExpression;
import net.sf.jsqlparser.expression.CaseExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitorAdapter;
import net.sf.jsqlparser.expression.NotExpression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.WhenClause;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.parser.ParseException;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Block;
import net.sf.jsqlparser.statement.Commit;
import net.sf.jsqlparser.statement.SetStatement;
import net.sf.jsqlparser.statement.StatementVisitor;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.UseStatement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.AlterView;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.execute.Execute;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.merge.Merge;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.AllColumns;
import net.sf.jsqlparser.statement.select.AllTableColumns;
import net.sf.jsqlparser.statement.select.FromItemVisitor;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.ParenthesisFromItem;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SelectItemVisitor;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.TableFunction;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.upsert.Upsert;

/**
 * Analyzes SQL statements and proposes association definitions. <br>
 * This allows to reverse-engineer the data model based on existing SQL queries. <br><br>
 * 
 * It uses this recursive procedure:
 * <ul>
 * <li>Open new scope.</li>
 * <li>Retrieve information about used tables and aliases.</li>
 * <li>Analyze sub-queries</li>
 * <li>Collect equations between columns.</li>
 * <li>Assemble association-proposals based on transitive closure of the equations.</li>
 * <li>Close the scope.</li>
 * </ul>
 * 
 * @author <NAME>
 */
public class AssociationProposer {

	private final DataModel dataModel;
	private final Map<net.sf.jailer.datamodel.Column, net.sf.jailer.datamodel.Table> columnToTable = new IdentityHashMap<net.sf.jailer.datamodel.Column, net.sf.jailer.datamodel.Table>();
	private final Set<Equation> equations = new HashSet<Equation>();
	private final Set<Association> fromDataModel = new HashSet<Association>();
	
	/**
	 * Constructor.
	 * 
	 * @param dataModel the data model
	 */
	public AssociationProposer(DataModel dataModel) {
		this.dataModel = dataModel;
		for (Entry<String, Association> e: dataModel.namedAssociations.entrySet()) {
			Association association = e.getValue();
			fromDataModel.add(association);
			addAssociation(e.getKey(), new Pair<net.sf.jailer.datamodel.Table, net.sf.jailer.datamodel.Table>(association.source, association.destination), association, false);
			association = association.reversalAssociation;
			fromDataModel.add(association);
			addAssociation(e.getKey(), new Pair<net.sf.jailer.datamodel.Table, net.sf.jailer.datamodel.Table>(association.source, association.destination), association, false);
		}
	}

	/**
	 * Analyzes a given statement.
	 * 
	 * @param sqlStatement the statement
	 * @param startLineNumber the line number at which the statement begins in the script
	 * @return an error message if statement is invalid, else <code>null</code>
	 */
	public synchronized String analyze(String sqlStatement, int startLineNumber) {
		sqlStatement = SqlUtil.removeNonMeaningfulFragments(sqlStatement);
		net.sf.jsqlparser.statement.Statement st;
		try {
			st = CCJSqlParserUtil.parse(sqlStatement);
		} catch (JSQLParserException e) {
			String prefix = "Line " + startLineNumber + ": ";
			if (e.getCause() instanceof ParseException) {
				ParseException pe = (ParseException) e.getCause();
				return prefix + pe.getMessage().replaceAll("\\s+", " ");
			}
			Throwable t = e;
			while (t.getMessage() == null && t.getCause() != null) {
				t = t.getCause();
			}
			return prefix + t.getMessage();
		}
		st.accept(new APStatementVisitor());
		closeEquations();
		proposeJoinConditions();
		sqlStatement = st.toString();
		return null;
	}

	/**
	 * Scope of a "PlainSelect".
	 */
	private class Scope {
		Map<String, String> aliasToTable = new HashMap<String, String>();
		List<Expression> expressions = new ArrayList<Expression>();
		// List<Cond><commit_msg>removed dead store<commit_after>/*
 * Copyright 2007 - 2019 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package net.sf.jailer.ui.associationproposer;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.IdentityHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.Stack;
import java.util.UUID;

import net.sf.jailer.datamodel.Association;
import net.sf.jailer.datamodel.DataModel;
import net.sf.jailer.util.Pair;
import net.sf.jailer.util.Quoting;
import net.sf.jailer.util.SqlUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.AllComparisonExpression;
import net.sf.jsqlparser.expression.AnalyticExpression;
import net.sf.jsqlparser.expression.CaseExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitorAdapter;
import net.sf.jsqlparser.expression.NotExpression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.WhenClause;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.parser.ParseException;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Block;
import net.sf.jsqlparser.statement.Commit;
import net.sf.jsqlparser.statement.SetStatement;
import net.sf.jsqlparser.statement.StatementVisitor;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.UseStatement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.index.CreateIndex;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.view.AlterView;
import net.sf.jsqlparser.statement.create.view.CreateView;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.execute.Execute;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.merge.Merge;
import net.sf.jsqlparser.statement.replace.Replace;
import net.sf.jsqlparser.statement.select.AllColumns;
import net.sf.jsqlparser.statement.select.AllTableColumns;
import net.sf.jsqlparser.statement.select.FromItemVisitor;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.ParenthesisFromItem;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SelectItemVisitor;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.TableFunction;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.upsert.Upsert;

/**
 * Analyzes SQL statements and proposes association definitions. <br>
 * This allows to reverse-engineer the data model based on existing SQL queries. <br><br>
 * 
 * It uses this recursive procedure:
 * <ul>
 * <li>Open new scope.</li>
 * <li>Retrieve information about used tables and aliases.</li>
 * <li>Analyze sub-queries</li>
 * <li>Collect equations between columns.</li>
 * <li>Assemble association-proposals based on transitive closure of the equations.</li>
 * <li>Close the scope.</li>
 * </ul>
 * 
 * @author <NAME>
 */
public class AssociationProposer {

	private final DataModel dataModel;
	private final Map<net.sf.jailer.datamodel.Column, net.sf.jailer.datamodel.Table> columnToTable = new IdentityHashMap<net.sf.jailer.datamodel.Column, net.sf.jailer.datamodel.Table>();
	private final Set<Equation> equations = new HashSet<Equation>();
	private final Set<Association> fromDataModel = new HashSet<Association>();
	
	/**
	 * Constructor.
	 * 
	 * @param dataModel the data model
	 */
	public AssociationProposer(DataModel dataModel) {
		this.dataModel = dataModel;
		for (Entry<String, Association> e: dataModel.namedAssociations.entrySet()) {
			Association association = e.getValue();
			fromDataModel.add(association);
			addAssociation(e.getKey(), new Pair<net.sf.jailer.datamodel.Table, net.sf.jailer.datamodel.Table>(association.source, association.destination), association, false);
			association = association.reversalAssociation;
			fromDataModel.add(association);
			addAssociation(e.getKey(), new Pair<net.sf.jailer.datamodel.Table, net.sf.jailer.datamodel.Table>(association.source, association.destination), association, false);
		}
	}

	/**
	 * Analyzes a given statement.
	 * 
	 * @param sqlStatement the statement
	 * @param startLineNumber the line number at which the statement begins in the script
	 * @return an error message if statement is invalid, else <code>null</code>
	 */
	public synchronized String analyze(String sqlStatement, int startLineNumber) {
		sqlStatement = SqlUtil.removeNonMeaningfulFragments(sqlStatement);
		net.sf.jsqlparser.statement.Statement st;
		try {
			st = CCJSqlParserUtil.parse(sqlStatement);
		} catch (JSQLParserException e) {
			String prefix = "Line " + startLineNumber + ": ";
			if (e.getCause() instanceof ParseException) {
				ParseException pe = (ParseException) e.getCause();
				return prefix + pe.getMessage().replaceAll("\\s+", " ");
			}
			Throwable t = e;
			while (t.getMessage() == null && t.getCause() != null) {
				t = t.getCause();
			}
			return prefix + t.getMessage();
		}
		st.accept(new APStatementVisitor());
		closeEquations();
		proposeJoinConditions();
		return null;
	}

	/**
	 * Scope of a "PlainSelect".
	 */
	private class Scope {
		Map<String, String> aliasToTable = new HashMap<String, String>();
		List<Expression> expressions = new ArrayList<Expression>();
		// List<Cond>
{'fraction_non_alphanumeric': 0.11392946205913787, 'fraction_numerical': 0.0017100106875667972, 'mean_word_length': 5.2133687472332895, 'pattern_counts': {'":': 2, '<': 71, '<?xml version=': 0, '>': 71, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1118277', 'n_tokens_mistral': 4468, 'n_tokens_neox': 4273, 'n_words': 913}