postgresql_ext_version_opt.yml

# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru>
# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)
# Tests for postgresql_ext version option

- vars:
    test_ext: dummy
    test_schema: schema1
    task_parameters: &task_parameters
      become_user: '{{ pg_user }}'
      become: yes
      register: result
    pg_parameters: &pg_parameters
      login_user: '{{ pg_user }}'
      login_db: postgres

  block:
  # Preparation:
  - name: postgresql_ext_version - create schema schema1
    <<: *task_parameters
    postgresql_schema:
      <<: *pg_parameters
      name: "{{ test_schema }}"

  # Do tests:
  - name: postgresql_ext_version - create extension of specific version, check mode
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '1.0'
      trust_input: no
    check_mode: yes

  - assert:
      that:
      - result is changed

  - name: postgresql_ext_version - check that nothing was actually changed
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}'"

  - assert:
      that:
      - result.rowcount == 0

  - name: postgresql_ext_version - create extension of specific version
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '1.0'
      trust_input: no

  - assert:
      that:
      - result is changed
      - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\" WITH SCHEMA \"{{ test_schema }}\" VERSION '1.0'"]

  - name: postgresql_ext_version - check
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - try to create extension of the same version again in check_mode
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '1.0'
      trust_input: no
    check_mode: yes

  - assert:
      that:
      - result is not changed

  - name: postgresql_ext_version - check
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - try to create extension of the same version again in actual mode
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '1.0'
      trust_input: no

  - assert:
      that:
      - result is not changed

  - name: postgresql_ext_version - check
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - update the extension to the next version in check_mode
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '2.0'
      trust_input: no
    check_mode: yes

  - assert:
      that:
      - result is changed

  - name: postgresql_ext_version - check, the version must be 1.0
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - update the extension to the next version
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '2.0'
      trust_input: no

  - assert:
      that:
      - result is changed
      - result.queries == ["SELECT path FROM pg_extension_update_paths('dummy') WHERE source = '1.0' AND target = '2.0'", "ALTER EXTENSION \"{{ test_ext }}\" UPDATE TO '2.0'"]

  - name: postgresql_ext_version - check, the version must be 2.0
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '2.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - check that version won't be changed if version won't be passed
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      trust_input: no

  - assert:
      that:
      - result is not changed

  - name: postgresql_ext_version - check, the version must be 2.0
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '2.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - update the extension to the latest version
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: latest
      trust_input: no

  - assert:
      that:
      - result is changed
      - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE"]

  - name: postgresql_ext_version - check
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - try to update the extension to the latest version again which always runs an update.
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: latest
      trust_input: no

  - assert:
      that:
      - result is changed

  - name: postgresql_ext_version - check that version number did not change even though update ran
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - try to downgrade the extension version, must fail
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      schema: "{{ test_schema }}"
      version: '1.0'
      trust_input: no
    ignore_errors: yes

  - assert:
      that:
      - result.failed == true

  - name: postgresql_ext_version - drop the extension in check_mode
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      state: absent
      trust_input: no
    check_mode: yes

  - assert:
      that:
      - result is changed

  - name: postgresql_ext_version - check that extension exists
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - drop the extension in actual mode
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      state: absent
      trust_input: no

  - assert:
      that:
      - result is changed

  - name: postgresql_ext_version - check that extension doesn't exist after the prev step
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}'"

  - assert:
      that:
      - result.rowcount == 0

  - name: postgresql_ext_version - try to drop the non-existent extension again
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      state: absent
      trust_input: no

  - assert:
      that:
      - result is not changed

  - name: postgresql_ext_version - create the extension without passing version
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      trust_input: no

  - assert:
      that:
      - result is changed
      - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\""]

  - name: postgresql_ext_version - check
    <<: *task_parameters
    postgresql_query:
      <<: *pg_parameters
      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"

  - assert:
      that:
      - result.rowcount == 1

  - name: postgresql_ext_version - try to install non-existent version
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: non_existent
      trust_input: no
    ignore_errors: yes

  - assert:
      that:
      - result.failed == true
      - result.msg == "Extension non_existent is not installed"

  ######################################################################
  # https://github.com/ansible-collections/community.general/issues/1095
  - name: Install postgis
    package:
      name: '{{ postgis }}'

  - name: Create postgis extension
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: postgis
      version: latest

  - assert:
      that:
      - result is changed

  # https://github.com/ansible-collections/community.postgresql/issues/137
  - name: Drop extension
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: '{{ test_ext }}'
      schema: '{{ test_schema }}'
      state: absent

  - name: Non standard version
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: '{{ test_ext }}'
      schema: '{{ test_schema }}'
      version: 0

  - name: Test
    <<: *task_parameters
    postgresql_info:
      <<: *pg_parameters

  - assert:
      that:
      - result['databases']['postgres']['extensions']['dummy']['extversion']['major'] == 0
      - result['databases']['postgres']['extensions']['dummy']['extversion']['minor'] == None
      - result['databases']['postgres']['extensions']['dummy']['extversion']['raw'] == '0'

  - name: Upgrade extension to the latest
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: '{{ test_ext }}'
      schema: '{{ test_schema }}'
      version: latest

  - name: Test
    <<: *task_parameters
    postgresql_info:
      <<: *pg_parameters

  - assert:
      that:
      - result['databases']['postgres']['extensions']['dummy']['extversion']['major'] == 3
      - result['databases']['postgres']['extensions']['dummy']['extversion']['minor'] == 0
      - result['databases']['postgres']['extensions']['dummy']['extversion']['raw'] == '3.0'

  # Cleanup:
  - name: postgresql_ext_version - drop the extension
    <<: *task_parameters
    postgresql_ext:
      <<: *pg_parameters
      name: "{{ test_ext }}"
      state: absent
      trust_input: no

  - name: postgresql_ext_version - drop the schema
    <<: *task_parameters
    postgresql_schema:
      <<: *pg_parameters
      name: "{{ test_schema }}"
      state: absent