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