1# Copyright: (c) 2019, Tobias Birkefeld (@tcraxs) <t@craxs.de>
2# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt)
3
4# Preparation for tests.
5- name: postgresql_sequence - create DB
6  become_user: "{{ pg_user }}"
7  become: yes
8  postgresql_db:
9    state: present
10    name: "{{ db_name }}"
11    owner: "{{ db_user1 }}"
12    login_user: "{{ pg_user }}"
13
14- name: postgresql_sequence - create a user to be owner of a database
15  become_user: "{{ pg_user }}"
16  become: yes
17  postgresql_user:
18    name: "{{ db_user1 }}"
19    state: present
20    encrypted: yes
21    password: password
22    role_attr_flags: LOGIN
23    db: "{{ db_name }}"
24    login_user: "{{ pg_user }}"
25
26- name: Create a user to be owner of a sequence
27  become_user: "{{ pg_user }}"
28  become: yes
29  postgresql_user:
30    name: "{{ db_user2 }}"
31    state: present
32    encrypted: yes
33    password: password
34    role_attr_flags: LOGIN
35    db: "{{ db_name }}"
36    login_user: "{{ pg_user }}"
37
38- name: postgresql_sequence - create a schema
39  become_user: "{{ pg_user }}"
40  become: yes
41  postgresql_schema:
42    db: "{{ db_name }}"
43    login_user: "{{ pg_user }}"
44    name: foobar_schema
45
46####################
47# Test: create sequence in checkmode
48- name: postgresql_sequence - create a new sequence with name "foobar" in check_mode
49  become_user: "{{ pg_user }}"
50  become: yes
51  check_mode: yes
52  postgresql_sequence:
53    db: "{{ db_name }}"
54    login_user: "{{ pg_user }}"
55    name: foobar
56  register: result
57
58# Checks
59- name: postgresql_sequence - check with assert the output
60  assert:
61    that:
62      - result is changed
63      - result.sequence == 'foobar'
64      - result.queries == ["CREATE SEQUENCE \"public\".\"foobar\""]
65
66# Real SQL check
67- name: postgresql_sequence - check that the new sequence "foobar" not exists
68  become: yes
69  become_user: "{{ pg_user }}"
70  postgresql_query:
71    db: "{{ db_name }}"
72    login_user: "{{ pg_user }}"
73    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar'"
74  register: result
75
76- name: postgresql_sequence - check with assert the output
77  assert:
78    that:
79      - result.rowcount == 0
80      - result.statusmessage == 'SELECT 0'
81
82####################
83# Test: create sequence
84- name: postgresql_sequence - create a new sequence with name "foobar"
85  become_user: "{{ pg_user }}"
86  become: yes
87  postgresql_sequence:
88    db: "{{ db_name }}"
89    login_user: "{{ pg_user }}"
90    name: foobar
91  register: result
92
93# Checks
94- name: postgresql_sequence - check with assert the output
95  assert:
96    that:
97      - result is changed
98      - result.sequence == 'foobar'
99      - result.queries == ["CREATE SEQUENCE \"public\".\"foobar\""]
100
101# Real SQL check
102- name: postgresql_sequence - check that the new sequence "foobar" exists
103  become_user: "{{ pg_user }}"
104  become: yes
105  postgresql_query:
106    db: "{{ db_name }}"
107    login_user: "{{ pg_user }}"
108    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar'"
109  register: result
110
111- name: postgresql_sequence - check with assert the output
112  assert:
113    that:
114      - result.rowcount == 1
115
116####################
117# Test: drop sequence in checkmode
118- name: postgresql_sequence - drop a sequence called foobar
119  become_user: "{{ pg_user }}"
120  become: yes
121  check_mode: yes
122  postgresql_sequence:
123    db: "{{ db_name }}"
124    login_user: "{{ pg_user }}"
125    name: foobar
126    state: absent
127  register: result
128
129# Checks
130- name: postgresql_sequence - check with assert the output
131  assert:
132    that:
133      - result is changed
134      - result.sequence == 'foobar'
135      - result.queries == ["DROP SEQUENCE \"public\".\"foobar\""]
136
137# Real SQL check
138- name: postgresql_sequence - check that the sequence "foobar" still exists
139  become_user: "{{ pg_user }}"
140  become: yes
141  postgresql_query:
142    db: "{{ db_name }}"
143    login_user: "{{ pg_user }}"
144    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar'"
145  register: result
146
147- name: postgresql_sequence - check with assert the output
148  assert:
149    that:
150      - result.rowcount == 1
151
152####################
153# Test: drop sequence
154- name: postgresql_sequence - drop a sequence called foobar
155  become_user: "{{ pg_user }}"
156  become: yes
157  postgresql_sequence:
158    db: "{{ db_name }}"
159    login_user: "{{ pg_user }}"
160    name: foobar
161    state: absent
162  register: result
163
164# Checks
165- name: postgresql_sequence - check with assert the output
166  assert:
167    that:
168      - result is changed
169      - result.sequence == 'foobar'
170      - result.queries == ["DROP SEQUENCE \"public\".\"foobar\""]
171
172# Real SQL check
173- name: postgresql_sequence - check that the sequence "foobar" not exists
174  become_user: "{{ pg_user }}"
175  become: yes
176  postgresql_query:
177    db: "{{ db_name }}"
178    login_user: "{{ pg_user }}"
179    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar'"
180  register: result
181
182- name: postgresql_sequence - check with assert the output
183  assert:
184    that:
185      - result.rowcount == 0
186
187####################
188# Test: drop nonexistent sequence
189- name: postgresql_sequence - drop a sequence called foobar which does not exists
190  become_user: "{{ pg_user }}"
191  become: yes
192  postgresql_sequence:
193    db: "{{ db_name }}"
194    login_user: "{{ pg_user }}"
195    name: foobar
196    state: absent
197  register: result
198
199# Checks
200- name: postgresql_sequence - check with assert the output
201  assert:
202    that:
203      - result is not changed
204      - result.sequence == 'foobar'
205      - result.queries == []
206
207# Real SQL check
208- name: postgresql_sequence - check that the sequence "foobar" not exists
209  become_user: "{{ pg_user }}"
210  become: yes
211  postgresql_query:
212    db: "{{ db_name }}"
213    login_user: "{{ pg_user }}"
214    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar'"
215  register: result
216
217- name: postgresql_sequence - check with assert the output
218  assert:
219    that:
220      - result.rowcount == 0
221
222####################
223# Test: create sequence with options
224- name: postgresql_sequence - create an descending sequence called foobar_desc, starting at 101 and which cycle between 1 to 1000
225  become_user: "{{ pg_user }}"
226  become: yes
227  postgresql_sequence:
228    db: "{{ db_name }}"
229    login_user: "{{ pg_user }}"
230    name: foobar_desc
231    increment: -1
232    start: 101
233    minvalue: 1
234    maxvalue: 1000
235    cycle: yes
236  register: result
237
238# Checks
239- name: postgresql_sequence - check with assert the output
240  assert:
241    that:
242      - result is changed
243      - result.sequence == 'foobar_desc'
244      - result.increment == '-1'
245      - result.minvalue == '1'
246      - result.maxvalue == '1000'
247      - result.cycle == 'YES'
248      - result.queries == ["CREATE SEQUENCE \"public\".\"foobar_desc\" INCREMENT BY -1 MINVALUE 1 MAXVALUE 1000 START WITH 101 CYCLE"]
249
250# Real SQL check
251- name: postgresql_sequence - check that the new sequence "foobar_desc" exists
252  postgresql_query:
253    db: "{{ db_name }}"
254    login_user: "{{ pg_user }}"
255    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar_desc'"
256  register: result
257
258- name: postgresql_sequence - check with assert the output
259  assert:
260    that:
261      - result.rowcount == 1
262
263####################
264# Test: rename a sequence in checkmode
265- name: postgresql_sequence - rename an existing sequence named foobar_desc to foobar_with_options
266  become_user: "{{ pg_user }}"
267  become: yes
268  check_mode: yes
269  postgresql_sequence:
270    db: "{{ db_name }}"
271    login_user: "{{ pg_user }}"
272    name: foobar_desc
273    rename_to: foobar_with_options
274  register: result
275
276# Checks
277- name: postgresql_sequence - check with assert the output
278  assert:
279    that:
280      - result is changed
281      - result.sequence == 'foobar_desc'
282      - result.newname == 'foobar_with_options'
283      - result.queries == ["ALTER SEQUENCE \"public\".\"foobar_desc\" RENAME TO \"foobar_with_options\""]
284
285# Real SQL check
286- name: postgresql_sequence - check that the sequence "foobar_desc" still exists and is not renamed
287  become_user: "{{ pg_user }}"
288  become: yes
289  postgresql_query:
290    db: "{{ db_name }}"
291    login_user: "{{ pg_user }}"
292    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar_desc'"
293  register: result
294
295- name: postgresql_sequence - check with assert the output
296  assert:
297    that:
298      - result.rowcount == 1
299
300####################
301# Test: rename a sequence
302- name: postgresql_sequence - rename an existing sequence named foobar_desc to foobar_with_options
303  become_user: "{{ pg_user }}"
304  become: yes
305  postgresql_sequence:
306    db: "{{ db_name }}"
307    login_user: "{{ pg_user }}"
308    name: foobar_desc
309    rename_to: foobar_with_options
310  register: result
311
312# Checks
313- name: postgresql_sequence - check with assert the output
314  assert:
315    that:
316      - result is changed
317      - result.sequence == 'foobar_desc'
318      - result.newname == 'foobar_with_options'
319      - result.queries == ["ALTER SEQUENCE \"public\".\"foobar_desc\" RENAME TO \"foobar_with_options\""]
320
321# Real SQL check
322- name: postgresql_sequence - check that the renamed sequence "foobar_with_options" exists
323  become_user: "{{ pg_user }}"
324  become: yes
325  postgresql_query:
326    db: "{{ db_name }}"
327    login_user: "{{ pg_user }}"
328    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar_with_options'"
329  register: result
330
331- name: postgresql_sequence - check with assert the output
332  assert:
333    that:
334      - result.rowcount == 1
335
336####################
337# Test: change schema of a sequence in checkmode
338- name: postgresql_sequence - change schema of an existing sequence from public to foobar_schema
339  become_user: "{{ pg_user }}"
340  become: yes
341  check_mode: yes
342  postgresql_sequence:
343    db: "{{ db_name }}"
344    login_user: "{{ pg_user }}"
345    name: foobar_with_options
346    newschema: foobar_schema
347  register: result
348
349# Checks
350- name: postgresql_sequence - check with assert the output
351  assert:
352    that:
353      - result is changed
354      - result.sequence == 'foobar_with_options'
355      - result.schema == 'public'
356      - result.newschema == 'foobar_schema'
357      - result.queries == ["ALTER SEQUENCE \"public\".\"foobar_with_options\" SET SCHEMA \"foobar_schema\""]
358
359# Real SQL check
360- name: postgresql_sequence - check that the sequence "foobar_with_options" still exists in the old schema
361  become_user: "{{ pg_user }}"
362  become: yes
363  postgresql_query:
364    db: "{{ db_name }}"
365    login_user: "{{ pg_user }}"
366    query: "SELECT sequence_name,sequence_schema FROM information_schema.sequences WHERE sequence_name = 'foobar_with_options' AND sequence_schema = 'public'"
367  register: result
368
369- name: postgresql_sequence - check with assert the output
370  assert:
371    that:
372      - result.rowcount == 1
373
374####################
375# Test: change schema of a sequence
376- name: postgresql_sequence - change schema of an existing sequence from public to foobar_schema
377  become_user: "{{ pg_user }}"
378  become: yes
379  postgresql_sequence:
380    db: "{{ db_name }}"
381    login_user: "{{ pg_user }}"
382    name: foobar_with_options
383    newschema: foobar_schema
384  register: result
385
386# Checks
387- name: postgresql_sequence - check with assert the output
388  assert:
389    that:
390      - result is changed
391      - result.sequence == 'foobar_with_options'
392      - result.schema == 'public'
393      - result.newschema == 'foobar_schema'
394      - result.queries == ["ALTER SEQUENCE \"public\".\"foobar_with_options\" SET SCHEMA \"foobar_schema\""]
395
396# Real SQL check
397- name: postgresql_sequence - check that the sequence "foobar_with_options" exists in new schema
398  become_user: "{{ pg_user }}"
399  become: yes
400  postgresql_query:
401    db: "{{ db_name }}"
402    login_user: "{{ pg_user }}"
403    query: "SELECT sequence_name,sequence_schema FROM information_schema.sequences WHERE sequence_name = 'foobar_with_options' AND sequence_schema = 'foobar_schema'"
404  register: result
405
406- name: postgresql_sequence - check with assert the output
407  assert:
408    that:
409      - result.rowcount == 1
410
411####################
412# Test: change owner of a sequence in checkmode
413- name: postgresql_sequence - change owner of an existing sequence from "{{ pg_user }}" to "{{ db_user1 }}"
414  become_user: "{{ pg_user }}"
415  become: yes
416  check_mode: yes
417  postgresql_sequence:
418    db: "{{ db_name }}"
419    login_user: "{{ pg_user }}"
420    name: foobar_with_options
421    schema: foobar_schema
422    owner: "{{ db_user1 }}"
423  register: result
424
425# Checks
426- name: postgresql_sequence - check with assert the output
427  assert:
428    that:
429      - result is changed
430      - result.sequence == 'foobar_with_options'
431      - result.owner == "{{ pg_user }}"
432      - result.queries == ["ALTER SEQUENCE \"foobar_schema\".\"foobar_with_options\" OWNER TO \"{{ db_user1 }}\""]
433
434# Real SQL check
435- name: postgresql_sequence - check that the sequence "foobar_with_options" has still the old owner
436  become_user: "{{ pg_user }}"
437  become: yes
438  postgresql_query:
439    db: "{{ db_name }}"
440    login_user: "{{ pg_user }}"
441    query: "SELECT c.relname,a.rolname,n.nspname
442            FROM pg_class as c
443            JOIN pg_authid as a on (c.relowner = a.oid)
444            JOIN pg_namespace as n on (c.relnamespace = n.oid)
445            WHERE c.relkind = 'S' and
446            c.relname = 'foobar_with_options' and
447            n.nspname = 'foobar_schema' and
448            a.rolname = '{{ pg_user }}'"
449  register: result
450
451- name: postgresql_sequence - check with assert the output
452  assert:
453    that:
454      - result.rowcount == 1
455
456####################
457# Test: change owner of a sequence
458- name: postgresql_sequence - change owner of an existing sequence from "{{ pg_user }}" to "{{ db_user1 }}"
459  become_user: "{{ pg_user }}"
460  become: yes
461  postgresql_sequence:
462    db: "{{ db_name }}"
463    login_user: "{{ pg_user }}"
464    name: foobar_with_options
465    schema: foobar_schema
466    owner: "{{ db_user1 }}"
467  register: result
468
469# Checks
470- name: postgresql_sequence - check with assert the output
471  assert:
472    that:
473      - result is changed
474      - result.sequence == 'foobar_with_options'
475      - result.owner == "{{ pg_user }}"
476      - result.queries == ["ALTER SEQUENCE \"foobar_schema\".\"foobar_with_options\" OWNER TO \"{{ db_user1 }}\""]
477
478# Real SQL check
479- name: postgresql_sequence - check that the sequence "foobar_with_options" has a new owner
480  become_user: "{{ pg_user }}"
481  become: yes
482  postgresql_query:
483    db: "{{ db_name }}"
484    login_user: "{{ pg_user }}"
485    query: "SELECT c.relname,a.rolname,n.nspname
486            FROM pg_class as c
487            JOIN pg_authid as a on (c.relowner = a.oid)
488            JOIN pg_namespace as n on (c.relnamespace = n.oid)
489            WHERE c.relkind = 'S' and
490            c.relname = 'foobar_with_options' and
491            n.nspname = 'foobar_schema' and
492            a.rolname = '{{ db_user1 }}'"
493  register: result
494
495- name: postgresql_sequence - check with assert the output
496  assert:
497    that:
498      - result.rowcount == 1
499
500####################
501# Test: drop sequence with cascade
502
503# CREATE SEQUENCE seq1;
504# CREATE TABLE t1 (f1 INT NOT NULL DEFAULT nextval('seq1'));
505# DROP SEQUENCE seq1 CASCADE;
506- name: postgresql_sequence - create sequence for drop cascade test
507  become_user: "{{ pg_user }}"
508  become: yes
509  postgresql_sequence:
510    db: "{{ db_name }}"
511    login_user: "{{ pg_user }}"
512    name: seq1
513
514- name: postgresql_sequence - create table which use sequence for drop cascade test
515  become_user: "{{ pg_user }}"
516  become: yes
517  postgresql_table:
518    db: "{{ db_name }}"
519    login_user: "{{ pg_user }}"
520    name: t1
521    columns:
522    - f1 INT NOT NULL DEFAULT nextval('seq1')
523
524####################
525# Test: drop sequence with cascade in checkmode
526- name: postgresql_sequence - drop with cascade a sequence called seq1
527  become_user: "{{ pg_user }}"
528  become: yes
529  check_mode: yes
530  postgresql_sequence:
531    db: "{{ db_name }}"
532    login_user: "{{ pg_user }}"
533    name: seq1
534    state: absent
535    cascade: yes
536  register: result
537
538# Checks
539- name: postgresql_sequence - check with assert the output
540  assert:
541    that:
542      - result is changed
543      - result.sequence == 'seq1'
544      - result.queries == ["DROP SEQUENCE \"public\".\"seq1\" CASCADE"]
545
546# Real SQL check
547- name: postgresql_sequence - check that the sequence "seq1" still exists
548  become_user: "{{ pg_user }}"
549  become: yes
550  postgresql_query:
551    db: "{{ db_name }}"
552    login_user: "{{ pg_user }}"
553    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'seq1'"
554  register: result
555
556- name: postgresql_sequence - check with assert the output
557  assert:
558    that:
559      - result.rowcount == 1
560
561####################
562# Test: drop sequence with cascade
563- name: postgresql_sequence - drop with cascade a sequence called seq1
564  become_user: "{{ pg_user }}"
565  become: yes
566  postgresql_sequence:
567    db: "{{ db_name }}"
568    login_user: "{{ pg_user }}"
569    name: seq1
570    state: absent
571    cascade: yes
572  register: result
573
574# Checks
575- name: postgresql_sequence - check with assert the output
576  assert:
577    that:
578      - result is changed
579      - result.sequence == 'seq1'
580      - result.queries == ["DROP SEQUENCE \"public\".\"seq1\" CASCADE"]
581
582# Real SQL check
583- name: postgresql_sequence - check that the sequence "seq1" not exists
584  become_user: "{{ pg_user }}"
585  become: yes
586  postgresql_query:
587    db: "{{ db_name }}"
588    login_user: "{{ pg_user }}"
589    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'seq1'"
590  register: result
591
592- name: postgresql_sequence - check with assert the output
593  assert:
594    that:
595      - result.rowcount == 0
596
597####################
598# Test: create sequence with owner in checkmode
599- name: postgresql_sequence - create a new sequence with name "foobar2" with owner "{{ db_user2 }}"
600  become_user: "{{ pg_user }}"
601  become: yes
602  check_mode: yes
603  postgresql_sequence:
604    db: "{{ db_name }}"
605    login_user: "{{ pg_user }}"
606    name: foobar2
607    owner: "{{ db_user2 }}"
608  register: result
609
610# Checks
611- name: postgresql_sequence - check with assert the output
612  assert:
613    that:
614      - result is changed
615      - result.sequence == 'foobar2'
616      - result.queries == ["CREATE SEQUENCE \"public\".\"foobar2\"", "ALTER SEQUENCE \"public\".\"foobar2\" OWNER TO \"ansible_db_user2\""]
617
618# Real SQL check
619- name: postgresql_sequence - check that the new sequence "foobar2" does not exists
620  become_user: "{{ pg_user }}"
621  become: yes
622  postgresql_query:
623    db: "{{ db_name }}"
624    login_user: "{{ pg_user }}"
625    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar2'"
626  register: result
627
628- name: postgresql_sequence - check with assert the output
629  assert:
630    that:
631      - result.rowcount == 0
632
633####################
634# Test: create sequence with owner
635- name: postgresql_sequence - create a new sequence with name "foobar2" with owner "{{ db_user2 }}"
636  become_user: "{{ pg_user }}"
637  become: yes
638  postgresql_sequence:
639    db: "{{ db_name }}"
640    login_user: "{{ pg_user }}"
641    name: foobar2
642    owner: "{{ db_user2 }}"
643  register: result
644
645# Checks
646- name: postgresql_sequence - check with assert the output
647  assert:
648    that:
649      - result is changed
650      - result.sequence == 'foobar2'
651      - result.queries == ["CREATE SEQUENCE \"public\".\"foobar2\"", "ALTER SEQUENCE \"public\".\"foobar2\" OWNER TO \"ansible_db_user2\""]
652
653# Real SQL check
654- name: postgresql_sequence - check that the new sequence "foobar2" exists
655  become_user: "{{ pg_user }}"
656  become: yes
657  postgresql_query:
658    db: "{{ db_name }}"
659    login_user: "{{ pg_user }}"
660    query: "SELECT sequence_name FROM information_schema.sequences WHERE sequence_name = 'foobar2'"
661  register: result
662
663- name: postgresql_sequence - check with assert the output
664  assert:
665    that:
666      - result.rowcount == 1
667
668- name: postgresql_sequence - check that the sequence "foobar2" has owner "{{ db_user2 }}"
669  become_user: "{{ pg_user }}"
670  become: yes
671  postgresql_query:
672    db: "{{ db_name }}"
673    login_user: "{{ pg_user }}"
674    query: "SELECT c.relname,a.rolname,n.nspname
675            FROM pg_class as c
676            JOIN pg_authid as a on (c.relowner = a.oid)
677            JOIN pg_namespace as n on (c.relnamespace = n.oid)
678            WHERE c.relkind = 'S' and
679            c.relname = 'foobar2' and
680            n.nspname = 'public' and
681            a.rolname = '{{ db_user2 }}'"
682  register: result
683
684- name: postgresql_sequence - check with assert the output
685  assert:
686    that:
687      - result.rowcount == 1
688
689# Cleanup
690- name: postgresql_sequence - destroy DB
691  become_user: "{{ pg_user }}"
692  become: yes
693  postgresql_db:
694    state: absent
695    name: "{{ db_name }}"
696    login_user: "{{ pg_user }}"
697