goflare/sql/queries/records.sql

-- name: ListRecords :many
SELECT r.*, z.zone_id as cf_zone_id, z.name as zone_name
FROM records r
JOIN zones z ON z.id = r.zone_id
ORDER BY r.name;

-- name: ListRecordsByZone :many
SELECT r.*, z.zone_id as cf_zone_id, z.name as zone_name
FROM records r
JOIN zones z ON z.id = r.zone_id
WHERE r.zone_id = ?
ORDER BY r.name;

-- name: ListNonStaticRecords :many
SELECT r.*, z.zone_id as cf_zone_id, z.api_key as zone_api_key
FROM records r
JOIN zones z ON z.id = r.zone_id
WHERE r.is_static = 0;

-- name: GetRecord :one
SELECT r.*, z.zone_id as cf_zone_id, z.name as zone_name
FROM records r
JOIN zones z ON z.id = r.zone_id
WHERE r.id = ?;

-- name: CreateRecord :one
INSERT INTO records (zone_id, cf_record_id, name, type, content, proxied, is_static)
VALUES (?, ?, ?, ?, ?, ?, ?)
RETURNING *;

-- name: UpdateRecord :one
UPDATE records
SET name = ?, type = ?, content = ?, proxied = ?, is_static = ?,
    last_updated_at = CASE WHEN ? = 1 THEN NULL ELSE last_updated_at END,
    updated_at = CURRENT_TIMESTAMP
WHERE id = ?
RETURNING *;

-- name: UpdateRecordContent :exec
UPDATE records
SET content = ?, last_updated_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE id = ?;

-- name: DeleteRecord :exec
DELETE FROM records WHERE id = ?;

-- name: UpsertRecord :one
INSERT INTO records (zone_id, cf_record_id, name, type, content, proxied, is_static)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(zone_id, cf_record_id) DO UPDATE SET
  name = excluded.name,
  type = excluded.type,
  content = excluded.content,
  proxied = excluded.proxied,
  updated_at = CURRENT_TIMESTAMP
RETURNING *;

-- name: DeleteRecordByCfID :exec
DELETE FROM records WHERE zone_id = ? AND cf_record_id = ?;

-- name: ListRecordCfIDsByZone :many
SELECT cf_record_id FROM records WHERE zone_id = ?;