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