org.nuiton.topia.templates.sql.plan.SqlHelper Maven / Gradle / Ivy

Go to download
package org.nuiton.topia.templates.sql.plan;

/*-
 * #%L
 * Toolkit :: Templates
 * %%
 * Copyright (C) 2017 - 2024 Ultreia.io
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public
 * License along with this program.  If not, see
 * .
 * #L%
 */

import org.nuiton.topia.persistence.TopiaEntity;
import org.nuiton.topia.service.sql.model.AbstractTopiaEntitySqlTable;
import org.nuiton.topia.service.sql.model.TopiaEntitySqlDescriptor;
import org.nuiton.topia.service.sql.model.TopiaEntitySqlSelector;
import org.nuiton.topia.service.sql.model.TopiaEntitySqlTable;

import java.util.LinkedList;
import java.util.List;

/**
 * Created on 08/03/2022.
 *
 * @author Tony Chemit - [email protected]
 * @since 1.0.69
 */
public class SqlHelper {
    public static final String FROM_SQL = "%s WHERE %s";
    public static final String FROM_SQL_NO_WHERE = "%s";
    public static final String SQL_WHERE_CLAUSE_ALIAS = "%1$s.%2$s";
    public static final String DELETE_REVERSE_ASSOCIATION_STATEMENT = "UPDATE %1$s.%2$s SET %3$s = NULL WHERE %4$s";
    public static final String DELETE_FROM_SELECTOR_STATEMENT = "DELETE FROM %1$s.%2$s WHERE %2$s.%3$s IN (%4$s);";
    public static final String DELETE_FROM_SELECTOR_SIMPLIFIED_STATEMENT = "DELETE FROM %1$s.%2$s WHERE %3$s;";

    public static String newFrom(TopiaEntitySqlSelector selector) {
        String joinClauses = selector.getJoinClauses();
        if (!joinClauses.isEmpty()) {
            joinClauses = " " + joinClauses;
        }
        String whereClause = selector.getWhereClauseWithValuesPrototype();
        return String.format(FROM_SQL, joinClauses, whereClause).trim();
    }

    public static String newFromWithNoWhere(TopiaEntitySqlSelector selector) {
        String joinClauses = selector.getJoinClauses();
        if (!joinClauses.isEmpty()) {
            joinClauses = " " + joinClauses;
        }
        return String.format(FROM_SQL_NO_WHERE, joinClauses.trim()).trim();
    }

    public static String newDeleteStatementSql(TopiaEntitySqlTable table, boolean noPath, TopiaEntitySqlSelector selector) {
        if (noPath || selector.getJoinClauses().isEmpty()) {
            String selectClause = selector.getWhereClauseWithValuesPrototype();
            return String.format(DELETE_FROM_SELECTOR_SIMPLIFIED_STATEMENT, table.getSchemaName(), table.getTableName(), selectClause);
        }
        String selectClause = table.generatePrototype(selector, table.getTableName() + "." + table.getJoinColumnName());
        return String.format(DELETE_FROM_SELECTOR_STATEMENT, table.getSchemaName(), table.getTableName(), table.getJoinColumnName(), selectClause);
    }

    public static String newDeleteReverseAssociationStatementSql(AbstractTopiaEntitySqlTable table, String whereClaus) {
        return String.format(DELETE_REVERSE_ASSOCIATION_STATEMENT, table.getSchemaName(), table.getTableName(), table.getJoinColumnName(), whereClaus);
    }

    public static String newDeleteReverseCompositionStatementSql(AbstractTopiaEntitySqlTable table, String whereClause) {
        return String.format(DELETE_FROM_SELECTOR_SIMPLIFIED_STATEMENT, table.getSchemaName(), table.getTableName(), whereClause);
    }

    public static String newDeleteReverseCompositionStatementSqlWithJoin(AbstractTopiaEntitySqlTable table, TopiaEntitySqlSelector selector, String whereClause) {
        String selectClause = table.generatePrototype(selector, table.getTableName() + "." + table.getJoinColumnName());
        return String.format(DELETE_FROM_SELECTOR_STATEMENT, table.getSchemaName(), table.getTableName(), table.getJoinColumnName(), String.format(selectClause, whereClause));
    }

    public static String newDeleteStatementSql(TopiaEntitySqlDescriptor descriptor, AbstractTopiaEntitySqlTable associationTable, TopiaEntitySqlSelector mainTableSelector, TopiaEntitySqlSelector associationSelector) {
        if (descriptor.isNoPath() || associationSelector.getJoinClauses().isEmpty()) {
            String selectClause = mainTableSelector.getWhereClauseWithValuesPrototype(associationTable.getTableName() + "." + associationTable.getJoinColumnName());
            return String.format(DELETE_FROM_SELECTOR_SIMPLIFIED_STATEMENT, associationTable.getSchemaName(), associationTable.getTableName(), selectClause);
        }
        TopiaEntitySqlTable table = descriptor.getTable();
        String selectClause = associationTable.generatePrototype(mainTableSelector, table.getTableName() + "." + table.getJoinColumnName());
        return String.format(DELETE_FROM_SELECTOR_STATEMENT, associationTable.getSchemaName(), associationTable.getTableName(), associationTable.getJoinColumnName(), selectClause);
    }

