Sort Playlists

[arodgil]

Hello,

My Subsonic versión is : 4.7.beta3 (build 3060) an run in Ubuntu.
This new version of Subsonic modify the playlists management.
I use a script (Bash) to generate automatic playlists (many, using the ID3 tag "Comment" field as comma separate tags).
I need Subsonic display playlists names in alphabetic order, in same form an ls command:

0-MARVELOUS-ALL-arg(104).m3u
0-MARVELOUS-BLUES-arg(1).m3u
0-MARVELOUS-BLUES ROCK-arg(10).m3u
0-MARVELOUS-COUNTRY-arg(1).m3u
0-MARVELOUS-COUNTRY ROCK-arg(6).m3u
0-MARVELOUS-FOLK ROCK-arg(2).m3u
0-MARVELOUS-JAZZ-arg(30).m3u
0-MARVELOUS-PROGRESSIVE ROCK-arg(7).m3u
0-MARVELOUS-ROCK-arg(7).m3u
0-MARVELOUS-SPACE ROCK-arg(8).m3u
ALL-3-arg(422).m3u
ALL-4-arg(452).m3u
ALL-5-arg(353).m3u
ALTERNATIVE ROCK-3-arg(1).m3u
ALTERNATIVE ROCK-4-arg(1).m3u
ALTERNATIVE ROCK-5-arg(2).m3u
art-A.BASHUNG-3-arg(1).m3u
art-A.COLLINS-3-arg(1).m3u
art-A.FRANKLIN-3-arg(25).m3u
art-A.FRANKLIN-4-arg(6).m3u
art-A.FRANKLIN-5-4-3-arg(33).m3u
art-A.FRANKLIN-5-arg(2).m3u
art-A.KING-3-arg(7).m3u
art-A.KING-4-arg(1).m3u
art-A.KING-5-4-3-arg(8).m3u
art-AL JARREAU-3-arg(1).m3u
art-ALTAN-4-arg(1).m3u
art-ANITA O'DAY-4-arg(1).m3u
art-B.BILL BROONZY-4-arg(1).m3u
art-B.B. KING-3-arg(2).m3u
art-B.B. KING-4-arg(2).m3u
art-B.B. KING-5-4-3-arg(5).m3u
art-B.B. KING-5-arg(1).m3u
art-B.BLAKE-3-arg(1).m3u
art-B."BLUE" BLAND-3-arg(1).m3u
art-B."BLUE" BLAND-4-arg(1).m3u
art-B."BLUE" BLAND-5-4-3-arg(2).m3u
art-B.DYLAN-3-arg(26).m3u
art-B.DYLAN-4-arg(13).m3u
art-B.DYLAN-5-4-3-arg(51).m3u
art-B.DYLAN-5-arg(12).m3u
art-B.GUY-4-arg(1).m3u
art-B.HARPER-3-arg(1).m3u
art-B.HARPER-4-arg(6).m3u
art-B.HARPER-5-4-3-arg(8).m3u
art-B.HARPER-5-arg(1).m3u
art-B.HARPER & THE In.Cr.-5-arg(2).m3u
art-B.HOLIDAY-3-arg(3).m3u
art-B.HOLIDAY-4-arg(4).m3u
art-B.HOLIDAY-5-4-3-arg(7).m3u
art-B.JOE WILLIAMS-3-arg(1).m3u
art-B.LAVETTE-3-arg(2).m3u
art-B.LAVILLIERS-3-arg(13).m3u
art-B.LAVILLIERS-4-arg(18).m3u
art-B.LAVILLIERS-5-4-3-arg(40).m3u
art-B.LAVILLIERS-5-arg(9).m3u
art-BLUES BR.-5-arg(1).m3u
art-B.MACEO-4-arg(1).m3u
art-B.SIMS-3-arg(1).m3u
art-B.VIAN-4-arg(1).m3u
art-B.WYMAN'S RHYTHM KINGS-4-arg(1).m3u
art-CAMARÓN DE LA I.-5-arg(3).m3u
art-C.BAILEY RAE-3-arg(2).m3u
art-C.BASIE & HIS ORCHESTRA-4-arg(2).m3u
art-C."GATEMOUTH" BROWN-3-arg(3).m3u
art-C."GATEMOUTH" BROWN-4-arg(1).m3u
art-C."GATEMOUTH" BROWN-5-4-3-arg(6).m3u
art-C."GATEMOUTH" BROWN-5-arg(2).m3u
art-C.JACK DUPREE-5-arg(1).m3u
art-C.&NASH-3-arg(1).m3u
art-C.NOUGARO-3-arg(17).m3u
art-C.NOUGARO-4-arg(19).m3u
art-C.NOUGARO-5-4-3-arg(53).m3u
art-C.NOUGARO-5-arg(17).m3u
art-C.SANTANA-5-arg(1).m3u
art-C.SEGUNDO-3-arg(1).m3u
art-CSN-3-arg(3).m3u
art-CSN-4-arg(2).m3u
art-CSN-5-4-3-arg(6).m3u
art-CSN-5-arg(1).m3u
art-CSN&Y-3-arg(8).m3u
art-CSN&Y-4-arg(12).m3u
art-CSN&Y-5-4-3-arg(21).m3u
art-CSN&Y-5-arg(1).m3u
art-C.TRENET-4-arg(3).m3u
art-C.TRENET-5-4-3-arg(4).m3u
art-C.TRENET-5-arg(1).m3u
art-C.VELOSO-5-arg(1).m3u
art-C.WILSON-3-arg(1).m3u
art-D.CROSBY-4-arg(1).m3u
art-DEREK & THE D.-3-arg(1).m3u
art-D.KRALL-3-arg(7).m3u
art-D.KRALL-4-arg(5).m3u
art-D.KRALL-5-4-3-arg(12).m3u
art-D.REEVES-3-arg(2).m3u
art-D.REEVES-4-arg(7).m3u
art-D.REEVES-5-4-3-arg(9).m3u
art-D.REINHARDT-5-arg(1).m3u
art-D.STRAITS-3-arg(21).m3u
art-D.STRAITS-4-arg(10).m3u
art-D.STRAITS-5-4-3-arg(50).m3u
art-D.STRAITS-5-arg(19).m3u
art-D.WASHINGTON-3-arg(2).m3u
art-D.WASHINGTON-4-arg(7).m3u
art-D.WASHINGTON-5-4-3-arg(10).m3u
art-D.WASHINGTON-5-arg(1).m3u
art-E.CABRERO-5-arg(1).m3u
art-E.CLAPTON-5-arg(3).m3u
art-E.FITZGERALD-3-arg(2).m3u
art-E.FITZGERALD-4-arg(2).m3u
art-E.FITZGERALD-5-4-3-arg(8).m3u
art-E.FITZGERALD-5-arg(4).m3u
art-E.MORRICONE-3-arg(3).m3u
art-E.MORRICONE-4-arg(3).m3u
art-E.MORRICONE-5-4-3-arg(9).m3u
art-E.MORRICONE-5-arg(3).m3u
art-F.CABREL-3-arg(2).m3u
art-F.CABREL-4-arg(7).m3u
art-F.CABREL-5-4-3-arg(17).m3u
art-F.CABREL-5-arg(8).m3u
art-F.SINATRA-5-arg(1).m3u
art-G.BRASSENS-3-arg(1).m3u
art-G.BRASSENS-4-arg(3).m3u
art-G.BRASSENS-5-4-3-arg(28).m3u
art-G.BRASSENS-5-arg(24).m3u
art-G.MARTIN-4-arg(1).m3u
art-G.MOORE-3-arg(1).m3u
art-G.MOORE-4-arg(3).m3u
art-G.MOORE-5-4-3-arg(9).m3u
art-G.MOORE-5-arg(5).m3u
art-G.NASH-3-arg(1).m3u
art-G.NASH-5-4-3-arg(3).m3u
art-G.NASH-5-arg(2).m3u
art-H.LAURIE-4-arg(1).m3u
art-H.SALVADOR-4-arg(1).m3u
art-I.SERRANO-5-arg(1).m3u
art-J.BERTOLA-5-arg(2).m3u
art-J.BREL-4-arg(4).m3u
art-J.BREL-5-4-3-arg(14).m3u
art-J.BREL-5-arg(10).m3u
art-J.HALLYDAY-4-arg(3).m3u
art-J.HALLYDAY-5-4-3-arg(4).m3u
art-J.HALLYDAY-5-arg(1).m3u
art-J.HENDRIX-3-arg(2).m3u
art-J.HENDRIX-4-arg(8).m3u
art-J.HENDRIX-5-4-3-arg(13).m3u
art-J.HENDRIX-5-arg(3).m3u
art-J.J.CALE-3-arg(14).m3u
art-J.J.CALE-4-arg(12).m3u
art-J.J.CALE-5-4-3-arg(35).m3u
art-J.J.CALE-5-arg(9).m3u
art-J.JOPLIN-3-arg(15).m3u
art-J.JOPLIN-4-arg(7).m3u
art-J.JOPLIN-5-4-3-arg(28).m3u
art-J.JOPLIN-5-arg(6).m3u
art-J.LANG-5-arg(1).m3u
art-J.LEE HOOKER-3-arg(1).m3u
art-J.LEE HOOKER-4-arg(3).m3u
art-J.LEE HOOKER-5-4-3-arg(5).m3u
art-J.LEE HOOKER-5-arg(1).m3u
art-J.MAYALL-3-arg(1).m3u
art-J.MOORE'S THREE BLAZERS-3-arg(1).m3u
art-J.OTIS QUINTETTE-3-arg(1).m3u
art-J.RODGERS-3-arg(1).m3u
art-J.SABINA-3-arg(2).m3u
art-J.SABINA-4-arg(2).m3u
art-J.SABINA-5-4-3-arg(5).m3u
art-J.SABINA-5-arg(1).m3u
art-J.WELLS-3-arg(1).m3u
art-J.WINTER-3-arg(1).m3u
art-J.WITHERSPOON-5-arg(1).m3u
art-K.MO'-4-arg(1).m3u
art-L.CARR-4-arg(1).m3u
art-L.ENFOIRéS-3-arg(1).m3u
art-L.FERRé-3-arg(5).m3u
art-L.FERRé-4-arg(9).m3u
art-L.FERRé-5-4-3-arg(24).m3u
art-L.FERRé-5-arg(10).m3u
art-L.FOLY-4-arg(1).m3u
art-L.FOLY-5-4-3-arg(2).m3u
art-L.FOLY-5-arg(1).m3u
art-L.JORDAN-4-arg(1).m3u
art-L.LEMAY-3-arg(5).m3u
art-L.LEMAY-4-arg(4).m3u
art-L.LEMAY-5-4-3-arg(9).m3u
art-MANASSAS-3-arg(1).m3u
art-M.BUBLé-4-arg(5).m3u
art-M.BUBLé-5-4-3-arg(7).m3u
art-M.BUBLé-5-arg(2).m3u
art-M.CARACOL-4-arg(1).m3u
art-M.DADI-3-arg(5).m3u
art-M.DADI-4-arg(5).m3u
art-M.DADI-5-4-3-arg(13).m3u
art-M.DADI-5-arg(3).m3u
art-M.FRED MCDOWELL-3-arg(1).m3u
art-M.GARCíA-3-arg(2).m3u
art-M.GARDOT-3-arg(7).m3u
art-M.GARDOT-4-arg(10).m3u
art-M.GARDOT-5-4-3-arg(28).m3u
art-M.GARDOT-5-arg(11).m3u
art-M.JIMéNEZ-4-arg(1).m3u
art-M.JONASZ-3-arg(16).m3u
art-M.JONASZ-4-arg(6).m3u
art-M.JONASZ-5-4-3-arg(30).m3u
art-M.JONASZ-5-arg(8).m3u
art-M.LE FORESTIER-3-arg(1).m3u
art-M.LE FORESTIER-4-arg(7).m3u
art-M.LE FORESTIER-5-4-3-arg(15).m3u
art-M.LE FORESTIER-5-arg(7).m3u
art-M.PEYROUX-3-arg(6).m3u
art-M.PEYROUX-4-arg(8).m3u
art-M.PEYROUX-5-4-3-arg(18).m3u
art-M.PEYROUX-5-arg(4).m3u
art-M.SHEIKS-3-arg(1).m3u
art-M.SLIM-3-arg(2).m3u
art-M.WATERS-3-arg(3).m3u
art-N.KING COLE-4-arg(1).m3u
art-N.KING COLE-5-4-3-arg(3).m3u
art-N.KING COLE-5-arg(2).m3u
art-N.SIMONE-3-arg(9).m3u
art-N.SIMONE-4-arg(33).m3u
art-N.SIMONE-5-4-3-arg(55).m3u
art-N.SIMONE-5-arg(13).m3u
art-N.YOUNG-3-arg(3).m3u
art-N.YOUNG-4-arg(7).m3u
art-N.YOUNG-5-4-3-arg(13).m3u
art-N.YOUNG-5-arg(3).m3u
art-O.REDDING-3-arg(4).m3u
art-O.REDDING-4-arg(5).m3u
art-O.REDDING-5-4-3-arg(20).m3u
art-O.REDDING-5-arg(11).m3u
art-O.RUSH-3-arg(5).m3u
art-O.RUSH-4-arg(1).m3u
art-O.RUSH-5-4-3-arg(6).m3u
art-P.DE BADAJOZ-4-arg(1).m3u
art-P.DE LUCíA-5-arg(3).m3u
art-P.FLOYD-3-arg(4).m3u
art-P.FLOYD-4-arg(7).m3u
art-P.FLOYD-5-4-3-arg(28).m3u
art-P.FLOYD-5-arg(17).m3u
art-P.KAAS-4-arg(1).m3u
art-P.MAYFIELD-3-arg(1).m3u
art-P.MILANéS-3-arg(1).m3u
art-P.MILANéS-5-4-3-arg(2).m3u
art-P.MILANéS-5-arg(1).m3u
art-R.BUCHANAN-3-arg(4).m3u
art-R.BUCHANAN-4-arg(4).m3u
art-R.BUCHANAN-5-4-3-arg(9).m3u
art-R.BUCHANAN-5-arg(1).m3u
art-R.CHARLEBOIS-4-arg(3).m3u
art-R.CHARLEBOIS-5-4-3-arg(4).m3u
art-R.CHARLEBOIS-5-arg(1).m3u
art-R.CHARLES-3-arg(7).m3u
art-R.CHARLES-4-arg(13).m3u
art-R.CHARLES-5-4-3-arg(28).m3u
art-R.CHARLES-5-arg(8).m3u
art-R.CRAY-3-arg(1).m3u
art-RENAUD-3-arg(2).m3u
art-RENAUD-4-arg(1).m3u
art-RENAUD-5-4-3-arg(4).m3u
art-RENAUD-5-arg(1).m3u
art-SABICAS-3-arg(1).m3u
art-SANTANA-3-arg(1).m3u
art-SANTANA-5-4-3-arg(4).m3u
art-SANTANA-5-arg(3).m3u
art-S.BECHET-3-arg(2).m3u
art-S.BECHET-4-arg(1).m3u
art-S.BECHET-5-4-3-arg(7).m3u
art-S.BECHET-5-arg(4).m3u
art-S.BECHET & HIS NEW ORL.FEETWARMERS-4-arg(2).m3u
art-S.BRIGHTMAN-4-arg(1).m3u
art-S.COOKE-3-arg(34).m3u
art-S.COOKE-4-arg(28).m3u
art-S.COOKE-5-4-3-arg(71).m3u
art-S.COOKE-5-arg(9).m3u
art-S.GAINSBOURG-4-arg(1).m3u
art-S.GRAPPELLI-5-arg(2).m3u
art-S.RAY VAUGHAN-3-arg(1).m3u
art-S.RAY VAUGHAN-4-arg(3).m3u
art-S.RAY VAUGHAN-5-4-3-arg(13).m3u
art-S.RAY VAUGHAN-5-arg(9).m3u
art-S.RAY VAUGHAN AND DOUBLE TROUBLE-4-arg(3).m3u
art-S.RAY VAUGHAN AND DOUBLE TROUBLE-5-4-3-arg(6).m3u
art-S.RAY VAUGHAN AND DOUBLE TROUBLE-5-arg(3).m3u
art-S.REGGIANI-3-arg(1).m3u
art-S.REGGIANI-4-arg(3).m3u
art-S.REGGIANI-5-4-3-arg(4).m3u
art-S.VAUGHAN-5-arg(1).m3u
art-T.BEATLES-3-arg(12).m3u
art-T.BEATLES-4-arg(8).m3u
art-T.BEATLES-5-4-3-arg(20).m3u
art-T.CLARK-3-arg(1).m3u
art-THE ALLMAN BR.BAND-3-arg(13).m3u
art-THE ALLMAN BR.BAND-4-arg(13).m3u
art-THE ALLMAN BR.BAND-5-4-3-arg(36).m3u
art-THE ALLMAN BR.BAND-5-arg(10).m3u
art-THE B.BOYS OF ALABAMA-3-arg(1).m3u
art-T.MAHAL-3-arg(1).m3u
art-T.MANHATTAN TRANSFER-5-arg(1).m3u
art-T.NOTTING HILLBILLIES-4-arg(5).m3u
art-T.NOTTING HILLBILLIES-5-4-3-arg(11).m3u
art-T.NOTTING HILLBILLIES-5-arg(6).m3u
art-T.WALKER-3-arg(2).m3u
art-T.WALKER-4-arg(1).m3u
art-T.WALKER-5-4-3-arg(4).m3u
art-T.WALKER-5-arg(1).m3u
art-T.YEARS AFTER-5-arg(1).m3u
art-VALDERRAMA-4-arg(3).m3u
art-V.MORRISON-4-arg(5).m3u
art-V.MORRISON-5-4-3-arg(15).m3u
art-V.MORRISON-5-arg(10).m3u
art-V.SANSON-3-arg(2).m3u
art-V.SANSON-4-arg(5).m3u
art-V.SANSON-5-4-3-arg(10).m3u
art-V.SANSON-5-arg(3).m3u
art-W.HARRIS-3-arg(1).m3u
art-W.SHELLER-4-arg(2).m3u
art-W.SHELLER-5-4-3-arg(3).m3u
art-W.SHELLER-5-arg(1).m3u
art-Y.MONTAND-3-arg(4).m3u
art-Y.MONTAND-4-arg(16).m3u
art-Y.MONTAND-5-4-3-arg(23).m3u
art-Y.MONTAND-5-arg(3).m3u
art-Z.Z. HILL-4-arg(1).m3u
art-ZZ TOP-3-arg(2).m3u
art-ZZ TOP-5-4-3-arg(4).m3u
art-ZZ TOP-5-arg(2).m3u
BALLAD-3-arg(17).m3u
BALLAD-4-arg(25).m3u
BALLAD-5-arg(60).m3u
bizarre-3-arg(1).m3u
BLUES-3-arg(29).m3u
blues-3-arg(2).m3u
BLUES-4-arg(19).m3u
BLUES-5-arg(15).m3u
BLUES OLD-4-arg(1).m3u
BLUES ROCK-3-arg(22).m3u
BLUES ROCK-4-arg(29).m3u
BLUES ROCK-5-arg(28).m3u
BOOGIE-3-arg(2).m3u
cante jondo-3-arg(5).m3u
cante jondo-4-arg(2).m3u
cante jondo-5-arg(7).m3u
chanson française-4-arg(16).m3u
chanson française-5-arg(40).m3u
CHARLESTON-3-arg(3).m3u
clarinette-5-arg(4).m3u
classic-4-arg(3).m3u
classic-5-arg(2).m3u
cool-3-arg(133).m3u
cool-4-arg(201).m3u
cool-5-arg(189).m3u
COUNTRY-3-arg(8).m3u
COUNTRY-4-arg(9).m3u
COUNTRY-5-arg(5).m3u
COUNTRY ROCK-3-arg(12).m3u
COUNTRY ROCK-4-arg(10).m3u
COUNTRY ROCK-5-arg(16).m3u
CUBA-3-arg(1).m3u
FLAMENCO-3-arg(8).m3u
FLAMENCO-4-arg(5).m3u
FLAMENCO-5-arg(10).m3u
FOLK-3-arg(8).m3u
FOLK-4-arg(8).m3u
FOLK-5-arg(12).m3u
FOLK ROCK-3-arg(21).m3u
FOLK ROCK-4-arg(10).m3u
FOLK ROCK-5-arg(8).m3u
french sixties-4-arg(3).m3u
FUNK SOUL-3-arg(2).m3u
FUNK SOUL-4-arg(4).m3u
FUNK SOUL-5-arg(2).m3u
GOSPEL-3-arg(6).m3u
GOSPEL-4-arg(9).m3u
GOSPEL-5-arg(4).m3u
irish-4-arg(2).m3u
IRISH-4-arg(5).m3u
IRISH-5-arg(10).m3u
JAZZ-3-arg(32).m3u
JAZZ-4-arg(65).m3u
JAZZ-5-arg(91).m3u
JAZZ gitan-5-arg(1).m3u
JAZZ LATINO-3-arg(1).m3u
lang-ENGLISH-5-4-3-arg(817).m3u
lang-FRENCH-5-4-3-arg(301).m3u
lang-ITALIAN-5-4-3-arg(2).m3u
lang-MULTIPLE-5-4-3-arg(5).m3u
lang-SPANISH-5-4-3-arg(34).m3u
latino-3-arg(1).m3u
LATINO-3-arg(5).m3u
LATINO-4-arg(1).m3u
LATINO-5-arg(6).m3u
move-3-arg(5).m3u
move-4-arg(13).m3u
move-5-arg(65).m3u
opera-3-arg(3).m3u
opera-4-arg(6).m3u
POP-4-arg(4).m3u
POP-5-arg(8).m3u
POP ROCK-4-arg(1).m3u
POP ROCK-5-arg(5).m3u
PROGRESSIVE ROCK-3-arg(8).m3u
PROGRESSIVE ROCK-4-arg(5).m3u
PROGRESSIVE ROCK-5-arg(12).m3u
RAG-3-arg(2).m3u
RAG-4-arg(1).m3u
R&B-3-arg(11).m3u
R&B-4-arg(2).m3u
R&B-5-arg(3).m3u
REGGAE-4-arg(1).m3u
REGGAE-5-arg(2).m3u
rhthm-4-arg(1).m3u
rhythm-3-arg(74).m3u
rhythm-4-arg(76).m3u
rhythm-5-arg(90).m3u
ROCK-3-arg(36).m3u
ROCK-4-arg(24).m3u
ROCK-5-arg(16).m3u
ROCK FOLK-5-arg(1).m3u
SALSA-3-arg(1).m3u
SALSA-4-arg(2).m3u
SALSA-5-arg(1).m3u
seguiriyas-5-arg(1).m3u
sixties-3-arg(2).m3u
slow-3-arg(9).m3u
slow-4-arg(22).m3u
slow-5-arg(43).m3u
slowsp-5-arg(1).m3u
SOFT ROCK-4-arg(2).m3u
SOUL-3-arg(20).m3u
SOUL-4-arg(18).m3u
SOUL-5-arg(28).m3u
soundtrack-3-arg(4).m3u
soundtrack-4-arg(3).m3u
soundtrack-5-arg(7).m3u
SOUTHERN ROCK-3-arg(6).m3u
SOUTHERN ROCK-4-arg(5).m3u
SPACE ROCK-3-arg(3).m3u
SPACE ROCK-4-arg(6).m3u
SPACE ROCK-5-arg(17).m3u
SPATIAL ROCK-3-arg(1).m3u
SPATIAL ROCK-4-arg(1).m3u
SPATIAL ROCK-5-arg(5).m3u
subversif-4-arg(1).m3u
subversif-5-arg(12).m3u
SWING-3-arg(92).m3u
SWING-4-arg(126).m3u
SWING-5-arg(76).m3u
SWING rhythm-4-arg(1).m3u

