Chercher les mêmes albums avec un nom, date, artistes différents.

Version imprimable

25/09/2016, 21h02
troumad

Chercher les mêmes albums avec un nom, date, artistes différents.

Bonjour

J'ai une table de musique avec un champs "chemin vers le morceau", "album", "artiste", "année" qui a été générée automatiquement par ampache à partir des tag des morceaux de musique.
J'aimerai trouver à partir du chemin (chaque album a un chemin différent) les albums dont l'artiste n'est pas constant, l'année n'est pas constante et l'album n'est pas le même.

Suis-je obligé de passer par un programme (php pour moi) qui me crée une table avec une entrée par chemin d'album pour vérifier si pour chaque chemin, les trois attributs sont toujours les mêmes ?
25/09/2016, 21h06
SQLpro

NON... Il suffit d'une requête SSQL.

A +
25/09/2016, 21h38
troumad

SSQL ?
Mais laquelle, c'est un peu pour ça que je pose la question...
26/09/2016, 09h22
SQLpro

Pardon SQL.

Sans la description de vos tables, difficile de vous répondre. Postez le DDL de vos tables (CREATE TABLE ...)

A +
26/09/2016, 10h35
escartefigue

Citation:

Envoyé par troumad

Bonjour

J'ai une table de musique avec un champs "chemin vers le morceau", "album", "artiste", "année" qui a été générée automatiquement par ampache à partir des tag des morceaux de musique.
J'aimerai trouver à partir du chemin (chaque album a un chemin différent) les albums dont l'artiste n'est pas constant, l'année n'est pas constante et l'album n'est pas le même.

Suis-je obligé de passer par un programme (php pour moi) qui me crée une table avec une entrée par chemin d'album pour vérifier si pour chaque chemin, les trois attributs sont toujours les mêmes ?

Bonjour,
Effectivement une description des tables aiderait, mais toutefois, votre énoncé me semble contradictoire, ce que j'ai mis en couleur m'interpelle :
Si chaque album a un chemin différent, à partir du chemin, vous ne trouverez qu'au plus un seul album ! Or vous demandez LES albumS :weird:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MariaDB [ampache]> show columns in song;
+---------------+-------------------------+------+-----+---------+----------------+
| Field         | Type                    | Null | Key | Default | Extra          |
+---------------+-------------------------+------+-----+---------+----------------+
| id            | int(11) unsigned        | NO   | PRI | NULL    | auto_increment |
| file          | varchar(4096)           | YES  | MUL | NULL    |                |
| catalog       | int(11) unsigned        | NO   | MUL | 0       |                |
| album         | int(11) unsigned        | NO   | MUL | 0       |                |
| year          | mediumint(4) unsigned   | NO   |     | 0       |                |
| artist        | int(11) unsigned        | NO   | MUL | 0       |                |
| title         | varchar(255)            | YES  | MUL | NULL    |                |
| bitrate       | mediumint(8) unsigned   | NO   |     | 0       |                |
| rate          | mediumint(8) unsigned   | NO   |     | 0       |                |
| mode          | enum('abr','vbr','cbr') | YES  |     | NULL    |                |
| size          | int(11) unsigned        | NO   |     | 0       |                |
| time          | smallint(5) unsigned    | NO   |     | 0       |                |
| track         | smallint(5) unsigned    | YES  |     | NULL    |                |
| mbid          | varchar(36)             | YES  |     | NULL    |                |
| played        | tinyint(1) unsigned     | NO   | MUL | 0       |                |
| enabled       | tinyint(1) unsigned     | NO   | MUL | 1       |                |
| update_time   | int(11) unsigned        | YES  | MUL | 0       |                |
| addition_time | int(11) unsigned        | YES  | MUL | 0       |                |
+---------------+-------------------------+------+-----+---------+----------------+
18 rows in set (0.00 sec)

Je trouve un album à partir du champ file : "/usr/share/ampache/www/musique/hard/Saxon/denim and leather/princess of the night.ogg" est dans le même album que tous les chants qui sont dans "/usr/share/ampache/www/musique/hard/Saxon/denim and leather/"
Ce que je recherche, c'est parmi tous les morceaux qui sont dans le même album (d'après le chemin) si les champ album, year, artist sont bien semblables afin de débusquer les problèmes et corriger ma base de données.

