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... |
Partager