tlu/salt/modules/_modules/mysql.py

"""
mysql execution module
"""

import salt
import subprocess
import logging

LOG = logging.getLogger(__name__)

__virtualname__ = "mysql"

def __virtual__():
    """
    only load module if mysql client is installed
    """
    if salt.utils.path.which("mysql") is None:
        return (False, "mysql isn't installed on this minion")
    elif salt.utils.path.which("mysqladmin") is None:
        return (False, "mysqladmin isn't installed on this minion")
    else:
        return __virtualname__

def _execute(cmd):
    """
    executes the cmd and returns a salt ret dictionary
    """
    ret = {}
    LOG.debug(f"Executing: {cmd}")
    process = subprocess.Popen(cmd.split(), stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    stdout, stderr = process.communicate()
    if stdout:
        ret["out"] = stdout
    if stderr:
        ret["err"] = stderr
    LOG.debug(f"Exit code: { process.returncode }")
    if process.returncode == 0:
        ret["result"] = True
    else:
        ret["result"] = False
    return ret

def check_credentials(username, password, host="127.0.0.1", port="3306"):
    """
    Check if able to login with credentials

    :param str username: The username to check
    
    :param str password: The password for username

    :param str host: Optional, the IP or fqdn to the server (default: 127.0.0.1)

    :param str port: Optional, port the mysql is listening on (default: 3306)

    CLI Example:

        salt '*' mysql.check_credentials root secret
    """
    return execute(username, password, ";" , host=host, port=port)["result"]


def set_root_password(password):
    """
    Set the mysql/mariadb root password

    :param str password: The password to set

    CLI Example:
    
        salt '*' mysql.set_root_password secret
    """
    ret= {}
    cmd = f"mysqladmin password {password}"
    LOG.debug(f"Executing: {cmd}")
    process = subprocess.Popen(cmd.split(), stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    stdout, stderr = process.communicate()
    if stdout:
        ret["out"] = stdout
    if stderr:
        ret["err"] = stderr
    LOG.debug(f"Exit code: { process.returncode }")
    if process.returncode == 0:
        ret["result"] = True
    else:
        ret["result"] = False
    return ret



def execute(user, password, sql, host='127.0.0.1', port='3306'):
    """
    Execute an SQL statement
    
    :param str user: The user to connect with

    :param str password: The user password

    :param str host: the mysql server address 
    
    :param str sql: The SQL statement to be executed

    :param str port: the mysql server port

    CLI Example

      salt '*' mysql.execute root secret "SHOW DATABASES;"
    """

    ret = {}
    cmd = [ "mysql",
            "--skip-column-names",
            "--silent",
            f"--user={user}",
            f"--password={password}",
            f"--host={host}",
            f"--port={port}",
            f"--execute={sql}"
         ]
            
    LOG.debug(f"Executing: {' '.join(cmd)}")
    process = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    stdout, stderr = process.communicate()
    if stdout:
        ret["out"] = stdout
    if stderr:
        ret["err"] = stderr
    LOG.debug(f"Exit code: { process.returncode }")
    if process.returncode == 0:
        ret["result"] = True
    else:
        ret["result"] = False
    return ret