/*+ exists */
SELECT COUNT(*) as count FROM google.compute.firewalls
WHERE project = '{{ project }}'
AND name = '{{ fw_name }}'
/*+ create */
INSERT INTO google.compute.firewalls
(
project,
name,
network,
direction,
sourceRanges,
allowed
)
SELECT
'{{ project }}',
'{{ fw_name }}',
'{{ vpc_link }}',
'{{ fw_direction }}',
'{{ fw_source_ranges }}',
'{{ fw_allowed }}'
/*+ statecheck, retries=5, retry_delay=10 */
SELECT COUNT(*) as count FROM
(
SELECT
network = '{{ vpc_link }}' as test_network,
direction = '{{ fw_direction }}' as test_direction,
JSON_EQUAL(allowed, '{{ fw_allowed }}') as test_allowed,
JSON_EQUAL(sourceRanges, '{{ fw_source_ranges }}') as test_source_ranges
FROM google.compute.firewalls
WHERE project = '{{ project }}'
AND name = '{{ fw_name }}'
) t
WHERE test_network = 1
AND test_direction = 1
AND test_allowed = 1
AND test_source_ranges = 1
/*+ update */
UPDATE google.compute.firewalls
SET
network = '{{ vpc_link }}',
direction = '{{ fw_direction }}',
sourceRanges = '{{ fw_source_ranges }}',
allowed = '{{ fw_allowed }}'
WHERE firewall = '{{ fw_name }}'
AND project = '{{ project }}'
/*+ delete, retries=20, retry_delay=10 */
DELETE FROM google.compute.firewalls
WHERE project = '{{ project }}'
AND firewall = '{{ fw_name }}'