<commit_before>/*
 * Copyright (c) 2022, WSO2 LLC. (http://www.wso2.org).
 *
 * WSO2 LLC. licenses this file to you 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 org.wso2.carbon.identity.application.mgt.dao.impl;

/**
 * This class contains default SQL queries
 * <p/>
 * TODO : Make the queries configurable from a file TODO : Use transactions and joins
 * SQL queries are internal implementation of the DAO layer and should not be exposed to components outside
 */
public class ApplicationMgtDBQueries {

    // STORE Queries
    public static final String STORE_BASIC_APPINFO = "INSERT INTO SP_APP (TENANT_ID, APP_NAME, USER_STORE, USERNAME, " +
            "DESCRIPTION, AUTH_TYPE, IS_USE_TENANT_DOMAIN_SUBJECT, ENABLE_AUTHORIZATION,IS_USE_USER_DOMAIN_SUBJECT, " +
            "UUID, IMAGE_URL, ACCESS_URL) " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    public static final String UPDATE_BASIC_APPINFO = "UPDATE SP_APP SET APP_NAME=:APP_NAME;, " +
            "DESCRIPTION=:DESCRIPTION;, IS_SAAS_APP=:IS_SAAS_APP;, IS_DISCOVERABLE=:IS_DISCOVERABLE;, " +
            "IMAGE_URL=:IMAGE_URL;, ACCESS_URL=:ACCESS_URL; WHERE TENANT_ID=:TENANT_ID; AND ID=:ID;";
    public static final String UPDATE_BASIC_APPINFO_WITH_OWNER_UPDATE = "UPDATE SP_APP SET APP_NAME=:APP_NAME;, " +
            "DESCRIPTION=:DESCRIPTION;, IS_SAAS_APP=:IS_SAAS_APP;, IS_DISCOVERABLE=:IS_DISCOVERABLE;, " +
            "USERNAME=:USERNAME;, USER_STORE=:USER_STORE;, IMAGE_URL=:IMAGE_URL;, " +
            "ACCESS_URL=:ACCESS_URL; WHERE TENANT_ID=:TENANT_ID; AND ID=:ID;";
    public static final String UPDATE_BASIC_APPINFO_WITH_ROLE_CLAIM = "UPDATE SP_APP SET ROLE_CLAIM=? WHERE TENANT_ID" +
            "= ? AND ID = ?";
    public static final String UPDATE_BASIC_APPINFO_WITH_CLAIM_DIALEECT_AND_SEND_LOCAL_SUB_ID =
            "UPDATE SP_APP SET IS_LOCAL_CLAIM_DIALECT=?, IS_SEND_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ?";
    public static final String UPDATE_BASIC_APPINFO_WITH_LOCAL_AND_OUTBOUND_CONFIGURATION = "UPDATE SP_APP SET " +
            "IS_SEND_AUTH_LIST_OF_IDPS=?, IS_USE_TENANT_DOMAIN_SUBJECT=?, IS_USE_USER_DOMAIN_SUBJECT=?, " +
            "ENABLE_AUTHORIZATION=?, SUBJECT_CLAIM_URI=?, AUTH_TYPE=? WHERE TENANT_ID= ? AND ID = ?";
    public static final String UPDATE_CERTIFICATE = "UPDATE IDN_CERTIFICATE SET CERTIFICATE_IN_PEM = ? WHERE " +
            "ID = ?";
    public static final String ADD_CERTIFICATE = "INSERT INTO IDN_CERTIFICATE(NAME, CERTIFICATE_IN_PEM, TENANT_ID) " +
            "VALUES(?, ?, ?)";
    public static final String UPDATE_BASIC_APPINFO_WITH_PRO_PROPERTIES = "UPDATE SP_APP SET PROVISIONING_USERSTORE_" +
            "DOMAIN=?, IS_DUMB_MODE=? WHERE TENANT_ID= ? AND ID = ?";
    public static final String UPDATE_SP_PERMISSIONS = "UPDATE UM_PERMISSION SET UM_RESOURCE_ID=? WHERE UM_ID=?";

