+/*
+ * 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.c para_server's mysql-based database tool */
+
+/** \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 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 dbtool, misc_meta_data::dbinfo, dopey.c
+ */
+int mysql_dbtool_init(struct dbtool *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
+ */
+}
projects / paraslash.git / commitdiff
