Ampache

De Wiki.

Aller à : Navigation, rechercher

Sommaire

1 Requêtes SQL utiles

Quelques requêtes SQL pratiques pour automatiser la génération de rapports.

1.1 Liste des albums

select from_unixtime(s.addition_time) AS Date,
       s.year, concat(IFNULL(concat(al.prefix, ' '), ''), al.name) AS Album,
       GROUP_CONCAT(DISTINCT ar.name) As Artiste
from album al
join song s on s.album=al.id
join artist ar on s.artist=ar.id and al.name != 'Unknown (Orphaned)'
group by al.name
order by al.name;

1.2 Liste des derniers albums

select DATE_FORMAT(FROM_UNIXTIME(s.addition_time), '%d/%m/%Y'),
       GROUP_CONCAT(DISTINCT concat(IFNULL(concat(ar.prefix, ' '), ''), ar.name)) As Artiste,
       s.year as Annee,
       /* ou concat('- ', s.year, ' -') as Annee, */
       concat(IFNULL(concat(al.prefix, ' '), ''), al.name) AS Album
from album al
join song s on s.album=al.id
join artist ar on s.artist=ar.id and al.name != 'Unknown (Orphaned)'
group by al.name
order by s.addition_time desc
limit 50;

1.3 Liste des albums sans pochette

select concat(IFNULL(concat(al.prefix, ' '), ''), al.name) AS Album,
       GROUP_CONCAT(DISTINCT ar.name) As Artiste,
	   s.year
from album al
join song s on s.album=al.id
join artist ar on s.artist=ar.id and al.name != 'Unknown (Orphaned)'
left join album_data ad on al.id=ad.album_id
where ad.album_id is NULL
group by al.name
order by al.name;

2 Amélioration

2.1 Longueur des chemins de fichiers

Pour permettre la prise en compte des fichiers dont le chemin complet dépasse 255 caractères, il est nécessaire de changer le type de champs dans la base de données (varchar(255) -> text), testé sur la version 3.5.4:

drop index file on song;
alter table song modify file text CHARACTER SET utf8;
create index file on song (file(10));
Récupérée de « http://kerlinux.org/wiki/Ampache »
Outils personnels
Espaces de noms
Variantes
Actions
Navigation
Liens
Boîte à outils