<commit_before>/***************************************************************************
 *                                                                          *
 *  Organization: Lawrence Livermore National Lab (LLNL)                    *
 *   Directorate: Computation                                               *
 *    Department: Computing Applications and Research                       *
 *      Division: S&T Global Security                                       *
 *        Matrix: Atmospheric, Earth and Energy Division                    *
 *       Program: PCMDI                                                     *
 *       Project: Earth Systems Grid (ESG) Data Node Software Stack         *
 *  First Author: <NAME> (<EMAIL>)                            *
 *                                                                          *
 ****************************************************************************
 *                                                                          *
 *   Copyright (c) 2009, Lawrence Livermore National Security, LLC.         *
 *   Produced at the Lawrence Livermore National Laboratory                 *
 *   Written by: <NAME> (<EMAIL>)                             *
 *   LLNL-CODE-420962                                                       *
 *                                                                          *
 *   All rights reserved. This file is part of the:                         *
 *   Earth System Grid (ESG) Data Node Software Stack, Version 1.0          *
 *                                                                          *
 *   For details, see http://esgf.org/esg-node/                             *
 *   Please also read this link                                             *
 *    http://esgf.org/LICENSE                                               *
 *                                                                          *
 *   * Redistribution and use in source and binary forms, with or           *
 *   without modification, are permitted provided that the following        *
 *   conditions are met:                                                    *
 *                                                                          *
 *   * Redistributions of source code must retain the above copyright       *
 *   notice, this list of conditions and the disclaimer below.              *
 *                                                                          *
 *   * Redistributions in binary form must reproduce the above copyright    *
 *   notice, this list of conditions and the disclaimer (as noted below)    *
 *   in the documentation and/or other materials provided with the          *
 *   distribution.                                                          *
 *                                                                          *
 *   Neither the name of the LLNS/LLNL nor the names of its contributors    *
 *   may be used to endorse or promote products derived from this           *
 *   software without specific prior written permission.                    *
 *                                                                          *
 *   THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS    *
 *   "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT      *
 *   LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS      *
 *   FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL LAWRENCE    *
 *   LIVERMORE NATIONAL SECURITY, LLC, THE U.S. DEPARTMENT OF ENERGY OR     *
 *   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,           *
 *   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT       *
 *   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF       *
 *   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND    *
 *   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,     *
 *   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT     *
 *   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF     *
 *   SUCH DAMAGE.                                                           *
 *                                                                          *
 ***************************************************************************/

/**
   Description:
   Perform sql query to find out all the people who
   Return Tuple of info needed (dataset_id, recipients/(user), names of updated files)
   
**/
package esg.node.security;

import static esg.common.Utils.getFQDN;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.List;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import java.sql.ResultSetMetaData;
import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import esg.common.db.DatabaseResource;
import esg.common.util.ESGFProperties;
import esg.security.utils.encryption.MD5CryptPasswordEncoder;
import esg.security.utils.encryption.PasswordEncoder;

public class UserInfoDAO {

    private static final long serialVersionUID = 1L;
    
    //-------------------
    //Selection queries (fills in the UserInfo data carrying object)
    //-------------------
    private static final String idQuery = 
        "SELECT id, openid, firstname, middlename, lastname, username, email, dn, organization, organization_type, city, state, country, status_code "+
        "FROM esgf_security.user "+
        "WHERE openid = ?";

    //-------------------
    //Insertion queries
    //-------------------
    
    //User Queries...
    private static final String hasUserOpenidQuery =
        "SELECT id from esgf_security.user "+
        "WHERE openid = ?";
    private static final String updateUserQuery = 
        "UPDATE esgf_security.user "+
        "SET openid = ?, firstname = ?, middlename = ?, lastname = ?, username = ?, email = ?, dn = ?, organization = ?, organization_type = ?, city = ?, state = ?, country = ?, status_code = ? "+
        "WHERE id = ? ";
    private static final String getNextUserPrimaryKeyValQuery = 
        "SELECT NEXTVAL('esgf_security.user_id_seq')";
    private static final String addUserQuery = 
        "INSERT INTO esgf_security.user (id, openid, firstname, middlename, lastname, username, email, dn, organization, organization_type, city, state, country, status_code) "+
        "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String delUserQuery =
        "DELETE FROM esgf_security.user "+
        "WHERE openid = ?";

