2 * Copyright (C) 1999-2006 Andre Noll <maan@systemlinux.org>
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.
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.
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.
19 /** \file mysql_selector.c para_server's mysql-based audio file selector */
21 /** \cond some internal constants */
22 #define MEDIUM_BLOB_SIZE 16777220 /* (2**24 + 4) */
23 #define BLOB_SIZE 65539 /* (2**16 + 3) */
25 #include "server.cmdline.h"
29 #include <mysql/mysql.h>
30 #include <mysql/mysql_version.h>
35 extern struct gengetopt_args_info conf;
36 /** pointer to the shared memory area */
37 extern struct misc_meta_data *mmd;
39 static void *mysql_ptr = NULL;
41 static int com_cam(int, int, char **);
42 static int com_cdb(int, int, char **);
43 static int com_cs(int, int, char **);
44 static int com_da(int, int, char **);
45 static int com_hist(int, int, char **);
46 static int com_info(int, int, char **);
47 static int com_laa(int, int, char **);
48 static int com_last(int, int, char **);
49 static int com_ls(int, int, char **);
50 static int com_mbox(int, int, char **);
51 static int com_mv(int, int, char **);
52 static int com_na(int, int, char **);
53 static int com_pic(int, int, char **);
54 static int com_picch(int, int, char **);
55 static int com_picdel(int, int, char **);
56 static int com_piclist(int, int, char **);
57 static int com_ps(int, int, char **);
58 static int com_rm_ne(int, int, char **);
59 static int com_sa(int, int, char **);
60 static int com_set(int, int, char **);
61 static int com_sl(int, int, char **);
62 static int com_stradd_picadd(int, int, char **);
63 static int com_streams(int, int, char **);
64 static int com_strdel(int, int, char **);
65 static int com_strq(int, int, char **);
66 static int com_summary(int, int, char **);
67 static int com_upd(int, int, char **);
68 static int com_us(int, int, char **);
69 static int com_verb(int, int, char **);
70 static int com_vrfy(int, int, char **);
72 static struct server_command cmds[] = {
76 .perms = DB_READ|DB_WRITE,
77 .description = "copy all metadata",
78 .synopsis = "cam source dest1 [dest2 ...]",
81 "Copy attributes and other meta data from source file to destination\n"
82 "file(s). Useful for files that have been renamed.\n"
88 .perms = DB_READ|DB_WRITE,
89 .description = "create database",
90 .synopsis = "cdb [name]",
93 "\tCreate database name containing the initial columns for basic\n"
94 "\tinteroperation with server. This command has to be used only once\n"
95 "\twhen you use the mysql audio file selector for the very first time.\n"
97 "\tThe optional name defaults to 'paraslash' if not given.\n"
103 .perms = DB_READ | DB_WRITE,
104 .description = "nuke invalid entries in database",
108 "If the vrfy command shows you any invalid entries in your database,\n"
109 "you can get rid of them with clean. Always run 'upd' and 'vrfy'\n"
110 "before running this command. Use with caution!\n"
116 .perms = AFS_WRITE | DB_READ | DB_WRITE,
117 .description = "change stream",
118 .synopsis = "cs [s]",
121 "Selects stream s or prints current stream when s was not given.\n"
127 .perms = AFS_WRITE | DB_READ,
128 .description = "change stream and play",
132 "Select stream s and start playing. If this results in a stream-change,\n"
133 "skip rest of current audio file.\n"
139 .perms = DB_READ | DB_WRITE,
140 .description = "drop attribute from database",
141 .synopsis = "da att",
144 "Use with caution. All info on attribute att will be lost.\n"
151 .description = "print history",
155 "Print list of all audio files together with number of days since each\n"
156 "file was last played.\n"
163 .description = "print database info",
164 .synopsis = "info [af]",
167 "print database informations for audio file af. Current audio file is\n"
168 "used if af is not given.\n"
175 .description = "list attributes",
176 .synopsis = "la [af]",
179 "List attributes of audio file af or of current audio file when invoked\n"
180 "without arguments.\n"
187 .description = "list available attributes",
191 "What should I say more?\n"
198 .description = "print list of audio files, ordered by lastplayed time",
199 .synopsis = "last [n]",
202 "The optional number n defaults to 10 if not specified.\n"
209 .description = "list all audio files that match a LIKE pattern",
210 .synopsis = "ls [pattern]",
213 "\tIf pattern was not given, print list of all audio files known\n"
214 "\tto the mysql selector. See the documentation of mysql\n"
215 "\tfor the definition of LIKE patterns.\n"
222 .description = "dump audio file list in mbox format",
223 .synopsis = "mbox [p]",
226 "\tDump list of audio files in mbox format (email) to stdout. If\n"
227 "\tthe optional pattern p is given, only those audio files,\n"
228 "\twhose basename match p are going to be included. Otherwise,\n"
229 "\tall files are selected.\n"
232 "\tThe mbox command can be used together with your favorite\n"
233 "\tmailer (this example uses mutt) for browsing the audio file\n"
236 "\t\tpara_client mbox > ~/para_mbox\n"
238 "\t\tmutt -F ~/.muttrc.para -f ~/para_mbox\n"
240 "\tFor playlists, you can use mutt's powerful pattern matching\n"
241 "\tlanguage to select files. If you like to tag all files\n"
242 "\tcontaining the pattern 'foo', type 'T', then '~s foo'.\n"
244 "\tWhen ready with the list, type ';|' (i.e., hit the semicolon\n"
245 "\tkey to apply the next mutt command to all tagged messages,\n"
246 "\tthen the pipe key) to pipe the selected \"mails\" to a\n"
247 "\tsuitable script which adds a paraslash stream where exactly\n"
248 "\tthese files are admissable or does whatever thou wilt.\n"
254 .perms = DB_READ | DB_WRITE,
255 .description = "rename entry in database",
256 .synopsis = "mv oldname newname",
259 "Rename oldname to newname. This updates the data table to reflect the\n"
260 "new name. All internal data (numplayed, lastplayed, picid,..) is kept.\n"
261 "If newname is a full path, the dir table is updated as well.\n"
267 .perms = DB_READ | DB_WRITE,
268 .description = "add new attribute to database",
269 .synopsis = "na att",
272 "This adds a column named att to your mysql database. att should only\n"
273 "contain letters and numbers, in paricular, '+' and '-' are not allowed.\n"
278 .handler = com_rm_ne,
279 .perms = DB_READ | DB_WRITE,
280 .description = "add new database entries",
281 .synopsis = "ne file1 [file2 [...]]",
284 "Add the given filename(s) to the database, where file1,... must\n"
285 "be full path names. This command might be much faster than 'upd'\n"
286 "if the number of given files is small.\n"
292 .perms = AFS_WRITE | DB_READ | DB_WRITE,
293 .description = "change to next stream",
297 "Cycle forwards through stream list.\n"
304 .description = "get picture by name or by identifier",
305 .synopsis = "pic [name]",
308 "\tDump jpg image that is associated to given audio file (current\n"
309 "\taudio file if not specified) to stdout. If name starts with\n"
310 "\t'#' it is interpreted as an identifier instead and the picture\n"
311 "\thaving that identifier is dumped to stdout.\n"
315 "\tpara_client pic '#123' > pic123.jpg\n"
320 .handler = com_stradd_picadd,
321 .perms = DB_READ | DB_WRITE,
322 .description = "add picture to database",
323 .synopsis = "picadd [picname]",
326 "\tRead jpeg file from stdin and store it as picname in database.\n"
330 "\tpara_client picadd foo.jpg < foo.jpg\n"
336 .perms = DB_READ | DB_WRITE,
337 .description = "associate a picture to file(s)",
338 .synopsis = "picass pic_id file1 [file2...]",
341 "Associate the picture given by pic_id to all given files.\n"
346 .handler = com_picch,
347 .perms = DB_READ | DB_WRITE,
348 .description = "change name of picture",
349 .synopsis = "picch id new_name",
352 "Asign new_name to picture with identifier id.\n"
357 .handler = com_picdel,
358 .perms = DB_READ | DB_WRITE,
359 .description = "delete picture from database",
360 .synopsis = "picdel id1 [id2...]",
363 "Delete each given picture from database.\n"
368 .handler = com_piclist,
370 .description = "print list of pictures",
371 .synopsis = "piclist",
374 "Print id, name and length of each picture contained in the database.\n"
380 .perms = AFS_WRITE | DB_READ | DB_WRITE,
381 .description = "change to previous stream",
385 "Cycle backwards through stream list.\n"
390 .handler = com_rm_ne,
391 .perms = DB_READ | DB_WRITE,
392 .description = "remove entries from database",
393 .synopsis = "rm name1 [name2 [...]]",
396 "Remove name1, name2, ... from the data table. Use with caution\n"
402 .perms = DB_READ | DB_WRITE,
403 .description = "set/unset attributes",
404 .synopsis = "sa at1<'+' | '-'> [at2<'+' | '-'> ] [af1 ...]",
408 "Set ('+') or unset ('-') attribute at1, at2 etc. for given list of\n"
409 "audio files. If no audio files were given the current audio file is\n"
412 "sa rock+ punk+ classic- LZ__Waldsterben.mp3\n"
414 "sets the 'rock' and the 'punk' attribute but unsets the 'classic'\n"
421 .perms = DB_READ | DB_WRITE,
422 .description = "skip subsequent audio files(s)",
423 .synopsis = "skip n [s]",
426 "Skip the next n audio files of stream s. This is equivalent to the\n"
427 "command 'sl n s', followed by 'us name' for each name the output of sl.\n"
434 .description = "print score list",
435 .synopsis = "sl n [s]",
438 "Print sorted list of maximal n lines. Each line is an admissible entry\n"
439 "with respect to stream s. The list is sorted by score-value which is\n"
440 "given by the definition of s. If s is not given, the current stream\n"
441 "is used. Example:\n"
445 "shows you the audio file the server would select right now.\n"
451 .perms = DB_READ | DB_WRITE,
452 .description = "set numplayed",
453 .synopsis = "snp number af1 [af2 ...]",
456 "Update the numplayed field in the data table for all given audio files.\n"
461 .handler = com_stradd_picadd,
462 .perms = DB_READ | DB_WRITE,
463 .description = "add stream",
464 .synopsis = "stradd s",
467 "Add stream s to the list of available streams. The stream definition\n"
468 "for s is read from stdin and is then sent to para_server. Example:\n"
470 " echo 'deny: NAME_LIKE(%Madonna%)' | para_client stradd no_madonna\n"
472 "adds the new stream 'no_madonna' to the list of available streams. A given\n"
473 "audio file is admissible for this stream iff its basename does not contain the\n"
474 "string 'Madonna'.\n"
480 .handler = com_strdel,
481 .perms = DB_READ | DB_WRITE,
482 .description = "delete stream",
483 .synopsis = "strdel s",
486 "Remove stream s from database.\n"
491 .handler = com_streams,
493 .description = "list streams",
494 .synopsis = "streams",
497 "Print list of available streams. Use 'cs' to switch to any of these.\n"
504 .description = "query stream definition",
505 .synopsis = "strq [s]",
508 "Print definition of stream s to stdout. Use current stream if s was\n"
514 .handler = com_summary,
516 .description = "list attributes",
517 .synopsis = "summary",
520 "\tPrint a list of attributes together with number of audio\n"
521 "\tfiles having that attribute set.\n"
527 .perms = DB_READ | DB_WRITE,
528 .description = "update database",
532 "This command uses the --audio_file_dir option of para_server to locate\n"
533 "your audio files. New files are then added to the mysql database. Use\n"
534 "this command if you got new files or if you have moved some files\n"
541 .perms = DB_READ | DB_WRITE,
542 .description = "update lastplayed time",
543 .synopsis = "us name",
546 "Update lastplayed time without actually playing the thing.\n"
552 .perms = DB_READ | DB_WRITE,
553 .description = "send verbatim sql query",
554 .synopsis = "verb cmd",
557 "Send cmd to mysql server. For expert/debugging only. Note that cmd\n"
558 "usually must be escaped. Use only if you know what you are doing!\n"
565 .description = "list invalid entries in database",
569 "Show what clean would delete. Run 'upd' before this command to make\n"
570 "sure your database is up to date.\n"
578 static struct para_macro macro_list[] = {
579 { .name = "IS_N_SET",
580 .replacement = "(data.%s != '1')"
583 .replacement = "(data.%s = '1')"
586 .replacement = "%sdata.Pic_Id"
589 .replacement = "(data.name like '%s')"
591 .name = "LASTPLAYED",
592 .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())"
593 "-UNIX_TIMESTAMP(data.Lastplayed))/60)"
596 .replacement = "%sdata.Numplayed"
602 static int real_query(const char *query)
606 PARA_DEBUG_LOG("%s\n", query);
607 if (mysql_real_query(mysql_ptr, query, strlen(query))) {
608 PARA_ERROR_LOG("real_query error (%s)\n",
609 mysql_error(mysql_ptr));
616 * Use open connection given by mysql_ptr to query server. Returns a
617 * result pointer on succes and NULL on errors
619 static struct MYSQL_RES *get_result(const char *query)
623 if (real_query(query) < 0)
625 result = mysql_store_result(mysql_ptr);
627 PARA_ERROR_LOG("%s", "store_result error\n");
631 * write input from fd to dynamically allocated char array,
632 * but maximal max_size byte. Return size.
634 static int fd2buf(int fd, char **buf_ptr, size_t max_size)
636 const size_t chunk_size = 1024;
638 char *buf = para_malloc(size * sizeof(char)), *p = buf;
641 while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) {
643 if ((p - buf) + chunk_size >= size) {
647 if (size > max_size) {
651 tmp = para_realloc(buf, size);
666 static char *escape_blob(char* old, int size)
670 if (!mysql_ptr || size < 0)
672 new = para_malloc(2 * size * sizeof(char) + 1);
673 mysql_real_escape_string(mysql_ptr, new, old, size);
677 static char *escape_str(char* old)
679 return escape_blob(old, strlen(old));
682 static char *escaped_basename(const char *name)
684 char *esc, *bn = para_basename(name);
688 esc = escape_str(bn);
696 static int com_na(__unused int fd, int argc, char *argv[])
702 return -E_MYSQL_SYNTAX;
703 tmp = escape_str(argv[1]);
706 q = make_message("alter table data add %s char(1) "
707 "not null default 0", tmp);
717 static int com_da(__unused int fd, int argc, char *argv[])
723 return -E_MYSQL_SYNTAX;
724 tmp = escape_str(argv[1]);
727 q = make_message("alter table data drop %s", tmp);
735 static int com_stradd_picadd(int fd, int argc, char *argv[])
737 char *blob = NULL, *esc_blob = NULL, *q = NULL, *tmp = NULL;
738 const char *fmt, *del_fmt;
739 int ret, stradd = strcmp(argv[0], "picadd");
743 return -E_MYSQL_SYNTAX;
744 if (strlen(argv[1]) >= MAXLINE - 1)
745 return -E_NAMETOOLONG;
750 fmt = "insert into streams (name, def) values ('%s','%s')";
751 del_fmt="delete from streams where name='%s'";
753 size = MEDIUM_BLOB_SIZE;
754 fmt = "insert into pics (name, pic) values ('%s','%s')";
755 del_fmt="delete from pics where pic='%s'";
757 tmp = escape_str(argv[1]);
760 q = make_message(del_fmt, tmp);
766 if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0))
768 if ((ret = fd2buf(fd, &blob, size)) < 0)
774 esc_blob = escape_blob(blob, size);
777 tmp = escape_str(argv[1]);
780 q = make_message(fmt, tmp, esc_blob);
791 * print results to fd
793 static int print_results(int fd, void *result,
794 unsigned int top, unsigned int left,
795 unsigned int bottom, unsigned int right)
801 for (i = top; i <= bottom; i++) {
802 row = mysql_fetch_row(result);
805 for (j = left; j <= right; j++) {
806 ret = send_va_buffer(fd, j == left? "%s" : "\t%s",
807 row[j]? row[j] : "NULL");
811 ret = send_buffer(fd, "\n");
821 static int com_verb(int fd, int argc, char *argv[])
825 unsigned int num_rows, num_fields;
829 return -E_MYSQL_SYNTAX;
830 tmp = escape_str(argv[1]);
833 result = get_result(tmp);
836 /* return success, because it's ok to have no results */
838 num_fields = mysql_field_count(mysql_ptr);
839 num_rows = mysql_num_rows(result);
841 if (num_fields && num_rows)
842 ret = print_results(fd, result, 0, 0, num_rows - 1,
844 mysql_free_result(result);
848 /* returns NULL on errors or if there are no atts defined yet */
849 static void *get_all_attributes(void)
851 void *result = get_result("desc data");
852 unsigned int num_rows;
856 num_rows = mysql_num_rows(result);
858 mysql_free_result(result);
861 mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */
866 * list all attributes
868 static int com_laa(int fd, int argc, __unused char *argv[])
874 return -E_MYSQL_SYNTAX;
875 result = get_all_attributes();
878 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0);
879 mysql_free_result(result);
886 static int com_hist(int fd, int argc, char *argv[]) {
890 unsigned int num_rows;
893 return -E_MYSQL_SYNTAX;
895 char *tmp = escape_str(argv[1]);
898 atts = make_message("where %s = '1'", tmp);
901 atts = para_strdup(NULL);
903 q = make_message("select name, to_days(now()) - to_days(lastplayed) from "
904 "data %s order by lastplayed", atts);
906 result = get_result(q);
910 num_rows = mysql_num_rows(result);
913 ret = print_results(fd, result, 0, 0, num_rows - 1, 1);
914 mysql_free_result(result);
919 * get last num audio files
921 static int com_last(int fd, int argc, char *argv[])
932 return -E_MYSQL_SYNTAX;
933 q = make_message("select name from data order by lastplayed desc "
935 result = get_result(q);
939 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0);
940 mysql_free_result(result);
944 static int com_mbox(int fd, int argc, char *argv[])
949 unsigned int num_rows, num_fields;
950 char *query = para_strdup("select concat('From foo@localhost ', "
951 "date_format(Lastplayed, '%a %b %e %T %Y'), "
952 "'\nReceived: from\nTo: bar\n");
955 result = get_all_attributes();
959 while ((row = mysql_fetch_row(result))) {
964 tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query,
969 query = para_strcat(query,
977 char *esc = escape_str(argv[1]), *tmp;
981 tmp = make_message("%s where name LIKE '%s'", query, esc);
986 mysql_free_result(result);
988 result = get_result(query);
991 ret = -E_EMPTY_RESULT;
992 num_fields = mysql_field_count(mysql_ptr);
993 num_rows = mysql_num_rows(result);
994 if (!num_fields || !num_rows)
996 ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1);
1000 mysql_free_result(result);
1004 /* get attributes by name. If verbose is not 0, get_a writes a string
1005 * into atts of the form 'att1="0",att2="1"', which is used in com_cam
1006 * for contructing a mysql update query.
1007 * never returns NULL in *NON VERBOSE* mode
1009 static char *get_atts(char *name, int verbose)
1011 char *atts = NULL, *buf, *ebn;
1012 void *result = NULL, *result2 = NULL;
1013 MYSQL_ROW row, row2;
1015 unsigned int num_fields;
1018 result2 = get_all_attributes();
1022 if (!(ebn = escaped_basename(name)))
1024 buf = make_message("select * from data where name='%s'", ebn);
1027 result = get_result(buf);
1031 ret = -E_EMPTY_RESULT;
1032 num_fields = mysql_num_fields(result);
1035 mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */
1036 row = mysql_fetch_row(result);
1040 for (i = 4; i < num_fields; i++) {
1041 int is_set = row[i] && !strcmp(row[i], "1");
1042 row2 = mysql_fetch_row(result2);
1043 if (!row2 || !row2[0])
1045 if (atts && (verbose || is_set))
1046 atts = para_strcat(atts, verbose? "," : " ");
1047 if (is_set || verbose)
1048 atts = para_strcat(atts, row2[0]);
1050 atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\"");
1055 mysql_free_result(result2);
1057 mysql_free_result(result);
1058 if (!atts && !verbose)
1059 atts = para_strdup("(none)");
1063 /* never returns NULL in verbose mode */
1064 static char *get_meta(char *name, int verbose)
1067 void *result = NULL;
1068 char *ebn, *q, *ret = NULL;
1069 const char *verbose_fmt =
1070 "select concat('lastplayed: ', "
1071 "(to_days(now()) - to_days(lastplayed)),"
1072 "' day(s). numplayed: ', numplayed, "
1073 "', pic: ', pic_id) "
1074 "from data where name = '%s'";
1075 /* is that really needed? */
1076 const char *fmt = "select concat('lastplayed=\\'', lastplayed, "
1077 "'\\', numplayed=\\'', numplayed, "
1078 "'\\', pic_id=\\'', pic_id, '\\'') "
1079 "from data where name = '%s'";
1081 if (!(ebn = escaped_basename(name)))
1083 q = make_message(verbose? verbose_fmt : fmt, ebn);
1085 result = get_result(q);
1089 row = mysql_fetch_row(result);
1090 if (!row || !row[0])
1092 ret = para_strdup(row[0]);
1095 mysql_free_result(result);
1096 if (!ret && verbose)
1097 ret = para_strdup("(not yet played)");
1101 static char *get_dir(char *name)
1103 char *ret = NULL, *q, *ebn;
1107 if (!(ebn = escaped_basename(name)))
1109 q = make_message("select dir from dir where name = '%s'", ebn);
1111 result = get_result(q);
1115 row = mysql_fetch_row(result);
1117 ret = para_strdup(row[0]);
1118 mysql_free_result(result);
1122 /* never returns NULL */
1123 static char *get_current_stream(void)
1127 void *result = get_result("select def from streams where "
1128 "name = 'current_stream'");
1132 row = mysql_fetch_row(result);
1133 if (!row || !row[0])
1135 ret = para_strdup(row[0]);
1136 mysql_free_result(result);
1140 mysql_free_result(result);
1141 return para_strdup("(none)");
1145 * Read stream definition of stream streamname and construct mysql
1146 * query. Return NULL on errors. If streamname is NULL, use current
1147 * stream. If that is also NULL, use query that selects everything.
1148 * If filename is NULL, query will list everything, otherwise only
1149 * the score of given file.
1151 static char *get_query(char *streamname, char *filename, int with_path)
1153 char *accept_opts = NULL, *deny_opts = NULL, *score = NULL;
1154 char *where_clause, *order, *query;
1155 char command[255] = ""; /* buffer for sscanf */
1159 char *select_clause = NULL;
1161 tmp = get_current_stream();
1163 tmp = escape_str(streamname);
1164 if (!strcmp(tmp, "(none)")) {
1167 char *ret, *ebn = escaped_basename(filename);
1168 ret = make_message("select to_days(now()) - "
1169 "to_days(lastplayed) from data "
1170 "where name = '%s'", ebn);
1175 return make_message(
1176 "select concat(dir.dir, '/', dir.name) "
1177 "from data, dir where dir.name = data.name "
1178 "order by data.lastplayed"
1180 return make_message(
1181 "select name from data where name is not NULL "
1182 "order by lastplayed"
1186 query = make_message("select def from streams where name = '%s'",
1188 result = get_result(query);
1193 row = mysql_fetch_row(result);
1194 if (!row || !row[0])
1199 char *arg, *line = end;
1201 if (!(end = strchr(line, '\n')))
1205 if (sscanf(line, "%200s%n", command, &n) < 1)
1208 if (!strcmp(command, "accept:")) {
1209 char *tmp2 = s_a_r_list(macro_list, arg);
1211 accept_opts = para_strcat(
1212 accept_opts, " or ");
1213 accept_opts = para_strcat(accept_opts, tmp2);
1217 if (!strcmp(command, "deny:")) {
1218 char *tmp2 = s_a_r_list(macro_list, arg);
1220 deny_opts = para_strcat(deny_opts, " or ");
1221 deny_opts = para_strcat(deny_opts, tmp2);
1225 if (!score && !strcmp(command, "score:"))
1226 score = s_a_r_list(macro_list, arg);
1229 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
1234 char *ebn = escaped_basename(filename);
1237 select_clause = make_message("select %s from data ", score);
1239 where_clause = make_message( "where name = '%s' ", ebn);
1241 order = para_strdup("");
1244 select_clause = para_strdup(with_path?
1245 "select concat(dir.dir, '/', dir.name) from data, dir "
1246 "where dir.name = data.name "
1248 "select name from data where name is not NULL");
1249 order = make_message("order by -(%s)", score);
1251 if (accept_opts && deny_opts) {
1252 where_clause = make_message("and ((%s) and not (%s)) ",
1253 accept_opts, deny_opts);
1256 if (accept_opts && !deny_opts) {
1257 where_clause = make_message("and (%s) ", accept_opts);
1260 if (!accept_opts && deny_opts) {
1261 where_clause = make_message("and not (%s) ", deny_opts);
1264 where_clause = para_strdup("");
1266 query = make_message("%s %s %s", select_clause, where_clause, order);
1268 free(select_clause);
1276 mysql_free_result(result);
1283 * This is called from server and from some commands. Name must not be NULL
1284 * Never returns NULL.
1286 static char *get_selector_info(char *name)
1288 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
1289 void *result = NULL;
1290 MYSQL_ROW row = NULL;
1293 return para_strdup("(none)");
1294 stream = get_current_stream();
1295 meta = get_meta(name, 1);
1296 atts = get_atts(name, 0);
1297 dir = get_dir(name);
1299 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
1302 result = get_result(query);
1305 row = mysql_fetch_row(result);
1307 info = make_message("dbinfo1:dir: %s\n"
1308 "dbinfo2:stream: %s, %s, score: %s\n"
1310 dir? dir : "(not contained in table)",
1312 (result && row && row[0])? row[0] : "(no score)",
1323 mysql_free_result(result);
1328 /* might return NULL */
1329 static char *get_current_audio_file(void)
1333 name = para_basename(mmd->filename);
1339 /* print database info */
1340 static int com_info(int fd, int argc, char *argv[])
1342 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
1343 int ret, com_la = strcmp(argv[0], "info");
1346 ret = -E_GET_AUDIO_FILE;
1347 if (!(name = get_current_audio_file()))
1349 ret = send_va_buffer(fd, "%s\n", name);
1354 if (!(name = escaped_basename(argv[1])))
1357 meta = get_meta(name, 1);
1358 atts = get_atts(name, 0);
1359 dir = get_dir(name);
1361 ret = send_va_buffer(fd, "%s\n", atts);
1363 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
1364 dir? dir : "(not contained in table)", meta, atts);
1373 static int change_stream(const char *stream)
1377 query = make_message("update streams set def='%s' "
1378 "where name = 'current_stream'", stream);
1379 ret = real_query(query);
1384 static int get_pic_id_by_name(char *name)
1387 void *result = NULL;
1391 if (!(ebn = escaped_basename(name)))
1393 q = make_message("select pic_id from data where name = '%s'", ebn);
1395 result = get_result(q);
1399 row = mysql_fetch_row(result);
1403 mysql_free_result(result);
1407 static int remove_entry(const char *name)
1409 char *q, *ebn = escaped_basename(name);
1410 int ret = -E_ESCAPE;
1414 q = make_message("delete from data where name = '%s'", ebn);
1415 real_query(q); /* ignore errors */
1417 q = make_message("delete from dir where name = '%s'", ebn);
1418 real_query(q); /* ignore errors */
1426 static int add_entry(const char *name)
1428 char *q, *dn, *ebn = NULL, *edn = NULL;
1431 if (!name || !*name)
1432 return -E_MYSQL_SYNTAX;
1433 ebn = escaped_basename(name);
1436 ret = -E_MYSQL_SYNTAX;
1437 dn = para_dirname(name);
1441 edn = escape_str(dn);
1445 q = make_message("insert into data (name, pic_id) values "
1446 "('%s', '%s')", ebn, "1");
1447 ret = real_query(q);
1448 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1452 q = make_message("insert into dir (name, dir) values "
1453 "('%s', '%s')", ebn, edn);
1454 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1455 ret = real_query(q);
1466 * remove/add entries
1468 static int com_rm_ne(__unused int fd, int argc, char *argv[])
1470 int ne = !strcmp(argv[0], "ne");
1473 return -E_MYSQL_SYNTAX;
1474 for (i = 1; i < argc; i++) {
1475 ret = remove_entry(argv[i]);
1480 ret = add_entry(argv[i]);
1490 static int com_mv(__unused int fd, int argc, char *argv[])
1492 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1496 return -E_MYSQL_SYNTAX;
1498 ebn1 = escaped_basename(argv[1]);
1499 ebn2 = escaped_basename(argv[2]);
1500 if (!ebn1 || !ebn2 | !*ebn1 || !*ebn2)
1502 ret = -E_MYSQL_SYNTAX;
1503 if (!strcmp(ebn1, ebn2))
1505 remove_entry(argv[2]); /* no need to escape, ignore error */
1506 q = make_message("update data set name = '%s' where name = '%s'",
1508 ret = real_query(q);
1512 ret = -E_AUDIO_FILE;
1513 if (!mysql_affected_rows(mysql_ptr))
1515 q = make_message("update dir set name = '%s' where name = '%s'",
1517 ret = real_query(q);
1522 dn = para_dirname(argv[2]);
1526 edn = escape_str(dn);
1533 q = make_message("update dir set dir = '%s' where name = '%s'",
1535 ret = real_query(q);
1545 * picass: associate pic to audio file
1546 * snp: set numplayed
1548 static int com_set(__unused int fd, int argc, char *argv[])
1553 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1556 return -E_MYSQL_SYNTAX;
1558 for (i = 2; i < argc; i++) {
1559 ebn = escaped_basename(argv[i]);
1562 q = make_message("update data set %s = %lu "
1563 "where name = '%s'", field, id, ebn);
1565 ret = real_query(q);
1574 * picch: change entry's name in pics table
1576 static int com_picch(__unused int fd, int argc, char *argv[])
1583 return -E_MYSQL_SYNTAX;
1586 tmp = escape_str(argv[2]);
1589 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1591 ret = real_query(q);
1597 * piclist: print list of pics in db
1599 static int com_piclist(__unused int fd, int argc, __unused char *argv[])
1601 void *result = NULL;
1603 unsigned long *length;
1607 return -E_MYSQL_SYNTAX;
1608 result = get_result("select id,name,pic from pics order by id");
1611 while ((row = mysql_fetch_row(result))) {
1612 length = mysql_fetch_lengths(result);
1613 if (!row || !row[0] || !row[1] || !row[2])
1615 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1621 mysql_free_result(result);
1626 * picdel: delete picture from database
1628 static int com_picdel(int fd, int argc, char *argv[])
1636 return -E_MYSQL_SYNTAX;
1637 for (i = 1; i < argc; i++) {
1639 q = make_message("delete from pics where id = %lu", id);
1640 ret = real_query(q);
1644 aff = mysql_affected_rows(mysql_ptr);
1646 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1651 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1652 ret = real_query(q);
1658 * pic: get picture by name or by number
1660 static int com_pic(int fd, int argc, char *argv[])
1662 void *result = NULL;
1664 unsigned long *length, id;
1666 char *q, *name = NULL;
1669 ret = -E_GET_AUDIO_FILE;
1670 name = get_current_audio_file();
1673 name = escaped_basename(argv[1]);
1678 id = atoi(name + 1);
1680 id = get_pic_id_by_name(name);
1684 q = make_message("select pic from pics where id = '%lu'", id);
1685 result = get_result(q);
1689 row = mysql_fetch_row(result);
1691 if (!row || !row[0])
1693 length = mysql_fetch_lengths(result);
1694 ret = send_bin_buffer(fd, row[0], *length);
1696 mysql_free_result(result);
1701 static int com_strdel(__unused int fd, int argc, char *argv[])
1707 return -E_MYSQL_SYNTAX;
1708 tmp = escape_str(argv[1]);
1711 q = make_message("delete from streams where name='%s'", tmp);
1713 ret = real_query(q);
1721 static int com_ls(int fd, int argc, char *argv[])
1726 unsigned int num_rows;
1729 return -E_MYSQL_SYNTAX;
1731 char *tmp = escape_str(argv[1]);
1734 q = make_message("select name from data where name like '%s'",
1738 q = para_strdup("select name from data");
1739 result = get_result(q);
1743 num_rows = mysql_num_rows(result);
1746 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1747 mysql_free_result(result);
1754 static int com_summary(__unused int fd, int argc, __unused char *argv[])
1759 void *result2 = NULL;
1760 const char *fmt = "select count(name) from data where %s='1'";
1761 int ret = -E_NORESULT;
1764 return -E_MYSQL_SYNTAX;
1765 result = get_all_attributes();
1768 while ((row = mysql_fetch_row(result))) {
1775 buf = make_message(fmt, row[0]);
1776 result2 = get_result(buf);
1781 row2 = mysql_fetch_row(result2);
1782 if (!row2 || !row2[0])
1784 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1791 mysql_free_result(result2);
1793 mysql_free_result(result);
1797 static int get_numplayed(char *name)
1801 const char *fmt = "select numplayed from data where name = '%s'";
1802 char *buf = make_message(fmt, name);
1803 int ret = -E_NORESULT;
1805 result = get_result(buf);
1810 row = mysql_fetch_row(result);
1811 if (!row || !row[0])
1816 mysql_free_result(result);
1820 static int update_audio_file(char *name)
1823 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1824 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1826 char *ebn = escaped_basename(name);
1831 q = make_message(fmt1, ebn);
1832 ret = real_query(q);
1836 ret = get_numplayed(ebn);
1839 q = make_message(fmt2, ret + 1, ebn);
1840 ret = real_query(q);
1847 /* If called as child, mmd_lock must be held */
1848 static void update_mmd(char *info)
1850 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1851 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1852 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1855 static void update_audio_file_server_handler(char *name)
1858 info = get_selector_info(name);
1861 update_audio_file(name);
1864 static int com_us(__unused int fd, int argc, char *argv[])
1870 return -E_MYSQL_SYNTAX;
1871 tmp = escape_str(argv[1]);
1874 ret = update_audio_file(argv[1]);
1879 static void refresh_selector_info(void)
1881 char *name = get_current_audio_file();
1886 info = get_selector_info(name);
1894 /* select previous/next stream */
1895 static int com_ps(__unused int fd, int argc, char *argv[])
1897 char *query, *stream = get_current_stream();
1898 void *result = get_result("select name from streams");
1900 int match = -1, ret, i;
1901 unsigned int num_rows;
1904 return -E_MYSQL_SYNTAX;
1908 num_rows = mysql_num_rows(result);
1909 ret = -E_EMPTY_RESULT;
1913 for (i = 0; i < num_rows; i++) {
1914 row = mysql_fetch_row(result);
1915 if (!row || !row[0])
1917 if (!strcmp(row[0], "current_stream"))
1919 if (!strcmp(row[0], stream)) {
1927 if (!strcmp(argv[0], "ps"))
1928 i = match > 0? match - 1 : num_rows - 1;
1930 i = match < num_rows - 1? match + 1 : 0;
1932 mysql_data_seek(result, i);
1933 row = mysql_fetch_row(result);
1934 if (!row || !row[0])
1936 if (!strcmp(row[0], "current_stream")) {
1937 if (!strcmp(argv[0], "ps")) {
1939 i = i < 0? i + num_rows : i;
1942 i = i > num_rows - 1? i - num_rows : i;
1944 mysql_data_seek(result, i);
1945 row = mysql_fetch_row(result);
1946 if (!row || !row[0])
1949 query = make_message("update streams set def='%s' where name = "
1950 "'current_stream'", row[0]);
1951 ret = real_query(query);
1953 refresh_selector_info();
1957 mysql_free_result(result);
1962 static int com_streams(int fd, int argc, __unused char *argv[])
1964 unsigned int num_rows;
1965 int i, ret = -E_NORESULT;
1969 if (argc > 1 && strcmp(argv[1], "current_stream"))
1970 return -E_MYSQL_SYNTAX;
1972 char *cs = get_current_stream();
1973 ret = send_va_buffer(fd, "%s\n", cs);
1977 result = get_result("select name from streams");
1980 num_rows = mysql_num_rows(result);
1985 for (i = 0; i < num_rows; i++) {
1986 row = mysql_fetch_row(result);
1987 if (!row || !row[0])
1989 if (strcmp(row[0], "current_stream"))
1990 send_va_buffer(fd, "%s\n", row[0]);
1995 mysql_free_result(result);
1999 /* query stream definition */
2000 static int com_strq(int fd, int argc, char *argv[])
2008 ret = -E_GET_STREAM;
2009 name = get_current_stream();
2012 name = escaped_basename(argv[1]);
2017 query = make_message("select def from streams where name='%s'", name);
2019 result = get_result(query);
2024 row = mysql_fetch_row(result);
2025 if (!row || !row[0])
2027 /* no '\n' needed */
2028 ret = send_buffer(fd, row[0]);
2031 mysql_free_result(result);
2035 /* change stream / change stream and play */
2036 static int com_cs(int fd, int argc, char *argv[])
2038 int ret, stream_change;
2039 char *query, *stream = NULL;
2040 char *old_stream = get_current_stream();
2041 int csp = !strcmp(argv[0], "csp");
2044 ret = -E_MYSQL_SYNTAX;
2047 ret = send_va_buffer(fd, "%s\n", old_stream);
2051 /* test if stream is valid, no need to escape argv[1] */
2052 query = get_query(argv[1], NULL, 0);
2057 stream = escape_str(argv[1]);
2060 stream_change = strcmp(stream, old_stream);
2061 if (stream_change) {
2062 ret = change_stream(stream);
2065 refresh_selector_info();
2069 mmd->new_afs_status_flags |= AFS_PLAYING;
2071 mmd->new_afs_status_flags |= AFS_NEXT;
2084 static int com_sl(int fd, int argc, char *argv[])
2086 void *result = NULL;
2088 int ret, i, skip = !strcmp(argv[0], "skip");
2089 char *query, *stream, *tmp;
2090 unsigned int num_rows, num;
2093 return -E_MYSQL_SYNTAX;
2094 num = atoi(argv[1]);
2096 return -E_MYSQL_SYNTAX;
2097 stream = (argc == 2)? get_current_stream() : escape_str(argv[2]);
2098 tmp = get_query(stream, NULL, 0);
2099 query = make_message("%s limit %d", tmp, num);
2106 result = get_result(query);
2110 ret = -E_EMPTY_RESULT;
2111 num_rows = mysql_num_rows(result);
2114 for (i = 0; i < num_rows && i < num; i++) {
2115 row = mysql_fetch_row(result);
2117 send_va_buffer(fd, "Skipping %s\n", row[0]);
2118 update_audio_file(row[0]);
2120 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2125 mysql_free_result(result);
2130 * update attributes of name
2132 static int update_atts(int fd, char *name, char *atts)
2135 char *ebn, *q, *old, *new = NULL;
2139 ebn = escaped_basename(name);
2142 q = make_message("update data set %s where name = '%s'", atts, ebn);
2143 old = get_atts(ebn, 0);
2144 send_va_buffer(fd, "old: %s\n", old);
2146 ret = real_query(q);
2150 new = get_atts(ebn, 0);
2151 ret = send_va_buffer(fd, "new: %s\n", new);
2161 static int com_sa(int fd, int argc, char *argv[])
2164 char *atts = NULL, *name;
2167 return -E_MYSQL_SYNTAX;
2168 for (i = 1; i < argc; i++) {
2170 char *esc, *tmp, *p =argv[i];
2171 int len = strlen(p);
2175 switch (p[len - 1]) {
2186 esc = escape_str(p);
2189 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2192 atts = para_strcat(atts, tmp);
2198 if (i >= argc) { /* no name given, use current af */
2199 ret = -E_GET_AUDIO_FILE;
2200 if (!(name = get_current_audio_file()))
2202 ret = update_atts(fd, name, atts);
2206 for (; argv[i] && ret >= 0; i++)
2207 ret = update_atts(fd, argv[i], atts);
2209 refresh_selector_info();
2217 static int com_cam(int fd, int argc, char *argv[])
2219 char *name = NULL, *meta = NULL, *atts = NULL;
2223 return -E_MYSQL_SYNTAX;
2224 if (!(name = escaped_basename(argv[1])))
2227 if (!(atts = get_atts(name, 1)))
2230 if (!(meta = get_meta(name, 0)))
2232 for (i = 2; i < argc; i++) {
2235 if (!(ebn = escaped_basename(argv[i])))
2237 ret = send_va_buffer(fd, "updating %s\n", ebn);
2242 q = make_message("update data set %s where name = '%s'",
2244 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2245 ret = real_query(q);
2265 static int com_vrfy(int fd, int argc, __unused char *argv[])
2268 int ret, vrfy_mode = strcmp(argv[0], "clean");
2269 void *result = NULL;
2270 unsigned int num_rows;
2275 return -E_MYSQL_SYNTAX;
2277 result = get_result("select data.name from data left join dir on "
2278 "dir.name = data.name where dir.name is NULL");
2281 num_rows = mysql_num_rows(result);
2283 ret = send_buffer(fd, "No invalid entries\n");
2287 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2288 num_rows == 1? "y" : "ies");
2289 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2292 while ((row = mysql_fetch_row(result))) {
2297 escaped_name = escape_str(row[0]);
2300 send_va_buffer(fd, "deleting %s\n", escaped_name);
2301 query = make_message("delete from data where name = '%s'",
2303 ret = real_query(query);
2311 mysql_free_result(result);
2315 static FILE *out_file;
2317 static int mysql_write_tmp_file(const char *dir, const char *name)
2319 int ret = -E_TMPFILE;
2320 char *msg = make_message("%s\t%s\n", dir, name);
2322 if (fputs(msg, out_file) != EOF)
2331 static int com_upd(int fd, int argc, __unused char *argv[])
2333 char *tempname = NULL, *query = NULL;
2334 int ret, out_fd = -1, num = 0;
2335 void *result = NULL;
2336 unsigned int num_rows;
2340 return -E_MYSQL_SYNTAX;
2342 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2343 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2347 out_file = fdopen(out_fd, "w");
2352 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2354 num = ftell(out_file);
2356 * we have to make sure the file hit the disk before we call
2361 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2364 if ((ret = real_query("delete from dir")) < 0)
2366 query = make_message("load data infile '%s' into table dir "
2367 "fields terminated by '\t' lines terminated by '\n' "
2368 "(dir, name)", tempname);
2369 ret = real_query(query);
2373 result = get_result("select dir.name from dir left join data on "
2374 "data.name = dir.name where data.name is NULL");
2378 num_rows = mysql_num_rows(result);
2380 ret = send_buffer(fd, "no new entries\n");
2383 while ((row = mysql_fetch_row(result))) {
2387 send_va_buffer(fd, "new entry: %s\n", row[0]);
2388 query = make_message("insert into data (name, pic_id) values "
2389 "('%s','%s')", row[0], "1");
2390 ret = real_query(query);
2403 mysql_free_result(result);
2407 static char **server_get_audio_file_list(unsigned int num)
2409 char **list = para_malloc((num + 1) * sizeof(char *));
2410 char *tmp, *query, *stream = get_current_stream();
2411 void *result = NULL;
2412 unsigned int num_rows;
2416 tmp = get_query(stream, NULL, 1);
2418 query = make_message("%s limit %d", tmp, num);
2422 result = get_result(query);
2425 num_rows = mysql_num_rows(result);
2428 for (i = 0; i < num_rows && i < num; i++) {
2429 row = mysql_fetch_row(result);
2430 if (!row || !row[0])
2432 list[i] = para_strdup(row[0]);
2447 mysql_free_result(result);
2452 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2453 * on errors. Called from parent on startup and also from com_cdb().
2455 static int init_mysql_server(void)
2457 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2459 mysql_ptr = mysql_init(NULL);
2461 PARA_CRIT_LOG("%s", "mysql init error\n");
2464 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2465 conf.mysql_port_arg);
2466 if (!conf.mysql_user_arg)
2469 * If host is NULL a connection to the local host is assumed,
2470 * If user is NULL, the current user is assumed
2472 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2473 conf.mysql_host_arg,
2474 conf.mysql_user_arg,
2475 conf.mysql_passwd_arg,
2476 conf.mysql_database_arg,
2477 conf.mysql_port_arg, NULL, 0))) {
2478 PARA_CRIT_LOG("%s", "connect error\n");
2481 PARA_INFO_LOG("%s", "success\n");
2485 /* mmd lock must be held */
2486 static void write_msg2mmd(int success)
2488 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2489 success < 0? PARA_STRERROR(-success) :
2490 "successfully connected to mysql server",
2491 success < 0? "" : mysql_get_server_info(mysql_ptr));
2494 /* create database */
2495 static int com_cdb(int fd, int argc, char *argv[])
2501 PARA_INFO_LOG("%s", "closing database\n");
2502 mysql_close(mysql_ptr);
2504 /* dont use any database */
2505 conf.mysql_database_arg = NULL; /* leak? */
2506 ret = -E_MYSQL_INIT;
2507 if (init_mysql_server() < 0 || !mysql_ptr)
2510 conf.mysql_database_arg = para_strdup("paraslash");
2512 conf.mysql_database_arg = escape_str(argv[1]);
2513 query = make_message("create database %s", conf.mysql_database_arg);
2514 ret = real_query(query);
2518 /* reconnect with database just created */
2519 mysql_close(mysql_ptr);
2520 ret = -E_MYSQL_INIT;
2521 if (init_mysql_server() < 0 || !mysql_ptr)
2527 if (real_query("create table data (name varchar(255) binary not null "
2529 "lastplayed datetime not null default "
2531 "numplayed int not null default 0, "
2532 "pic_id bigint unsigned not null default 1)") < 0)
2534 if (real_query("create table dir (name varchar(255) binary not null "
2535 "primary key, dir varchar(255) default null)") < 0)
2537 if (real_query("create table pics ("
2538 "id bigint(20) unsigned not null primary key "
2540 "name varchar(255) binary not null, "
2541 "pic mediumblob not null)") < 0)
2543 if (real_query("create table streams ("
2544 "name varchar(255) binary not null primary key, "
2545 "def blob not null)") < 0)
2547 if (real_query("insert into streams (name, def) values "
2548 "('current_stream', '(none)')") < 0)
2550 ret = send_va_buffer(fd, "successfully created database %s\n",
2551 conf.mysql_database_arg);
2556 static void shutdown_connection(void)
2559 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2560 mysql_close(mysql_ptr);
2566 * the init function of the mysql-based audio file selector
2568 * Check the command line options and initialize all function pointers of \a db.
2569 * Connect to the mysql server and initialize the info string.
2571 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2574 int mysql_selector_init(struct audio_file_selector *db)
2578 if (!conf.mysql_passwd_given)
2579 return -E_NO_MYSQL_PASSWD;
2580 if (!conf.mysql_audio_file_dir_given)
2581 return -E_NO_AF_DIR;
2583 db->cmd_list = cmds;
2584 db->get_audio_file_list = server_get_audio_file_list;
2585 db->update_audio_file = update_audio_file_server_handler;
2586 db->shutdown = shutdown_connection;
2587 ret = init_mysql_server();
2589 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2591 return 1; /* return success even if connect failed to give the
2592 * user the chance to exec com_cdb