1 /*
2 * Copyright (C) 2009-2013 Christian Heckendorf <heckendorfc@gmail.com>
3 *
4 * This program is free software: you can redistribute it and/or modify
5 * it under the terms of the GNU General Public License as published by
6 * the Free Software Foundation, either version 3 of the License, or
7 * (at your option) any later version.
8 *
9 * This program is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 * GNU General Public License for more details.
13 *
14 * You should have received a copy of the GNU General Public License
15 * along with this program. If not, see <http://www.gnu.org/licenses/>.
16 */
17
18 #include "shuffle.h"
19 #include "defs.h"
20 #include "dbact.h"
21 #include "util.h"
22
23 struct shuffle_data{
24 int count;
25 int batch_count;
26 int tail;
27 char *query;
28 };
29
30 static struct shuffle_queries{
31 char group;
32 char *count;
33 char *primary_select;
34 char *fill;
35 }shuffle_q[]={
36 { 's',
37 "SELECT COUNT(SelectID) FROM TempSelect WHERE TempID=%d",
38 "SELECT SelectID,1 FROM TempSelect WHERE TempID=%d",
39 "SELECT SongID,%d FROM TempPlaylistSong WHERE \"Order\"<0 ORDER BY \"Order\" ASC"
40 //"UPDATE TempPlaylistSong SET \"Order\"=\"Order\"*-1"
41 },
42 { 'a',
43 "SELECT COUNT(SongID) FROM Song WHERE SongID IN (SELECT SelectID FROM TempSelect,Song WHERE SelectID=SongID AND TempID=%d GROUP BY AlbumID)",
44 "SELECT DISTINCT AlbumID,1 FROM Song WHERE SongID IN (SELECT SelectID FROM TempSelect WHERE TempID=%d)",
45 "SELECT SelectID,1 FROM TempSelect INNER JOIN Song ON SelectID=Song.SongID INNER JOIN TempPlaylistSong ON TempPlaylistSong.SongID=Song.AlbumID WHERE TempID=%d AND \"Order\"<1 ORDER BY \"Order\" ASC,Track ASC"
46 },
47 { 'r',
48 "SELECT COUNT(SongID) FROM Song WHERE SongID IN (SELECT SelectID FROM TempSelect,Song,AlbumArtist WHERE SelectID=SongID AND Song.AlbumID=AlbumArtist.AlbumID AND TempID=%d GROUP BY ArtistID)",
49 "SELECT DISTINCT ArtistID,1 FROM AlbumArtist NATURAL JOIN Song WHERE SongID IN (SELECT SelectID FROM TempSelect WHERE TempID=%d)",
50 "SELECT SelectID,1 FROM TempSelect INNER JOIN Song ON SelectID=Song.SongID NATURAL JOIN AlbumArtist INNER JOIN TempPlaylistSong ON TempPlaylistSong.SongID=ArtistID WHERE TempID=%d AND \"Order\"<1 ORDER BY \"Order\" ASC,AlbumID ASC,Track ASC"
51 },
52 {0,NULL,NULL,NULL}
53 };
54
fillTempPlaylistSong(int tempid,int group)55 static void fillTempPlaylistSong(int tempid,int group){
56 char query[450],cb_query[450];
57
58 harp_sqlite3_exec(conn,"CREATE TEMPORARY TRIGGER ShuffleList AFTER INSERT ON TempPlaylistSong BEGIN "
59 "UPDATE TempPlaylistSong SET \"Order\"=(SELECT count(PlaylistSongID) FROM TempPlaylistSong WHERE \"Order\">0) WHERE PlaylistSongID=NEW.PlaylistSongID;"
60 "END",NULL,NULL,NULL);
61
62 sprintf(cb_query,shuffle_q[group].fill,tempid);
63 sprintf(query,"INSERT INTO TempPlaylistSong(SongID,\"Order\") %s",cb_query);
64 debug(3,query);
65 harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
66 //harp_sqlite3_exec(conn,query,batch_tempplaylistsong_insert_cb,&data,NULL);
67
68 harp_sqlite3_exec(conn,"DROP TRIGGER ShuffleList",NULL,NULL,NULL);
69 }
70
71 /*
72 static int rand_next(struct shuffle_data *data){
73 return random()%data->count;
74 }
75
76 static int clean_shuffle_cb(void *arg, int col_count, char **row, char **titles){
77 struct shuffle_data *data=(struct shuffle_data*)arg;
78 sprintf(data->query,"UPDATE TempPlaylistSong SET \"Order\"=%d WHERE SongID=%s",-(data->tail++),*row);
79 harp_sqlite3_exec(conn,data->query,NULL,NULL,NULL);
80 data->batch_count++;
81 if(data->count>DB_BATCH_SIZE){
82 data->batch_count=0;
83 harp_sqlite3_exec(conn,"COMMIT",NULL,NULL,NULL);
84 harp_sqlite3_exec(conn,"BEGIN",NULL,NULL,NULL);
85 }
86 return 0;
87 }
88
89 static int shuffle_cb(void *arg, int col_count, char **row, char **titles){
90 unsigned int order;
91 struct shuffle_data *data=(struct shuffle_data*)arg;
92 if((order=rand_next(data))==-1){
93 debug(2,"rand_next returned -1");
94 return 1;
95 }
96 sprintf(data->query,"INSERT INTO TempPlaylistSong(SongID,\"Order\") VALUES(%s,%d)",*row,-(order+1));
97 harp_sqlite3_exec(conn,data->query,NULL,NULL,NULL);
98 data->batch_count++;
99 if(data->count>DB_BATCH_SIZE){
100 data->batch_count=0;
101 harp_sqlite3_exec(conn,"COMMIT",NULL,NULL,NULL);
102 harp_sqlite3_exec(conn,"BEGIN",NULL,NULL,NULL);
103 }
104 return 0;
105 }
106 */
107
shuffle(int list)108 void shuffle(int list){
109 char query[350],cb_query[350];
110 int tempid,group=0;
111 unsigned int items=0;
112
113 srandom((unsigned int)time(NULL));
114
115 if(arglist[ASHUFFLE].active && arglist[ASHUFFLE].subarg){
116 for(group=0;shuffle_q[group].group && shuffle_q[group].group!=*arglist[ASHUFFLE].subarg;group++);
117 if(!shuffle_q[group].group){
118 debug(2,"Defaulting shuffle group to 's'");
119 group=0;
120 }
121 }
122
123 if(list){
124 sprintf(query,"SELECT %%d,SongID FROM PlaylistSong WHERE PlaylistID=%d",list);
125 tempid=insertTempSelectQuery(query);
126 createTempPlaylistSong();
127 }
128 else{
129 tempid=insertTempSelectQuery("SELECT %d,SongID FROM TempPlaylistSong");
130 }
131
132 sprintf(query,shuffle_q[group].count,tempid);
133 harp_sqlite3_exec(conn,query,uint_return_cb,&items,NULL);
134 sprintf(query,shuffle_q[group].primary_select,tempid);
135
136 harp_sqlite3_exec(conn,"DELETE FROM TempPlaylistSong",NULL,NULL,NULL);
137
138 harp_sqlite3_exec(conn,"CREATE TEMPORARY TRIGGER ShuffleList AFTER INSERT ON TempPlaylistSong BEGIN "
139 "UPDATE TempPlaylistSong SET \"Order\"=(SELECT -count(PlaylistSongID) FROM TempPlaylistSong) WHERE PlaylistSongID=NEW.PlaylistSongID;"
140 "END",NULL,NULL,NULL);
141
142 sprintf(cb_query,"INSERT INTO TempPlaylistSong(SongID,\"Order\") %s ORDER BY random()",shuffle_q[group].primary_select);
143 sprintf(query,cb_query,tempid);
144 harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
145
146 harp_sqlite3_exec(conn,"DROP TRIGGER ShuffleList",NULL,NULL,NULL);
147
148 harp_sqlite3_exec(conn,"DELETE FROM TempPlaylistSong WHERE \"Order\">0",NULL,NULL,NULL); /* Just in case */
149 fillTempPlaylistSong(tempid,group);
150
151 harp_sqlite3_exec(conn,"DELETE FROM TempPlaylistSong WHERE \"Order\"<1",NULL,NULL,NULL); /* Just in case */
152 if(arglist[AZSHUFFLE].active){
153 debug(1,"Z Shuffling");
154 zshuffle(items);
155 }
156 }
157
158 struct zs_arg{
159 const unsigned int items;
160 const unsigned int group_items;
161 const unsigned int increment;
162 const unsigned int slide;
163 unsigned int count;
164 int slidemod;
165 char *query;
166 };
167
168 struct candidate_data{
169 int *list;
170 int num;
171 };
172
zs_candidate_cb(void * arg,int col_count,char ** row,char ** titles)173 static int zs_candidate_cb(void *arg, int col_count, char **row, char **titles){
174 struct candidate_data *data = (struct candidate_data*)arg;
175
176 data->list[data->num++]=strtol(row[0],NULL,10);
177
178 return 0;
179 }
180
181 /*
182 static int zs_skip_cb(void *arg, int col_count, char **row, char **titles){
183 struct zs_arg *data = (struct zs_arg*)arg;
184 int id=data->count;
185
186 // SWAP ORDERS!
187 sprintf(data->query,"UPDATE TempPlaylistSong SET \"Order\"=%s WHERE \"Order\"=%d",*(row+2),id);
188 harp_sqlite3_exec(conn,data->query,NULL,NULL,NULL);
189 sprintf(data->query,"UPDATE TempPlaylistSong SET \"Order\"=%d WHERE PlaylistSongID=%s",id,*(row+1));
190 harp_sqlite3_exec(conn,data->query,NULL,NULL,NULL);
191
192 data->count+=data->increment;
193 return 0;
194 }
195
196 static int zs_slide_cb(void *arg, int col_count, char **row, char **titles){
197 struct zs_arg *data = (struct zs_arg*)arg;
198 int order=(int)strtol(*(row+2),NULL,10);
199 int stat;
200 if(col_count>3)
201 stat=(int)strtol(*(row+3),NULL,10);
202 else
203 stat=1;
204 int neworder=order+(data->slidemod*stat);
205 if(neworder<1)neworder=1;
206 else if(neworder>data->items)neworder=data->items;
207
208 if(data->slidemod<0) // Moving up
209 sprintf(data->query,"UPDATE TempPlaylistSong SET \"Order\"=\"Order\"+1 WHERE \"Order\">%d AND \"Order\"<(SELECT \"Order\" FROM TempPlaylistSong WHERE PlaylistSongID=%s LIMIT 1)",neworder-1,*(row+1));
210 else // Moving down
211 sprintf(data->query,"UPDATE TempPlaylistSong SET \"Order\"=\"Order\"-1 WHERE \"Order\"<%d AND \"Order\">(SELECT \"Order\" FROM TempPlaylistSong WHERE PlaylistSongID=%s LIMIT 1)",neworder+1,*(row+1));
212 harp_sqlite3_exec(conn,data->query,NULL,NULL,NULL);
213 sprintf(data->query,"UPDATE TempPlaylistSong SET \"Order\"=%d WHERE PlaylistSongID=%s",neworder,*(row+1));
214 harp_sqlite3_exec(conn,data->query,NULL,NULL,NULL);
215
216 data->count++;
217 if(data->count>DB_BATCH_SIZE){
218 data->count=0;
219 harp_sqlite3_exec(conn,"COMMIT",NULL,NULL,NULL);
220 harp_sqlite3_exec(conn,"BEGIN",NULL,NULL,NULL);
221 }
222 return 0;
223 }
224 */
225
update_order_zs(struct candidate_data * candlist,int slidemod)226 static void update_order_zs(struct candidate_data *candlist, int slidemod){
227 char query[250];
228
229 //harp_sqlite3_exec(conn,"BEGIN",NULL,NULL,NULL);
230 while(candlist->num>0){
231 sprintf(query,"UPDATE TempPlaylistSong SET \"Order\"=\"Order\"%+d WHERE PlaylistSongID=%d",slidemod,candlist->list[--candlist->num]);
232 debug(3,query);
233 harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
234 }
235 //harp_sqlite3_exec(conn,"COMMIT",NULL,NULL,NULL);
236 }
237
skip_order_zs(struct candidate_data * candlist,int slidemod)238 static void skip_order_zs(struct candidate_data *candlist, int slidemod){
239 char query[250];
240
241 //harp_sqlite3_exec(conn,"BEGIN",NULL,NULL,NULL);
242 while(candlist->num>0){
243 candlist->num--;
244 sprintf(query,"UPDATE TempPlaylistSong SET \"Order\"=%d WHERE PlaylistSongID=%d",candlist->num+1,candlist->list[candlist->num]);
245 harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
246 }
247 //harp_sqlite3_exec(conn,"COMMIT",NULL,NULL,NULL);
248 }
249
zshuffle(unsigned int items)250 void zshuffle(unsigned int items){
251 char query[450],cb_query[250];
252 int x;
253 const int ten_percent=items*0.1;
254 const int alter_limit=100; // Max songs to alter
255 const int mod_count=ten_percent>alter_limit?alter_limit:ten_percent; // Songs to alter this round
256 const int group=(mod_count>>2)|1; // Number of songs to float each iter
257 struct candidate_data candlist;
258
259 srandom((unsigned int)time(NULL));
260 struct zs_arg data={.items=items,.group_items=group,.increment=(random()%2)+2,.slide=(random()%3)+2,.count=0,.slidemod=1,.query=cb_query};
261
262 sprintf(query,"Mod count: %d\nGroup: %d\nIncrement: %d",mod_count,group,data.increment);
263 debug(2,query);
264
265 candlist.list=malloc(sizeof(*candlist.list)*mod_count);
266 if(candlist.list==NULL)
267 return;
268
269 // Skip Count
270 data.slidemod=data.slide*3;
271 data.count=0;
272 sprintf(query,"CREATE TEMPORARY TRIGGER SlideList AFTER UPDATE ON TempPlaylistSong BEGIN "
273 "UPDATE TempPlaylistSong SET \"Order\"=\"Order\"-1 WHERE \"Order\"<=NEW.\"Order\" AND \"Order\">=OLD.\"Order\" AND PlaylistSongID!=OLD.PlaylistSongID; "
274 "END");
275 x=harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
276 sprintf(query,"SELECT PlaylistSongID FROM TempPlaylistSong NATURAL JOIN Song WHERE SkipCount>0 AND \"Order\"<(SELECT COUNT(PlaylistSongID)-%d FROM TempPlaylistSong) ORDER BY SkipCount DESC LIMIT %d",data.slidemod,mod_count);
277 candlist.num=0;
278 harp_sqlite3_exec(conn,query,zs_candidate_cb,&candlist,NULL);
279 sprintf(query,"Skip count mod count: %d",candlist.num);
280 debug(2,query);
281 update_order_zs(&candlist,data.slidemod);
282 harp_sqlite3_exec(conn,"DROP TRIGGER SlideList",NULL,NULL,NULL);
283
284
285 // Play Count
286 data.slidemod=data.slide*-2;
287 data.count=0;
288 sprintf(query,"CREATE TEMPORARY TRIGGER SlideList AFTER UPDATE ON TempPlaylistSong BEGIN "
289 "UPDATE TempPlaylistSong SET \"Order\"=\"Order\"+1 WHERE \"Order\">=NEW.\"Order\" AND \"Order\"<=OLD.\"Order\" AND PlaylistSongID!=OLD.PlaylistSongID; "
290 "END");
291 x=harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
292 sprintf(query,"SELECT PlaylistSongID FROM TempPlaylistSong NATURAL JOIN Song WHERE PlayCount>0 AND \"Order\">%d ORDER BY SkipCount DESC LIMIT %d",-data.slidemod,mod_count);
293 candlist.num=0;
294 harp_sqlite3_exec(conn,query,zs_candidate_cb,&candlist,NULL);
295 sprintf(query,"Play count mod count: %d",candlist.num);
296 debug(2,query);
297 update_order_zs(&candlist,data.slidemod);
298
299 // Rating
300 data.slidemod=data.slide*-1;
301 data.count=0;
302 sprintf(query,"SELECT PlaylistSongID FROM TempPlaylistSong NATURAL JOIN Song WHERE Rating>3 AND \"Order\">%d ORDER BY SkipCount DESC LIMIT %d",-data.slidemod,mod_count);
303 candlist.num=0;
304 harp_sqlite3_exec(conn,query,zs_candidate_cb,&candlist,NULL);
305 sprintf(query,"Rating mod count: %d",candlist.num);
306 debug(2,query);
307 update_order_zs(&candlist,data.slidemod);
308 harp_sqlite3_exec(conn,"DROP TRIGGER SlideList",NULL,NULL,NULL);
309
310 // Last Play
311 data.count=data.increment;
312 sprintf(query,"SELECT COUNT(PlaylistSongID) FROM TempPlaylistSong NATURAL JOIN Song WHERE LastPlay=0");
313 harp_sqlite3_exec(conn,query,uint_return_cb,&x,NULL);
314 if(x>ten_percent){
315 debug(1,"ZSHUFFLE | Too many unplayed songs; skipping LastPlay modifier.");
316 free(candlist.list);
317 return;
318 }
319 sprintf(query,"CREATE TEMPORARY TRIGGER SkipList BEFORE UPDATE ON TempPlaylistSong BEGIN "
320 "UPDATE TempPlaylistSong SET \"Order\"=OLD.\"Order\" WHERE \"Order\"=NEW.\"Order\"; "
321 "END");
322 x=harp_sqlite3_exec(conn,query,NULL,NULL,NULL);
323 sprintf(query,"SELECT PlaylistSongID FROM TempPlaylistSong WHERE SongID IN (SELECT SongID FROM Song NATURAL JOIN TempPlaylistSong ORDER BY LastPlay ASC LIMIT %d) ORDER BY \"Order\" ASC",mod_count);
324 candlist.num=0;
325 harp_sqlite3_exec(conn,query,zs_candidate_cb,&candlist,NULL);
326 skip_order_zs(&candlist,data.slidemod);
327 harp_sqlite3_exec(conn,"DROP TRIGGER SkipList",NULL,NULL,NULL);
328
329 free(candlist.list);
330 }
331
332