aws_route53

Components

Tables

    alias_target

    hosted_zone

    resource_record_set

Enums

    record_type

Code examples

Manage Route53

--- installs module
SELECT * FROM iasql_install('<modules>');
--- adds a new hosted zone
INSERT INTO hosted_zone (domain_name)
VALUES ('<domainName>');
--- check adds a new hosted zone
SELECT *
FROM hosted_zone
WHERE domain_name = '<domainName>';
--- check default record sets have been added
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<domainName>';
--- adds a new record to hosted zone
INSERT INTO resource_record_set (name, record_type, record, ttl, parent_hosted_zone_id)
SELECT '<resourceRecordSetName>', '<resourceRecordSetTypeCNAME>', '<resourceRecordSetRecord>',<resourceRecordSetTtl>, id
FROM hosted_zone
WHERE domain_name = '<domainName>';
--- check default record sets have been added
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<domainName>';
--- adds a new A record to hosted zone
BEGIN;
INSERT INTO load_balancer (load_balancer_name, scheme, load_balancer_type, ip_address_type)
VALUES ('<lbName>', '<lbScheme>', '<lbType>', '<lbIPAddressType>');

INSERT INTO alias_target (load_balancer_id)
VALUES ((SELECT id FROM load_balancer WHERE load_balancer_name = '<lbName>'));

INSERT INTO resource_record_set (name, record_type, parent_hosted_zone_id, alias_target_id)
SELECT '<aliasResourceRecordSetName>', '<resourceRecordSetTypeA>', hosted_zone.id, alias_target.id
FROM hosted_zone, alias_target
INNER JOIN load_balancer ON load_balancer.id = alias_target.load_balancer_id
WHERE domain_name = '<domainName>' AND load_balancer.load_balancer_name = '<lbName>';
COMMIT;
--- check alias target record has been added
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<domainName>';
--- adds a new record to hosted zone
INSERT INTO resource_record_set (name, record_type, record, ttl, parent_hosted_zone_id)
SELECT '<resourceRecordSetMultilineName>', '<resourceRecordSetTypeA>', '<resourceRecordSetRecordMultiline>',<resourceRecordSetTtl>, id
FROM hosted_zone
WHERE domain_name = '<replaceDomainName>';
--- check multiline record set have been added
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<replaceDomainName>';
--- updates a record name
UPDATE resource_record_set
SET name = '<resourceRecordSetMultilineNameReplace>'
FROM hosted_zone
WHERE domain_name = '<replaceDomainName>' AND name = '<resourceRecordSetMultilineName>';
--- check records after update
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<replaceDomainName>';
--- deletes the hosted zone with the same name
BEGIN;
DELETE FROM resource_record_set
USING hosted_zone
WHERE hosted_zone.id IN (SELECT id FROM hosted_zone WHERE domain_name = '<replaceDomainName>' ORDER BY ID DESC LIMIT 1);
DELETE FROM hosted_zone
WHERE id IN (SELECT id FROM hosted_zone WHERE domain_name = '<replaceDomainName>' ORDER BY ID DESC LIMIT 1);
COMMIT;
--- deletes records
DELETE FROM resource_record_set
USING hosted_zone
WHERE hosted_zone.id = parent_hosted_zone_id AND domain_name = '<replaceDomainName>';
--- check records after delete. SOA and NS recordsets have to be keeped
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<replaceDomainName>';
--- deletes mandatory records and hosted zone
BEGIN;
DELETE FROM resource_record_set
USING hosted_zone
WHERE hosted_zone.id = parent_hosted_zone_id AND domain_name = '<replaceDomainName>';
DELETE FROM hosted_zone
WHERE domain_name = '<replaceDomainName>';
COMMIT;
--- check records after delete
SELECT *
FROM resource_record_set
INNER JOIN hosted_zone ON hosted_zone.id = parent_hosted_zone_id
WHERE domain_name = '<replaceDomainName>';
--- check hosted zones after delete
SELECT *
FROM hosted_zone
WHERE domain_name = '<replaceDomainName>';