Ma base de données a été crée par ampache ( http://ampache.org/ ) à partir de toutes mes données en lisant les tag. Avant mon jukebox était géré par netjuke ( https://sourceforge.net/projects/netjuke/ ) mais, bien que pratique, il n'évolue plus beaucoup et n'est pas compatible avec tous les nouveaux médias. Et j'avais rippé tous les CD un à un en corrigeant au fur et à mesure les problèmes lors de l'enregistrement dans netjuke. Donc, certains tags ne sont pas exacts ou inexistants.

Cette requête vous donnera la liste des albums ayant des dissemblances dans les attributs album, year, artist

Code:

1
2
3
4
5
6
7
8
SELECT "file"
FROM   (SELECT COUNT(*) AS n, "file" 
        FROM   song 
        GROUP  BY "file") AS A1
       JOIN (SELECT COUNT(*) AS n, "file"
             FROM   song 
             GROUP  BY "file", album, year, artist) AS A2
            ON A1."file" = A2."file" AND A1.n <> A2.n;

Avec un autre SGBDR que le nullissime MySQL, cette requête peut s'écrire :

Code:

1
2
3
4
5
6
7
8
9
10
11
WITH
albums_only   AS (SELECT COUNT(*) AS n, "file" 
                  FROM   song 
                  GROUP  BY "file"),
album_details AS (SELECT COUNT(*) AS n, "file"
                  FROM   song 
                  GROUP  BY "file", album, year, artist)
SELECT "file"
FROM   albums_only AS AO
       JOIN album_details AS AD
            ON AO."file" = AD."file" AND AO.n <> AD.n;

Plus compréhensible !

A +

Salut troumad.

Citation:

Envoyé par troumad

J'aimerai trouver à partir du chemin (chaque album a un chemin différent) les albums dont l'artiste n'est pas constant, l'année n'est pas constante et l'album n'est pas le même.

La difficulté repose sur le fait que le chemin est unique pour un morceau de musique.
Et comme le titre du morceau de musique se trouve en fin de chemin, il faut le supprimer pour faire un regroupement par album.

Toute la question demeure dans le nombre de subdivision de votre chemin, en espérant qu'il soit constant. Dans votre exemple, en comptabilisant les "/", il y a neuf subdivisions.

Citation:

Envoyé par troumad

Ce que je recherche, c'est parmi tous les morceaux qui sont dans le même album (d'après le chemin) si les champ album, year, artist sont bien semblables afin de débusquer les problèmes et corriger ma base de données.

Ce que l'on peut faire, c'est comptabiliser les lignes identiques selon vos trois critères, à savoir "album", "year", "artist".
Si on trouve '1', alors on sait qu'il n'y a pas de différences, mais si on trouve '>1', cela signifie qu'il existe une différence.

J'ai décomposé mon exemple en plusieurs phases afin de montrer le cheminement de mon raisonnement.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(
  `id`         integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `file`       varchar(255)      NOT NULL,
  `year`       smallint unsigned NOT NULL,
  `album`      integer  unsigned NOT NULL,
  `artist`     integer  unsigned NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`file`,`year`,`album`,`artist`) VALUES
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/princess of the night.ogg',      1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Never Surrender.ogg',            1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Out of Control.ogg',             1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Rough and Ready.ogg',            1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Play It Loud.ogg',               1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/And the Band Played On.ogg',     1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Midnight Rider.ogg',             1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Fire in the Sky.ogg',            1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Denim and Leather.ogg',          1981, 25, 154),
 
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Heavy Metal Thunder.ogg',    1980, 24, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/To Hell And Back Again.ogg', 1980, 24, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Strong Arm Of The Law.ogg',  1980, 24, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Taking Your Chances',        1980, 24, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/20,000 FT.ogg',              1980, 42, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Hungry Years.ogg',           1980, 24, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Sixth Form Girls.ogg',       1980, 24, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Dallas 1 PM.ogg',            1980, 24, 154)
--------------
 
--------------
select * from test
--------------
 
