fgms 0.11.8
The
FlightGear MultiPlayer Server
project
testdb.c
Go to the documentation of this file.
1 /*
2  * testdb.c - test a connection to the prostgresql database
3  *
4  * Author: Geoff R. McLane <reports@geoffair.info>
5  * License: GPL
6  *
7  * Revision 0.1 2012/07/02 geoff
8  * Initial cut. Some testing routines, searching alternatives
9  * Some code cut from : http://www.postgresql.org/docs/8.0/static/libpq-example.html
10  *
11  */
12 
13 #include "common.h"
14 #include "wrappers.h"
15 #include "error.h"
16 #include <libpq-fe.h>
17 
18 /* DEFAULT DATABASE INFORMATION IF NOT GIVEN ON THE COMMAND LINE */
19 
20 #ifndef DEF_IP_ADDRESS
21 #define DEF_IP_ADDRESS "192.168.1.105"
22 #endif
23 
24 #ifndef DEF_PORT
25 #define DEF_PORT "5432"
26 #endif
27 
28 #ifndef DEF_DATABASE
29 #define DEF_DATABASE "fgtracker"
30 #endif
31 
32 #ifndef DEF_USER_LOGIN
33 #define DEF_USER_LOGIN "fgtracker"
34 #endif
35 
36 #ifndef DEF_USER_PWD
37 #define DEF_USER_PWD "fgtracker"
38 #endif
39 
40 static char *ip_address = (char *)DEF_IP_ADDRESS;
41 static char *port = (char *)DEF_PORT;
42 static char *database = (char *)DEF_DATABASE;
43 static char *user = (char *)DEF_USER_LOGIN;
44 static char *pwd = (char *)DEF_USER_PWD;
45 static char *pgoptions = (char *)"";
46 static char *pgtty = (char *)"";
47 
48 static int got_flights = 0;
49 static int got_waypts = 0;
50 
51 #define PQ_EXEC_SUCCESS(res) ((PQresultStatus(res) == PGRES_COMMAND_OK)||(PQresultStatus(res) == PGRES_TUPLES_OK))
52 
53 
54 /* --------------------------------------
55 PGconn *PQsetdbLogin(const char *pghost,
56  const char *pgport,
57  const char *pgoptions,
58  const char *pgtty,
59  const char *dbName,
60  const char *login,
61  const char *pwd);
62  ------------------------------------- */
63 
64 static void exit_nicely(PGconn* conn)
65 {
66  PQfinish(conn);
67  exit(1);
68 }
69 
70 void test_template1(void)
71 {
72  char *pghost, *pgport, *pgoptions, *pgtty;
73  char* dbName;
74  int nFields;
75  int i,j;
76 
77  /* FILE *debug; */
78 
79  PGconn* conn;
80  PGresult* res;
81 
82  /* begin, by setting the parameters for a backend connection
83  if the parameters are null, then the system will try to use
84  reasonable defaults by looking up environment variables
85  or, failing that, using hardwired constants */
86  pghost = NULL; /* host name of the backend server */
87  pgport = NULL; /* port of the backend server */
88  pgoptions = NULL; /* special options to start up the backend server */
89  pgtty = NULL; /* debugging tty for the backend server */
90  dbName = "template1";
91 
92  /* make a connection to the database */
93  conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
94 
95  /* check to see that the backend connection was successfully made */
96  if (PQstatus(conn) == CONNECTION_BAD) {
97  fprintf(stderr,"Connection to database '%s' failed. ", dbName);
98  fprintf(stderr,"[%s]\n",PQerrorMessage(conn));
99  exit_nicely(conn);
100  }
101 
102  /* debug = fopen("/tmp/trace.out","w"); */
103  /* PQtrace(conn, debug); */
104 
105  /* start a transaction block */
106 
107  res = PQexec(conn,"BEGIN");
108  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
109  fprintf(stderr,"BEGIN command failed!\n");
110  PQclear(res);
111  exit_nicely(conn);
112  }
113  /* should PQclear PGresult whenever it is no longer needed to avoid
114  memory leaks */
115  PQclear(res);
116 
117  /* fetch instances from the pg_database, the system catalog of databases*/
118  res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database");
119  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
120  fprintf(stderr,"DECLARE CURSOR command failed!\n");
121  PQclear(res);
122  exit_nicely(conn);
123  }
124  PQclear(res);
125 
126  res = PQexec(conn,"FETCH ALL in myportal");
127  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
128  fprintf(stderr,"FETCH ALL command didn't return tuples properly\n");
129  PQclear(res);
130  exit_nicely(conn);
131  }
132 
133  /* first, print out the attribute names */
134  nFields = PQnfields(res);
135  for (i=0; i < nFields; i++) {
136  printf("%-15s",PQfname(res,i));
137  }
138  printf("\n");
139 
140  /* next, print out the instances */
141  for (i=0; i < PQntuples(res); i++) {
142  for (j=0 ; j < nFields; j++) {
143  printf("%-15s", PQgetvalue(res,i,j));
144  }
145  printf("\n");
146  }
147 
148  PQclear(res);
149 
150  /* close the portal */
151  res = PQexec(conn, "CLOSE myportal");
152  PQclear(res);
153 
154  /* end the transaction */
155  res = PQexec(conn, "END");
156  PQclear(res);
157 
158  /* close the connection to the database and cleanup */
159  PQfinish(conn);
160 
161  /* fclose(debug); */
162 }
163 
164 
165 void query_tables(PGconn *conn, int verb)
166 {
167  PGresult *res;
168  char buff[MAXLINE];
169  char *cp = buff;
170  char *val;
171  int i, j, i2, nFields, nRows;
172 
173  if (PQstatus(conn) == CONNECTION_OK) {
174  res = PQexec(conn,"BEGIN");
175  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
176  fprintf(stderr,"BEGIN command failed!\n");
177  PQclear(res);
178  return;
179  }
180  /* should PQclear PGresult whenever it is no longer needed to avoid memory leaks */
181  PQclear(res);
182  strcpy(cp,"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';");
183  res = PQexec(conn, cp);
184  if (PQ_EXEC_SUCCESS(res)) {
185  nFields = PQnfields(res);
186  nRows = PQntuples(res);
187  printf("PQexec(%s) succeeded... %d fields, %d rows\n",cp,nFields,nRows);
188  if (verb) {
189  if (nFields > 1) {
190  printf("List of %d fields...\n",nFields);
191  for (i = 0; i < nFields; i++) {
192  printf("%s ", PQfname(res, i));
193  }
194  printf("\n");
195  }
196  /* next, print out the rows */
197  for (j = 0; j < nFields; j++) {
198  printf("Field: [%s]\n", PQfname(res, j));
199  for (i = 0; i < nRows; i++) {
200  i2 = i + 1;
201  val = PQgetvalue(res, i, j);
202  if (val) {
203  printf(" Row %3d: [%s]\n", i2, val);
204  if (strcmp(val,"flights") == 0)
205  got_flights = 1;
206  else if (strcmp(val,"waypoints") == 0)
207  got_waypts = 1;
208 
209  } else
210  printf(" Row %3d: [%s]\n", i2, "<null>");
211  }
212  }
213  }
214  } else {
215  printf("PQexec(%s) FAILED: [%s]\n",cp, PQerrorMessage(conn));
216  }
217  PQclear(res);
218  /* end the transaction */
219  res = PQexec(conn, "END");
220  PQclear(res);
221  } else {
222  printf("ERROR: connection to database not valid: [%s]\n", PQerrorMessage(conn) );
223  }
224 }
225 
226 
228 {
229  int iret = 0; // assume success
230  PGconn *conn = NULL;
231  char buff[MAXLINE];
232  char *cp = buff;
233  sprintf(cp,"hostaddr=%s port=%s dbname=%s user=%s password=%s",
235  conn = PQconnectdb(cp);
236  if (PQstatus(conn) == CONNECTION_OK) {
237  printf("Alternate connection [%s] was also successful.\n",cp);
238  query_tables(conn,0); // again show the tables, but not all repeated info
239  } else {
240  printf("ERROR: connection not valid: %s\n", PQerrorMessage(conn) );
241  iret = 1;
242  }
243  PQfinish(conn);
244  return iret;
245 }
246 
247 int ConnectDB(PGconn **conn)
248 {
249  int iret = 0; /* assume no error */
250  // char debugstr[MAXLINE];
251 
252  *conn = PQsetdbLogin(ip_address, port, pgoptions, pgtty, database, user, pwd);
253 
254  if (PQstatus(*conn) != CONNECTION_OK)
255  {
256  printf("ERROR: Connection to database failed: %s\n", PQerrorMessage(*conn) );
257  //debug(1,debugstr);
258  PQfinish(*conn);
259  iret = 1;
260  }
261  return iret;
262 }
263 
264 char *get_base_name(char *name)
265 {
266  char *bn = strdup(name);
267  size_t len = strlen(bn);
268  size_t i, off;
269  int c;
270  off = 0;
271  for (i = 0; i < len; i++) {
272  c = bn[i];
273  if (( c == '/' )||( c == '\\' ))
274  off = i + 1;
275  }
276  return &bn[off];
277 }
278 
279 void give_help(char *name)
280 {
281  char *bn = get_base_name(name);
282  printf("%s - version 0.1, compiled %s, at %s\n", bn, __DATE__, __TIME__);
283  printf(" --help (-h. -?) = This help, and exit(0).\n");
284  printf(" --version = This help, and exit(0).\n");
285  printf(" --db database (-d) = Set the database name. (def=%s)\n",database);
286  printf(" --ip addr (-i) = Set the IP address of the postgresql server. (def=%s)\n",ip_address);
287  printf(" --port val (-p) = Set the port of the postgreql server. (def=%s)\n",port);
288  printf(" --user name (-u) = Set the user name. (def=%s)\n",user);
289  printf(" --word pwd (-w) = Set the password for the above user. (def=%s)\n",pwd);
290 }
291 
292 int parse_commands( int argc, char **argv )
293 {
294  int i, i2;
295  char *arg;
296  char *sarg;
297 
298  for ( i = 1; i < argc; i++ ) {
299  i2 = i + 1;
300  arg = argv[i];
301  sarg = arg;
302  if ((strcmp(arg,"--help") == 0) || (strcmp(arg,"-h") == 0) ||
303  (strcmp(arg,"-?") == 0) || (strcmp(arg,"--version") == 0)) {
304  give_help(argv[0]);
305  exit(0);
306  } else if (*sarg == '-') {
307  sarg++;
308  while (*sarg == '-') sarg++;
309  switch (*sarg)
310  {
311  case 'd':
312  if (i2 < argc) {
313  sarg = argv[i2];
314  database = strdup(sarg);
315  i++;
316  } else {
317  printf("database name must follow!\n");
318  goto Bad_ARG;
319  }
320  break;
321  case 'i':
322  if (i2 < argc) {
323  sarg = argv[i2];
324  ip_address = strdup(sarg);
325  i++;
326  } else {
327  printf("IP address must follow!\n");
328  goto Bad_ARG;
329  }
330  break;
331  case 'p':
332  if (i2 < argc) {
333  sarg = argv[i2];
334  port = strdup(sarg);
335  i++;
336  } else {
337  printf("port value must follow!\n");
338  goto Bad_ARG;
339  }
340  break;
341  case 'u':
342  if (i2 < argc) {
343  sarg = argv[i2];
344  user = strdup(sarg);
345  i++;
346  } else {
347  printf("user name must follow!\n");
348  goto Bad_ARG;
349  }
350  break;
351  case 'w':
352  if (i2 < argc) {
353  sarg = argv[i2];
354  pwd = strdup(sarg);
355  i++;
356  } else {
357  printf("password must follow!\n");
358  goto Bad_ARG;
359  }
360  break;
361  default:
362  goto Bad_ARG;
363  }
364  } else {
365 Bad_ARG:
366  printf("ERROR: Unknown argument [%s]! Try -?\n",arg);
367  return 1;
368  }
369  }
370  return 0;
371 }
372 
373 
374 
375 int main( int argc, char **argv )
376 {
377  int res;
378  PGconn *conn = NULL;
379 
380  if (parse_commands(argc,argv))
381  return 1;
382 
383  // test_template1(); // this will fail unless there is a $USER users with $HOME/.passwd being the password
384 
385  printf("Attempting connection on [%s], port [%s], database [%s], user [%s], pwd [%s]\n",
387 
388  res = ConnectDB(&conn);
389  if (res)
390  return 1;
391 
392  printf("Connection successful...\n");
393  // sleep(1);
394 
395  printf("Query tables...\n");
396  query_tables(conn,1); // query and show fields, rows
397 
398  printf("Closing connection...\n");
399  PQfinish(conn);
400 
402 
403  if (got_flights && got_waypts) {
404  printf("database %s has 'flights' and 'waypoints' tables\n so appears suitable for 'fgt_server' use. Congrats!\n",database);
405  }
406  // printf("End testdb - return(0)\n");
407  return 0;
408 }
409 
410 /* eof - testdb.c */
411 
void give_help(char *name)
Definition: testdb.c:279
void test_template1(void)
Definition: testdb.c:70
static char * ip_address
Definition: testdb.c:40
#define DEF_IP_ADDRESS
Definition: testdb.c:21
#define MAXLINE
Definition: fg_common.hxx:179
#define PQ_EXEC_SUCCESS(res)
Definition: testdb.c:51
#define DEF_PORT
Definition: testdb.c:25
void query_tables(PGconn *conn, int verb)
Definition: testdb.c:165
static char * pwd
Definition: testdb.c:44
static char * pgoptions
Definition: testdb.c:45
#define DEF_DATABASE
Definition: testdb.c:29
static char * port
Definition: testdb.c:41
int test_alternate_connection(void)
Definition: testdb.c:227
#define DEF_USER_LOGIN
Definition: testdb.c:33
static char * database
Definition: testdb.c:42
static int got_waypts
Definition: testdb.c:49
static int got_flights
Definition: testdb.c:48
int main(int argc, char **argv)
Definition: testdb.c:375
static char * user
Definition: testdb.c:43
#define DEF_USER_PWD
Definition: testdb.c:37
int ConnectDB(PGconn **conn)
Definition: testdb.c:247
static char * pgtty
Definition: testdb.c:46
int parse_commands(int argc, char **argv)
Definition: testdb.c:292
static void exit_nicely(PGconn *conn)
Definition: testdb.c:64
char * get_base_name(char *name)
Definition: testdb.c:264