    public static final String STORE_CLIENT_INFO = "INSERT INTO SP_INBOUND_AUTH (TENANT_ID, INBOUND_AUTH_KEY," +
            "INBOUND_AUTH_TYPE,PROP_NAME, PROP_VALUE, APP_ID,INBOUND_CONFIG_TYPE) VALUES (?,?,?,?,?,?,?)";
    public static final String STORE_STEP_INFO = "INSERT INTO SP_AUTH_STEP (TENANT_ID, STEP_ORDER, APP_ID, " +
            "IS_SUBJECT_STEP, IS_ATTRIBUTE_STEP) VALUES (?,?,?,?,?)";
    public static final String STORE_STEP_IDP_AUTH = "INSERT INTO SP_FEDERATED_IDP (ID, TENANT_ID, AUTHENTICATOR_ID) " +
            "VALUES (?,?,?)";
    public static final String STORE_SP_AUTH_SCRIPT = "INSERT INTO SP_AUTH_SCRIPT (TENANT_ID, APP_ID, TYPE, " +
            " CONTENT, IS_ENABLED)" +
            " VALUES (?,?,?,?,?)";
    public static final String STORE_CLAIM_MAPPING = "INSERT INTO SP_CLAIM_MAPPING (TENANT_ID, IDP_CLAIM, SP_CLAIM, " +
            "APP_ID, IS_REQUESTED, IS_MANDATORY, DEFAULT_VALUE) VALUES (?,?,?,?,?,?,?)";
    public static final String STORE_ROLE_MAPPING =
            "INSERT INTO SP_ROLE_MAPPING (TENANT_ID, IDP_ROLE, SP_ROLE, APP_ID)" +
                    " VALUES (?,?,?,?)";
    public static final String STORE_REQ_PATH_AUTHENTICATORS = "INSERT INTO SP_REQ_PATH_AUTHENTICATOR (TENANT_ID, " +
            "AUTHENTICATOR_NAME, APP_ID) VALUES (?,?,?)";
    public static final String STORE_PRO_CONNECTORS = "INSERT INTO SP_PROVISIONING_CONNECTOR (TENANT_ID, IDP_NAME, " +
            "CONNECTOR_NAME, APP_ID,IS_JIT_ENABLED, BLOCKING, RULE_ENABLED) VALUES (?,?,?,?,?,?,?)";

    // LOAD Queries
    public static final String LOAD_APP_ID_BY_APP_NAME = "SELECT ID FROM SP_APP WHERE APP_NAME = ? AND TENANT_ID = ?";
    public static final String LOAD_APP_NAMES_BY_TENANT = "SELECT ID, APP_NAME, DESCRIPTION FROM SP_APP WHERE " +
            "TENANT_ID = ? AND APP_NAME != ? ORDER BY ID DESC";
    public static final String LOAD_APP_NAMES_BY_TENANT_AND_FILTER = "SELECT SP_APP.ID, SP_APP.APP_NAME, " +
            "SP_APP.DESCRIPTION FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE " +
            "SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (%s) ORDER BY SP_APP.ID DESC";<commit_msg>fix duplicate SP Names listing issue<commit_after>/*
 * Copyright (c) 2022, WSO2 LLC. (http://www.wso2.org).
 *
 * WSO2 LLC. licenses this file to you 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 org.wso2.carbon.identity.application.mgt.dao.impl;

/**
 * This class contains default SQL queries
 * <p/>
 * TODO : Make the queries configurable from a file TODO : Use transactions and joins
 * SQL queries are internal implementation of the DAO layer and should not be exposed to components outside
 */
public class ApplicationMgtDBQueries {

    // STORE Queries
    public static final String STORE_BASIC_APPINFO = "INSERT INTO SP_APP (TENANT_ID, APP_NAME, USER_STORE, USERNAME, " +
            "DESCRIPTION, AUTH_TYPE, IS_USE_TENANT_DOMAIN_SUBJECT, ENABLE_AUTHORIZATION,IS_USE_USER_DOMAIN_SUBJECT, " +
            "UUID, IMAGE_URL, ACCESS_URL) " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    public static final String UPDATE_BASIC_APPINFO = "UPDATE SP_APP SET APP_NAME=:APP_NAME;, " +
            "DESCRIPTION=:DESCRIPTION;, IS_SAAS_APP=:IS_SAAS_APP;, IS_DISCOVERABLE=:IS_DISCOVERABLE;, " +
            "IMAGE_URL=:IMAGE_URL;, ACCESS_URL=:ACCESS_URL; WHERE TENANT_ID=:TENANT_ID; AND ID=:ID;";
    public static final String UPDATE_BASIC_APPINFO_WITH_OWNER_UPDATE = "UPDATE SP_APP SET APP_NAME=:APP_NAME;, " +
            "DESCRIPTION=:DESCRIPTION;, IS_SAAS_APP=:IS_SAAS_APP;, IS_DISCOVERABLE=:IS_DISCOVERABLE;, " +
            "USERNAME=:USERNAME;, USER_STORE=:USER_STORE;, IMAGE_URL=:IMAGE_URL;, " +
            "ACCESS_URL=:ACCESS_URL; WHERE TENANT_ID=:TENANT_ID; AND ID=:ID;";
    public static final String UPDATE_BASIC_APPINFO_WITH_ROLE_CLAIM = "UPDATE SP_APP SET ROLE_CLAIM=? WHERE TENANT_ID" +
            "= ? AND ID = ?";
    public static final String UPDATE_BASIC_APPINFO_WITH_CLAIM_DIALEECT_AND_SEND_LOCAL_SUB_ID =
            "UPDATE SP_APP SET IS_LOCAL_CLAIM_DIALECT=?, IS_SEND_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ?";
    public static final String UPDATE_BASIC_APPINFO_WITH_LOCAL_AND_OUTBOUND_CONFIGURATION = "UPDATE SP_APP SET " +
            "IS_SEND_AUTH_LIST_OF_IDPS=?, IS_USE_TENANT_DOMAIN_SUBJECT=?, IS_USE_USER_DOMAIN_SUBJECT=?, " +
            "ENABLE_AUTHORIZATION=?, SUBJECT_CLAIM_URI=?, AUTH_TYPE=? WHERE TENANT_ID= ? AND ID = ?";
    public static final String UPDATE_CERTIFICATE = "UPDATE IDN_CERTIFICATE SET CERTIFICATE_IN_PEM = ? WHERE " +
            "ID = ?";
    public static final String ADD_CERTIFICATE = "INSERT INTO IDN_CERTIFICATE(NAME, CERTIFICATE_IN_PEM, TENANT_ID) " +
            "VALUES(?, ?, ?)";
    public static final String UPDATE_BASIC_APPINFO_WITH_PRO_PROPERTIES = "UPDATE SP_APP SET PROVISIONING_USERSTORE_" +
            "DOMAIN=?, IS_DUMB_MODE=? WHERE TENANT_ID= ? AND ID = ?";
    public static final String UPDATE_SP_PERMISSIONS = "UPDATE UM_PERMISSION SET UM_RESOURCE_ID=? WHERE UM_ID=?";

