function DBDropColumn 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 case DB_SYNTAX_SQLITE:
316 _sntprintf(query, 1024, _T("ALTER TABLE %s RENAME TO %s"), oldName, newName);
317 break;
318 case DB_SYNTAX_MSSQL:
319 _sntprintf(query, 1024, _T("EXEC sp_rename '%s','%s'"), oldName, newName);
320 break;
321 default: // Unsupported DB engine
322 return false;
323 }
324 return DBQuery(hdb, query);
325 }
326
327 /**
328 * Drop primary key from table
329 */
330 bool LIBNXDB_EXPORTABLE DBDropPrimaryKey(DB_HANDLE hdb, const TCHAR *table)
331 {
332 int syntax = DBGetSyntax(hdb);
333
334 TCHAR query[1024];
335 DB_RESULT hResult;
336 bool success;
337
338 switch(syntax)
339 {
340 case DB_SYNTAX_DB2:
341 case DB_SYNTAX_INFORMIX:
342 case DB_SYNTAX_MYSQL:
343 case DB_SYNTAX_ORACLE:
344 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP PRIMARY KEY"), table);
345 success = DBQuery(hdb, query);
346 break;
347 case DB_SYNTAX_PGSQL:
348 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP CONSTRAINT %s_pkey"), table, table);
349 success = DBQuery(hdb, query);
350 break;
351 case DB_SYNTAX_MSSQL:
352 success = FALSE;
353 _sntprintf(query, 1024, _T("SELECT name FROM sysobjects WHERE xtype='PK' AND parent_obj=OBJECT_ID('%s')"), table);
354 hResult = DBSelect(hdb, query);
355 if (hResult != NULL)
356 {
357 if (DBGetNumRows(hResult) > 0)
358 {
359 TCHAR objName[512];
360
361 DBGetField(hResult, 0, 0, objName, 512);
362 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP CONSTRAINT %s"), table, objName);
363 success = DBQuery(hdb, query);
364 }
365 else
366 {
367 success = true; // No PK to drop
368 }
369 DBFreeResult(hResult);
370 }
371 break;
372 default: // Unsupported DB engine
373 success = false;
374 break;
375 }
376
377 if ((syntax == DB_SYNTAX_DB2) && success)
378 {
379 _sntprintf(query, 1024, _T("CALL Sysproc.admin_cmd('REORG TABLE %s')"), table);
380 success = DBQuery(hdb, query);
381 }
382 return success;
383 }
384
385 /**
386 * Add primary key to table. Columns should be passed as comma separated list.
387 */
388 bool LIBNXDB_EXPORTABLE DBAddPrimaryKey(DB_HANDLE hdb, const TCHAR *table, const TCHAR *columns)
389 {
390 int syntax = DBGetSyntax(hdb);
391
392 TCHAR query[1024];
393 bool success;
394 switch(syntax)
395 {
396 case DB_SYNTAX_INFORMIX:
397 _sntprintf(query, 1024, _T("ALTER TABLE %s ADD CONSTRAINT PRIMARY KEY (%s)"), table, columns);
398 success = DBQuery(hdb, query);
399 break;
400 case DB_SYNTAX_DB2:
401 case DB_SYNTAX_MSSQL:
402 case DB_SYNTAX_ORACLE:
403 _sntprintf(query, 1024, _T("ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY (%s)"), table, table, columns);
404 success = DBQuery(hdb, query);
405 break;
406 case DB_SYNTAX_MYSQL:
407 case DB_SYNTAX_PGSQL:
408 _sntprintf(query, 1024, _T("ALTER TABLE %s ADD PRIMARY KEY (%s)"), table, columns);
409 success = DBQuery(hdb, query);
410 break;
411 default: // Unsupported DB engine
412 success = false;
413 break;
414 }
415
416 if ((syntax == DB_SYNTAX_DB2) && success)
417 {
418 _sntprintf(query, 1024, _T("CALL Sysproc.admin_cmd('REORG TABLE %s')"), table);
419 success = DBQuery(hdb, query);
420 }
421 return success;
422 }
423
424 /**
425 * Remove NOT NULL constraint from column
426 */
427 bool LIBNXDB_EXPORTABLE DBRemoveNotNullConstraint(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column)
428 {
429 int syntax = DBGetSyntax(hdb);
430
431 TCHAR query[1024] = _T("");
432 switch(syntax)
433 {
434 case DB_SYNTAX_ORACLE:
435 _sntprintf(query, 1024, _T("DECLARE already_null EXCEPTION; ")
436 _T("PRAGMA EXCEPTION_INIT(already_null, -1451); ")
437 _T("BEGIN EXECUTE IMMEDIATE 'ALTER TABLE %s MODIFY %s null'; ")
438 _T("EXCEPTION WHEN already_null THEN null; END;"), table, column);
439 break;
440 case DB_SYNTAX_PGSQL:
441 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL"), table, column);
442 break;
443 default:
444 break;
445 }
446
447 return (query[0] != 0) ? DBQuery(hdb, query) : true;
448 }
449
450 /**
451 * Set NOT NULL constraint on column
452 */
453 bool LIBNXDB_EXPORTABLE DBSetNotNullConstraint(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column)
454 {
455 int syntax = DBGetSyntax(hdb);
456
457 TCHAR query[1024] = _T("");
458 switch(syntax)
459 {
460 case DB_SYNTAX_ORACLE:
461 _sntprintf(query, 1024, _T("DECLARE already_not_null EXCEPTION; ")
462 _T("PRAGMA EXCEPTION_INIT(already_not_null, -1442); ")
463 _T("BEGIN EXECUTE IMMEDIATE 'ALTER TABLE %s MODIFY %s NOT NULL'; ")
464 _T("EXCEPTION WHEN already_not_null THEN null; END;"), table, column);
465 break;
466 case DB_SYNTAX_PGSQL:
467 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s SET NOT NULL"), table, column);
468 break;
469 default:
470 break;
471 }
472
473 return (query[0] != 0) ? DBQuery(hdb, query) : true;
474 }
475
476 /**
477 * Resize varchar column
478 */
479 bool LIBNXDB_EXPORTABLE DBResizeColumn(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column, int newSize, bool nullable)
480 {
481 int syntax = DBGetSyntax(hdb);
482
483 TCHAR query[1024];
484 switch(syntax)
485 {
486 case DB_SYNTAX_DB2:
487 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s SET DATA TYPE varchar(%d)"), table, column, newSize);
488 break;
489 case DB_SYNTAX_MSSQL:
490 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s varchar(%d) %s NULL"), table, column, newSize, nullable ? _T("") : _T("NOT"));
491 break;
492 case DB_SYNTAX_PGSQL:
493 _sntprintf(query, 1024, _T("ALTER TABLE %s ALTER COLUMN %s TYPE varchar(%d)"), table, column, newSize);
494 break;
495 case DB_SYNTAX_SQLITE:
496 /* TODO: add SQLite support */
497 query[0] = 0;
498 break;
499 default:
500 _sntprintf(query, 1024, _T("ALTER TABLE %s MODIFY %s varchar(%d)"), table, column, newSize);
501 break;
502 }
503
504 return (query[0] != 0) ? DBQuery(hdb, query) : true;
505 }
506
507 /**
508 * Drop column from the table
509 */
510 bool LIBNXDB_EXPORTABLE DBDropColumn(DB_HANDLE hdb, const TCHAR *table, const TCHAR *column)
511 {
512 int syntax = DBGetSyntax(hdb);
513
514 TCHAR query[1024];
515 bool success = false;
516 if (syntax != DB_SYNTAX_SQLITE)
517 {
518 _sntprintf(query, 1024, _T("ALTER TABLE %s DROP COLUMN %s"), table, column);
519 success = DBQuery(hdb, query);
520 if (syntax == DB_SYNTAX_DB2)
521 {
522 _sntprintf(query, 1024, _T("CALL Sysproc.admin_cmd('REORG TABLE %s')"), table);
523 success = DBQuery(hdb, query);
524 }
525 }
526 else
527 {
528 _sntprintf(query, 1024, _T("PRAGMA TABLE_INFO('%s')"), table);
529 DB_RESULT hResult = DBSelect(hdb, query);
530 if (hResult != NULL)
531 {
532 int rows = DBGetNumRows(hResult);
533 const int blen = 2048;
534 TCHAR buffer[blen];
535 // Intermediate buffers for SQLs
536 TCHAR columnList[1024], createList[1024];
537 // TABLE_INFO() columns
538 TCHAR tabColName[128], tabColType[64], tabColNull[10], tabColDefault[128];
539 columnList[0] = createList[0] = _T('\0');
540 for (int i = 0; i < rows; i++)
541 {
542 DBGetField(hResult, i, 1, tabColName, 128);
543 DBGetField(hResult, i, 2, tabColType, 64);
544 DBGetField(hResult, i, 3, tabColNull, 10);
545 DBGetField(hResult, i, 4, tabColDefault, 128);
546 if (_tcsnicmp(tabColName, column, 128))
547 {
548 _tcscat(columnList, tabColName);
549 if (columnList[0] != _T('\0'))
550 _tcscat(columnList, _T(","));
551 _tcscat(createList, tabColName);
552 _tcscat(createList, tabColType);
553 if (tabColDefault[0] != _T('\0'))
554 {
555 _tcscat(createList, _T("DEFAULT "));
556 _tcscat(createList, tabColDefault);
557 }
558 if (tabColNull[0] == _T('1'))
559 _tcscat(createList, _T(" NOT NULL"));
560 _tcscat(createList, _T(","));
561 }
562 }
563 DBFreeResult(hResult);
564 if (rows > 0)
565 {
566 int cllen = (int)_tcslen(columnList);
567 if (cllen > 0 && columnList[cllen - 1] == _T(','))
568 columnList[cllen - 1] = _T('\0');
569 // TODO: figure out if SQLite transactions will work here
570 _sntprintf(buffer, blen, _T("CREATE TABLE %s__backup__ (%s)"), table, columnList);
571 success = DBQuery(hdb, buffer);
572 if (success)
573 {
574 _sntprintf(buffer, blen, _T("INSERT INTO %s__backup__ (%s) SELECT %s FROM %s"),
575 table, columnList, columnList, table);
576 success = DBQuery(hdb, buffer);
577 }
578 if (success)
579 {
580 _sntprintf(buffer, blen, _T("DROP TABLE %s"), table);
581 success = DBQuery(hdb, buffer);
582 }
583 if (success)
584 {
585 _sntprintf(buffer, blen, _T("ALTER TABLE %s__backup__ RENAME TO %s"), table, table);
586 success = DBQuery(hdb, buffer);
587 }
588 }
589 }
590 // TODO: preserve indices and constraints??
591 }
592
593 return success;
594 }