sql.c

   1 /* MiniDLNA media server
   2  * Copyright (C) 2008-2017  Justin Maggard
   3  *
   4  * This file is part of MiniDLNA.
   5  *
   6  * MiniDLNA is free software; you can redistribute it and/or modify
   7  * it under the terms of the GNU General Public License version 2 as
   8  * published by the Free Software Foundation.
   9  *
  10  * MiniDLNA is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  *
  15  * You should have received a copy of the GNU General Public License
  16  * along with MiniDLNA. If not, see <http://www.gnu.org/licenses/>.
  17  */
  18 #include <stdio.h>
  19 #include <string.h>
  20 #include <unistd.h>
  21
  22 #include "sql.h"
  23 #include "upnpglobalvars.h"
  24 #include "log.h"
  25
  26 int
  27 sql_exec(sqlite3 *db, const char *fmt, ...)
  28 {
  29         int ret;
  30         char *errMsg = NULL;
  31         char *sql;
  32         va_list ap;
  33         //DPRINTF(E_DEBUG, L_DB_SQL, "SQL: %s\n", sql);
  34
  35         va_start(ap, fmt);
  36         sql = sqlite3_vmprintf(fmt, ap);
  37         va_end(ap);
  38         ret = sqlite3_exec(db, sql, 0, 0, &errMsg);
  39         if( ret != SQLITE_OK )
  40         {
  41                 DPRINTF(E_ERROR, L_DB_SQL, "SQL ERROR %d [%s]\n%s\n", ret, errMsg, sql);
  42                 if (errMsg)
  43                         sqlite3_free(errMsg);
  44         }
  45         sqlite3_free(sql);
  46
  47         return ret;
  48 }
  49
  50 int
  51 sql_get_table(sqlite3 *db, const char *sql, char ***pazResult, int *pnRow, int *pnColumn)
  52 {
  53         int ret;
  54         char *errMsg = NULL;
  55         //DPRINTF(E_DEBUG, L_DB_SQL, "SQL: %s\n", sql);
  56
  57         ret = sqlite3_get_table(db, sql, pazResult, pnRow, pnColumn, &errMsg);
  58         if( ret != SQLITE_OK )
  59         {
  60                 DPRINTF(E_ERROR, L_DB_SQL, "SQL ERROR %d [%s]\n%s\n", ret, errMsg, sql);
  61                 if (errMsg)
  62                         sqlite3_free(errMsg);
  63         }
  64
  65         return ret;
  66 }
  67
  68 int
  69 sql_get_int_field(sqlite3 *db, const char *fmt, ...)
  70 {
  71         va_list         ap;
  72         int             counter, result;
  73         char            *sql;
  74         int             ret;
  75         sqlite3_stmt    *stmt;
  76
  77         va_start(ap, fmt);
  78         sql = sqlite3_vmprintf(fmt, ap);
  79         va_end(ap);
  80
  81         //DPRINTF(E_DEBUG, L_DB_SQL, "sql: %s\n", sql);
  82
  83         switch (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL))
  84         {
  85                 case SQLITE_OK:
  86                         break;
  87                 default:
  88                         DPRINTF(E_ERROR, L_DB_SQL, "prepare failed: %s\n%s\n", sqlite3_errmsg(db), sql);
  89                         sqlite3_free(sql);
  90                         return -1;
  91         }
  92
  93         for (counter = 0;
  94              ((result = sqlite3_step(stmt)) == SQLITE_BUSY || result == SQLITE_LOCKED) && counter < 2;
  95              counter++) {
  96                 /* While SQLITE_BUSY has a built in timeout,
  97                  * SQLITE_LOCKED does not, so sleep */
  98                 if (result == SQLITE_LOCKED)
  99                         sleep(1);
 100         }
 101
 102         switch (result)
 103         {
 104                 case SQLITE_DONE:
 105                         /* no rows returned */
 106                         ret = 0;
 107                         break;
 108                 case SQLITE_ROW:
 109                         if (sqlite3_column_type(stmt, 0) == SQLITE_NULL)
 110                         {
 111                                 ret = 0;
 112                                 break;
 113                         }
 114                         ret = sqlite3_column_int(stmt, 0);
 115                         break;
 116                 default:
 117                         DPRINTF(E_WARN, L_DB_SQL, "%s: step failed: %s\n%s\n", __func__, sqlite3_errmsg(db), sql);
 118                         ret = -1;
 119                         break;
 120         }
 121         sqlite3_free(sql);
 122         sqlite3_finalize(stmt);
 123
 124         return ret;
 125 }
 126
 127 int64_t
 128 sql_get_int64_field(sqlite3 *db, const char *fmt, ...)
 129 {
 130         va_list         ap;
 131         int             counter, result;
 132         char            *sql;
 133         int64_t         ret;
 134         sqlite3_stmt    *stmt;
 135
 136         va_start(ap, fmt);
 137         sql = sqlite3_vmprintf(fmt, ap);
 138         va_end(ap);
 139
 140         //DPRINTF(E_DEBUG, L_DB_SQL, "sql: %s\n", sql);
 141
 142         switch (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL))
 143         {
 144                 case SQLITE_OK:
 145                         break;
 146                 default:
 147                         DPRINTF(E_ERROR, L_DB_SQL, "prepare failed: %s\n%s\n", sqlite3_errmsg(db), sql);
 148                         sqlite3_free(sql);
 149                         return -1;
 150         }
 151
 152         for (counter = 0;
 153              ((result = sqlite3_step(stmt)) == SQLITE_BUSY || result == SQLITE_LOCKED) && counter < 2;
 154              counter++) {
 155                 /* While SQLITE_BUSY has a built in timeout,
 156                  * SQLITE_LOCKED does not, so sleep */
 157                 if (result == SQLITE_LOCKED)
 158                         sleep(1);
 159         }
 160
 161         switch (result)
 162         {
 163                 case SQLITE_DONE:
 164                         /* no rows returned */
 165                         ret = 0;
 166                         break;
 167                 case SQLITE_ROW:
 168                         if (sqlite3_column_type(stmt, 0) == SQLITE_NULL)
 169                         {
 170                                 ret = 0;
 171                                 break;
 172                         }
 173                         ret = sqlite3_column_int64(stmt, 0);
 174                         break;
 175                 default:
 176                         DPRINTF(E_WARN, L_DB_SQL, "%s: step failed: %s\n%s\n", __func__, sqlite3_errmsg(db), sql);
 177                         ret = -1;
 178                         break;
 179         }
 180         sqlite3_free(sql);
 181         sqlite3_finalize(stmt);
 182
 183         return ret;
 184 }
 185
 186 char *
 187 sql_get_text_field(sqlite3 *db, const char *fmt, ...)
 188 {
 189         va_list         ap;
 190         int             counter, result, len;
 191         char            *sql;
 192         char            *str;
 193         sqlite3_stmt    *stmt;
 194
 195         if (db == NULL)
 196         {
 197                 DPRINTF(E_WARN, L_DB_SQL, "db is NULL\n");
 198                 return NULL;
 199         }
 200
 201         va_start(ap, fmt);
 202         sql = sqlite3_vmprintf(fmt, ap);
 203         va_end(ap);
 204
 205         //DPRINTF(E_DEBUG, L_DB_SQL, "sql: %s\n", sql);
 206
 207         switch (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL))
 208         {
 209                 case SQLITE_OK:
 210                         break;
 211                 default:
 212                         DPRINTF(E_ERROR, L_DB_SQL, "prepare failed: %s\n%s\n", sqlite3_errmsg(db), sql);
 213                         sqlite3_free(sql);
 214                         return NULL;
 215         }
 216         sqlite3_free(sql);
 217
 218         for (counter = 0;
 219              ((result = sqlite3_step(stmt)) == SQLITE_BUSY || result == SQLITE_LOCKED) && counter < 2;
 220              counter++)
 221         {
 222                 /* While SQLITE_BUSY has a built in timeout,
 223                  * SQLITE_LOCKED does not, so sleep */
 224                 if (result == SQLITE_LOCKED)
 225                         sleep(1);
 226         }
 227
 228         switch (result)
 229         {
 230                 case SQLITE_DONE:
 231                         /* no rows returned */
 232                         str = NULL;
 233                         break;
 234
 235                 case SQLITE_ROW:
 236                         if (sqlite3_column_type(stmt, 0) == SQLITE_NULL)
 237                         {
 238                                 str = NULL;
 239                                 break;
 240                         }
 241
 242                         len = sqlite3_column_bytes(stmt, 0);
 243                         if ((str = sqlite3_malloc(len + 1)) == NULL)
 244                         {
 245                                 DPRINTF(E_ERROR, L_DB_SQL, "malloc failed\n");
 246                                 break;
 247                         }
 248
 249                         strncpy(str, (char *)sqlite3_column_text(stmt, 0), len + 1);
 250                         break;
 251
 252                 default:
 253                         DPRINTF(E_WARN, L_DB_SQL, "SQL step failed: %s\n", sqlite3_errmsg(db));
 254                         str = NULL;
 255                         break;
 256         }
 257         sqlite3_finalize(stmt);
 258
 259         return str;
 260 }
 261
 262 int
 263 db_upgrade(sqlite3 *db)
 264 {
 265         int db_vers;
 266         int ret;
 267
 268         db_vers = sql_get_int_field(db, "PRAGMA user_version");
 269
 270         if (db_vers == DB_VERSION)
 271                 return 0;
 272         if (db_vers > DB_VERSION)
 273                 return -2;
 274         if (db_vers < 1)
 275                 return -1;
 276         if (db_vers < 9)
 277                 return db_vers;
 278         if (db_vers < 10)
 279         {
 280                 DPRINTF(E_WARN, L_DB_SQL, "Updating DB version to v%d\n", 10);
 281                 ret = sql_exec(db, "ALTER TABLE BOOKMARKS ADD WATCH_COUNT INTEGER");
 282                 if (ret != SQLITE_OK)
 283                         return 9;
 284         }
 285         if (db_vers < 11)
 286         {
 287                 DPRINTF(E_WARN, L_DB_SQL, "Updating DB version to v%d\n", 11);
 288                 ret = sql_exec(db, "ALTER TABLE PLAYLISTS ADD TIMESTAMP INTEGER DEFAULT 1");
 289                 if (ret != SQLITE_OK)
 290                         return 10;
 291         }
 292         sql_exec(db, "PRAGMA user_version = %d", DB_VERSION);
 293
 294         return 0;
 295 }