+----+--------------------------------------------------------------------------------------------+------+-------+--------+
| id | file                                                                                       | year | album | artist |
+----+--------------------------------------------------------------------------------------------+------+-------+--------+
|  1 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/princess of the night.ogg      | 1981 |    25 |    154 |
|  2 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Never Surrender.ogg            | 1981 |    25 |    154 |
|  3 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Out of Control.ogg             | 1981 |    25 |    154 |
|  4 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Rough and Ready.ogg            | 1981 |    25 |    154 |
|  5 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Play It Loud.ogg               | 1981 |    25 |    154 |
|  6 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/And the Band Played On.ogg     | 1981 |    25 |    154 |
|  7 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Midnight Rider.ogg             | 1981 |    25 |    154 |
|  8 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Fire in the Sky.ogg            | 1981 |    25 |    154 |
|  9 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Denim and Leather.ogg          | 1981 |    25 |    154 |
| 10 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Heavy Metal Thunder.ogg    | 1980 |    24 |    154 |
| 11 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/To Hell And Back Again.ogg | 1980 |    24 |    154 |
| 12 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Strong Arm Of The Law.ogg  | 1980 |    24 |    154 |
| 13 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Taking Your Chances        | 1980 |    24 |    154 |
| 14 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/20,000 FT.ogg              | 1980 |    42 |    154 |
| 15 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Hungry Years.ogg           | 1980 |    24 |    154 |
| 16 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Sixth Form Girls.ogg       | 1980 |    24 |    154 |
| 17 | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law/Dallas 1 PM.ogg            | 1980 |    24 |    154 |
+----+--------------------------------------------------------------------------------------------+------+-------+--------+
--------------
select SUBSTRING_INDEX(`file`,'/', -1) as 'titre',
       SUBSTRING_INDEX(`file`,'/', +9) as 'album'
 
from test
--------------
 
+----------------------------+-----------------------------------------------------------------+
| titre                      | album                                                           |
+----------------------------+-----------------------------------------------------------------+
| princess of the night.ogg  | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Never Surrender.ogg        | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Out of Control.ogg         | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Rough and Ready.ogg        | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Play It Loud.ogg           | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| And the Band Played On.ogg | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Midnight Rider.ogg         | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Fire in the Sky.ogg        | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Denim and Leather.ogg      | /usr/share/ampache/www/musique/hard/Saxon/denim and leather     |
| Heavy Metal Thunder.ogg    | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| To Hell And Back Again.ogg | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| Strong Arm Of The Law.ogg  | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| Taking Your Chances        | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| 20,000 FT.ogg              | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| Hungry Years.ogg           | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| Sixth Form Girls.ogg       | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
| Dallas 1 PM.ogg            | /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law |
+----------------------------+-----------------------------------------------------------------+
--------------
select *, count(*) as tot
from (select SUBSTRING_INDEX(`file`,'/', +9) as 'chemin', `year`, `album`, `artist` from test group by chemin, year, album, artist) as x
group by chemin
having tot > 1
--------------
 
+-----------------------------------------------------------------+------+-------+--------+-----+
| chemin                                                          | year | album | artist | tot |
+-----------------------------------------------------------------+------+-------+--------+-----+
| /usr/share/ampache/www/musique/hard/Saxon/strong arm of the law | 1980 |    24 |    154 |   2 |
+-----------------------------------------------------------------+------+-------+--------+-----+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Dans cet exemple, j'ai volontairement mal écrit le numéro de l'album (42 au lieu de 24) pour l'album "strong arm of the law".
De ce fait, en faisant un regroupement, deux lignes qui vont apparaître.
Bien sûr, j'affiche le résultat que si j'ai plus de 1 ligne.

@+

29/09/2016, 16h47
troumad

Mon problème, c'est que tous les chemins n'ont pas la même profondeur...
/usr/share/ampache/www/musique/hard/Therion/01 Der Mitternachtslome.mp3 par exemple.

Je me demande si je ne vais pas tester de gérer le MySQL en C. Ce serai un nouveau chalenge ;)
29/09/2016, 17h59
SQLpro

Citation:

Envoyé par troumad

Mon problème, c'est que tous les chemins n'ont pas la même profondeur...
/usr/share/ampache/www/musique/hard/Therion/01 Der Mitternachtslome.mp3 par exemple.

Je me demande si je ne vais pas tester de gérer le MySQL en C. Ce serai un nouveau chalenge ;)

Ca n'est pas plus compliqué. Effectivement Artemus est partie avec une recherche à profondeur unique et constante. Si ce n'est pas le cas; il suffit d'un peut d'astuce pour récupérer la partie chemin d'un accès au fichier :
1) renverser la chaine avec une fonction comme REVERSE si ça existe sous MySQmerde
2) supprimer de cette chaine la partie avant le premier /
3) renversé la chaine ainsi tronquée.

Je n'ai pas MySQL sous la main (et j'évite de l'installer tellement il est pourri) mais en SQL Server c'est :

Code:

REVERSE(SUBSTRING(REVERSE(colonne), CHARINDEX('/', REVERSE(colonne)), LEN(REVERSE(colonne)) - CHARINDEX('/', REVERSE(colonne)) + 1))