    //Permission Queries...
    private static final String getPermissionsQuery = 
        "SELECT g.name, r.name from esgf_security.group as g, esgf_security.role as r, esgf_security.permission as p, esgf_security.user as u "+
        "WHERE p.user_id = u.id and u.openid = ? and p.group_id = g.id and p.role_id = r.id "+
        "ORDER BY g.name";
    private static final String addPermissionQuery = 
        "INSERT INTO esgf_security.permission (user_id, group_id, role_id) "+
        "VALUES ( ?, (SELECT id FROM esgf_security.group WHERE name = ? ), (SELECT id FROM esgf_security.role WHERE name = ?))";
    private static final String delPermissionQuery = 
        "DELETE FROM esgf_security.permission "+
        "WHERE user_id = ?, "+
        "group_id = (SELECT id FROM esgf_security.group WHERE name = ?), "+
        "role_id = (SELECT id FROM esgf_security.role WHERE name = ?)";
    private static final String delAllUserPermissionsQuery =
        "DELETE FROM esgf_security.permission WHERE user_id = (SELECT id FROM esgf_security.user WHERE openid = ?)";
    private static final String existsPermissionQuery = 
        "SELECT COUNT(*) FROM esgf_security.permission "+
        "WHERE user_id = ? "+
        "AND group_id = (SELECT id FROM esgf_security.group WHERE name = ? ) "+
        "AND role_id = (SELECT id FROM esgf_security.role WHERE name = ? )";

    //Status Queries
    private static final String setStatusCodeQuery = 
        "UPDATE esgf_security.user SET status_code = ? "+
        "WHERE openid = ?";

    private static final String changeStatusQuery = 
        "UPDATE esgf_security.user SET status_code = ? "+
        "WHERE verification_token = ? AND openid = ? ";

    private static final String setVerificationTokenQuery =
        "UPDATE esgf_security.user SET verification_token = ? "+
        "WHERE openid = ? ";

    private static final String getVerificationTokenQuery = 
        "SELECT verification_token FROM esgf_security.user WHERE openid = ?";
        
    //Password Queries...
    private static final String setPasswordQuery = 
        "UPDATE esgf_security.user SET password = ? "+
        "WHERE openid = ?";

    private static final String getPasswordQuery = 
        "SELECT password FROM esgf_security.user WHERE openid = ?";

    //-------------------

    private static final String showUsersQuery =
        "SELECT * from FROM esgf_security.user";

    
    private static final Log log = LogFactory.getLog(UserInfoDAO.class);

    private Properties props = null;
    private DataSource dataSource = null;<commit_msg>first refinement of user "show" query - maybe show group info too, or perhaps tack that onto the group query or both<commit_after>/***************************************************************************
 *                                                                          *
 *  Organization: Lawrence Livermore National Lab (LLNL)                    *
 *   Directorate: Computation                                               *
 *    Department: Computing Applications and Research                       *
 *      Division: S&T Global Security                                       *
 *        Matrix: Atmospheric, Earth and Energy Division                    *
 *       Program: PCMDI                                                     *
 *       Project: Earth Systems Grid (ESG) Data Node Software Stack         *
 *  First Author: <NAME> (<EMAIL>)                            *
 *                                                                          *
 ****************************************************************************
 *                                                                          *
 *   Copyright (c) 2009, Lawrence Livermore National Security, LLC.         *
 *   Produced at the Lawrence Livermore National Laboratory                 *
 *   Written by: <NAME> (<EMAIL>)                             *
 *   LLNL-CODE-420962                                                       *
 *                                                                          *
 *   All rights reserved. This file is part of the:                         *
 *   Earth System Grid (ESG) Data Node Software Stack, Version 1.0          *
 *                                                                          *
 *   For details, see http://esgf.org/esg-node/                             *
 *   Please also read this link                                             *
 *    http://esgf.org/LICENSE                                               *
 *                                                                          *
 *   * Redistribution and use in source and binary forms, with or           *
 *   without modification, are permitted provided that the following        *
 *   conditions are met:                                                    *
 *                                                                          *
 *   * Redistributions of source code must retain the above copyright       *
 *   notice, this list of conditions and the disclaimer below.              *
 *                                                                          *
 *   * Redistributions in binary form must reproduce the above copyright    *
 *   notice, this list of conditions and the disclaimer (as noted below)    *
 *   in the documentation and/or other materials provided with the          *
 *   distribution.                                                          *
 *                                                                          *
 *   Neither the name of the LLNS/LLNL nor the names of its contributors    *
 *   may be used to endorse or promote products derived from this           *
 *   software without specific prior written permission.                    *
 *                                                                          *
 *   THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS    *
 *   "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT      *
 *   LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS      *
 *   FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL LAWRENCE    *
 *   LIVERMORE NATIONAL SECURITY, LLC, THE U.S. DEPARTMENT OF ENERGY OR     *
 *   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,           *
 *   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT       *
 *   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF       *
 *   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND    *
 *   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,     *
 *   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT     *
 *   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF     *
 *   SUCH DAMAGE.                                                           *
 *                                                                          *
 ***************************************************************************/

/**
   Description:
   Perform sql query to find out all the people who
   Return Tuple of info needed (dataset_id, recipients/(user), names of updated files)
   
**/
package esg.node.security;

import static esg.common.Utils.getFQDN;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.List;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import java.sql.ResultSetMetaData;
import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import esg.common.db.DatabaseResource;
import esg.common.util.ESGFProperties;
import esg.security.utils.encryption.MD5CryptPasswordEncoder;
import esg.security.utils.encryption.PasswordEncoder;

public class UserInfoDAO {