Thanks

[okein]

Hello,

As far as i know and for those wanting to sort their playlists in the 4.7 (build 3105) – September 11, 2012 version; you can use some thing like the following script, step by step, in the db.view page :

Code: Select all

--drop temp tables
DROP TABLE PLAYLIST_TMP
DROP TABLE PLAYLIST_FILE_TMP

--creation de la table temporaire des playlists
CREATE MEMORY TABLE PLAYLIST_TMP
(
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
   OLD_ID INTEGER,USERNAME VARCHAR NOT NULL,
   IS_PUBLIC BOOLEAN NOT NULL,
   NAME VARCHAR NOT NULL,COMMENT VARCHAR,
   FILE_COUNT INTEGER DEFAULT 0 NOT NULL,
   DURATION_SECONDS INTEGER DEFAULT 0 NOT NULL,
   CREATED TIMESTAMP NOT NULL,
   CHANGED TIMESTAMP NOT NULL,
   IMPORTED_FROM VARCHAR
)

--deuxieme table temporaire pour les liens fichiers
CREATE CACHED TABLE PLAYLIST_FILE_TMP
(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,PLAYLIST_ID INTEGER NOT NULL,MEDIA_FILE_ID INTEGER NOT NULL)


-- transfert des playlists dans la nouvelle table avec recalcul des ID
INSERT INTO
   PLAYLIST_TMP
