8d043f5ae4759b21487a30916155df8275aeff3d
[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(__a_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(__a_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, __a_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(__a_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(__a_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(__a_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(__a_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(__a_unused int fd, int argc, __a_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(__a_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(__a_unused int fd, int argc, __a_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 free(ebn);
1844 return ret;
1845 }
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(__a_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(__a_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, __a_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 ret = -E_MYSQL_SYNTAX;
2044 if (argc > 2)
2045 goto out;
2046 if (argc == 1) {
2047 if (csp)
2048 goto out;
2049 ret = send_va_buffer(fd, "%s\n", old_stream);
2050 goto out;
2051 }
2052 ret = -E_GET_QUERY;
2053 /* test if stream is valid, no need to escape argv[1] */
2054 query = get_query(argv[1], NULL, 0);
2055 if (!query)
2056 goto out;
2057 free(query);
2058 /* stream is ok */
2059 stream = escape_str(argv[1]);
2060 if (!stream)
2061 goto out;
2062 stream_change = strcmp(stream, old_stream);
2063 if (stream_change) {
2064 ret = change_stream(stream);
2065 if (ret < 0)
2066 goto out;
2067 refresh_selector_info();
2068 }
2069 if (csp) {
2070 mmd_lock();
2071 mmd->new_afs_status_flags |= AFS_PLAYING;
2072 if (stream_change)
2073 mmd->new_afs_status_flags |= AFS_NEXT;
2074 mmd_unlock();
2075 }
2076 ret = 1;
2077 out:
2078 free(old_stream);
2079 free(stream);
2080 return ret;
2081 }
2082
2083 /*
2084 * sl/skip
2085 */
2086 static int com_sl(int fd, int argc, char *argv[])
2087 {
2088 void *result = NULL;
2089 MYSQL_ROW row;
2090 int ret, i, skip = !strcmp(argv[0], "skip");
2091 char *query, *stream, *tmp;
2092 unsigned int num_rows, num;
2093
2094 if (argc < 2)
2095 return -E_MYSQL_SYNTAX;
2096 num = atoi(argv[1]);
2097 if (!num)
2098 return -E_MYSQL_SYNTAX;
2099 stream = (argc == 2)? get_current_stream() : escape_str(argv[2]);
2100 tmp = get_query(stream, NULL, 0);
2101 query = make_message("%s limit %d", tmp, num);
2102 free(tmp);
2103 ret = -E_GET_QUERY;
2104 free(stream);
2105 if (!query)
2106 goto out;
2107 ret = -E_NORESULT;
2108 result = get_result(query);
2109 free(query);
2110 if (!result)
2111 goto out;
2112 ret = -E_EMPTY_RESULT;
2113 num_rows = mysql_num_rows(result);
2114 if (!num_rows)
2115 goto out;
2116 for (i = 0; i < num_rows && i < num; i++) {
2117 row = mysql_fetch_row(result);
2118 if (skip) {
2119 send_va_buffer(fd, "Skipping %s\n", row[0]);
2120 update_audio_file(row[0]);
2121 } else
2122 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2123 }
2124 ret = 1;
2125 out:
2126 if (result)
2127 mysql_free_result(result);
2128 return ret;
2129 }
2130
2131 /*
2132 * update attributes of name
2133 */
2134 static int update_atts(int fd, char *name, char *atts)
2135 {
2136 int ret;
2137 char *ebn, *q, *old, *new = NULL;
2138
2139 if (!mysql_ptr)
2140 return -E_NOTCONN;
2141 ebn = escaped_basename(name);
2142 if (!ebn)
2143 return -E_ESCAPE;
2144 q = make_message("update data set %s where name = '%s'", atts, ebn);
2145 old = get_atts(ebn, 0);
2146 send_va_buffer(fd, "old: %s\n", old);
2147 free(old);
2148 ret = real_query(q);
2149 free(q);
2150 if (ret < 0)
2151 goto out;
2152 new = get_atts(ebn, 0);
2153 ret = send_va_buffer(fd, "new: %s\n", new);
2154 free(new);
2155 out:
2156 free(ebn);
2157 return ret;
2158 }
2159
2160 /*
2161 * set attributes
2162 */
2163 static int com_sa(int fd, int argc, char *argv[])
2164 {
2165 int i, ret;
2166 char *atts = NULL, *name;
2167
2168 if (argc < 2)
2169 return -E_MYSQL_SYNTAX;
2170 for (i = 1; i < argc; i++) {
2171 int unset = 0;
2172 char *esc, *tmp, *p =argv[i];
2173 int len = strlen(p);
2174
2175 if (!len)
2176 continue;
2177 switch (p[len - 1]) {
2178 case '+':
2179 unset = 0;
2180 break;
2181 case '-':
2182 unset = 1;
2183 break;
2184 default:
2185 goto no_more_atts;
2186 }
2187 p[len - 1] = '\0';
2188 esc = escape_str(p);
2189 if (!esc)
2190 return -E_ESCAPE;
2191 tmp = make_message("%s%s='%s'", atts? "," : "", esc,
2192 unset? "0" : "1");
2193 free(esc);
2194 atts = para_strcat(atts, tmp);
2195 free(tmp);
2196 }
2197 no_more_atts:
2198 if (!atts)
2199 return -E_NOATTS;
2200 if (i >= argc) { /* no name given, use current af */
2201 ret = -E_GET_AUDIO_FILE;
2202 if (!(name = get_current_audio_file()))
2203 goto out;
2204 ret = update_atts(fd, name, atts);
2205 free(name);
2206 } else {
2207 ret = 1;
2208 for (; argv[i] && ret >= 0; i++)
2209 ret = update_atts(fd, argv[i], atts);
2210 }
2211 refresh_selector_info();
2212 out:
2213 free(atts);
2214 return ret;
2215 }
2216
2217 /*
2218 * copy attributes
2219 */
2220 static int com_cam(int fd, int argc, char *argv[])
2221 {
2222 char *name = NULL, *meta = NULL, *atts = NULL;
2223 int i, ret;
2224
2225 if (argc < 3)
2226 return -E_MYSQL_SYNTAX;
2227 if (!(name = escaped_basename(argv[1])))
2228 return -E_ESCAPE;
2229 ret = -E_NOATTS;
2230 if (!(atts = get_atts(name, 1)))
2231 goto out;
2232 ret = -E_META;
2233 if (!(meta = get_meta(name, 0)))
2234 goto out;
2235 for (i = 2; i < argc; i++) {
2236 char *ebn, *q;
2237 ret = -E_ESCAPE;
2238 if (!(ebn = escaped_basename(argv[i])))
2239 goto out;
2240 ret = send_va_buffer(fd, "updating %s\n", ebn);
2241 if (ret < 0) {
2242 free(ebn);
2243 goto out;
2244 }
2245 q = make_message("update data set %s where name = '%s'",
2246 meta, ebn);
2247 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2248 ret = real_query(q);
2249 free(ebn);
2250 free(q);
2251 if (ret < 0)
2252 goto out;
2253 }
2254 ret = 1;
2255 out:
2256 if (name)
2257 free(name);
2258 if (meta)
2259 free(meta);
2260 if (atts)
2261 free(atts);
2262 return ret;
2263 }
2264
2265 /*
2266 * verify / clean
2267 */
2268 static int com_vrfy(int fd, int argc, __a_unused char *argv[])
2269 {
2270 char *query;
2271 int ret, vrfy_mode = strcmp(argv[0], "clean");
2272 void *result = NULL;
2273 unsigned int num_rows;
2274 MYSQL_ROW row;
2275 char *escaped_name;
2276
2277 if (argc != 1)
2278 return -E_MYSQL_SYNTAX;
2279 ret = -E_NORESULT;
2280 result = get_result("select data.name from data left join dir on "
2281 "dir.name = data.name where dir.name is NULL");
2282 if (!result)
2283 goto out;
2284 num_rows = mysql_num_rows(result);
2285 if (!num_rows) {
2286 ret = send_buffer(fd, "No invalid entries\n");
2287 goto out;
2288 }
2289 if (vrfy_mode) {
2290 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2291 num_rows == 1? "y" : "ies");
2292 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2293 goto out;
2294 }
2295 while ((row = mysql_fetch_row(result))) {
2296 ret = -E_NOROW;
2297 if (!row[0])
2298 goto out;
2299 ret = -E_ESCAPE;
2300 escaped_name = escape_str(row[0]);
2301 if (!escaped_name)
2302 goto out;
2303 send_va_buffer(fd, "deleting %s\n", escaped_name);
2304 query = make_message("delete from data where name = '%s'",
2305 escaped_name);
2306 ret = real_query(query);
2307 free(query);
2308 if (ret < 0)
2309 goto out;
2310 }
2311
2312 out:
2313 if (result)
2314 mysql_free_result(result);
2315 return ret;
2316 }
2317
2318 static FILE *out_file;
2319
2320 static int mysql_write_tmp_file(const char *dir, const char *name)
2321 {
2322 int ret = -E_TMPFILE;
2323 char *msg = make_message("%s\t%s\n", dir, name);
2324
2325 if (fputs(msg, out_file) != EOF)
2326 ret = 1;
2327 free(msg);
2328 return ret;
2329 }
2330
2331 /*
2332 * update database
2333 */
2334 static int com_upd(int fd, int argc, __a_unused char *argv[])
2335 {
2336 char *tempname = NULL, *query = NULL;
2337 int ret, out_fd = -1, num = 0;
2338 void *result = NULL;
2339 unsigned int num_rows;
2340 MYSQL_ROW row;
2341
2342 if (argc != 1)
2343 return -E_MYSQL_SYNTAX;
2344 out_file = NULL;
2345 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2346 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2347 if (ret < 0)
2348 goto out;
2349 out_fd = ret;
2350 out_file = fdopen(out_fd, "w");
2351 if (!out_file) {
2352 close(out_fd);
2353 goto out;
2354 }
2355 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2356 goto out;
2357 num = ftell(out_file);
2358 /*
2359 * we have to make sure the file hit the disk before we call
2360 * real_query
2361 */
2362 fclose(out_file);
2363 out_file = NULL;
2364 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2365 if (!num)
2366 goto out;
2367 if ((ret = real_query("delete from dir")) < 0)
2368 goto out;
2369 query = make_message("load data infile '%s' ignore into table dir "
2370 "fields terminated by '\t' lines terminated by '\n' "
2371 "(dir, name)", tempname);
2372 ret = real_query(query);
2373 free(query);
2374 if (ret < 0)
2375 goto out;
2376 result = get_result("select dir.name from dir left join data on "
2377 "data.name = dir.name where data.name is NULL");
2378 ret = -E_NORESULT;
2379 if (!result)
2380 goto out;
2381 num_rows = mysql_num_rows(result);
2382 if (!num_rows) {
2383 ret = send_buffer(fd, "no new entries\n");
2384 goto out;
2385 }
2386 while ((row = mysql_fetch_row(result))) {
2387 ret = -E_NOROW;
2388 if (!row[0])
2389 goto out;
2390 send_va_buffer(fd, "new entry: %s\n", row[0]);
2391 query = make_message("insert into data (name, pic_id) values "
2392 "('%s','%s')", row[0], "1");
2393 ret = real_query(query);
2394 free(query);
2395 if (ret < 0)
2396 goto out;
2397 }
2398 ret = 1;
2399 out:
2400 if (out_fd >= 0)
2401 unlink(tempname);
2402 free(tempname);
2403 if (out_file)
2404 fclose(out_file);
2405 if (result)
2406 mysql_free_result(result);
2407 return ret;
2408 }
2409
2410 static char **server_get_audio_file_list(unsigned int num)
2411 {
2412 char **list = para_malloc((num + 1) * sizeof(char *));
2413 char *tmp, *query, *stream = get_current_stream();
2414 void *result = NULL;
2415 unsigned int num_rows;
2416 int i = 0;
2417 MYSQL_ROW row;
2418
2419 tmp = get_query(stream, NULL, 1);
2420 free(stream);
2421 query = make_message("%s limit %d", tmp, num);
2422 free(tmp);
2423 result = get_result(query);
2424 free(query);
2425 if (!result)
2426 goto err_out;
2427 num_rows = mysql_num_rows(result);
2428 if (!num_rows)
2429 goto err_out;
2430 for (i = 0; i < num_rows && i < num; i++) {
2431 row = mysql_fetch_row(result);
2432 if (!row || !row[0])
2433 goto err_out;
2434 list[i] = para_strdup(row[0]);
2435 }
2436 list[i] = NULL;
2437 goto success;
2438 err_out:
2439 while (i > 0) {
2440 i--;
2441 free(list[i]);
2442 }
2443 free(list);
2444 list = NULL;
2445 success:
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 }