libdb/mysql.c

   1 /* -*- Mode: C ; c-basic-offset: 2 -*- */
   2 /*****************************************************************************
   3  *
   4  * $Id: mysql.c,v 1.3 2004/05/02 20:12: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
  22 #include "internal.h"
  23 #include "memory.h"
  24
  25 #define MEDIADB_MYSQL_MEDIA_TABLE "media"
  26 #define MEDIADB_MYSQL_FILES_TABLE "files"
  27
  28 //#define DUMMY_SQL_QUERIES
  29 #define FORMATTING_BUFFER_SIZE 10240
  30
  31 static
  32 void
  33 set_error_message(
  34   mediadb_mysql *pDB,
  35   const char *pszFormat,
  36   ...)
  37 {
  38   mediadb_result r;
  39   size_t s;
  40   va_list argList;
  41   char Buffer[FORMATTING_BUFFER_SIZE];
  42
  43   va_start(argList, pszFormat);
  44   vsnprintf(Buffer, FORMATTING_BUFFER_SIZE-1, pszFormat, argList);
  45   va_end(argList);
  46
  47   s = strlen(Buffer);
  48
  49   r = maybe_enlarge_buffer(
  50     &pDB->pErrorMsgBuffer,
  51     &pDB->sizeErrorMsgBuffer,
  52     s+1);
  53   if (MEDIADB_IS_ERROR(r))
  54   {
  55     if (pDB->sizeErrorMsgBuffer > 0)
  56     {
  57       pDB->pErrorMsgBuffer[0] = 0;
  58     }
  59   }
  60
  61   memcpy(pDB->pErrorMsgBuffer, Buffer, s + 1);
  62 }
  63
  64 static
  65 void
  66 reset_error_message(
  67   mediadb_mysql *pDB)
  68 {
  69   if (pDB->pErrorMsgBuffer != NULL)
  70   {
  71     pDB->pErrorMsgBuffer[0] = 0;
  72   }
  73 }
  74
  75 const char *
  76 mediadb_mysql_get_error_message(
  77   mediadb_mysql *pDB)
  78 {
  79   if (pDB->pErrorMsgBuffer == NULL)
  80   {
  81     return "";
  82   }
  83
  84   return pDB->pErrorMsgBuffer;
  85 }
  86
  87 mediadb_result
  88 mediadb_mysql_open(
  89   mediadb_mysql *pDB,
  90   const char *pszHost,
  91   const char *pszUser,
  92   const char *pszPass,
  93   const char *pszDB)
  94 {
  95   MYSQL *pMYSQL;
  96
  97   pMYSQL = mysql_init(NULL);
  98   if (pMYSQL == NULL)
  99   {
 100     set_error_message(
 101       pDB,
 102       "Cannot create MYSQL object. Error is \"%s\"",
 103       mysql_error(pMYSQL));
 104     return MEDIADB_FAIL;
 105   }
 106
 107   if (mysql_real_connect(
 108         pMYSQL,
 109         pszHost,
 110         pszUser,
 111         pszPass,
 112         pszDB,
 113         0,
 114         NULL,
 115         0) == NULL)
 116   {
 117     set_error_message(
 118       pDB,
 119       "Failed to connect to database. Error: %s",
 120       mysql_error(pMYSQL));
 121     mysql_close(pMYSQL);
 122     return MEDIADB_FAIL;
 123   }
 124
 125   pDB->pMYSQL = pMYSQL;
 126   pDB->pszSQLQueryBuffer = NULL;
 127   pDB->sizeSQLQueryBuffer = 0;
 128   pDB->pErrorMsgBuffer = NULL;
 129   pDB->sizeErrorMsgBuffer = 0;
 130
 131   reset_error_message(pDB);
 132
 133   return MEDIADB_OK;
 134 }
 135
 136 mediadb_result
 137 mediadb_mysql_media_add_new(
 138   mediadb_mysql *pDB,
 139   const char *pszName,
 140   const char *pszComment,
 141   mediadb_mediatype nType,
 142   mediadb_uint *pnNewMediaID)
 143 {
 144   mediadb_result r;
 145   size_t sizeName, sizeComment, sizePart;
 146   char *psz, *pszPart;
 147   my_ulonglong nMediaID;
 148
 149   /* Ensure we have large enough buffer */
 150
 151   sizeName = strlen(pszName);
 152   sizeComment = strlen(pszComment);
 153
 154   r = maybe_enlarge_buffer(
 155     &pDB->pszSQLQueryBuffer,
 156     &pDB->sizeSQLQueryBuffer,
 157     sizeName*2 + sizeComment*2 + 1024);
 158   if (MEDIADB_IS_ERROR(r))
 159   {
 160     set_error_message(
 161       pDB,
 162       "Cannot enlarge buffer");
 163     return r;
 164   }
 165
 166   /* Compose SQL query string */
 167
 168   psz = pDB->pszSQLQueryBuffer;
 169
 170   pszPart = "INSERT INTO " MEDIADB_MYSQL_MEDIA_TABLE " (name,type,comment) VALUES ('";
 171   sizePart = strlen(pszPart);
 172   memcpy(psz, pszPart, sizePart);
 173   psz += sizePart;
 174
 175   sizePart = mysql_real_escape_string(
 176     pDB->pMYSQL,
 177     psz,
 178     pszName,
 179     sizeName);
 180   psz += sizePart;
 181
 182   sizePart = sprintf(psz, "',%u,'", (unsigned int)nType);
 183   psz += sizePart;
 184
 185   sizePart = mysql_real_escape_string(
 186     pDB->pMYSQL,
 187     psz,
 188     pszComment,
 189     sizeComment);
 190   psz += sizePart;
 191
 192   pszPart = "')";
 193   sizePart = strlen(pszPart);
 194   memcpy(psz, pszPart, sizePart);
 195   psz += sizePart;
 196
 197   *psz = 0;
 198
 199   /* Execute SQL query */
 200
 201 #ifdef DUMMY_SQL_QUERIES
 202   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 203   nMediaID = 123;
 204 #else
 205   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 206   {
 207     set_error_message(
 208       pDB,
 209       "Failed to execute query \"%s\" Error: %s",
 210       pDB->pszSQLQueryBuffer,
 211       mysql_error(pDB->pMYSQL));
 212     return MEDIADB_FAIL;
 213   }
 214
 215   nMediaID = mysql_insert_id(pDB->pMYSQL);
 216   if (nMediaID == 0ULL)
 217   {
 218     set_error_message(
 219       pDB,
 220       "Invalid media ID 0 after media INSERT");
 221     return MEDIADB_FAIL;
 222   }
 223 #endif
 224
 225   *pnNewMediaID = nMediaID;
 226
 227   reset_error_message(pDB);
 228
 229   return MEDIADB_OK;
 230 }
 231
 232 mediadb_result
 233 mediadb_mysql_media_update_properties(
 234   mediadb_mysql *pDB,
 235   mediadb_uint nMediaID,
 236   mediadb_uint nTimeAdded,
 237   mediadb_uint nTotalFiles,
 238   mediadb_uint nTotalSize)
 239 {
 240   mediadb_result r;
 241
 242   /* Ensure we have large enough buffer */
 243
 244   r = maybe_enlarge_buffer(
 245     &pDB->pszSQLQueryBuffer,
 246     &pDB->sizeSQLQueryBuffer,
 247     1024);
 248   if (MEDIADB_IS_ERROR(r))
 249   {
 250     set_error_message(
 251       pDB,
 252       "Cannot enlarge buffer");
 253     return r;
 254   }
 255
 256   /* Compose SQL query string */
 257
 258   sprintf(
 259     pDB->pszSQLQueryBuffer,
 260     "UPDATE " MEDIADB_MYSQL_MEDIA_TABLE " SET added=%u, info1=%u, info2=%u WHERE mediaid = %u",
 261     (unsigned int)nTimeAdded,
 262     (unsigned int)nTotalFiles,
 263     (unsigned int)nTotalSize,
 264     (unsigned int)nMediaID);
 265
 266   /* Execute SQL query */
 267
 268 #ifdef DUMMY_SQL_QUERIES
 269   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 270 #else
 271   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 272   {
 273     set_error_message(
 274       pDB,
 275       "Failed to execute query \"%s\" Error: %s",
 276       pDB->pszSQLQueryBuffer,
 277       mysql_error(pDB->pMYSQL));
 278     return MEDIADB_FAIL;
 279   }
 280 #endif
 281
 282   reset_error_message(pDB);
 283
 284   return MEDIADB_OK;
 285 }
 286
 287 mediadb_result
 288 mediadb_mysql_file_add_new(
 289   mediadb_mysql *pDB,
 290   mediadb_uint nMediaID,
 291   mediadb_filetype Filetype,
 292   const char *pszPath,
 293   const char *pszName,
 294   mediadb_uint nFileSize,
 295   mediadb_uint nFileTime)
 296 {
 297   mediadb_result r;
 298   size_t sizePath, sizeName, sizePart;
 299   char *psz, *pszPart;
 300
 301   /* Ensure we have large enough buffer */
 302
 303   sizePath = strlen(pszPath);
 304   sizeName = strlen(pszName);
 305
 306   r = maybe_enlarge_buffer(
 307     &pDB->pszSQLQueryBuffer,
 308     &pDB->sizeSQLQueryBuffer,
 309     sizePath*2 + sizeName*2 + 1024);
 310   if (MEDIADB_IS_ERROR(r))
 311   {
 312     set_error_message(
 313       pDB,
 314       "Cannot enlarge buffer");
 315     return r;
 316   }
 317
 318   /* Compose SQL query string */
 319
 320   psz = pDB->pszSQLQueryBuffer;
 321
 322   sizePart = sprintf(
 323     psz,
 324     "INSERT INTO " MEDIADB_MYSQL_FILES_TABLE " (mediaid,size,time,path,name) VALUES (%u,%u,%u,'",
 325     (unsigned int)nMediaID,
 326     (unsigned int)nFileSize,
 327     (unsigned int)nFileTime);
 328   psz += sizePart;
 329
 330   sizePart = mysql_real_escape_string(
 331     pDB->pMYSQL,
 332     psz,
 333     pszPath,
 334     sizePath);
 335   psz += sizePart;
 336
 337   pszPart = "','";
 338   sizePart = strlen(pszPart);
 339   memcpy(psz, pszPart, sizePart);
 340   psz += sizePart;
 341
 342   sizePart = mysql_real_escape_string(
 343     pDB->pMYSQL,
 344     psz,
 345     pszName,
 346     sizeName);
 347   psz += sizePart;
 348
 349   if (Filetype == MEDIADB_FILETYPE_DIR)
 350   {
 351     pszPart = "/";
 352     sizePart = strlen(pszPart);
 353     memcpy(psz, pszPart, sizePart);
 354     psz += sizePart;
 355   }
 356   else if (Filetype != MEDIADB_FILETYPE_FILE)
 357   {
 358     set_error_message(
 359       pDB,
 360       "Cannot add file of unknown type");
 361     return MEDIADB_INVAL_ARG;
 362   }
 363
 364   pszPart = "')";
 365   sizePart = strlen(pszPart);
 366   memcpy(psz, pszPart, sizePart);
 367   psz += sizePart;
 368
 369   *psz = 0;
 370
 371   /* Execute SQL query */
 372
 373 #ifdef DUMMY_SQL_QUERIES
 374   printf("MySQL query \"%s\"\n", pDB->pszSQLQueryBuffer);
 375 #else
 376   if (mysql_query(pDB->pMYSQL, pDB->pszSQLQueryBuffer) != 0)
 377   {
 378     set_error_message(
 379       pDB,
 380       "Failed to execute query \"%s\" Error: %s",
 381       pDB->pszSQLQueryBuffer,
 382       mysql_error(pDB->pMYSQL));
 383     return MEDIADB_FAIL;
 384   }
 385 #endif
 386
 387   reset_error_message(pDB);
 388
 389   return MEDIADB_OK;
 390 }
 391
 392 mediadb_result
 393 mediadb_mysql_close(
 394   mediadb_mysql *pDB)
 395 {
 396   if (pDB->pszSQLQueryBuffer != NULL)
 397   {
 398     free(pDB->pszSQLQueryBuffer);
 399   }
 400
 401   mysql_close(pDB->pMYSQL);
 402
 403   return MEDIADB_OK;
 404 }
 405
 406 /*****************************************************************************
 407  *
 408  * Modifications log:
 409  *
 410  * !!! WARNING !!! Following lines are automatically updated by the CVS system.
 411  *
 412  *   $Log: mysql.c,v $
 413  *   Revision 1.3  2004/05/02 20:12:11  nedko
 414  *   Improve error dumps.
 415  *
 416  *   Revision 1.2  2004/05/02 13:18:36  nedko
 417  *   Supply filetype when adding new file.
 418  *   Initialize mediaid when in dummy sql queries mode.
 419  *
 420  *   Revision 1.1  2004/04/27 09:12:28  nedko
 421  *   Initial revision.
 422  *
 423  *****************************************************************************/