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