1
/*****************************************************************************
2
* sqlite.c: An SQLite3 wrapper for VLC
3
*****************************************************************************
4
* Copyright (C) 2008-2009 the VideoLAN team
5
* $Id: 360577f447289abb69f5bd86e3323a21b8551301 $
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>
35
#include <vlc_plugin.h>
41
/*****************************************************************************
43
*****************************************************************************/
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 */
53
sqlite3_stmt* p_sqlitestmt;
57
/*****************************************************************************
59
*****************************************************************************/
60
static int load ( vlc_object_t * );
61
static void unload ( vlc_object_t * );
63
static int OpenDatabase( sql_t * );
64
static int CloseDatabase (sql_t * );
65
static int QueryCallback( sql_t * p_sql,
67
sql_query_callback_t callback,
68
void *arg ); // 1st argument to callback
69
static int Query( sql_t * p_sql,
74
static int GetTables( sql_t * p_sql,
76
static void FreeResult( sql_t * p_sql,
78
static char* VMSprintf( const char* psz_fmt,
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,
86
static int BindValues( sql_t* p_sql,
90
const sql_value_t* p_value );
91
static int StatementStep( sql_t* p_sql,
93
static int StatementReset( sql_t* p_sql,
95
static int StatementFinalize( sql_t* p_sql,
97
static int GetColumnFromStatement( sql_t* p_sql,
101
sql_value_t *p_res );
102
static int GetColumnTypeFromStatement( sql_t* p_sql,
106
static int GetColumnSize( sql_t* p_sql,
110
/*****************************************************************************
112
*****************************************************************************/
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 )
124
* @param obj Parent object
125
* @return VLC_SUCCESS or VLC_ENOMEM
127
static int load( vlc_object_t *p_this )
129
sql_t *p_sql = (sql_t *) p_this;
131
/* Initialize sys_t */
132
p_sql->p_sys = calloc( 1, sizeof( *p_sql->p_sys ) );
136
vlc_mutex_init( &p_sql->p_sys->lock );
137
vlc_mutex_init( &p_sql->p_sys->trans_lock );
140
if( OpenDatabase( p_sql ) == VLC_SUCCESS )
141
msg_Dbg( p_sql, "sqlite module loaded" );
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 );
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;
171
* @brief Unload module
172
* @param obj This sql_t object
175
static void unload( vlc_object_t *p_this )
177
sql_t *p_sql = (sql_t *)p_this;
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 );
186
* @brief Sqlite Busy handler
187
* @param p_data sql_t object
188
* @param i_times Number of times busy handler has been invoked
190
static int vlc_sqlite_busy_handler( void* p_data, int i_times )
194
msg_Warn( (sql_t*) p_data, "Wait limit exceeded in SQLITE_BUSY handler" );
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
207
static int OpenDatabase( sql_t *p_sql )
209
assert( p_sql->psz_host && *p_sql->psz_host );
211
if( sqlite3_threadsafe() == 0 )
213
msg_Err( p_sql, "Sqlite library on your system is not threadsafe" );
216
if( sqlite3_open( p_sql->psz_host, &p_sql->p_sys->db ) != SQLITE_OK )
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 ) );
224
if( sqlite3_busy_timeout( p_sql->p_sys->db, 30000 ) != SQLITE_OK )
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 ) );
231
if( sqlite3_busy_handler( p_sql->p_sys->db, vlc_sqlite_busy_handler, p_sql )
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 ) );
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
249
static int CloseDatabase( sql_t *p_sql )
251
assert( p_sql->p_sys->db );
253
/* Close all prepared statements */
254
sqlite3_stmt* p_stmt;
255
while( ( p_stmt = sqlite3_next_stmt( p_sql->p_sys->db, NULL ) ) != NULL )
257
if( sqlite3_finalize( p_stmt ) != SQLITE_OK )
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 ) );
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;
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
283
static int QueryCallback( sql_t * p_sql,
285
sql_query_callback_t callback,
288
int i_ret = VLC_SUCCESS;
289
vlc_mutex_lock( &p_sql->p_sys->lock );
290
assert( p_sql->p_sys->db );
292
msg_Dbg( p_sql, "QueryCallback: %s", query );
294
sqlite3_exec( p_sql->p_sys->db, query, callback, arg, NULL );
295
if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
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;
303
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
318
static int Query( sql_t * p_sql,
324
assert( p_sql->p_sys->db );
325
int i_ret = VLC_SUCCESS;
326
vlc_mutex_lock( &p_sql->p_sys->lock );
329
msg_Dbg( p_sql, "Query: %s", query );
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 )
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;
340
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
351
static int GetTables( sql_t * p_sql,
354
int nrow, i_num = -1;
356
vlc_mutex_lock( &p_sql->p_sys->lock );
358
assert( p_sql->p_sys->db );
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 )
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 ) );
368
vlc_mutex_unlock( &p_sql->p_sys->lock );
373
* @brief Free SQL request's result
374
* @param p_sql This SQL object.
375
* @param ppsz_result SQL result to free
377
static void FreeResult( sql_t * p_sql, char **ppsz_result )
380
if( ppsz_result != NULL )
381
sqlite3_free_table( ppsz_result );
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.
390
static char* VMSprintf( const char* psz_fmt, va_list args )
392
char *psz = sqlite3_vmprintf( psz_fmt, args );
393
char *ret = strdup( psz );
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.
405
static int BeginTransaction( sql_t* p_sql )
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 );
412
sqlite3_exec( p_sql->p_sys->db, "BEGIN;", NULL, NULL, NULL );
414
msg_Dbg( p_sql, "Transaction Query: BEGIN;" );
416
if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
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;
425
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
438
static int CommitTransaction( sql_t* p_sql )
440
int i_ret = VLC_SUCCESS;
441
assert( p_sql->p_sys->db );
442
vlc_mutex_lock( &p_sql->p_sys->lock );
444
/** This turns the auto commit on. */
445
sqlite3_exec( p_sql->p_sys->db, "COMMIT;", NULL, NULL, NULL );
447
msg_Dbg( p_sql, "Transaction Query: COMMIT;" );
449
if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
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;
457
vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
458
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
473
static void RollbackTransaction( sql_t* p_sql )
475
assert( p_sql->p_sys->db );
476
vlc_mutex_lock( &p_sql->p_sys->lock );
478
sqlite3_exec( p_sql->p_sys->db, "ROLLBACK;", NULL, NULL, NULL );
480
msg_Dbg( p_sql, "Transaction Query: ROLLBACK;" );
482
if( sqlite3_errcode( p_sql->p_sys->db ) != SQLITE_OK )
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 ) );
488
vlc_mutex_unlock( &p_sql->p_sys->trans_lock );
489
vlc_mutex_unlock( &p_sql->p_sys->lock );
493
* Prepare an sqlite statement
494
* @return statement object or NULL in case of failure
496
static sql_stmt_t* PrepareStatement( sql_t* p_sql, const char* psz_fmt, int i_length )
498
assert( p_sql->p_sys->db );
500
p_stmt = calloc( 1, sizeof( *p_stmt ) );
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 )
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 );
515
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
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 )
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;
538
i_ret = sqlite3_bind_int( p_stmt->p_sqlitestmt, i_pos, p_value->value.i );
541
i_ret = sqlite3_bind_double( p_stmt->p_sqlitestmt, i_pos, p_value->value.dbl );
544
i_ret = sqlite3_bind_text( p_stmt->p_sqlitestmt, i_pos, p_value->value.psz, p_value->length, NULL );
547
i_ret = sqlite3_bind_blob( p_stmt->p_sqlitestmt, i_pos, p_value->value.ptr, p_value->length, NULL );
550
i_ret = sqlite3_bind_null( p_stmt->p_sqlitestmt, i_pos );
553
msg_Warn( p_sql, "Trying to bind invalid type of value %d", i_type );
554
vlc_mutex_unlock( &p_sql->p_sys->lock );
557
if( i_ret != SQLITE_OK )
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;
564
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
578
static int StatementStep( sql_t* p_sql, sql_stmt_t* p_stmt )
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 )
587
else if( i_ret == SQLITE_DONE )
588
i_ret = VLC_SQL_DONE;
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;
596
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
607
static int StatementReset( sql_t* p_sql, sql_stmt_t* p_stmt )
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 )
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;
620
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
630
static int StatementFinalize( sql_t* p_sql, sql_stmt_t* p_stmt )
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 )
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;
644
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
657
static int GetColumnFromStatement( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
658
int type, sql_value_t *p_res )
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;
670
p_res->value.i = sqlite3_column_int( p_stmt->p_sqlitestmt, i_col );
673
p_res->value.dbl = sqlite3_column_double( p_stmt->p_sqlitestmt, i_col );
676
psz = sqlite3_column_text( p_stmt->p_sqlitestmt, i_col );
678
p_res->value.psz = strdup( (const char* ) psz );
681
ptr = sqlite3_column_blob( p_stmt->p_sqlitestmt, i_col );
682
size = sqlite3_column_bytes( p_stmt->p_sqlitestmt, i_col );
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 );
695
msg_Warn( p_sql, "Trying to bind invalid type of value %d", type );
696
i_ret = VLC_EGENERIC;
698
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
710
static int GetColumnTypeFromStatement( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
713
assert( p_sql->p_sys->db );
714
assert( p_stmt->p_sqlitestmt );
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 );
725
*pi_type= SQL_DOUBLE;
737
i_ret = VLC_EGENERIC;
739
vlc_mutex_unlock( &p_sql->p_sys->lock );
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
750
static int GetColumnSize( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col )
752
assert( p_sql->p_sys->db );
753
assert( p_stmt->p_sqlitestmt );
754
return sqlite3_column_bytes( p_stmt->p_sqlitestmt, i_col );