aboutsummaryrefslogtreecommitdiffstats
path: root/CommonLibs/sqlite3util.cpp
blob: b8984454ad65a498647598d8ee6cab4bd6cebac3 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
/*
* Written by David A. Burgess, Kestrel Signal Processing, Inc., 2010
* The author disclaims copyright to this source code.
*/


#include "sqlite3.h"
#include "sqlite3util.h"

#include <string.h>
#include <unistd.h>
#include <stdio.h>


// Wrappers to sqlite operations.
// These will eventually get moved to commonlibs.

int sqlite3_prepare_statement(sqlite3* DB, sqlite3_stmt **stmt, const char* query)
{
	int prc = sqlite3_prepare_v2(DB,query,strlen(query),stmt,NULL);
	if (prc) {
		fprintf(stderr,"sqlite3_prepare_v2 failed for \"%s\": %s\n",query,sqlite3_errmsg(DB));
		sqlite3_finalize(*stmt);
	}
	return prc;
}

int sqlite3_run_query(sqlite3* DB, sqlite3_stmt *stmt)
{
	int src = SQLITE_BUSY;
	while (src==SQLITE_BUSY) {
		src = sqlite3_step(stmt);
		if (src==SQLITE_BUSY) {
			usleep(100000);
		}
	}
	if ((src!=SQLITE_DONE) && (src!=SQLITE_ROW)) {
		fprintf(stderr,"sqlite3_run_query failed: %s: %s\n", sqlite3_sql(stmt), sqlite3_errmsg(DB));
	}
	return src;
}


bool sqlite3_exists(sqlite3* DB, const char *tableName,
		const char* keyName, const char* keyData)
{
	size_t stringSize = 100 + strlen(tableName) + strlen(keyName) + strlen(keyData);
	char query[stringSize];
	sprintf(query,"SELECT * FROM %s WHERE %s == \"%s\"",tableName,keyName,keyData);
	// Prepare the statement.
	sqlite3_stmt *stmt;
	if (sqlite3_prepare_statement(DB,&stmt,query)) return false;
	// Read the result.
	int src = sqlite3_run_query(DB,stmt);
	sqlite3_finalize(stmt);
	// Anything there?
	return (src == SQLITE_ROW);
}



bool sqlite3_single_lookup(sqlite3* DB, const char *tableName,
		const char* keyName, const char* keyData,
		const char* valueName, unsigned &valueData)
{
	size_t stringSize = 100 + strlen(valueName) + strlen(tableName) + strlen(keyName) + strlen(keyData);
	char query[stringSize];
	sprintf(query,"SELECT %s FROM %s WHERE %s == \"%s\"",valueName,tableName,keyName,keyData);
	// Prepare the statement.
	sqlite3_stmt *stmt;
	if (sqlite3_prepare_statement(DB,&stmt,query)) return false;
	// Read the result.
	int src = sqlite3_run_query(DB,stmt);
	bool retVal = false;
	if (src == SQLITE_ROW) {
		valueData = (unsigned)sqlite3_column_int64(stmt,0);
		retVal = true;
	}
	sqlite3_finalize(stmt);
	return retVal;
}


// This function returns an allocated string that must be free'd by the caller.
bool sqlite3_single_lookup(sqlite3* DB, const char* tableName,
		const char* keyName, const char* keyData,
		const char* valueName, char* &valueData)
{
	valueData=NULL;
	size_t stringSize = 100 + strlen(valueName) + strlen(tableName) + strlen(keyName) + strlen(keyData);
	char query[stringSize];
	sprintf(query,"SELECT %s FROM %s WHERE %s == \"%s\"",valueName,tableName,keyName,keyData);
	// Prepare the statement.
	sqlite3_stmt *stmt;
	if (sqlite3_prepare_statement(DB,&stmt,query)) return false;
	// Read the result.
	int src = sqlite3_run_query(DB,stmt);
	bool retVal = false;
	if (src == SQLITE_ROW) {
		const char* ptr = (const char*)sqlite3_column_text(stmt,0);
		if (ptr) valueData = strdup(ptr);
		retVal = true;
	}
	sqlite3_finalize(stmt);
	return retVal;
}


// This function returns an allocated string that must be free'd by tha caller.
bool sqlite3_single_lookup(sqlite3* DB, const char* tableName,
		const char* keyName, unsigned keyData,
		const char* valueName, char* &valueData)
{
	valueData=NULL;
	size_t stringSize = 100 + strlen(valueName) + strlen(tableName) + strlen(keyName) + 20;
	char query[stringSize];
	sprintf(query,"SELECT %s FROM %s WHERE %s == %u",valueName,tableName,keyName,keyData);
	// Prepare the statement.
	sqlite3_stmt *stmt;
	if (sqlite3_prepare_statement(DB,&stmt,query)) return false;
	// Read the result.
	int src = sqlite3_run_query(DB,stmt);
	bool retVal = false;
	if (src == SQLITE_ROW) {
		const char* ptr = (const char*)sqlite3_column_text(stmt,0);
		if (ptr) valueData = strdup(ptr);
		retVal = true;
	}
	sqlite3_finalize(stmt);
	return retVal;
}




bool sqlite3_command(sqlite3* DB, const char* query)
{
	// Prepare the statement.
	sqlite3_stmt *stmt;
	if (sqlite3_prepare_statement(DB,&stmt,query)) return false;
	// Run the query.
	int src = sqlite3_run_query(DB,stmt);
	return src==SQLITE_DONE;
}