3700871b289ee57a5535faac33bfef6508c5d061
[public/netxms.git] / src / db / libnxdb / util.cpp
1 /*
2 ** NetXMS - Network Management System
3 ** Database Abstraction Library
4 ** Copyright (C) 2003-2015 Victor Kirhenshtein
5 **
6 ** This program is free software; you can redistribute it and/or modify
7 ** it under the terms of the GNU Lesser General Public License as published by
8 ** the Free Software Foundation; either version 3 of the License, or
9 ** (at your option) any later version.
10 **
11 ** This program is distributed in the hope that it will be useful,
12 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
13 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 ** GNU General Public License for more details.
15 **
16 ** You should have received a copy of the GNU Lesser General Public License
17 ** along with this program; if not, write to the Free Software
18 ** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
19 **
20 ** File: util.cpp
21 **
22 **/
23
24 #include "libnxdb.h"
25
26 /**
27 * Check if given record exists in database
28 */
29 bool LIBNXDB_EXPORTABLE IsDatabaseRecordExist(DB_HANDLE hdb, const TCHAR *table, const TCHAR *idColumn, UINT32 id)
30 {
31 bool exist = false;
32
33 TCHAR query[256];
34 _sntprintf(query, 256, _T("SELECT %s FROM %s WHERE %s=?"), idColumn, table, idColumn);
35
36 DB_STATEMENT hStmt = DBPrepare(hdb, query);
37 if (hStmt != NULL)
38 {
39 DBBind(hStmt, 1, DB_SQLTYPE_INTEGER, id);
40 DB_RESULT hResult = DBSelectPrepared(hStmt);
41 if (hResult != NULL)
42 {
43 exist = (DBGetNumRows(hResult) > 0);
44 DBFreeResult(hResult);
45 }
46 DBFreeStatement(hStmt);
47 }
48 return exist;
49 }
50
51 /**
52 * Check if given record exists in database
53 */
54 bool LIBNXDB_EXPORTABLE IsDatabaseRecordExist(DB_HANDLE hdb, const TCHAR *table, const TCHAR *idColumn, const uuid& id)
55 {
56 bool exist = false;
57
58 TCHAR query[256];
59 _sntprintf(query, 256, _T("SELECT %s FROM %s WHERE %s=?"), idColumn, table, idColumn);
60
61 DB_STATEMENT hStmt = DBPrepare(hdb, query);
62 if (hStmt != NULL)
63 {
64 DBBind(hStmt, 1, DB_SQLTYPE_VARCHAR, id);
65 DB_RESULT hResult = DBSelectPrepared(hStmt);
66 if (hResult != NULL)
67 {
68 exist = (DBGetNumRows(hResult) > 0);
69 DBFreeResult(hResult);
70 }
71 DBFreeStatement(hStmt);
72 }
73 return exist;
74 }
75
76 /**
77 * Check if given record exists in database
78 */
79 bool LIBNXDB_EXPORTABLE IsDatabaseRecordExist(DB_HANDLE hdb, const TCHAR *table, const TCHAR *idColumn, const TCHAR *id)
80 {
81 bool exist = false;
82
83 TCHAR query[1256];
84 _sntprintf(query, sizeof(query), _T("SELECT %s FROM %s WHERE %s=?"), idColumn, table, idColumn);
85
86 DB_STATEMENT hStmt = DBPrepare(hdb, query);
87 if (hStmt != NULL)
88 {
89 DBBind(hStmt, 1, DB_SQLTYPE_VARCHAR, id, DB_BIND_STATIC);
90 DB_RESULT hResult = DBSelectPrepared(hStmt);
91 if (hResult != NULL)
92 {
93 exist = (DBGetNumRows(hResult) > 0);
94 DBFreeResult(hResult);
95 }
96 DBFreeStatement(hStmt);
97 }
98 return exist;
99 }
100
101 /**
102 * Characters to be escaped before writing to SQL
103 */
104 static TCHAR m_szSpecialChars[] = _T("\x01\x02\x03\x04\x05\x06\x07\x08")
105 _T("\x09\x0A\x0B\x0C\x0D\x0E\x0F\x10")
106 _T("\x11\x12\x13\x14\x15\x16\x17\x18")
107 _T("\x19\x1A\x1B\x1C\x1D\x1E\x1F")
108 _T("#%\\'\x7F");
109
110 /**
111 * Escape some special characters in string for writing into database.
112 * DEPRECATED!
113 */
114 TCHAR LIBNXDB_EXPORTABLE *EncodeSQLString(const TCHAR *pszIn)
115 {
116 TCHAR *pszOut;
117 int iPosIn, iPosOut, iStrSize;
118
119 if ((pszIn != NULL) && (*pszIn != 0))
120 {
121 // Allocate destination buffer
122 iStrSize = (int)_tcslen(pszIn) + 1;
123 for(iPosIn = 0; pszIn[iPosIn] != 0; iPosIn++)
124 if (_tcschr(m_szSpecialChars, pszIn[iPosIn]) != NULL)
125 iStrSize += 2;
126 pszOut = (TCHAR *)malloc(iStrSize * sizeof(TCHAR));
127
128 // Translate string
129 for(iPosIn = 0, iPosOut = 0; pszIn[iPosIn] != 0; iPosIn++)
130 if (_tcschr(m_szSpecialChars, pszIn[iPosIn]) != NULL)
131 {
132 pszOut[iPosOut++] = _T('#');
133 pszOut[iPosOut++] = bin2hex(pszIn[iPosIn] >> 4);
134 pszOut[iPosOut++] = bin2hex(pszIn[iPosIn] & 0x0F);
135 }
136 else
137 {
138 pszOut[iPosOut++] = pszIn[iPosIn];
139 }
140 pszOut[iPosOut] = 0;
141 }
142 else
143 {
144 // Encode empty strings as #00
145 pszOut = (TCHAR *)malloc(4 * sizeof(TCHAR));
146 _tcscpy(pszOut, _T("#00"));
147 }
148 return pszOut;
149 }
150
151 /**
152 * Restore characters encoded by EncodeSQLString()
153 * Characters are decoded "in place"
154 */
155 void LIBNXDB_EXPORTABLE DecodeSQLString(TCHAR *pszStr)
156 {
157 int iPosIn, iPosOut;
158
159 if (pszStr == NULL)
160 return;
161
162 for(iPosIn = 0, iPosOut = 0; pszStr[iPosIn] != 0; iPosIn++)
163 {
164 if (pszStr[iPosIn] == _T('#'))
165 {
166 iPosIn++;
167 pszStr[iPosOut] = hex2bin(pszStr[iPosIn]) << 4;
168 iPosIn++;
169 pszStr[iPosOut] |= hex2bin(pszStr[iPosIn]);
170 iPosOut++;
171 }
172 else
173 {
174 pszStr[iPosOut++] = pszStr[iPosIn];
175 }
176 }
177 pszStr[iPosOut] = 0;
178 }
179
180 /**
181 * Get database schema version
182 * Will return 0 for unknown and -1 in case of SQL errors
183 */
184 int LIBNXDB_EXPORTABLE DBGetSchemaVersion(DB_HANDLE conn)
185 {
186 DB_RESULT hResult;
187 int version = 0;
188
189 // Read schema version from 'metadata' table, where it should
190 // be stored starting from schema version 87
191 // We ignore SQL error in this case, because table 'metadata'
192 // may not exist in old schema versions
193 hResult = DBSelect(conn, _T("SELECT var_value FROM metadata WHERE var_name='SchemaVersion'"));
194 if (hResult != NULL)
195 {
196 if (DBGetNumRows(hResult) > 0)
197 version = DBGetFieldLong(hResult, 0, 0);
198 DBFreeResult(hResult);
199 }
200
201 // If database schema version is less than 87, version number
202 // will be stored in 'config' table
203 if (version == 0)
204 {
205 hResult = DBSelect(conn, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
206 if (hResult != NULL)
207 {
208 if (DBGetNumRows(hResult) > 0)
209 version = DBGetFieldLong(hResult, 0, 0);
210 DBFreeResult(hResult);
211 }
212 else
213 {
214 version = -1;
215 }
216 }
217
218 return version;
219 }
220
221 /**
222 * Get database syntax
223 */
224 int LIBNXDB_EXPORTABLE DBGetSyntax(DB_HANDLE conn)
225 {
226 DB_RESULT hResult;
227 TCHAR syntaxId[256];
228 bool read = false;
229 int syntax;
230
231 // Get database syntax
232 hResult = DBSelect(conn, _T("SELECT var_value FROM metadata WHERE var_name='Syntax'"));
233 if (hResult != NULL)
234 {
235 if (DBGetNumRows(hResult) > 0)
236 {
237 DBGetField(hResult, 0, 0, syntaxId, sizeof(syntaxId) / sizeof(TCHAR));
238 read = true;
239 }
240 else
241 {
242 _tcscpy(syntaxId, _T("UNKNOWN"));
243 }
244 DBFreeResult(hResult);
245 }
246
247 // If database schema version is less than 87, syntax
248 // will be stored in 'config' table, so try it
249 if (!read)
250 {
251 hResult = DBSelect(conn, _T("SELECT var_value FROM config WHERE var_name='DBSyntax'"));
252 if (hResult != NULL)
253 {
254 if (DBGetNumRows(hResult) > 0)
255 {
256 DBGetField(hResult, 0, 0, syntaxId, sizeof(syntaxId) / sizeof(TCHAR));
257 }
258 else
259 {
260 _tcscpy(syntaxId, _T("UNKNOWN"));
261 }
262 DBFreeResult(hResult);
263 }
264 }
265
266 if (!_tcscmp(syntaxId, _T("MYSQL")))
267 {
268 syntax = DB_SYNTAX_MYSQL;
269 }
270 else if (!_tcscmp(syntaxId, _T("PGSQL")))
271 {
272 syntax = DB_SYNTAX_PGSQL;
273 }
274 else if (!_tcscmp(syntaxId, _T("MSSQL")))
275 {
276 syntax = DB_SYNTAX_MSSQL;
277 }
278 else if (!_tcscmp(syntaxId, _T("ORACLE")))
279 {
280 syntax = DB_SYNTAX_ORACLE;
281 }
282 else if (!_tcscmp(syntaxId, _T("SQLITE")))
283 {
284 syntax = DB_SYNTAX_SQLITE;
285 }
286 else if (!_tcscmp(syntaxId, _T("DB2")))
287 {
288 syntax = DB_SYNTAX_DB2;
289 }
290 else
291 {
292 syntax = DB_SYNTAX_UNKNOWN;
293 }
294
295 return syntax;
296 }