modules/misc/sqlite.c

   1 /*****************************************************************************
   2  * sqlite.c: An SQLite3 wrapper for VLC
   3  *****************************************************************************
   4  * Copyright (C) 2008-2009 the VideoLAN team
   5  * $Id$
   6  *
   7  * Authors: Antoine Lejeune <phytos@videolan.org>
   8  *          Jean-Philippe André <jpeg@videolan.org>
   9  *          Rémi Duraffort <ivoire@videolan.org>
  10  *          Adrien Maglo <magsoft@videolan.org>
  11  *          Srikanth Raju <srikiraju@gmail.com>
  12  *
  13  * This program is free software; you can redistribute it and/or modify
  14  * it under the terms of the GNU General Public License as published by
  15  * the Free Software Foundation; either version 2 of the License, or
  16  * (at your option) any later version.
  17  *
  18  * This program is distributed in the hope that it will be useful,
  19  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  20  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21  * GNU General Public License for more details.
  22  *
  23  * You should have received a copy of the GNU General Public License
  24  * along with this program; if not, write to the Free Software
  25  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301, USA.
  26  *****************************************************************************/
  27
  28
  29 #ifdef HAVE_CONFIG_H
  30 # include "config.h"
  31 #endif
  32
  33 #include <vlc_common.h>
  34 #include <vlc_sql.h>
  35 #include <vlc_plugin.h>
  36
  37 #include <sqlite3.h>
  38 #include <assert.h>
  39
  40
  41 /*****************************************************************************
  42  * Private structures
  43  *****************************************************************************/
  44 struct sql_sys_t
  45 {
  46     sqlite3 *db;              /**< Database connection. */
  47     vlc_mutex_t lock;         /**< SQLite mutex. Threads are evil here. */
  48     vlc_mutex_t trans_lock;   /**< Mutex for running transactions */
  49 };
  50
  51 struct sql_stmt_t
  52 {
  53     sqlite3_stmt* p_sqlitestmt;
  54 };
  55
  56
  57 /*****************************************************************************
  58  * Headers
  59  *****************************************************************************/
  60 static int load ( vlc_object_t * );
  61 static void unload ( vlc_object_t * );
  62
  63 static int OpenDatabase( sql_t * );
  64 static int CloseDatabase (sql_t * );
  65 static int QueryCallback( sql_t * p_sql,
  66                           const char * query,
  67                           sql_query_callback_t callback,
  68                           void *arg ); // 1st argument to callback
  69 static int Query( sql_t * p_sql,
  70                   const char * query,
  71                   char *** result,
  72                   int * nrow,
  73                   int * ncol );
  74 static int GetTables( sql_t * p_sql,
  75                       char *** result );
  76 static void FreeResult( sql_t * p_sql,
  77                         char **pp_result );
  78 static char* VMSprintf( const char* psz_fmt,
  79                         va_list args );
  80 static int BeginTransaction( sql_t* p_sql );
  81 static int CommitTransaction( sql_t* p_sql );
  82 static void RollbackTransaction( sql_t* p_sql );
  83 static sql_stmt_t* PrepareStatement( sql_t* p_sql,
  84                                      const char* psz_fmt,
  85                                      int i_length );
  86 static int BindValues( sql_t* p_sql,
  87                        sql_stmt_t* p_stmt,
  88                        int i_pos,
  89                        unsigned int i_type,
  90                        const sql_value_t* p_value );
  91 static int StatementStep( sql_t* p_sql,
  92                           sql_stmt_t* p_stmt );
  93 static int StatementReset( sql_t* p_sql,
  94                            sql_stmt_t* p_stmt );
  95 static int StatementFinalize( sql_t* p_sql,
  96                               sql_stmt_t* p_stmt );
  97 static int GetColumnFromStatement( sql_t* p_sql,
  98                                    sql_stmt_t* p_stmt,
  99                                    int i_col,
 100                                    int type,
 101                                    sql_value_t *p_res );
 102 static int GetColumnTypeFromStatement( sql_t* p_sql,
 103                                        sql_stmt_t* p_stmt,
 104                                        int i_col,
 105                                        int* pi_type );
 106 static int GetColumnSize( sql_t* p_sql,
 107                           sql_stmt_t* p_stmt,
 108                           int i_col );
 109
 110 /*****************************************************************************
 111  * Module description
 112  *****************************************************************************/
 113 vlc_module_begin()
 114     set_shortname( "SQLite" )
 115     set_description( _("SQLite database module") )
 116     set_capability( "sql", 1 )
 117     set_callbacks( load, unload )
 118     set_category( CAT_ADVANCED )
 119 vlc_module_end()
 120
 121
 122 /**
 123  * @brief Load module
 124  * @param obj Parent object
 125  * @return VLC_SUCCESS or VLC_ENOMEM
 126  */
 127 static int load( vlc_object_t *p_this )
 128 {
 129     sql_t *p_sql = (sql_t *) p_this;
 130
 131     /* Initialize sys_t */
 132     p_sql->p_sys = calloc( 1, sizeof( *p_sql->p_sys ) );
 133     if( !p_sql->p_sys )
 134         return VLC_ENOMEM;
 135
 136     vlc_mutex_init( &p_sql->p_sys->lock );
 137     vlc_mutex_init( &p_sql->p_sys->trans_lock );
 138
 139     /* Open Database */
 140     if( OpenDatabase( p_sql ) == VLC_SUCCESS )
 141         msg_Dbg( p_sql, "sqlite module loaded" );
 142     else
 143     {
 144         free( p_sql->p_sys );
 145         vlc_mutex_destroy( &p_sql->p_sys->lock );
 146         vlc_mutex_destroy( &p_sql->p_sys->trans_lock );
 147         return VLC_EGENERIC;
 148     }
 149
 150     p_sql->pf_query_callback = QueryCallback;
 151     p_sql->pf_get_tables = GetTables;
 152     p_sql->pf_query = Query;
 153     p_sql->pf_free = FreeResult;
 154     p_sql->pf_vmprintf = VMSprintf;
 155     p_sql->pf_begin = BeginTransaction;
 156     p_sql->pf_commit = CommitTransaction;
 157     p_sql->pf_rollback = RollbackTransaction;
 158     p_sql->pf_prepare = PrepareStatement;
 159     p_sql->pf_bind = BindValues;
 160     p_sql->pf_run = StatementStep;
 161     p_sql->pf_reset = StatementReset;
 162     p_sql->pf_finalize = StatementFinalize;
 163     p_sql->pf_gettype = GetColumnTypeFromStatement;
 164     p_sql->pf_getcolumn = GetColumnFromStatement;
 165     p_sql->pf_getcolumnsize = GetColumnSize;
 166
 167     return VLC_SUCCESS;
 168 }
 169
 170 /**
 171  * @brief Unload module
 172  * @param obj This sql_t object
 173  * @return Nothing
 174  */
 175 static void unload( vlc_object_t *p_this )
 176 {
 177     sql_t *p_sql = (sql_t *)p_this;
 178
 179     CloseDatabase( p_sql );
 180     vlc_mutex_destroy( &p_sql->p_sys->lock );
 181     vlc_mutex_destroy( &p_sql->p_sys->trans_lock );
 182     free( p_sql->p_sys );
 183 }
 184
 185 /**
 186  * @brief Sqlite Busy handler
 187  * @param p_data sql_t object
 188  * @param i_times Number of times busy handler has been invoked
 189  */
 190 static int vlc_sqlite_busy_handler( void* p_data, int i_times )
 191 {
 192     if( i_times >= 10 )
 193     {
 194         msg_Warn( (sql_t*) p_data, "Wait limit exceeded in SQLITE_BUSY handler" );
 195         return 0;
 196     }
 197     msleep( 2000000 );
 198     return 1;
 199 }
 200
 201 /**
 202  * @brief Open current database
 203  * @param p_sql This sql_t object
 204  * @return VLC_SUCCESS or VLC_EGENERIC
 205  * @note p_sql->psz_host is required
 206  */
 207 static int OpenDatabase( sql_t *p_sql )
 208 {
 209     assert( p_sql->psz_host && *p_sql->psz_host );
 210
 211     if( sqlite3_threadsafe() == 0 )
 212     {
 213         msg_Err( p_sql, "Sqlite library on your system is not threadsafe" );
 214         return VLC_EGENERIC;
 215     }
 216     if( sqlite3_open( p_sql->psz_host, &p_sql->p_sys->db ) != SQLITE_OK )
 217     {
 218         msg_Err( p_sql, "Can't open database : %s", p_sql->psz_host );
 219         msg_Err( p_sql, "sqlite3 error: %d: %s",
 220                       sqlite3_errcode( p_sql->p_sys->db ),
 221                       sqlite3_errmsg( p_sql->p_sys->db ) );
 222         return VLC_EGENERIC;
 223     }
 224     if( sqlite3_busy_timeout( p_sql->p_sys->db, 30000 ) != SQLITE_OK )
 225     {
 226         msg_Err( p_sql, "sqlite3 error: %d: %s",
 227                       sqlite3_errcode( p_sql->p_sys->db ),
 228                       sqlite3_errmsg( p_sql->p_sys->db ) );
 229         return VLC_EGENERIC;
 230     }
 231     if( sqlite3_busy_handler( p_sql->p_sys->db, vlc_sqlite_busy_handler, p_sql )
 232             != SQLITE_OK )
 233     {
 234         msg_Err( p_sql, "sqlite3 error: %d: %s",
 235                       sqlite3_errcode( p_sql->p_sys->db ),
 236                       sqlite3_errmsg( p_sql->p_sys->db ) );
 237         return VLC_EGENERIC;
 238     }
 239
 240     return VLC_SUCCESS;
 241 }
 242
 243 /**
 244  * @brief Close current database
 245  * @param p_sql This sql_t object
 246  * @return VLC_SUCCESS
 247  * You have to set and open current database first
 248  */
 249 static int CloseDatabase( sql_t *p_sql )
 250 {
 251     assert( p_sql->p_sys->db );
 252
 253     /* Close all prepared statements */
 254     sqlite3_stmt* p_stmt;
 255     while( ( p_stmt = sqlite3_next_stmt( p_sql->p_sys->db, NULL ) ) != NULL )
 256     {
 257         if( sqlite3_finalize( p_stmt ) != SQLITE_OK )
 258         {
 259             msg_Warn( p_sql, "sqlite3 error: %d: %s",
 260                       sqlite3_errcode( p_sql->p_sys->db ),
 261                       sqlite3_errmsg( p_sql->p_sys->db ) );
 262         }
 263     }
 264
 265     /* Close database */
 266     /* TODO: We've closed all open prepared statements
 267      * Perhaps sqlite3_close can still fail? */
 268     sqlite3_close( p_sql->p_sys->db );
 269     p_sql->p_sys->db = NULL;
 270
 271     return VLC_SUCCESS;
 272 }
 273
 274 /**
 275  * @brief SQL Query with callback
 276  * @param p_sql This sql_t object
 277  * @param query SQL query
 278  * @param callback Callback function to receive results row by row
 279  * @param arg Argument to pass to callback
 280  * @return VLC_SUCCESS or an error code
 281  * You have to set and open current database first
 282  */
 283 static int QueryCallback( sql_t * p_sql,
 284                           const char * query,
 285                           sql_query_callback_t callback,
 286                           void *arg )
 287 {
 288     int i_ret = VLC_SUCCESS;
 289     vlc_mutex_lock( &p_sql->p_sys->lock );
 290     assert( p_sql->p_sys->db );
 291 #ifndef NDEBUG
 292     msg_Dbg( p_sql, "QueryCallback: %s", query );
 293 #endif
 294     sqlite3_exec( p_sql->p_sys->db, query, callback, arg, NULL );
 295     if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
 296     {
 297         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 298                   sqlite3_errcode( p_sql->p_sys->db ),
 299                   sqlite3_errmsg( p_sql->p_sys->db ) );
 300         i_ret = VLC_EGENERIC;
 301     }
 302
 303     vlc_mutex_unlock( &p_sql->p_sys->lock );
 304     return i_ret;
 305 }
 306
 307 /**
 308  * @brief Direct SQL Query
 309  * @param p_sql This sql_t object
 310  * @param query SQL query
 311  * @param result Return value : Array of results
 312  * @param nrow Return value : Row number
 313  * @param ncol Return value : Column number
 314  * @return VLC_SUCCESS or an error code
 315  * You have to set and open current database first
 316  * @todo Handle transaction closing due to errors in sql query
 317  */
 318 static int Query( sql_t * p_sql,
 319                   const char * query,
 320                   char *** result,
 321                   int * nrow,
 322                   int * ncol )
 323 {
 324     assert( p_sql->p_sys->db );
 325     int i_ret = VLC_SUCCESS;
 326     vlc_mutex_lock( &p_sql->p_sys->lock );
 327
 328 #ifndef NDEBUG
 329     msg_Dbg( p_sql, "Query: %s", query );
 330 #endif
 331     sqlite3_get_table( p_sql->p_sys->db, query, result, nrow, ncol, NULL );
 332     if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
 333     {
 334         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 335                   sqlite3_errcode( p_sql->p_sys->db ),
 336                   sqlite3_errmsg( p_sql->p_sys->db ) );
 337         i_ret = VLC_EGENERIC;
 338     }
 339
 340     vlc_mutex_unlock( &p_sql->p_sys->lock );
 341     return i_ret;
 342 }
 343
 344 /**
 345  * @brief Get tables in database
 346  * @param p_sql This sql_t object
 347  * @param result SQL query result
 348  * @return Number of elements
 349  * You have to set and open current database first
 350  */
 351 static int GetTables( sql_t * p_sql,
 352                       char *** result )
 353 {
 354     int nrow, i_num = -1;
 355
 356     vlc_mutex_lock( &p_sql->p_sys->lock );
 357
 358     assert( p_sql->p_sys->db );
 359
 360     sqlite3_get_table( p_sql->p_sys->db, "SELECT * FROM sqlite_master;",
 361                        result, &nrow, &i_num, NULL );
 362     if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
 363     {
 364         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 365                   sqlite3_errcode( p_sql->p_sys->db ),
 366                   sqlite3_errmsg( p_sql->p_sys->db ) );
 367     }
 368     vlc_mutex_unlock( &p_sql->p_sys->lock );
 369     return i_num;
 370 }
 371
 372 /**
 373  * @brief Free SQL request's result
 374  * @param p_sql This SQL object.
 375  * @param ppsz_result SQL result to free
 376  */
 377 static void FreeResult( sql_t * p_sql, char **ppsz_result )
 378 {
 379     VLC_UNUSED( p_sql );
 380     if( ppsz_result != NULL )
 381         sqlite3_free_table( ppsz_result );
 382 }
 383
 384 /**
 385  * @brief vmprintf replacement for SQLite.
 386  * @param psz_fmt Format string
 387  * @param args va_list of arguments
 388  * This function implements the formats %q, %Q and %z.
 389  */
 390 static char* VMSprintf( const char* psz_fmt, va_list args )
 391 {
 392     char *psz = sqlite3_vmprintf( psz_fmt, args );
 393     char *ret = strdup( psz );
 394     sqlite3_free( psz );
 395     return ret;
 396 }
 397
 398 /**
 399  * @brief Starts a Transaction and waits if necessary
 400  * @param p_sql The SQL object
 401  * @note This function locks the transactions on the database.
 402  * Within the period of the transaction, only the calling thread may
 403  * execute sql statements provided all threads use these transaction fns.
 404  */
 405 static int BeginTransaction( sql_t* p_sql )
 406 {
 407     int i_ret = VLC_SUCCESS;
 408     vlc_mutex_lock( &p_sql->p_sys->trans_lock );
 409     vlc_mutex_lock( &p_sql->p_sys->lock );
 410     assert( p_sql->p_sys->db );
 411
 412     sqlite3_exec( p_sql->p_sys->db, "BEGIN;", NULL, NULL, NULL );
 413 #ifndef NDEBUG
 414     msg_Dbg( p_sql, "Transaction Query: BEGIN;" );
 415 #endif
 416     if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
 417     {
 418         vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
 419         vlc_mutex_unlock( &p_sql->p_sys->lock );
 420         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 421                   sqlite3_errcode( p_sql->p_sys->db ),
 422                   sqlite3_errmsg( p_sql->p_sys->db ) );
 423         i_ret = VLC_EGENERIC;
 424     }
 425     vlc_mutex_unlock( &p_sql->p_sys->lock );
 426     return i_ret;
 427 }
 428
 429 /**
 430  * @brief Commit a transaction
 431  * @param p_sql The SQL object
 432  * @note This function unlocks the transactions on the database
 433  * Only the calling thread of "BeginTransaction" is allowed to call this method
 434  * If the commit fails, the transaction lock is still held by the thread
 435  * and this function may be retried or RollbackTransaction can be called
 436  * @return VLC_SUCCESS or VLC_EGENERIC
 437  */
 438 static int CommitTransaction( sql_t* p_sql )
 439 {
 440     int i_ret = VLC_SUCCESS;
 441     assert( p_sql->p_sys->db );
 442     vlc_mutex_lock( &p_sql->p_sys->lock );
 443
 444     /** This turns the auto commit on. */
 445     sqlite3_exec( p_sql->p_sys->db, "COMMIT;", NULL, NULL, NULL );
 446 #ifndef NDEBUG
 447     msg_Dbg( p_sql, "Transaction Query: COMMIT;" );
 448 #endif
 449     if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
 450     {
 451         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 452                   sqlite3_errcode( p_sql->p_sys->db ),
 453                   sqlite3_errmsg( p_sql->p_sys->db ) );
 454         i_ret = VLC_EGENERIC;
 455     }
 456     else
 457         vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
 458     vlc_mutex_unlock( &p_sql->p_sys->lock );
 459     return i_ret;
 460 }
 461
 462 /**
 463  * @brief Rollback a transaction, in case of failure
 464  * @param p_sql The SQL object
 465  * @return VLC_SUCCESS or VLC_EGENERIC
 466  * @note This function unlocks the transactions on the database
 467  * Only the calling thread of "BeginTransaction" is allowed to call this method
 468  * If failed, if a statement in the transaction failed, it means that
 469  * the transaction was automatically rolled back
 470  * If failed otherwise, the engine is busy executing some queries and you must
 471  * try again
 472  */
 473 static void RollbackTransaction( sql_t* p_sql )
 474 {
 475     assert( p_sql->p_sys->db );
 476     vlc_mutex_lock( &p_sql->p_sys->lock );
 477
 478     sqlite3_exec( p_sql->p_sys->db, "ROLLBACK;", NULL, NULL, NULL );
 479 #ifndef NDEBUG
 480     msg_Dbg( p_sql, "Transaction Query: ROLLBACK;" );
 481 #endif
 482     if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
 483     {
 484         msg_Err( p_sql, "sqlite3 error: %d: %s",
 485                   sqlite3_errcode( p_sql->p_sys->db ),
 486                   sqlite3_errmsg( p_sql->p_sys->db ) );
 487     }
 488     vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
 489     vlc_mutex_unlock( &p_sql->p_sys->lock );
 490 }
 491
 492 /**
 493  * Prepare an sqlite statement
 494  * @return statement object or NULL in case of failure
 495  */
 496 static sql_stmt_t* PrepareStatement( sql_t* p_sql, const char* psz_fmt, int i_length )
 497 {
 498     assert( p_sql->p_sys->db );
 499     sql_stmt_t* p_stmt;
 500     p_stmt = calloc( 1, sizeof( *p_stmt ) );
 501     if( p_stmt == NULL )
 502         return NULL;
 503     vlc_mutex_lock( &p_sql->p_sys->lock );
 504     if( sqlite3_prepare_v2( p_sql->p_sys->db, psz_fmt, i_length,
 505                             &p_stmt->p_sqlitestmt, NULL ) != SQLITE_OK )
 506     {
 507         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 508                   sqlite3_errcode( p_sql->p_sys->db ),
 509                   sqlite3_errmsg( p_sql->p_sys->db ) );
 510         vlc_mutex_unlock( &p_sql->p_sys->lock );
 511         free( p_stmt );
 512         return NULL;
 513     }
 514
 515     vlc_mutex_unlock( &p_sql->p_sys->lock );
 516     return p_stmt;
 517 }
 518
 519 /**
 520  * @brief Bind arguments to a sql_stmt_t object
 521  * @param p_sql The SQL object
 522  * @param p_stmt Statement Object
 523  * @param i_pos Position at which the parameter should be bound
 524  * @param i_type Data type of the value
 525  * @param p_value Value to be bound
 526  * @return VLC_SUCCESS or VLC_EGENERIC
 527  */
 528 static int BindValues( sql_t* p_sql, sql_stmt_t* p_stmt,
 529         int i_pos, unsigned int i_type, const sql_value_t* p_value )
 530 {
 531     assert( p_sql->p_sys->db );
 532     assert( p_stmt->p_sqlitestmt );
 533     vlc_mutex_lock( &p_sql->p_sys->lock );
 534     int i_ret, i_vlc_ret = VLC_SUCCESS;
 535     switch( i_type )
 536     {
 537         case SQL_INT:
 538             i_ret = sqlite3_bind_int( p_stmt->p_sqlitestmt, i_pos, p_value->value.i );
 539             break;
 540         case SQL_DOUBLE:
 541             i_ret = sqlite3_bind_double( p_stmt->p_sqlitestmt, i_pos, p_value->value.dbl );
 542             break;
 543         case SQL_TEXT:
 544             i_ret = sqlite3_bind_text( p_stmt->p_sqlitestmt, i_pos, p_value->value.psz, p_value->length, NULL );
 545             break;
 546         case SQL_BLOB:
 547             i_ret = sqlite3_bind_blob( p_stmt->p_sqlitestmt, i_pos, p_value->value.ptr, p_value->length, NULL );
 548             break;
 549         case SQL_NULL:
 550             i_ret = sqlite3_bind_null( p_stmt->p_sqlitestmt, i_pos );
 551             break;
 552         default:
 553             msg_Warn( p_sql, "Trying to bind invalid type of value %d", i_type );
 554             vlc_mutex_unlock( &p_sql->p_sys->lock );
 555             return VLC_EGENERIC;
 556     }
 557     if( i_ret != SQLITE_OK )
 558     {
 559         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 560                   sqlite3_errcode( p_sql->p_sys->db ),
 561                   sqlite3_errmsg( p_sql->p_sys->db ) );
 562         i_vlc_ret = VLC_EGENERIC;
 563     }
 564     vlc_mutex_unlock( &p_sql->p_sys->lock );
 565     return i_vlc_ret;
 566 }
 567
 568 /**
 569  * @brief Run the SQL statement. If the statement fetches data, then only
 570  * one row of the data is fetched at a time. Run this function again to
 571  * fetch the next row.
 572  * @param p_sql The SQL object
 573  * @param p_stmt The statement
 574  * @return VLC_SQL_DONE if done fetching all rows or there are no rows to fetch
 575  * VLC_SQL_ROW if a row was fetched for this statement.
 576  * VLC_EGENERIC if this function failed
 577  */
 578 static int StatementStep( sql_t* p_sql, sql_stmt_t* p_stmt )
 579 {
 580     assert( p_sql->p_sys->db );
 581     assert( p_stmt->p_sqlitestmt );
 582     vlc_mutex_lock( &p_sql->p_sys->lock );
 583     int i_sqlret = sqlite3_step( p_stmt->p_sqlitestmt );
 584     int i_ret = VLC_EGENERIC;
 585     if( i_sqlret == SQLITE_ROW )
 586         i_ret = VLC_SQL_ROW;
 587     else if( i_ret == SQLITE_DONE )
 588         i_ret = VLC_SQL_DONE;
 589     else
 590     {
 591        msg_Warn( p_sql, "sqlite3 error: %d: %s",
 592                   sqlite3_errcode( p_sql->p_sys->db ),
 593                   sqlite3_errmsg( p_sql->p_sys->db ) );
 594        i_ret = VLC_EGENERIC;
 595     }
 596     vlc_mutex_unlock( &p_sql->p_sys->lock );
 597     return i_ret;
 598 }
 599
 600 /**
 601  * @brief Reset the SQL statement. Resetting the statement will unbind all
 602  * the values that were bound on this statement
 603  * @param p_sql The SQL object
 604  * @param p_stmt The sql statement object
 605  * @return VLC_SUCCESS or VLC_EGENERIC
 606  */
 607 static int StatementReset( sql_t* p_sql, sql_stmt_t* p_stmt )
 608 {
 609     assert( p_sql->p_sys->db );
 610     assert( p_stmt->p_sqlitestmt );
 611     int i_ret = VLC_SUCCESS;
 612     vlc_mutex_lock( &p_sql->p_sys->lock );
 613     if( sqlite3_reset( p_stmt->p_sqlitestmt ) != SQLITE_OK )
 614     {
 615         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 616                   sqlite3_errcode( p_sql->p_sys->db ),
 617                   sqlite3_errmsg( p_sql->p_sys->db ) );
 618         i_ret = VLC_EGENERIC;
 619     }
 620     vlc_mutex_unlock( &p_sql->p_sys->lock );
 621     return i_ret;
 622 }
 623
 624 /**
 625  * @brief Destroy the sql statement object. This will free memory.
 626  * @param p_sql The SQL object
 627  * @param p_stmt The statement object
 628  * @return VLC_SUCCESS or VLC_EGENERIC
 629  */
 630 static int StatementFinalize( sql_t* p_sql, sql_stmt_t* p_stmt )
 631 {
 632     assert( p_sql->p_sys->db );
 633     assert( p_stmt->p_sqlitestmt );
 634     int i_ret = VLC_SUCCESS;
 635     vlc_mutex_lock( &p_sql->p_sys->lock );
 636     if( sqlite3_finalize( p_stmt->p_sqlitestmt ) != SQLITE_OK )
 637     {
 638         msg_Warn( p_sql, "sqlite3 error: %d: %s",
 639                   sqlite3_errcode( p_sql->p_sys->db ),
 640                   sqlite3_errmsg( p_sql->p_sys->db ) );
 641         i_ret = VLC_EGENERIC;
 642     }
 643     free( p_stmt );
 644     vlc_mutex_unlock( &p_sql->p_sys->lock );
 645     return i_ret;
 646 }
 647
 648 /**
 649  * @brief Get the column data
 650  * @param p_sql The SQL object
 651  * @param p_stmt The statement object
 652  * @param i_col The column number
 653  * @param type Datatype of result
 654  * @param p_res The structure which contains the value of the result
 655  * @return VLC_SUCCESS or VLC_EGENERIC
 656  */
 657 static int GetColumnFromStatement( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
 658                           int type, sql_value_t *p_res )
 659 {
 660     assert( p_sql->p_sys->db );
 661     assert( p_stmt->p_sqlitestmt );
 662     int i_ret = VLC_SUCCESS;
 663     vlc_mutex_lock( &p_sql->p_sys->lock );
 664     const unsigned char* psz;
 665     const void* ptr;
 666     int size;
 667     switch( type )
 668     {
 669         case SQL_INT:
 670             p_res->value.i = sqlite3_column_int( p_stmt->p_sqlitestmt, i_col );
 671             break;
 672         case SQL_DOUBLE:
 673             p_res->value.dbl = sqlite3_column_double( p_stmt->p_sqlitestmt, i_col );
 674             break;
 675         case SQL_TEXT:
 676             psz = sqlite3_column_text( p_stmt->p_sqlitestmt, i_col );
 677             if( psz )
 678                 p_res->value.psz = strdup( (const char* ) psz );
 679             break;
 680         case SQL_BLOB:
 681             ptr = sqlite3_column_blob( p_stmt->p_sqlitestmt, i_col );
 682             size = sqlite3_column_bytes( p_stmt->p_sqlitestmt, i_col );
 683             if( ptr )
 684             {
 685                 p_res->value.ptr = malloc( size );
 686                 p_res->length = size;
 687                 if( p_res->value.ptr )
 688                     memcpy( p_res->value.ptr, ptr, size );
 689                 else
 690                     i_ret = VLC_ENOMEM;
 691             }
 692             break;
 693         case SQL_NULL:
 694         default:
 695             msg_Warn( p_sql, "Trying to bind invalid type of value %d", type );
 696             i_ret = VLC_EGENERIC;
 697     }
 698     vlc_mutex_unlock( &p_sql->p_sys->lock );
 699     return i_ret;
 700 }
 701
 702 /**
 703  * @brief Get the datatype of the result of the column
 704  * @param p_sql The SQL object
 705  * @param p_stmt The sql statement object
 706  * @param i_col The column
 707  * @param pi_type pointer to datatype of the given column
 708  * @return VLC_SUCCESS or VLC_EGENERIC
 709  */
 710 static int GetColumnTypeFromStatement( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
 711                         int* pi_type )
 712 {
 713     assert( p_sql->p_sys->db );
 714     assert( p_stmt->p_sqlitestmt );
 715     assert( pi_type );
 716     vlc_mutex_lock( &p_sql->p_sys->lock );
 717     int i_ret = VLC_SUCCESS;
 718     int i_sqlret = sqlite3_column_type( p_stmt->p_sqlitestmt, i_col );
 719     switch( i_sqlret )
 720     {
 721         case SQLITE_INTEGER:
 722             *pi_type = SQL_INT;
 723             break;
 724         case SQLITE_FLOAT:
 725             *pi_type= SQL_DOUBLE;
 726             break;
 727         case SQLITE_TEXT:
 728             *pi_type = SQL_TEXT;
 729             break;
 730         case SQLITE_BLOB:
 731             *pi_type = SQL_BLOB;
 732             break;
 733         case SQLITE_NULL:
 734             *pi_type = SQL_NULL;
 735             break;
 736         default:
 737             i_ret = VLC_EGENERIC;
 738     }
 739     vlc_mutex_unlock( &p_sql->p_sys->lock );
 740     return i_ret;
 741 }
 742
 743 /**
 744  * @brief Get the size of the column in bytes
 745  * @param p_sql The SQL object
 746  * @param p_stmt The sql statement object
 747  * @param i_col The column
 748  * @return Size of the column in bytes, undefined for invalid columns
 749  */
 750 static int GetColumnSize( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col )
 751 {
 752     assert( p_sql->p_sys->db );
 753     assert( p_stmt->p_sqlitestmt );
 754     return sqlite3_column_bytes( p_stmt->p_sqlitestmt, i_col );
 755 }