1 //////////////////////////////////////////////////////////////////////////
2 //
3 // pgAdmin III - PostgreSQL Tools
4 //
5 // Copyright (C) 2002 - 2016, The pgAdmin Development Team
6 // This software is released under the PostgreSQL Licence
7 //
8 // pgConn.cpp - PostgreSQL Connection class
9 //
10 /////////////////////////////////////////////////////////////////////////
11
12 #include "pgAdmin3.h"
13
14 // wxWindows headers
15 #include <wx/wx.h>
16
17 // PostgreSQL headers
18 #include <libpq-fe.h>
19 #include "utils/pgfeatures.h"
20
21 // Network headers
22 #ifdef __WXMSW__
23 #include <winsock.h>
24 typedef u_long in_addr_t;
25
26 #else
27
28 #include <arpa/inet.h>
29 #include <netdb.h>
30 #include <netinet/in.h>
31
32 #ifndef INADDR_NONE
33 #define INADDR_NONE (-1)
34 #endif
35
36 #endif
37
38 // App headers
39 #include "utils/misc.h"
40 #include "utils/sysLogger.h"
41 #include "db/pgConn.h"
42 #include "utils/misc.h"
43 #include "db/pgSet.h"
44
45 double pgConn::libpqVersion = 8.0;
46
pgNoticeProcessor(void * arg,const char * message)47 static void pgNoticeProcessor(void *arg, const char *message)
48 {
49 ((pgConn *)arg)->Notice(message);
50 }
51
pgConn(const wxString & server,const wxString & service,const wxString & hostaddr,const wxString & database,const wxString & username,const wxString & password,int port,const wxString & rolename,int sslmode,OID oid,const wxString & applicationname,const wxString & sslcert,const wxString & sslkey,const wxString & sslrootcert,const wxString & sslcrl,const bool sslcompression)52 pgConn::pgConn(const wxString &server, const wxString &service, const wxString &hostaddr, const wxString &database, const wxString &username, const wxString &password,
53 int port, const wxString &rolename, int sslmode, OID oid, const wxString &applicationname,
54 const wxString &sslcert, const wxString &sslkey, const wxString &sslrootcert, const wxString &sslcrl,
55 const bool sslcompression) : m_cancelConn(NULL)
56 {
57 wxString msg;
58
59 save_server = server;
60 save_hostaddr = hostaddr;
61 save_service = service;
62 save_database = database;
63 save_username = username;
64 save_password = password;
65 save_port = port;
66 save_rolename = rolename;
67 save_sslmode = sslmode;
68 save_oid = oid;
69 save_applicationname = applicationname;
70 save_sslcert = sslcert;
71 save_sslkey = sslkey;
72 save_sslrootcert = sslrootcert;
73 save_sslcrl = sslcrl;
74 save_sslcompression = sslcompression;
75
76 memset(features, 0, sizeof(features));
77 majorVersion = 0;
78
79 conv = &wxConvLibc;
80 needColQuoting = false;
81 utfConnectString = false;
82
83 // Check the hostname/ipaddress
84 conn = 0;
85 noticeArg = 0;
86 connStatus = PGCONN_BAD;
87
88 // Create the connection string
89 if (!server.IsEmpty())
90 {
91 connstr.Append(wxT(" host="));
92 connstr.Append(qtConnString(server));
93 }
94 if (!hostaddr.IsEmpty())
95 {
96 connstr.Append(wxT(" hostaddr="));
97 connstr.Append(qtConnString(hostaddr));
98 }
99 if (!service.IsEmpty())
100 {
101 connstr.Append(wxT(" service="));
102 connstr.Append(qtConnString(service));
103 }
104 if (!database.IsEmpty())
105 {
106 connstr.Append(wxT(" dbname="));
107 connstr.Append(qtConnString(database));
108 }
109 if (!username.IsEmpty())
110 {
111 connstr.Append(wxT(" user="));
112 connstr.Append(qtConnString(username));
113 }
114 if (!password.IsEmpty())
115 {
116 connstr.Append(wxT(" password="));
117 connstr.Append(qtConnString(password));
118 }
119
120 if (port > 0)
121 {
122 connstr.Append(wxT(" port="));
123 connstr.Append(NumToStr((long)port));
124 }
125
126 if (libpqVersion > 7.3)
127 {
128 switch (sslmode)
129 {
130 case 1:
131 connstr.Append(wxT(" sslmode=require"));
132 break;
133 case 2:
134 connstr.Append(wxT(" sslmode=prefer"));
135 break;
136 case 3:
137 connstr.Append(wxT(" sslmode=allow"));
138 break;
139 case 4:
140 connstr.Append(wxT(" sslmode=disable"));
141 break;
142 case 5:
143 connstr.Append(wxT(" sslmode=verify-ca"));
144 break;
145 case 6:
146 connstr.Append(wxT(" sslmode=verify-full"));
147 break;
148 }
149 }
150 else
151 {
152 switch (sslmode)
153 {
154 case 1:
155 connstr.Append(wxT(" requiressl=1"));
156 break;
157 case 2:
158 connstr.Append(wxT(" requiressl=0"));
159 break;
160 }
161 }
162
163 if (libpqVersion > 8.3 && sslmode != 4)
164 {
165 if (!sslcert.IsEmpty())
166 {
167 connstr.Append(wxT(" sslcert="));
168 connstr.Append(qtConnString(sslcert));
169 }
170 if (!sslkey.IsEmpty())
171 {
172 connstr.Append(wxT(" sslkey="));
173 connstr.Append(qtConnString(sslkey));
174 }
175 if (!sslrootcert.IsEmpty())
176 {
177 connstr.Append(wxT(" sslrootcert="));
178 connstr.Append(qtConnString(sslrootcert));
179 }
180 if (!sslcrl.IsEmpty())
181 {
182 connstr.Append(wxT(" sslcrl="));
183 connstr.Append(qtConnString(sslcrl));
184 }
185 }
186
187 if (libpqVersion > 9.1 && sslmode != 4)
188 {
189 if (!sslcompression)
190 {
191 connstr.Append(wxT(" sslcompression=0"));
192 }
193 }
194
195 connstr.Trim(false);
196
197 dbHost = server;
198 dbHostName = server;
199 dbRole = rolename;
200
201 #ifdef HAVE_CONNINFO_PARSE
202 if (!applicationname.IsEmpty())
203 {
204 // Check connection string with application_name
205 char *errmsg;
206 wxString connstr_with_applicationname = connstr + wxT(" application_name='") + applicationname + wxT("'");
207 if (PQconninfoParse(connstr_with_applicationname.mb_str(wxConvUTF8), &errmsg))
208 {
209 connstr = connstr_with_applicationname;
210 }
211 else if (PQconninfoParse(connstr_with_applicationname.mb_str(wxConvLibc), &errmsg))
212 {
213 connstr = connstr_with_applicationname;
214 }
215 }
216 #endif
217
218 // Open the connection
219 wxString cleanConnStr = connstr;
220 cleanConnStr.Replace(qtConnString(password), wxT("'XXXXXX'"));
221 wxLogInfo(wxT("Opening connection with connection string: %s"), cleanConnStr.c_str());
222
223 DoConnect();
224 }
225
226
~pgConn()227 pgConn::~pgConn()
228 {
229 Close();
230 }
231
232
DoConnect()233 bool pgConn::DoConnect()
234 {
235 wxCharBuffer cstrUTF = connstr.mb_str(wxConvUTF8);
236 conn = PQconnectdb(cstrUTF);
237 if (PQstatus(conn) == CONNECTION_OK)
238 utfConnectString = true;
239 else
240 {
241 wxCharBuffer cstrLibc = connstr.mb_str(wxConvLibc);
242 if (strcmp(cstrUTF, cstrLibc))
243 {
244 PQfinish(conn);
245 conn = PQconnectdb(cstrLibc);
246 }
247 }
248
249 if (!Initialize())
250 return false;
251
252 return true;
253 }
254
255
Initialize()256 bool pgConn::Initialize()
257 {
258 // Set client encoding to Unicode/Ascii, Datestyle to ISO, and ask for notices.
259 if (PQstatus(conn) == CONNECTION_OK)
260 {
261 connStatus = PGCONN_OK;
262 PQsetNoticeProcessor(conn, pgNoticeProcessor, this);
263
264 wxString sql = wxT("SET DateStyle=ISO;\nSET client_min_messages=notice;\n");
265 if (BackendMinimumVersion(9, 0))
266 sql += wxT("SET bytea_output=escape;\n");
267
268 sql += wxT("SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid\n")
269 wxT(" FROM pg_database WHERE ");
270
271 if (save_oid)
272 sql += wxT("oid = ") + NumToStr(save_oid);
273 else
274 {
275 // Note, can't use qtDbString here as we don't know the server version yet.
276 wxString db = save_database;
277 db.Replace(wxT("\\"), wxT("\\\\"));
278 db.Replace(wxT("'"), wxT("''"));
279 sql += wxT("datname=") + qtString(db);
280 }
281
282 pgSet *set = ExecuteSet(sql);
283 if (set)
284 {
285 if (set->ColNumber(wxT("\"datlastsysoid\"")) >= 0)
286 needColQuoting = true;
287
288 lastSystemOID = set->GetOid(wxT("datlastsysoid"));
289 dbOid = set->GetOid(wxT("oid"));
290 wxString encoding = set->GetVal(wxT("encoding"));
291
292 if (encoding != wxT("SQL_ASCII") && encoding != wxT("MULE_INTERNAL"))
293 {
294 encoding = wxT("UNICODE");
295 conv = &wxConvUTF8;
296 }
297 else
298 conv = &wxConvLibc;
299
300 wxLogInfo(wxT("Setting client_encoding to '%s'"), encoding.c_str());
301 if (PQsetClientEncoding(conn, encoding.ToAscii()))
302 {
303 wxLogError(wxT("%s"), GetLastError().c_str());
304 }
305
306 delete set;
307
308 // Switch to the requested default role if supported by backend
309 if (dbRole != wxEmptyString && BackendMinimumVersion(8, 1))
310 {
311 sql = wxT("SET ROLE TO ");
312 sql += qtIdent(dbRole);
313
314 pgSet *set = ExecuteSet(sql);
315
316 if (set)
317 delete set;
318 else
319 return false;
320 }
321 return true;
322 }
323 }
324 return false;
325 }
326
327
Close()328 void pgConn::Close()
329 {
330 if (conn)
331 {
332 CancelExecution();
333 PQfinish(conn);
334 }
335 conn = 0;
336 connStatus = PGCONN_BAD;
337 }
338
339
340 // Reconnect to the server
Reconnect()341 bool pgConn::Reconnect()
342 {
343 // Close the existing (possibly broken) connection
344 Close();
345
346 // Reset any vars that need to be in a defined state before connecting
347 needColQuoting = false;
348
349 // Attempt the reconnect
350 if (!DoConnect())
351 {
352 wxLogError(_("Failed to re-establish the connection to the server %s"), GetName().c_str());
353 return false;
354 }
355
356 return true;
357 }
358
359
Duplicate(const wxString & _appName)360 pgConn *pgConn::Duplicate(const wxString &_appName)
361 {
362 pgConn *res = new pgConn(wxString(save_server), wxString(save_service),
363 wxString(save_hostaddr), wxString(save_database), wxString(save_username),
364 wxString(save_password), save_port, save_rolename, save_sslmode, save_oid,
365 _appName.IsEmpty() ? save_applicationname : _appName, save_sslcert, save_sslkey,
366 save_sslrootcert, save_sslcrl, save_sslcompression);
367
368 // Save the version and features information from the existing connection
369 res->majorVersion = majorVersion;
370 res->minorVersion = minorVersion;
371 res->patchVersion = patchVersion;
372 res->isEdb = isEdb;
373 res->isGreenplum = isGreenplum;
374 res->isHawq = isHawq;
375 res->reservedNamespaces = reservedNamespaces;
376
377 for (size_t index = FEATURE_INITIALIZED; index < FEATURE_LAST; index++)
378 res->features[index] = features[index];
379
380 return res;
381 }
382
383
384 // Return the SSL mode name
GetSslModeName()385 wxString pgConn::GetSslModeName()
386 {
387 switch (save_sslmode)
388 {
389 case 1:
390 return wxT("require");
391 case 2:
392 return wxT("prefer");
393 case 3:
394 return wxT("allow");
395 case 4:
396 return wxT("disable");
397 case 5:
398 return wxT("verify-ca");
399 case 6:
400 return wxT("verify-full");
401 default:
402 return wxT("prefer");
403 }
404 }
405
GetIsEdb()406 bool pgConn::GetIsEdb()
407 {
408 // to retrieve edb flag
409 BackendMinimumVersion(0, 0);
410 return isEdb;
411 }
412
GetIsGreenplum()413 bool pgConn::GetIsGreenplum()
414 {
415 // to retrieve Greenplum flag
416 BackendMinimumVersion(0, 0);
417 return isGreenplum;
418 }
419
GetIsHawq()420 bool pgConn::GetIsHawq()
421 {
422 // to retrieve Greenplum HAWQ flag
423 BackendMinimumVersion(0, 0);
424 return isHawq;
425 }
426
SystemNamespaceRestriction(const wxString & nsp)427 wxString pgConn::SystemNamespaceRestriction(const wxString &nsp)
428 {
429 if (reservedNamespaces.IsEmpty())
430 {
431 reservedNamespaces = wxT("'information_schema'");
432
433 if (GetIsEdb())
434 reservedNamespaces += wxT(", 'sys'");
435
436 pgSet *set = ExecuteSet(
437 wxT("SELECT nspname FROM pg_namespace nsp\n")
438 wxT(" JOIN pg_proc pr ON pronamespace=nsp.oid\n")
439 wxT(" WHERE proname IN ('slonyversion')"));
440 if (set)
441 {
442 while (!set->Eof())
443 {
444 reservedNamespaces += wxT(", ") + qtDbString(set->GetVal(wxT("nspname")));
445 set->MoveNext();
446 }
447 delete set;
448 }
449 }
450
451 if (BackendMinimumVersion(8, 1))
452 return wxT("(") + nsp + wxT(" NOT LIKE E'pg\\_%' AND ") + nsp + wxT(" NOT in (") + reservedNamespaces + wxT("))");
453 else
454 return wxT("(") + nsp + wxT(" NOT LIKE 'pg\\_%' AND ") + nsp + wxT(" NOT in (") + reservedNamespaces + wxT("))");
455 }
456
HasPrivilege(const wxString & objTyp,const wxString & objName,const wxString & priv)457 bool pgConn::HasPrivilege(const wxString &objTyp, const wxString &objName, const wxString &priv)
458 {
459 wxString res = ExecuteScalar(
460 wxT("SELECT has_") + objTyp.Lower()
461 + wxT("_privilege(") + qtDbString(objName)
462 + wxT(", ") + qtDbString(priv) + wxT(")"));
463
464 return StrToBool(res);
465 }
466
IsSuperuser()467 bool pgConn::IsSuperuser()
468 {
469 wxString res = ExecuteScalar(
470 wxT("SELECT rolsuper FROM pg_roles ")
471 wxT("WHERE rolname='") + qtIdent(GetUser()) + wxT("'"));
472
473 return StrToBool(res);
474 }
475
BackendMinimumVersion(int major,int minor)476 bool pgConn::BackendMinimumVersion(int major, int minor)
477 {
478 if (!majorVersion)
479 {
480 wxString version = GetVersionString();
481 sscanf(version.ToAscii(), "%*s %d.%d.%d", &majorVersion, &minorVersion, &patchVersion);
482 isEdb = version.Upper().Matches(wxT("ENTERPRISEDB*"));
483
484 // EnterpriseDB 8.3 beta 1 & 2 and possibly later actually have PostgreSQL 8.2 style
485 // catalogs. This is expected to change either before GA, but in the meantime we
486 // need to check the catalogue version in more detail, and if we don't see what looks
487 // like a 8.3 catalog, force the version number back to 8.2. Yuck.
488 if (isEdb && majorVersion == 8 && minorVersion == 3)
489 {
490 if (ExecuteScalar(wxT("SELECT count(*) FROM pg_attribute WHERE attname = 'proconfig' AND attrelid = 'pg_proc'::regclass")) == wxT("0"))
491 minorVersion = 2;
492 }
493
494 isGreenplum = version.Upper().Matches(wxT("*GREENPLUM DATABASE*"));
495 isHawq = version.Upper().Matches(wxT("*GREENPLUM DATABASE*")) && version.Upper().Matches(wxT("*HAWQ*"));;
496 }
497
498 return majorVersion > major || (majorVersion == major && minorVersion >= minor);
499 }
500
501
502 // Greenplum sometimes adds features in patch releases, because Greenplum
503 // releases are not coordinated with PostgreSQL minor releases.
BackendMinimumVersion(int major,int minor,int patch)504 bool pgConn::BackendMinimumVersion(int major, int minor, int patch)
505 {
506 if (!majorVersion)
507 BackendMinimumVersion(0, 0);
508
509 return majorVersion > major || (majorVersion == major && minorVersion > minor) || (majorVersion == major && minorVersion == minor && patchVersion >= patch);
510 }
511
512
EdbMinimumVersion(int major,int minor)513 bool pgConn::EdbMinimumVersion(int major, int minor)
514 {
515 return BackendMinimumVersion(major, minor) && GetIsEdb();
516 }
517
518
HasFeature(int featureNo,bool forceCheck)519 bool pgConn::HasFeature(int featureNo, bool forceCheck)
520 {
521 if (!features[FEATURE_INITIALIZED] || forceCheck)
522 {
523 features[FEATURE_INITIALIZED] = true;
524
525 wxString sql =
526 wxT("SELECT proname, pronargs, proargtypes[0] AS arg0, proargtypes[1] AS arg1, proargtypes[2] AS arg2\n")
527 wxT(" FROM pg_proc\n")
528 wxT(" JOIN pg_namespace n ON n.oid=pronamespace\n")
529 wxT(" WHERE proname IN ('pg_tablespace_size', 'pg_file_read', 'pg_logfile_rotate',")
530 wxT( " 'pg_postmaster_starttime', 'pg_terminate_backend', 'pg_reload_conf',")
531 wxT( " 'pgstattuple', 'pgstatindex')\n")
532 wxT(" AND nspname IN ('pg_catalog', 'public')");
533
534 pgSet *set = ExecuteSet(sql);
535
536 if (set)
537 {
538 while (!set->Eof())
539 {
540 wxString proname = set->GetVal(wxT("proname"));
541 long pronargs = set->GetLong(wxT("pronargs"));
542
543 if (proname == wxT("pg_tablespace_size") && pronargs == 1 && set->GetLong(wxT("arg0")) == 26)
544 features[FEATURE_SIZE] = true;
545 else if (proname == wxT("pg_file_read") && pronargs == 3 && set->GetLong(wxT("arg0")) == 25
546 && set->GetLong(wxT("arg1")) == 20 && set->GetLong(wxT("arg2")) == 20)
547 features[FEATURE_FILEREAD] = true;
548 else if (proname == wxT("pg_logfile_rotate") && pronargs == 0)
549 features[FEATURE_ROTATELOG] = true;
550 else if (proname == wxT("pg_postmaster_starttime") && pronargs == 0)
551 features[FEATURE_POSTMASTER_STARTTIME] = true;
552 else if (proname == wxT("pg_terminate_backend") && pronargs == 1 && set->GetLong(wxT("arg0")) == 23)
553 features[FEATURE_TERMINATE_BACKEND] = true;
554 else if (proname == wxT("pg_reload_conf") && pronargs == 0)
555 features[FEATURE_RELOAD_CONF] = true;
556 else if (proname == wxT("pgstattuple") && pronargs == 1 && set->GetLong(wxT("arg0")) == 25)
557 features[FEATURE_PGSTATTUPLE] = true;
558 else if (proname == wxT("pgstatindex") && pronargs == 1 && set->GetLong(wxT("arg0")) == 25)
559 features[FEATURE_PGSTATINDEX] = true;
560
561 set->MoveNext();
562 }
563 delete set;
564 }
565
566 // Check for EDB function parameter default support
567 wxString defCol = wxT("'proargdefaults'");
568
569 if (EdbMinimumVersion(8, 3) && !EdbMinimumVersion(8, 4))
570 defCol = wxT("'proargdefvals'");
571
572 wxString hasFuncDefs = ExecuteScalar(wxT("SELECT count(*) FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_proc'::regclass AND attname = ") + defCol);
573 if (hasFuncDefs == wxT("1"))
574 features[FEATURE_FUNCTION_DEFAULTS] = true;
575 else
576 features[FEATURE_FUNCTION_DEFAULTS] = false;
577 }
578
579 if (featureNo <= FEATURE_INITIALIZED || featureNo >= FEATURE_LAST)
580 return false;
581 return features[featureNo];
582 }
583
584
585 // Encrypt a password using the appropriate encoding conversion
EncryptPassword(const wxString & user,const wxString & password)586 wxString pgConn::EncryptPassword(const wxString &user, const wxString &password)
587 {
588 char *chrPassword;
589 wxString strPassword;
590
591 chrPassword = PQencryptPassword(password.mb_str(*conv), user.mb_str(*conv));
592 strPassword = wxString::FromAscii(chrPassword);
593
594 PQfreemem(chrPassword);
595
596 return strPassword;
597 }
598
qtDbString(const wxString & value)599 wxString pgConn::qtDbString(const wxString &value)
600 {
601 wxString result = value;
602
603 result.Replace(wxT("\\"), wxT("\\\\"));
604 result.Replace(wxT("'"), wxT("''"));
605 result.Append(wxT("'"));
606
607 if (BackendMinimumVersion(8, 1))
608 {
609 if (result.Contains(wxT("\\")))
610 result.Prepend(wxT("E'"));
611 else
612 result.Prepend(wxT("'"));
613 }
614 else
615 result.Prepend(wxT("'"));
616
617 return result;
618 }
619
ExamineLibpqVersion()620 void pgConn::ExamineLibpqVersion()
621 {
622 libpqVersion = 7.3;
623 PQconninfoOption *cio = PQconndefaults();
624
625 if (cio)
626 {
627 PQconninfoOption *co = cio;
628 while (co->keyword)
629 {
630 if (!strcmp(co->keyword, "sslmode"))
631 {
632 if (libpqVersion < 7.4)
633 libpqVersion = 7.4;
634 }
635 if (!strcmp(co->keyword, "sslrootcert"))
636 {
637 if (libpqVersion < 8.4)
638 libpqVersion = 8.4;
639 }
640 if (!strcmp(co->keyword, "sslcompression"))
641 {
642 if (libpqVersion < 9.2)
643 libpqVersion = 9.2;
644 }
645 co++;
646 }
647 PQconninfoFree(cio);
648 }
649 }
650
GetName() const651 wxString pgConn::GetName() const
652 {
653 wxString str;
654 if (save_service.IsEmpty())
655 {
656 if (dbHost.IsEmpty())
657 str.Printf(_("%s on local socket"), save_database.c_str());
658 else
659 str.Printf(_("%s on %s@%s:%d"), save_database.c_str(), GetUser().c_str(), dbHost.c_str(), GetPort());
660 }
661 else
662 str.Printf(_("service %s"), save_service.c_str());
663
664
665 return str;
666 }
667
668 #ifdef PG_SSL
669 // we don't define USE_SSL so we don't get ssl.h included
670 extern "C"
671 {
672 extern void *PQgetssl(PGconn *conn);
673 }
674
IsSSLconnected()675 bool pgConn::IsSSLconnected()
676 {
677 return (conn && PQstatus(conn) == CONNECTION_OK && PQgetssl(conn) != NULL);
678 }
679 #else
680
IsSSLconnected()681 bool pgConn::IsSSLconnected()
682 {
683 return false ;
684 }
685 #endif
686
687
RegisterNoticeProcessor(PQnoticeProcessor proc,void * arg)688 void pgConn::RegisterNoticeProcessor(PQnoticeProcessor proc, void *arg)
689 {
690 noticeArg = arg;
691 noticeProc = proc;
692 }
693
694
695
696
Notice(const char * msg)697 void pgConn::Notice(const char *msg)
698 {
699 if (noticeArg && noticeProc)
700 (*noticeProc)(noticeArg, msg);
701 else
702 {
703 wxString str(msg, *conv);
704
705 // Display the notice if required
706 if (settings->GetShowNotices())
707 wxMessageBox(str, _("Notice"), wxICON_INFORMATION | wxOK);
708
709 wxLogNotice(wxT("%s"), str.Trim().c_str());
710 }
711 }
712
713
GetNotification()714 pgNotification *pgConn::GetNotification()
715 {
716 pgNotify *notify;
717
718 notify = PQnotifies(conn);
719 if (!notify)
720 return NULL;
721
722 pgNotification *ret = new pgNotification;
723 ret->name = wxString(notify->relname, *conv);
724 ret->pid = notify->be_pid;
725 ret->data = wxString(notify->extra, *conv);
726
727 return ret;
728 }
729
GetTxStatus()730 int pgConn::GetTxStatus()
731 {
732 return PQtransactionStatus(conn);
733 }
734
735 //////////////////////////////////////////////////////////////////////////
736 // Execute SQL
737 //////////////////////////////////////////////////////////////////////////
738
ExecuteVoid(const wxString & sql,bool reportError)739 bool pgConn::ExecuteVoid(const wxString &sql, bool reportError)
740 {
741 if (GetStatus() != PGCONN_OK)
742 return false;
743
744 // Execute the query and get the status.
745 PGresult *qryRes;
746
747 wxLogSql(wxT("Void query (%s:%d): %s"), this->GetHost().c_str(), this->GetPort(), sql.c_str());
748
749 SetConnCancel();
750 qryRes = PQexec(conn, sql.mb_str(*conv));
751 ResetConnCancel();
752
753 lastResultStatus = PQresultStatus(qryRes);
754 SetLastResultError(qryRes);
755
756 // Check for errors
757 if (lastResultStatus != PGRES_TUPLES_OK &&
758 lastResultStatus != PGRES_COMMAND_OK)
759 {
760 LogError(!reportError);
761 PQclear(qryRes);
762 return false;
763 }
764
765 // Cleanup & exit
766 PQclear(qryRes);
767 return true;
768 }
769
770
771
ExecuteScalar(const wxString & sql,bool reportError)772 wxString pgConn::ExecuteScalar(const wxString &sql, bool reportError)
773 {
774 wxString result;
775
776 if (GetStatus() == PGCONN_OK)
777 {
778 // Execute the query and get the status.
779 PGresult *qryRes;
780 wxLogSql(wxT("Scalar query (%s:%d): %s"), this->GetHost().c_str(), this->GetPort(), sql.c_str());
781
782 SetConnCancel();
783 qryRes = PQexec(conn, sql.mb_str(*conv));
784 ResetConnCancel();
785
786 lastResultStatus = PQresultStatus(qryRes);
787 SetLastResultError(qryRes);
788
789 // Check for errors
790 if (lastResultStatus != PGRES_TUPLES_OK && lastResultStatus != PGRES_COMMAND_OK)
791 {
792 LogError(!reportError);
793 PQclear(qryRes);
794 return wxEmptyString;
795 }
796
797 // Check for a returned row
798 if (PQntuples(qryRes) < 1)
799 {
800 wxLogInfo(wxT("Query returned no tuples"));
801 PQclear(qryRes);
802 return wxEmptyString;
803 }
804
805 // Retrieve the query result and return it.
806 result = wxString(PQgetvalue(qryRes, 0, 0), *conv);
807
808 wxLogSql(wxT("Query result: %s"), result.c_str());
809
810 // Cleanup & exit
811 PQclear(qryRes);
812 }
813
814 return result;
815 }
816
ExecuteSet(const wxString & sql,bool reportError)817 pgSet *pgConn::ExecuteSet(const wxString &sql, bool reportError)
818 {
819 // Execute the query and get the status.
820 if (GetStatus() == PGCONN_OK)
821 {
822 PGresult *qryRes;
823 wxLogSql(wxT("Set query (%s:%d): %s"), this->GetHost().c_str(), this->GetPort(), sql.c_str());
824
825 SetConnCancel();
826 qryRes = PQexec(conn, sql.mb_str(*conv));
827 ResetConnCancel();
828
829 lastResultStatus = PQresultStatus(qryRes);
830 SetLastResultError(qryRes);
831
832 if (lastResultStatus == PGRES_TUPLES_OK || lastResultStatus == PGRES_COMMAND_OK)
833 {
834 pgSet *set = new pgSet(qryRes, this, *conv, needColQuoting);
835 if (!set)
836 {
837 if (reportError)
838 wxLogError(_("Couldn't create a pgSet object!"));
839 else
840 wxLogQuietError(_("Couldn't create a pgSet object!"));
841 PQclear(qryRes);
842 }
843 return set;
844 }
845 else
846 {
847 LogError(!reportError);
848 PQclear(qryRes);
849 }
850 }
851 return new pgSet();
852 }
853
854 //////////////////////////////////////////////////////////////////////////
855 // COPY functions
856 //////////////////////////////////////////////////////////////////////////
857
StartCopy(const wxString query)858 bool pgConn::StartCopy(const wxString query)
859 {
860 if (GetStatus() != PGCONN_OK)
861 return false;
862
863 // Execute the query and get the status
864 PGresult *qryRes;
865
866 wxLogSql(wxT("COPY query (%s:%d): %s"), this->GetHost().c_str(), this->GetPort(), query.c_str());
867 qryRes = PQexec(conn, query.mb_str(*conv));
868 lastResultStatus = PQresultStatus(qryRes);
869 SetLastResultError(qryRes);
870
871 // Check for errors
872 if (lastResultStatus != PGRES_COPY_IN)
873 {
874 LogError(false);
875 PQclear(qryRes);
876 return false;
877 }
878
879 // NO cleanup & exit
880 return true;
881 }
882
PutCopyData(const char * data,long count)883 bool pgConn::PutCopyData(const char *data, long count)
884 {
885 // Execute the query and get the status
886 int result = PQputCopyData(conn, data, count);
887
888 return result == 1;
889 }
890
EndPutCopy(const wxString errormsg)891 bool pgConn::EndPutCopy(const wxString errormsg)
892 {
893 int result;
894
895 // Execute the query and get the status
896 if (errormsg.Length() == 0)
897 result = PQputCopyEnd(conn, NULL);
898 else
899 result = PQputCopyEnd(conn, errormsg.mb_str(*conv));
900
901 return result == 1;
902 }
903
GetCopyFinalStatus(void)904 bool pgConn::GetCopyFinalStatus(void)
905 {
906 PGresult *qryRes;
907
908 // Get status
909 qryRes = PQgetResult(conn);
910 lastResultStatus = PQresultStatus(qryRes);
911
912 // Check for errors
913 if (lastResultStatus != PGRES_COMMAND_OK)
914 {
915 LogError(false);
916 PQclear(qryRes);
917 return false;
918 }
919
920 // Cleanup & exit
921 PQclear(qryRes);
922 return true;
923 }
924
925 //////////////////////////////////////////////////////////////////////////
926 // Info
927 //////////////////////////////////////////////////////////////////////////
928
GetLastError() const929 wxString pgConn::GetLastError() const
930 {
931 wxString errmsg;
932 char *pqErr;
933 if (conn && (pqErr = PQerrorMessage(conn)) != 0)
934 {
935 errmsg = wxString(pqErr, wxConvUTF8);
936 if (errmsg.IsNull())
937 errmsg = wxString(pqErr, wxConvLibc);
938 }
939 else
940 {
941 if (connStatus == PGCONN_BROKEN)
942 errmsg = _("Connection to database broken.");
943 else
944 errmsg = _("No connection to database.");
945 }
946 return errmsg;
947 }
948
949
950
LogError(const bool quiet)951 void pgConn::LogError(const bool quiet)
952 {
953 if (conn)
954 {
955 if (quiet)
956 {
957 wxLogQuietError(wxT("%s"), GetLastError().Trim().c_str());
958 }
959 else
960 {
961 wxLogError(wxT("%s"), GetLastError().Trim().c_str());
962 IsAlive();
963 }
964 }
965 }
966
967
968
IsAlive()969 bool pgConn::IsAlive()
970 {
971 if (GetStatus() != PGCONN_OK)
972 {
973 if (conn)
974 {
975 PQfinish(conn);
976 conn = 0;
977 connStatus = PGCONN_BROKEN;
978 }
979 return false;
980 }
981
982 PGresult *qryRes = PQexec(conn, "SELECT 1;");
983 lastResultStatus = PQresultStatus(qryRes);
984 if (lastResultStatus != PGRES_TUPLES_OK)
985 {
986 PQclear(qryRes);
987 qryRes = PQexec(conn, "ROLLBACK TRANSACTION; SELECT 1;");
988 lastResultStatus = PQresultStatus(qryRes);
989 SetLastResultError(qryRes);
990 }
991 PQclear(qryRes);
992
993 // Check for errors
994 if (lastResultStatus != PGRES_TUPLES_OK)
995 {
996 PQfinish(conn);
997 conn = 0;
998 connStatus = PGCONN_BROKEN;
999 return false;
1000 }
1001
1002 return true;
1003 }
1004
1005
GetStatus() const1006 int pgConn::GetStatus() const
1007 {
1008 if (!this)
1009 return PGCONN_BAD;
1010
1011 if (conn)
1012 ((pgConn *)this)->connStatus = PQstatus(conn);
1013
1014 return connStatus;
1015 }
1016
1017
Reset()1018 void pgConn::Reset()
1019 {
1020 PQreset(conn);
1021
1022 // Reset any vars that need to be in a defined state before connecting
1023 needColQuoting = false;
1024
1025 Initialize();
1026 }
1027
1028
SetConnCancel(void)1029 void pgConn::SetConnCancel(void)
1030 {
1031 wxMutexLocker lock(m_cancelConnMutex);
1032 PGcancel *oldCancelConn = m_cancelConn;
1033
1034 m_cancelConn = NULL;
1035
1036 if (oldCancelConn != NULL)
1037 PQfreeCancel(oldCancelConn);
1038
1039 if (!conn)
1040 return;
1041
1042 m_cancelConn = PQgetCancel(conn);
1043
1044 }
1045
1046
ResetConnCancel(void)1047 void pgConn::ResetConnCancel(void)
1048 {
1049 wxMutexLocker lock(m_cancelConnMutex);
1050 PGcancel *oldCancelConn = m_cancelConn;
1051
1052 m_cancelConn = NULL;
1053
1054 if (oldCancelConn != NULL)
1055 PQfreeCancel(oldCancelConn);
1056 }
1057
1058
CancelExecution(void)1059 void pgConn::CancelExecution(void)
1060 {
1061 char errbuf[256];
1062 wxMutexLocker lock(m_cancelConnMutex);
1063
1064 if (m_cancelConn)
1065 {
1066 PGcancel *cancelConn = m_cancelConn;
1067 m_cancelConn = NULL;
1068
1069 if (PQcancel(cancelConn, errbuf, sizeof(errbuf)))
1070 {
1071 SetLastResultError(NULL, wxT("Cancel request sent"));
1072 }
1073 else
1074 {
1075 SetLastResultError(NULL, wxString::Format(wxT("Could not send cancel request:\n%s"), errbuf));
1076 }
1077 PQfreeCancel(cancelConn);
1078 }
1079 }
1080
1081
GetVersionString()1082 wxString pgConn::GetVersionString()
1083 {
1084 return ExecuteScalar(wxT("SELECT version();"));
1085 }
1086
SetLastResultError(PGresult * res,const wxString & msg)1087 void pgConn::SetLastResultError(PGresult *res, const wxString &msg)
1088 {
1089 if (res)
1090 {
1091 lastResultError.severity = wxString(PQresultErrorField(res, PG_DIAG_SEVERITY), *conv);
1092 lastResultError.sql_state = wxString(PQresultErrorField(res, PG_DIAG_SQLSTATE), *conv);
1093 lastResultError.msg_primary = wxString(PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY), *conv);
1094 lastResultError.msg_detail = wxString(PQresultErrorField(res, PG_DIAG_MESSAGE_DETAIL), *conv);
1095 lastResultError.msg_hint = wxString(PQresultErrorField(res, PG_DIAG_MESSAGE_HINT), *conv);
1096 lastResultError.statement_pos = wxString(PQresultErrorField(res, PG_DIAG_STATEMENT_POSITION), *conv);
1097 lastResultError.internal_pos = wxString(PQresultErrorField(res, PG_DIAG_INTERNAL_POSITION), *conv);
1098 lastResultError.internal_query = wxString(PQresultErrorField(res, PG_DIAG_INTERNAL_QUERY), *conv);
1099 lastResultError.context = wxString(PQresultErrorField(res, PG_DIAG_CONTEXT), *conv);
1100 lastResultError.source_file = wxString(PQresultErrorField(res, PG_DIAG_SOURCE_FILE), *conv);
1101 lastResultError.source_line = wxString(PQresultErrorField(res, PG_DIAG_SOURCE_LINE), *conv);
1102 lastResultError.source_function = wxString(PQresultErrorField(res, PG_DIAG_SOURCE_FUNCTION), *conv);
1103 }
1104 else
1105 {
1106 lastResultError.severity = wxEmptyString;
1107 lastResultError.sql_state = wxEmptyString;
1108 if (msg.IsEmpty())
1109 lastResultError.msg_primary = GetLastError();
1110 else
1111 lastResultError.msg_primary = msg;
1112 lastResultError.msg_detail = wxEmptyString;
1113 lastResultError.msg_hint = wxEmptyString;
1114 lastResultError.statement_pos = wxEmptyString;
1115 lastResultError.internal_pos = wxEmptyString;
1116 lastResultError.internal_query = wxEmptyString;
1117 lastResultError.context = wxEmptyString;
1118 lastResultError.source_file = wxEmptyString;
1119 lastResultError.source_line = wxEmptyString;
1120 lastResultError.source_function = wxEmptyString;
1121 }
1122
1123 wxString errMsg;
1124
1125 if (lastResultError.severity != wxEmptyString && lastResultError.msg_primary != wxEmptyString)
1126 errMsg = lastResultError.severity + wxT(": ") + lastResultError.msg_primary;
1127 else if (lastResultError.msg_primary != wxEmptyString)
1128 errMsg = lastResultError.msg_primary;
1129
1130 if (!lastResultError.sql_state.IsEmpty())
1131 {
1132 if (!errMsg.EndsWith(wxT("\n")))
1133 errMsg += wxT("\n");
1134 errMsg += _("SQL state: ");
1135 errMsg += lastResultError.sql_state;
1136 }
1137
1138 if (!lastResultError.msg_detail.IsEmpty())
1139 {
1140 if (!errMsg.EndsWith(wxT("\n")))
1141 errMsg += wxT("\n");
1142 errMsg += _("Detail: ");
1143 errMsg += lastResultError.msg_detail;
1144 }
1145
1146 if (!lastResultError.msg_hint.IsEmpty())
1147 {
1148 if (!errMsg.EndsWith(wxT("\n")))
1149 errMsg += wxT("\n");
1150 errMsg += _("Hint: ");
1151 errMsg += lastResultError.msg_hint;
1152 }
1153
1154 if (!lastResultError.statement_pos.IsEmpty())
1155 {
1156 if (!errMsg.EndsWith(wxT("\n")))
1157 errMsg += wxT("\n");
1158 errMsg += _("Character: ");
1159 errMsg += lastResultError.statement_pos;
1160 }
1161
1162 if (!lastResultError.context.IsEmpty())
1163 {
1164 if (!errMsg.EndsWith(wxT("\n")))
1165 errMsg += wxT("\n");
1166 errMsg += _("Context: ");
1167 errMsg += lastResultError.context;
1168 }
1169 lastResultError.formatted_msg = errMsg;
1170 }
1171
1172 // String quoting - only for use during the connection phase!!
qtString(const wxString & value)1173 wxString pgConn::qtString(const wxString &value)
1174 {
1175 wxString result = value;
1176
1177 result.Replace(wxT("\\"), wxT("\\\\"));
1178 result.Replace(wxT("'"), wxT("\\'"));
1179 result.Append(wxT("'"));
1180 result.Prepend(wxT("'"));
1181
1182 return result;
1183 }
1184
1185 // Check if, TABLE (tblname) has column with name colname
TableHasColumn(wxString schemaname,wxString tblname,const wxString & colname)1186 bool pgConn::TableHasColumn(wxString schemaname, wxString tblname, const wxString &colname)
1187 {
1188 //
1189 // SELECT a.attname
1190 // FROM pg_catalog.pg_attribute a
1191 // WHERE a.attrelid = (SELECT c.oid
1192 // FROM pg_catalog.pg_class c
1193 // LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1194 // WHERE c.relname ~ '^(TABLENAME)$' AND
1195 // pg_catalog.pg_table_is_visible(c.oid) AND
1196 // n.nspname ~ '^(SCHEMANAME)$') AND
1197 // a.attnum > 0 AND NOT a.attisdropped
1198 // ORDER BY a.attnum
1199 //
1200
1201 if (tblname.IsEmpty() || colname.IsEmpty())
1202 return false;
1203
1204 if (schemaname.IsEmpty())
1205 schemaname = wxT("public");
1206
1207 if (this && GetStatus() == PGCONN_OK)
1208 {
1209 tblname.Replace(wxT("\\"), wxT("\\\\"));
1210 tblname.Replace(wxT("'"), wxT("''"));
1211 schemaname.Replace(wxT("\\"), wxT("\\\\"));
1212 schemaname.Replace(wxT("'"), wxT("''"));
1213
1214 wxString sql
1215 = wxT("SELECT a.attname AS colname FROM pg_catalog.pg_attribute a ") \
1216 wxT("WHERE a.attrelid = (SELECT c.oid FROM pg_catalog.pg_class c ") \
1217 wxT(" LEFT JOIN pg_catalog.pg_namespace n ON ") \
1218 wxT(" n.oid = c.relnamespace ") \
1219 wxT(" WHERE c.relname ~ '^(") + tblname + wxT(")$' AND ") \
1220 wxT(" n.nspname ~ '^(") + schemaname + wxT(")$') AND ") \
1221 wxT(" a.attnum > 0 AND NOT a.attisdropped ") \
1222 wxT("ORDER BY a.attnum");
1223
1224 pgSet *set = ExecuteSet(sql);
1225 if (set)
1226 {
1227 while (!set->Eof())
1228 {
1229 if (set->GetVal(wxT("colname")) == colname)
1230 {
1231 delete set;
1232 return true;
1233 }
1234 set->MoveNext();
1235 }
1236 }
1237 delete set;
1238 }
1239
1240 return false;
1241 }
1242
SetError(PGresult * _res,wxMBConv * _conv)1243 void pgError::SetError(PGresult *_res, wxMBConv *_conv)
1244 {
1245 if (!_conv)
1246 {
1247 _conv = &wxConvLibc;
1248 }
1249 if (_res)
1250 {
1251 msg_primary = wxString(PQresultErrorField(_res, PG_DIAG_MESSAGE_PRIMARY), *_conv);
1252 severity = wxString(PQresultErrorField(_res, PG_DIAG_SEVERITY), *_conv);
1253 sql_state = wxString(PQresultErrorField(_res, PG_DIAG_SQLSTATE), *_conv);
1254 msg_detail = wxString(PQresultErrorField(_res, PG_DIAG_MESSAGE_DETAIL), *_conv);
1255 msg_hint = wxString(PQresultErrorField(_res, PG_DIAG_MESSAGE_HINT), *_conv);
1256 statement_pos = wxString(PQresultErrorField(_res, PG_DIAG_STATEMENT_POSITION), *_conv);
1257 internal_pos = wxString(PQresultErrorField(_res, PG_DIAG_INTERNAL_POSITION), *_conv);
1258 internal_query = wxString(PQresultErrorField(_res, PG_DIAG_INTERNAL_QUERY), *_conv);
1259 context = wxString(PQresultErrorField(_res, PG_DIAG_CONTEXT), *_conv);
1260 source_file = wxString(PQresultErrorField(_res, PG_DIAG_SOURCE_FILE), *_conv);
1261 source_line = wxString(PQresultErrorField(_res, PG_DIAG_SOURCE_LINE), *_conv);
1262 source_function = wxString(PQresultErrorField(_res, PG_DIAG_SOURCE_FUNCTION), *_conv);
1263 }
1264 else
1265 {
1266 msg_primary = wxEmptyString;
1267 severity = wxEmptyString;
1268 sql_state = wxEmptyString;
1269 msg_detail = wxEmptyString;
1270 msg_hint = wxEmptyString;
1271 statement_pos = wxEmptyString;
1272 internal_pos = wxEmptyString;
1273 internal_query = wxEmptyString;
1274 context = wxEmptyString;
1275 source_file = wxEmptyString;
1276 source_line = wxEmptyString;
1277 source_function = wxEmptyString;
1278 }
1279
1280 wxString errMsg;
1281
1282 if (severity != wxEmptyString && msg_primary != wxEmptyString)
1283 errMsg = severity + wxT(": ") + msg_primary;
1284 else if (msg_primary != wxEmptyString)
1285 errMsg = msg_primary;
1286
1287 if (!sql_state.IsEmpty())
1288 {
1289 if (!errMsg.EndsWith(wxT("\n")))
1290 errMsg += wxT("\n");
1291 errMsg += _("SQL state: ");
1292 errMsg += sql_state;
1293 }
1294
1295 if (!msg_detail.IsEmpty())
1296 {
1297 if (!errMsg.EndsWith(wxT("\n")))
1298 errMsg += wxT("\n");
1299 errMsg += _("Detail: ");
1300 errMsg += msg_detail;
1301 }
1302
1303 if (!msg_hint.IsEmpty())
1304 {
1305 if (!errMsg.EndsWith(wxT("\n")))
1306 errMsg += wxT("\n");
1307 errMsg += _("Hint: ");
1308 errMsg += msg_hint;
1309 }
1310
1311 if (!statement_pos.IsEmpty())
1312 {
1313 if (!errMsg.EndsWith(wxT("\n")))
1314 errMsg += wxT("\n");
1315 errMsg += _("Character: ");
1316 errMsg += statement_pos;
1317 }
1318
1319 if (!context.IsEmpty())
1320 {
1321 if (!errMsg.EndsWith(wxT("\n")))
1322 errMsg += wxT("\n");
1323 errMsg += _("Context: ");
1324 errMsg += context;
1325 }
1326 formatted_msg = errMsg;
1327 }
1328