libdb/sqlite.c

   1 /* -*- Mode: C ; c-basic-offset: 2 -*- */
   2 /*****************************************************************************
   3  *
   4  * $Id: sqlite.c,v 1.11 2004/09/18 21:32:34 nedko Exp $
   5  *
   6  * DESCRIPTION:
   7  *  SQLite 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_SQLITE_MEDIA_TABLE "media"
  28 #define MEDIADB_SQLITE_FILES_TABLE "files"
  29 #define MEDIADB_SQLITE_LOCATIONS_TABLE "locations"
  30 #define MEDIADB_SQLITE_LOCATION_TYPES_TABLE "location_types"
  31
  32 //#define DUMMY_SQL_QUERIES
  33 #define FORMATTING_BUFFER_SIZE 10240
  34
  35 #define MEDIADB_SQLITE_PMM_EXACT_CASE_INSENSITIVE    1
  36 #define MEDIADB_SQLITE_PMM_EXACT_CASE_SENSITIVE      2
  37 #define MEDIADB_SQLITE_PMM_LIKE_CASE_INSENSITIVE     3
  38 #define MEDIADB_SQLITE_PMM_LIKE_CASE_SENSITIVE       4
  39 #define MEDIADB_SQLITE_PMM_REGEXP_CASE_INSENSITIVE   5
  40 #define MEDIADB_SQLITE_PMM_REGEXP_CASE_SENSITIVE     6
  41
  42 static struct mediadb_pattern_match_method
  43 mediadb_sqlite_pattern_match_methods[] =
  44 {
  45   {
  46     MEDIADB_SQLITE_PMM_EXACT_CASE_INSENSITIVE,
  47     "exact - case insensitive",
  48     "Match exactly, ignore case",
  49     "Char by char match"
  50   },
  51 /*
  52   {
  53     MEDIADB_SQLITE_PMM_EXACT_CASE_SENSITIVE,
  54     "exact - case sensitive",
  55     "Match exactly, do not ignore case",
  56     "Char by char match"
  57   },
  58 */
  59   {
  60     MEDIADB_SQLITE_PMM_LIKE_CASE_INSENSITIVE,
  61     "SQL \"LIKE\" - case insensitive",
  62     "Traditional SQL \"LIKE\" oprator pattern matching, ignore case",
  63     "SQL pattern matching allows you to use `_' to match any single character "
  64     "and `%' to match an arbitrary number of characters (including zero characters). "
  65     "LIKE pattern match succeeds only if the pattern matches the entire value."
  66   },
  67 /*
  68   {
  69     MEDIADB_SQLITE_PMM_LIKE_CASE_SENSITIVE,
  70     "SQL \"LIKE\" - case sensitive",
  71     "Traditional SQL \"LIKE\" oprator pattern matching, do not ignore case",
  72     "SQL pattern matching allows you to use `_' to match any single character "
  73     "and `%' to match an arbitrary number of characters (including zero characters). "
  74     "LIKE pattern match succeeds only if the pattern matches the entire value."
  75   },
  76 */
  77   {
  78     MEDIADB_PMM_NULL,
  79     NULL,
  80     NULL
  81   }
  82 };
  83
  84 static
  85 void
  86 set_error_message(
  87   mediadb_sqlite *pDB,
  88   const char *pszFormat,
  89   ...)
  90 {
  91   mediadb_result r;
  92   size_t s;
  93   va_list argList;
  94   char Buffer[FORMATTING_BUFFER_SIZE];
  95
  96   va_start(argList, pszFormat);
  97   vsnprintf(Buffer, FORMATTING_BUFFER_SIZE-1, pszFormat, argList);
  98   va_end(argList);
  99
 100   s = strlen(Buffer);
 101
 102   r = maybe_enlarge_buffer(
 103     &pDB->pErrorMsgBuffer,
 104     &pDB->sizeErrorMsgBuffer,
 105     s+1);
 106   if (MEDIADB_IS_ERROR(r))
 107   {
 108     if (pDB->sizeErrorMsgBuffer > 0)
 109     {
 110       pDB->pErrorMsgBuffer[0] = 0;
 111     }
 112   }
 113
 114   memcpy(pDB->pErrorMsgBuffer, Buffer, s + 1);
 115 }
 116
 117 static
 118 void
 119 reset_error_message(
 120   mediadb_sqlite *pDB)
 121 {
 122   if (pDB->pErrorMsgBuffer != NULL)
 123   {
 124     pDB->pErrorMsgBuffer[0] = 0;
 125   }
 126 }
 127
 128 const char *
 129 mediadb_sqlite_get_error_message(
 130   mediadb_sqlite *pDB)
 131 {
 132   if (pDB->pErrorMsgBuffer == NULL)
 133   {
 134     return "";
 135   }
 136
 137   return pDB->pErrorMsgBuffer;
 138 }
 139
 140 mediadb_result
 141 mediadb_sqlite_open(
 142   mediadb_sqlite *pDB,
 143   const char *pszHost,
 144   const char *pszUser,
 145   const char *pszPass,
 146   const char *pszDB)
 147 {
 148   sqlite * hDB;
 149   char *pErrorMsg;
 150
 151   pDB->pErrorMsgBuffer = NULL;
 152   pDB->sizeErrorMsgBuffer = 0;
 153
 154   hDB = sqlite_open(
 155     pszDB,
 156     0,
 157     &pErrorMsg);
 158   if (hDB == NULL)
 159   {
 160     set_error_message(
 161       pDB,
 162       "Failed to open database. Error: %s",
 163       pErrorMsg);
 164
 165     free(pErrorMsg);
 166
 167     return MEDIADB_FAIL;
 168   }
 169
 170   pDB->hDB = hDB;
 171
 172   reset_error_message(pDB);
 173
 174   return MEDIADB_OK;
 175 }
 176
 177 mediadb_result
 178 mediadb_sqlite_close(
 179   mediadb_sqlite *pDB)
 180 {
 181   if (pDB->pErrorMsgBuffer != NULL)
 182   {
 183     free(pDB->pErrorMsgBuffer);
 184   }
 185
 186   sqlite_close(pDB->hDB);
 187
 188   return MEDIADB_OK;
 189 }
 190
 191 mediadb_result
 192 mediadb_sqlite_media_add_new(
 193   mediadb_sqlite *pDB,
 194   const char *pszName,
 195   const char *pszComment,
 196   mediadb_mediatype nType,
 197   mediadb_uint *pnNewMediaID)
 198 {
 199   int nRet;
 200   char *pErrorMsg;
 201   my_ulonglong nMediaID;
 202
 203   nRet = sqlite_exec_printf(
 204     pDB->hDB,
 205     "INSERT INTO " MEDIADB_SQLITE_MEDIA_TABLE
 206     " (catid,type,added,info1,info2,name,comment,location)"
 207     " VALUES (0,%u,0,0,0,'%q','%q','0')",
 208     0,
 209     0,
 210     &pErrorMsg,
 211     (unsigned int)nType,
 212     pszName,
 213     pszComment);
 214   if (nRet != SQLITE_OK)
 215   {
 216     set_error_message(
 217       pDB,
 218       "Failed to execute SQL query. Error: %s",
 219       pErrorMsg);
 220
 221     free(pErrorMsg);
 222
 223     return MEDIADB_FAIL;
 224   }
 225
 226   nMediaID = sqlite_last_insert_rowid(pDB->hDB);
 227   if (nMediaID == 0ULL)
 228   {
 229     set_error_message(
 230       pDB,
 231       "Invalid media ID 0 after media INSERT");
 232     return MEDIADB_FAIL;
 233   }
 234
 235   *pnNewMediaID = nMediaID;
 236
 237   reset_error_message(pDB);
 238
 239   return MEDIADB_OK;
 240 }
 241
 242 mediadb_result
 243 mediadb_sqlite_media_update_properties(
 244   mediadb_sqlite *pDB,
 245   mediadb_uint nMediaID,
 246   mediadb_uint nTimeAdded,
 247   mediadb_uint nTotalFiles,
 248   mediadb_uint nTotalSize)
 249 {
 250   int nRet;
 251   char *pErrorMsg;
 252
 253   nRet = sqlite_exec_printf(
 254     pDB->hDB,
 255     "UPDATE " MEDIADB_SQLITE_MEDIA_TABLE " SET added=%u, info1=%u, info2=%u WHERE mediaid = %u",
 256     0,
 257     0,
 258     &pErrorMsg,
 259     (unsigned int)nTimeAdded,
 260     (unsigned int)nTotalFiles,
 261     (unsigned int)nTotalSize,
 262     (unsigned int)nMediaID);
 263   if (nRet != SQLITE_OK)
 264   {
 265     set_error_message(
 266       pDB,
 267       "Failed to execute SQL query. Error: %s",
 268       pErrorMsg);
 269
 270     free(pErrorMsg);
 271
 272     return MEDIADB_FAIL;
 273   }
 274
 275   reset_error_message(pDB);
 276
 277   return MEDIADB_OK;
 278 }
 279
 280 mediadb_result
 281 mediadb_sqlite_file_add_new(
 282   mediadb_sqlite *pDB,
 283   mediadb_uint nMediaID,
 284   mediadb_filetype Filetype,
 285   const char *pszPath,
 286   const char *pszName,
 287   mediadb_uint nFileSize,
 288   mediadb_uint nFileTime)
 289 {
 290   int nRet;
 291   char *pErrorMsg;
 292
 293   if (Filetype != MEDIADB_FILETYPE_FILE &&
 294       Filetype != MEDIADB_FILETYPE_DIR)
 295   {
 296     set_error_message(
 297       pDB,
 298       "Cannot add file of unknown type");
 299     return MEDIADB_INVAL_ARG;
 300   }
 301
 302   nRet = sqlite_exec_printf(
 303     pDB->hDB,
 304     "INSERT INTO " MEDIADB_SQLITE_FILES_TABLE " (mediaid,size,time,path,name) VALUES (%u,%u,%u,'%q','%q%s')",
 305     0,
 306     0,
 307     &pErrorMsg,
 308     (unsigned int)nMediaID,
 309     (unsigned int)nFileSize,
 310     (unsigned int)nFileTime,
 311     pszPath,
 312     pszName,
 313     (Filetype == MEDIADB_FILETYPE_DIR)?"/":"");
 314   if (nRet != SQLITE_OK)
 315   {
 316     set_error_message(
 317       pDB,
 318       "Failed to execute SQL query. Error: %s",
 319       pErrorMsg);
 320
 321     free(pErrorMsg);
 322
 323     return MEDIADB_FAIL;
 324   }
 325
 326   reset_error_message(pDB);
 327
 328   return MEDIADB_OK;
 329 }
 330
 331 mediadb_result
 332 mediadb_sqlite_media_get_properties(
 333   mediadb_sqlite *pDB,
 334   mediadb_uint nMediaID,
 335   mediadb_uint *pnTimeAdded,
 336   mediadb_mediatype *pnType,
 337   mediadb_uint *pnLocationID,
 338   char **ppszTitle)
 339 {
 340   int nRet;
 341   char *pErrorMsg;
 342   char **pResult;
 343   int nRows, nColumns;
 344
 345   nRet = sqlite_get_table_printf(
 346     pDB->hDB,
 347     "SELECT added, type, name, location FROM " MEDIADB_SQLITE_MEDIA_TABLE " WHERE mediaid = %u",
 348     &pResult,
 349     &nRows,
 350     &nColumns,
 351     &pErrorMsg,
 352     (unsigned int)nMediaID);
 353   if (nRet != SQLITE_OK)
 354   {
 355     set_error_message(
 356       pDB,
 357       "Failed to execute SQL query. Error: %s",
 358       pErrorMsg);
 359
 360     free(pErrorMsg);
 361
 362     return MEDIADB_FAIL;
 363   }
 364
 365   if (nColumns != 4 ||
 366       nRows != 1)
 367   {
 368     set_error_message(
 369       pDB,
 370       "SQL query returned unexpected result (%d, %d).",
 371       nColumns,
 372       nRows);
 373
 374     sqlite_free_table(pResult);
 375
 376     free(pErrorMsg);
 377
 378     return MEDIADB_FAIL;
 379   }
 380
 381   if (pResult[4] == NULL)
 382   {
 383     *pnTimeAdded = 0;
 384   }
 385   else
 386   {
 387     *pnTimeAdded = strtoull(pResult[4], NULL, 10);
 388   }
 389
 390   if (pResult[5] == NULL)
 391   {
 392     *pnType = MEDIADB_MT_UNKNOWN;
 393   }
 394   else
 395   {
 396     *pnType = strtoull(pResult[5], NULL, 10);
 397   }
 398
 399   if (pResult[6] == NULL)
 400   {
 401     *ppszTitle = strdup("");
 402   }
 403   else
 404   {
 405     *ppszTitle = strdup(pResult[6]);
 406   }
 407
 408   if (pResult[7] == NULL)
 409   {
 410     *pnLocationID = MEDIADB_MT_UNKNOWN;
 411   }
 412   else
 413   {
 414     *pnLocationID = strtoull(pResult[7], NULL, 10);
 415   }
 416
 417   sqlite_free_table(pResult);
 418
 419   reset_error_message(pDB);
 420
 421   return MEDIADB_OK;
 422 }
 423
 424 mediadb_result
 425 mediadb_sqlite_location_get_properties(
 426   mediadb_sqlite *pDB,
 427   mediadb_uint nLocationID,
 428   mediadb_uint *pnLocationTypeID,
 429   char **ppszDescription)
 430 {
 431   int nRet;
 432   char *pErrorMsg;
 433   char **pResult;
 434   int nRows, nColumns;
 435
 436   nRet = sqlite_get_table_printf(
 437     pDB->hDB,
 438     "SELECT type, description FROM " MEDIADB_SQLITE_LOCATIONS_TABLE " WHERE id = %u",
 439     &pResult,
 440     &nRows,
 441     &nColumns,
 442     &pErrorMsg,
 443     (unsigned int)nLocationID);
 444   if (nRet != SQLITE_OK)
 445   {
 446     set_error_message(
 447       pDB,
 448       "Failed to execute SQL query. Error: %s",
 449       pErrorMsg);
 450
 451     free(pErrorMsg);
 452
 453     return MEDIADB_FAIL;
 454   }
 455
 456   if (nColumns != 2 ||
 457       nRows != 1)
 458   {
 459     set_error_message(
 460       pDB,
 461       "SQL query returned unexpected result (%d, %d).",
 462       nColumns,
 463       nRows);
 464
 465     sqlite_free_table(pResult);
 466
 467     free(pErrorMsg);
 468
 469     return MEDIADB_FAIL;
 470   }
 471
 472   if (pResult[2] == NULL)
 473   {
 474     *pnLocationTypeID = 0;
 475   }
 476   else
 477   {
 478     *pnLocationTypeID = strtoull(pResult[2], NULL, 10);
 479   }
 480
 481   if (pResult[3] == NULL)
 482   {
 483     *ppszDescription = strdup("");
 484   }
 485   else
 486   {
 487     *ppszDescription = strdup(pResult[3]);
 488   }
 489
 490   sqlite_free_table(pResult);
 491
 492   reset_error_message(pDB);
 493
 494   return MEDIADB_OK;
 495 }
 496
 497 mediadb_result
 498 mediadb_sqlite_location_type_get_properties(
 499   mediadb_sqlite *pDB,
 500   mediadb_uint nLocationTypeID,
 501   char **ppszDescription)
 502 {
 503   int nRet;
 504   char *pErrorMsg;
 505   char **pResult;
 506   int nRows, nColumns;
 507
 508   nRet = sqlite_get_table_printf(
 509     pDB->hDB,
 510     "SELECT description FROM " MEDIADB_SQLITE_LOCATION_TYPES_TABLE " WHERE id = %u",
 511     &pResult,
 512     &nRows,
 513     &nColumns,
 514     &pErrorMsg,
 515     (unsigned int)nLocationTypeID);
 516   if (nRet != SQLITE_OK)
 517   {
 518     set_error_message(
 519       pDB,
 520       "Failed to execute SQL query. Error: %s",
 521       pErrorMsg);
 522
 523     free(pErrorMsg);
 524
 525     return MEDIADB_FAIL;
 526   }
 527
 528   if (nColumns != 1 ||
 529       nRows != 1)
 530   {
 531     set_error_message(
 532       pDB,
 533       "SQL query returned unexpected result (%d, %d).",
 534       nColumns,
 535       nRows);
 536
 537     sqlite_free_table(pResult);
 538
 539     free(pErrorMsg);
 540
 541     return MEDIADB_FAIL;
 542   }
 543
 544   if (pResult[1] == NULL)
 545   {
 546     *ppszDescription = strdup("");
 547   }
 548   else
 549   {
 550     *ppszDescription = strdup(pResult[1]);
 551   }
 552
 553   sqlite_free_table(pResult);
 554
 555   reset_error_message(pDB);
 556
 557   return MEDIADB_OK;
 558 }
 559
 560 mediadb_result
 561 mediadb_sqlite_media_get_properties_data(
 562   mediadb_sqlite *pDB,
 563   mediadb_uint nMediaID,
 564   mediadb_uint *pnTotalFiles,
 565   mediadb_uint *pnTotalSize)
 566 {
 567   int nRet;
 568   char *pErrorMsg;
 569   char **pResult;
 570   int nRows, nColumns;
 571
 572   nRet = sqlite_get_table_printf(
 573     pDB->hDB,
 574     "SELECT info1, info2 FROM " MEDIADB_SQLITE_MEDIA_TABLE " WHERE mediaid = %u",
 575     &pResult,
 576     &nRows,
 577     &nColumns,
 578     &pErrorMsg,
 579     (unsigned int)nMediaID);
 580   if (nRet != SQLITE_OK)
 581   {
 582     set_error_message(
 583       pDB,
 584       "Failed to execute SQL query. Error: %s",
 585       pErrorMsg);
 586
 587     free(pErrorMsg);
 588
 589     return MEDIADB_FAIL;
 590   }
 591
 592   if (nColumns != 2 ||
 593       nRows != 1)
 594   {
 595     set_error_message(
 596       pDB,
 597       "SQL query returned unexpected result (%d, %d).",
 598       nColumns,
 599       nRows);
 600
 601     sqlite_free_table(pResult);
 602
 603     free(pErrorMsg);
 604
 605     return MEDIADB_FAIL;
 606   }
 607
 608   if (pResult[2] == NULL)
 609   {
 610     *pnTotalFiles = 0;
 611   }
 612   else
 613   {
 614     *pnTotalFiles = strtoull(pResult[2], NULL, 10);
 615   }
 616
 617   if (pResult[3] == NULL)
 618   {
 619     *pnTotalSize = 0;
 620   }
 621   else
 622   {
 623     *pnTotalSize = strtoull(pResult[3], NULL, 10);
 624   }
 625
 626   sqlite_free_table(pResult);
 627
 628   reset_error_message(pDB);
 629
 630   return MEDIADB_OK;
 631 }
 632
 633 mediadb_result
 634 mediadb_sqlite_delete_media_files(
 635   mediadb_sqlite *pDB,
 636   mediadb_uint nMediaID)
 637 {
 638   int nRet;
 639   char *pErrorMsg;
 640
 641   nRet = sqlite_exec_printf(
 642     pDB->hDB,
 643     "DELETE FROM " MEDIADB_SQLITE_FILES_TABLE " WHERE mediaid = %u",
 644     0,
 645     0,
 646     &pErrorMsg,
 647     (unsigned int)nMediaID);
 648   if (nRet != SQLITE_OK)
 649   {
 650     set_error_message(
 651       pDB,
 652       "Failed to execute SQL query. Error: %s",
 653       pErrorMsg);
 654
 655     free(pErrorMsg);
 656
 657     return MEDIADB_FAIL;
 658   }
 659
 660   reset_error_message(pDB);
 661
 662   return MEDIADB_OK;
 663 }
 664
 665 mediadb_result
 666 mediadb_sqlite_media_update_name(
 667   mediadb_sqlite *pDB,
 668   mediadb_uint nMediaID,
 669   const char *pszName)
 670 {
 671   int nRet;
 672   char *pErrorMsg;
 673
 674   nRet = sqlite_exec_printf(
 675     pDB->hDB,
 676     "UPDATE " MEDIADB_SQLITE_MEDIA_TABLE " SET name = '%q' WHERE mediaid = %u",
 677     0,
 678     0,
 679     &pErrorMsg,
 680     pszName,
 681     (unsigned int)nMediaID);
 682   if (nRet != SQLITE_OK)
 683   {
 684     set_error_message(
 685       pDB,
 686       "Failed to execute SQL query. Error: %s",
 687       pErrorMsg);
 688
 689     free(pErrorMsg);
 690
 691     return MEDIADB_FAIL;
 692   }
 693
 694   reset_error_message(pDB);
 695
 696   return MEDIADB_OK;
 697 }
 698
 699 struct mediadb_media_callback_context
 700 {
 701   mediadb_media_callback pCallback;
 702   void *pUserContext;
 703   sqlite * hDB;
 704 };
 705
 706 int
 707 mediadb_sqlite_media_get_callback(
 708   void *pArg,
 709   int argc,
 710   char **argv,
 711   char **columnNames)
 712 {
 713   long nMediaID;
 714   const char *pszName;
 715   const char *pszComment;
 716   long nAdded;
 717   long nTotalFiles;
 718   long nTotalSize;
 719   long nLocationID;
 720   mediadb_mediatype nType;
 721   char strLocation[1000];
 722   char *pszLocation;
 723   char *pLocationDescriptionBuffer;
 724   size_t s;
 725   int nRet;
 726   char *pErrorMsg;
 727   char **pResult;
 728   int nRows, nColumns;
 729
 730   pLocationDescriptionBuffer = NULL;
 731
 732   if (argv == NULL)
 733     return 0;
 734
 735   if (argc != 9)
 736     return SQLITE_ABORT;
 737
 738   /* mediaid */
 739   if (argv[0] == NULL)
 740     return SQLITE_ABORT;
 741
 742   nMediaID = atol(argv[0]);
 743   if (nMediaID <= 0)
 744     return SQLITE_ABORT;
 745
 746   /* catid */
 747   /* not used */
 748
 749   /* type */
 750   if (argv[2] == NULL)
 751     return SQLITE_ABORT;
 752   nType = atol(argv[2]);
 753   if (nAdded <= 0)
 754   {
 755     nType = MEDIADB_MT_EMPTY;
 756   }
 757
 758   /* added */
 759   if (argv[3] == NULL)
 760     return SQLITE_ABORT;
 761
 762   nAdded = atol(argv[3]);
 763   if (nAdded <= 0)
 764     return SQLITE_ABORT;
 765
 766   /* info1 */
 767   if (argv[4] == NULL)
 768     return SQLITE_ABORT;
 769   nTotalFiles = atol(argv[4]);
 770   if (nTotalFiles < 0)
 771     return SQLITE_ABORT;
 772
 773   /* info2 */
 774   if (argv[5] == NULL)
 775     return SQLITE_ABORT;
 776   nTotalSize = atol(argv[5]);
 777   if (nTotalSize < 0)
 778     return SQLITE_ABORT;
 779
 780   /* name */
 781   if (argv[6] == NULL)
 782     return SQLITE_ABORT;
 783
 784   pszName = argv[6];
 785
 786   /* comment */
 787   if (argv[7] == NULL)
 788     return SQLITE_ABORT;
 789
 790   pszComment = argv[7];
 791
 792   /* location */
 793   if (argv[8] == NULL)
 794     return SQLITE_ABORT;
 795   nLocationID = atol(argv[8]);
 796   if (nLocationID < 0)
 797     return SQLITE_ABORT;
 798
 799   nRet = sqlite_get_table_printf(
 800     ((struct mediadb_media_callback_context *) pArg)->hDB,
 801     "SELECT description FROM " MEDIADB_SQLITE_LOCATIONS_TABLE " WHERE id = %u",
 802     &pResult,
 803     &nRows,
 804     &nColumns,
 805     &pErrorMsg,
 806     (unsigned int)nLocationID);
 807   if (nRet != SQLITE_OK)
 808   {
 809     sprintf(strLocation,
 810             "Cannot get description for location with ID \"%ld\" (SQLite error \"%s\")",
 811             nLocationID,
 812             pErrorMsg);
 813
 814     free(pErrorMsg);
 815
 816     pszLocation = strLocation;
 817   }
 818   else if (nColumns != 1 ||
 819            nRows != 1)
 820   {
 821     sprintf(strLocation,
 822             "Cannot get description for location with ID \"%ld\" (SQLite query returned unexpected result (%d, %d).",
 823             nLocationID,
 824             nColumns,
 825             nRows);
 826     sqlite_free_table(pResult);
 827     pszLocation = strLocation;
 828   }
 829   else if (pResult[1] == NULL)
 830   {
 831     sprintf(strLocation,
 832             "Cannot get description for location with ID \"%ld\" (SQLite query returned NULL)",
 833             nLocationID);
 834     sqlite_free_table(pResult);
 835     pszLocation = strLocation;
 836   }
 837   else
 838   {
 839     s = strlen(pResult[1]);
 840
 841     if (s < sizeof(strLocation))
 842     {
 843       memcpy(strLocation, pResult[1], s);
 844       strLocation[s] = 0;
 845       pszLocation = strLocation;
 846     }
 847     else
 848     {
 849       pLocationDescriptionBuffer = malloc(s+1);
 850       if (pLocationDescriptionBuffer == NULL)
 851       {
 852         sprintf(strLocation,
 853                 "Cannot get description for location with ID \"%ld\" (out of memory)",
 854                 nLocationID);
 855         pszLocation = strLocation;
 856       }
 857       else
 858       {
 859         memcpy(pLocationDescriptionBuffer, pResult[1], s);
 860         pLocationDescriptionBuffer[s] = 0;
 861         pszLocation = pLocationDescriptionBuffer;
 862       }
 863     }
 864
 865     sqlite_free_table(pResult);
 866   }
 867
 868   ((struct mediadb_media_callback_context *) pArg)->pCallback(
 869     ((struct mediadb_media_callback_context *) pArg)->pUserContext,
 870     (mediadb_uint)nMediaID,
 871     nType,
 872     pszName,
 873     pszComment,
 874     (mediadb_uint)nAdded,
 875     (mediadb_uint)nTotalFiles,
 876     (mediadb_uint)nTotalSize,
 877     (mediadb_uint)nLocationID,
 878     pszLocation);
 879
 880   if (pLocationDescriptionBuffer != NULL)
 881   {
 882     free(pLocationDescriptionBuffer);
 883   }
 884
 885   return 0;
 886 }
 887
 888 mediadb_result
 889 mediadb_sqlite_media_get_all(
 890   mediadb_sqlite *pDB,
 891   mediadb_media_callback pCallback,
 892   void *pUserContext)
 893 {
 894   struct mediadb_media_callback_context context;
 895   int nRet;
 896   char *pErrorMsg;
 897
 898   context.pCallback = pCallback;
 899   context.pUserContext = pUserContext;
 900   context.hDB = pDB->hDB;
 901
 902   nRet = sqlite_exec(
 903     pDB->hDB,
 904     "SELECT * FROM " MEDIADB_SQLITE_MEDIA_TABLE " ORDER BY mediaid ASC",
 905     mediadb_sqlite_media_get_callback,
 906     &context,
 907     &pErrorMsg);
 908   if (nRet != SQLITE_OK)
 909   {
 910     set_error_message(
 911       pDB,
 912       "Failed to execute SQL query. Error: %s",
 913       pErrorMsg);
 914
 915     free(pErrorMsg);
 916
 917     return MEDIADB_FAIL;
 918   }
 919
 920   reset_error_message(pDB);
 921
 922   return MEDIADB_OK;
 923 }
 924
 925 struct mediadb_files_callback_context
 926 {
 927   mediadb_files_callback pCallback;
 928   void *pUserContext;
 929 };
 930
 931 int
 932 mediadb_sqlite_files_get_callback(
 933   void *pArg,
 934   int argc,
 935   char **argv,
 936   char **columnNames)
 937 {
 938   char *pszName;
 939   char *pszPath;
 940   long nSize;
 941   long nTime;
 942   mediadb_filetype Filetype = MEDIADB_FILETYPE_FILE;
 943   size_t s;
 944
 945   if (argv == NULL)
 946     return 0;
 947
 948   if (argc != 4)
 949     return SQLITE_ABORT;
 950
 951   /* path */
 952   if (argv[0] == NULL)
 953     return SQLITE_ABORT;
 954
 955   pszPath = argv[0];
 956
 957   /* name */
 958   if (argv[1] == NULL)
 959     return SQLITE_ABORT;
 960
 961   pszName = argv[1];
 962
 963   /* size */
 964   if (argv[2] == NULL)
 965     return SQLITE_ABORT;
 966
 967   nSize = atol(argv[2]);
 968
 969   /* time */
 970   if (argv[3] == NULL)
 971     return SQLITE_ABORT;
 972
 973   nTime = atol(argv[3]);
 974
 975   s = strlen(pszName);
 976
 977   if (s > 1 && pszName[s-1] == '/')
 978   {
 979     Filetype = MEDIADB_FILETYPE_DIR;
 980     pszName[s-1] = 0;
 981   }
 982
 983   ((struct mediadb_files_callback_context *) pArg)->pCallback(
 984     ((struct mediadb_files_callback_context *) pArg)->pUserContext,
 985     pszPath,
 986     pszName,
 987     Filetype,
 988     (mediadb_uint)nSize,
 989     (mediadb_uint)nTime);
 990
 991   return 0;
 992 }
 993
 994 mediadb_result
 995 mediadb_sqlite_files_get(
 996   mediadb_sqlite *pDB,
 997   mediadb_uint nMediaID,
 998   const char *pszPath,
 999   mediadb_files_callback pCallback,
1000   void *pUserContext)
1001 {
1002   struct mediadb_files_callback_context context;
1003   int nRet;
1004   char *pErrorMsg;
1005
1006   context.pCallback = pCallback;
1007   context.pUserContext = pUserContext;
1008
1009   nRet = sqlite_exec_printf(
1010     pDB->hDB,
1011     "SELECT path, name, size, time FROM " MEDIADB_SQLITE_FILES_TABLE " WHERE mediaid = %u AND path = '%q/'",
1012     mediadb_sqlite_files_get_callback,
1013     &context,
1014     &pErrorMsg,
1015     (unsigned int)nMediaID,
1016     pszPath?pszPath:"");
1017   if (nRet != SQLITE_OK)
1018   {
1019     set_error_message(
1020       pDB,
1021       "Failed to execute SQL query. Error: %s",
1022       pErrorMsg);
1023
1024     free(pErrorMsg);
1025
1026     return MEDIADB_FAIL;
1027   }
1028
1029   reset_error_message(pDB);
1030
1031   return MEDIADB_OK;
1032 }
1033
1034 mediadb_result
1035 mediadb_sqlite_get_pattern_match_methods(
1036   mediadb_sqlite *pDB,
1037   const struct mediadb_pattern_match_method **ppPMM)
1038 {
1039   *ppPMM =  mediadb_sqlite_pattern_match_methods;
1040   return MEDIADB_OK;
1041 }
1042
1043 struct mediadb_files_search_callback_context
1044 {
1045   mediadb_files_search_callback pCallback;
1046   void *pUserContext;
1047   sqlite * hDB;
1048 };
1049
1050 int
1051 mediadb_sqlite_files_search_callback(
1052   void *pArg,
1053   int argc,
1054   char **argv,
1055   char **columnNames)
1056 {
1057   char *pszName;
1058   char *pszPath;
1059   long nSize;
1060   long nTime;
1061   long nMediaID;
1062   long nLocationID;
1063   mediadb_filetype Filetype = MEDIADB_FILETYPE_FILE;
1064   size_t s;
1065   char strMediaName[1000];
1066   char *pszMediaName;
1067   char *pMediaNameBuffer;
1068   char strLocation[1000];
1069   char *pszLocation;
1070   char *pLocationDescriptionBuffer;
1071   int nRet;
1072   char *pErrorMsg;
1073   char **pResult;
1074   int nRows, nColumns;
1075
1076   pMediaNameBuffer = NULL;
1077   pLocationDescriptionBuffer = NULL;
1078
1079   if (argv == NULL)
1080     return 0;
1081
1082   if (argc != 5)
1083     return SQLITE_ABORT;
1084
1085   /* mediaid */
1086   if (argv[0] == NULL)
1087     return SQLITE_ABORT;
1088
1089   nMediaID = atol(argv[0]);
1090
1091   /* path */
1092   if (argv[1] == NULL)
1093     return SQLITE_ABORT;
1094
1095   pszPath = argv[1];
1096
1097   /* name */
1098   if (argv[2] == NULL)
1099     return SQLITE_ABORT;
1100
1101   pszName = argv[2];
1102
1103   /* size */
1104   if (argv[3] == NULL)
1105     return SQLITE_ABORT;
1106
1107   nSize = atol(argv[3]);
1108
1109   /* time */
1110   if (argv[4] == NULL)
1111     return SQLITE_ABORT;
1112
1113   nTime = atol(argv[4]);
1114
1115   s = strlen(pszName);
1116
1117   if (s > 1 && pszName[s-1] == '/')
1118   {
1119     Filetype = MEDIADB_FILETYPE_DIR;
1120     pszName[s-1] = 0;
1121   }
1122
1123   nLocationID = -1;
1124
1125   nRet = sqlite_get_table_printf(
1126     ((struct mediadb_files_search_callback_context *) pArg)->hDB,
1127     "SELECT name, location FROM " MEDIADB_SQLITE_MEDIA_TABLE " WHERE mediaid = %u",
1128     &pResult,
1129     &nRows,
1130     &nColumns,
1131     &pErrorMsg,
1132     (unsigned int)nMediaID);
1133   if (nRet != SQLITE_OK)
1134   {
1135     sprintf(strMediaName,
1136             "Cannot get name of media with ID \"%ld\" (SQLite error \"%s\")",
1137             nMediaID,
1138             pErrorMsg);
1139
1140     free(pErrorMsg);
1141
1142     pszMediaName = strMediaName;
1143   }
1144   else if (nColumns != 2 ||
1145            nRows != 1)
1146   {
1147     sprintf(strMediaName,
1148             "Cannot get name of media ID \"%ld\" (SQLite query returned unexpected result (%d, %d).",
1149             nMediaID,
1150             nColumns,
1151             nRows);
1152     sqlite_free_table(pResult);
1153     pszMediaName = strMediaName;
1154   }
1155   else
1156   {
1157     if (pResult[2] == NULL)
1158     {
1159       sprintf(strMediaName,
1160               "Cannot get name of media ID \"%ld\" (SQLite query returned NULL)",
1161               nMediaID);
1162       sqlite_free_table(pResult);
1163       pszMediaName = strMediaName;
1164     }
1165     else
1166     {
1167       s = strlen(pResult[2]);
1168
1169       if (s < sizeof(strMediaName))
1170       {
1171         memcpy(strMediaName, pResult[2], s);
1172         strMediaName[s] = 0;
1173         pszMediaName = strMediaName;
1174       }
1175       else
1176       {
1177         pMediaNameBuffer = malloc(s+1);
1178         if (pMediaNameBuffer == NULL)
1179         {
1180           sprintf(strMediaName,
1181                   "Cannot get description for location with ID \"%ld\" (out of memory)",
1182                   nMediaID);
1183           pszMediaName = strMediaName;
1184         }
1185         else
1186         {
1187           memcpy(pMediaNameBuffer, pResult[2], s);
1188           pMediaNameBuffer[s] = 0;
1189           pszMediaName = pMediaNameBuffer;
1190         }
1191       }
1192     }
1193
1194     if (pResult[3] != NULL)
1195     {
1196       nLocationID = atol(pResult[3]);
1197     }
1198
1199     sqlite_free_table(pResult);
1200   }
1201
1202   if (nLocationID >= 0)
1203   {
1204     nRet = sqlite_get_table_printf(
1205       ((struct mediadb_media_callback_context *) pArg)->hDB,
1206       "SELECT description FROM " MEDIADB_SQLITE_LOCATIONS_TABLE " WHERE id = %u",
1207       &pResult,
1208       &nRows,
1209       &nColumns,
1210       &pErrorMsg,
1211       (unsigned int)nLocationID);
1212     if (nRet != SQLITE_OK)
1213     {
1214       sprintf(strLocation,
1215               "Cannot get description for location with ID \"%ld\" (SQLite error \"%s\")",
1216               nLocationID,
1217               pErrorMsg);
1218
1219       free(pErrorMsg);
1220
1221       pszLocation = strLocation;
1222     }
1223     else if (nColumns != 1 ||
1224              nRows != 1)
1225     {
1226       sprintf(strLocation,
1227               "Cannot get description for location with ID \"%ld\" (SQLite query returned unexpected result (%d, %d).",
1228               nLocationID,
1229               nColumns,
1230               nRows);
1231       sqlite_free_table(pResult);
1232       pszLocation = strLocation;
1233     }
1234     else if (pResult[1] == NULL)
1235     {
1236       sprintf(strLocation,
1237               "Cannot get description for location with ID \"%ld\" (SQLite query returned NULL)",
1238               nLocationID);
1239       sqlite_free_table(pResult);
1240       pszLocation = strLocation;
1241     }
1242     else
1243     {
1244       s = strlen(pResult[1]);
1245
1246       if (s < sizeof(strLocation))
1247       {
1248         memcpy(strLocation, pResult[1], s);
1249         strLocation[s] = 0;
1250         pszLocation = strLocation;
1251       }
1252       else
1253       {
1254         pLocationDescriptionBuffer = malloc(s+1);
1255         if (pLocationDescriptionBuffer == NULL)
1256         {
1257           sprintf(strLocation,
1258                   "Cannot get description for location with ID \"%ld\" (out of memory)",
1259                   nLocationID);
1260           pszLocation = strLocation;
1261         }
1262         else
1263         {
1264           memcpy(pLocationDescriptionBuffer, pResult[1], s);
1265           pLocationDescriptionBuffer[s] = 0;
1266           pszLocation = pLocationDescriptionBuffer;
1267         }
1268       }
1269
1270       sqlite_free_table(pResult);
1271     }
1272   }
1273   else
1274   {
1275     pszLocation = "Cannot retrieve location from database";
1276   }
1277
1278   ((struct mediadb_files_search_callback_context *) pArg)->pCallback(
1279     ((struct mediadb_files_search_callback_context *) pArg)->pUserContext,
1280     nMediaID,
1281     MEDIADB_MT_DATA,
1282     pszMediaName,
1283     pszLocation,
1284     pszPath,
1285     pszName,
1286     Filetype,
1287     (mediadb_uint)nSize,
1288     (mediadb_uint)nTime);
1289
1290   if (pMediaNameBuffer != NULL)
1291   {
1292     free(pMediaNameBuffer);
1293   }
1294
1295   if (pLocationDescriptionBuffer != NULL)
1296   {
1297     free(pLocationDescriptionBuffer);
1298   }
1299
1300   return 0;
1301 }
1302
1303 const char *
1304 mediadb_sqlite_get_match_keyword(mediadb_uint nPMMID)
1305 {
1306   switch (nPMMID)
1307   {
1308   case MEDIADB_SQLITE_PMM_EXACT_CASE_INSENSITIVE:
1309     return " == ";
1310   case MEDIADB_SQLITE_PMM_LIKE_CASE_INSENSITIVE:
1311     return " LIKE ";
1312   }
1313
1314   assert(0);
1315   return "XXX_BUG_XXX";
1316 }
1317
1318 mediadb_result
1319 mediadb_sqlite_files_search(
1320   mediadb_sqlite *pDB,
1321   mediadb_uint nFilenamePMMID,
1322   const char *pszFilenamePattern,
1323   mediadb_uint nPathPMMID,
1324   const char *pszPathPattern,
1325   const mediadb_uint *pnMinSize,
1326   const mediadb_uint *pnMaxSize,
1327   mediadb_files_search_callback pCallback,
1328   void *pUserContext)
1329 {
1330   struct mediadb_files_search_callback_context context;
1331   int nRet;
1332   char *pErrorMsg;
1333   mediadb_result r;
1334   char *pszWhere;
1335   size_t sizeWhereAllocated;
1336   size_t sizeWhereUsed;
1337   size_t sizeSearchCriteria;
1338   size_t sizeAND;
1339   int nCount;
1340   const char *pszAND;
1341   char *pszTemp;
1342
1343   context.pCallback = pCallback;
1344   context.pUserContext = pUserContext;
1345   context.hDB = pDB->hDB;
1346
1347   /* Compose SQL WHERE expression */
1348
1349   pszWhere = NULL;
1350   sizeWhereAllocated = 0;
1351   sizeWhereUsed = 0;
1352   sizeSearchCriteria = 0;
1353   nCount = 0;
1354   pszAND = " AND ";
1355   sizeAND = strlen(pszAND);
1356
1357   if (nFilenamePMMID != MEDIADB_PMM_NULL)
1358   {
1359     nCount++;
1360   }
1361
1362   if (nPathPMMID != MEDIADB_PMM_NULL)
1363   {
1364     nCount++;
1365   }
1366
1367   if (pnMinSize != NULL)
1368   {
1369     nCount++;
1370   }
1371
1372   if (pnMaxSize != NULL)
1373   {
1374     nCount++;
1375   }
1376
1377   if (nFilenamePMMID != MEDIADB_PMM_NULL)
1378   {
1379     nCount--;
1380
1381     pszTemp = sqlite_mprintf(
1382       "name%s'%q'",
1383       mediadb_sqlite_get_match_keyword(nFilenamePMMID),
1384       pszFilenamePattern);
1385     if (pszTemp == NULL)
1386     {
1387       r = MEDIADB_MEM;
1388       goto FailFreeWhere;
1389     }
1390
1391     sizeSearchCriteria = strlen(pszTemp);
1392     r = maybe_enlarge_buffer(
1393       &pszWhere,
1394       &sizeWhereAllocated,
1395       sizeWhereUsed + sizeSearchCriteria + (nCount == 0?0:sizeAND));
1396     if (MEDIADB_IS_ERROR(r))
1397     {
1398       goto FailFreeTemp;
1399     }
1400
1401     sizeWhereUsed += sprintf(
1402       pszWhere + sizeWhereUsed,
1403       "%s%s",
1404       pszTemp,
1405       nCount == 0?"":pszAND);
1406
1407     sqlite_freemem(pszTemp);
1408   }
1409
1410   if (nPathPMMID != MEDIADB_PMM_NULL)
1411   {
1412     nCount--;
1413
1414     pszTemp = sqlite_mprintf(
1415       "path%s'%q'",
1416       mediadb_sqlite_get_match_keyword(nPathPMMID),
1417       pszPathPattern);
1418     if (pszTemp == NULL)
1419     {
1420       r = MEDIADB_MEM;
1421       goto FailFreeWhere;
1422     }
1423
1424     sizeSearchCriteria = strlen(pszTemp);
1425     r = maybe_enlarge_buffer(
1426       &pszWhere,
1427       &sizeWhereAllocated,
1428       sizeWhereUsed + sizeSearchCriteria + (nCount == 0?0:sizeAND));
1429     if (MEDIADB_IS_ERROR(r))
1430     {
1431       goto FailFreeTemp;
1432     }
1433
1434     sizeWhereUsed += sprintf(
1435       pszWhere + sizeWhereUsed,
1436       "%s%s",
1437       pszTemp,
1438       nCount == 0?"":pszAND);
1439
1440     sqlite_freemem(pszTemp);
1441   }
1442
1443   if (pnMinSize != NULL)
1444   {
1445     nCount--;
1446
1447     pszTemp = sqlite_mprintf(
1448       "size >= %u",
1449       (unsigned int)*pnMinSize);
1450     if (pszTemp == NULL)
1451     {
1452       r = MEDIADB_MEM;
1453       goto FailFreeWhere;
1454     }
1455
1456     sizeSearchCriteria = strlen(pszTemp);
1457     r = maybe_enlarge_buffer(
1458       &pszWhere,
1459       &sizeWhereAllocated,
1460       sizeWhereUsed + sizeSearchCriteria + (nCount == 0?0:sizeAND));
1461     if (MEDIADB_IS_ERROR(r))
1462     {
1463       goto FailFreeTemp;
1464     }
1465
1466     sizeWhereUsed += sprintf(
1467       pszWhere + sizeWhereUsed,
1468       "%s%s",
1469       pszTemp,
1470       nCount == 0?"":pszAND);
1471
1472     sqlite_freemem(pszTemp);
1473   }
1474
1475   if (pnMaxSize != NULL)
1476   {
1477     nCount--;
1478
1479     pszTemp = sqlite_mprintf(
1480       "size <= %u",
1481       (unsigned int)*pnMaxSize);
1482     if (pszTemp == NULL)
1483     {
1484       r = MEDIADB_MEM;
1485       goto FailFreeWhere;
1486     }
1487
1488     sizeSearchCriteria = strlen(pszTemp);
1489     r = maybe_enlarge_buffer(
1490       &pszWhere,
1491       &sizeWhereAllocated,
1492       sizeWhereUsed + sizeSearchCriteria + (nCount == 0?0:sizeAND));
1493     if (MEDIADB_IS_ERROR(r))
1494     {
1495       goto FailFreeTemp;
1496     }
1497
1498     sizeWhereUsed += sprintf(
1499       pszWhere + sizeWhereUsed,
1500       "%s%s",
1501       pszTemp,
1502       nCount == 0?"":pszAND);
1503
1504     sqlite_freemem(pszTemp);
1505   }
1506
1507   if (pszWhere == NULL)
1508   {
1509     /* Nothing to search - empty result */
1510     return MEDIADB_OK;
1511   }
1512
1513   assert(nCount == 0);
1514
1515   /* Execute SQl query */
1516   nRet = sqlite_exec_printf(
1517     pDB->hDB,
1518     "SELECT mediaid, path, name, size, time FROM " MEDIADB_SQLITE_FILES_TABLE " WHERE %s",
1519     mediadb_sqlite_files_search_callback,
1520     &context,
1521     &pErrorMsg,
1522     pszWhere);
1523   if (nRet != SQLITE_OK)
1524   {
1525     set_error_message(
1526       pDB,
1527       "Failed to execute SQL query. Error: %s",
1528       pErrorMsg);
1529
1530     free(pErrorMsg);
1531     free(pszWhere);
1532
1533     return MEDIADB_FAIL;
1534   }
1535
1536   free(pszWhere);
1537
1538   reset_error_message(pDB);
1539
1540   return MEDIADB_OK;
1541
1542 FailFreeTemp:
1543   sqlite_freemem(pszTemp);
1544
1545 FailFreeWhere:
1546   if (pszWhere != NULL)
1547   {
1548     free(pszWhere);
1549   }
1550
1551   return r;
1552 }
1553
1554 /*****************************************************************************
1555  *
1556  * Modifications log:
1557  *
1558  * !!! WARNING !!! Following lines are automatically updated by the CVS system.
1559  *
1560  *   $Log: sqlite.c,v $
1561  *   Revision 1.11  2004/09/18 21:32:34  nedko
1562  *   Use min and max size when searching files.
1563  *   Return media name, media type and media location when searching files.
1564  *
1565  *   Revision 1.10  2004/09/01 05:02:17  nedko
1566  *   Implement search by filename and path
1567  *
1568  *   Revision 1.9  2004/08/31 22:40:15  nedko
1569  *   Partitally implemented search feature.
1570  *
1571  *   Revision 1.8  2004/08/08 00:47:42  nedko
1572  *   Get more info for media from database.
1573  *
1574  *   Revision 1.7  2004/05/21 23:40:46  nedko
1575  *   New functionality: mediadb_files_get()
1576  *
1577  *   Revision 1.6  2004/05/16 18:57:12  nedko
1578  *   media_get_all functionality implemented.
1579  *
1580  *   Revision 1.5  2004/05/11 01:18:53  nedko
1581  *   Implement SQLite backend.
1582  *
1583  *   Revision 1.4  2004/05/03 20:47:15  nedko
1584  *   Update mode for cui
1585  *
1586  *   Revision 1.3  2004/05/02 20:12:11  nedko
1587  *   Improve error dumps.
1588  *
1589  *   Revision 1.2  2004/05/02 13:16:58  nedko
1590  *   Supply filetype when adding new file
1591  *
1592  *   Revision 1.1  2004/04/27 09:12:28  nedko
1593  *   Initial revision.
1594  *
1595  *****************************************************************************/