oracle_sqldba

#!/usr/bin/python
# -*- coding: utf-8 -*-

DOCUMENTATION = '''
---
module: oracle_sqldba
short_description: Execute sql (scripts) using sqlplus (BEQ) or catcon.pl
description:
    - Needed for post-installation tasks not covered by other modules
    - Uses sqlplus (BEQ connect, e.g. / as sysdba) or $OH//perl catcon.pl
options:
    sql:
        description:
            - Single SQL statement
            - Will be executed by sqlplus
            - Used for DDL and DML
        required: false
        default: None
    sqlscript:
        description:
            - Script name, optionally followed by parameters
            - Will be executed by sqlplus
        required: false
        default: None
    catcon_pl:
        description:
            - Script name, optionally followed by parameters
            - Will be executed by $OH//perl catcon.pl
        required: false
        default: None
    sqlselect:
        description:
            - Single SQL statement
            - Will be executed by sqlplus using dbms_xmlgen.getxml
            - Used for select only, returns dict in .state
            - To access the column "value" of the first row write: "<<registered result>>.state.ROW[0].VALUE" (use uppercase)
        required: false
        default: None
    creates_sql:
        description:
            - This is the check query to ensure idempotence.
            - Must be a single SQL select that results to no rows or a plain 0 if the catcon_pl/sqlscript/sql has to be executed. Any other result prevent the execution of catcon_pl/sqlscript/sql.
            - The catcon_pl/sqlscript/sql will be executed unconditionally if creates_sql is omitted.
            - Creates_sql must be omitted when sqlselect is used.
            - Creates_sql is executed with sqlplus / as sysdba in the root container. Write the sql query according to this fact.
            - If pdb_list is given (implicitely whith all_pdbs) creates_sql is executed in every PDB incl. CDB$ROOT. The pdb_list will be shortened according to the results of creates_sql in the PDBs.
        required: false
        default: None
    username:
        description:
            - Database username, defaults to "/ as sysdba"
        required: false
        default: None
    password:
        description:
            - Password of database user
        required: false
        default: None
    scope:
        description:
            - Shall the SQL be applied to CDB, PDBs, or both?
        values:
            - default: if catcon_pl is filled then all_pdbs_and_root else cdb
            - db: alias for cdb, allows for better readability for non-cdb
            - cdb: apply to root container or whole db
            - pdbs: apply to specified PDB's only (requires pdb_list)
            - all_pdbs: apply to all PDB's except PDB$SEED and CDB$ROOT
            - all_pdbs_and_root: apply to all PDB's except PDB$SEED
        required: false
        default: cdb
    pdb_list:
        description:
            - Optional list of PDB names
            - Space separated, as catcon.pl wants
            - Gets used only if scope is "pdbs"
            - Will be automatically filled and used when scope = all_pdbs and action like sql%
        required: false
        default: None
    oracle_home:
        description:
            - content of $ORACLE_HOME
    oracle_db_name:
        description:
            - SID or DB_NAME, needed for BEQ connect
    nls_lang:
        description:
            - set NLS_LANG to the given value
    chdir:
        description:
            - Working directory for SQL/script execution

author: Dietmar Uhlig, Robotron (www.robotron.de)
'''

EXAMPLES = '''
# Example 1, mixed post installation tasks
# from inventory:

oracle_databases:
  - oracle_db_name: eek17ec
      home: 12.2.0.1-ee
      state: present
      init_parameters: "{{ init_parameters['12.2.0.1-EMS'] }}"
      profiles: "{{ db_profiles['12.2.0.1-EMS'] }}"
      postinstall: "{{ db_postinstall['12.2.0.1-EMS'] }}"

oracle_pdbs:
  - cdb: eek17ec
    pdb_name: eckpdb
  - cdb: eek17ec
    pdb_name: sckpdb

db_postinstall:
  12.2.0.1-EMS:
    - catcon_pl: "$ORACLE_HOME/ctx/admin/catctx.sql context SYSAUX TEMP NOLOCK"
      creates_sql: "select 1 from dba_registry where comp_id = 'CONTEXT'"
    - sqlscript: "?/rdbms/admin/initsqlj.sql"
      scope: pdbs
      creates_sql: "select count(*) from dba_tab_privs where table_name = 'SQLJUTL' and grantee = 'PUBLIC'"
    - sqlscript: "?/rdbms/admin/utlrp.sql"
    - sql: "alter pluggable database {{ pdb.pdb_name | default(omit) }} save state"
      scope: pdbs

# see role oradb-postinstall, loops over {{ oracle_databases }} = loop_var oradb

- name: Conditionally execute post installation tasks
  oracle_sqldba:
    sql: "{{ pitask.sql | default(omit) }}"
    sqlscript: "{{ pitask.sqlscript | default(omit) }}"
    catcon_pl: "{{ pitask.catcon_pl | default(omit) }}"
    creates_sql: "{{ pitask.creates_sql | default(omit) }}"
    username: "{{ pitask.username | default(omit) }}"
    password: "{%if pitask.username is defined%}{{ dbpasswords[oradb.oracle_db_name][pitask.username] }}{%endif%}"
    scope: "{{ pitask.scope | default(omit) }}"
    pdb_list: "{{ oracle_pdbs | default([]) | json_query('[?cdb==`' + oradb.oracle_db_name + '`].pdb_name') | join(' ') }}"
    oracle_home: "{{ db_homes_config[oradb.home].oracle_home }}"
    oracle_db_name: "{{ oradb.oracle_db_name }}"
  loop: "{{ oradb.postinstall }}"
  loop_control:
    loop_var: pitask

# Example 2, read sql result

- name: Read job_queue_processes
  oracle_sqldba:
    sqlselect: "select value from gv$parameter where name = 'job_queue_processes'"
    oracle_home: "{{ oracle_db_home }}"
    oracle_db_name: "{{ oracle_db_name }}"
  register: jqpresult

- name: Store job_queue_processes
  set_fact:
    job_queue_processes: "{{ jqpresult.state.ROW[0].VALUE }}"
    # Use all uppercase for "ROW" and for column names!

'''

