mod_musicindex  1.4.1
cache-mysql.c
Go to the documentation of this file.
1 /*
2  * cache-mysql.c
3  * mod_musicindex
4  *
5  * $Id: cache-mysql.c 1011 2012-08-07 20:23:39Z varenet $
6  *
7  * Created by Thibaut VARENE on Tue Feb 22 2005.
8  * Copyright (c) 2005,2007,2009-2010,2012 Thibaut VARENE
9  *
10  * This program is free software; you can redistribute it and/or modify
11  * it under the terms of the GNU Lesser General Public License version 2.1,
12  * as published by the Free Software Foundation.
13  *
14  */
15 
56 #include "playlist.h"
57 #include "cache-mysql.h"
58 
59 #include <libgen.h> /* basename() */
60 #include <string.h> /* strdup() */
61 #include <mysql.h>
62 #ifdef HAVE_SYS_STAT_H
63 #include <sys/stat.h> /* file handling */
64 #endif
65 
66 #define TABLE_FILES "musicindexfiles"
67 #define TABLE_DIRS "musicindexdirs"
68 #define TABLE_FORMAT "musicindexformat"
69 #define TABLE_FORMAT_ID 1
70 #define SQL_SMAX_UPD 64
71 #define SQL_SMAX_H 256
72 #define SUBSTRINGIFY(x) STRINGIFY(x)
73 #define STRINGIFY(x) #x
74 #define CACHE_VERS 3
75 #define AINC_OVFLERR 1062
77 /* http://dev.mysql.com/doc/mysql/en/c.html */
78 
80 static struct {
81  char user[SQL_SMAX_UPD];
82  char pass[SQL_SMAX_UPD];
83  char db[SQL_SMAX_UPD];
84  char host[SQL_SMAX_H];
85  short ready;
86 } mysql_params;
87 
89 #define SQL_CDATAF "cvers,mtime,filetype,flags,track,posn," \
90  "date,freq,length,bitrate,size," \
91  "album,artist,title,genre"
92 
93 #define SQL_CDATAN 15
94 
95 #define CA_OK 0
96 #define CA_CREATE 1
97 #define CA_STALE 2
98 #define CA_NOTREADY 3
99 #define CA_FATAL -1
100 
108 static inline mu_ent*
109 mysql_cache_new_ent(apr_pool_t *pool, MYSQL_ROW mysql_row)
110 {
111  mu_ent *p = NEW_ENT(pool);
112  if (unlikely(!p))
113  return p;
114 
115  p->mtime = (unsigned)atol(mysql_row[1]);
116  p->filetype = (signed char)atoi(mysql_row[2]);
117  p->flags = (unsigned char)atoi(mysql_row[3]);
118  p->track = (unsigned char)atoi(mysql_row[4]);
119  p->posn = (unsigned char)atoi(mysql_row[5]);
120  p->date = (unsigned short)atoi(mysql_row[6]);
121  p->freq = (unsigned short)atoi(mysql_row[7]);
122  p->length = (unsigned short)atoi(mysql_row[8]);
123  p->bitrate = (unsigned)atol(mysql_row[9]);
124  p->size = (unsigned)atol(mysql_row[10]);
125  p->album = mysql_row[11] ? apr_pstrdup(pool, mysql_row[11]) : NULL;
126  p->artist = mysql_row[12] ? apr_pstrdup(pool, mysql_row[12]) : NULL;
127  p->title = apr_pstrdup(pool, mysql_row[13]);
128  p->genre = mysql_row[14] ? apr_pstrdup(pool, mysql_row[14]) : NULL;
129 
130  return p;
131 }
132 
150 static int
151 mysql_cache_init(server_rec *s, const char *const setup_string)
152 {
153  MYSQL *mysql;
154  MYSQL_RES *mysql_res = NULL;
155  MYSQL_ROW mysql_row;
156  unsigned short len, create_dirs = 1, create_files = 1, create_format = 1;
157  char *temp;
158  int ret = -1;
159 
160  /* XXX TODO indexes */
161  const char const drop_tformat[] = "DROP TABLE IF EXISTS `" TABLE_FORMAT "`";
162  const char const create_tformat[] = "CREATE TABLE `" TABLE_FORMAT "` ("
163  "`formatid` TINYINT UNSIGNED NOT NULL DEFAULT " SUBSTRINGIFY(TABLE_FORMAT_ID) ")";
164  const char const init_tformat[] = "INSERT INTO `" TABLE_FORMAT "` () VALUES ()";
165 
166  const char const drop_tdirs[] = "DROP TABLE IF EXISTS `" TABLE_DIRS "`";
167  const char const create_tdirs[] = "CREATE TABLE `" TABLE_DIRS "` ("
168  "`id` SMALLINT UNSIGNED SERIAL DEFAULT VALUE," /* 65535 dirs should be plenty enough :P */
169  "`timestamp` INT UNSIGNED,"
170 #if MYSQL_VERSION_ID > 50003 /* MySQL 5.0.3 and above support up to 65,535 wide CHAR columns */
171  "`fullpath` VARCHAR(" SUBSTRINGIFY(MAX_PATHNAME) ") CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,"
172  "INDEX `check` (`fullpath`))"
173 #else /* earlier versions stop at 255. No point in creating a fulltext index, we're not MATCH()'ing strings */
174  "`fullpath` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,"
175 #endif
176  "CHARACTER SET = utf8,"
177  "COLLATE = utf8_bin";
178 
179  /* XXX hardcoded genre length - we have to use _ci collation for the fields that are used
180  * in the search FULLTEXT index, otherwise MATCH() is case sensitive. Use
181  * COLLATE utf8_bin for all case sensitive searches */
182  const char const drop_tfiles[] = "DROP TABLE IF EXISTS `" TABLE_FILES "`";
183  const char const create_tfiles[] = "CREATE TABLE `" TABLE_FILES "` ("
184  "`id` MEDIUMINT UNSIGNED SERIAL DEFAULT VALUE," /* 16,777,215 files. Call me when you overflow! */
185  "`pid` SMALLINT UNSIGNED NOT NULL REFERENCES `" TABLE_DIRS "`(`id`)," /* XXX should eventually be a FOREIGN KEY() */
186  "`filetype` TINYINT NOT NULL," /* XXX this should be an enum, but I can't figure out how to do it without hardcoding values */
187  "`flags` TINYINT UNSIGNED NOT NULL,"
188  "`track` TINYINT UNSIGNED NOT NULL,"
189  "`posn` TINYINT UNSIGNED NOT NULL,"
190  "`cvers` TINYINT UNSIGNED NOT NULL," /* XXX this should be useless since we trash the whole cache on version bumps... */
191  "`date` SMALLINT UNSIGNED NOT NULL,"
192  "`freq` SMALLINT UNSIGNED NOT NULL,"
193  "`length` SMALLINT UNSIGNED NOT NULL,"
194  "`bitrate` MEDIUMINT UNSIGNED NOT NULL,"
195  "`size` INT UNSIGNED NOT NULL,"
196  "`mtime` INT UNSIGNED NOT NULL,"
197  "`genre` VARCHAR(" SUBSTRINGIFY(MAX_GENRE) ") CHARACTER SET utf8 COLLATE utf8_unicode_ci,"
198  "`album` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci,"
199  "`artist` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci,"
200  "`title` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,"
201  "`filename` VARCHAR(" SUBSTRINGIFY(MAX_FNAME) ") CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,"
202  "INDEX `make_entry` (`pid`,`filename`),"
203  "FULLTEXT INDEX `search` (`filename`,`title`,`artist`,`album`))"
204  "CHARACTER SET = utf8,"
205  "COLLATE = utf8_bin";
206 
207  /* from http://dev.mysql.com/doc/mysql/en/fulltext-search.html
208  For natural-language full-text searches, it is a requirement that the columns named
209  in the MATCH() function be the same columns included in some FULLTEXT index in
210  your table. For the preceding query, note that the columns named in the MATCH()
211  function (title and body) are the same as those named in the definition of the article
212  table's FULLTEXT index. If you wanted to search the title or body separately, you would
213  need to create FULLTEXT indexes for each column. */
214 
215  /* get the fields of interest:
216  user[:pass]@host/db
217  setup struct mysql_params accordingly */
218  mysql_params.ready = 0;
219 
220  /* first, check whether we have a passwd */
221  temp = strchr(setup_string, ':');
222 
223  if (temp) {
224  /* copy the passwd */
225  len = strchr(setup_string, '@') - (temp+1);
226  if (len >= SQL_SMAX_UPD)
227  goto err1;
228 
229  strncpy(mysql_params.pass, temp+1, len);
230  mysql_params.pass[len] = '\0';
231 
232  /* store user field length */
233  len = temp - (setup_string);
234  }
235  else {
236  /* NULLify for safety */
237  mysql_params.pass[0] = '\0';
238 
239  /* store user field length */
240  len = strchr(setup_string, '@') - (setup_string);
241  }
242 
243  /* copy the user field anyway */
244  if (len >= SQL_SMAX_UPD)
245  goto err1;
246 
247  strncpy(mysql_params.user, setup_string, len);
248  mysql_params.user[len] = '\0';
249 
250  /* as well as the host field */
251  temp = strchr(setup_string, '@')+1;
252  len = strchr(temp, '/') - temp;
253 
254  if (len >= SQL_SMAX_H)
255  goto err1;
256 
257  strncpy(mysql_params.host, temp, len);
258  mysql_params.host[len] = '\0';
259 
260  /* be done with db field */
261  temp = strchr(temp, '/')+1;
262  len = strlen(temp);
263 
264  if (len >= SQL_SMAX_UPD)
265  goto err1;
266 
267  strcpy(mysql_params.db, temp);
268 
269  mysql_params.ready = 1;
270 
271  /* perform sanity check for DB access
272  mysqlconnect/selectdb...
273  create the tables if they are empty/non existent
274  (do that ourselves so that we can drop them if we change the format */
275 
276  mysql = mysql_init(NULL);
277 
278  if (!mysql_real_connect(mysql, mysql_params.host, mysql_params.user,
279  mysql_params.pass, mysql_params.db, 0, NULL, 0))
280  goto err2;
281 
282  if (mysql_set_character_set(mysql, "utf8"))
283  goto err2;
284 
285  mysql_res = mysql_list_tables(mysql, NULL); /* will be freed later */
286  if (!mysql_res)
287  goto err2;
288 
289  /* goto algorithm bullshit
290  1. check we have >0 tables
291  no -> break, (drop existing), create everything
292  yes:
293  2. check we have format table
294  no -> break, drop existing, create everything
295  yes:
296  3. check we have good format
297  no -> break, drop existing, create everything
298  yes:
299  4. check we have all tables
300  no -> drop possibly out of sync tables, create missing
301  */
302 
303  /* 1. */
304  if (mysql_num_rows(mysql_res) <= 0)
305  goto create;
306 
307  /* 2. */
308  while ((mysql_row = mysql_fetch_row(mysql_res))) {
309  if (!strcmp(mysql_row[0], TABLE_FORMAT)) {
310  create_format = 0;
311  break;
312  }
313  }
314 
315  if (create_format)
316  goto create;
317 
318  /* 3. */
319  mysql_free_result(mysql_res);
320  mysql_res = NULL;
321  if (mysql_query(mysql, "SELECT formatid FROM `" TABLE_FORMAT "`"))
322  goto err2;
323 
324  mysql_res = mysql_use_result(mysql);
325  mysql_row = mysql_fetch_row(mysql_res);
326 
327  if (!mysql_row)
328  goto err2;
329 
330  if (((unsigned)atoi(mysql_row[0]) != TABLE_FORMAT_ID)) {
331  create_format = 1; /* hard update format table */
332  goto create;
333  }
334 
335  /* 4. */
336  mysql_free_result(mysql_res); /* clear the previous result first and... */
337  mysql_res = mysql_list_tables(mysql, NULL); /* ...make sure we're 'rewinded' */
338  if (!mysql_res)
339  goto err2;
340 
341  while ((mysql_row = mysql_fetch_row(mysql_res))) {
342  if (!strcmp(mysql_row[0], TABLE_FILES))
343  create_files = 0;
344  if (!strcmp(mysql_row[0], TABLE_DIRS))
345  create_dirs = 0;
346  }
347 
348  if (1 == create_dirs) /* if dirs is missing, (re)create files no matter what */
349  create_files = 1; /* files depends on dirs for pid */
350 
351 create:
352  mysql_free_result(mysql_res);
353  mysql_res = NULL;
354 
355  if (create_dirs) {
356  if (mysql_query(mysql, drop_tdirs))
357  goto err2;
358  if (mysql_query(mysql, create_tdirs))
359  goto err2;
360  }
361  if (create_files) {
362  if (mysql_query(mysql, drop_tfiles))
363  goto err2;
364  if (mysql_query(mysql, create_tfiles))
365  goto err2;
366  }
367  if (create_format) {
368  if (mysql_query(mysql, drop_tformat))
369  goto err2;
370  if (mysql_query(mysql, create_tformat))
371  goto err2;
372  if (mysql_query(mysql, init_tformat))
373  goto err2;
374  }
375 
376  ret = 0;
377 
378  /* optimize table upon server startup. Helps keeping them fresh, and
379  * is harmless if they were just created */
380  mysql_query(mysql, "OPTIMIZE TABLE `" TABLE_DIRS "`, `" TABLE_FILES "`");
381 
382 err2:
383  if (mysql_errno(mysql))
384  mi_serror("An error occured: %s", mysql_error(mysql));
385  mysql_free_result(mysql_res);
386  mysql_close(mysql);
387 err1:
388  if (!mysql_params.ready)
389  mi_serror("Likely length overflow in configuration fields: "
390  "user: %zu/%u, pass: %zu/%u, db: %zu/%u, host: %zu/%u",
391  strlen(mysql_params.user), SQL_SMAX_UPD, strlen(mysql_params.pass),
392  SQL_SMAX_UPD, strlen(mysql_params.db), SQL_SMAX_UPD,
393  strlen(mysql_params.host), SQL_SMAX_H);
394  return ret;
395 
396 }
397 
409 static void
410 mysql_cache_trunc_tables(request_rec *r, MYSQL *mysql)
411 {
412  if (mysql_query(mysql, "TRUNCATE TABLE `" TABLE_FILES "`"))
413  goto exit;
414  mysql_query(mysql, "TRUNCATE TABLE `" TABLE_DIRS "`");
415 
416  /* since we just did a major change, let's defragment too */
417  mysql_query(mysql, "OPTIMIZE TABLE `" TABLE_DIRS "`, `" TABLE_FILES "`");
418 
419 exit:
420  if (mysql_errno(mysql))
421  mi_rerror("An error occured: %s", mysql_error(mysql));
422 }
423 
439 static int
440 mysql_cache_make_dir(request_rec *r, const char *const dirpath,
441  const unsigned long timestamp, MYSQL *mysql)
442 {
443  char *restrict sqlstr, *restrict query = NULL;
444  MYSQL_RES *mysql_res = NULL;
445  int ret = CA_FATAL;
446  unsigned int myerrno = 0;
447 
448  if (!(sqlstr = apr_palloc(r->pool, 2*strlen(dirpath)+1)))
449  goto error;
450 
451  mysql_real_escape_string(mysql, sqlstr, dirpath, strlen(dirpath));
452 
453  /* ON DUPLICATE cannot deal with long paths (UNIQUE keys must be <1000 bytes),
454  * we need to do the check by hand:
455  * SELECT; if (num_rows > 0) UPDATE else INSERT
456  * Since this is no longer an atomic SQL operation, we have to lock.
457  * Using LOCK_TABLES as updates shouldn't happen frequently anyway.
458  * See http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html */
459 
460  mysql_query(mysql, "LOCK TABLES " TABLE_DIRS " WRITE");
461 
462  query = apr_psprintf(r->pool, "SELECT `id` FROM `" TABLE_DIRS "` WHERE `fullpath`='%s'", sqlstr);
463 
464  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
465  goto error;
466 
467  mysql_res = mysql_store_result(mysql);
468 
469  if (mysql_num_rows(mysql_res))
470  query = apr_psprintf(r->pool, "UPDATE `" TABLE_DIRS "` SET `timestamp`='%lu' "
471  "WHERE `fullpath`='%s'", timestamp, sqlstr);
472  else
473  query = apr_psprintf(r->pool, "INSERT INTO `" TABLE_DIRS "` (timestamp, fullpath) VALUES ('%lu','%s')",
474  timestamp, sqlstr);
475 
476  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
477  goto error;
478 
479  ret = CA_OK;
480 
481 error:
482  myerrno = mysql_errno(mysql);
483  if (unlikely(myerrno))
484  query = apr_pstrdup(r->pool, (char *)mysql_error(mysql)); /* static buffer */
485 
486  mysql_query(mysql, "UNLOCK TABLES");
487 
488  mysql_free_result(mysql_res);
489 
490  if (unlikely(myerrno)) {
491  if (AINC_OVFLERR == myerrno)
492  mysql_cache_trunc_tables(r, mysql);
493  else
494  mi_rerror("An error occured: %s", query);
495  }
496  return ret;
497 }
498 
514 static int
515 mysql_cache_remove_dir(request_rec *r, const char *const curdir, MYSQL *mysql)
516 {
517  char *restrict sqlstr, *restrict query = NULL;
518  int ret = CA_FATAL;
519 
520  if (!(sqlstr = apr_palloc(r->pool, 2*strlen(curdir)+1)))
521  goto error;
522 
523  mysql_real_escape_string(mysql, sqlstr, curdir, strlen(curdir));
524 
525  query = apr_psprintf(r->pool, "DELETE FROM `" TABLE_FILES "` WHERE `pid` IN (SELECT id FROM "
526  TABLE_DIRS " WHERE `fullpath` LIKE '%s%%')", sqlstr);
527 
528  mysql_query(mysql, "LOCK TABLES " TABLE_DIRS " WRITE, " TABLE_FILES " WRITE");
529 
530  if (mysql_query(mysql, query))
531  goto error;
532 
533  /* If we don't trash the subtree we'll keep junk in the cache */
534  query = apr_psprintf(r->pool, "DELETE FROM `" TABLE_DIRS "` WHERE `fullpath` LIKE '%s%%'", sqlstr);
535 
536  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
537  goto error;
538 
539  ret = CA_OK;
540 
541 error:
542  if (unlikely(mysql_errno(mysql)))
543  mi_rerror("An error occured: %s", mysql_error(mysql));
544  mysql_query(mysql, "UNLOCK TABLES");
545  return ret;
546 }
547 
570 static int
571 mysql_cache_check_dir(request_rec *r, const char *const path, MYSQL *mysql)
572 {
573  MYSQL_RES *mysql_res = NULL;
574  MYSQL_ROW mysql_row;
575  int ret = CA_FATAL;
576  char *restrict sqlstr = NULL, *restrict query = NULL;
577  struct stat dirstat;
578 
579  if (unlikely(!path))
580  return ret;
581 
582  if (unlikely(!(sqlstr = malloc(2*strlen(path)+1))))
583  goto error;
584 
585  mysql_real_escape_string(mysql, sqlstr, path, strlen(path));
586 
587  /* XXX MEM OPTIM: asprintf() */
588  query = apr_psprintf(r->pool, "SELECT timestamp FROM `" TABLE_DIRS "` WHERE `fullpath`='%s'", sqlstr);
589  free(sqlstr);
590 
591  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
592  goto error;
593 
594  mysql_res = mysql_store_result(mysql);
595 
596  if ((mysql_num_rows(mysql_res) == 0))
597  ret = CA_CREATE;
598  else {
599  /* Checking for cache sanity. */
600  unsigned long test;
601  mysql_row = mysql_fetch_row(mysql_res);
602 
603  if (unlikely(!mysql_row))
604  goto error;
605 
606  ret = CA_OK;
607 
608  stat(path, &dirstat);
609 
610  test = (unsigned)atol(mysql_row[0]);
611  if (test < dirstat.st_mtime) {
612  if (0 == test)
613  ret = CA_NOTREADY;
614  else
615  ret = CA_STALE;
616  }
617  }
618 
619 error:
620  mysql_free_result(mysql_res);
621 
622  if (unlikely(mysql_errno(mysql)))
623  mi_rerror("An error occured: %s", mysql_error(mysql));
624  return ret;
625 }
626 
646 static mu_ent*
647 mysql_cache_make_entry(request_rec *r, apr_pool_t *pool, FILE *const in,
648  const char *const filename)
649 {
650  const mu_config *const conf = (mu_config *)ap_get_module_config(r->per_dir_config, &musicindex_module);
651  MYSQL *mysql = conf->cache_setup;
652  MYSQL_RES *mysql_res = NULL;
653  MYSQL_ROW mysql_row;
654  mu_ent *p = NULL;
655  char * restrict sqldirn = NULL, * restrict sqlbasen = NULL, * restrict query = NULL;
656  char *dirs = NULL, *bases = NULL, *dirn = NULL, *basen = NULL;
657  struct stat statbuf;
658 
659  if (unlikely((!mysql) || (!mysql_params.ready)))
660  return p;
661 
662  if (unlikely(stat(filename, &statbuf)))
663  return p;
664 
665  dirs = strdup(filename);
666  bases = strdup(filename);
667  if (unlikely(!dirs || !bases))
668  goto exit;
669 
670  dirn = dirname(dirs);
671  basen = basename(bases);
672 
673  sqldirn = malloc(2*strlen(dirn)+1);
674  sqlbasen = malloc(2*strlen(basen)+1);
675  if (unlikely(!sqldirn || !sqlbasen))
676  goto exit;
677 
678  mysql_real_escape_string(mysql, sqldirn, dirn, strlen(dirn));
679  mysql_real_escape_string(mysql, sqlbasen, basen, strlen(basen));
680 
681  /* XXX MEM OPTIM: asprintf() */
682  query = apr_psprintf(pool, "SELECT " SQL_CDATAF " FROM `" TABLE_FILES
683  "` WHERE `pid`=(SELECT `id` from `" TABLE_DIRS "` WHERE `fullpath`='%s')"
684  "AND `filename`='%s' COLLATE utf8_bin",
685  sqldirn, sqlbasen);
686 
687  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
688  goto error;
689 
690  mysql_res = mysql_store_result(mysql);
691 
692  /* Actually check for the file in the cache. */
693  if ((mysql_num_rows(mysql_res) == 0))
694  goto exit; /* creation of the cache entry is handled separately */
695 
696  /* XXX we don't check whether there's more than 1 result. That *outta* be impossible */
697  mysql_row = mysql_fetch_row(mysql_res);
698 
699  if (unlikely(!mysql_row))
700  goto error;
701 
702  /* Check whether the cache is somehow invalid */
703  if ((unlikely(((unsigned short)atoi(mysql_row[0]) < CACHE_VERS))) ||
704  (unlikely((unsigned)atol(mysql_row[1]) < statbuf.st_mtime)) )
705  goto exit;
706 
707  p = mysql_cache_new_ent(pool, mysql_row);
708  if (likely(p))
709  fclose(in); /* mandatory */
710 
711 error:
712  if (unlikely(mysql_errno(mysql)))
713  mi_rerror("An error occured: %s", mysql_error(mysql));
714 exit:
715  mysql_free_result(mysql_res);
716  free(dirs), free(bases);
717  free(sqldirn), free(sqlbasen);
718  return p;
719 }
720 
733 static void
734 mysql_cache_write(request_rec *r, const mu_ent *const p, const char *const filename)
735 {
736  const mu_config *const conf = (mu_config *)ap_get_module_config(r->per_dir_config, &musicindex_module);
737  MYSQL *mysql = conf->cache_setup;
738  MYSQL_RES *mysql_res = NULL;
739  char * restrict sqldirn = NULL, * restrict sqlbasen = NULL, * restrict query = NULL;
740  char * restrict sqlalb = NULL, * restrict sqlart = NULL, * restrict sqltit = NULL, * restrict sqlgen = NULL;
741  char *dirs = NULL, *bases = NULL, *dirn = NULL, *basen = NULL;
742  unsigned int myerrno = 0;
743 
744  if (unlikely((!mysql) || (!mysql_params.ready)))
745  return;
746 
747  /* if we have a directory, it could be a child dir in a given directory listing
748  * we need to record it so that mysql_cache_dircontents() works properly, but
749  * we also need to differentiate from normally created dirs by making sure that
750  * mysql_cache_dircontents() will not try to return its content later on. We
751  * thus "invalidate" this cache entry by giving it a 0 timestamp */
752  if (p->filetype < 0) {
753  int check = mysql_cache_check_dir(r, filename, mysql);
754  if (CA_CREATE == check)
755  mysql_cache_make_dir(r, filename, 0, mysql);
756  goto exit;
757  }
758 
759  dirs = strdup(filename);
760  bases = strdup(filename);
761  if (unlikely(!dirs || !bases))
762  goto exit;
763 
764  dirn = dirname(dirs);
765  basen = basename(bases);
766 
767  sqldirn = malloc(2*strlen(dirn)+1);
768  sqlbasen = malloc(2*strlen(basen)+1);
769  if (unlikely(!sqldirn || !sqlbasen))
770  goto exit;
771 
772  mysql_real_escape_string(mysql, sqldirn, dirn, strlen(dirn));
773  mysql_real_escape_string(mysql, sqlbasen, basen, strlen(basen));
774 
775  /* Prepare the strings properly */
776  if (p->album) {
777  if (unlikely(!(sqlalb = malloc(2*strlen(p->album)+1))))
778  goto exit;
779  mysql_real_escape_string(mysql, sqlalb, p->album, strlen(p->album));
780  }
781  if (p->artist) {
782  if (unlikely(!(sqlart = malloc(2*strlen(p->artist)+1))))
783  goto exit;
784  mysql_real_escape_string(mysql, sqlart, p->artist, strlen(p->artist));
785  }
786  if (p->genre) {
787  if (unlikely(!(sqlgen = malloc(2*strlen(p->genre)+1))))
788  goto exit;
789  mysql_real_escape_string(mysql, sqlgen, p->genre, strlen(p->genre));
790  }
791  if (unlikely(!(sqltit = malloc(2*strlen(p->title)+1))))
792  goto exit;
793  mysql_real_escape_string(mysql, sqltit, p->title, strlen(p->title));
794 
795  /* We need to lock here because the rest of the logic relies on the results
796  * of this first SELECT */
797  mysql_query(mysql, "LOCK TABLES " TABLE_FILES " WRITE, " TABLE_DIRS " READ");
798 
799  query = apr_psprintf(r->pool, "SELECT `id` FROM `" TABLE_DIRS "` WHERE `fullpath`='%s'", sqldirn);
800 
801  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
802  goto error;
803 
804  mysql_res = mysql_store_result(mysql);
805 
806  /* if the cache directory doesn't exist that means either we haven't called cache_check_dir
807  first or it's been trashed since then. Have to get out of here. */
808  if (unlikely(mysql_num_rows(mysql_res) == 0))
809  goto error;
810 
811  mysql_free_result(mysql_res);
812  mysql_res = NULL;
813 
814  query = apr_psprintf(r->pool, "SELECT `id` FROM `" TABLE_FILES "` WHERE `filename`='%s' COLLATE utf8_bin "
815  "AND pid=(SELECT `id` FROM `" TABLE_DIRS "` WHERE `fullpath`='%s')",
816  sqlbasen, sqldirn);
817 
818  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
819  goto error;
820 
821  mysql_res = mysql_store_result(mysql);
822 
823  if (unlikely(mysql_num_rows(mysql_res))) {
824  /* This update might happen if dircontents() found an out of date entry */
825  /* XXX there shouldn't be more than 1 row */
826  MYSQL_ROW mysql_row;
827 
828  mysql_row = mysql_fetch_row(mysql_res);
829 
830  query = apr_psprintf(r->pool, "UPDATE `" TABLE_FILES
831  "` SET cvers='%hi',filetype='%hu',flags='%hu',track='%hu',"
832  "posn='%hu',date='%hu',freq='%hu',length='%hu',"
833  "bitrate='%lu',size='%lu',mtime='%lu',title='%s'",
834  CACHE_VERS, p->filetype, (p->flags & EF_FLAGSTOSAVE), p->track, p->posn, p->date,
835  p->freq, p->length, p->bitrate, p->size, p->mtime, sqltit);
836 
837  query = apr_pstrcat(r->pool, query, /* XXX no alloc error checking on psprintf() */
838  sqlalb ? apr_psprintf(r->pool, ",album='%s'", sqlalb) : ",album=NULL",
839  sqlart ? apr_psprintf(r->pool, ",artist='%s'", sqlart) : ",artist=NULL",
840  sqlgen ? apr_psprintf(r->pool, ",genre='%s'", sqlgen) : ",genre=NULL",
841  apr_psprintf(r->pool, " WHERE id='%lu'", (unsigned long)atol(mysql_row[0])), NULL);
842  }
843  else {
844  query = apr_psprintf(r->pool, "INSERT INTO `" TABLE_FILES
845  "` (`pid`,`cvers`,`filetype`,`flags`,`track`,`posn`,`date`,`freq`,"
846  "`length`,`bitrate`,`size`,`mtime`,`title`,`filename`,`album`,`artist`,"
847  "`genre`) VALUES "
848  "((SELECT `id` FROM `" TABLE_DIRS "` WHERE `fullpath`='%s'),"
849  "'%hi','%hu','%hu','%hu','%hu','%hu','%hu','%hu',"
850  "'%lu','%lu','%lu','%s','%s',",
851  sqldirn, CACHE_VERS, p->filetype, (p->flags & EF_FLAGSTOSAVE), p->track, p->posn, p->date,
852  p->freq, p->length, p->bitrate, p->size, p->mtime, sqltit, sqlbasen);
853  query = apr_pstrcat(r->pool, query,
854  sqlalb ? apr_psprintf(r->pool, "'%s',", sqlalb) : "NULL,", /* XXX no alloc error checking on psprintf() */
855  sqlart ? apr_psprintf(r->pool, "'%s',", sqlart) : "NULL,",
856  sqlgen ? apr_psprintf(r->pool, "'%s'", sqlgen) : "NULL",
857  ")", NULL);
858  }
859 
860  /* submit the query */
861  if (likely(query))
862  mysql_query(mysql, query);
863 
864 error:
865  myerrno = mysql_errno(mysql);
866  if (unlikely(myerrno)) {
867  query = apr_pstrdup(r->pool, (char *)mysql_error(mysql)); /* have to dup as mysql_error() sends a static buffer */
868  mysql_cache_make_dir(r, dirn, 0, mysql); /* the query failed we must invalidate the directory. XXX no fail check */
869  }
870 
871  mysql_query(mysql, "UNLOCK TABLES");
872 
873  if (unlikely(myerrno)) {
874  if (AINC_OVFLERR == myerrno)
875  mysql_cache_trunc_tables(r, mysql);
876  else
877  mi_rerror("An error occured: %s", query);
878  }
879 exit:
880  mysql_free_result(mysql_res);
881  free(dirs), free(bases);
882  free(sqldirn), free(sqlbasen);
883  free(sqlalb), free(sqlart), free(sqlgen), free(sqltit);
884  return;
885 }
886 
888 typedef struct mysql_dir {
889  char *d_name;
890  const struct mysql_dir *next;
891 } mysql_dir;
892 
918 static mysql_dir
919 *mysql_cache_dircontents(request_rec *r, apr_pool_t *pool, mu_pack *const pack,
920  const char *const filename, const char *const uri, MYSQL *mysql, unsigned long soptions)
921 {
922  const mu_config *const conf = (mu_config *)ap_get_module_config(r->per_dir_config, &musicindex_module);
923  MYSQL_RES *mysql_res;
924  MYSQL_ROW mysql_row;
925  mu_ent *p = NULL;
926  const mu_ent *prev = pack->head;
927  mysql_dir *mhead = NULL, *mdir = NULL, *ret = NULL;
928  char *restrict sqlstr = NULL, *restrict query = NULL;
929  char *restrict basen = NULL, *restrict fullpath = NULL;
930  unsigned long nb = 0, size = 0;
931  const size_t fnlen = strlen(filename);
932 
933  /* 1. we need to treat *only* the immediate subdirs, as we can't assert that treating all
934  subdirs is idempotent (eg: won't duplicate work with the rest of make_music_entry()).
935  To the contrary, we would have duplicate entries if we sent all subdirs */
936 
937  sqlstr = malloc(2*fnlen+1);
938  if (unlikely(!sqlstr))
939  goto error;
940 
941  mysql_real_escape_string(mysql, sqlstr, filename, fnlen);
942 
943  /* this request should hopefully return only immediate subdirs,
944  as it will only match entries not containing any '/' after the current
945  path. Indeed, we always strip trailing '/' in mysql_cache_check_dir().
946  XXX correct modifier for size_t is 'z' but seems (some versions of?)
947  apache doesn't know about it. */
948  /* XXX MEM OPTIM: asprintf() */
949  query = apr_psprintf(pool, "SELECT fullpath FROM " TABLE_DIRS " WHERE "
950  "`fullpath` LIKE '%s/%%' AND (LOCATE('/', fullpath, %lu)=0)",
951  sqlstr, strlen(sqlstr)+2);
952 
953  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
954  goto error;
955 
956  mysql_res = mysql_use_result(mysql);
957 
958  while ((mysql_row = mysql_fetch_row(mysql_res))) {
959  basen = apr_pstrdup(pool, mysql_row[0]+(fnlen+1));
960  /* simulate basename by skipping the prefix path */
961  mdir = (mysql_dir *)apr_pcalloc(pool, sizeof(mysql_dir));
962  if (likely(basen && mdir)) {
963  mdir->d_name = basen;
964  mdir->next = mhead;
965  mhead = mdir;
966  }
967  }
968 
969  mysql_free_result(mysql_res);
970  mysql_res = NULL;
971  /* 1. done */
972 
973  /* 2. */
974  /* we're reusing the previous content of sqlstr */
975  /* XXX MEM OPTIM: asprintf() */
976  query = apr_psprintf(pool, "SELECT " SQL_CDATAF ",filename FROM `" TABLE_FILES
977  "` WHERE `pid`="
978  "(SELECT `id` from `" TABLE_DIRS "` WHERE `fullpath`='%s')",
979  sqlstr);
980 
981  /* attention, MATCH has a builtin list of stopwords, see
982  * http://dev.mysql.com/doc/refman/4.1/en/fulltext-natural-language.html */
983  if (unlikely(conf->search)) {
984  char * restrict escsearch = apr_palloc(pool, 2*strlen(conf->search)+1);
985  if (!escsearch)
986  goto error;
987  mysql_real_escape_string(mysql, escsearch, conf->search, strlen(conf->search));
988  query = apr_pstrcat(pool, query, " AND MATCH(`filename`,`title`,`artist`,`album`) AGAINST ('",
989  escsearch, "')", NULL);
990  }
991 
992  if (unlikely(!query) || unlikely(mysql_query(mysql, query)))
993  goto error;
994 
995  mysql_res = mysql_use_result(mysql);
996 
997  /* filename + '/' + '\0' */
998  fullpath = malloc(fnlen + 2);
999  if (!fullpath) {
1000  ret = NULL;
1001  goto error;
1002  }
1003  strncpy(fullpath, filename, fnlen);
1004  fullpath[fnlen] = '/';
1005  fullpath[fnlen+1] = '\0';
1006 
1007  while ((mysql_row = mysql_fetch_row(mysql_res))) {
1008  /* Check whether the cache is somehow invalid, if so, requeue the file */
1009  if (unlikely(((unsigned)atoi(mysql_row[0]) < CACHE_VERS))) {
1010  /* XXX MEM OPTIM: malloc here, free in readdir() */
1011  basen = apr_pstrdup(pool, mysql_row[SQL_CDATAN]);
1012  mdir = (mysql_dir *)apr_pcalloc(pool, sizeof(mysql_dir));
1013  if (likely(basen && mdir)) {
1014  mdir->d_name = basen;
1015  mdir->next = mhead;
1016  mhead = mdir;
1017  }
1018  }
1019  else {
1020  p = mysql_cache_new_ent(pool, mysql_row);
1021  if (likely(p)) {
1022  struct stat statbuf;
1023  fullpath = realloc(fullpath, fnlen + 1 + strlen(mysql_row[SQL_CDATAN]) + 1);
1024  if (unlikely(!fullpath)) {
1025  ret = NULL;
1026  goto error;
1027  }
1028  fullpath[fnlen+1] = '\0'; /* ignore previous entry */
1029  strncat(fullpath, mysql_row[SQL_CDATAN], strlen(mysql_row[SQL_CDATAN]));
1030 
1031  /* Here we check 1) that the file still exists (stat will fail otherwise) and
1032  * 2) that it hasn't been modified since it went into the cache
1033  * If 1) is not verified, we trash the directory cache and exit
1034  * If 2) is not verified, we requeue the file */
1035  if (unlikely(stat(fullpath, &statbuf))) {
1036  mi_rdebug("Trashing cache for: %s", filename);
1037  mysql_free_result(mysql_res);
1038  mysql_cache_remove_dir(r, filename, mysql);
1039  ret = NULL;
1040  goto error;
1041  }
1042  if (unlikely(p->mtime < statbuf.st_mtime)) {
1043  mi_rdebug("Requeuing: %s", fullpath);
1044  basen = apr_pstrdup(pool, mysql_row[SQL_CDATAN]);
1045  mdir = (mysql_dir *)apr_pcalloc(pool, sizeof(mysql_dir));
1046  if (likely(basen && mdir)) {
1047  mdir->d_name = basen;
1048  mdir->next = mhead;
1049  mhead = mdir;
1050  }
1051  continue;
1052  }
1053 
1054  p->uri = apr_pstrcat(pool, uri, mysql_row[SQL_CDATAN], NULL);
1055  p->filename = p->file = p->uri;
1056  p->filename = (strrchr(p->filename, '/') + 1);
1057  P_FLAGS_OPTIONS(p, soptions);
1058  if ((soptions & MI_CUSTOM) == 0)
1059  p->file += strlen(r->parsed_uri.path); /* offset the path before the filename relative to request current dir, except for custom playlists */
1060  if (conf->options & MI_TARBALL)
1061  p->filename = apr_pstrdup(pool, fullpath); /* tarball needs full path for access */
1062  p->flags |= EF_INCACHE;
1063  nb++;
1064  size += p->size;
1065  p->next = prev;
1066  prev = p;
1067  }
1068  }
1069  }
1070 
1071  mysql_free_result(mysql_res);
1072  /* 2. done */
1073 
1074  if (likely(p)) {
1075  pack->filenb += nb;
1076  pack->fsize += size;
1077  pack->head = p;
1078  }
1079 
1080  ret = (mysql_dir *)apr_pcalloc(pool, sizeof(mysql_dir));
1081  if (unlikely(!ret))
1082  goto error;
1083  ret->d_name = NULL;
1084  ret->next = mhead;
1085 
1086 error:
1087  if (unlikely(mysql_errno(mysql)))
1088  mi_rerror("An error occured: %s", mysql_error(mysql));
1089  free(sqlstr), free(fullpath);
1090  return ret;
1091 }
1092 
1112 static void
1113 *mysql_cache_opendir(request_rec *r, mu_pack *const pack, const char * const filename,
1114  const char * const uri, unsigned long soptions)
1115 {
1116  const mu_config *const conf = (mu_config *)ap_get_module_config(r->per_dir_config, &musicindex_module);
1117  MYSQL *mysql = conf->cache_setup;
1118  int val;
1119  mysql_dir *mdir = NULL;
1120  void *ret = NULL;
1121  struct stat dirstat;
1122 
1123  if (unlikely((!mysql) || (!mysql_params.ready))) {
1124  mi_rdebug("FATAL: Entered with mysql=%x, mysql_params.ready=%d", mysql, mysql_params.ready);
1125  goto error;
1126  }
1127 
1128  val = mysql_cache_check_dir(r, filename, mysql);
1129  switch (val) {
1130  case CA_OK:
1131  break;
1132  case CA_STALE:
1133  mysql_cache_remove_dir(r, filename, mysql);
1134  case CA_CREATE:
1135  case CA_NOTREADY:
1136  if (likely(!(conf->options & MI_QUICKPL))) {
1137  /* XXX HACK. This is a gross workaround until the interface
1138  * is better specified: we don't want to record a directory
1139  * in the cache as valid when we're not going to record any
1140  * content for it, which happens when MI_QUICKPL is on */
1141  stat(filename, &dirstat);
1142  mysql_cache_make_dir(r, filename, dirstat.st_mtime, mysql);
1143  }
1144  case CA_FATAL:
1145  default:
1146  goto error;
1147  }
1148 
1149  mdir = mysql_cache_dircontents(r, r->pool, pack, filename, uri, mysql, soptions);
1150 
1151  ret = (void *)mdir; /* NULL if mysql_cache_dircontents() failed, which is what we want */
1152 
1153 error:
1154  return ret;
1155 }
1156 
1163 static const char
1164 *mysql_cache_readdir(void *dir)
1165 {
1166  mysql_dir *mdir;
1167  const char *ret = NULL;
1168 
1169  if (unlikely(!dir))
1170  return NULL;
1171 
1172  mdir = (mysql_dir *)dir;
1173 
1174  /* the following is a silly workaround the fact that dir is not void** */
1175  if (likely(mdir->next)) {
1176  ret = mdir->next->d_name;
1177  mdir->next = mdir->next->next;
1178  }
1179 
1180  return ret;
1181 }
1182 
1190 static void
1191 mysql_cache_prologue(request_rec *r)
1192 {
1193  mu_config *const conf = (mu_config *)ap_get_module_config(r->per_dir_config, &musicindex_module);
1194  MYSQL *mysql = NULL;
1195 
1196  if (unlikely(!mysql_params.ready))
1197  goto out;
1198 
1199  mysql = mysql_init(NULL);
1200  if (unlikely(!mysql))
1201  goto out;
1202 
1203  if (unlikely(!mysql_real_connect(mysql, mysql_params.host, mysql_params.user,
1204  mysql_params.pass, mysql_params.db, 0, NULL, 0))) {
1205  mysql_close(mysql);
1206  goto out;
1207  }
1208 
1209  if (unlikely(mysql_set_character_set(mysql, "utf8"))) {
1210  mysql_close(mysql);
1211  goto out;
1212  }
1213 
1214  conf->cache_setup = mysql;
1215 out:
1216  return;
1217 }
1218 
1227 static void
1228 mysql_cache_epilogue(request_rec *r)
1229 {
1230  mu_config *const conf = (mu_config *)ap_get_module_config(r->per_dir_config, &musicindex_module);
1231  MYSQL *mysql = (MYSQL *)conf->cache_setup;
1232 
1233  if (unlikely(!mysql))
1234  goto out;
1235 
1236  mysql_close(mysql);
1237  conf->cache_setup = NULL;
1238 out:
1239  return;
1240 }
1241 
1242 static const cache_backend cache_backend_mysql = {
1243  .opendir = mysql_cache_opendir,
1244  .readdir = mysql_cache_readdir,
1245  .closedir = NULL,
1246  .make_entry = mysql_cache_make_entry,
1247  .write = mysql_cache_write,
1248  .prologue = mysql_cache_prologue,
1249  .epilogue = mysql_cache_epilogue,
1250 };
1251 
1252 int cache_mysql_setup(cmd_parms *cmd, const char *const setup_string, mu_config *const conf)
1253 {
1254  static const char biniou[] = "mysql://";
1255  int ret = 1;
1256 
1257  if (strncmp(biniou, setup_string, 8) == 0) {
1258  ret = mysql_cache_init(cmd->server, setup_string+8);
1259  if (ret)
1260  goto exit;
1261 
1262  conf->cache_setup = NULL; /* this is prologue()'s baby */
1263  conf->cache = &cache_backend_mysql;
1264  }
1265 
1266 exit:
1267  return ret;
1268 }