1 /******************************************************************************
2 * $Id$
3 *
4 * Project: MapServer
5 * Purpose: Postgres CONNECTIONTYPE support.
6 * Author: Mark Leslie, Refractions Research
7 *
8 ******************************************************************************
9 * Copyright (c) 1996-2005 Regents of the University of Minnesota.
10 *
11 * Permission is hereby granted, free of charge, to any person obtaining a
12 * copy of this software and associated documentation files (the "Software"),
13 * to deal in the Software without restriction, including without limitation
14 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 * and/or sell copies of the Software, and to permit persons to whom the
16 * Software is furnished to do so, subject to the following conditions:
17 *
18 * The above copyright notice and this permission notice shall be included in
19 * all copies of this Software or works derived from this Software.
20 *
21 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 * DEALINGS IN THE SOFTWARE.
28 ****************************************************************************/
29
30
31 /* $Id$ */
32 #include <assert.h>
33 #include "mapserver.h"
34 #include "maptime.h"
35
36 #ifndef FLT_MAX
37 #define FLT_MAX 25000000.0
38 #endif
39
40 #ifdef USE_POSTGIS
41
42 #ifndef LITTLE_ENDIAN
43 #define LITTLE_ENDIAN 1
44 #endif
45 #ifndef BIG_ENDIAN
46 #define BIG_ENDIAN 2
47 #endif
48
49 #include "libpq-fe.h"
50 #include <string.h>
51 #include <ctype.h> /* tolower() */
52
53
54
55
56 typedef struct {
57 PGconn *conn; /* connection to db */
58 long row_num; /* what row is the NEXT to be read (for random access) */
59 PGresult *query_result; /* for fetching rows from the db */
60 int from_index;
61 char *to_column;
62 char *from_value;
63 int layer_debug; /* there's no debug on the join, so use the layer */
64 } msPOSTGRESQLJoinInfo;
65
66 /************************************************************************/
67 /* msPOSTGRESQLJoinConnect() */
68 /* */
69 /* Creates and populates the joininfo struct, including establishing */
70 /* a connection to the database. Since the join and layer won't always */
71 /* share connection details, there is currently no mechanism to use */
72 /* pooled connections with joins. */
73 /************************************************************************/
74
msPOSTGRESQLJoinConnect(layerObj * layer,joinObj * join)75 int msPOSTGRESQLJoinConnect(layerObj *layer, joinObj *join)
76 {
77 char *maskeddata, *temp, *sql, *column;
78 char *conn_decrypted;
79 int i, test;
80 PGresult *query_result;
81 msPOSTGRESQLJoinInfo *joininfo;
82
83 if(join->joininfo)
84 return MS_SUCCESS;
85
86 joininfo = (msPOSTGRESQLJoinInfo *)malloc(sizeof(msPOSTGRESQLJoinInfo));
87 if(!joininfo) {
88 msSetError(MS_MEMERR, "Error allocating join info struct.",
89 "msPOSTGRESQLJoinConnect()");
90 return MS_FAILURE;
91 }
92 joininfo->conn = NULL;
93 joininfo->row_num = 0;
94 joininfo->query_result = NULL;
95 joininfo->from_index = 0;
96 joininfo->to_column = join->to;
97 joininfo->from_value = NULL;
98 joininfo->layer_debug = layer->debug;
99 join->joininfo = joininfo;
100
101 /*
102 * We need three things at a minimum, the connection string, a table
103 * name, and a column to join on.
104 */
105 if(!join->connection) {
106 msSetError(MS_QUERYERR, "No connection information provided.",
107 "MSPOSTGRESQLJoinConnect()");
108 return MS_FAILURE;
109 }
110 if(!join->table) {
111 msSetError(MS_QUERYERR, "No join table name found.",
112 "msPOSTGRESQLJoinConnect()");
113 return MS_FAILURE;
114 }
115 if(!joininfo->to_column) {
116 msSetError(MS_QUERYERR, "No join to column name found.",
117 "msPOSTGRESQLJoinConnect()");
118 return MS_FAILURE;
119 }
120
121 /* Establish database connection */
122 conn_decrypted = msDecryptStringTokens(layer->map, join->connection);
123 if (conn_decrypted != NULL) {
124 joininfo->conn = PQconnectdb(conn_decrypted);
125 free(conn_decrypted);
126 }
127 if(!joininfo->conn || PQstatus(joininfo->conn) == CONNECTION_BAD) {
128 maskeddata = (char *)malloc(strlen(layer->connection) + 1);
129 strcpy(maskeddata, join->connection);
130 temp = strstr(maskeddata, "password=");
131 if(temp) {
132 temp = (char *)(temp + 9);
133 while (*temp != '\0' && *temp != ' ') {
134 *temp = '*';
135 temp++;
136 }
137 }
138 msSetError(MS_QUERYERR,
139 "Unable to connect to PostgreSQL using the string %s.\n Error reported: %s\n",
140 "msPOSTGRESQLJoinConnect()",
141 maskeddata, PQerrorMessage(joininfo->conn));
142 free(maskeddata);
143 if(!joininfo->conn) {
144 free(joininfo->conn);
145 }
146 free(joininfo);
147 join->joininfo = NULL;
148 return MS_FAILURE;
149 }
150
151 /* Determine the number and names of columns in the join table. */
152 sql = (char *)malloc(36 + strlen(join->table) + 1);
153 sprintf(sql, "SELECT * FROM %s WHERE false LIMIT 0", join->table);
154
155 if(joininfo->layer_debug) {
156 msDebug("msPOSTGRESQLJoinConnect(): executing %s.\n", sql);
157 }
158
159 query_result = PQexec(joininfo->conn, sql);
160 if(!query_result || PQresultStatus(query_result) != PGRES_TUPLES_OK) {
161 msSetError(MS_QUERYERR, "Error determining join items: %s.",
162 "msPOSTGRESQLJoinConnect()", PQerrorMessage(joininfo->conn));
163 if(query_result) {
164 PQclear(query_result);
165 query_result = NULL;
166 }
167 free(sql);
168 return MS_FAILURE;
169 }
170 free(sql);
171 join->numitems = PQnfields(query_result);
172 join->items = malloc(sizeof(char *) * (join->numitems));
173
174 /* We want the join-to column to be first in the list. */
175 test = 1;
176 for(i = 0; i < join->numitems; i++) {
177 column = PQfname(query_result, i);
178 if(strcmp(column, joininfo->to_column) != 0) {
179 join->items[i + test] = (char *)malloc(strlen(column) + 1);
180 strcpy(join->items[i + test], column);
181 } else {
182 test = 0;
183 join->items[0] = (char *)malloc(strlen(column) + 1);
184 strcpy(join->items[0], column);
185 }
186 }
187 PQclear(query_result);
188 query_result = NULL;
189 if(test == 1) {
190 msSetError(MS_QUERYERR, "Unable to find join to column: %s",
191 "msPOSTGRESQLJoinConnect()", joininfo->to_column);
192 return MS_FAILURE;
193 }
194
195 if(joininfo->layer_debug) {
196 for(i = 0; i < join->numitems; i++) {
197 msDebug("msPOSTGRESQLJoinConnect(): Column %d named %s\n", i, join->items[i]);
198 }
199 }
200
201 /* Determine the index of the join from column. */
202 for(i = 0; i < layer->numitems; i++) {
203 if(strcasecmp(layer->items[i], join->from) == 0) {
204 joininfo->from_index = i;
205 break;
206 }
207 }
208
209 if(i == layer->numitems) {
210 msSetError(MS_JOINERR, "Item %s not found in layer %s.",
211 "msPOSTGRESQLJoinConnect()", join->from, layer->name);
212 return MS_FAILURE;
213 }
214
215 return MS_SUCCESS;
216 }
217
218 /************************************************************************/
219 /* */
220 /* msPOSTGRESQLJoinPrepare() */
221 /* Sets up the joininfo to be ready to join against the given shape. */
222 /* There's not much involved here, just freeing previous results and */
223 /* resources, and setting the next value to join to. */
224 /************************************************************************/
225
msPOSTGRESQLJoinPrepare(joinObj * join,shapeObj * shape)226 int msPOSTGRESQLJoinPrepare(joinObj *join, shapeObj *shape)
227 {
228
229 /* We need a connection, and a shape with values to join to. */
230 msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
231 if(!joininfo) {
232 msSetError(MS_JOINERR, "Join has not been connected.", "msPOSTGRESQLJoinPrepare()");
233 return MS_FAILURE;
234 }
235
236 if(!shape) {
237 msSetError(MS_JOINERR, "Null shape provided for join.", "msPOSTGRESQLJoinPrepare()");
238 return MS_FAILURE;
239 }
240
241 if(!shape->values) {
242 msSetError(MS_JOINERR, "Shape has no attributes. Kinda hard to join against.", "msPOSTGRESQLJoinPrepare()");
243 return MS_FAILURE;
244 }
245 joininfo->row_num = 0;
246
247 /* Free the previous join value, if any. */
248 if(joininfo->from_value) {
249 free(joininfo->from_value);
250 }
251
252 /* Free the previous results, if any. */
253 if(joininfo->query_result) {
254 PQclear(joininfo->query_result);
255 joininfo->query_result = NULL;
256 }
257
258 /* Copy the next join value from the shape. */
259 joininfo->from_value = msStrdup(shape->values[joininfo->from_index]);
260
261 if(joininfo->layer_debug) {
262 msDebug("msPOSTGRESQLJoinPrepare() preping for value %s.\n",
263 joininfo->from_value);
264 }
265
266 return MS_SUCCESS;
267 }
268
269 /************************************************************************/
270 /* msPOSTGRESQLJoinNext() */
271 /* */
272 /* The goal here is to populate join->values with the detail of the */
273 /* join against the previously prepared shapeObj. This will be called */
274 /* only once for a one-to-one join, with msPOSTGRESQLJoinPrepare() */
275 /* being called before each. It will be called repeatedly for */
276 /* one-to-many joins, until in returns MS_DONE. To accomodate this, */
277 /* we store the next row number and query results in the joininfo and */
278 /* process the next tuple on each call. */
279 /************************************************************************/
msPOSTGRESQLJoinNext(joinObj * join)280 int msPOSTGRESQLJoinNext(joinObj *join)
281 {
282 msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
283 int i, length, row_count;
284 char *sql, *columns;
285
286 /* We need a connection, and a join value. */
287 if(!joininfo || !joininfo->conn) {
288 msSetError(MS_JOINERR, "Join has not been connected.\n",
289 "msPOSTGRESQLJoinNext()");
290 return MS_FAILURE;
291 }
292
293 if(!joininfo->from_value) {
294 msSetError(MS_JOINERR, "Join has not been prepared.\n",
295 "msPOSTGRESQLJoinNext()");
296 return MS_FAILURE;
297 }
298
299 /* Free the previous results. */
300 if(join->values) {
301 msFreeCharArray(join->values, join->numitems);
302 join->values = NULL;
303 }
304
305 /* We only need to execute the query if no results exist. */
306 if(!joininfo->query_result) {
307 /* Write the list of column names. */
308 length = 0;
309 for(i = 0; i < join->numitems; i++) {
310 length += 8 + strlen(join->items[i]) + 2;
311 }
312
313 columns = (char *)malloc(length);
314 if(!columns) {
315 msSetError(MS_MEMERR, "Failure to malloc.\n",
316 "msPOSTGRESQLJoinNext()");
317 return MS_FAILURE;
318 }
319
320 strcpy(columns, "");
321 for(i = 0; i < join->numitems; i++) {
322 strcat(columns, "\"");
323 strcat(columns, join->items[i]);
324 strcat(columns, "\"::text");
325 if(i != join->numitems - 1) {
326 strcat(columns, ", ");
327 }
328 }
329
330 /* Create the query string. */
331 sql = (char *)malloc(26 + strlen(columns) + strlen(join->table) +
332 strlen(join->to) + strlen(joininfo->from_value));
333 if(!sql) {
334 msSetError(MS_MEMERR, "Failure to malloc.\n",
335 "msPOSTGRESQLJoinNext()");
336 return MS_FAILURE;
337 }
338 sprintf(sql, "SELECT %s FROM %s WHERE %s = '%s'", columns, join->table, join->to, joininfo->from_value);
339 if(joininfo->layer_debug) {
340 msDebug("msPOSTGRESQLJoinNext(): executing %s.\n", sql);
341 }
342
343 free(columns);
344
345 joininfo->query_result = PQexec(joininfo->conn, sql);
346
347 if(!joininfo->query_result ||
348 PQresultStatus(joininfo->query_result) != PGRES_TUPLES_OK) {
349 msSetError(MS_QUERYERR, "Error executing queri %s: %s\n",
350 "msPOSTGRESQLJoinNext()", sql,
351 PQerrorMessage(joininfo->conn));
352 if(joininfo->query_result) {
353 PQclear(joininfo->query_result);
354 joininfo->query_result = NULL;
355 }
356 free(sql);
357 return MS_FAILURE;
358 }
359 free(sql);
360 }
361 row_count = PQntuples(joininfo->query_result);
362
363 /* see if we're done processing this set */
364 if(joininfo->row_num >= row_count) {
365 return(MS_DONE);
366 }
367 if(joininfo->layer_debug) {
368 msDebug("msPOSTGRESQLJoinNext(): fetching row %ld.\n",
369 joininfo->row_num);
370 }
371
372 /* Copy the resulting values into the joinObj. */
373 join->values = (char **)malloc(sizeof(char *) * join->numitems);
374 for(i = 0; i < join->numitems; i++) {
375 join->values[i] = msStrdup(PQgetvalue(
376 joininfo->query_result, joininfo->row_num, i));
377 }
378
379 joininfo->row_num++;
380
381 return MS_SUCCESS;
382 }
383
384 /************************************************************************/
385 /* msPOSTGRESQLJoinClose() */
386 /* */
387 /* Closes the connection and frees the resources used by the joininfo. */
388 /************************************************************************/
389
msPOSTGRESQLJoinClose(joinObj * join)390 int msPOSTGRESQLJoinClose(joinObj *join)
391 {
392 msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
393
394 if(!joininfo) {
395 msDebug("msPOSTGRESQLJoinClose() already close or never opened.\n");
396 return MS_SUCCESS;
397 }
398
399 if(joininfo->query_result) {
400 msDebug("msPOSTGRESQLJoinClose(): clearing query_result.\n");
401 PQclear(joininfo->query_result);
402 joininfo->query_result = NULL;
403 }
404
405 if(joininfo->conn) {
406 msDebug("msPOSTGRESQLJoinClose(): closing connection.\n");
407 PQfinish(joininfo->conn);
408 joininfo->conn = NULL;
409 }
410
411 /* removed free(joininfo->to_column), see bug #2936 */
412
413 if(joininfo->from_value) {
414 free(joininfo->from_value);
415 }
416
417 free(joininfo);
418 join->joininfo = NULL;
419
420 return MS_SUCCESS;
421 }
422
423 #else /* not USE_POSTGIS */
msPOSTGRESQLJoinConnect(layerObj * layer,joinObj * join)424 int msPOSTGRESQLJoinConnect(layerObj *layer, joinObj *join)
425 {
426 msSetError(MS_QUERYERR, "PostgreSQL support not available.", "msPOSTGRESQLJoinConnect()");
427 return MS_FAILURE;
428 }
429
msPOSTGRESQLJoinPrepare(joinObj * join,shapeObj * shape)430 int msPOSTGRESQLJoinPrepare(joinObj *join, shapeObj *shape)
431 {
432 msSetError(MS_QUERYERR, "PostgreSQL support not available.", "msPOSTGRESQLJoinPrepare()");
433 return MS_FAILURE;
434
435 }
436
msPOSTGRESQLJoinNext(joinObj * join)437 int msPOSTGRESQLJoinNext(joinObj *join)
438 {
439 msSetError(MS_QUERYERR, "PostgreSQL support not available.", "msPOSTGRESQLJoinNext()");
440 return MS_FAILURE;
441
442 }
443
msPOSTGRESQLJoinClose(joinObj * join)444 int msPOSTGRESQLJoinClose(joinObj *join)
445 {
446 msSetError(MS_QUERYERR, "PostgreSQL support not available.", "msPOSTGRESQLJoinClose()");
447 return MS_FAILURE;
448
449 }
450 #endif
451