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