include/vlc_sql.h

   1 /*****************************************************************************
   2  * vlc_sql.h: SQL abstraction layer
   3  *****************************************************************************
   4  * Copyright (C) 2009 the VideoLAN team
   5  * $Id$
   6  *
   7  * Authors: Antoine Lejeune <phytos@videolan.org>
   8  *          Jean-Philippe André <jpeg@videolan.org>
   9  *          Srikanth Raju <srikiraju@gmail.com>
  10  *
  11  * This program is free software; you can redistribute it and/or modify
  12  * it under the terms of the GNU General Public License as published by
  13  * the Free Software Foundation; either version 2 of the License, or
  14  * (at your option) any later version.
  15  *
  16  * This program is distributed in the hope that it will be useful,
  17  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  18  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  19  * GNU General Public License for more details.
  20  *
  21  * You should have received a copy of the GNU General Public License
  22  * along with this program; if not, write to the Free Software
  23  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301, USA.
  24  *****************************************************************************/
  25
  26 #if !defined( __LIBVLC__ )
  27 # error You are not libvlc or one of its plugins. You cannot include this file
  28 #endif
  29
  30 #ifndef VLC_SQL_H
  31 # define VLC_SQL_H
  32
  33 # ifdef __cplusplus
  34 extern "C" {
  35 # endif
  36
  37
  38 /*****************************************************************************
  39  * General structure: SQL object.
  40  *****************************************************************************/
  41
  42 /**
  43  * Return values for the function @see sql_Run()
  44  */
  45 #define VLC_SQL_ROW 1
  46 #define VLC_SQL_DONE 2
  47
  48 typedef struct sql_t sql_t;
  49 typedef struct sql_sys_t sql_sys_t;
  50 typedef struct sql_stmt_t sql_stmt_t;
  51
  52 typedef int ( *sql_query_callback_t ) ( void*, int, char**, char** );
  53
  54 typedef enum {
  55     SQL_NULL,
  56     SQL_INT,
  57     SQL_DOUBLE,
  58     SQL_TEXT,
  59     SQL_BLOB
  60 } sql_type_e;
  61
  62 typedef struct
  63 {
  64     int length;
  65     union
  66     {
  67         int i;
  68         double dbl;
  69         char* psz;
  70         void* ptr;
  71     } value;
  72 } sql_value_t;
  73
  74 struct sql_t
  75 {
  76     VLC_COMMON_MEMBERS
  77
  78     /** Module properties */
  79     module_t  *p_module;
  80
  81     /** Connection Data */
  82     char *psz_host;         /**< Location or host of the database */
  83     char *psz_user;         /**< Username used to connect to database */
  84     char *psz_pass;         /**< Password used to connect to database */
  85     int i_port;             /**< Port on which database is running */
  86
  87     /** Internal data */
  88     sql_sys_t *p_sys;
  89
  90     /** All the functions are implemented as threadsafe functions */
  91     /** Perform a query with a row-by-row callback function */
  92     int (*pf_query_callback) ( sql_t *, const char *, sql_query_callback_t, void * );
  93
  94     /** Perform a query and return result directly */
  95     int (*pf_query) ( sql_t *, const char *, char ***, int *, int * );
  96
  97     /** Get database tables */
  98     int (*pf_get_tables) ( sql_t *, char *** );
  99
 100     /** Free result of a call to sql_Query or sql_GetTables */
 101     void (*pf_free) ( sql_t *, char ** );
 102
 103     /** vmprintf replacement for SQL */
 104     char* (*pf_vmprintf) ( const char*, va_list args );
 105
 106     /** Begin transaction */
 107     int (*pf_begin) ( sql_t* );
 108
 109     /** Commit transaction */
 110     int (*pf_commit) ( sql_t* );
 111
 112     /** Rollback transaction */
 113     void (*pf_rollback) ( sql_t* );
 114
 115     /** Create a statement object */
 116     sql_stmt_t* (*pf_prepare) ( sql_t* p_sql, const char* p_fmt,
 117                                 int i_length );
 118
 119     /** Bind parameters to a statement */
 120     int (*pf_bind) ( sql_t* p_sql, sql_stmt_t* p_stmt, int i_pos,
 121                     unsigned int type, const sql_value_t* p_value );
 122
 123     /** Run the prepared statement */
 124     int (*pf_run) ( sql_t* p_sql, sql_stmt_t* p_stmt );
 125
 126     /** Reset the prepared statement */
 127     int (*pf_reset) ( sql_t* p_sql, sql_stmt_t* p_stmt );
 128
 129     /** Destroy the statement object */
 130     int (*pf_finalize) ( sql_t* p_sql, sql_stmt_t* p_stmt );
 131
 132     /** Get the datatype for a specified column */
 133     int (*pf_gettype) ( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
 134                         int* type );
 135
 136     /** Get the data from a specified column */
 137     int (*pf_getcolumn) ( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col,
 138                           int type, sql_value_t *p_res );
 139
 140     /** Get column size of a specified column */
 141     int (*pf_getcolumnsize) ( sql_t* p_sql, sql_stmt_t* p_stmt, int i_col );
 142 };
 143
 144 /*****************************************************************************
 145  * SQL Function headers
 146  *****************************************************************************/
 147
 148 /**
 149  * @brief Create a new SQL object.
 150  * @param p_this Parent object to attach the SQL object to.
 151  * @param psz_host URL to the database
 152  * @param i_port Port on which the database is running
 153  * @param psz_user Username to access the database
 154  * @param psz_pass Password for the database
 155  * @return The VLC SQL object, type sql_t.
 156  **/
 157 VLC_EXPORT( sql_t*, sql_Create, ( vlc_object_t *p_this, const char *psz_name,
 158             const char* psz_host, int i_port,
 159             const char* psz_user, const char* psz_pass ) );
 160 #define sql_Create( a, b, c, d, e, f ) sql_Create( VLC_OBJECT(a), b, c, d, e, f )
 161
 162
 163 /**
 164  * @brief Destructor for p_sql object
 165  * @param obj This p_sql object
 166  * @return Nothing
 167  */
 168 VLC_EXPORT( void, sql_Destroy, ( vlc_object_t *obj ) );
 169 #define sql_Destroy( a ) sql_Destroy( VLC_OBJECT( a ) )
 170
 171
 172 /**
 173  * @brief Perform a query using a callback function
 174  * @param p_sql This SQL object.
 175  * @param psz_query The SQL query string.
 176  * @param pf_callback A callback function that will be called for each row of
 177  * the result: 1st argument is be p_opaque,
 178  *             2nd argument is the number of columns,
 179  *             3rd is the result columns (array of strings),
 180  *             4th is the columns names (array of strings).
 181  * @param p_opaque Any pointer to an object you may need in the callback.
 182  * @return VLC_SUCCESS or VLC_EGENERIC.
 183  * @note The query will not necessarily be processed in a separate thread, but
 184  * it is threadsafe
 185  **/
 186 static inline int sql_QueryCallback( sql_t *p_sql, const char *psz_query,
 187                                      sql_query_callback_t pf_callback,
 188                                      void *p_opaque )
 189 {
 190     return p_sql->pf_query_callback( p_sql, psz_query, pf_callback, p_opaque );
 191 }
 192
 193 /**
 194  * @brief Perform a query directly
 195  * @param p_sql This SQL object.
 196  * @param psz_query The SQL query string.
 197  * @param pppsz_result A pointer to a array of strings: result of the query.
 198  * Dynamically allocated.
 199  * @param pi_rows Pointer to an integer that will receive the number of result
 200  * rows written.
 201  * @param pi_cols Pointer to an integer that will receive the number of result
 202  * columns written.
 203  * @return VLC_SUCCESS or VLC_EGENERIC.
 204  * @note pppsz_result will point to an array of strings, ppsz_result.
 205  * This array of strings contains actually a 2D-matrix of strings where the
 206  * first row (row 0) contains the SQL table header names.
 207  * *pi_rows will be the number of result rows, so that the number of text rows
 208  * in ppsz_result will be (*pi_rows + 1) (because of row 0).
 209  * To get result[row,col] use (*pppsz_result)[ (row+1) * (*pi_cols) + col ].
 210  * This function is threadsafe
 211  **/
 212 static inline int sql_Query( sql_t *p_sql, const char *psz_query,
 213                              char ***pppsz_result, int *pi_rows, int *pi_cols )
 214 {
 215     return p_sql->pf_query( p_sql, psz_query, pppsz_result, pi_rows, pi_cols );
 216 }
 217
 218 /**
 219  * @brief Get database table name list
 220  * @param p_sql This SQL object.
 221  * @param pppsz_tables Pointer to an array of strings. Dynamically allocated.
 222  * Similar to pppsz_result of sql_Query but with only one row.
 223  * @return Number of tables or <0 in case of error.
 224  * @note This function is threadsafe
 225  **/
 226 static inline int sql_GetTables( sql_t *p_sql, char ***pppsz_tables )
 227 {
 228     return p_sql->pf_get_tables( p_sql, pppsz_tables );
 229 }
 230
 231 /**
 232  * @brief Free the result of a query.
 233  * @param p_sql This SQL object.
 234  * @param ppsz_result The result of sql_Query or sql_GetTables. See above.
 235  * @return Nothing.
 236  * @note This function is threadsafe
 237  **/
 238 static inline void sql_Free( sql_t *p_sql, char **ppsz_result )
 239 {
 240     p_sql->pf_free( p_sql, ppsz_result );
 241 }
 242
 243 /**
 244  * @brief printf-like function that can escape forbidden/reserved characters.
 245  * @param p_sql This SQL object.
 246  * @param psz_fmt Format of the string (with %q, %Q and %z enabled).
 247  * @param ... Printf arguments
 248  * @return Dynamically allocated string or NULL in case of error.
 249  * @note Refer to SQLite documentation for more details about %q, %Q and %z.
 250  **/
 251 static inline char* sql_Printf( sql_t *p_sql, const char *psz_fmt, ... )
 252 {
 253     va_list args;
 254     va_start( args, psz_fmt );
 255     char *r = p_sql->pf_vmprintf( psz_fmt, args );
 256     va_end( args );
 257     return r;
 258 }
 259
 260 /**
 261  * @brief vprintf replacement for SQL queries, escaping forbidden characters
 262  * @param p_sql This SQL object
 263  * @param psz_fmt Format of the string
 264  * @param arg Variable list of arguments
 265  * @return Dynamically allocated string or NULL in case of error.
 266  **/
 267 static inline char* sql_VPrintf( sql_t *p_sql, const char *psz_fmt,
 268                                  va_list arg )
 269 {
 270     return p_sql->pf_vmprintf( psz_fmt, arg );
 271 }
 272
 273 /**
 274  * @brief Begin a SQL transaction
 275  * @param p_sql The SQL object
 276  * @return VLC error code or success
 277  * @note This function is threadsafe
 278  **/
 279 static inline int sql_BeginTransaction( sql_t *p_sql )
 280 {
 281     return p_sql->pf_begin( p_sql );
 282 }
 283
 284 /**
 285  * @brief Commit a SQL transaction
 286  * @param p_sql The SQL object
 287  * @return VLC error code or success
 288  * @note This function is threadsafe
 289  **/
 290 static inline int sql_CommitTransaction( sql_t *p_sql )
 291 {
 292     return p_sql->pf_commit( p_sql );
 293 }
 294
 295 /**
 296  * @brief Rollback a SQL transaction
 297  * @param p_sql The SQL object
 298  * @return VLC error code or success
 299  * @note This function is threadsafe
 300  **/
 301 static inline void sql_RollbackTransaction( sql_t *p_sql )
 302 {
 303     p_sql->pf_rollback( p_sql );
 304 }
 305
 306 /**
 307  * @brief Prepare an sql statement
 308  * @param p_sql The SQL object
 309  * @param p_fmt SQL query string
 310  * @param i_length length of the string. If negative, length will be
 311  * considered upto the first \0 character equivalent to strlen(p_fmt).
 312  * Otherwise the first i_length bytes will be used
 313  * @return a sql_stmt_t pointer or NULL on failure
 314  */
 315 static inline sql_stmt_t* sql_Prepare( sql_t* p_sql, const char* p_fmt,
 316         int i_length )
 317 {
 318     return p_sql->pf_prepare( p_sql, p_fmt, i_length );
 319 }
 320
 321 /**
 322  * @brief Bind arguments to a sql_stmt_t object
 323  * @param p_sql The SQL object
 324  * @param p_stmt Statement Object
 325  * @param type Data type of the value
 326  * @param p_value Value to be bound
 327  * @param i_pos Position at which the parameter should be bound
 328  * @return VLC_SUCCESS or VLC_EGENERIC
 329  */
 330 static inline int sql_BindGeneric( sql_t* p_sql, sql_stmt_t* p_stmt,
 331         int i_pos, int type, const sql_value_t* p_value )
 332 {
 333     return p_sql->pf_bind( p_sql, p_stmt, i_pos, type, p_value );
 334 }
 335
 336 /**
 337  * @brief Bind a NULL value to a position
 338  * @param p_sql The SQL object
 339  * @param p_stmt Statement Object
 340  * @param i_pos Position at which the parameter should be bound
 341  * @return VLC_SUCCESS or VLC_EGENERIC
 342  */
 343 static inline int sql_BindNull( sql_t *p_sql, sql_stmt_t* p_stmt, int i_pos )
 344 {
 345     int i_ret = sql_BindGeneric( p_sql, p_stmt, i_pos, SQL_NULL, NULL );
 346     return i_ret;
 347 }
 348
 349 /**
 350  * @brief Bind an integer to the statement object at some position
 351  * @param p_sql The SQL object
 352  * @param p_stmt Statement Object
 353  * @param i_pos Position at which the parameter should be bound
 354  * @param i_int Value to be bound
 355  * @return VLC_SUCCESS or VLC_EGENERIC
 356  */
 357 static inline int sql_BindInteger( sql_t *p_sql, sql_stmt_t* p_stmt,
 358                                    int i_pos, int i_int )
 359 {
 360     sql_value_t value;
 361     value.length = 0;
 362     value.value.i = i_int;
 363     int i_ret = sql_BindGeneric( p_sql, p_stmt, i_pos, SQL_INT, &value );
 364     return i_ret;
 365 }
 366
 367 /**
 368  * @brief Bind a double to the statement object at some position
 369  * @param p_sql The SQL object
 370  * @param p_stmt Statement Object
 371  * @param i_pos Position at which the parameter should be bound
 372  * @param d_dbl Value to be bound
 373  * @return VLC_SUCCESS or VLC_EGENERIC
 374  */
 375 static inline int sql_BindDouble( sql_t *p_sql, sql_stmt_t* p_stmt,
 376                                   int i_pos, double d_dbl )
 377 {
 378     sql_value_t value;
 379     value.length = 0;
 380     value.value.dbl = d_dbl;
 381     int i_ret = sql_BindGeneric( p_sql, p_stmt, i_pos, SQL_INT, &value );
 382     return i_ret;
 383 }
 384
 385 /**
 386  * @brief Bind Text to the statement
 387  * @param p_sql The SQL object
 388  * @param p_stmt Statement Object
 389  * @param i_pos Position at which the parameter should be bound
 390  * @param p_fmt Value to be bound
 391  * @param i_length Length of text. If -ve text upto the first null char
 392  * will be selected.
 393  * @return VLC_SUCCESS or VLC_EGENERIC
 394  */
 395 static inline int sql_BindText( sql_t *p_sql, sql_stmt_t* p_stmt, int i_pos,
 396                                    char* p_fmt, int i_length )
 397 {
 398     sql_value_t value;
 399     value.length = i_length;
 400     value.value.psz = p_fmt;
 401     int i_ret = sql_BindGeneric( p_sql, p_stmt, i_pos, SQL_TEXT, &value );
 402     return i_ret;
 403 }
 404
 405 /**
 406  * @brief Bind a binary object to the statement
 407  * @param p_sql The SQL object
 408  * @param p_stmt Statement Object
 409  * @param i_pos Position at which the parameter should be bound
 410  * @param p_ptr Value to be bound
 411  * @param i_length Size of the blob to read
 412  * @return VLC_SUCCESS or VLC_EGENERIC
 413  */
 414 static inline int sql_BindBlob( sql_t *p_sql, sql_stmt_t* p_stmt, int i_pos,
 415                                    void* p_ptr, int i_length )
 416 {
 417     sql_value_t value;
 418     value.length = i_length;
 419     value.value.ptr = p_ptr;
 420     int i_ret = sql_BindGeneric( p_sql, p_stmt, i_pos, SQL_INT, &value );
 421     return i_ret;
 422 }
 423
 424 /**
 425  * @brief Run the SQL statement. If the statement fetches data, then only
 426  * one row of the data is fetched at a time. Run this function again to
 427  * fetch the next row.
 428  * @param p_sql The SQL object
 429  * @param p_stmt The statement
 430  * @return VLC_SQL_DONE if done fetching all rows or there are no rows to fetch
 431  * VLC_SQL_ROW if a row was fetched for this statement.
 432  * VLC_EGENERIC if this function failed
 433  */
 434 static inline int sql_Run( sql_t* p_sql, sql_stmt_t* p_stmt )
 435 {
 436     return p_sql->pf_run( p_sql, p_stmt );
 437 }
 438
 439 /**
 440  * @brief Reset the SQL statement. Resetting the statement will unbind all
 441  * the values that were bound on this statement
 442  * @param p_sql The SQL object
 443  * @param p_stmt The sql statement object
 444  * @return VLC_SUCCESS or VLC_EGENERIC
 445  */
 446 static inline int sql_Reset( sql_t* p_sql, sql_stmt_t* p_stmt )
 447 {
 448     return p_sql->pf_reset( p_sql, p_stmt );
 449 }
 450
 451 /**
 452  * @brief Destroy the sql statement object. This will free memory.
 453  * @param p_sql The SQL object
 454  * @param p_stmt The statement object
 455  * @return VLC_SUCCESS or VLC_EGENERIC
 456  */
 457 static inline int sql_Finalize( sql_t* p_sql, sql_stmt_t* p_stmt )
 458 {
 459     return p_sql->pf_finalize( p_sql, p_stmt );
 460 }
 461
 462 /**
 463  * @brief Get the datatype of the result of the column
 464  * @param p_sql The SQL object
 465  * @param p_stmt The sql statement object
 466  * @param i_col The column
 467  * @param type pointer to datatype of the given column
 468  * @return VLC_SUCCESS or VLC_EGENERIC
 469  */
 470 static inline int sql_GetColumnType( sql_t* p_sql, sql_stmt_t* p_stmt,
 471         int i_col, int* type )
 472 {
 473     return p_sql->pf_gettype( p_sql, p_stmt, i_col, type );
 474 }
 475
 476 /**
 477  * @brief Get the column data
 478  * @param p_sql The SQL object
 479  * @param p_stmt The statement object
 480  * @param i_col The column number
 481  * @param type Datatype of result
 482  * @param p_res The structure which contains the value of the result
 483  * @return VLC_SUCCESS or VLC_EGENERIC
 484  */
 485 static inline int sql_GetColumn( sql_t* p_sql, sql_stmt_t* p_stmt,
 486         int i_col, int type, sql_value_t *p_res )
 487 {
 488     return p_sql->pf_getcolumn( p_sql, p_stmt, i_col, type, p_res );
 489 }
 490
 491 /**
 492  * @brief Get an integer from the results of a statement
 493  * @param p_sql The SQL object
 494  * @param p_stmt The statement object
 495  * @param i_col The column number
 496  * @param i_res Pointer of the location for result to be stored
 497  * @return VLC_SUCCESS or VLC_EGENERIC
 498  */
 499 static inline int sql_GetColumnInteger( sql_t* p_sql, sql_stmt_t* p_stmt,
 500         int i_col, int* pi_res )
 501 {
 502     sql_value_t tmp;
 503     int i_ret = p_sql->pf_getcolumn( p_sql, p_stmt, i_col, SQL_INT, &tmp );
 504     if( i_ret == VLC_SUCCESS )
 505         *pi_res = tmp.value.i;
 506     return i_ret;
 507 }
 508
 509 /**
 510  * @brief Get a double from the results of a statement
 511  * @param p_sql The SQL object
 512  * @param p_stmt The statement object
 513  * @param i_col The column number
 514  * @param d_res Pointer of the location for result to be stored
 515  * @return VLC_SUCCESS or VLC_EGENERIC
 516  */
 517 static inline int sql_GetColumnDouble( sql_t* p_sql, sql_stmt_t* p_stmt,
 518         int i_col, double* pd_res )
 519 {
 520     sql_value_t tmp;
 521     int i_ret = p_sql->pf_getcolumn( p_sql, p_stmt, i_col, SQL_DOUBLE, &tmp );
 522     if( i_ret == VLC_SUCCESS )
 523         *pd_res = tmp.value.dbl;
 524     return i_ret;
 525 }
 526
 527 /**
 528  * @brief Get some text from the results of a statement
 529  * @param p_sql The SQL object
 530  * @param p_stmt The statement object
 531  * @param i_col The column number
 532  * @param pp_res Pointer of the location for result to be stored
 533  * @return VLC_SUCCESS or VLC_EGENERIC
 534  */
 535 static inline int sql_GetColumnText( sql_t* p_sql, sql_stmt_t* p_stmt,
 536         int i_col, char** pp_res )
 537 {
 538     sql_value_t tmp;
 539     int i_ret = p_sql->pf_getcolumn( p_sql, p_stmt, i_col, SQL_TEXT, &tmp );
 540     if( i_ret == VLC_SUCCESS )
 541         *pp_res = tmp.value.psz;
 542     return i_ret;
 543 }
 544
 545 /**
 546  * @brief Get a blob from the results of a statement
 547  * @param p_sql The SQL object
 548  * @param p_stmt The statement object
 549  * @param i_col The column number
 550  * @param pp_res Pointer of the location for result to be stored
 551  * @return VLC_SUCCESS or VLC_EGENERIC
 552  */
 553 static inline int sql_GetColumnBlob( sql_t* p_sql, sql_stmt_t* p_stmt,
 554         int i_col, void** pp_res )
 555 {
 556     sql_value_t tmp;
 557     int i_ret = p_sql->pf_getcolumn( p_sql, p_stmt, i_col, SQL_BLOB, &tmp );
 558     if( i_ret == VLC_SUCCESS )
 559         *pp_res = tmp.value.ptr;
 560     return i_ret;
 561 }
 562
 563 /**
 564  * @brief Get the size of the column in bytes
 565  * @param p_sql The SQL object
 566  * @param p_stmt The sql statement object
 567  * @param i_col The column
 568  * @return Size of the column in bytes, excluding the zero terminator
 569  */
 570 static inline int sql_GetColumnSize( sql_t* p_sql, sql_stmt_t* p_stmt,
 571         int i_col )
 572 {
 573     return p_sql->pf_getcolumnsize( p_sql, p_stmt, i_col );
 574 }
 575
 576 # ifdef __cplusplus
 577 }
 578 # endif /* C++ extern "C" */
 579
 580 #endif /* VLC_SQL_H */