    public static final String STORE_CLIENT_INFO = "INSERT INTO SP_INBOUND_AUTH (TENANT_ID, INBOUND_AUTH_KEY," +
            "INBOUND_AUTH_TYPE,PROP_NAME, PROP_VALUE, APP_ID,INBOUND_CONFIG_TYPE) VALUES (?,?,?,?,?,?,?)";
    public static final String STORE_STEP_INFO = "INSERT INTO SP_AUTH_STEP (TENANT_ID, STEP_ORDER, APP_ID, " +
            "IS_SUBJECT_STEP, IS_ATTRIBUTE_STEP) VALUES (?,?,?,?,?)";
    public static final String STORE_STEP_IDP_AUTH = "INSERT INTO SP_FEDERATED_IDP (ID, TENANT_ID, AUTHENTICATOR_ID) " +
            "VALUES (?,?,?)";
    public static final String STORE_SP_AUTH_SCRIPT = "INSERT INTO SP_AUTH_SCRIPT (TENANT_ID, APP_ID, TYPE, " +
            " CONTENT, IS_ENABLED)" +
            " VALUES (?,?,?,?,?)";
    public static final String STORE_CLAIM_MAPPING = "INSERT INTO SP_CLAIM_MAPPING (TENANT_ID, IDP_CLAIM, SP_CLAIM, " +
            "APP_ID, IS_REQUESTED, IS_MANDATORY, DEFAULT_VALUE) VALUES (?,?,?,?,?,?,?)";
    public static final String STORE_ROLE_MAPPING =
            "INSERT INTO SP_ROLE_MAPPING (TENANT_ID, IDP_ROLE, SP_ROLE, APP_ID)" +
                    " VALUES (?,?,?,?)";
    public static final String STORE_REQ_PATH_AUTHENTICATORS = "INSERT INTO SP_REQ_PATH_AUTHENTICATOR (TENANT_ID, " +
            "AUTHENTICATOR_NAME, APP_ID) VALUES (?,?,?)";
    public static final String STORE_PRO_CONNECTORS = "INSERT INTO SP_PROVISIONING_CONNECTOR (TENANT_ID, IDP_NAME, " +
            "CONNECTOR_NAME, APP_ID,IS_JIT_ENABLED, BLOCKING, RULE_ENABLED) VALUES (?,?,?,?,?,?,?)";

    // LOAD Queries
    public static final String LOAD_APP_ID_BY_APP_NAME = "SELECT ID FROM SP_APP WHERE APP_NAME = ? AND TENANT_ID = ?";
    public static final String LOAD_APP_NAMES_BY_TENANT = "SELECT ID, APP_NAME, DESCRIPTION FROM SP_APP WHERE " +
            "TENANT_ID = ? AND APP_NAME != ? ORDER BY ID DESC";
    public static final String LOAD_APP_NAMES_BY_TENANT_AND_FILTER = "SELECT DISTINCT SP_APP.ID, SP_APP.APP_NAME, " +
            "SP_APP.DESCRIPTION FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE " +
            "SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (%s) ORDER BY SP_APP.ID DESC";
{'fraction_non_alphanumeric': 0.10576749231603688, 'fraction_numerical': 0.0021695895859699874, 'mean_word_length': 4.0081484834766865, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 4, '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': '3757717', 'n_tokens_mistral': 4068, 'n_tokens_neox': 3808, 'n_words': 919}