From: Andre Date: Fri, 10 Mar 2006 20:53:05 +0000 (+0100) Subject: rename mysql.c to mysql_selector.c X-Git-Tag: v0.2.11~13 X-Git-Url: http://git.tuebingen.mpg.de/?p=paraslash.git;a=commitdiff_plain;h=1776383c89bcc2bec6b469b3d58d5f5c3a37f090;hp=bae3ea84f99790bf0ab3361a51a8115216699b6b rename mysql.c to mysql_selector.c Just to be consistent. --- diff --git a/configure.ac b/configure.ac index e05765ec..06cbe7ac 100644 --- a/configure.ac +++ b/configure.ac @@ -150,10 +150,10 @@ AC_CHECK_LIB([mysqlclient], [mysql_init], [], [ ]) if test "$have_mysql" = "yes"; then server_ldflags="$server_ldflags -lmysqlclient" - server_errlist_objs="$server_errlist_objs mysql" + server_errlist_objs="$server_errlist_objs mysql_selector" AC_DEFINE(HAVE_MYSQL, 1, [define to 1 to turn on mysql support]) else - AC_MSG_WARN([no libmysqlclient, cannot build mysql-based dbtool]) + AC_MSG_WARN([cannot build mysql-based audio file selector]) fi ########################################################################### ogg have_ogg="yes" diff --git a/error.h b/error.h index f15d0dc6..335950b7 100644 --- a/error.h +++ b/error.h @@ -25,7 +25,8 @@ enum para_subsystem {SS_RECV, SS_RECV_COMMON, SS_FILTER_CHAIN, SS_WAV, SS_COMPRESS, SS_OGGDEC, SS_FILTER, SS_COMMAND, SS_RANDOM_SELECTOR, SS_PLAYLIST_SELECTOR, SS_CRYPT, SS_HTTP_SEND, SS_ORTP_SEND, SS_DB, SS_OGG, - SS_MP3, SS_MP3DEC, SS_SERVER, SS_AFS, SS_MYSQL, SS_IPC, SS_RINGBUFFER}; + SS_MP3, SS_MP3DEC, SS_SERVER, SS_AFS, SS_MYSQL_SELECTOR, + SS_IPC, SS_RINGBUFFER}; #define NUM_SS (SS_RINGBUFFER + 1) extern const char **para_errlist[]; /** \endcond */ @@ -191,7 +192,7 @@ extern const char **para_errlist[]; PARA_ERROR(NOTHING_FOUND, "no audio files found"), \ -#define MYSQL_ERRORS \ +#define MYSQL_SELECTOR_ERRORS \ PARA_ERROR(MYSQL_SYNTAX, "mysql syntax error"), \ PARA_ERROR(NOTCONN, "not connected to mysql server"), \ PARA_ERROR(TOOBIG, "mysql: file too large"), \ @@ -349,7 +350,7 @@ SS_ENUM(CRYPT); SS_ENUM(HTTP_SEND); SS_ENUM(ORTP_SEND); SS_ENUM(DB); -SS_ENUM(MYSQL); +SS_ENUM(MYSQL_SELECTOR); SS_ENUM(IPC); SS_ENUM(RINGBUFFER); /** \endcond */ diff --git a/mysql.c b/mysql.c deleted file mode 100644 index 9292ab29..00000000 --- a/mysql.c +++ /dev/null @@ -1,2539 +0,0 @@ -/* - * Copyright (C) 1999-2006 Andre Noll - * - * This program is free software; you can redistribute it and/or modify - * it under the terms of the GNU General Public License as published by - * the Free Software Foundation; either version 2 of the License, or - * (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU General Public License - * along with this program; if not, write to the Free Software - * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA. - */ - -/** \file mysql.c para_server's mysql-based audio file selector */ - -/** \cond some internal constants */ -#define MEDIUM_BLOB_SIZE 16777220 /* (2**24 + 4) */ -#define BLOB_SIZE 65539 /* (2**16 + 3) */ -/** \endcond */ -#include "server.cmdline.h" -#include "server.h" -#include "afs.h" -#include "db.h" -#include -#include -#include "error.h" -#include "net.h" -#include "string.h" - -extern struct gengetopt_args_info conf; -/** pointer to the shared memory area */ -extern struct misc_meta_data *mmd; - -static void *mysql_ptr = NULL; - - -static int com_cam(int, int, char **); -static int com_cdb(int, int, char **); -static int com_cs(int, int, char **); -static int com_da(int, int, char **); -static int com_hist(int, int, char **); -static int com_info(int, int, char **); -static int com_laa(int, int, char **); -static int com_last(int, int, char **); -static int com_ls(int, int, char **); -static int com_mbox(int, int, char **); -static int com_mv(int, int, char **); -static int com_na(int, int, char **); -static int com_pic(int, int, char **); -static int com_picch(int, int, char **); -static int com_picdel(int, int, char **); -static int com_piclist(int, int, char **); -static int com_ps(int, int, char **); -static int com_rm_ne(int, int, char **); -static int com_sa(int, int, char **); -static int com_set(int, int, char **); -static int com_sl(int, int, char **); -static int com_stradd_picadd(int, int, char **); -static int com_streams(int, int, char **); -static int com_strdel(int, int, char **); -static int com_strq(int, int, char **); -static int com_summary(int, int, char **); -static int com_upd(int, int, char **); -static int com_us(int, int, char **); -static int com_verb(int, int, char **); -static int com_vrfy(int, int, char **); - -static struct server_command cmds[] = { -{ -.name = "cam", -.handler = com_cam, -.perms = DB_READ|DB_WRITE, -.description = "copy all metadata", -.synopsis = "cam source dest1 [dest2 ...]", -.help = - -"Copy attributes and other meta data from source file to destination\n" -"file(s). Useful for files that have been renamed.\n" - -}, -{ -.name = "cdb", -.handler = com_cdb, -.perms = DB_READ|DB_WRITE, -.description = "create database", -.synopsis = "cdb [name]", -.help = - -"\tCreate database name containing the initial columns for basic\n" -"\tinteroperation with server. This command has to be used only once\n" -"\twhen you use the mysql database tool for the very first time.\n" -"\n" -"\tThe optional name defaults to 'paraslash' if not given.\n" - -}, -{ -.name = "clean", -.handler = com_vrfy, -.perms = DB_READ | DB_WRITE, -.description = "nuke invalid entries in database", -.synopsis = "clean", -.help = - -"If the vrfy command shows you any invalid entries in your database,\n" -"you can get rid of them with clean. Always run 'upd' and 'vrfy'\n" -"before running this command. Use with caution!\n" - -}, -{ -.name = "cs", -.handler = com_cs, -.perms = AFS_WRITE | DB_READ | DB_WRITE, -.description = "change stream", -.synopsis = "cs [s]", -.help = - -"Selects stream s or prints current stream when s was not given.\n" - -}, -{ -.name = "csp", -.handler = com_cs, -.perms = AFS_WRITE | DB_READ, -.description = "change stream and play", -.synopsis = "csp s", -.help = - -"Select stream s and start playing. If this results in a stream-change,\n" -"skip rest of current audio file.\n" - -}, -{ -.name = "da", -.handler = com_da, -.perms = DB_READ | DB_WRITE, -.description = "drop attribute from database", -.synopsis = "da att", -.help = - -"Use with caution. All info on attribute att will be lost.\n" - -}, -{ -.name = "hist", -.handler = com_hist, -.perms = DB_READ, -.description = "print history", -.synopsis = "hist", -.help = - -"Print list of all audio files together with number of days since each\n" -"file was last played.\n" - -}, -{ -.name = "info", -.handler = com_info, -.perms = DB_READ, -.description = "print database info", -.synopsis = "info [af]", -.help = - -"print database informations for audio file af. Current audio file is\n" -"used if af is not given.\n" - -}, -{ -.name = "la", -.handler = com_info, -.perms = DB_READ, -.description = "list attributes", -.synopsis = "la [af]", -.help = - -"List attributes of audio file af or of current audio file when invoked\n" -"without arguments.\n" - -}, -{ -.name = "laa", -.handler = com_laa, -.perms = DB_READ, -.description = "list available attributes", -.synopsis = "laa", -.help = - -"What should I say more?\n" - -}, -{ -.name = "last", -.handler = com_last, -.perms = DB_READ, -.description = "print list of audio files, ordered by lastplayed time", -.synopsis = "last [n]", -.help = - -"The optional number n defaults to 10 if not specified.\n" - -}, -{ -.name = "ls", -.handler = com_ls, -.perms = DB_READ, -.description = "list all audio files that match a LIKE pattern", -.synopsis = "ls [pattern]", -.help = - -"\tIf pattern was not given, print list of all audio files known\n" -"\tto the mysql database tool. See the documentation of mysql\n" -"\tfor the definition of LIKE patterns.\n" - -}, -{ -.name = "mbox", -.handler = com_mbox, -.perms = DB_READ, -.description = "dump audio file list in mbox format", -.synopsis = "mbox [p]", -.help = - -"\tDump list of audio files in mbox format (email) to stdout. If\n" -"\tthe optional pattern p is given, only those audio files,\n" -"\twhose basename match p are going to be included. Otherwise,\n" -"\tall files are selected.\n" -"\n" -"EXAMPLE\n" -"\tThe mbox command can be used together with your favorite\n" -"\tmailer (this example uses mutt) for browsing the audio file\n" -"\tcollection:\n" -"\n" -"\t\tpara_client mbox > ~/para_mbox\n" -"\n" -"\t\tmutt -F ~/.muttrc.para -f ~/para_mbox\n" -"\n" -"\tFor playlists, you can use mutt's powerful pattern matching\n" -"\tlanguage to select files. If you like to tag all files\n" -"\tcontaining the pattern 'foo', type 'T', then '~s foo'.\n" -"\n" -"\tWhen ready with the list, type ';|' (i.e., hit the semicolon\n" -"\tkey to apply the next mutt command to all tagged messages,\n" -"\tthen the pipe key) to pipe the selected \"mails\" to a\n" -"\tsuitable script which adds a paraslash stream where exactly\n" -"\tthese files are admissable or does whatever thou wilt.\n" - -}, -{ -.name = "mv", -.handler = com_mv, -.perms = DB_READ | DB_WRITE, -.description = "rename entry in database", -.synopsis = "mv oldname newname", -.help = - -"Rename oldname to newname. This updates the data table to reflect the\n" -"new name. All internal data (numplayed, lastplayed, picid,..) is kept.\n" -"If newname is a full path, the dir table is updated as well.\n" - -}, -{ -.name = "na", -.handler = com_na, -.perms = DB_READ | DB_WRITE, -.description = "add new attribute to database", -.synopsis = "na att", -.help = - -"This adds a column named att to your mysql database. att should only\n" -"contain letters and numbers, in paricular, '+' and '-' are not allowed.\n" - -}, -{ -.name = "ne", -.handler = com_rm_ne, -.perms = DB_READ | DB_WRITE, -.description = "add new database entries", -.synopsis = "ne file1 [file2 [...]]", -.help = - -"Add the given filename(s) to the database, where file1,... must\n" -"be full path names. This command might be much faster than 'upd'\n" -"if the number of given files is small.\n" - -}, -{ -.name = "ns", -.handler = com_ps, -.perms = AFS_WRITE | DB_READ | DB_WRITE, -.description = "change to next stream", -.synopsis = "ns", -.help = - -"Cycle forwards through stream list.\n" - -}, -{ -.name = "pic", -.handler = com_pic, -.perms = DB_READ, -.description = "get picture by name or by identifier", -.synopsis = "pic [name]", -.help = - -"\tDump jpg image that is associated to given audio file (current\n" -"\taudio file if not specified) to stdout. If name starts with\n" -"\t'#' it is interpreted as an identifier instead and the picture\n" -"\thaving that identifier is dumped to stdout.\n" -"\n" -"EXAMPLE\n" -"\n" -"\tpara_client pic '#123' > pic123.jpg\n" - -}, -{ -.name = "picadd", -.handler = com_stradd_picadd, -.perms = DB_READ | DB_WRITE, -.description = "add picture to database", -.synopsis = "picadd [picname]", -.help = - -"\tRead jpeg file from stdin and store it as picname in database.\n" -"\n" -"EXAMPLE\n" -"\n" -"\tpara_client picadd foo.jpg < foo.jpg\n" - -}, -{ -.name = "picass", -.handler = com_set, -.perms = DB_READ | DB_WRITE, -.description = "associate a picture to file(s)", -.synopsis = "picass pic_id file1 [file2...]", -.help = - -"Associate the picture given by pic_id to all given files.\n" - -}, -{ -.name = "picch", -.handler = com_picch, -.perms = DB_READ | DB_WRITE, -.description = "change name of picture", -.synopsis = "picch id new_name", -.help = - -"Asign new_name to picture with identifier id.\n" - -}, -{ -.name = "picdel", -.handler = com_picdel, -.perms = DB_READ | DB_WRITE, -.description = "delete picture from database", -.synopsis = "picdel id1 [id2...]", -.help = - -"Delete each given picture from database.\n" - -}, -{ -.name = "piclist", -.handler = com_piclist, -.perms = DB_READ, -.description = "print list of pictures", -.synopsis = "piclist", -.help = - -"Print id, name and length of each picture contained in the database.\n" - -}, -{ -.name = "ps", -.handler = com_ps, -.perms = AFS_WRITE | DB_READ | DB_WRITE, -.description = "change to previous stream", -.synopsis = "ps", -.help = - -"Cycle backwards through stream list.\n" - -}, -{ -.name = "rm", -.handler = com_rm_ne, -.perms = DB_READ | DB_WRITE, -.description = "remove entries from database", -.synopsis = "rm name1 [name2 [...]]", -.help = - -"Remove name1, name2, ... from the data table. Use with caution\n" - -}, -{ -.name = "sa", -.handler = com_sa, -.perms = DB_READ | DB_WRITE, -.description = "set/unset attributes", -.synopsis = "sa at1<'+' | '-'> [at2<'+' | '-'> ] [af1 ...]", -//.synopsis = "foo", -.help = - -"Set ('+') or unset ('-') attribute at1, at2 etc. for given list of\n" -"audio files. If no audio files were given the current audio file is\n" -"used. Example:\n" -"\n" -"sa rock+ punk+ classic- LZ__Waldsterben.mp3\n" -"\n" -"sets the 'rock' and the 'punk' attribute but unsets the 'classic'\n" -"attribute.\n" - -}, -{ -.name = "skip", -.handler = com_sl, -.perms = DB_READ | DB_WRITE, -.description = "skip subsequent audio files(s)", -.synopsis = "skip n [s]", -.help = - -"Skip the next n audio files of stream s. This is equivalent to the\n" -"command 'sl n s', followed by 'us name' for each name the output of sl.\n" - -}, -{ -.name = "sl", -.handler = com_sl, -.perms = DB_READ, -.description = "print score list", -.synopsis = "sl n [s]", -.help = - -"Print sorted list of maximal n lines. Each line is an admissible entry\n" -"with respect to stream s. The list is sorted by score-value which is\n" -"given by the definition of s. If s is not given, the current stream\n" -"is used. Example:\n" -"\n" -" sl 1\n" -"\n" -"shows you the audio file the server would select right now.\n" - -}, -{ -.name = "snp", -.handler = com_set, -.perms = DB_READ | DB_WRITE, -.description = "set numplayed", -.synopsis = "snp number af1 [af2 ...]", -.help = - -"Update the numplayed field in the data table for all given audio files.\n" - -}, -{ -.name = "stradd", -.handler = com_stradd_picadd, -.perms = DB_READ | DB_WRITE, -.description = "add stream", -.synopsis = "stradd s", -.help = - -"Add stream s to the list of available streams. The stream definition\n" -"for s is read from stdin and is then sent to para_server. Example:\n" -"\n" -" echo 'deny: NAME_LIKE(%Madonna%)' | para_client stradd no_madonna\n" -"\n" -"adds the new stream 'no_madonna' to the list of available streams. A given\n" -"audio file is admissible for this stream iff its basename does not contain the\n" -"string 'Madonna'.\n" - - -}, -{ -.name = "strdel", -.handler = com_strdel, -.perms = DB_READ | DB_WRITE, -.description = "delete stream", -.synopsis = "strdel s", -.help = - -"Remove stream s from database.\n" - -}, -{ -.name = "streams", -.handler = com_streams, -.perms = DB_READ, -.description = "list streams", -.synopsis = "streams", -.help = - -"Print list of available streams. Use 'cs' to switch to any of these.\n" - -}, -{ -.name = "strq", -.handler = com_strq, -.perms = DB_READ, -.description = "query stream definition", -.synopsis = "strq [s]", -.help = - -"Print definition of stream s to stdout. Use current stream if s was\n" -"not given.\n" - -}, -{ -.name = "summary", -.handler = com_summary, -.perms = DB_READ, -.description = "list attributes", -.synopsis = "summary", -.help = - -"\tPrint a list of attributes together with number of audio\n" -"\tfiles having that attribute set.\n" - -}, -{ -.name = "upd", -.handler = com_upd, -.perms = DB_READ | DB_WRITE, -.description = "update database", -.synopsis = "upd", -.help = - -"This command uses the --audio_file_dir option of para_server to locate\n" -"your audio files. New files are then added to the mysql database. Use\n" -"this command if you got new files or if you have moved some files\n" -"around.\n" - -}, -{ -.name = "us", -.handler = com_us, -.perms = DB_READ | DB_WRITE, -.description = "update lastplayed time", -.synopsis = "us name", -.help = - -"Update lastplayed time without actually playing the thing.\n" - -}, -{ -.name = "verb", -.handler = com_verb, -.perms = DB_READ | DB_WRITE, -.description = "send verbatim sql query", -.synopsis = "verb cmd", -.help = - -"Send cmd to mysql server. For expert/debugging only. Note that cmd\n" -"usually must be escaped. Use only if you know what you are doing!\n" - -}, -{ -.name = "vrfy", -.handler = com_vrfy, -.perms = DB_READ, -.description = "list invalid entries in database", -.synopsis = "vrfy", -.help = - -"Show what clean would delete. Run 'upd' before this command to make\n" -"sure your database is up to date.\n" - -}, -{ -.name = NULL, -} -}; - -static struct para_macro macro_list[] = { - { .name = "IS_N_SET", - .replacement = "(data.%s != '1')" - }, { - .name = "IS_SET", - .replacement = "(data.%s = '1')" - }, { - .name = "PICID", - .replacement = "%sdata.Pic_Id" - }, { - .name = "NAME_LIKE", - .replacement = "(data.name like '%s')" - }, { - .name = "LASTPLAYED", - .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())" - "-UNIX_TIMESTAMP(data.Lastplayed))/60)" - }, { - .name = "NUMPLAYED", - .replacement = "%sdata.Numplayed" - }, { - .name = NULL, - } -}; - -static int real_query(char *query) -{ - if (!mysql_ptr) - return -E_NOTCONN; - PARA_DEBUG_LOG("%s\n", query); - if (mysql_real_query(mysql_ptr, query, strlen(query))) { - PARA_ERROR_LOG("real_query error (%s)\n", - mysql_error(mysql_ptr)); - return -E_QFAILED; - } - return 1; -} - -/* - * Use open connection given by mysql_ptr to query server. Returns a - * result pointer on succes and NULL on errors - */ -static struct MYSQL_RES *get_result(char *query) -{ - void *result; - - if (real_query(query) < 0) - return NULL; - result = mysql_store_result(mysql_ptr); - if (!result) - PARA_ERROR_LOG("%s", "store_result error\n"); - return result; -} -/* - * write input from fd to dynamically allocated char array, - * but maximal max_size byte. Return size. - */ -static int fd2buf(int fd, char **buf_ptr, size_t max_size) -{ - const size_t chunk_size = 1024; - size_t size = 2048; - char *buf = para_malloc(size * sizeof(char)), *p = buf; - int ret; - - while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) { - p += ret; - if ((p - buf) + chunk_size >= size) { - char *tmp; - - size *= 2; - if (size > max_size) { - ret = -E_TOOBIG; - goto out; - } - tmp = para_realloc(buf, size); - p = (p - buf) + tmp; - buf = tmp; - } - } - if (ret < 0) - goto out; - *buf_ptr = buf; - ret = p - buf; -out: - if (ret < 0 && buf) - free(buf); - return ret; -} - -static char *escape_blob(char* old, int size) -{ - char *new; - - if (!mysql_ptr || size < 0) - return NULL; - new = para_malloc(2 * size * sizeof(char) + 1); - mysql_real_escape_string(mysql_ptr, new, old, size); - return new; -} - -static char *escape_str(char* old) -{ - return escape_blob(old, strlen(old)); -} - -static char *escaped_basename(const char *name) -{ - char *esc, *bn = para_basename(name); - - if (!bn) - return NULL; - esc = escape_str(bn); - free(bn); - return esc; -} - -/* - * new attribute - */ -static int com_na(__unused int fd, int argc, char *argv[]) -{ - char *q; - int ret; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - q = make_message("alter table data add %s char(1) " - "not null default 0", argv[1]); - ret = real_query(q); - free(q); - return ret; -} - -/* - * delete attribute - */ -static int com_da(__unused int fd, int argc, char *argv[]) -{ - char *q; - int ret; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - q = make_message("alter table data drop %s", argv[1]); - ret = real_query(q); - free(q); - return ret; -} - -/* stradd/pic_add */ -static int com_stradd_picadd(int fd, int argc, char *argv[]) -{ - char *blob = NULL, *esc_blob = NULL, *q; - const char *fmt, *del_fmt; - int ret, stradd = strcmp(argv[0], "picadd"); - size_t size; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - if (strlen(argv[1]) >= MAXLINE - 1) - return -E_NAMETOOLONG; - if (!mysql_ptr) - return -E_NOTCONN; - if (stradd) { - size = BLOB_SIZE; - fmt = "insert into streams (name, def) values ('%s','%s')"; - del_fmt="delete from streams where name='%s'"; - } else { - size = MEDIUM_BLOB_SIZE; - fmt = "insert into pics (name, pic) values ('%s','%s')"; - del_fmt="delete from pics where pic='%s'"; - } - q = make_message(del_fmt, argv[1]); - ret = real_query(q); - free(q); - if (ret < 0) - return ret; - if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0)) - return ret; - if ((ret = fd2buf(fd, &blob, size)) < 0) - return ret; - PARA_DEBUG_LOG("length: %i\n", ret); - size = ret; - if (stradd) - blob[size] = '\0'; - esc_blob = escape_blob(blob, ret); - free(blob); - if (!esc_blob) - return -E_TOOBIG; - q = make_message(fmt, argv[1], esc_blob); - free(esc_blob); - ret = real_query(q); - free(q); - return ret; -} - -/* - * print results to fd - */ -static int print_results(int fd, void *result, - unsigned int top, unsigned int left, - unsigned int bottom, unsigned int right) -{ - unsigned int i,j; - int ret; - MYSQL_ROW row; - - for (i = top; i <= bottom; i++) { - row = mysql_fetch_row(result); - if (!row || !row[0]) - return -E_NOROW; - for (j = left; j <= right; j++) { - ret = send_va_buffer(fd, j == left? "%s" : "\t%s", - row[j]? row[j] : "NULL"); - if (ret < 0) - return ret; - } - ret = send_buffer(fd, "\n"); - if (ret < 0) - return ret; - } - return 0; -} - -/* - * verbatim - */ -static int com_verb(int fd, int argc, char *argv[]) -{ - void *result = NULL; - int ret; - unsigned int num_rows, num_fields; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - result = get_result(argv[1]); - if (!result) - /* return success, because it's ok to have no results */ - return 1; - num_fields = mysql_field_count(mysql_ptr); - num_rows = mysql_num_rows(result); - ret = 1; - if (num_fields && num_rows) - ret = print_results(fd, result, 0, 0, num_rows - 1, - num_fields - 1); - mysql_free_result(result); - return ret; -} - -/* returns NULL on errors or if there are no atts defined yet */ -static void *get_all_attributes(void) -{ - void *result = get_result("desc data"); - unsigned int num_rows; - - if (!result) - return NULL; - num_rows = mysql_num_rows(result); - if (num_rows < 5) { - mysql_free_result(result); - return NULL; - } - mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */ - return result; -} - -/* - * list all attributes - */ -static int com_laa(int fd, int argc, __unused char *argv[]) -{ - void *result; - int ret; - - if (argc) - return -E_MYSQL_SYNTAX; - result = get_all_attributes(); - if (!result) - return -E_NOATTS; - ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0); - mysql_free_result(result); - return ret; -} - -/* - * history - */ -static int com_hist(int fd, int argc, char *argv[]) { - int ret; - void *result = NULL; - char *q; - unsigned int num_rows; - - q = make_message("select name, to_days(now()) - to_days(lastplayed) from " - "data%s%s%s order by lastplayed", - (argc < 1)? "" : " where ", - (argc < 1)? "" : argv[1], - (argc < 1)? "" : " = '1'"); - result = get_result(q); - free(q); - if (!result) - return -E_NORESULT; - num_rows = mysql_num_rows(result); - ret = 1; - if (num_rows) - ret = print_results(fd, result, 0, 0, num_rows - 1, 1); - mysql_free_result(result); - return ret; -} - -/* - * get last num audio files - */ -static int com_last(int fd, int argc, char *argv[]) -{ - void *result = NULL; - char *q; - int num, ret; - - if (argc < 1) - num = 10; - else - num = atoi(argv[1]); - if (!num) - return -E_MYSQL_SYNTAX; - q = make_message("select name from data order by lastplayed desc " - "limit %u", num); - result = get_result(q); - free(q); - if (!result) - return -E_NORESULT; - ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0); - mysql_free_result(result); - return ret; -} - -static int com_mbox(int fd, int argc, char *argv[]) -{ - void *result; - MYSQL_ROW row; - int ret; - unsigned int num_rows, num_fields; - char *query = para_strdup("select concat('From foo@localhost ', " - "date_format(Lastplayed, '%a %b %e %T %Y'), " - "'\nReceived: from\nTo: bar\n"); - - ret = -E_NOATTS; - result = get_all_attributes(); - if (!result) - goto out; - ret = -E_NOROW; - while ((row = mysql_fetch_row(result))) { - char *tmp; - - if (!row[0]) - goto out; - tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query, - row[0], row[0]); - free(query); - query = tmp; - } - query = para_strcat(query, - "From: a\n" - "Subject: " - "', name, '" - "\n\n\n" - "') from data" - ); - if (argc >= 1) { - char *tmp = make_message("%s where name LIKE '%s'", query, - argv[1]); - free(query); - query = tmp; - } - mysql_free_result(result); - ret = -E_NORESULT; - result = get_result(query); - if (!result) - goto out; - ret = -E_EMPTY_RESULT; - num_fields = mysql_field_count(mysql_ptr); - num_rows = mysql_num_rows(result); - if (!num_fields || !num_rows) - goto out; - ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1); -out: - free(query); - if (result) - mysql_free_result(result); - return ret; -} - -/* get attributes by name. If verbose is not 0, get_a writes a string - * into atts of the form 'att1="0",att2="1"', which is used in com_cam - * for contructing a mysql update query. - * never returns NULL in *NON VERBOSE* mode - */ -static char *get_atts(char *name, int verbose) -{ - char *atts = NULL, *buf, *ebn; - void *result = NULL, *result2 = NULL; - MYSQL_ROW row, row2; - int i, ret; - unsigned int num_fields; - - ret = -E_NOATTS; - result2 = get_all_attributes(); - if (!result2) - goto out; - ret = -E_ESCAPE; - if (!(ebn = escaped_basename(name))) - goto out; - buf = make_message("select * from data where name='%s'", ebn); - free(ebn); - ret = -E_NORESULT; - result = get_result(buf); - free(buf); - if (!result) - goto out; - ret = -E_EMPTY_RESULT; - num_fields = mysql_num_fields(result); - if (num_fields < 5) - goto out; - mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */ - row = mysql_fetch_row(result); - ret = -E_NOROW; - if (!row) - goto out; - for (i = 4; i < num_fields; i++) { - int is_set = row[i] && !strcmp(row[i], "1"); - row2 = mysql_fetch_row(result2); - if (!row2 || !row2[0]) - goto out; - if (atts && (verbose || is_set)) - atts = para_strcat(atts, verbose? "," : " "); - if (is_set || verbose) - atts = para_strcat(atts, row2[0]); - if (verbose) - atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\""); - } - ret = 1; -out: - if (result2) - mysql_free_result(result2); - if (result) - mysql_free_result(result); - if (!atts && !verbose) - atts = para_strdup("(none)"); - return atts; -} - -/* never returns NULL in verbose mode */ -static char *get_meta(char *name, int verbose) -{ - MYSQL_ROW row; - void *result = NULL; - char *ebn, *q, *ret = NULL; - const char *verbose_fmt = - "select concat('lastplayed: ', " - "(to_days(now()) - to_days(lastplayed))," - "' day(s). numplayed: ', numplayed, " - "', pic: ', pic_id) " - "from data where name = '%s'"; - /* is that really needed? */ - const char *fmt = "select concat('lastplayed=\\'', lastplayed, " - "'\\', numplayed=\\'', numplayed, " - "'\\', pic_id=\\'', pic_id, '\\'') " - "from data where name = '%s'"; - - if (!(ebn = escaped_basename(name))) - goto out; - q = make_message(verbose? verbose_fmt : fmt, ebn); - free(ebn); - result = get_result(q); - free(q); - if (!result) - goto out; - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - ret = para_strdup(row[0]); -out: - if (result) - mysql_free_result(result); - if (!ret && verbose) - ret = para_strdup("(not yet played)"); - return ret; -} - -static char *get_dir(char *name) -{ - char *ret = NULL, *q, *ebn; - void *result; - MYSQL_ROW row; - - if (!(ebn = escaped_basename(name))) - return NULL; - q = make_message("select dir from dir where name = '%s'", ebn); - free(ebn); - result = get_result(q); - free(q); - if (!result) - return NULL; - row = mysql_fetch_row(result); - if (row && row[0]) - ret = para_strdup(row[0]); - mysql_free_result(result); - return ret; -} - -/* never returns NULL */ -static char *get_current_stream(void) -{ - char *ret; - MYSQL_ROW row; - void *result = get_result("select def from streams where " - "name = 'current_stream'"); - - if (!result) - goto err_out; - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto err_out; - ret = para_strdup(row[0]); - mysql_free_result(result); - return ret; -err_out: - if (result) - mysql_free_result(result); - return para_strdup("(none)"); -} -/* - * Read stream definition of stream streamname and construct mysql - * query. Return NULL on errors. If streamname is NULL, use current - * stream. If that is also NULL, use query that selects everything. - * If filename is NULL, query will list everything, otherwise only - * the score of given file. - */ -static char *get_query(char *streamname, char *filename, int with_path) -{ - char *accept_opts = NULL, *deny_opts = NULL, *score = NULL; - char *where_clause, *order, *query; - char command[255] = ""; /* buffer for sscanf */ - void *result; - MYSQL_ROW row; - char *end, *tmp; - char *select_clause = NULL; - if (!streamname) - tmp = get_current_stream(); - else - tmp = para_strdup(streamname); - if (!strcmp(tmp, "(none)")) { - free(tmp); - if (filename) { - char *ret, *ebn = escaped_basename(filename); - ret = make_message("select to_days(now()) - " - "to_days(lastplayed) from data " - "where name = '%s'", ebn); - free(ebn); - return ret; - } - if (with_path) - return make_message( - "select concat(dir.dir, '/', dir.name) " - "from data, dir where dir.name = data.name " - "order by data.lastplayed" - ); - return make_message( - "select name from data where name is not NULL " - "order by lastplayed" - ); - } - free(tmp); - query = make_message("select def from streams where name = '%s'", - streamname); - result = get_result(query); - free(query); - query = NULL; - if (!result) - goto out; - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - end = row[0]; - while (*end) { - int n; - char *arg, *line = end; - - if (!(end = strchr(line, '\n'))) - break; - *end = '\0'; - end++; - if (sscanf(line, "%200s%n", command, &n) < 1) - continue; - arg = line + n; - if (!strcmp(command, "accept:")) { - char *tmp2 = s_a_r_list(macro_list, arg); - if (accept_opts) - accept_opts = para_strcat( - accept_opts, " or "); - accept_opts = para_strcat(accept_opts, tmp2); - free(tmp2); - continue; - } - if (!strcmp(command, "deny:")) { - char *tmp2 = s_a_r_list(macro_list, arg); - if (deny_opts) - deny_opts = para_strcat(deny_opts, " or "); - deny_opts = para_strcat(deny_opts, tmp2); - free(tmp2); - continue; - } - if (!strcmp(command, "score:")) - score = s_a_r_list(macro_list, arg); - } - if (!score) { - score = s_a_r_list(macro_list, conf.mysql_default_score_arg); - if (!score) - goto out; - } - if (filename) { - char *ebn = escaped_basename(filename); - if (!ebn) - goto out; - select_clause = make_message("select %s from data ", score); - free(score); - where_clause = make_message( "where name = '%s' ", ebn); - free(ebn); - order = para_strdup(""); - goto write_query; - } - select_clause = para_strdup(with_path? - "select concat(dir.dir, '/', dir.name) from data, dir " - "where dir.name = data.name " - : - "select name from data where name is not NULL"); - order = make_message("order by -(%s)", score); - free(score); - if (accept_opts && deny_opts) { - where_clause = make_message("and ((%s) and not (%s)) ", - accept_opts, deny_opts); - goto write_query; - } - if (accept_opts && !deny_opts) { - where_clause = make_message("and (%s) ", accept_opts); - goto write_query; - } - if (!accept_opts && deny_opts) { - where_clause = make_message("and not (%s) ", deny_opts); - goto write_query; - } - where_clause = para_strdup(""); -write_query: - query = make_message("%s %s %s", select_clause, where_clause, order); - free(order); - free(select_clause); - free(where_clause); -out: - if (accept_opts) - free(accept_opts); - if (deny_opts) - free(deny_opts); - if (result) - mysql_free_result(result); - return query; -} - - - -/* - * This is called from server and from some commands. Name must not be NULL - * Never returns NULL. - */ -static char *get_dbinfo(char *name) -{ - char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL; - void *result = NULL; - MYSQL_ROW row = NULL; - - if (!name) - return para_strdup("(none)"); - stream = get_current_stream(); - meta = get_meta(name, 1); - atts = get_atts(name, 0); - dir = get_dir(name); - /* get score */ - query = get_query(stream, name, 0); - if (!query) - goto write; - result = get_result(query); - free(query); - if (result) - row = mysql_fetch_row(result); -write: - info = make_message("dbinfo1:dir: %s\n" - "dbinfo2:stream: %s, %s, score: %s\n" - "dbinfo3:%s\n", - dir? dir : "(not contained in table)", - stream, meta, - (result && row && row[0])? row[0] : "(no score)", - atts); - if (dir) - free(dir); - if (meta) - free(meta); - if (atts) - free(atts); - if (stream) - free(stream); - if (result) - mysql_free_result(result); - return info; -} - - -/* might return NULL */ -static char *get_current_audio_file(void) -{ - char *name; - mmd_lock(); - name = para_basename(mmd->filename); - mmd_unlock(); - return name; -} - - -/* print database info */ -static int com_info(int fd, int argc, char *argv[]) -{ - char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL; - int ret, com_la = strcmp(argv[0], "info"); - - if (argc < 1) { - ret = -E_GET_AUDIO_FILE; - if (!(name = get_current_audio_file())) - goto out; - ret = send_va_buffer(fd, "%s\n", name); - if (ret < 0) - goto out; - } else { - ret = -E_ESCAPE; - if (!(name = escaped_basename(argv[1]))) - goto out; - } - meta = get_meta(name, 1); - atts = get_atts(name, 0); - dir = get_dir(name); - if (com_la) - ret = send_va_buffer(fd, "%s\n", atts); - else - ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n", - dir? dir : "(not contained in table)", meta, atts); -out: - if (meta) - free(meta); - if (atts) - free(atts); - if (dir) - free(dir); - if (name) - free(name); - return ret; -} -static int change_stream(char *stream) -{ - char *query; - int ret; - /* try to insert if it does not exist (compatibility) */ -// query = make_message("insert into streams (name, def) values " -// "('current_stream', '%s')", stream); -// real_query(query); /* ignore return value */ -// free(query); - query = make_message("update streams set def='%s' " - "where name = 'current_stream'", stream); - ret = real_query(query); - free(query); - return ret; -} - -static int get_pic_id_by_name(char *name) -{ - char *q, *ebn; - void *result = NULL; - long unsigned ret; - MYSQL_ROW row; - - if (!(ebn = escaped_basename(name))) - return -E_ESCAPE; - q = make_message("select pic_id from data where name = '%s'", ebn); - free(ebn); - result = get_result(q); - free(q); - if (!result) - return -E_NORESULT; - row = mysql_fetch_row(result); - ret = -E_NOROW; - if (row && row[0]) - ret = atol(row[0]); - mysql_free_result(result); - return ret; -} - -static int remove_entry(const char *name) -{ - char *q, *ebn = escaped_basename(name); - int ret = -E_ESCAPE; - - if (!ebn || !*ebn) - goto out; - q = make_message("delete from data where name = '%s'", ebn); - real_query(q); /* ignore errors */ - free(q); - q = make_message("delete from dir where name = '%s'", ebn); - real_query(q); /* ignore errors */ - free(q); - ret = 1; -out: - free(ebn); - return ret; -} - -static int add_entry(const char *name) -{ - char *q, *dn, *ebn = NULL, *edn = NULL; - int ret; - - if (!name || !*name) - return -E_MYSQL_SYNTAX; - ebn = escaped_basename(name); - if (!ebn) - return -E_ESCAPE; - ret = -E_MYSQL_SYNTAX; - dn = para_dirname(name); - if (!dn) - goto out; - ret = -E_ESCAPE; - edn = escape_str(dn); - free(dn); - if (!edn || !*edn) - goto out; - q = make_message("insert into data (name, pic_id) values " - "('%s', '%s')", ebn, "1"); - ret = real_query(q); -// ret = 1; PARA_DEBUG_LOG("q: %s\n", q); - free(q); - if (ret < 0) - goto out; - q = make_message("insert into dir (name, dir) values " - "('%s', '%s')", ebn, edn); -// ret = 1; PARA_DEBUG_LOG("q: %s\n", q); - ret = real_query(q); - free(q); -out: - if (ebn) - free(ebn); - if (edn) - free(edn); - return ret; -} - -/* - * remove/add entries - */ -static int com_rm_ne(__unused int fd, int argc, char *argv[]) -{ - int ne = !strcmp(argv[0], "ne"); - int i, ret; - if (argc < 1) - return -E_MYSQL_SYNTAX; - for (i = 1; i <= argc; i++) { - ret = remove_entry(argv[i]); - if (ret < 0) - return ret; - if (!ne) - continue; - ret = add_entry(argv[i]); - if (ret < 0) - return ret; - } - return 1; -} - -/* - * mv: rename entry - */ -static int com_mv(__unused int fd, int argc, char *argv[]) -{ - char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL; - int ret; - - if (argc != 2) - return -E_MYSQL_SYNTAX; - ebn1 = escaped_basename(argv[1]); - ebn2 = escaped_basename(argv[2]); - dn = para_dirname(argv[2]); - edn = escape_str(dn); - free(dn); - ret = -E_ESCAPE; - if (!ebn1 || !ebn2) - goto out; - remove_entry(ebn2); - q = make_message("update data set name = '%s' where name = '%s'", - ebn2, ebn1); - ret = real_query(q); - free(q); - if (ret < 0) - goto out; - q = make_message("update dir set name = '%s' where name = '%s'", - ebn2, ebn1); - ret = real_query(q); - free(q); - if (ret < 0) - goto out; - /* do not touch table dir, return success if argv[2] is no full path */ - ret = 1; - if (!edn || !*edn) - goto out; - q = make_message("update dir set dir = '%s' where name = '%s'", - edn, ebn2); -// PARA_DEBUG_LOG("q: %s\n", q); - ret = real_query(q); - free(q); -out: - if (ebn1) - free(ebn1); - if (ebn2) - free(ebn2); - if (edn) - free(edn); - return ret; - -} - -/* - * picass: associate pic to audio file - * snp: set numplayed - */ -static int com_set(__unused int fd, int argc, char *argv[]) -{ - char *q, *ebn; - long unsigned id; - int i, ret; - char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id"; - - if (argc < 2) - return -E_MYSQL_SYNTAX; - id = atol(argv[1]); - for (i = 2; i <= argc; i++) { - ebn = escaped_basename(argv[i]); - if (!ebn) - return -E_ESCAPE; - q = make_message("update data set %s = %lu " - "where name = '%s'", field, id, ebn); - free(ebn); - ret = real_query(q); - free(q); - if (ret < 0) - return ret; - } - return 1; -} - -/* - * picch: change entry's name in pics table - */ -static int com_picch(__unused int fd, int argc, char *argv[]) -{ - int ret; - long unsigned id; - char *q; - - if (argc != 2) - return -E_MYSQL_SYNTAX; - id = atol(argv[1]); - if (strlen(argv[2]) > MAXLINE) - return -E_NAMETOOLONG; - q = make_message("update pics set name = '%s' where id = %lu", argv[2], id); - ret = real_query(q); - free(q); - return ret; -} - -/* - * piclist: print list of pics in db - */ -static int com_piclist(__unused int fd, int argc, __unused char *argv[]) -{ - void *result = NULL; - MYSQL_ROW row; - unsigned long *length; - int ret; - - if (argc) - return -E_MYSQL_SYNTAX; - result = get_result("select id,name,pic from pics order by id"); - if (!result) - return -E_NORESULT; - while ((row = mysql_fetch_row(result))) { - length = mysql_fetch_lengths(result); - if (!row || !row[0] || !row[1] || !row[2]) - continue; - ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]); - if (ret < 0) - goto out; - } - ret = 1; -out: - mysql_free_result(result); - return ret; -} - -/* - * picdel: delete picture from database - */ -static int com_picdel(int fd, int argc, char *argv[]) -{ - char *q; - long unsigned id; - my_ulonglong aff; - int i, ret; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - for (i = 1; i <= argc; i++) { - id = atol(argv[i]); - q = make_message("delete from pics where id = %lu", id); - ret = real_query(q); - free(q); - if (ret < 0) - return ret; - aff = mysql_affected_rows(mysql_ptr); - if (!aff) { - ret = send_va_buffer(fd, "No such id: %lu\n", id); - if (ret < 0) - return ret; - continue; - } - q = make_message("update data set pic_id = 1 where pic_id = %lu", id); - ret = real_query(q); - free(q); - } - return 1; -} -/* - * pic: get picture by name or by number - */ -static int com_pic(int fd, int argc, char *argv[]) -{ - void *result = NULL; - MYSQL_ROW row; - unsigned long *length, id; - int ret; - char *q, *name = NULL; - - if (argc < 1) { - ret = -E_GET_AUDIO_FILE; - name = get_current_audio_file(); - } else { - ret = -E_ESCAPE; - name = escaped_basename(argv[1]); - } - if (!name) - return ret; - if (*name == '#') - id = atoi(name + 1); - else - id = get_pic_id_by_name(name); - free(name); - if (id <= 0) - return id; - q = make_message("select pic from pics where id = '%lu'", id); - result = get_result(q); - free(q); - if (!result) - return -E_NORESULT; - row = mysql_fetch_row(result); - ret = -E_NOROW; - if (!row || !row[0]) - goto out; - length = mysql_fetch_lengths(result); - ret = send_bin_buffer(fd, row[0], *length); -out: - mysql_free_result(result); - return ret; -} - -/* strdel */ -static int com_strdel(__unused int fd, int argc, char *argv[]) -{ - char *tmp; - int ret = -1; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - tmp = make_message("delete from streams where name='%s'", argv[1]); - ret = real_query(tmp); - free(tmp); - if (ret < 0) - return ret; - tmp = get_current_stream(); - ret = 1; - if (strcmp(tmp, "(none)") && !strcmp(tmp, argv[1])) - ret = change_stream("(none)"); - return ret; -} - -/* - * ls - */ -static int com_ls(int fd, int argc, char *argv[]) -{ - char *q; - void *result; - int ret; - unsigned int num_rows; - - if (argc > 0) - q = make_message("select name from data where name LIKE '%s'", - argv[1]); - else - q = para_strdup("select name from data"); - result = get_result(q); - free(q); - if (!result) - return -E_NORESULT; - num_rows = mysql_num_rows(result); - ret = 1; - if (num_rows) - ret = print_results(fd, result, 0, 0, num_rows - 1, 0); - mysql_free_result(result); - return ret; -} -/* - * summary - */ -static int com_summary(__unused int fd, int argc, __unused char *argv[]) -{ - MYSQL_ROW row; - MYSQL_ROW row2; - void *result; - void *result2 = NULL; - const char *fmt = "select count(name) from data where %s='1'"; - int ret = -E_NORESULT; - - if (argc) - return -E_MYSQL_SYNTAX; - result = get_all_attributes(); - if (!result) - goto out; - while ((row = mysql_fetch_row(result))) { - char *buf; - - ret = -E_NOROW; - if (!row[0]) - goto out; - ret = -E_NORESULT; - buf = make_message(fmt, row[0]); - result2 = get_result(buf); - free(buf); - if (!result2) - goto out; - ret = -E_NOROW; - row2 = mysql_fetch_row(result2); - if (!row2 || !row2[0]) - goto out; - ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]); - if (ret < 0) - goto out; - } - ret = 1; -out: - if (result2) - mysql_free_result(result2); - if (result) - mysql_free_result(result); - return ret; -} - -static int get_numplayed(char *name) -{ - void *result; - MYSQL_ROW row; - const char *fmt = "select numplayed from data where name = '%s'"; - char *buf = make_message(fmt, name); - int ret = -E_NORESULT; - - result = get_result(buf); - free(buf); - if (!result) - goto out; - ret = -E_NOROW; - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - ret = atoi(row[0]); -out: - if (result) - mysql_free_result(result); - return ret; -} - -static int update_audio_file(char *name) -{ - int ret; - const char *fmt1 = "update data set lastplayed = now() where name = '%s'"; - const char *fmt2 = "update data set numplayed = %i where name = '%s'"; - char *q; - char *ebn = escaped_basename(name); - - ret = -E_ESCAPE; - if (!ebn) - goto out; - q = make_message(fmt1, ebn); - ret = real_query(q); - free(q); - if (ret < 0) - goto out; - ret = get_numplayed(ebn); - if (ret < 0) - goto out; - q = make_message(fmt2, ret + 1, ebn); - ret = real_query(q); - free(q); -out: - if (ebn) - free(ebn); - return ret; -} -/* If called as child, mmd_lock must be held */ -static void update_mmd(char *info) -{ - PARA_DEBUG_LOG("%s", "updating shared memory area\n"); - strncpy(mmd->dbinfo, info, MMD_INFO_SIZE - 1); - mmd->dbinfo[MMD_INFO_SIZE - 1] = '\0'; -} - -static void update_audio_file_server_handler(char *name) -{ - char *info; - info = get_dbinfo(name); - update_mmd(info); - free(info); - update_audio_file(name); -} - -static int com_us(__unused int fd, int argc, char *argv[]) -{ - if (argc != 1) - return -E_MYSQL_SYNTAX; - return update_audio_file(argv[1]); -} - -static void refresh_mmd_dbinfo(void) -{ - char *name = get_current_audio_file(); - char *info; - - if (!name) - return; - info = get_dbinfo(name); - free(name); - mmd_lock(); - update_mmd(info); - mmd_unlock(); - free(info); -} - -/* select previous/next stream */ -static int com_ps(__unused int fd, int argc, char *argv[]) -{ - char *query, *stream = get_current_stream(); - void *result = get_result("select name from streams"); - MYSQL_ROW row; - int match = -1, ret, i; - unsigned int num_rows; - - if (argc) - return -E_MYSQL_SYNTAX; - ret = -E_NORESULT; - if (!result) - goto out; - num_rows = mysql_num_rows(result); - ret = -E_EMPTY_RESULT; - if (num_rows < 2) - goto out; - ret = -E_NOROW; - for (i = 0; i < num_rows; i++) { - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - if (!strcmp(row[0], "current_stream")) - continue; - if (!strcmp(row[0], stream)) { - match = i; - break; - } - } - ret = -E_NO_STREAM; - if (match < 0) - goto out; - if (!strcmp(argv[0], "ps")) - i = match > 0? match - 1 : num_rows - 1; - else - i = match < num_rows - 1? match + 1 : 0; - ret = -E_NOROW; - mysql_data_seek(result, i); - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - if (!strcmp(row[0], "current_stream")) { - if (!strcmp(argv[0], "ps")) { - i = match - 2; - i = i < 0? i + num_rows : i; - } else { - i = match + 2; - i = i > num_rows - 1? i - num_rows : i; - } - mysql_data_seek(result, i); - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - } - query = make_message("update streams set def='%s' where name = " - "'current_stream'", row[0]); - ret = real_query(query); - free(query); - refresh_mmd_dbinfo(); -out: - free(stream); - if (result) - mysql_free_result(result); - return ret; -} - -/* streams */ -static int com_streams(int fd, int argc, __unused char *argv[]) -{ - unsigned int num_rows; - int i, ret = -E_NORESULT; - void *result; - MYSQL_ROW row; - - if (argc && strcmp(argv[1], "current_stream")) - return -E_MYSQL_SYNTAX; - if (argc) { - char *cs = get_current_stream(); - ret = send_va_buffer(fd, "%s\n", cs); - free(cs); - return ret; - } - result = get_result("select name from streams"); - if (!result) - goto out; - num_rows = mysql_num_rows(result); - ret = 1; - if (!num_rows) - goto out; - ret = -E_NOROW; - for (i = 0; i < num_rows; i++) { - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - if (strcmp(row[0], "current_stream")) - send_va_buffer(fd, "%s\n", row[0]); - } - ret = 1; -out: - if (result) - mysql_free_result(result); - return ret; -} - -/* query stream definition */ -static int com_strq(int fd, int argc, char *argv[]) -{ - MYSQL_ROW row; - char *query, *name; - void *result; - int ret; - - if (argc < 1) { - ret = -E_GET_STREAM; - name = get_current_stream(); - } else { - ret = -E_ESCAPE; - name = escaped_basename(argv[1]); - } - if (!name) - return ret; - ret = -E_NORESULT; - query = make_message("select def from streams where name='%s'", name); - free(name); - result = get_result(query); - free(query); - if (!result) - goto out; - ret = -E_NOROW; - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto out; - /* no '\n' needed */ - ret = send_buffer(fd, row[0]); -out: - if (result) - mysql_free_result(result); - return ret; -} - -/* change stream / change stream and play */ -static int com_cs(int fd, int argc, char *argv[]) -{ - int ret, stream_change; - char *query; - char *old_stream = get_current_stream(); - int csp = !strcmp(argv[0], "csp"); - - if (!argc) { - ret = -E_MYSQL_SYNTAX; - if (csp) - goto out; - ret = send_va_buffer(fd, "%s\n", old_stream); - goto out; - } - ret = -E_GET_QUERY; - query = get_query(argv[1], NULL, 0); /* test if stream is valid */ - if (!query) - goto out; - free(query); - /* stream is ok */ - stream_change = strcmp(argv[1], old_stream); - if (stream_change) { - ret = change_stream(argv[1]); - if (ret < 0) - goto out; - refresh_mmd_dbinfo(); - } - if (csp) { - mmd_lock(); - mmd->new_afs_status_flags |= AFS_PLAYING; - if (stream_change) - mmd->new_afs_status_flags |= AFS_NEXT; - mmd_unlock(); - } - ret = 1; -out: - free(old_stream); - return ret; -} - -/* - * sl/skip - */ -static int com_sl(int fd, int argc, char *argv[]) -{ - void *result = NULL; - MYSQL_ROW row; - int ret, i, skip = !strcmp(argv[0], "skip"); - char *query, *stream, *tmp; - unsigned int num_rows, num; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - num = atoi(argv[1]); - if (!num) - return -E_MYSQL_SYNTAX; - stream = (argc == 1)? get_current_stream() : para_strdup(argv[2]); - tmp = get_query(stream, NULL, 0); - query = make_message("%s limit %d", tmp, num); - free(tmp); - ret = -E_GET_QUERY; - free(stream); - if (!query) - goto out; - ret = -E_NORESULT; - result = get_result(query); - free(query); - if (!result) - goto out; - ret = -E_EMPTY_RESULT; - num_rows = mysql_num_rows(result); - if (!num_rows) - goto out; - for (i = 0; i < num_rows && i < num; i++) { - row = mysql_fetch_row(result); - if (skip) { - send_va_buffer(fd, "Skipping %s\n", row[0]); - update_audio_file(row[0]); - } else - send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG"); - } - ret = 1; -out: - if (result) - mysql_free_result(result); - return ret; -} - -/* - * update attributes of name - */ -static int update_atts(int fd, char *name, char *atts) -{ - int ret; - char *ebn, *q, *old, *new = NULL; - - if (!mysql_ptr) - return -E_NOTCONN; - ebn = escaped_basename(name); - if (!ebn) - return -E_ESCAPE; - q = make_message("update data set %s where name = '%s'", atts, ebn); - old = get_atts(ebn, 0); - send_va_buffer(fd, "old: %s\n", old); - free(old); - ret = real_query(q); - free(q); - if (ret < 0) - goto out; - new = get_atts(ebn, 0); - ret = send_va_buffer(fd, "new: %s\n", new); - free(new); -out: - free(ebn); - return ret; -} - -/* - * set attributes - */ -static int com_sa(int fd, int argc, char *argv[]) -{ - int i, ret; - char *atts = NULL, *name; - - if (argc < 1) - return -E_MYSQL_SYNTAX; - for (i = 1; i <= argc; i++) { - int unset = 0; - char *tmp, *p =argv[i]; - int len = strlen(p); - - if (!len) - continue; - switch (p[len - 1]) { - case '+': - unset = 0; - break; - case '-': - unset = 1; - break; - default: - goto no_more_atts; - } - p[len - 1] = '\0'; - tmp = make_message("%s%s='%s'", atts? "," : "", p, - unset? "0" : "1"); - atts = para_strcat(atts, tmp); - free(tmp); - } -no_more_atts: - if (!atts) - return -E_NOATTS; - if (i > argc) { /* no name given, use current af */ - ret = -E_GET_AUDIO_FILE; - if (!(name = get_current_audio_file())) - goto out; - ret = update_atts(fd, name, atts); - free(name); - } else { - ret = 1; - for (; argv[i] && ret >= 0; i++) - ret = update_atts(fd, argv[i], atts); - } - refresh_mmd_dbinfo(); -out: - return ret; -} - -/* - * copy attributes - */ -static int com_cam(int fd, int argc, char *argv[]) -{ - char *name = NULL, *meta = NULL, *atts = NULL; - int i, ret; - - if (argc < 2) - return -E_MYSQL_SYNTAX; - if (!(name = escaped_basename(argv[1]))) - return -E_ESCAPE; - ret = -E_NOATTS; - if (!(atts = get_atts(name, 1))) - goto out; - ret = -E_META; - if (!(meta = get_meta(name, 0))) - goto out; - for (i = 2; i <= argc; i++) { - char *ebn, *q; - ret = -E_ESCAPE; - if (!(ebn = escaped_basename(argv[i]))) - goto out; - ret = send_va_buffer(fd, "updating %s\n", ebn); - if (ret < 0) { - free(ebn); - goto out; - } - q = make_message("update data set %s where name = '%s'", - meta, ebn); - if ((ret = update_atts(fd, ebn, atts)) >= 0) - ret = real_query(q); - free(ebn); - free(q); - if (ret < 0) - goto out; - } - ret = 1; -out: - if (name) - free(name); - if (meta) - free(meta); - if (atts) - free(atts); - return ret; -} - -/* - * verify / clean - */ -static int com_vrfy(int fd, int argc, __unused char *argv[]) -{ - char *query; - int ret, vrfy_mode = strcmp(argv[0], "clean"); - void *result = NULL; - unsigned int num_rows; - MYSQL_ROW row; - char *escaped_name; - - if (argc) - return -E_MYSQL_SYNTAX; - ret = -E_NORESULT; - result = get_result("select data.name from data left join dir on " - "dir.name = data.name where dir.name is NULL"); - if (!result) - goto out; - num_rows = mysql_num_rows(result); - if (!num_rows) { - ret = send_buffer(fd, "No invalid entries\n"); - goto out; - } - if (vrfy_mode) { - send_va_buffer(fd, "found %i invalid entr%s\n", num_rows, - num_rows == 1? "y" : "ies"); - ret = print_results(fd, result, 0, 0, num_rows - 1, 0); - goto out; - } - while ((row = mysql_fetch_row(result))) { - ret = -E_NOROW; - if (!row[0]) - goto out; - ret = -E_ESCAPE; - escaped_name = escape_str(row[0]); - if (!escaped_name) - goto out; - send_va_buffer(fd, "deleting %s\n", escaped_name); - query = make_message("delete from data where name = '%s'", - escaped_name); - ret = real_query(query); - free(query); - if (ret < 0) - goto out; - } - -out: - if (result) - mysql_free_result(result); - return ret; -} - -static FILE *out_file; - -static int mysql_write_tmp_file(const char *dir, const char *name) -{ - int ret = -E_TMPFILE; - char *msg = make_message("%s\t%s\n", dir, name); - - if (fputs(msg, out_file) != EOF) - ret = 1; - free(msg); - return ret; -} - -/* - * update database - */ -static int com_upd(int fd, int argc, __unused char *argv[]) -{ - char *tempname = NULL, *query = NULL; - int ret, out_fd = -1, num = 0; - void *result = NULL; - unsigned int num_rows; - MYSQL_ROW row; - - if (argc) - return -E_MYSQL_SYNTAX; - out_file = NULL; - tempname = para_strdup("/tmp/mysql.tmp.XXXXXX"); - ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH); - if (ret < 0) - goto out; - out_fd = ret; - out_file = fdopen(out_fd, "w"); - if (!out_file) { - close(out_fd); - goto out; - } - if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0) - goto out; - num = ftell(out_file); - /* - * we have to make sure the file hit the disk before we call - * real_query - */ - fclose(out_file); - out_file = NULL; - PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num); - if (!num) - goto out; - if ((ret = real_query("delete from dir")) < 0) - goto out; - query = make_message("load data infile '%s' into table dir " - "fields terminated by '\t' lines terminated by '\n' " - "(dir, name)", tempname); - ret = real_query(query); - free(query); - if (ret < 0) - goto out; - result = get_result("select dir.name from dir left join data on " - "data.name = dir.name where data.name is NULL"); - ret = -E_NORESULT; - if (!result) - goto out; - num_rows = mysql_num_rows(result); - if (!num_rows) { - ret = send_buffer(fd, "no new entries\n"); - goto out; - } - while ((row = mysql_fetch_row(result))) { - ret = -E_NOROW; - if (!row[0]) - goto out; - send_va_buffer(fd, "new entry: %s\n", row[0]); - query = make_message("insert into data (name, pic_id) values " - "('%s','%s')", row[0], "1"); - ret = real_query(query); - free(query); - if (ret < 0) - goto out; - } - ret = 1; -out: - if (out_fd >= 0) - unlink(tempname); - free(tempname); - if (out_file) - fclose(out_file); - if (result) - mysql_free_result(result); - return ret; -} - -static char **server_get_audio_file_list(unsigned int num) -{ - char **list = para_malloc((num + 1) * sizeof(char *)); - char *tmp, *query, *stream = get_current_stream(); - void *result = NULL; - unsigned int num_rows; - int i = 0; - MYSQL_ROW row; - - tmp = get_query(stream, NULL, 1); - free(stream); - query = make_message("%s limit %d", tmp, num); - free(tmp); - if (!query) - goto err_out; - result = get_result(query); - if (!result) - goto err_out; - num_rows = mysql_num_rows(result); - if (!num_rows) - goto err_out; - for (i = 0; i < num_rows && i < num; i++) { - row = mysql_fetch_row(result); - if (!row || !row[0]) - goto err_out; - list[i] = para_strdup(row[0]); - } - list[i] = NULL; - goto success; -err_out: - while (i > 0) { - i--; - free(list[i]); - } - free(list); - list = NULL; -success: - if (query) - free(query); - if (result) - mysql_free_result(result); - return list; -} - -/* - * connect to mysql server, return mysql pointer on success, -E_NOTCONN - * on errors. Called from parent on startup and also from com_cdb(). - */ -static int init_mysql_server(void) -{ - char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname(); - - mysql_ptr = mysql_init(NULL); - if (!mysql_ptr) { - PARA_CRIT_LOG("%s", "mysql init error\n"); - return -E_NOTCONN; - } - PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg, - conf.mysql_port_arg); - if (!conf.mysql_user_arg) - free(u); - /* - * If host is NULL a connection to the local host is assumed, - * If user is NULL, the current user is assumed - */ - if (!(mysql_ptr = mysql_real_connect(mysql_ptr, - conf.mysql_host_arg, - conf.mysql_user_arg, - conf.mysql_passwd_arg, - conf.mysql_database_arg, - conf.mysql_port_arg, NULL, 0))) { - PARA_CRIT_LOG("%s", "connect error\n"); - return -E_NOTCONN; - } - PARA_INFO_LOG("%s", "success\n"); - return 1; -} - -/* mmd lock must be held */ -static void write_msg2mmd(int success) -{ - sprintf(mmd->dbinfo, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n", - success < 0? PARA_STRERROR(-success) : - "successfully connected to mysql server", - success < 0? "" : mysql_get_server_info(mysql_ptr)); -} - -/* create database */ -static int com_cdb(int fd, int argc, char *argv[]) -{ - char *query, *name; - int ret; - - if (argc < 1) - name = "paraslash"; - else { - ret = -E_NAMETOOLONG; - name = argv[1]; - if (strlen(name) > MAXLINE) - goto out; - } - if (mysql_ptr) { - PARA_INFO_LOG("%s", "closing database\n"); - mysql_close(mysql_ptr); - } - /* dont use any database */ - conf.mysql_database_arg = NULL; /* leak? */ - ret = -E_MYSQL_INIT; - if (init_mysql_server() < 0 || !mysql_ptr) - goto out; - query = make_message("create database %s", name); - ret = real_query(query); - free(query); - if (ret < 0) - goto out; - /* reconnect with database just created */ - mysql_close(mysql_ptr); - conf.mysql_database_arg = para_strdup(name); - ret = -E_MYSQL_INIT; - if (init_mysql_server() < 0 || !mysql_ptr) - goto out; - mmd_lock(); - write_msg2mmd(1); - mmd_unlock(); - ret = -E_QFAILED; - if (real_query("create table data (name varchar(255) binary not null " - "primary key, " - "lastplayed datetime not null default " - "'1970-01-01', " - "numplayed int not null default 0, " - "pic_id bigint unsigned not null default 1)") < 0) - goto out; - if (real_query("create table dir (name varchar(255) binary not null " - "primary key, dir varchar(255) default null)") < 0) - goto out; - if (real_query("create table pics (" - "id bigint(20) unsigned not null primary key " - "auto_increment, " - "name varchar(255) binary not null, " - "pic mediumblob not null)") < 0) - goto out; - if (real_query("create table streams (" - "name varchar(255) binary not null primary key, " - "def blob not null)") < 0) - goto out; - if (real_query("insert into streams (name, def) values " - "('current_stream', '(none)')") < 0) - goto out; - ret = send_va_buffer(fd, "successfully created database %s\n", name); -out: - return ret; -} - -static void shutdown_connection(void) -{ - if (mysql_ptr) { - PARA_NOTICE_LOG("%s", "shutting down mysql connection\n"); - mysql_close(mysql_ptr); - mysql_ptr = NULL; - } -} - -/** - * the init function of the mysql-based database tool - * - * Check the command line options and initialize all function pointers of \a db. - * Connect to the mysql server and initialize the dbinfo string. - * - * \sa struct audio_file_selector, misc_meta_data::dbinfo, random_selector.c - */ -int mysql_selector_init(struct audio_file_selector *db) -{ - int ret; - - if (!conf.mysql_passwd_given) - return -E_NO_MYSQL_PASSWD; - if (!conf.mysql_audio_file_dir_given) - return -E_NO_AF_DIR; - db->name = "mysql"; - db->cmd_list = cmds; - db->get_audio_file_list = server_get_audio_file_list; - db->update_audio_file = update_audio_file_server_handler; - db->shutdown = shutdown_connection; - ret = init_mysql_server(); - if (ret < 0) - PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret)); - write_msg2mmd(ret); - return 1; /* return success even if connect failed to give the - * user the chance to exec com_cdb - */ -} diff --git a/mysql_selector.c b/mysql_selector.c new file mode 100644 index 00000000..85d16ee2 --- /dev/null +++ b/mysql_selector.c @@ -0,0 +1,2539 @@ +/* + * Copyright (C) 1999-2006 Andre Noll + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA. + */ + +/** \file mysql_selector.c para_server's mysql-based audio file selector */ + +/** \cond some internal constants */ +#define MEDIUM_BLOB_SIZE 16777220 /* (2**24 + 4) */ +#define BLOB_SIZE 65539 /* (2**16 + 3) */ +/** \endcond */ +#include "server.cmdline.h" +#include "server.h" +#include "afs.h" +#include "db.h" +#include +#include +#include "error.h" +#include "net.h" +#include "string.h" + +extern struct gengetopt_args_info conf; +/** pointer to the shared memory area */ +extern struct misc_meta_data *mmd; + +static void *mysql_ptr = NULL; + + +static int com_cam(int, int, char **); +static int com_cdb(int, int, char **); +static int com_cs(int, int, char **); +static int com_da(int, int, char **); +static int com_hist(int, int, char **); +static int com_info(int, int, char **); +static int com_laa(int, int, char **); +static int com_last(int, int, char **); +static int com_ls(int, int, char **); +static int com_mbox(int, int, char **); +static int com_mv(int, int, char **); +static int com_na(int, int, char **); +static int com_pic(int, int, char **); +static int com_picch(int, int, char **); +static int com_picdel(int, int, char **); +static int com_piclist(int, int, char **); +static int com_ps(int, int, char **); +static int com_rm_ne(int, int, char **); +static int com_sa(int, int, char **); +static int com_set(int, int, char **); +static int com_sl(int, int, char **); +static int com_stradd_picadd(int, int, char **); +static int com_streams(int, int, char **); +static int com_strdel(int, int, char **); +static int com_strq(int, int, char **); +static int com_summary(int, int, char **); +static int com_upd(int, int, char **); +static int com_us(int, int, char **); +static int com_verb(int, int, char **); +static int com_vrfy(int, int, char **); + +static struct server_command cmds[] = { +{ +.name = "cam", +.handler = com_cam, +.perms = DB_READ|DB_WRITE, +.description = "copy all metadata", +.synopsis = "cam source dest1 [dest2 ...]", +.help = + +"Copy attributes and other meta data from source file to destination\n" +"file(s). Useful for files that have been renamed.\n" + +}, +{ +.name = "cdb", +.handler = com_cdb, +.perms = DB_READ|DB_WRITE, +.description = "create database", +.synopsis = "cdb [name]", +.help = + +"\tCreate database name containing the initial columns for basic\n" +"\tinteroperation with server. This command has to be used only once\n" +"\twhen you use the mysql database tool for the very first time.\n" +"\n" +"\tThe optional name defaults to 'paraslash' if not given.\n" + +}, +{ +.name = "clean", +.handler = com_vrfy, +.perms = DB_READ | DB_WRITE, +.description = "nuke invalid entries in database", +.synopsis = "clean", +.help = + +"If the vrfy command shows you any invalid entries in your database,\n" +"you can get rid of them with clean. Always run 'upd' and 'vrfy'\n" +"before running this command. Use with caution!\n" + +}, +{ +.name = "cs", +.handler = com_cs, +.perms = AFS_WRITE | DB_READ | DB_WRITE, +.description = "change stream", +.synopsis = "cs [s]", +.help = + +"Selects stream s or prints current stream when s was not given.\n" + +}, +{ +.name = "csp", +.handler = com_cs, +.perms = AFS_WRITE | DB_READ, +.description = "change stream and play", +.synopsis = "csp s", +.help = + +"Select stream s and start playing. If this results in a stream-change,\n" +"skip rest of current audio file.\n" + +}, +{ +.name = "da", +.handler = com_da, +.perms = DB_READ | DB_WRITE, +.description = "drop attribute from database", +.synopsis = "da att", +.help = + +"Use with caution. All info on attribute att will be lost.\n" + +}, +{ +.name = "hist", +.handler = com_hist, +.perms = DB_READ, +.description = "print history", +.synopsis = "hist", +.help = + +"Print list of all audio files together with number of days since each\n" +"file was last played.\n" + +}, +{ +.name = "info", +.handler = com_info, +.perms = DB_READ, +.description = "print database info", +.synopsis = "info [af]", +.help = + +"print database informations for audio file af. Current audio file is\n" +"used if af is not given.\n" + +}, +{ +.name = "la", +.handler = com_info, +.perms = DB_READ, +.description = "list attributes", +.synopsis = "la [af]", +.help = + +"List attributes of audio file af or of current audio file when invoked\n" +"without arguments.\n" + +}, +{ +.name = "laa", +.handler = com_laa, +.perms = DB_READ, +.description = "list available attributes", +.synopsis = "laa", +.help = + +"What should I say more?\n" + +}, +{ +.name = "last", +.handler = com_last, +.perms = DB_READ, +.description = "print list of audio files, ordered by lastplayed time", +.synopsis = "last [n]", +.help = + +"The optional number n defaults to 10 if not specified.\n" + +}, +{ +.name = "ls", +.handler = com_ls, +.perms = DB_READ, +.description = "list all audio files that match a LIKE pattern", +.synopsis = "ls [pattern]", +.help = + +"\tIf pattern was not given, print list of all audio files known\n" +"\tto the mysql database tool. See the documentation of mysql\n" +"\tfor the definition of LIKE patterns.\n" + +}, +{ +.name = "mbox", +.handler = com_mbox, +.perms = DB_READ, +.description = "dump audio file list in mbox format", +.synopsis = "mbox [p]", +.help = + +"\tDump list of audio files in mbox format (email) to stdout. If\n" +"\tthe optional pattern p is given, only those audio files,\n" +"\twhose basename match p are going to be included. Otherwise,\n" +"\tall files are selected.\n" +"\n" +"EXAMPLE\n" +"\tThe mbox command can be used together with your favorite\n" +"\tmailer (this example uses mutt) for browsing the audio file\n" +"\tcollection:\n" +"\n" +"\t\tpara_client mbox > ~/para_mbox\n" +"\n" +"\t\tmutt -F ~/.muttrc.para -f ~/para_mbox\n" +"\n" +"\tFor playlists, you can use mutt's powerful pattern matching\n" +"\tlanguage to select files. If you like to tag all files\n" +"\tcontaining the pattern 'foo', type 'T', then '~s foo'.\n" +"\n" +"\tWhen ready with the list, type ';|' (i.e., hit the semicolon\n" +"\tkey to apply the next mutt command to all tagged messages,\n" +"\tthen the pipe key) to pipe the selected \"mails\" to a\n" +"\tsuitable script which adds a paraslash stream where exactly\n" +"\tthese files are admissable or does whatever thou wilt.\n" + +}, +{ +.name = "mv", +.handler = com_mv, +.perms = DB_READ | DB_WRITE, +.description = "rename entry in database", +.synopsis = "mv oldname newname", +.help = + +"Rename oldname to newname. This updates the data table to reflect the\n" +"new name. All internal data (numplayed, lastplayed, picid,..) is kept.\n" +"If newname is a full path, the dir table is updated as well.\n" + +}, +{ +.name = "na", +.handler = com_na, +.perms = DB_READ | DB_WRITE, +.description = "add new attribute to database", +.synopsis = "na att", +.help = + +"This adds a column named att to your mysql database. att should only\n" +"contain letters and numbers, in paricular, '+' and '-' are not allowed.\n" + +}, +{ +.name = "ne", +.handler = com_rm_ne, +.perms = DB_READ | DB_WRITE, +.description = "add new database entries", +.synopsis = "ne file1 [file2 [...]]", +.help = + +"Add the given filename(s) to the database, where file1,... must\n" +"be full path names. This command might be much faster than 'upd'\n" +"if the number of given files is small.\n" + +}, +{ +.name = "ns", +.handler = com_ps, +.perms = AFS_WRITE | DB_READ | DB_WRITE, +.description = "change to next stream", +.synopsis = "ns", +.help = + +"Cycle forwards through stream list.\n" + +}, +{ +.name = "pic", +.handler = com_pic, +.perms = DB_READ, +.description = "get picture by name or by identifier", +.synopsis = "pic [name]", +.help = + +"\tDump jpg image that is associated to given audio file (current\n" +"\taudio file if not specified) to stdout. If name starts with\n" +"\t'#' it is interpreted as an identifier instead and the picture\n" +"\thaving that identifier is dumped to stdout.\n" +"\n" +"EXAMPLE\n" +"\n" +"\tpara_client pic '#123' > pic123.jpg\n" + +}, +{ +.name = "picadd", +.handler = com_stradd_picadd, +.perms = DB_READ | DB_WRITE, +.description = "add picture to database", +.synopsis = "picadd [picname]", +.help = + +"\tRead jpeg file from stdin and store it as picname in database.\n" +"\n" +"EXAMPLE\n" +"\n" +"\tpara_client picadd foo.jpg < foo.jpg\n" + +}, +{ +.name = "picass", +.handler = com_set, +.perms = DB_READ | DB_WRITE, +.description = "associate a picture to file(s)", +.synopsis = "picass pic_id file1 [file2...]", +.help = + +"Associate the picture given by pic_id to all given files.\n" + +}, +{ +.name = "picch", +.handler = com_picch, +.perms = DB_READ | DB_WRITE, +.description = "change name of picture", +.synopsis = "picch id new_name", +.help = + +"Asign new_name to picture with identifier id.\n" + +}, +{ +.name = "picdel", +.handler = com_picdel, +.perms = DB_READ | DB_WRITE, +.description = "delete picture from database", +.synopsis = "picdel id1 [id2...]", +.help = + +"Delete each given picture from database.\n" + +}, +{ +.name = "piclist", +.handler = com_piclist, +.perms = DB_READ, +.description = "print list of pictures", +.synopsis = "piclist", +.help = + +"Print id, name and length of each picture contained in the database.\n" + +}, +{ +.name = "ps", +.handler = com_ps, +.perms = AFS_WRITE | DB_READ | DB_WRITE, +.description = "change to previous stream", +.synopsis = "ps", +.help = + +"Cycle backwards through stream list.\n" + +}, +{ +.name = "rm", +.handler = com_rm_ne, +.perms = DB_READ | DB_WRITE, +.description = "remove entries from database", +.synopsis = "rm name1 [name2 [...]]", +.help = + +"Remove name1, name2, ... from the data table. Use with caution\n" + +}, +{ +.name = "sa", +.handler = com_sa, +.perms = DB_READ | DB_WRITE, +.description = "set/unset attributes", +.synopsis = "sa at1<'+' | '-'> [at2<'+' | '-'> ] [af1 ...]", +//.synopsis = "foo", +.help = + +"Set ('+') or unset ('-') attribute at1, at2 etc. for given list of\n" +"audio files. If no audio files were given the current audio file is\n" +"used. Example:\n" +"\n" +"sa rock+ punk+ classic- LZ__Waldsterben.mp3\n" +"\n" +"sets the 'rock' and the 'punk' attribute but unsets the 'classic'\n" +"attribute.\n" + +}, +{ +.name = "skip", +.handler = com_sl, +.perms = DB_READ | DB_WRITE, +.description = "skip subsequent audio files(s)", +.synopsis = "skip n [s]", +.help = + +"Skip the next n audio files of stream s. This is equivalent to the\n" +"command 'sl n s', followed by 'us name' for each name the output of sl.\n" + +}, +{ +.name = "sl", +.handler = com_sl, +.perms = DB_READ, +.description = "print score list", +.synopsis = "sl n [s]", +.help = + +"Print sorted list of maximal n lines. Each line is an admissible entry\n" +"with respect to stream s. The list is sorted by score-value which is\n" +"given by the definition of s. If s is not given, the current stream\n" +"is used. Example:\n" +"\n" +" sl 1\n" +"\n" +"shows you the audio file the server would select right now.\n" + +}, +{ +.name = "snp", +.handler = com_set, +.perms = DB_READ | DB_WRITE, +.description = "set numplayed", +.synopsis = "snp number af1 [af2 ...]", +.help = + +"Update the numplayed field in the data table for all given audio files.\n" + +}, +{ +.name = "stradd", +.handler = com_stradd_picadd, +.perms = DB_READ | DB_WRITE, +.description = "add stream", +.synopsis = "stradd s", +.help = + +"Add stream s to the list of available streams. The stream definition\n" +"for s is read from stdin and is then sent to para_server. Example:\n" +"\n" +" echo 'deny: NAME_LIKE(%Madonna%)' | para_client stradd no_madonna\n" +"\n" +"adds the new stream 'no_madonna' to the list of available streams. A given\n" +"audio file is admissible for this stream iff its basename does not contain the\n" +"string 'Madonna'.\n" + + +}, +{ +.name = "strdel", +.handler = com_strdel, +.perms = DB_READ | DB_WRITE, +.description = "delete stream", +.synopsis = "strdel s", +.help = + +"Remove stream s from database.\n" + +}, +{ +.name = "streams", +.handler = com_streams, +.perms = DB_READ, +.description = "list streams", +.synopsis = "streams", +.help = + +"Print list of available streams. Use 'cs' to switch to any of these.\n" + +}, +{ +.name = "strq", +.handler = com_strq, +.perms = DB_READ, +.description = "query stream definition", +.synopsis = "strq [s]", +.help = + +"Print definition of stream s to stdout. Use current stream if s was\n" +"not given.\n" + +}, +{ +.name = "summary", +.handler = com_summary, +.perms = DB_READ, +.description = "list attributes", +.synopsis = "summary", +.help = + +"\tPrint a list of attributes together with number of audio\n" +"\tfiles having that attribute set.\n" + +}, +{ +.name = "upd", +.handler = com_upd, +.perms = DB_READ | DB_WRITE, +.description = "update database", +.synopsis = "upd", +.help = + +"This command uses the --audio_file_dir option of para_server to locate\n" +"your audio files. New files are then added to the mysql database. Use\n" +"this command if you got new files or if you have moved some files\n" +"around.\n" + +}, +{ +.name = "us", +.handler = com_us, +.perms = DB_READ | DB_WRITE, +.description = "update lastplayed time", +.synopsis = "us name", +.help = + +"Update lastplayed time without actually playing the thing.\n" + +}, +{ +.name = "verb", +.handler = com_verb, +.perms = DB_READ | DB_WRITE, +.description = "send verbatim sql query", +.synopsis = "verb cmd", +.help = + +"Send cmd to mysql server. For expert/debugging only. Note that cmd\n" +"usually must be escaped. Use only if you know what you are doing!\n" + +}, +{ +.name = "vrfy", +.handler = com_vrfy, +.perms = DB_READ, +.description = "list invalid entries in database", +.synopsis = "vrfy", +.help = + +"Show what clean would delete. Run 'upd' before this command to make\n" +"sure your database is up to date.\n" + +}, +{ +.name = NULL, +} +}; + +static struct para_macro macro_list[] = { + { .name = "IS_N_SET", + .replacement = "(data.%s != '1')" + }, { + .name = "IS_SET", + .replacement = "(data.%s = '1')" + }, { + .name = "PICID", + .replacement = "%sdata.Pic_Id" + }, { + .name = "NAME_LIKE", + .replacement = "(data.name like '%s')" + }, { + .name = "LASTPLAYED", + .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())" + "-UNIX_TIMESTAMP(data.Lastplayed))/60)" + }, { + .name = "NUMPLAYED", + .replacement = "%sdata.Numplayed" + }, { + .name = NULL, + } +}; + +static int real_query(char *query) +{ + if (!mysql_ptr) + return -E_NOTCONN; + PARA_DEBUG_LOG("%s\n", query); + if (mysql_real_query(mysql_ptr, query, strlen(query))) { + PARA_ERROR_LOG("real_query error (%s)\n", + mysql_error(mysql_ptr)); + return -E_QFAILED; + } + return 1; +} + +/* + * Use open connection given by mysql_ptr to query server. Returns a + * result pointer on succes and NULL on errors + */ +static struct MYSQL_RES *get_result(char *query) +{ + void *result; + + if (real_query(query) < 0) + return NULL; + result = mysql_store_result(mysql_ptr); + if (!result) + PARA_ERROR_LOG("%s", "store_result error\n"); + return result; +} +/* + * write input from fd to dynamically allocated char array, + * but maximal max_size byte. Return size. + */ +static int fd2buf(int fd, char **buf_ptr, size_t max_size) +{ + const size_t chunk_size = 1024; + size_t size = 2048; + char *buf = para_malloc(size * sizeof(char)), *p = buf; + int ret; + + while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) { + p += ret; + if ((p - buf) + chunk_size >= size) { + char *tmp; + + size *= 2; + if (size > max_size) { + ret = -E_TOOBIG; + goto out; + } + tmp = para_realloc(buf, size); + p = (p - buf) + tmp; + buf = tmp; + } + } + if (ret < 0) + goto out; + *buf_ptr = buf; + ret = p - buf; +out: + if (ret < 0 && buf) + free(buf); + return ret; +} + +static char *escape_blob(char* old, int size) +{ + char *new; + + if (!mysql_ptr || size < 0) + return NULL; + new = para_malloc(2 * size * sizeof(char) + 1); + mysql_real_escape_string(mysql_ptr, new, old, size); + return new; +} + +static char *escape_str(char* old) +{ + return escape_blob(old, strlen(old)); +} + +static char *escaped_basename(const char *name) +{ + char *esc, *bn = para_basename(name); + + if (!bn) + return NULL; + esc = escape_str(bn); + free(bn); + return esc; +} + +/* + * new attribute + */ +static int com_na(__unused int fd, int argc, char *argv[]) +{ + char *q; + int ret; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + q = make_message("alter table data add %s char(1) " + "not null default 0", argv[1]); + ret = real_query(q); + free(q); + return ret; +} + +/* + * delete attribute + */ +static int com_da(__unused int fd, int argc, char *argv[]) +{ + char *q; + int ret; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + q = make_message("alter table data drop %s", argv[1]); + ret = real_query(q); + free(q); + return ret; +} + +/* stradd/pic_add */ +static int com_stradd_picadd(int fd, int argc, char *argv[]) +{ + char *blob = NULL, *esc_blob = NULL, *q; + const char *fmt, *del_fmt; + int ret, stradd = strcmp(argv[0], "picadd"); + size_t size; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + if (strlen(argv[1]) >= MAXLINE - 1) + return -E_NAMETOOLONG; + if (!mysql_ptr) + return -E_NOTCONN; + if (stradd) { + size = BLOB_SIZE; + fmt = "insert into streams (name, def) values ('%s','%s')"; + del_fmt="delete from streams where name='%s'"; + } else { + size = MEDIUM_BLOB_SIZE; + fmt = "insert into pics (name, pic) values ('%s','%s')"; + del_fmt="delete from pics where pic='%s'"; + } + q = make_message(del_fmt, argv[1]); + ret = real_query(q); + free(q); + if (ret < 0) + return ret; + if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0)) + return ret; + if ((ret = fd2buf(fd, &blob, size)) < 0) + return ret; + PARA_DEBUG_LOG("length: %i\n", ret); + size = ret; + if (stradd) + blob[size] = '\0'; + esc_blob = escape_blob(blob, ret); + free(blob); + if (!esc_blob) + return -E_TOOBIG; + q = make_message(fmt, argv[1], esc_blob); + free(esc_blob); + ret = real_query(q); + free(q); + return ret; +} + +/* + * print results to fd + */ +static int print_results(int fd, void *result, + unsigned int top, unsigned int left, + unsigned int bottom, unsigned int right) +{ + unsigned int i,j; + int ret; + MYSQL_ROW row; + + for (i = top; i <= bottom; i++) { + row = mysql_fetch_row(result); + if (!row || !row[0]) + return -E_NOROW; + for (j = left; j <= right; j++) { + ret = send_va_buffer(fd, j == left? "%s" : "\t%s", + row[j]? row[j] : "NULL"); + if (ret < 0) + return ret; + } + ret = send_buffer(fd, "\n"); + if (ret < 0) + return ret; + } + return 0; +} + +/* + * verbatim + */ +static int com_verb(int fd, int argc, char *argv[]) +{ + void *result = NULL; + int ret; + unsigned int num_rows, num_fields; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + result = get_result(argv[1]); + if (!result) + /* return success, because it's ok to have no results */ + return 1; + num_fields = mysql_field_count(mysql_ptr); + num_rows = mysql_num_rows(result); + ret = 1; + if (num_fields && num_rows) + ret = print_results(fd, result, 0, 0, num_rows - 1, + num_fields - 1); + mysql_free_result(result); + return ret; +} + +/* returns NULL on errors or if there are no atts defined yet */ +static void *get_all_attributes(void) +{ + void *result = get_result("desc data"); + unsigned int num_rows; + + if (!result) + return NULL; + num_rows = mysql_num_rows(result); + if (num_rows < 5) { + mysql_free_result(result); + return NULL; + } + mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */ + return result; +} + +/* + * list all attributes + */ +static int com_laa(int fd, int argc, __unused char *argv[]) +{ + void *result; + int ret; + + if (argc) + return -E_MYSQL_SYNTAX; + result = get_all_attributes(); + if (!result) + return -E_NOATTS; + ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0); + mysql_free_result(result); + return ret; +} + +/* + * history + */ +static int com_hist(int fd, int argc, char *argv[]) { + int ret; + void *result = NULL; + char *q; + unsigned int num_rows; + + q = make_message("select name, to_days(now()) - to_days(lastplayed) from " + "data%s%s%s order by lastplayed", + (argc < 1)? "" : " where ", + (argc < 1)? "" : argv[1], + (argc < 1)? "" : " = '1'"); + result = get_result(q); + free(q); + if (!result) + return -E_NORESULT; + num_rows = mysql_num_rows(result); + ret = 1; + if (num_rows) + ret = print_results(fd, result, 0, 0, num_rows - 1, 1); + mysql_free_result(result); + return ret; +} + +/* + * get last num audio files + */ +static int com_last(int fd, int argc, char *argv[]) +{ + void *result = NULL; + char *q; + int num, ret; + + if (argc < 1) + num = 10; + else + num = atoi(argv[1]); + if (!num) + return -E_MYSQL_SYNTAX; + q = make_message("select name from data order by lastplayed desc " + "limit %u", num); + result = get_result(q); + free(q); + if (!result) + return -E_NORESULT; + ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0); + mysql_free_result(result); + return ret; +} + +static int com_mbox(int fd, int argc, char *argv[]) +{ + void *result; + MYSQL_ROW row; + int ret; + unsigned int num_rows, num_fields; + char *query = para_strdup("select concat('From foo@localhost ', " + "date_format(Lastplayed, '%a %b %e %T %Y'), " + "'\nReceived: from\nTo: bar\n"); + + ret = -E_NOATTS; + result = get_all_attributes(); + if (!result) + goto out; + ret = -E_NOROW; + while ((row = mysql_fetch_row(result))) { + char *tmp; + + if (!row[0]) + goto out; + tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query, + row[0], row[0]); + free(query); + query = tmp; + } + query = para_strcat(query, + "From: a\n" + "Subject: " + "', name, '" + "\n\n\n" + "') from data" + ); + if (argc >= 1) { + char *tmp = make_message("%s where name LIKE '%s'", query, + argv[1]); + free(query); + query = tmp; + } + mysql_free_result(result); + ret = -E_NORESULT; + result = get_result(query); + if (!result) + goto out; + ret = -E_EMPTY_RESULT; + num_fields = mysql_field_count(mysql_ptr); + num_rows = mysql_num_rows(result); + if (!num_fields || !num_rows) + goto out; + ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1); +out: + free(query); + if (result) + mysql_free_result(result); + return ret; +} + +/* get attributes by name. If verbose is not 0, get_a writes a string + * into atts of the form 'att1="0",att2="1"', which is used in com_cam + * for contructing a mysql update query. + * never returns NULL in *NON VERBOSE* mode + */ +static char *get_atts(char *name, int verbose) +{ + char *atts = NULL, *buf, *ebn; + void *result = NULL, *result2 = NULL; + MYSQL_ROW row, row2; + int i, ret; + unsigned int num_fields; + + ret = -E_NOATTS; + result2 = get_all_attributes(); + if (!result2) + goto out; + ret = -E_ESCAPE; + if (!(ebn = escaped_basename(name))) + goto out; + buf = make_message("select * from data where name='%s'", ebn); + free(ebn); + ret = -E_NORESULT; + result = get_result(buf); + free(buf); + if (!result) + goto out; + ret = -E_EMPTY_RESULT; + num_fields = mysql_num_fields(result); + if (num_fields < 5) + goto out; + mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */ + row = mysql_fetch_row(result); + ret = -E_NOROW; + if (!row) + goto out; + for (i = 4; i < num_fields; i++) { + int is_set = row[i] && !strcmp(row[i], "1"); + row2 = mysql_fetch_row(result2); + if (!row2 || !row2[0]) + goto out; + if (atts && (verbose || is_set)) + atts = para_strcat(atts, verbose? "," : " "); + if (is_set || verbose) + atts = para_strcat(atts, row2[0]); + if (verbose) + atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\""); + } + ret = 1; +out: + if (result2) + mysql_free_result(result2); + if (result) + mysql_free_result(result); + if (!atts && !verbose) + atts = para_strdup("(none)"); + return atts; +} + +/* never returns NULL in verbose mode */ +static char *get_meta(char *name, int verbose) +{ + MYSQL_ROW row; + void *result = NULL; + char *ebn, *q, *ret = NULL; + const char *verbose_fmt = + "select concat('lastplayed: ', " + "(to_days(now()) - to_days(lastplayed))," + "' day(s). numplayed: ', numplayed, " + "', pic: ', pic_id) " + "from data where name = '%s'"; + /* is that really needed? */ + const char *fmt = "select concat('lastplayed=\\'', lastplayed, " + "'\\', numplayed=\\'', numplayed, " + "'\\', pic_id=\\'', pic_id, '\\'') " + "from data where name = '%s'"; + + if (!(ebn = escaped_basename(name))) + goto out; + q = make_message(verbose? verbose_fmt : fmt, ebn); + free(ebn); + result = get_result(q); + free(q); + if (!result) + goto out; + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + ret = para_strdup(row[0]); +out: + if (result) + mysql_free_result(result); + if (!ret && verbose) + ret = para_strdup("(not yet played)"); + return ret; +} + +static char *get_dir(char *name) +{ + char *ret = NULL, *q, *ebn; + void *result; + MYSQL_ROW row; + + if (!(ebn = escaped_basename(name))) + return NULL; + q = make_message("select dir from dir where name = '%s'", ebn); + free(ebn); + result = get_result(q); + free(q); + if (!result) + return NULL; + row = mysql_fetch_row(result); + if (row && row[0]) + ret = para_strdup(row[0]); + mysql_free_result(result); + return ret; +} + +/* never returns NULL */ +static char *get_current_stream(void) +{ + char *ret; + MYSQL_ROW row; + void *result = get_result("select def from streams where " + "name = 'current_stream'"); + + if (!result) + goto err_out; + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto err_out; + ret = para_strdup(row[0]); + mysql_free_result(result); + return ret; +err_out: + if (result) + mysql_free_result(result); + return para_strdup("(none)"); +} +/* + * Read stream definition of stream streamname and construct mysql + * query. Return NULL on errors. If streamname is NULL, use current + * stream. If that is also NULL, use query that selects everything. + * If filename is NULL, query will list everything, otherwise only + * the score of given file. + */ +static char *get_query(char *streamname, char *filename, int with_path) +{ + char *accept_opts = NULL, *deny_opts = NULL, *score = NULL; + char *where_clause, *order, *query; + char command[255] = ""; /* buffer for sscanf */ + void *result; + MYSQL_ROW row; + char *end, *tmp; + char *select_clause = NULL; + if (!streamname) + tmp = get_current_stream(); + else + tmp = para_strdup(streamname); + if (!strcmp(tmp, "(none)")) { + free(tmp); + if (filename) { + char *ret, *ebn = escaped_basename(filename); + ret = make_message("select to_days(now()) - " + "to_days(lastplayed) from data " + "where name = '%s'", ebn); + free(ebn); + return ret; + } + if (with_path) + return make_message( + "select concat(dir.dir, '/', dir.name) " + "from data, dir where dir.name = data.name " + "order by data.lastplayed" + ); + return make_message( + "select name from data where name is not NULL " + "order by lastplayed" + ); + } + free(tmp); + query = make_message("select def from streams where name = '%s'", + streamname); + result = get_result(query); + free(query); + query = NULL; + if (!result) + goto out; + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + end = row[0]; + while (*end) { + int n; + char *arg, *line = end; + + if (!(end = strchr(line, '\n'))) + break; + *end = '\0'; + end++; + if (sscanf(line, "%200s%n", command, &n) < 1) + continue; + arg = line + n; + if (!strcmp(command, "accept:")) { + char *tmp2 = s_a_r_list(macro_list, arg); + if (accept_opts) + accept_opts = para_strcat( + accept_opts, " or "); + accept_opts = para_strcat(accept_opts, tmp2); + free(tmp2); + continue; + } + if (!strcmp(command, "deny:")) { + char *tmp2 = s_a_r_list(macro_list, arg); + if (deny_opts) + deny_opts = para_strcat(deny_opts, " or "); + deny_opts = para_strcat(deny_opts, tmp2); + free(tmp2); + continue; + } + if (!strcmp(command, "score:")) + score = s_a_r_list(macro_list, arg); + } + if (!score) { + score = s_a_r_list(macro_list, conf.mysql_default_score_arg); + if (!score) + goto out; + } + if (filename) { + char *ebn = escaped_basename(filename); + if (!ebn) + goto out; + select_clause = make_message("select %s from data ", score); + free(score); + where_clause = make_message( "where name = '%s' ", ebn); + free(ebn); + order = para_strdup(""); + goto write_query; + } + select_clause = para_strdup(with_path? + "select concat(dir.dir, '/', dir.name) from data, dir " + "where dir.name = data.name " + : + "select name from data where name is not NULL"); + order = make_message("order by -(%s)", score); + free(score); + if (accept_opts && deny_opts) { + where_clause = make_message("and ((%s) and not (%s)) ", + accept_opts, deny_opts); + goto write_query; + } + if (accept_opts && !deny_opts) { + where_clause = make_message("and (%s) ", accept_opts); + goto write_query; + } + if (!accept_opts && deny_opts) { + where_clause = make_message("and not (%s) ", deny_opts); + goto write_query; + } + where_clause = para_strdup(""); +write_query: + query = make_message("%s %s %s", select_clause, where_clause, order); + free(order); + free(select_clause); + free(where_clause); +out: + if (accept_opts) + free(accept_opts); + if (deny_opts) + free(deny_opts); + if (result) + mysql_free_result(result); + return query; +} + + + +/* + * This is called from server and from some commands. Name must not be NULL + * Never returns NULL. + */ +static char *get_dbinfo(char *name) +{ + char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL; + void *result = NULL; + MYSQL_ROW row = NULL; + + if (!name) + return para_strdup("(none)"); + stream = get_current_stream(); + meta = get_meta(name, 1); + atts = get_atts(name, 0); + dir = get_dir(name); + /* get score */ + query = get_query(stream, name, 0); + if (!query) + goto write; + result = get_result(query); + free(query); + if (result) + row = mysql_fetch_row(result); +write: + info = make_message("dbinfo1:dir: %s\n" + "dbinfo2:stream: %s, %s, score: %s\n" + "dbinfo3:%s\n", + dir? dir : "(not contained in table)", + stream, meta, + (result && row && row[0])? row[0] : "(no score)", + atts); + if (dir) + free(dir); + if (meta) + free(meta); + if (atts) + free(atts); + if (stream) + free(stream); + if (result) + mysql_free_result(result); + return info; +} + + +/* might return NULL */ +static char *get_current_audio_file(void) +{ + char *name; + mmd_lock(); + name = para_basename(mmd->filename); + mmd_unlock(); + return name; +} + + +/* print database info */ +static int com_info(int fd, int argc, char *argv[]) +{ + char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL; + int ret, com_la = strcmp(argv[0], "info"); + + if (argc < 1) { + ret = -E_GET_AUDIO_FILE; + if (!(name = get_current_audio_file())) + goto out; + ret = send_va_buffer(fd, "%s\n", name); + if (ret < 0) + goto out; + } else { + ret = -E_ESCAPE; + if (!(name = escaped_basename(argv[1]))) + goto out; + } + meta = get_meta(name, 1); + atts = get_atts(name, 0); + dir = get_dir(name); + if (com_la) + ret = send_va_buffer(fd, "%s\n", atts); + else + ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n", + dir? dir : "(not contained in table)", meta, atts); +out: + if (meta) + free(meta); + if (atts) + free(atts); + if (dir) + free(dir); + if (name) + free(name); + return ret; +} +static int change_stream(char *stream) +{ + char *query; + int ret; + /* try to insert if it does not exist (compatibility) */ +// query = make_message("insert into streams (name, def) values " +// "('current_stream', '%s')", stream); +// real_query(query); /* ignore return value */ +// free(query); + query = make_message("update streams set def='%s' " + "where name = 'current_stream'", stream); + ret = real_query(query); + free(query); + return ret; +} + +static int get_pic_id_by_name(char *name) +{ + char *q, *ebn; + void *result = NULL; + long unsigned ret; + MYSQL_ROW row; + + if (!(ebn = escaped_basename(name))) + return -E_ESCAPE; + q = make_message("select pic_id from data where name = '%s'", ebn); + free(ebn); + result = get_result(q); + free(q); + if (!result) + return -E_NORESULT; + row = mysql_fetch_row(result); + ret = -E_NOROW; + if (row && row[0]) + ret = atol(row[0]); + mysql_free_result(result); + return ret; +} + +static int remove_entry(const char *name) +{ + char *q, *ebn = escaped_basename(name); + int ret = -E_ESCAPE; + + if (!ebn || !*ebn) + goto out; + q = make_message("delete from data where name = '%s'", ebn); + real_query(q); /* ignore errors */ + free(q); + q = make_message("delete from dir where name = '%s'", ebn); + real_query(q); /* ignore errors */ + free(q); + ret = 1; +out: + free(ebn); + return ret; +} + +static int add_entry(const char *name) +{ + char *q, *dn, *ebn = NULL, *edn = NULL; + int ret; + + if (!name || !*name) + return -E_MYSQL_SYNTAX; + ebn = escaped_basename(name); + if (!ebn) + return -E_ESCAPE; + ret = -E_MYSQL_SYNTAX; + dn = para_dirname(name); + if (!dn) + goto out; + ret = -E_ESCAPE; + edn = escape_str(dn); + free(dn); + if (!edn || !*edn) + goto out; + q = make_message("insert into data (name, pic_id) values " + "('%s', '%s')", ebn, "1"); + ret = real_query(q); +// ret = 1; PARA_DEBUG_LOG("q: %s\n", q); + free(q); + if (ret < 0) + goto out; + q = make_message("insert into dir (name, dir) values " + "('%s', '%s')", ebn, edn); +// ret = 1; PARA_DEBUG_LOG("q: %s\n", q); + ret = real_query(q); + free(q); +out: + if (ebn) + free(ebn); + if (edn) + free(edn); + return ret; +} + +/* + * remove/add entries + */ +static int com_rm_ne(__unused int fd, int argc, char *argv[]) +{ + int ne = !strcmp(argv[0], "ne"); + int i, ret; + if (argc < 1) + return -E_MYSQL_SYNTAX; + for (i = 1; i <= argc; i++) { + ret = remove_entry(argv[i]); + if (ret < 0) + return ret; + if (!ne) + continue; + ret = add_entry(argv[i]); + if (ret < 0) + return ret; + } + return 1; +} + +/* + * mv: rename entry + */ +static int com_mv(__unused int fd, int argc, char *argv[]) +{ + char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL; + int ret; + + if (argc != 2) + return -E_MYSQL_SYNTAX; + ebn1 = escaped_basename(argv[1]); + ebn2 = escaped_basename(argv[2]); + dn = para_dirname(argv[2]); + edn = escape_str(dn); + free(dn); + ret = -E_ESCAPE; + if (!ebn1 || !ebn2) + goto out; + remove_entry(ebn2); + q = make_message("update data set name = '%s' where name = '%s'", + ebn2, ebn1); + ret = real_query(q); + free(q); + if (ret < 0) + goto out; + q = make_message("update dir set name = '%s' where name = '%s'", + ebn2, ebn1); + ret = real_query(q); + free(q); + if (ret < 0) + goto out; + /* do not touch table dir, return success if argv[2] is no full path */ + ret = 1; + if (!edn || !*edn) + goto out; + q = make_message("update dir set dir = '%s' where name = '%s'", + edn, ebn2); +// PARA_DEBUG_LOG("q: %s\n", q); + ret = real_query(q); + free(q); +out: + if (ebn1) + free(ebn1); + if (ebn2) + free(ebn2); + if (edn) + free(edn); + return ret; + +} + +/* + * picass: associate pic to audio file + * snp: set numplayed + */ +static int com_set(__unused int fd, int argc, char *argv[]) +{ + char *q, *ebn; + long unsigned id; + int i, ret; + char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id"; + + if (argc < 2) + return -E_MYSQL_SYNTAX; + id = atol(argv[1]); + for (i = 2; i <= argc; i++) { + ebn = escaped_basename(argv[i]); + if (!ebn) + return -E_ESCAPE; + q = make_message("update data set %s = %lu " + "where name = '%s'", field, id, ebn); + free(ebn); + ret = real_query(q); + free(q); + if (ret < 0) + return ret; + } + return 1; +} + +/* + * picch: change entry's name in pics table + */ +static int com_picch(__unused int fd, int argc, char *argv[]) +{ + int ret; + long unsigned id; + char *q; + + if (argc != 2) + return -E_MYSQL_SYNTAX; + id = atol(argv[1]); + if (strlen(argv[2]) > MAXLINE) + return -E_NAMETOOLONG; + q = make_message("update pics set name = '%s' where id = %lu", argv[2], id); + ret = real_query(q); + free(q); + return ret; +} + +/* + * piclist: print list of pics in db + */ +static int com_piclist(__unused int fd, int argc, __unused char *argv[]) +{ + void *result = NULL; + MYSQL_ROW row; + unsigned long *length; + int ret; + + if (argc) + return -E_MYSQL_SYNTAX; + result = get_result("select id,name,pic from pics order by id"); + if (!result) + return -E_NORESULT; + while ((row = mysql_fetch_row(result))) { + length = mysql_fetch_lengths(result); + if (!row || !row[0] || !row[1] || !row[2]) + continue; + ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]); + if (ret < 0) + goto out; + } + ret = 1; +out: + mysql_free_result(result); + return ret; +} + +/* + * picdel: delete picture from database + */ +static int com_picdel(int fd, int argc, char *argv[]) +{ + char *q; + long unsigned id; + my_ulonglong aff; + int i, ret; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + for (i = 1; i <= argc; i++) { + id = atol(argv[i]); + q = make_message("delete from pics where id = %lu", id); + ret = real_query(q); + free(q); + if (ret < 0) + return ret; + aff = mysql_affected_rows(mysql_ptr); + if (!aff) { + ret = send_va_buffer(fd, "No such id: %lu\n", id); + if (ret < 0) + return ret; + continue; + } + q = make_message("update data set pic_id = 1 where pic_id = %lu", id); + ret = real_query(q); + free(q); + } + return 1; +} +/* + * pic: get picture by name or by number + */ +static int com_pic(int fd, int argc, char *argv[]) +{ + void *result = NULL; + MYSQL_ROW row; + unsigned long *length, id; + int ret; + char *q, *name = NULL; + + if (argc < 1) { + ret = -E_GET_AUDIO_FILE; + name = get_current_audio_file(); + } else { + ret = -E_ESCAPE; + name = escaped_basename(argv[1]); + } + if (!name) + return ret; + if (*name == '#') + id = atoi(name + 1); + else + id = get_pic_id_by_name(name); + free(name); + if (id <= 0) + return id; + q = make_message("select pic from pics where id = '%lu'", id); + result = get_result(q); + free(q); + if (!result) + return -E_NORESULT; + row = mysql_fetch_row(result); + ret = -E_NOROW; + if (!row || !row[0]) + goto out; + length = mysql_fetch_lengths(result); + ret = send_bin_buffer(fd, row[0], *length); +out: + mysql_free_result(result); + return ret; +} + +/* strdel */ +static int com_strdel(__unused int fd, int argc, char *argv[]) +{ + char *tmp; + int ret = -1; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + tmp = make_message("delete from streams where name='%s'", argv[1]); + ret = real_query(tmp); + free(tmp); + if (ret < 0) + return ret; + tmp = get_current_stream(); + ret = 1; + if (strcmp(tmp, "(none)") && !strcmp(tmp, argv[1])) + ret = change_stream("(none)"); + return ret; +} + +/* + * ls + */ +static int com_ls(int fd, int argc, char *argv[]) +{ + char *q; + void *result; + int ret; + unsigned int num_rows; + + if (argc > 0) + q = make_message("select name from data where name LIKE '%s'", + argv[1]); + else + q = para_strdup("select name from data"); + result = get_result(q); + free(q); + if (!result) + return -E_NORESULT; + num_rows = mysql_num_rows(result); + ret = 1; + if (num_rows) + ret = print_results(fd, result, 0, 0, num_rows - 1, 0); + mysql_free_result(result); + return ret; +} +/* + * summary + */ +static int com_summary(__unused int fd, int argc, __unused char *argv[]) +{ + MYSQL_ROW row; + MYSQL_ROW row2; + void *result; + void *result2 = NULL; + const char *fmt = "select count(name) from data where %s='1'"; + int ret = -E_NORESULT; + + if (argc) + return -E_MYSQL_SYNTAX; + result = get_all_attributes(); + if (!result) + goto out; + while ((row = mysql_fetch_row(result))) { + char *buf; + + ret = -E_NOROW; + if (!row[0]) + goto out; + ret = -E_NORESULT; + buf = make_message(fmt, row[0]); + result2 = get_result(buf); + free(buf); + if (!result2) + goto out; + ret = -E_NOROW; + row2 = mysql_fetch_row(result2); + if (!row2 || !row2[0]) + goto out; + ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]); + if (ret < 0) + goto out; + } + ret = 1; +out: + if (result2) + mysql_free_result(result2); + if (result) + mysql_free_result(result); + return ret; +} + +static int get_numplayed(char *name) +{ + void *result; + MYSQL_ROW row; + const char *fmt = "select numplayed from data where name = '%s'"; + char *buf = make_message(fmt, name); + int ret = -E_NORESULT; + + result = get_result(buf); + free(buf); + if (!result) + goto out; + ret = -E_NOROW; + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + ret = atoi(row[0]); +out: + if (result) + mysql_free_result(result); + return ret; +} + +static int update_audio_file(char *name) +{ + int ret; + const char *fmt1 = "update data set lastplayed = now() where name = '%s'"; + const char *fmt2 = "update data set numplayed = %i where name = '%s'"; + char *q; + char *ebn = escaped_basename(name); + + ret = -E_ESCAPE; + if (!ebn) + goto out; + q = make_message(fmt1, ebn); + ret = real_query(q); + free(q); + if (ret < 0) + goto out; + ret = get_numplayed(ebn); + if (ret < 0) + goto out; + q = make_message(fmt2, ret + 1, ebn); + ret = real_query(q); + free(q); +out: + if (ebn) + free(ebn); + return ret; +} +/* If called as child, mmd_lock must be held */ +static void update_mmd(char *info) +{ + PARA_DEBUG_LOG("%s", "updating shared memory area\n"); + strncpy(mmd->dbinfo, info, MMD_INFO_SIZE - 1); + mmd->dbinfo[MMD_INFO_SIZE - 1] = '\0'; +} + +static void update_audio_file_server_handler(char *name) +{ + char *info; + info = get_dbinfo(name); + update_mmd(info); + free(info); + update_audio_file(name); +} + +static int com_us(__unused int fd, int argc, char *argv[]) +{ + if (argc != 1) + return -E_MYSQL_SYNTAX; + return update_audio_file(argv[1]); +} + +static void refresh_mmd_dbinfo(void) +{ + char *name = get_current_audio_file(); + char *info; + + if (!name) + return; + info = get_dbinfo(name); + free(name); + mmd_lock(); + update_mmd(info); + mmd_unlock(); + free(info); +} + +/* select previous/next stream */ +static int com_ps(__unused int fd, int argc, char *argv[]) +{ + char *query, *stream = get_current_stream(); + void *result = get_result("select name from streams"); + MYSQL_ROW row; + int match = -1, ret, i; + unsigned int num_rows; + + if (argc) + return -E_MYSQL_SYNTAX; + ret = -E_NORESULT; + if (!result) + goto out; + num_rows = mysql_num_rows(result); + ret = -E_EMPTY_RESULT; + if (num_rows < 2) + goto out; + ret = -E_NOROW; + for (i = 0; i < num_rows; i++) { + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + if (!strcmp(row[0], "current_stream")) + continue; + if (!strcmp(row[0], stream)) { + match = i; + break; + } + } + ret = -E_NO_STREAM; + if (match < 0) + goto out; + if (!strcmp(argv[0], "ps")) + i = match > 0? match - 1 : num_rows - 1; + else + i = match < num_rows - 1? match + 1 : 0; + ret = -E_NOROW; + mysql_data_seek(result, i); + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + if (!strcmp(row[0], "current_stream")) { + if (!strcmp(argv[0], "ps")) { + i = match - 2; + i = i < 0? i + num_rows : i; + } else { + i = match + 2; + i = i > num_rows - 1? i - num_rows : i; + } + mysql_data_seek(result, i); + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + } + query = make_message("update streams set def='%s' where name = " + "'current_stream'", row[0]); + ret = real_query(query); + free(query); + refresh_mmd_dbinfo(); +out: + free(stream); + if (result) + mysql_free_result(result); + return ret; +} + +/* streams */ +static int com_streams(int fd, int argc, __unused char *argv[]) +{ + unsigned int num_rows; + int i, ret = -E_NORESULT; + void *result; + MYSQL_ROW row; + + if (argc && strcmp(argv[1], "current_stream")) + return -E_MYSQL_SYNTAX; + if (argc) { + char *cs = get_current_stream(); + ret = send_va_buffer(fd, "%s\n", cs); + free(cs); + return ret; + } + result = get_result("select name from streams"); + if (!result) + goto out; + num_rows = mysql_num_rows(result); + ret = 1; + if (!num_rows) + goto out; + ret = -E_NOROW; + for (i = 0; i < num_rows; i++) { + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + if (strcmp(row[0], "current_stream")) + send_va_buffer(fd, "%s\n", row[0]); + } + ret = 1; +out: + if (result) + mysql_free_result(result); + return ret; +} + +/* query stream definition */ +static int com_strq(int fd, int argc, char *argv[]) +{ + MYSQL_ROW row; + char *query, *name; + void *result; + int ret; + + if (argc < 1) { + ret = -E_GET_STREAM; + name = get_current_stream(); + } else { + ret = -E_ESCAPE; + name = escaped_basename(argv[1]); + } + if (!name) + return ret; + ret = -E_NORESULT; + query = make_message("select def from streams where name='%s'", name); + free(name); + result = get_result(query); + free(query); + if (!result) + goto out; + ret = -E_NOROW; + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto out; + /* no '\n' needed */ + ret = send_buffer(fd, row[0]); +out: + if (result) + mysql_free_result(result); + return ret; +} + +/* change stream / change stream and play */ +static int com_cs(int fd, int argc, char *argv[]) +{ + int ret, stream_change; + char *query; + char *old_stream = get_current_stream(); + int csp = !strcmp(argv[0], "csp"); + + if (!argc) { + ret = -E_MYSQL_SYNTAX; + if (csp) + goto out; + ret = send_va_buffer(fd, "%s\n", old_stream); + goto out; + } + ret = -E_GET_QUERY; + query = get_query(argv[1], NULL, 0); /* test if stream is valid */ + if (!query) + goto out; + free(query); + /* stream is ok */ + stream_change = strcmp(argv[1], old_stream); + if (stream_change) { + ret = change_stream(argv[1]); + if (ret < 0) + goto out; + refresh_mmd_dbinfo(); + } + if (csp) { + mmd_lock(); + mmd->new_afs_status_flags |= AFS_PLAYING; + if (stream_change) + mmd->new_afs_status_flags |= AFS_NEXT; + mmd_unlock(); + } + ret = 1; +out: + free(old_stream); + return ret; +} + +/* + * sl/skip + */ +static int com_sl(int fd, int argc, char *argv[]) +{ + void *result = NULL; + MYSQL_ROW row; + int ret, i, skip = !strcmp(argv[0], "skip"); + char *query, *stream, *tmp; + unsigned int num_rows, num; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + num = atoi(argv[1]); + if (!num) + return -E_MYSQL_SYNTAX; + stream = (argc == 1)? get_current_stream() : para_strdup(argv[2]); + tmp = get_query(stream, NULL, 0); + query = make_message("%s limit %d", tmp, num); + free(tmp); + ret = -E_GET_QUERY; + free(stream); + if (!query) + goto out; + ret = -E_NORESULT; + result = get_result(query); + free(query); + if (!result) + goto out; + ret = -E_EMPTY_RESULT; + num_rows = mysql_num_rows(result); + if (!num_rows) + goto out; + for (i = 0; i < num_rows && i < num; i++) { + row = mysql_fetch_row(result); + if (skip) { + send_va_buffer(fd, "Skipping %s\n", row[0]); + update_audio_file(row[0]); + } else + send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG"); + } + ret = 1; +out: + if (result) + mysql_free_result(result); + return ret; +} + +/* + * update attributes of name + */ +static int update_atts(int fd, char *name, char *atts) +{ + int ret; + char *ebn, *q, *old, *new = NULL; + + if (!mysql_ptr) + return -E_NOTCONN; + ebn = escaped_basename(name); + if (!ebn) + return -E_ESCAPE; + q = make_message("update data set %s where name = '%s'", atts, ebn); + old = get_atts(ebn, 0); + send_va_buffer(fd, "old: %s\n", old); + free(old); + ret = real_query(q); + free(q); + if (ret < 0) + goto out; + new = get_atts(ebn, 0); + ret = send_va_buffer(fd, "new: %s\n", new); + free(new); +out: + free(ebn); + return ret; +} + +/* + * set attributes + */ +static int com_sa(int fd, int argc, char *argv[]) +{ + int i, ret; + char *atts = NULL, *name; + + if (argc < 1) + return -E_MYSQL_SYNTAX; + for (i = 1; i <= argc; i++) { + int unset = 0; + char *tmp, *p =argv[i]; + int len = strlen(p); + + if (!len) + continue; + switch (p[len - 1]) { + case '+': + unset = 0; + break; + case '-': + unset = 1; + break; + default: + goto no_more_atts; + } + p[len - 1] = '\0'; + tmp = make_message("%s%s='%s'", atts? "," : "", p, + unset? "0" : "1"); + atts = para_strcat(atts, tmp); + free(tmp); + } +no_more_atts: + if (!atts) + return -E_NOATTS; + if (i > argc) { /* no name given, use current af */ + ret = -E_GET_AUDIO_FILE; + if (!(name = get_current_audio_file())) + goto out; + ret = update_atts(fd, name, atts); + free(name); + } else { + ret = 1; + for (; argv[i] && ret >= 0; i++) + ret = update_atts(fd, argv[i], atts); + } + refresh_mmd_dbinfo(); +out: + return ret; +} + +/* + * copy attributes + */ +static int com_cam(int fd, int argc, char *argv[]) +{ + char *name = NULL, *meta = NULL, *atts = NULL; + int i, ret; + + if (argc < 2) + return -E_MYSQL_SYNTAX; + if (!(name = escaped_basename(argv[1]))) + return -E_ESCAPE; + ret = -E_NOATTS; + if (!(atts = get_atts(name, 1))) + goto out; + ret = -E_META; + if (!(meta = get_meta(name, 0))) + goto out; + for (i = 2; i <= argc; i++) { + char *ebn, *q; + ret = -E_ESCAPE; + if (!(ebn = escaped_basename(argv[i]))) + goto out; + ret = send_va_buffer(fd, "updating %s\n", ebn); + if (ret < 0) { + free(ebn); + goto out; + } + q = make_message("update data set %s where name = '%s'", + meta, ebn); + if ((ret = update_atts(fd, ebn, atts)) >= 0) + ret = real_query(q); + free(ebn); + free(q); + if (ret < 0) + goto out; + } + ret = 1; +out: + if (name) + free(name); + if (meta) + free(meta); + if (atts) + free(atts); + return ret; +} + +/* + * verify / clean + */ +static int com_vrfy(int fd, int argc, __unused char *argv[]) +{ + char *query; + int ret, vrfy_mode = strcmp(argv[0], "clean"); + void *result = NULL; + unsigned int num_rows; + MYSQL_ROW row; + char *escaped_name; + + if (argc) + return -E_MYSQL_SYNTAX; + ret = -E_NORESULT; + result = get_result("select data.name from data left join dir on " + "dir.name = data.name where dir.name is NULL"); + if (!result) + goto out; + num_rows = mysql_num_rows(result); + if (!num_rows) { + ret = send_buffer(fd, "No invalid entries\n"); + goto out; + } + if (vrfy_mode) { + send_va_buffer(fd, "found %i invalid entr%s\n", num_rows, + num_rows == 1? "y" : "ies"); + ret = print_results(fd, result, 0, 0, num_rows - 1, 0); + goto out; + } + while ((row = mysql_fetch_row(result))) { + ret = -E_NOROW; + if (!row[0]) + goto out; + ret = -E_ESCAPE; + escaped_name = escape_str(row[0]); + if (!escaped_name) + goto out; + send_va_buffer(fd, "deleting %s\n", escaped_name); + query = make_message("delete from data where name = '%s'", + escaped_name); + ret = real_query(query); + free(query); + if (ret < 0) + goto out; + } + +out: + if (result) + mysql_free_result(result); + return ret; +} + +static FILE *out_file; + +static int mysql_write_tmp_file(const char *dir, const char *name) +{ + int ret = -E_TMPFILE; + char *msg = make_message("%s\t%s\n", dir, name); + + if (fputs(msg, out_file) != EOF) + ret = 1; + free(msg); + return ret; +} + +/* + * update database + */ +static int com_upd(int fd, int argc, __unused char *argv[]) +{ + char *tempname = NULL, *query = NULL; + int ret, out_fd = -1, num = 0; + void *result = NULL; + unsigned int num_rows; + MYSQL_ROW row; + + if (argc) + return -E_MYSQL_SYNTAX; + out_file = NULL; + tempname = para_strdup("/tmp/mysql.tmp.XXXXXX"); + ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH); + if (ret < 0) + goto out; + out_fd = ret; + out_file = fdopen(out_fd, "w"); + if (!out_file) { + close(out_fd); + goto out; + } + if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0) + goto out; + num = ftell(out_file); + /* + * we have to make sure the file hit the disk before we call + * real_query + */ + fclose(out_file); + out_file = NULL; + PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num); + if (!num) + goto out; + if ((ret = real_query("delete from dir")) < 0) + goto out; + query = make_message("load data infile '%s' into table dir " + "fields terminated by '\t' lines terminated by '\n' " + "(dir, name)", tempname); + ret = real_query(query); + free(query); + if (ret < 0) + goto out; + result = get_result("select dir.name from dir left join data on " + "data.name = dir.name where data.name is NULL"); + ret = -E_NORESULT; + if (!result) + goto out; + num_rows = mysql_num_rows(result); + if (!num_rows) { + ret = send_buffer(fd, "no new entries\n"); + goto out; + } + while ((row = mysql_fetch_row(result))) { + ret = -E_NOROW; + if (!row[0]) + goto out; + send_va_buffer(fd, "new entry: %s\n", row[0]); + query = make_message("insert into data (name, pic_id) values " + "('%s','%s')", row[0], "1"); + ret = real_query(query); + free(query); + if (ret < 0) + goto out; + } + ret = 1; +out: + if (out_fd >= 0) + unlink(tempname); + free(tempname); + if (out_file) + fclose(out_file); + if (result) + mysql_free_result(result); + return ret; +} + +static char **server_get_audio_file_list(unsigned int num) +{ + char **list = para_malloc((num + 1) * sizeof(char *)); + char *tmp, *query, *stream = get_current_stream(); + void *result = NULL; + unsigned int num_rows; + int i = 0; + MYSQL_ROW row; + + tmp = get_query(stream, NULL, 1); + free(stream); + query = make_message("%s limit %d", tmp, num); + free(tmp); + if (!query) + goto err_out; + result = get_result(query); + if (!result) + goto err_out; + num_rows = mysql_num_rows(result); + if (!num_rows) + goto err_out; + for (i = 0; i < num_rows && i < num; i++) { + row = mysql_fetch_row(result); + if (!row || !row[0]) + goto err_out; + list[i] = para_strdup(row[0]); + } + list[i] = NULL; + goto success; +err_out: + while (i > 0) { + i--; + free(list[i]); + } + free(list); + list = NULL; +success: + if (query) + free(query); + if (result) + mysql_free_result(result); + return list; +} + +/* + * connect to mysql server, return mysql pointer on success, -E_NOTCONN + * on errors. Called from parent on startup and also from com_cdb(). + */ +static int init_mysql_server(void) +{ + char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname(); + + mysql_ptr = mysql_init(NULL); + if (!mysql_ptr) { + PARA_CRIT_LOG("%s", "mysql init error\n"); + return -E_NOTCONN; + } + PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg, + conf.mysql_port_arg); + if (!conf.mysql_user_arg) + free(u); + /* + * If host is NULL a connection to the local host is assumed, + * If user is NULL, the current user is assumed + */ + if (!(mysql_ptr = mysql_real_connect(mysql_ptr, + conf.mysql_host_arg, + conf.mysql_user_arg, + conf.mysql_passwd_arg, + conf.mysql_database_arg, + conf.mysql_port_arg, NULL, 0))) { + PARA_CRIT_LOG("%s", "connect error\n"); + return -E_NOTCONN; + } + PARA_INFO_LOG("%s", "success\n"); + return 1; +} + +/* mmd lock must be held */ +static void write_msg2mmd(int success) +{ + sprintf(mmd->dbinfo, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n", + success < 0? PARA_STRERROR(-success) : + "successfully connected to mysql server", + success < 0? "" : mysql_get_server_info(mysql_ptr)); +} + +/* create database */ +static int com_cdb(int fd, int argc, char *argv[]) +{ + char *query, *name; + int ret; + + if (argc < 1) + name = "paraslash"; + else { + ret = -E_NAMETOOLONG; + name = argv[1]; + if (strlen(name) > MAXLINE) + goto out; + } + if (mysql_ptr) { + PARA_INFO_LOG("%s", "closing database\n"); + mysql_close(mysql_ptr); + } + /* dont use any database */ + conf.mysql_database_arg = NULL; /* leak? */ + ret = -E_MYSQL_INIT; + if (init_mysql_server() < 0 || !mysql_ptr) + goto out; + query = make_message("create database %s", name); + ret = real_query(query); + free(query); + if (ret < 0) + goto out; + /* reconnect with database just created */ + mysql_close(mysql_ptr); + conf.mysql_database_arg = para_strdup(name); + ret = -E_MYSQL_INIT; + if (init_mysql_server() < 0 || !mysql_ptr) + goto out; + mmd_lock(); + write_msg2mmd(1); + mmd_unlock(); + ret = -E_QFAILED; + if (real_query("create table data (name varchar(255) binary not null " + "primary key, " + "lastplayed datetime not null default " + "'1970-01-01', " + "numplayed int not null default 0, " + "pic_id bigint unsigned not null default 1)") < 0) + goto out; + if (real_query("create table dir (name varchar(255) binary not null " + "primary key, dir varchar(255) default null)") < 0) + goto out; + if (real_query("create table pics (" + "id bigint(20) unsigned not null primary key " + "auto_increment, " + "name varchar(255) binary not null, " + "pic mediumblob not null)") < 0) + goto out; + if (real_query("create table streams (" + "name varchar(255) binary not null primary key, " + "def blob not null)") < 0) + goto out; + if (real_query("insert into streams (name, def) values " + "('current_stream', '(none)')") < 0) + goto out; + ret = send_va_buffer(fd, "successfully created database %s\n", name); +out: + return ret; +} + +static void shutdown_connection(void) +{ + if (mysql_ptr) { + PARA_NOTICE_LOG("%s", "shutting down mysql connection\n"); + mysql_close(mysql_ptr); + mysql_ptr = NULL; + } +} + +/** + * the init function of the mysql-based database tool + * + * Check the command line options and initialize all function pointers of \a db. + * Connect to the mysql server and initialize the dbinfo string. + * + * \sa struct audio_file_selector, misc_meta_data::dbinfo, random_selector.c + */ +int mysql_selector_init(struct audio_file_selector *db) +{ + int ret; + + if (!conf.mysql_passwd_given) + return -E_NO_MYSQL_PASSWD; + if (!conf.mysql_audio_file_dir_given) + return -E_NO_AF_DIR; + db->name = "mysql"; + db->cmd_list = cmds; + db->get_audio_file_list = server_get_audio_file_list; + db->update_audio_file = update_audio_file_server_handler; + db->shutdown = shutdown_connection; + ret = init_mysql_server(); + if (ret < 0) + PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret)); + write_msg2mmd(ret); + return 1; /* return success even if connect failed to give the + * user the chance to exec com_cdb + */ +}