libdb/sqlite.c

   1 /* -*- Mode: C ; c-basic-offset: 2 -*- */
   2 /*****************************************************************************
   3  *
   4  * $Id: sqlite.c,v 1.6 2004/05/16 18:57:12 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
  22 #include "../result.h"
  23 #include "internal.h"
  24 #include "memory.h"
  25
  26 #define MEDIADB_SQLITE_MEDIA_TABLE "media"
  27 #define MEDIADB_SQLITE_FILES_TABLE "files"
  28 #define MEDIADB_SQLITE_LOCATIONS_TABLE "locations"
  29
  30 //#define DUMMY_SQL_QUERIES
  31 #define FORMATTING_BUFFER_SIZE 10240
  32
  33 static
  34 void
  35 set_error_message(
  36   mediadb_sqlite *pDB,
  37   const char *pszFormat,
  38   ...)
  39 {
  40   mediadb_result r;
  41   size_t s;
  42   va_list argList;
  43   char Buffer[FORMATTING_BUFFER_SIZE];
  44
  45   va_start(argList, pszFormat);
  46   vsnprintf(Buffer, FORMATTING_BUFFER_SIZE-1, pszFormat, argList);
  47   va_end(argList);
  48
  49   s = strlen(Buffer);
  50
  51   r = maybe_enlarge_buffer(
  52     &pDB->pErrorMsgBuffer,
  53     &pDB->sizeErrorMsgBuffer,
  54     s+1);
  55   if (MEDIADB_IS_ERROR(r))
  56   {
  57     if (pDB->sizeErrorMsgBuffer > 0)
  58     {
  59       pDB->pErrorMsgBuffer[0] = 0;
  60     }
  61   }
  62
  63   memcpy(pDB->pErrorMsgBuffer, Buffer, s + 1);
  64 }
  65
  66 static
  67 void
  68 reset_error_message(
  69   mediadb_sqlite *pDB)
  70 {
  71   if (pDB->pErrorMsgBuffer != NULL)
  72   {
  73     pDB->pErrorMsgBuffer[0] = 0;
  74   }
  75 }
  76
  77 const char *
  78 mediadb_sqlite_get_error_message(
  79   mediadb_sqlite *pDB)
  80 {
  81   if (pDB->pErrorMsgBuffer == NULL)
  82   {
  83     return "";
  84   }
  85
  86   return pDB->pErrorMsgBuffer;
  87 }
  88
  89 mediadb_result
  90 mediadb_sqlite_open(
  91   mediadb_sqlite *pDB,
  92   const char *pszHost,
  93   const char *pszUser,
  94   const char *pszPass,
  95   const char *pszDB)
  96 {
  97   sqlite * hDB;
  98   char *pErrorMsg;
  99
 100   pDB->pErrorMsgBuffer = NULL;
 101   pDB->sizeErrorMsgBuffer = 0;
 102
 103   hDB = sqlite_open(
 104     pszDB,
 105     0,
 106     &pErrorMsg);
 107   if (hDB == NULL)
 108   {
 109     set_error_message(
 110       pDB,
 111       "Failed to open database. Error: %s",
 112       pErrorMsg);
 113
 114     free(pErrorMsg);
 115
 116     return MEDIADB_FAIL;
 117   }
 118
 119   pDB->hDB = hDB;
 120
 121   reset_error_message(pDB);
 122
 123   return MEDIADB_OK;
 124 }
 125
 126 mediadb_result
 127 mediadb_sqlite_close(
 128   mediadb_sqlite *pDB)
 129 {
 130   if (pDB->pErrorMsgBuffer != NULL)
 131   {
 132     free(pDB->pErrorMsgBuffer);
 133   }
 134
 135   sqlite_close(pDB->hDB);
 136
 137   return MEDIADB_OK;
 138 }
 139
 140 mediadb_result
 141 mediadb_sqlite_media_add_new(
 142   mediadb_sqlite *pDB,
 143   const char *pszName,
 144   const char *pszComment,
 145   mediadb_mediatype nType,
 146   mediadb_uint *pnNewMediaID)
 147 {
 148   int nRet;
 149   char *pErrorMsg;
 150   my_ulonglong nMediaID;
 151
 152   nRet = sqlite_exec_printf(
 153     pDB->hDB,
 154     "INSERT INTO " MEDIADB_SQLITE_MEDIA_TABLE
 155     " (catid,type,added,info1,info2,name,comment,location)"
 156     " VALUES (0,%u,0,0,0,'%q','%q','0')",
 157     0,
 158     0,
 159     &pErrorMsg,
 160     (unsigned int)nType,
 161     pszName,
 162     pszComment);
 163   if (nRet != SQLITE_OK)
 164   {
 165     set_error_message(
 166       pDB,
 167       "Failed to execute SQL query. Error: %s",
 168       pErrorMsg);
 169
 170     free(pErrorMsg);
 171
 172     return MEDIADB_FAIL;
 173   }
 174
 175   nMediaID = sqlite_last_insert_rowid(pDB->hDB);
 176   if (nMediaID == 0ULL)
 177   {
 178     set_error_message(
 179       pDB,
 180       "Invalid media ID 0 after media INSERT");
 181     return MEDIADB_FAIL;
 182   }
 183
 184   *pnNewMediaID = nMediaID;
 185
 186   reset_error_message(pDB);
 187
 188   return MEDIADB_OK;
 189 }
 190
 191 mediadb_result
 192 mediadb_sqlite_media_update_properties(
 193   mediadb_sqlite *pDB,
 194   mediadb_uint nMediaID,
 195   mediadb_uint nTimeAdded,
 196   mediadb_uint nTotalFiles,
 197   mediadb_uint nTotalSize)
 198 {
 199   int nRet;
 200   char *pErrorMsg;
 201
 202   nRet = sqlite_exec_printf(
 203     pDB->hDB,
 204     "UPDATE " MEDIADB_SQLITE_MEDIA_TABLE " SET added=%u, info1=%u, info2=%u WHERE mediaid = %u",
 205     0,
 206     0,
 207     &pErrorMsg,
 208     (unsigned int)nTimeAdded,
 209     (unsigned int)nTotalFiles,
 210     (unsigned int)nTotalSize,
 211     (unsigned int)nMediaID);
 212   if (nRet != SQLITE_OK)
 213   {
 214     set_error_message(
 215       pDB,
 216       "Failed to execute SQL query. Error: %s",
 217       pErrorMsg);
 218
 219     free(pErrorMsg);
 220
 221     return MEDIADB_FAIL;
 222   }
 223
 224   reset_error_message(pDB);
 225
 226   return MEDIADB_OK;
 227 }
 228
 229 mediadb_result
 230 mediadb_sqlite_file_add_new(
 231   mediadb_sqlite *pDB,
 232   mediadb_uint nMediaID,
 233   mediadb_filetype Filetype,
 234   const char *pszPath,
 235   const char *pszName,
 236   mediadb_uint nFileSize,
 237   mediadb_uint nFileTime)
 238 {
 239   int nRet;
 240   char *pErrorMsg;
 241
 242   if (Filetype != MEDIADB_FILETYPE_FILE &&
 243       Filetype != MEDIADB_FILETYPE_DIR)
 244   {
 245     set_error_message(
 246       pDB,
 247       "Cannot add file of unknown type");
 248     return MEDIADB_INVAL_ARG;
 249   }
 250
 251   nRet = sqlite_exec_printf(
 252     pDB->hDB,
 253     "INSERT INTO " MEDIADB_SQLITE_FILES_TABLE " (mediaid,size,time,path,name) VALUES (%u,%u,%u,'%q','%q%s')",
 254     0,
 255     0,
 256     &pErrorMsg,
 257     (unsigned int)nMediaID,
 258     (unsigned int)nFileSize,
 259     (unsigned int)nFileTime,
 260     pszPath,
 261     pszName,
 262     (Filetype == MEDIADB_FILETYPE_DIR)?"/":"");
 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_media_get_properties(
 282   mediadb_sqlite *pDB,
 283   mediadb_uint nMediaID,
 284   mediadb_uint *pnTimeAdded,
 285   mediadb_uint *pnTotalFiles,
 286   mediadb_uint *pnTotalSize,
 287   char **ppszTitle)
 288 {
 289   int nRet;
 290   char *pErrorMsg;
 291   char **pResult;
 292   int nRows, nColumns;
 293
 294   nRet = sqlite_get_table_printf(
 295     pDB->hDB,
 296     "SELECT added, info1, info2, name FROM " MEDIADB_SQLITE_MEDIA_TABLE " WHERE mediaid = %u",
 297     &pResult,
 298     &nRows,
 299     &nColumns,
 300     &pErrorMsg,
 301     (unsigned int)nMediaID);
 302   if (nRet != SQLITE_OK)
 303   {
 304     set_error_message(
 305       pDB,
 306       "Failed to execute SQL query. Error: %s",
 307       pErrorMsg);
 308
 309     free(pErrorMsg);
 310
 311     return MEDIADB_FAIL;
 312   }
 313
 314   if (nColumns != 4 ||
 315       nRows != 1)
 316   {
 317     set_error_message(
 318       pDB,
 319       "SQL query returned unexpected result (%d, %d).",
 320       nColumns,
 321       nRows);
 322
 323     sqlite_free_table(pResult);
 324
 325     free(pErrorMsg);
 326
 327     return MEDIADB_FAIL;
 328   }
 329
 330   if (pResult[4] == NULL)
 331   {
 332     *pnTimeAdded = 0;
 333   }
 334   else
 335   {
 336     *pnTimeAdded = strtoull(pResult[4], NULL, 10);
 337   }
 338
 339   if (pResult[5] == NULL)
 340   {
 341     *pnTotalFiles = 0;
 342   }
 343   else
 344   {
 345     *pnTotalFiles = strtoull(pResult[5], NULL, 10);
 346   }
 347
 348   if (pResult[6] == NULL)
 349   {
 350     *pnTotalSize = 0;
 351   }
 352   else
 353   {
 354     *pnTotalSize = strtoull(pResult[6], NULL, 10);
 355   }
 356
 357   if (pResult[7] == NULL)
 358   {
 359     *ppszTitle = strdup("");
 360   }
 361   else
 362   {
 363     *ppszTitle = strdup(pResult[7]);
 364   }
 365
 366   sqlite_free_table(pResult);
 367
 368   reset_error_message(pDB);
 369
 370   return MEDIADB_OK;
 371 }
 372
 373 mediadb_result
 374 mediadb_sqlite_delete_media_files(
 375   mediadb_sqlite *pDB,
 376   mediadb_uint nMediaID)
 377 {
 378   int nRet;
 379   char *pErrorMsg;
 380
 381   nRet = sqlite_exec_printf(
 382     pDB->hDB,
 383     "DELETE FROM " MEDIADB_SQLITE_FILES_TABLE " WHERE mediaid = %u",
 384     0,
 385     0,
 386     &pErrorMsg,
 387     (unsigned int)nMediaID);
 388   if (nRet != SQLITE_OK)
 389   {
 390     set_error_message(
 391       pDB,
 392       "Failed to execute SQL query. Error: %s",
 393       pErrorMsg);
 394
 395     free(pErrorMsg);
 396
 397     return MEDIADB_FAIL;
 398   }
 399
 400   reset_error_message(pDB);
 401
 402   return MEDIADB_OK;
 403 }
 404
 405 mediadb_result
 406 mediadb_sqlite_media_update_name(
 407   mediadb_sqlite *pDB,
 408   mediadb_uint nMediaID,
 409   const char *pszName)
 410 {
 411   int nRet;
 412   char *pErrorMsg;
 413
 414   nRet = sqlite_exec_printf(
 415     pDB->hDB,
 416     "UPDATE " MEDIADB_SQLITE_MEDIA_TABLE " SET name = '%q' WHERE mediaid = %u",
 417     0,
 418     0,
 419     &pErrorMsg,
 420     pszName,
 421     (unsigned int)nMediaID);
 422   if (nRet != SQLITE_OK)
 423   {
 424     set_error_message(
 425       pDB,
 426       "Failed to execute SQL query. Error: %s",
 427       pErrorMsg);
 428
 429     free(pErrorMsg);
 430
 431     return MEDIADB_FAIL;
 432   }
 433
 434   reset_error_message(pDB);
 435
 436   return MEDIADB_OK;
 437 }
 438
 439 struct mediadb_media_callback_context
 440 {
 441   mediadb_media_callback pCallback;
 442   void *pUserContext;
 443   sqlite * hDB;
 444 };
 445
 446 int
 447 mediadb_sqlite_media_get_callback(
 448   void *pArg,
 449   int argc,
 450   char **argv,
 451   char **columnNames)
 452 {
 453   long nMediaID;
 454   const char *pszName;
 455   const char *pszComment;
 456   long nAdded;
 457   long nTotalFiles;
 458   long nTotalSize;
 459   long nLocationID;
 460   mediadb_mediatype nType;
 461   char strLocation[1000];
 462   char *pszLocation;
 463   char *pLocationDescriptionBuffer;
 464   size_t s;
 465   int nRet;
 466   char *pErrorMsg;
 467   char **pResult;
 468   int nRows, nColumns;
 469
 470   pLocationDescriptionBuffer = NULL;
 471
 472   if (argv == NULL)
 473     return 0;
 474
 475   if (argc != 9)
 476     return SQLITE_ABORT;
 477
 478   /* mediaid */
 479   if (argv[0] == NULL)
 480     return SQLITE_ABORT;
 481
 482   nMediaID = atol(argv[0]);
 483   if (nMediaID <= 0)
 484     return SQLITE_ABORT;
 485
 486   /* catid */
 487   /* not used */
 488
 489   /* type */
 490   if (argv[2] == NULL)
 491     return SQLITE_ABORT;
 492   nType = atol(argv[2]);
 493   if (nAdded <= 0)
 494   {
 495     nType = MEDIADB_MT_EMPTY;
 496   }
 497
 498   /* added */
 499   if (argv[3] == NULL)
 500     return SQLITE_ABORT;
 501
 502   nAdded = atol(argv[3]);
 503   if (nAdded <= 0)
 504     return SQLITE_ABORT;
 505
 506   /* info1 */
 507   if (argv[4] == NULL)
 508     return SQLITE_ABORT;
 509   nTotalFiles = atol(argv[4]);
 510   if (nTotalFiles < 0)
 511     return SQLITE_ABORT;
 512
 513   /* info2 */
 514   if (argv[5] == NULL)
 515     return SQLITE_ABORT;
 516   nTotalSize = atol(argv[5]);
 517   if (nTotalSize < 0)
 518     return SQLITE_ABORT;
 519
 520   /* name */
 521   if (argv[6] == NULL)
 522     return SQLITE_ABORT;
 523
 524   pszName = argv[6];
 525
 526   /* comment */
 527   if (argv[7] == NULL)
 528     return SQLITE_ABORT;
 529
 530   pszComment = argv[7];
 531
 532   /* location */
 533   if (argv[8] == NULL)
 534     return SQLITE_ABORT;
 535   nLocationID = atol(argv[8]);
 536   if (nLocationID < 0)
 537     return SQLITE_ABORT;
 538
 539   nRet = sqlite_get_table_printf(
 540     ((struct mediadb_media_callback_context *) pArg)->hDB,
 541     "SELECT description FROM " MEDIADB_SQLITE_LOCATIONS_TABLE " WHERE id = %u",
 542     &pResult,
 543     &nRows,
 544     &nColumns,
 545     &pErrorMsg,
 546     (unsigned int)nLocationID);
 547   if (nRet != SQLITE_OK)
 548   {
 549     sprintf(strLocation,
 550             "Cannot get description for location with ID \"%ld\" (SQLite error \"%s\")",
 551             nLocationID,
 552             pErrorMsg);
 553
 554     free(pErrorMsg);
 555
 556     pszLocation = strLocation;
 557   }
 558   else if (nColumns != 1 ||
 559       nRows != 1)
 560   {
 561     sprintf(strLocation,
 562             "Cannot get description for location with ID \"%ld\" (SQLite query returned unexpected result (%d, %d).",
 563             nLocationID,
 564             nColumns,
 565             nRows);
 566     sqlite_free_table(pResult);
 567     pszLocation = strLocation;
 568   }
 569   else if (pResult[1] == NULL)
 570   {
 571     sprintf(strLocation,
 572             "Cannot get description for location with ID \"%ld\" (SQLite query returned NULL)",
 573             nLocationID);
 574     sqlite_free_table(pResult);
 575     pszLocation = strLocation;
 576   }
 577   else
 578   {
 579     s = strlen(pResult[1]);
 580
 581     if (s < sizeof(strLocation))
 582     {
 583       memcpy(strLocation, pResult[1], s);
 584       strLocation[s] = 0;
 585       pszLocation = strLocation;
 586     }
 587     else
 588     {
 589       pLocationDescriptionBuffer = malloc(s+1);
 590       if (pLocationDescriptionBuffer == NULL)
 591       {
 592         sprintf(strLocation,
 593                 "Cannot get description for location with ID \"%ld\" (out of memory)",
 594                 nLocationID);
 595         pszLocation = strLocation;
 596       }
 597       else
 598       {
 599         memcpy(pLocationDescriptionBuffer, pResult[1], s);
 600         pLocationDescriptionBuffer[s] = 0;
 601         pszLocation = pLocationDescriptionBuffer;
 602       }
 603     }
 604
 605     sqlite_free_table(pResult);
 606   }
 607
 608   ((struct mediadb_media_callback_context *) pArg)->pCallback(
 609     ((struct mediadb_media_callback_context *) pArg)->pUserContext,
 610     (mediadb_uint)nMediaID,
 611     nType,
 612     pszName,
 613     pszComment,
 614     (mediadb_uint)nAdded,
 615     (mediadb_uint)nTotalFiles,
 616     (mediadb_uint)nTotalSize,
 617     (mediadb_uint)nLocationID,
 618     pszLocation);
 619
 620   if (pLocationDescriptionBuffer != NULL)
 621   {
 622     free(pLocationDescriptionBuffer);
 623   }
 624
 625   return 0;
 626 }
 627
 628 mediadb_result
 629 mediadb_sqlite_media_get_all(
 630   mediadb_sqlite *pDB,
 631   mediadb_media_callback pCallback,
 632   void *pUserContext)
 633 {
 634   struct mediadb_media_callback_context context;
 635   int nRet;
 636   char *pErrorMsg;
 637
 638   context.pCallback = pCallback;
 639   context.pUserContext = pUserContext;
 640   context.hDB = pDB->hDB;
 641
 642   nRet = sqlite_exec(
 643     pDB->hDB,
 644     "SELECT * FROM " MEDIADB_SQLITE_MEDIA_TABLE " ORDER BY mediaid ASC",
 645     mediadb_sqlite_media_get_callback,
 646     &context,
 647     &pErrorMsg);
 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 /*****************************************************************************
 666  *
 667  * Modifications log:
 668  *
 669  * !!! WARNING !!! Following lines are automatically updated by the CVS system.
 670  *
 671  *   $Log: sqlite.c,v $
 672  *   Revision 1.6  2004/05/16 18:57:12  nedko
 673  *   media_get_all functionality implemented.
 674  *
 675  *   Revision 1.5  2004/05/11 01:18:53  nedko
 676  *   Implement SQLite backend.
 677  *
 678  *   Revision 1.4  2004/05/03 20:47:15  nedko
 679  *   Update mode for cui
 680  *
 681  *   Revision 1.3  2004/05/02 20:12:11  nedko
 682  *   Improve error dumps.
 683  *
 684  *   Revision 1.2  2004/05/02 13:16:58  nedko
 685  *   Supply filetype when adding new file
 686  *
 687  *   Revision 1.1  2004/04/27 09:12:28  nedko
 688  *   Initial revision.
 689  *
 690  *****************************************************************************/