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