A +

Salut à tous.

@ SQLPRO : ce n'est pas moi qui t'ai mis deux fois un '-1'.

Citation:

Envoyé par SQLPRO

Effectivement Artemus est partie avec une recherche à profondeur unique et constante.

En effet, j'ai classé mes morceaux de musique sur mon ordinateur, et tous les chemins ont la même profondeur.

La fonction "reverse" existe bien sous MySql : http://dev.mysql.com/doc/refman/5.7/...nction_reverse

Je n'ai pas suivi le même raisonnement.
Je extrais la sous-chaîne de caractère du chemin, en partant de la gauche, sur la longueur qui correspond à la position du titre du morceau de musique dans ce chemin.

@ troumad : voici ce que je propose.
J'ai repris mes deux exemples, et cette fois-ci dans le chemin, j'ai supprimé le répertoire "hard/" afin de ne pas avoir la même profondeur.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(
  `id`         integer  unsigned NOT NULL AUTO_INCREMENT primary key,
  `file`       varchar(255)      NOT NULL,
  `year`       smallint unsigned NOT NULL,
  `album`      integer  unsigned NOT NULL,
  `artist`     integer  unsigned NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`file`,`year`,`album`,`artist`) VALUES
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/princess of the night.ogg',      1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Never Surrender.ogg',            1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Out of Control.ogg',             1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Rough and Ready.ogg',            1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Play It Loud.ogg',               1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/And the Band Played On.ogg',     1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Midnight Rider.ogg',             1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Fire in the Sky.ogg',            1981, 25, 154),
  ('/usr/share/ampache/www/musique/hard/Saxon/denim and leather/Denim and Leather.ogg',          1981, 25, 154),
 
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/Heavy Metal Thunder.ogg',    1980, 24, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/To Hell And Back Again.ogg', 1980, 24, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/Strong Arm Of The Law.ogg',  1980, 24, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/Taking Your Chances',        1980, 24, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/20,000 FT.ogg',              1980, 42, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/Hungry Years.ogg',           1980, 24, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/Sixth Form Girls.ogg',       1980, 24, 154),
  ('/usr/share/ampache/www/musique/Saxon/strong arm of the law/Dallas 1 PM.ogg',            1980, 24, 154)
--------------
 
--------------
select * from test
--------------
 
+----+----------------------------------------------------------------------------------------+------+-------+--------+
| id | file                                                                                   | year | album | artist |
+----+----------------------------------------------------------------------------------------+------+-------+--------+
|  1 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/princess of the night.ogg  | 1981 |    25 |    154 |
|  2 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Never Surrender.ogg        | 1981 |    25 |    154 |
|  3 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Out of Control.ogg         | 1981 |    25 |    154 |
|  4 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Rough and Ready.ogg        | 1981 |    25 |    154 |
|  5 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Play It Loud.ogg           | 1981 |    25 |    154 |
|  6 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/And the Band Played On.ogg | 1981 |    25 |    154 |
|  7 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Midnight Rider.ogg         | 1981 |    25 |    154 |
|  8 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Fire in the Sky.ogg        | 1981 |    25 |    154 |
|  9 | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/Denim and Leather.ogg      | 1981 |    25 |    154 |
| 10 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/Heavy Metal Thunder.ogg     | 1980 |    24 |    154 |
| 11 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/To Hell And Back Again.ogg  | 1980 |    24 |    154 |
| 12 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/Strong Arm Of The Law.ogg   | 1980 |    24 |    154 |
| 13 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/Taking Your Chances         | 1980 |    24 |    154 |
| 14 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/20,000 FT.ogg               | 1980 |    42 |    154 |
| 15 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/Hungry Years.ogg            | 1980 |    24 |    154 |
| 16 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/Sixth Form Girls.ogg        | 1980 |    24 |    154 |
| 17 | /usr/share/ampache/www/musique/Saxon/strong arm of the law/Dallas 1 PM.ogg             | 1980 |    24 |    154 |
+----+----------------------------------------------------------------------------------------+------+-------+--------+
--------------
select reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)) as titre,
       left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as album
from test
--------------
 
