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.
68 lines
4.0 KiB
68 lines
4.0 KiB
CREATE TYPE "public"."formacion_vocal" AS ENUM('hombre', 'mujer', 'dueto-femenino', 'dueto-masculino', 'dueto-mixto', 'multiple-mixto', 'multiple-femenino', 'multiple-masculino');--> statement-breakpoint
|
|
DROP VIEW "cancion_principal";--> statement-breakpoint
|
|
ALTER TABLE "cancion" RENAME COLUMN "instrumental" TO "tiene_letra";--> statement-breakpoint
|
|
-- La columna anterior expresaba el concepto al revés: instrumental=true
|
|
-- significaba que no había letra. El renombrado conserva físicamente el dato;
|
|
-- esta inversión conserva su significado.
|
|
ALTER TABLE "cancion" ALTER COLUMN "tiene_letra" DROP DEFAULT;--> statement-breakpoint
|
|
UPDATE "cancion" SET "tiene_letra" = NOT "tiene_letra";--> statement-breakpoint
|
|
ALTER TABLE "cancion" ALTER COLUMN "tiene_letra" SET DEFAULT true;--> statement-breakpoint
|
|
ALTER TABLE "cancion" ALTER COLUMN "idioma" DROP DEFAULT;--> statement-breakpoint
|
|
ALTER TABLE "cancion" ADD COLUMN "formacion_vocal" "formacion_vocal";--> statement-breakpoint
|
|
-- Recupera únicamente las voces que el prompt nombra de forma explícita. Si
|
|
-- no aparece una pista inequívoca, el campo permanece nulo para que el panel
|
|
-- muestre «Sin indicar» en vez de fabricar un dato.
|
|
WITH estilos AS (
|
|
SELECT DISTINCT ON (p."cancion_id")
|
|
p."cancion_id",
|
|
lower(p."contenido" ->> 'estilo') AS texto
|
|
FROM "prompt" p
|
|
WHERE p."contenido" ->> 'estilo' IS NOT NULL
|
|
ORDER BY p."cancion_id", p."preferido" DESC, p."creado_en" DESC
|
|
), detectadas AS (
|
|
SELECT "cancion_id",
|
|
CASE
|
|
WHEN texto ~ '(^|[^a-z])(choir|vocal ensemble|ensemble vocal|multiple vocals?|multiple voices|coro|coral)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(female|woman|women|feminine|femme|mujer|mujeres)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(male|man|men|masculine|homme|hombre|hombres|baritone|tenor|crooner)([^a-z]|$)'
|
|
THEN 'multiple-mixto'
|
|
WHEN texto ~ '(^|[^a-z])(choir|vocal ensemble|ensemble vocal|multiple vocals?|multiple voices|coro|coral)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(female|woman|women|feminine|femme|mujer|mujeres)([^a-z]|$)'
|
|
THEN 'multiple-femenino'
|
|
WHEN texto ~ '(^|[^a-z])(choir|vocal ensemble|ensemble vocal|multiple vocals?|multiple voices|coro|coral)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(male|man|men|masculine|homme|hombre|hombres|baritone|tenor|crooner)([^a-z]|$)'
|
|
THEN 'multiple-masculino'
|
|
WHEN texto ~ '(homme/femme|femme/homme|male/female|female/male)'
|
|
OR (
|
|
texto ~ '(^|[^a-z])(duet|duo|dueto|double vocals?|doble voz)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(female|woman|women|feminine|femme|mujer|mujeres)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(male|man|men|masculine|homme|hombre|hombres|baritone|tenor|crooner)([^a-z]|$)'
|
|
)
|
|
THEN 'dueto-mixto'
|
|
WHEN texto ~ '(^|[^a-z])(duet|duo|dueto|double vocals?|doble voz)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(female|woman|women|feminine|femme|mujer|mujeres)([^a-z]|$)'
|
|
THEN 'dueto-femenino'
|
|
WHEN texto ~ '(^|[^a-z])(duet|duo|dueto|double vocals?|doble voz)([^a-z]|$)'
|
|
AND texto ~ '(^|[^a-z])(male|man|men|masculine|homme|hombre|hombres|baritone|tenor|crooner)([^a-z]|$)'
|
|
THEN 'dueto-masculino'
|
|
WHEN texto ~ '(^|[^a-z])(female|woman|women|feminine|femme|mujer|mujeres)([^a-z]|$)'
|
|
THEN 'mujer'
|
|
WHEN texto ~ '(^|[^a-z])(male|man|men|masculine|homme|hombre|hombres|baritone|tenor|crooner)([^a-z]|$)'
|
|
THEN 'hombre'
|
|
END AS formacion
|
|
FROM estilos
|
|
)
|
|
UPDATE "cancion" c
|
|
SET "formacion_vocal" = d.formacion::formacion_vocal
|
|
FROM detectadas d
|
|
WHERE d."cancion_id" = c."id" AND d.formacion IS NOT NULL;--> statement-breakpoint
|
|
-- Una versión nueva hereda estos datos; las ya existentes hacen lo mismo una
|
|
-- sola vez durante la migración y después pueden editarse por separado.
|
|
UPDATE "cancion" v
|
|
SET "formacion_vocal" = p."formacion_vocal"
|
|
FROM "cancion" p
|
|
WHERE v."version_de_id" = p."id" AND v."formacion_vocal" IS NULL;--> statement-breakpoint
|
|
ALTER TABLE "cancion" ADD CONSTRAINT "cancion_idioma_bcp47" CHECK ("cancion"."idioma" ~ '^[a-z]{2,8}(-[A-Za-z0-9]{1,8})*$');--> statement-breakpoint
|
|
CREATE VIEW "cancion_principal" AS
|
|
SELECT * FROM "cancion" WHERE "version_de_id" IS NULL;
|