import errno
import os
import re
import shlex
import shutil
import tempfile
from subprocess import Popen, PIPE
from threading import Timer
from ansible.module_utils.basic import AnsibleModule
from ansible.module_utils._text import to_native, to_text
import xml.etree.ElementTree as ET
from copy import copy

changed = False
result = ""
err_msg = ""
oracle_home = ""
pdb_list = ""
sql_process = None
debug_trace = ""

# Maximum runtime for sqlplus and catcon.pl in seconds. 0 means no timeout.
timeout = 0

def trc(msg):
    global debug_trace
    debug_trace += msg
    if msg[-1] != "\n":
        debug_trace += "\n"
    return msg

# dictify is based on https://stackoverflow.com/questions/2148119/how-to-convert-an-xml-string-to-a-dictionary/10077069#10077069
def dictify(r,root=True):
    if root:
        #return {r.tag : dictify(r, False)} # no, but...
        return dictify(r, False) # skip root node "ROWSET"
    d=copy(r.attrib)
    if (r.text).strip():
        d["_text"]=r.text
    for x in r.findall("./*"):
        if x.tag not in d:
            d[x.tag]=[]
        if (x.text).strip(): # assume scalar
            d[x.tag] = x.text
        else:
            d[x.tag].append(dictify(x,False))
    return d

def sqlplus():
    global oracle_home

    sql_bin = os.path.join(oracle_home, "bin", "sqlplus")
    return [sql_bin, "-l", "-s", "/nolog"]

def conn(username, password):
    if username == None:
        return trc("conn / as sysdba\n")
    else:
        trc("conn " + username + "/***")
        return "conn " + "/".join([username, password]) + "\n"

def sql_input(sql, username, password, pdb):
    trc("-- sql_input start --")
    sql_scr  = trc("set heading off echo off feedback off termout on\n")
    sql_scr += trc("set long 1000000 pagesize 0 linesize 1000 trimspool on\n")
    sql_scr += conn(username, password)

    if pdb is not None:
        sql_scr += trc("alter session set container = " + pdb + ";\n")
    sql_scr += trc(sql + "\n")
    sql_scr += trc("exit;\n")
    trc("-- sql_input end --")
    return sql_scr

def kill_process():
    global err_msg, sql_process

    sql_process.kill()
    err_msg = "Timeout occured after %d seconds. " % timeout

def run_sql_p(sql, username, password, scope, pdb_list):
    global changed, err_msg, sql_process

    trc("-- run_sql_p start --")
    err_msg = ""
    result = ""
    if scope == 'pdbs':
        trc("-- run_sql_p -- scope == 'pdbs' --")
        for pdb in pdb_list.split():
            trc("-- run_sql_p -- call run_sql --")
            result += run_sql(sql, username, password, pdb)
    else:
        trc("-- run_sql_p -- scope != 'pdbs' --")
        result = run_sql(sql, username, password, None)
    trc("-- run_sql_p end --")
    return result

