Logo ROOT   6.30.04
Reference Guide
 All Namespaces Files Pages
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 
25 TSQLServer* ROOT_Plugin_TPgSQLServer(const char* db, const char* uid, const char* pw) {
26  return new TPgSQLServer(db, uid, pw);
27 }
28 
29 
30 ClassImp(TPgSQLServer);
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 
38 TPgSQLServer::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 
95 TPgSQLServer::~TPgSQLServer()
96 {
97  if (IsConnected())
98  Close();
99 }
100 
101 ////////////////////////////////////////////////////////////////////////////////
102 /// Close connection to PgSQL DB server.
103 
104 void TPgSQLServer::Close(Option_t *)
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 
118 TSQLResult *TPgSQLServer::Query(const char *sql)
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 
141 Int_t TPgSQLServer::SelectDataBase(const char *dbname)
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 
183 TSQLResult *TPgSQLServer::GetDataBases(const char *wild)
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 
203 TSQLResult *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 
228 TSQLResult *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 
260 Int_t TPgSQLServer::CreateDataBase(const char *dbname)
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 
277 Int_t TPgSQLServer::DropDataBase(const char *dbname)
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 
294 Int_t TPgSQLServer::Reload()
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 
309 Int_t TPgSQLServer::Shutdown()
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 
323 const char *TPgSQLServer::ServerInfo()
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 
338 Bool_t TPgSQLServer::HasStatement() const
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
351 TSQLStatement* TPgSQLServer::Statement(const char *sql, Int_t)
352 #else
353 TSQLStatement* TPgSQLServer::Statement(const char *, Int_t)
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 
389 TSQLTableInfo *TPgSQLServer::GetTableInfo(const char* tablename)
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 }