INSTALL dns FROM community;
LOAD dns;

-- Performs a forward DNS lookup to resolve a hostname to its first IPv4 address.
D SELECT dns_lookup('google.com') as ip;
┌─────────────────┐
│       ip        │
│     varchar     │
├─────────────────┤
│ 142.251.209.142 │
└─────────────────┘

-- Performs a DNS lookup to resolve all TXT records for a hostname.
D SELECT unnest(dns_lookup_all('google.com', 'TXT')) as txt_record order by txt_record ASC LIMIT 5;
┌───────────────────────────────────────────────────────────────────────────────────────────────┐
│                                          txt_record                                           │
│                                            varchar                                            │
├───────────────────────────────────────────────────────────────────────────────────────────────┤
│ MS=E4A68B9AB2BB9670BCE15412F62916164C0B20BB                                                   │
│ apple-domain-verification=30afIBcvSuDV2PLX                                                    │
│ cisco-ci-domain-verification=47c38bc8c4b74b7233e9053220c1bbe76bcc1cd33c7acf7acd36cd6a5332004b │
│ docusign=05958488-4752-4ef2-95eb-aa7ba8a3bd0e                                                 │
│ docusign=1b0a6754-49b1-4db5-8540-d2c12664b289                                                 │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

-- Performs a reverse DNS lookup to resolve an IP address to its hostname.
D SELECT reverse_dns_lookup('8.8.8.8') as hostname;
┌────────────┐
│  hostname  │
│  varchar   │
├────────────┤
│ dns.google │
└────────────┘

-- Returns all TXT records for a hostname as a table.
D SELECT * FROM corey('lastweekinaws.com') order by txt_record ASC;
┌───────────────────────────────────────────────────────────────────────────────┐
│                                  txt_record                                   │
│                                    varchar                                    │
├───────────────────────────────────────────────────────────────────────────────┤
│ google-site-verification=2cfGboK6oBt4GlzI62WGmKWI_SjoDEKRSaAgmZsQZlo          │
│ google-site-verification=FRrs1UZEfmPke4kYWmYH0wcOPwcMLM0pphvpyhGCa2w          │
│ google-site-verification=FuHv5niDbZdqLlXOlvyqokvFJDso9WtDAE3-zHJgz40          │
│ google-site-verification=VE5d97aE3ZCw4GzBHIKKeUGKaCYs2evsGq3QCM0t87I          │
│ v=spf1 include:_spf.google.com include:sendgrid.net include:spf.revue.co ~all │
└───────────────────────────────────────────────────────────────────────────────┘

-- Sets the DNS resolver to use for lookups (e.g. 'google' or 'cloudflare').
-- Hint: The C-API doesn't currently support using SET commands. The usage of a SELECT is a workaround.
D SELECT set_dns_config('google');
┌───────────────────────────────────────┐
│       set_dns_config('google')        │
│                varchar                │
├───────────────────────────────────────┤
│ DNS configuration updated to 'google' │
└───────────────────────────────────────┘

-- Set concurrency limit to 250 (default: 50)
D SELECT set_dns_concurrency_limit(250);
┌───────────────────────────────────────┐
│    set_dns_concurrency_limit(250)     │
│                varchar                │
├───────────────────────────────────────┤
│ DNS concurrency limit updated to 250  │
└───────────────────────────────────────┘

-- Set DNS cache size to 8192 (default: 1000)
D SELECT set_dns_cache_size(8192);
┌────────────────────────────────┐
│    set_dns_cache_size(8192)    │
│            varchar             │
├────────────────────────────────┤
│ DNS cache size updated to 8192 │
└────────────────────────────────┘

-- Get first IPv4 address (default behavior)
SELECT dns_lookup('google.com');
-- Returns: 142.250.181.206 (or similar)

-- Get TXT record
SELECT dns_lookup('_dmarc.google.com', 'TXT');
-- Returns: v=DMARC1; p=reject; rua=mailto:mailauth-reports@google.com

-- Get MX record
SELECT dns_lookup('google.com', 'MX');
-- Returns: 10 smtp.google.com.

-- Get CNAME record
SELECT dns_lookup('www.github.com', 'CNAME');
-- Returns: github.com.

-- Get all IPv4 addresses (default behavior)
SELECT dns_lookup_all('cloudflare.com');
-- Returns: [104.16.132.229, 104.16.133.229] (or similar)

-- Get all MX records
SELECT dns_lookup_all('google.com', 'MX');
-- Returns: [10 smtp.google.com.]

-- Get all TXT records
SELECT dns_lookup_all('google.com', 'TXT');
-- Returns: [v=spf1 include:_spf.google.com ~all, google-site-verification=..., ...]

-- Unnest to get individual records
SELECT unnest(dns_lookup_all('google.com', 'TXT')) as txt_record;

SELECT reverse_dns_lookup('8.8.8.8');
-- Returns: dns.google

-- Switch to Google Public DNS
SELECT set_dns_config('google');
-- Returns: DNS configuration updated to 'google'

-- Switch to Cloudflare DNS
SELECT set_dns_config('cloudflare');
-- Returns: DNS configuration updated to 'cloudflare'

-- All subsequent queries use the new configuration
SELECT dns_lookup('example.com');

-- Reset to system default
SELECT set_dns_config('default');
-- Returns: DNS configuration updated to 'default'

-- Invalid preset returns error
SELECT set_dns_config('invalid');
-- Returns: Unknown preset 'invalid'. Supported: default, google, cloudflare, quad9

-- Set concurrency limit to 500 for high-throughput scenarios
SELECT set_dns_concurrency_limit(500);
-- Returns: Concurrency limit updated to 500

-- Set cache size to 8192 for larger workloads
SELECT set_dns_cache_size(8192);
-- Returns: DNS cache size updated to 8192

-- Set cache size to 2048 for smaller memory footprint
SELECT set_dns_cache_size(2048);
-- Returns: DNS cache size updated to 2048

-- Get all TXT records for a domain
SELECT * FROM corey('google.com');

-- Filter TXT records
SELECT * FROM corey('google.com')
WHERE txt_record LIKE '%spf%';

-- Count TXT records
SELECT COUNT(*) as record_count
FROM corey('google.com');

-- Query multiple domains using UNION ALL
SELECT 'google.com' as domain, * FROM corey('google.com')
UNION ALL
SELECT 'github.com' as domain, * FROM corey('github.com')
UNION ALL
SELECT 'cloudflare.com' as domain, * FROM corey('cloudflare.com');

-- Find domains with DMARC records
SELECT '_dmarc.google.com' as domain, * FROM corey('_dmarc.google.com')
WHERE txt_record LIKE 'v=DMARC%'
UNION ALL
SELECT '_dmarc.github.com' as domain, * FROM corey('_dmarc.github.com')
WHERE txt_record LIKE 'v=DMARC%';

-- Alternative: Use dns_lookup_all for dynamic queries with columns
SELECT
    domain,
    dns_lookup_all(domain, 'TXT') as txt_records
FROM (VALUES ('google.com'), ('github.com')) AS domains(domain)
WHERE dns_lookup_all(domain, 'TXT') IS NOT NULL;