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 26int 27sql_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 50int 51sql_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 68int 69sql_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 127int64_t 128sql_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 186char * 187sql_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 262int 263db_upgrade(sqlite3 *db) 264{ 265 int db_vers; 266 267 db_vers = sql_get_int_field(db, "PRAGMA user_version"); 268 269 if (db_vers == DB_VERSION) 270 return 0; 271 if (db_vers > DB_VERSION) 272 return -2; 273 if (db_vers < 1) 274 return -1; 275 if (db_vers < 9) 276 return db_vers; 277 sql_exec(db, "PRAGMA user_version = %d", DB_VERSION); 278 279 return 0; 280} 281