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 /** pointer to the shared memory area */
36 extern struct misc_meta_data *mmd;
38 static void *mysql_ptr = NULL;
40 static int com_cam(int, int, char **);
41 static int com_cdb(int, int, char **);
42 static int com_cs(int, int, char **);
43 static int com_da(int, int, char **);
44 static int com_hist(int, int, char **);
45 static int com_info(int, int, char **);
46 static int com_laa(int, int, char **);
47 static int com_last(int, int, char **);
48 static int com_ls(int, int, char **);
49 static int com_mbox(int, int, char **);
50 static int com_mv(int, int, char **);
51 static int com_na(int, int, char **);
52 static int com_pic(int, int, char **);
53 static int com_picch(int, int, char **);
54 static int com_picdel(int, int, char **);
55 static int com_piclist(int, int, char **);
56 static int com_ps(int, int, char **);
57 static int com_rm_ne(int, int, char **);
58 static int com_sa(int, int, char **);
59 static int com_set(int, int, char **);
60 static int com_sl(int, int, char **);
61 static int com_stradd_picadd(int, int, char **);
62 static int com_streams(int, int, char **);
63 static int com_strdel(int, int, char **);
64 static int com_strq(int, int, char **);
65 static int com_summary(int, int, char **);
66 static int com_upd(int, int, char **);
67 static int com_us(int, int, char **);
68 static int com_verb(int, int, char **);
69 static int com_vrfy(int, int, char **);
71 static struct server_command cmds[] = {
75 .perms = DB_READ|DB_WRITE,
76 .description = "copy all metadata",
77 .synopsis = "cam source dest1 [dest2 ...]",
80 "Copy attributes and other meta data from source file to destination\n"
81 "file(s). Useful for files that have been renamed.\n"
87 .perms = DB_READ|DB_WRITE,
88 .description = "create database",
89 .synopsis = "cdb [name]",
92 "\tCreate database name containing the initial columns for basic\n"
93 "\tinteroperation with server. This command has to be used only once\n"
94 "\twhen you use the mysql audio file selector for the very first time.\n"
96 "\tThe optional name defaults to 'paraslash' if not given.\n"
102 .perms = DB_READ | DB_WRITE,
103 .description = "nuke invalid entries in database",
107 "If the vrfy command shows you any invalid entries in your database,\n"
108 "you can get rid of them with clean. Always run 'upd' and 'vrfy'\n"
109 "before running this command. Use with caution!\n"
115 .perms = AFS_WRITE | DB_READ | DB_WRITE,
116 .description = "change stream",
117 .synopsis = "cs [s]",
120 "Selects stream s or prints current stream when s was not given.\n"
126 .perms = AFS_WRITE | DB_READ,
127 .description = "change stream and play",
131 "Select stream s and start playing. If this results in a stream-change,\n"
132 "skip rest of current audio file.\n"
138 .perms = DB_READ | DB_WRITE,
139 .description = "drop attribute from database",
140 .synopsis = "da att",
143 "Use with caution. All info on attribute att will be lost.\n"
150 .description = "print history",
154 "Print list of all audio files together with number of days since each\n"
155 "file was last played.\n"
162 .description = "print database info",
163 .synopsis = "info [af]",
166 "print database informations for audio file af. Current audio file is\n"
167 "used if af is not given.\n"
174 .description = "list attributes",
175 .synopsis = "la [af]",
178 "List attributes of audio file af or of current audio file when invoked\n"
179 "without arguments.\n"
186 .description = "list available attributes",
190 "What should I say more?\n"
197 .description = "print list of audio files, ordered by lastplayed time",
198 .synopsis = "last [n]",
201 "The optional number n defaults to 10 if not specified.\n"
208 .description = "list all audio files that match a LIKE pattern",
209 .synopsis = "ls [pattern]",
212 "\tIf pattern was not given, print list of all audio files known\n"
213 "\tto the mysql selector. See the documentation of mysql\n"
214 "\tfor the definition of LIKE patterns.\n"
221 .description = "dump audio file list in mbox format",
222 .synopsis = "mbox [p]",
225 "\tDump list of audio files in mbox format (email) to stdout. If\n"
226 "\tthe optional pattern p is given, only those audio files,\n"
227 "\twhose basename match p are going to be included. Otherwise,\n"
228 "\tall files are selected.\n"
231 "\tThe mbox command can be used together with your favorite\n"
232 "\tmailer (this example uses mutt) for browsing the audio file\n"
235 "\t\tpara_client mbox > ~/para_mbox\n"
237 "\t\tmutt -F ~/.muttrc.para -f ~/para_mbox\n"
239 "\tFor playlists, you can use mutt's powerful pattern matching\n"
240 "\tlanguage to select files. If you like to tag all files\n"
241 "\tcontaining the pattern 'foo', type 'T', then '~s foo'.\n"
243 "\tWhen ready with the list, type ';|' (i.e., hit the semicolon\n"
244 "\tkey to apply the next mutt command to all tagged messages,\n"
245 "\tthen the pipe key) to pipe the selected \"mails\" to a\n"
246 "\tsuitable script which adds a paraslash stream where exactly\n"
247 "\tthese files are admissable or does whatever thou wilt.\n"
253 .perms = DB_READ | DB_WRITE,
254 .description = "rename entry in database",
255 .synopsis = "mv oldname newname",
258 "Rename oldname to newname. This updates the data table to reflect the\n"
259 "new name. All internal data (numplayed, lastplayed, picid,..) is kept.\n"
260 "If newname is a full path, the dir table is updated as well.\n"
266 .perms = DB_READ | DB_WRITE,
267 .description = "add new attribute to database",
268 .synopsis = "na att",
271 "This adds a column named att to your mysql database. att should only\n"
272 "contain letters and numbers, in paricular, '+' and '-' are not allowed.\n"
277 .handler = com_rm_ne,
278 .perms = DB_READ | DB_WRITE,
279 .description = "add new database entries",
280 .synopsis = "ne file1 [file2 [...]]",
283 "Add the given filename(s) to the database, where file1,... must\n"
284 "be full path names. This command might be much faster than 'upd'\n"
285 "if the number of given files is small.\n"
291 .perms = AFS_WRITE | DB_READ | DB_WRITE,
292 .description = "change to next stream",
296 "Cycle forwards through stream list.\n"
303 .description = "get picture by name or by identifier",
304 .synopsis = "pic [name]",
307 "\tDump jpg image that is associated to given audio file (current\n"
308 "\taudio file if not specified) to stdout. If name starts with\n"
309 "\t'#' it is interpreted as an identifier instead and the picture\n"
310 "\thaving that identifier is dumped to stdout.\n"
314 "\tpara_client pic '#123' > pic123.jpg\n"
319 .handler = com_stradd_picadd,
320 .perms = DB_READ | DB_WRITE,
321 .description = "add picture to database",
322 .synopsis = "picadd [picname]",
325 "\tRead jpeg file from stdin and store it as picname in database.\n"
329 "\tpara_client picadd foo.jpg < foo.jpg\n"
335 .perms = DB_READ | DB_WRITE,
336 .description = "associate a picture to file(s)",
337 .synopsis = "picass pic_id file1 [file2...]",
340 "Associate the picture given by pic_id to all given files.\n"
345 .handler = com_picch,
346 .perms = DB_READ | DB_WRITE,
347 .description = "change name of picture",
348 .synopsis = "picch id new_name",
351 "Asign new_name to picture with identifier id.\n"
356 .handler = com_picdel,
357 .perms = DB_READ | DB_WRITE,
358 .description = "delete picture from database",
359 .synopsis = "picdel id1 [id2...]",
362 "Delete each given picture from database.\n"
367 .handler = com_piclist,
369 .description = "print list of pictures",
370 .synopsis = "piclist",
373 "Print id, name and length of each picture contained in the database.\n"
379 .perms = AFS_WRITE | DB_READ | DB_WRITE,
380 .description = "change to previous stream",
384 "Cycle backwards through stream list.\n"
389 .handler = com_rm_ne,
390 .perms = DB_READ | DB_WRITE,
391 .description = "remove entries from database",
392 .synopsis = "rm name1 [name2 [...]]",
395 "Remove name1, name2, ... from the data table. Use with caution\n"
401 .perms = DB_READ | DB_WRITE,
402 .description = "set/unset attributes",
403 .synopsis = "sa at1<'+' | '-'> [at2<'+' | '-'> ] [af1 ...]",
407 "Set ('+') or unset ('-') attribute at1, at2 etc. for given list of\n"
408 "audio files. If no audio files were given the current audio file is\n"
411 "sa rock+ punk+ classic- LZ__Waldsterben.mp3\n"
413 "sets the 'rock' and the 'punk' attribute but unsets the 'classic'\n"
420 .perms = DB_READ | DB_WRITE,
421 .description = "skip subsequent audio files(s)",
422 .synopsis = "skip n [s]",
425 "Skip the next n audio files of stream s. This is equivalent to the\n"
426 "command 'sl n s', followed by 'us name' for each name the output of sl.\n"
433 .description = "print score list",
434 .synopsis = "sl n [s]",
437 "Print sorted list of maximal n lines. Each line is an admissible entry\n"
438 "with respect to stream s. The list is sorted by score-value which is\n"
439 "given by the definition of s. If s is not given, the current stream\n"
440 "is used. Example:\n"
444 "shows you the audio file the server would select right now.\n"
450 .perms = DB_READ | DB_WRITE,
451 .description = "set numplayed",
452 .synopsis = "snp number af1 [af2 ...]",
455 "Update the numplayed field in the data table for all given audio files.\n"
460 .handler = com_stradd_picadd,
461 .perms = DB_READ | DB_WRITE,
462 .description = "add stream",
463 .synopsis = "stradd s",
466 "Add stream s to the list of available streams. The stream definition\n"
467 "for s is read from stdin and is then sent to para_server. Example:\n"
469 " echo 'deny: NAME_LIKE(%Madonna%)' | para_client stradd no_madonna\n"
471 "adds the new stream 'no_madonna' to the list of available streams. A given\n"
472 "audio file is admissible for this stream iff its basename does not contain the\n"
473 "string 'Madonna'.\n"
479 .handler = com_strdel,
480 .perms = DB_READ | DB_WRITE,
481 .description = "delete stream",
482 .synopsis = "strdel s",
485 "Remove stream s from database.\n"
490 .handler = com_streams,
492 .description = "list streams",
493 .synopsis = "streams",
496 "Print list of available streams. Use 'cs' to switch to any of these.\n"
503 .description = "query stream definition",
504 .synopsis = "strq [s]",
507 "Print definition of stream s to stdout. Use current stream if s was\n"
513 .handler = com_summary,
515 .description = "list attributes",
516 .synopsis = "summary",
519 "\tPrint a list of attributes together with number of audio\n"
520 "\tfiles having that attribute set.\n"
526 .perms = DB_READ | DB_WRITE,
527 .description = "update database",
531 "This command uses the --audio_file_dir option of para_server to locate\n"
532 "your audio files. New files are then added to the mysql database. Use\n"
533 "this command if you got new files or if you have moved some files\n"
540 .perms = DB_READ | DB_WRITE,
541 .description = "update lastplayed time",
542 .synopsis = "us name",
545 "Update lastplayed time without actually playing the thing.\n"
551 .perms = DB_READ | DB_WRITE,
552 .description = "send verbatim sql query",
553 .synopsis = "verb cmd",
556 "Send cmd to mysql server. For expert/debugging only. Note that cmd\n"
557 "usually must be escaped. Use only if you know what you are doing!\n"
564 .description = "list invalid entries in database",
568 "Show what clean would delete. Run 'upd' before this command to make\n"
569 "sure your database is up to date.\n"
577 static struct para_macro macro_list[] = {
578 { .name = "IS_N_SET",
579 .replacement = "(data.%s != '1')"
582 .replacement = "(data.%s = '1')"
585 .replacement = "%sdata.Pic_Id"
588 .replacement = "(data.name like '%s')"
590 .name = "LASTPLAYED",
591 .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())"
592 "-UNIX_TIMESTAMP(data.Lastplayed))/60)"
595 .replacement = "%sdata.Numplayed"
601 static int real_query(const char *query)
605 PARA_DEBUG_LOG("%s\n", query);
606 if (mysql_real_query(mysql_ptr, query, strlen(query))) {
607 PARA_ERROR_LOG("real_query error (%s)\n",
608 mysql_error(mysql_ptr));
615 * Use open connection given by mysql_ptr to query server. Returns a
616 * result pointer on succes and NULL on errors
618 static struct MYSQL_RES *get_result(const char *query)
622 if (real_query(query) < 0)
624 result = mysql_store_result(mysql_ptr);
626 PARA_ERROR_LOG("%s", "store_result error\n");
630 * write input from fd to dynamically allocated char array,
631 * but maximal max_size byte. Return size.
633 static int fd2buf(int fd, char **buf_ptr, size_t max_size)
635 const size_t chunk_size = 1024;
637 char *buf = para_malloc(size * sizeof(char)), *p = buf;
640 while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) {
642 if ((p - buf) + chunk_size >= size) {
646 if (size > max_size) {
650 tmp = para_realloc(buf, size);
665 static char *escape_blob(const char* old, int size)
669 if (!mysql_ptr || size < 0)
671 new = para_malloc(2 * size * sizeof(char) + 1);
672 mysql_real_escape_string(mysql_ptr, new, old, size);
676 static char *escape_str(const char* old)
678 return escape_blob(old, strlen(old));
681 static char *escaped_basename(const char *name)
683 char *esc, *bn = para_basename(name);
687 esc = escape_str(bn);
695 static int com_na(__a_unused int fd, int argc, char *argv[])
701 return -E_MYSQL_SYNTAX;
702 tmp = escape_str(argv[1]);
705 q = make_message("alter table data add %s char(1) "
706 "not null default 0", tmp);
716 static int com_da(__a_unused int fd, int argc, char *argv[])
722 return -E_MYSQL_SYNTAX;
723 tmp = escape_str(argv[1]);
726 q = make_message("alter table data drop %s", tmp);
734 static int com_stradd_picadd(int fd, int argc, char *argv[])
736 char *blob = NULL, *esc_blob = NULL, *q = NULL, *tmp = NULL;
737 const char *fmt, *del_fmt;
738 int ret, stradd = strcmp(argv[0], "picadd");
742 return -E_MYSQL_SYNTAX;
743 if (strlen(argv[1]) >= MAXLINE - 1)
744 return -E_NAMETOOLONG;
749 fmt = "insert into streams (name, def) values ('%s','%s')";
750 del_fmt="delete from streams where name='%s'";
752 size = MEDIUM_BLOB_SIZE;
753 fmt = "insert into pics (name, pic) values ('%s','%s')";
754 del_fmt="delete from pics where pic='%s'";
756 tmp = escape_str(argv[1]);
759 q = make_message(del_fmt, tmp);
765 if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0))
767 if ((ret = fd2buf(fd, &blob, size)) < 0)
773 esc_blob = escape_blob(blob, size);
776 tmp = escape_str(argv[1]);
779 q = make_message(fmt, tmp, esc_blob);
790 * print results to fd
792 static int print_results(int fd, void *result,
793 unsigned int top, unsigned int left,
794 unsigned int bottom, unsigned int right)
800 for (i = top; i <= bottom; i++) {
801 row = mysql_fetch_row(result);
804 for (j = left; j <= right; j++) {
805 ret = send_va_buffer(fd, j == left? "%s" : "\t%s",
806 row[j]? row[j] : "NULL");
810 ret = send_buffer(fd, "\n");
820 static int com_verb(int fd, int argc, char *argv[])
824 unsigned int num_rows, num_fields;
828 return -E_MYSQL_SYNTAX;
829 tmp = escape_str(argv[1]);
832 result = get_result(tmp);
835 /* return success, because it's ok to have no results */
837 num_fields = mysql_field_count(mysql_ptr);
838 num_rows = mysql_num_rows(result);
840 if (num_fields && num_rows)
841 ret = print_results(fd, result, 0, 0, num_rows - 1,
843 mysql_free_result(result);
847 /* returns NULL on errors or if there are no atts defined yet */
848 static void *get_all_attributes(void)
850 void *result = get_result("desc data");
851 unsigned int num_rows;
855 num_rows = mysql_num_rows(result);
857 mysql_free_result(result);
860 mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */
865 * list all attributes
867 static int com_laa(int fd, int argc, __a_unused char *argv[])
873 return -E_MYSQL_SYNTAX;
874 result = get_all_attributes();
877 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0);
878 mysql_free_result(result);
885 static int com_hist(int fd, int argc, char *argv[]) {
889 unsigned int num_rows;
892 return -E_MYSQL_SYNTAX;
894 char *tmp = escape_str(argv[1]);
897 atts = make_message("where %s = '1'", tmp);
900 atts = para_strdup(NULL);
902 q = make_message("select name, to_days(now()) - to_days(lastplayed) from "
903 "data %s order by lastplayed", atts);
905 result = get_result(q);
909 num_rows = mysql_num_rows(result);
912 ret = print_results(fd, result, 0, 0, num_rows - 1, 1);
913 mysql_free_result(result);
918 * get last num audio files
920 static int com_last(int fd, int argc, char *argv[])
931 return -E_MYSQL_SYNTAX;
932 q = make_message("select name from data order by lastplayed desc "
934 result = get_result(q);
938 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0);
939 mysql_free_result(result);
943 static int com_mbox(int fd, int argc, char *argv[])
948 unsigned int num_rows, num_fields;
949 char *query = para_strdup("select concat('From foo@localhost ', "
950 "date_format(Lastplayed, '%a %b %e %T %Y'), "
951 "'\nReceived: from\nTo: bar\n");
954 result = get_all_attributes();
958 while ((row = mysql_fetch_row(result))) {
963 tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query,
968 query = para_strcat(query,
976 char *esc = escape_str(argv[1]), *tmp;
980 tmp = make_message("%s where name LIKE '%s'", query, esc);
985 mysql_free_result(result);
987 result = get_result(query);
990 ret = -E_EMPTY_RESULT;
991 num_fields = mysql_field_count(mysql_ptr);
992 num_rows = mysql_num_rows(result);
993 if (!num_fields || !num_rows)
995 ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1);
999 mysql_free_result(result);
1003 /* get attributes by name. If verbose is not 0, get_a writes a string
1004 * into atts of the form 'att1="0",att2="1"', which is used in com_cam
1005 * for contructing a mysql update query.
1006 * never returns NULL in *NON VERBOSE* mode
1008 static char *get_atts(char *name, int verbose)
1010 char *atts = NULL, *buf, *ebn;
1011 void *result = NULL, *result2 = NULL;
1012 MYSQL_ROW row, row2;
1014 unsigned int num_fields;
1017 result2 = get_all_attributes();
1021 if (!(ebn = escaped_basename(name)))
1023 buf = make_message("select * from data where name='%s'", ebn);
1026 result = get_result(buf);
1030 ret = -E_EMPTY_RESULT;
1031 num_fields = mysql_num_fields(result);
1034 mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */
1035 row = mysql_fetch_row(result);
1039 for (i = 4; i < num_fields; i++) {
1040 int is_set = row[i] && !strcmp(row[i], "1");
1041 row2 = mysql_fetch_row(result2);
1042 if (!row2 || !row2[0])
1044 if (atts && (verbose || is_set))
1045 atts = para_strcat(atts, verbose? "," : " ");
1046 if (is_set || verbose)
1047 atts = para_strcat(atts, row2[0]);
1049 atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\"");
1054 mysql_free_result(result2);
1056 mysql_free_result(result);
1057 if (!atts && !verbose)
1058 atts = para_strdup("(none)");
1062 /* never returns NULL in verbose mode */
1063 static char *get_meta(char *name, int verbose)
1066 void *result = NULL;
1067 char *ebn, *q, *ret = NULL;
1068 const char *verbose_fmt =
1069 "select concat('lastplayed: ', "
1070 "(to_days(now()) - to_days(lastplayed)),"
1071 "' day(s). numplayed: ', numplayed, "
1072 "', pic: ', pic_id) "
1073 "from data where name = '%s'";
1074 /* is that really needed? */
1075 const char *fmt = "select concat('lastplayed=\\'', lastplayed, "
1076 "'\\', numplayed=\\'', numplayed, "
1077 "'\\', pic_id=\\'', pic_id, '\\'') "
1078 "from data where name = '%s'";
1080 if (!(ebn = escaped_basename(name)))
1082 q = make_message(verbose? verbose_fmt : fmt, ebn);
1084 result = get_result(q);
1088 row = mysql_fetch_row(result);
1089 if (!row || !row[0])
1091 ret = para_strdup(row[0]);
1094 mysql_free_result(result);
1095 if (!ret && verbose)
1096 ret = para_strdup("(not yet played)");
1100 static char *get_dir(char *name)
1102 char *ret = NULL, *q, *ebn;
1106 if (!(ebn = escaped_basename(name)))
1108 q = make_message("select dir from dir where name = '%s'", ebn);
1110 result = get_result(q);
1114 row = mysql_fetch_row(result);
1116 ret = para_strdup(row[0]);
1117 mysql_free_result(result);
1121 /* never returns NULL */
1122 static char *get_current_stream(void)
1126 void *result = get_result("select def from streams where "
1127 "name = 'current_stream'");
1131 row = mysql_fetch_row(result);
1132 if (!row || !row[0])
1134 ret = para_strdup(row[0]);
1135 mysql_free_result(result);
1139 mysql_free_result(result);
1140 return para_strdup("(none)");
1144 * Read stream definition of stream streamname and construct mysql
1145 * query. Return NULL on errors. If streamname is NULL, use current
1146 * stream. If that is also NULL, use query that selects everything.
1147 * If filename is NULL, query will list everything, otherwise only
1148 * the score of given file.
1150 static char *get_query(char *streamname, char *filename, int with_path)
1152 char *accept_opts = NULL, *deny_opts = NULL, *score = NULL;
1153 char *where_clause, *order, *query;
1154 char command[255] = ""; /* buffer for sscanf */
1158 char *select_clause = NULL;
1160 tmp = get_current_stream();
1162 tmp = escape_str(streamname);
1166 if (!strcmp(tmp, "(none)")) {
1169 char *ret, *ebn = escaped_basename(filename);
1172 ret = make_message("select to_days(now()) - "
1173 "to_days(lastplayed) from data "
1174 "where name = '%s'", ebn);
1179 return make_message(
1180 "select concat(dir.dir, '/', dir.name) "
1181 "from data, dir where dir.name = data.name "
1182 "order by data.lastplayed"
1184 return make_message(
1185 "select name from data where name is not NULL "
1186 "order by lastplayed"
1190 query = make_message("select def from streams where name = '%s'",
1192 result = get_result(query);
1197 row = mysql_fetch_row(result);
1198 if (!row || !row[0])
1203 char *arg, *line = end;
1205 if (!(end = strchr(line, '\n')))
1209 if (sscanf(line, "%200s%n", command, &n) < 1)
1212 if (!strcmp(command, "accept:")) {
1213 char *tmp2 = s_a_r_list(macro_list, arg);
1215 accept_opts = para_strcat(
1216 accept_opts, " or ");
1217 accept_opts = para_strcat(accept_opts, tmp2);
1221 if (!strcmp(command, "deny:")) {
1222 char *tmp2 = s_a_r_list(macro_list, arg);
1224 deny_opts = para_strcat(deny_opts, " or ");
1225 deny_opts = para_strcat(deny_opts, tmp2);
1229 if (!score && !strcmp(command, "score:"))
1230 score = s_a_r_list(macro_list, arg);
1233 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
1238 char *ebn = escaped_basename(filename);
1241 select_clause = make_message("select %s from data ", score);
1243 where_clause = make_message( "where name = '%s' ", ebn);
1245 order = para_strdup("");
1248 select_clause = para_strdup(with_path?
1249 "select concat(dir.dir, '/', dir.name) from data, dir "
1250 "where dir.name = data.name "
1252 "select name from data where name is not NULL");
1253 order = make_message("order by -(%s)", score);
1255 if (accept_opts && deny_opts) {
1256 where_clause = make_message("and ((%s) and not (%s)) ",
1257 accept_opts, deny_opts);
1260 if (accept_opts && !deny_opts) {
1261 where_clause = make_message("and (%s) ", accept_opts);
1264 if (!accept_opts && deny_opts) {
1265 where_clause = make_message("and not (%s) ", deny_opts);
1268 where_clause = para_strdup("");
1270 query = make_message("%s %s %s", select_clause, where_clause, order);
1272 free(select_clause);
1280 mysql_free_result(result);
1287 * This is called from server and from some commands. Name must not be NULL
1288 * Never returns NULL.
1290 static char *get_selector_info(char *name)
1292 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
1293 void *result = NULL;
1294 MYSQL_ROW row = NULL;
1297 return para_strdup("(none)");
1298 stream = get_current_stream();
1299 meta = get_meta(name, 1);
1300 atts = get_atts(name, 0);
1301 dir = get_dir(name);
1303 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
1306 result = get_result(query);
1309 row = mysql_fetch_row(result);
1311 info = make_message("dbinfo1:dir: %s\n"
1312 "dbinfo2:stream: %s, %s, score: %s\n"
1314 dir? dir : "(not contained in table)",
1316 (result && row && row[0])? row[0] : "(no score)",
1327 mysql_free_result(result);
1332 /* might return NULL */
1333 static char *get_current_audio_file(void)
1337 name = para_basename(mmd->filename);
1343 /* print database info */
1344 static int com_info(int fd, int argc, char *argv[])
1346 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
1347 int ret, com_la = strcmp(argv[0], "info");
1350 ret = -E_GET_AUDIO_FILE;
1351 if (!(name = get_current_audio_file()))
1353 ret = send_va_buffer(fd, "%s\n", name);
1358 if (!(name = escaped_basename(argv[1])))
1361 meta = get_meta(name, 1);
1362 atts = get_atts(name, 0);
1363 dir = get_dir(name);
1365 ret = send_va_buffer(fd, "%s\n", atts);
1367 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
1368 dir? dir : "(not contained in table)", meta, atts);
1377 static int change_stream(const char *stream)
1381 query = make_message("update streams set def='%s' "
1382 "where name = 'current_stream'", stream);
1383 ret = real_query(query);
1388 static int get_pic_id_by_name(char *name)
1391 void *result = NULL;
1395 if (!(ebn = escaped_basename(name)))
1397 q = make_message("select pic_id from data where name = '%s'", ebn);
1399 result = get_result(q);
1403 row = mysql_fetch_row(result);
1407 mysql_free_result(result);
1411 static int remove_entry(const char *name)
1413 char *q, *ebn = escaped_basename(name);
1414 int ret = -E_ESCAPE;
1418 q = make_message("delete from data where name = '%s'", ebn);
1419 real_query(q); /* ignore errors */
1421 q = make_message("delete from dir where name = '%s'", ebn);
1422 real_query(q); /* ignore errors */
1430 static int add_entry(const char *name)
1432 char *q, *dn, *ebn = NULL, *edn = NULL;
1435 if (!name || !*name)
1436 return -E_MYSQL_SYNTAX;
1437 ebn = escaped_basename(name);
1440 ret = -E_MYSQL_SYNTAX;
1441 dn = para_dirname(name);
1445 edn = escape_str(dn);
1449 q = make_message("insert into data (name, pic_id) values "
1450 "('%s', '%s')", ebn, "1");
1451 ret = real_query(q);
1452 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1456 q = make_message("insert into dir (name, dir) values "
1457 "('%s', '%s')", ebn, edn);
1458 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1459 ret = real_query(q);
1470 * remove/add entries
1472 static int com_rm_ne(__a_unused int fd, int argc, char *argv[])
1474 int ne = !strcmp(argv[0], "ne");
1477 return -E_MYSQL_SYNTAX;
1478 for (i = 1; i < argc; i++) {
1479 ret = remove_entry(argv[i]);
1484 ret = add_entry(argv[i]);
1494 static int com_mv(__a_unused int fd, int argc, char *argv[])
1496 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1500 return -E_MYSQL_SYNTAX;
1502 ebn1 = escaped_basename(argv[1]);
1503 ebn2 = escaped_basename(argv[2]);
1504 if (!ebn1 || !ebn2 | !*ebn1 || !*ebn2)
1506 ret = -E_MYSQL_SYNTAX;
1507 if (!strcmp(ebn1, ebn2))
1509 remove_entry(argv[2]); /* no need to escape, ignore error */
1510 q = make_message("update data set name = '%s' where name = '%s'",
1512 ret = real_query(q);
1516 ret = -E_AUDIO_FILE;
1517 if (!mysql_affected_rows(mysql_ptr))
1519 q = make_message("update dir set name = '%s' where name = '%s'",
1521 ret = real_query(q);
1526 dn = para_dirname(argv[2]);
1530 edn = escape_str(dn);
1537 q = make_message("update dir set dir = '%s' where name = '%s'",
1539 ret = real_query(q);
1549 * picass: associate pic to audio file
1550 * snp: set numplayed
1552 static int com_set(__a_unused int fd, int argc, char *argv[])
1557 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1560 return -E_MYSQL_SYNTAX;
1562 for (i = 2; i < argc; i++) {
1563 ebn = escaped_basename(argv[i]);
1566 q = make_message("update data set %s = %lu "
1567 "where name = '%s'", field, id, ebn);
1569 ret = real_query(q);
1578 * picch: change entry's name in pics table
1580 static int com_picch(__a_unused int fd, int argc, char *argv[])
1587 return -E_MYSQL_SYNTAX;
1590 tmp = escape_str(argv[2]);
1593 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1595 ret = real_query(q);
1601 * piclist: print list of pics in db
1603 static int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1605 void *result = NULL;
1607 unsigned long *length;
1611 return -E_MYSQL_SYNTAX;
1612 result = get_result("select id,name,pic from pics order by id");
1615 while ((row = mysql_fetch_row(result))) {
1616 length = mysql_fetch_lengths(result);
1617 if (!row || !row[0] || !row[1] || !row[2])
1619 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1625 mysql_free_result(result);
1630 * picdel: delete picture from database
1632 static int com_picdel(int fd, int argc, char *argv[])
1640 return -E_MYSQL_SYNTAX;
1641 for (i = 1; i < argc; i++) {
1643 q = make_message("delete from pics where id = %lu", id);
1644 ret = real_query(q);
1648 aff = mysql_affected_rows(mysql_ptr);
1650 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1655 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1656 ret = real_query(q);
1662 * pic: get picture by name or by number
1664 static int com_pic(int fd, int argc, char *argv[])
1666 void *result = NULL;
1668 unsigned long *length, id;
1670 char *q, *name = NULL;
1673 ret = -E_GET_AUDIO_FILE;
1674 name = get_current_audio_file();
1677 name = escaped_basename(argv[1]);
1682 id = atoi(name + 1);
1684 id = get_pic_id_by_name(name);
1688 q = make_message("select pic from pics where id = '%lu'", id);
1689 result = get_result(q);
1693 row = mysql_fetch_row(result);
1695 if (!row || !row[0])
1697 length = mysql_fetch_lengths(result);
1698 ret = send_bin_buffer(fd, row[0], *length);
1700 mysql_free_result(result);
1705 static int com_strdel(__a_unused int fd, int argc, char *argv[])
1711 return -E_MYSQL_SYNTAX;
1712 tmp = escape_str(argv[1]);
1715 q = make_message("delete from streams where name='%s'", tmp);
1717 ret = real_query(q);
1725 static int com_ls(int fd, int argc, char *argv[])
1730 unsigned int num_rows;
1733 return -E_MYSQL_SYNTAX;
1735 char *tmp = escape_str(argv[1]);
1738 q = make_message("select name from data where name like '%s'",
1742 q = para_strdup("select name from data");
1743 result = get_result(q);
1747 num_rows = mysql_num_rows(result);
1750 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1751 mysql_free_result(result);
1758 static int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1763 void *result2 = NULL;
1764 const char *fmt = "select count(name) from data where %s='1'";
1765 int ret = -E_NORESULT;
1768 return -E_MYSQL_SYNTAX;
1769 result = get_all_attributes();
1772 while ((row = mysql_fetch_row(result))) {
1779 buf = make_message(fmt, row[0]);
1780 result2 = get_result(buf);
1785 row2 = mysql_fetch_row(result2);
1786 if (!row2 || !row2[0])
1788 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1795 mysql_free_result(result2);
1797 mysql_free_result(result);
1801 static int get_numplayed(char *name)
1805 const char *fmt = "select numplayed from data where name = '%s'";
1806 char *buf = make_message(fmt, name);
1807 int ret = -E_NORESULT;
1809 result = get_result(buf);
1814 row = mysql_fetch_row(result);
1815 if (!row || !row[0])
1820 mysql_free_result(result);
1824 static int update_audio_file(char *name)
1827 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1828 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1830 char *ebn = escaped_basename(name);
1835 q = make_message(fmt1, ebn);
1836 ret = real_query(q);
1840 ret = get_numplayed(ebn);
1843 q = make_message(fmt2, ret + 1, ebn);
1844 ret = real_query(q);
1851 /* If called as child, mmd_lock must be held */
1852 static void update_mmd(char *info)
1854 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1855 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1856 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1859 static void update_audio_file_server_handler(char *name)
1862 info = get_selector_info(name);
1865 update_audio_file(name);
1868 static int com_us(__a_unused int fd, int argc, char *argv[])
1874 return -E_MYSQL_SYNTAX;
1875 tmp = escape_str(argv[1]);
1878 ret = update_audio_file(argv[1]);
1883 static void refresh_selector_info(void)
1885 char *name = get_current_audio_file();
1890 info = get_selector_info(name);
1898 /* select previous/next stream */
1899 static int com_ps(__a_unused int fd, int argc, char *argv[])
1901 char *query, *stream = get_current_stream();
1902 void *result = get_result("select name from streams");
1904 int match = -1, ret, i;
1905 unsigned int num_rows;
1908 return -E_MYSQL_SYNTAX;
1912 num_rows = mysql_num_rows(result);
1913 ret = -E_EMPTY_RESULT;
1917 for (i = 0; i < num_rows; i++) {
1918 row = mysql_fetch_row(result);
1919 if (!row || !row[0])
1921 if (!strcmp(row[0], "current_stream"))
1923 if (!strcmp(row[0], stream)) {
1931 if (!strcmp(argv[0], "ps"))
1932 i = match > 0? match - 1 : num_rows - 1;
1934 i = match < num_rows - 1? match + 1 : 0;
1936 mysql_data_seek(result, i);
1937 row = mysql_fetch_row(result);
1938 if (!row || !row[0])
1940 if (!strcmp(row[0], "current_stream")) {
1941 if (!strcmp(argv[0], "ps")) {
1943 i = i < 0? i + num_rows : i;
1946 i = i > num_rows - 1? i - num_rows : i;
1948 mysql_data_seek(result, i);
1949 row = mysql_fetch_row(result);
1950 if (!row || !row[0])
1953 query = make_message("update streams set def='%s' where name = "
1954 "'current_stream'", row[0]);
1955 ret = real_query(query);
1957 refresh_selector_info();
1961 mysql_free_result(result);
1966 static int com_streams(int fd, int argc, __a_unused char *argv[])
1968 unsigned int num_rows;
1969 int i, ret = -E_NORESULT;
1973 if (argc > 1 && strcmp(argv[1], "current_stream"))
1974 return -E_MYSQL_SYNTAX;
1976 char *cs = get_current_stream();
1977 ret = send_va_buffer(fd, "%s\n", cs);
1981 result = get_result("select name from streams");
1984 num_rows = mysql_num_rows(result);
1989 for (i = 0; i < num_rows; i++) {
1990 row = mysql_fetch_row(result);
1991 if (!row || !row[0])
1993 if (strcmp(row[0], "current_stream"))
1994 send_va_buffer(fd, "%s\n", row[0]);
1999 mysql_free_result(result);
2003 /* query stream definition */
2004 static int com_strq(int fd, int argc, char *argv[])
2012 ret = -E_GET_STREAM;
2013 name = get_current_stream();
2016 name = escaped_basename(argv[1]);
2021 query = make_message("select def from streams where name='%s'", name);
2023 result = get_result(query);
2028 row = mysql_fetch_row(result);
2029 if (!row || !row[0])
2031 /* no '\n' needed */
2032 ret = send_buffer(fd, row[0]);
2035 mysql_free_result(result);
2039 /* change stream / change stream and play */
2040 static int com_cs(int fd, int argc, char *argv[])
2042 int ret, stream_change;
2043 char *query, *stream = NULL;
2044 char *old_stream = get_current_stream();
2045 int csp = !strcmp(argv[0], "csp");
2047 ret = -E_MYSQL_SYNTAX;
2053 ret = send_va_buffer(fd, "%s\n", old_stream);
2057 /* test if stream is valid, no need to escape argv[1] */
2058 query = get_query(argv[1], NULL, 0);
2063 stream = escape_str(argv[1]);
2066 stream_change = strcmp(stream, old_stream);
2067 if (stream_change) {
2068 ret = change_stream(stream);
2071 refresh_selector_info();
2075 mmd->new_afs_status_flags |= AFS_PLAYING;
2077 mmd->new_afs_status_flags |= AFS_NEXT;
2090 static int com_sl(int fd, int argc, char *argv[])
2092 void *result = NULL;
2094 int ret, i, skip = !strcmp(argv[0], "skip");
2095 char *query, *stream, *tmp;
2096 unsigned int num_rows, num;
2099 return -E_MYSQL_SYNTAX;
2100 num = atoi(argv[1]);
2102 return -E_MYSQL_SYNTAX;
2104 stream = get_current_stream();
2106 return -E_GET_STREAM;
2108 stream = escape_str(argv[2]);
2112 tmp = get_query(stream, NULL, 0);
2115 return -E_GET_QUERY;
2116 query = make_message("%s limit %d", tmp, num);
2119 result = get_result(query);
2123 ret = -E_EMPTY_RESULT;
2124 num_rows = mysql_num_rows(result);
2127 for (i = 0; i < num_rows && i < num; i++) {
2128 row = mysql_fetch_row(result);
2130 send_va_buffer(fd, "Skipping %s\n", row[0]);
2131 update_audio_file(row[0]);
2133 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2138 mysql_free_result(result);
2143 * update attributes of name
2145 static int update_atts(int fd, char *name, char *atts)
2148 char *ebn, *q, *old, *new = NULL;
2152 ebn = escaped_basename(name);
2155 q = make_message("update data set %s where name = '%s'", atts, ebn);
2156 old = get_atts(ebn, 0);
2157 send_va_buffer(fd, "old: %s\n", old);
2159 ret = real_query(q);
2163 new = get_atts(ebn, 0);
2164 ret = send_va_buffer(fd, "new: %s\n", new);
2174 static int com_sa(int fd, int argc, char *argv[])
2177 char *atts = NULL, *name;
2180 return -E_MYSQL_SYNTAX;
2181 for (i = 1; i < argc; i++) {
2183 char *esc, *tmp, *p =argv[i];
2184 int len = strlen(p);
2188 switch (p[len - 1]) {
2199 esc = escape_str(p);
2202 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2205 atts = para_strcat(atts, tmp);
2211 if (i >= argc) { /* no name given, use current af */
2212 ret = -E_GET_AUDIO_FILE;
2213 if (!(name = get_current_audio_file()))
2215 ret = update_atts(fd, name, atts);
2219 for (; argv[i] && ret >= 0; i++)
2220 ret = update_atts(fd, argv[i], atts);
2222 refresh_selector_info();
2231 static int com_cam(int fd, int argc, char *argv[])
2233 char *name = NULL, *meta = NULL, *atts = NULL;
2237 return -E_MYSQL_SYNTAX;
2238 if (!(name = escaped_basename(argv[1])))
2241 if (!(atts = get_atts(name, 1)))
2244 if (!(meta = get_meta(name, 0)))
2246 for (i = 2; i < argc; i++) {
2249 if (!(ebn = escaped_basename(argv[i])))
2251 ret = send_va_buffer(fd, "updating %s\n", ebn);
2256 q = make_message("update data set %s where name = '%s'",
2258 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2259 ret = real_query(q);
2279 static int com_vrfy(int fd, int argc, __a_unused char *argv[])
2282 int ret, vrfy_mode = strcmp(argv[0], "clean");
2283 void *result = NULL;
2284 unsigned int num_rows;
2289 return -E_MYSQL_SYNTAX;
2291 result = get_result("select data.name from data left join dir on "
2292 "dir.name = data.name where dir.name is NULL");
2295 num_rows = mysql_num_rows(result);
2297 ret = send_buffer(fd, "No invalid entries\n");
2301 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2302 num_rows == 1? "y" : "ies");
2303 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2306 while ((row = mysql_fetch_row(result))) {
2311 escaped_name = escape_str(row[0]);
2314 send_va_buffer(fd, "deleting %s\n", escaped_name);
2315 query = make_message("delete from data where name = '%s'",
2317 ret = real_query(query);
2325 mysql_free_result(result);
2329 static FILE *out_file;
2331 static int mysql_write_tmp_file(const char *dir, const char *name)
2333 int ret = -E_TMPFILE;
2334 char *msg = make_message("%s\t%s\n", dir, name);
2335 if (fputs(msg, out_file) != EOF)
2344 static int com_upd(int fd, int argc, __a_unused char *argv[])
2346 char *tempname = NULL, *query = NULL;
2347 int ret, out_fd = -1, num = 0;
2348 void *result = NULL;
2349 unsigned int num_rows;
2353 return -E_MYSQL_SYNTAX;
2355 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2356 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2360 out_file = fdopen(out_fd, "w");
2365 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2367 num = ftell(out_file);
2369 * we have to make sure the file hit the disk before we call
2374 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2377 if ((ret = real_query("delete from dir")) < 0)
2379 query = make_message("load data infile '%s' ignore into table dir "
2380 "fields terminated by '\t' lines terminated by '\n' "
2381 "(dir, name)", tempname);
2382 ret = real_query(query);
2386 result = get_result("select dir.name from dir left join data on "
2387 "data.name = dir.name where data.name is NULL");
2391 num_rows = mysql_num_rows(result);
2393 ret = send_buffer(fd, "no new entries\n");
2396 while ((row = mysql_fetch_row(result))) {
2401 send_va_buffer(fd, "new entry: %s\n", row[0]);
2402 erow = escape_str(row[0]);
2405 query = make_message("insert into data (name, pic_id) values "
2406 "('%s','%s')", erow, "1");
2408 ret = real_query(query);
2421 mysql_free_result(result);
2425 static char **server_get_audio_file_list(unsigned int num)
2427 char **list = para_malloc((num + 1) * sizeof(char *));
2428 char *tmp, *query, *stream = get_current_stream();
2429 void *result = NULL;
2430 unsigned int num_rows;
2434 tmp = get_query(stream, NULL, 1);
2438 query = make_message("%s limit %d", tmp, num);
2440 result = get_result(query);
2444 num_rows = mysql_num_rows(result);
2447 for (i = 0; i < num_rows && i < num; i++) {
2448 row = mysql_fetch_row(result);
2449 if (!row || !row[0])
2451 list[i] = para_strdup(row[0]);
2464 mysql_free_result(result);
2469 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2470 * on errors. Called from parent on startup and also from com_cdb().
2472 static int init_mysql_server(void)
2474 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2476 mysql_ptr = mysql_init(NULL);
2478 PARA_CRIT_LOG("%s", "mysql init error\n");
2481 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2482 conf.mysql_port_arg);
2483 if (!conf.mysql_user_arg)
2486 * If host is NULL a connection to the local host is assumed,
2487 * If user is NULL, the current user is assumed
2489 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2490 conf.mysql_host_arg,
2491 conf.mysql_user_arg,
2492 conf.mysql_passwd_arg,
2493 conf.mysql_database_arg,
2494 conf.mysql_port_arg, NULL, 0))) {
2495 PARA_CRIT_LOG("%s", "connect error\n");
2498 PARA_INFO_LOG("%s", "success\n");
2502 /* mmd lock must be held */
2503 static void write_msg2mmd(int success)
2505 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2506 success < 0? PARA_STRERROR(-success) :
2507 "successfully connected to mysql server",
2508 success < 0? "" : mysql_get_server_info(mysql_ptr));
2511 /* create database */
2512 static int com_cdb(int fd, int argc, char *argv[])
2518 PARA_INFO_LOG("%s", "closing database\n");
2519 mysql_close(mysql_ptr);
2521 /* dont use any database */
2522 conf.mysql_database_arg = NULL; /* leak? */
2523 ret = -E_MYSQL_INIT;
2524 if (init_mysql_server() < 0 || !mysql_ptr)
2527 conf.mysql_database_arg = para_strdup("paraslash");
2530 conf.mysql_database_arg = escape_str(argv[1]);
2531 if (!conf.mysql_database_arg)
2534 query = make_message("create database %s", conf.mysql_database_arg);
2535 ret = real_query(query);
2539 /* reconnect with database just created */
2540 mysql_close(mysql_ptr);
2541 ret = -E_MYSQL_INIT;
2542 if (init_mysql_server() < 0 || !mysql_ptr)
2548 if (real_query("create table data (name varchar(255) binary not null "
2550 "lastplayed datetime not null default "
2552 "numplayed int not null default 0, "
2553 "pic_id bigint unsigned not null default 1)") < 0)
2555 if (real_query("create table dir (name varchar(255) binary not null "
2556 "primary key, dir varchar(255) default null)") < 0)
2558 if (real_query("create table pics ("
2559 "id bigint(20) unsigned not null primary key "
2561 "name varchar(255) binary not null, "
2562 "pic mediumblob not null)") < 0)
2564 if (real_query("create table streams ("
2565 "name varchar(255) binary not null primary key, "
2566 "def blob not null)") < 0)
2568 if (real_query("insert into streams (name, def) values "
2569 "('current_stream', '(none)')") < 0)
2571 ret = send_va_buffer(fd, "successfully created database %s\n",
2572 conf.mysql_database_arg);
2577 static void shutdown_connection(void)
2580 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2581 mysql_close(mysql_ptr);
2587 * the init function of the mysql-based audio file selector
2589 * Check the command line options and initialize all function pointers of \a db.
2590 * Connect to the mysql server and initialize the info string.
2592 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2595 int mysql_selector_init(struct audio_file_selector *db)
2599 if (!conf.mysql_passwd_given)
2600 return -E_NO_MYSQL_PASSWD;
2601 if (!conf.mysql_audio_file_dir_given)
2602 return -E_NO_AF_DIR;
2604 db->cmd_list = cmds;
2605 db->get_audio_file_list = server_get_audio_file_list;
2606 db->update_audio_file = update_audio_file_server_handler;
2607 db->shutdown = shutdown_connection;
2608 ret = init_mysql_server();
2610 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2612 return 1; /* return success even if connect failed to give the
2613 * user the chance to exec com_cdb