Logo ROOT  
Reference Guide
 
Loading...
Searching...
No Matches
TPgSQLServer.cxx
Go to the documentation of this file.
1// @(#)root/pgsql:$Id$
2// Author: g.p.ciceri <gp.ciceri@acm.org> 01/06/2001
3
4/*************************************************************************
5 * Copyright (C) 1995-2016, Rene Brun and Fons Rademakers. *
6 * All rights reserved. *
7 * *
8 * For the licensing terms see $ROOTSYS/LICENSE. *
9 * For the list of contributors see $ROOTSYS/README/CREDITS. *
10 *************************************************************************/
11
12#include "TPgSQLServer.h"
13#include "TPgSQLResult.h"
14#include "TPgSQLStatement.h"
15
16#include "TSQLColumnInfo.h"
17#include "TSQLTableInfo.h"
18#include "TSQLRow.h"
19#include "TUrl.h"
20#include "TList.h"
21
22#include <pg_config.h> // to get PG_VERSION_NUM
23
24#define pgsql_success(x) (((x) == PGRES_EMPTY_QUERY) \
25 || ((x) == PGRES_COMMAND_OK) \
26 || ((x) == PGRES_TUPLES_OK))
27
28#include <libpq-fe.h>
29
30////////////////////////////////////////////////////////////////////////////////
31/// PluginManager generator function
32
33TSQLServer* ROOT_Plugin_TPgSQLServer(const char* db, const char* uid, const char* pw) {
34 return new TPgSQLServer(db, uid, pw);
35}
36
37
39
40////////////////////////////////////////////////////////////////////////////////
41/// Open a connection to a PgSQL DB server. The db arguments should be
42/// of the form "pgsql://<host>[:<port>][/<database>]", e.g.:
43/// "pgsql://pcroot.cern.ch:3456/test". The uid is the username and pw
44/// the password that should be used for the connection.
45
46TPgSQLServer::TPgSQLServer(const char *db, const char *uid, const char *pw)
47{
48 fPgSQL = nullptr;
49 fSrvInfo = "";
50
51 TUrl url(db);
52
53 if (!url.IsValid()) {
54 Error("TPgSQLServer", "malformed db argument %s", db);
55 MakeZombie();
56 return;
57 }
58
59 if (strncmp(url.GetProtocol(), "pgsql", 5)) {
60 Error("TPgSQLServer", "protocol in db argument should be pgsql it is %s",
61 url.GetProtocol());
62 MakeZombie();
63 return;
64 }
65
66 const char *dbase = url.GetFile();
67
68 if (url.GetPort()) {
69 TString port;
70 port += url.GetPort();
71 fPgSQL = PQsetdbLogin(url.GetHost(), port.Data(), nullptr, nullptr, dbase, uid, pw);
72 } else {
73 fPgSQL = PQsetdbLogin(url.GetHost(), nullptr, nullptr, nullptr, dbase, uid, pw);
74 }
75
76 if (PQstatus(fPgSQL) != CONNECTION_BAD) {
77 fType = "PgSQL";
78 fHost = url.GetHost();
79 fDB = dbase;
80 fPort = url.GetPort();
81
82 // Populate server-info
83 fSrvInfo = "postgres ";
84 static const char *sql = "select setting from pg_settings where name='server_version'";
85 PGresult *res = PQexec(fPgSQL, sql);
86 int stat = PQresultStatus(res);
87 if (stat == PGRES_TUPLES_OK && PQntuples(res)) {
88 char *vers = PQgetvalue(res,0,0);
89 fSrvInfo += vers;
90 PQclear(res);
91 } else {
92 fSrvInfo += "unknown version number";
93 }
94 } else {
95 Error("TPgSQLServer", "connection to %s failed", url.GetHost());
96 MakeZombie();
97 }
98}
99
100////////////////////////////////////////////////////////////////////////////////
101/// Close connection to PgSQL DB server.
102
104{
105 if (IsConnected())
106 Close();
107}
108
109////////////////////////////////////////////////////////////////////////////////
110/// Close connection to PgSQL DB server.
111
113{
114 if (!fPgSQL)
115 return;
116
117 PQfinish(fPgSQL);
118 fPort = -1;
119}
120
121////////////////////////////////////////////////////////////////////////////////
122/// Execute SQL command. Result object must be deleted by the user.
123/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
124/// The result object must be deleted by the user.
125
127{
128 if (!IsConnected()) {
129 Error("Query", "not connected");
130 return nullptr;
131 }
132
133 PGresult *res = PQexec(fPgSQL, sql);
134
135 if ((PQresultStatus(res) != PGRES_COMMAND_OK) &&
136 (PQresultStatus(res) != PGRES_TUPLES_OK)) {
137 Error("Query", "%s",PQresultErrorMessage(res));
138 PQclear(res);
139 return nullptr;
140 }
141
142 return new TPgSQLResult(res);
143}
144
145////////////////////////////////////////////////////////////////////////////////
146/// Select a database. Returns 0 if successful, non-zero otherwise.
147
149{
150 TString usr;
151 TString pwd;
152 TString port;
153 TString opts;
154
155 if (!IsConnected()) {
156 Error("SelectDataBase", "not connected");
157 return -1;
158 }
159
160 if (dbname == fDB) {
161 return 0;
162 } else {
163 usr = PQuser(fPgSQL);
164 pwd = PQpass(fPgSQL);
165 port = PQport(fPgSQL);
166 opts = PQoptions(fPgSQL);
167
168 Close();
169 fPgSQL = PQsetdbLogin(fHost.Data(), port.Data(),
170 opts.Data(), nullptr, dbname,
171 usr.Data(), pwd.Data());
172
173 if (PQstatus(fPgSQL) == CONNECTION_OK) {
174 fDB=dbname;
175 fPort=port.Atoi();
176 } else {
177 Error("SelectDataBase", "%s",PQerrorMessage(fPgSQL));
178 return -1;
179 }
180 }
181 return 0;
182}
183
184////////////////////////////////////////////////////////////////////////////////
185/// List all available databases. Wild is for wildcarding "t%" list all
186/// databases starting with "t".
187/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
188/// The result object must be deleted by the user.
189
191{
192 if (!IsConnected()) {
193 Error("GetDataBases", "not connected");
194 return nullptr;
195 }
196
197 TString sql = "SELECT pg_database.datname FROM pg_database";
198 if (wild && *wild)
199 sql += TString::Format(" WHERE pg_database.datname LIKE '%s'", wild);
200
201 return Query(sql.Data());
202}
203
204////////////////////////////////////////////////////////////////////////////////
205/// List all tables in the specified database. Wild is for wildcarding
206/// "t%" list all tables starting with "t".
207/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
208/// The result object must be deleted by the user.
209
210TSQLResult *TPgSQLServer::GetTables(const char *dbname, const char *wild)
211{
212 if (!IsConnected()) {
213 Error("GetTables", "not connected");
214 return nullptr;
215 }
216
217 if (SelectDataBase(dbname) != 0) {
218 Error("GetTables", "no such database %s", dbname);
219 return nullptr;
220 }
221
222 TString sql = "SELECT relname FROM pg_class where relkind='r'";
223 if (wild && *wild)
224 sql += TString::Format(" AND relname LIKE '%s'", wild);
225
226 return Query(sql.Data());
227}
228
229////////////////////////////////////////////////////////////////////////////////
230/// List all columns in specified table in the specified database.
231/// Wild is for wildcarding "t%" list all columns starting with "t".
232/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
233/// The result object must be deleted by the user.
234
235TSQLResult *TPgSQLServer::GetColumns(const char *dbname, const char *table,
236 const char *wild)
237{
238 if (!IsConnected()) {
239 Error("GetColumns", "not connected");
240 return nullptr;
241 }
242
243 if (SelectDataBase(dbname) != 0) {
244 Error("GetColumns", "no such database %s", dbname);
245 return nullptr;
246 }
247
248 TString sql;
249 if (wild && *wild)
250 sql.Form("select a.attname,t.typname,a.attnotnull \
251 from pg_attribute a, pg_class c, pg_type t \
252 where c.oid=a.attrelid and c.relname='%s' and \
253 a.atttypid=t.oid and a.attnum>0 \
254 and a.attname like '%s' order by a.attnum ", table, wild);
255 else
256 sql.Form("select a.attname,t.typname,a.attnotnull \
257 from pg_attribute a, pg_class c, pg_type t \
258 where c.oid=a.attrelid and c.relname='%s' and \
259 a.atttypid=t.oid and a.attnum>0 order by a.attnum", table);
260
261 return Query(sql.Data());
262}
263
264////////////////////////////////////////////////////////////////////////////////
265/// Create a database. Returns 0 if successful, non-zero otherwise.
266
268{
269 if (!IsConnected()) {
270 Error("CreateDataBase", "not connected");
271 return -1;
272 }
273 TString sql;
274 sql.Form("CREATE DATABASE %s", dbname);
275 PGresult *res = PQexec(fPgSQL, sql.Data());
276 PQclear(res);
277 return 0;
278}
279
280////////////////////////////////////////////////////////////////////////////////
281/// Drop (i.e. delete) a database. Returns 0 if successful, non-zero
282/// otherwise.
283
285{
286 if (!IsConnected()) {
287 Error("DropDataBase", "not connected");
288 return -1;
289 }
290 TString sql;
291 sql.Form("DROP DATABASE %s", dbname);
292 PGresult *res = PQexec(fPgSQL, sql.Data());
293 PQclear(res);
294 return 0;
295}
296
297////////////////////////////////////////////////////////////////////////////////
298/// Reload permission tables. Returns 0 if successful, non-zero
299/// otherwise. User must have reload permissions.
300
302{
303 if (!IsConnected()) {
304 Error("Reload", "not connected");
305 return -1;
306 }
307
308 Error("Reload", "not implemented");
309 return 0;
310}
311
312////////////////////////////////////////////////////////////////////////////////
313/// Shutdown the database server. Returns 0 if successful, non-zero
314/// otherwise. User must have shutdown permissions.
315
317{
318 if (!IsConnected()) {
319 Error("Shutdown", "not connected");
320 return -1;
321 }
322
323 Error("Shutdown", "not implemented");
324 return 0;
325}
326
327////////////////////////////////////////////////////////////////////////////////
328/// Return server info.
329
331{
332 if (!IsConnected()) {
333 Error("ServerInfo", "not connected");
334 return nullptr;
335 }
336
337 return fSrvInfo.Data();
338}
339
340////////////////////////////////////////////////////////////////////////////////
341/// PG_VERSION_NUM conveniently only started being #%defined at 8.2.3
342/// which is the first version of libpq which explicitly supports prepared
343/// statements
344
346{
347#ifdef PG_VERSION_NUM
348 return kTRUE;
349#else
350 return kFALSE;
351#endif
352}
353
354////////////////////////////////////////////////////////////////////////////////
355/// Produce TPgSQLStatement.
356
357#ifdef PG_VERSION_NUM
359#else
361#endif
362{
363#ifdef PG_VERSION_NUM
364 if (!sql || !*sql) {
365 SetError(-1, "no query string specified","Statement");
366 return nullptr;
367 }
368
369 PgSQL_Stmt_t *stmt = new PgSQL_Stmt_t;
370 if (!stmt){
371 SetError(-1, "cannot allocate PgSQL_Stmt_t", "Statement");
372 return nullptr;
373 }
374 stmt->fConn = fPgSQL;
375 stmt->fRes = PQprepare(fPgSQL, "preparedstmt", sql, 0, (const Oid*)0);
376
377 ExecStatusType stat = PQresultStatus(stmt->fRes);
378 if (pgsql_success(stat)) {
379 fErrorOut = stat;
380 return new TPgSQLStatement(stmt, fErrorOut);
381 } else {
382 SetError(stat, PQresultErrorMessage(stmt->fRes), "Statement");
383 stmt->fConn = nullptr;
384 delete stmt;
385 return nullptr;
386 }
387#else
388 Error("Statement", "not implemented for pgsql < 8.2");
389#endif
390 return nullptr;
391}
392
393////////////////////////////////////////////////////////////////////////////////
394/// Produce TSQLTableInfo.
395
397{
398 if (!IsConnected()) {
399 Error("GetColumns", "not connected");
400 return nullptr;
401 }
402
403 // Check table name
404 if (!tablename || (*tablename==0))
405 return nullptr;
406
407 // Query first row ( works same way as MySQL)
408 PGresult *res = PQexec(fPgSQL, TString::Format("SELECT * FROM %s LIMIT 1;", tablename));
409
410 if ((PQresultStatus(res) != PGRES_COMMAND_OK) &&
411 (PQresultStatus(res) != PGRES_TUPLES_OK)) {
412 Error("Query", "%s",PQresultErrorMessage(res));
413 PQclear(res);
414 return nullptr;
415 }
416
417 if (fOidTypNameMap.empty()) {
418 // Oid-TypNameMap empty, populate it, stays valid at least for connection
419 // lifetime.
420 PGresult *res_type = PQexec(fPgSQL, "SELECT OID, TYPNAME FROM PG_TYPE;");
421
422 if ((PQresultStatus(res_type) != PGRES_COMMAND_OK) &&
423 (PQresultStatus(res_type) != PGRES_TUPLES_OK)) {
424 Error("Query", "%s", PQresultErrorMessage(res_type));
425 PQclear(res);
426 PQclear(res_type);
427 return nullptr;
428 }
429
430 Int_t nOids = PQntuples(res_type);
431 for (Int_t oid=0; oid<nOids; oid++) {
432 Int_t tOid;
433 char* oidString = PQgetvalue(res_type, oid, 0);
434 char* typeString = PQgetvalue(res_type, oid, 1);
435 if (sscanf(oidString, "%10d", &tOid) != 1) {
436 Error("GetTableInfo", "Bad non-numeric oid '%s' for type '%s'", oidString, typeString);
437 }
438 fOidTypNameMap[tOid]=std::string(typeString);
439 }
440 PQclear(res_type);
441 }
442
443 TList * lst = nullptr;
444
445 Int_t nfields = PQnfields(res);
446
447 for (Int_t col=0;col<nfields;col++){
448 Int_t sqltype = kSQL_NONE;
449 Int_t data_size = -1; // size in bytes
450 Int_t data_length = -1; // declaration like VARCHAR(n) or NUMERIC(n)
451 Int_t data_scale = -1; // second argument in declaration
452 Int_t data_sign = -1; // signed type or not
453 Bool_t nullable = 0;
454
455 const char* column_name = PQfname(res,col);
456 const char* type_name;
457 int imod = PQfmod(res,col);
458 //int isize = PQfsize(res,col);
459
460 int oid_code = PQftype(res,col);
461
462 // Search for oid in map
463 std::map<Int_t,std::string>::iterator lookupOid = fOidTypNameMap.find(oid_code);
464 if (lookupOid == fOidTypNameMap.end()) {
465 // Not found.
466 //default
467 sqltype = kSQL_NUMERIC;
468 type_name = "NUMERIC";
469 data_size=-1;
470 } else if (lookupOid->second == "int2"){
471 sqltype = kSQL_INTEGER;
472 type_name = "INT";
473 data_size=2;
474 } else if (lookupOid->second == "int4"){
475 sqltype = kSQL_INTEGER;
476 type_name = "INT";
477 data_size=4;
478 } else if (lookupOid->second == "int8"){
479 sqltype = kSQL_INTEGER;
480 type_name = "INT";
481 data_size=8;
482 } else if (lookupOid->second == "float4"){
483 sqltype = kSQL_FLOAT;
484 type_name = "FLOAT";
485 data_size=4;
486 } else if (lookupOid->second == "float8"){
487 sqltype = kSQL_DOUBLE;
488 type_name = "DOUBLE";
489 data_size=8;
490 } else if (lookupOid->second == "bool"){
491 sqltype = kSQL_INTEGER;
492 type_name = "INT";
493 data_size=1;
494 } else if (lookupOid->second == "char"){
495 sqltype = kSQL_CHAR;
496 type_name = "CHAR";
497 data_size=1;
498 } else if (lookupOid->second == "varchar"){
499 sqltype = kSQL_VARCHAR;
500 type_name = "VARCHAR";
501 data_size=imod;
502 } else if (lookupOid->second == "text"){
503 sqltype = kSQL_VARCHAR;
504 type_name = "VARCHAR";
505 data_size=imod;
506 } else if (lookupOid->second == "name"){
507 sqltype = kSQL_VARCHAR;
508 type_name = "VARCHAR";
509 data_size=imod;
510 } else if (lookupOid->second == "date"){
511 sqltype = kSQL_TIMESTAMP;
512 type_name = "TIMESTAMP";
513 data_size=8;
514 } else if (lookupOid->second == "time"){
515 sqltype = kSQL_TIMESTAMP;
516 type_name = "TIMESTAMP";
517 data_size=8;
518 } else if (lookupOid->second == "timetz"){
519 sqltype = kSQL_TIMESTAMP;
520 type_name = "TIMESTAMP";
521 data_size=8;
522 } else if (lookupOid->second == "timestamp"){
523 sqltype = kSQL_TIMESTAMP;
524 type_name = "TIMESTAMP";
525 data_size=8;
526 } else if (lookupOid->second == "timestamptz"){
527 sqltype = kSQL_TIMESTAMP;
528 type_name = "TIMESTAMP";
529 data_size=8;
530 } else if (lookupOid->second == "interval"){
531 sqltype = kSQL_TIMESTAMP;
532 type_name = "TIMESTAMP";
533 data_size=8;
534 } else if (lookupOid->second == "bytea"){
535 sqltype = kSQL_BINARY;
536 type_name = "BINARY";
537 data_size=-1;
538 } else if (lookupOid->second == ""){
539 sqltype = kSQL_NONE;
540 type_name = "UNKNOWN";
541 data_size=-1;
542 } else{
543 //default
544 sqltype = kSQL_NUMERIC;
545 type_name = "NUMERIC";
546 data_size=-1;
547 }
548
549 if (!lst)
550 lst = new TList;
551
552 lst->Add(new TSQLColumnInfo(column_name,
553 type_name,
554 nullable,
555 sqltype,
556 data_size,
557 data_length,
558 data_scale,
559 data_sign));
560 } //! ( cols)
561
562 PQclear(res);
563 return (new TSQLTableInfo(tablename,lst));
564}
int Int_t
Definition RtypesCore.h:45
constexpr Bool_t kFALSE
Definition RtypesCore.h:101
constexpr Bool_t kTRUE
Definition RtypesCore.h:100
const char Option_t
Definition RtypesCore.h:66
#define ClassImp(name)
Definition Rtypes.h:377
void Error(const char *location, const char *msgfmt,...)
Use this function in case an error occurred.
Definition TError.cxx:185
struct pg_result PGresult
#define pgsql_success(x)
TSQLServer * ROOT_Plugin_TPgSQLServer(const char *db, const char *uid, const char *pw)
PluginManager generator function.
A doubly linked list.
Definition TList.h:38
void Add(TObject *obj) override
Definition TList.h:81
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition TObject.cxx:976
void MakeZombie()
Definition TObject.h:53
TSQLResult * GetTables(const char *dbname, const char *wild=nullptr) final
List all tables in the specified database.
Int_t CreateDataBase(const char *dbname) final
Create a database. Returns 0 if successful, non-zero otherwise.
TSQLResult * Query(const char *sql) final
Execute SQL command.
const char * ServerInfo() final
Return server info.
Int_t SelectDataBase(const char *dbname) final
Select a database. Returns 0 if successful, non-zero otherwise.
Bool_t HasStatement() const final
PG_VERSION_NUM conveniently only started being #defined at 8.2.3 which is the first version of libpq ...
TSQLTableInfo * GetTableInfo(const char *tablename) final
Produce TSQLTableInfo.
std::map< Int_t, std::string > fOidTypNameMap
Int_t Reload() final
Reload permission tables.
Int_t Shutdown() final
Shutdown the database server.
Int_t DropDataBase(const char *dbname) final
Drop (i.e.
TSQLResult * GetDataBases(const char *wild=nullptr) final
List all available databases.
PGconn * fPgSQL
void Close(Option_t *opt="") final
Close connection to PgSQL DB server.
TPgSQLServer(const char *db, const char *uid, const char *pw)
Open a connection to a PgSQL DB server.
TString fSrvInfo
TSQLStatement * Statement(const char *sql, Int_t=100) final
Produce TPgSQLStatement.
~TPgSQLServer()
Close connection to PgSQL DB server.
TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=nullptr) final
List all columns in specified table in the specified database.
TString fHost
Definition TSQLServer.h:45
Int_t fPort
Definition TSQLServer.h:47
TString fDB
Definition TSQLServer.h:46
virtual Bool_t IsConnected() const
Definition TSQLServer.h:93
TString fType
Definition TSQLServer.h:44
Basic string class.
Definition TString.h:139
Int_t Atoi() const
Return integer value of string.
Definition TString.cxx:1988
const char * Data() const
Definition TString.h:376
static TString Format(const char *fmt,...)
Static method which formats a string using a printf style format descriptor and return a TString.
Definition TString.cxx:2378
void Form(const char *fmt,...)
Formats a string using a printf style format descriptor.
Definition TString.cxx:2356
This class represents a WWW compatible URL.
Definition TUrl.h:33
const char * GetFile() const
Definition TUrl.h:69
Bool_t IsValid() const
Definition TUrl.h:79
const char * GetHost() const
Definition TUrl.h:67
const char * GetProtocol() const
Definition TUrl.h:64
Int_t GetPort() const
Definition TUrl.h:78
PGresult * fRes