def run_sql(sql, username, password, pdb):
    global changed, err_msg, sql_process

    trc("-- run_sql start --")
    t = None
    try:
        sql_cmd = sql_input(sql, username, password, pdb)
        sql_process = Popen(sqlplus(), stdin = PIPE, stdout = PIPE, stderr = PIPE)
        if timeout > 0:
            t = Timer(timeout, kill_process)
            t.start()
        [sout, serr] = sql_process.communicate(input = sql_cmd.encode('latin-1'))
    except Exception as e:
        trc("-- run_sql -- catched exception --")
        err_msg += 'Could not call sqlplus. %s. called: %s.' % (to_native(e), " ".join(sqlplus()))
        return "[ERR]"
    finally:
        trc("-- run_sql -- finally --")
        if timeout > 0 and t is not None:
            t.cancel()
    if sql_process.returncode != 0:
        trc("-- run_sql -- sql_process.returncode != 0 --")
        err_msg += "called: %s\nreturncode: %d\nresult: %s. stderr = %s." % (sql_cmd, sql_process.returncode, sout.decode(), serr.decode())
        return "[ERR]"
    sqlerr_pat = re.compile("^(ORA|TNS|SP2)-[0-9]+", re.MULTILINE)
    sqlplus_err = sqlerr_pat.search(sout.decode())
    if sqlplus_err:
        trc("-- run_sql -- sqlplus_err is not None --")
        err_msg += "[ERR] sqlplus: %s\nERR Code: %s.\n" % (sql_cmd, sqlplus_err.group())
        return "[ERR]\n%s\n" % sout.strip().decode()

    changed = True
    trc("-- run_sql end --")
    return sout.strip().decode()


def check_creates_sql(sql, scope):
    global pdb_list

    trc("-- check_creates_sql start --")
    if not sql.endswith(";"):
        sql += ";"
    if scope == 'cdb':
        trc("-- check_creates_sql -- call run_sql --")
        res = run_sql(sql, None, None, None)
        # error handling see call of check_creates_sql
        trc("-- check_creates_sql -- result: " + res + " --")
        trc("-- check_creates_sql end --")
        return False if not res or res == "0" else True
    else:
        checked_pdb_list = ""
        for pdb in pdb_list.split():
            trc("-- check_creates_sql -- call run_sql, pdb = " + pdb + " --")
            res = run_sql(sql, None, None, pdb)
            # error handling see call of check_creates_sql
            trc("-- check_creates_sql -- result: " + res + " --")
            if not res or res == "0":
                checked_pdb_list += " " + pdb
        pdb_list = checked_pdb_list.lstrip()
        trc("-- check_creates_sql -- pdb_list: " + pdb_list + " --")
        trc("-- check_creates_sql end --")
        return True if pdb_list == "" else False


def is_container():
    return run_sql("select cdb from gv$database;", None, None, None) == 'YES'

def get_all_pdbs(scope):
    global result, pdb_list

    trc("-- get_all_pdbs start --")
    sql  = "select listagg(pdb_name, ' ') within group (order by pdb_name) from dba_pdbs where status = 'NORMAL' and pdb_name <> 'PDB$SEED';"
    pdb_list = run_sql(sql, None, None, None)
    if scope == 'all_pdbs_and_root':
        pdb_list = 'CDB$ROOT ' + pdb_list
    trc("-- get_all_pdbs -- pdb_list: " + pdb_list + " --")
    trc("-- get_all_pdbs end --")


def run_catcon_pl(catcon_pl):
    # after pre-processing in main() the parameter scope is not necessary any more
    global oracle_home, changed, result, err_msg, pdb_list, sql_process

    err_msg = ""
    catcon_pl = re.sub("^(\$ORACLE_HOME|\?)", oracle_home, catcon_pl)
    logdir = tempfile.mkdtemp()
    catcon_cmd = [ os.path.join(oracle_home, "perl", "bin", "perl"),
                   os.path.join(oracle_home, "rdbms", "admin", "catcon.pl"),
                   "-l", logdir, "-b", "catcon" ]
    if pdb_list is not None:
        catcon_cmd += [ "-c", pdb_list ]
    cc_script = shlex.split(catcon_pl)
    if len(cc_script) > 1:
        for i in range(1, len(cc_script)):
            cc_script[i] = "1" + cc_script[i]
        catcon_cmd += [ "-a", "1" ]
    catcon_cmd += [ "--" ] + cc_script
    try:
        sql_process = Popen(catcon_cmd, stdout = PIPE, stderr = PIPE)
        if timeout > 0:
            t = Timer(timeout, kill_process)
            t.start()
        [sout, serr] = sql_process.communicate()
    except Exception as e:
        err_msg += 'Could not call perl. %s. called: %s.' % (to_native(e), " ".join(catcon_cmd))
        return
    finally:
        if timeout > 0:
            t.cancel()
        try:
            with open(logdir + '/catcon0.log') as f:
                result += f.read()
            shutil.rmtree(logdir)
        except OSError as exc:
            result += sout.decode()
            if exc.errno != errno.ENOENT:
                raise
    if sql_process.returncode != 0:
        err_msg += "called: %s\nreturncode: %d\nresult: %s\nstderr = %s." % (" ".join(catcon_cmd), sql_process.returncode, sout.decode(), serr.decode())
        return
    changed = True