    private static final long serialVersionUID = 1L;
    
    //-------------------
    //Selection queries (fills in the UserInfo data carrying object)
    //-------------------
    private static final String idQuery = 
        "SELECT id, openid, firstname, middlename, lastname, username, email, dn, organization, organization_type, city, state, country, status_code "+
        "FROM esgf_security.user "+
        "WHERE openid = ?";

    //-------------------
    //Insertion queries
    //-------------------
    
    //User Queries...
    private static final String hasUserOpenidQuery =
        "SELECT id from esgf_security.user "+
        "WHERE openid = ?";
    private static final String updateUserQuery = 
        "UPDATE esgf_security.user "+
        "SET openid = ?, firstname = ?, middlename = ?, lastname = ?, username = ?, email = ?, dn = ?, organization = ?, organization_type = ?, city = ?, state = ?, country = ?, status_code = ? "+
        "WHERE id = ? ";
    private static final String getNextUserPrimaryKeyValQuery = 
        "SELECT NEXTVAL('esgf_security.user_id_seq')";
    private static final String addUserQuery = 
        "INSERT INTO esgf_security.user (id, openid, firstname, middlename, lastname, username, email, dn, organization, organization_type, city, state, country, status_code) "+
        "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String delUserQuery =
        "DELETE FROM esgf_security.user "+
        "WHERE openid = ?";

    //Permission Queries...
    private static final String getPermissionsQuery = 
        "SELECT g.name, r.name from esgf_security.group as g, esgf_security.role as r, esgf_security.permission as p, esgf_security.user as u "+
        "WHERE p.user_id = u.id and u.openid = ? and p.group_id = g.id and p.role_id = r.id "+
        "ORDER BY g.name";
    private static final String addPermissionQuery = 
        "INSERT INTO esgf_security.permission (user_id, group_id, role_id) "+
        "VALUES ( ?, (SELECT id FROM esgf_security.group WHERE name = ? ), (SELECT id FROM esgf_security.role WHERE name = ?))";
    private static final String delPermissionQuery = 
        "DELETE FROM esgf_security.permission "+
        "WHERE user_id = ?, "+
        "group_id = (SELECT id FROM esgf_security.group WHERE name = ?), "+
        "role_id = (SELECT id FROM esgf_security.role WHERE name = ?)";
    private static final String delAllUserPermissionsQuery =
        "DELETE FROM esgf_security.permission WHERE user_id = (SELECT id FROM esgf_security.user WHERE openid = ?)";
    private static final String existsPermissionQuery = 
        "SELECT COUNT(*) FROM esgf_security.permission "+
        "WHERE user_id = ? "+
        "AND group_id = (SELECT id FROM esgf_security.group WHERE name = ? ) "+
        "AND role_id = (SELECT id FROM esgf_security.role WHERE name = ? )";

    //Status Queries
    private static final String setStatusCodeQuery = 
        "UPDATE esgf_security.user SET status_code = ? "+
        "WHERE openid = ?";

    private static final String changeStatusQuery = 
        "UPDATE esgf_security.user SET status_code = ? "+
        "WHERE verification_token = ? AND openid = ? ";

    private static final String setVerificationTokenQuery =
        "UPDATE esgf_security.user SET verification_token = ? "+
        "WHERE openid = ? ";

    private static final String getVerificationTokenQuery = 
        "SELECT verification_token FROM esgf_security.user WHERE openid = ?";
        
    //Password Queries...
    private static final String setPasswordQuery = 
        "UPDATE esgf_security.user SET password = ? "+
        "WHERE openid = ?";

    private static final String getPasswordQuery = 
        "SELECT password FROM esgf_security.user WHERE openid = ?";

    //-------------------

    private static final String showUsersQuery =
        "SELECT username, firstname, lastname, openid from FROM esgf_security.user";

    //-------------------
    
    private static final Log log = LogFactory.getLog(UserInfoDAO.class);

    private Properties props = null;
    private DataSource dataSource = null;
{'fraction_non_alphanumeric': 0.11005085723273737, 'fraction_numerical': 0.0014680438315944004, 'mean_word_length': 1.701699716713881, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 100, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3514131', 'n_tokens_mistral': 4651, 'n_tokens_neox': 4348, 'n_words': 1465}