(
   OLD_ID,
USERNAME,IS_PUBLIC,NAME,COMMENT,FILE_COUNT,DURATION_SECONDS,CREATED,CHANGED,IMPORTED_FROM)
SELECT
   ID,
   USERNAME,
   IS_PUBLIC,
   NAME,
   COMMENT,
   FILE_COUNT,
   DURATION_SECONDS,
   CREATED,
   CHANGED,
   IMPORTED_FROM
FROM
   PLAYLIST
ORDER BY
   NAME ASC


-- transfert des liens fichiers dans la nouvelle table avec nouveau playlist_id
INSERT INTO
   PLAYLIST_FILE_TMP
(
   PLAYLIST_ID,
   MEDIA_FILE_ID
)
SELECT
   PLAYLIST_TMP.ID,            --nouvel ID de playlist
   PLAYLIST_FILE.MEDIA_FILE_ID      --toujours le meme lien fichier
FROM
   PLAYLIST_FILE
INNER JOIN
   PLAYLIST_TMP ON PLAYLIST_FILE.PLAYLIST_ID = PLAYLIST_TMP.OLD_ID

--ptite verif   
SELECT COUNT(*) FROM PLAYLIST_TMP
SELECT COUNT(*) FROM PLAYLIST_FILE_TMP

--on supprime les données actuelles vu que tout est dans les tables temporaires
DELETE FROM PLAYLIST
DELETE FROM PLAYLIST_FILE
SELECT COUNT(*) FROM PLAYLIST
SELECT COUNT(*) FROM PLAYLIST_FILE

