release/src/router/minidlna/sql.c

   1 /* MiniDLNA media server
   2  * Copyright (C) 2008-2009  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
  37         sql = sqlite3_vmprintf(fmt, 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
  79         sql = sqlite3_vmprintf(fmt, 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
 122         sqlite3_free(sql);
 123         sqlite3_finalize(stmt);
 124         return ret;
 125 }
 126
 127 char *
 128 sql_get_text_field(sqlite3 *db, const char *fmt, ...)
 129 {
 130         va_list         ap;
 131         int             counter, result, len;
 132         char            *sql;
 133         char            *str;
 134         sqlite3_stmt    *stmt;
 135
 136         va_start(ap, fmt);
 137
 138         if (db == NULL)
 139         {
 140                 DPRINTF(E_WARN, L_DB_SQL, "db is NULL\n");
 141                 return NULL;
 142         }
 143
 144         sql = sqlite3_vmprintf(fmt, ap);
 145
 146         //DPRINTF(E_DEBUG, L_DB_SQL, "sql: %s\n", sql);
 147
 148         switch (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL))
 149         {
 150                 case SQLITE_OK:
 151                         break;
 152                 default:
 153                         DPRINTF(E_ERROR, L_DB_SQL, "prepare failed: %s\n%s\n", sqlite3_errmsg(db), sql);
 154                         sqlite3_free(sql);
 155                         return NULL;
 156         }
 157         sqlite3_free(sql);
 158
 159         for (counter = 0;
 160              ((result = sqlite3_step(stmt)) == SQLITE_BUSY || result == SQLITE_LOCKED) && counter < 2;
 161              counter++)
 162         {
 163                 /* While SQLITE_BUSY has a built in timeout,
 164                  * SQLITE_LOCKED does not, so sleep */
 165                 if (result == SQLITE_LOCKED)
 166                         sleep(1);
 167         }
 168
 169         switch (result)
 170         {
 171                 case SQLITE_DONE:
 172                         /* no rows returned */
 173                         str = NULL;
 174                         break;
 175
 176                 case SQLITE_ROW:
 177                         if (sqlite3_column_type(stmt, 0) == SQLITE_NULL)
 178                         {
 179                                 str = NULL;
 180                                 break;
 181                         }
 182
 183                         len = sqlite3_column_bytes(stmt, 0);
 184                         if ((str = sqlite3_malloc(len + 1)) == NULL)
 185                         {
 186                                 DPRINTF(E_ERROR, L_DB_SQL, "malloc failed\n");
 187                                 break;
 188                         }
 189
 190                         strncpy(str, (char *)sqlite3_column_text(stmt, 0), len + 1);
 191                         break;
 192
 193                 default:
 194                         DPRINTF(E_WARN, L_DB_SQL, "SQL step failed: %s\n", sqlite3_errmsg(db));
 195                         str = NULL;
 196                         break;
 197         }
 198
 199         sqlite3_finalize(stmt);
 200         return str;
 201 }
 202
 203 int
 204 db_upgrade(sqlite3 *db)
 205 {
 206         int db_vers;
 207         int ret;
 208
 209         db_vers = sql_get_int_field(db, "PRAGMA user_version");
 210
 211         if (db_vers == DB_VERSION)
 212                 return 0;
 213         if (db_vers > DB_VERSION)
 214                 return -2;
 215         if (db_vers < 1)
 216                 return -1;
 217         if (db_vers < 5)
 218                 return 5;
 219         if (db_vers < 6)
 220         {
 221                 DPRINTF(E_WARN, L_DB_SQL, "Updating DB version to v%d.\n", 6);
 222                 ret = sql_exec(db, "CREATE TABLE BOOKMARKS ("
 223                                         "ID INTEGER PRIMARY KEY, "
 224                                         "SEC INTEGER)");
 225                 if( ret != SQLITE_OK )
 226                         return 6;
 227         }
 228         if (db_vers < 7)
 229         {
 230                 DPRINTF(E_WARN, L_DB_SQL, "Updating DB version to v%d.\n", 7);
 231                 ret = sql_exec(db, "ALTER TABLE DETAILS ADD rotation INTEGER");
 232                 if( ret != SQLITE_OK )
 233                         return 7;
 234         }
 235         if (db_vers < 8)
 236         {
 237                 DPRINTF(E_WARN, L_DB_SQL, "Updating DB version to v%d.\n", 8);
 238                 ret = sql_exec(db, "UPDATE DETAILS set DLNA_PN = replace(DLNA_PN, ';DLNA.ORG_OP=01;DLNA.ORG_CI=0', '')");
 239                 if( ret != SQLITE_OK )
 240                         return 8;
 241         }
 242         sql_exec(db, "PRAGMA user_version = %d", DB_VERSION);
 243
 244         return 0;
 245 }