Merge ../paraslash.fml/paraslash
[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 update_dir;
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 update_dir:
1526 ret = 1;
1527 dn = para_dirname(argv[2]);
1528 if (!dn)
1529 goto out;
1530 ret = -E_ESCAPE;
1531 edn = escape_str(dn);
1532 free(dn);
1533 if (!edn)
1534 goto out;
1535 ret = 1;
1536 if (!*edn)
1537 goto out;
1538 q = make_message("update dir set dir = '%s' where name = '%s'",
1539 edn, ebn2);
1540 ret = real_query(q);
1541 free(q);
1542 out:
1543 free(edn);
1544 free(ebn1);
1545 free(ebn2);
1546 return ret;
1547 }
1548
1549 /*
1550 * picass: associate pic to audio file
1551 * snp: set numplayed
1552 */
1553 static int com_set(__a_unused int fd, int argc, char *argv[])
1554 {
1555 char *q, *ebn;
1556 long unsigned id;
1557 int i, ret;
1558 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1559
1560 if (argc < 3)
1561 return -E_MYSQL_SYNTAX;
1562 id = atol(argv[1]);
1563 for (i = 2; i < argc; i++) {
1564 ebn = escaped_basename(argv[i]);
1565 if (!ebn)
1566 return -E_ESCAPE;
1567 q = make_message("update data set %s = %lu "
1568 "where name = '%s'", field, id, ebn);
1569 free(ebn);
1570 ret = real_query(q);
1571 free(q);
1572 if (ret < 0)
1573 return ret;
1574 }
1575 return 1;
1576 }
1577
1578 /*
1579 * picch: change entry's name in pics table
1580 */
1581 static int com_picch(__a_unused int fd, int argc, char *argv[])
1582 {
1583 int ret;
1584 long unsigned id;
1585 char *q, *tmp;
1586
1587 if (argc != 3)
1588 return -E_MYSQL_SYNTAX;
1589 id = atol(argv[1]);
1590 ret = -E_ESCAPE;
1591 tmp = escape_str(argv[2]);
1592 if (!tmp)
1593 return -E_ESCAPE;
1594 q = make_message("update pics set name = '%s' where id = %lu", tmp, id);
1595 free(tmp);
1596 ret = real_query(q);
1597 free(q);
1598 return ret;
1599 }
1600
1601 /*
1602 * piclist: print list of pics in db
1603 */
1604 static int com_piclist(__a_unused int fd, int argc, __a_unused char *argv[])
1605 {
1606 void *result = NULL;
1607 MYSQL_ROW row;
1608 unsigned long *length;
1609 int ret;
1610
1611 if (argc != 1)
1612 return -E_MYSQL_SYNTAX;
1613 result = get_result("select id,name,pic from pics order by id");
1614 if (!result)
1615 return -E_NORESULT;
1616 while ((row = mysql_fetch_row(result))) {
1617 length = mysql_fetch_lengths(result);
1618 if (!row || !row[0] || !row[1] || !row[2])
1619 continue;
1620 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1621 if (ret < 0)
1622 goto out;
1623 }
1624 ret = 1;
1625 out:
1626 mysql_free_result(result);
1627 return ret;
1628 }
1629
1630 /*
1631 * picdel: delete picture from database
1632 */
1633 static int com_picdel(int fd, int argc, char *argv[])
1634 {
1635 char *q;
1636 long unsigned id;
1637 my_ulonglong aff;
1638 int i, ret;
1639
1640 if (argc < 2)
1641 return -E_MYSQL_SYNTAX;
1642 for (i = 1; i < argc; i++) {
1643 id = atol(argv[i]);
1644 q = make_message("delete from pics where id = %lu", id);
1645 ret = real_query(q);
1646 free(q);
1647 if (ret < 0)
1648 return ret;
1649 aff = mysql_affected_rows(mysql_ptr);
1650 if (!aff) {
1651 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1652 if (ret < 0)
1653 return ret;
1654 continue;
1655 }
1656 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1657 ret = real_query(q);
1658 free(q);
1659 }
1660 return 1;
1661 }
1662 /*
1663 * pic: get picture by name or by number
1664 */
1665 static int com_pic(int fd, int argc, char *argv[])
1666 {
1667 void *result = NULL;
1668 MYSQL_ROW row;
1669 unsigned long *length, id;
1670 int ret;
1671 char *q, *name = NULL;
1672
1673 if (argc < 2) {
1674 ret = -E_GET_AUDIO_FILE;
1675 name = get_current_audio_file();
1676 } else {
1677 ret = -E_ESCAPE;
1678 name = escaped_basename(argv[1]);
1679 }
1680 if (!name)
1681 return ret;
1682 if (*name == '#')
1683 id = atoi(name + 1);
1684 else
1685 id = get_pic_id_by_name(name);
1686 free(name);
1687 if (id <= 0)
1688 return id;
1689 q = make_message("select pic from pics where id = '%lu'", id);
1690 result = get_result(q);
1691 free(q);
1692 if (!result)
1693 return -E_NORESULT;
1694 row = mysql_fetch_row(result);
1695 ret = -E_NOROW;
1696 if (!row || !row[0])
1697 goto out;
1698 length = mysql_fetch_lengths(result);
1699 ret = send_bin_buffer(fd, row[0], *length);
1700 out:
1701 mysql_free_result(result);
1702 return ret;
1703 }
1704
1705 /* strdel */
1706 static int com_strdel(__a_unused int fd, int argc, char *argv[])
1707 {
1708 char *q, *tmp;
1709 int ret;
1710
1711 if (argc < 2)
1712 return -E_MYSQL_SYNTAX;
1713 tmp = escape_str(argv[1]);
1714 if (!tmp)
1715 return -E_ESCAPE;
1716 q = make_message("delete from streams where name='%s'", tmp);
1717 free(tmp);
1718 ret = real_query(q);
1719 free(q);
1720 return ret;
1721 }
1722
1723 /*
1724 * ls
1725 */
1726 static int com_ls(int fd, int argc, char *argv[])
1727 {
1728 char *q;
1729 void *result;
1730 int ret;
1731 unsigned int num_rows;
1732
1733 if (argc > 2)
1734 return -E_MYSQL_SYNTAX;
1735 if (argc > 1) {
1736 char *tmp = escape_str(argv[1]);
1737 if (!tmp)
1738 return -E_ESCAPE;
1739 q = make_message("select name from data where name like '%s'",
1740 tmp);
1741 free(tmp);
1742 } else
1743 q = para_strdup("select name from data");
1744 result = get_result(q);
1745 free(q);
1746 if (!result)
1747 return -E_NORESULT;
1748 num_rows = mysql_num_rows(result);
1749 ret = 1;
1750 if (num_rows)
1751 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1752 mysql_free_result(result);
1753 return ret;
1754 }
1755
1756 /*
1757 * summary
1758 */
1759 static int com_summary(__a_unused int fd, int argc, __a_unused char *argv[])
1760 {
1761 MYSQL_ROW row;
1762 MYSQL_ROW row2;
1763 void *result;
1764 void *result2 = NULL;
1765 const char *fmt = "select count(name) from data where %s='1'";
1766 int ret = -E_NORESULT;
1767
1768 if (argc != 1)
1769 return -E_MYSQL_SYNTAX;
1770 result = get_all_attributes();
1771 if (!result)
1772 goto out;
1773 while ((row = mysql_fetch_row(result))) {
1774 char *buf;
1775
1776 ret = -E_NOROW;
1777 if (!row[0])
1778 goto out;
1779 ret = -E_NORESULT;
1780 buf = make_message(fmt, row[0]);
1781 result2 = get_result(buf);
1782 free(buf);
1783 if (!result2)
1784 goto out;
1785 ret = -E_NOROW;
1786 row2 = mysql_fetch_row(result2);
1787 if (!row2 || !row2[0])
1788 goto out;
1789 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1790 if (ret < 0)
1791 goto out;
1792 }
1793 ret = 1;
1794 out:
1795 if (result2)
1796 mysql_free_result(result2);
1797 if (result)
1798 mysql_free_result(result);
1799 return ret;
1800 }
1801
1802 static int get_numplayed(char *name)
1803 {
1804 void *result;
1805 MYSQL_ROW row;
1806 const char *fmt = "select numplayed from data where name = '%s'";
1807 char *buf = make_message(fmt, name);
1808 int ret = -E_NORESULT;
1809
1810 result = get_result(buf);
1811 free(buf);
1812 if (!result)
1813 goto out;
1814 ret = -E_NOROW;
1815 row = mysql_fetch_row(result);
1816 if (!row || !row[0])
1817 goto out;
1818 ret = atoi(row[0]);
1819 out:
1820 if (result)
1821 mysql_free_result(result);
1822 return ret;
1823 }
1824
1825 static int update_audio_file(char *name)
1826 {
1827 int ret;
1828 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1829 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1830 char *q;
1831 char *ebn = escaped_basename(name);
1832
1833 ret = -E_ESCAPE;
1834 if (!ebn)
1835 goto out;
1836 q = make_message(fmt1, ebn);
1837 ret = real_query(q);
1838 free(q);
1839 if (ret < 0)
1840 goto out;
1841 ret = get_numplayed(ebn);
1842 if (ret < 0)
1843 goto out;
1844 q = make_message(fmt2, ret + 1, ebn);
1845 ret = real_query(q);
1846 free(q);
1847 out:
1848 free(ebn);
1849 return ret;
1850 }
1851
1852 /* If called as child, mmd_lock must be held */
1853 static void update_mmd(char *info)
1854 {
1855 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1856 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1857 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1858 }
1859
1860 static void update_audio_file_server_handler(char *name)
1861 {
1862 char *info;
1863 info = get_selector_info(name);
1864 update_mmd(info);
1865 free(info);
1866 update_audio_file(name);
1867 }
1868
1869 static int com_us(__a_unused int fd, int argc, char *argv[])
1870 {
1871 char *tmp;
1872 int ret;
1873
1874 if (argc != 2)
1875 return -E_MYSQL_SYNTAX;
1876 tmp = escape_str(argv[1]);
1877 if (!tmp)
1878 return -E_ESCAPE;
1879 ret = update_audio_file(argv[1]);
1880 free(tmp);
1881 return ret;
1882 }
1883
1884 static void refresh_selector_info(void)
1885 {
1886 char *name = get_current_audio_file();
1887 char *info;
1888
1889 if (!name)
1890 return;
1891 info = get_selector_info(name);
1892 free(name);
1893 mmd_lock();
1894 update_mmd(info);
1895 mmd_unlock();
1896 free(info);
1897 }
1898
1899 /* select previous/next stream */
1900 static int com_ps(__a_unused int fd, int argc, char *argv[])
1901 {
1902 char *query, *stream = get_current_stream();
1903 void *result = get_result("select name from streams");
1904 MYSQL_ROW row;
1905 int match = -1, ret, i;
1906 unsigned int num_rows;
1907
1908 if (argc != 1)
1909 return -E_MYSQL_SYNTAX;
1910 ret = -E_NORESULT;
1911 if (!result)
1912 goto out;
1913 num_rows = mysql_num_rows(result);
1914 ret = -E_EMPTY_RESULT;
1915 if (num_rows < 2)
1916 goto out;
1917 ret = -E_NOROW;
1918 for (i = 0; i < num_rows; i++) {
1919 row = mysql_fetch_row(result);
1920 if (!row || !row[0])
1921 goto out;
1922 if (!strcmp(row[0], "current_stream"))
1923 continue;
1924 if (!strcmp(row[0], stream)) {
1925 match = i;
1926 break;
1927 }
1928 }
1929 ret = -E_NO_STREAM;
1930 if (match < 0)
1931 goto out;
1932 if (!strcmp(argv[0], "ps"))
1933 i = match > 0? match - 1 : num_rows - 1;
1934 else
1935 i = match < num_rows - 1? match + 1 : 0;
1936 ret = -E_NOROW;
1937 mysql_data_seek(result, i);
1938 row = mysql_fetch_row(result);
1939 if (!row || !row[0])
1940 goto out;
1941 if (!strcmp(row[0], "current_stream")) {
1942 if (!strcmp(argv[0], "ps")) {
1943 i = match - 2;
1944 i = i < 0? i + num_rows : i;
1945 } else {
1946 i = match + 2;
1947 i = i > num_rows - 1? i - num_rows : i;
1948 }
1949 mysql_data_seek(result, i);
1950 row = mysql_fetch_row(result);
1951 if (!row || !row[0])
1952 goto out;
1953 }
1954 query = make_message("update streams set def='%s' where name = "
1955 "'current_stream'", row[0]);
1956 ret = real_query(query);
1957 free(query);
1958 refresh_selector_info();
1959 out:
1960 free(stream);
1961 if (result)
1962 mysql_free_result(result);
1963 return ret;
1964 }
1965
1966 /* streams */
1967 static int com_streams(int fd, int argc, __a_unused char *argv[])
1968 {
1969 unsigned int num_rows;
1970 int i, ret = -E_NORESULT;
1971 void *result;
1972 MYSQL_ROW row;
1973
1974 if (argc > 1 && strcmp(argv[1], "current_stream"))
1975 return -E_MYSQL_SYNTAX;
1976 if (argc > 1) {
1977 char *cs = get_current_stream();
1978 ret = send_va_buffer(fd, "%s\n", cs);
1979 free(cs);
1980 return ret;
1981 }
1982 result = get_result("select name from streams");
1983 if (!result)
1984 goto out;
1985 num_rows = mysql_num_rows(result);
1986 ret = 1;
1987 if (!num_rows)
1988 goto out;
1989 ret = -E_NOROW;
1990 for (i = 0; i < num_rows; i++) {
1991 row = mysql_fetch_row(result);
1992 if (!row || !row[0])
1993 goto out;
1994 if (strcmp(row[0], "current_stream"))
1995 send_va_buffer(fd, "%s\n", row[0]);
1996 }
1997 ret = 1;
1998 out:
1999 if (result)
2000 mysql_free_result(result);
2001 return ret;
2002 }
2003
2004 /* query stream definition */
2005 static int com_strq(int fd, int argc, char *argv[])
2006 {
2007 MYSQL_ROW row;
2008 char *query, *name;
2009 void *result;
2010 int ret;
2011
2012 if (argc < 2) {
2013 ret = -E_GET_STREAM;
2014 name = get_current_stream();
2015 } else {
2016 ret = -E_ESCAPE;
2017 name = escaped_basename(argv[1]);
2018 }
2019 if (!name)
2020 return ret;
2021 ret = -E_NORESULT;
2022 query = make_message("select def from streams where name='%s'", name);
2023 free(name);
2024 result = get_result(query);
2025 free(query);
2026 if (!result)
2027 goto out;
2028 ret = -E_NOROW;
2029 row = mysql_fetch_row(result);
2030 if (!row || !row[0])
2031 goto out;
2032 /* no '\n' needed */
2033 ret = send_buffer(fd, row[0]);
2034 out:
2035 if (result)
2036 mysql_free_result(result);
2037 return ret;
2038 }
2039
2040 /* change stream / change stream and play */
2041 static int com_cs(int fd, int argc, char *argv[])
2042 {
2043 int ret, stream_change;
2044 char *query, *stream = NULL;
2045 char *old_stream = get_current_stream();
2046 int csp = !strcmp(argv[0], "csp");
2047
2048 ret = -E_MYSQL_SYNTAX;
2049 if (argc > 2)
2050 goto out;
2051 if (argc == 1) {
2052 if (csp)
2053 goto out;
2054 ret = send_va_buffer(fd, "%s\n", old_stream);
2055 goto out;
2056 }
2057 ret = -E_GET_QUERY;
2058 /* test if stream is valid, no need to escape argv[1] */
2059 query = get_query(argv[1], NULL, 0);
2060 if (!query)
2061 goto out;
2062 free(query);
2063 /* stream is ok */
2064 stream = escape_str(argv[1]);
2065 if (!stream)
2066 goto out;
2067 stream_change = strcmp(stream, old_stream);
2068 if (stream_change) {
2069 ret = change_stream(stream);
2070 if (ret < 0)
2071 goto out;
2072 refresh_selector_info();
2073 }
2074 if (csp) {
2075 mmd_lock();
2076 mmd->new_afs_status_flags |= AFS_PLAYING;
2077 if (stream_change)
2078 mmd->new_afs_status_flags |= AFS_NEXT;
2079 mmd_unlock();
2080 }
2081 ret = 1;
2082 out:
2083 free(old_stream);
2084 free(stream);
2085 return ret;
2086 }
2087
2088 /*
2089 * sl/skip
2090 */
2091 static int com_sl(int fd, int argc, char *argv[])
2092 {
2093 void *result = NULL;
2094 MYSQL_ROW row;
2095 int ret, i, skip = !strcmp(argv[0], "skip");
2096 char *query, *stream, *tmp;
2097 unsigned int num_rows, num;
2098
2099 if (argc < 2)
2100 return -E_MYSQL_SYNTAX;
2101 num = atoi(argv[1]);
2102 if (!num)
2103 return -E_MYSQL_SYNTAX;
2104 if (argc == 2) {
2105 stream = get_current_stream();
2106 if (!stream)
2107 return -E_GET_STREAM;
2108 } else {
2109 stream = escape_str(argv[2]);
2110 if (!stream)
2111 return -E_ESCAPE;
2112 }
2113 tmp = get_query(stream, NULL, 0);
2114 free(stream);
2115 if (!tmp)
2116 return -E_GET_QUERY;
2117 query = make_message("%s limit %d", tmp, num);
2118 free(tmp);
2119 ret = -E_NORESULT;
2120 result = get_result(query);
2121 free(query);
2122 if (!result)
2123 goto out;
2124 ret = -E_EMPTY_RESULT;
2125 num_rows = mysql_num_rows(result);
2126 if (!num_rows)
2127 goto out;
2128 for (i = 0; i < num_rows && i < num; i++) {
2129 row = mysql_fetch_row(result);
2130 if (skip) {
2131 send_va_buffer(fd, "Skipping %s\n", row[0]);
2132 update_audio_file(row[0]);
2133 } else
2134 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2135 }
2136 ret = 1;
2137 out:
2138 if (result)
2139 mysql_free_result(result);
2140 return ret;
2141 }
2142
2143 /*
2144 * update attributes of name
2145 */
2146 static int update_atts(int fd, char *name, char *atts)
2147 {
2148 int ret;
2149 char *ebn, *q, *old, *new = NULL;
2150
2151 if (!mysql_ptr)
2152 return -E_NOTCONN;
2153 ebn = escaped_basename(name);
2154 if (!ebn)
2155 return -E_ESCAPE;
2156 q = make_message("update data set %s where name = '%s'", atts, ebn);
2157 old = get_atts(ebn, 0);
2158 send_va_buffer(fd, "old: %s\n", old);
2159 free(old);
2160 ret = real_query(q);
2161 free(q);
2162 if (ret < 0)
2163 goto out;
2164 new = get_atts(ebn, 0);
2165 ret = send_va_buffer(fd, "new: %s\n", new);
2166 free(new);
2167 out:
2168 free(ebn);
2169 return ret;
2170 }
2171
2172 /*
2173 * set attributes
2174 */
2175 static int com_sa(int fd, int argc, char *argv[])
2176 {
2177 int i, ret;
2178 char *atts = NULL, *name;
2179
2180 if (argc < 2)
2181 return -E_MYSQL_SYNTAX;
2182 for (i = 1; i < argc; i++) {
2183 int unset = 0;
2184 char *esc, *tmp, *p =argv[i];
2185 int len = strlen(p);
2186
2187 if (!len)
2188 continue;
2189 switch (p[len - 1]) {
2190 case '+':
2191 unset = 0;
2192 break;
2193 case '-':
2194 unset = 1;
2195 break;
2196 default:
2197 goto no_more_atts;
2198 }
2199 p[len - 1] = '\0';
2200 esc = escape_str(p);
2201 if (!esc)
2202 return -E_ESCAPE;
2203 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2204 unset? "0" : "1");
2205 free(esc);
2206 atts = para_strcat(atts, tmp);
2207 free(tmp);
2208 }
2209 no_more_atts:
2210 if (!atts)
2211 return -E_NOATTS;
2212 if (i >= argc) { /* no name given, use current af */
2213 ret = -E_GET_AUDIO_FILE;
2214 if (!(name = get_current_audio_file()))
2215 goto out;
2216 ret = update_atts(fd, name, atts);
2217 free(name);
2218 } else {
2219 ret = 1;
2220 for (; argv[i] && ret >= 0; i++)
2221 ret = update_atts(fd, argv[i], atts);
2222 }
2223 refresh_selector_info();
2224 out:
2225 free(atts);
2226 return ret;
2227 }
2228
2229 /*
2230 * copy attributes
2231 */
2232 static int com_cam(int fd, int argc, char *argv[])
2233 {
2234 char *name = NULL, *meta = NULL, *atts = NULL;
2235 int i, ret;
2236
2237 if (argc < 3)
2238 return -E_MYSQL_SYNTAX;
2239 if (!(name = escaped_basename(argv[1])))
2240 return -E_ESCAPE;
2241 ret = -E_NOATTS;
2242 if (!(atts = get_atts(name, 1)))
2243 goto out;
2244 ret = -E_META;
2245 if (!(meta = get_meta(name, 0)))
2246 goto out;
2247 for (i = 2; i < argc; i++) {
2248 char *ebn, *q;
2249 ret = -E_ESCAPE;
2250 if (!(ebn = escaped_basename(argv[i])))
2251 goto out;
2252 ret = send_va_buffer(fd, "updating %s\n", ebn);
2253 if (ret < 0) {
2254 free(ebn);
2255 goto out;
2256 }
2257 q = make_message("update data set %s where name = '%s'",
2258 meta, ebn);
2259 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2260 ret = real_query(q);
2261 free(ebn);
2262 free(q);
2263 if (ret < 0)
2264 goto out;
2265 }
2266 ret = 1;
2267 out:
2268 if (name)
2269 free(name);
2270 if (meta)
2271 free(meta);
2272 if (atts)
2273 free(atts);
2274 return ret;
2275 }
2276
2277 /*
2278 * verify / clean
2279 */
2280 static int com_vrfy(int fd, int argc, __a_unused char *argv[])
2281 {
2282 char *query;
2283 int ret, vrfy_mode = strcmp(argv[0], "clean");
2284 void *result = NULL;
2285 unsigned int num_rows;
2286 MYSQL_ROW row;
2287 char *escaped_name;
2288
2289 if (argc != 1)
2290 return -E_MYSQL_SYNTAX;
2291 ret = -E_NORESULT;
2292 result = get_result("select data.name from data left join dir on "
2293 "dir.name = data.name where dir.name is NULL");
2294 if (!result)
2295 goto out;
2296 num_rows = mysql_num_rows(result);
2297 if (!num_rows) {
2298 ret = send_buffer(fd, "No invalid entries\n");
2299 goto out;
2300 }
2301 if (vrfy_mode) {
2302 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2303 num_rows == 1? "y" : "ies");
2304 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2305 goto out;
2306 }
2307 while ((row = mysql_fetch_row(result))) {
2308 ret = -E_NOROW;
2309 if (!row[0])
2310 goto out;
2311 ret = -E_ESCAPE;
2312 escaped_name = escape_str(row[0]);
2313 if (!escaped_name)
2314 goto out;
2315 send_va_buffer(fd, "deleting %s\n", escaped_name);
2316 query = make_message("delete from data where name = '%s'",
2317 escaped_name);
2318 ret = real_query(query);
2319 free(query);
2320 if (ret < 0)
2321 goto out;
2322 }
2323
2324 out:
2325 if (result)
2326 mysql_free_result(result);
2327 return ret;
2328 }
2329
2330 static FILE *out_file;
2331
2332 static int mysql_write_tmp_file(const char *dir, const char *name)
2333 {
2334 int ret = -E_TMPFILE;
2335 char *msg = make_message("%s\t%s\n", dir, name);
2336 if (fputs(msg, out_file) != EOF)
2337 ret = 1;
2338 free(msg);
2339 return ret;
2340 }
2341
2342 /*
2343 * update database
2344 */
2345 static int com_upd(int fd, int argc, __a_unused char *argv[])
2346 {
2347 char *tempname = NULL, *query = NULL;
2348 int ret, out_fd = -1, num = 0;
2349 void *result = NULL;
2350 unsigned int num_rows;
2351 MYSQL_ROW row;
2352
2353 if (argc != 1)
2354 return -E_MYSQL_SYNTAX;
2355 out_file = NULL;
2356 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2357 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2358 if (ret < 0)
2359 goto out;
2360 out_fd = ret;
2361 out_file = fdopen(out_fd, "w");
2362 if (!out_file) {
2363 close(out_fd);
2364 goto out;
2365 }
2366 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2367 goto out;
2368 num = ftell(out_file);
2369 /*
2370 * we have to make sure the file hit the disk before we call
2371 * real_query
2372 */
2373 fclose(out_file);
2374 out_file = NULL;
2375 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2376 if (!num)
2377 goto out;
2378 if ((ret = real_query("delete from dir")) < 0)
2379 goto out;
2380 query = make_message("load data infile '%s' ignore into table dir "
2381 "fields terminated by '\t' lines terminated by '\n' "
2382 "(dir, name)", tempname);
2383 ret = real_query(query);
2384 free(query);
2385 if (ret < 0)
2386 goto out;
2387 result = get_result("select dir.name from dir left join data on "
2388 "data.name = dir.name where data.name is NULL");
2389 ret = -E_NORESULT;
2390 if (!result)
2391 goto out;
2392 num_rows = mysql_num_rows(result);
2393 if (!num_rows) {
2394 ret = send_buffer(fd, "no new entries\n");
2395 goto out;
2396 }
2397 while ((row = mysql_fetch_row(result))) {
2398 char *erow;
2399 ret = -E_NOROW;
2400 if (!row[0])
2401 goto out;
2402 send_va_buffer(fd, "new entry: %s\n", row[0]);
2403 erow = escape_str(row[0]);
2404 if (!erow)
2405 goto out;
2406 query = make_message("insert into data (name, pic_id) values "
2407 "('%s','%s')", erow, "1");
2408 free(erow);
2409 ret = real_query(query);
2410 free(query);
2411 if (ret < 0)
2412 goto out;
2413 }
2414 ret = 1;
2415 out:
2416 if (out_fd >= 0)
2417 unlink(tempname);
2418 free(tempname);
2419 if (out_file)
2420 fclose(out_file);
2421 if (result)
2422 mysql_free_result(result);
2423 return ret;
2424 }
2425
2426 static char **server_get_audio_file_list(unsigned int num)
2427 {
2428 char **list = para_malloc((num + 1) * sizeof(char *));
2429 char *tmp, *query, *stream = get_current_stream();
2430 void *result = NULL;
2431 unsigned int num_rows;
2432 int i = 0;
2433 MYSQL_ROW row;
2434
2435 tmp = get_query(stream, NULL, 1);
2436 free(stream);
2437 if (!tmp)
2438 goto err_out;
2439 query = make_message("%s limit %d", tmp, num);
2440 free(tmp);
2441 result = get_result(query);
2442 free(query);
2443 if (!result)
2444 goto err_out;
2445 num_rows = mysql_num_rows(result);
2446 if (!num_rows)
2447 goto err_out;
2448 for (i = 0; i < num_rows && i < num; i++) {
2449 row = mysql_fetch_row(result);
2450 if (!row || !row[0])
2451 goto err_out;
2452 list[i] = para_strdup(row[0]);
2453 }
2454 list[i] = NULL;
2455 goto success;
2456 err_out:
2457 while (i > 0) {
2458 i--;
2459 free(list[i]);
2460 }
2461 free(list);
2462 list = NULL;
2463 success:
2464 if (result)
2465 mysql_free_result(result);
2466 return list;
2467 }
2468
2469 /*
2470 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2471 * on errors. Called from parent on startup and also from com_cdb().
2472 */
2473 static int init_mysql_server(void)
2474 {
2475 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2476
2477 mysql_ptr = mysql_init(NULL);
2478 if (!mysql_ptr) {
2479 PARA_CRIT_LOG("%s", "mysql init error\n");
2480 return -E_NOTCONN;
2481 }
2482 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2483 conf.mysql_port_arg);
2484 if (!conf.mysql_user_arg)
2485 free(u);
2486 /*
2487 * If host is NULL a connection to the local host is assumed,
2488 * If user is NULL, the current user is assumed
2489 */
2490 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2491 conf.mysql_host_arg,
2492 conf.mysql_user_arg,
2493 conf.mysql_passwd_arg,
2494 conf.mysql_database_arg,
2495 conf.mysql_port_arg, NULL, 0))) {
2496 PARA_CRIT_LOG("%s", "connect error\n");
2497 return -E_NOTCONN;
2498 }
2499 PARA_INFO_LOG("%s", "success\n");
2500 return 1;
2501 }
2502
2503 /* mmd lock must be held */
2504 static void write_msg2mmd(int success)
2505 {
2506 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2507 success < 0? PARA_STRERROR(-success) :
2508 "successfully connected to mysql server",
2509 success < 0? "" : mysql_get_server_info(mysql_ptr));
2510 }
2511
2512 /* create database */
2513 static int com_cdb(int fd, int argc, char *argv[])
2514 {
2515 char *query;
2516 int ret;
2517
2518 if (mysql_ptr) {
2519 PARA_INFO_LOG("%s", "closing database\n");
2520 mysql_close(mysql_ptr);
2521 }
2522 /* dont use any database */
2523 conf.mysql_database_arg = NULL; /* leak? */
2524 ret = -E_MYSQL_INIT;
2525 if (init_mysql_server() < 0 || !mysql_ptr)
2526 goto out;
2527 if (argc < 2)
2528 conf.mysql_database_arg = para_strdup("paraslash");
2529 else {
2530 ret = -E_ESCAPE;
2531 conf.mysql_database_arg = escape_str(argv[1]);
2532 if (!conf.mysql_database_arg)
2533 goto out;
2534 }
2535 query = make_message("create database %s", conf.mysql_database_arg);
2536 ret = real_query(query);
2537 free(query);
2538 if (ret < 0)
2539 goto out;
2540 /* reconnect with database just created */
2541 mysql_close(mysql_ptr);
2542 ret = -E_MYSQL_INIT;
2543 if (init_mysql_server() < 0 || !mysql_ptr)
2544 goto out;
2545 mmd_lock();
2546 write_msg2mmd(1);
2547 mmd_unlock();
2548 ret = -E_QFAILED;
2549 if (real_query("create table data (name varchar(255) binary not null "
2550 "primary key, "
2551 "lastplayed datetime not null default "
2552 "'1970-01-01', "
2553 "numplayed int not null default 0, "
2554 "pic_id bigint unsigned not null default 1)") < 0)
2555 goto out;
2556 if (real_query("create table dir (name varchar(255) binary not null "
2557 "primary key, dir varchar(255) default null)") < 0)
2558 goto out;
2559 if (real_query("create table pics ("
2560 "id bigint(20) unsigned not null primary key "
2561 "auto_increment, "
2562 "name varchar(255) binary not null, "
2563 "pic mediumblob not null)") < 0)
2564 goto out;
2565 if (real_query("create table streams ("
2566 "name varchar(255) binary not null primary key, "
2567 "def blob not null)") < 0)
2568 goto out;
2569 if (real_query("insert into streams (name, def) values "
2570 "('current_stream', '(none)')") < 0)
2571 goto out;
2572 ret = send_va_buffer(fd, "successfully created database %s\n",
2573 conf.mysql_database_arg);
2574 out:
2575 return ret;
2576 }
2577
2578 static void shutdown_connection(void)
2579 {
2580 if (mysql_ptr) {
2581 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2582 mysql_close(mysql_ptr);
2583 mysql_ptr = NULL;
2584 }
2585 }
2586
2587 /**
2588 * the init function of the mysql-based audio file selector
2589 *
2590 * Check the command line options and initialize all function pointers of \a db.
2591 * Connect to the mysql server and initialize the info string.
2592 *
2593 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2594 * random_selector.c
2595 */
2596 int mysql_selector_init(struct audio_file_selector *db)
2597 {
2598 int ret;
2599
2600 if (!conf.mysql_passwd_given)
2601 return -E_NO_MYSQL_PASSWD;
2602 if (!conf.mysql_audio_file_dir_given)
2603 return -E_NO_AF_DIR;
2604 db->name = "mysql";
2605 db->cmd_list = cmds;
2606 db->get_audio_file_list = server_get_audio_file_list;
2607 db->update_audio_file = update_audio_file_server_handler;
2608 db->shutdown = shutdown_connection;
2609 ret = init_mysql_server();
2610 if (ret < 0)
2611 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2612 write_msg2mmd(ret);
2613 return 1; /* return success even if connect failed to give the
2614 * user the chance to exec com_cdb
2615 */
2616 }