fix potential segfault in para_client
[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;
699 int ret;
700
701 if (argc < 1)
702 return -E_MYSQL_SYNTAX;
703 q = make_message("alter table data add %s char(1) "
704 "not null default 0", argv[1]);
705 ret = real_query(q);
706 free(q);
707 return ret;
708 }
709
710 /*
711 * delete attribute
712 */
713 static int com_da(__unused int fd, int argc, char *argv[])
714 {
715 char *q;
716 int ret;
717
718 if (argc < 1)
719 return -E_MYSQL_SYNTAX;
720 q = make_message("alter table data drop %s", argv[1]);
721 ret = real_query(q);
722 free(q);
723 return ret;
724 }
725
726 /* stradd/pic_add */
727 static int com_stradd_picadd(int fd, int argc, char *argv[])
728 {
729 char *blob = NULL, *esc_blob = NULL, *q;
730 const char *fmt, *del_fmt;
731 int ret, stradd = strcmp(argv[0], "picadd");
732 size_t size;
733
734 if (argc < 1)
735 return -E_MYSQL_SYNTAX;
736 if (strlen(argv[1]) >= MAXLINE - 1)
737 return -E_NAMETOOLONG;
738 if (!mysql_ptr)
739 return -E_NOTCONN;
740 if (stradd) {
741 size = BLOB_SIZE;
742 fmt = "insert into streams (name, def) values ('%s','%s')";
743 del_fmt="delete from streams where name='%s'";
744 } else {
745 size = MEDIUM_BLOB_SIZE;
746 fmt = "insert into pics (name, pic) values ('%s','%s')";
747 del_fmt="delete from pics where pic='%s'";
748 }
749 q = make_message(del_fmt, argv[1]);
750 ret = real_query(q);
751 free(q);
752 if (ret < 0)
753 return ret;
754 if ((ret = send_buffer(fd, AWAITING_DATA_MSG) < 0))
755 return ret;
756 if ((ret = fd2buf(fd, &blob, size)) < 0)
757 return ret;
758 PARA_DEBUG_LOG("length: %i\n", ret);
759 size = ret;
760 if (stradd)
761 blob[size] = '\0';
762 esc_blob = escape_blob(blob, ret);
763 free(blob);
764 if (!esc_blob)
765 return -E_TOOBIG;
766 q = make_message(fmt, argv[1], esc_blob);
767 free(esc_blob);
768 ret = real_query(q);
769 free(q);
770 return ret;
771 }
772
773 /*
774 * print results to fd
775 */
776 static int print_results(int fd, void *result,
777 unsigned int top, unsigned int left,
778 unsigned int bottom, unsigned int right)
779 {
780 unsigned int i,j;
781 int ret;
782 MYSQL_ROW row;
783
784 for (i = top; i <= bottom; i++) {
785 row = mysql_fetch_row(result);
786 if (!row || !row[0])
787 return -E_NOROW;
788 for (j = left; j <= right; j++) {
789 ret = send_va_buffer(fd, j == left? "%s" : "\t%s",
790 row[j]? row[j] : "NULL");
791 if (ret < 0)
792 return ret;
793 }
794 ret = send_buffer(fd, "\n");
795 if (ret < 0)
796 return ret;
797 }
798 return 0;
799 }
800
801 /*
802 * verbatim
803 */
804 static int com_verb(int fd, int argc, char *argv[])
805 {
806 void *result = NULL;
807 int ret;
808 unsigned int num_rows, num_fields;
809
810 if (argc < 1)
811 return -E_MYSQL_SYNTAX;
812 result = get_result(argv[1]);
813 if (!result)
814 /* return success, because it's ok to have no results */
815 return 1;
816 num_fields = mysql_field_count(mysql_ptr);
817 num_rows = mysql_num_rows(result);
818 ret = 1;
819 if (num_fields && num_rows)
820 ret = print_results(fd, result, 0, 0, num_rows - 1,
821 num_fields - 1);
822 mysql_free_result(result);
823 return ret;
824 }
825
826 /* returns NULL on errors or if there are no atts defined yet */
827 static void *get_all_attributes(void)
828 {
829 void *result = get_result("desc data");
830 unsigned int num_rows;
831
832 if (!result)
833 return NULL;
834 num_rows = mysql_num_rows(result);
835 if (num_rows < 5) {
836 mysql_free_result(result);
837 return NULL;
838 }
839 mysql_data_seek(result, 4); /* skip Lastplayed, Numplayed... */
840 return result;
841 }
842
843 /*
844 * list all attributes
845 */
846 static int com_laa(int fd, int argc, __unused char *argv[])
847 {
848 void *result;
849 int ret;
850
851 if (argc)
852 return -E_MYSQL_SYNTAX;
853 result = get_all_attributes();
854 if (!result)
855 return -E_NOATTS;
856 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 5, 0);
857 mysql_free_result(result);
858 return ret;
859 }
860
861 /*
862 * history
863 */
864 static int com_hist(int fd, int argc, char *argv[]) {
865 int ret;
866 void *result = NULL;
867 char *q;
868 unsigned int num_rows;
869
870 q = make_message("select name, to_days(now()) - to_days(lastplayed) from "
871 "data%s%s%s order by lastplayed",
872 (argc < 1)? "" : " where ",
873 (argc < 1)? "" : argv[1],
874 (argc < 1)? "" : " = '1'");
875 result = get_result(q);
876 free(q);
877 if (!result)
878 return -E_NORESULT;
879 num_rows = mysql_num_rows(result);
880 ret = 1;
881 if (num_rows)
882 ret = print_results(fd, result, 0, 0, num_rows - 1, 1);
883 mysql_free_result(result);
884 return ret;
885 }
886
887 /*
888 * get last num audio files
889 */
890 static int com_last(int fd, int argc, char *argv[])
891 {
892 void *result = NULL;
893 char *q;
894 int num, ret;
895
896 if (argc < 1)
897 num = 10;
898 else
899 num = atoi(argv[1]);
900 if (!num)
901 return -E_MYSQL_SYNTAX;
902 q = make_message("select name from data order by lastplayed desc "
903 "limit %u", num);
904 result = get_result(q);
905 free(q);
906 if (!result)
907 return -E_NORESULT;
908 ret = print_results(fd, result, 0, 0, mysql_num_rows(result) - 1, 0);
909 mysql_free_result(result);
910 return ret;
911 }
912
913 static int com_mbox(int fd, int argc, char *argv[])
914 {
915 void *result;
916 MYSQL_ROW row;
917 int ret;
918 unsigned int num_rows, num_fields;
919 char *query = para_strdup("select concat('From foo@localhost ', "
920 "date_format(Lastplayed, '%a %b %e %T %Y'), "
921 "'\nReceived: from\nTo: bar\n");
922
923 ret = -E_NOATTS;
924 result = get_all_attributes();
925 if (!result)
926 goto out;
927 ret = -E_NOROW;
928 while ((row = mysql_fetch_row(result))) {
929 char *tmp;
930
931 if (!row[0])
932 goto out;
933 tmp = make_message("%s X-Attribute-%s: ', %s, '\n", query,
934 row[0], row[0]);
935 free(query);
936 query = tmp;
937 }
938 query = para_strcat(query,
939 "From: a\n"
940 "Subject: "
941 "', name, '"
942 "\n\n\n"
943 "') from data"
944 );
945 if (argc >= 1) {
946 char *tmp = make_message("%s where name LIKE '%s'", query,
947 argv[1]);
948 free(query);
949 query = tmp;
950 }
951 mysql_free_result(result);
952 ret = -E_NORESULT;
953 result = get_result(query);
954 if (!result)
955 goto out;
956 ret = -E_EMPTY_RESULT;
957 num_fields = mysql_field_count(mysql_ptr);
958 num_rows = mysql_num_rows(result);
959 if (!num_fields || !num_rows)
960 goto out;
961 ret = print_results(fd, result, 0, 0, num_rows - 1, num_fields - 1);
962 out:
963 free(query);
964 if (result)
965 mysql_free_result(result);
966 return ret;
967 }
968
969 /* get attributes by name. If verbose is not 0, get_a writes a string
970 * into atts of the form 'att1="0",att2="1"', which is used in com_cam
971 * for contructing a mysql update query.
972 * never returns NULL in *NON VERBOSE* mode
973 */
974 static char *get_atts(char *name, int verbose)
975 {
976 char *atts = NULL, *buf, *ebn;
977 void *result = NULL, *result2 = NULL;
978 MYSQL_ROW row, row2;
979 int i, ret;
980 unsigned int num_fields;
981
982 ret = -E_NOATTS;
983 result2 = get_all_attributes();
984 if (!result2)
985 goto out;
986 ret = -E_ESCAPE;
987 if (!(ebn = escaped_basename(name)))
988 goto out;
989 buf = make_message("select * from data where name='%s'", ebn);
990 free(ebn);
991 ret = -E_NORESULT;
992 result = get_result(buf);
993 free(buf);
994 if (!result)
995 goto out;
996 ret = -E_EMPTY_RESULT;
997 num_fields = mysql_num_fields(result);
998 if (num_fields < 5)
999 goto out;
1000 mysql_data_seek(result2, 4); /* skip Lastplayed, Numplayed... */
1001 row = mysql_fetch_row(result);
1002 ret = -E_NOROW;
1003 if (!row)
1004 goto out;
1005 for (i = 4; i < num_fields; i++) {
1006 int is_set = row[i] && !strcmp(row[i], "1");
1007 row2 = mysql_fetch_row(result2);
1008 if (!row2 || !row2[0])
1009 goto out;
1010 if (atts && (verbose || is_set))
1011 atts = para_strcat(atts, verbose? "," : " ");
1012 if (is_set || verbose)
1013 atts = para_strcat(atts, row2[0]);
1014 if (verbose)
1015 atts = para_strcat(atts, is_set? "=\"1\"" : "=\"0\"");
1016 }
1017 ret = 1;
1018 out:
1019 if (result2)
1020 mysql_free_result(result2);
1021 if (result)
1022 mysql_free_result(result);
1023 if (!atts && !verbose)
1024 atts = para_strdup("(none)");
1025 return atts;
1026 }
1027
1028 /* never returns NULL in verbose mode */
1029 static char *get_meta(char *name, int verbose)
1030 {
1031 MYSQL_ROW row;
1032 void *result = NULL;
1033 char *ebn, *q, *ret = NULL;
1034 const char *verbose_fmt =
1035 "select concat('lastplayed: ', "
1036 "(to_days(now()) - to_days(lastplayed)),"
1037 "' day(s). numplayed: ', numplayed, "
1038 "', pic: ', pic_id) "
1039 "from data where name = '%s'";
1040 /* is that really needed? */
1041 const char *fmt = "select concat('lastplayed=\\'', lastplayed, "
1042 "'\\', numplayed=\\'', numplayed, "
1043 "'\\', pic_id=\\'', pic_id, '\\'') "
1044 "from data where name = '%s'";
1045
1046 if (!(ebn = escaped_basename(name)))
1047 goto out;
1048 q = make_message(verbose? verbose_fmt : fmt, ebn);
1049 free(ebn);
1050 result = get_result(q);
1051 free(q);
1052 if (!result)
1053 goto out;
1054 row = mysql_fetch_row(result);
1055 if (!row || !row[0])
1056 goto out;
1057 ret = para_strdup(row[0]);
1058 out:
1059 if (result)
1060 mysql_free_result(result);
1061 if (!ret && verbose)
1062 ret = para_strdup("(not yet played)");
1063 return ret;
1064 }
1065
1066 static char *get_dir(char *name)
1067 {
1068 char *ret = NULL, *q, *ebn;
1069 void *result;
1070 MYSQL_ROW row;
1071
1072 if (!(ebn = escaped_basename(name)))
1073 return NULL;
1074 q = make_message("select dir from dir where name = '%s'", ebn);
1075 free(ebn);
1076 result = get_result(q);
1077 free(q);
1078 if (!result)
1079 return NULL;
1080 row = mysql_fetch_row(result);
1081 if (row && row[0])
1082 ret = para_strdup(row[0]);
1083 mysql_free_result(result);
1084 return ret;
1085 }
1086
1087 /* never returns NULL */
1088 static char *get_current_stream(void)
1089 {
1090 char *ret;
1091 MYSQL_ROW row;
1092 void *result = get_result("select def from streams where "
1093 "name = 'current_stream'");
1094
1095 if (!result)
1096 goto err_out;
1097 row = mysql_fetch_row(result);
1098 if (!row || !row[0])
1099 goto err_out;
1100 ret = para_strdup(row[0]);
1101 mysql_free_result(result);
1102 return ret;
1103 err_out:
1104 if (result)
1105 mysql_free_result(result);
1106 return para_strdup("(none)");
1107 }
1108 /*
1109 * Read stream definition of stream streamname and construct mysql
1110 * query. Return NULL on errors. If streamname is NULL, use current
1111 * stream. If that is also NULL, use query that selects everything.
1112 * If filename is NULL, query will list everything, otherwise only
1113 * the score of given file.
1114 */
1115 static char *get_query(char *streamname, char *filename, int with_path)
1116 {
1117 char *accept_opts = NULL, *deny_opts = NULL, *score = NULL;
1118 char *where_clause, *order, *query;
1119 char command[255] = ""; /* buffer for sscanf */
1120 void *result;
1121 MYSQL_ROW row;
1122 char *end, *tmp;
1123 char *select_clause = NULL;
1124 if (!streamname)
1125 tmp = get_current_stream();
1126 else
1127 tmp = para_strdup(streamname);
1128 if (!strcmp(tmp, "(none)")) {
1129 free(tmp);
1130 if (filename) {
1131 char *ret, *ebn = escaped_basename(filename);
1132 ret = make_message("select to_days(now()) - "
1133 "to_days(lastplayed) from data "
1134 "where name = '%s'", ebn);
1135 free(ebn);
1136 return ret;
1137 }
1138 if (with_path)
1139 return make_message(
1140 "select concat(dir.dir, '/', dir.name) "
1141 "from data, dir where dir.name = data.name "
1142 "order by data.lastplayed"
1143 );
1144 return make_message(
1145 "select name from data where name is not NULL "
1146 "order by lastplayed"
1147 );
1148 }
1149 free(tmp);
1150 query = make_message("select def from streams where name = '%s'",
1151 streamname);
1152 result = get_result(query);
1153 free(query);
1154 query = NULL;
1155 if (!result)
1156 goto out;
1157 row = mysql_fetch_row(result);
1158 if (!row || !row[0])
1159 goto out;
1160 end = row[0];
1161 while (*end) {
1162 int n;
1163 char *arg, *line = end;
1164
1165 if (!(end = strchr(line, '\n')))
1166 break;
1167 *end = '\0';
1168 end++;
1169 if (sscanf(line, "%200s%n", command, &n) < 1)
1170 continue;
1171 arg = line + n;
1172 if (!strcmp(command, "accept:")) {
1173 char *tmp2 = s_a_r_list(macro_list, arg);
1174 if (accept_opts)
1175 accept_opts = para_strcat(
1176 accept_opts, " or ");
1177 accept_opts = para_strcat(accept_opts, tmp2);
1178 free(tmp2);
1179 continue;
1180 }
1181 if (!strcmp(command, "deny:")) {
1182 char *tmp2 = s_a_r_list(macro_list, arg);
1183 if (deny_opts)
1184 deny_opts = para_strcat(deny_opts, " or ");
1185 deny_opts = para_strcat(deny_opts, tmp2);
1186 free(tmp2);
1187 continue;
1188 }
1189 if (!score && !strcmp(command, "score:"))
1190 score = s_a_r_list(macro_list, arg);
1191 }
1192 if (!score) {
1193 score = s_a_r_list(macro_list, conf.mysql_default_score_arg);
1194 if (!score)
1195 goto out;
1196 }
1197 if (filename) {
1198 char *ebn = escaped_basename(filename);
1199 if (!ebn)
1200 goto out;
1201 select_clause = make_message("select %s from data ", score);
1202 free(score);
1203 where_clause = make_message( "where name = '%s' ", ebn);
1204 free(ebn);
1205 order = para_strdup("");
1206 goto write_query;
1207 }
1208 select_clause = para_strdup(with_path?
1209 "select concat(dir.dir, '/', dir.name) from data, dir "
1210 "where dir.name = data.name "
1211 :
1212 "select name from data where name is not NULL");
1213 order = make_message("order by -(%s)", score);
1214 free(score);
1215 if (accept_opts && deny_opts) {
1216 where_clause = make_message("and ((%s) and not (%s)) ",
1217 accept_opts, deny_opts);
1218 goto write_query;
1219 }
1220 if (accept_opts && !deny_opts) {
1221 where_clause = make_message("and (%s) ", accept_opts);
1222 goto write_query;
1223 }
1224 if (!accept_opts && deny_opts) {
1225 where_clause = make_message("and not (%s) ", deny_opts);
1226 goto write_query;
1227 }
1228 where_clause = para_strdup("");
1229 write_query:
1230 query = make_message("%s %s %s", select_clause, where_clause, order);
1231 free(order);
1232 free(select_clause);
1233 free(where_clause);
1234 out:
1235 if (accept_opts)
1236 free(accept_opts);
1237 if (deny_opts)
1238 free(deny_opts);
1239 if (result)
1240 mysql_free_result(result);
1241 return query;
1242 }
1243
1244
1245
1246 /*
1247 * This is called from server and from some commands. Name must not be NULL
1248 * Never returns NULL.
1249 */
1250 static char *get_selector_info(char *name)
1251 {
1252 char *meta = NULL, *atts = NULL, *info, *dir = NULL, *query, *stream = NULL;
1253 void *result = NULL;
1254 MYSQL_ROW row = NULL;
1255
1256 if (!name)
1257 return para_strdup("(none)");
1258 stream = get_current_stream();
1259 meta = get_meta(name, 1);
1260 atts = get_atts(name, 0);
1261 dir = get_dir(name);
1262 /* get score */
1263 query = get_query(stream, name, 0);
1264 if (!query)
1265 goto write;
1266 result = get_result(query);
1267 free(query);
1268 if (result)
1269 row = mysql_fetch_row(result);
1270 write:
1271 info = make_message("dbinfo1:dir: %s\n"
1272 "dbinfo2:stream: %s, %s, score: %s\n"
1273 "dbinfo3:%s\n",
1274 dir? dir : "(not contained in table)",
1275 stream, meta,
1276 (result && row && row[0])? row[0] : "(no score)",
1277 atts);
1278 if (dir)
1279 free(dir);
1280 if (meta)
1281 free(meta);
1282 if (atts)
1283 free(atts);
1284 if (stream)
1285 free(stream);
1286 if (result)
1287 mysql_free_result(result);
1288 return info;
1289 }
1290
1291
1292 /* might return NULL */
1293 static char *get_current_audio_file(void)
1294 {
1295 char *name;
1296 mmd_lock();
1297 name = para_basename(mmd->filename);
1298 mmd_unlock();
1299 return name;
1300 }
1301
1302
1303 /* print database info */
1304 static int com_info(int fd, int argc, char *argv[])
1305 {
1306 char *name = NULL, *meta = NULL, *atts = NULL, *dir = NULL;
1307 int ret, com_la = strcmp(argv[0], "info");
1308
1309 if (argc < 1) {
1310 ret = -E_GET_AUDIO_FILE;
1311 if (!(name = get_current_audio_file()))
1312 goto out;
1313 ret = send_va_buffer(fd, "%s\n", name);
1314 if (ret < 0)
1315 goto out;
1316 } else {
1317 ret = -E_ESCAPE;
1318 if (!(name = escaped_basename(argv[1])))
1319 goto out;
1320 }
1321 meta = get_meta(name, 1);
1322 atts = get_atts(name, 0);
1323 dir = get_dir(name);
1324 if (com_la)
1325 ret = send_va_buffer(fd, "%s\n", atts);
1326 else
1327 ret = send_va_buffer(fd, "dir: %s\n" "%s\n" "attributes: %s\n",
1328 dir? dir : "(not contained in table)", meta, atts);
1329 out:
1330 if (meta)
1331 free(meta);
1332 if (atts)
1333 free(atts);
1334 if (dir)
1335 free(dir);
1336 if (name)
1337 free(name);
1338 return ret;
1339 }
1340 static int change_stream(const char *stream)
1341 {
1342 char *query;
1343 int ret;
1344 /* try to insert if it does not exist (compatibility) */
1345 // query = make_message("insert into streams (name, def) values "
1346 // "('current_stream', '%s')", stream);
1347 // real_query(query); /* ignore return value */
1348 // free(query);
1349 query = make_message("update streams set def='%s' "
1350 "where name = 'current_stream'", stream);
1351 ret = real_query(query);
1352 free(query);
1353 return ret;
1354 }
1355
1356 static int get_pic_id_by_name(char *name)
1357 {
1358 char *q, *ebn;
1359 void *result = NULL;
1360 long unsigned ret;
1361 MYSQL_ROW row;
1362
1363 if (!(ebn = escaped_basename(name)))
1364 return -E_ESCAPE;
1365 q = make_message("select pic_id from data where name = '%s'", ebn);
1366 free(ebn);
1367 result = get_result(q);
1368 free(q);
1369 if (!result)
1370 return -E_NORESULT;
1371 row = mysql_fetch_row(result);
1372 ret = -E_NOROW;
1373 if (row && row[0])
1374 ret = atol(row[0]);
1375 mysql_free_result(result);
1376 return ret;
1377 }
1378
1379 static int remove_entry(const char *name)
1380 {
1381 char *q, *ebn = escaped_basename(name);
1382 int ret = -E_ESCAPE;
1383
1384 if (!ebn || !*ebn)
1385 goto out;
1386 q = make_message("delete from data where name = '%s'", ebn);
1387 real_query(q); /* ignore errors */
1388 free(q);
1389 q = make_message("delete from dir where name = '%s'", ebn);
1390 real_query(q); /* ignore errors */
1391 free(q);
1392 ret = 1;
1393 out:
1394 free(ebn);
1395 return ret;
1396 }
1397
1398 static int add_entry(const char *name)
1399 {
1400 char *q, *dn, *ebn = NULL, *edn = NULL;
1401 int ret;
1402
1403 if (!name || !*name)
1404 return -E_MYSQL_SYNTAX;
1405 ebn = escaped_basename(name);
1406 if (!ebn)
1407 return -E_ESCAPE;
1408 ret = -E_MYSQL_SYNTAX;
1409 dn = para_dirname(name);
1410 if (!dn)
1411 goto out;
1412 ret = -E_ESCAPE;
1413 edn = escape_str(dn);
1414 free(dn);
1415 if (!edn || !*edn)
1416 goto out;
1417 q = make_message("insert into data (name, pic_id) values "
1418 "('%s', '%s')", ebn, "1");
1419 ret = real_query(q);
1420 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1421 free(q);
1422 if (ret < 0)
1423 goto out;
1424 q = make_message("insert into dir (name, dir) values "
1425 "('%s', '%s')", ebn, edn);
1426 // ret = 1; PARA_DEBUG_LOG("q: %s\n", q);
1427 ret = real_query(q);
1428 free(q);
1429 out:
1430 if (ebn)
1431 free(ebn);
1432 if (edn)
1433 free(edn);
1434 return ret;
1435 }
1436
1437 /*
1438 * remove/add entries
1439 */
1440 static int com_rm_ne(__unused int fd, int argc, char *argv[])
1441 {
1442 int ne = !strcmp(argv[0], "ne");
1443 int i, ret;
1444 if (argc < 1)
1445 return -E_MYSQL_SYNTAX;
1446 for (i = 1; i <= argc; i++) {
1447 ret = remove_entry(argv[i]);
1448 if (ret < 0)
1449 return ret;
1450 if (!ne)
1451 continue;
1452 ret = add_entry(argv[i]);
1453 if (ret < 0)
1454 return ret;
1455 }
1456 return 1;
1457 }
1458
1459 /*
1460 * mv: rename entry
1461 */
1462 static int com_mv(__unused int fd, int argc, char *argv[])
1463 {
1464 char *q, *dn, *ebn1 = NULL, *ebn2 = NULL, *edn = NULL;
1465 int ret;
1466
1467 if (argc != 2)
1468 return -E_MYSQL_SYNTAX;
1469 ebn1 = escaped_basename(argv[1]);
1470 ebn2 = escaped_basename(argv[2]);
1471 dn = para_dirname(argv[2]);
1472 edn = escape_str(dn);
1473 free(dn);
1474 ret = -E_ESCAPE;
1475 if (!ebn1 || !ebn2)
1476 goto out;
1477 remove_entry(ebn2);
1478 q = make_message("update data set name = '%s' where name = '%s'",
1479 ebn2, ebn1);
1480 ret = real_query(q);
1481 free(q);
1482 if (ret < 0)
1483 goto out;
1484 q = make_message("update dir set name = '%s' where name = '%s'",
1485 ebn2, ebn1);
1486 ret = real_query(q);
1487 free(q);
1488 if (ret < 0)
1489 goto out;
1490 /* do not touch table dir, return success if argv[2] is no full path */
1491 ret = 1;
1492 if (!edn || !*edn)
1493 goto out;
1494 q = make_message("update dir set dir = '%s' where name = '%s'",
1495 edn, ebn2);
1496 // PARA_DEBUG_LOG("q: %s\n", q);
1497 ret = real_query(q);
1498 free(q);
1499 out:
1500 if (ebn1)
1501 free(ebn1);
1502 if (ebn2)
1503 free(ebn2);
1504 if (edn)
1505 free(edn);
1506 return ret;
1507
1508 }
1509
1510 /*
1511 * picass: associate pic to audio file
1512 * snp: set numplayed
1513 */
1514 static int com_set(__unused int fd, int argc, char *argv[])
1515 {
1516 char *q, *ebn;
1517 long unsigned id;
1518 int i, ret;
1519 const char *field = strcmp(argv[0], "picass")? "numplayed" : "pic_id";
1520
1521 if (argc < 2)
1522 return -E_MYSQL_SYNTAX;
1523 id = atol(argv[1]);
1524 for (i = 2; i <= argc; i++) {
1525 ebn = escaped_basename(argv[i]);
1526 if (!ebn)
1527 return -E_ESCAPE;
1528 q = make_message("update data set %s = %lu "
1529 "where name = '%s'", field, id, ebn);
1530 free(ebn);
1531 ret = real_query(q);
1532 free(q);
1533 if (ret < 0)
1534 return ret;
1535 }
1536 return 1;
1537 }
1538
1539 /*
1540 * picch: change entry's name in pics table
1541 */
1542 static int com_picch(__unused int fd, int argc, char *argv[])
1543 {
1544 int ret;
1545 long unsigned id;
1546 char *q;
1547
1548 if (argc != 2)
1549 return -E_MYSQL_SYNTAX;
1550 id = atol(argv[1]);
1551 if (strlen(argv[2]) > MAXLINE)
1552 return -E_NAMETOOLONG;
1553 q = make_message("update pics set name = '%s' where id = %lu", argv[2], id);
1554 ret = real_query(q);
1555 free(q);
1556 return ret;
1557 }
1558
1559 /*
1560 * piclist: print list of pics in db
1561 */
1562 static int com_piclist(__unused int fd, int argc, __unused char *argv[])
1563 {
1564 void *result = NULL;
1565 MYSQL_ROW row;
1566 unsigned long *length;
1567 int ret;
1568
1569 if (argc)
1570 return -E_MYSQL_SYNTAX;
1571 result = get_result("select id,name,pic from pics order by id");
1572 if (!result)
1573 return -E_NORESULT;
1574 while ((row = mysql_fetch_row(result))) {
1575 length = mysql_fetch_lengths(result);
1576 if (!row || !row[0] || !row[1] || !row[2])
1577 continue;
1578 ret = send_va_buffer(fd, "%s\t%lu\t%s\n", row[0], length[2], row[1]);
1579 if (ret < 0)
1580 goto out;
1581 }
1582 ret = 1;
1583 out:
1584 mysql_free_result(result);
1585 return ret;
1586 }
1587
1588 /*
1589 * picdel: delete picture from database
1590 */
1591 static int com_picdel(int fd, int argc, char *argv[])
1592 {
1593 char *q;
1594 long unsigned id;
1595 my_ulonglong aff;
1596 int i, ret;
1597
1598 if (argc < 1)
1599 return -E_MYSQL_SYNTAX;
1600 for (i = 1; i <= argc; i++) {
1601 id = atol(argv[i]);
1602 q = make_message("delete from pics where id = %lu", id);
1603 ret = real_query(q);
1604 free(q);
1605 if (ret < 0)
1606 return ret;
1607 aff = mysql_affected_rows(mysql_ptr);
1608 if (!aff) {
1609 ret = send_va_buffer(fd, "No such id: %lu\n", id);
1610 if (ret < 0)
1611 return ret;
1612 continue;
1613 }
1614 q = make_message("update data set pic_id = 1 where pic_id = %lu", id);
1615 ret = real_query(q);
1616 free(q);
1617 }
1618 return 1;
1619 }
1620 /*
1621 * pic: get picture by name or by number
1622 */
1623 static int com_pic(int fd, int argc, char *argv[])
1624 {
1625 void *result = NULL;
1626 MYSQL_ROW row;
1627 unsigned long *length, id;
1628 int ret;
1629 char *q, *name = NULL;
1630
1631 if (argc < 1) {
1632 ret = -E_GET_AUDIO_FILE;
1633 name = get_current_audio_file();
1634 } else {
1635 ret = -E_ESCAPE;
1636 name = escaped_basename(argv[1]);
1637 }
1638 if (!name)
1639 return ret;
1640 if (*name == '#')
1641 id = atoi(name + 1);
1642 else
1643 id = get_pic_id_by_name(name);
1644 free(name);
1645 if (id <= 0)
1646 return id;
1647 q = make_message("select pic from pics where id = '%lu'", id);
1648 result = get_result(q);
1649 free(q);
1650 if (!result)
1651 return -E_NORESULT;
1652 row = mysql_fetch_row(result);
1653 ret = -E_NOROW;
1654 if (!row || !row[0])
1655 goto out;
1656 length = mysql_fetch_lengths(result);
1657 ret = send_bin_buffer(fd, row[0], *length);
1658 out:
1659 mysql_free_result(result);
1660 return ret;
1661 }
1662
1663 /* strdel */
1664 static int com_strdel(__unused int fd, int argc, char *argv[])
1665 {
1666 char *tmp;
1667 int ret = -1;
1668
1669 if (argc < 1)
1670 return -E_MYSQL_SYNTAX;
1671 tmp = make_message("delete from streams where name='%s'", argv[1]);
1672 ret = real_query(tmp);
1673 free(tmp);
1674 if (ret < 0)
1675 return ret;
1676 tmp = get_current_stream();
1677 ret = 1;
1678 if (strcmp(tmp, "(none)") && !strcmp(tmp, argv[1]))
1679 ret = change_stream("(none)");
1680 return ret;
1681 }
1682
1683 /*
1684 * ls
1685 */
1686 static int com_ls(int fd, int argc, char *argv[])
1687 {
1688 char *q;
1689 void *result;
1690 int ret;
1691 unsigned int num_rows;
1692
1693 if (argc > 0)
1694 q = make_message("select name from data where name LIKE '%s'",
1695 argv[1]);
1696 else
1697 q = para_strdup("select name from data");
1698 result = get_result(q);
1699 free(q);
1700 if (!result)
1701 return -E_NORESULT;
1702 num_rows = mysql_num_rows(result);
1703 ret = 1;
1704 if (num_rows)
1705 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
1706 mysql_free_result(result);
1707 return ret;
1708 }
1709 /*
1710 * summary
1711 */
1712 static int com_summary(__unused int fd, int argc, __unused char *argv[])
1713 {
1714 MYSQL_ROW row;
1715 MYSQL_ROW row2;
1716 void *result;
1717 void *result2 = NULL;
1718 const char *fmt = "select count(name) from data where %s='1'";
1719 int ret = -E_NORESULT;
1720
1721 if (argc)
1722 return -E_MYSQL_SYNTAX;
1723 result = get_all_attributes();
1724 if (!result)
1725 goto out;
1726 while ((row = mysql_fetch_row(result))) {
1727 char *buf;
1728
1729 ret = -E_NOROW;
1730 if (!row[0])
1731 goto out;
1732 ret = -E_NORESULT;
1733 buf = make_message(fmt, row[0]);
1734 result2 = get_result(buf);
1735 free(buf);
1736 if (!result2)
1737 goto out;
1738 ret = -E_NOROW;
1739 row2 = mysql_fetch_row(result2);
1740 if (!row2 || !row2[0])
1741 goto out;
1742 ret = send_va_buffer(fd, "%s\t%s\n", row[0], row2[0]);
1743 if (ret < 0)
1744 goto out;
1745 }
1746 ret = 1;
1747 out:
1748 if (result2)
1749 mysql_free_result(result2);
1750 if (result)
1751 mysql_free_result(result);
1752 return ret;
1753 }
1754
1755 static int get_numplayed(char *name)
1756 {
1757 void *result;
1758 MYSQL_ROW row;
1759 const char *fmt = "select numplayed from data where name = '%s'";
1760 char *buf = make_message(fmt, name);
1761 int ret = -E_NORESULT;
1762
1763 result = get_result(buf);
1764 free(buf);
1765 if (!result)
1766 goto out;
1767 ret = -E_NOROW;
1768 row = mysql_fetch_row(result);
1769 if (!row || !row[0])
1770 goto out;
1771 ret = atoi(row[0]);
1772 out:
1773 if (result)
1774 mysql_free_result(result);
1775 return ret;
1776 }
1777
1778 static int update_audio_file(char *name)
1779 {
1780 int ret;
1781 const char *fmt1 = "update data set lastplayed = now() where name = '%s'";
1782 const char *fmt2 = "update data set numplayed = %i where name = '%s'";
1783 char *q;
1784 char *ebn = escaped_basename(name);
1785
1786 ret = -E_ESCAPE;
1787 if (!ebn)
1788 goto out;
1789 q = make_message(fmt1, ebn);
1790 ret = real_query(q);
1791 free(q);
1792 if (ret < 0)
1793 goto out;
1794 ret = get_numplayed(ebn);
1795 if (ret < 0)
1796 goto out;
1797 q = make_message(fmt2, ret + 1, ebn);
1798 ret = real_query(q);
1799 free(q);
1800 out:
1801 if (ebn)
1802 free(ebn);
1803 return ret;
1804 }
1805 /* If called as child, mmd_lock must be held */
1806 static void update_mmd(char *info)
1807 {
1808 PARA_DEBUG_LOG("%s", "updating shared memory area\n");
1809 strncpy(mmd->selector_info, info, MMD_INFO_SIZE - 1);
1810 mmd->selector_info[MMD_INFO_SIZE - 1] = '\0';
1811 }
1812
1813 static void update_audio_file_server_handler(char *name)
1814 {
1815 char *info;
1816 info = get_selector_info(name);
1817 update_mmd(info);
1818 free(info);
1819 update_audio_file(name);
1820 }
1821
1822 static int com_us(__unused int fd, int argc, char *argv[])
1823 {
1824 if (argc != 1)
1825 return -E_MYSQL_SYNTAX;
1826 return update_audio_file(argv[1]);
1827 }
1828
1829 static void refresh_selector_info(void)
1830 {
1831 char *name = get_current_audio_file();
1832 char *info;
1833
1834 if (!name)
1835 return;
1836 info = get_selector_info(name);
1837 free(name);
1838 mmd_lock();
1839 update_mmd(info);
1840 mmd_unlock();
1841 free(info);
1842 }
1843
1844 /* select previous/next stream */
1845 static int com_ps(__unused int fd, int argc, char *argv[])
1846 {
1847 char *query, *stream = get_current_stream();
1848 void *result = get_result("select name from streams");
1849 MYSQL_ROW row;
1850 int match = -1, ret, i;
1851 unsigned int num_rows;
1852
1853 if (argc)
1854 return -E_MYSQL_SYNTAX;
1855 ret = -E_NORESULT;
1856 if (!result)
1857 goto out;
1858 num_rows = mysql_num_rows(result);
1859 ret = -E_EMPTY_RESULT;
1860 if (num_rows < 2)
1861 goto out;
1862 ret = -E_NOROW;
1863 for (i = 0; i < num_rows; i++) {
1864 row = mysql_fetch_row(result);
1865 if (!row || !row[0])
1866 goto out;
1867 if (!strcmp(row[0], "current_stream"))
1868 continue;
1869 if (!strcmp(row[0], stream)) {
1870 match = i;
1871 break;
1872 }
1873 }
1874 ret = -E_NO_STREAM;
1875 if (match < 0)
1876 goto out;
1877 if (!strcmp(argv[0], "ps"))
1878 i = match > 0? match - 1 : num_rows - 1;
1879 else
1880 i = match < num_rows - 1? match + 1 : 0;
1881 ret = -E_NOROW;
1882 mysql_data_seek(result, i);
1883 row = mysql_fetch_row(result);
1884 if (!row || !row[0])
1885 goto out;
1886 if (!strcmp(row[0], "current_stream")) {
1887 if (!strcmp(argv[0], "ps")) {
1888 i = match - 2;
1889 i = i < 0? i + num_rows : i;
1890 } else {
1891 i = match + 2;
1892 i = i > num_rows - 1? i - num_rows : i;
1893 }
1894 mysql_data_seek(result, i);
1895 row = mysql_fetch_row(result);
1896 if (!row || !row[0])
1897 goto out;
1898 }
1899 query = make_message("update streams set def='%s' where name = "
1900 "'current_stream'", row[0]);
1901 ret = real_query(query);
1902 free(query);
1903 refresh_selector_info();
1904 out:
1905 free(stream);
1906 if (result)
1907 mysql_free_result(result);
1908 return ret;
1909 }
1910
1911 /* streams */
1912 static int com_streams(int fd, int argc, __unused char *argv[])
1913 {
1914 unsigned int num_rows;
1915 int i, ret = -E_NORESULT;
1916 void *result;
1917 MYSQL_ROW row;
1918
1919 if (argc && strcmp(argv[1], "current_stream"))
1920 return -E_MYSQL_SYNTAX;
1921 if (argc) {
1922 char *cs = get_current_stream();
1923 ret = send_va_buffer(fd, "%s\n", cs);
1924 free(cs);
1925 return ret;
1926 }
1927 result = get_result("select name from streams");
1928 if (!result)
1929 goto out;
1930 num_rows = mysql_num_rows(result);
1931 ret = 1;
1932 if (!num_rows)
1933 goto out;
1934 ret = -E_NOROW;
1935 for (i = 0; i < num_rows; i++) {
1936 row = mysql_fetch_row(result);
1937 if (!row || !row[0])
1938 goto out;
1939 if (strcmp(row[0], "current_stream"))
1940 send_va_buffer(fd, "%s\n", row[0]);
1941 }
1942 ret = 1;
1943 out:
1944 if (result)
1945 mysql_free_result(result);
1946 return ret;
1947 }
1948
1949 /* query stream definition */
1950 static int com_strq(int fd, int argc, char *argv[])
1951 {
1952 MYSQL_ROW row;
1953 char *query, *name;
1954 void *result;
1955 int ret;
1956
1957 if (argc < 1) {
1958 ret = -E_GET_STREAM;
1959 name = get_current_stream();
1960 } else {
1961 ret = -E_ESCAPE;
1962 name = escaped_basename(argv[1]);
1963 }
1964 if (!name)
1965 return ret;
1966 ret = -E_NORESULT;
1967 query = make_message("select def from streams where name='%s'", name);
1968 free(name);
1969 result = get_result(query);
1970 free(query);
1971 if (!result)
1972 goto out;
1973 ret = -E_NOROW;
1974 row = mysql_fetch_row(result);
1975 if (!row || !row[0])
1976 goto out;
1977 /* no '\n' needed */
1978 ret = send_buffer(fd, row[0]);
1979 out:
1980 if (result)
1981 mysql_free_result(result);
1982 return ret;
1983 }
1984
1985 /* change stream / change stream and play */
1986 static int com_cs(int fd, int argc, char *argv[])
1987 {
1988 int ret, stream_change;
1989 char *query;
1990 char *old_stream = get_current_stream();
1991 int csp = !strcmp(argv[0], "csp");
1992
1993 if (!argc) {
1994 ret = -E_MYSQL_SYNTAX;
1995 if (csp)
1996 goto out;
1997 ret = send_va_buffer(fd, "%s\n", old_stream);
1998 goto out;
1999 }
2000 ret = -E_GET_QUERY;
2001 query = get_query(argv[1], NULL, 0); /* test if stream is valid */
2002 if (!query)
2003 goto out;
2004 free(query);
2005 /* stream is ok */
2006 stream_change = strcmp(argv[1], old_stream);
2007 if (stream_change) {
2008 ret = change_stream(argv[1]);
2009 if (ret < 0)
2010 goto out;
2011 refresh_selector_info();
2012 }
2013 if (csp) {
2014 mmd_lock();
2015 mmd->new_afs_status_flags |= AFS_PLAYING;
2016 if (stream_change)
2017 mmd->new_afs_status_flags |= AFS_NEXT;
2018 mmd_unlock();
2019 }
2020 ret = 1;
2021 out:
2022 free(old_stream);
2023 return ret;
2024 }
2025
2026 /*
2027 * sl/skip
2028 */
2029 static int com_sl(int fd, int argc, char *argv[])
2030 {
2031 void *result = NULL;
2032 MYSQL_ROW row;
2033 int ret, i, skip = !strcmp(argv[0], "skip");
2034 char *query, *stream, *tmp;
2035 unsigned int num_rows, num;
2036
2037 if (argc < 1)
2038 return -E_MYSQL_SYNTAX;
2039 num = atoi(argv[1]);
2040 if (!num)
2041 return -E_MYSQL_SYNTAX;
2042 stream = (argc == 1)? get_current_stream() : para_strdup(argv[2]);
2043 tmp = get_query(stream, NULL, 0);
2044 query = make_message("%s limit %d", tmp, num);
2045 free(tmp);
2046 ret = -E_GET_QUERY;
2047 free(stream);
2048 if (!query)
2049 goto out;
2050 ret = -E_NORESULT;
2051 result = get_result(query);
2052 free(query);
2053 if (!result)
2054 goto out;
2055 ret = -E_EMPTY_RESULT;
2056 num_rows = mysql_num_rows(result);
2057 if (!num_rows)
2058 goto out;
2059 for (i = 0; i < num_rows && i < num; i++) {
2060 row = mysql_fetch_row(result);
2061 if (skip) {
2062 send_va_buffer(fd, "Skipping %s\n", row[0]);
2063 update_audio_file(row[0]);
2064 } else
2065 send_va_buffer(fd, "%s\n", row[0]? row[0]: "BUG");
2066 }
2067 ret = 1;
2068 out:
2069 if (result)
2070 mysql_free_result(result);
2071 return ret;
2072 }
2073
2074 /*
2075 * update attributes of name
2076 */
2077 static int update_atts(int fd, char *name, char *atts)
2078 {
2079 int ret;
2080 char *ebn, *q, *old, *new = NULL;
2081
2082 if (!mysql_ptr)
2083 return -E_NOTCONN;
2084 ebn = escaped_basename(name);
2085 if (!ebn)
2086 return -E_ESCAPE;
2087 q = make_message("update data set %s where name = '%s'", atts, ebn);
2088 old = get_atts(ebn, 0);
2089 send_va_buffer(fd, "old: %s\n", old);
2090 free(old);
2091 ret = real_query(q);
2092 free(q);
2093 if (ret < 0)
2094 goto out;
2095 new = get_atts(ebn, 0);
2096 ret = send_va_buffer(fd, "new: %s\n", new);
2097 free(new);
2098 out:
2099 free(ebn);
2100 return ret;
2101 }
2102
2103 /*
2104 * set attributes
2105 */
2106 static int com_sa(int fd, int argc, char *argv[])
2107 {
2108 int i, ret;
2109 char *atts = NULL, *name;
2110
2111 if (argc < 1)
2112 return -E_MYSQL_SYNTAX;
2113 for (i = 1; i <= argc; i++) {
2114 int unset = 0;
2115 char *tmp, *p =argv[i];
2116 int len = strlen(p);
2117
2118 if (!len)
2119 continue;
2120 switch (p[len - 1]) {
2121 case '+':
2122 unset = 0;
2123 break;
2124 case '-':
2125 unset = 1;
2126 break;
2127 default:
2128 goto no_more_atts;
2129 }
2130 p[len - 1] = '\0';
2131 tmp = make_message("%s%s='%s'", atts? "," : "", p,
2132 unset? "0" : "1");
2133 atts = para_strcat(atts, tmp);
2134 free(tmp);
2135 }
2136 no_more_atts:
2137 if (!atts)
2138 return -E_NOATTS;
2139 if (i > argc) { /* no name given, use current af */
2140 ret = -E_GET_AUDIO_FILE;
2141 if (!(name = get_current_audio_file()))
2142 goto out;
2143 ret = update_atts(fd, name, atts);
2144 free(name);
2145 } else {
2146 ret = 1;
2147 for (; argv[i] && ret >= 0; i++)
2148 ret = update_atts(fd, argv[i], atts);
2149 }
2150 refresh_selector_info();
2151 out:
2152 return ret;
2153 }
2154
2155 /*
2156 * copy attributes
2157 */
2158 static int com_cam(int fd, int argc, char *argv[])
2159 {
2160 char *name = NULL, *meta = NULL, *atts = NULL;
2161 int i, ret;
2162
2163 if (argc < 2)
2164 return -E_MYSQL_SYNTAX;
2165 if (!(name = escaped_basename(argv[1])))
2166 return -E_ESCAPE;
2167 ret = -E_NOATTS;
2168 if (!(atts = get_atts(name, 1)))
2169 goto out;
2170 ret = -E_META;
2171 if (!(meta = get_meta(name, 0)))
2172 goto out;
2173 for (i = 2; i <= argc; i++) {
2174 char *ebn, *q;
2175 ret = -E_ESCAPE;
2176 if (!(ebn = escaped_basename(argv[i])))
2177 goto out;
2178 ret = send_va_buffer(fd, "updating %s\n", ebn);
2179 if (ret < 0) {
2180 free(ebn);
2181 goto out;
2182 }
2183 q = make_message("update data set %s where name = '%s'",
2184 meta, ebn);
2185 if ((ret = update_atts(fd, ebn, atts)) >= 0)
2186 ret = real_query(q);
2187 free(ebn);
2188 free(q);
2189 if (ret < 0)
2190 goto out;
2191 }
2192 ret = 1;
2193 out:
2194 if (name)
2195 free(name);
2196 if (meta)
2197 free(meta);
2198 if (atts)
2199 free(atts);
2200 return ret;
2201 }
2202
2203 /*
2204 * verify / clean
2205 */
2206 static int com_vrfy(int fd, int argc, __unused char *argv[])
2207 {
2208 char *query;
2209 int ret, vrfy_mode = strcmp(argv[0], "clean");
2210 void *result = NULL;
2211 unsigned int num_rows;
2212 MYSQL_ROW row;
2213 char *escaped_name;
2214
2215 if (argc)
2216 return -E_MYSQL_SYNTAX;
2217 ret = -E_NORESULT;
2218 result = get_result("select data.name from data left join dir on "
2219 "dir.name = data.name where dir.name is NULL");
2220 if (!result)
2221 goto out;
2222 num_rows = mysql_num_rows(result);
2223 if (!num_rows) {
2224 ret = send_buffer(fd, "No invalid entries\n");
2225 goto out;
2226 }
2227 if (vrfy_mode) {
2228 send_va_buffer(fd, "found %i invalid entr%s\n", num_rows,
2229 num_rows == 1? "y" : "ies");
2230 ret = print_results(fd, result, 0, 0, num_rows - 1, 0);
2231 goto out;
2232 }
2233 while ((row = mysql_fetch_row(result))) {
2234 ret = -E_NOROW;
2235 if (!row[0])
2236 goto out;
2237 ret = -E_ESCAPE;
2238 escaped_name = escape_str(row[0]);
2239 if (!escaped_name)
2240 goto out;
2241 send_va_buffer(fd, "deleting %s\n", escaped_name);
2242 query = make_message("delete from data where name = '%s'",
2243 escaped_name);
2244 ret = real_query(query);
2245 free(query);
2246 if (ret < 0)
2247 goto out;
2248 }
2249
2250 out:
2251 if (result)
2252 mysql_free_result(result);
2253 return ret;
2254 }
2255
2256 static FILE *out_file;
2257
2258 static int mysql_write_tmp_file(const char *dir, const char *name)
2259 {
2260 int ret = -E_TMPFILE;
2261 char *msg = make_message("%s\t%s\n", dir, name);
2262
2263 if (fputs(msg, out_file) != EOF)
2264 ret = 1;
2265 free(msg);
2266 return ret;
2267 }
2268
2269 /*
2270 * update database
2271 */
2272 static int com_upd(int fd, int argc, __unused char *argv[])
2273 {
2274 char *tempname = NULL, *query = NULL;
2275 int ret, out_fd = -1, num = 0;
2276 void *result = NULL;
2277 unsigned int num_rows;
2278 MYSQL_ROW row;
2279
2280 if (argc)
2281 return -E_MYSQL_SYNTAX;
2282 out_file = NULL;
2283 tempname = para_strdup("/tmp/mysql.tmp.XXXXXX");
2284 ret = para_mkstemp(tempname, S_IRUSR | S_IWUSR | S_IRGRP | S_IROTH);
2285 if (ret < 0)
2286 goto out;
2287 out_fd = ret;
2288 out_file = fdopen(out_fd, "w");
2289 if (!out_file) {
2290 close(out_fd);
2291 goto out;
2292 }
2293 if (find_audio_files(conf.mysql_audio_file_dir_arg, mysql_write_tmp_file) < 0)
2294 goto out;
2295 num = ftell(out_file);
2296 /*
2297 * we have to make sure the file hit the disk before we call
2298 * real_query
2299 */
2300 fclose(out_file);
2301 out_file = NULL;
2302 PARA_DEBUG_LOG("wrote tempfile %s (%d bytes)\n", tempname, num);
2303 if (!num)
2304 goto out;
2305 if ((ret = real_query("delete from dir")) < 0)
2306 goto out;
2307 query = make_message("load data infile '%s' into table dir "
2308 "fields terminated by '\t' lines terminated by '\n' "
2309 "(dir, name)", tempname);
2310 ret = real_query(query);
2311 free(query);
2312 if (ret < 0)
2313 goto out;
2314 result = get_result("select dir.name from dir left join data on "
2315 "data.name = dir.name where data.name is NULL");
2316 ret = -E_NORESULT;
2317 if (!result)
2318 goto out;
2319 num_rows = mysql_num_rows(result);
2320 if (!num_rows) {
2321 ret = send_buffer(fd, "no new entries\n");
2322 goto out;
2323 }
2324 while ((row = mysql_fetch_row(result))) {
2325 ret = -E_NOROW;
2326 if (!row[0])
2327 goto out;
2328 send_va_buffer(fd, "new entry: %s\n", row[0]);
2329 query = make_message("insert into data (name, pic_id) values "
2330 "('%s','%s')", row[0], "1");
2331 ret = real_query(query);
2332 free(query);
2333 if (ret < 0)
2334 goto out;
2335 }
2336 ret = 1;
2337 out:
2338 if (out_fd >= 0)
2339 unlink(tempname);
2340 free(tempname);
2341 if (out_file)
2342 fclose(out_file);
2343 if (result)
2344 mysql_free_result(result);
2345 return ret;
2346 }
2347
2348 static char **server_get_audio_file_list(unsigned int num)
2349 {
2350 char **list = para_malloc((num + 1) * sizeof(char *));
2351 char *tmp, *query, *stream = get_current_stream();
2352 void *result = NULL;
2353 unsigned int num_rows;
2354 int i = 0;
2355 MYSQL_ROW row;
2356
2357 tmp = get_query(stream, NULL, 1);
2358 free(stream);
2359 query = make_message("%s limit %d", tmp, num);
2360 free(tmp);
2361 if (!query)
2362 goto err_out;
2363 result = get_result(query);
2364 if (!result)
2365 goto err_out;
2366 num_rows = mysql_num_rows(result);
2367 if (!num_rows)
2368 goto err_out;
2369 for (i = 0; i < num_rows && i < num; i++) {
2370 row = mysql_fetch_row(result);
2371 if (!row || !row[0])
2372 goto err_out;
2373 list[i] = para_strdup(row[0]);
2374 }
2375 list[i] = NULL;
2376 goto success;
2377 err_out:
2378 while (i > 0) {
2379 i--;
2380 free(list[i]);
2381 }
2382 free(list);
2383 list = NULL;
2384 success:
2385 if (query)
2386 free(query);
2387 if (result)
2388 mysql_free_result(result);
2389 return list;
2390 }
2391
2392 /*
2393 * connect to mysql server, return mysql pointer on success, -E_NOTCONN
2394 * on errors. Called from parent on startup and also from com_cdb().
2395 */
2396 static int init_mysql_server(void)
2397 {
2398 char *u = conf.mysql_user_arg? conf.mysql_user_arg : para_logname();
2399
2400 mysql_ptr = mysql_init(NULL);
2401 if (!mysql_ptr) {
2402 PARA_CRIT_LOG("%s", "mysql init error\n");
2403 return -E_NOTCONN;
2404 }
2405 PARA_DEBUG_LOG("connecting: %s@%s:%d\n", u, conf.mysql_host_arg,
2406 conf.mysql_port_arg);
2407 if (!conf.mysql_user_arg)
2408 free(u);
2409 /*
2410 * If host is NULL a connection to the local host is assumed,
2411 * If user is NULL, the current user is assumed
2412 */
2413 if (!(mysql_ptr = mysql_real_connect(mysql_ptr,
2414 conf.mysql_host_arg,
2415 conf.mysql_user_arg,
2416 conf.mysql_passwd_arg,
2417 conf.mysql_database_arg,
2418 conf.mysql_port_arg, NULL, 0))) {
2419 PARA_CRIT_LOG("%s", "connect error\n");
2420 return -E_NOTCONN;
2421 }
2422 PARA_INFO_LOG("%s", "success\n");
2423 return 1;
2424 }
2425
2426 /* mmd lock must be held */
2427 static void write_msg2mmd(int success)
2428 {
2429 sprintf(mmd->selector_info, "dbinfo1:%s\ndbinfo2:mysql-%s\ndbinfo3:\n",
2430 success < 0? PARA_STRERROR(-success) :
2431 "successfully connected to mysql server",
2432 success < 0? "" : mysql_get_server_info(mysql_ptr));
2433 }
2434
2435 /* create database */
2436 static int com_cdb(int fd, int argc, char *argv[])
2437 {
2438 char *query;
2439 int ret;
2440
2441 if (mysql_ptr) {
2442 PARA_INFO_LOG("%s", "closing database\n");
2443 mysql_close(mysql_ptr);
2444 }
2445 /* dont use any database */
2446 conf.mysql_database_arg = NULL; /* leak? */
2447 ret = -E_MYSQL_INIT;
2448 if (init_mysql_server() < 0 || !mysql_ptr)
2449 goto out;
2450 conf.mysql_database_arg = para_strdup((argc < 1)?
2451 "paraslash" : argv[1]);
2452 query = make_message("create database %s", conf.mysql_database_arg);
2453 ret = real_query(query);
2454 free(query);
2455 if (ret < 0)
2456 goto out;
2457 /* reconnect with database just created */
2458 mysql_close(mysql_ptr);
2459 ret = -E_MYSQL_INIT;
2460 if (init_mysql_server() < 0 || !mysql_ptr)
2461 goto out;
2462 mmd_lock();
2463 write_msg2mmd(1);
2464 mmd_unlock();
2465 ret = -E_QFAILED;
2466 if (real_query("create table data (name varchar(255) binary not null "
2467 "primary key, "
2468 "lastplayed datetime not null default "
2469 "'1970-01-01', "
2470 "numplayed int not null default 0, "
2471 "pic_id bigint unsigned not null default 1)") < 0)
2472 goto out;
2473 if (real_query("create table dir (name varchar(255) binary not null "
2474 "primary key, dir varchar(255) default null)") < 0)
2475 goto out;
2476 if (real_query("create table pics ("
2477 "id bigint(20) unsigned not null primary key "
2478 "auto_increment, "
2479 "name varchar(255) binary not null, "
2480 "pic mediumblob not null)") < 0)
2481 goto out;
2482 if (real_query("create table streams ("
2483 "name varchar(255) binary not null primary key, "
2484 "def blob not null)") < 0)
2485 goto out;
2486 if (real_query("insert into streams (name, def) values "
2487 "('current_stream', '(none)')") < 0)
2488 goto out;
2489 ret = send_va_buffer(fd, "successfully created database %s\n",
2490 conf.mysql_database_arg);
2491 out:
2492 return ret;
2493 }
2494
2495 static void shutdown_connection(void)
2496 {
2497 if (mysql_ptr) {
2498 PARA_NOTICE_LOG("%s", "shutting down mysql connection\n");
2499 mysql_close(mysql_ptr);
2500 mysql_ptr = NULL;
2501 }
2502 }
2503
2504 /**
2505 * the init function of the mysql-based audio file selector
2506 *
2507 * Check the command line options and initialize all function pointers of \a db.
2508 * Connect to the mysql server and initialize the info string.
2509 *
2510 * \sa struct audio_file_selector, misc_meta_data::selector_info,
2511 * random_selector.c
2512 */
2513 int mysql_selector_init(struct audio_file_selector *db)
2514 {
2515 int ret;
2516
2517 if (!conf.mysql_passwd_given)
2518 return -E_NO_MYSQL_PASSWD;
2519 if (!conf.mysql_audio_file_dir_given)
2520 return -E_NO_AF_DIR;
2521 db->name = "mysql";
2522 db->cmd_list = cmds;
2523 db->get_audio_file_list = server_get_audio_file_list;
2524 db->update_audio_file = update_audio_file_server_handler;
2525 db->shutdown = shutdown_connection;
2526 ret = init_mysql_server();
2527 if (ret < 0)
2528 PARA_WARNING_LOG("%s\n", PARA_STRERROR(-ret));
2529 write_msg2mmd(ret);
2530 return 1; /* return success even if connect failed to give the
2531 * user the chance to exec com_cdb
2532 */
2533 }