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