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) */
17 #include "server.cmdline.h"
22 #include "afs_common.h"
23 #include <mysql/mysql.h>
24 #include <mysql/mysql_version.h>
28 #include "user_list.h"
29 #include "mysql_selector_command_list.h"
32 /** pointer to the shared memory area */
33 extern struct misc_meta_data *mmd;
35 static void *mysql_ptr = NULL;
36 static int mysql_lock;
39 * contains name/replacement pairs used by s_a_r_list()
44 /** the name of the macro */
46 /** the replacement text */
47 const char *replacement;
50 static const struct para_macro mysql_macro_list[] = {
52 .replacement = "(data.%s != '1')"
55 .replacement = "(data.%s = '1')"
58 .replacement = "%sdata.Pic_Id"
61 .replacement = "(data.name like '%s')"
64 .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())"
65 "-UNIX_TIMESTAMP(data.Lastplayed))/60)"
68 .replacement = "%sdata.Numplayed"
75 * Simple search and replace routine.
77 * \param src Source String.
78 * \param macro_name The name of the macro.
79 * \param replacement The replacement format string.
81 * In \p src, replace each occurence of \p macro_name(arg) by the string
82 * determined by the \p replacement format string. \p replacement may (but
83 * needs not) contain a single string conversion specifier (%s) which gets
86 * \return A string in which all matches in \p src are replaced, or \p NULL if
87 * an error was encountered. Caller must free the result.
91 __must_check __malloc static char *s_a_r(const char *src, const char* macro_name,
92 const char *replacement)
99 const char *bufptr = src;
101 if (!macro_name || !replacement || !src)
102 return para_strdup(src);
103 if (regcomp(&preg, macro_name, 0) != 0)
105 while (regexec(&preg, bufptr, nmatch, pmatch, eflags)
107 char *tmp, *arg, *o_bracket, *c_bracket;
109 o_bracket = strchr(bufptr + pmatch[0].rm_so, '(');
110 c_bracket = o_bracket? strchr(o_bracket, ')') : NULL;
113 tmp = para_strdup(bufptr);
114 tmp[pmatch[0].rm_so] = '\0';
115 dest = para_strcat(dest, tmp);
118 arg = para_strdup(o_bracket + 1);
119 arg[c_bracket - o_bracket - 1] = '\0';
120 tmp = make_message(replacement, arg);
122 dest = para_strcat(dest, tmp);
127 dest = para_strcat(dest, bufptr);
128 // PARA_DEBUG_LOG("%s: returning %s\n", __func__, dest);
135 * replace a string according to a list of macros
137 * \param macro_list the array containing a macro/replacement pairs.
138 * \param src the source string
140 * This function just calls s_a_r() for each element of \p macro_list.
142 * \return \p NULL if one of the underlying calls to \p s_a_r returned \p NULL.
143 * Otherwise the completely expanded version of \p src is returned.
145 __must_check __malloc static char *s_a_r_list(const struct para_macro *macro_list,
148 const struct para_macro *mp = macro_list;
149 char *ret = NULL, *tmp = para_strdup(src);
152 ret = s_a_r(tmp, mp->name, mp->replacement);
154 if (!ret) /* syntax error */
159 //PARA_DEBUG_LOG("%s: returning %s\n", __func__, dest);
163 static int lockless_real_query(const char *query)
167 PARA_DEBUG_LOG("%s\n", query);
168 if (mysql_real_query(mysql_ptr, query, strlen(query))) {
169 PARA_ERROR_LOG("real_query error (%s)\n",
170 mysql_error(mysql_ptr));
176 static int real_query(const char *query)
180 mutex_lock(mysql_lock);
181 ret = lockless_real_query(query);
182 mutex_unlock(mysql_lock);
187 * Use open connection given by mysql_ptr to query server. Returns a
188 * result pointer on succes and NULL on errors
190 static struct MYSQL_RES *get_result(const char *query)
194 mutex_lock(mysql_lock);
195 if (lockless_real_query(query) < 0)
197 result = mysql_store_result(mysql_ptr);
199 PARA_ERROR_LOG("%s", "store_result error\n");
201 mutex_unlock(mysql_lock);
205 * write input from fd to dynamically allocated char array,
206 * but maximal max_size byte. Return size.
208 static int fd2buf(int fd, char **buf_ptr, size_t max_size)
210 const size_t chunk_size = 1024;
212 char *buf = para_malloc(size * sizeof(char)), *p = buf;
215 while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) {
217 if ((p - buf) + chunk_size >= size) {
221 if (size > max_size) {
225 tmp = para_realloc(buf, size);
240 static char *escape_blob(const char* old, size_t size)
246 new = para_malloc(2 * size * sizeof(char) + 1);
247 mysql_real_escape_string(mysql_ptr, new, old, size);
251 static char *escape_str(const char* old)
253 return escape_blob(old, strlen(old));
256 static char *escaped_basename(const char *name)
258 char *esc, *bn = para_basename(name);
262 esc = escape_str(bn);
270 int com_na(__a_unused int fd, int argc, char * const * argv)
276 return -E_MYSQL_SYNTAX;
277 tmp = escape_str(argv[1]);
280 q = make_message("alter table data add %s char(1) "
281 "not null default 0", tmp);
291 int com_da(__a_unused int fd, int argc, char * const * argv)
297 return -E_MYSQL_SYNTAX;
298 tmp = escape_str(argv[1]);
301 q = make_message("alter table data drop %s", tmp);
309 static int com_stradd_picadd(int fd, int argc, char * const * argv)
311 char *blob = NULL, *esc_blob = NULL, *q = NULL, *tmp = NULL;
312 const char *fmt, *del_fmt;
313 int ret, stradd = strcmp(argv[0], "picadd");
317 return -E_MYSQL_SYNTAX;
318 if (strlen(argv[1]) >= MAXLINE - 1)
319 return -E_NAMETOOLONG;
324 fmt = "insert into streams (name, def) values ('%s','%s')";
325 del_fmt="delete from streams where name='%s'";
327 size = MEDIUM_BLOB_SIZE;
328 fmt = "insert into pics (name, pic) values ('%s','%s')";
329 del_fmt="delete from pics where pic='%s'";
331 tmp = escape_str(argv[1]);
334 q = make_message(del_fmt, tmp);
340 if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0))
342 if ((ret = fd2buf(fd, &blob, size)) < 0)
348 esc_blob = escape_blob(blob, size);
351 tmp = escape_str(argv[1]);
354 q = make_message(fmt, tmp, esc_blob);
365 int com_stradd(int fd, int argc, char * const * argv)
367 return com_stradd_picadd(fd, argc, argv);
371 int com_picadd(int fd, int argc, char * const * argv)
373 return com_stradd_picadd(fd, argc, argv);
377 * print results to fd
379 static int print_results(int fd, void *result,
380 my_ulonglong top, my_ulonglong left,
381 my_ulonglong bottom, my_ulonglong right)
387 for (i = top; i <= bottom; i++) {
388 row = mysql_fetch_row(result);
391 for (j = left; j <= right; j++) {
392 ret = send_va_buffer(fd, j == left? "%s" : "\t%s",
393 row[j]? row[j] : "NULL");
397 ret = send_buffer(fd, "\n");
407 int com_verb(int fd, int argc, char * const * argv)
411 my_ulonglong num_rows, num_fields, top = 0, left = 0;
415 return -E_MYSQL_SYNTAX;
416 tmp = escape_str(argv[1]);
419 result = get_result(tmp);
422 /* return success, because it's ok to have no results */
424 num_fields = mysql_field_count(mysql_ptr);
425 num_rows = mysql_num_rows(result);
427 if (num_fields && num_rows)
428 ret = print_results(fd, result, top, left, num_rows - 1,
430 mysql_free_result(result);
434 /* returns NULL on errors or if there are no atts defined yet */
435 static void *get_all_attributes(void)
437 void *result = get_result("desc data");
438 unsigned int num_rows;
442 num_rows = mysql_num_rows(result);
444 mysql_free_result(result);
447 mysql_data_seek(result, (my_ulonglong)4); /* skip Lastplayed, Numplayed... */
452 * list all attributes
454 int com_laa(int fd, int argc, __a_unused char * const * argv)
458 my_ulonglong top = 0, left = 0, bottom, right = 0;
461 return -E_MYSQL_SYNTAX;
462 result = get_all_attributes();
465 bottom = mysql_num_rows(result);
467 return -E_MYSQL_SYNTAX;
469 ret = print_results(fd, result, top, left, bottom, right);
470 mysql_free_result(result);
477 int com_hist(int fd, int argc, char * const * argv)
482 my_ulonglong num_rows, top = 0, left = 0, right = 1;
485 return -E_MYSQL_SYNTAX;
487 char *tmp = escape_str(argv[1]);
490 atts = make_message("where %s = '1'", tmp);
493 atts = para_strdup(NULL);
495 q = make_message("select name, to_days(now()) - to_days(lastplayed) from "
496 "data %s order by lastplayed", atts);
498 result = get_result(q);
502 num_rows = mysql_num_rows(result);
505 ret = print_results(fd, result, top, left, num_rows - 1, right);
506 mysql_free_result(result);
511 * get last num audio files
513 int com_last(int fd, int argc, char * const * argv)
518 my_ulonglong top = 0, left = 0, right = 0;
525 return -E_MYSQL_SYNTAX;
526 q = make_message("select name from data order by lastplayed desc "
528 result = get_result(q);
532 ret = print_results(fd, result, top, left, mysql_num_rows(result) - 1,
534 mysql_free_result(result);
538 int com_mbox(int fd, int argc, char * const * argv)
543 my_ulonglong num_rows, num_fields, top = 0, left = 0;
544 char *query = para_strdup("select concat('From foo@localhost ', "
545 "date_format(Lastplayed, '%a %b %e %T %Y'), "
546 "'\nReceived: from\nTo: bar\n");
549 result = get_all_attributes();
553 while ((row = mysql_fetch_row(result))) {
558 tmp = make_message("%sX-Attribute-%s: ', %s, '\n", query,
563 query = para_strcat(query,
571 char *esc = escape_str(argv[1]), *tmp;
575 tmp = make_message("%s where name LIKE '%s'", query, esc);
580 mysql_free_result(result);
582 result = get_result(query);
585 ret = -E_EMPTY_RESULT;
586 num_fields = mysql_field_count(mysql_ptr);
587 num_rows = mysql_num_rows(result);
588 if (!num_fields || !num_rows)
590 ret = print_results(fd, result, top, left, num_rows - 1,
595 mysql_free_result(result);
600 * get attributes by name. If verbose is not 0, this function returns a string
601 * of the form 'att1="0",att2="1"'... which is used in com_cam() for
602 * constructing a mysql update query. Otherwise the space-separated list of all
603 * attributes which are set in the audio file given by name is returned. Never
604 * returns NULL in *NON VERBOSE* mode.
606 static char *get_atts(char *name, int verbose)
608 char *atts = NULL, *buf, *ebn;
609 void *result = NULL, *result2 = NULL;
612 my_ulonglong num_fields, offset = 4; /* skip Lastplayed, Numplayed... */
615 result2 = get_all_attributes();
618 ebn = escaped_basename(name);
621 buf = make_message("select * from data where name='%s'", ebn);
623 result = get_result(buf);
627 num_fields = mysql_num_fields(result);
630 mysql_data_seek(result2, offset);
631 row = mysql_fetch_row(result);
634 for (i = 4; i < num_fields; i++) {
635 int is_set = row[i] && !strcmp(row[i], "1");
636 row2 = mysql_fetch_row(result2);
637 if (!row2 || !row2[0])
639 if (atts && (verbose || is_set))
640 atts = para_strcat(atts, verbose? "," : " ");
641 if (is_set || verbose)
642 atts = para_strcat(atts, row2[0]);
644 atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\"");
648 mysql_free_result(result2);
650 mysql_free_result(result);
651 if (!atts && !verbose)
652 atts = para_strdup("(none)");
656 /* never returns NULL in verbose mode */
657 static char *get_meta(char *name, int verbose)
661 char *ebn, *q, *ret = NULL;
662 const char *verbose_fmt =
663 "select concat('lastplayed: ', "
664 "(to_days(now()) - to_days(lastplayed)),"
665 "' day(s). numplayed: ', numplayed, "
666 "', pic: ', pic_id) "
667 "from data where name = '%s'";
668 /* is that really needed? */
669 const char *fmt = "select concat('lastplayed=\\'', lastplayed, "
670 "'\\', numplayed=\\'', numplayed, "
671 "'\\', pic_id=\\'', pic_id, '\\'') "
672 "from data where name = '%s'";
674 if (!(ebn = escaped_basename(name)))
676 q = make_message(verbose? verbose_fmt : fmt, ebn);
678 result = get_result(q);
682 row = mysql_fetch_row(result);
685 ret = para_strdup(row[0]);
688 mysql_free_result(result);
690 ret = para_strdup("(not yet played)");
694 static char *get_dir(char *name)
696 char *ret = NULL, *q, *ebn;
700 if (!(ebn = escaped_basename(name)))
702 q = make_message("select dir from dir where name = '%s'", ebn);
704 result = get_result(q);
708 row = mysql_fetch_row(result);
710 ret = para_strdup(row[0]);
711 mysql_free_result(result);
715 /* never returns NULL */
716 static char *get_current_stream(void)
720 void *result = get_result("select def from streams where "
721 "name = 'current_stream'");
725 row = mysql_fetch_row(result);
728 ret = para_strdup(row[0]);
729 mysql_free_result(result);
733 mysql_free_result(result);
734 return para_strdup("(none)");
738 * Read stream definition of stream streamname and construct mysql
739 * query. Return NULL on errors. If streamname is NULL, use current
740 * stream. If that is also NULL, use query that selects everything.
741 * If filename is NULL, query will list everything, otherwise only
742 * the score of given file.
744 static char *get_query(const char *streamname, char *filename, int with_path)
746 char *accept_opts = NULL, *deny_opts = NULL, *score = NULL;
747 char *where_clause, *order, *query;
748 char command[255] = ""; /* buffer for sscanf */
752 char *select_clause = NULL;
754 tmp = get_current_stream();
756 tmp = escape_str(streamname);
760 if (!strcmp(tmp, "(none)")) {
763 char *ret, *ebn = escaped_basename(filename);
766 ret = make_message("select to_days(now()) - "
767 "to_days(lastplayed) from data "
768 "where name = '%s'", ebn);
774 "select concat(dir.dir, '/', dir.name) "
775 "from data, dir where dir.name = data.name "
776 "order by data.lastplayed"
779 "select name from data where name is not NULL "
780 "order by lastplayed"
784 query = make_message("select def from streams where name = '%s'",
786 result = get_result(query);
791 row = mysql_fetch_row(result);
797 char *arg, *line = end;
799 if (!(end = strchr(line, '\n')))
803 if (sscanf(line, "%200s%n", command, &n) < 1)
806 if (!strcmp(command, "accept:")) {
807 char *tmp2 = s_a_r_list(mysql_macro_list, arg);
809 accept_opts = para_strcat(
810 accept_opts, " or ");
811 accept_opts = para_strcat(accept_opts, tmp2);
815 if (!strcmp(command, "deny:")) {
816 char *tmp2 = s_a_r_list(mysql_macro_list, arg);
818 deny_opts = para_strcat(deny_opts, " or ");
819 deny_opts = para_strcat(deny_opts, tmp2);
823 if (!score && !strcmp(command, "score:"))
824 score = s_a_r_list(mysql_macro_list, arg);
827 score = s_a_r_list(mysql_macro_list, conf.mysql_default_score_arg);
832 char *ebn = escaped_basename(filename);
835 select_clause = make_message("select %s from data ", score);
837 where_clause = make_message( "where name = '%s' ", ebn);
839 order = para_strdup("");
842 select_clause = para_strdup(with_path?
843 "select concat(dir.dir, '/', dir.name) from data, dir "
844 "where dir.name = data.name "
846 "select name from data where name is not NULL");
847 order = make_message("order by -(%s)", score);
849 if (accept_opts && deny_opts) {
850 where_clause = make_message("and ((%s) and not (%s)) ",
851 accept_opts, deny_opts);
854 if (accept_opts && !deny_opts) {
855 where_clause = make_message("and (%s) ", accept_opts);
858 if (!accept_opts && deny_opts) {
859 where_clause = make_message("and not (%s) ", deny_opts);
862 where_clause = para_strdup("");
864 query = make_message("%s %s %s", select_clause, where_clause, order);
874 mysql_free_result(result);
881 * This is called from server and from some commands. Name must not be NULL
882 * Never returns NULL.
884 static char *get_selector_info(char *name)
886 char *meta, *atts, *info, *dir, *query, *stream;
888 MYSQL_ROW row = NULL;
891 return para_strdup("(none)");
892 stream = get_current_stream();
893 meta = get_meta(name, 1);
894 atts = get_atts(name, 0);
897 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
900 result = get_result(query);
903 row = mysql_fetch_row(result);
905 info = make_message("dbinfo1:dir: %s\n"
906 "dbinfo2:stream: %s, %s, score: %s\n"
908 dir? dir : "(not contained in table)",
910 (result && row && row[0])? row[0] : "(no score)",
917 mysql_free_result(result);
921 /* might return NULL */
922 static char *get_current_audio_file(void)
926 name = para_basename(mmd->filename);
931 /* If called as child, mmd_lock must be held */
932 static void update_mmd(char *info)
934 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
935 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
936 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
939 static void refresh_selector_info(void)
941 char *name = get_current_audio_file();
946 info = get_selector_info(name);
954 /* list attributes / print database info */
955 static int com_la_info(int fd, int argc, char * const * argv)
957 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
958 int ret, la = strcmp(argv[0], "info");
961 ret = -E_GET_AUDIO_FILE;
962 if (!(name = get_current_audio_file()))
964 ret = send_va_buffer(fd, "%s\n", name);
969 if (!(name = escaped_basename(argv[1])))
972 meta = get_meta(name, 1);
973 atts = get_atts(name, 0);
976 ret = send_va_buffer(fd, "%s\n", atts);
978 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
979 dir? dir : "(not contained in table)", meta, atts);
988 /* list attributes */
989 int com_la(int fd, int argc, char * const * argv)
991 return com_la_info(fd, argc, argv);
994 /* print database info */
995 int com_info(int fd, int argc, char * const * argv)
997 return com_la_info(fd, argc, argv);
1000 static int change_stream(const char *stream)
1004 query = make_message("update streams set def='%s' "
1005 "where name = 'current_stream'", stream);
1006 ret = real_query(query);
1011 static int get_pic_id_by_name(char *name)
1014 void *result = NULL;
1018 if (!(ebn = escaped_basename(name)))
1020 q = make_message("select pic_id from data where name = '%s'", ebn);
1022 result = get_result(q);
1026 row = mysql_fetch_row(result);
1030 mysql_free_result(result);
1034 static int remove_entry(const char *name)
1036 char *q, *ebn = escaped_basename(name);
1037 int ret = -E_ESCAPE;
1041 q = make_message("delete from data where name = '%s'", ebn);
1042 real_query(q); /* ignore errors */
1044 q = make_message("delete from dir where name = '%s'", ebn);
1045 real_query(q); /* ignore errors */
1053 static int add_entry(const char *name)
1055 char *q, *dn, *ebn = NULL, *edn = NULL;
1058 if (!name || !*name)
1059 return -E_MYSQL_SYNTAX;
1060 ebn = escaped_basename(name);
1063 ret = -E_MYSQL_SYNTAX;
1064 dn = para_dirname(name);
1068 edn = escape_str(dn);
1072 q = make_message("insert into data (name, pic_id) values "
1073 "('%s', '%s')", ebn, "1");
1074 ret = real_query(q);
1075 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1079 q = make_message("insert into dir (name, dir) values "
1080 "('%s', '%s')", ebn, edn);
1081 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1082 ret = real_query(q);
1093 * remove/add entries
1095 static int com_rm_ne(__a_unused int fd, int argc, char * const * argv)
1097 int ne = !strcmp(argv[0], "ne");
1100 return -E_MYSQL_SYNTAX;
1101 for (i = 1; i < argc; i++) {
1102 ret = remove_entry(argv[i]);
1107 ret = add_entry(argv[i]);
1117 int com_rm(int fd, int argc, char * const * argv)
1119 return com_rm_ne(fd, argc, argv);
1125 int com_ne(int fd, int argc, char * const * argv)
1127 return com_ne(fd, argc, argv);
1133 int com_mv(__a_unused int fd, int argc, char * const * argv)
1135 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1139 return -E_MYSQL_SYNTAX;
1141 ebn1 = escaped_basename(argv[1]);
1142 ebn2 = escaped_basename(argv[2]);
1143 if (!ebn1 || !ebn2 || !*ebn1 || !*ebn2)
1145 ret = -E_MYSQL_SYNTAX;
1146 if (!strcmp(ebn1, ebn2))
1148 remove_entry(argv[2]); /* no need to escape, ignore error */
1149 q = make_message("update data set name = '%s' where name = '%s'",
1151 ret = real_query(q);
1155 ret = -E_AUDIO_FILE;
1156 if (!mysql_affected_rows(mysql_ptr))
1158 q = make_message("update dir set name = '%s' where name = '%s'",
1160 ret = real_query(q);
1166 dn = para_dirname(argv[2]);
1170 edn = escape_str(dn);
1177 q = make_message("update dir set dir = '%s' where name = '%s'",
1179 ret = real_query(q);
1191 static int com_set(__a_unused int fd, int argc, char * const * argv)
1196 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1199 return -E_MYSQL_SYNTAX;
1201 for (i = 2; i < argc; i++) {
1202 ebn = escaped_basename(argv[i]);
1205 q = make_message("update data set %s = %lu "
1206 "where name = '%s'", field, id, ebn);
1208 ret = real_query(q);
1217 * snp: set numplayed
1219 int com_picass(int fd, int argc, char * const * argv)
1221 return com_set(fd, argc, argv);
1225 * snp: set numplayed
1227 int com_snp(int fd, int argc, char * const * argv)
1229 int ret = com_set(fd, argc, argv);
1231 refresh_selector_info();
1236 * picch: change entry's name in pics table
1238 int com_picch(__a_unused int fd, int argc, char * const * argv)
1245 return -E_MYSQL_SYNTAX;
1248 tmp = escape_str(argv[2]);
1251 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1253 ret = real_query(q);
1259 * piclist: print list of pics in db
1261 int com_piclist(__a_unused int fd, int argc, __a_unused char * const * argv)
1263 void *result = NULL;
1265 unsigned long *length;
1269 return -E_MYSQL_SYNTAX;
1270 result = get_result("select id,name,pic from pics order by id");
1273 while ((row = mysql_fetch_row(result))) {
1274 length = mysql_fetch_lengths(result);
1275 if (!row || !row[0] || !row[1] || !row[2])
1277 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1283 mysql_free_result(result);
1288 * picdel: delete picture from database
1290 int com_picdel(int fd, int argc, char * const * argv)
1298 return -E_MYSQL_SYNTAX;
1299 for (i = 1; i < argc; i++) {
1301 q = make_message("delete from pics where id = %lu", id);
1302 ret = real_query(q);
1306 aff = mysql_affected_rows(mysql_ptr);
1308 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1313 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1314 ret = real_query(q);
1320 * pic: get picture by name or by number
1322 int com_pic(int fd, int argc, char * const * argv)
1324 void *result = NULL;
1326 unsigned long *length, id;
1328 char *q, *name = NULL;
1331 ret = -E_GET_AUDIO_FILE;
1332 name = get_current_audio_file();
1335 name = escaped_basename(argv[1]);
1340 id = atoi(name + 1);
1342 id = get_pic_id_by_name(name);
1346 q = make_message("select pic from pics where id = '%lu'", id);
1347 result = get_result(q);
1351 row = mysql_fetch_row(result);
1353 if (!row || !row[0])
1355 length = mysql_fetch_lengths(result);
1356 ret = send_bin_buffer(fd, row[0], *length);
1358 mysql_free_result(result);
1363 int com_strdel(__a_unused int fd, int argc, char * const * argv)
1369 return -E_MYSQL_SYNTAX;
1370 tmp = escape_str(argv[1]);
1373 q = make_message("delete from streams where name='%s'", tmp);
1375 ret = real_query(q);
1383 int com_ls(int fd, int argc, char * const * argv)
1388 my_ulonglong num_rows, top = 0, left = 0, right = 0;
1391 return -E_MYSQL_SYNTAX;
1393 char *tmp = escape_str(argv[1]);
1396 q = make_message("select name from data where name like '%s'",
1400 q = para_strdup("select name from data");
1401 result = get_result(q);
1405 num_rows = mysql_num_rows(result);
1408 ret = print_results(fd, result, top, left, num_rows - 1, right);
1409 mysql_free_result(result);
1416 int com_summary(__a_unused int fd, int argc, __a_unused char * const * argv)
1421 void *result2 = NULL;
1422 const char *fmt = "select count(name) from data where %s='1'";
1423 int ret = -E_NORESULT;
1426 return -E_MYSQL_SYNTAX;
1427 result = get_all_attributes();
1430 while ((row = mysql_fetch_row(result))) {
1437 buf = make_message(fmt, row[0]);
1438 result2 = get_result(buf);
1443 row2 = mysql_fetch_row(result2);
1444 if (!row2 || !row2[0])
1446 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1453 mysql_free_result(result2);
1455 mysql_free_result(result);
1459 static int get_numplayed(char *name)
1463 const char *fmt = "select numplayed from data where name = '%s'";
1464 char *buf = make_message(fmt, name);
1465 int ret = -E_NORESULT;
1467 result = get_result(buf);
1472 row = mysql_fetch_row(result);
1473 if (!row || !row[0])
1478 mysql_free_result(result);
1482 static int update_audio_file(const char *name)
1485 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1486 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1488 char *ebn = escaped_basename(name);
1493 q = make_message(fmt1, ebn);
1494 ret = real_query(q);
1498 ret = get_numplayed(ebn);
1501 q = make_message(fmt2, ret + 1, ebn);
1502 ret = real_query(q);
1509 static void update_audio_file_server_handler(char *name)
1512 info = get_selector_info(name);
1515 update_audio_file(name);
1518 int com_us(__a_unused int fd, int argc, char * const * argv)
1524 return -E_MYSQL_SYNTAX;
1525 tmp = escape_str(argv[1]);
1528 ret = update_audio_file(argv[1]);
1531 refresh_selector_info();
1535 /* select previous / next stream */
1536 static int com_ps_ns(__a_unused int fd, int argc, char * const * argv)
1538 char *query, *stream = get_current_stream();
1539 void *result = get_result("select name from streams");
1542 my_ulonglong num_rows, match, i;
1544 ret = -E_MYSQL_SYNTAX;
1550 num_rows = mysql_num_rows(result);
1551 ret = -E_EMPTY_RESULT;
1555 for (i = 0; i < num_rows; i++) {
1556 row = mysql_fetch_row(result);
1557 if (!row || !row[0])
1559 if (!strcmp(row[0], "current_stream"))
1561 if (!strcmp(row[0], stream))
1568 if (!strcmp(argv[0], "ps"))
1569 i = match > 0? match - 1 : num_rows - 1;
1571 i = match < num_rows - 1? match + 1 : 0;
1573 mysql_data_seek(result, i);
1574 row = mysql_fetch_row(result);
1575 if (!row || !row[0])
1577 if (!strcmp(row[0], "current_stream")) {
1578 if (!strcmp(argv[0], "ps")) {
1579 i = match < 2? match + num_rows - 2 : match - 2;
1582 i = i > num_rows - 1? i - num_rows : i;
1584 mysql_data_seek(result, i);
1585 row = mysql_fetch_row(result);
1586 if (!row || !row[0])
1589 query = make_message("update streams set def='%s' where name = "
1590 "'current_stream'", row[0]);
1591 ret = real_query(query);
1593 refresh_selector_info();
1597 mysql_free_result(result);
1601 /* select previous stream */
1602 int com_ps(int fd, int argc, char * const * argv)
1604 return com_ps_ns(fd, argc, argv);
1607 /* select next stream */
1608 int com_ns(int fd, int argc, char * const * argv)
1610 return com_ps_ns(fd, argc, argv);
1614 int com_streams(int fd, int argc, __a_unused char * const * argv)
1616 unsigned int num_rows;
1617 int i, ret = -E_NORESULT;
1621 if (argc > 1 && strcmp(argv[1], "current_stream"))
1622 return -E_MYSQL_SYNTAX;
1624 char *cs = get_current_stream();
1625 ret = send_va_buffer(fd, "%s\n", cs);
1629 result = get_result("select name from streams");
1632 num_rows = mysql_num_rows(result);
1637 for (i = 0; i < num_rows; i++) {
1638 row = mysql_fetch_row(result);
1639 if (!row || !row[0])
1641 if (strcmp(row[0], "current_stream"))
1642 send_va_buffer(fd, "%s\n", row[0]);
1647 mysql_free_result(result);
1651 /* query stream definition */
1652 int com_strq(int fd, int argc, char * const * argv)
1660 ret = -E_GET_STREAM;
1661 name = get_current_stream();
1664 name = escaped_basename(argv[1]);
1669 query = make_message("select def from streams where name='%s'", name);
1671 result = get_result(query);
1676 row = mysql_fetch_row(result);
1677 if (!row || !row[0])
1679 /* no '\n' needed */
1680 ret = send_buffer(fd, row[0]);
1683 mysql_free_result(result);
1687 /* change stream / change stream and play */
1688 static int com_cs_csp(int fd, int argc, char * const * argv)
1690 int ret, stream_change;
1691 char *query, *stream = NULL;
1692 char *old_stream = get_current_stream();
1693 int csp = !strcmp(argv[0], "csp");
1695 ret = -E_MYSQL_SYNTAX;
1701 ret = send_va_buffer(fd, "%s\n", old_stream);
1705 /* test if stream is valid, no need to escape argv[1] */
1706 query = get_query(argv[1], NULL, 0);
1711 stream = escape_str(argv[1]);
1714 stream_change = strcmp(stream, old_stream);
1715 if (stream_change) {
1716 ret = change_stream(stream);
1719 refresh_selector_info();
1723 mmd->new_vss_status_flags |= VSS_PLAYING;
1725 mmd->new_vss_status_flags |= VSS_NEXT;
1736 int com_cs(int fd, int argc, char * const * argv)
1738 return com_cs_csp(fd, argc, argv);
1741 /* change stream and play */
1742 int com_csp(int fd, int argc, char * const * argv)
1744 return com_cs_csp(fd, argc, argv);
1747 /* score list / skip */
1748 static int com_sl_skip(int fd, int argc, char * const * argv)
1750 void *result = NULL;
1752 int ret, i, skip = !strcmp(argv[0], "skip");
1753 char *query, *stream, *tmp;
1754 unsigned int num_rows, num;
1757 return -E_MYSQL_SYNTAX;
1758 num = atoi(argv[1]);
1760 return -E_MYSQL_SYNTAX;
1762 stream = get_current_stream();
1764 return -E_GET_STREAM;
1766 stream = escape_str(argv[2]);
1770 tmp = get_query(stream, NULL, 0);
1773 return -E_GET_QUERY;
1774 query = make_message("%s limit %d", tmp, num);
1777 result = get_result(query);
1781 ret = -E_EMPTY_RESULT;
1782 num_rows = mysql_num_rows(result);
1785 for (i = 0; i < num_rows && i < num; i++) {
1786 row = mysql_fetch_row(result);
1788 send_va_buffer(fd, "Skipping %s\n", row[0]);
1789 update_audio_file(row[0]);
1791 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
1796 mysql_free_result(result);
1801 int com_sl(int fd, int argc, char * const * argv)
1803 return com_sl_skip(fd, argc, argv);
1807 int com_skip(int fd, int argc, char * const * argv)
1809 return com_sl_skip(fd, argc, argv);
1813 * update attributes of name
1815 static int update_atts(int fd, const char *name, char *atts)
1818 char *ebn, *q, *old, *new = NULL;
1822 ebn = escaped_basename(name);
1825 q = make_message("update data set %s where name = '%s'", atts, ebn);
1826 old = get_atts(ebn, 0);
1827 send_va_buffer(fd, "old: %s\n", old);
1829 ret = real_query(q);
1833 new = get_atts(ebn, 0);
1834 ret = send_va_buffer(fd, "new: %s\n", new);
1844 int com_sa(int fd, int argc, char * const * argv)
1847 char *atts = NULL, *name;
1850 return -E_MYSQL_SYNTAX;
1851 for (i = 1; i < argc; i++) {
1853 char *esc, *tmp, *p;
1854 int len = strlen(argv[i]);
1858 switch (argv[i][len - 1]) {
1868 p = para_strdup(argv[i]);
1870 esc = escape_str(p);
1874 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
1877 atts = para_strcat(atts, tmp);
1883 if (i >= argc) { /* no name given, use current af */
1884 ret = -E_GET_AUDIO_FILE;
1885 if (!(name = get_current_audio_file()))
1887 ret = update_atts(fd, name, atts);
1891 for (; argv[i] && ret >= 0; i++)
1892 ret = update_atts(fd, argv[i], atts);
1894 refresh_selector_info();
1903 int com_cam(int fd, int argc, char * const * argv)
1905 char *name = NULL, *meta = NULL, *atts = NULL;
1909 return -E_MYSQL_SYNTAX;
1910 if (!(name = escaped_basename(argv[1])))
1913 if (!(atts = get_atts(name, 1)))
1916 if (!(meta = get_meta(name, 0)))
1918 for (i = 2; i < argc; i++) {
1921 if (!(ebn = escaped_basename(argv[i])))
1923 ret = send_va_buffer(fd, "updating %s\n", ebn);
1928 q = make_message("update data set %s where name = '%s'",
1930 if ((ret = update_atts(fd, ebn, atts)) >= 0)
1931 ret = real_query(q);
1951 static int com_vrfy_clean(int fd, int argc, __a_unused char * const * argv)
1954 int ret, vrfy_mode = strcmp(argv[0], "clean");
1955 void *result = NULL;
1958 my_ulonglong num_rows, top = 0, left = 0, right = 0;
1961 return -E_MYSQL_SYNTAX;
1963 result = get_result("select data.name from data left join dir on "
1964 "dir.name = data.name where dir.name is NULL");
1967 num_rows = mysql_num_rows(result);
1969 ret = send_buffer(fd, "No invalid entries\n");
1973 send_va_buffer(fd, "found %lli invalid entr%s\n", num_rows,
1974 num_rows == 1? "y" : "ies");
1975 ret = print_results(fd, result, top, left, num_rows - 1, right);
1978 while ((row = mysql_fetch_row(result))) {
1983 escaped_name = escape_str(row[0]);
1986 send_va_buffer(fd, "deleting %s\n", escaped_name);
1987 query = make_message("delete from data where name = '%s'",
1989 ret = real_query(query);
1997 mysql_free_result(result);
2004 int com_vrfy(int fd, int argc, char * const * argv)
2006 return com_vrfy_clean(fd, argc, argv);
2012 int com_clean(int fd, int argc, char * const * argv)
2014 return com_vrfy_clean(fd, argc, argv);
2017 static FILE *out_file;
2019 static int mysql_write_tmp_file(const char *dir, const char *name)
2021 int ret = -E_TMPFILE;
2022 char *msg = make_message("%s\t%s\n", dir, name);
2023 if (fputs(msg, out_file) != EOF)
2032 int com_upd(int fd, int argc, __a_unused char * const * argv)
2034 char *tempname = NULL, *query = NULL;
2035 int ret, out_fd = -1, num = 0;
2036 void *result = NULL;
2037 unsigned int num_rows;
2041 return -E_MYSQL_SYNTAX;
2043 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2044 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2048 out_file = fdopen(out_fd, "w");
2053 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2055 num = ftell(out_file);
2057 * we have to make sure the file hit the disk before we call
2062 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2065 if ((ret = real_query("delete from dir")) < 0)
2067 query = make_message("load data infile '%s' ignore into table dir "
2068 "fields terminated by '\t' lines terminated by '\n' "
2069 "(dir, name)", tempname);
2070 ret = real_query(query);
2074 result = get_result("select dir.name from dir left join data on "
2075 "data.name = dir.name where data.name is NULL");
2079 num_rows = mysql_num_rows(result);
2081 ret = send_buffer(fd, "no new entries\n");
2084 while ((row = mysql_fetch_row(result))) {
2089 send_va_buffer(fd, "new entry: %s\n", row[0]);
2090 erow = escape_str(row[0]);
2093 query = make_message("insert into data (name, pic_id) values "
2094 "('%s','%s')", erow, "1");
2096 ret = real_query(query);
2109 mysql_free_result(result);
2113 static char **server_get_audio_file_list(unsigned int num)
2115 char **list = para_malloc((num + 1) * sizeof(char *));
2116 char *tmp, *query, *stream = get_current_stream();
2117 void *result = NULL;
2118 unsigned int num_rows;
2122 tmp = get_query(stream, NULL, 1);
2126 query = make_message("%s limit %d", tmp, num);
2128 result = get_result(query);
2132 num_rows = mysql_num_rows(result);
2135 for (i = 0; i < num_rows && i < num; i++) {
2136 row = mysql_fetch_row(result);
2137 if (!row || !row[0])
2139 list[i] = para_strdup(row[0]);
2152 mysql_free_result(result);
2157 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2158 * on errors. Called from parent on startup and also from com_cdb().
2160 static int init_mysql_server(void)
2162 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2165 mysql_ptr = mysql_init(NULL);
2167 PARA_CRIT_LOG("%s", "mysql init error\n");
2170 if (conf.mysql_port_arg < 0)
2171 return -E_MYSQL_SYNTAX;
2172 port = conf.mysql_port_arg;
2173 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg, port);
2174 if (!conf.mysql_user_arg)
2177 * If host is NULL a connection to the local host is assumed,
2178 * If user is NULL, the current user is assumed
2180 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2181 conf.mysql_host_arg,
2182 conf.mysql_user_arg,
2183 conf.mysql_passwd_arg,
2184 conf.mysql_database_arg,
2186 PARA_CRIT_LOG("%s", "connect error\n");
2189 PARA_INFO_LOG("%s", "success\n");
2193 /* mmd lock must be held */
2194 static void write_msg2mmd(int success)
2196 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2197 success < 0? PARA_STRERROR(-success) :
2198 "successfully connected to mysql server",
2199 success < 0? "" : mysql_get_server_info(mysql_ptr));
2202 /* create database */
2203 int com_cdb(int fd, int argc, char * const * argv)
2209 PARA_INFO_LOG("%s", "closing database\n");
2210 mysql_close(mysql_ptr);
2212 /* dont use any database */
2213 conf.mysql_database_arg = NULL; /* leak? */
2214 ret = -E_MYSQL_INIT;
2215 if (init_mysql_server() < 0 || !mysql_ptr)
2218 conf.mysql_database_arg = para_strdup("paraslash");
2221 conf.mysql_database_arg = escape_str(argv[1]);
2222 if (!conf.mysql_database_arg)
2225 query = make_message("create database %s", conf.mysql_database_arg);
2226 ret = real_query(query);
2230 /* reconnect with database just created */
2231 mysql_close(mysql_ptr);
2232 ret = -E_MYSQL_INIT;
2233 if (init_mysql_server() < 0 || !mysql_ptr)
2239 if (real_query("create table data (name varchar(255) binary not null "
2241 "lastplayed datetime not null default "
2243 "numplayed int not null default 0, "
2244 "pic_id bigint unsigned not null default 1)") < 0)
2246 if (real_query("create table dir (name varchar(255) binary not null "
2247 "primary key, dir varchar(255) default null)") < 0)
2249 if (real_query("create table pics ("
2250 "id bigint(20) unsigned not null primary key "
2252 "name varchar(255) binary not null, "
2253 "pic mediumblob not null)") < 0)
2255 if (real_query("create table streams ("
2256 "name varchar(255) binary not null primary key, "
2257 "def blob not null)") < 0)
2259 if (real_query("insert into streams (name, def) values "
2260 "('current_stream', '(none)')") < 0)
2262 ret = send_va_buffer(fd, "successfully created database %s\n",
2263 conf.mysql_database_arg);
2268 static void shutdown_connection(void)
2273 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2274 mysql_close(mysql_ptr);
2278 ret = mutex_destroy(mysql_lock);
2280 PARA_ERROR_LOG("%s\n", PARA_STRERROR(-ret));
2286 * the init function of the mysql-based audio file selector
2288 * \param db pointer to the struct to initialize
2290 * Check the command line options and initialize all function pointers of \a
2291 * db. Connect to the mysql server and initialize the info string.
2293 * \return This function returns success even if it could not connect
2294 * to the mysql server. This is because the connect is expected to fail
2295 * if there the paraslash database is not yet created. This gives the
2296 * user a chance to send the "cdb" to create the database.
2298 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2301 int mysql_selector_init(struct audio_file_selector *db)
2305 if (!conf.mysql_passwd_given)
2306 return -E_NO_MYSQL_PASSWD;
2307 if (!conf.mysql_audio_file_dir_given)
2308 return -E_NO_AF_DIR;
2310 db->cmd_list = mysql_selector_cmds;
2311 db->get_audio_file_list = server_get_audio_file_list;
2312 db->update_audio_file = update_audio_file_server_handler;
2313 db->shutdown = shutdown_connection;
2318 ret = init_mysql_server();
2320 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2322 return 1; /* return success even if connect failed to give the
2323 * user the chance to exec com_cdb