+----------------------------+--------------------------------------------------------------+
| titre                      | album                                                        |
+----------------------------+--------------------------------------------------------------+
| princess of the night.ogg  | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Never Surrender.ogg        | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Out of Control.ogg         | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Rough and Ready.ogg        | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Play It Loud.ogg           | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| And the Band Played On.ogg | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Midnight Rider.ogg         | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Fire in the Sky.ogg        | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Denim and Leather.ogg      | /usr/share/ampache/www/musique/hard/Saxon/denim and leather/ |
| Heavy Metal Thunder.ogg    | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| To Hell And Back Again.ogg | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| Strong Arm Of The Law.ogg  | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| Taking Your Chances        | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| 20,000 FT.ogg              | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| Hungry Years.ogg           | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| Sixth Form Girls.ogg       | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
| Dallas 1 PM.ogg            | /usr/share/ampache/www/musique/Saxon/strong arm of the law/  |
+----------------------------+--------------------------------------------------------------+
--------------
select chemin, count(*) as tot
from (select left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as 'chemin',
             `year`, `album`, `artist` from test group by chemin, year, album, artist) as x
group by chemin
having tot > 1
--------------
 
+-------------------------------------------------------------+-----+
| chemin                                                      | tot |
+-------------------------------------------------------------+-----+
| /usr/share/ampache/www/musique/Saxon/strong arm of the law/ |   2 |
+-------------------------------------------------------------+-----+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

En espérant que cela réponde à votre attente ! :D

@+

30/09/2016, 11h43
troumad

Merci !

en fait, c'est le "reverse" et le "SUBSTRING_INDEX" que je ne connaissais pas. Il ne me reste plus qu'à tester. Je pense avoir compris le système.
30/09/2016, 15h46
SQLpro

Citation:

Envoyé par Artemus24

Salut à tous.

@ SQLPRO : ce n'est pas moi qui t'ai mis deux fois un '-1'.

Très franchement je m'en fous un peu... Je me marre quand des crétins qui n'ont rien compris au SGBDR jugent les profs en leur mettant des -1 parce que ça marche pas il voudrait !
ça me permet de vendre assez cher mes prestations pour rectifier les conneries en production !

A +
30/09/2016, 15h49
Artemus24

Salut SQLPRO.

Je suis entièrement d'accord avec toi ! :D

@+

je veux créer une table avec le résultat de cette requête pour pouvoir tranquillement travailler avec. J'essaie avec :

Code:

select reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)) as nom, left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as chemin, year as yaer, album as album, artist as artist from song;

Ça ne marche pas, ça a besoin d'une id. POurquoi , je pensais que ça allait s'autoincrémenter !

Code:

insert into test select id as id, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)) as nom, left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as chemin, year as year, album as album, artist as artist from song;

Finalement, ceci marche et, c'est bien mieux car au bilan, je garde le même id dans les deux tables.

J'ai oublié de dire que j'ai modifié un peu la base test :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 MariaDB [ampache]> show columns in test; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | nom | varchar(255) | NO | | NULL | | | year | smallint(5) unsigned | NO | | NULL | | | album | int(10) unsigned | NO | | NULL | | | artist | int(10) unsigned | NO | | NULL | | | chemin | varchar(4096) | NO | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)
La suite plus tard...

Étape suivante :

Code:

insert into test select id as id, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)) as nom, year as year, album as album, artist as artist, left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as chemin from song

En effet, il fallait respecter l'ordre...

Le lendemain matin :
Code:

1 2 3 4 5 6 MariaDB [ampache]> CREATE TABLE `nb_erreur` ( `id` integer unsigned NOT NULL AUTO_INCREMENT primary key, `chemin` varchar(4096), `erreur` integer); Query OK, 0 rows affected (0.08 sec) MariaDB [ampache]> insert into nb_erreur select id, chemin, count(*) as tot from test as x group by chemin having tot >2; Query OK, 782 rows affected (0.90 sec) Records: 782 Duplicates: 0 Warnings: 0
Je pense avoir fini de travailler la base pour finir mes recherches.

02/10/2016, 09h57
troumad

je viens de modifier pour que ça marche :

Code:

insert into nb_erreur select id, chemin, count(*) as tot from (select * from test group by chemin,year,album,artist ) as x group by chemin having tot > 1

J'ai la table des éléments à problème.

Après avoir tout disséqué et compris, voici ma commande finale :
Code:

1 2 3 4 5 6 7 8 9 10 TRUNCATE nb_erreur; insert into nb_erreur select id, chemin, count(*) as tot from ( select * from ( select id as id, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)) as nom, year as year, album as album, artist as artist, left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as chemin from song ) as tmp_table group by chemin,year,album,artist ) as tmp_table_2 group by chemin having tot > 1
Comme j'arrive à comprendre, je peux utiliser petit à petit les nouvelles notions ;)

Code:

