Implemented DB driver call PrepareString; alarms table converted to new format
[public/netxms.git] / src / server / dbdrv / pgsql / pgsql.cpp
1 /* $Id$ */
2 /*
3 ** PostgreSQL Database Driver
4 ** Copyright (C) 2003 - 2008 Victor Kirhenshtein and Alex Kirhenshtein
5 **
6 ** This program is free software; you can redistribute it and/or modify
7 ** it under the terms of the GNU General Public License as published by
8 ** the Free Software Foundation; either version 2 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 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: pgsql.cpp
21 **
22 **/
23
24 #include "pgsqldrv.h"
25
26 #ifdef _WIN32
27 #pragma warning(disable : 4996)
28 #endif
29
30
31 //
32 // API version
33 //
34
35 extern "C" int EXPORT drvAPIVersion;
36 int EXPORT drvAPIVersion = DBDRV_API_VERSION;
37
38
39 //
40 // Prepare string for using in SQL query - enclose in quotes and escape as needed
41 //
42
43 extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
44 {
45 int len = (int)_tcslen(str) + 3; // + two quotes and \0 at the end
46 int bufferSize = len + 128;
47 TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
48 out[0] = _T('\'');
49
50 const TCHAR *src = str;
51 int outPos;
52 for(outPos = 1; *src != NULL; src++)
53 {
54 if (*src < 32)
55 {
56 TCHAR buffer[8];
57
58 _sntprintf(buffer, 8, _T("\\%03o"), *src);
59 len += 4;
60 if (len >= bufferSize)
61 {
62 bufferSize += 128;
63 out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
64 }
65 memcpy(&out[outPos], buffer, 4 * sizeof(TCHAR));
66 outPos += 4;
67 }
68 else if (*src == _T('\''))
69 {
70 len++;
71 if (len >= bufferSize)
72 {
73 bufferSize += 128;
74 out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
75 }
76 out[outPos++] = _T('\'');
77 out[outPos++] = _T('\'');
78 }
79 else if (*src == _T('\\'))
80 {
81 len++;
82 if (len >= bufferSize)
83 {
84 bufferSize += 128;
85 out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
86 }
87 out[outPos++] = _T('\\');
88 out[outPos++] = _T('\\');
89 }
90 else
91 {
92 out[outPos++] = *src;
93 }
94 }
95 out[outPos++] = _T('\'');
96 out[outPos++] = 0;
97
98 return out;
99 }
100
101
102 //
103 // Initialize driver
104 //
105
106 extern "C" BOOL EXPORT DrvInit(char *szCmdLine)
107 {
108 return TRUE;
109 }
110
111 //
112 // Unload handler
113 //
114
115 extern "C" void EXPORT DrvUnload(void)
116 {
117 }
118
119
120 //
121 // Connect to database
122 //
123
124 extern "C" DB_CONNECTION EXPORT DrvConnect(
125 char *szHost,
126 char *szLogin,
127 char *szPassword,
128 char *szDatabase)
129 {
130 PG_CONN *pConn;
131
132 if (szDatabase == NULL || *szDatabase == 0)
133 {
134 return NULL;
135 }
136
137 pConn = (PG_CONN *)malloc(sizeof(PG_CONN));
138 if (pConn != NULL)
139 {
140 // should be replaced with PQconnectdb();
141 pConn->pHandle = PQsetdbLogin(szHost, NULL, NULL, NULL,
142 szDatabase, szLogin, szPassword);
143
144 if (PQstatus(pConn->pHandle) == CONNECTION_BAD)
145 {
146 free(pConn);
147 pConn = NULL;
148 }
149 else
150 {
151 PGresult *pResult;
152
153 pResult = PQexec(pConn->pHandle, "SET standard_conforming_strings TO off");
154 PQclear(pResult);
155
156 pResult = PQexec(pConn->pHandle, "SET escape_string_warning TO off");
157 PQclear(pResult);
158
159 pConn->mutexQueryLock = MutexCreate();
160 pConn->pFetchBuffer = NULL;
161 }
162 }
163
164 return (DB_CONNECTION)pConn;
165 }
166
167
168 //
169 // Disconnect from database
170 //
171
172 extern "C" void EXPORT DrvDisconnect(DB_CONNECTION pConn)
173 {
174 if (pConn != NULL)
175 {
176 PQfinish(((PG_CONN *)pConn)->pHandle);
177 MutexDestroy(((PG_CONN *)pConn)->mutexQueryLock);
178 free(pConn);
179 }
180 }
181
182
183 //
184 // Perform non-SELECT query
185 //
186
187 static BOOL UnsafeDrvQuery(PG_CONN *pConn, char *szQuery, TCHAR *errorText)
188 {
189 PGresult *pResult;
190
191 pResult = PQexec(pConn->pHandle, szQuery);
192
193 if (pResult == NULL)
194 {
195 if (errorText != NULL)
196 nx_strncpy(errorText, _T("Internal error (pResult is NULL in UnsafeDrvQuery)"), DBDRV_MAX_ERROR_TEXT);
197 return FALSE;
198 }
199
200 if (PQresultStatus(pResult) != PGRES_COMMAND_OK)
201 {
202 if (errorText != NULL)
203 {
204 nx_strncpy(errorText, PQerrorMessage(pConn->pHandle), DBDRV_MAX_ERROR_TEXT);
205 RemoveTrailingCRLF(errorText);
206 }
207 PQclear(pResult);
208 return FALSE;
209 }
210
211 PQclear(pResult);
212 if (errorText != NULL)
213 *errorText = 0;
214 return TRUE;
215 }
216
217 extern "C" DWORD EXPORT DrvQuery(PG_CONN *pConn, WCHAR *pwszQuery, TCHAR *errorText)
218 {
219 DWORD dwRet = DBERR_INVALID_HANDLE;
220 char *pszQueryUTF8;
221
222 if ((pConn != NULL) && (pwszQuery != NULL))
223 {
224 pszQueryUTF8 = UTF8StringFromWideString(pwszQuery);
225 MutexLock(pConn->mutexQueryLock, INFINITE);
226 if (UnsafeDrvQuery(pConn, pszQueryUTF8, errorText))
227 {
228 dwRet = DBERR_SUCCESS;
229 }
230 else
231 {
232 dwRet = (PQstatus(pConn->pHandle) == CONNECTION_BAD) ? DBERR_CONNECTION_LOST : DBERR_OTHER_ERROR;
233 }
234 MutexUnlock(pConn->mutexQueryLock);
235 free(pszQueryUTF8);
236 }
237
238 return dwRet;
239 }
240
241
242 //
243 // Perform SELECT query
244 //
245
246 static DB_RESULT UnsafeDrvSelect(PG_CONN *pConn, char *szQuery, TCHAR *errorText)
247 {
248 PGresult *pResult;
249
250 pResult = PQexec(((PG_CONN *)pConn)->pHandle, szQuery);
251
252 if (pResult == NULL)
253 {
254 if (errorText != NULL)
255 nx_strncpy(errorText, _T("Internal error (pResult is NULL in UnsafeDrvSelect)"), DBDRV_MAX_ERROR_TEXT);
256 return NULL;
257 }
258
259 if ((PQresultStatus(pResult) != PGRES_COMMAND_OK) &&
260 (PQresultStatus(pResult) != PGRES_TUPLES_OK))
261 {
262
263 if (errorText != NULL)
264 {
265 nx_strncpy(errorText, PQerrorMessage(pConn->pHandle), DBDRV_MAX_ERROR_TEXT);
266 RemoveTrailingCRLF(errorText);
267 }
268 PQclear(pResult);
269 return NULL;
270 }
271
272 if (errorText != NULL)
273 *errorText = 0;
274 return (DB_RESULT)pResult;
275 }
276
277 extern "C" DB_RESULT EXPORT DrvSelect(PG_CONN *pConn, WCHAR *pwszQuery, DWORD *pdwError, TCHAR *errorText)
278 {
279 DB_RESULT pResult;
280 char *pszQueryUTF8;
281
282 if (pConn == NULL)
283 {
284 *pdwError = DBERR_INVALID_HANDLE;
285 return NULL;
286 }
287
288 pszQueryUTF8 = UTF8StringFromWideString(pwszQuery);
289 MutexLock(pConn->mutexQueryLock, INFINITE);
290 pResult = UnsafeDrvSelect(pConn, pszQueryUTF8, errorText);
291 if (pResult != NULL)
292 {
293 *pdwError = DBERR_SUCCESS;
294 }
295 else
296 {
297 *pdwError = (PQstatus(pConn->pHandle) == CONNECTION_BAD) ? DBERR_CONNECTION_LOST : DBERR_OTHER_ERROR;
298 }
299 MutexUnlock(pConn->mutexQueryLock);
300 free(pszQueryUTF8);
301
302 return pResult;
303 }
304
305
306 //
307 // Get field length from result
308 //
309
310 extern "C" LONG EXPORT DrvGetFieldLength(DB_RESULT pResult, int nRow, int nColumn)
311 {
312 char *pszValue;
313
314 if (pResult == NULL)
315 return -1;
316
317 pszValue = PQgetvalue((PGresult *)pResult, nRow, nColumn);
318 return (pszValue != NULL) ? (LONG)strlen(pszValue) : (LONG)-1;
319 }
320
321
322 //
323 // Get field value from result
324 //
325
326 extern "C" WCHAR EXPORT *DrvGetField(DB_RESULT pResult, int nRow, int nColumn,
327 WCHAR *pBuffer, int nBufLen)
328 {
329 if (pResult == NULL)
330 return NULL;
331
332 MultiByteToWideChar(CP_UTF8, 0, PQgetvalue((PGresult *)pResult, nRow, nColumn),
333 -1, pBuffer, nBufLen);
334 pBuffer[nBufLen - 1] = 0;
335 return pBuffer;
336 }
337
338
339 //
340 // Get number of rows in result
341 //
342
343 extern "C" int EXPORT DrvGetNumRows(DB_RESULT pResult)
344 {
345 return (pResult != NULL) ? PQntuples((PGresult *)pResult) : 0;
346 }
347
348
349 //
350 // Get column count in query result
351 //
352
353 extern "C" int EXPORT DrvGetColumnCount(DB_RESULT hResult)
354 {
355 return (hResult != NULL) ? PQnfields((PGresult *)hResult) : 0;
356 }
357
358
359 //
360 // Get column name in query result
361 //
362
363 extern "C" const char EXPORT *DrvGetColumnName(DB_RESULT hResult, int column)
364 {
365 return (hResult != NULL) ? PQfname((PGresult *)hResult, column) : NULL;
366 }
367
368
369 //
370 // Free SELECT results
371 //
372
373 extern "C" void EXPORT DrvFreeResult(DB_RESULT pResult)
374 {
375 if (pResult != NULL)
376 {
377 PQclear((PGresult *)pResult);
378 }
379 }
380
381
382 //
383 // Perform asynchronous SELECT query
384 //
385
386 extern "C" DB_ASYNC_RESULT EXPORT DrvAsyncSelect(PG_CONN *pConn, WCHAR *pwszQuery,
387 DWORD *pdwError, TCHAR *errorText)
388 {
389 BOOL bSuccess = FALSE;
390 char *pszReq, *pszQueryUTF8;
391 static char szDeclareCursor[] = "DECLARE cur1 CURSOR FOR ";
392
393 if (pConn == NULL)
394 return NULL;
395
396 MutexLock(pConn->mutexQueryLock, INFINITE);
397
398 if (UnsafeDrvQuery(pConn, "BEGIN", errorText))
399 {
400 pszQueryUTF8 = UTF8StringFromWideString(pwszQuery);
401 pszReq = (char *)malloc(strlen(pszQueryUTF8) + sizeof(szDeclareCursor));
402 if (pszReq != NULL)
403 {
404 strcpy(pszReq, szDeclareCursor);
405 strcat(pszReq, pszQueryUTF8);
406 if (UnsafeDrvQuery(pConn, pszReq, errorText))
407 {
408 bSuccess = TRUE;
409 }
410 free(pszReq);
411 }
412 free(pszQueryUTF8);
413
414 if (!bSuccess)
415 UnsafeDrvQuery(pConn, "ROLLBACK", NULL);
416 }
417
418 if (bSuccess)
419 {
420 *pdwError = DBERR_SUCCESS;
421 }
422 else
423 {
424 *pdwError = (PQstatus(pConn->pHandle) == CONNECTION_BAD) ? DBERR_CONNECTION_LOST : DBERR_OTHER_ERROR;
425 MutexUnlock(pConn->mutexQueryLock);
426 return NULL;
427 }
428
429 return (DB_ASYNC_RESULT)pConn;
430 }
431
432
433 //
434 // Fetch next result line from asynchronous SELECT results
435 //
436
437 extern "C" BOOL EXPORT DrvFetch(DB_ASYNC_RESULT pConn)
438 {
439 BOOL bResult = TRUE;
440
441 if (pConn == NULL)
442 {
443 bResult = FALSE;
444 }
445 else
446 {
447 if (((PG_CONN *)pConn)->pFetchBuffer != NULL)
448 PQclear(((PG_CONN *)pConn)->pFetchBuffer);
449 ((PG_CONN *)pConn)->pFetchBuffer =
450 (PGresult *)UnsafeDrvSelect((PG_CONN *)pConn, "FETCH cur1", NULL);
451 if (((PG_CONN *)pConn)->pFetchBuffer != NULL)
452 {
453 if (DrvGetNumRows(((PG_CONN *)pConn)->pFetchBuffer) <= 0)
454 {
455 PQclear(((PG_CONN *)pConn)->pFetchBuffer);
456 ((PG_CONN *)pConn)->pFetchBuffer = NULL;
457 bResult = FALSE;
458 }
459 }
460 else
461 {
462 bResult = FALSE;
463 }
464 }
465 return bResult;
466 }
467
468
469 //
470 // Get field length from async quety result
471 //
472
473 extern "C" LONG EXPORT DrvGetFieldLengthAsync(PG_CONN *pConn, int nColumn)
474 {
475 if ((pConn == NULL) || (pConn->pFetchBuffer == NULL))
476 {
477 return 0;
478 }
479
480 // validate column index
481 if (nColumn >= PQnfields(pConn->pFetchBuffer))
482 {
483 return 0;
484 }
485
486 char *value = PQgetvalue(pConn->pFetchBuffer, 0, nColumn);
487 if (value == NULL)
488 {
489 return 0;
490 }
491
492 return strlen(value);
493 }
494
495
496 //
497 // Get field from current row in async query result
498 //
499
500 extern "C" WCHAR EXPORT *DrvGetFieldAsync(
501 PG_CONN *pConn,
502 int nColumn,
503 WCHAR *pBuffer,
504 int nBufSize)
505 {
506 char *pszResult;
507
508 if ((pConn == NULL) || (pConn->pFetchBuffer == NULL))
509 {
510 return NULL;
511 }
512
513 // validate column index
514 if (nColumn >= PQnfields(pConn->pFetchBuffer))
515 {
516 return NULL;
517 }
518
519 // FIXME: correct processing of binary fields
520 // PQfformat not supported in 7.3
521 #ifdef HAVE_PQFFORMAT
522 if (PQfformat(pConn->pFetchBuffer, nColumn) != 0)
523 #else
524 if (PQbinaryTuples(pConn->pFetchBuffer) != 0)
525 #endif
526 {
527 //fprintf(stderr, "db:postgres:binary fields not supported\n");
528 //fflush(stderr);
529 // abort();
530 return NULL;
531 }
532
533 pszResult = PQgetvalue(pConn->pFetchBuffer, 0, nColumn);
534 if (pszResult == NULL)
535 {
536 return NULL;
537 }
538
539 // Now get column data
540 MultiByteToWideChar(CP_UTF8, 0, pszResult, -1, pBuffer, nBufSize);
541 pBuffer[nBufSize - 1] = 0;
542
543 return pBuffer;
544 }
545
546
547 //
548 // Get column count in async query result
549 //
550
551 extern "C" int EXPORT DrvGetColumnCountAsync(DB_ASYNC_RESULT hResult)
552 {
553 return ((hResult != NULL) && (((PG_CONN *)hResult)->pFetchBuffer != NULL))? PQnfields(((PG_CONN *)hResult)->pFetchBuffer) : 0;
554 }
555
556
557 //
558 // Get column name in async query result
559 //
560
561 extern "C" const char EXPORT *DrvGetColumnNameAsync(DB_ASYNC_RESULT hResult, int column)
562 {
563 return ((hResult != NULL) && (((PG_CONN *)hResult)->pFetchBuffer != NULL))? PQfname(((PG_CONN *)hResult)->pFetchBuffer, column) : NULL;
564 }
565
566
567 //
568 // Destroy result of async query
569 //
570
571 extern "C" void EXPORT DrvFreeAsyncResult(PG_CONN *pConn)
572 {
573 if (pConn != NULL)
574 {
575 if (pConn->pFetchBuffer != NULL)
576 {
577 PQclear(pConn->pFetchBuffer);
578 pConn->pFetchBuffer = NULL;
579 }
580 UnsafeDrvQuery(pConn, "CLOSE cur1", NULL);
581 UnsafeDrvQuery(pConn, "COMMIT", NULL);
582 }
583 MutexUnlock(pConn->mutexQueryLock);
584 }
585
586
587 //
588 // Begin transaction
589 //
590
591 extern "C" DWORD EXPORT DrvBegin(PG_CONN *pConn)
592 {
593 DWORD dwResult;
594
595 if (pConn == NULL)
596 return DBERR_INVALID_HANDLE;
597
598 MutexLock(pConn->mutexQueryLock, INFINITE);
599 if (UnsafeDrvQuery(pConn, "BEGIN", NULL))
600 {
601 dwResult = DBERR_SUCCESS;
602 }
603 else
604 {
605 dwResult = (PQstatus(pConn->pHandle) == CONNECTION_BAD) ? DBERR_CONNECTION_LOST : DBERR_OTHER_ERROR;
606 }
607 MutexUnlock(pConn->mutexQueryLock);
608 return dwResult;
609 }
610
611
612 //
613 // Commit transaction
614 //
615
616 extern "C" DWORD EXPORT DrvCommit(PG_CONN *pConn)
617 {
618 BOOL bRet;
619
620 if (pConn == NULL)
621 return DBERR_INVALID_HANDLE;
622
623 MutexLock(pConn->mutexQueryLock, INFINITE);
624 bRet = UnsafeDrvQuery(pConn, "COMMIT", NULL);
625 MutexUnlock(pConn->mutexQueryLock);
626 return bRet ? DBERR_SUCCESS : DBERR_OTHER_ERROR;
627 }
628
629
630 //
631 // Rollback transaction
632 //
633
634 extern "C" DWORD EXPORT DrvRollback(PG_CONN *pConn)
635 {
636 BOOL bRet;
637
638 if (pConn == NULL)
639 return DBERR_INVALID_HANDLE;
640
641 MutexLock(pConn->mutexQueryLock, INFINITE);
642 bRet = UnsafeDrvQuery(pConn, "ROLLBACK", NULL);
643 MutexUnlock(pConn->mutexQueryLock);
644 return bRet ? DBERR_SUCCESS : DBERR_OTHER_ERROR;
645 }
646
647
648 //
649 // DLL Entry point
650 //
651
652 #ifdef _WIN32
653
654 BOOL WINAPI DllMain(HINSTANCE hInstance, DWORD dwReason, LPVOID lpReserved)
655 {
656 if (dwReason == DLL_PROCESS_ATTACH)
657 DisableThreadLibraryCalls(hInstance);
658 return TRUE;
659 }
660
661 #endif