sakila/queries/film.sql

-- name: ListFilms :many
SELECT film_id,
       title,
       description,
       release_year,
       language_id,
       original_language_id,
       rental_duration,
       rental_rate,
       length,
       replacement_cost,
       rating,
       special_features,
       last_update
FROM film
ORDER BY film_id ASC
LIMIT ? OFFSET ?;

-- name: CountFilms :one
SELECT COUNT(*)
FROM film;

-- name: GetFilm :one
SELECT film_id,
       title,
       description,
       release_year,
       language_id,
       original_language_id,
       rental_duration,
       rental_rate,
       length,
       replacement_cost,
       rating,
       special_features,
       last_update
FROM film
WHERE film_id = ?;

-- name: ListActorsByFilm :many
SELECT a.actor_id, a.first_name, a.last_name, a.last_update
FROM actor a
         JOIN film_actor fa ON fa.actor_id = a.actor_id
WHERE fa.film_id = ?
ORDER BY a.last_name ASC, a.first_name ASC;

-- name: NextFilmID :one
SELECT COALESCE(MAX(film_id), 0) + 1 FROM film;

-- name: InsertFilm :exec
INSERT INTO film (
  film_id,
  title,
  description,
  release_year,
  language_id,
  original_language_id,
  rental_duration,
  rental_rate,
  length,
  replacement_cost,
  rating,
  special_features,
  last_update
) VALUES (
  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP
);

-- name: UpdateFilmPut :execrows
UPDATE film SET
  title = ?,
  description = ?,
  release_year = ?,
  language_id = ?,
  original_language_id = ?,
  rental_duration = ?,
  rental_rate = ?,
  length = ?,
  replacement_cost = ?,
  rating = ?,
  special_features = ?
WHERE film_id = ?;

-- name: PatchFilm :execrows
UPDATE film SET
  title = COALESCE(?1, title),
  description = COALESCE(?2, description),
  release_year = COALESCE(?3, release_year),
  language_id = COALESCE(?4, language_id),
  original_language_id = COALESCE(?5, original_language_id),
  rental_duration = COALESCE(?6, rental_duration),
  rental_rate = COALESCE(?7, rental_rate),
  length = COALESCE(?8, length),
  replacement_cost = COALESCE(?9, replacement_cost),
  rating = COALESCE(?10, rating),
  special_features = COALESCE(?11, special_features)
WHERE film_id = ?12;

-- name: DeleteFilm :execrows
DELETE FROM film WHERE film_id = ?;