UPDATE `ampache`.`song` SET `artist` = '42', year='2012', album='303', title=SUBSTRING_INDEX(reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)),'.',1) WHERE `file` LIKE '/usr/share/ampache/www/musique/hard/Led Zeppelin/Celebration Day/%'

Salut Troumad.
Code:

1 2 3 4 5 select chemin, count(*) as tot from (select left(`file`, instr(`file`, reverse(SUBSTRING_INDEX(reverse(`file`), '/', 1)))-1) as 'chemin', `year`, `album`, `artist` from test group by chemin, year, album, artist) as x group by chemin having tot > 1;
Le principe de cette requête consiste à extraire les colonnes "chemin", "year", "album", "artist" en faisant un regroupement sur ces mêmes colonnes.
A partir de cette extraction, deux cas peuvent arriver :

1) soit vous avez toujours les mêmes valeurs dans ces colonnes et de ce fait, une seule ligne sera produite.
Cela signifie qu'il n'y a pas de différences et que l'album a été correctement renseignée.

2) soit il existe des différences de valeurs dans l'une de ces colonnes et donc plusieurs lignes seront produites.

A partir de ces lignes extraites, et en comptabilisant sur le critère du chemin, normalement vous aurez :
--> soit "tot" = 1 alors il n'y a pas de différences.
--> soit "tot" > 1 alors il existe au moins une différence sur l'une des colonnes "year", "album", "artist".

Mais s'il existe une différence dans le chemin, cela ne sera pas détectée !

Une mauvaise conception de votre base de données provoque des problèmes de performances mais aussi une ou plusieurs difficultés dans ce que vous essayez de faire.

Il aurait fallu créer deux tables, l'une sur les caractéristiques de l'album et l'autre sur les caractéristiques des morceaux de musique.
A partir du moment où le lien (une clef étrangère) a été créé entre l'album et le morceau de musique, il est plus facile de gérer vos particularités.

Le principe d'une base de données est d'éviter les répétitions de données qui occupent de l'espace pour rien, mais aussi de mettre ces données en relations.

@+

02/10/2016, 18h16
troumad

Il peut en effet avoir plusieurs valeurs si la source est une compilation. C'est assez rare, mais pas impossible.
Pour ce qui est de la conception, il faut voir avec ceux qui ont conçu ampache (voir http://ampache.org/ ), je rentre à la main dans la base de données pour repérer et corriger les erreurs dues à l'importation de beaucoup de morceaux avec trop d'erreurs de tag. Pour le moment, j'essaie de réparer la base de données, après, j'essaierai de voir si je peux taguer à nouveau mes fichiers à partir de la correction de la base de données.

Pour ce qui est de la requête, je pense avoir compris, ça n'a pas été facile. D'une part, il y a des fonctions MySQL que je ne connaissais pas, d'autre part, ça fait bien longtemps que je ne faisais plus que des requêtes drop, insert et select avec MySQL. Chaque fois que je me relance dans MySQL, je (re)découvre des choses nouvelles.

Pour l’anecdote en 1990, quand j'ai du toucher pour mes études à SQL, j'avais retenu qu'avec ce langage de programmation, on ne pouvait pas faire simplement une affectation du genre "a=b" facilement. À l'époque, je ne voyais pas l'intérêt des bases de données, et ce d'autant plus qu'un gros disque dur faisait 40Mo avec de grosses disquettes de 1,4 Mo.

Grâce à ce coup de main, j'ai fait d'autres requêtes pour vérifier et nettoyer ma base de données ampache.

Je vais peut-être avoir d'autres idées de tri.

Par exemple, est-ce que celle-ci est optimisée ?
Code:

1 2 3 4 5 6 7 8 select album, chemin, count(*) as tot from ( select * from ( select id as id, album as album, reverse(SUBSTRING_INDEX(reverse(SUBSTRING_INDEX(`file`, '/usr/share/ampache/www/musique/', -1)),reverse(SUBSTRING_INDEX(SUBSTRING_INDEX(`file`, '/usr/share/ampache/www/musique/', -1),'/',-1)),-1)) as chemin from song ) as tmp1 group by album, chemin ) as tmp2 group by album having tot > 1
Je peux fournir les fichiers php pour mettre dans la racine d'ampache pour vérifier si :
- les répertoires ont tous le même artiste, la même année, le même album
- les albums ne sont présents qu'une fois
- si l’arborescence des artiste est correcte
- les artistes sans chanson
- les albums sans chanson

Ceci pour mon arborescence : genre/artiste/album