    public static String newDeleteStatementSql(TopiaEntitySqlTable mainTable, TopiaEntitySqlTable table, AbstractTopiaEntitySqlTable associationTable, TopiaEntitySqlSelector mainTableSelector) {
        if (mainTableSelector.getJoinClauses().isEmpty()) {
            if (mainTable.equals(table)) {
                String selectClause = mainTableSelector.getWhereClauseWithValuesPrototype(associationTable.getTableName() + "." + associationTable.getJoinColumnName());
                return String.format(DELETE_FROM_SELECTOR_SIMPLIFIED_STATEMENT, associationTable.getSchemaName(), associationTable.getTableName(), selectClause);
            }
            String whereClauseAlias = mainTableSelector.getWhereClauseAlias();
            String select = whereClauseAlias.substring(0, whereClauseAlias.indexOf(".") + 1) + TopiaEntity.PROPERTY_TOPIA_ID;
            String selectClause = mainTableSelector.generatePrototype(select);
            return String.format(DELETE_FROM_SELECTOR_STATEMENT, associationTable.getSchemaName(), associationTable.getTableName(), associationTable.getJoinColumnName(), selectClause);
        }
        String selectClause = associationTable.generatePrototype(mainTableSelector, table.getTableName() + "." + table.getJoinColumnName());
        return String.format(DELETE_FROM_SELECTOR_STATEMENT, associationTable.getSchemaName(), associationTable.getTableName(), associationTable.getJoinColumnName(), selectClause);
    }

    public static TopiaEntitySqlSelector newMainSelector(AbstractTopiaEntitySqlTable table) {
        // same table, so direct selector
        return new TopiaEntitySqlSelector(table.getSchemaAndTableName(), String.format(SQL_WHERE_CLAUSE_ALIAS, table.getTableName(), TopiaEntity.PROPERTY_TOPIA_ID), "", false);
    }

    public static TopiaEntitySqlSelector newSelector(TopiaEntitySqlDescriptor mainDescriptor, AbstractTopiaEntitySqlTable table) {
        if (table.equals(mainDescriptor.getTable())) {
            // same table, so direct selector
            return new TopiaEntitySqlSelector(table.getSchemaAndTableName(), String.format(SQL_WHERE_CLAUSE_ALIAS, table.getTableName(), TopiaEntity.PROPERTY_TOPIA_ID), "", false);
        }
        List selectors = table.getSelectors();
        //FIXME Should get it from model this only works if dbColumnName has not be changed...
        //FIXME Dangerous!!!
        String columnName = mainDescriptor.getTable().getTableName();
        String schemaAndTableNames = mainDescriptor.getTable().getSchemaAndTableName();
        TopiaEntitySqlSelector selector = null;
        if (selectors.size() == 1) {
            selector = selectors.get(0);
        } else {
            // got more than one selector must find out the correct one
            for (TopiaEntitySqlSelector candidate : selectors) {
                String joinClauses = candidate.getJoinClauses();
                if (joinClauses.contains(" " + schemaAndTableNames)) {
                    selector = candidate;
                    break;
                }
            }
        }
        if (selector == null) {
            throw new IllegalStateException(String.format("Need at least one selector not reverse for table: %s from %s", table, mainDescriptor.getTable()));
        }
        TopiaEntitySqlSelector.Builder builder = TopiaEntitySqlSelector.builder().setFromClause(selector.getFromClause());
        String joinClauses = selector.getJoinClauses();
        String[] parts = joinClauses.split("INNER JOIN\\s");
        List newJoinClauses = new LinkedList<>();
        String lastSelector = null;
        for (int i = 1; i < parts.length; i++) {
            // part is XXX.YYY ON YYY.topiaId = VVV.WWW (normal)
            // part is XXX.YYY ON YYY.ZZZ = VVV.ZZZ (reverse)
            String part = parts[i].trim();
            String[] sides = part.substring(part.indexOf(" ON ") + 3).trim().split("\\s*=\\s*");
            boolean reverseSelector = !part.contains("." + TopiaEntity.PROPERTY_TOPIA_ID);// || sides[1].contains("." + TopiaEntity.PROPERTY_TOPIA_ID);
            boolean onFirstPart = reverseSelector && sides[0].contains("." + columnName);
            boolean onLastPart = sides[1].contains("." + columnName);
            boolean onMainTable = !reverseSelector && part.startsWith(schemaAndTableNames + " ");
            if (onFirstPart) {
                // stop here
                lastSelector = sides[1];
                break;
            }
            if (!onMainTable || !onLastPart) {
                newJoinClauses.add(String.format(" INNER JOIN %s", part));
            }
            if (reverseSelector) {
                if (onLastPart) {
                    // stop here
                    lastSelector = sides[1];
                    break;
                }
            } else {
                if (onMainTable) {
                    // stop here
                    if (onLastPart) {
                        lastSelector = sides[1];
                    }
//                    else { lastSelector = null; }
                    break;
                }
            }
        }
        if (lastSelector == null) {
            lastSelector = mainDescriptor.getTable().getTableName() + "." + TopiaEntity.PROPERTY_TOPIA_ID;
        }
        String whereClauseAlias = lastSelector;
        builder.addJoinClause(String.join(" ", newJoinClauses)).setWhereClauseAlias(whereClauseAlias);
        if (selector.isReverseSelector()) {
            builder.reverseSelector();
        }
        return builder.build();
    }
}