1# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru>
2# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)
3# Tests for postgresql_ext version option
4
5- vars:
6    test_ext: dummy
7    test_schema: schema1
8    task_parameters: &task_parameters
9      become_user: '{{ pg_user }}'
10      become: yes
11      register: result
12    pg_parameters: &pg_parameters
13      login_user: '{{ pg_user }}'
14      login_db: postgres
15
16  block:
17  # Preparation:
18  - name: postgresql_ext_version - create schema schema1
19    <<: *task_parameters
20    postgresql_schema:
21      <<: *pg_parameters
22      name: "{{ test_schema }}"
23
24  # Do tests:
25  - name: postgresql_ext_version - create extension of specific version, check mode
26    <<: *task_parameters
27    postgresql_ext:
28      <<: *pg_parameters
29      name: "{{ test_ext }}"
30      schema: "{{ test_schema }}"
31      version: '1.0'
32    check_mode: yes
33
34  - assert:
35      that:
36      - result is changed
37
38  - name: postgresql_ext_version - check that nothing was actually changed
39    <<: *task_parameters
40    postgresql_query:
41      <<: *pg_parameters
42      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}'"
43
44  - assert:
45      that:
46      - result.rowcount == 0
47
48  - name: postgresql_ext_version - create extension of specific version
49    <<: *task_parameters
50    postgresql_ext:
51      <<: *pg_parameters
52      name: "{{ test_ext }}"
53      schema: "{{ test_schema }}"
54      version: '1.0'
55
56  - assert:
57      that:
58      - result is changed
59      - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\" WITH SCHEMA \"{{ test_schema }}\" VERSION '1.0'"]
60
61  - name: postgresql_ext_version - check
62    <<: *task_parameters
63    postgresql_query:
64      <<: *pg_parameters
65      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"
66
67  - assert:
68      that:
69      - result.rowcount == 1
70
71  - name: postgresql_ext_version - try to create extension of the same version again in check_mode
72    <<: *task_parameters
73    postgresql_ext:
74      <<: *pg_parameters
75      name: "{{ test_ext }}"
76      schema: "{{ test_schema }}"
77      version: '1.0'
78    check_mode: yes
79
80  - assert:
81      that:
82      - result is not changed
83
84  - name: postgresql_ext_version - check
85    <<: *task_parameters
86    postgresql_query:
87      <<: *pg_parameters
88      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"
89
90  - assert:
91      that:
92      - result.rowcount == 1
93
94  - name: postgresql_ext_version - try to create extension of the same version again in actual mode
95    <<: *task_parameters
96    postgresql_ext:
97      <<: *pg_parameters
98      name: "{{ test_ext }}"
99      schema: "{{ test_schema }}"
100      version: '1.0'
101
102  - assert:
103      that:
104      - result is not changed
105
106  - name: postgresql_ext_version - check
107    <<: *task_parameters
108    postgresql_query:
109      <<: *pg_parameters
110      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"
111
112  - assert:
113      that:
114      - result.rowcount == 1
115
116  - name: postgresql_ext_version - update the extension to the next version in check_mode
117    <<: *task_parameters
118    postgresql_ext:
119      <<: *pg_parameters
120      name: "{{ test_ext }}"
121      schema: "{{ test_schema }}"
122      version: '2.0'
123    check_mode: yes
124
125  - assert:
126      that:
127      - result is changed
128
129  - name: postgresql_ext_version - check, the version must be 1.0
130    <<: *task_parameters
131    postgresql_query:
132      <<: *pg_parameters
133      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '1.0'"
134
135  - assert:
136      that:
137      - result.rowcount == 1
138
139  - name: postgresql_ext_version - update the extension to the next version
140    <<: *task_parameters
141    postgresql_ext:
142      <<: *pg_parameters
143      name: "{{ test_ext }}"
144      schema: "{{ test_schema }}"
145      version: '2.0'
146
147  - assert:
148      that:
149      - result is changed
150      - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE TO '2.0'"]
151
152  - name: postgresql_ext_version - check, the version must be 2.0
153    <<: *task_parameters
154    postgresql_query:
155      <<: *pg_parameters
156      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '2.0'"
157
158  - assert:
159      that:
160      - result.rowcount == 1
161
162  - name: postgresql_ext_version - check that version won't be changed if version won't be passed
163    <<: *task_parameters
164    postgresql_ext:
165      <<: *pg_parameters
166      name: "{{ test_ext }}"
167      schema: "{{ test_schema }}"
168
169  - assert:
170      that:
171      - result is not changed
172
173  - name: postgresql_ext_version - check, the version must be 2.0
174    <<: *task_parameters
175    postgresql_query:
176      <<: *pg_parameters
177      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '2.0'"
178
179  - assert:
180      that:
181      - result.rowcount == 1
182
183  - name: postgresql_ext_version - update the extension to the latest version
184    <<: *task_parameters
185    postgresql_ext:
186      <<: *pg_parameters
187      name: "{{ test_ext }}"
188      schema: "{{ test_schema }}"
189      version: latest
190
191  - assert:
192      that:
193      - result is changed
194      - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE TO '3.0'"]
195
196  - name: postgresql_ext_version - check
197    <<: *task_parameters
198    postgresql_query:
199      <<: *pg_parameters
200      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"
201
202  - assert:
203      that:
204      - result.rowcount == 1
205
206  - name: postgresql_ext_version - try to update the extension to the latest version again
207    <<: *task_parameters
208    postgresql_ext:
209      <<: *pg_parameters
210      name: "{{ test_ext }}"
211      schema: "{{ test_schema }}"
212      version: latest
213
214  - assert:
215      that:
216      - result is not changed
217
218  - name: postgresql_ext_version - try to downgrade the extension version, must fail
219    <<: *task_parameters
220    postgresql_ext:
221      <<: *pg_parameters
222      name: "{{ test_ext }}"
223      schema: "{{ test_schema }}"
224      version: '1.0'
225    ignore_errors: yes
226
227  - assert:
228      that:
229      - result.failed == true
230
231  - name: postgresql_ext_version - drop the extension in check_mode
232    <<: *task_parameters
233    postgresql_ext:
234      <<: *pg_parameters
235      name: "{{ test_ext }}"
236      state: absent
237    check_mode: yes
238
239  - assert:
240      that:
241      - result is changed
242
243  - name: postgresql_ext_version - check that extension exists
244    <<: *task_parameters
245    postgresql_query:
246      <<: *pg_parameters
247      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"
248
249  - assert:
250      that:
251      - result.rowcount == 1
252
253  - name: postgresql_ext_version - drop the extension in actual mode
254    <<: *task_parameters
255    postgresql_ext:
256      <<: *pg_parameters
257      name: "{{ test_ext }}"
258      state: absent
259
260  - assert:
261      that:
262      - result is changed
263
264  - name: postgresql_ext_version - check that extension doesn't exist after the prev step
265    <<: *task_parameters
266    postgresql_query:
267      <<: *pg_parameters
268      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}'"
269
270  - assert:
271      that:
272      - result.rowcount == 0
273
274  - name: postgresql_ext_version - try to drop the non-existent extension again
275    <<: *task_parameters
276    postgresql_ext:
277      <<: *pg_parameters
278      name: "{{ test_ext }}"
279      state: absent
280
281  - assert:
282      that:
283      - result is not changed
284
285  - name: postgresql_ext_version - create the extension without passing version
286    <<: *task_parameters
287    postgresql_ext:
288      <<: *pg_parameters
289      name: "{{ test_ext }}"
290
291  - assert:
292      that:
293      - result is changed
294      - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\""]
295
296  - name: postgresql_ext_version - check
297    <<: *task_parameters
298    postgresql_query:
299      <<: *pg_parameters
300      query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '3.0'"
301
302  - assert:
303      that:
304      - result.rowcount == 1
305
306  - name: postgresql_ext_version - try to install non-existent version
307    <<: *task_parameters
308    postgresql_ext:
309      <<: *pg_parameters
310      name: non_existent
311    ignore_errors: yes
312
313  - assert:
314      that:
315      - result.failed == true
316      - result.msg == "Extension non_existent is not installed"
317
318  # Cleanup:
319  - name: postgresql_ext_version - drop the extension
320    <<: *task_parameters
321    postgresql_ext:
322      <<: *pg_parameters
323      name: "{{ test_ext }}"
324      state: absent
325
326  - name: postgresql_ext_version - drop the schema
327    <<: *task_parameters
328    postgresql_schema:
329      <<: *pg_parameters
330      name: "{{ test_schema }}"
331      state: absent
332