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