Merge branch 'master' into my-osx
[paraslash.git] / mysql_selector.c
1 /*
2 * Copyright (C) 1999-2006 Andre Noll <maan@systemlinux.org>
3 *
4 * This program is free software; you can redistribute it and/or modify
5 * it under the terms of the GNU General Public License as published by
6 * the Free Software Foundation; either version 2 of the License, or
7 * (at your option) any later version.
8 *
9 * This program is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 * GNU General Public License for more details.
13 *
14 * You should have received a copy of the GNU General Public License
15 * along with this program; if not, write to the Free Software
16 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111, USA.
17 */
18
19 /** \file mysql_selector.c para_server's mysql-based audio file selector */
20
21 /** \cond some internal constants */
22 #define MEDIUM_BLOB_SIZE 16777220 /* (2**24 + 4) */
23 #define BLOB_SIZE 65539 /* (2**16 + 3) */
24 /** \endcond */
25 #include "server.cmdline.h"
26 #include "server.h"
27 #include "afs.h"
28 #include "db.h"
29 #include <mysql/mysql.h>
30 #include <mysql/mysql_version.h>
31 #include "error.h"
32 #include "net.h"
33 #include "string.h"
34
35 /** pointer to the shared memory area */
36 extern struct misc_meta_data *mmd;
37
38 static void *mysql_ptr = NULL;
39
40 static int com_cam(int, int, char **);
41 static int com_cdb(int, int, char **);
42 static int com_cs(int, int, char **);
43 static int com_da(int, int, char **);
44 static int com_hist(int, int, char **);
45 static int com_info(int, int, char **);
46 static int com_laa(int, int, char **);
47 static int com_last(int, int, char **);
48 static int com_ls(int, int, char **);
49 static int com_mbox(int, int, char **);
50 static int com_mv(int, int, char **);
51 static int com_na(int, int, char **);
52 static int com_pic(int, int, char **);
53 static int com_picch(int, int, char **);
54 static int com_picdel(int, int, char **);
55 static int com_piclist(int, int, char **);
56 static int com_ps(int, int, char **);
57 static int com_rm_ne(int, int, char **);
58 static int com_sa(int, int, char **);
59 static int com_set(int, int, char **);
60 static int com_sl(int, int, char **);
61 static int com_stradd_picadd(int, int, char **);
62 static int com_streams(int, int, char **);
63 static int com_strdel(int, int, char **);
64 static int com_strq(int, int, char **);
65 static int com_summary(int, int, char **);
66 static int com_upd(int, int, char **);
67 static int com_us(int, int, char **);
68 static int com_verb(int, int, char **);
69 static int com_vrfy(int, int, char **);
70
71 static struct server_command cmds[] = {
72 {
73 .name = "cam",
74 .handler = com_cam,
75 .perms = DB_READ|DB_WRITE,
76 .description = "copy all metadata",
77 .synopsis = "cam source dest1 [dest2 ...]",
78 .help =
79
80 "Copy attributes and other meta data from source file to destination\n"
81 "file(s). Useful for files that have been renamed.\n"
82
83 },
84 {
85 .name = "cdb",
86 .handler = com_cdb,
87 .perms = DB_READ|DB_WRITE,
88 .description = "create database",
89 .synopsis = "cdb [name]",
90 .help =
91
92 "\tCreate database name containing the initial columns for basic\n"
93 "\tinteroperation with server. This command has to be used only once\n"
94 "\twhen you use the mysql audio file selector for the very first time.\n"
95 "\n"
96 "\tThe optional name defaults to 'paraslash' if not given.\n"
97
98 },
99 {
100 .name = "clean",
101 .handler = com_vrfy,
102 .perms = DB_READ | DB_WRITE,
103 .description = "nuke invalid entries in database",
104 .synopsis = "clean",
105 .help =
106
107 "If the vrfy command shows you any invalid entries in your database,\n"
108 "you can get rid of them with clean. Always run 'upd' and 'vrfy'\n"
109 "before running this command. Use with caution!\n"
110
111 },
112 {
113 .name = "cs",
114 .handler = com_cs,
115 .perms = AFS_WRITE | DB_READ | DB_WRITE,
116 .description = "change stream",
117 .synopsis = "cs [s]",
118 .help =
119
120 "Selects stream s or prints current stream when s was not given.\n"
121
122 },
123 {
124 .name = "csp",
125 .handler = com_cs,
126 .perms = AFS_WRITE | DB_READ,
127 .description = "change stream and play",
128 .synopsis = "csp s",
129 .help =
130
131 "Select stream s and start playing. If this results in a stream-change,\n"
132 "skip rest of current audio file.\n"
133
134 },
135 {
136 .name = "da",
137 .handler = com_da,
138 .perms = DB_READ | DB_WRITE,
139 .description = "drop attribute from database",
140 .synopsis = "da att",
141 .help =
142
143 "Use with caution. All info on attribute att will be lost.\n"
144
145 },
146 {
147 .name = "hist",
148 .handler = com_hist,
149 .perms = DB_READ,
150 .description = "print history",
151 .synopsis = "hist",
152 .help =
153
154 "Print list of all audio files together with number of days since each\n"
155 "file was last played.\n"
156
157 },
158 {
159 .name = "info",
160 .handler = com_info,
161 .perms = DB_READ,
162 .description = "print database info",
163 .synopsis = "info [af]",
164 .help =
165
166 "print database informations for audio file af. Current audio file is\n"
167 "used if af is not given.\n"
168
169 },
170 {
171 .name = "la",
172 .handler = com_info,
173 .perms = DB_READ,
174 .description = "list attributes",
175 .synopsis = "la [af]",
176 .help =
177
178 "List attributes of audio file af or of current audio file when invoked\n"
179 "without arguments.\n"
180
181 },
182 {
183 .name = "laa",
184 .handler = com_laa,
185 .perms = DB_READ,
186 .description = "list available attributes",
187 .synopsis = "laa",
188 .help =
189
190 "What should I say more?\n"
191
192 },
193 {
194 .name = "last",
195 .handler = com_last,
196 .perms = DB_READ,
197 .description = "print list of audio files, ordered by lastplayed time",
198 .synopsis = "last [n]",
199 .help =
200
201 "The optional number n defaults to 10 if not specified.\n"
202
203 },
204 {
205 .name = "ls",
206 .handler = com_ls,
207 .perms = DB_READ,
208 .description = "list all audio files that match a LIKE pattern",
209 .synopsis = "ls [pattern]",
210 .help =
211
212 "\tIf pattern was not given, print list of all audio files known\n"
213 "\tto the mysql selector. See the documentation of mysql\n"
214 "\tfor the definition of LIKE patterns.\n"
215
216 },
217 {
218 .name = "mbox",
219 .handler = com_mbox,
220 .perms = DB_READ,
221 .description = "dump audio file list in mbox format",
222 .synopsis = "mbox [p]",
223 .help =
224
225 "\tDump list of audio files in mbox format (email) to stdout. If\n"
226 "\tthe optional pattern p is given, only those audio files,\n"
227 "\twhose basename match p are going to be included. Otherwise,\n"
228 "\tall files are selected.\n"
229 "\n"
230 "EXAMPLE\n"
231 "\tThe mbox command can be used together with your favorite\n"
232 "\tmailer (this example uses mutt) for browsing the audio file\n"
233 "\tcollection:\n"
234 "\n"
235 "\t\tpara_client mbox > ~/para_mbox\n"
236 "\n"
237 "\t\tmutt -F ~/.muttrc.para -f ~/para_mbox\n"
238 "\n"
239 "\tFor playlists, you can use mutt's powerful pattern matching\n"
240 "\tlanguage to select files. If you like to tag all files\n"
241 "\tcontaining the pattern 'foo', type 'T', then '~s foo'.\n"
242 "\n"
243 "\tWhen ready with the list, type ';|' (i.e., hit the semicolon\n"
244 "\tkey to apply the next mutt command to all tagged messages,\n"
245 "\tthen the pipe key) to pipe the selected \"mails\" to a\n"
246 "\tsuitable script which adds a paraslash stream where exactly\n"
247 "\tthese files are admissable or does whatever thou wilt.\n"
248
249 },
250 {
251 .name = "mv",
252 .handler = com_mv,
253 .perms = DB_READ | DB_WRITE,
254 .description = "rename entry in database",
255 .synopsis = "mv oldname newname",
256 .help =
257
258 "Rename oldname to newname. This updates the data table to reflect the\n"
259 "new name. All internal data (numplayed, lastplayed, picid,..) is kept.\n"
260 "If newname is a full path, the dir table is updated as well.\n"
261
262 },
263 {
264 .name = "na",
265 .handler = com_na,
266 .perms = DB_READ | DB_WRITE,
267 .description = "add new attribute to database",
268 .synopsis = "na att",
269 .help =
270
271 "This adds a column named att to your mysql database. att should only\n"
272 "contain letters and numbers, in paricular, '+' and '-' are not allowed.\n"
273
274 },
275 {
276 .name = "ne",
277 .handler = com_rm_ne,
278 .perms = DB_READ | DB_WRITE,
279 .description = "add new database entries",
280 .synopsis = "ne file1 [file2 [...]]",
281 .help =
282
283 "Add the given filename(s) to the database, where file1,... must\n"
284 "be full path names. This command might be much faster than 'upd'\n"
285 "if the number of given files is small.\n"
286
287 },
288 {
289 .name = "ns",
290 .handler = com_ps,
291 .perms = AFS_WRITE | DB_READ | DB_WRITE,
292 .description = "change to next stream",
293 .synopsis = "ns",
294 .help =
295
296 "Cycle forwards through stream list.\n"
297
298 },
299 {
300 .name = "pic",
301 .handler = com_pic,
302 .perms = DB_READ,
303 .description = "get picture by name or by identifier",
304 .synopsis = "pic [name]",
305 .help =
306
307 "\tDump jpg image that is associated to given audio file (current\n"
308 "\taudio file if not specified) to stdout. If name starts with\n"
309 "\t'#' it is interpreted as an identifier instead and the picture\n"
310 "\thaving that identifier is dumped to stdout.\n"
311 "\n"
312 "EXAMPLE\n"
313 "\n"
314 "\tpara_client pic '#123' > pic123.jpg\n"
315
316 },
317 {
318 .name = "picadd",
319 .handler = com_stradd_picadd,
320 .perms = DB_READ | DB_WRITE,
321 .description = "add picture to database",
322 .synopsis = "picadd [picname]",
323 .help =
324
325 "\tRead jpeg file from stdin and store it as picname in database.\n"
326 "\n"
327 "EXAMPLE\n"
328 "\n"
329 "\tpara_client picadd foo.jpg < foo.jpg\n"
330
331 },
332 {
333 .name = "picass",
334 .handler = com_set,
335 .perms = DB_READ | DB_WRITE,
336 .description = "associate a picture to file(s)",
337 .synopsis = "picass pic_id file1 [file2...]",
338 .help =
339
340 "Associate the picture given by pic_id to all given files.\n"
341
342 },
343 {
344 .name = "picch",
345 .handler = com_picch,
346 .perms = DB_READ | DB_WRITE,
347 .description = "change name of picture",
348 .synopsis = "picch id new_name",
349 .help =
350
351 "Asign new_name to picture with identifier id.\n"
352
353 },
354 {
355 .name = "picdel",
356 .handler = com_picdel,
357 .perms = DB_READ | DB_WRITE,
358 .description = "delete picture from database",
359 .synopsis = "picdel id1 [id2...]",
360 .help =
361
362 "Delete each given picture from database.\n"
363
364 },
365 {
366 .name = "piclist",
367 .handler = com_piclist,
368 .perms = DB_READ,
369 .description = "print list of pictures",
370 .synopsis = "piclist",
371 .help =
372
373 "Print id, name and length of each picture contained in the database.\n"
374
375 },
376 {
377 .name = "ps",
378 .handler = com_ps,
379 .perms = AFS_WRITE | DB_READ | DB_WRITE,
380 .description = "change to previous stream",
381 .synopsis = "ps",
382 .help =
383
384 "Cycle backwards through stream list.\n"
385
386 },
387 {
388 .name = "rm",
389 .handler = com_rm_ne,
390 .perms = DB_READ | DB_WRITE,
391 .description = "remove entries from database",
392 .synopsis = "rm name1 [name2 [...]]",
393 .help =
394
395 "Remove name1, name2, ... from the data table. Use with caution\n"
396
397 },
398 {
399 .name = "sa",
400 .handler = com_sa,
401 .perms = DB_READ | DB_WRITE,
402 .description = "set/unset attributes",
403 .synopsis = "sa at1<'+' | '-'> [at2<'+' | '-'> ] [af1 ...]",
404 //.synopsis = "foo",
405 .help =
406
407 "Set ('+') or unset ('-') attribute at1, at2 etc. for given list of\n"
408 "audio files. If no audio files were given the current audio file is\n"
409 "used. Example:\n"
410 "\n"
411 "sa rock+ punk+ classic- LZ__Waldsterben.mp3\n"
412 "\n"
413 "sets the 'rock' and the 'punk' attribute but unsets the 'classic'\n"
414 "attribute.\n"
415
416 },
417 {
418 .name = "skip",
419 .handler = com_sl,
420 .perms = DB_READ | DB_WRITE,
421 .description = "skip subsequent audio files(s)",
422 .synopsis = "skip n [s]",
423 .help =
424
425 "Skip the next n audio files of stream s. This is equivalent to the\n"
426 "command 'sl n s', followed by 'us name' for each name the output of sl.\n"
427
428 },
429 {
430 .name = "sl",
431 .handler = com_sl,
432 .perms = DB_READ,
433 .description = "print score list",
434 .synopsis = "sl n [s]",
435 .help =
436
437 "Print sorted list of maximal n lines. Each line is an admissible entry\n"
438 "with respect to stream s. The list is sorted by score-value which is\n"
439 "given by the definition of s. If s is not given, the current stream\n"
440 "is used. Example:\n"
441 "\n"
442 " sl 1\n"
443 "\n"
444 "shows you the audio file the server would select right now.\n"
445
446 },
447 {
448 .name = "snp",
449 .handler = com_set,
450 .perms = DB_READ | DB_WRITE,
451 .description = "set numplayed",
452 .synopsis = "snp number af1 [af2 ...]",
453 .help =
454
455 "Update the numplayed field in the data table for all given audio files.\n"
456
457 },
458 {
459 .name = "stradd",
460 .handler = com_stradd_picadd,
461 .perms = DB_READ | DB_WRITE,
462 .description = "add stream",
463 .synopsis = "stradd s",
464 .help =
465
466 "Add stream s to the list of available streams. The stream definition\n"
467 "for s is read from stdin and is then sent to para_server. Example:\n"
468 "\n"
469 " echo 'deny: NAME_LIKE(%Madonna%)' | para_client stradd no_madonna\n"
470 "\n"
471 "adds the new stream 'no_madonna' to the list of available streams. A given\n"
472 "audio file is admissible for this stream iff its basename does not contain the\n"
473 "string 'Madonna'.\n"
474
475
476 },
477 {
478 .name = "strdel",
479 .handler = com_strdel,
480 .perms = DB_READ | DB_WRITE,
481 .description = "delete stream",
482 .synopsis = "strdel s",
483 .help =
484
485 "Remove stream s from database.\n"
486
487 },
488 {
489 .name = "streams",
490 .handler = com_streams,
491 .perms = DB_READ,
492 .description = "list streams",
493 .synopsis = "streams",
494 .help =
495
496 "Print list of available streams. Use 'cs' to switch to any of these.\n"
497
498 },
499 {
500 .name = "strq",
501 .handler = com_strq,
502 .perms = DB_READ,
503 .description = "query stream definition",
504 .synopsis = "strq [s]",
505 .help =
506
507 "Print definition of stream s to stdout. Use current stream if s was\n"
508 "not given.\n"
509
510 },
511 {
512 .name = "summary",
513 .handler = com_summary,
514 .perms = DB_READ,
515 .description = "list attributes",
516 .synopsis = "summary",
517 .help =
518
519 "\tPrint a list of attributes together with number of audio\n"
520 "\tfiles having that attribute set.\n"
521
522 },
523 {
524 .name = "upd",
525 .handler = com_upd,
526 .perms = DB_READ | DB_WRITE,
527 .description = "update database",
528 .synopsis = "upd",
529 .help =
530
531 "This command uses the --audio_file_dir option of para_server to locate\n"
532 "your audio files. New files are then added to the mysql database. Use\n"
533 "this command if you got new files or if you have moved some files\n"
534 "around.\n"
535
536 },
537 {
538 .name = "us",
539 .handler = com_us,
540 .perms = DB_READ | DB_WRITE,
541 .description = "update lastplayed time",
542 .synopsis = "us name",
543 .help =
544
545 "Update lastplayed time without actually playing the thing.\n"
546
547 },
548 {
549 .name = "verb",
550 .handler = com_verb,
551 .perms = DB_READ | DB_WRITE,
552 .description = "send verbatim sql query",
553 .synopsis = "verb cmd",
554 .help =
555
556 "Send cmd to mysql server. For expert/debugging only. Note that cmd\n"
557 "usually must be escaped. Use only if you know what you are doing!\n"
558
559 },
560 {
561 .name = "vrfy",
562 .handler = com_vrfy,
563 .perms = DB_READ,
564 .description = "list invalid entries in database",
565 .synopsis = "vrfy",
566 .help =
567
568 "Show what clean would delete. Run 'upd' before this command to make\n"
569 "sure your database is up to date.\n"
570
571 },
572 {
573 .name = NULL,
574 }
575 };
576
577 static struct para_macro macro_list[] = {
578 { .name = "IS_N_SET",
579 .replacement = "(data.%s != '1')"
580 }, {
581 .name = "IS_SET",
582 .replacement = "(data.%s = '1')"
583 }, {
584 .name = "PICID",
585 .replacement = "%sdata.Pic_Id"
586 }, {
587 .name = "NAME_LIKE",
588 .replacement = "(data.name like '%s')"
589 }, {
590 .name = "LASTPLAYED",
591 .replacement = "%sFLOOR((UNIX_TIMESTAMP(now())"
592 "-UNIX_TIMESTAMP(data.Lastplayed))/60)"
593 }, {
594 .name = "NUMPLAYED",
595 .replacement = "%sdata.Numplayed"
596 }, {
597 .name = NULL,
598 }
599 };
600
601 static int real_query(const char *query)
602 {
603 if (!mysql_ptr)
604 return -E_NOTCONN;
605 PARA_DEBUG_LOG("%s\n", query);
606 if (mysql_real_query(mysql_ptr, query, strlen(query))) {
607 PARA_ERROR_LOG("real_query error (%s)\n",
608 mysql_error(mysql_ptr));
609 return -E_QFAILED;
610 }
611 return 1;
612 }
613
614 /*
615 * Use open connection given by mysql_ptr to query server. Returns a
616 * result pointer on succes and NULL on errors
617 */
618 static struct MYSQL_RES *get_result(const char *query)
619 {
620 void *result;
621
622 if (real_query(query) < 0)
623 return NULL;
624 result = mysql_store_result(mysql_ptr);
625 if (!result)
626 PARA_ERROR_LOG("%s", "store_result error\n");
627 return result;
628 }
629 /*
630 * write input from fd to dynamically allocated char array,
631 * but maximal max_size byte. Return size.
632 */
633 static int fd2buf(int fd, char **buf_ptr, size_t max_size)
634 {
635 const size_t chunk_size = 1024;
636 size_t size = 2048;
637 char *buf = para_malloc(size * sizeof(char)), *p = buf;
638 int ret;
639
640 while ((ret = recv_bin_buffer(fd, p, chunk_size)) > 0) {
641 p += ret;
642 if ((p - buf) + chunk_size >= size) {
643 char *tmp;
644
645 size *= 2;
646 if (size > max_size) {
647 ret = -E_TOOBIG;
648 goto out;
649 }
650 tmp = para_realloc(buf, size);
651 p = (p - buf) + tmp;
652 buf = tmp;
653 }
654 }
655 if (ret < 0)
656 goto out;
657 *buf_ptr = buf;
658 ret = p - buf;
659 out:
660 if (ret < 0 && buf)
661 free(buf);
662 return ret;
663 }
664
665 static char *escape_blob(const char* old, int size)
666 {
667 char *new;
668
669 if (!mysql_ptr || size < 0)
670 return NULL;
671 new = para_malloc(2 * size * sizeof(char) + 1);
672 mysql_real_escape_string(mysql_ptr, new, old, size);
673 return new;
674 }
675
676 static char *escape_str(const char* old)
677 {
678 return escape_blob(old, strlen(old));
679 }
680
681 static char *escaped_basename(const char *name)
682 {
683 char *esc, *bn = para_basename(name);
684
685 if (!bn)
686 return NULL;
687 esc = escape_str(bn);
688 free(bn);
689 return esc;
690 }
691
692 /*
693 * new attribute
694 */
695 static int com_na(__a_unused int fd, int argc, char *argv[])
696 {
697 char *q, *tmp;
698 int ret;
699
700 if (argc < 2)
701 return -E_MYSQL_SYNTAX;
702 tmp = escape_str(argv[1]);
703 if (!tmp)
704 return -E_ESCAPE;
705 q = make_message("alter table data add %s char(1) "
706 "not null default 0", tmp);
707 free(tmp);
708 ret = real_query(q);
709 free(q);
710 return ret;
711 }
712
713 /*
714 * delete attribute
715 */
716 static int com_da(__a_unused int fd, int argc, char *argv[])
717 {
718 char *q, *tmp;
719 int ret;
720
721 if (argc < 2)
722 return -E_MYSQL_SYNTAX;
723 tmp = escape_str(argv[1]);
724 if (!tmp)
725 return -E_ESCAPE;
726 q = make_message("alter table data drop %s", tmp);
727 free(tmp);
728 ret = real_query(q);
729 free(q);
730 return ret;
731 }
732
733 /* stradd/pic_add */
734 static int com_stradd_picadd(int fd, int argc, char *argv[])
735 {
736 char *blob = NULL, *esc_blob = NULL, *q = NULL, *tmp = NULL;
737 const char *fmt, *del_fmt;
738 int ret, stradd = strcmp(argv[0], "picadd");
739 size_t size;
740
741 if (argc < 2)
742 return -E_MYSQL_SYNTAX;
743 if (strlen(argv[1]) >= MAXLINE - 1)
744 return -E_NAMETOOLONG;
745 if (!mysql_ptr)
746 return -E_NOTCONN;
747 if (stradd) {
748 size = BLOB_SIZE;
749 fmt = "insert into streams (name, def) values ('%s','%s')";
750 del_fmt="delete from streams where name='%s'";
751 } else {
752 size = MEDIUM_BLOB_SIZE;
753 fmt = "insert into pics (name, pic) values ('%s','%s')";
754 del_fmt="delete from pics where pic='%s'";
755 }
756 tmp = escape_str(argv[1]);
757 if (!tmp)
758 return -E_ESCAPE;
759 q = make_message(del_fmt, tmp);
760 free(tmp);
761 ret = real_query(q);
762 free(q);
763 if (ret < 0)
764 return ret;
765 if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0))
766 return ret;
767 if ((ret = fd2buf(fd, &blob, size)) < 0)
768 return ret;
769 size = ret;
770 if (stradd)
771 blob[size] = '\0';
772 ret = -E_ESCAPE;
773 esc_blob = escape_blob(blob, size);
774 if (!esc_blob)
775 goto out;
776 tmp = escape_str(argv[1]);
777 if (!tmp)
778 goto out;
779 q = make_message(fmt, tmp, esc_blob);
780 ret = real_query(q);
781 out:
782 free(blob);
783 free(esc_blob);
784 free(tmp);
785 free(q);
786 return ret;
787 }
788
789 /*
790 * print results to fd
791 */
792 static int print_results(int fd, void *result,
793 unsigned int top, unsigned int left,
794 unsigned int bottom, unsigned int right)
795 {
796 unsigned int i,j;
797 int ret;
798 MYSQL_ROW row;
799
800 for (i = top; i <= bottom; i++) {
801 row = mysql_fetch_row(result);
802 if (!row || !row[0])
803 return -E_NOROW;
804 for (j = left; j <= right; j++) {
805 ret = send_va_buffer(fd, j == left? "%s" : "\t%s",
806 row[j]? row[j] : "NULL");
807 if (ret < 0)
808 return ret;
809 }
810 ret = send_buffer(fd, "\n");
811 if (ret < 0)
812 return ret;
813 }
814 return 0;
815 }
816
817 /*
818 * verbatim
819 */
820 static int com_verb(int fd, int argc, char *argv[])
821 {
822 void *result = NULL;
823 int ret;
824 unsigned int num_rows, num_fields;
825 char *tmp;
826
827 if (argc < 2)
828 return -E_MYSQL_SYNTAX;
829 tmp = escape_str(argv[1]);
830 if (!tmp)
831 return -E_ESCAPE;
832 result = get_result(tmp);
833 free(tmp);
834 if (!result)
835 /* return success, because it's ok to have no results */
836 return 1;
837 num_fields = mysql_field_count(mysql_ptr);
838 num_rows = mysql_num_rows(result);
839 ret = 1;
840 if (num_fields && num_rows)
841 ret = print_results(fd, result, 0, 0, num_rows - 1,
842 num_fields - 1);
843 mysql_free_result(result);
844 return ret;
845 }
846
847 /* returns NULL on errors or if there are no atts defined yet */
848 static void *get_all_attributes(void)
849 {
850 void *result = get_result("desc data");
851 unsigned int num_rows;
852
853 if (!result)
854 return NULL;
855 num_rows = mysql_num_rows(result);
856 if (num_rows < 5) {
857 mysql_free_result(result);
858 return NULL;
859 }
860 mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */
861 return result;
862 }
863
864 /*
865 * list all attributes
866 */
867 static int com_laa(int fd, int argc, __a_unused char *argv[])
868 {
869 void *result;
870 int ret;
871
872 if (argc != 1)
873 return -E_MYSQL_SYNTAX;
874 result = get_all_attributes();
875 if (!result)
876 return -E_NOATTS;
877 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0);
878 mysql_free_result(result);
879 return ret;
880 }
881
882 /*
883 * history
884 */
885 static int com_hist(int fd, int argc, char *argv[]) {
886 int ret;
887 void *result = NULL;
888 char *q, *atts;
889 unsigned int num_rows;
890
891 if (argc > 3)
892 return -E_MYSQL_SYNTAX;
893 if (argc > 1) {
894 char *tmp = escape_str(argv[1]);
895 if (!tmp)
896 return -E_ESCAPE;
897 atts = make_message("where %s = '1'", tmp);
898 free(tmp);
899 } else
900 atts = para_strdup(NULL);
901
902 q = make_message("select name, to_days(now()) - to_days(lastplayed) from "
903 "data %s order by lastplayed", atts);
904 free(atts);
905 result = get_result(q);
906 free(q);
907 if (!result)
908 return -E_NORESULT;
909 num_rows = mysql_num_rows(result);
910 ret = 1;
911 if (num_rows)
912 ret = print_results(fd, result, 0, 0, num_rows - 1, 1);
913 mysql_free_result(result);
914 return ret;
915 }
916
917 /*
918 * get last num audio files
919 */
920 static int com_last(int fd, int argc, char *argv[])
921 {
922 void *result = NULL;
923 char *q;
924 int num, ret;
925
926 if (argc < 2)
927 num = 10;
928 else
929 num = atoi(argv[1]);
930 if (!num)
931 return -E_MYSQL_SYNTAX;
932 q = make_message("select name from data order by lastplayed desc "
933 "limit %u", num);
934 result = get_result(q);
935 free(q);
936 if (!result)
937 return -E_NORESULT;
938 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0);
939 mysql_free_result(result);
940 return ret;
941 }
942
943 static int com_mbox(int fd, int argc, char *argv[])
944 {
945 void *result;
946 MYSQL_ROW row;
947 int ret;
948 unsigned int num_rows, num_fields;
949 char *query = para_strdup("select concat('From foo@localhost ', "
950 "date_format(Lastplayed, '%a %b %e %T %Y'), "
951 "'\nReceived: from\nTo: bar\n");
952
953 ret = -E_NOATTS;
954 result = get_all_attributes();
955 if (!result)
956 goto out;
957 ret = -E_NOROW;
958 while ((row = mysql_fetch_row(result))) {
959 char *tmp;
960
961 if (!row[0])
962 goto out;
963 tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query,
964 row[0], row[0]);
965 free(query);
966 query = tmp;
967 }
968 query = para_strcat(query,
969 "From: a\n"
970 "Subject: "
971 "', name, '"
972 "\n\n\n"
973 "') from data"
974 );
975 if (argc >= 2) {
976 char *esc = escape_str(argv[1]), *tmp;
977 ret = -E_ESCAPE;
978 if (!esc)
979 goto out;
980 tmp = make_message("%s where name LIKE '%s'", query, esc);
981 free(esc);
982 free(query);
983 query = tmp;
984 }
985 mysql_free_result(result);
986 ret = -E_NORESULT;
987 result = get_result(query);
988 if (!result)
989 goto out;
990 ret = -E_EMPTY_RESULT;
991 num_fields = mysql_field_count(mysql_ptr);
992 num_rows = mysql_num_rows(result);
993 if (!num_fields || !num_rows)
994 goto out;
995 ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1);
996 out:
997 free(query);
998 if (result)
999 mysql_free_result(result);
1000 return ret;
1001 }
1002
1003 /* get attributes by name. If verbose is not 0, get_a writes a string
1004 * into atts of the form 'att1="0",att2="1"', which is used in com_cam
1005 * for contructing a mysql update query.
1006 * never returns NULL in *NON VERBOSE* mode
1007 */
1008 static char *get_atts(char *name, int verbose)
1009 {
1010 char *atts = NULL, *buf, *ebn;
1011 void *result = NULL, *result2 = NULL;
1012 MYSQL_ROW row, row2;
1013 int i, ret;
1014 unsigned int num_fields;
1015
1016 ret = -E_NOATTS;
1017 result2 = get_all_attributes();
1018 if (!result2)
1019 goto out;
1020 ret = -E_ESCAPE;
1021 if (!(ebn = escaped_basename(name)))
1022 goto out;
1023 buf = make_message("select * from data where name='%s'", ebn);
1024 free(ebn);
1025 ret = -E_NORESULT;
1026 result = get_result(buf);
1027 free(buf);
1028 if (!result)
1029 goto out;
1030 ret = -E_EMPTY_RESULT;
1031 num_fields = mysql_num_fields(result);
1032 if (num_fields < 5)
1033 goto out;
1034 mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */
1035 row = mysql_fetch_row(result);
1036 ret = -E_NOROW;
1037 if (!row)
1038 goto out;
1039 for (i = 4; i < num_fields; i++) {
1040 int is_set = row[i] && !strcmp(row[i], "1");
1041 row2 = mysql_fetch_row(result2);
1042 if (!row2 || !row2[0])
1043 goto out;
1044 if (atts && (verbose || is_set))
1045 atts = para_strcat(atts, verbose? "," : " ");
1046 if (is_set || verbose)
1047 atts = para_strcat(atts, row2[0]);
1048 if (verbose)
1049 atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\"");
1050 }
1051 ret = 1;
1052 out:
1053 if (result2)
1054 mysql_free_result(result2);
1055 if (result)
1056 mysql_free_result(result);
1057 if (!atts && !verbose)
1058 atts = para_strdup("(none)");
1059 return atts;
1060 }
1061
1062 /* never returns NULL in verbose mode */
1063 static char *get_meta(char *name, int verbose)
1064 {
1065 MYSQL_ROW row;
1066 void *result = NULL;
1067 char *ebn, *q, *ret = NULL;
1068 const char *verbose_fmt =
1069 "select concat('lastplayed: ', "
1070 "(to_days(now()) - to_days(lastplayed)),"
1071 "' day(s). numplayed: ', numplayed, "
1072 "', pic: ', pic_id) "
1073 "from data where name = '%s'";
1074 /* is that really needed? */
1075 const char *fmt = "select concat('lastplayed=\\'', lastplayed, "
1076 "'\\', numplayed=\\'', numplayed, "
1077 "'\\', pic_id=\\'', pic_id, '\\'') "
1078 "from data where name = '%s'";
1079
1080 if (!(ebn = escaped_basename(name)))
1081 goto out;
1082 q = make_message(verbose? verbose_fmt : fmt, ebn);
1083 free(ebn);
1084 result = get_result(q);
1085 free(q);
1086 if (!result)
1087 goto out;
1088 row = mysql_fetch_row(result);
1089 if (!row || !row[0])
1090 goto out;
1091 ret = para_strdup(row[0]);
1092 out:
1093 if (result)
1094 mysql_free_result(result);
1095 if (!ret && verbose)
1096 ret = para_strdup("(not yet played)");
1097 return ret;
1098 }
1099
1100 static char *get_dir(char *name)
1101 {
1102 char *ret = NULL, *q, *ebn;
1103 void *result;
1104 MYSQL_ROW row;
1105
1106 if (!(ebn = escaped_basename(name)))
1107 return NULL;
1108 q = make_message("select dir from dir where name = '%s'", ebn);
1109 free(ebn);
1110 result = get_result(q);
1111 free(q);
1112 if (!result)
1113 return NULL;
1114 row = mysql_fetch_row(result);
1115 if (row && row[0])
1116 ret = para_strdup(row[0]);
1117 mysql_free_result(result);
1118 return ret;
1119 }
1120
1121 /* never returns NULL */
1122 static char *get_current_stream(void)
1123 {
1124 char *ret;
1125 MYSQL_ROW row;
1126 void *result = get_result("select def from streams where "
1127 "name = 'current_stream'");
1128
1129 if (!result)
1130 goto err_out;
1131 row = mysql_fetch_row(result);
1132 if (!row || !row[0])
1133 goto err_out;
1134 ret = para_strdup(row[0]);
1135 mysql_free_result(result);
1136 return ret;
1137 err_out:
1138 if (result)
1139 mysql_free_result(result);
1140 return para_strdup("(none)");
1141 }
1142
1143 /*
1144 * Read stream definition of stream streamname and construct mysql
1145 * query. Return NULL on errors. If streamname is NULL, use current
1146 * stream. If that is also NULL, use query that selects everything.
1147 * If filename is NULL, query will list everything, otherwise only
1148 * the score of given file.
1149 */
1150 static char *get_query(char *streamname, char *filename, int with_path)
1151 {
1152 char *accept_opts = NULL, *deny_opts = NULL, *score = NULL;
1153 char *where_clause, *order, *query;
1154 char command[255] = ""; /* buffer for sscanf */
1155 void *result;
1156 MYSQL_ROW row;
1157 char *end, *tmp;
1158 char *select_clause = NULL;
1159 if (!streamname)
1160 tmp = get_current_stream();
1161 else {
1162 tmp = escape_str(streamname);
1163 if (!tmp)
1164 return NULL;
1165 }
1166 if (!strcmp(tmp, "(none)")) {
1167 free(tmp);
1168 if (filename) {
1169 char *ret, *ebn = escaped_basename(filename);
1170 if (!ebn)
1171 return NULL;
1172 ret = make_message("select to_days(now()) - "
1173 "to_days(lastplayed) from data "
1174 "where name = '%s'", ebn);
1175 free(ebn);
1176 return ret;
1177 }
1178 if (with_path)
1179 return make_message(
1180 "select concat(dir.dir, '/', dir.name) "
1181 "from data, dir where dir.name = data.name "
1182 "order by data.lastplayed"
1183 );
1184 return make_message(
1185 "select name from data where name is not NULL "
1186 "order by lastplayed"
1187 );
1188 }
1189 free(tmp);
1190 query = make_message("select def from streams where name = '%s'",
1191 streamname);
1192 result = get_result(query);
1193 free(query);
1194 query = NULL;
1195 if (!result)
1196 goto out;
1197 row = mysql_fetch_row(result);
1198 if (!row || !row[0])
1199 goto out;
1200 end = row[0];
1201 while (*end) {
1202 int n;
1203 char *arg, *line = end;
1204
1205 if (!(end = strchr(line, '\n')))
1206 break;
1207 *end = '\0';
1208 end++;
1209 if (sscanf(line, "%200s%n", command, &n) < 1)
1210 continue;
1211 arg = line + n;
1212 if (!strcmp(command, "accept:")) {
1213 char *tmp2 = s_a_r_list(macro_list, arg);
1214 if (accept_opts)
1215 accept_opts = para_strcat(
1216 accept_opts, " or ");
1217 accept_opts = para_strcat(accept_opts, tmp2);
1218 free(tmp2);
1219 continue;
1220 }
1221 if (!strcmp(command, "deny:")) {
1222 char *tmp2 = s_a_r_list(macro_list, arg);
1223 if (deny_opts)
1224 deny_opts = para_strcat(deny_opts, " or ");
1225 deny_opts = para_strcat(deny_opts, tmp2);
1226 free(tmp2);
1227 continue;
1228 }
1229 if (!score && !strcmp(command, "score:"))
1230 score = s_a_r_list(macro_list, arg);
1231 }
1232 if (!score) {
1233 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
1234 if (!score)
1235 goto out;
1236 }
1237 if (filename) {
1238 char *ebn = escaped_basename(filename);
1239 if (!ebn)
1240 goto out;
1241 select_clause = make_message("select %s from data ", score);
1242 free(score);
1243 where_clause = make_message( "where name = '%s' ", ebn);
1244 free(ebn);
1245 order = para_strdup("");
1246 goto write_query;
1247 }
1248 select_clause = para_strdup(with_path?
1249 "select concat(dir.dir, '/', dir.name) from data, dir "
1250 "where dir.name = data.name "
1251 :
1252 "select name from data where name is not NULL");
1253 order = make_message("order by -(%s)", score);
1254 free(score);
1255 if (accept_opts && deny_opts) {
1256 where_clause = make_message("and ((%s) and not (%s)) ",
1257 accept_opts, deny_opts);
1258 goto write_query;
1259 }
1260 if (accept_opts && !deny_opts) {
1261 where_clause = make_message("and (%s) ", accept_opts);
1262 goto write_query;
1263 }
1264 if (!accept_opts && deny_opts) {
1265 where_clause = make_message("and not (%s) ", deny_opts);
1266 goto write_query;
1267 }
1268 where_clause = para_strdup("");
1269 write_query:
1270 query = make_message("%s %s %s", select_clause, where_clause, order);
1271 free(order);
1272 free(select_clause);
1273 free(where_clause);
1274 out:
1275 if (accept_opts)
1276 free(accept_opts);
1277 if (deny_opts)
1278 free(deny_opts);
1279 if (result)
1280 mysql_free_result(result);
1281 return query;
1282 }
1283
1284
1285
1286 /*
1287 * This is called from server and from some commands. Name must not be NULL
1288 * Never returns NULL.
1289 */
1290 static char *get_selector_info(char *name)
1291 {
1292 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
1293 void *result = NULL;
1294 MYSQL_ROW row = NULL;
1295
1296 if (!name)
1297 return para_strdup("(none)");
1298 stream = get_current_stream();
1299 meta = get_meta(name, 1);
1300 atts = get_atts(name, 0);
1301 dir = get_dir(name);
1302 /* get score */
1303 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
1304 if (!query)
1305 goto write;
1306 result = get_result(query);
1307 free(query);
1308 if (result)
1309 row = mysql_fetch_row(result);
1310 write:
1311 info = make_message("dbinfo1:dir: %s\n"
1312 "dbinfo2:stream: %s, %s, score: %s\n"
1313 "dbinfo3:%s\n",
1314 dir? dir : "(not contained in table)",
1315 stream, meta,
1316 (result && row && row[0])? row[0] : "(no score)",
1317 atts);
1318 if (dir)
1319 free(dir);
1320 if (meta)
1321 free(meta);
1322 if (atts)
1323 free(atts);
1324 if (stream)
1325 free(stream);
1326 if (result)
1327 mysql_free_result(result);
1328 return info;
1329 }
1330
1331
1332 /* might return NULL */
1333 static char *get_current_audio_file(void)
1334 {
1335 char *name;
1336 mmd_lock();
1337 name = para_basename(mmd->filename);
1338 mmd_unlock();
1339 return name;
1340 }
1341
1342
1343 /* print database info */
1344 static int com_info(int fd, int argc, char *argv[])
1345 {
1346 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
1347 int ret, com_la = strcmp(argv[0], "info");
1348
1349 if (argc < 2) {
1350 ret = -E_GET_AUDIO_FILE;
1351 if (!(name = get_current_audio_file()))
1352 goto out;
1353 ret = send_va_buffer(fd, "%s\n", name);
1354 if (ret < 0)
1355 goto out;
1356 } else {
1357 ret = -E_ESCAPE;
1358 if (!(name = escaped_basename(argv[1])))
1359 goto out;
1360 }
1361 meta = get_meta(name, 1);
1362 atts = get_atts(name, 0);
1363 dir = get_dir(name);
1364 if (com_la)
1365 ret = send_va_buffer(fd, "%s\n", atts);
1366 else
1367 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
1368 dir? dir : "(not contained in table)", meta, atts);
1369 out:
1370 free(meta);
1371 free(atts);
1372 free(dir);
1373 free(name);
1374 return ret;
1375 }
1376
1377 static int change_stream(const char *stream)
1378 {
1379 char *query;
1380 int ret;
1381 query = make_message("update streams set def='%s' "
1382 "where name = 'current_stream'", stream);
1383 ret = real_query(query);
1384 free(query);
1385 return ret;
1386 }
1387
1388 static int get_pic_id_by_name(char *name)
1389 {
1390 char *q, *ebn;
1391 void *result = NULL;
1392 long unsigned ret;
1393 MYSQL_ROW row;
1394
1395 if (!(ebn = escaped_basename(name)))
1396 return -E_ESCAPE;
1397 q = make_message("select pic_id from data where name = '%s'", ebn);
1398 free(ebn);
1399 result = get_result(q);
1400 free(q);
1401 if (!result)
1402 return -E_NORESULT;
1403 row = mysql_fetch_row(result);
1404 ret = -E_NOROW;
1405 if (row && row[0])
1406 ret = atol(row[0]);
1407 mysql_free_result(result);
1408 return ret;
1409 }
1410
1411 static int remove_entry(const char *name)
1412 {
1413 char *q, *ebn = escaped_basename(name);
1414 int ret = -E_ESCAPE;
1415
1416 if (!ebn)
1417 goto out;
1418 q = make_message("delete from data where name = '%s'", ebn);
1419 real_query(q); /* ignore errors */
1420 free(q);
1421 q = make_message("delete from dir where name = '%s'", ebn);
1422 real_query(q); /* ignore errors */
1423 free(q);
1424 ret = 1;
1425 out:
1426 free(ebn);
1427 return ret;
1428 }
1429
1430 static int add_entry(const char *name)
1431 {
1432 char *q, *dn, *ebn = NULL, *edn = NULL;
1433 int ret;
1434
1435 if (!name || !*name)
1436 return -E_MYSQL_SYNTAX;
1437 ebn = escaped_basename(name);
1438 if (!ebn)
1439 return -E_ESCAPE;
1440 ret = -E_MYSQL_SYNTAX;
1441 dn = para_dirname(name);
1442 if (!dn)
1443 goto out;
1444 ret = -E_ESCAPE;
1445 edn = escape_str(dn);
1446 free(dn);
1447 if (!edn || !*edn)
1448 goto out;
1449 q = make_message("insert into data (name, pic_id) values "
1450 "('%s', '%s')", ebn, "1");
1451 ret = real_query(q);
1452 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1453 free(q);
1454 if (ret < 0)
1455 goto out;
1456 q = make_message("insert into dir (name, dir) values "
1457 "('%s', '%s')", ebn, edn);
1458 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1459 ret = real_query(q);
1460 free(q);
1461 out:
1462 if (ebn)
1463 free(ebn);
1464 if (edn)
1465 free(edn);
1466 return ret;
1467 }
1468
1469 /*
1470 * remove/add entries
1471 */
1472 static int com_rm_ne(__a_unused int fd, int argc, char *argv[])
1473 {
1474 int ne = !strcmp(argv[0], "ne");
1475 int i, ret;
1476 if (argc < 2)
1477 return -E_MYSQL_SYNTAX;
1478 for (i = 1; i < argc; i++) {
1479 ret = remove_entry(argv[i]);
1480 if (ret < 0)
1481 return ret;
1482 if (!ne)
1483 continue;
1484 ret = add_entry(argv[i]);
1485 if (ret < 0)
1486 return ret;
1487 }
1488 return 1;
1489 }
1490
1491 /*
1492 * mv: rename entry
1493 */
1494 static int com_mv(__a_unused int fd, int argc, char *argv[])
1495 {
1496 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1497 int ret;
1498
1499 if (argc != 3)
1500 return -E_MYSQL_SYNTAX;
1501 ret = -E_ESCAPE;
1502 ebn1 = escaped_basename(argv[1]);
1503 ebn2 = escaped_basename(argv[2]);
1504 if (!ebn1 || !ebn2 | !*ebn1 || !*ebn2)
1505 goto out;
1506 ret = -E_MYSQL_SYNTAX;
1507 if (!strcmp(ebn1, ebn2))
1508 goto out;
1509 remove_entry(argv[2]); /* no need to escape, ignore error */
1510 q = make_message("update data set name = '%s' where name = '%s'",
1511 ebn2, ebn1);
1512 ret = real_query(q);
1513 free(q);
1514 if (ret < 0)
1515 goto out;
1516 ret = -E_AUDIO_FILE;
1517 if (!mysql_affected_rows(mysql_ptr))
1518 goto out;
1519 q = make_message("update dir set name = '%s' where name = '%s'",
1520 ebn2, ebn1);
1521 ret = real_query(q);
1522 free(q);
1523 if (ret < 0)
1524 goto out;
1525 ret = 1;
1526 dn = para_dirname(argv[2]);
1527 if (!dn)
1528 goto out;
1529 ret = -E_ESCAPE;
1530 edn = escape_str(dn);
1531 free(dn);
1532 if (!edn)
1533 goto out;
1534 ret = 1;
1535 if (!*edn)
1536 goto out;
1537 q = make_message("update dir set dir = '%s' where name = '%s'",
1538 edn, ebn2);
1539 ret = real_query(q);
1540 free(q);
1541 out:
1542 free(edn);
1543 free(ebn1);
1544 free(ebn2);
1545 return ret;
1546 }
1547
1548 /*
1549 * picass: associate pic to audio file
1550 * snp: set numplayed
1551 */
1552 static int com_set(__a_unused int fd, int argc, char *argv[])
1553 {
1554 char *q, *ebn;
1555 long unsigned id;
1556 int i, ret;
1557 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1558
1559 if (argc < 3)
1560 return -E_MYSQL_SYNTAX;
1561 id = atol(argv[1]);
1562 for (i = 2; i < argc; i++) {
1563 ebn = escaped_basename(argv[i]);
1564 if (!ebn)
1565 return -E_ESCAPE;
1566 q = make_message("update data set %s = %lu "
1567 "where name = '%s'", field, id, ebn);
1568 free(ebn);
1569 ret = real_query(q);
1570 free(q);
1571 if (ret < 0)
1572 return ret;
1573 }
1574 return 1;
1575 }
1576
1577 /*
1578 * picch: change entry's name in pics table
1579 */
1580 static int com_picch(__a_unused int fd, int argc, char *argv[])
1581 {
1582 int ret;
1583 long unsigned id;
1584 char *q, *tmp;
1585
1586 if (argc != 3)
1587 return -E_MYSQL_SYNTAX;
1588 id = atol(argv[1]);
1589 ret = -E_ESCAPE;
1590 tmp = escape_str(argv[2]);
1591 if (!tmp)
1592 return -E_ESCAPE;
1593 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1594 free(tmp);
1595 ret = real_query(q);
1596 free(q);
1597 return ret;
1598 }
1599
1600 /*
1601 * piclist: print list of pics in db
1602 */
1603 static int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1604 {
1605 void *result = NULL;
1606 MYSQL_ROW row;
1607 unsigned long *length;
1608 int ret;
1609
1610 if (argc != 1)
1611 return -E_MYSQL_SYNTAX;
1612 result = get_result("select id,name,pic from pics order by id");
1613 if (!result)
1614 return -E_NORESULT;
1615 while ((row = mysql_fetch_row(result))) {
1616 length = mysql_fetch_lengths(result);
1617 if (!row || !row[0] || !row[1] || !row[2])
1618 continue;
1619 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1620 if (ret < 0)
1621 goto out;
1622 }
1623 ret = 1;
1624 out:
1625 mysql_free_result(result);
1626 return ret;
1627 }
1628
1629 /*
1630 * picdel: delete picture from database
1631 */
1632 static int com_picdel(int fd, int argc, char *argv[])
1633 {
1634 char *q;
1635 long unsigned id;
1636 my_ulonglong aff;
1637 int i, ret;
1638
1639 if (argc < 2)
1640 return -E_MYSQL_SYNTAX;
1641 for (i = 1; i < argc; i++) {
1642 id = atol(argv[i]);
1643 q = make_message("delete from pics where id = %lu", id);
1644 ret = real_query(q);
1645 free(q);
1646 if (ret < 0)
1647 return ret;
1648 aff = mysql_affected_rows(mysql_ptr);
1649 if (!aff) {
1650 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1651 if (ret < 0)
1652 return ret;
1653 continue;
1654 }
1655 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1656 ret = real_query(q);
1657 free(q);
1658 }
1659 return 1;
1660 }
1661 /*
1662 * pic: get picture by name or by number
1663 */
1664 static int com_pic(int fd, int argc, char *argv[])
1665 {
1666 void *result = NULL;
1667 MYSQL_ROW row;
1668 unsigned long *length, id;
1669 int ret;
1670 char *q, *name = NULL;
1671
1672 if (argc < 2) {
1673 ret = -E_GET_AUDIO_FILE;
1674 name = get_current_audio_file();
1675 } else {
1676 ret = -E_ESCAPE;
1677 name = escaped_basename(argv[1]);
1678 }
1679 if (!name)
1680 return ret;
1681 if (*name == '#')
1682 id = atoi(name + 1);
1683 else
1684 id = get_pic_id_by_name(name);
1685 free(name);
1686 if (id <= 0)
1687 return id;
1688 q = make_message("select pic from pics where id = '%lu'", id);
1689 result = get_result(q);
1690 free(q);
1691 if (!result)
1692 return -E_NORESULT;
1693 row = mysql_fetch_row(result);
1694 ret = -E_NOROW;
1695 if (!row || !row[0])
1696 goto out;
1697 length = mysql_fetch_lengths(result);
1698 ret = send_bin_buffer(fd, row[0], *length);
1699 out:
1700 mysql_free_result(result);
1701 return ret;
1702 }
1703
1704 /* strdel */
1705 static int com_strdel(__a_unused int fd, int argc, char *argv[])
1706 {
1707 char *q, *tmp;
1708 int ret;
1709
1710 if (argc < 2)
1711 return -E_MYSQL_SYNTAX;
1712 tmp = escape_str(argv[1]);
1713 if (!tmp)
1714 return -E_ESCAPE;
1715 q = make_message("delete from streams where name='%s'", tmp);
1716 free(tmp);
1717 ret = real_query(q);
1718 free(q);
1719 return ret;
1720 }
1721
1722 /*
1723 * ls
1724 */
1725 static int com_ls(int fd, int argc, char *argv[])
1726 {
1727 char *q;
1728 void *result;
1729 int ret;
1730 unsigned int num_rows;
1731
1732 if (argc > 2)
1733 return -E_MYSQL_SYNTAX;
1734 if (argc > 1) {
1735 char *tmp = escape_str(argv[1]);
1736 if (!tmp)
1737 return -E_ESCAPE;
1738 q = make_message("select name from data where name like '%s'",
1739 tmp);
1740 free(tmp);
1741 } else
1742 q = para_strdup("select name from data");
1743 result = get_result(q);
1744 free(q);
1745 if (!result)
1746 return -E_NORESULT;
1747 num_rows = mysql_num_rows(result);
1748 ret = 1;
1749 if (num_rows)
1750 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1751 mysql_free_result(result);
1752 return ret;
1753 }
1754
1755 /*
1756 * summary
1757 */
1758 static int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1759 {
1760 MYSQL_ROW row;
1761 MYSQL_ROW row2;
1762 void *result;
1763 void *result2 = NULL;
1764 const char *fmt = "select count(name) from data where %s='1'";
1765 int ret = -E_NORESULT;
1766
1767 if (argc != 1)
1768 return -E_MYSQL_SYNTAX;
1769 result = get_all_attributes();
1770 if (!result)
1771 goto out;
1772 while ((row = mysql_fetch_row(result))) {
1773 char *buf;
1774
1775 ret = -E_NOROW;
1776 if (!row[0])
1777 goto out;
1778 ret = -E_NORESULT;
1779 buf = make_message(fmt, row[0]);
1780 result2 = get_result(buf);
1781 free(buf);
1782 if (!result2)
1783 goto out;
1784 ret = -E_NOROW;
1785 row2 = mysql_fetch_row(result2);
1786 if (!row2 || !row2[0])
1787 goto out;
1788 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1789 if (ret < 0)
1790 goto out;
1791 }
1792 ret = 1;
1793 out:
1794 if (result2)
1795 mysql_free_result(result2);
1796 if (result)
1797 mysql_free_result(result);
1798 return ret;
1799 }
1800
1801 static int get_numplayed(char *name)
1802 {
1803 void *result;
1804 MYSQL_ROW row;
1805 const char *fmt = "select numplayed from data where name = '%s'";
1806 char *buf = make_message(fmt, name);
1807 int ret = -E_NORESULT;
1808
1809 result = get_result(buf);
1810 free(buf);
1811 if (!result)
1812 goto out;
1813 ret = -E_NOROW;
1814 row = mysql_fetch_row(result);
1815 if (!row || !row[0])
1816 goto out;
1817 ret = atoi(row[0]);
1818 out:
1819 if (result)
1820 mysql_free_result(result);
1821 return ret;
1822 }
1823
1824 static int update_audio_file(char *name)
1825 {
1826 int ret;
1827 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1828 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1829 char *q;
1830 char *ebn = escaped_basename(name);
1831
1832 ret = -E_ESCAPE;
1833 if (!ebn)
1834 goto out;
1835 q = make_message(fmt1, ebn);
1836 ret = real_query(q);
1837 free(q);
1838 if (ret < 0)
1839 goto out;
1840 ret = get_numplayed(ebn);
1841 if (ret < 0)
1842 goto out;
1843 q = make_message(fmt2, ret + 1, ebn);
1844 ret = real_query(q);
1845 free(q);
1846 out:
1847 free(ebn);
1848 return ret;
1849 }
1850
1851 /* If called as child, mmd_lock must be held */
1852 static void update_mmd(char *info)
1853 {
1854 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1855 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1856 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1857 }
1858
1859 static void update_audio_file_server_handler(char *name)
1860 {
1861 char *info;
1862 info = get_selector_info(name);
1863 update_mmd(info);
1864 free(info);
1865 update_audio_file(name);
1866 }
1867
1868 static int com_us(__a_unused int fd, int argc, char *argv[])
1869 {
1870 char *tmp;
1871 int ret;
1872
1873 if (argc != 2)
1874 return -E_MYSQL_SYNTAX;
1875 tmp = escape_str(argv[1]);
1876 if (!tmp)
1877 return -E_ESCAPE;
1878 ret = update_audio_file(argv[1]);
1879 free(tmp);
1880 return ret;
1881 }
1882
1883 static void refresh_selector_info(void)
1884 {
1885 char *name = get_current_audio_file();
1886 char *info;
1887
1888 if (!name)
1889 return;
1890 info = get_selector_info(name);
1891 free(name);
1892 mmd_lock();
1893 update_mmd(info);
1894 mmd_unlock();
1895 free(info);
1896 }
1897
1898 /* select previous/next stream */
1899 static int com_ps(__a_unused int fd, int argc, char *argv[])
1900 {
1901 char *query, *stream = get_current_stream();
1902 void *result = get_result("select name from streams");
1903 MYSQL_ROW row;
1904 int match = -1, ret, i;
1905 unsigned int num_rows;
1906
1907 if (argc != 1)
1908 return -E_MYSQL_SYNTAX;
1909 ret = -E_NORESULT;
1910 if (!result)
1911 goto out;
1912 num_rows = mysql_num_rows(result);
1913 ret = -E_EMPTY_RESULT;
1914 if (num_rows < 2)
1915 goto out;
1916 ret = -E_NOROW;
1917 for (i = 0; i < num_rows; i++) {
1918 row = mysql_fetch_row(result);
1919 if (!row || !row[0])
1920 goto out;
1921 if (!strcmp(row[0], "current_stream"))
1922 continue;
1923 if (!strcmp(row[0], stream)) {
1924 match = i;
1925 break;
1926 }
1927 }
1928 ret = -E_NO_STREAM;
1929 if (match < 0)
1930 goto out;
1931 if (!strcmp(argv[0], "ps"))
1932 i = match > 0? match - 1 : num_rows - 1;
1933 else
1934 i = match < num_rows - 1? match + 1 : 0;
1935 ret = -E_NOROW;
1936 mysql_data_seek(result, i);
1937 row = mysql_fetch_row(result);
1938 if (!row || !row[0])
1939 goto out;
1940 if (!strcmp(row[0], "current_stream")) {
1941 if (!strcmp(argv[0], "ps")) {
1942 i = match - 2;
1943 i = i < 0? i + num_rows : i;
1944 } else {
1945 i = match + 2;
1946 i = i > num_rows - 1? i - num_rows : i;
1947 }
1948 mysql_data_seek(result, i);
1949 row = mysql_fetch_row(result);
1950 if (!row || !row[0])
1951 goto out;
1952 }
1953 query = make_message("update streams set def='%s' where name = "
1954 "'current_stream'", row[0]);
1955 ret = real_query(query);
1956 free(query);
1957 refresh_selector_info();
1958 out:
1959 free(stream);
1960 if (result)
1961 mysql_free_result(result);
1962 return ret;
1963 }
1964
1965 /* streams */
1966 static int com_streams(int fd, int argc, __a_unused char *argv[])
1967 {
1968 unsigned int num_rows;
1969 int i, ret = -E_NORESULT;
1970 void *result;
1971 MYSQL_ROW row;
1972
1973 if (argc > 1 && strcmp(argv[1], "current_stream"))
1974 return -E_MYSQL_SYNTAX;
1975 if (argc > 1) {
1976 char *cs = get_current_stream();
1977 ret = send_va_buffer(fd, "%s\n", cs);
1978 free(cs);
1979 return ret;
1980 }
1981 result = get_result("select name from streams");
1982 if (!result)
1983 goto out;
1984 num_rows = mysql_num_rows(result);
1985 ret = 1;
1986 if (!num_rows)
1987 goto out;
1988 ret = -E_NOROW;
1989 for (i = 0; i < num_rows; i++) {
1990 row = mysql_fetch_row(result);
1991 if (!row || !row[0])
1992 goto out;
1993 if (strcmp(row[0], "current_stream"))
1994 send_va_buffer(fd, "%s\n", row[0]);
1995 }
1996 ret = 1;
1997 out:
1998 if (result)
1999 mysql_free_result(result);
2000 return ret;
2001 }
2002
2003 /* query stream definition */
2004 static int com_strq(int fd, int argc, char *argv[])
2005 {
2006 MYSQL_ROW row;
2007 char *query, *name;
2008 void *result;
2009 int ret;
2010
2011 if (argc < 2) {
2012 ret = -E_GET_STREAM;
2013 name = get_current_stream();
2014 } else {
2015 ret = -E_ESCAPE;
2016 name = escaped_basename(argv[1]);
2017 }
2018 if (!name)
2019 return ret;
2020 ret = -E_NORESULT;
2021 query = make_message("select def from streams where name='%s'", name);
2022 free(name);
2023 result = get_result(query);
2024 free(query);
2025 if (!result)
2026 goto out;
2027 ret = -E_NOROW;
2028 row = mysql_fetch_row(result);
2029 if (!row || !row[0])
2030 goto out;
2031 /* no '\n' needed */
2032 ret = send_buffer(fd, row[0]);
2033 out:
2034 if (result)
2035 mysql_free_result(result);
2036 return ret;
2037 }
2038
2039 /* change stream / change stream and play */
2040 static int com_cs(int fd, int argc, char *argv[])
2041 {
2042 int ret, stream_change;
2043 char *query, *stream = NULL;
2044 char *old_stream = get_current_stream();
2045 int csp = !strcmp(argv[0], "csp");
2046
2047 ret = -E_MYSQL_SYNTAX;
2048 if (argc > 2)
2049 goto out;
2050 if (argc == 1) {
2051 if (csp)
2052 goto out;
2053 ret = send_va_buffer(fd, "%s\n", old_stream);
2054 goto out;
2055 }
2056 ret = -E_GET_QUERY;
2057 /* test if stream is valid, no need to escape argv[1] */
2058 query = get_query(argv[1], NULL, 0);
2059 if (!query)
2060 goto out;
2061 free(query);
2062 /* stream is ok */
2063 stream = escape_str(argv[1]);
2064 if (!stream)
2065 goto out;
2066 stream_change = strcmp(stream, old_stream);
2067 if (stream_change) {
2068 ret = change_stream(stream);
2069 if (ret < 0)
2070 goto out;
2071 refresh_selector_info();
2072 }
2073 if (csp) {
2074 mmd_lock();
2075 mmd->new_afs_status_flags |= AFS_PLAYING;
2076 if (stream_change)
2077 mmd->new_afs_status_flags |= AFS_NEXT;
2078 mmd_unlock();
2079 }
2080 ret = 1;
2081 out:
2082 free(old_stream);
2083 free(stream);
2084 return ret;
2085 }
2086
2087 /*
2088 * sl/skip
2089 */
2090 static int com_sl(int fd, int argc, char *argv[])
2091 {
2092 void *result = NULL;
2093 MYSQL_ROW row;
2094 int ret, i, skip = !strcmp(argv[0], "skip");
2095 char *query, *stream, *tmp;
2096 unsigned int num_rows, num;
2097
2098 if (argc < 2)
2099 return -E_MYSQL_SYNTAX;
2100 num = atoi(argv[1]);
2101 if (!num)
2102 return -E_MYSQL_SYNTAX;
2103 if (argc == 2) {
2104 stream = get_current_stream();
2105 if (!stream)
2106 return -E_GET_STREAM;
2107 } else {
2108 stream = escape_str(argv[2]);
2109 if (!stream)
2110 return -E_ESCAPE;
2111 }
2112 tmp = get_query(stream, NULL, 0);
2113 free(stream);
2114 if (!tmp)
2115 return -E_GET_QUERY;
2116 query = make_message("%s limit %d", tmp, num);
2117 free(tmp);
2118 ret = -E_NORESULT;
2119 result = get_result(query);
2120 free(query);
2121 if (!result)
2122 goto out;
2123 ret = -E_EMPTY_RESULT;
2124 num_rows = mysql_num_rows(result);
2125 if (!num_rows)
2126 goto out;
2127 for (i = 0; i < num_rows && i < num; i++) {
2128 row = mysql_fetch_row(result);
2129 if (skip) {
2130 send_va_buffer(fd, "Skipping %s\n", row[0]);
2131 update_audio_file(row[0]);
2132 } else
2133 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2134 }
2135 ret = 1;
2136 out:
2137 if (result)
2138 mysql_free_result(result);
2139 return ret;
2140 }
2141
2142 /*
2143 * update attributes of name
2144 */
2145 static int update_atts(int fd, char *name, char *atts)
2146 {
2147 int ret;
2148 char *ebn, *q, *old, *new = NULL;
2149
2150 if (!mysql_ptr)
2151 return -E_NOTCONN;
2152 ebn = escaped_basename(name);
2153 if (!ebn)
2154 return -E_ESCAPE;
2155 q = make_message("update data set %s where name = '%s'", atts, ebn);
2156 old = get_atts(ebn, 0);
2157 send_va_buffer(fd, "old: %s\n", old);
2158 free(old);
2159 ret = real_query(q);
2160 free(q);
2161 if (ret < 0)
2162 goto out;
2163 new = get_atts(ebn, 0);
2164 ret = send_va_buffer(fd, "new: %s\n", new);
2165 free(new);
2166 out:
2167 free(ebn);
2168 return ret;
2169 }
2170
2171 /*
2172 * set attributes
2173 */
2174 static int com_sa(int fd, int argc, char *argv[])
2175 {
2176 int i, ret;
2177 char *atts = NULL, *name;
2178
2179 if (argc < 2)
2180 return -E_MYSQL_SYNTAX;
2181 for (i = 1; i < argc; i++) {
2182 int unset = 0;
2183 char *esc, *tmp, *p =argv[i];
2184 int len = strlen(p);
2185
2186 if (!len)
2187 continue;
2188 switch (p[len - 1]) {
2189 case '+':
2190 unset = 0;
2191 break;
2192 case '-':
2193 unset = 1;
2194 break;
2195 default:
2196 goto no_more_atts;
2197 }
2198 p[len - 1] = '\0';
2199 esc = escape_str(p);
2200 if (!esc)
2201 return -E_ESCAPE;
2202 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2203 unset? "0" : "1");
2204 free(esc);
2205 atts = para_strcat(atts, tmp);
2206 free(tmp);
2207 }
2208 no_more_atts:
2209 if (!atts)
2210 return -E_NOATTS;
2211 if (i >= argc) { /* no name given, use current af */
2212 ret = -E_GET_AUDIO_FILE;
2213 if (!(name = get_current_audio_file()))
2214 goto out;
2215 ret = update_atts(fd, name, atts);
2216 free(name);
2217 } else {
2218 ret = 1;
2219 for (; argv[i] && ret >= 0; i++)
2220 ret = update_atts(fd, argv[i], atts);
2221 }
2222 refresh_selector_info();
2223 out:
2224 free(atts);
2225 return ret;
2226 }
2227
2228 /*
2229 * copy attributes
2230 */
2231 static int com_cam(int fd, int argc, char *argv[])
2232 {
2233 char *name = NULL, *meta = NULL, *atts = NULL;
2234 int i, ret;
2235
2236 if (argc < 3)
2237 return -E_MYSQL_SYNTAX;
2238 if (!(name = escaped_basename(argv[1])))
2239 return -E_ESCAPE;
2240 ret = -E_NOATTS;
2241 if (!(atts = get_atts(name, 1)))
2242 goto out;
2243 ret = -E_META;
2244 if (!(meta = get_meta(name, 0)))
2245 goto out;
2246 for (i = 2; i < argc; i++) {
2247 char *ebn, *q;
2248 ret = -E_ESCAPE;
2249 if (!(ebn = escaped_basename(argv[i])))
2250 goto out;
2251 ret = send_va_buffer(fd, "updating %s\n", ebn);
2252 if (ret < 0) {
2253 free(ebn);
2254 goto out;
2255 }
2256 q = make_message("update data set %s where name = '%s'",
2257 meta, ebn);
2258 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2259 ret = real_query(q);
2260 free(ebn);
2261 free(q);
2262 if (ret < 0)
2263 goto out;
2264 }
2265 ret = 1;
2266 out:
2267 if (name)
2268 free(name);
2269 if (meta)
2270 free(meta);
2271 if (atts)
2272 free(atts);
2273 return ret;
2274 }
2275
2276 /*
2277 * verify / clean
2278 */
2279 static int com_vrfy(int fd, int argc, __a_unused char *argv[])
2280 {
2281 char *query;
2282 int ret, vrfy_mode = strcmp(argv[0], "clean");
2283 void *result = NULL;
2284 unsigned int num_rows;
2285 MYSQL_ROW row;
2286 char *escaped_name;
2287
2288 if (argc != 1)
2289 return -E_MYSQL_SYNTAX;
2290 ret = -E_NORESULT;
2291 result = get_result("select data.name from data left join dir on "
2292 "dir.name = data.name where dir.name is NULL");
2293 if (!result)
2294 goto out;
2295 num_rows = mysql_num_rows(result);
2296 if (!num_rows) {
2297 ret = send_buffer(fd, "No invalid entries\n");
2298 goto out;
2299 }
2300 if (vrfy_mode) {
2301 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2302 num_rows == 1? "y" : "ies");
2303 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2304 goto out;
2305 }
2306 while ((row = mysql_fetch_row(result))) {
2307 ret = -E_NOROW;
2308 if (!row[0])
2309 goto out;
2310 ret = -E_ESCAPE;
2311 escaped_name = escape_str(row[0]);
2312 if (!escaped_name)
2313 goto out;
2314 send_va_buffer(fd, "deleting %s\n", escaped_name);
2315 query = make_message("delete from data where name = '%s'",
2316 escaped_name);
2317 ret = real_query(query);
2318 free(query);
2319 if (ret < 0)
2320 goto out;
2321 }
2322
2323 out:
2324 if (result)
2325 mysql_free_result(result);
2326 return ret;
2327 }
2328
2329 static FILE *out_file;
2330
2331 static int mysql_write_tmp_file(const char *dir, const char *name)
2332 {
2333 int ret = -E_TMPFILE;
2334 char *msg = make_message("%s\t%s\n", dir, name);
2335 if (fputs(msg, out_file) != EOF)
2336 ret = 1;
2337 free(msg);
2338 return ret;
2339 }
2340
2341 /*
2342 * update database
2343 */
2344 static int com_upd(int fd, int argc, __a_unused char *argv[])
2345 {
2346 char *tempname = NULL, *query = NULL;
2347 int ret, out_fd = -1, num = 0;
2348 void *result = NULL;
2349 unsigned int num_rows;
2350 MYSQL_ROW row;
2351
2352 if (argc != 1)
2353 return -E_MYSQL_SYNTAX;
2354 out_file = NULL;
2355 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2356 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2357 if (ret < 0)
2358 goto out;
2359 out_fd = ret;
2360 out_file = fdopen(out_fd, "w");
2361 if (!out_file) {
2362 close(out_fd);
2363 goto out;
2364 }
2365 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2366 goto out;
2367 num = ftell(out_file);
2368 /*
2369 * we have to make sure the file hit the disk before we call
2370 * real_query
2371 */
2372 fclose(out_file);
2373 out_file = NULL;
2374 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2375 if (!num)
2376 goto out;
2377 if ((ret = real_query("delete from dir")) < 0)
2378 goto out;
2379 query = make_message("load data infile '%s' ignore into table dir "
2380 "fields terminated by '\t' lines terminated by '\n' "
2381 "(dir, name)", tempname);
2382 ret = real_query(query);
2383 free(query);
2384 if (ret < 0)
2385 goto out;
2386 result = get_result("select dir.name from dir left join data on "
2387 "data.name = dir.name where data.name is NULL");
2388 ret = -E_NORESULT;
2389 if (!result)
2390 goto out;
2391 num_rows = mysql_num_rows(result);
2392 if (!num_rows) {
2393 ret = send_buffer(fd, "no new entries\n");
2394 goto out;
2395 }
2396 while ((row = mysql_fetch_row(result))) {
2397 char *erow;
2398 ret = -E_NOROW;
2399 if (!row[0])
2400 goto out;
2401 send_va_buffer(fd, "new entry: %s\n", row[0]);
2402 erow = escape_str(row[0]);
2403 if (!erow)
2404 goto out;
2405 query = make_message("insert into data (name, pic_id) values "
2406 "('%s','%s')", erow, "1");
2407 free(erow);
2408 ret = real_query(query);
2409 free(query);
2410 if (ret < 0)
2411 goto out;
2412 }
2413 ret = 1;
2414 out:
2415 if (out_fd >= 0)
2416 unlink(tempname);
2417 free(tempname);
2418 if (out_file)
2419 fclose(out_file);
2420 if (result)
2421 mysql_free_result(result);
2422 return ret;
2423 }
2424
2425 static char **server_get_audio_file_list(unsigned int num)
2426 {
2427 char **list = para_malloc((num + 1) * sizeof(char *));
2428 char *tmp, *query, *stream = get_current_stream();
2429 void *result = NULL;
2430 unsigned int num_rows;
2431 int i = 0;
2432 MYSQL_ROW row;
2433
2434 tmp = get_query(stream, NULL, 1);
2435 free(stream);
2436 if (!tmp)
2437 goto err_out;
2438 query = make_message("%s limit %d", tmp, num);
2439 free(tmp);
2440 result = get_result(query);
2441 free(query);
2442 if (!result)
2443 goto err_out;
2444 num_rows = mysql_num_rows(result);
2445 if (!num_rows)
2446 goto err_out;
2447 for (i = 0; i < num_rows && i < num; i++) {
2448 row = mysql_fetch_row(result);
2449 if (!row || !row[0])
2450 goto err_out;
2451 list[i] = para_strdup(row[0]);
2452 }
2453 list[i] = NULL;
2454 goto success;
2455 err_out:
2456 while (i > 0) {
2457 i--;
2458 free(list[i]);
2459 }
2460 free(list);
2461 list = NULL;
2462 success:
2463 if (result)
2464 mysql_free_result(result);
2465 return list;
2466 }
2467
2468 /*
2469 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2470 * on errors. Called from parent on startup and also from com_cdb().
2471 */
2472 static int init_mysql_server(void)
2473 {
2474 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2475
2476 mysql_ptr = mysql_init(NULL);
2477 if (!mysql_ptr) {
2478 PARA_CRIT_LOG("%s", "mysql init error\n");
2479 return -E_NOTCONN;
2480 }
2481 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2482 conf.mysql_port_arg);
2483 if (!conf.mysql_user_arg)
2484 free(u);
2485 /*
2486 * If host is NULL a connection to the local host is assumed,
2487 * If user is NULL, the current user is assumed
2488 */
2489 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2490 conf.mysql_host_arg,
2491 conf.mysql_user_arg,
2492 conf.mysql_passwd_arg,
2493 conf.mysql_database_arg,
2494 conf.mysql_port_arg, NULL, 0))) {
2495 PARA_CRIT_LOG("%s", "connect error\n");
2496 return -E_NOTCONN;
2497 }
2498 PARA_INFO_LOG("%s", "success\n");
2499 return 1;
2500 }
2501
2502 /* mmd lock must be held */
2503 static void write_msg2mmd(int success)
2504 {
2505 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2506 success < 0? PARA_STRERROR(-success) :
2507 "successfully connected to mysql server",
2508 success < 0? "" : mysql_get_server_info(mysql_ptr));
2509 }
2510
2511 /* create database */
2512 static int com_cdb(int fd, int argc, char *argv[])
2513 {
2514 char *query;
2515 int ret;
2516
2517 if (mysql_ptr) {
2518 PARA_INFO_LOG("%s", "closing database\n");
2519 mysql_close(mysql_ptr);
2520 }
2521 /* dont use any database */
2522 conf.mysql_database_arg = NULL; /* leak? */
2523 ret = -E_MYSQL_INIT;
2524 if (init_mysql_server() < 0 || !mysql_ptr)
2525 goto out;
2526 if (argc < 2)
2527 conf.mysql_database_arg = para_strdup("paraslash");
2528 else {
2529 ret = -E_ESCAPE;
2530 conf.mysql_database_arg = escape_str(argv[1]);
2531 if (!conf.mysql_database_arg)
2532 goto out;
2533 }
2534 query = make_message("create database %s", conf.mysql_database_arg);
2535 ret = real_query(query);
2536 free(query);
2537 if (ret < 0)
2538 goto out;
2539 /* reconnect with database just created */
2540 mysql_close(mysql_ptr);
2541 ret = -E_MYSQL_INIT;
2542 if (init_mysql_server() < 0 || !mysql_ptr)
2543 goto out;
2544 mmd_lock();
2545 write_msg2mmd(1);
2546 mmd_unlock();
2547 ret = -E_QFAILED;
2548 if (real_query("create table data (name varchar(255) binary not null "
2549 "primary key, "
2550 "lastplayed datetime not null default "
2551 "'1970-01-01', "
2552 "numplayed int not null default 0, "
2553 "pic_id bigint unsigned not null default 1)") < 0)
2554 goto out;
2555 if (real_query("create table dir (name varchar(255) binary not null "
2556 "primary key, dir varchar(255) default null)") < 0)
2557 goto out;
2558 if (real_query("create table pics ("
2559 "id bigint(20) unsigned not null primary key "
2560 "auto_increment, "
2561 "name varchar(255) binary not null, "
2562 "pic mediumblob not null)") < 0)
2563 goto out;
2564 if (real_query("create table streams ("
2565 "name varchar(255) binary not null primary key, "
2566 "def blob not null)") < 0)
2567 goto out;
2568 if (real_query("insert into streams (name, def) values "
2569 "('current_stream', '(none)')") < 0)
2570 goto out;
2571 ret = send_va_buffer(fd, "successfully created database %s\n",
2572 conf.mysql_database_arg);
2573 out:
2574 return ret;
2575 }
2576
2577 static void shutdown_connection(void)
2578 {
2579 if (mysql_ptr) {
2580 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2581 mysql_close(mysql_ptr);
2582 mysql_ptr = NULL;
2583 }
2584 }
2585
2586 /**
2587 * the init function of the mysql-based audio file selector
2588 *
2589 * Check the command line options and initialize all function pointers of \a db.
2590 * Connect to the mysql server and initialize the info string.
2591 *
2592 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2593 * random_selector.c
2594 */
2595 int mysql_selector_init(struct audio_file_selector *db)
2596 {
2597 int ret;
2598
2599 if (!conf.mysql_passwd_given)
2600 return -E_NO_MYSQL_PASSWD;
2601 if (!conf.mysql_audio_file_dir_given)
2602 return -E_NO_AF_DIR;
2603 db->name = "mysql";
2604 db->cmd_list = cmds;
2605 db->get_audio_file_list = server_get_audio_file_list;
2606 db->update_audio_file = update_audio_file_server_handler;
2607 db->shutdown = shutdown_connection;
2608 ret = init_mysql_server();
2609 if (ret < 0)
2610 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2611 write_msg2mmd(ret);
2612 return 1; /* return success even if connect failed to give the
2613 * user the chance to exec com_cdb
2614 */
2615 }