libdb/mysql.c

   1 /* -*- Mode: C ; c-basic-offset: 2 -*- */
   2 /*****************************************************************************
   3  *
   4  * $Id: mysql.c,v 1.12 2004/10/03 22:03:11 nedko Exp $
   5  *
   6  * DESCRIPTION:
   7  *  MySQL backend implementation.
   8  *
   9  * AUTHOR:
  10  *  Nedko Arnaudov <nedko@users.sourceforge.net>
  11  *
  12  * LICENSE:
  13  *  GNU GENERAL PUBLIC LICENSE version 2
  14  *
  15  *****************************************************************************/
  16
  17 #include <stdlib.h>
  18 #include <stdarg.h>
  19 #include <stdio.h>
  20 #include <string.h>
  21 #include <assert.h>
  22
  23 #include "../result.h"
  24 #include "internal.h"
  25 #include "memory.h"
  26
  27 #define MEDIADB_MYSQL_MEDIA_TABLE "media"
  28 #define MEDIADB_MYSQL_FILES_TABLE "files"
  29 #define MEDIADB_MYSQL_LOCATIONS_TABLE "locations"
  30 #define MEDIADB_MYSQL_LOCATION_TYPES_TABLE "location_types"
  31
  32 //#define DUMMY_SQL_QUERIES
  33 #define FORMATTING_BUFFER_SIZE 10240
  34
  35 #define MEDIADB_MYSQL_PMM_EXACT_CASE_INSENSITIVE    1
  36 #define MEDIADB_MYSQL_PMM_EXACT_CASE_SENSITIVE      2
  37 #define MEDIADB_MYSQL_PMM_LIKE_CASE_INSENSITIVE     3
  38 #define MEDIADB_MYSQL_PMM_LIKE_CASE_SENSITIVE       4
  39 #define MEDIADB_MYSQL_PMM_REGEXP_CASE_INSENSITIVE   5
  40 #define MEDIADB_MYSQL_PMM_REGEXP_CASE_SENSITIVE     6
  41
  42 static struct mediadb_pattern_match_method
  43 mediadb_mysql_pattern_match_methods[] =
  44 {
  45   {
  46     MEDIADB_MYSQL_PMM_REGEXP_CASE_INSENSITIVE,
  47     "ERE - case insensitive",
  48     "Extended regular expressions, ignore case",
  49     "Extended regular expressions are powerful pattern matching method.\n"
  50     "Some characteristics of extended regular expressions are:\n"
  51     " * `.' matches any single character.\n"
  52     " * A character class `[...]' matches any character within the brackets. "
  53     "   For example, `[abc]' matches `a', `b', or `c'. To name a range of characters, "
  54     "   use a dash. `[a-z]' matches any letter, whereas `[0-9]' matches any digit.\n"
  55     " * `*' matches zero or more instances of the thing preceding it. "
  56     "   For example, `x*' matches any number of `x' characters, `[0-9]*' matches any "
  57     "   number of digits, and `.*' matches any number of anything.\n"
  58     " * A REGEXP pattern match succeed if the pattern matches anywhere in the value "
  59     "   being tested.\n"
  60     " * To anchor a pattern so that it must match the beginning or end of the value "
  61     "   being tested, use `^' at the beginning or `$' at the end of the pattern."
  62   },
  63   {
  64     MEDIADB_MYSQL_PMM_REGEXP_CASE_SENSITIVE,
  65     "ERE - case sensitive",
  66     "Extended regular expressions, do not ignore case",
  67     "Extended regular expressions are powerful pattern matching method.\n"
  68     "Some characteristics of extended regular expressions are:\n"
  69     " * `.' matches any single character.\n"
  70     " * A character class `[...]' matches any character within the brackets. "
  71     "   For example, `[abc]' matches `a', `b', or `c'. To name a range of characters, "
  72     "   use a dash. `[a-z]' matches any letter, whereas `[0-9]' matches any digit.\n"
  73     " * `*' matches zero or more instances of the thing preceding it. "
  74     "   For example, `x*' matches any number of `x' characters, `[0-9]*' matches any "
  75     "   number of digits, and `.*' matches any number of anything.\n"
  76     " * A REGEXP pattern match succeed if the pattern matches anywhere in the value "
  77     "   being tested.\n"
  78     " * To anchor a pattern so that it must match the beginning or end of the value "
  79     "   being tested, use `^' at the beginning or `$' at the end of the pattern."
  80   },
  81   {
  82     MEDIADB_MYSQL_PMM_LIKE_CASE_INSENSITIVE,
  83     "SQL \"LIKE\" - case insensitive",
  84     "Traditional SQL \"LIKE\" oprator pattern matching, ignore case",
  85     "SQL pattern matching allows you to use `_' to match any single character "
  86     "and `%' to match an arbitrary number of characters (including zero characters). "
  87     "LIKE pattern match succeeds only if the pattern matches the entire value."
  88   },
  89   {
  90     MEDIADB_MYSQL_PMM_LIKE_CASE_SENSITIVE,
  91     "SQL \"LIKE\" - case sensitive",
  92     "Traditional SQL \"LIKE\" oprator pattern matching, do not ignore case",
  93     "SQL pattern matching allows you to use `_' to match any single character "
  94     "and `%' to match an arbitrary number of characters (including zero characters). "
  95     "LIKE pattern match succeeds only if the pattern matches the entire value."
  96   },
  97   {
  98     MEDIADB_MYSQL_PMM_EXACT_CASE_INSENSITIVE,
  99     "exact - case insensitive",
 100     "Match exactly, ignore case",
 101     "Char by char match"
 102   },
 103   {
 104     MEDIADB_MYSQL_PMM_EXACT_CASE_SENSITIVE,
 105     "exact - case sensitive",
 106     "Match exactly, do not ignore case",
 107     "Char by char match"
 108   },
 109   {
 110     MEDIADB_PMM_NULL,
 111     NULL,
 112     NULL
 113   }
 114 };
 115
 116 static
 117 void
 118 set_error_message(
 119   mediadb_mysql *pDB,
 120   const char *pszFormat,
 121   ...)
 122 {
 123   mediadb_result r;
 124   size_t s;
 125   va_list argList;
 126   char Buffer[FORMATTING_BUFFER_SIZE];
 127
 128   va_start(argList, pszFormat);
 129   vsnprintf(Buffer, FORMATTING_BUFFER_SIZE-1, pszFormat, argList);
 130   va_end(argList);
 131
 132   s = strlen(Buffer);
 133
 134   r = maybe_enlarge_buffer(
 135     &pDB->pErrorMsgBuffer,
 136     &pDB->sizeErrorMsgBuffer,
 137     s+1);
 138   if (MEDIADB_IS_ERROR(r))
 139   {
 140     if (pDB->sizeErrorMsgBuffer > 0)
 141     {
 142       pDB->pErrorMsgBuffer[0] = 0;
 143     }
 144   }
 145
 146   memcpy(pDB->pErrorMsgBuffer, Buffer, s + 1);
 147 }
 148
 149 static
 150 void
 151 reset_error_message(
 152   mediadb_mysql *pDB)
 153 {
 154   if (pDB->pErrorMsgBuffer != NULL)
 155   {
 156     pDB->pErrorMsgBuffer[0] = 0;
 157   }
 158 }
 159
 160 const char *
 161 mediadb_mysql_get_error_message(
 162   mediadb_mysql *pDB)
 163 {
 164   if (pDB->pErrorMsgBuffer == NULL)
 165   {
 166     return "";
 167   }
 168
 169   return pDB->pErrorMsgBuffer;
 170 }
 171
 172 mediadb_result
 173 mediadb_mysql_open(
 174   mediadb_mysql *pDB,
 175   const char *pszHost,
 176   const char *pszUser,
 177   const char *pszPass,
 178   const char *pszDB)
 179 {
 180   MYSQL *pMYSQL;
 181
 182   pDB->pszSQLQueryBuffer = NULL;
 183   pDB->sizeSQLQueryBuffer = 0;
 184   pDB->pErrorMsgBuffer = NULL;
 185   pDB->sizeErrorMsgBuffer = 0;
 186
 187   pMYSQL = mysql_init(NULL);
 188   if (pMYSQL == NULL)
 189   {
 190     set_error_message(
 191       pDB,
 192       "Cannot create MYSQL object. Error is \"%s\"",
 193       mysql_error(pMYSQL));
 194     return MEDIADB_FAIL;
 195   }
 196
 197   if (mysql_real_connect(
 198         pMYSQL,
 199         pszHost,
 200         pszUser,
 201         pszPass,
 202         pszDB,
 203         0,
 204         NULL,
 205         0) == NULL)
 206   {
 207     set_error_message(
 208       pDB,
 209       "Failed to connect to database. Error: %s",
 210       mysql_error(pMYSQL));
 211     mysql_close(pMYSQL);
 212     return MEDIADB_FAIL;
 213   }
 214
 215   pDB->pMYSQL = pMYSQL;
 216
 217   reset_error_message(pDB);
 218
 219   return MEDIADB_OK;
 220 }
 221
 222 mediadb_result
 223 mediadb_mysql_media_add_new(
 224   mediadb_mysql *pDB,
 225   const char *pszName,
 226   const char *pszComment,
 227   mediadb_mediatype nType,
 228   mediadb_uint *pnNewMediaID)
 229 {
 230   mediadb_result r;
 231   size_t sizeName, sizeComment, sizePart;
 232   char *psz, *pszPart;
 233   my_ulonglong nMediaID;
 234
 235   /* Ensure we have large enough buffer */
 236
 237   sizeName = strlen(pszName);
 238   sizeComment = strlen(pszComment);
 239
 240   r = maybe_enlarge_buffer(
 241     &pDB->pszSQLQueryBuffer,
 242     &pDB->sizeSQLQueryBuffer,
 243     sizeName*2 + sizeComment*2 + 1024);
 244   if (MEDIADB_IS_ERROR(r))
 245   {
 246     set_error_message(
 247       pDB,
 248       "Cannot enlarge buffer");
 249     return r;
 250   }
 251
 252   /* Compose SQL query string */
 253
 254   psz = pDB->pszSQLQueryBuffer;
 255
 256   pszPart = "INSERT INTO " MEDIADB_MYSQL_MEDIA_TABLE " (name,type,comment) VALUES ('";
 257   sizePart = strlen(pszPart);
 258   memcpy(psz, pszPart, sizePart);
 259   psz += sizePart;
 260
 261   sizePart = mysql_real_escape_string(
 262     pDB->pMYSQL,
 263     psz,
 264     pszName,
 265     sizeName);
 266   psz += sizePart;
 267
 268   sizePart = sprintf(psz, "',%u,'", (unsigned int)nType);
 269   psz += sizePart;
 270
 271   sizePart = mysql_real_escape_string(
 272     pDB->pMYSQL,
 273     psz,
 274     pszComment,
 275     sizeComment);
 276   psz += sizePart;
 277
 278   pszPart = "')";
 279   sizePart = strlen(pszPart);
 280   memcpy(psz, pszPart, sizePart);
 281   psz += sizePart;
 282
 283   *psz = 0;
 284
 285   /* Execute SQL query */
 286
 287 #ifdef DUMMY_SQL_QUERIES
 288   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 289   nMediaID = 123;
 290 #else
 291   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 292   {
 293     set_error_message(
 294       pDB,
 295       "Failed to execute query \"%s\" Error: %s",
 296       pDB->pszSQLQueryBuffer,
 297       mysql_error(pDB->pMYSQL));
 298     return MEDIADB_FAIL;
 299   }
 300
 301   nMediaID = mysql_insert_id(pDB->pMYSQL);
 302   if (nMediaID == 0ULL)
 303   {
 304     set_error_message(
 305       pDB,
 306       "Invalid media ID 0 after media INSERT");
 307     return MEDIADB_FAIL;
 308   }
 309 #endif
 310
 311   *pnNewMediaID = nMediaID;
 312
 313   reset_error_message(pDB);
 314
 315   return MEDIADB_OK;
 316 }
 317
 318 mediadb_result
 319 mediadb_mysql_media_update_properties(
 320   mediadb_mysql *pDB,
 321   mediadb_uint nMediaID,
 322   mediadb_uint nTimeAdded,
 323   mediadb_uint nTotalFiles,
 324   mediadb_uint nTotalSize)
 325 {
 326   mediadb_result r;
 327
 328   /* Ensure we have large enough buffer */
 329
 330   r = maybe_enlarge_buffer(
 331     &pDB->pszSQLQueryBuffer,
 332     &pDB->sizeSQLQueryBuffer,
 333     1024);
 334   if (MEDIADB_IS_ERROR(r))
 335   {
 336     set_error_message(
 337       pDB,
 338       "Cannot enlarge buffer");
 339     return r;
 340   }
 341
 342   /* Compose SQL query string */
 343
 344   sprintf(
 345     pDB->pszSQLQueryBuffer,
 346     "UPDATE " MEDIADB_MYSQL_MEDIA_TABLE " SET added=%u, info1=%u, info2=%u WHERE mediaid = %u",
 347     (unsigned int)nTimeAdded,
 348     (unsigned int)nTotalFiles,
 349     (unsigned int)nTotalSize,
 350     (unsigned int)nMediaID);
 351
 352   /* Execute SQL query */
 353
 354 #ifdef DUMMY_SQL_QUERIES
 355   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 356 #else
 357   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 358   {
 359     set_error_message(
 360       pDB,
 361       "Failed to execute query \"%s\" Error: %s",
 362       pDB->pszSQLQueryBuffer,
 363       mysql_error(pDB->pMYSQL));
 364     return MEDIADB_FAIL;
 365   }
 366 #endif
 367
 368   reset_error_message(pDB);
 369
 370   return MEDIADB_OK;
 371 }
 372
 373 mediadb_result
 374 mediadb_mysql_file_add_new(
 375   mediadb_mysql *pDB,
 376   mediadb_uint nMediaID,
 377   mediadb_filetype Filetype,
 378   const char *pszPath,
 379   const char *pszName,
 380   mediadb_uint nFileSize,
 381   mediadb_uint nFileTime)
 382 {
 383   mediadb_result r;
 384   size_t sizePath, sizeName, sizePart;
 385   char *psz, *pszPart;
 386
 387   /* Ensure we have large enough buffer */
 388
 389   sizePath = strlen(pszPath);
 390   sizeName = strlen(pszName);
 391
 392   r = maybe_enlarge_buffer(
 393     &pDB->pszSQLQueryBuffer,
 394     &pDB->sizeSQLQueryBuffer,
 395     sizePath*2 + sizeName*2 + 1024);
 396   if (MEDIADB_IS_ERROR(r))
 397   {
 398     set_error_message(
 399       pDB,
 400       "Cannot enlarge buffer");
 401     return r;
 402   }
 403
 404   /* Compose SQL query string */
 405
 406   psz = pDB->pszSQLQueryBuffer;
 407
 408   sizePart = sprintf(
 409     psz,
 410     "INSERT INTO " MEDIADB_MYSQL_FILES_TABLE " (mediaid,size,time,path,name) VALUES (%u,%u,%u,'",
 411     (unsigned int)nMediaID,
 412     (unsigned int)nFileSize,
 413     (unsigned int)nFileTime);
 414   psz += sizePart;
 415
 416   sizePart = mysql_real_escape_string(
 417     pDB->pMYSQL,
 418     psz,
 419     pszPath,
 420     sizePath);
 421   psz += sizePart;
 422
 423   pszPart = "','";
 424   sizePart = strlen(pszPart);
 425   memcpy(psz, pszPart, sizePart);
 426   psz += sizePart;
 427
 428   sizePart = mysql_real_escape_string(
 429     pDB->pMYSQL,
 430     psz,
 431     pszName,
 432     sizeName);
 433   psz += sizePart;
 434
 435   if (Filetype == MEDIADB_FILETYPE_DIR)
 436   {
 437     pszPart = "/";
 438     sizePart = strlen(pszPart);
 439     memcpy(psz, pszPart, sizePart);
 440     psz += sizePart;
 441   }
 442   else if (Filetype != MEDIADB_FILETYPE_FILE)
 443   {
 444     set_error_message(
 445       pDB,
 446       "Cannot add file of unknown type");
 447     return MEDIADB_INVAL_ARG;
 448   }
 449
 450   pszPart = "')";
 451   sizePart = strlen(pszPart);
 452   memcpy(psz, pszPart, sizePart);
 453   psz += sizePart;
 454
 455   *psz = 0;
 456
 457   /* Execute SQL query */
 458
 459 #ifdef DUMMY_SQL_QUERIES
 460   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 461 #else
 462   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 463   {
 464     set_error_message(
 465       pDB,
 466       "Failed to execute query \"%s\" Error: %s",
 467       pDB->pszSQLQueryBuffer,
 468       mysql_error(pDB->pMYSQL));
 469     return MEDIADB_FAIL;
 470   }
 471 #endif
 472
 473   reset_error_message(pDB);
 474
 475   return MEDIADB_OK;
 476 }
 477
 478 mediadb_result
 479 mediadb_mysql_media_get_properties(
 480   mediadb_mysql *pDB,
 481   mediadb_uint nMediaID,
 482   mediadb_uint *pnTimeAdded,
 483   mediadb_mediatype *pnType,
 484   mediadb_uint *pnLocationID,
 485   char **ppszTitle)
 486 {
 487   mediadb_result r;
 488   MYSQL_RES * sqlresult;
 489   my_ulonglong nRowCount;
 490   MYSQL_ROW sqlrow;
 491
 492   /* Ensure we have large enough buffer */
 493
 494   r = maybe_enlarge_buffer(
 495     &pDB->pszSQLQueryBuffer,
 496     &pDB->sizeSQLQueryBuffer,
 497     1024);
 498   if (MEDIADB_IS_ERROR(r))
 499   {
 500     set_error_message(
 501       pDB,
 502       "Cannot enlarge buffer");
 503     return r;
 504   }
 505
 506   /* Compose SQL query string */
 507
 508   sprintf(
 509     pDB->pszSQLQueryBuffer,
 510     "SELECT added, type, name, location FROM " MEDIADB_MYSQL_MEDIA_TABLE " WHERE mediaid = %u",
 511     (unsigned int)nMediaID);
 512
 513   /* Execute SQL query */
 514
 515 #ifdef DUMMY_SQL_QUERIES
 516   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 517 #else
 518   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 519   {
 520     set_error_message(
 521       pDB,
 522       "Failed to execute query \"%s\" Error: %s",
 523       pDB->pszSQLQueryBuffer,
 524       mysql_error(pDB->pMYSQL));
 525     return MEDIADB_FAIL;
 526   }
 527 #endif
 528
 529   sqlresult = mysql_store_result(pDB->pMYSQL);
 530   if (sqlresult == NULL)
 531   {
 532     set_error_message(
 533       pDB,
 534       "Failed to store result after query \"%s\" Error: %s",
 535       pDB->pszSQLQueryBuffer,
 536       mysql_error(pDB->pMYSQL));
 537     return MEDIADB_FAIL;
 538   }
 539
 540   nRowCount = mysql_num_rows(sqlresult);
 541   if (nRowCount != 1)
 542   {
 543     set_error_message(
 544       pDB,
 545       "%u rows, insted of 1, after query \"%s\"",
 546       (unsigned int)nRowCount,
 547       pDB->pszSQLQueryBuffer);
 548
 549     mysql_free_result(sqlresult);
 550
 551     return MEDIADB_FAIL;
 552   }
 553
 554   sqlrow = mysql_fetch_row(sqlresult);
 555   if (sqlrow == NULL)
 556   {
 557     set_error_message(
 558       pDB,
 559       "Failed to fetch row after query \"%s\"",
 560       pDB->pszSQLQueryBuffer);
 561
 562     mysql_free_result(sqlresult);
 563
 564     return MEDIADB_FAIL;
 565   }
 566
 567   if (sqlrow[0] == NULL)
 568   {
 569     *pnTimeAdded = 0;
 570   }
 571   else
 572   {
 573     *pnTimeAdded = strtoull(sqlrow[0], NULL, 10);
 574   }
 575
 576   if (sqlrow[1] == NULL)
 577   {
 578     *pnType = MEDIADB_MT_UNKNOWN;
 579   }
 580   else
 581   {
 582     *pnType = strtoull(sqlrow[1], NULL, 10);
 583   }
 584
 585   if (sqlrow[2] == NULL)
 586   {
 587     *ppszTitle = strdup("");
 588   }
 589   else
 590   {
 591     *ppszTitle = strdup(sqlrow[2]);
 592   }
 593
 594   if (sqlrow[3] == NULL)
 595   {
 596     *pnLocationID = 0;
 597   }
 598   else
 599   {
 600     *pnLocationID = strtoull(sqlrow[3], NULL, 10);
 601   }
 602
 603   mysql_free_result(sqlresult);
 604
 605   if (*ppszTitle == NULL)
 606   {
 607     set_error_message(
 608       pDB,
 609       "Out of memory");
 610     return MEDIADB_MEM;
 611   }
 612
 613   reset_error_message(pDB);
 614
 615   return MEDIADB_OK;
 616 }
 617
 618 mediadb_result
 619 mediadb_mysql_location_get_properties(
 620   mediadb_mysql *pDB,
 621   mediadb_uint nLocationID,
 622   mediadb_uint *pnLocationTypeID,
 623   char **ppszDescription)
 624 {
 625   mediadb_result r;
 626   MYSQL_RES * sqlresult;
 627   my_ulonglong nRowCount;
 628   MYSQL_ROW sqlrow;
 629
 630   /* Ensure we have large enough buffer */
 631
 632   r = maybe_enlarge_buffer(
 633     &pDB->pszSQLQueryBuffer,
 634     &pDB->sizeSQLQueryBuffer,
 635     1024);
 636   if (MEDIADB_IS_ERROR(r))
 637   {
 638     set_error_message(
 639       pDB,
 640       "Cannot enlarge buffer");
 641     return r;
 642   }
 643
 644   /* Compose SQL query string */
 645
 646   sprintf(
 647     pDB->pszSQLQueryBuffer,
 648     "SELECT type, description FROM " MEDIADB_MYSQL_LOCATIONS_TABLE " WHERE id = %u",
 649     (unsigned int)nLocationID);
 650
 651   /* Execute SQL query */
 652
 653 #ifdef DUMMY_SQL_QUERIES
 654   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 655 #else
 656   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 657   {
 658     set_error_message(
 659       pDB,
 660       "Failed to execute query \"%s\" Error: %s",
 661       pDB->pszSQLQueryBuffer,
 662       mysql_error(pDB->pMYSQL));
 663     return MEDIADB_FAIL;
 664   }
 665 #endif
 666
 667   sqlresult = mysql_store_result(pDB->pMYSQL);
 668   if (sqlresult == NULL)
 669   {
 670     set_error_message(
 671       pDB,
 672       "Failed to store result after query \"%s\" Error: %s",
 673       pDB->pszSQLQueryBuffer,
 674       mysql_error(pDB->pMYSQL));
 675     return MEDIADB_FAIL;
 676   }
 677
 678   nRowCount = mysql_num_rows(sqlresult);
 679   if (nRowCount != 1)
 680   {
 681     set_error_message(
 682       pDB,
 683       "%u rows, insted of 1, after query \"%s\"",
 684       (unsigned int)nRowCount,
 685       pDB->pszSQLQueryBuffer);
 686
 687     mysql_free_result(sqlresult);
 688
 689     return MEDIADB_FAIL;
 690   }
 691
 692   sqlrow = mysql_fetch_row(sqlresult);
 693   if (sqlrow == NULL)
 694   {
 695     set_error_message(
 696       pDB,
 697       "Failed to fetch row after query \"%s\"",
 698       pDB->pszSQLQueryBuffer);
 699
 700     mysql_free_result(sqlresult);
 701
 702     return MEDIADB_FAIL;
 703   }
 704
 705   if (sqlrow[0] == NULL)
 706   {
 707     *pnLocationTypeID = 0;
 708   }
 709   else
 710   {
 711     *pnLocationTypeID = strtoull(sqlrow[0], NULL, 10);
 712   }
 713
 714   if (sqlrow[1] == NULL)
 715   {
 716     *ppszDescription = strdup("");
 717   }
 718   else
 719   {
 720     *ppszDescription = strdup(sqlrow[1]);
 721   }
 722
 723   mysql_free_result(sqlresult);
 724
 725   if (*ppszDescription == NULL)
 726   {
 727     set_error_message(
 728       pDB,
 729       "Out of memory");
 730     return MEDIADB_MEM;
 731   }
 732
 733   reset_error_message(pDB);
 734
 735   return MEDIADB_OK;
 736 }
 737
 738 mediadb_result
 739 mediadb_mysql_location_type_get_properties(
 740   mediadb_mysql *pDB,
 741   mediadb_uint nLocationTypeID,
 742   char **ppszDescription)
 743 {
 744   mediadb_result r;
 745   MYSQL_RES * sqlresult;
 746   my_ulonglong nRowCount;
 747   MYSQL_ROW sqlrow;
 748
 749   /* Ensure we have large enough buffer */
 750
 751   r = maybe_enlarge_buffer(
 752     &pDB->pszSQLQueryBuffer,
 753     &pDB->sizeSQLQueryBuffer,
 754     1024);
 755   if (MEDIADB_IS_ERROR(r))
 756   {
 757     set_error_message(
 758       pDB,
 759       "Cannot enlarge buffer");
 760     return r;
 761   }
 762
 763   /* Compose SQL query string */
 764
 765   sprintf(
 766     pDB->pszSQLQueryBuffer,
 767     "SELECT description FROM " MEDIADB_MYSQL_LOCATION_TYPES_TABLE " WHERE id = %u",
 768     (unsigned int)nLocationTypeID);
 769
 770   /* Execute SQL query */
 771
 772 #ifdef DUMMY_SQL_QUERIES
 773   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 774 #else
 775   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 776   {
 777     set_error_message(
 778       pDB,
 779       "Failed to execute query \"%s\" Error: %s",
 780       pDB->pszSQLQueryBuffer,
 781       mysql_error(pDB->pMYSQL));
 782     return MEDIADB_FAIL;
 783   }
 784 #endif
 785
 786   sqlresult = mysql_store_result(pDB->pMYSQL);
 787   if (sqlresult == NULL)
 788   {
 789     set_error_message(
 790       pDB,
 791       "Failed to store result after query \"%s\" Error: %s",
 792       pDB->pszSQLQueryBuffer,
 793       mysql_error(pDB->pMYSQL));
 794     return MEDIADB_FAIL;
 795   }
 796
 797   nRowCount = mysql_num_rows(sqlresult);
 798   if (nRowCount != 1)
 799   {
 800     set_error_message(
 801       pDB,
 802       "%u rows, insted of 1, after query \"%s\"",
 803       (unsigned int)nRowCount,
 804       pDB->pszSQLQueryBuffer);
 805
 806     mysql_free_result(sqlresult);
 807
 808     return MEDIADB_FAIL;
 809   }
 810
 811   sqlrow = mysql_fetch_row(sqlresult);
 812   if (sqlrow == NULL)
 813   {
 814     set_error_message(
 815       pDB,
 816       "Failed to fetch row after query \"%s\"",
 817       pDB->pszSQLQueryBuffer);
 818
 819     mysql_free_result(sqlresult);
 820
 821     return MEDIADB_FAIL;
 822   }
 823
 824   if (sqlrow[0] == NULL)
 825   {
 826     *ppszDescription = strdup("");
 827   }
 828   else
 829   {
 830     *ppszDescription = strdup(sqlrow[0]);
 831   }
 832
 833   mysql_free_result(sqlresult);
 834
 835   if (*ppszDescription == NULL)
 836   {
 837     set_error_message(
 838       pDB,
 839       "Out of memory");
 840     return MEDIADB_MEM;
 841   }
 842
 843   reset_error_message(pDB);
 844
 845   return MEDIADB_OK;
 846 }
 847
 848 mediadb_result
 849 mediadb_mysql_media_get_properties_data(
 850   mediadb_mysql *pDB,
 851   mediadb_uint nMediaID,
 852   mediadb_uint *pnTotalFiles,
 853   mediadb_uint *pnTotalSize)
 854 {
 855   mediadb_result r;
 856   MYSQL_RES * sqlresult;
 857   my_ulonglong nRowCount;
 858   MYSQL_ROW sqlrow;
 859
 860   /* Ensure we have large enough buffer */
 861
 862   r = maybe_enlarge_buffer(
 863     &pDB->pszSQLQueryBuffer,
 864     &pDB->sizeSQLQueryBuffer,
 865     1024);
 866   if (MEDIADB_IS_ERROR(r))
 867   {
 868     set_error_message(
 869       pDB,
 870       "Cannot enlarge buffer");
 871     return r;
 872   }
 873
 874   /* Compose SQL query string */
 875
 876   sprintf(
 877     pDB->pszSQLQueryBuffer,
 878     "SELECT info1, info2 FROM " MEDIADB_MYSQL_MEDIA_TABLE " WHERE mediaid = %u",
 879     (unsigned int)nMediaID);
 880
 881   /* Execute SQL query */
 882
 883 #ifdef DUMMY_SQL_QUERIES
 884   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 885 #else
 886   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 887   {
 888     set_error_message(
 889       pDB,
 890       "Failed to execute query \"%s\" Error: %s",
 891       pDB->pszSQLQueryBuffer,
 892       mysql_error(pDB->pMYSQL));
 893     return MEDIADB_FAIL;
 894   }
 895 #endif
 896
 897   sqlresult = mysql_store_result(pDB->pMYSQL);
 898   if (sqlresult == NULL)
 899   {
 900     set_error_message(
 901       pDB,
 902       "Failed to store result after query \"%s\" Error: %s",
 903       pDB->pszSQLQueryBuffer,
 904       mysql_error(pDB->pMYSQL));
 905     return MEDIADB_FAIL;
 906   }
 907
 908   nRowCount = mysql_num_rows(sqlresult);
 909   if (nRowCount != 1)
 910   {
 911     set_error_message(
 912       pDB,
 913       "%u rows, insted of 1, after query \"%s\"",
 914       (unsigned int)nRowCount,
 915       pDB->pszSQLQueryBuffer);
 916
 917     mysql_free_result(sqlresult);
 918
 919     return MEDIADB_FAIL;
 920   }
 921
 922   sqlrow = mysql_fetch_row(sqlresult);
 923   if (sqlrow == NULL)
 924   {
 925     set_error_message(
 926       pDB,
 927       "Failed to fetch row after query \"%s\"",
 928       pDB->pszSQLQueryBuffer);
 929
 930     mysql_free_result(sqlresult);
 931
 932     return MEDIADB_FAIL;
 933   }
 934
 935   if (sqlrow[0] == NULL)
 936   {
 937     *pnTotalFiles = 0;
 938   }
 939   else
 940   {
 941     *pnTotalFiles = strtoull(sqlrow[0], NULL, 10);
 942   }
 943
 944   if (sqlrow[1] == NULL)
 945   {
 946     *pnTotalSize = 0;
 947   }
 948   else
 949   {
 950     *pnTotalSize = strtoull(sqlrow[1], NULL, 10);
 951   }
 952
 953   mysql_free_result(sqlresult);
 954
 955   reset_error_message(pDB);
 956
 957   return MEDIADB_OK;
 958 }
 959
 960 mediadb_result
 961 mediadb_mysql_delete_media_files(
 962   mediadb_mysql *pDB,
 963   mediadb_uint nMediaID)
 964 {
 965   mediadb_result r;
 966
 967   /* Ensure we have large enough buffer */
 968
 969   r = maybe_enlarge_buffer(
 970     &pDB->pszSQLQueryBuffer,
 971     &pDB->sizeSQLQueryBuffer,
 972     1024);
 973   if (MEDIADB_IS_ERROR(r))
 974   {
 975     set_error_message(
 976       pDB,
 977       "Cannot enlarge buffer");
 978     return r;
 979   }
 980
 981   /* Compose SQL query string */
 982
 983   sprintf(
 984     pDB->pszSQLQueryBuffer,
 985     "DELETE FROM " MEDIADB_MYSQL_FILES_TABLE " WHERE mediaid = %u",
 986     (unsigned int)nMediaID);
 987
 988   /* Execute SQL query */
 989
 990 #ifdef DUMMY_SQL_QUERIES
 991   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 992 #else
 993   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 994   {
 995     set_error_message(
 996       pDB,
 997       "Failed to execute query \"%s\" Error: %s",
 998       pDB->pszSQLQueryBuffer,
 999       mysql_error(pDB->pMYSQL));
1000     return MEDIADB_FAIL;
1001   }
1002 #endif
1003
1004   reset_error_message(pDB);
1005
1006   return MEDIADB_OK;
1007 }
1008
1009 mediadb_result
1010 mediadb_mysql_media_update_name(
1011   mediadb_mysql *pDB,
1012   mediadb_uint nMediaID,
1013   const char *pszName)
1014 {
1015   mediadb_result r;
1016   size_t sizeName, sizePart;
1017   char *psz, *pszPart;
1018
1019   /* Ensure we have large enough buffer */
1020
1021   sizeName = strlen(pszName);
1022
1023   r = maybe_enlarge_buffer(
1024     &pDB->pszSQLQueryBuffer,
1025     &pDB->sizeSQLQueryBuffer,
1026     1024);
1027   if (MEDIADB_IS_ERROR(r))
1028   {
1029     set_error_message(
1030       pDB,
1031       "Cannot enlarge buffer");
1032     return r;
1033   }
1034
1035   /* Compose SQL query string */
1036
1037   psz = pDB->pszSQLQueryBuffer;
1038
1039   pszPart = "UPDATE " MEDIADB_MYSQL_MEDIA_TABLE " SET name = '";
1040   sizePart = strlen(pszPart);
1041   memcpy(psz, pszPart, sizePart);
1042   psz += sizePart;
1043
1044   sizePart = mysql_real_escape_string(
1045     pDB->pMYSQL,
1046     psz,
1047     pszName,
1048     sizeName);
1049   psz += sizePart;
1050
1051   sizePart = sprintf(psz, "' WHERE mediaid = %u", (unsigned int)nMediaID);
1052   psz += sizePart;
1053
1054   /* Execute SQL query */
1055
1056 #ifdef DUMMY_SQL_QUERIES
1057   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
1058 #else
1059   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
1060   {
1061     set_error_message(
1062       pDB,
1063       "Failed to execute query \"%s\" Error: %s",
1064       pDB->pszSQLQueryBuffer,
1065       mysql_error(pDB->pMYSQL));
1066     return MEDIADB_FAIL;
1067   }
1068 #endif
1069
1070   reset_error_message(pDB);
1071
1072   return MEDIADB_OK;
1073 }
1074
1075 mediadb_result
1076 mediadb_mysql_close(
1077   mediadb_mysql *pDB)
1078 {
1079   if (pDB->pszSQLQueryBuffer != NULL)
1080   {
1081     free(pDB->pszSQLQueryBuffer);
1082   }
1083
1084   if (pDB->pErrorMsgBuffer != NULL)
1085   {
1086     free(pDB->pErrorMsgBuffer);
1087   }
1088
1089   mysql_close(pDB->pMYSQL);
1090
1091   return MEDIADB_OK;
1092 }
1093
1094 mediadb_result
1095 mediadb_mysql_media_get_all(
1096   mediadb_mysql *pDB,
1097   mediadb_media_callback pCallback,
1098   void *pUserContext)
1099 {
1100   mediadb_result r;
1101   MYSQL_RES * sqlresult;
1102   my_ulonglong nRowCount;
1103   MYSQL_ROW sqlrow;
1104   mediadb_uint nMediaID;
1105   const char *pszName;
1106   const char *pszComment;
1107   mediadb_uint nAdded;
1108   mediadb_uint nTotalFiles;
1109   mediadb_uint nTotalSize;
1110   mediadb_uint nLocationID;
1111   mediadb_mediatype nType;
1112   char *pszLocation;
1113
1114   /* Ensure we have large enough buffer */
1115
1116   r = maybe_enlarge_buffer(
1117     &pDB->pszSQLQueryBuffer,
1118     &pDB->sizeSQLQueryBuffer,
1119     1024);
1120   if (MEDIADB_IS_ERROR(r))
1121   {
1122     set_error_message(
1123       pDB,
1124       "Cannot enlarge buffer");
1125     return r;
1126   }
1127
1128   /* Compose SQL query string */
1129
1130   strcpy(pDB->pszSQLQueryBuffer,
1131          "SELECT m.mediaid, m.added, m.info1, m.info2, "
1132          "m.name, m.comment, l.id, l.description, m.type FROM "
1133          MEDIADB_MYSQL_MEDIA_TABLE " AS m, "
1134          MEDIADB_MYSQL_LOCATIONS_TABLE " AS l "
1135          "WHERE m.location = l.id "
1136          "ORDER BY m.mediaid ASC");
1137
1138   /* Execute SQL query */
1139
1140 #ifdef DUMMY_SQL_QUERIES
1141   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
1142 #else
1143   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
1144   {
1145     set_error_message(
1146       pDB,
1147       "Failed to execute query \"%s\" Error: %s",
1148       pDB->pszSQLQueryBuffer,
1149       mysql_error(pDB->pMYSQL));
1150     return MEDIADB_FAIL;
1151   }
1152 #endif
1153
1154   sqlresult = mysql_store_result(pDB->pMYSQL);
1155   if (sqlresult == NULL)
1156   {
1157     set_error_message(
1158       pDB,
1159       "Failed to store result after query \"%s\" Error: %s",
1160       pDB->pszSQLQueryBuffer,
1161       mysql_error(pDB->pMYSQL));
1162     return MEDIADB_FAIL;
1163   }
1164
1165   nRowCount = mysql_num_rows(sqlresult);
1166
1167   while (nRowCount--)
1168   {
1169     sqlrow = mysql_fetch_row(sqlresult);
1170     if (sqlrow == NULL)
1171     {
1172       set_error_message(
1173         pDB,
1174         "Failed to fetch row after query \"%s\"",
1175         pDB->pszSQLQueryBuffer);
1176
1177       mysql_free_result(sqlresult);
1178
1179       return MEDIADB_FAIL;
1180     }
1181
1182     /* m.mediaid */
1183     if (sqlrow[0] == NULL)
1184     {
1185       nMediaID = 0;
1186     }
1187     else
1188     {
1189       nMediaID = strtoull(sqlrow[0], NULL, 10);
1190     }
1191
1192     /* m.added */
1193     if (sqlrow[1] == NULL)
1194     {
1195       nAdded = 0;
1196     }
1197     else
1198     {
1199       nAdded = strtoull(sqlrow[1], NULL, 10);
1200     }
1201
1202     /* m.info1 */
1203     if (sqlrow[2] == NULL)
1204     {
1205       nTotalFiles = 0;
1206     }
1207     else
1208     {
1209       nTotalFiles = strtoull(sqlrow[2], NULL, 10);
1210     }
1211
1212     /* m.info2 */
1213     if (sqlrow[3] == NULL)
1214     {
1215       nTotalSize = 0;
1216     }
1217     else
1218     {
1219       nTotalSize = strtoull(sqlrow[3], NULL, 10);
1220     }
1221
1222     /* m.name */
1223     if (sqlrow[4] == NULL)
1224     {
1225       pszName = "";
1226     }
1227     else
1228     {
1229       pszName = sqlrow[4];
1230     }
1231
1232     /* m.comment */
1233     if (sqlrow[5] == NULL)
1234     {
1235       pszComment = "";
1236     }
1237     else
1238     {
1239       pszComment = sqlrow[5];
1240     }
1241
1242     /* l.id */
1243     if (sqlrow[6] == NULL)
1244     {
1245       nLocationID = 0;
1246     }
1247     else
1248     {
1249       nLocationID = strtoull(sqlrow[6], NULL, 10);
1250     }
1251
1252     /* l.description */
1253     if (sqlrow[7] == NULL)
1254     {
1255       pszLocation = "";
1256     }
1257     else
1258     {
1259       pszLocation = sqlrow[7];
1260     }
1261
1262     /* m.type */
1263     if (sqlrow[8] == NULL)
1264     {
1265       nType = MEDIADB_MT_EMPTY;
1266     }
1267     else
1268     {
1269       nType = strtoull(sqlrow[8], NULL, 10);
1270     }
1271
1272     pCallback(
1273       pUserContext,
1274       nMediaID,
1275       nType,
1276       pszName,
1277       pszComment,
1278       nAdded,
1279       nTotalFiles,
1280       nTotalSize,
1281       nLocationID,
1282       pszLocation);
1283   }
1284
1285   mysql_free_result(sqlresult);
1286
1287   reset_error_message(pDB);
1288
1289   return MEDIADB_OK;
1290 }
1291
1292 mediadb_result
1293 mediadb_mysql_files_get(
1294   mediadb_mysql *pDB,
1295   mediadb_uint nMediaID,
1296   const char *pszPath,
1297   mediadb_files_callback pCallback,
1298   void *pUserContext)
1299 {
1300   mediadb_result r;
1301   MYSQL_RES * sqlresult;
1302   my_ulonglong nRowCount;
1303   MYSQL_ROW sqlrow;
1304   char *pszName;
1305   char *pszPath1;
1306   mediadb_uint nTime;
1307   mediadb_uint nSize;
1308   mediadb_filetype nType;
1309   size_t sizePath, sizePart, s;
1310   char *psz, *pszPart;
1311
1312   if (pszPath == NULL)
1313   {
1314     pszPath = "";
1315   }
1316
1317   sizePath = strlen(pszPath);
1318
1319   /* Ensure we have large enough buffer */
1320
1321   r = maybe_enlarge_buffer(
1322     &pDB->pszSQLQueryBuffer,
1323     &pDB->sizeSQLQueryBuffer,
1324     sizePath*2 + 1024);
1325   if (MEDIADB_IS_ERROR(r))
1326   {
1327     set_error_message(
1328       pDB,
1329       "Cannot enlarge buffer");
1330     return r;
1331   }
1332
1333   /* Compose SQL query string */
1334
1335   psz = pDB->pszSQLQueryBuffer;
1336
1337   sizePart = sprintf(
1338     psz,
1339     "SELECT name, size, time, path FROM "
1340     MEDIADB_MYSQL_FILES_TABLE
1341     " WHERE mediaid = %u AND path = '", (unsigned int)nMediaID);
1342   psz += sizePart;
1343
1344   sizePart = mysql_real_escape_string(
1345     pDB->pMYSQL,
1346     psz,
1347     pszPath,
1348     sizePath);
1349   psz += sizePart;
1350
1351   pszPart = "/'";
1352   sizePart = strlen(pszPart);
1353   memcpy(psz, pszPart, sizePart);
1354   psz += sizePart;
1355
1356   *psz = 0;
1357
1358   /* Execute SQL query */
1359
1360 #ifdef DUMMY_SQL_QUERIES
1361   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
1362 #else
1363   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
1364   {
1365     set_error_message(
1366       pDB,
1367       "Failed to execute query \"%s\" Error: %s",
1368       pDB->pszSQLQueryBuffer,
1369       mysql_error(pDB->pMYSQL));
1370     return MEDIADB_FAIL;
1371   }
1372 #endif
1373
1374   sqlresult = mysql_store_result(pDB->pMYSQL);
1375   if (sqlresult == NULL)
1376   {
1377     set_error_message(
1378       pDB,
1379       "Failed to store result after query \"%s\" Error: %s",
1380       pDB->pszSQLQueryBuffer,
1381       mysql_error(pDB->pMYSQL));
1382     return MEDIADB_FAIL;
1383   }
1384
1385   nRowCount = mysql_num_rows(sqlresult);
1386
1387   while (nRowCount--)
1388   {
1389     sqlrow = mysql_fetch_row(sqlresult);
1390     if (sqlrow == NULL)
1391     {
1392       set_error_message(
1393         pDB,
1394         "Failed to fetch row after query \"%s\"",
1395         pDB->pszSQLQueryBuffer);
1396
1397       mysql_free_result(sqlresult);
1398
1399       return MEDIADB_FAIL;
1400     }
1401
1402     /* name */
1403     if (sqlrow[0] == NULL)
1404     {
1405       pszName = "";
1406     }
1407     else
1408     {
1409       pszName = sqlrow[0];
1410     }
1411
1412     /* size */
1413     if (sqlrow[1] == NULL)
1414     {
1415       nSize = 0;
1416     }
1417     else
1418     {
1419       nSize = strtoull(sqlrow[1], NULL, 10);
1420     }
1421
1422     /* time */
1423     if (sqlrow[2] == NULL)
1424     {
1425       nTime = 0;
1426     }
1427     else
1428     {
1429       nTime = strtoull(sqlrow[2], NULL, 10);
1430     }
1431
1432     /* path */
1433     if (sqlrow[3] == NULL)
1434     {
1435       pszPath1 = "";
1436     }
1437     else
1438     {
1439       pszPath1 = sqlrow[3];
1440     }
1441
1442     s = strlen(pszName);
1443
1444     if (s > 1 && pszName[s-1] == '/')
1445     {
1446       nType = MEDIADB_FILETYPE_DIR;
1447       pszName[s-1] = 0;
1448     }
1449     else
1450     {
1451       nType = MEDIADB_FILETYPE_FILE;
1452     }
1453
1454     pCallback(
1455       pUserContext,
1456       pszPath1,
1457       pszName,
1458       nType,
1459       nSize,
1460       nTime);
1461   }
1462
1463   mysql_free_result(sqlresult);
1464
1465   reset_error_message(pDB);
1466
1467   return MEDIADB_OK;
1468 }
1469
1470 const char *
1471 mediadb_mysql_get_match_keyword(mediadb_uint nPMMID)
1472 {
1473   switch (nPMMID)
1474   {
1475   case MEDIADB_MYSQL_PMM_EXACT_CASE_INSENSITIVE:
1476     return " = ";
1477   case MEDIADB_MYSQL_PMM_EXACT_CASE_SENSITIVE:
1478     return " = BINARY ";
1479   case MEDIADB_MYSQL_PMM_LIKE_CASE_INSENSITIVE:
1480     return " LIKE ";
1481   case MEDIADB_MYSQL_PMM_LIKE_CASE_SENSITIVE:
1482     return " LIKE BINARY ";
1483   case MEDIADB_MYSQL_PMM_REGEXP_CASE_INSENSITIVE:
1484     return " REGEXP ";
1485   case MEDIADB_MYSQL_PMM_REGEXP_CASE_SENSITIVE:
1486     return " REGEXP BINARY ";
1487   }
1488
1489   assert(0);
1490   return "XXX_BUG_XXX";
1491 }
1492
1493
1494 mediadb_result
1495 mediadb_mysql_get_pattern_match_methods(
1496   mediadb_mysql *pDB,
1497   const struct mediadb_pattern_match_method **ppPMM)
1498 {
1499   *ppPMM =  mediadb_mysql_pattern_match_methods;
1500   return MEDIADB_OK;
1501 }
1502
1503 mediadb_result
1504 mediadb_mysql_files_search(
1505   mediadb_mysql *pDB,
1506   mediadb_uint nFilenamePMMID,
1507   const char *pszFilenamePattern,
1508   mediadb_uint nPathPMMID,
1509   const char *pszPathPattern,
1510   const mediadb_uint *pnMinSize,
1511   const mediadb_uint *pnMaxSize,
1512   mediadb_files_search_callback pCallback,
1513   void *pUserContext)
1514 {
1515   mediadb_result r;
1516   int nCount;
1517   const char *pszAND;
1518   size_t sizePart;
1519   size_t sizeFilenamePattern;
1520   size_t sizePathPattern;
1521   char *psz;
1522   MYSQL_RES * sqlresult;
1523   my_ulonglong nRowCount;
1524   MYSQL_ROW sqlrow;
1525   mediadb_uint nMediaID;
1526   char *pszPath;
1527   char *pszName;
1528   char *pszMediaName;
1529   char *pszMediaLocation;
1530   mediadb_uint nSize;
1531   mediadb_uint nTime;
1532   size_t s;
1533   mediadb_filetype nType;
1534
1535   nCount = 0;
1536   pszAND = " AND ";
1537
1538   if (nFilenamePMMID != MEDIADB_PMM_NULL)
1539   {
1540     nCount++;
1541     sizeFilenamePattern = strlen(pszFilenamePattern);
1542   }
1543   else
1544   {
1545     sizeFilenamePattern = 0;
1546   }
1547
1548   if (nPathPMMID != MEDIADB_PMM_NULL)
1549   {
1550     nCount++;
1551     sizePathPattern = strlen(pszPathPattern);
1552   }
1553   else
1554   {
1555     sizePathPattern = 0;
1556   }
1557
1558   if (pnMinSize != NULL)
1559   {
1560     nCount++;
1561   }
1562
1563   if (pnMaxSize != NULL)
1564   {
1565     nCount++;
1566   }
1567
1568   if (nCount == 0)
1569   {
1570     /* Nothing to search - empty result */
1571     return MEDIADB_OK;
1572   }
1573
1574   /* Ensure we have large enough buffer */
1575
1576   r = maybe_enlarge_buffer(
1577     &pDB->pszSQLQueryBuffer,
1578     &pDB->sizeSQLQueryBuffer,
1579     sizeFilenamePattern*2 + sizePathPattern + 1024);
1580   if (MEDIADB_IS_ERROR(r))
1581   {
1582     set_error_message(
1583       pDB,
1584       "Cannot enlarge buffer");
1585     return r;
1586   }
1587
1588   /* Compose SQL query string */
1589
1590   psz = pDB->pszSQLQueryBuffer;
1591
1592   sizePart = sprintf(
1593     psz,
1594     "SELECT f.mediaid, f.path, f.name, f.size, f.time, m.name, l.description FROM "
1595     MEDIADB_MYSQL_FILES_TABLE " AS f, "
1596     MEDIADB_MYSQL_MEDIA_TABLE " AS m, "
1597     MEDIADB_MYSQL_LOCATIONS_TABLE " AS l "
1598     "WHERE f.mediaid = m.mediaid AND m.location = l.id AND ");
1599   psz += sizePart;
1600
1601   if (nFilenamePMMID != MEDIADB_PMM_NULL)
1602   {
1603     nCount--;
1604
1605     sizePart = sprintf(
1606       psz,
1607       "f.name%s'",
1608       mediadb_mysql_get_match_keyword(nFilenamePMMID));
1609     psz += sizePart;
1610
1611     sizePart = mysql_real_escape_string(
1612       pDB->pMYSQL,
1613       psz,
1614       pszFilenamePattern,
1615       sizeFilenamePattern);
1616     psz += sizePart;
1617
1618     sizePart = sprintf(
1619       psz,
1620       "'%s",
1621       nCount == 0?"":pszAND);
1622     psz += sizePart;
1623   }
1624
1625   if (nPathPMMID != MEDIADB_PMM_NULL)
1626   {
1627     nCount--;
1628
1629     sizePart = sprintf(
1630       psz,
1631       "f.path%s'",
1632       mediadb_mysql_get_match_keyword(nPathPMMID));
1633     psz += sizePart;
1634
1635     sizePart = mysql_real_escape_string(
1636       pDB->pMYSQL,
1637       psz,
1638       pszPathPattern,
1639       sizePathPattern);
1640     psz += sizePart;
1641
1642     sizePart = sprintf(
1643       psz,
1644       "'%s",
1645       nCount == 0?"":pszAND);
1646     psz += sizePart;
1647   }
1648
1649   if (pnMinSize != NULL)
1650   {
1651     nCount--;
1652
1653     sizePart = sprintf(
1654       psz,
1655       "f.size >= %u%s",
1656       (unsigned int)*pnMinSize,
1657       nCount == 0?"":pszAND);
1658     psz += sizePart;
1659   }
1660
1661   if (pnMaxSize != NULL)
1662   {
1663     nCount--;
1664
1665     sizePart = sprintf(
1666       psz,
1667       "f.size <= %u%s",
1668       (unsigned int)*pnMaxSize,
1669       nCount == 0?"":pszAND);
1670     psz += sizePart;
1671   }
1672
1673   assert(nCount == 0);
1674
1675   *psz = 0;
1676
1677   /* Execute SQL query */
1678
1679 #ifdef DUMMY_SQL_QUERIES
1680   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
1681 #else
1682   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
1683   {
1684     set_error_message(
1685       pDB,
1686       "Failed to execute query \"%s\" Error: %s",
1687       pDB->pszSQLQueryBuffer,
1688       mysql_error(pDB->pMYSQL));
1689     return MEDIADB_FAIL;
1690   }
1691 #endif
1692
1693   sqlresult = mysql_store_result(pDB->pMYSQL);
1694   if (sqlresult == NULL)
1695   {
1696     set_error_message(
1697       pDB,
1698       "Failed to store result after query \"%s\" Error: %s",
1699       pDB->pszSQLQueryBuffer,
1700       mysql_error(pDB->pMYSQL));
1701     return MEDIADB_FAIL;
1702   }
1703
1704   nRowCount = mysql_num_rows(sqlresult);
1705
1706   while (nRowCount--)
1707   {
1708     sqlrow = mysql_fetch_row(sqlresult);
1709     if (sqlrow == NULL)
1710     {
1711       set_error_message(
1712         pDB,
1713         "Failed to fetch row after query \"%s\"",
1714         pDB->pszSQLQueryBuffer);
1715
1716       mysql_free_result(sqlresult);
1717
1718       return MEDIADB_FAIL;
1719     }
1720
1721     /* mediaid */
1722     if (sqlrow[0] == NULL)
1723     {
1724       nMediaID = 0;
1725     }
1726     else
1727     {
1728       nMediaID = strtoull(sqlrow[0], NULL, 10);
1729     }
1730
1731     /* path */
1732     if (sqlrow[1] == NULL)
1733     {
1734       pszPath = "";
1735     }
1736     else
1737     {
1738       pszPath = sqlrow[1];
1739     }
1740
1741     /* name */
1742     if (sqlrow[2] == NULL)
1743     {
1744       pszName = "";
1745     }
1746     else
1747     {
1748       pszName = sqlrow[2];
1749     }
1750
1751     /* size */
1752     if (sqlrow[3] == NULL)
1753     {
1754       nSize = 0;
1755     }
1756     else
1757     {
1758       nSize = strtoull(sqlrow[3], NULL, 10);
1759     }
1760
1761     /* time */
1762     if (sqlrow[4] == NULL)
1763     {
1764       nTime = 0;
1765     }
1766     else
1767     {
1768       nTime = strtoull(sqlrow[4], NULL, 10);
1769     }
1770
1771     /* media name */
1772     if (sqlrow[5] == NULL)
1773     {
1774       pszMediaName = "";
1775     }
1776     else
1777     {
1778       pszMediaName = sqlrow[5];
1779     }
1780
1781     /* media location */
1782     if (sqlrow[6] == NULL)
1783     {
1784       pszMediaLocation = "";
1785     }
1786     else
1787     {
1788       pszMediaLocation = sqlrow[6];
1789     }
1790
1791     s = strlen(pszName);
1792
1793     if (s > 1 && pszName[s-1] == '/')
1794     {
1795       nType = MEDIADB_FILETYPE_DIR;
1796       pszName[s-1] = 0;
1797     }
1798     else
1799     {
1800       nType = MEDIADB_FILETYPE_FILE;
1801     }
1802
1803     pCallback(
1804       pUserContext,
1805       nMediaID,
1806       MEDIADB_MT_DATA,
1807       pszMediaName,
1808       pszMediaLocation,
1809       pszPath,
1810       pszName,
1811       nType,
1812       (mediadb_uint)nSize,
1813       (mediadb_uint)nTime);
1814   }
1815
1816   mysql_free_result(sqlresult);
1817
1818   reset_error_message(pDB);
1819
1820   return MEDIADB_OK;
1821 }
1822
1823 /*****************************************************************************
1824  *
1825  * Modifications log:
1826  *
1827  * !!! WARNING !!! Following lines are automatically updated by the CVS system.
1828  *
1829  *   $Log: mysql.c,v $
1830  *   Revision 1.12  2004/10/03 22:03:11  nedko
1831  *   Reorder patern match methods, "ERE" first, "LIKE", "exact" last
1832  *
1833  *   Revision 1.11  2004/10/03 21:57:52  nedko
1834  *   Implement file search for MySQL backend.
1835  *
1836  *   Revision 1.10  2004/08/31 22:40:15  nedko
1837  *   Partitally implemented search feature.
1838  *
1839  *   Revision 1.9  2004/08/08 00:47:42  nedko
1840  *   Get more info for media from database.
1841  *
1842  *   Revision 1.8  2004/05/22 00:15:09  nedko
1843  *   Implement mediadb_mysql_files_get()
1844  *
1845  *   Revision 1.7  2004/05/21 23:40:46  nedko
1846  *   New functionality: mediadb_files_get()
1847  *
1848  *   Revision 1.6  2004/05/16 18:57:12  nedko
1849  *   media_get_all functionality implemented.
1850  *
1851  *   Revision 1.5  2004/05/11 01:15:19  nedko
1852  *   Bugfixes.
1853  *
1854  *   Revision 1.4  2004/05/03 20:47:15  nedko
1855  *   Update mode for cui
1856  *
1857  *   Revision 1.3  2004/05/02 20:12:11  nedko
1858  *   Improve error dumps.
1859  *
1860  *   Revision 1.2  2004/05/02 13:18:36  nedko
1861  *   Supply filetype when adding new file.
1862  *   Initialize mediaid when in dummy sql queries mode.
1863  *
1864  *   Revision 1.1  2004/04/27 09:12:28  nedko
1865  *   Initial revision.
1866  *
1867  *****************************************************************************/