include/vlc_sql.h

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