1 /*****************************************************************************
2 * sqlite.c: An SQLite3 wrapper for VLC
3 *****************************************************************************
4 * Copyright (C) 2008-2009 the VideoLAN team
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>
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.
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.
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 *****************************************************************************/
33 #include <vlc_common.h>
36 #include <vlc_plugin.h>
42 /*****************************************************************************
44 *****************************************************************************/
47 sqlite3 *db; /**< Database connection. */
48 vlc_mutex_t lock; /**< SQLite mutex. Threads are evil here. */
49 vlc_mutex_t trans_lock; /**< Mutex for running transactions */
54 sqlite3_stmt* p_sqlitestmt;
58 /*****************************************************************************
60 *****************************************************************************/
61 static int load ( vlc_object_t * );
62 static void unload ( vlc_object_t * );
64 static int OpenDatabase( sql_t * );
65 static int CloseDatabase (sql_t * );
66 static int QueryCallback( sql_t * p_sql,
68 sql_query_callback_t callback,
69 void *arg ); // 1st argument to callback
70 static int Query( sql_t * p_sql,
75 static int GetTables( sql_t * p_sql,
77 static void FreeResult( sql_t * p_sql,
79 static char* VMSprintf( const char* psz_fmt,
81 static int BeginTransaction( sql_t* p_sql );
82 static int CommitTransaction( sql_t* p_sql );
83 static void RollbackTransaction( sql_t* p_sql );
84 static sql_stmt_t* PrepareStatement( sql_t* p_sql,
87 static int BindValues( sql_t* p_sql,
91 const sql_value_t* p_value );
92 static int StatementStep( sql_t* p_sql,
94 static int StatementReset( sql_t* p_sql,
96 static int StatementFinalize( sql_t* p_sql,
98 static int GetColumnFromStatement( sql_t* p_sql,
102 sql_value_t *p_res );
103 static int GetColumnTypeFromStatement( sql_t* p_sql,
108 /*****************************************************************************
110 *****************************************************************************/
112 set_shortname( "SQLite" )
113 set_description( _("SQLite database module") )
114 set_capability( "sql", 1 )
115 set_callbacks( load, unload )
116 set_category( CAT_ADVANCED )
122 * @param obj Parent object
123 * @return VLC_SUCCESS or VLC_ENOMEM
125 static int load( vlc_object_t *p_this )
127 sql_t *p_sql = (sql_t *) p_this;
129 /* Initialize sys_t */
130 p_sql->p_sys = calloc( 1, sizeof( *p_sql->p_sys ) );
134 vlc_mutex_init( &p_sql->p_sys->lock );
135 vlc_mutex_init( &p_sql->p_sys->trans_lock );
138 if( OpenDatabase( p_sql ) == VLC_SUCCESS )
139 msg_Dbg( p_sql, "sqlite module loaded" );
142 free( p_sql->p_sys );
143 vlc_mutex_destroy( &p_sql->p_sys->lock );
144 vlc_mutex_destroy( &p_sql->p_sys->trans_lock );
148 p_sql->pf_query_callback = QueryCallback;
149 p_sql->pf_get_tables = GetTables;
150 p_sql->pf_query = Query;
151 p_sql->pf_free = FreeResult;
152 p_sql->pf_vmprintf = VMSprintf;
153 p_sql->pf_begin = BeginTransaction;
154 p_sql->pf_commit = CommitTransaction;
155 p_sql->pf_rollback = RollbackTransaction;
156 p_sql->pf_prepare = PrepareStatement;
157 p_sql->pf_bind = BindValues;
158 p_sql->pf_run = StatementStep;
159 p_sql->pf_reset = StatementReset;
160 p_sql->pf_finalize = StatementFinalize;
161 p_sql->pf_gettype = GetColumnTypeFromStatement;
162 p_sql->pf_getcolumn = GetColumnFromStatement;
168 * @brief Unload module
169 * @param obj This sql_t object
172 static void unload( vlc_object_t *p_this )
174 sql_t *p_sql = (sql_t *)p_this;
176 CloseDatabase( p_sql );
177 vlc_mutex_destroy( &p_sql->p_sys->lock );
178 vlc_mutex_destroy( &p_sql->p_sys->trans_lock );
179 free( p_sql->p_sys );
183 * @brief Sqlite Busy handler
184 * @param p_data sql_t object
185 * @param i_times Number of times busy handler has been invoked
187 static int vlc_sqlite_busy_handler( void* p_data, int i_times )
191 msg_Warn( (sql_t*) p_data, "Wait limit exceeded in SQLITE_BUSY handler" );
199 * @brief Open current database
200 * @param p_sql This sql_t object
201 * @return VLC_SUCCESS or VLC_EGENERIC
202 * @note p_sql->psz_host is required
204 static int OpenDatabase( sql_t *p_sql )
206 assert( p_sql->psz_host && *p_sql->psz_host );
208 if( sqlite3_threadsafe() != 0 )
210 msg_Err( p_sql, "Sqlite library on your system is not threadsafe" );
213 if( sqlite3_open( p_sql->psz_host, &p_sql->p_sys->db ) != SQLITE_OK )
215 msg_Err( p_sql, "Can't open database : %s", p_sql->psz_host );
216 msg_Err( p_sql, "sqlite3 error: %d: %s",
217 sqlite3_errcode( p_sql->p_sys->db ),
218 sqlite3_errmsg( p_sql->p_sys->db ) );
221 if( sqlite3_busy_timeout( p_sql->p_sys->db, 30000 ) != SQLITE_OK )
223 msg_Err( p_sql, "sqlite3 error: %d: %s",
224 sqlite3_errcode( p_sql->p_sys->db ),
225 sqlite3_errmsg( p_sql->p_sys->db ) );
228 if( sqlite3_busy_handler( p_sql->p_sys->db, vlc_sqlite_busy_handler, p_sql )
231 msg_Err( p_sql, "sqlite3 error: %d: %s",
232 sqlite3_errcode( p_sql->p_sys->db ),
233 sqlite3_errmsg( p_sql->p_sys->db ) );
241 * @brief Close current database
242 * @param p_sql This sql_t object
243 * @return VLC_SUCCESS
244 * You have to set and open current database first
246 static int CloseDatabase( sql_t *p_sql )
248 assert( p_sql->p_sys->db );
250 /* Close all prepared statements */
251 sqlite3_stmt* p_stmt;
252 while( ( p_stmt = sqlite3_next_stmt( p_sql->p_sys->db, NULL ) ) != NULL )
254 if( sqlite3_finalize( p_stmt ) != SQLITE_OK )
256 msg_Warn( p_sql, "sqlite3 error: %d: %s",
257 sqlite3_errcode( p_sql->p_sys->db ),
258 sqlite3_errmsg( p_sql->p_sys->db ) );
263 /* TODO: We've closed all open prepared statements
264 * Perhaps sqlite3_close can still fail? */
265 sqlite3_close( p_sql->p_sys->db );
266 p_sql->p_sys->db = NULL;
272 * @brief SQL Query with callback
273 * @param p_sql This sql_t object
274 * @param query SQL query
275 * @param callback Callback function to receive results row by row
276 * @param arg Argument to pass to callback
277 * @return VLC_SUCCESS or an error code
278 * You have to set and open current database first
280 static int QueryCallback( sql_t * p_sql,
282 sql_query_callback_t callback,
285 int i_ret = VLC_SUCCESS;
286 vlc_mutex_lock( &p_sql->p_sys->lock );
287 assert( p_sql->p_sys->db );
289 msg_Dbg( p_sql, "QueryCallback: %s", query );
291 sqlite3_exec( p_sql->p_sys->db, query, callback, arg, NULL );
292 if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
294 msg_Warn( p_sql, "sqlite3 error: %d: %s",
295 sqlite3_errcode( p_sql->p_sys->db ),
296 sqlite3_errmsg( p_sql->p_sys->db ) );
297 i_ret = VLC_EGENERIC;
300 vlc_mutex_unlock( &p_sql->p_sys->lock );
305 * @brief Direct SQL Query
306 * @param p_sql This sql_t object
307 * @param query SQL query
308 * @param result Return value : Array of results
309 * @param nrow Return value : Row number
310 * @param ncol Return value : Column number
311 * @return VLC_SUCCESS or an error code
312 * You have to set and open current database first
313 * @todo Handle transaction closing due to errors in sql query
315 static int Query( sql_t * p_sql,
321 assert( p_sql->p_sys->db );
322 int i_ret = VLC_SUCCESS;
323 vlc_mutex_lock( &p_sql->p_sys->lock );
326 msg_Dbg( p_sql, "Query: %s", query );
328 sqlite3_get_table( p_sql->p_sys->db, query, result, nrow, ncol, NULL );
329 if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
331 msg_Warn( p_sql, "sqlite3 error: %d: %s",
332 sqlite3_errcode( p_sql->p_sys->db ),
333 sqlite3_errmsg( p_sql->p_sys->db ) );
334 i_ret = VLC_EGENERIC;
337 vlc_mutex_unlock( &p_sql->p_sys->lock );
342 * @brief Get tables in database
343 * @param p_sql This sql_t object
344 * @param result SQL query result
345 * @return Number of elements
346 * You have to set and open current database first
348 static int GetTables( sql_t * p_sql,
351 int nrow, i_num = -1;
353 vlc_mutex_lock( &p_sql->p_sys->lock );
355 assert( p_sql->p_sys->db );
357 sqlite3_get_table( p_sql->p_sys->db, "SELECT * FROM sqlite_master;",
358 result, &nrow, &i_num, NULL );
359 if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
361 msg_Warn( p_sql, "sqlite3 error: %d: %s",
362 sqlite3_errcode( p_sql->p_sys->db ),
363 sqlite3_errmsg( p_sql->p_sys->db ) );
365 vlc_mutex_unlock( &p_sql->p_sys->lock );
370 * @brief Free SQL request's result
371 * @param p_sql This SQL object.
372 * @param ppsz_result SQL result to free
374 static void FreeResult( sql_t * p_sql, char **ppsz_result )
377 if( ppsz_result != NULL )
378 sqlite3_free_table( ppsz_result );
382 * @brief vmprintf replacement for SQLite.
383 * @param psz_fmt Format string
384 * @param args va_list of arguments
385 * This function implements the formats %q, %Q and %z.
387 static char* VMSprintf( const char* psz_fmt, va_list args )
389 char *psz = sqlite3_vmprintf( psz_fmt, args );
390 char *ret = strdup( psz );
396 * @brief Starts a Transaction and waits if necessary
397 * @param p_sql The SQL object
398 * @note This function locks the transactions on the database.
399 * Within the period of the transaction, only the calling thread may
400 * execute sql statements provided all threads use these transaction fns.
402 static int BeginTransaction( sql_t* p_sql )
404 int i_ret = VLC_SUCCESS;
405 vlc_mutex_lock( &p_sql->p_sys->trans_lock );
406 vlc_mutex_lock( &p_sql->p_sys->lock );
407 assert( p_sql->p_sys->db );
409 sqlite3_exec( p_sql->p_sys->db, "BEGIN;", NULL, NULL, NULL );
411 msg_Dbg( p_sql, "Transaction Query: BEGIN;" );
413 if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
415 vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
416 vlc_mutex_unlock( &p_sql->p_sys->lock );
417 msg_Warn( p_sql, "sqlite3 error: %d: %s",
418 sqlite3_errcode( p_sql->p_sys->db ),
419 sqlite3_errmsg( p_sql->p_sys->db ) );
420 i_ret = VLC_EGENERIC;
422 vlc_mutex_unlock( &p_sql->p_sys->lock );
427 * @brief Commit a transaction
428 * @param p_sql The SQL object
429 * @note This function unlocks the transactions on the database
430 * Only the calling thread of "BeginTransaction" is allowed to call this method
431 * If the commit fails, the transaction lock is still held by the thread
432 * and this function may be retried or RollbackTransaction can be called
433 * @return VLC_SUCCESS or VLC_EGENERIC
435 static int CommitTransaction( sql_t* p_sql )
437 int i_ret = VLC_SUCCESS;
438 assert( p_sql->p_sys->db );
439 vlc_mutex_lock( &p_sql->p_sys->lock );
441 /** This turns the auto commit on. */
442 sqlite3_exec( p_sql->p_sys->db, "COMMIT;", NULL, NULL, NULL );
444 msg_Dbg( p_sql, "Transaction Query: COMMIT;" );
446 if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
448 msg_Warn( p_sql, "sqlite3 error: %d: %s",
449 sqlite3_errcode( p_sql->p_sys->db ),
450 sqlite3_errmsg( p_sql->p_sys->db ) );
451 vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
452 i_ret = VLC_EGENERIC;
454 vlc_mutex_unlock( &p_sql->p_sys->lock );
459 * @brief Rollback a transaction, in case of failure
460 * @param p_sql The SQL object
461 * @return VLC_SUCCESS or VLC_EGENERIC
462 * @note This function unlocks the transactions on the database
463 * Only the calling thread of "BeginTransaction" is allowed to call this method
464 * If failed, if a statement in the transaction failed, it means that
465 * the transaction was automatically rolled back
466 * If failed otherwise, the engine is busy executing some queries and you must
469 static void RollbackTransaction( sql_t* p_sql )
471 assert( p_sql->p_sys->db );
472 vlc_mutex_lock( &p_sql->p_sys->lock );
474 sqlite3_exec( p_sql->p_sys->db, "ROLLBACK;", NULL, NULL, NULL );
476 msg_Dbg( p_sql, "Transaction Query: ROLLBACK;" );
478 if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
480 msg_Err( p_sql, "sqlite3 error: %d: %s",
481 sqlite3_errcode( p_sql->p_sys->db ),
482 sqlite3_errmsg( p_sql->p_sys->db ) );
484 vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
485 vlc_mutex_unlock( &p_sql->p_sys->lock );
489 * Prepare an sqlite statement
490 * @return statement object or NULL in case of failure
492 static sql_stmt_t* PrepareStatement( sql_t* p_sql, const char* psz_fmt, int i_length )
494 assert( p_sql->p_sys->db );
496 p_stmt = calloc( 1, sizeof( *p_stmt ) );
499 vlc_mutex_lock( &p_sql->p_sys->lock );
500 if( sqlite3_prepare_v2( p_sql->p_sys->db, psz_fmt, i_length,
501 &p_stmt->p_sqlitestmt, NULL ) != SQLITE_OK )
503 msg_Warn( p_sql, "sqlite3 error: %d: %s",
504 sqlite3_errcode( p_sql->p_sys->db ),
505 sqlite3_errmsg( p_sql->p_sys->db ) );
506 vlc_mutex_unlock( &p_sql->p_sys->lock );
511 vlc_mutex_unlock( &p_sql->p_sys->lock );
516 * @brief Bind arguments to a sql_stmt_t object
517 * @param p_sql The SQL object
518 * @param p_stmt Statement Object
519 * @param i_pos Position at which the parameter should be bound
520 * @param i_type Data type of the value
521 * @param p_value Value to be bound
522 * @return VLC_SUCCESS or VLC_EGENERIC
524 static int BindValues( sql_t* p_sql, sql_stmt_t* p_stmt,
525 int i_pos, unsigned int i_type, const sql_value_t* p_value )
527 assert( p_sql->p_sys->db );
528 assert( p_stmt->p_sqlitestmt );
529 vlc_mutex_lock( &p_sql->p_sys->lock );
530 int i_ret, i_vlc_ret = VLC_SUCCESS;
534 i_ret = sqlite3_bind_int( p_stmt->p_sqlitestmt, i_pos, p_value->value.i );
537 i_ret = sqlite3_bind_double( p_stmt->p_sqlitestmt, i_pos, p_value->value.dbl );
540 i_ret = sqlite3_bind_text( p_stmt->p_sqlitestmt, i_pos, p_value->value.psz, p_value->length, NULL );
543 i_ret = sqlite3_bind_blob( p_stmt->p_sqlitestmt, i_pos, p_value->value.ptr, p_value->length, NULL );
546 i_ret = sqlite3_bind_null( p_stmt->p_sqlitestmt, i_pos );
549 msg_Warn( p_sql, "Trying to bind invalid type of value %d", i_type );
550 vlc_mutex_unlock( &p_sql->p_sys->lock );
553 if( i_ret != SQLITE_OK )
555 msg_Warn( p_sql, "sqlite3 error: %d: %s",
556 sqlite3_errcode( p_sql->p_sys->db ),
557 sqlite3_errmsg( p_sql->p_sys->db ) );
558 i_vlc_ret = VLC_EGENERIC;
560 vlc_mutex_unlock( &p_sql->p_sys->lock );
565 * @brief Run the SQL statement. If the statement fetches data, then only
566 * one row of the data is fetched at a time. Run this function again to
567 * fetch the next row.
568 * @param p_sql The SQL object
569 * @param p_stmt The statement
570 * @return VLC_SQL_DONE if done fetching all rows or there are no rows to fetch
571 * VLC_SQL_ROW if a row was fetched for this statement.
572 * VLC_EGENERIC if this function failed
574 static int StatementStep( sql_t* p_sql, sql_stmt_t* p_stmt )
576 assert( p_sql->p_sys->db );
577 assert( p_stmt->p_sqlitestmt );
578 vlc_mutex_lock( &p_sql->p_sys->lock );
579 int i_sqlret = sqlite3_step( p_stmt->p_sqlitestmt );
580 int i_ret = VLC_EGENERIC;
581 if( i_sqlret == SQLITE_ROW )
583 else if( i_ret == SQLITE_DONE )
584 i_ret = VLC_SQL_DONE;
587 msg_Warn( p_sql, "sqlite3 error: %d: %s",
588 sqlite3_errcode( p_sql->p_sys->db ),
589 sqlite3_errmsg( p_sql->p_sys->db ) );
590 i_ret = VLC_EGENERIC;
592 vlc_mutex_unlock( &p_sql->p_sys->lock );
597 * @brief Reset the SQL statement. Resetting the statement will unbind all
598 * the values that were bound on this statement
599 * @param p_sql The SQL object
600 * @param p_stmt The sql statement object
601 * @return VLC_SUCCESS or VLC_EGENERIC
603 static int StatementReset( sql_t* p_sql, sql_stmt_t* p_stmt )
605 assert( p_sql->p_sys->db );
606 assert( p_stmt->p_sqlitestmt );
607 int i_ret = VLC_SUCCESS;
608 vlc_mutex_lock( &p_sql->p_sys->lock );
609 if( sqlite3_reset( p_stmt->p_sqlitestmt ) != SQLITE_OK )
611 msg_Warn( p_sql, "sqlite3 error: %d: %s",
612 sqlite3_errcode( p_sql->p_sys->db ),
613 sqlite3_errmsg( p_sql->p_sys->db ) );
614 i_ret = VLC_EGENERIC;
616 vlc_mutex_unlock( &p_sql->p_sys->lock );
621 * @brief Destroy the sql statement object. This will free memory.
622 * @param p_sql The SQL object
623 * @param p_stmt The statement object
624 * @return VLC_SUCCESS or VLC_EGENERIC
626 static int StatementFinalize( sql_t* p_sql, sql_stmt_t* p_stmt )
628 assert( p_sql->p_sys->db );
629 assert( p_stmt->p_sqlitestmt );
630 int i_ret = VLC_SUCCESS;
631 vlc_mutex_lock( &p_sql->p_sys->lock );
632 if( sqlite3_finalize( p_stmt->p_sqlitestmt ) != SQLITE_OK )
634 msg_Warn( p_sql, "sqlite3 error: %d: %s",
635 sqlite3_errcode( p_sql->p_sys->db ),
636 sqlite3_errmsg( p_sql->p_sys->db ) );
637 i_ret = VLC_EGENERIC;
640 vlc_mutex_unlock( &p_sql->p_sys->lock );
645 * @brief Get the column data
646 * @param p_sql The SQL object
647 * @param p_stmt The statement object
648 * @param i_col The column number
649 * @param type Datatype of result
650 * @param p_res The structure which contains the value of the result
651 * @return VLC_SUCCESS or VLC_EGENERIC
653 static int GetColumnFromStatement( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
654 int type, sql_value_t *p_res )
656 assert( p_sql->p_sys->db );
657 assert( p_stmt->p_sqlitestmt );
658 int i_ret = VLC_SUCCESS;
659 vlc_mutex_lock( &p_sql->p_sys->lock );
663 p_res->value.i = sqlite3_column_int( p_stmt->p_sqlitestmt, i_col );
666 p_res->value.dbl = sqlite3_column_double( p_stmt->p_sqlitestmt, i_col );
669 p_res->value.psz = sqlite3_column_text( p_stmt->p_sqlitestmt, i_col );
672 p_res->value.ptr = sqlite3_column_blob( p_stmt->p_sqlitestmt, i_col );
676 msg_Warn( p_sql, "Trying to bind invalid type of value %d", type );
677 i_ret = VLC_EGENERIC;
679 vlc_mutex_unlock( &p_sql->p_sys->lock );
684 * @brief Get the datatype of the result of the column
685 * @param p_sql The SQL object
686 * @param p_stmt The sql statement object
687 * @param i_col The column
688 * @param pi_type pointer to datatype of the given column
689 * @return VLC_SUCCESS or VLC_EGENERIC
691 static int GetColumnTypeFromStatement( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
694 assert( p_sql->p_sys->db );
695 assert( p_stmt->p_sqlitestmt );
697 vlc_mutex_lock( &p_sql->p_sys->lock );
698 int i_ret = VLC_SUCCESS;
699 int i_sqlret = sqlite3_column_type( p_stmt->p_sqlitestmt, i_col );
706 *pi_type= SQL_DOUBLE;
718 i_ret = VLC_EGENERIC;
720 vlc_mutex_unlock( &p_sql->p_sys->lock );