2 * Copyright (C) 1999-2007 Andre Noll <maan@systemlinux.org>
4 * Licensed under the GPL v2. For licencing details see COPYING.
7 /** \file mysql_selector.c para_server's mysql-based audio file selector */
9 /** \cond some internal constants */
10 #define MEDIUM_BLOB_SIZE 16777220 /* (2**24 + 4) */
11 #define BLOB_SIZE 65539 /* (2**16 + 3) */
13 #include "server.cmdline.h"
17 #include <mysql/mysql.h>
18 #include <mysql/mysql_version.h>
23 #include "user_list.h"
24 #include "mysql_selector_command_list.h"
26 /** pointer to the shared memory area */
27 extern struct misc_meta_data
*mmd
;
29 static void *mysql_ptr
= NULL
;
32 * contains name/replacement pairs used by s_a_r_list()
37 /** the name of the macro */
39 /** the replacement text */
40 const char *replacement
;
43 static const struct para_macro mysql_macro_list
[] = {
45 .replacement
= "(data.%s != '1')"
48 .replacement
= "(data.%s = '1')"
51 .replacement
= "%sdata.Pic_Id"
54 .replacement
= "(data.name like '%s')"
57 .replacement
= "%sFLOOR((UNIX_TIMESTAMP(now())"
58 "-UNIX_TIMESTAMP(data.Lastplayed))/60)"
61 .replacement
= "%sdata.Numplayed"
68 * simple search and replace routine
70 * \param src source string
71 * \param macro_name the name of the macro
72 * \param replacement the replacement format string
74 * In \p src, replace each occurence of \p macro_name(arg) by the string
75 * determined by the \p replacement format string. \p replacement may (but
76 * needs not) contain a single string conversion specifier (%s) which gets
79 * \return A string in which all matches in \p src are replaced, or \p NULL if
80 * an syntax error was encountered. Caller must free the result.
84 __must_check __malloc
static char *s_a_r(const char *src
, const char* macro_name
,
85 const char *replacement
)
92 const char *bufptr
= src
;
94 if (!macro_name
|| !replacement
|| !src
)
95 return para_strdup(src
);
96 regcomp(&preg
, macro_name
, 0);
97 while (regexec(&preg
, bufptr
, nmatch
, pmatch
, eflags
)
99 char *tmp
, *arg
, *o_bracket
, *c_bracket
;
101 o_bracket
= strchr(bufptr
+ pmatch
[0].rm_so
, '(');
102 c_bracket
= o_bracket
? strchr(o_bracket
, ')') : NULL
;
105 tmp
= para_strdup(bufptr
);
106 tmp
[pmatch
[0].rm_so
] = '\0';
107 dest
= para_strcat(dest
, tmp
);
110 arg
= para_strdup(o_bracket
+ 1);
111 arg
[c_bracket
- o_bracket
- 1] = '\0';
112 tmp
= make_message(replacement
, arg
);
114 dest
= para_strcat(dest
, tmp
);
119 dest
= para_strcat(dest
, bufptr
);
120 // PARA_DEBUG_LOG("%s: returning %s\n", __func__, dest);
127 * replace a string according to a list of macros
129 * \param macro_list the array containing a macro/replacement pairs.
130 * \param src the source string
132 * This function just calls s_a_r() for each element of \p macro_list.
134 * \return \p NULL if one of the underlying calls to \p s_a_r returned \p NULL.
135 * Otherwise the completely expanded version of \p src is returned.
137 __must_check __malloc
static char *s_a_r_list(const struct para_macro
*macro_list
,
140 const struct para_macro
*mp
= macro_list
;
141 char *ret
= NULL
, *tmp
= para_strdup(src
);
144 ret
= s_a_r(tmp
, mp
->name
, mp
->replacement
);
146 if (!ret
) /* syntax error */
151 //PARA_DEBUG_LOG("%s: returning %s\n", __func__, dest);
155 static int real_query(const char *query
)
159 PARA_DEBUG_LOG("%s\n", query
);
160 if (mysql_real_query(mysql_ptr
, query
, strlen(query
))) {
161 PARA_ERROR_LOG("real_query error (%s)\n",
162 mysql_error(mysql_ptr
));
169 * Use open connection given by mysql_ptr to query server. Returns a
170 * result pointer on succes and NULL on errors
172 static struct MYSQL_RES
*get_result(const char *query
)
176 if (real_query(query
) < 0)
178 result
= mysql_store_result(mysql_ptr
);
180 PARA_ERROR_LOG("%s", "store_result error\n");
184 * write input from fd to dynamically allocated char array,
185 * but maximal max_size byte. Return size.
187 static int fd2buf(int fd
, char **buf_ptr
, size_t max_size
)
189 const size_t chunk_size
= 1024;
191 char *buf
= para_malloc(size
* sizeof(char)), *p
= buf
;
194 while ((ret
= recv_bin_buffer(fd
, p
, chunk_size
)) > 0) {
196 if ((p
- buf
) + chunk_size
>= size
) {
200 if (size
> max_size
) {
204 tmp
= para_realloc(buf
, size
);
219 static char *escape_blob(const char* old
, size_t size
)
225 new = para_malloc(2 * size
* sizeof(char) + 1);
226 mysql_real_escape_string(mysql_ptr
, new, old
, size
);
230 static char *escape_str(const char* old
)
232 return escape_blob(old
, strlen(old
));
235 static char *escaped_basename(const char *name
)
237 char *esc
, *bn
= para_basename(name
);
241 esc
= escape_str(bn
);
249 int com_na(__a_unused
int fd
, int argc
, char *argv
[])
255 return -E_MYSQL_SYNTAX
;
256 tmp
= escape_str(argv
[1]);
259 q
= make_message("alter table data add %s char(1) "
260 "not null default 0", tmp
);
270 int com_da(__a_unused
int fd
, int argc
, char *argv
[])
276 return -E_MYSQL_SYNTAX
;
277 tmp
= escape_str(argv
[1]);
280 q
= make_message("alter table data drop %s", tmp
);
288 static int com_stradd_picadd(int fd
, int argc
, char *argv
[])
290 char *blob
= NULL
, *esc_blob
= NULL
, *q
= NULL
, *tmp
= NULL
;
291 const char *fmt
, *del_fmt
;
292 int ret
, stradd
= strcmp(argv
[0], "picadd");
296 return -E_MYSQL_SYNTAX
;
297 if (strlen(argv
[1]) >= MAXLINE
- 1)
298 return -E_NAMETOOLONG
;
303 fmt
= "insert into streams (name, def) values ('%s','%s')";
304 del_fmt
="delete from streams where name='%s'";
306 size
= MEDIUM_BLOB_SIZE
;
307 fmt
= "insert into pics (name, pic) values ('%s','%s')";
308 del_fmt
="delete from pics where pic='%s'";
310 tmp
= escape_str(argv
[1]);
313 q
= make_message(del_fmt
, tmp
);
319 if ((ret
= send_buffer(fd
, AWAITING_DATA_MSG
) < 0))
321 if ((ret
= fd2buf(fd
, &blob
, size
)) < 0)
327 esc_blob
= escape_blob(blob
, size
);
330 tmp
= escape_str(argv
[1]);
333 q
= make_message(fmt
, tmp
, esc_blob
);
344 int com_stradd(int fd
, int argc
, char *argv
[])
346 return com_stradd_picadd(fd
, argc
, argv
);
350 int com_picadd(int fd
, int argc
, char *argv
[])
352 return com_stradd_picadd(fd
, argc
, argv
);
356 * print results to fd
358 static int print_results(int fd
, void *result
,
359 my_ulonglong top
, my_ulonglong left
,
360 my_ulonglong bottom
, my_ulonglong right
)
366 for (i
= top
; i
<= bottom
; i
++) {
367 row
= mysql_fetch_row(result
);
370 for (j
= left
; j
<= right
; j
++) {
371 ret
= send_va_buffer(fd
, j
== left
? "%s" : "\t%s",
372 row
[j
]? row
[j
] : "NULL");
376 ret
= send_buffer(fd
, "\n");
386 int com_verb(int fd
, int argc
, char *argv
[])
390 my_ulonglong num_rows
, num_fields
, top
= 0, left
= 0;
394 return -E_MYSQL_SYNTAX
;
395 tmp
= escape_str(argv
[1]);
398 result
= get_result(tmp
);
401 /* return success, because it's ok to have no results */
403 num_fields
= mysql_field_count(mysql_ptr
);
404 num_rows
= mysql_num_rows(result
);
406 if (num_fields
&& num_rows
)
407 ret
= print_results(fd
, result
, top
, left
, num_rows
- 1,
409 mysql_free_result(result
);
413 /* returns NULL on errors or if there are no atts defined yet */
414 static void *get_all_attributes(void)
416 void *result
= get_result("desc data");
417 unsigned int num_rows
;
421 num_rows
= mysql_num_rows(result
);
423 mysql_free_result(result
);
426 mysql_data_seek(result
, (my_ulonglong
)4); /* skip Lastplayed, Numplayed... */
431 * list all attributes
433 int com_laa(int fd
, int argc
, __a_unused
char *argv
[])
437 my_ulonglong top
= 0, left
= 0, bottom
, right
= 0;
440 return -E_MYSQL_SYNTAX
;
441 result
= get_all_attributes();
444 bottom
= mysql_num_rows(result
);
446 return -E_MYSQL_SYNTAX
;
448 ret
= print_results(fd
, result
, top
, left
, bottom
, right
);
449 mysql_free_result(result
);
456 int com_hist(int fd
, int argc
, char *argv
[])
461 my_ulonglong num_rows
, top
= 0, left
= 0, right
= 1;
464 return -E_MYSQL_SYNTAX
;
466 char *tmp
= escape_str(argv
[1]);
469 atts
= make_message("where %s = '1'", tmp
);
472 atts
= para_strdup(NULL
);
474 q
= make_message("select name, to_days(now()) - to_days(lastplayed) from "
475 "data %s order by lastplayed", atts
);
477 result
= get_result(q
);
481 num_rows
= mysql_num_rows(result
);
484 ret
= print_results(fd
, result
, top
, left
, num_rows
- 1, right
);
485 mysql_free_result(result
);
490 * get last num audio files
492 int com_last(int fd
, int argc
, char *argv
[])
497 my_ulonglong top
= 0, left
= 0, right
= 0;
504 return -E_MYSQL_SYNTAX
;
505 q
= make_message("select name from data order by lastplayed desc "
507 result
= get_result(q
);
511 ret
= print_results(fd
, result
, top
, left
, mysql_num_rows(result
) - 1,
513 mysql_free_result(result
);
517 int com_mbox(int fd
, int argc
, char *argv
[])
522 my_ulonglong num_rows
, num_fields
, top
= 0, left
= 0;
523 char *query
= para_strdup("select concat('From foo@localhost ', "
524 "date_format(Lastplayed, '%a %b %e %T %Y'), "
525 "'\nReceived: from\nTo: bar\n");
528 result
= get_all_attributes();
532 while ((row
= mysql_fetch_row(result
))) {
537 tmp
= make_message("%sX-Attribute-%s: ', %s, '\n", query
,
542 query
= para_strcat(query
,
550 char *esc
= escape_str(argv
[1]), *tmp
;
554 tmp
= make_message("%s where name LIKE '%s'", query
, esc
);
559 mysql_free_result(result
);
561 result
= get_result(query
);
564 ret
= -E_EMPTY_RESULT
;
565 num_fields
= mysql_field_count(mysql_ptr
);
566 num_rows
= mysql_num_rows(result
);
567 if (!num_fields
|| !num_rows
)
569 ret
= print_results(fd
, result
, top
, left
, num_rows
- 1,
574 mysql_free_result(result
);
579 * get attributes by name. If verbose is not 0, this function returns a string
580 * of the form 'att1="0",att2="1"'... which is used in com_cam() for
581 * constructing a mysql update query. Otherwise the space-separated list of all
582 * attributes which are set in the audio file given by name is returned. Never
583 * returns NULL in *NON VERBOSE* mode.
585 static char *get_atts(char *name
, int verbose
)
587 char *atts
= NULL
, *buf
, *ebn
;
588 void *result
= NULL
, *result2
= NULL
;
591 my_ulonglong num_fields
, offset
= 4; /* skip Lastplayed, Numplayed... */
594 result2
= get_all_attributes();
597 ebn
= escaped_basename(name
);
600 buf
= make_message("select * from data where name='%s'", ebn
);
602 result
= get_result(buf
);
606 num_fields
= mysql_num_fields(result
);
609 mysql_data_seek(result2
, offset
);
610 row
= mysql_fetch_row(result
);
613 for (i
= 4; i
< num_fields
; i
++) {
614 int is_set
= row
[i
] && !strcmp(row
[i
], "1");
615 row2
= mysql_fetch_row(result2
);
616 if (!row2
|| !row2
[0])
618 if (atts
&& (verbose
|| is_set
))
619 atts
= para_strcat(atts
, verbose
? "," : " ");
620 if (is_set
|| verbose
)
621 atts
= para_strcat(atts
, row2
[0]);
623 atts
= para_strcat(atts
, is_set
? "=\"1\"" : "=\"0\"");
627 mysql_free_result(result2
);
629 mysql_free_result(result
);
630 if (!atts
&& !verbose
)
631 atts
= para_strdup("(none)");
635 /* never returns NULL in verbose mode */
636 static char *get_meta(char *name
, int verbose
)
640 char *ebn
, *q
, *ret
= NULL
;
641 const char *verbose_fmt
=
642 "select concat('lastplayed: ', "
643 "(to_days(now()) - to_days(lastplayed)),"
644 "' day(s). numplayed: ', numplayed, "
645 "', pic: ', pic_id) "
646 "from data where name = '%s'";
647 /* is that really needed? */
648 const char *fmt
= "select concat('lastplayed=\\'', lastplayed, "
649 "'\\', numplayed=\\'', numplayed, "
650 "'\\', pic_id=\\'', pic_id, '\\'') "
651 "from data where name = '%s'";
653 if (!(ebn
= escaped_basename(name
)))
655 q
= make_message(verbose
? verbose_fmt
: fmt
, ebn
);
657 result
= get_result(q
);
661 row
= mysql_fetch_row(result
);
664 ret
= para_strdup(row
[0]);
667 mysql_free_result(result
);
669 ret
= para_strdup("(not yet played)");
673 static char *get_dir(char *name
)
675 char *ret
= NULL
, *q
, *ebn
;
679 if (!(ebn
= escaped_basename(name
)))
681 q
= make_message("select dir from dir where name = '%s'", ebn
);
683 result
= get_result(q
);
687 row
= mysql_fetch_row(result
);
689 ret
= para_strdup(row
[0]);
690 mysql_free_result(result
);
694 /* never returns NULL */
695 static char *get_current_stream(void)
699 void *result
= get_result("select def from streams where "
700 "name = 'current_stream'");
704 row
= mysql_fetch_row(result
);
707 ret
= para_strdup(row
[0]);
708 mysql_free_result(result
);
712 mysql_free_result(result
);
713 return para_strdup("(none)");
717 * Read stream definition of stream streamname and construct mysql
718 * query. Return NULL on errors. If streamname is NULL, use current
719 * stream. If that is also NULL, use query that selects everything.
720 * If filename is NULL, query will list everything, otherwise only
721 * the score of given file.
723 static char *get_query(char *streamname
, char *filename
, int with_path
)
725 char *accept_opts
= NULL
, *deny_opts
= NULL
, *score
= NULL
;
726 char *where_clause
, *order
, *query
;
727 char command
[255] = ""; /* buffer for sscanf */
731 char *select_clause
= NULL
;
733 tmp
= get_current_stream();
735 tmp
= escape_str(streamname
);
739 if (!strcmp(tmp
, "(none)")) {
742 char *ret
, *ebn
= escaped_basename(filename
);
745 ret
= make_message("select to_days(now()) - "
746 "to_days(lastplayed) from data "
747 "where name = '%s'", ebn
);
753 "select concat(dir.dir, '/', dir.name) "
754 "from data, dir where dir.name = data.name "
755 "order by data.lastplayed"
758 "select name from data where name is not NULL "
759 "order by lastplayed"
763 query
= make_message("select def from streams where name = '%s'",
765 result
= get_result(query
);
770 row
= mysql_fetch_row(result
);
776 char *arg
, *line
= end
;
778 if (!(end
= strchr(line
, '\n')))
782 if (sscanf(line
, "%200s%n", command
, &n
) < 1)
785 if (!strcmp(command
, "accept:")) {
786 char *tmp2
= s_a_r_list(mysql_macro_list
, arg
);
788 accept_opts
= para_strcat(
789 accept_opts
, " or ");
790 accept_opts
= para_strcat(accept_opts
, tmp2
);
794 if (!strcmp(command
, "deny:")) {
795 char *tmp2
= s_a_r_list(mysql_macro_list
, arg
);
797 deny_opts
= para_strcat(deny_opts
, " or ");
798 deny_opts
= para_strcat(deny_opts
, tmp2
);
802 if (!score
&& !strcmp(command
, "score:"))
803 score
= s_a_r_list(mysql_macro_list
, arg
);
806 score
= s_a_r_list(mysql_macro_list
, conf
.mysql_default_score_arg
);
811 char *ebn
= escaped_basename(filename
);
814 select_clause
= make_message("select %s from data ", score
);
816 where_clause
= make_message( "where name = '%s' ", ebn
);
818 order
= para_strdup("");
821 select_clause
= para_strdup(with_path
?
822 "select concat(dir.dir, '/', dir.name) from data, dir "
823 "where dir.name = data.name "
825 "select name from data where name is not NULL");
826 order
= make_message("order by -(%s)", score
);
828 if (accept_opts
&& deny_opts
) {
829 where_clause
= make_message("and ((%s) and not (%s)) ",
830 accept_opts
, deny_opts
);
833 if (accept_opts
&& !deny_opts
) {
834 where_clause
= make_message("and (%s) ", accept_opts
);
837 if (!accept_opts
&& deny_opts
) {
838 where_clause
= make_message("and not (%s) ", deny_opts
);
841 where_clause
= para_strdup("");
843 query
= make_message("%s %s %s", select_clause
, where_clause
, order
);
853 mysql_free_result(result
);
860 * This is called from server and from some commands. Name must not be NULL
861 * Never returns NULL.
863 static char *get_selector_info(char *name
)
865 char *meta
, *atts
, *info
, *dir
, *query
, *stream
;
867 MYSQL_ROW row
= NULL
;
870 return para_strdup("(none)");
871 stream
= get_current_stream();
872 meta
= get_meta(name
, 1);
873 atts
= get_atts(name
, 0);
876 query
= get_query(stream
, name
, 0); /* FIXME: pass stream == NULL instead? */
879 result
= get_result(query
);
882 row
= mysql_fetch_row(result
);
884 info
= make_message("dbinfo1:dir: %s\n"
885 "dbinfo2:stream: %s, %s, score: %s\n"
887 dir
? dir
: "(not contained in table)",
889 (result
&& row
&& row
[0])? row
[0] : "(no score)",
896 mysql_free_result(result
);
900 /* might return NULL */
901 static char *get_current_audio_file(void)
905 name
= para_basename(mmd
->filename
);
910 /* If called as child, mmd_lock must be held */
911 static void update_mmd(char *info
)
913 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
914 strncpy(mmd
->selector_info
, info
, MMD_INFO_SIZE
- 1);
915 mmd
->selector_info
[MMD_INFO_SIZE
- 1] = '\0';
918 static void refresh_selector_info(void)
920 char *name
= get_current_audio_file();
925 info
= get_selector_info(name
);
933 /* list attributes / print database info */
934 static int com_la_info(int fd
, int argc
, char *argv
[])
936 char *name
= NULL
, *meta
= NULL
, *atts
= NULL
, *dir
= NULL
;
937 int ret
, la
= strcmp(argv
[0], "info");
940 ret
= -E_GET_AUDIO_FILE
;
941 if (!(name
= get_current_audio_file()))
943 ret
= send_va_buffer(fd
, "%s\n", name
);
948 if (!(name
= escaped_basename(argv
[1])))
951 meta
= get_meta(name
, 1);
952 atts
= get_atts(name
, 0);
955 ret
= send_va_buffer(fd
, "%s\n", atts
);
957 ret
= send_va_buffer(fd
, "dir: %s\n" "%s\n" "attributes: %s\n",
958 dir
? dir
: "(not contained in table)", meta
, atts
);
967 /* list attributes */
968 int com_la(int fd
, int argc
, char *argv
[])
970 return com_la_info(fd
, argc
, argv
);
973 /* print database info */
974 int com_info(int fd
, int argc
, char *argv
[])
976 return com_la_info(fd
, argc
, argv
);
979 static int change_stream(const char *stream
)
983 query
= make_message("update streams set def='%s' "
984 "where name = 'current_stream'", stream
);
985 ret
= real_query(query
);
990 static int get_pic_id_by_name(char *name
)
997 if (!(ebn
= escaped_basename(name
)))
999 q
= make_message("select pic_id from data where name = '%s'", ebn
);
1001 result
= get_result(q
);
1005 row
= mysql_fetch_row(result
);
1009 mysql_free_result(result
);
1013 static int remove_entry(const char *name
)
1015 char *q
, *ebn
= escaped_basename(name
);
1016 int ret
= -E_ESCAPE
;
1020 q
= make_message("delete from data where name = '%s'", ebn
);
1021 real_query(q
); /* ignore errors */
1023 q
= make_message("delete from dir where name = '%s'", ebn
);
1024 real_query(q
); /* ignore errors */
1032 static int add_entry(const char *name
)
1034 char *q
, *dn
, *ebn
= NULL
, *edn
= NULL
;
1037 if (!name
|| !*name
)
1038 return -E_MYSQL_SYNTAX
;
1039 ebn
= escaped_basename(name
);
1042 ret
= -E_MYSQL_SYNTAX
;
1043 dn
= para_dirname(name
);
1047 edn
= escape_str(dn
);
1051 q
= make_message("insert into data (name, pic_id) values "
1052 "('%s', '%s')", ebn
, "1");
1053 ret
= real_query(q
);
1054 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1058 q
= make_message("insert into dir (name, dir) values "
1059 "('%s', '%s')", ebn
, edn
);
1060 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1061 ret
= real_query(q
);
1072 * remove/add entries
1074 static int com_rm_ne(__a_unused
int fd
, int argc
, char *argv
[])
1076 int ne
= !strcmp(argv
[0], "ne");
1079 return -E_MYSQL_SYNTAX
;
1080 for (i
= 1; i
< argc
; i
++) {
1081 ret
= remove_entry(argv
[i
]);
1086 ret
= add_entry(argv
[i
]);
1096 int com_rm(int fd
, int argc
, char *argv
[])
1098 return com_rm_ne(fd
, argc
, argv
);
1104 int com_ne(int fd
, int argc
, char *argv
[])
1106 return com_ne(fd
, argc
, argv
);
1112 int com_mv(__a_unused
int fd
, int argc
, char *argv
[])
1114 char *q
, *dn
, *ebn1
= NULL
, *ebn2
= NULL
, *edn
= NULL
;
1118 return -E_MYSQL_SYNTAX
;
1120 ebn1
= escaped_basename(argv
[1]);
1121 ebn2
= escaped_basename(argv
[2]);
1122 if (!ebn1
|| !ebn2
|| !*ebn1
|| !*ebn2
)
1124 ret
= -E_MYSQL_SYNTAX
;
1125 if (!strcmp(ebn1
, ebn2
))
1127 remove_entry(argv
[2]); /* no need to escape, ignore error */
1128 q
= make_message("update data set name = '%s' where name = '%s'",
1130 ret
= real_query(q
);
1134 ret
= -E_AUDIO_FILE
;
1135 if (!mysql_affected_rows(mysql_ptr
))
1137 q
= make_message("update dir set name = '%s' where name = '%s'",
1139 ret
= real_query(q
);
1145 dn
= para_dirname(argv
[2]);
1149 edn
= escape_str(dn
);
1156 q
= make_message("update dir set dir = '%s' where name = '%s'",
1158 ret
= real_query(q
);
1170 static int com_set(__a_unused
int fd
, int argc
, char *argv
[])
1175 const char *field
= strcmp(argv
[0], "picass")? "numplayed" : "pic_id";
1178 return -E_MYSQL_SYNTAX
;
1180 for (i
= 2; i
< argc
; i
++) {
1181 ebn
= escaped_basename(argv
[i
]);
1184 q
= make_message("update data set %s = %lu "
1185 "where name = '%s'", field
, id
, ebn
);
1187 ret
= real_query(q
);
1196 * snp: set numplayed
1198 int com_picass(int fd
, int argc
, char *argv
[])
1200 return com_set(fd
, argc
, argv
);
1204 * snp: set numplayed
1206 int com_snp(int fd
, int argc
, char *argv
[])
1208 int ret
= com_set(fd
, argc
, argv
);
1210 refresh_selector_info();
1215 * picch: change entry's name in pics table
1217 int com_picch(__a_unused
int fd
, int argc
, char *argv
[])
1224 return -E_MYSQL_SYNTAX
;
1227 tmp
= escape_str(argv
[2]);
1230 q
= make_message("update pics set name = '%s' where id = %lu", tmp
, id
);
1232 ret
= real_query(q
);
1238 * piclist: print list of pics in db
1240 int com_piclist(__a_unused
int fd
, int argc
, __a_unused
char *argv
[])
1242 void *result
= NULL
;
1244 unsigned long *length
;
1248 return -E_MYSQL_SYNTAX
;
1249 result
= get_result("select id,name,pic from pics order by id");
1252 while ((row
= mysql_fetch_row(result
))) {
1253 length
= mysql_fetch_lengths(result
);
1254 if (!row
|| !row
[0] || !row
[1] || !row
[2])
1256 ret
= send_va_buffer(fd
, "%s\t%lu\t%s\n", row
[0], length
[2], row
[1]);
1262 mysql_free_result(result
);
1267 * picdel: delete picture from database
1269 int com_picdel(int fd
, int argc
, char *argv
[])
1277 return -E_MYSQL_SYNTAX
;
1278 for (i
= 1; i
< argc
; i
++) {
1280 q
= make_message("delete from pics where id = %lu", id
);
1281 ret
= real_query(q
);
1285 aff
= mysql_affected_rows(mysql_ptr
);
1287 ret
= send_va_buffer(fd
, "No such id: %lu\n", id
);
1292 q
= make_message("update data set pic_id = 1 where pic_id = %lu", id
);
1293 ret
= real_query(q
);
1299 * pic: get picture by name or by number
1301 int com_pic(int fd
, int argc
, char *argv
[])
1303 void *result
= NULL
;
1305 unsigned long *length
, id
;
1307 char *q
, *name
= NULL
;
1310 ret
= -E_GET_AUDIO_FILE
;
1311 name
= get_current_audio_file();
1314 name
= escaped_basename(argv
[1]);
1319 id
= atoi(name
+ 1);
1321 id
= get_pic_id_by_name(name
);
1325 q
= make_message("select pic from pics where id = '%lu'", id
);
1326 result
= get_result(q
);
1330 row
= mysql_fetch_row(result
);
1332 if (!row
|| !row
[0])
1334 length
= mysql_fetch_lengths(result
);
1335 ret
= send_bin_buffer(fd
, row
[0], *length
);
1337 mysql_free_result(result
);
1342 int com_strdel(__a_unused
int fd
, int argc
, char *argv
[])
1348 return -E_MYSQL_SYNTAX
;
1349 tmp
= escape_str(argv
[1]);
1352 q
= make_message("delete from streams where name='%s'", tmp
);
1354 ret
= real_query(q
);
1362 int com_ls(int fd
, int argc
, char *argv
[])
1367 my_ulonglong num_rows
, top
= 0, left
= 0, right
= 0;
1370 return -E_MYSQL_SYNTAX
;
1372 char *tmp
= escape_str(argv
[1]);
1375 q
= make_message("select name from data where name like '%s'",
1379 q
= para_strdup("select name from data");
1380 result
= get_result(q
);
1384 num_rows
= mysql_num_rows(result
);
1387 ret
= print_results(fd
, result
, top
, left
, num_rows
- 1, right
);
1388 mysql_free_result(result
);
1395 int com_summary(__a_unused
int fd
, int argc
, __a_unused
char *argv
[])
1400 void *result2
= NULL
;
1401 const char *fmt
= "select count(name) from data where %s='1'";
1402 int ret
= -E_NORESULT
;
1405 return -E_MYSQL_SYNTAX
;
1406 result
= get_all_attributes();
1409 while ((row
= mysql_fetch_row(result
))) {
1416 buf
= make_message(fmt
, row
[0]);
1417 result2
= get_result(buf
);
1422 row2
= mysql_fetch_row(result2
);
1423 if (!row2
|| !row2
[0])
1425 ret
= send_va_buffer(fd
, "%s\t%s\n", row
[0], row2
[0]);
1432 mysql_free_result(result2
);
1434 mysql_free_result(result
);
1438 static int get_numplayed(char *name
)
1442 const char *fmt
= "select numplayed from data where name = '%s'";
1443 char *buf
= make_message(fmt
, name
);
1444 int ret
= -E_NORESULT
;
1446 result
= get_result(buf
);
1451 row
= mysql_fetch_row(result
);
1452 if (!row
|| !row
[0])
1457 mysql_free_result(result
);
1461 static int update_audio_file(char *name
)
1464 const char *fmt1
= "update data set lastplayed = now() where name = '%s'";
1465 const char *fmt2
= "update data set numplayed = %i where name = '%s'";
1467 char *ebn
= escaped_basename(name
);
1472 q
= make_message(fmt1
, ebn
);
1473 ret
= real_query(q
);
1477 ret
= get_numplayed(ebn
);
1480 q
= make_message(fmt2
, ret
+ 1, ebn
);
1481 ret
= real_query(q
);
1488 static void update_audio_file_server_handler(char *name
)
1491 info
= get_selector_info(name
);
1494 update_audio_file(name
);
1497 int com_us(__a_unused
int fd
, int argc
, char *argv
[])
1503 return -E_MYSQL_SYNTAX
;
1504 tmp
= escape_str(argv
[1]);
1507 ret
= update_audio_file(argv
[1]);
1510 refresh_selector_info();
1514 /* select previous / next stream */
1515 static int com_ps_ns(__a_unused
int fd
, int argc
, char *argv
[])
1517 char *query
, *stream
= get_current_stream();
1518 void *result
= get_result("select name from streams");
1521 my_ulonglong num_rows
, match
, i
;
1523 ret
= -E_MYSQL_SYNTAX
;
1529 num_rows
= mysql_num_rows(result
);
1530 ret
= -E_EMPTY_RESULT
;
1534 for (i
= 0; i
< num_rows
; i
++) {
1535 row
= mysql_fetch_row(result
);
1536 if (!row
|| !row
[0])
1538 if (!strcmp(row
[0], "current_stream"))
1540 if (!strcmp(row
[0], stream
))
1547 if (!strcmp(argv
[0], "ps"))
1548 i
= match
> 0? match
- 1 : num_rows
- 1;
1550 i
= match
< num_rows
- 1? match
+ 1 : 0;
1552 mysql_data_seek(result
, i
);
1553 row
= mysql_fetch_row(result
);
1554 if (!row
|| !row
[0])
1556 if (!strcmp(row
[0], "current_stream")) {
1557 if (!strcmp(argv
[0], "ps")) {
1558 i
= match
< 2? match
+ num_rows
- 2 : match
- 2;
1561 i
= i
> num_rows
- 1? i
- num_rows
: i
;
1563 mysql_data_seek(result
, i
);
1564 row
= mysql_fetch_row(result
);
1565 if (!row
|| !row
[0])
1568 query
= make_message("update streams set def='%s' where name = "
1569 "'current_stream'", row
[0]);
1570 ret
= real_query(query
);
1572 refresh_selector_info();
1576 mysql_free_result(result
);
1580 /* select previous stream */
1581 int com_ps(int fd
, int argc
, char *argv
[])
1583 return com_ps_ns(fd
, argc
, argv
);
1586 /* select next stream */
1587 int com_ns(int fd
, int argc
, char *argv
[])
1589 return com_ps_ns(fd
, argc
, argv
);
1593 int com_streams(int fd
, int argc
, __a_unused
char *argv
[])
1595 unsigned int num_rows
;
1596 int i
, ret
= -E_NORESULT
;
1600 if (argc
> 1 && strcmp(argv
[1], "current_stream"))
1601 return -E_MYSQL_SYNTAX
;
1603 char *cs
= get_current_stream();
1604 ret
= send_va_buffer(fd
, "%s\n", cs
);
1608 result
= get_result("select name from streams");
1611 num_rows
= mysql_num_rows(result
);
1616 for (i
= 0; i
< num_rows
; i
++) {
1617 row
= mysql_fetch_row(result
);
1618 if (!row
|| !row
[0])
1620 if (strcmp(row
[0], "current_stream"))
1621 send_va_buffer(fd
, "%s\n", row
[0]);
1626 mysql_free_result(result
);
1630 /* query stream definition */
1631 int com_strq(int fd
, int argc
, char *argv
[])
1639 ret
= -E_GET_STREAM
;
1640 name
= get_current_stream();
1643 name
= escaped_basename(argv
[1]);
1648 query
= make_message("select def from streams where name='%s'", name
);
1650 result
= get_result(query
);
1655 row
= mysql_fetch_row(result
);
1656 if (!row
|| !row
[0])
1658 /* no '\n' needed */
1659 ret
= send_buffer(fd
, row
[0]);
1662 mysql_free_result(result
);
1666 /* change stream / change stream and play */
1667 static int com_cs_csp(int fd
, int argc
, char *argv
[])
1669 int ret
, stream_change
;
1670 char *query
, *stream
= NULL
;
1671 char *old_stream
= get_current_stream();
1672 int csp
= !strcmp(argv
[0], "csp");
1674 ret
= -E_MYSQL_SYNTAX
;
1680 ret
= send_va_buffer(fd
, "%s\n", old_stream
);
1684 /* test if stream is valid, no need to escape argv[1] */
1685 query
= get_query(argv
[1], NULL
, 0);
1690 stream
= escape_str(argv
[1]);
1693 stream_change
= strcmp(stream
, old_stream
);
1694 if (stream_change
) {
1695 ret
= change_stream(stream
);
1698 refresh_selector_info();
1702 mmd
->new_vss_status_flags
|= VSS_PLAYING
;
1704 mmd
->new_vss_status_flags
|= VSS_NEXT
;
1715 int com_cs(int fd
, int argc
, char *argv
[])
1717 return com_cs_csp(fd
, argc
, argv
);
1720 /* change stream and play */
1721 int com_csp(int fd
, int argc
, char *argv
[])
1723 return com_cs_csp(fd
, argc
, argv
);
1726 /* score list / skip */
1727 static int com_sl_skip(int fd
, int argc
, char *argv
[])
1729 void *result
= NULL
;
1731 int ret
, i
, skip
= !strcmp(argv
[0], "skip");
1732 char *query
, *stream
, *tmp
;
1733 unsigned int num_rows
, num
;
1736 return -E_MYSQL_SYNTAX
;
1737 num
= atoi(argv
[1]);
1739 return -E_MYSQL_SYNTAX
;
1741 stream
= get_current_stream();
1743 return -E_GET_STREAM
;
1745 stream
= escape_str(argv
[2]);
1749 tmp
= get_query(stream
, NULL
, 0);
1752 return -E_GET_QUERY
;
1753 query
= make_message("%s limit %d", tmp
, num
);
1756 result
= get_result(query
);
1760 ret
= -E_EMPTY_RESULT
;
1761 num_rows
= mysql_num_rows(result
);
1764 for (i
= 0; i
< num_rows
&& i
< num
; i
++) {
1765 row
= mysql_fetch_row(result
);
1767 send_va_buffer(fd
, "Skipping %s\n", row
[0]);
1768 update_audio_file(row
[0]);
1770 send_va_buffer(fd
, "%s\n", row
[0]? row
[0]: "BUG");
1775 mysql_free_result(result
);
1780 int com_sl(int fd
, int argc
, char *argv
[])
1782 return com_sl_skip(fd
, argc
, argv
);
1786 int com_skip(int fd
, int argc
, char *argv
[])
1788 return com_sl_skip(fd
, argc
, argv
);
1792 * update attributes of name
1794 static int update_atts(int fd
, char *name
, char *atts
)
1797 char *ebn
, *q
, *old
, *new = NULL
;
1801 ebn
= escaped_basename(name
);
1804 q
= make_message("update data set %s where name = '%s'", atts
, ebn
);
1805 old
= get_atts(ebn
, 0);
1806 send_va_buffer(fd
, "old: %s\n", old
);
1808 ret
= real_query(q
);
1812 new = get_atts(ebn
, 0);
1813 ret
= send_va_buffer(fd
, "new: %s\n", new);
1823 int com_sa(int fd
, int argc
, char *argv
[])
1826 char *atts
= NULL
, *name
;
1829 return -E_MYSQL_SYNTAX
;
1830 for (i
= 1; i
< argc
; i
++) {
1832 char *esc
, *tmp
, *p
=argv
[i
];
1833 int len
= strlen(p
);
1837 switch (p
[len
- 1]) {
1848 esc
= escape_str(p
);
1851 tmp
= make_message("%s%s='%s'", atts
? "," : "", esc
,
1854 atts
= para_strcat(atts
, tmp
);
1860 if (i
>= argc
) { /* no name given, use current af */
1861 ret
= -E_GET_AUDIO_FILE
;
1862 if (!(name
= get_current_audio_file()))
1864 ret
= update_atts(fd
, name
, atts
);
1868 for (; argv
[i
] && ret
>= 0; i
++)
1869 ret
= update_atts(fd
, argv
[i
], atts
);
1871 refresh_selector_info();
1880 int com_cam(int fd
, int argc
, char *argv
[])
1882 char *name
= NULL
, *meta
= NULL
, *atts
= NULL
;
1886 return -E_MYSQL_SYNTAX
;
1887 if (!(name
= escaped_basename(argv
[1])))
1890 if (!(atts
= get_atts(name
, 1)))
1893 if (!(meta
= get_meta(name
, 0)))
1895 for (i
= 2; i
< argc
; i
++) {
1898 if (!(ebn
= escaped_basename(argv
[i
])))
1900 ret
= send_va_buffer(fd
, "updating %s\n", ebn
);
1905 q
= make_message("update data set %s where name = '%s'",
1907 if ((ret
= update_atts(fd
, ebn
, atts
)) >= 0)
1908 ret
= real_query(q
);
1928 static int com_vrfy_clean(int fd
, int argc
, __a_unused
char *argv
[])
1931 int ret
, vrfy_mode
= strcmp(argv
[0], "clean");
1932 void *result
= NULL
;
1935 my_ulonglong num_rows
, top
= 0, left
= 0, right
= 0;
1938 return -E_MYSQL_SYNTAX
;
1940 result
= get_result("select data.name from data left join dir on "
1941 "dir.name = data.name where dir.name is NULL");
1944 num_rows
= mysql_num_rows(result
);
1946 ret
= send_buffer(fd
, "No invalid entries\n");
1950 send_va_buffer(fd
, "found %i invalid entr%s\n", num_rows
,
1951 num_rows
== 1? "y" : "ies");
1952 ret
= print_results(fd
, result
, top
, left
, num_rows
- 1, right
);
1955 while ((row
= mysql_fetch_row(result
))) {
1960 escaped_name
= escape_str(row
[0]);
1963 send_va_buffer(fd
, "deleting %s\n", escaped_name
);
1964 query
= make_message("delete from data where name = '%s'",
1966 ret
= real_query(query
);
1974 mysql_free_result(result
);
1981 int com_vrfy(int fd
, int argc
, char **argv
)
1983 return com_vrfy_clean(fd
, argc
, argv
);
1989 int com_clean(int fd
, int argc
, char **argv
)
1991 return com_vrfy_clean(fd
, argc
, argv
);
1994 static FILE *out_file
;
1996 static int mysql_write_tmp_file(const char *dir
, const char *name
)
1998 int ret
= -E_TMPFILE
;
1999 char *msg
= make_message("%s\t%s\n", dir
, name
);
2000 if (fputs(msg
, out_file
) != EOF
)
2009 int com_upd(int fd
, int argc
, __a_unused
char *argv
[])
2011 char *tempname
= NULL
, *query
= NULL
;
2012 int ret
, out_fd
= -1, num
= 0;
2013 void *result
= NULL
;
2014 unsigned int num_rows
;
2018 return -E_MYSQL_SYNTAX
;
2020 tempname
= para_strdup("/tmp/mysql.tmp.XXXXXX");
2021 ret
= para_mkstemp(tempname
, S_IRUSR
| S_IWUSR
| S_IRGRP
| S_IROTH
);
2025 out_file
= fdopen(out_fd
, "w");
2030 if (find_audio_files(conf
.mysql_audio_file_dir_arg
, mysql_write_tmp_file
) < 0)
2032 num
= ftell(out_file
);
2034 * we have to make sure the file hit the disk before we call
2039 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname
, num
);
2042 if ((ret
= real_query("delete from dir")) < 0)
2044 query
= make_message("load data infile '%s' ignore into table dir "
2045 "fields terminated by '\t' lines terminated by '\n' "
2046 "(dir, name)", tempname
);
2047 ret
= real_query(query
);
2051 result
= get_result("select dir.name from dir left join data on "
2052 "data.name = dir.name where data.name is NULL");
2056 num_rows
= mysql_num_rows(result
);
2058 ret
= send_buffer(fd
, "no new entries\n");
2061 while ((row
= mysql_fetch_row(result
))) {
2066 send_va_buffer(fd
, "new entry: %s\n", row
[0]);
2067 erow
= escape_str(row
[0]);
2070 query
= make_message("insert into data (name, pic_id) values "
2071 "('%s','%s')", erow
, "1");
2073 ret
= real_query(query
);
2086 mysql_free_result(result
);
2090 static char **server_get_audio_file_list(unsigned int num
)
2092 char **list
= para_malloc((num
+ 1) * sizeof(char *));
2093 char *tmp
, *query
, *stream
= get_current_stream();
2094 void *result
= NULL
;
2095 unsigned int num_rows
;
2099 tmp
= get_query(stream
, NULL
, 1);
2103 query
= make_message("%s limit %d", tmp
, num
);
2105 result
= get_result(query
);
2109 num_rows
= mysql_num_rows(result
);
2112 for (i
= 0; i
< num_rows
&& i
< num
; i
++) {
2113 row
= mysql_fetch_row(result
);
2114 if (!row
|| !row
[0])
2116 list
[i
] = para_strdup(row
[0]);
2129 mysql_free_result(result
);
2134 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2135 * on errors. Called from parent on startup and also from com_cdb().
2137 static int init_mysql_server(void)
2139 char *u
= conf
.mysql_user_arg
? conf
.mysql_user_arg
: para_logname();
2142 mysql_ptr
= mysql_init(NULL
);
2144 PARA_CRIT_LOG("%s", "mysql init error\n");
2147 if (conf
.mysql_port_arg
< 0)
2148 return -E_MYSQL_SYNTAX
;
2149 port
= conf
.mysql_port_arg
;
2150 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u
, conf
.mysql_host_arg
, port
);
2151 if (!conf
.mysql_user_arg
)
2154 * If host is NULL a connection to the local host is assumed,
2155 * If user is NULL, the current user is assumed
2157 if (!(mysql_ptr
= mysql_real_connect(mysql_ptr
,
2158 conf
.mysql_host_arg
,
2159 conf
.mysql_user_arg
,
2160 conf
.mysql_passwd_arg
,
2161 conf
.mysql_database_arg
,
2163 PARA_CRIT_LOG("%s", "connect error\n");
2166 PARA_INFO_LOG("%s", "success\n");
2170 /* mmd lock must be held */
2171 static void write_msg2mmd(int success
)
2173 sprintf(mmd
->selector_info
, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2174 success
< 0? PARA_STRERROR(-success
) :
2175 "successfully connected to mysql server",
2176 success
< 0? "" : mysql_get_server_info(mysql_ptr
));
2179 /* create database */
2180 int com_cdb(int fd
, int argc
, char *argv
[])
2186 PARA_INFO_LOG("%s", "closing database\n");
2187 mysql_close(mysql_ptr
);
2189 /* dont use any database */
2190 conf
.mysql_database_arg
= NULL
; /* leak? */
2191 ret
= -E_MYSQL_INIT
;
2192 if (init_mysql_server() < 0 || !mysql_ptr
)
2195 conf
.mysql_database_arg
= para_strdup("paraslash");
2198 conf
.mysql_database_arg
= escape_str(argv
[1]);
2199 if (!conf
.mysql_database_arg
)
2202 query
= make_message("create database %s", conf
.mysql_database_arg
);
2203 ret
= real_query(query
);
2207 /* reconnect with database just created */
2208 mysql_close(mysql_ptr
);
2209 ret
= -E_MYSQL_INIT
;
2210 if (init_mysql_server() < 0 || !mysql_ptr
)
2216 if (real_query("create table data (name varchar(255) binary not null "
2218 "lastplayed datetime not null default "
2220 "numplayed int not null default 0, "
2221 "pic_id bigint unsigned not null default 1)") < 0)
2223 if (real_query("create table dir (name varchar(255) binary not null "
2224 "primary key, dir varchar(255) default null)") < 0)
2226 if (real_query("create table pics ("
2227 "id bigint(20) unsigned not null primary key "
2229 "name varchar(255) binary not null, "
2230 "pic mediumblob not null)") < 0)
2232 if (real_query("create table streams ("
2233 "name varchar(255) binary not null primary key, "
2234 "def blob not null)") < 0)
2236 if (real_query("insert into streams (name, def) values "
2237 "('current_stream', '(none)')") < 0)
2239 ret
= send_va_buffer(fd
, "successfully created database %s\n",
2240 conf
.mysql_database_arg
);
2245 static void shutdown_connection(void)
2248 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2249 mysql_close(mysql_ptr
);
2255 * the init function of the mysql-based audio file selector
2257 * \param db pointer to the struct to initialize
2259 * Check the command line options and initialize all function pointers of \a
2260 * db. Connect to the mysql server and initialize the info string.
2262 * \return This function returns success even if it could not connect
2263 * to the mysql server. This is because the connect is expected to fail
2264 * if there the paraslash database is not yet created. This gives the
2265 * user a chance to send the "cdb" to create the database.
2267 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2270 int mysql_selector_init(struct audio_file_selector
*db
)
2274 if (!conf
.mysql_passwd_given
)
2275 return -E_NO_MYSQL_PASSWD
;
2276 if (!conf
.mysql_audio_file_dir_given
)
2277 return -E_NO_AF_DIR
;
2279 db
->cmd_list
= mysql_selector_cmds
;
2280 db
->get_audio_file_list
= server_get_audio_file_list
;
2281 db
->update_audio_file
= update_audio_file_server_handler
;
2282 db
->shutdown
= shutdown_connection
;
2283 ret
= init_mysql_server();
2285 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret
));
2287 return 1; /* return success even if connect failed to give the
2288 * user the chance to exec com_cdb