somer DB schema manipulation functions moved to libnxdb from nxdbmgr
[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] = _T("");
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 }
297
298 /**
299 * Rename table
300 */
301 bool LIBNXDB_EXPORTABLE DBRenameTable(DB_HANDLE hdb, const TCHAR *oldName, const TCHAR *newName)
302 {
303 int syntax = DBGetSyntax(hdb);
304
305 TCHAR query[1024];
306 switch(syntax)
307 {
308 case DB_SYNTAX_DB2:
309 case DB_SYNTAX_INFORMIX:
310 case DB_SYNTAX_MYSQL:
311 _sntprintf(query, 1024, _T("RENAME TABLE %s TO %s"), oldName, newName);
312 break;
313 case DB_SYNTAX_ORACLE:
314 case DB_SYNTAX_PGSQL:
315 _sntprintf(query, 1024, _T("ALTER TABLE %s RENAME TO %s"), oldName, newName);
316 break;
317 case DB_SYNTAX_MSSQL:
318 _sntprintf(query, 1024, _T("EXEC sp_rename '%s','%s'"), oldName, newName);
319 break;
320 default: // Unsupported DB engine
321 return false;
322 }
323 return DBQuery(hdb, query);
324 }
325
326 /**
327 * Drop primary key from table
328 */
329 bool LIBNXDB_EXPORTABLE DBDropPrimaryKey(DB_HANDLE hdb, const TCHAR *table)
330 {
331 int syntax = DBGetSyntax(hdb);
332
333 TCHAR query[1024];
334 DB_RESULT hResult;
335 bool success;
336
337 switch(syntax)
338 {
339 case DB_SYNTAX_DB2:
340 case DB_SYNTAX_INFORMIX:
341 case DB_SYNTAX_MYSQL:
342 case DB_SYNTAX_ORACLE:
343 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP PRIMARY KEY"), table);
344 success = DBQuery(hdb, query);
345 break;
346 case DB_SYNTAX_PGSQL:
347 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP CONSTRAINT %s_pkey"), table, table);
348 success = DBQuery(hdb, query);
349 break;
350 case DB_SYNTAX_MSSQL:
351 success = FALSE;
352 _sntprintf(query, 1024, _T("SELECT name FROM sysobjects WHERE xtype='PK' AND parent_obj=OBJECT_ID('%s')"), table);
353 hResult = DBSelect(hdb, query);
354 if (hResult != NULL)
355 {
356 if (DBGetNumRows(hResult) > 0)
357 {
358 TCHAR objName[512];
359
360 DBGetField(hResult, 0, 0, objName, 512);
361 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP CONSTRAINT %s"), table, objName);
362 success = DBQuery(hdb, query);
363 }
364 else
365 {
366 success = true; // No PK to drop
367 }
368 DBFreeResult(hResult);
369 }
370 break;
371 default: // Unsupported DB engine
372 success = false;
373 break;
374 }
375
376 if ((syntax == DB_SYNTAX_DB2) && success)
377 {
378 _sntprintf(query, 1024, _T("CALL Sysproc.admin_cmd('REORG TABLE %s')"), table);
379 success = DBQuery(hdb, query);
380 }
381 return success;
382 }
383
384 /**
385 * Remove NOT NULL constraint from column
386 */
387 bool LIBNXDB_EXPORTABLE DBRemoveNotNullConstraint(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column)
388 {
389 int syntax = DBGetSyntax(hdb);
390
391 TCHAR query[1024] = _T("");
392 switch(syntax)
393 {
394 case DB_SYNTAX_ORACLE:
395 _sntprintf(query, 1024, _T("DECLARE already_null EXCEPTION; ")
396 _T("PRAGMA EXCEPTION_INIT(already_null, -1451); ")
397 _T("BEGIN EXECUTE IMMEDIATE 'ALTER TABLE %s MODIFY %s null'; ")
398 _T("EXCEPTION WHEN already_null THEN null; END;"), table, column);
399 break;
400 case DB_SYNTAX_PGSQL:
401 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL"), table, column);
402 break;
403 default:
404 break;
405 }
406
407 return (query[0] != 0) ? DBQuery(hdb, query) : true;
408 }
409
410 /**
411 * Set NOT NULL constraint on column
412 */
413 bool LIBNXDB_EXPORTABLE DBSetNotNullConstraint(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column)
414 {
415 int syntax = DBGetSyntax(hdb);
416
417 TCHAR query[1024] = _T("");
418 switch(syntax)
419 {
420 case DB_SYNTAX_ORACLE:
421 _sntprintf(query, 1024, _T("DECLARE already_not_null EXCEPTION; ")
422 _T("PRAGMA EXCEPTION_INIT(already_not_null, -1442); ")
423 _T("BEGIN EXECUTE IMMEDIATE 'ALTER TABLE %s MODIFY %s NOT NULL'; ")
424 _T("EXCEPTION WHEN already_not_null THEN null; END;"), table, column);
425 break;
426 case DB_SYNTAX_PGSQL:
427 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s SET NOT NULL"), table, column);
428 break;
429 default:
430 break;
431 }
432
433 return (query[0] != 0) ? DBQuery(hdb, query) : true;
434 }