FEATURES: (no log message)

[paraslash.git] / mysql_selector.c

/*
 * Copyright (C) 1999-2006 Andre Noll <maan@systemlinux.org>
 *
 *     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 <mysql/mysql.h>
#include <mysql/mysql_version.h>
#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 audio file selector 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 selector. 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(const 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(const 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 (!score && !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_selector_info(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(const 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;
        const 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->selector_info, info, MMD_INFO_SIZE - 1);
        mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
}

static void update_audio_file_server_handler(char *name)
{
        char *info;
        info = get_selector_info(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_selector_info(void)
{
        char *name = get_current_audio_file();
        char *info;

        if (!name)
                return;
        info = get_selector_info(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_selector_info();
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_selector_info();
        }
        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_selector_info();
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->selector_info, "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;
        int ret;

        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;
        conf.mysql_database_arg = para_strdup((argc < 1)?
                "paraslash" : argv[1]);
        query = make_message("create database %s", conf.mysql_database_arg);
        ret = real_query(query);
        free(query);
        if (ret < 0)
                goto out;
        /* reconnect with database just created */
        mysql_close(mysql_ptr);
        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",
                conf.mysql_database_arg);
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 audio file selector
 *
 * Check the command line options and initialize all function pointers of \a db.
 * Connect to the mysql server and initialize the info string.
 *
 * \sa struct audio_file_selector, misc_meta_data::selector_info,
 * 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
                         */
}