mirror of
https://github.com/navidrome/navidrome.git
synced 2026-04-03 06:41:01 +00:00
5c3568f758
* fix: make playlist name sorting case-insensitive Add collation NOCASE to playlist.name column to ensure case-insensitive sorting, matching the behavior of other tables like radio and user. This fixes the issue where uppercase playlist names would appear before lowercase names regardless of alphabetical order. The migration recreates the playlist table with the proper collation and recreates all associated indexes. Corresponding collation tests are added to verify the fix persists through future migrations. * fix: add default sorting to playlist names Signed-off-by: Deluan <deluan@navidrome.org> --------- Signed-off-by: Deluan <deluan@navidrome.org>
100 lines
3.3 KiB
SQL
100 lines
3.3 KiB
SQL
-- +goose Up
|
|
-- Fix case-insensitive sorting for playlist names
|
|
create table playlist_dg_tmp
|
|
(
|
|
id varchar(255) not null
|
|
primary key,
|
|
name varchar(255) collate NOCASE default '' not null,
|
|
comment varchar(255) default '' not null,
|
|
duration real default 0 not null,
|
|
song_count integer default 0 not null,
|
|
public bool default FALSE not null,
|
|
created_at datetime,
|
|
updated_at datetime,
|
|
path string default '' not null,
|
|
sync bool default false not null,
|
|
size integer default 0 not null,
|
|
rules varchar,
|
|
evaluated_at datetime,
|
|
owner_id varchar(255) not null
|
|
constraint playlist_user_user_id_fk
|
|
references user
|
|
on update cascade on delete cascade
|
|
);
|
|
|
|
insert into playlist_dg_tmp(id, name, comment, duration, song_count, public, created_at, updated_at, path, sync, size,
|
|
rules, evaluated_at, owner_id)
|
|
select id, name, comment, duration, song_count, public, created_at, updated_at, path, sync, size, rules, evaluated_at,
|
|
owner_id
|
|
from playlist;
|
|
|
|
drop table playlist;
|
|
|
|
alter table playlist_dg_tmp
|
|
rename to playlist;
|
|
|
|
create index playlist_name
|
|
on playlist (name);
|
|
|
|
create index playlist_created_at
|
|
on playlist (created_at);
|
|
|
|
create index playlist_updated_at
|
|
on playlist (updated_at);
|
|
|
|
create index playlist_evaluated_at
|
|
on playlist (evaluated_at);
|
|
|
|
create index playlist_size
|
|
on playlist (size);
|
|
|
|
-- +goose Down
|
|
-- Note: Downgrade loses the collation but preserves data
|
|
create table playlist_dg_tmp
|
|
(
|
|
id varchar(255) not null
|
|
primary key,
|
|
name varchar(255) default '' not null,
|
|
comment varchar(255) default '' not null,
|
|
duration real default 0 not null,
|
|
song_count integer default 0 not null,
|
|
public bool default FALSE not null,
|
|
created_at datetime,
|
|
updated_at datetime,
|
|
path string default '' not null,
|
|
sync bool default false not null,
|
|
size integer default 0 not null,
|
|
rules varchar,
|
|
evaluated_at datetime,
|
|
owner_id varchar(255) not null
|
|
constraint playlist_user_user_id_fk
|
|
references user
|
|
on update cascade on delete cascade
|
|
);
|
|
|
|
insert into playlist_dg_tmp(id, name, comment, duration, song_count, public, created_at, updated_at, path, sync, size,
|
|
rules, evaluated_at, owner_id)
|
|
select id, name, comment, duration, song_count, public, created_at, updated_at, path, sync, size, rules, evaluated_at,
|
|
owner_id
|
|
from playlist;
|
|
|
|
drop table playlist;
|
|
|
|
alter table playlist_dg_tmp
|
|
rename to playlist;
|
|
|
|
create index playlist_name
|
|
on playlist (name);
|
|
|
|
create index playlist_created_at
|
|
on playlist (created_at);
|
|
|
|
create index playlist_updated_at
|
|
on playlist (updated_at);
|
|
|
|
create index playlist_evaluated_at
|
|
on playlist (evaluated_at);
|
|
|
|
create index playlist_size
|
|
on playlist (size);
|