fix gcc warnings on shadowed declarations
[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 (!strcmp(tmp, "(none)")) {
1164 free(tmp);
1165 if (filename) {
1166 char *ret, *ebn = escaped_basename(filename);
1167 ret = make_message("select to_days(now()) - "
1168 "to_days(lastplayed) from data "
1169 "where name = '%s'", ebn);
1170 free(ebn);
1171 return ret;
1172 }
1173 if (with_path)
1174 return make_message(
1175 "select concat(dir.dir, '/', dir.name) "
1176 "from data, dir where dir.name = data.name "
1177 "order by data.lastplayed"
1178 );
1179 return make_message(
1180 "select name from data where name is not NULL "
1181 "order by lastplayed"
1182 );
1183 }
1184 free(tmp);
1185 query = make_message("select def from streams where name = '%s'",
1186 streamname);
1187 result = get_result(query);
1188 free(query);
1189 query = NULL;
1190 if (!result)
1191 goto out;
1192 row = mysql_fetch_row(result);
1193 if (!row || !row[0])
1194 goto out;
1195 end = row[0];
1196 while (*end) {
1197 int n;
1198 char *arg, *line = end;
1199
1200 if (!(end = strchr(line, '\n')))
1201 break;
1202 *end = '\0';
1203 end++;
1204 if (sscanf(line, "%200s%n", command, &n) < 1)
1205 continue;
1206 arg = line + n;
1207 if (!strcmp(command, "accept:")) {
1208 char *tmp2 = s_a_r_list(macro_list, arg);
1209 if (accept_opts)
1210 accept_opts = para_strcat(
1211 accept_opts, " or ");
1212 accept_opts = para_strcat(accept_opts, tmp2);
1213 free(tmp2);
1214 continue;
1215 }
1216 if (!strcmp(command, "deny:")) {
1217 char *tmp2 = s_a_r_list(macro_list, arg);
1218 if (deny_opts)
1219 deny_opts = para_strcat(deny_opts, " or ");
1220 deny_opts = para_strcat(deny_opts, tmp2);
1221 free(tmp2);
1222 continue;
1223 }
1224 if (!score && !strcmp(command, "score:"))
1225 score = s_a_r_list(macro_list, arg);
1226 }
1227 if (!score) {
1228 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
1229 if (!score)
1230 goto out;
1231 }
1232 if (filename) {
1233 char *ebn = escaped_basename(filename);
1234 if (!ebn)
1235 goto out;
1236 select_clause = make_message("select %s from data ", score);
1237 free(score);
1238 where_clause = make_message( "where name = '%s' ", ebn);
1239 free(ebn);
1240 order = para_strdup("");
1241 goto write_query;
1242 }
1243 select_clause = para_strdup(with_path?
1244 "select concat(dir.dir, '/', dir.name) from data, dir "
1245 "where dir.name = data.name "
1246 :
1247 "select name from data where name is not NULL");
1248 order = make_message("order by -(%s)", score);
1249 free(score);
1250 if (accept_opts && deny_opts) {
1251 where_clause = make_message("and ((%s) and not (%s)) ",
1252 accept_opts, deny_opts);
1253 goto write_query;
1254 }
1255 if (accept_opts && !deny_opts) {
1256 where_clause = make_message("and (%s) ", accept_opts);
1257 goto write_query;
1258 }
1259 if (!accept_opts && deny_opts) {
1260 where_clause = make_message("and not (%s) ", deny_opts);
1261 goto write_query;
1262 }
1263 where_clause = para_strdup("");
1264 write_query:
1265 query = make_message("%s %s %s", select_clause, where_clause, order);
1266 free(order);
1267 free(select_clause);
1268 free(where_clause);
1269 out:
1270 if (accept_opts)
1271 free(accept_opts);
1272 if (deny_opts)
1273 free(deny_opts);
1274 if (result)
1275 mysql_free_result(result);
1276 return query;
1277 }
1278
1279
1280
1281 /*
1282 * This is called from server and from some commands. Name must not be NULL
1283 * Never returns NULL.
1284 */
1285 static char *get_selector_info(char *name)
1286 {
1287 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
1288 void *result = NULL;
1289 MYSQL_ROW row = NULL;
1290
1291 if (!name)
1292 return para_strdup("(none)");
1293 stream = get_current_stream();
1294 meta = get_meta(name, 1);
1295 atts = get_atts(name, 0);
1296 dir = get_dir(name);
1297 /* get score */
1298 query = get_query(stream, name, 0); /* FIXME: pass stream == NULL instead? */
1299 if (!query)
1300 goto write;
1301 result = get_result(query);
1302 free(query);
1303 if (result)
1304 row = mysql_fetch_row(result);
1305 write:
1306 info = make_message("dbinfo1:dir: %s\n"
1307 "dbinfo2:stream: %s, %s, score: %s\n"
1308 "dbinfo3:%s\n",
1309 dir? dir : "(not contained in table)",
1310 stream, meta,
1311 (result && row && row[0])? row[0] : "(no score)",
1312 atts);
1313 if (dir)
1314 free(dir);
1315 if (meta)
1316 free(meta);
1317 if (atts)
1318 free(atts);
1319 if (stream)
1320 free(stream);
1321 if (result)
1322 mysql_free_result(result);
1323 return info;
1324 }
1325
1326
1327 /* might return NULL */
1328 static char *get_current_audio_file(void)
1329 {
1330 char *name;
1331 mmd_lock();
1332 name = para_basename(mmd->filename);
1333 mmd_unlock();
1334 return name;
1335 }
1336
1337
1338 /* print database info */
1339 static int com_info(int fd, int argc, char *argv[])
1340 {
1341 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
1342 int ret, com_la = strcmp(argv[0], "info");
1343
1344 if (argc < 2) {
1345 ret = -E_GET_AUDIO_FILE;
1346 if (!(name = get_current_audio_file()))
1347 goto out;
1348 ret = send_va_buffer(fd, "%s\n", name);
1349 if (ret < 0)
1350 goto out;
1351 } else {
1352 ret = -E_ESCAPE;
1353 if (!(name = escaped_basename(argv[1])))
1354 goto out;
1355 }
1356 meta = get_meta(name, 1);
1357 atts = get_atts(name, 0);
1358 dir = get_dir(name);
1359 if (com_la)
1360 ret = send_va_buffer(fd, "%s\n", atts);
1361 else
1362 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
1363 dir? dir : "(not contained in table)", meta, atts);
1364 out:
1365 free(meta);
1366 free(atts);
1367 free(dir);
1368 free(name);
1369 return ret;
1370 }
1371
1372 static int change_stream(const char *stream)
1373 {
1374 char *query;
1375 int ret;
1376 query = make_message("update streams set def='%s' "
1377 "where name = 'current_stream'", stream);
1378 ret = real_query(query);
1379 free(query);
1380 return ret;
1381 }
1382
1383 static int get_pic_id_by_name(char *name)
1384 {
1385 char *q, *ebn;
1386 void *result = NULL;
1387 long unsigned ret;
1388 MYSQL_ROW row;
1389
1390 if (!(ebn = escaped_basename(name)))
1391 return -E_ESCAPE;
1392 q = make_message("select pic_id from data where name = '%s'", ebn);
1393 free(ebn);
1394 result = get_result(q);
1395 free(q);
1396 if (!result)
1397 return -E_NORESULT;
1398 row = mysql_fetch_row(result);
1399 ret = -E_NOROW;
1400 if (row && row[0])
1401 ret = atol(row[0]);
1402 mysql_free_result(result);
1403 return ret;
1404 }
1405
1406 static int remove_entry(const char *name)
1407 {
1408 char *q, *ebn = escaped_basename(name);
1409 int ret = -E_ESCAPE;
1410
1411 if (!ebn)
1412 goto out;
1413 q = make_message("delete from data where name = '%s'", ebn);
1414 real_query(q); /* ignore errors */
1415 free(q);
1416 q = make_message("delete from dir where name = '%s'", ebn);
1417 real_query(q); /* ignore errors */
1418 free(q);
1419 ret = 1;
1420 out:
1421 free(ebn);
1422 return ret;
1423 }
1424
1425 static int add_entry(const char *name)
1426 {
1427 char *q, *dn, *ebn = NULL, *edn = NULL;
1428 int ret;
1429
1430 if (!name || !*name)
1431 return -E_MYSQL_SYNTAX;
1432 ebn = escaped_basename(name);
1433 if (!ebn)
1434 return -E_ESCAPE;
1435 ret = -E_MYSQL_SYNTAX;
1436 dn = para_dirname(name);
1437 if (!dn)
1438 goto out;
1439 ret = -E_ESCAPE;
1440 edn = escape_str(dn);
1441 free(dn);
1442 if (!edn || !*edn)
1443 goto out;
1444 q = make_message("insert into data (name, pic_id) values "
1445 "('%s', '%s')", ebn, "1");
1446 ret = real_query(q);
1447 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1448 free(q);
1449 if (ret < 0)
1450 goto out;
1451 q = make_message("insert into dir (name, dir) values "
1452 "('%s', '%s')", ebn, edn);
1453 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1454 ret = real_query(q);
1455 free(q);
1456 out:
1457 if (ebn)
1458 free(ebn);
1459 if (edn)
1460 free(edn);
1461 return ret;
1462 }
1463
1464 /*
1465 * remove/add entries
1466 */
1467 static int com_rm_ne(__a_unused int fd, int argc, char *argv[])
1468 {
1469 int ne = !strcmp(argv[0], "ne");
1470 int i, ret;
1471 if (argc < 2)
1472 return -E_MYSQL_SYNTAX;
1473 for (i = 1; i < argc; i++) {
1474 ret = remove_entry(argv[i]);
1475 if (ret < 0)
1476 return ret;
1477 if (!ne)
1478 continue;
1479 ret = add_entry(argv[i]);
1480 if (ret < 0)
1481 return ret;
1482 }
1483 return 1;
1484 }
1485
1486 /*
1487 * mv: rename entry
1488 */
1489 static int com_mv(__a_unused int fd, int argc, char *argv[])
1490 {
1491 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1492 int ret;
1493
1494 if (argc != 3)
1495 return -E_MYSQL_SYNTAX;
1496 ret = -E_ESCAPE;
1497 ebn1 = escaped_basename(argv[1]);
1498 ebn2 = escaped_basename(argv[2]);
1499 if (!ebn1 || !ebn2 | !*ebn1 || !*ebn2)
1500 goto out;
1501 ret = -E_MYSQL_SYNTAX;
1502 if (!strcmp(ebn1, ebn2))
1503 goto out;
1504 remove_entry(argv[2]); /* no need to escape, ignore error */
1505 q = make_message("update data set name = '%s' where name = '%s'",
1506 ebn2, ebn1);
1507 ret = real_query(q);
1508 free(q);
1509 if (ret < 0)
1510 goto out;
1511 ret = -E_AUDIO_FILE;
1512 if (!mysql_affected_rows(mysql_ptr))
1513 goto out;
1514 q = make_message("update dir set name = '%s' where name = '%s'",
1515 ebn2, ebn1);
1516 ret = real_query(q);
1517 free(q);
1518 if (ret < 0)
1519 goto out;
1520 ret = 1;
1521 dn = para_dirname(argv[2]);
1522 if (!dn)
1523 goto out;
1524 ret = -E_ESCAPE;
1525 edn = escape_str(dn);
1526 free(dn);
1527 if (!edn)
1528 goto out;
1529 ret = 1;
1530 if (!*edn)
1531 goto out;
1532 q = make_message("update dir set dir = '%s' where name = '%s'",
1533 edn, ebn2);
1534 ret = real_query(q);
1535 free(q);
1536 out:
1537 free(edn);
1538 free(ebn1);
1539 free(ebn2);
1540 return ret;
1541 }
1542
1543 /*
1544 * picass: associate pic to audio file
1545 * snp: set numplayed
1546 */
1547 static int com_set(__a_unused int fd, int argc, char *argv[])
1548 {
1549 char *q, *ebn;
1550 long unsigned id;
1551 int i, ret;
1552 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1553
1554 if (argc < 3)
1555 return -E_MYSQL_SYNTAX;
1556 id = atol(argv[1]);
1557 for (i = 2; i < argc; i++) {
1558 ebn = escaped_basename(argv[i]);
1559 if (!ebn)
1560 return -E_ESCAPE;
1561 q = make_message("update data set %s = %lu "
1562 "where name = '%s'", field, id, ebn);
1563 free(ebn);
1564 ret = real_query(q);
1565 free(q);
1566 if (ret < 0)
1567 return ret;
1568 }
1569 return 1;
1570 }
1571
1572 /*
1573 * picch: change entry's name in pics table
1574 */
1575 static int com_picch(__a_unused int fd, int argc, char *argv[])
1576 {
1577 int ret;
1578 long unsigned id;
1579 char *q, *tmp;
1580
1581 if (argc != 3)
1582 return -E_MYSQL_SYNTAX;
1583 id = atol(argv[1]);
1584 ret = -E_ESCAPE;
1585 tmp = escape_str(argv[2]);
1586 if (!tmp)
1587 return -E_ESCAPE;
1588 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1589 free(tmp);
1590 ret = real_query(q);
1591 free(q);
1592 return ret;
1593 }
1594
1595 /*
1596 * piclist: print list of pics in db
1597 */
1598 static int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1599 {
1600 void *result = NULL;
1601 MYSQL_ROW row;
1602 unsigned long *length;
1603 int ret;
1604
1605 if (argc != 1)
1606 return -E_MYSQL_SYNTAX;
1607 result = get_result("select id,name,pic from pics order by id");
1608 if (!result)
1609 return -E_NORESULT;
1610 while ((row = mysql_fetch_row(result))) {
1611 length = mysql_fetch_lengths(result);
1612 if (!row || !row[0] || !row[1] || !row[2])
1613 continue;
1614 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1615 if (ret < 0)
1616 goto out;
1617 }
1618 ret = 1;
1619 out:
1620 mysql_free_result(result);
1621 return ret;
1622 }
1623
1624 /*
1625 * picdel: delete picture from database
1626 */
1627 static int com_picdel(int fd, int argc, char *argv[])
1628 {
1629 char *q;
1630 long unsigned id;
1631 my_ulonglong aff;
1632 int i, ret;
1633
1634 if (argc < 2)
1635 return -E_MYSQL_SYNTAX;
1636 for (i = 1; i < argc; i++) {
1637 id = atol(argv[i]);
1638 q = make_message("delete from pics where id = %lu", id);
1639 ret = real_query(q);
1640 free(q);
1641 if (ret < 0)
1642 return ret;
1643 aff = mysql_affected_rows(mysql_ptr);
1644 if (!aff) {
1645 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1646 if (ret < 0)
1647 return ret;
1648 continue;
1649 }
1650 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1651 ret = real_query(q);
1652 free(q);
1653 }
1654 return 1;
1655 }
1656 /*
1657 * pic: get picture by name or by number
1658 */
1659 static int com_pic(int fd, int argc, char *argv[])
1660 {
1661 void *result = NULL;
1662 MYSQL_ROW row;
1663 unsigned long *length, id;
1664 int ret;
1665 char *q, *name = NULL;
1666
1667 if (argc < 2) {
1668 ret = -E_GET_AUDIO_FILE;
1669 name = get_current_audio_file();
1670 } else {
1671 ret = -E_ESCAPE;
1672 name = escaped_basename(argv[1]);
1673 }
1674 if (!name)
1675 return ret;
1676 if (*name == '#')
1677 id = atoi(name + 1);
1678 else
1679 id = get_pic_id_by_name(name);
1680 free(name);
1681 if (id <= 0)
1682 return id;
1683 q = make_message("select pic from pics where id = '%lu'", id);
1684 result = get_result(q);
1685 free(q);
1686 if (!result)
1687 return -E_NORESULT;
1688 row = mysql_fetch_row(result);
1689 ret = -E_NOROW;
1690 if (!row || !row[0])
1691 goto out;
1692 length = mysql_fetch_lengths(result);
1693 ret = send_bin_buffer(fd, row[0], *length);
1694 out:
1695 mysql_free_result(result);
1696 return ret;
1697 }
1698
1699 /* strdel */
1700 static int com_strdel(__a_unused int fd, int argc, char *argv[])
1701 {
1702 char *q, *tmp;
1703 int ret;
1704
1705 if (argc < 2)
1706 return -E_MYSQL_SYNTAX;
1707 tmp = escape_str(argv[1]);
1708 if (!tmp)
1709 return -E_ESCAPE;
1710 q = make_message("delete from streams where name='%s'", tmp);
1711 free(tmp);
1712 ret = real_query(q);
1713 free(q);
1714 return ret;
1715 }
1716
1717 /*
1718 * ls
1719 */
1720 static int com_ls(int fd, int argc, char *argv[])
1721 {
1722 char *q;
1723 void *result;
1724 int ret;
1725 unsigned int num_rows;
1726
1727 if (argc > 2)
1728 return -E_MYSQL_SYNTAX;
1729 if (argc > 1) {
1730 char *tmp = escape_str(argv[1]);
1731 if (!tmp)
1732 return -E_ESCAPE;
1733 q = make_message("select name from data where name like '%s'",
1734 tmp);
1735 free(tmp);
1736 } else
1737 q = para_strdup("select name from data");
1738 result = get_result(q);
1739 free(q);
1740 if (!result)
1741 return -E_NORESULT;
1742 num_rows = mysql_num_rows(result);
1743 ret = 1;
1744 if (num_rows)
1745 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1746 mysql_free_result(result);
1747 return ret;
1748 }
1749
1750 /*
1751 * summary
1752 */
1753 static int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1754 {
1755 MYSQL_ROW row;
1756 MYSQL_ROW row2;
1757 void *result;
1758 void *result2 = NULL;
1759 const char *fmt = "select count(name) from data where %s='1'";
1760 int ret = -E_NORESULT;
1761
1762 if (argc != 1)
1763 return -E_MYSQL_SYNTAX;
1764 result = get_all_attributes();
1765 if (!result)
1766 goto out;
1767 while ((row = mysql_fetch_row(result))) {
1768 char *buf;
1769
1770 ret = -E_NOROW;
1771 if (!row[0])
1772 goto out;
1773 ret = -E_NORESULT;
1774 buf = make_message(fmt, row[0]);
1775 result2 = get_result(buf);
1776 free(buf);
1777 if (!result2)
1778 goto out;
1779 ret = -E_NOROW;
1780 row2 = mysql_fetch_row(result2);
1781 if (!row2 || !row2[0])
1782 goto out;
1783 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1784 if (ret < 0)
1785 goto out;
1786 }
1787 ret = 1;
1788 out:
1789 if (result2)
1790 mysql_free_result(result2);
1791 if (result)
1792 mysql_free_result(result);
1793 return ret;
1794 }
1795
1796 static int get_numplayed(char *name)
1797 {
1798 void *result;
1799 MYSQL_ROW row;
1800 const char *fmt = "select numplayed from data where name = '%s'";
1801 char *buf = make_message(fmt, name);
1802 int ret = -E_NORESULT;
1803
1804 result = get_result(buf);
1805 free(buf);
1806 if (!result)
1807 goto out;
1808 ret = -E_NOROW;
1809 row = mysql_fetch_row(result);
1810 if (!row || !row[0])
1811 goto out;
1812 ret = atoi(row[0]);
1813 out:
1814 if (result)
1815 mysql_free_result(result);
1816 return ret;
1817 }
1818
1819 static int update_audio_file(char *name)
1820 {
1821 int ret;
1822 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1823 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1824 char *q;
1825 char *ebn = escaped_basename(name);
1826
1827 ret = -E_ESCAPE;
1828 if (!ebn)
1829 goto out;
1830 q = make_message(fmt1, ebn);
1831 ret = real_query(q);
1832 free(q);
1833 if (ret < 0)
1834 goto out;
1835 ret = get_numplayed(ebn);
1836 if (ret < 0)
1837 goto out;
1838 q = make_message(fmt2, ret + 1, ebn);
1839 ret = real_query(q);
1840 free(q);
1841 out:
1842 free(ebn);
1843 return ret;
1844 }
1845
1846 /* If called as child, mmd_lock must be held */
1847 static void update_mmd(char *info)
1848 {
1849 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1850 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1851 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1852 }
1853
1854 static void update_audio_file_server_handler(char *name)
1855 {
1856 char *info;
1857 info = get_selector_info(name);
1858 update_mmd(info);
1859 free(info);
1860 update_audio_file(name);
1861 }
1862
1863 static int com_us(__a_unused int fd, int argc, char *argv[])
1864 {
1865 char *tmp;
1866 int ret;
1867
1868 if (argc != 2)
1869 return -E_MYSQL_SYNTAX;
1870 tmp = escape_str(argv[1]);
1871 if (!tmp)
1872 return -E_ESCAPE;
1873 ret = update_audio_file(argv[1]);
1874 free(tmp);
1875 return ret;
1876 }
1877
1878 static void refresh_selector_info(void)
1879 {
1880 char *name = get_current_audio_file();
1881 char *info;
1882
1883 if (!name)
1884 return;
1885 info = get_selector_info(name);
1886 free(name);
1887 mmd_lock();
1888 update_mmd(info);
1889 mmd_unlock();
1890 free(info);
1891 }
1892
1893 /* select previous/next stream */
1894 static int com_ps(__a_unused int fd, int argc, char *argv[])
1895 {
1896 char *query, *stream = get_current_stream();
1897 void *result = get_result("select name from streams");
1898 MYSQL_ROW row;
1899 int match = -1, ret, i;
1900 unsigned int num_rows;
1901
1902 if (argc != 1)
1903 return -E_MYSQL_SYNTAX;
1904 ret = -E_NORESULT;
1905 if (!result)
1906 goto out;
1907 num_rows = mysql_num_rows(result);
1908 ret = -E_EMPTY_RESULT;
1909 if (num_rows < 2)
1910 goto out;
1911 ret = -E_NOROW;
1912 for (i = 0; i < num_rows; i++) {
1913 row = mysql_fetch_row(result);
1914 if (!row || !row[0])
1915 goto out;
1916 if (!strcmp(row[0], "current_stream"))
1917 continue;
1918 if (!strcmp(row[0], stream)) {
1919 match = i;
1920 break;
1921 }
1922 }
1923 ret = -E_NO_STREAM;
1924 if (match < 0)
1925 goto out;
1926 if (!strcmp(argv[0], "ps"))
1927 i = match > 0? match - 1 : num_rows - 1;
1928 else
1929 i = match < num_rows - 1? match + 1 : 0;
1930 ret = -E_NOROW;
1931 mysql_data_seek(result, i);
1932 row = mysql_fetch_row(result);
1933 if (!row || !row[0])
1934 goto out;
1935 if (!strcmp(row[0], "current_stream")) {
1936 if (!strcmp(argv[0], "ps")) {
1937 i = match - 2;
1938 i = i < 0? i + num_rows : i;
1939 } else {
1940 i = match + 2;
1941 i = i > num_rows - 1? i - num_rows : i;
1942 }
1943 mysql_data_seek(result, i);
1944 row = mysql_fetch_row(result);
1945 if (!row || !row[0])
1946 goto out;
1947 }
1948 query = make_message("update streams set def='%s' where name = "
1949 "'current_stream'", row[0]);
1950 ret = real_query(query);
1951 free(query);
1952 refresh_selector_info();
1953 out:
1954 free(stream);
1955 if (result)
1956 mysql_free_result(result);
1957 return ret;
1958 }
1959
1960 /* streams */
1961 static int com_streams(int fd, int argc, __a_unused char *argv[])
1962 {
1963 unsigned int num_rows;
1964 int i, ret = -E_NORESULT;
1965 void *result;
1966 MYSQL_ROW row;
1967
1968 if (argc > 1 && strcmp(argv[1], "current_stream"))
1969 return -E_MYSQL_SYNTAX;
1970 if (argc > 1) {
1971 char *cs = get_current_stream();
1972 ret = send_va_buffer(fd, "%s\n", cs);
1973 free(cs);
1974 return ret;
1975 }
1976 result = get_result("select name from streams");
1977 if (!result)
1978 goto out;
1979 num_rows = mysql_num_rows(result);
1980 ret = 1;
1981 if (!num_rows)
1982 goto out;
1983 ret = -E_NOROW;
1984 for (i = 0; i < num_rows; i++) {
1985 row = mysql_fetch_row(result);
1986 if (!row || !row[0])
1987 goto out;
1988 if (strcmp(row[0], "current_stream"))
1989 send_va_buffer(fd, "%s\n", row[0]);
1990 }
1991 ret = 1;
1992 out:
1993 if (result)
1994 mysql_free_result(result);
1995 return ret;
1996 }
1997
1998 /* query stream definition */
1999 static int com_strq(int fd, int argc, char *argv[])
2000 {
2001 MYSQL_ROW row;
2002 char *query, *name;
2003 void *result;
2004 int ret;
2005
2006 if (argc < 2) {
2007 ret = -E_GET_STREAM;
2008 name = get_current_stream();
2009 } else {
2010 ret = -E_ESCAPE;
2011 name = escaped_basename(argv[1]);
2012 }
2013 if (!name)
2014 return ret;
2015 ret = -E_NORESULT;
2016 query = make_message("select def from streams where name='%s'", name);
2017 free(name);
2018 result = get_result(query);
2019 free(query);
2020 if (!result)
2021 goto out;
2022 ret = -E_NOROW;
2023 row = mysql_fetch_row(result);
2024 if (!row || !row[0])
2025 goto out;
2026 /* no '\n' needed */
2027 ret = send_buffer(fd, row[0]);
2028 out:
2029 if (result)
2030 mysql_free_result(result);
2031 return ret;
2032 }
2033
2034 /* change stream / change stream and play */
2035 static int com_cs(int fd, int argc, char *argv[])
2036 {
2037 int ret, stream_change;
2038 char *query, *stream = NULL;
2039 char *old_stream = get_current_stream();
2040 int csp = !strcmp(argv[0], "csp");
2041
2042 ret = -E_MYSQL_SYNTAX;
2043 if (argc > 2)
2044 goto out;
2045 if (argc == 1) {
2046 if (csp)
2047 goto out;
2048 ret = send_va_buffer(fd, "%s\n", old_stream);
2049 goto out;
2050 }
2051 ret = -E_GET_QUERY;
2052 /* test if stream is valid, no need to escape argv[1] */
2053 query = get_query(argv[1], NULL, 0);
2054 if (!query)
2055 goto out;
2056 free(query);
2057 /* stream is ok */
2058 stream = escape_str(argv[1]);
2059 if (!stream)
2060 goto out;
2061 stream_change = strcmp(stream, old_stream);
2062 if (stream_change) {
2063 ret = change_stream(stream);
2064 if (ret < 0)
2065 goto out;
2066 refresh_selector_info();
2067 }
2068 if (csp) {
2069 mmd_lock();
2070 mmd->new_afs_status_flags |= AFS_PLAYING;
2071 if (stream_change)
2072 mmd->new_afs_status_flags |= AFS_NEXT;
2073 mmd_unlock();
2074 }
2075 ret = 1;
2076 out:
2077 free(old_stream);
2078 free(stream);
2079 return ret;
2080 }
2081
2082 /*
2083 * sl/skip
2084 */
2085 static int com_sl(int fd, int argc, char *argv[])
2086 {
2087 void *result = NULL;
2088 MYSQL_ROW row;
2089 int ret, i, skip = !strcmp(argv[0], "skip");
2090 char *query, *stream, *tmp;
2091 unsigned int num_rows, num;
2092
2093 if (argc < 2)
2094 return -E_MYSQL_SYNTAX;
2095 num = atoi(argv[1]);
2096 if (!num)
2097 return -E_MYSQL_SYNTAX;
2098 stream = (argc == 2)? get_current_stream() : escape_str(argv[2]);
2099 tmp = get_query(stream, NULL, 0);
2100 query = make_message("%s limit %d", tmp, num);
2101 free(tmp);
2102 ret = -E_GET_QUERY;
2103 free(stream);
2104 if (!query)
2105 goto out;
2106 ret = -E_NORESULT;
2107 result = get_result(query);
2108 free(query);
2109 if (!result)
2110 goto out;
2111 ret = -E_EMPTY_RESULT;
2112 num_rows = mysql_num_rows(result);
2113 if (!num_rows)
2114 goto out;
2115 for (i = 0; i < num_rows && i < num; i++) {
2116 row = mysql_fetch_row(result);
2117 if (skip) {
2118 send_va_buffer(fd, "Skipping %s\n", row[0]);
2119 update_audio_file(row[0]);
2120 } else
2121 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2122 }
2123 ret = 1;
2124 out:
2125 if (result)
2126 mysql_free_result(result);
2127 return ret;
2128 }
2129
2130 /*
2131 * update attributes of name
2132 */
2133 static int update_atts(int fd, char *name, char *atts)
2134 {
2135 int ret;
2136 char *ebn, *q, *old, *new = NULL;
2137
2138 if (!mysql_ptr)
2139 return -E_NOTCONN;
2140 ebn = escaped_basename(name);
2141 if (!ebn)
2142 return -E_ESCAPE;
2143 q = make_message("update data set %s where name = '%s'", atts, ebn);
2144 old = get_atts(ebn, 0);
2145 send_va_buffer(fd, "old: %s\n", old);
2146 free(old);
2147 ret = real_query(q);
2148 free(q);
2149 if (ret < 0)
2150 goto out;
2151 new = get_atts(ebn, 0);
2152 ret = send_va_buffer(fd, "new: %s\n", new);
2153 free(new);
2154 out:
2155 free(ebn);
2156 return ret;
2157 }
2158
2159 /*
2160 * set attributes
2161 */
2162 static int com_sa(int fd, int argc, char *argv[])
2163 {
2164 int i, ret;
2165 char *atts = NULL, *name;
2166
2167 if (argc < 2)
2168 return -E_MYSQL_SYNTAX;
2169 for (i = 1; i < argc; i++) {
2170 int unset = 0;
2171 char *esc, *tmp, *p =argv[i];
2172 int len = strlen(p);
2173
2174 if (!len)
2175 continue;
2176 switch (p[len - 1]) {
2177 case '+':
2178 unset = 0;
2179 break;
2180 case '-':
2181 unset = 1;
2182 break;
2183 default:
2184 goto no_more_atts;
2185 }
2186 p[len - 1] = '\0';
2187 esc = escape_str(p);
2188 if (!esc)
2189 return -E_ESCAPE;
2190 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2191 unset? "0" : "1");
2192 free(esc);
2193 atts = para_strcat(atts, tmp);
2194 free(tmp);
2195 }
2196 no_more_atts:
2197 if (!atts)
2198 return -E_NOATTS;
2199 if (i >= argc) { /* no name given, use current af */
2200 ret = -E_GET_AUDIO_FILE;
2201 if (!(name = get_current_audio_file()))
2202 goto out;
2203 ret = update_atts(fd, name, atts);
2204 free(name);
2205 } else {
2206 ret = 1;
2207 for (; argv[i] && ret >= 0; i++)
2208 ret = update_atts(fd, argv[i], atts);
2209 }
2210 refresh_selector_info();
2211 out:
2212 free(atts);
2213 return ret;
2214 }
2215
2216 /*
2217 * copy attributes
2218 */
2219 static int com_cam(int fd, int argc, char *argv[])
2220 {
2221 char *name = NULL, *meta = NULL, *atts = NULL;
2222 int i, ret;
2223
2224 if (argc < 3)
2225 return -E_MYSQL_SYNTAX;
2226 if (!(name = escaped_basename(argv[1])))
2227 return -E_ESCAPE;
2228 ret = -E_NOATTS;
2229 if (!(atts = get_atts(name, 1)))
2230 goto out;
2231 ret = -E_META;
2232 if (!(meta = get_meta(name, 0)))
2233 goto out;
2234 for (i = 2; i < argc; i++) {
2235 char *ebn, *q;
2236 ret = -E_ESCAPE;
2237 if (!(ebn = escaped_basename(argv[i])))
2238 goto out;
2239 ret = send_va_buffer(fd, "updating %s\n", ebn);
2240 if (ret < 0) {
2241 free(ebn);
2242 goto out;
2243 }
2244 q = make_message("update data set %s where name = '%s'",
2245 meta, ebn);
2246 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2247 ret = real_query(q);
2248 free(ebn);
2249 free(q);
2250 if (ret < 0)
2251 goto out;
2252 }
2253 ret = 1;
2254 out:
2255 if (name)
2256 free(name);
2257 if (meta)
2258 free(meta);
2259 if (atts)
2260 free(atts);
2261 return ret;
2262 }
2263
2264 /*
2265 * verify / clean
2266 */
2267 static int com_vrfy(int fd, int argc, __a_unused char *argv[])
2268 {
2269 char *query;
2270 int ret, vrfy_mode = strcmp(argv[0], "clean");
2271 void *result = NULL;
2272 unsigned int num_rows;
2273 MYSQL_ROW row;
2274 char *escaped_name;
2275
2276 if (argc != 1)
2277 return -E_MYSQL_SYNTAX;
2278 ret = -E_NORESULT;
2279 result = get_result("select data.name from data left join dir on "
2280 "dir.name = data.name where dir.name is NULL");
2281 if (!result)
2282 goto out;
2283 num_rows = mysql_num_rows(result);
2284 if (!num_rows) {
2285 ret = send_buffer(fd, "No invalid entries\n");
2286 goto out;
2287 }
2288 if (vrfy_mode) {
2289 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2290 num_rows == 1? "y" : "ies");
2291 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2292 goto out;
2293 }
2294 while ((row = mysql_fetch_row(result))) {
2295 ret = -E_NOROW;
2296 if (!row[0])
2297 goto out;
2298 ret = -E_ESCAPE;
2299 escaped_name = escape_str(row[0]);
2300 if (!escaped_name)
2301 goto out;
2302 send_va_buffer(fd, "deleting %s\n", escaped_name);
2303 query = make_message("delete from data where name = '%s'",
2304 escaped_name);
2305 ret = real_query(query);
2306 free(query);
2307 if (ret < 0)
2308 goto out;
2309 }
2310
2311 out:
2312 if (result)
2313 mysql_free_result(result);
2314 return ret;
2315 }
2316
2317 static FILE *out_file;
2318
2319 static int mysql_write_tmp_file(const char *dir, const char *name)
2320 {
2321 int ret = -E_TMPFILE;
2322 char *msg = make_message("%s\t%s\n", dir, name);
2323 if (fputs(msg, out_file) != EOF)
2324 ret = 1;
2325 free(msg);
2326 return ret;
2327 }
2328
2329 /*
2330 * update database
2331 */
2332 static int com_upd(int fd, int argc, __a_unused char *argv[])
2333 {
2334 char *tempname = NULL, *query = NULL;
2335 int ret, out_fd = -1, num = 0;
2336 void *result = NULL;
2337 unsigned int num_rows;
2338 MYSQL_ROW row;
2339
2340 if (argc != 1)
2341 return -E_MYSQL_SYNTAX;
2342 out_file = NULL;
2343 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2344 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2345 if (ret < 0)
2346 goto out;
2347 out_fd = ret;
2348 out_file = fdopen(out_fd, "w");
2349 if (!out_file) {
2350 close(out_fd);
2351 goto out;
2352 }
2353 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2354 goto out;
2355 num = ftell(out_file);
2356 /*
2357 * we have to make sure the file hit the disk before we call
2358 * real_query
2359 */
2360 fclose(out_file);
2361 out_file = NULL;
2362 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2363 if (!num)
2364 goto out;
2365 if ((ret = real_query("delete from dir")) < 0)
2366 goto out;
2367 query = make_message("load data infile '%s' ignore into table dir "
2368 "fields terminated by '\t' lines terminated by '\n' "
2369 "(dir, name)", tempname);
2370 ret = real_query(query);
2371 free(query);
2372 if (ret < 0)
2373 goto out;
2374 result = get_result("select dir.name from dir left join data on "
2375 "data.name = dir.name where data.name is NULL");
2376 ret = -E_NORESULT;
2377 if (!result)
2378 goto out;
2379 num_rows = mysql_num_rows(result);
2380 if (!num_rows) {
2381 ret = send_buffer(fd, "no new entries\n");
2382 goto out;
2383 }
2384 while ((row = mysql_fetch_row(result))) {
2385 char *erow;
2386 ret = -E_NOROW;
2387 if (!row[0])
2388 goto out;
2389 send_va_buffer(fd, "new entry: %s\n", row[0]);
2390 erow = escape_str(row[0]);
2391 query = make_message("insert into data (name, pic_id) values "
2392 "('%s','%s')", erow, "1");
2393 free(erow);
2394 ret = real_query(query);
2395 free(query);
2396 if (ret < 0)
2397 goto out;
2398 }
2399 ret = 1;
2400 out:
2401 if (out_fd >= 0)
2402 unlink(tempname);
2403 free(tempname);
2404 if (out_file)
2405 fclose(out_file);
2406 if (result)
2407 mysql_free_result(result);
2408 return ret;
2409 }
2410
2411 static char **server_get_audio_file_list(unsigned int num)
2412 {
2413 char **list = para_malloc((num + 1) * sizeof(char *));
2414 char *tmp, *query, *stream = get_current_stream();
2415 void *result = NULL;
2416 unsigned int num_rows;
2417 int i = 0;
2418 MYSQL_ROW row;
2419
2420 tmp = get_query(stream, NULL, 1);
2421 free(stream);
2422 query = make_message("%s limit %d", tmp, num);
2423 free(tmp);
2424 result = get_result(query);
2425 free(query);
2426 if (!result)
2427 goto err_out;
2428 num_rows = mysql_num_rows(result);
2429 if (!num_rows)
2430 goto err_out;
2431 for (i = 0; i < num_rows && i < num; i++) {
2432 row = mysql_fetch_row(result);
2433 if (!row || !row[0])
2434 goto err_out;
2435 list[i] = para_strdup(row[0]);
2436 }
2437 list[i] = NULL;
2438 goto success;
2439 err_out:
2440 while (i > 0) {
2441 i--;
2442 free(list[i]);
2443 }
2444 free(list);
2445 list = NULL;
2446 success:
2447 if (result)
2448 mysql_free_result(result);
2449 return list;
2450 }
2451
2452 /*
2453 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2454 * on errors. Called from parent on startup and also from com_cdb().
2455 */
2456 static int init_mysql_server(void)
2457 {
2458 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2459
2460 mysql_ptr = mysql_init(NULL);
2461 if (!mysql_ptr) {
2462 PARA_CRIT_LOG("%s", "mysql init error\n");
2463 return -E_NOTCONN;
2464 }
2465 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2466 conf.mysql_port_arg);
2467 if (!conf.mysql_user_arg)
2468 free(u);
2469 /*
2470 * If host is NULL a connection to the local host is assumed,
2471 * If user is NULL, the current user is assumed
2472 */
2473 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2474 conf.mysql_host_arg,
2475 conf.mysql_user_arg,
2476 conf.mysql_passwd_arg,
2477 conf.mysql_database_arg,
2478 conf.mysql_port_arg, NULL, 0))) {
2479 PARA_CRIT_LOG("%s", "connect error\n");
2480 return -E_NOTCONN;
2481 }
2482 PARA_INFO_LOG("%s", "success\n");
2483 return 1;
2484 }
2485
2486 /* mmd lock must be held */
2487 static void write_msg2mmd(int success)
2488 {
2489 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2490 success < 0? PARA_STRERROR(-success) :
2491 "successfully connected to mysql server",
2492 success < 0? "" : mysql_get_server_info(mysql_ptr));
2493 }
2494
2495 /* create database */
2496 static int com_cdb(int fd, int argc, char *argv[])
2497 {
2498 char *query;
2499 int ret;
2500
2501 if (mysql_ptr) {
2502 PARA_INFO_LOG("%s", "closing database\n");
2503 mysql_close(mysql_ptr);
2504 }
2505 /* dont use any database */
2506 conf.mysql_database_arg = NULL; /* leak? */
2507 ret = -E_MYSQL_INIT;
2508 if (init_mysql_server() < 0 || !mysql_ptr)
2509 goto out;
2510 if (argc < 2)
2511 conf.mysql_database_arg = para_strdup("paraslash");
2512 else
2513 conf.mysql_database_arg = escape_str(argv[1]);
2514 query = make_message("create database %s", conf.mysql_database_arg);
2515 ret = real_query(query);
2516 free(query);
2517 if (ret < 0)
2518 goto out;
2519 /* reconnect with database just created */
2520 mysql_close(mysql_ptr);
2521 ret = -E_MYSQL_INIT;
2522 if (init_mysql_server() < 0 || !mysql_ptr)
2523 goto out;
2524 mmd_lock();
2525 write_msg2mmd(1);
2526 mmd_unlock();
2527 ret = -E_QFAILED;
2528 if (real_query("create table data (name varchar(255) binary not null "
2529 "primary key, "
2530 "lastplayed datetime not null default "
2531 "'1970-01-01', "
2532 "numplayed int not null default 0, "
2533 "pic_id bigint unsigned not null default 1)") < 0)
2534 goto out;
2535 if (real_query("create table dir (name varchar(255) binary not null "
2536 "primary key, dir varchar(255) default null)") < 0)
2537 goto out;
2538 if (real_query("create table pics ("
2539 "id bigint(20) unsigned not null primary key "
2540 "auto_increment, "
2541 "name varchar(255) binary not null, "
2542 "pic mediumblob not null)") < 0)
2543 goto out;
2544 if (real_query("create table streams ("
2545 "name varchar(255) binary not null primary key, "
2546 "def blob not null)") < 0)
2547 goto out;
2548 if (real_query("insert into streams (name, def) values "
2549 "('current_stream', '(none)')") < 0)
2550 goto out;
2551 ret = send_va_buffer(fd, "successfully created database %s\n",
2552 conf.mysql_database_arg);
2553 out:
2554 return ret;
2555 }
2556
2557 static void shutdown_connection(void)
2558 {
2559 if (mysql_ptr) {
2560 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2561 mysql_close(mysql_ptr);
2562 mysql_ptr = NULL;
2563 }
2564 }
2565
2566 /**
2567 * the init function of the mysql-based audio file selector
2568 *
2569 * Check the command line options and initialize all function pointers of \a db.
2570 * Connect to the mysql server and initialize the info string.
2571 *
2572 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2573 * random_selector.c
2574 */
2575 int mysql_selector_init(struct audio_file_selector *db)
2576 {
2577 int ret;
2578
2579 if (!conf.mysql_passwd_given)
2580 return -E_NO_MYSQL_PASSWD;
2581 if (!conf.mysql_audio_file_dir_given)
2582 return -E_NO_AF_DIR;
2583 db->name = "mysql";
2584 db->cmd_list = cmds;
2585 db->get_audio_file_list = server_get_audio_file_list;
2586 db->update_audio_file = update_audio_file_server_handler;
2587 db->shutdown = shutdown_connection;
2588 ret = init_mysql_server();
2589 if (ret < 0)
2590 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2591 write_msg2mmd(ret);
2592 return 1; /* return success even if connect failed to give the
2593 * user the chance to exec com_cdb
2594 */
2595 }