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