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