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