def main():
    global oracle_home, changed, result, err_msg, pdb_list

    module = AnsibleModule(
        argument_spec = dict(
            sql            = dict(required = False),
            sqlscript      = dict(required = False),
            catcon_pl      = dict(required = False),
            sqlselect      = dict(required = False),
            creates_sql    = dict(required = False),
            username       = dict(required = False),
            password       = dict(required = False, no_log = True),
            scope          = dict(required = False, choices = ["default", "db", "cdb", "pdbs", "all_pdbs", "all_pdbs_and_root"], default = 'default'),
            pdb_list       = dict(required = False),
            oracle_home    = dict(required = True),
            oracle_db_name = dict(required = True),
            nls_lang       = dict(required = False),
            chdir          = dict(required = False)
        ),
        mutually_exclusive=[['sql', 'sqlscript', 'catcon_pl', 'sqlselect'], ['sqlselect', 'creates_sql']]
    )

    sql            = module.params["sql"]
    sqlscript      = module.params["sqlscript"]
    catcon_pl      = module.params["catcon_pl"]
    sqlselect      = module.params["sqlselect"]
    creates_sql    = module.params["creates_sql"]
    username       = module.params["username"]
    password       = module.params["password"]
    scope          = module.params["scope"]
    pdb_list       = module.params["pdb_list"]
    oracle_home    = module.params["oracle_home"]
    oracle_db_name = module.params["oracle_db_name"]
    nls_lang       = module.params["nls_lang"]
    workdir        = module.params["chdir"]

    os.environ["ORACLE_HOME"] = oracle_home
    os.environ["ORACLE_SID"] = oracle_db_name
    os.environ["PATH"] += os.pathsep + os.path.join(oracle_home, "bin")
    if nls_lang is not None:
        os.environ["NLS_LANG"] = nls_lang

    if scope == 'db':
        scope = 'cdb'
    if scope == 'default':
        scope = "all_pdbs_and_root" if catcon_pl is not None else "cdb"
    if scope == 'pdbs' and (pdb_list is None or pdb_list.strip() == ""):
        module.exit_json(msg = "scope = pdbs, but pdb_list is empty", changed = False, trace = debug_trace)
    if scope == 'cdb' and catcon_pl is not None:
        scope = 'pdbs'
        pdb_list = 'CDB$ROOT'
    if scope in ['all_pdbs', 'all_pdbs_and_root']  and (catcon_pl is None or creates_sql is not None):
        if is_container():
            get_all_pdbs(scope)
            scope = 'pdbs'
        else:
            scope = 'cdb'
    trc("scope: " + scope)
    
    if workdir is not None:
        try:
            os.chdir(workdir)
        except Exception as e:
            module.fail_json(msg = 'Could not chdir to %s: %s.' % (workdir, to_native(e)), changed = False, trace = debug_trace)

    if creates_sql is not None:
        trc("call check_creates_sql")
        already_done = check_creates_sql(creates_sql, scope)
        if err_msg:
            module.fail_json(msg = "%s\n%s" % (result, err_msg), changed = False, trace = debug_trace)
        else:
            trc("check_creates_sql said: already_done = {}".format(already_done))
            if already_done:
                module.exit_json(msg = result, changed = False, trace = debug_trace)

    if pdb_list is not None:
        result = "Run on these PDBs: %s\n" % pdb_list
        
    if sqlselect is not None:
        if sqlselect.endswith(";"):
            sqlselect.rstrip(";")
        sqlselect = "select dbms_xmlgen.getxml('" + sqlselect.replace("'", "''") + "') from dual;"
        result = run_sql_p(sqlselect, username, password, scope, pdb_list)
    elif sql is not None:
        sql = os.linesep.join([s for s in sql.splitlines() if s.strip()])
        if not sql.endswith(";") and not sql.endswith("/"):
            sql += ";"
        result += run_sql_p(sql, username, password, scope, pdb_list)
    elif sqlscript is not None:
        if not sqlscript.startswith("@"):
            sqlscript = "@" + sqlscript
        result += run_sql_p(sqlscript, username, password, scope, pdb_list)
    elif catcon_pl is not None:
        run_catcon_pl(catcon_pl)

    if not err_msg:
        if sqlselect is not None:
            res_dict = dictify(ET.fromstring(result)) if result else {"ROW": []}
            module.exit_json(msg = result, changed = False, state = res_dict, trace = debug_trace)
        else:
            module.exit_json(msg = result, changed = changed, trace = debug_trace)
    else:
        module.fail_json(msg = "%s\n%s\nENV:\n  ORACLE_HOME = %s\n  ORACLE_SID = %s\n" % (result, err_msg, oracle_home, oracle_db_name), changed = changed, trace = debug_trace)


if __name__ == '__main__':
    main()