projects / paraslash.git / blob
? search:


0eb19bc58da2f7d78681a793efb746cc0b14bc00
[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, *atts, *info, *dir, *query, *stream;
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 free(dir);
796 free(meta);
797 free(atts);
798 free(stream);
799 if (result)
800 mysql_free_result(result);
801 return info;
802 }
803
804
805 /* might return NULL */
806 static char *get_current_audio_file(void)
807 {
808 char *name;
809 mmd_lock();
810 name = para_basename(mmd->filename);
811 mmd_unlock();
812 return name;
813 }
814
815 /* list attributes / print database info */
816 static int com_la_info(int fd, int argc, char *argv[])
817 {
818 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
819 int ret, com_la = strcmp(argv[0], "info");
820
821 if (argc < 2) {
822 ret = -E_GET_AUDIO_FILE;
823 if (!(name = get_current_audio_file()))
824 goto out;
825 ret = send_va_buffer(fd, "%s\n", name);
826 if (ret < 0)
827 goto out;
828 } else {
829 ret = -E_ESCAPE;
830 if (!(name = escaped_basename(argv[1])))
831 goto out;
832 }
833 meta = get_meta(name, 1);
834 atts = get_atts(name, 0);
835 dir = get_dir(name);
836 if (com_la)
837 ret = send_va_buffer(fd, "%s\n", atts);
838 else
839 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
840 dir? dir : "(not contained in table)", meta, atts);
841 out:
842 free(meta);
843 free(atts);
844 free(dir);
845 free(name);
846 return ret;
847 }
848
849 /* list attributes */
850 int com_la(int fd, int argc, char *argv[])
851 {
852 return com_la_info(fd, argc, argv);
853 }
854
855 /* print database info */
856 int com_info(int fd, int argc, char *argv[])
857 {
858 return com_la_info(fd, argc, argv);
859 }
860
861 static int change_stream(const char *stream)
862 {
863 char *query;
864 int ret;
865 query = make_message("update streams set def='%s' "
866 "where name = 'current_stream'", stream);
867 ret = real_query(query);
868 free(query);
869 return ret;
870 }
871
872 static int get_pic_id_by_name(char *name)
873 {
874 char *q, *ebn;
875 void *result = NULL;
876 long unsigned ret;
877 MYSQL_ROW row;
878
879 if (!(ebn = escaped_basename(name)))
880 return -E_ESCAPE;
881 q = make_message("select pic_id from data where name = '%s'", ebn);
882 free(ebn);
883 result = get_result(q);
884 free(q);
885 if (!result)
886 return -E_NORESULT;
887 row = mysql_fetch_row(result);
888 ret = -E_NOROW;
889 if (row && row[0])
890 ret = atol(row[0]);
891 mysql_free_result(result);
892 return ret;
893 }
894
895 static int remove_entry(const char *name)
896 {
897 char *q, *ebn = escaped_basename(name);
898 int ret = -E_ESCAPE;
899
900 if (!ebn)
901 goto out;
902 q = make_message("delete from data where name = '%s'", ebn);
903 real_query(q); /* ignore errors */
904 free(q);
905 q = make_message("delete from dir where name = '%s'", ebn);
906 real_query(q); /* ignore errors */
907 free(q);
908 ret = 1;
909 out:
910 free(ebn);
911 return ret;
912 }
913
914 static int add_entry(const char *name)
915 {
916 char *q, *dn, *ebn = NULL, *edn = NULL;
917 int ret;
918
919 if (!name || !*name)
920 return -E_MYSQL_SYNTAX;
921 ebn = escaped_basename(name);
922 if (!ebn)
923 return -E_ESCAPE;
924 ret = -E_MYSQL_SYNTAX;
925 dn = para_dirname(name);
926 if (!dn)
927 goto out;
928 ret = -E_ESCAPE;
929 edn = escape_str(dn);
930 free(dn);
931 if (!edn || !*edn)
932 goto out;
933 q = make_message("insert into data (name, pic_id) values "
934 "('%s', '%s')", ebn, "1");
935 ret = real_query(q);
936 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
937 free(q);
938 if (ret < 0)
939 goto out;
940 q = make_message("insert into dir (name, dir) values "
941 "('%s', '%s')", ebn, edn);
942 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
943 ret = real_query(q);
944 free(q);
945 out:
946 if (ebn)
947 free(ebn);
948 if (edn)
949 free(edn);
950 return ret;
951 }
952
953 /*
954 * remove/add entries
955 */
956 static int com_rm_ne(__a_unused int fd, int argc, char *argv[])
957 {
958 int ne = !strcmp(argv[0], "ne");
959 int i, ret;
960 if (argc < 2)
961 return -E_MYSQL_SYNTAX;
962 for (i = 1; i < argc; i++) {
963 ret = remove_entry(argv[i]);
964 if (ret < 0)
965 return ret;
966 if (!ne)
967 continue;
968 ret = add_entry(argv[i]);
969 if (ret < 0)
970 return ret;
971 }
972 return 1;
973 }
974
975 /*
976 * rm
977 */
978 int com_rm(int fd, int argc, char *argv[])
979 {
980 return com_rm_ne(fd, argc, argv);
981 }
982
983 /*
984 * ne
985 */
986 int com_ne(int fd, int argc, char *argv[])
987 {
988 return com_ne(fd, argc, argv);
989 }
990
991 /*
992 * mv: rename entry
993 */
994 int com_mv(__a_unused int fd, int argc, char *argv[])
995 {
996 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
997 int ret;
998
999 if (argc != 3)
1000 return -E_MYSQL_SYNTAX;
1001 ret = -E_ESCAPE;
1002 ebn1 = escaped_basename(argv[1]);
1003 ebn2 = escaped_basename(argv[2]);
1004 if (!ebn1 || !ebn2 || !*ebn1 || !*ebn2)
1005 goto out;
1006 ret = -E_MYSQL_SYNTAX;
1007 if (!strcmp(ebn1, ebn2))
1008 goto update_dir;
1009 remove_entry(argv[2]); /* no need to escape, ignore error */
1010 q = make_message("update data set name = '%s' where name = '%s'",
1011 ebn2, ebn1);
1012 ret = real_query(q);
1013 free(q);
1014 if (ret < 0)
1015 goto out;
1016 ret = -E_AUDIO_FILE;
1017 if (!mysql_affected_rows(mysql_ptr))
1018 goto out;
1019 q = make_message("update dir set name = '%s' where name = '%s'",
1020 ebn2, ebn1);
1021 ret = real_query(q);
1022 free(q);
1023 if (ret < 0)
1024 goto out;
1025 update_dir:
1026 ret = 1;
1027 dn = para_dirname(argv[2]);
1028 if (!dn)
1029 goto out;
1030 ret = -E_ESCAPE;
1031 edn = escape_str(dn);
1032 free(dn);
1033 if (!edn)
1034 goto out;
1035 ret = 1;
1036 if (!*edn)
1037 goto out;
1038 q = make_message("update dir set dir = '%s' where name = '%s'",
1039 edn, ebn2);
1040 ret = real_query(q);
1041 free(q);
1042 out:
1043 free(edn);
1044 free(ebn1);
1045 free(ebn2);
1046 return ret;
1047 }
1048
1049 /*
1050 * set field
1051 */
1052 static int com_set(__a_unused int fd, int argc, char *argv[])
1053 {
1054 char *q, *ebn;
1055 long unsigned id;
1056 int i, ret;
1057 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1058
1059 if (argc < 3)
1060 return -E_MYSQL_SYNTAX;
1061 id = atol(argv[1]);
1062 for (i = 2; i < argc; i++) {
1063 ebn = escaped_basename(argv[i]);
1064 if (!ebn)
1065 return -E_ESCAPE;
1066 q = make_message("update data set %s = %lu "
1067 "where name = '%s'", field, id, ebn);
1068 free(ebn);
1069 ret = real_query(q);
1070 free(q);
1071 if (ret < 0)
1072 return ret;
1073 }
1074 return 1;
1075 }
1076
1077 /*
1078 * snp: set numplayed
1079 */
1080 int com_picass(int fd, int argc, char *argv[])
1081 {
1082 return com_set(fd, argc, argv);
1083 }
1084
1085 /*
1086 * snp: set numplayed
1087 */
1088 int com_snp(int fd, int argc, char *argv[])
1089 {
1090 return com_set(fd, argc, argv);
1091 }
1092
1093 /*
1094 * picch: change entry's name in pics table
1095 */
1096 int com_picch(__a_unused int fd, int argc, char *argv[])
1097 {
1098 int ret;
1099 long unsigned id;
1100 char *q, *tmp;
1101
1102 if (argc != 3)
1103 return -E_MYSQL_SYNTAX;
1104 id = atol(argv[1]);
1105 ret = -E_ESCAPE;
1106 tmp = escape_str(argv[2]);
1107 if (!tmp)
1108 return -E_ESCAPE;
1109 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1110 free(tmp);
1111 ret = real_query(q);
1112 free(q);
1113 return ret;
1114 }
1115
1116 /*
1117 * piclist: print list of pics in db
1118 */
1119 int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1120 {
1121 void *result = NULL;
1122 MYSQL_ROW row;
1123 unsigned long *length;
1124 int ret;
1125
1126 if (argc != 1)
1127 return -E_MYSQL_SYNTAX;
1128 result = get_result("select id,name,pic from pics order by id");
1129 if (!result)
1130 return -E_NORESULT;
1131 while ((row = mysql_fetch_row(result))) {
1132 length = mysql_fetch_lengths(result);
1133 if (!row || !row[0] || !row[1] || !row[2])
1134 continue;
1135 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1136 if (ret < 0)
1137 goto out;
1138 }
1139 ret = 1;
1140 out:
1141 mysql_free_result(result);
1142 return ret;
1143 }
1144
1145 /*
1146 * picdel: delete picture from database
1147 */
1148 int com_picdel(int fd, int argc, char *argv[])
1149 {
1150 char *q;
1151 long unsigned id;
1152 my_ulonglong aff;
1153 int i, ret;
1154
1155 if (argc < 2)
1156 return -E_MYSQL_SYNTAX;
1157 for (i = 1; i < argc; i++) {
1158 id = atol(argv[i]);
1159 q = make_message("delete from pics where id = %lu", id);
1160 ret = real_query(q);
1161 free(q);
1162 if (ret < 0)
1163 return ret;
1164 aff = mysql_affected_rows(mysql_ptr);
1165 if (!aff) {
1166 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1167 if (ret < 0)
1168 return ret;
1169 continue;
1170 }
1171 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1172 ret = real_query(q);
1173 free(q);
1174 }
1175 return 1;
1176 }
1177 /*
1178 * pic: get picture by name or by number
1179 */
1180 int com_pic(int fd, int argc, char *argv[])
1181 {
1182 void *result = NULL;
1183 MYSQL_ROW row;
1184 unsigned long *length, id;
1185 int ret;
1186 char *q, *name = NULL;
1187
1188 if (argc < 2) {
1189 ret = -E_GET_AUDIO_FILE;
1190 name = get_current_audio_file();
1191 } else {
1192 ret = -E_ESCAPE;
1193 name = escaped_basename(argv[1]);
1194 }
1195 if (!name)
1196 return ret;
1197 if (*name == '#')
1198 id = atoi(name + 1);
1199 else
1200 id = get_pic_id_by_name(name);
1201 free(name);
1202 if (id <= 0)
1203 return id;
1204 q = make_message("select pic from pics where id = '%lu'", id);
1205 result = get_result(q);
1206 free(q);
1207 if (!result)
1208 return -E_NORESULT;
1209 row = mysql_fetch_row(result);
1210 ret = -E_NOROW;
1211 if (!row || !row[0])
1212 goto out;
1213 length = mysql_fetch_lengths(result);
1214 ret = send_bin_buffer(fd, row[0], *length);
1215 out:
1216 mysql_free_result(result);
1217 return ret;
1218 }
1219
1220 /* strdel */
1221 int com_strdel(__a_unused int fd, int argc, char *argv[])
1222 {
1223 char *q, *tmp;
1224 int ret;
1225
1226 if (argc < 2)
1227 return -E_MYSQL_SYNTAX;
1228 tmp = escape_str(argv[1]);
1229 if (!tmp)
1230 return -E_ESCAPE;
1231 q = make_message("delete from streams where name='%s'", tmp);
1232 free(tmp);
1233 ret = real_query(q);
1234 free(q);
1235 return ret;
1236 }
1237
1238 /*
1239 * ls
1240 */
1241 int com_ls(int fd, int argc, char *argv[])
1242 {
1243 char *q;
1244 void *result;
1245 int ret;
1246 unsigned int num_rows;
1247
1248 if (argc > 2)
1249 return -E_MYSQL_SYNTAX;
1250 if (argc > 1) {
1251 char *tmp = escape_str(argv[1]);
1252 if (!tmp)
1253 return -E_ESCAPE;
1254 q = make_message("select name from data where name like '%s'",
1255 tmp);
1256 free(tmp);
1257 } else
1258 q = para_strdup("select name from data");
1259 result = get_result(q);
1260 free(q);
1261 if (!result)
1262 return -E_NORESULT;
1263 num_rows = mysql_num_rows(result);
1264 ret = 1;
1265 if (num_rows)
1266 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1267 mysql_free_result(result);
1268 return ret;
1269 }
1270
1271 /*
1272 * summary
1273 */
1274 int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1275 {
1276 MYSQL_ROW row;
1277 MYSQL_ROW row2;
1278 void *result;
1279 void *result2 = NULL;
1280 const char *fmt = "select count(name) from data where %s='1'";
1281 int ret = -E_NORESULT;
1282
1283 if (argc != 1)
1284 return -E_MYSQL_SYNTAX;
1285 result = get_all_attributes();
1286 if (!result)
1287 goto out;
1288 while ((row = mysql_fetch_row(result))) {
1289 char *buf;
1290
1291 ret = -E_NOROW;
1292 if (!row[0])
1293 goto out;
1294 ret = -E_NORESULT;
1295 buf = make_message(fmt, row[0]);
1296 result2 = get_result(buf);
1297 free(buf);
1298 if (!result2)
1299 goto out;
1300 ret = -E_NOROW;
1301 row2 = mysql_fetch_row(result2);
1302 if (!row2 || !row2[0])
1303 goto out;
1304 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1305 if (ret < 0)
1306 goto out;
1307 }
1308 ret = 1;
1309 out:
1310 if (result2)
1311 mysql_free_result(result2);
1312 if (result)
1313 mysql_free_result(result);
1314 return ret;
1315 }
1316
1317 static int get_numplayed(char *name)
1318 {
1319 void *result;
1320 MYSQL_ROW row;
1321 const char *fmt = "select numplayed from data where name = '%s'";
1322 char *buf = make_message(fmt, name);
1323 int ret = -E_NORESULT;
1324
1325 result = get_result(buf);
1326 free(buf);
1327 if (!result)
1328 goto out;
1329 ret = -E_NOROW;
1330 row = mysql_fetch_row(result);
1331 if (!row || !row[0])
1332 goto out;
1333 ret = atoi(row[0]);
1334 out:
1335 if (result)
1336 mysql_free_result(result);
1337 return ret;
1338 }
1339
1340 static int update_audio_file(char *name)
1341 {
1342 int ret;
1343 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1344 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1345 char *q;
1346 char *ebn = escaped_basename(name);
1347
1348 ret = -E_ESCAPE;
1349 if (!ebn)
1350 goto out;
1351 q = make_message(fmt1, ebn);
1352 ret = real_query(q);
1353 free(q);
1354 if (ret < 0)
1355 goto out;
1356 ret = get_numplayed(ebn);
1357 if (ret < 0)
1358 goto out;
1359 q = make_message(fmt2, ret + 1, ebn);
1360 ret = real_query(q);
1361 free(q);
1362 out:
1363 free(ebn);
1364 return ret;
1365 }
1366
1367 /* If called as child, mmd_lock must be held */
1368 static void update_mmd(char *info)
1369 {
1370 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1371 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1372 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1373 }
1374
1375 static void update_audio_file_server_handler(char *name)
1376 {
1377 char *info;
1378 info = get_selector_info(name);
1379 update_mmd(info);
1380 free(info);
1381 update_audio_file(name);
1382 }
1383
1384 int com_us(__a_unused int fd, int argc, char *argv[])
1385 {
1386 char *tmp;
1387 int ret;
1388
1389 if (argc != 2)
1390 return -E_MYSQL_SYNTAX;
1391 tmp = escape_str(argv[1]);
1392 if (!tmp)
1393 return -E_ESCAPE;
1394 ret = update_audio_file(argv[1]);
1395 free(tmp);
1396 return ret;
1397 }
1398
1399 static void refresh_selector_info(void)
1400 {
1401 char *name = get_current_audio_file();
1402 char *info;
1403
1404 if (!name)
1405 return;
1406 info = get_selector_info(name);
1407 free(name);
1408 mmd_lock();
1409 update_mmd(info);
1410 mmd_unlock();
1411 free(info);
1412 }
1413
1414 /* select previous / next stream */
1415 static int com_ps_ns(__a_unused int fd, int argc, char *argv[])
1416 {
1417 char *query, *stream = get_current_stream();
1418 void *result = get_result("select name from streams");
1419 MYSQL_ROW row;
1420 int match = -1, ret, i;
1421 unsigned int num_rows;
1422
1423 if (argc != 1)
1424 return -E_MYSQL_SYNTAX;
1425 ret = -E_NORESULT;
1426 if (!result)
1427 goto out;
1428 num_rows = mysql_num_rows(result);
1429 ret = -E_EMPTY_RESULT;
1430 if (num_rows < 2)
1431 goto out;
1432 ret = -E_NOROW;
1433 for (i = 0; i < num_rows; i++) {
1434 row = mysql_fetch_row(result);
1435 if (!row || !row[0])
1436 goto out;
1437 if (!strcmp(row[0], "current_stream"))
1438 continue;
1439 if (!strcmp(row[0], stream)) {
1440 match = i;
1441 break;
1442 }
1443 }
1444 ret = -E_NO_STREAM;
1445 if (match < 0)
1446 goto out;
1447 if (!strcmp(argv[0], "ps"))
1448 i = match > 0? match - 1 : num_rows - 1;
1449 else
1450 i = match < num_rows - 1? match + 1 : 0;
1451 ret = -E_NOROW;
1452 mysql_data_seek(result, i);
1453 row = mysql_fetch_row(result);
1454 if (!row || !row[0])
1455 goto out;
1456 if (!strcmp(row[0], "current_stream")) {
1457 if (!strcmp(argv[0], "ps")) {
1458 i = match - 2;
1459 i = i < 0? i + num_rows : i;
1460 } else {
1461 i = match + 2;
1462 i = i > num_rows - 1? i - num_rows : i;
1463 }
1464 mysql_data_seek(result, i);
1465 row = mysql_fetch_row(result);
1466 if (!row || !row[0])
1467 goto out;
1468 }
1469 query = make_message("update streams set def='%s' where name = "
1470 "'current_stream'", row[0]);
1471 ret = real_query(query);
1472 free(query);
1473 refresh_selector_info();
1474 out:
1475 free(stream);
1476 if (result)
1477 mysql_free_result(result);
1478 return ret;
1479 }
1480
1481 /* select previous stream */
1482 int com_ps(int fd, int argc, char *argv[])
1483 {
1484 return com_ps_ns(fd, argc, argv);
1485 }
1486
1487 /* select next stream */
1488 int com_ns(int fd, int argc, char *argv[])
1489 {
1490 return com_ps_ns(fd, argc, argv);
1491 }
1492
1493 /* streams */
1494 int com_streams(int fd, int argc, __a_unused char *argv[])
1495 {
1496 unsigned int num_rows;
1497 int i, ret = -E_NORESULT;
1498 void *result;
1499 MYSQL_ROW row;
1500
1501 if (argc > 1 && strcmp(argv[1], "current_stream"))
1502 return -E_MYSQL_SYNTAX;
1503 if (argc > 1) {
1504 char *cs = get_current_stream();
1505 ret = send_va_buffer(fd, "%s\n", cs);
1506 free(cs);
1507 return ret;
1508 }
1509 result = get_result("select name from streams");
1510 if (!result)
1511 goto out;
1512 num_rows = mysql_num_rows(result);
1513 ret = 1;
1514 if (!num_rows)
1515 goto out;
1516 ret = -E_NOROW;
1517 for (i = 0; i < num_rows; i++) {
1518 row = mysql_fetch_row(result);
1519 if (!row || !row[0])
1520 goto out;
1521 if (strcmp(row[0], "current_stream"))
1522 send_va_buffer(fd, "%s\n", row[0]);
1523 }
1524 ret = 1;
1525 out:
1526 if (result)
1527 mysql_free_result(result);
1528 return ret;
1529 }
1530
1531 /* query stream definition */
1532 int com_strq(int fd, int argc, char *argv[])
1533 {
1534 MYSQL_ROW row;
1535 char *query, *name;
1536 void *result;
1537 int ret;
1538
1539 if (argc < 2) {
1540 ret = -E_GET_STREAM;
1541 name = get_current_stream();
1542 } else {
1543 ret = -E_ESCAPE;
1544 name = escaped_basename(argv[1]);
1545 }
1546 if (!name)
1547 return ret;
1548 ret = -E_NORESULT;
1549 query = make_message("select def from streams where name='%s'", name);
1550 free(name);
1551 result = get_result(query);
1552 free(query);
1553 if (!result)
1554 goto out;
1555 ret = -E_NOROW;
1556 row = mysql_fetch_row(result);
1557 if (!row || !row[0])
1558 goto out;
1559 /* no '\n' needed */
1560 ret = send_buffer(fd, row[0]);
1561 out:
1562 if (result)
1563 mysql_free_result(result);
1564 return ret;
1565 }
1566
1567 /* change stream / change stream and play */
1568 static int com_cs_csp(int fd, int argc, char *argv[])
1569 {
1570 int ret, stream_change;
1571 char *query, *stream = NULL;
1572 char *old_stream = get_current_stream();
1573 int csp = !strcmp(argv[0], "csp");
1574
1575 ret = -E_MYSQL_SYNTAX;
1576 if (argc > 2)
1577 goto out;
1578 if (argc == 1) {
1579 if (csp)
1580 goto out;
1581 ret = send_va_buffer(fd, "%s\n", old_stream);
1582 goto out;
1583 }
1584 ret = -E_GET_QUERY;
1585 /* test if stream is valid, no need to escape argv[1] */
1586 query = get_query(argv[1], NULL, 0);
1587 if (!query)
1588 goto out;
1589 free(query);
1590 /* stream is ok */
1591 stream = escape_str(argv[1]);
1592 if (!stream)
1593 goto out;
1594 stream_change = strcmp(stream, old_stream);
1595 if (stream_change) {
1596 ret = change_stream(stream);
1597 if (ret < 0)
1598 goto out;
1599 refresh_selector_info();
1600 }
1601 if (csp) {
1602 mmd_lock();
1603 mmd->new_vss_status_flags |= VSS_PLAYING;
1604 if (stream_change)
1605 mmd->new_vss_status_flags |= VSS_NEXT;
1606 mmd_unlock();
1607 }
1608 ret = 1;
1609 out:
1610 free(old_stream);
1611 free(stream);
1612 return ret;
1613 }
1614
1615 /* change stream */
1616 int com_cs(int fd, int argc, char *argv[])
1617 {
1618 return com_cs_csp(fd, argc, argv);
1619 }
1620
1621 /* change stream and play */
1622 int com_csp(int fd, int argc, char *argv[])
1623 {
1624 return com_cs_csp(fd, argc, argv);
1625 }
1626
1627 /* score list / skip */
1628 static int com_sl_skip(int fd, int argc, char *argv[])
1629 {
1630 void *result = NULL;
1631 MYSQL_ROW row;
1632 int ret, i, skip = !strcmp(argv[0], "skip");
1633 char *query, *stream, *tmp;
1634 unsigned int num_rows, num;
1635
1636 if (argc < 2)
1637 return -E_MYSQL_SYNTAX;
1638 num = atoi(argv[1]);
1639 if (!num)
1640 return -E_MYSQL_SYNTAX;
1641 if (argc == 2) {
1642 stream = get_current_stream();
1643 if (!stream)
1644 return -E_GET_STREAM;
1645 } else {
1646 stream = escape_str(argv[2]);
1647 if (!stream)
1648 return -E_ESCAPE;
1649 }
1650 tmp = get_query(stream, NULL, 0);
1651 free(stream);
1652 if (!tmp)
1653 return -E_GET_QUERY;
1654 query = make_message("%s limit %d", tmp, num);
1655 free(tmp);
1656 ret = -E_NORESULT;
1657 result = get_result(query);
1658 free(query);
1659 if (!result)
1660 goto out;
1661 ret = -E_EMPTY_RESULT;
1662 num_rows = mysql_num_rows(result);
1663 if (!num_rows)
1664 goto out;
1665 for (i = 0; i < num_rows && i < num; i++) {
1666 row = mysql_fetch_row(result);
1667 if (skip) {
1668 send_va_buffer(fd, "Skipping %s\n", row[0]);
1669 update_audio_file(row[0]);
1670 } else
1671 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
1672 }
1673 ret = 1;
1674 out:
1675 if (result)
1676 mysql_free_result(result);
1677 return ret;
1678 }
1679
1680 /* score list */
1681 int com_sl(int fd, int argc, char *argv[])
1682 {
1683 return com_sl_skip(fd, argc, argv);
1684 }
1685
1686 /* skip */
1687 int com_skip(int fd, int argc, char *argv[])
1688 {
1689 return com_sl_skip(fd, argc, argv);
1690 }
1691
1692 /*
1693 * update attributes of name
1694 */
1695 static int update_atts(int fd, char *name, char *atts)
1696 {
1697 int ret;
1698 char *ebn, *q, *old, *new = NULL;
1699
1700 if (!mysql_ptr)
1701 return -E_NOTCONN;
1702 ebn = escaped_basename(name);
1703 if (!ebn)
1704 return -E_ESCAPE;
1705 q = make_message("update data set %s where name = '%s'", atts, ebn);
1706 old = get_atts(ebn, 0);
1707 send_va_buffer(fd, "old: %s\n", old);
1708 free(old);
1709 ret = real_query(q);
1710 free(q);
1711 if (ret < 0)
1712 goto out;
1713 new = get_atts(ebn, 0);
1714 ret = send_va_buffer(fd, "new: %s\n", new);
1715 free(new);
1716 out:
1717 free(ebn);
1718 return ret;
1719 }
1720
1721 /*
1722 * set attributes
1723 */
1724 int com_sa(int fd, int argc, char *argv[])
1725 {
1726 int i, ret;
1727 char *atts = NULL, *name;
1728
1729 if (argc < 2)
1730 return -E_MYSQL_SYNTAX;
1731 for (i = 1; i < argc; i++) {
1732 int unset = 0;
1733 char *esc, *tmp, *p =argv[i];
1734 int len = strlen(p);
1735
1736 if (!len)
1737 continue;
1738 switch (p[len - 1]) {
1739 case '+':
1740 unset = 0;
1741 break;
1742 case '-':
1743 unset = 1;
1744 break;
1745 default:
1746 goto no_more_atts;
1747 }
1748 p[len - 1] = '\0';
1749 esc = escape_str(p);
1750 if (!esc)
1751 return -E_ESCAPE;
1752 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
1753 unset? "0" : "1");
1754 free(esc);
1755 atts = para_strcat(atts, tmp);
1756 free(tmp);
1757 }
1758 no_more_atts:
1759 if (!atts)
1760 return -E_NOATTS;
1761 if (i >= argc) { /* no name given, use current af */
1762 ret = -E_GET_AUDIO_FILE;
1763 if (!(name = get_current_audio_file()))
1764 goto out;
1765 ret = update_atts(fd, name, atts);
1766 free(name);
1767 } else {
1768 ret = 1;
1769 for (; argv[i] && ret >= 0; i++)
1770 ret = update_atts(fd, argv[i], atts);
1771 }
1772 refresh_selector_info();
1773 out:
1774 free(atts);
1775 return ret;
1776 }
1777
1778 /*
1779 * copy attributes
1780 */
1781 int com_cam(int fd, int argc, char *argv[])
1782 {
1783 char *name = NULL, *meta = NULL, *atts = NULL;
1784 int i, ret;
1785
1786 if (argc < 3)
1787 return -E_MYSQL_SYNTAX;
1788 if (!(name = escaped_basename(argv[1])))
1789 return -E_ESCAPE;
1790 ret = -E_NOATTS;
1791 if (!(atts = get_atts(name, 1)))
1792 goto out;
1793 ret = -E_META;
1794 if (!(meta = get_meta(name, 0)))
1795 goto out;
1796 for (i = 2; i < argc; i++) {
1797 char *ebn, *q;
1798 ret = -E_ESCAPE;
1799 if (!(ebn = escaped_basename(argv[i])))
1800 goto out;
1801 ret = send_va_buffer(fd, "updating %s\n", ebn);
1802 if (ret < 0) {
1803 free(ebn);
1804 goto out;
1805 }
1806 q = make_message("update data set %s where name = '%s'",
1807 meta, ebn);
1808 if ((ret = update_atts(fd, ebn, atts)) >= 0)
1809 ret = real_query(q);
1810 free(ebn);
1811 free(q);
1812 if (ret < 0)
1813 goto out;
1814 }
1815 ret = 1;
1816 out:
1817 if (name)
1818 free(name);
1819 if (meta)
1820 free(meta);
1821 if (atts)
1822 free(atts);
1823 return ret;
1824 }
1825
1826 /*
1827 * verify / clean
1828 */
1829 static int com_vrfy_clean(int fd, int argc, __a_unused char *argv[])
1830 {
1831 char *query;
1832 int ret, vrfy_mode = strcmp(argv[0], "clean");
1833 void *result = NULL;
1834 unsigned int num_rows;
1835 MYSQL_ROW row;
1836 char *escaped_name;
1837
1838 if (argc != 1)
1839 return -E_MYSQL_SYNTAX;
1840 ret = -E_NORESULT;
1841 result = get_result("select data.name from data left join dir on "
1842 "dir.name = data.name where dir.name is NULL");
1843 if (!result)
1844 goto out;
1845 num_rows = mysql_num_rows(result);
1846 if (!num_rows) {
1847 ret = send_buffer(fd, "No invalid entries\n");
1848 goto out;
1849 }
1850 if (vrfy_mode) {
1851 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
1852 num_rows == 1? "y" : "ies");
1853 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1854 goto out;
1855 }
1856 while ((row = mysql_fetch_row(result))) {
1857 ret = -E_NOROW;
1858 if (!row[0])
1859 goto out;
1860 ret = -E_ESCAPE;
1861 escaped_name = escape_str(row[0]);
1862 if (!escaped_name)
1863 goto out;
1864 send_va_buffer(fd, "deleting %s\n", escaped_name);
1865 query = make_message("delete from data where name = '%s'",
1866 escaped_name);
1867 ret = real_query(query);
1868 free(query);
1869 if (ret < 0)
1870 goto out;
1871 }
1872
1873 out:
1874 if (result)
1875 mysql_free_result(result);
1876 return ret;
1877 }
1878
1879 /*
1880 * verify
1881 */
1882 int com_vrfy(int fd, int argc, char **argv)
1883 {
1884 return com_vrfy_clean(fd, argc, argv);
1885 }
1886
1887 /*
1888 * clean
1889 */
1890 int com_clean(int fd, int argc, char **argv)
1891 {
1892 return com_vrfy_clean(fd, argc, argv);
1893 }
1894
1895 static FILE *out_file;
1896
1897 static int mysql_write_tmp_file(const char *dir, const char *name)
1898 {
1899 int ret = -E_TMPFILE;
1900 char *msg = make_message("%s\t%s\n", dir, name);
1901 if (fputs(msg, out_file) != EOF)
1902 ret = 1;
1903 free(msg);
1904 return ret;
1905 }
1906
1907 /*
1908 * update database
1909 */
1910 int com_upd(int fd, int argc, __a_unused char *argv[])
1911 {
1912 char *tempname = NULL, *query = NULL;
1913 int ret, out_fd = -1, num = 0;
1914 void *result = NULL;
1915 unsigned int num_rows;
1916 MYSQL_ROW row;
1917
1918 if (argc != 1)
1919 return -E_MYSQL_SYNTAX;
1920 out_file = NULL;
1921 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
1922 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
1923 if (ret < 0)
1924 goto out;
1925 out_fd = ret;
1926 out_file = fdopen(out_fd, "w");
1927 if (!out_file) {
1928 close(out_fd);
1929 goto out;
1930 }
1931 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
1932 goto out;
1933 num = ftell(out_file);
1934 /*
1935 * we have to make sure the file hit the disk before we call
1936 * real_query
1937 */
1938 fclose(out_file);
1939 out_file = NULL;
1940 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
1941 if (!num)
1942 goto out;
1943 if ((ret = real_query("delete from dir")) < 0)
1944 goto out;
1945 query = make_message("load data infile '%s' ignore into table dir "
1946 "fields terminated by '\t' lines terminated by '\n' "
1947 "(dir, name)", tempname);
1948 ret = real_query(query);
1949 free(query);
1950 if (ret < 0)
1951 goto out;
1952 result = get_result("select dir.name from dir left join data on "
1953 "data.name = dir.name where data.name is NULL");
1954 ret = -E_NORESULT;
1955 if (!result)
1956 goto out;
1957 num_rows = mysql_num_rows(result);
1958 if (!num_rows) {
1959 ret = send_buffer(fd, "no new entries\n");
1960 goto out;
1961 }
1962 while ((row = mysql_fetch_row(result))) {
1963 char *erow;
1964 ret = -E_NOROW;
1965 if (!row[0])
1966 goto out;
1967 send_va_buffer(fd, "new entry: %s\n", row[0]);
1968 erow = escape_str(row[0]);
1969 if (!erow)
1970 goto out;
1971 query = make_message("insert into data (name, pic_id) values "
1972 "('%s','%s')", erow, "1");
1973 free(erow);
1974 ret = real_query(query);
1975 free(query);
1976 if (ret < 0)
1977 goto out;
1978 }
1979 ret = 1;
1980 out:
1981 if (out_fd >= 0)
1982 unlink(tempname);
1983 free(tempname);
1984 if (out_file)
1985 fclose(out_file);
1986 if (result)
1987 mysql_free_result(result);
1988 return ret;
1989 }
1990
1991 static char **server_get_audio_file_list(unsigned int num)
1992 {
1993 char **list = para_malloc((num + 1) * sizeof(char *));
1994 char *tmp, *query, *stream = get_current_stream();
1995 void *result = NULL;
1996 unsigned int num_rows;
1997 int i = 0;
1998 MYSQL_ROW row;
1999
2000 tmp = get_query(stream, NULL, 1);
2001 free(stream);
2002 if (!tmp)
2003 goto err_out;
2004 query = make_message("%s limit %d", tmp, num);
2005 free(tmp);
2006 result = get_result(query);
2007 free(query);
2008 if (!result)
2009 goto err_out;
2010 num_rows = mysql_num_rows(result);
2011 if (!num_rows)
2012 goto err_out;
2013 for (i = 0; i < num_rows && i < num; i++) {
2014 row = mysql_fetch_row(result);
2015 if (!row || !row[0])
2016 goto err_out;
2017 list[i] = para_strdup(row[0]);
2018 }
2019 list[i] = NULL;
2020 goto success;
2021 err_out:
2022 while (i > 0) {
2023 i--;
2024 free(list[i]);
2025 }
2026 free(list);
2027 list = NULL;
2028 success:
2029 if (result)
2030 mysql_free_result(result);
2031 return list;
2032 }
2033
2034 /*
2035 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2036 * on errors. Called from parent on startup and also from com_cdb().
2037 */
2038 static int init_mysql_server(void)
2039 {
2040 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2041
2042 mysql_ptr = mysql_init(NULL);
2043 if (!mysql_ptr) {
2044 PARA_CRIT_LOG("%s", "mysql init error\n");
2045 return -E_NOTCONN;
2046 }
2047 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2048 conf.mysql_port_arg);
2049 if (!conf.mysql_user_arg)
2050 free(u);
2051 /*
2052 * If host is NULL a connection to the local host is assumed,
2053 * If user is NULL, the current user is assumed
2054 */
2055 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2056 conf.mysql_host_arg,
2057 conf.mysql_user_arg,
2058 conf.mysql_passwd_arg,
2059 conf.mysql_database_arg,
2060 conf.mysql_port_arg, NULL, 0))) {
2061 PARA_CRIT_LOG("%s", "connect error\n");
2062 return -E_NOTCONN;
2063 }
2064 PARA_INFO_LOG("%s", "success\n");
2065 return 1;
2066 }
2067
2068 /* mmd lock must be held */
2069 static void write_msg2mmd(int success)
2070 {
2071 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2072 success < 0? PARA_STRERROR(-success) :
2073 "successfully connected to mysql server",
2074 success < 0? "" : mysql_get_server_info(mysql_ptr));
2075 }
2076
2077 /* create database */
2078 int com_cdb(int fd, int argc, char *argv[])
2079 {
2080 char *query;
2081 int ret;
2082
2083 if (mysql_ptr) {
2084 PARA_INFO_LOG("%s", "closing database\n");
2085 mysql_close(mysql_ptr);
2086 }
2087 /* dont use any database */
2088 conf.mysql_database_arg = NULL; /* leak? */
2089 ret = -E_MYSQL_INIT;
2090 if (init_mysql_server() < 0 || !mysql_ptr)
2091 goto out;
2092 if (argc < 2)
2093 conf.mysql_database_arg = para_strdup("paraslash");
2094 else {
2095 ret = -E_ESCAPE;
2096 conf.mysql_database_arg = escape_str(argv[1]);
2097 if (!conf.mysql_database_arg)
2098 goto out;
2099 }
2100 query = make_message("create database %s", conf.mysql_database_arg);
2101 ret = real_query(query);
2102 free(query);
2103 if (ret < 0)
2104 goto out;
2105 /* reconnect with database just created */
2106 mysql_close(mysql_ptr);
2107 ret = -E_MYSQL_INIT;
2108 if (init_mysql_server() < 0 || !mysql_ptr)
2109 goto out;
2110 mmd_lock();
2111 write_msg2mmd(1);
2112 mmd_unlock();
2113 ret = -E_QFAILED;
2114 if (real_query("create table data (name varchar(255) binary not null "
2115 "primary key, "
2116 "lastplayed datetime not null default "
2117 "'1970-01-01', "
2118 "numplayed int not null default 0, "
2119 "pic_id bigint unsigned not null default 1)") < 0)
2120 goto out;
2121 if (real_query("create table dir (name varchar(255) binary not null "
2122 "primary key, dir varchar(255) default null)") < 0)
2123 goto out;
2124 if (real_query("create table pics ("
2125 "id bigint(20) unsigned not null primary key "
2126 "auto_increment, "
2127 "name varchar(255) binary not null, "
2128 "pic mediumblob not null)") < 0)
2129 goto out;
2130 if (real_query("create table streams ("
2131 "name varchar(255) binary not null primary key, "
2132 "def blob not null)") < 0)
2133 goto out;
2134 if (real_query("insert into streams (name, def) values "
2135 "('current_stream', '(none)')") < 0)
2136 goto out;
2137 ret = send_va_buffer(fd, "successfully created database %s\n",
2138 conf.mysql_database_arg);
2139 out:
2140 return ret;
2141 }
2142
2143 static void shutdown_connection(void)
2144 {
2145 if (mysql_ptr) {
2146 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2147 mysql_close(mysql_ptr);
2148 mysql_ptr = NULL;
2149 }
2150 }
2151
2152 /**
2153 * the init function of the mysql-based audio file selector
2154 *
2155 * \param db pointer to the struct to initialize
2156 *
2157 * Check the command line options and initialize all function pointers of \a
2158 * db. Connect to the mysql server and initialize the info string.
2159 *
2160 * \return This function returns success even if it could not connect
2161 * to the mysql server. This is because the connect is expected to fail
2162 * if there the paraslash database is not yet created. This gives the
2163 * user a chance to send the "cdb" to create the database.
2164 *
2165 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2166 * random_selector.c
2167 */
2168 int mysql_selector_init(struct audio_file_selector *db)
2169 {
2170 int ret;
2171
2172 if (!conf.mysql_passwd_given)
2173 return -E_NO_MYSQL_PASSWD;
2174 if (!conf.mysql_audio_file_dir_given)
2175 return -E_NO_AF_DIR;
2176 db->name = "mysql";
2177 db->cmd_list = mysql_selector_cmds;
2178 db->get_audio_file_list = server_get_audio_file_list;
2179 db->update_audio_file = update_audio_file_server_handler;
2180 db->shutdown = shutdown_connection;
2181 ret = init_mysql_server();
2182 if (ret < 0)
2183 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2184 write_msg2mmd(ret);
2185 return 1; /* return success even if connect failed to give the
2186 * user the chance to exec com_cdb
2187 */
2188 }
paraslash - network audio streaming tools