83 lines
2.9 KiB
SQL
83 lines
2.9 KiB
SQL
-- The campaign editor. Everything here is behind an admin session.
|
|
|
|
-- name: ListAdminCampaigns :many
|
|
SELECT c.id, c.slug, c.title, c.status::text AS status, c.goal_amount, o.name,
|
|
(SELECT COUNT(*) FROM pledges p WHERE p.campaign_id = c.id)::int AS pledge_count
|
|
FROM campaigns c
|
|
JOIN owners o ON o.id = c.owner_id
|
|
ORDER BY c.created_at DESC;
|
|
|
|
-- name: ListOwners :many
|
|
SELECT id, name, COALESCE(avatar_url, '') AS avatar_url
|
|
FROM owners
|
|
ORDER BY name ASC;
|
|
|
|
-- name: GetAdminCampaign :one
|
|
SELECT id, slug, title, category, summary, art, goal_amount,
|
|
ends_at, status::text AS status, owner_id
|
|
FROM campaigns
|
|
WHERE slug = @slug;
|
|
|
|
-- name: ListAdminTiers :many
|
|
SELECT t.id, t.type::text AS type, t.price, t.list_price, t.title, t.includes,
|
|
t.inventory_limit, t.estimated_delivery, t.featured,
|
|
(SELECT COUNT(*) FROM pledges p WHERE p.tier_id = t.id)::int AS pledge_count
|
|
FROM reward_tiers t
|
|
WHERE t.campaign_id = @campaign_id
|
|
ORDER BY t.position ASC;
|
|
|
|
-- name: UpdateCampaign :execrows
|
|
UPDATE campaigns
|
|
SET slug = @slug, title = @title, category = @category, summary = @summary,
|
|
art = @art, goal_amount = @goal_amount, ends_at = @ends_at,
|
|
status = @status::campaign_status, owner_id = @owner_id, updated_at = now()
|
|
WHERE id = @id;
|
|
|
|
-- name: DeleteStorySections :exec
|
|
DELETE FROM story_sections WHERE campaign_id = @campaign_id;
|
|
|
|
-- name: InsertStorySection :exec
|
|
INSERT INTO story_sections (id, campaign_id, heading, body, position)
|
|
VALUES (@id, @campaign_id, @heading, @body, @position);
|
|
|
|
-- name: ListTierPledgeCounts :many
|
|
SELECT t.id, (SELECT COUNT(*) FROM pledges p WHERE p.tier_id = t.id)::int AS pledge_count
|
|
FROM reward_tiers t
|
|
WHERE t.campaign_id = @campaign_id;
|
|
|
|
-- name: ParkTierPosition :exec
|
|
UPDATE reward_tiers SET position = @position WHERE id = @id;
|
|
|
|
-- name: DeleteTier :exec
|
|
DELETE FROM reward_tiers WHERE id = @id;
|
|
|
|
-- name: GetTier :one
|
|
SELECT id, type::text AS type, price, list_price, title, includes,
|
|
inventory_limit, estimated_delivery, featured
|
|
FROM reward_tiers
|
|
WHERE id = @id;
|
|
|
|
-- name: UpdateTier :exec
|
|
UPDATE reward_tiers
|
|
SET type = @type::tier_type, price = @price, list_price = @list_price,
|
|
title = @title, includes = @includes, inventory_limit = @inventory_limit,
|
|
estimated_delivery = @estimated_delivery, featured = @featured,
|
|
position = @position
|
|
WHERE id = @id;
|
|
|
|
-- name: InsertTier :exec
|
|
INSERT INTO reward_tiers (id, campaign_id, type, price, list_price, title,
|
|
includes, inventory_limit, estimated_delivery,
|
|
featured, position)
|
|
VALUES (@id, @campaign_id, @type::tier_type, @price, @list_price, @title,
|
|
@includes, @inventory_limit, @estimated_delivery, @featured, @position);
|
|
|
|
-- name: CountCampaignPledges :one
|
|
SELECT COUNT(*)::int FROM pledges WHERE campaign_id = @campaign_id;
|
|
|
|
-- name: DeleteCampaign :execrows
|
|
DELETE FROM campaigns WHERE id = @id;
|
|
|
|
-- name: CampaignSlugByID :one
|
|
SELECT slug FROM campaigns WHERE id = @id;
|