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>
34 #include "user_list.h"
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(const 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(const 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(__a_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(__a_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, __a_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);
1167 if (!strcmp(tmp, "(none)")) {
1170 char *ret, *ebn = escaped_basename(filename);
1173 ret = make_message("select to_days(now()) - "
1174 "to_days(lastplayed) from data "
1175 "where name = '%s'", ebn);
1180 return make_message(
1181 "select concat(dir.dir, '/', dir.name) "
1182 "from data, dir where dir.name = data.name "
1183 "order by data.lastplayed"
1185 return make_message(
1186 "select name from data where name is not NULL "
1187 "order by lastplayed"
1191 query = make_message("select def from streams where name = '%s'",
1193 result = get_result(query);
1198 row = mysql_fetch_row(result);
1199 if (!row || !row[0])
1204 char *arg, *line = end;
1206 if (!(end = strchr(line, '\n')))
1210 if (sscanf(line, "%200s%n", command, &n) < 1)
1213 if (!strcmp(command, "accept:")) {
1214 char *tmp2 = s_a_r_list(macro_list, arg);
1216 accept_opts = para_strcat(
1217 accept_opts, " or ");
1218 accept_opts = para_strcat(accept_opts, tmp2);
1222 if (!strcmp(command, "deny:")) {
1223 char *tmp2 = s_a_r_list(macro_list, arg);
1225 deny_opts = para_strcat(deny_opts, " or ");
1226 deny_opts = para_strcat(deny_opts, tmp2);
1230 if (!score && !strcmp(command, "score:"))
1231 score = s_a_r_list(macro_list, arg);
1234 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
1239 char *ebn = escaped_basename(filename);
1242 select_clause = make_message("select %s from data ", score);
1244 where_clause = make_message( "where name = '%s' ", ebn);
1246 order = para_strdup("");
1249 select_clause = para_strdup(with_path?
1250 "select concat(dir.dir, '/', dir.name) from data, dir "
1251 "where dir.name = data.name "
1253 "select name from data where name is not NULL");
1254 order = make_message("order by -(%s)", score);
1256 if (accept_opts && deny_opts) {
1257 where_clause = make_message("and ((%s) and not (%s)) ",
1258 accept_opts, deny_opts);
1261 if (accept_opts && !deny_opts) {
1262 where_clause = make_message("and (%s) ", accept_opts);
1265 if (!accept_opts && deny_opts) {
1266 where_clause = make_message("and not (%s) ", deny_opts);
1269 where_clause = para_strdup("");
1271 query = make_message("%s %s %s", select_clause, where_clause, order);
1273 free(select_clause);
1281 mysql_free_result(result);
1288 * This is called from server and from some commands. Name must not be NULL
1289 * Never returns NULL.
1291 static char *get_selector_info(char *name)
1293 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
1294 void *result = NULL;
1295 MYSQL_ROW row = NULL;
1298 return para_strdup("(none)");
1299 stream = get_current_stream();
1300 meta = get_meta(name, 1);
1301 atts = get_atts(name, 0);
1302 dir = get_dir(name);
1304 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
1307 result = get_result(query);
1310 row = mysql_fetch_row(result);
1312 info = make_message("dbinfo1:dir: %s\n"
1313 "dbinfo2:stream: %s, %s, score: %s\n"
1315 dir? dir : "(not contained in table)",
1317 (result && row && row[0])? row[0] : "(no score)",
1328 mysql_free_result(result);
1333 /* might return NULL */
1334 static char *get_current_audio_file(void)
1338 name = para_basename(mmd->filename);
1344 /* print database info */
1345 static int com_info(int fd, int argc, char *argv[])
1347 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
1348 int ret, com_la = strcmp(argv[0], "info");
1351 ret = -E_GET_AUDIO_FILE;
1352 if (!(name = get_current_audio_file()))
1354 ret = send_va_buffer(fd, "%s\n", name);
1359 if (!(name = escaped_basename(argv[1])))
1362 meta = get_meta(name, 1);
1363 atts = get_atts(name, 0);
1364 dir = get_dir(name);
1366 ret = send_va_buffer(fd, "%s\n", atts);
1368 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
1369 dir? dir : "(not contained in table)", meta, atts);
1378 static int change_stream(const char *stream)
1382 query = make_message("update streams set def='%s' "
1383 "where name = 'current_stream'", stream);
1384 ret = real_query(query);
1389 static int get_pic_id_by_name(char *name)
1392 void *result = NULL;
1396 if (!(ebn = escaped_basename(name)))
1398 q = make_message("select pic_id from data where name = '%s'", ebn);
1400 result = get_result(q);
1404 row = mysql_fetch_row(result);
1408 mysql_free_result(result);
1412 static int remove_entry(const char *name)
1414 char *q, *ebn = escaped_basename(name);
1415 int ret = -E_ESCAPE;
1419 q = make_message("delete from data where name = '%s'", ebn);
1420 real_query(q); /* ignore errors */
1422 q = make_message("delete from dir where name = '%s'", ebn);
1423 real_query(q); /* ignore errors */
1431 static int add_entry(const char *name)
1433 char *q, *dn, *ebn = NULL, *edn = NULL;
1436 if (!name || !*name)
1437 return -E_MYSQL_SYNTAX;
1438 ebn = escaped_basename(name);
1441 ret = -E_MYSQL_SYNTAX;
1442 dn = para_dirname(name);
1446 edn = escape_str(dn);
1450 q = make_message("insert into data (name, pic_id) values "
1451 "('%s', '%s')", ebn, "1");
1452 ret = real_query(q);
1453 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1457 q = make_message("insert into dir (name, dir) values "
1458 "('%s', '%s')", ebn, edn);
1459 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1460 ret = real_query(q);
1471 * remove/add entries
1473 static int com_rm_ne(__a_unused int fd, int argc, char *argv[])
1475 int ne = !strcmp(argv[0], "ne");
1478 return -E_MYSQL_SYNTAX;
1479 for (i = 1; i < argc; i++) {
1480 ret = remove_entry(argv[i]);
1485 ret = add_entry(argv[i]);
1495 static int com_mv(__a_unused int fd, int argc, char *argv[])
1497 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1501 return -E_MYSQL_SYNTAX;
1503 ebn1 = escaped_basename(argv[1]);
1504 ebn2 = escaped_basename(argv[2]);
1505 if (!ebn1 || !ebn2 | !*ebn1 || !*ebn2)
1507 ret = -E_MYSQL_SYNTAX;
1508 if (!strcmp(ebn1, ebn2))
1510 remove_entry(argv[2]); /* no need to escape, ignore error */
1511 q = make_message("update data set name = '%s' where name = '%s'",
1513 ret = real_query(q);
1517 ret = -E_AUDIO_FILE;
1518 if (!mysql_affected_rows(mysql_ptr))
1520 q = make_message("update dir set name = '%s' where name = '%s'",
1522 ret = real_query(q);
1528 dn = para_dirname(argv[2]);
1532 edn = escape_str(dn);
1539 q = make_message("update dir set dir = '%s' where name = '%s'",
1541 ret = real_query(q);
1551 * picass: associate pic to audio file
1552 * snp: set numplayed
1554 static int com_set(__a_unused int fd, int argc, char *argv[])
1559 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1562 return -E_MYSQL_SYNTAX;
1564 for (i = 2; i < argc; i++) {
1565 ebn = escaped_basename(argv[i]);
1568 q = make_message("update data set %s = %lu "
1569 "where name = '%s'", field, id, ebn);
1571 ret = real_query(q);
1580 * picch: change entry's name in pics table
1582 static int com_picch(__a_unused int fd, int argc, char *argv[])
1589 return -E_MYSQL_SYNTAX;
1592 tmp = escape_str(argv[2]);
1595 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1597 ret = real_query(q);
1603 * piclist: print list of pics in db
1605 static int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1607 void *result = NULL;
1609 unsigned long *length;
1613 return -E_MYSQL_SYNTAX;
1614 result = get_result("select id,name,pic from pics order by id");
1617 while ((row = mysql_fetch_row(result))) {
1618 length = mysql_fetch_lengths(result);
1619 if (!row || !row[0] || !row[1] || !row[2])
1621 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1627 mysql_free_result(result);
1632 * picdel: delete picture from database
1634 static int com_picdel(int fd, int argc, char *argv[])
1642 return -E_MYSQL_SYNTAX;
1643 for (i = 1; i < argc; i++) {
1645 q = make_message("delete from pics where id = %lu", id);
1646 ret = real_query(q);
1650 aff = mysql_affected_rows(mysql_ptr);
1652 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1657 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1658 ret = real_query(q);
1664 * pic: get picture by name or by number
1666 static int com_pic(int fd, int argc, char *argv[])
1668 void *result = NULL;
1670 unsigned long *length, id;
1672 char *q, *name = NULL;
1675 ret = -E_GET_AUDIO_FILE;
1676 name = get_current_audio_file();
1679 name = escaped_basename(argv[1]);
1684 id = atoi(name + 1);
1686 id = get_pic_id_by_name(name);
1690 q = make_message("select pic from pics where id = '%lu'", id);
1691 result = get_result(q);
1695 row = mysql_fetch_row(result);
1697 if (!row || !row[0])
1699 length = mysql_fetch_lengths(result);
1700 ret = send_bin_buffer(fd, row[0], *length);
1702 mysql_free_result(result);
1707 static int com_strdel(__a_unused int fd, int argc, char *argv[])
1713 return -E_MYSQL_SYNTAX;
1714 tmp = escape_str(argv[1]);
1717 q = make_message("delete from streams where name='%s'", tmp);
1719 ret = real_query(q);
1727 static int com_ls(int fd, int argc, char *argv[])
1732 unsigned int num_rows;
1735 return -E_MYSQL_SYNTAX;
1737 char *tmp = escape_str(argv[1]);
1740 q = make_message("select name from data where name like '%s'",
1744 q = para_strdup("select name from data");
1745 result = get_result(q);
1749 num_rows = mysql_num_rows(result);
1752 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1753 mysql_free_result(result);
1760 static int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1765 void *result2 = NULL;
1766 const char *fmt = "select count(name) from data where %s='1'";
1767 int ret = -E_NORESULT;
1770 return -E_MYSQL_SYNTAX;
1771 result = get_all_attributes();
1774 while ((row = mysql_fetch_row(result))) {
1781 buf = make_message(fmt, row[0]);
1782 result2 = get_result(buf);
1787 row2 = mysql_fetch_row(result2);
1788 if (!row2 || !row2[0])
1790 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1797 mysql_free_result(result2);
1799 mysql_free_result(result);
1803 static int get_numplayed(char *name)
1807 const char *fmt = "select numplayed from data where name = '%s'";
1808 char *buf = make_message(fmt, name);
1809 int ret = -E_NORESULT;
1811 result = get_result(buf);
1816 row = mysql_fetch_row(result);
1817 if (!row || !row[0])
1822 mysql_free_result(result);
1826 static int update_audio_file(char *name)
1829 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1830 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1832 char *ebn = escaped_basename(name);
1837 q = make_message(fmt1, ebn);
1838 ret = real_query(q);
1842 ret = get_numplayed(ebn);
1845 q = make_message(fmt2, ret + 1, ebn);
1846 ret = real_query(q);
1853 /* If called as child, mmd_lock must be held */
1854 static void update_mmd(char *info)
1856 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1857 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1858 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1861 static void update_audio_file_server_handler(char *name)
1864 info = get_selector_info(name);
1867 update_audio_file(name);
1870 static int com_us(__a_unused int fd, int argc, char *argv[])
1876 return -E_MYSQL_SYNTAX;
1877 tmp = escape_str(argv[1]);
1880 ret = update_audio_file(argv[1]);
1885 static void refresh_selector_info(void)
1887 char *name = get_current_audio_file();
1892 info = get_selector_info(name);
1900 /* select previous/next stream */
1901 static int com_ps(__a_unused int fd, int argc, char *argv[])
1903 char *query, *stream = get_current_stream();
1904 void *result = get_result("select name from streams");
1906 int match = -1, ret, i;
1907 unsigned int num_rows;
1910 return -E_MYSQL_SYNTAX;
1914 num_rows = mysql_num_rows(result);
1915 ret = -E_EMPTY_RESULT;
1919 for (i = 0; i < num_rows; i++) {
1920 row = mysql_fetch_row(result);
1921 if (!row || !row[0])
1923 if (!strcmp(row[0], "current_stream"))
1925 if (!strcmp(row[0], stream)) {
1933 if (!strcmp(argv[0], "ps"))
1934 i = match > 0? match - 1 : num_rows - 1;
1936 i = match < num_rows - 1? match + 1 : 0;
1938 mysql_data_seek(result, i);
1939 row = mysql_fetch_row(result);
1940 if (!row || !row[0])
1942 if (!strcmp(row[0], "current_stream")) {
1943 if (!strcmp(argv[0], "ps")) {
1945 i = i < 0? i + num_rows : i;
1948 i = i > num_rows - 1? i - num_rows : i;
1950 mysql_data_seek(result, i);
1951 row = mysql_fetch_row(result);
1952 if (!row || !row[0])
1955 query = make_message("update streams set def='%s' where name = "
1956 "'current_stream'", row[0]);
1957 ret = real_query(query);
1959 refresh_selector_info();
1963 mysql_free_result(result);
1968 static int com_streams(int fd, int argc, __a_unused char *argv[])
1970 unsigned int num_rows;
1971 int i, ret = -E_NORESULT;
1975 if (argc > 1 && strcmp(argv[1], "current_stream"))
1976 return -E_MYSQL_SYNTAX;
1978 char *cs = get_current_stream();
1979 ret = send_va_buffer(fd, "%s\n", cs);
1983 result = get_result("select name from streams");
1986 num_rows = mysql_num_rows(result);
1991 for (i = 0; i < num_rows; i++) {
1992 row = mysql_fetch_row(result);
1993 if (!row || !row[0])
1995 if (strcmp(row[0], "current_stream"))
1996 send_va_buffer(fd, "%s\n", row[0]);
2001 mysql_free_result(result);
2005 /* query stream definition */
2006 static int com_strq(int fd, int argc, char *argv[])
2014 ret = -E_GET_STREAM;
2015 name = get_current_stream();
2018 name = escaped_basename(argv[1]);
2023 query = make_message("select def from streams where name='%s'", name);
2025 result = get_result(query);
2030 row = mysql_fetch_row(result);
2031 if (!row || !row[0])
2033 /* no '\n' needed */
2034 ret = send_buffer(fd, row[0]);
2037 mysql_free_result(result);
2041 /* change stream / change stream and play */
2042 static int com_cs(int fd, int argc, char *argv[])
2044 int ret, stream_change;
2045 char *query, *stream = NULL;
2046 char *old_stream = get_current_stream();
2047 int csp = !strcmp(argv[0], "csp");
2049 ret = -E_MYSQL_SYNTAX;
2055 ret = send_va_buffer(fd, "%s\n", old_stream);
2059 /* test if stream is valid, no need to escape argv[1] */
2060 query = get_query(argv[1], NULL, 0);
2065 stream = escape_str(argv[1]);
2068 stream_change = strcmp(stream, old_stream);
2069 if (stream_change) {
2070 ret = change_stream(stream);
2073 refresh_selector_info();
2077 mmd->new_afs_status_flags |= AFS_PLAYING;
2079 mmd->new_afs_status_flags |= AFS_NEXT;
2092 static int com_sl(int fd, int argc, char *argv[])
2094 void *result = NULL;
2096 int ret, i, skip = !strcmp(argv[0], "skip");
2097 char *query, *stream, *tmp;
2098 unsigned int num_rows, num;
2101 return -E_MYSQL_SYNTAX;
2102 num = atoi(argv[1]);
2104 return -E_MYSQL_SYNTAX;
2106 stream = get_current_stream();
2108 return -E_GET_STREAM;
2110 stream = escape_str(argv[2]);
2114 tmp = get_query(stream, NULL, 0);
2117 return -E_GET_QUERY;
2118 query = make_message("%s limit %d", tmp, num);
2121 result = get_result(query);
2125 ret = -E_EMPTY_RESULT;
2126 num_rows = mysql_num_rows(result);
2129 for (i = 0; i < num_rows && i < num; i++) {
2130 row = mysql_fetch_row(result);
2132 send_va_buffer(fd, "Skipping %s\n", row[0]);
2133 update_audio_file(row[0]);
2135 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2140 mysql_free_result(result);
2145 * update attributes of name
2147 static int update_atts(int fd, char *name, char *atts)
2150 char *ebn, *q, *old, *new = NULL;
2154 ebn = escaped_basename(name);
2157 q = make_message("update data set %s where name = '%s'", atts, ebn);
2158 old = get_atts(ebn, 0);
2159 send_va_buffer(fd, "old: %s\n", old);
2161 ret = real_query(q);
2165 new = get_atts(ebn, 0);
2166 ret = send_va_buffer(fd, "new: %s\n", new);
2176 static int com_sa(int fd, int argc, char *argv[])
2179 char *atts = NULL, *name;
2182 return -E_MYSQL_SYNTAX;
2183 for (i = 1; i < argc; i++) {
2185 char *esc, *tmp, *p =argv[i];
2186 int len = strlen(p);
2190 switch (p[len - 1]) {
2201 esc = escape_str(p);
2204 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2207 atts = para_strcat(atts, tmp);
2213 if (i >= argc) { /* no name given, use current af */
2214 ret = -E_GET_AUDIO_FILE;
2215 if (!(name = get_current_audio_file()))
2217 ret = update_atts(fd, name, atts);
2221 for (; argv[i] && ret >= 0; i++)
2222 ret = update_atts(fd, argv[i], atts);
2224 refresh_selector_info();
2233 static int com_cam(int fd, int argc, char *argv[])
2235 char *name = NULL, *meta = NULL, *atts = NULL;
2239 return -E_MYSQL_SYNTAX;
2240 if (!(name = escaped_basename(argv[1])))
2243 if (!(atts = get_atts(name, 1)))
2246 if (!(meta = get_meta(name, 0)))
2248 for (i = 2; i < argc; i++) {
2251 if (!(ebn = escaped_basename(argv[i])))
2253 ret = send_va_buffer(fd, "updating %s\n", ebn);
2258 q = make_message("update data set %s where name = '%s'",
2260 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2261 ret = real_query(q);
2281 static int com_vrfy(int fd, int argc, __a_unused char *argv[])
2284 int ret, vrfy_mode = strcmp(argv[0], "clean");
2285 void *result = NULL;
2286 unsigned int num_rows;
2291 return -E_MYSQL_SYNTAX;
2293 result = get_result("select data.name from data left join dir on "
2294 "dir.name = data.name where dir.name is NULL");
2297 num_rows = mysql_num_rows(result);
2299 ret = send_buffer(fd, "No invalid entries\n");
2303 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2304 num_rows == 1? "y" : "ies");
2305 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2308 while ((row = mysql_fetch_row(result))) {
2313 escaped_name = escape_str(row[0]);
2316 send_va_buffer(fd, "deleting %s\n", escaped_name);
2317 query = make_message("delete from data where name = '%s'",
2319 ret = real_query(query);
2327 mysql_free_result(result);
2331 static FILE *out_file;
2333 static int mysql_write_tmp_file(const char *dir, const char *name)
2335 int ret = -E_TMPFILE;
2336 char *msg = make_message("%s\t%s\n", dir, name);
2337 if (fputs(msg, out_file) != EOF)
2346 static int com_upd(int fd, int argc, __a_unused char *argv[])
2348 char *tempname = NULL, *query = NULL;
2349 int ret, out_fd = -1, num = 0;
2350 void *result = NULL;
2351 unsigned int num_rows;
2355 return -E_MYSQL_SYNTAX;
2357 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2358 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2362 out_file = fdopen(out_fd, "w");
2367 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2369 num = ftell(out_file);
2371 * we have to make sure the file hit the disk before we call
2376 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2379 if ((ret = real_query("delete from dir")) < 0)
2381 query = make_message("load data infile '%s' ignore into table dir "
2382 "fields terminated by '\t' lines terminated by '\n' "
2383 "(dir, name)", tempname);
2384 ret = real_query(query);
2388 result = get_result("select dir.name from dir left join data on "
2389 "data.name = dir.name where data.name is NULL");
2393 num_rows = mysql_num_rows(result);
2395 ret = send_buffer(fd, "no new entries\n");
2398 while ((row = mysql_fetch_row(result))) {
2403 send_va_buffer(fd, "new entry: %s\n", row[0]);
2404 erow = escape_str(row[0]);
2407 query = make_message("insert into data (name, pic_id) values "
2408 "('%s','%s')", erow, "1");
2410 ret = real_query(query);
2423 mysql_free_result(result);
2427 static char **server_get_audio_file_list(unsigned int num)
2429 char **list = para_malloc((num + 1) * sizeof(char *));
2430 char *tmp, *query, *stream = get_current_stream();
2431 void *result = NULL;
2432 unsigned int num_rows;
2436 tmp = get_query(stream, NULL, 1);
2440 query = make_message("%s limit %d", tmp, num);
2442 result = get_result(query);
2446 num_rows = mysql_num_rows(result);
2449 for (i = 0; i < num_rows && i < num; i++) {
2450 row = mysql_fetch_row(result);
2451 if (!row || !row[0])
2453 list[i] = para_strdup(row[0]);
2466 mysql_free_result(result);
2471 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2472 * on errors. Called from parent on startup and also from com_cdb().
2474 static int init_mysql_server(void)
2476 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2478 mysql_ptr = mysql_init(NULL);
2480 PARA_CRIT_LOG("%s", "mysql init error\n");
2483 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2484 conf.mysql_port_arg);
2485 if (!conf.mysql_user_arg)
2488 * If host is NULL a connection to the local host is assumed,
2489 * If user is NULL, the current user is assumed
2491 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2492 conf.mysql_host_arg,
2493 conf.mysql_user_arg,
2494 conf.mysql_passwd_arg,
2495 conf.mysql_database_arg,
2496 conf.mysql_port_arg, NULL, 0))) {
2497 PARA_CRIT_LOG("%s", "connect error\n");
2500 PARA_INFO_LOG("%s", "success\n");
2504 /* mmd lock must be held */
2505 static void write_msg2mmd(int success)
2507 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2508 success < 0? PARA_STRERROR(-success) :
2509 "successfully connected to mysql server",
2510 success < 0? "" : mysql_get_server_info(mysql_ptr));
2513 /* create database */
2514 static int com_cdb(int fd, int argc, char *argv[])
2520 PARA_INFO_LOG("%s", "closing database\n");
2521 mysql_close(mysql_ptr);
2523 /* dont use any database */
2524 conf.mysql_database_arg = NULL; /* leak? */
2525 ret = -E_MYSQL_INIT;
2526 if (init_mysql_server() < 0 || !mysql_ptr)
2529 conf.mysql_database_arg = para_strdup("paraslash");
2532 conf.mysql_database_arg = escape_str(argv[1]);
2533 if (!conf.mysql_database_arg)
2536 query = make_message("create database %s", conf.mysql_database_arg);
2537 ret = real_query(query);
2541 /* reconnect with database just created */
2542 mysql_close(mysql_ptr);
2543 ret = -E_MYSQL_INIT;
2544 if (init_mysql_server() < 0 || !mysql_ptr)
2550 if (real_query("create table data (name varchar(255) binary not null "
2552 "lastplayed datetime not null default "
2554 "numplayed int not null default 0, "
2555 "pic_id bigint unsigned not null default 1)") < 0)
2557 if (real_query("create table dir (name varchar(255) binary not null "
2558 "primary key, dir varchar(255) default null)") < 0)
2560 if (real_query("create table pics ("
2561 "id bigint(20) unsigned not null primary key "
2563 "name varchar(255) binary not null, "
2564 "pic mediumblob not null)") < 0)
2566 if (real_query("create table streams ("
2567 "name varchar(255) binary not null primary key, "
2568 "def blob not null)") < 0)
2570 if (real_query("insert into streams (name, def) values "
2571 "('current_stream', '(none)')") < 0)
2573 ret = send_va_buffer(fd, "successfully created database %s\n",
2574 conf.mysql_database_arg);
2579 static void shutdown_connection(void)
2582 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2583 mysql_close(mysql_ptr);
2589 * the init function of the mysql-based audio file selector
2591 * Check the command line options and initialize all function pointers of \a db.
2592 * Connect to the mysql server and initialize the info string.
2594 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2597 int mysql_selector_init(struct audio_file_selector *db)
2601 if (!conf.mysql_passwd_given)
2602 return -E_NO_MYSQL_PASSWD;
2603 if (!conf.mysql_audio_file_dir_given)
2604 return -E_NO_AF_DIR;
2606 db->cmd_list = cmds;
2607 db->get_audio_file_list = server_get_audio_file_list;
2608 db->update_audio_file = update_audio_file_server_handler;
2609 db->shutdown = shutdown_connection;
2610 ret = init_mysql_server();
2612 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2614 return 1; /* return success even if connect failed to give the
2615 * user the chance to exec com_cdb