You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
56 lines
3.1 KiB
56 lines
3.1 KiB
-- Guardas diferidas: una importación puede insertar créditos y audios en
|
|
-- cualquier orden dentro de la misma transacción. No reescribe datos existentes.
|
|
CREATE FUNCTION public.comprobar_grabacion_publicable(identificador text)
|
|
RETURNS void LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
|
|
DECLARE grabacion record;
|
|
BEGIN
|
|
FOR grabacion IN
|
|
SELECT c.id,c.titulo,c.tiene_letra,c.letra,
|
|
(c.ala_venta OR coalesce(p.ala_venta,false)) vendible
|
|
FROM public.cancion c LEFT JOIN public.cancion p ON p.id=c.version_de_id
|
|
WHERE (c.id=identificador OR c.version_de_id=identificador) AND c.estado='publicado'
|
|
LOOP
|
|
IF NOT EXISTS (SELECT 1 FROM public.cancion_interprete ci WHERE ci.cancion_id=grabacion.id AND ci.rol='principal') THEN
|
|
RAISE EXCEPTION 'La grabación % necesita un intérprete principal',grabacion.titulo USING ERRCODE='23514';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM public.cancion_audio ca WHERE ca.cancion_id=grabacion.id AND ca.uso='stream') THEN
|
|
RAISE EXCEPTION 'La grabación % necesita una representación de escucha',grabacion.titulo USING ERRCODE='23514';
|
|
END IF;
|
|
IF grabacion.tiene_letra AND nullif(btrim(grabacion.letra),'') IS NULL THEN
|
|
RAISE EXCEPTION 'La grabación % declara letra pero no la contiene',grabacion.titulo USING ERRCODE='23514';
|
|
END IF;
|
|
IF grabacion.vendible AND NOT EXISTS (SELECT 1 FROM public.cancion_audio ca WHERE ca.cancion_id=grabacion.id AND ca.uso IN ('master','descarga') AND ca.formato='mp3') THEN
|
|
RAISE EXCEPTION 'La grabación % necesita una entrega MP3',grabacion.titulo USING ERRCODE='23514';
|
|
END IF;
|
|
END LOOP;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
CREATE FUNCTION public.guardar_invariantes_publicacion()
|
|
RETURNS trigger LANGUAGE plpgsql SET search_path = pg_catalog, public AS $$
|
|
BEGIN
|
|
IF TG_TABLE_NAME='cancion' THEN
|
|
IF TG_OP='UPDATE' AND NEW.estado IS NOT DISTINCT FROM OLD.estado
|
|
AND NEW.tiene_letra IS NOT DISTINCT FROM OLD.tiene_letra
|
|
AND NEW.letra IS NOT DISTINCT FROM OLD.letra
|
|
AND NEW.ala_venta IS NOT DISTINCT FROM OLD.ala_venta
|
|
AND NEW.version_de_id IS NOT DISTINCT FROM OLD.version_de_id THEN RETURN NULL; END IF;
|
|
PERFORM public.comprobar_grabacion_publicable(NEW.id);
|
|
ELSE
|
|
IF TG_OP<>'DELETE' THEN PERFORM public.comprobar_grabacion_publicable(NEW.cancion_id); END IF;
|
|
IF TG_OP<>'INSERT' THEN PERFORM public.comprobar_grabacion_publicable(OLD.cancion_id); END IF;
|
|
END IF;
|
|
RETURN NULL;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
CREATE CONSTRAINT TRIGGER cancion_publicable
|
|
AFTER INSERT OR UPDATE ON public.cancion DEFERRABLE INITIALLY DEFERRED
|
|
FOR EACH ROW EXECUTE FUNCTION public.guardar_invariantes_publicacion();
|
|
--> statement-breakpoint
|
|
CREATE CONSTRAINT TRIGGER cancion_creditos_publicables
|
|
AFTER INSERT OR UPDATE OR DELETE ON public.cancion_interprete DEFERRABLE INITIALLY DEFERRED
|
|
FOR EACH ROW EXECUTE FUNCTION public.guardar_invariantes_publicacion();
|
|
--> statement-breakpoint
|
|
CREATE CONSTRAINT TRIGGER cancion_audios_publicables
|
|
AFTER INSERT OR UPDATE OR DELETE ON public.cancion_audio DEFERRABLE INITIALLY DEFERRED
|
|
FOR EACH ROW EXECUTE FUNCTION public.guardar_invariantes_publicacion();
|