projects / paraslash.git / blob
? search:
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
make the mysql_selector use command_util.sh
[paraslash.git] / mysql_selector.c
1 /*
2 * Copyright (C) 1999-2007 Andre Noll <maan@systemlinux.org>
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 2 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, write to the Free Software
16 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
17 */
18
19 /** \file mysql_selector.c para_server's mysql-based audio file selector */
20
21 /** \cond some internal constants */
22 #define MEDIUM_BLOB_SIZE 16777220 /* (2**24 + 4) */
23 #define BLOB_SIZE 65539 /* (2**16 + 3) */
24 /** \endcond */
25 #include "server.cmdline.h"
26 #include "server.h"
27 #include "vss.h"
28 #include "db.h"
29 #include <mysql/mysql.h>
30 #include <mysql/mysql_version.h>
31 #include "error.h"
32 #include "net.h"
33 #include "string.h"
34 #include "user_list.h"
35 #include "mysql_selector_command_list.h"
36
37 /** pointer to the shared memory area */
38 extern struct misc_meta_data *mmd;
39
40 static void *mysql_ptr = NULL;
41
42 static struct para_macro macro_list[] = {
43 { .name = "IS_N_SET",
44 .replacement = "(data.%s != '1')"
45 }, {
46 .name = "IS_SET",
47 .replacement = "(data.%s = '1')"
48 }, {
49 .name = "PICID",
50 .replacement = "%sdata.Pic_Id"
51 }, {
52 .name = "NAME_LIKE",
53 .replacement = "(data.name like '%s')"
54 }, {
55 .name = "LASTPLAYED",
56 .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())"
57 "-UNIX_TIMESTAMP(data.Lastplayed))/60)"
58 }, {
59 .name = "NUMPLAYED",
60 .replacement = "%sdata.Numplayed"
61 }, {
62 .name = NULL,
63 }
64 };
65
66 static int real_query(const char *query)
67 {
68 if (!mysql_ptr)
69 return -E_NOTCONN;
70 PARA_DEBUG_LOG("%s\n", query);
71 if (mysql_real_query(mysql_ptr, query, strlen(query))) {
72 PARA_ERROR_LOG("real_query error (%s)\n",
73 mysql_error(mysql_ptr));
74 return -E_QFAILED;
75 }
76 return 1;
77 }
78
79 /*
80 * Use open connection given by mysql_ptr to query server. Returns a
81 * result pointer on succes and NULL on errors
82 */
83 static struct MYSQL_RES *get_result(const char *query)
84 {
85 void *result;
86
87 if (real_query(query) < 0)
88 return NULL;
89 result = mysql_store_result(mysql_ptr);
90 if (!result)
91 PARA_ERROR_LOG("%s", "store_result error\n");
92 return result;
93 }
94 /*
95 * write input from fd to dynamically allocated char array,
96 * but maximal max_size byte. Return size.
97 */
98 static int fd2buf(int fd, char **buf_ptr, size_t max_size)
99 {
100 const size_t chunk_size = 1024;
101 size_t size = 2048;
102 char *buf = para_malloc(size * sizeof(char)), *p = buf;
103 int ret;
104
105 while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) {
106 p += ret;
107 if ((p - buf) + chunk_size >= size) {
108 char *tmp;
109
110 size *= 2;
111 if (size > max_size) {
112 ret = -E_TOOBIG;
113 goto out;
114 }
115 tmp = para_realloc(buf, size);
116 p = (p - buf) + tmp;
117 buf = tmp;
118 }
119 }
120 if (ret < 0)
121 goto out;
122 *buf_ptr = buf;
123 ret = p - buf;
124 out:
125 if (ret < 0 && buf)
126 free(buf);
127 return ret;
128 }
129
130 static char *escape_blob(const char* old, int size)
131 {
132 char *new;
133
134 if (!mysql_ptr || size < 0)
135 return NULL;
136 new = para_malloc(2 * size * sizeof(char) + 1);
137 mysql_real_escape_string(mysql_ptr, new, old, size);
138 return new;
139 }
140
141 static char *escape_str(const char* old)
142 {
143 return escape_blob(old, strlen(old));
144 }
145
146 static char *escaped_basename(const char *name)
147 {
148 char *esc, *bn = para_basename(name);
149
150 if (!bn)
151 return NULL;
152 esc = escape_str(bn);
153 free(bn);
154 return esc;
155 }
156
157 /*
158 * new attribute
159 */
160 int com_na(__a_unused int fd, int argc, char *argv[])
161 {
162 char *q, *tmp;
163 int ret;
164
165 if (argc < 2)
166 return -E_MYSQL_SYNTAX;
167 tmp = escape_str(argv[1]);
168 if (!tmp)
169 return -E_ESCAPE;
170 q = make_message("alter table data add %s char(1) "
171 "not null default 0", tmp);
172 free(tmp);
173 ret = real_query(q);
174 free(q);
175 return ret;
176 }
177
178 /*
179 * delete attribute
180 */
181 int com_da(__a_unused int fd, int argc, char *argv[])
182 {
183 char *q, *tmp;
184 int ret;
185
186 if (argc < 2)
187 return -E_MYSQL_SYNTAX;
188 tmp = escape_str(argv[1]);
189 if (!tmp)
190 return -E_ESCAPE;
191 q = make_message("alter table data drop %s", tmp);
192 free(tmp);
193 ret = real_query(q);
194 free(q);
195 return ret;
196 }
197
198 /* stradd/pic_add */
199 static int com_stradd_picadd(int fd, int argc, char *argv[])
200 {
201 char *blob = NULL, *esc_blob = NULL, *q = NULL, *tmp = NULL;
202 const char *fmt, *del_fmt;
203 int ret, stradd = strcmp(argv[0], "picadd");
204 size_t size;
205
206 if (argc < 2)
207 return -E_MYSQL_SYNTAX;
208 if (strlen(argv[1]) >= MAXLINE - 1)
209 return -E_NAMETOOLONG;
210 if (!mysql_ptr)
211 return -E_NOTCONN;
212 if (stradd) {
213 size = BLOB_SIZE;
214 fmt = "insert into streams (name, def) values ('%s','%s')";
215 del_fmt="delete from streams where name='%s'";
216 } else {
217 size = MEDIUM_BLOB_SIZE;
218 fmt = "insert into pics (name, pic) values ('%s','%s')";
219 del_fmt="delete from pics where pic='%s'";
220 }
221 tmp = escape_str(argv[1]);
222 if (!tmp)
223 return -E_ESCAPE;
224 q = make_message(del_fmt, tmp);
225 free(tmp);
226 ret = real_query(q);
227 free(q);
228 if (ret < 0)
229 return ret;
230 if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0))
231 return ret;
232 if ((ret = fd2buf(fd, &blob, size)) < 0)
233 return ret;
234 size = ret;
235 if (stradd)
236 blob[size] = '\0';
237 ret = -E_ESCAPE;
238 esc_blob = escape_blob(blob, size);
239 if (!esc_blob)
240 goto out;
241 tmp = escape_str(argv[1]);
242 if (!tmp)
243 goto out;
244 q = make_message(fmt, tmp, esc_blob);
245 ret = real_query(q);
246 out:
247 free(blob);
248 free(esc_blob);
249 free(tmp);
250 free(q);
251 return ret;
252 }
253
254 /* stradd */
255 int com_stradd(int fd, int argc, char *argv[])
256 {
257 return com_stradd_picadd(fd, argc, argv);
258 }
259
260 /* pic_add */
261 int com_picadd(int fd, int argc, char *argv[])
262 {
263 return com_stradd_picadd(fd, argc, argv);
264 }
265
266 /*
267 * print results to fd
268 */
269 static int print_results(int fd, void *result,
270 unsigned int top, unsigned int left,
271 unsigned int bottom, unsigned int right)
272 {
273 unsigned int i,j;
274 int ret;
275 MYSQL_ROW row;
276
277 for (i = top; i <= bottom; i++) {
278 row = mysql_fetch_row(result);
279 if (!row || !row[0])
280 return -E_NOROW;
281 for (j = left; j <= right; j++) {
282 ret = send_va_buffer(fd, j == left? "%s" : "\t%s",
283 row[j]? row[j] : "NULL");
284 if (ret < 0)
285 return ret;
286 }
287 ret = send_buffer(fd, "\n");
288 if (ret < 0)
289 return ret;
290 }
291 return 0;
292 }
293
294 /*
295 * verbatim
296 */
297 int com_verb(int fd, int argc, char *argv[])
298 {
299 void *result = NULL;
300 int ret;
301 unsigned int num_rows, num_fields;
302 char *tmp;
303
304 if (argc < 2)
305 return -E_MYSQL_SYNTAX;
306 tmp = escape_str(argv[1]);
307 if (!tmp)
308 return -E_ESCAPE;
309 result = get_result(tmp);
310 free(tmp);
311 if (!result)
312 /* return success, because it's ok to have no results */
313 return 1;
314 num_fields = mysql_field_count(mysql_ptr);
315 num_rows = mysql_num_rows(result);
316 ret = 1;
317 if (num_fields && num_rows)
318 ret = print_results(fd, result, 0, 0, num_rows - 1,
319 num_fields - 1);
320 mysql_free_result(result);
321 return ret;
322 }
323
324 /* returns NULL on errors or if there are no atts defined yet */
325 static void *get_all_attributes(void)
326 {
327 void *result = get_result("desc data");
328 unsigned int num_rows;
329
330 if (!result)
331 return NULL;
332 num_rows = mysql_num_rows(result);
333 if (num_rows < 5) {
334 mysql_free_result(result);
335 return NULL;
336 }
337 mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */
338 return result;
339 }
340
341 /*
342 * list all attributes
343 */
344 int com_laa(int fd, int argc, __a_unused char *argv[])
345 {
346 void *result;
347 int ret;
348
349 if (argc != 1)
350 return -E_MYSQL_SYNTAX;
351 result = get_all_attributes();
352 if (!result)
353 return -E_NOATTS;
354 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0);
355 mysql_free_result(result);
356 return ret;
357 }
358
359 /*
360 * history
361 */
362 int com_hist(int fd, int argc, char *argv[]) {
363 int ret;
364 void *result = NULL;
365 char *q, *atts;
366 unsigned int num_rows;
367
368 if (argc > 3)
369 return -E_MYSQL_SYNTAX;
370 if (argc > 1) {
371 char *tmp = escape_str(argv[1]);
372 if (!tmp)
373 return -E_ESCAPE;
374 atts = make_message("where %s = '1'", tmp);
375 free(tmp);
376 } else
377 atts = para_strdup(NULL);
378
379 q = make_message("select name, to_days(now()) - to_days(lastplayed) from "
380 "data %s order by lastplayed", atts);
381 free(atts);
382 result = get_result(q);
383 free(q);
384 if (!result)
385 return -E_NORESULT;
386 num_rows = mysql_num_rows(result);
387 ret = 1;
388 if (num_rows)
389 ret = print_results(fd, result, 0, 0, num_rows - 1, 1);
390 mysql_free_result(result);
391 return ret;
392 }
393
394 /*
395 * get last num audio files
396 */
397 int com_last(int fd, int argc, char *argv[])
398 {
399 void *result = NULL;
400 char *q;
401 int num, ret;
402
403 if (argc < 2)
404 num = 10;
405 else
406 num = atoi(argv[1]);
407 if (!num)
408 return -E_MYSQL_SYNTAX;
409 q = make_message("select name from data order by lastplayed desc "
410 "limit %u", num);
411 result = get_result(q);
412 free(q);
413 if (!result)
414 return -E_NORESULT;
415 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0);
416 mysql_free_result(result);
417 return ret;
418 }
419
420 int com_mbox(int fd, int argc, char *argv[])
421 {
422 void *result;
423 MYSQL_ROW row;
424 int ret;
425 unsigned int num_rows, num_fields;
426 char *query = para_strdup("select concat('From foo@localhost ', "
427 "date_format(Lastplayed, '%a %b %e %T %Y'), "
428 "'\nReceived: from\nTo: bar\n");
429
430 ret = -E_NOATTS;
431 result = get_all_attributes();
432 if (!result)
433 goto out;
434 ret = -E_NOROW;
435 while ((row = mysql_fetch_row(result))) {
436 char *tmp;
437
438 if (!row[0])
439 goto out;
440 tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query,
441 row[0], row[0]);
442 free(query);
443 query = tmp;
444 }
445 query = para_strcat(query,
446 "From: a\n"
447 "Subject: "
448 "', name, '"
449 "\n\n\n"
450 "') from data"
451 );
452 if (argc >= 2) {
453 char *esc = escape_str(argv[1]), *tmp;
454 ret = -E_ESCAPE;
455 if (!esc)
456 goto out;
457 tmp = make_message("%s where name LIKE '%s'", query, esc);
458 free(esc);
459 free(query);
460 query = tmp;
461 }
462 mysql_free_result(result);
463 ret = -E_NORESULT;
464 result = get_result(query);
465 if (!result)
466 goto out;
467 ret = -E_EMPTY_RESULT;
468 num_fields = mysql_field_count(mysql_ptr);
469 num_rows = mysql_num_rows(result);
470 if (!num_fields || !num_rows)
471 goto out;
472 ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1);
473 out:
474 free(query);
475 if (result)
476 mysql_free_result(result);
477 return ret;
478 }
479
480 /* get attributes by name. If verbose is not 0, get_a writes a string
481 * into atts of the form 'att1="0",att2="1"', which is used in com_cam
482 * for contructing a mysql update query.
483 * never returns NULL in *NON VERBOSE* mode
484 */
485 static char *get_atts(char *name, int verbose)
486 {
487 char *atts = NULL, *buf, *ebn;
488 void *result = NULL, *result2 = NULL;
489 MYSQL_ROW row, row2;
490 int i, ret;
491 unsigned int num_fields;
492
493 ret = -E_NOATTS;
494 result2 = get_all_attributes();
495 if (!result2)
496 goto out;
497 ret = -E_ESCAPE;
498 if (!(ebn = escaped_basename(name)))
499 goto out;
500 buf = make_message("select * from data where name='%s'", ebn);
501 free(ebn);
502 ret = -E_NORESULT;
503 result = get_result(buf);
504 free(buf);
505 if (!result)
506 goto out;
507 ret = -E_EMPTY_RESULT;
508 num_fields = mysql_num_fields(result);
509 if (num_fields < 5)
510 goto out;
511 mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */
512 row = mysql_fetch_row(result);
513 ret = -E_NOROW;
514 if (!row)
515 goto out;
516 for (i = 4; i < num_fields; i++) {
517 int is_set = row[i] && !strcmp(row[i], "1");
518 row2 = mysql_fetch_row(result2);
519 if (!row2 || !row2[0])
520 goto out;
521 if (atts && (verbose || is_set))
522 atts = para_strcat(atts, verbose? "," : " ");
523 if (is_set || verbose)
524 atts = para_strcat(atts, row2[0]);
525 if (verbose)
526 atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\"");
527 }
528 ret = 1;
529 out:
530 if (result2)
531 mysql_free_result(result2);
532 if (result)
533 mysql_free_result(result);
534 if (!atts && !verbose)
535 atts = para_strdup("(none)");
536 return atts;
537 }
538
539 /* never returns NULL in verbose mode */
540 static char *get_meta(char *name, int verbose)
541 {
542 MYSQL_ROW row;
543 void *result = NULL;
544 char *ebn, *q, *ret = NULL;
545 const char *verbose_fmt =
546 "select concat('lastplayed: ', "
547 "(to_days(now()) - to_days(lastplayed)),"
548 "' day(s). numplayed: ', numplayed, "
549 "', pic: ', pic_id) "
550 "from data where name = '%s'";
551 /* is that really needed? */
552 const char *fmt = "select concat('lastplayed=\\'', lastplayed, "
553 "'\\', numplayed=\\'', numplayed, "
554 "'\\', pic_id=\\'', pic_id, '\\'') "
555 "from data where name = '%s'";
556
557 if (!(ebn = escaped_basename(name)))
558 goto out;
559 q = make_message(verbose? verbose_fmt : fmt, ebn);
560 free(ebn);
561 result = get_result(q);
562 free(q);
563 if (!result)
564 goto out;
565 row = mysql_fetch_row(result);
566 if (!row || !row[0])
567 goto out;
568 ret = para_strdup(row[0]);
569 out:
570 if (result)
571 mysql_free_result(result);
572 if (!ret && verbose)
573 ret = para_strdup("(not yet played)");
574 return ret;
575 }
576
577 static char *get_dir(char *name)
578 {
579 char *ret = NULL, *q, *ebn;
580 void *result;
581 MYSQL_ROW row;
582
583 if (!(ebn = escaped_basename(name)))
584 return NULL;
585 q = make_message("select dir from dir where name = '%s'", ebn);
586 free(ebn);
587 result = get_result(q);
588 free(q);
589 if (!result)
590 return NULL;
591 row = mysql_fetch_row(result);
592 if (row && row[0])
593 ret = para_strdup(row[0]);
594 mysql_free_result(result);
595 return ret;
596 }
597
598 /* never returns NULL */
599 static char *get_current_stream(void)
600 {
601 char *ret;
602 MYSQL_ROW row;
603 void *result = get_result("select def from streams where "
604 "name = 'current_stream'");
605
606 if (!result)
607 goto err_out;
608 row = mysql_fetch_row(result);
609 if (!row || !row[0])
610 goto err_out;
611 ret = para_strdup(row[0]);
612 mysql_free_result(result);
613 return ret;
614 err_out:
615 if (result)
616 mysql_free_result(result);
617 return para_strdup("(none)");
618 }
619
620 /*
621 * Read stream definition of stream streamname and construct mysql
622 * query. Return NULL on errors. If streamname is NULL, use current
623 * stream. If that is also NULL, use query that selects everything.
624 * If filename is NULL, query will list everything, otherwise only
625 * the score of given file.
626 */
627 static char *get_query(char *streamname, char *filename, int with_path)
628 {
629 char *accept_opts = NULL, *deny_opts = NULL, *score = NULL;
630 char *where_clause, *order, *query;
631 char command[255] = ""; /* buffer for sscanf */
632 void *result;
633 MYSQL_ROW row;
634 char *end, *tmp;
635 char *select_clause = NULL;
636 if (!streamname)
637 tmp = get_current_stream();
638 else {
639 tmp = escape_str(streamname);
640 if (!tmp)
641 return NULL;
642 }
643 if (!strcmp(tmp, "(none)")) {
644 free(tmp);
645 if (filename) {
646 char *ret, *ebn = escaped_basename(filename);
647 if (!ebn)
648 return NULL;
649 ret = make_message("select to_days(now()) - "
650 "to_days(lastplayed) from data "
651 "where name = '%s'", ebn);
652 free(ebn);
653 return ret;
654 }
655 if (with_path)
656 return make_message(
657 "select concat(dir.dir, '/', dir.name) "
658 "from data, dir where dir.name = data.name "
659 "order by data.lastplayed"
660 );
661 return make_message(
662 "select name from data where name is not NULL "
663 "order by lastplayed"
664 );
665 }
666 free(tmp);
667 query = make_message("select def from streams where name = '%s'",
668 streamname);
669 result = get_result(query);
670 free(query);
671 query = NULL;
672 if (!result)
673 goto out;
674 row = mysql_fetch_row(result);
675 if (!row || !row[0])
676 goto out;
677 end = row[0];
678 while (*end) {
679 int n;
680 char *arg, *line = end;
681
682 if (!(end = strchr(line, '\n')))
683 break;
684 *end = '\0';
685 end++;
686 if (sscanf(line, "%200s%n", command, &n) < 1)
687 continue;
688 arg = line + n;
689 if (!strcmp(command, "accept:")) {
690 char *tmp2 = s_a_r_list(macro_list, arg);
691 if (accept_opts)
692 accept_opts = para_strcat(
693 accept_opts, " or ");
694 accept_opts = para_strcat(accept_opts, tmp2);
695 free(tmp2);
696 continue;
697 }
698 if (!strcmp(command, "deny:")) {
699 char *tmp2 = s_a_r_list(macro_list, arg);
700 if (deny_opts)
701 deny_opts = para_strcat(deny_opts, " or ");
702 deny_opts = para_strcat(deny_opts, tmp2);
703 free(tmp2);
704 continue;
705 }
706 if (!score && !strcmp(command, "score:"))
707 score = s_a_r_list(macro_list, arg);
708 }
709 if (!score) {
710 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
711 if (!score)
712 goto out;
713 }
714 if (filename) {
715 char *ebn = escaped_basename(filename);
716 if (!ebn)
717 goto out;
718 select_clause = make_message("select %s from data ", score);
719 free(score);
720 where_clause = make_message( "where name = '%s' ", ebn);
721 free(ebn);
722 order = para_strdup("");
723 goto write_query;
724 }
725 select_clause = para_strdup(with_path?
726 "select concat(dir.dir, '/', dir.name) from data, dir "
727 "where dir.name = data.name "
728 :
729 "select name from data where name is not NULL");
730 order = make_message("order by -(%s)", score);
731 free(score);
732 if (accept_opts && deny_opts) {
733 where_clause = make_message("and ((%s) and not (%s)) ",
734 accept_opts, deny_opts);
735 goto write_query;
736 }
737 if (accept_opts && !deny_opts) {
738 where_clause = make_message("and (%s) ", accept_opts);
739 goto write_query;
740 }
741 if (!accept_opts && deny_opts) {
742 where_clause = make_message("and not (%s) ", deny_opts);
743 goto write_query;
744 }
745 where_clause = para_strdup("");
746 write_query:
747 query = make_message("%s %s %s", select_clause, where_clause, order);
748 free(order);
749 free(select_clause);
750 free(where_clause);
751 out:
752 if (accept_opts)
753 free(accept_opts);
754 if (deny_opts)
755 free(deny_opts);
756 if (result)
757 mysql_free_result(result);
758 return query;
759 }
760
761
762
763 /*
764 * This is called from server and from some commands. Name must not be NULL
765 * Never returns NULL.
766 */
767 static char *get_selector_info(char *name)
768 {
769 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
770 void *result = NULL;
771 MYSQL_ROW row = NULL;
772
773 if (!name)
774 return para_strdup("(none)");
775 stream = get_current_stream();
776 meta = get_meta(name, 1);
777 atts = get_atts(name, 0);
778 dir = get_dir(name);
779 /* get score */
780 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
781 if (!query)
782 goto write;
783 result = get_result(query);
784 free(query);
785 if (result)
786 row = mysql_fetch_row(result);
787 write:
788 info = make_message("dbinfo1:dir: %s\n"
789 "dbinfo2:stream: %s, %s, score: %s\n"
790 "dbinfo3:%s\n",
791 dir? dir : "(not contained in table)",
792 stream, meta,
793 (result && row && row[0])? row[0] : "(no score)",
794 atts);
795 if (dir)
796 free(dir);
797 if (meta)
798 free(meta);
799 if (atts)
800 free(atts);
801 if (stream)
802 free(stream);
803 if (result)
804 mysql_free_result(result);
805 return info;
806 }
807
808
809 /* might return NULL */
810 static char *get_current_audio_file(void)
811 {
812 char *name;
813 mmd_lock();
814 name = para_basename(mmd->filename);
815 mmd_unlock();
816 return name;
817 }
818
819 /* list attributes / print database info */
820 static int com_la_info(int fd, int argc, char *argv[])
821 {
822 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
823 int ret, com_la = strcmp(argv[0], "info");
824
825 if (argc < 2) {
826 ret = -E_GET_AUDIO_FILE;
827 if (!(name = get_current_audio_file()))
828 goto out;
829 ret = send_va_buffer(fd, "%s\n", name);
830 if (ret < 0)
831 goto out;
832 } else {
833 ret = -E_ESCAPE;
834 if (!(name = escaped_basename(argv[1])))
835 goto out;
836 }
837 meta = get_meta(name, 1);
838 atts = get_atts(name, 0);
839 dir = get_dir(name);
840 if (com_la)
841 ret = send_va_buffer(fd, "%s\n", atts);
842 else
843 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
844 dir? dir : "(not contained in table)", meta, atts);
845 out:
846 free(meta);
847 free(atts);
848 free(dir);
849 free(name);
850 return ret;
851 }
852
853 /* list attributes */
854 int com_la(int fd, int argc, char *argv[])
855 {
856 return com_la_info(fd, argc, argv);
857 }
858
859 /* print database info */
860 int com_info(int fd, int argc, char *argv[])
861 {
862 return com_la_info(fd, argc, argv);
863 }
864
865 static int change_stream(const char *stream)
866 {
867 char *query;
868 int ret;
869 query = make_message("update streams set def='%s' "
870 "where name = 'current_stream'", stream);
871 ret = real_query(query);
872 free(query);
873 return ret;
874 }
875
876 static int get_pic_id_by_name(char *name)
877 {
878 char *q, *ebn;
879 void *result = NULL;
880 long unsigned ret;
881 MYSQL_ROW row;
882
883 if (!(ebn = escaped_basename(name)))
884 return -E_ESCAPE;
885 q = make_message("select pic_id from data where name = '%s'", ebn);
886 free(ebn);
887 result = get_result(q);
888 free(q);
889 if (!result)
890 return -E_NORESULT;
891 row = mysql_fetch_row(result);
892 ret = -E_NOROW;
893 if (row && row[0])
894 ret = atol(row[0]);
895 mysql_free_result(result);
896 return ret;
897 }
898
899 static int remove_entry(const char *name)
900 {
901 char *q, *ebn = escaped_basename(name);
902 int ret = -E_ESCAPE;
903
904 if (!ebn)
905 goto out;
906 q = make_message("delete from data where name = '%s'", ebn);
907 real_query(q); /* ignore errors */
908 free(q);
909 q = make_message("delete from dir where name = '%s'", ebn);
910 real_query(q); /* ignore errors */
911 free(q);
912 ret = 1;
913 out:
914 free(ebn);
915 return ret;
916 }
917
918 static int add_entry(const char *name)
919 {
920 char *q, *dn, *ebn = NULL, *edn = NULL;
921 int ret;
922
923 if (!name || !*name)
924 return -E_MYSQL_SYNTAX;
925 ebn = escaped_basename(name);
926 if (!ebn)
927 return -E_ESCAPE;
928 ret = -E_MYSQL_SYNTAX;
929 dn = para_dirname(name);
930 if (!dn)
931 goto out;
932 ret = -E_ESCAPE;
933 edn = escape_str(dn);
934 free(dn);
935 if (!edn || !*edn)
936 goto out;
937 q = make_message("insert into data (name, pic_id) values "
938 "('%s', '%s')", ebn, "1");
939 ret = real_query(q);
940 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
941 free(q);
942 if (ret < 0)
943 goto out;
944 q = make_message("insert into dir (name, dir) values "
945 "('%s', '%s')", ebn, edn);
946 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
947 ret = real_query(q);
948 free(q);
949 out:
950 if (ebn)
951 free(ebn);
952 if (edn)
953 free(edn);
954 return ret;
955 }
956
957 /*
958 * remove/add entries
959 */
960 static int com_rm_ne(__a_unused int fd, int argc, char *argv[])
961 {
962 int ne = !strcmp(argv[0], "ne");
963 int i, ret;
964 if (argc < 2)
965 return -E_MYSQL_SYNTAX;
966 for (i = 1; i < argc; i++) {
967 ret = remove_entry(argv[i]);
968 if (ret < 0)
969 return ret;
970 if (!ne)
971 continue;
972 ret = add_entry(argv[i]);
973 if (ret < 0)
974 return ret;
975 }
976 return 1;
977 }
978
979 /*
980 * rm
981 */
982 int com_rm(int fd, int argc, char *argv[])
983 {
984 return com_rm_ne(fd, argc, argv);
985 }
986
987 /*
988 * ne
989 */
990 int com_ne(int fd, int argc, char *argv[])
991 {
992 return com_ne(fd, argc, argv);
993 }
994
995 /*
996 * mv: rename entry
997 */
998 int com_mv(__a_unused int fd, int argc, char *argv[])
999 {
1000 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1001 int ret;
1002
1003 if (argc != 3)
1004 return -E_MYSQL_SYNTAX;
1005 ret = -E_ESCAPE;
1006 ebn1 = escaped_basename(argv[1]);
1007 ebn2 = escaped_basename(argv[2]);
1008 if (!ebn1 || !ebn2 || !*ebn1 || !*ebn2)
1009 goto out;
1010 ret = -E_MYSQL_SYNTAX;
1011 if (!strcmp(ebn1, ebn2))
1012 goto update_dir;
1013 remove_entry(argv[2]); /* no need to escape, ignore error */
1014 q = make_message("update data set name = '%s' where name = '%s'",
1015 ebn2, ebn1);
1016 ret = real_query(q);
1017 free(q);
1018 if (ret < 0)
1019 goto out;
1020 ret = -E_AUDIO_FILE;
1021 if (!mysql_affected_rows(mysql_ptr))
1022 goto out;
1023 q = make_message("update dir set name = '%s' where name = '%s'",
1024 ebn2, ebn1);
1025 ret = real_query(q);
1026 free(q);
1027 if (ret < 0)
1028 goto out;
1029 update_dir:
1030 ret = 1;
1031 dn = para_dirname(argv[2]);
1032 if (!dn)
1033 goto out;
1034 ret = -E_ESCAPE;
1035 edn = escape_str(dn);
1036 free(dn);
1037 if (!edn)
1038 goto out;
1039 ret = 1;
1040 if (!*edn)
1041 goto out;
1042 q = make_message("update dir set dir = '%s' where name = '%s'",
1043 edn, ebn2);
1044 ret = real_query(q);
1045 free(q);
1046 out:
1047 free(edn);
1048 free(ebn1);
1049 free(ebn2);
1050 return ret;
1051 }
1052
1053 /*
1054 * set field
1055 */
1056 static int com_set(__a_unused int fd, int argc, char *argv[])
1057 {
1058 char *q, *ebn;
1059 long unsigned id;
1060 int i, ret;
1061 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1062
1063 if (argc < 3)
1064 return -E_MYSQL_SYNTAX;
1065 id = atol(argv[1]);
1066 for (i = 2; i < argc; i++) {
1067 ebn = escaped_basename(argv[i]);
1068 if (!ebn)
1069 return -E_ESCAPE;
1070 q = make_message("update data set %s = %lu "
1071 "where name = '%s'", field, id, ebn);
1072 free(ebn);
1073 ret = real_query(q);
1074 free(q);
1075 if (ret < 0)
1076 return ret;
1077 }
1078 return 1;
1079 }
1080
1081 /*
1082 * snp: set numplayed
1083 */
1084 int com_picass(int fd, int argc, char *argv[])
1085 {
1086 return com_set(fd, argc, argv);
1087 }
1088
1089 /*
1090 * snp: set numplayed
1091 */
1092 int com_snp(int fd, int argc, char *argv[])
1093 {
1094 return com_set(fd, argc, argv);
1095 }
1096
1097 /*
1098 * picch: change entry's name in pics table
1099 */
1100 int com_picch(__a_unused int fd, int argc, char *argv[])
1101 {
1102 int ret;
1103 long unsigned id;
1104 char *q, *tmp;
1105
1106 if (argc != 3)
1107 return -E_MYSQL_SYNTAX;
1108 id = atol(argv[1]);
1109 ret = -E_ESCAPE;
1110 tmp = escape_str(argv[2]);
1111 if (!tmp)
1112 return -E_ESCAPE;
1113 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1114 free(tmp);
1115 ret = real_query(q);
1116 free(q);
1117 return ret;
1118 }
1119
1120 /*
1121 * piclist: print list of pics in db
1122 */
1123 int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1124 {
1125 void *result = NULL;
1126 MYSQL_ROW row;
1127 unsigned long *length;
1128 int ret;
1129
1130 if (argc != 1)
1131 return -E_MYSQL_SYNTAX;
1132 result = get_result("select id,name,pic from pics order by id");
1133 if (!result)
1134 return -E_NORESULT;
1135 while ((row = mysql_fetch_row(result))) {
1136 length = mysql_fetch_lengths(result);
1137 if (!row || !row[0] || !row[1] || !row[2])
1138 continue;
1139 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1140 if (ret < 0)
1141 goto out;
1142 }
1143 ret = 1;
1144 out:
1145 mysql_free_result(result);
1146 return ret;
1147 }
1148
1149 /*
1150 * picdel: delete picture from database
1151 */
1152 int com_picdel(int fd, int argc, char *argv[])
1153 {
1154 char *q;
1155 long unsigned id;
1156 my_ulonglong aff;
1157 int i, ret;
1158
1159 if (argc < 2)
1160 return -E_MYSQL_SYNTAX;
1161 for (i = 1; i < argc; i++) {
1162 id = atol(argv[i]);
1163 q = make_message("delete from pics where id = %lu", id);
1164 ret = real_query(q);
1165 free(q);
1166 if (ret < 0)
1167 return ret;
1168 aff = mysql_affected_rows(mysql_ptr);
1169 if (!aff) {
1170 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1171 if (ret < 0)
1172 return ret;
1173 continue;
1174 }
1175 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1176 ret = real_query(q);
1177 free(q);
1178 }
1179 return 1;
1180 }
1181 /*
1182 * pic: get picture by name or by number
1183 */
1184 int com_pic(int fd, int argc, char *argv[])
1185 {
1186 void *result = NULL;
1187 MYSQL_ROW row;
1188 unsigned long *length, id;
1189 int ret;
1190 char *q, *name = NULL;
1191
1192 if (argc < 2) {
1193 ret = -E_GET_AUDIO_FILE;
1194 name = get_current_audio_file();
1195 } else {
1196 ret = -E_ESCAPE;
1197 name = escaped_basename(argv[1]);
1198 }
1199 if (!name)
1200 return ret;
1201 if (*name == '#')
1202 id = atoi(name + 1);
1203 else
1204 id = get_pic_id_by_name(name);
1205 free(name);
1206 if (id <= 0)
1207 return id;
1208 q = make_message("select pic from pics where id = '%lu'", id);
1209 result = get_result(q);
1210 free(q);
1211 if (!result)
1212 return -E_NORESULT;
1213 row = mysql_fetch_row(result);
1214 ret = -E_NOROW;
1215 if (!row || !row[0])
1216 goto out;
1217 length = mysql_fetch_lengths(result);
1218 ret = send_bin_buffer(fd, row[0], *length);
1219 out:
1220 mysql_free_result(result);
1221 return ret;
1222 }
1223
1224 /* strdel */
1225 int com_strdel(__a_unused int fd, int argc, char *argv[])
1226 {
1227 char *q, *tmp;
1228 int ret;
1229
1230 if (argc < 2)
1231 return -E_MYSQL_SYNTAX;
1232 tmp = escape_str(argv[1]);
1233 if (!tmp)
1234 return -E_ESCAPE;
1235 q = make_message("delete from streams where name='%s'", tmp);
1236 free(tmp);
1237 ret = real_query(q);
1238 free(q);
1239 return ret;
1240 }
1241
1242 /*
1243 * ls
1244 */
1245 int com_ls(int fd, int argc, char *argv[])
1246 {
1247 char *q;
1248 void *result;
1249 int ret;
1250 unsigned int num_rows;
1251
1252 if (argc > 2)
1253 return -E_MYSQL_SYNTAX;
1254 if (argc > 1) {
1255 char *tmp = escape_str(argv[1]);
1256 if (!tmp)
1257 return -E_ESCAPE;
1258 q = make_message("select name from data where name like '%s'",
1259 tmp);
1260 free(tmp);
1261 } else
1262 q = para_strdup("select name from data");
1263 result = get_result(q);
1264 free(q);
1265 if (!result)
1266 return -E_NORESULT;
1267 num_rows = mysql_num_rows(result);
1268 ret = 1;
1269 if (num_rows)
1270 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1271 mysql_free_result(result);
1272 return ret;
1273 }
1274
1275 /*
1276 * summary
1277 */
1278 int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1279 {
1280 MYSQL_ROW row;
1281 MYSQL_ROW row2;
1282 void *result;
1283 void *result2 = NULL;
1284 const char *fmt = "select count(name) from data where %s='1'";
1285 int ret = -E_NORESULT;
1286
1287 if (argc != 1)
1288 return -E_MYSQL_SYNTAX;
1289 result = get_all_attributes();
1290 if (!result)
1291 goto out;
1292 while ((row = mysql_fetch_row(result))) {
1293 char *buf;
1294
1295 ret = -E_NOROW;
1296 if (!row[0])
1297 goto out;
1298 ret = -E_NORESULT;
1299 buf = make_message(fmt, row[0]);
1300 result2 = get_result(buf);
1301 free(buf);
1302 if (!result2)
1303 goto out;
1304 ret = -E_NOROW;
1305 row2 = mysql_fetch_row(result2);
1306 if (!row2 || !row2[0])
1307 goto out;
1308 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1309 if (ret < 0)
1310 goto out;
1311 }
1312 ret = 1;
1313 out:
1314 if (result2)
1315 mysql_free_result(result2);
1316 if (result)
1317 mysql_free_result(result);
1318 return ret;
1319 }
1320
1321 static int get_numplayed(char *name)
1322 {
1323 void *result;
1324 MYSQL_ROW row;
1325 const char *fmt = "select numplayed from data where name = '%s'";
1326 char *buf = make_message(fmt, name);
1327 int ret = -E_NORESULT;
1328
1329 result = get_result(buf);
1330 free(buf);
1331 if (!result)
1332 goto out;
1333 ret = -E_NOROW;
1334 row = mysql_fetch_row(result);
1335 if (!row || !row[0])
1336 goto out;
1337 ret = atoi(row[0]);
1338 out:
1339 if (result)
1340 mysql_free_result(result);
1341 return ret;
1342 }
1343
1344 static int update_audio_file(char *name)
1345 {
1346 int ret;
1347 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1348 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1349 char *q;
1350 char *ebn = escaped_basename(name);
1351
1352 ret = -E_ESCAPE;
1353 if (!ebn)
1354 goto out;
1355 q = make_message(fmt1, ebn);
1356 ret = real_query(q);
1357 free(q);
1358 if (ret < 0)
1359 goto out;
1360 ret = get_numplayed(ebn);
1361 if (ret < 0)
1362 goto out;
1363 q = make_message(fmt2, ret + 1, ebn);
1364 ret = real_query(q);
1365 free(q);
1366 out:
1367 free(ebn);
1368 return ret;
1369 }
1370
1371 /* If called as child, mmd_lock must be held */
1372 static void update_mmd(char *info)
1373 {
1374 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1375 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1376 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1377 }
1378
1379 static void update_audio_file_server_handler(char *name)
1380 {
1381 char *info;
1382 info = get_selector_info(name);
1383 update_mmd(info);
1384 free(info);
1385 update_audio_file(name);
1386 }
1387
1388 int com_us(__a_unused int fd, int argc, char *argv[])
1389 {
1390 char *tmp;
1391 int ret;
1392
1393 if (argc != 2)
1394 return -E_MYSQL_SYNTAX;
1395 tmp = escape_str(argv[1]);
1396 if (!tmp)
1397 return -E_ESCAPE;
1398 ret = update_audio_file(argv[1]);
1399 free(tmp);
1400 return ret;
1401 }
1402
1403 static void refresh_selector_info(void)
1404 {
1405 char *name = get_current_audio_file();
1406 char *info;
1407
1408 if (!name)
1409 return;
1410 info = get_selector_info(name);
1411 free(name);
1412 mmd_lock();
1413 update_mmd(info);
1414 mmd_unlock();
1415 free(info);
1416 }
1417
1418 /* select previous / next stream */
1419 static int com_ps_ns(__a_unused int fd, int argc, char *argv[])
1420 {
1421 char *query, *stream = get_current_stream();
1422 void *result = get_result("select name from streams");
1423 MYSQL_ROW row;
1424 int match = -1, ret, i;
1425 unsigned int num_rows;
1426
1427 if (argc != 1)
1428 return -E_MYSQL_SYNTAX;
1429 ret = -E_NORESULT;
1430 if (!result)
1431 goto out;
1432 num_rows = mysql_num_rows(result);
1433 ret = -E_EMPTY_RESULT;
1434 if (num_rows < 2)
1435 goto out;
1436 ret = -E_NOROW;
1437 for (i = 0; i < num_rows; i++) {
1438 row = mysql_fetch_row(result);
1439 if (!row || !row[0])
1440 goto out;
1441 if (!strcmp(row[0], "current_stream"))
1442 continue;
1443 if (!strcmp(row[0], stream)) {
1444 match = i;
1445 break;
1446 }
1447 }
1448 ret = -E_NO_STREAM;
1449 if (match < 0)
1450 goto out;
1451 if (!strcmp(argv[0], "ps"))
1452 i = match > 0? match - 1 : num_rows - 1;
1453 else
1454 i = match < num_rows - 1? match + 1 : 0;
1455 ret = -E_NOROW;
1456 mysql_data_seek(result, i);
1457 row = mysql_fetch_row(result);
1458 if (!row || !row[0])
1459 goto out;
1460 if (!strcmp(row[0], "current_stream")) {
1461 if (!strcmp(argv[0], "ps")) {
1462 i = match - 2;
1463 i = i < 0? i + num_rows : i;
1464 } else {
1465 i = match + 2;
1466 i = i > num_rows - 1? i - num_rows : i;
1467 }
1468 mysql_data_seek(result, i);
1469 row = mysql_fetch_row(result);
1470 if (!row || !row[0])
1471 goto out;
1472 }
1473 query = make_message("update streams set def='%s' where name = "
1474 "'current_stream'", row[0]);
1475 ret = real_query(query);
1476 free(query);
1477 refresh_selector_info();
1478 out:
1479 free(stream);
1480 if (result)
1481 mysql_free_result(result);
1482 return ret;
1483 }
1484
1485 /* select previous stream */
1486 int com_ps(int fd, int argc, char *argv[])
1487 {
1488 return com_ps_ns(fd, argc, argv);
1489 }
1490
1491 /* select next stream */
1492 int com_ns(int fd, int argc, char *argv[])
1493 {
1494 return com_ps_ns(fd, argc, argv);
1495 }
1496
1497 /* streams */
1498 int com_streams(int fd, int argc, __a_unused char *argv[])
1499 {
1500 unsigned int num_rows;
1501 int i, ret = -E_NORESULT;
1502 void *result;
1503 MYSQL_ROW row;
1504
1505 if (argc > 1 && strcmp(argv[1], "current_stream"))
1506 return -E_MYSQL_SYNTAX;
1507 if (argc > 1) {
1508 char *cs = get_current_stream();
1509 ret = send_va_buffer(fd, "%s\n", cs);
1510 free(cs);
1511 return ret;
1512 }
1513 result = get_result("select name from streams");
1514 if (!result)
1515 goto out;
1516 num_rows = mysql_num_rows(result);
1517 ret = 1;
1518 if (!num_rows)
1519 goto out;
1520 ret = -E_NOROW;
1521 for (i = 0; i < num_rows; i++) {
1522 row = mysql_fetch_row(result);
1523 if (!row || !row[0])
1524 goto out;
1525 if (strcmp(row[0], "current_stream"))
1526 send_va_buffer(fd, "%s\n", row[0]);
1527 }
1528 ret = 1;
1529 out:
1530 if (result)
1531 mysql_free_result(result);
1532 return ret;
1533 }
1534
1535 /* query stream definition */
1536 int com_strq(int fd, int argc, char *argv[])
1537 {
1538 MYSQL_ROW row;
1539 char *query, *name;
1540 void *result;
1541 int ret;
1542
1543 if (argc < 2) {
1544 ret = -E_GET_STREAM;
1545 name = get_current_stream();
1546 } else {
1547 ret = -E_ESCAPE;
1548 name = escaped_basename(argv[1]);
1549 }
1550 if (!name)
1551 return ret;
1552 ret = -E_NORESULT;
1553 query = make_message("select def from streams where name='%s'", name);
1554 free(name);
1555 result = get_result(query);
1556 free(query);
1557 if (!result)
1558 goto out;
1559 ret = -E_NOROW;
1560 row = mysql_fetch_row(result);
1561 if (!row || !row[0])
1562 goto out;
1563 /* no '\n' needed */
1564 ret = send_buffer(fd, row[0]);
1565 out:
1566 if (result)
1567 mysql_free_result(result);
1568 return ret;
1569 }
1570
1571 /* change stream / change stream and play */
1572 static int com_cs_csp(int fd, int argc, char *argv[])
1573 {
1574 int ret, stream_change;
1575 char *query, *stream = NULL;
1576 char *old_stream = get_current_stream();
1577 int csp = !strcmp(argv[0], "csp");
1578
1579 ret = -E_MYSQL_SYNTAX;
1580 if (argc > 2)
1581 goto out;
1582 if (argc == 1) {
1583 if (csp)
1584 goto out;
1585 ret = send_va_buffer(fd, "%s\n", old_stream);
1586 goto out;
1587 }
1588 ret = -E_GET_QUERY;
1589 /* test if stream is valid, no need to escape argv[1] */
1590 query = get_query(argv[1], NULL, 0);
1591 if (!query)
1592 goto out;
1593 free(query);
1594 /* stream is ok */
1595 stream = escape_str(argv[1]);
1596 if (!stream)
1597 goto out;
1598 stream_change = strcmp(stream, old_stream);
1599 if (stream_change) {
1600 ret = change_stream(stream);
1601 if (ret < 0)
1602 goto out;
1603 refresh_selector_info();
1604 }
1605 if (csp) {
1606 mmd_lock();
1607 mmd->new_vss_status_flags |= VSS_PLAYING;
1608 if (stream_change)
1609 mmd->new_vss_status_flags |= VSS_NEXT;
1610 mmd_unlock();
1611 }
1612 ret = 1;
1613 out:
1614 free(old_stream);
1615 free(stream);
1616 return ret;
1617 }
1618
1619 /* change stream */
1620 int com_cs(int fd, int argc, char *argv[])
1621 {
1622 return com_cs_csp(fd, argc, argv);
1623 }
1624
1625 /* change stream and play */
1626 int com_csp(int fd, int argc, char *argv[])
1627 {
1628 return com_cs_csp(fd, argc, argv);
1629 }
1630
1631 /* score list / skip */
1632 static int com_sl_skip(int fd, int argc, char *argv[])
1633 {
1634 void *result = NULL;
1635 MYSQL_ROW row;
1636 int ret, i, skip = !strcmp(argv[0], "skip");
1637 char *query, *stream, *tmp;
1638 unsigned int num_rows, num;
1639
1640 if (argc < 2)
1641 return -E_MYSQL_SYNTAX;
1642 num = atoi(argv[1]);
1643 if (!num)
1644 return -E_MYSQL_SYNTAX;
1645 if (argc == 2) {
1646 stream = get_current_stream();
1647 if (!stream)
1648 return -E_GET_STREAM;
1649 } else {
1650 stream = escape_str(argv[2]);
1651 if (!stream)
1652 return -E_ESCAPE;
1653 }
1654 tmp = get_query(stream, NULL, 0);
1655 free(stream);
1656 if (!tmp)
1657 return -E_GET_QUERY;
1658 query = make_message("%s limit %d", tmp, num);
1659 free(tmp);
1660 ret = -E_NORESULT;
1661 result = get_result(query);
1662 free(query);
1663 if (!result)
1664 goto out;
1665 ret = -E_EMPTY_RESULT;
1666 num_rows = mysql_num_rows(result);
1667 if (!num_rows)
1668 goto out;
1669 for (i = 0; i < num_rows && i < num; i++) {
1670 row = mysql_fetch_row(result);
1671 if (skip) {
1672 send_va_buffer(fd, "Skipping %s\n", row[0]);
1673 update_audio_file(row[0]);
1674 } else
1675 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
1676 }
1677 ret = 1;
1678 out:
1679 if (result)
1680 mysql_free_result(result);
1681 return ret;
1682 }
1683
1684 /* score list */
1685 int com_sl(int fd, int argc, char *argv[])
1686 {
1687 return com_sl_skip(fd, argc, argv);
1688 }
1689
1690 /* skip */
1691 int com_skip(int fd, int argc, char *argv[])
1692 {
1693 return com_sl_skip(fd, argc, argv);
1694 }
1695
1696 /*
1697 * update attributes of name
1698 */
1699 static int update_atts(int fd, char *name, char *atts)
1700 {
1701 int ret;
1702 char *ebn, *q, *old, *new = NULL;
1703
1704 if (!mysql_ptr)
1705 return -E_NOTCONN;
1706 ebn = escaped_basename(name);
1707 if (!ebn)
1708 return -E_ESCAPE;
1709 q = make_message("update data set %s where name = '%s'", atts, ebn);
1710 old = get_atts(ebn, 0);
1711 send_va_buffer(fd, "old: %s\n", old);
1712 free(old);
1713 ret = real_query(q);
1714 free(q);
1715 if (ret < 0)
1716 goto out;
1717 new = get_atts(ebn, 0);
1718 ret = send_va_buffer(fd, "new: %s\n", new);
1719 free(new);
1720 out:
1721 free(ebn);
1722 return ret;
1723 }
1724
1725 /*
1726 * set attributes
1727 */
1728 int com_sa(int fd, int argc, char *argv[])
1729 {
1730 int i, ret;
1731 char *atts = NULL, *name;
1732
1733 if (argc < 2)
1734 return -E_MYSQL_SYNTAX;
1735 for (i = 1; i < argc; i++) {
1736 int unset = 0;
1737 char *esc, *tmp, *p =argv[i];
1738 int len = strlen(p);
1739
1740 if (!len)
1741 continue;
1742 switch (p[len - 1]) {
1743 case '+':
1744 unset = 0;
1745 break;
1746 case '-':
1747 unset = 1;
1748 break;
1749 default:
1750 goto no_more_atts;
1751 }
1752 p[len - 1] = '\0';
1753 esc = escape_str(p);
1754 if (!esc)
1755 return -E_ESCAPE;
1756 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
1757 unset? "0" : "1");
1758 free(esc);
1759 atts = para_strcat(atts, tmp);
1760 free(tmp);
1761 }
1762 no_more_atts:
1763 if (!atts)
1764 return -E_NOATTS;
1765 if (i >= argc) { /* no name given, use current af */
1766 ret = -E_GET_AUDIO_FILE;
1767 if (!(name = get_current_audio_file()))
1768 goto out;
1769 ret = update_atts(fd, name, atts);
1770 free(name);
1771 } else {
1772 ret = 1;
1773 for (; argv[i] && ret >= 0; i++)
1774 ret = update_atts(fd, argv[i], atts);
1775 }
1776 refresh_selector_info();
1777 out:
1778 free(atts);
1779 return ret;
1780 }
1781
1782 /*
1783 * copy attributes
1784 */
1785 int com_cam(int fd, int argc, char *argv[])
1786 {
1787 char *name = NULL, *meta = NULL, *atts = NULL;
1788 int i, ret;
1789
1790 if (argc < 3)
1791 return -E_MYSQL_SYNTAX;
1792 if (!(name = escaped_basename(argv[1])))
1793 return -E_ESCAPE;
1794 ret = -E_NOATTS;
1795 if (!(atts = get_atts(name, 1)))
1796 goto out;
1797 ret = -E_META;
1798 if (!(meta = get_meta(name, 0)))
1799 goto out;
1800 for (i = 2; i < argc; i++) {
1801 char *ebn, *q;
1802 ret = -E_ESCAPE;
1803 if (!(ebn = escaped_basename(argv[i])))
1804 goto out;
1805 ret = send_va_buffer(fd, "updating %s\n", ebn);
1806 if (ret < 0) {
1807 free(ebn);
1808 goto out;
1809 }
1810 q = make_message("update data set %s where name = '%s'",
1811 meta, ebn);
1812 if ((ret = update_atts(fd, ebn, atts)) >= 0)
1813 ret = real_query(q);
1814 free(ebn);
1815 free(q);
1816 if (ret < 0)
1817 goto out;
1818 }
1819 ret = 1;
1820 out:
1821 if (name)
1822 free(name);
1823 if (meta)
1824 free(meta);
1825 if (atts)
1826 free(atts);
1827 return ret;
1828 }
1829
1830 /*
1831 * verify / clean
1832 */
1833 static int com_vrfy_clean(int fd, int argc, __a_unused char *argv[])
1834 {
1835 char *query;
1836 int ret, vrfy_mode = strcmp(argv[0], "clean");
1837 void *result = NULL;
1838 unsigned int num_rows;
1839 MYSQL_ROW row;
1840 char *escaped_name;
1841
1842 if (argc != 1)
1843 return -E_MYSQL_SYNTAX;
1844 ret = -E_NORESULT;
1845 result = get_result("select data.name from data left join dir on "
1846 "dir.name = data.name where dir.name is NULL");
1847 if (!result)
1848 goto out;
1849 num_rows = mysql_num_rows(result);
1850 if (!num_rows) {
1851 ret = send_buffer(fd, "No invalid entries\n");
1852 goto out;
1853 }
1854 if (vrfy_mode) {
1855 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
1856 num_rows == 1? "y" : "ies");
1857 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1858 goto out;
1859 }
1860 while ((row = mysql_fetch_row(result))) {
1861 ret = -E_NOROW;
1862 if (!row[0])
1863 goto out;
1864 ret = -E_ESCAPE;
1865 escaped_name = escape_str(row[0]);
1866 if (!escaped_name)
1867 goto out;
1868 send_va_buffer(fd, "deleting %s\n", escaped_name);
1869 query = make_message("delete from data where name = '%s'",
1870 escaped_name);
1871 ret = real_query(query);
1872 free(query);
1873 if (ret < 0)
1874 goto out;
1875 }
1876
1877 out:
1878 if (result)
1879 mysql_free_result(result);
1880 return ret;
1881 }
1882
1883 /*
1884 * verify
1885 */
1886 int com_vrfy(int fd, int argc, char **argv)
1887 {
1888 return com_vrfy_clean(fd, argc, argv);
1889 }
1890
1891 /*
1892 * clean
1893 */
1894 int com_clean(int fd, int argc, char **argv)
1895 {
1896 return com_vrfy_clean(fd, argc, argv);
1897 }
1898
1899 static FILE *out_file;
1900
1901 static int mysql_write_tmp_file(const char *dir, const char *name)
1902 {
1903 int ret = -E_TMPFILE;
1904 char *msg = make_message("%s\t%s\n", dir, name);
1905 if (fputs(msg, out_file) != EOF)
1906 ret = 1;
1907 free(msg);
1908 return ret;
1909 }
1910
1911 /*
1912 * update database
1913 */
1914 int com_upd(int fd, int argc, __a_unused char *argv[])
1915 {
1916 char *tempname = NULL, *query = NULL;
1917 int ret, out_fd = -1, num = 0;
1918 void *result = NULL;
1919 unsigned int num_rows;
1920 MYSQL_ROW row;
1921
1922 if (argc != 1)
1923 return -E_MYSQL_SYNTAX;
1924 out_file = NULL;
1925 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
1926 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
1927 if (ret < 0)
1928 goto out;
1929 out_fd = ret;
1930 out_file = fdopen(out_fd, "w");
1931 if (!out_file) {
1932 close(out_fd);
1933 goto out;
1934 }
1935 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
1936 goto out;
1937 num = ftell(out_file);
1938 /*
1939 * we have to make sure the file hit the disk before we call
1940 * real_query
1941 */
1942 fclose(out_file);
1943 out_file = NULL;
1944 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
1945 if (!num)
1946 goto out;
1947 if ((ret = real_query("delete from dir")) < 0)
1948 goto out;
1949 query = make_message("load data infile '%s' ignore into table dir "
1950 "fields terminated by '\t' lines terminated by '\n' "
1951 "(dir, name)", tempname);
1952 ret = real_query(query);
1953 free(query);
1954 if (ret < 0)
1955 goto out;
1956 result = get_result("select dir.name from dir left join data on "
1957 "data.name = dir.name where data.name is NULL");
1958 ret = -E_NORESULT;
1959 if (!result)
1960 goto out;
1961 num_rows = mysql_num_rows(result);
1962 if (!num_rows) {
1963 ret = send_buffer(fd, "no new entries\n");
1964 goto out;
1965 }
1966 while ((row = mysql_fetch_row(result))) {
1967 char *erow;
1968 ret = -E_NOROW;
1969 if (!row[0])
1970 goto out;
1971 send_va_buffer(fd, "new entry: %s\n", row[0]);
1972 erow = escape_str(row[0]);
1973 if (!erow)
1974 goto out;
1975 query = make_message("insert into data (name, pic_id) values "
1976 "('%s','%s')", erow, "1");
1977 free(erow);
1978 ret = real_query(query);
1979 free(query);
1980 if (ret < 0)
1981 goto out;
1982 }
1983 ret = 1;
1984 out:
1985 if (out_fd >= 0)
1986 unlink(tempname);
1987 free(tempname);
1988 if (out_file)
1989 fclose(out_file);
1990 if (result)
1991 mysql_free_result(result);
1992 return ret;
1993 }
1994
1995 static char **server_get_audio_file_list(unsigned int num)
1996 {
1997 char **list = para_malloc((num + 1) * sizeof(char *));
1998 char *tmp, *query, *stream = get_current_stream();
1999 void *result = NULL;
2000 unsigned int num_rows;
2001 int i = 0;
2002 MYSQL_ROW row;
2003
2004 tmp = get_query(stream, NULL, 1);
2005 free(stream);
2006 if (!tmp)
2007 goto err_out;
2008 query = make_message("%s limit %d", tmp, num);
2009 free(tmp);
2010 result = get_result(query);
2011 free(query);
2012 if (!result)
2013 goto err_out;
2014 num_rows = mysql_num_rows(result);
2015 if (!num_rows)
2016 goto err_out;
2017 for (i = 0; i < num_rows && i < num; i++) {
2018 row = mysql_fetch_row(result);
2019 if (!row || !row[0])
2020 goto err_out;
2021 list[i] = para_strdup(row[0]);
2022 }
2023 list[i] = NULL;
2024 goto success;
2025 err_out:
2026 while (i > 0) {
2027 i--;
2028 free(list[i]);
2029 }
2030 free(list);
2031 list = NULL;
2032 success:
2033 if (result)
2034 mysql_free_result(result);
2035 return list;
2036 }
2037
2038 /*
2039 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2040 * on errors. Called from parent on startup and also from com_cdb().
2041 */
2042 static int init_mysql_server(void)
2043 {
2044 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2045
2046 mysql_ptr = mysql_init(NULL);
2047 if (!mysql_ptr) {
2048 PARA_CRIT_LOG("%s", "mysql init error\n");
2049 return -E_NOTCONN;
2050 }
2051 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2052 conf.mysql_port_arg);
2053 if (!conf.mysql_user_arg)
2054 free(u);
2055 /*
2056 * If host is NULL a connection to the local host is assumed,
2057 * If user is NULL, the current user is assumed
2058 */
2059 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2060 conf.mysql_host_arg,
2061 conf.mysql_user_arg,
2062 conf.mysql_passwd_arg,
2063 conf.mysql_database_arg,
2064 conf.mysql_port_arg, NULL, 0))) {
2065 PARA_CRIT_LOG("%s", "connect error\n");
2066 return -E_NOTCONN;
2067 }
2068 PARA_INFO_LOG("%s", "success\n");
2069 return 1;
2070 }
2071
2072 /* mmd lock must be held */
2073 static void write_msg2mmd(int success)
2074 {
2075 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2076 success < 0? PARA_STRERROR(-success) :
2077 "successfully connected to mysql server",
2078 success < 0? "" : mysql_get_server_info(mysql_ptr));
2079 }
2080
2081 /* create database */
2082 int com_cdb(int fd, int argc, char *argv[])
2083 {
2084 char *query;
2085 int ret;
2086
2087 if (mysql_ptr) {
2088 PARA_INFO_LOG("%s", "closing database\n");
2089 mysql_close(mysql_ptr);
2090 }
2091 /* dont use any database */
2092 conf.mysql_database_arg = NULL; /* leak? */
2093 ret = -E_MYSQL_INIT;
2094 if (init_mysql_server() < 0 || !mysql_ptr)
2095 goto out;
2096 if (argc < 2)
2097 conf.mysql_database_arg = para_strdup("paraslash");
2098 else {
2099 ret = -E_ESCAPE;
2100 conf.mysql_database_arg = escape_str(argv[1]);
2101 if (!conf.mysql_database_arg)
2102 goto out;
2103 }
2104 query = make_message("create database %s", conf.mysql_database_arg);
2105 ret = real_query(query);
2106 free(query);
2107 if (ret < 0)
2108 goto out;
2109 /* reconnect with database just created */
2110 mysql_close(mysql_ptr);
2111 ret = -E_MYSQL_INIT;
2112 if (init_mysql_server() < 0 || !mysql_ptr)
2113 goto out;
2114 mmd_lock();
2115 write_msg2mmd(1);
2116 mmd_unlock();
2117 ret = -E_QFAILED;
2118 if (real_query("create table data (name varchar(255) binary not null "
2119 "primary key, "
2120 "lastplayed datetime not null default "
2121 "'1970-01-01', "
2122 "numplayed int not null default 0, "
2123 "pic_id bigint unsigned not null default 1)") < 0)
2124 goto out;
2125 if (real_query("create table dir (name varchar(255) binary not null "
2126 "primary key, dir varchar(255) default null)") < 0)
2127 goto out;
2128 if (real_query("create table pics ("
2129 "id bigint(20) unsigned not null primary key "
2130 "auto_increment, "
2131 "name varchar(255) binary not null, "
2132 "pic mediumblob not null)") < 0)
2133 goto out;
2134 if (real_query("create table streams ("
2135 "name varchar(255) binary not null primary key, "
2136 "def blob not null)") < 0)
2137 goto out;
2138 if (real_query("insert into streams (name, def) values "
2139 "('current_stream', '(none)')") < 0)
2140 goto out;
2141 ret = send_va_buffer(fd, "successfully created database %s\n",
2142 conf.mysql_database_arg);
2143 out:
2144 return ret;
2145 }
2146
2147 static void shutdown_connection(void)
2148 {
2149 if (mysql_ptr) {
2150 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2151 mysql_close(mysql_ptr);
2152 mysql_ptr = NULL;
2153 }
2154 }
2155
2156 /**
2157 * the init function of the mysql-based audio file selector
2158 *
2159 * Check the command line options and initialize all function pointers of \a db.
2160 * Connect to the mysql server and initialize the info string.
2161 *
2162 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2163 * random_selector.c
2164 */
2165 int mysql_selector_init(struct audio_file_selector *db)
2166 {
2167 int ret;
2168
2169 if (!conf.mysql_passwd_given)
2170 return -E_NO_MYSQL_PASSWD;
2171 if (!conf.mysql_audio_file_dir_given)
2172 return -E_NO_AF_DIR;
2173 db->name = "mysql";
2174 db->cmd_list = cmds;
2175 db->get_audio_file_list = server_get_audio_file_list;
2176 db->update_audio_file = update_audio_file_server_handler;
2177 db->shutdown = shutdown_connection;
2178 ret = init_mysql_server();
2179 if (ret < 0)
2180 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2181 write_msg2mmd(ret);
2182 return 1; /* return success even if connect failed to give the
2183 * user the chance to exec com_cdb
2184 */
2185 }
paraslash - network audio streaming tools