Logo ROOT  
Reference Guide
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
29////////////////////////////////////////////////////////////////////////////////
30/// PluginManager generator function
31
32TSQLServer* ROOT_Plugin_TPgSQLServer(const char* db, const char* uid, const char* pw) {
33 return new TPgSQLServer(db, uid, pw);
34}
35
36
38
39////////////////////////////////////////////////////////////////////////////////
40/// Open a connection to a PgSQL DB server. The db arguments should be
41/// of the form "pgsql://<host>[:<port>][/<database>]", e.g.:
42/// "pgsql://pcroot.cern.ch:3456/test". The uid is the username and pw
43/// the password that should be used for the connection.
44
45TPgSQLServer::TPgSQLServer(const char *db, const char *uid, const char *pw)
46{
47 fPgSQL = 0;
48 fSrvInfo="";
49
50 TUrl url(db);
51
52 if (!url.IsValid()) {
53 Error("TPgSQLServer", "malformed db argument %s", db);
54 MakeZombie();
55 return;
56 }
57
58 if (strncmp(url.GetProtocol(), "pgsql", 5)) {
59 Error("TPgSQLServer", "protocol in db argument should be pgsql it is %s",
60 url.GetProtocol());
61 MakeZombie();
62 return;
63 }
64
65 const char *dbase = url.GetFile();
66
67 if (url.GetPort()) {
68 TString port;
69 port += url.GetPort();
70 fPgSQL = PQsetdbLogin(url.GetHost(), port, 0, 0, dbase, uid, pw);
71 } else {
72 fPgSQL = PQsetdbLogin(url.GetHost(), 0, 0, 0, dbase, uid, pw);
73 }
74
75 if (PQstatus(fPgSQL) != CONNECTION_BAD) {
76 fType = "PgSQL";
77 fHost = url.GetHost();
78 fDB = dbase;
79 fPort = url.GetPort();
80
81 // Populate server-info
82 fSrvInfo = "postgres ";
83 static const char *sql = "select setting from pg_settings where name='server_version'";
84 PGresult *res = PQexec(fPgSQL, sql);
85 int stat = PQresultStatus(res);
86 if (stat == PGRES_TUPLES_OK && PQntuples(res)) {
87 char *vers = PQgetvalue(res,0,0);
88 fSrvInfo += vers;
89 PQclear(res);
90 } else {
91 fSrvInfo += "unknown version number";
92 }
93 } else {
94 Error("TPgSQLServer", "connection to %s failed", url.GetHost());
95 MakeZombie();
96 }
97}
98
99////////////////////////////////////////////////////////////////////////////////
100/// Close connection to PgSQL DB server.
101
103{
104 if (IsConnected())
105 Close();
106}
107
108////////////////////////////////////////////////////////////////////////////////
109/// Close connection to PgSQL DB server.
110
112{
113 if (!fPgSQL)
114 return;
115
116 PQfinish(fPgSQL);
117 fPort = -1;
118}
119
120////////////////////////////////////////////////////////////////////////////////
121/// Execute SQL command. Result object must be deleted by the user.
122/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
123/// The result object must be deleted by the user.
124
126{
127 if (!IsConnected()) {
128 Error("Query", "not connected");
129 return 0;
130 }
131
132 PGresult *res = PQexec(fPgSQL, sql);
133 //cout << " Query called " << sql << ":" << PQntuples(res) << endl;
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 0;
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(), 0, 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 0;
195 }
196
197 TString sql = "SELECT pg_database.datname FROM pg_database";
198 if (wild)
199 sql += Form(" WHERE pg_database.datname LIKE '%s'", wild);
200
201 return Query(sql);
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 0;
215 }
216
217 if (SelectDataBase(dbname) != 0) {
218 Error("GetTables", "no such database %s", dbname);
219 return 0;
220 }
221
222 TString sql = "SELECT relname FROM pg_class where relkind='r'";
223 if (wild)
224 sql += Form(" AND relname LIKE '%s'", wild);
225
226 return Query(sql);
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 0;
241 }
242
243 if (SelectDataBase(dbname) != 0) {
244 Error("GetColumns", "no such database %s", dbname);
245 return 0;
246 }
247
248 char *sql;
249 if (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);
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 char *sql;
274 sql = Form("CREATE DATABASE %s", dbname);
275 PGresult *res = PQexec(fPgSQL, sql);
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 char *sql;
291 sql = Form("DROP DATABASE %s", dbname);
292 PGresult *res = PQexec(fPgSQL, sql);
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 0;
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 0;
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 0;
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 = 0;
384 delete stmt;
385 return 0;
386 }
387#else
388 Error("Statement", "not implemented for pgsql < 8.2");
389#endif
390 return 0;
391}
392
393////////////////////////////////////////////////////////////////////////////////
394/// Produce TSQLTableInfo.
395
397{
398 if (!IsConnected()) {
399 Error("GetColumns", "not connected");
400 return NULL;
401 }
402
403 // Check table name
404 if ((tablename==0) || (*tablename==0)) return 0;
405 // Query first row ( works same way as MySQL)
406 PGresult *res = PQexec(fPgSQL, TString::Format("SELECT * FROM %s LIMIT 1;", tablename));
407
408 if ((PQresultStatus(res) != PGRES_COMMAND_OK) &&
409 (PQresultStatus(res) != PGRES_TUPLES_OK)) {
410 Error("Query", "%s",PQresultErrorMessage(res));
411 PQclear(res);
412 return 0;
413 }
414
415 if (fOidTypNameMap.empty()) {
416 // Oid-TypNameMap empty, populate it, stays valid at least for connection
417 // lifetime.
418 PGresult *res_type = PQexec(fPgSQL, "SELECT OID, TYPNAME FROM PG_TYPE;");
419
420 if ((PQresultStatus(res_type) != PGRES_COMMAND_OK) &&
421 (PQresultStatus(res_type) != PGRES_TUPLES_OK)) {
422 Error("Query", "%s", PQresultErrorMessage(res_type));
423 PQclear(res);
424 PQclear(res_type);
425 return 0;
426 }
427
428 Int_t nOids = PQntuples(res_type);
429 for (Int_t oid=0; oid<nOids; oid++) {
430 Int_t tOid;
431 char* oidString = PQgetvalue(res_type, oid, 0);
432 char* typeString = PQgetvalue(res_type, oid, 1);
433 if (sscanf(oidString, "%10d", &tOid) != 1) {
434 Error("GetTableInfo", "Bad non-numeric oid '%s' for type '%s'", oidString, typeString);
435 }
436 fOidTypNameMap[tOid]=std::string(typeString);
437 }
438 PQclear(res_type);
439 }
440
441 TList * lst = NULL;
442
443 Int_t nfields = PQnfields(res);
444
445 for (Int_t col=0;col<nfields;col++){
446 Int_t sqltype = kSQL_NONE;
447 Int_t data_size = -1; // size in bytes
448 Int_t data_length = -1; // declaration like VARCHAR(n) or NUMERIC(n)
449 Int_t data_scale = -1; // second argument in declaration
450 Int_t data_sign = -1; // signed type or not
451 Bool_t nullable = 0;
452
453 const char* column_name = PQfname(res,col);
454 const char* type_name;
455 int imod = PQfmod(res,col);
456 //int isize = PQfsize(res,col);
457
458 int oid_code = PQftype(res,col);
459
460 // Search for oid in map
461 std::map<Int_t,std::string>::iterator lookupOid = fOidTypNameMap.find(oid_code);
462 if (lookupOid == fOidTypNameMap.end()) {
463 // Not found.
464 //default
465 sqltype = kSQL_NUMERIC;
466 type_name = "NUMERIC";
467 data_size=-1;
468 } else if (lookupOid->second == "int2"){
469 sqltype = kSQL_INTEGER;
470 type_name = "INT";
471 data_size=2;
472 } else if (lookupOid->second == "int4"){
473 sqltype = kSQL_INTEGER;
474 type_name = "INT";
475 data_size=4;
476 } else if (lookupOid->second == "int8"){
477 sqltype = kSQL_INTEGER;
478 type_name = "INT";
479 data_size=8;
480 } else if (lookupOid->second == "float4"){
481 sqltype = kSQL_FLOAT;
482 type_name = "FLOAT";
483 data_size=4;
484 } else if (lookupOid->second == "float8"){
485 sqltype = kSQL_DOUBLE;
486 type_name = "DOUBLE";
487 data_size=8;
488 } else if (lookupOid->second == "bool"){
489 sqltype = kSQL_INTEGER;
490 type_name = "INT";
491 data_size=1;
492 } else if (lookupOid->second == "char"){
493 sqltype = kSQL_CHAR;
494 type_name = "CHAR";
495 data_size=1;
496 } else if (lookupOid->second == "varchar"){
497 sqltype = kSQL_VARCHAR;
498 type_name = "VARCHAR";
499 data_size=imod;
500 } else if (lookupOid->second == "text"){
501 sqltype = kSQL_VARCHAR;
502 type_name = "VARCHAR";
503 data_size=imod;
504 } else if (lookupOid->second == "name"){
505 sqltype = kSQL_VARCHAR;
506 type_name = "VARCHAR";
507 data_size=imod;
508 } else if (lookupOid->second == "date"){
509 sqltype = kSQL_TIMESTAMP;
510 type_name = "TIMESTAMP";
511 data_size=8;
512 } else if (lookupOid->second == "time"){
513 sqltype = kSQL_TIMESTAMP;
514 type_name = "TIMESTAMP";
515 data_size=8;
516 } else if (lookupOid->second == "timetz"){
517 sqltype = kSQL_TIMESTAMP;
518 type_name = "TIMESTAMP";
519 data_size=8;
520 } else if (lookupOid->second == "timestamp"){
521 sqltype = kSQL_TIMESTAMP;
522 type_name = "TIMESTAMP";
523 data_size=8;
524 } else if (lookupOid->second == "timestamptz"){
525 sqltype = kSQL_TIMESTAMP;
526 type_name = "TIMESTAMP";
527 data_size=8;
528 } else if (lookupOid->second == "interval"){
529 sqltype = kSQL_TIMESTAMP;
530 type_name = "TIMESTAMP";
531 data_size=8;
532 } else if (lookupOid->second == "bytea"){
533 sqltype = kSQL_BINARY;
534 type_name = "BINARY";
535 data_size=-1;
536 } else if (lookupOid->second == ""){
537 sqltype = kSQL_NONE;
538 type_name = "UNKNOWN";
539 data_size=-1;
540 } else{
541 //default
542 sqltype = kSQL_NUMERIC;
543 type_name = "NUMERIC";
544 data_size=-1;
545 }
546
547 if (!lst) {
548 lst = new TList;
549 }
550
551 lst->Add(new TSQLColumnInfo(column_name,
552 type_name,
553 nullable,
554 sqltype,
555 data_size,
556 data_length,
557 data_scale,
558 data_sign));
559 } //! ( cols)
560
561 PQclear(res);
562 return (new TSQLTableInfo(tablename,lst));
563}
int Int_t
Definition: RtypesCore.h:45
const Bool_t kFALSE
Definition: RtypesCore.h:101
bool Bool_t
Definition: RtypesCore.h:63
const Bool_t kTRUE
Definition: RtypesCore.h:100
const char Option_t
Definition: RtypesCore.h:66
#define ClassImp(name)
Definition: Rtypes.h:364
void Error(const char *location, const char *msgfmt,...)
Use this function in case an error occurred.
Definition: TError.cxx:187
#define pgsql_success(x)
TSQLServer * ROOT_Plugin_TPgSQLServer(const char *db, const char *uid, const char *pw)
PluginManager generator function.
char * Form(const char *fmt,...)
A doubly linked list.
Definition: TList.h:44
virtual void Add(TObject *obj)
Definition: TList.h:87
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition: TObject.cxx:893
void MakeZombie()
Definition: TObject.h:49
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
Definition: TPgSQLServer.h:27
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
Definition: TPgSQLServer.h:25
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
Definition: TPgSQLServer.h:26
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
@ kSQL_VARCHAR
Definition: TSQLServer.h:63
@ kSQL_TIMESTAMP
Definition: TSQLServer.h:69
@ kSQL_INTEGER
Definition: TSQLServer.h:64
@ kSQL_NUMERIC
Definition: TSQLServer.h:67
Basic string class.
Definition: TString.h:136
Int_t Atoi() const
Return integer value of string.
Definition: TString.cxx:1946
const char * Data() const
Definition: TString.h:369
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:2336
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
PGconn * fConn
PGresult * fRes