Trying to compare shared and client schema versions and upgrade client schema, if condition satisfies in Ansible using nested loops?

Archives Ansible Project
1

I am trying to write an ansible nested loop condition to do the schema upgrade based on if the version matches between shared schema and client schema.

Here is what my play book doing.

  1. Logs in to Shared Schema DB and get the shared schema version and registering the output.
  2. Streamlining the CSV file which contains client schema information and registering the output.
  3. Now run the client schema version SQL script to get the associated version # of each client schema.
  4. Now update the Client schema if Shared Schema and Client schema matches. Thanks to @Kai Stian Olstad who helped to get to this far.

Here is the code I’ve written in ansible to fetch the shared schema and client schema versions.

---

  - name: Run sqlplus command on {{ shared_service_name }}
    shell: source ~/.bash_profile && sqlplus -S '{{ shared_username }}/{{ shared_password }}@{{ shared_database_host }}:1521/{{ shared_service_name }}' @"sharedschema_version.sql"
    args:
      chdir: roles/oracle-sql/files/
    delegate_to: localhost
    register: sharedschema_version
    tags: shared_schema

  - debug: var=sharedschema_version.stdout_lines
    delegate_to: localhost

  - name: Get CSV File Content with out header
    shell: source ~/.bash_profile && awk -F',' '!/^#/ && !/^$/ && (NR!=1) { print }' "{{ output_csv_path }}" | sed 's/\"//g'
    register: groups_out
    delegate_to: localhost
    tags: client_schema

  - debug: var=groups_out.stdout_lines
    delegate_to: localhost

  - name: Run sqlplus command on Client DB
    shell: source ~/.bash_profile && sqlplus -S '{{ client_user }}/{{ client_password}}@{{ item.split(",").3 }}' @"clientschema_version.sql" {{ item.split(",").0 }}
    args:
      chdir: roles/oracle-sql/files/
    with_items: "{{ groups_out.stdout_lines }}"
    delegate_to: localhost
    register: clientschema_version
    tags: client_schema

  - debug: var=clientschema_version.stdout_lines
    delegate_to: localhost

  - name: Update the Client Schema
    shell: source ~/.bash_profile && sqlplus -S '{{ client_user }}/{{ alter_client_user_password }}@{{ item.split(",").3 }}' @"hotfix_client.sql" {{ item.split(",").0 }}
    with_items:
                - "{{ groups_out.stdout_lines }}"
                - "{{ sharedschema_version.stdout_lines }}"
                - "{{ clientschema_version.stdout_lines }}"
    args:
      chdir: roles/oracle-sql/files/
	when: {{ sharedschema_version.stdout_lines }} == {{ clientschema_version.stdout_lines }}
    delegate_to: localhost

However I am seeing issues as fetching client schema version task running against all client schema and trying to register the last schema value instead of each client at a time and compare and apply the client schema upgrade. Can some one guide me how to use the nested loop along with When condition.

here is the sample output I am getting.

    ok: [sandbox -> localhost] => {
    "sharedschema_version.stdout_lines": [
        "\t  101"
    ]
}

    "item": "Client_Schema1,client_user,password,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sandbox)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
    "rc": 0,
    "start": "2018-02-28 16:22:33.349118",
    "stderr": "",
    "stderr_lines": [],
    "stdout": "old   1: select version_build from &schema..version\nnew   1: select version_build from Client_Schema1.version\n\t    0",
    "stdout_lines": [
        "old   1: select version_build from &schema..version",
        "new   1: select version_build from Client_Schema1.version",
        "\t    0"
    ]
}

ok: [sandbox -> localhost] => {
    "clientschema_version.stdout_lines": "VARIABLE IS NOT DEFINED!"
}

fatal: [sandbox]: FAILED! => {
    "failed": true,
    "msg": "'dict object' has no attribute 'stdout_lines'"
}

Thanks in advance.