--on insere les nouvelles données de playlist en forcant les id
INSERT INTO
   PLAYLIST
   (ID, USERNAME,IS_PUBLIC,NAME,COMMENT,FILE_COUNT,DURATION_SECONDS,CREATED,CHANGED,IMPORTED_FROM)
SELECT
   ID,USERNAME,IS_PUBLIC,NAME,COMMENT,FILE_COUNT,DURATION_SECONDS,CREATED,CHANGED,IMPORTED_FROM
FROM PLAYLIST_TMP order by NAME asc

SELECT COUNT(*) FROM PLAYLIST

-- transfert des liens fichiers dans la nouvelle table avec nouveau playlist_id
INSERT INTO
   PLAYLIST_FILE
(
   PLAYLIST_ID,
   MEDIA_FILE_ID
)
SELECT
   PLAYLIST_FILE_TMP.PLAYLIST_ID,            --nouvel ID de playlist
   PLAYLIST_FILE_TMP.MEDIA_FILE_ID      --toujours le meme lien fichier
FROM
   PLAYLIST_FILE_TMP
   
   SELECT COUNT(*) FROM PLAYLIST_FILE
   
   --drop tables
DROP TABLE PLAYLIST_TMP
DROP TABLE PLAYLIST_FILE_TMP

Basically it transfers to temporary tables sorting by playlist name, cleans the original tables and fill them again with the updated values.

[JayG]

Hello okein,
I have tested your script on Subsonic v4.8 and 4.9.beta4 and I have an error :

Code: Select all

Unexpected token: FROM in statement [FROM]

It comes here :

Code: Select all

DELETE FROM PLAYLIST
DELETE FROM PLAYLIST_FILE
SELECT COUNT(*) FROM PLAYLIST
SELECT COUNT(*) FROM PLAYLIST_FILE

I've tested a TRUNCATE TABLE myTable instead of DELETE FROM myTable but I have the same error :

Code: Select all

Unexpected token: TRUNCATE in statement [TRUNCATE]

Any Idea?

[JayG]

DELETE FROM PLAYLIST WHERE ID > -1
seems to work well

However, If I run the script in one time, I always have errors... like Unexpected token.
But if I run the script part by part, there is no problem... Strange...