/src/MapServer/src/mappostgresql.c

Source
/******************************************************************************
 * $Id$
 *
 * Project:  MapServer
 * Purpose:  Postgres CONNECTIONTYPE support.
 * Author:   Mark Leslie, Refractions Research
 *
 ******************************************************************************
 * Copyright (c) 1996-2005 Regents of the University of Minnesota.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a
 * copy of this software and associated documentation files (the "Software"),
 * to deal in the Software without restriction, including without limitation
 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies of this Software or works derived from this Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
 * DEALINGS IN THE SOFTWARE.
 ****************************************************************************/

/* $Id$ */
#include <assert.h>
#include "mapserver.h"
#include "maptime.h"

#ifndef FLT_MAX
#define FLT_MAX 25000000.0
#endif

#ifdef USE_POSTGIS

#ifndef LITTLE_ENDIAN
#define LITTLE_ENDIAN 1
#endif
#ifndef BIG_ENDIAN
#define BIG_ENDIAN 2
#endif

#include "libpq-fe.h"
#include <string.h>
#include <ctype.h> /* tolower() */

typedef struct {
  PGconn *conn; /* connection to db */
  long row_num; /* what row is the NEXT to be read (for random access) */
  PGresult *query_result; /* for fetching rows from the db */
  int from_index;
  char *to_column;
  char *from_value;
  int layer_debug; /* there's no debug on the join, so use the layer */
} msPOSTGRESQLJoinInfo;

/************************************************************************/
/*                      msPOSTGRESQLJoinConnect()                       */
/*                                                                      */
/* Creates and populates the joininfo struct, including establishing    */
/* a connection to the database.  Since the join and layer won't always */
/* share connection details, there is currently no mechanism to use     */
/* pooled connections with joins.                                       */
/************************************************************************/

int msPOSTGRESQLJoinConnect(layerObj *layer, joinObj *join) {
  char *maskeddata, *temp, *sql, *column;
  char *conn_decrypted;
  int i, test;
  PGresult *query_result;
  msPOSTGRESQLJoinInfo *joininfo;

  if (join->joininfo)
    return MS_SUCCESS;

  joininfo = (msPOSTGRESQLJoinInfo *)malloc(sizeof(msPOSTGRESQLJoinInfo));
  if (!joininfo) {
    msSetError(MS_MEMERR, "Error allocating join info struct.",
               "msPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }
  joininfo->conn = NULL;
  joininfo->row_num = 0;
  joininfo->query_result = NULL;
  joininfo->from_index = 0;
  joininfo->to_column = join->to;
  joininfo->from_value = NULL;
  joininfo->layer_debug = layer->debug;
  join->joininfo = joininfo;

  /*
   * We need three things at a minimum, the connection string, a table
   * name, and a column to join on.
   */
  if (!join->connection) {
    msSetError(MS_QUERYERR, "No connection information provided.",
               "MSPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }
  if (!join->table) {
    msSetError(MS_QUERYERR, "No join table name found.",
               "msPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }
  if (!joininfo->to_column) {
    msSetError(MS_QUERYERR, "No join to column name found.",
               "msPOSTGRESQLJoinConnect()");
    return MS_FAILURE;
  }

  /* Establish database connection */
  conn_decrypted = msDecryptStringTokens(layer->map, join->connection);
  if (conn_decrypted != NULL) {
    joininfo->conn = PQconnectdb(conn_decrypted);
    free(conn_decrypted);
  }
  if (!joininfo->conn || PQstatus(joininfo->conn) == CONNECTION_BAD) {
    maskeddata = (char *)malloc(strlen(layer->connection) + 1);
    strcpy(maskeddata, join->connection);
    temp = strstr(maskeddata, "password=");
    if (temp) {
      temp = (char *)(temp + 9);
      while (*temp != '\0' && *temp != ' ') {
        *temp = '*';
        temp++;
      }
    }
    msSetError(MS_QUERYERR,
               "Unable to connect to PostgreSQL using the string %s.\n  Error "
               "reported: %s\n",
               "msPOSTGRESQLJoinConnect()", maskeddata,
               PQerrorMessage(joininfo->conn));
    free(maskeddata);
    if (!joininfo->conn) {
      free(joininfo->conn);
    }
    free(joininfo);
    join->joininfo = NULL;
    return MS_FAILURE;
  }

  /* Determine the number and names of columns in the join table. */
  sql = (char *)malloc(36 + strlen(join->table) + 1);
  sprintf(sql, "SELECT * FROM %s WHERE false LIMIT 0", join->table);

  if (joininfo->layer_debug) {
    msDebug("msPOSTGRESQLJoinConnect(): executing %s.\n", sql);
  }

  query_result = PQexec(joininfo->conn, sql);
  if (!query_result || PQresultStatus(query_result) != PGRES_TUPLES_OK) {
    msSetError(MS_QUERYERR, "Error determining join items: %s.",
               "msPOSTGRESQLJoinConnect()", PQerrorMessage(joininfo->conn));
    if (query_result) {
      PQclear(query_result);
      query_result = NULL;
    }
    free(sql);
    return MS_FAILURE;
  }
  free(sql);
  join->numitems = PQnfields(query_result);
  join->items = malloc(sizeof(char *) * (join->numitems));

  /* We want the join-to column to be first in the list. */
  test = 1;
  for (i = 0; i < join->numitems; i++) {
    column = PQfname(query_result, i);
    if (strcmp(column, joininfo->to_column) != 0) {
      join->items[i + test] = (char *)malloc(strlen(column) + 1);
      strcpy(join->items[i + test], column);
    } else {
      test = 0;
      join->items[0] = (char *)malloc(strlen(column) + 1);
      strcpy(join->items[0], column);
    }
  }
  PQclear(query_result);
  query_result = NULL;
  if (test == 1) {
    msSetError(MS_QUERYERR, "Unable to find join to column: %s",
               "msPOSTGRESQLJoinConnect()", joininfo->to_column);
    return MS_FAILURE;
  }

  if (joininfo->layer_debug) {
    for (i = 0; i < join->numitems; i++) {
      msDebug("msPOSTGRESQLJoinConnect(): Column %d named %s\n", i,
              join->items[i]);
    }
  }

  /* Determine the index of the join from column. */
  for (i = 0; i < layer->numitems; i++) {
    if (strcasecmp(layer->items[i], join->from) == 0) {
      joininfo->from_index = i;
      break;
    }
  }

  if (i == layer->numitems) {
    msSetError(MS_JOINERR, "Item %s not found in layer %s.",
               "msPOSTGRESQLJoinConnect()", join->from, layer->name);
    return MS_FAILURE;
  }

  return MS_SUCCESS;
}

/************************************************************************/
/*                                                                      */
/*                       msPOSTGRESQLJoinPrepare()                      */
/* Sets up the joininfo to be ready to join against the given shape.    */
/* There's not much involved here, just freeing previous results and    */
/* resources, and setting the next value to join to.                    */
/************************************************************************/

int msPOSTGRESQLJoinPrepare(joinObj *join, shapeObj *shape) {

  /* We need a connection, and a shape with values to join to. */
  msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
  if (!joininfo) {
    msSetError(MS_JOINERR, "Join has not been connected.",
               "msPOSTGRESQLJoinPrepare()");
    return MS_FAILURE;
  }

  if (!shape) {
    msSetError(MS_JOINERR, "Null shape provided for join.",
               "msPOSTGRESQLJoinPrepare()");
    return MS_FAILURE;
  }

  if (!shape->values) {
    msSetError(MS_JOINERR,
               "Shape has no attributes.  Kinda hard to join against.",
               "msPOSTGRESQLJoinPrepare()");
    return MS_FAILURE;
  }
  joininfo->row_num = 0;

  /* Free the previous join value, if any. */
  if (joininfo->from_value) {
    free(joininfo->from_value);
  }

  /* Free the previous results, if any. */
  if (joininfo->query_result) {
    PQclear(joininfo->query_result);
    joininfo->query_result = NULL;
  }

  /* Copy the next join value from the shape. */
  joininfo->from_value = msStrdup(shape->values[joininfo->from_index]);

  if (joininfo->layer_debug) {
    msDebug("msPOSTGRESQLJoinPrepare() preping for value %s.\n",
            joininfo->from_value);
  }

  return MS_SUCCESS;
}

/************************************************************************/
/*                       msPOSTGRESQLJoinNext()                         */
/*                                                                      */
/* The goal here is to populate join->values with the detail of the     */
/* join against the previously prepared shapeObj.  This will be called  */
/* only once for a one-to-one join, with msPOSTGRESQLJoinPrepare()      */
/* being called before each.  It will be called repeatedly for          */
/* one-to-many joins, until in returns MS_DONE.  To accommodate this,    */
/* we store the next row number and query results in the joininfo and   */
/* process the next tuple on each call.                                 */
/************************************************************************/
int msPOSTGRESQLJoinNext(joinObj *join) {
  msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
  int i, length, row_count;
  char *sql, *columns;

  /* We need a connection, and a join value. */
  if (!joininfo || !joininfo->conn) {
    msSetError(MS_JOINERR, "Join has not been connected.\n",
               "msPOSTGRESQLJoinNext()");
    return MS_FAILURE;
  }

  if (!joininfo->from_value) {
    msSetError(MS_JOINERR, "Join has not been prepared.\n",
               "msPOSTGRESQLJoinNext()");
    return MS_FAILURE;
  }

  /* Free the previous results. */
  if (join->values) {
    msFreeCharArray(join->values, join->numitems);
    join->values = NULL;
  }

  /* We only need to execute the query if no results exist. */
  if (!joininfo->query_result) {
    /* Write the list of column names. */
    length = 0;
    for (i = 0; i < join->numitems; i++) {
      length += 8 + strlen(join->items[i]) + 2;
    }
    if (length > 1024 * 1024) {
      msSetError(MS_MEMERR, "Too many joins.\n", "msPOSTGRESQLJoinNext()");
      return MS_FAILURE;
    }

    columns = (char *)malloc(length + 1);
    if (!columns) {
      msSetError(MS_MEMERR, "Failure to malloc.\n", "msPOSTGRESQLJoinNext()");
      return MS_FAILURE;
    }

    columns[0] = 0;
    for (i = 0; i < join->numitems; i++) {
      strcat(columns, "\"");
      strcat(columns, join->items[i]);
      strcat(columns, "\"::text");
      if (i != join->numitems - 1) {
        strcat(columns, ", ");
      }
    }

    /* Create the query string. */
    const size_t nSize = 26 + strlen(columns) + strlen(join->table) +
                         strlen(join->to) + strlen(joininfo->from_value);
    sql = (char *)malloc(nSize);
    if (!sql) {
      msSetError(MS_MEMERR, "Failure to malloc.\n", "msPOSTGRESQLJoinNext()");
      return MS_FAILURE;
    }
    snprintf(sql, nSize, "SELECT %s FROM %s WHERE %s = '%s'", columns,
             join->table, join->to, joininfo->from_value);
    if (joininfo->layer_debug) {
      msDebug("msPOSTGRESQLJoinNext(): executing %s.\n", sql);
    }

    free(columns);

    joininfo->query_result = PQexec(joininfo->conn, sql);

    if (!joininfo->query_result ||
        PQresultStatus(joininfo->query_result) != PGRES_TUPLES_OK) {
      msSetError(MS_QUERYERR, "Error executing queri %s: %s\n",
                 "msPOSTGRESQLJoinNext()", sql, PQerrorMessage(joininfo->conn));
      if (joininfo->query_result) {
        PQclear(joininfo->query_result);
        joininfo->query_result = NULL;
      }
      free(sql);
      return MS_FAILURE;
    }
    free(sql);
  }
  row_count = PQntuples(joininfo->query_result);

  /* see if we're done processing this set */
  if (joininfo->row_num >= row_count) {
    return (MS_DONE);
  }
  if (joininfo->layer_debug) {
    msDebug("msPOSTGRESQLJoinNext(): fetching row %ld.\n", joininfo->row_num);
  }

  /* Copy the resulting values into the joinObj. */
  join->values = (char **)malloc(sizeof(char *) * join->numitems);
  for (i = 0; i < join->numitems; i++) {
    join->values[i] =
        msStrdup(PQgetvalue(joininfo->query_result, joininfo->row_num, i));
  }

  joininfo->row_num++;

  return MS_SUCCESS;
}

/************************************************************************/
/*                       msPOSTGRESQLJoinClose()                        */
/*                                                                      */
/* Closes the connection and frees the resources used by the joininfo.  */
/************************************************************************/

int msPOSTGRESQLJoinClose(joinObj *join) {
  msPOSTGRESQLJoinInfo *joininfo = join->joininfo;

  if (!joininfo) {
    msDebug("msPOSTGRESQLJoinClose() already close or never opened.\n");
    return MS_SUCCESS;
  }

  if (joininfo->query_result) {
    msDebug("msPOSTGRESQLJoinClose(): clearing query_result.\n");
    PQclear(joininfo->query_result);
    joininfo->query_result = NULL;
  }

  if (joininfo->conn) {
    msDebug("msPOSTGRESQLJoinClose(): closing connection.\n");
    PQfinish(joininfo->conn);
    joininfo->conn = NULL;
  }

  /* removed free(joininfo->to_column), see bug #2936 */

  if (joininfo->from_value) {
    free(joininfo->from_value);
  }

  free(joininfo);
  join->joininfo = NULL;

  return MS_SUCCESS;
}

#else /* not USE_POSTGIS */
int msPOSTGRESQLJoinConnect(layerObj *layer, joinObj *join) {
  msSetError(MS_QUERYERR, "PostgreSQL support not available.",
             "msPOSTGRESQLJoinConnect()");
  return MS_FAILURE;
}

int msPOSTGRESQLJoinPrepare(joinObj *join, shapeObj *shape) {
  msSetError(MS_QUERYERR, "PostgreSQL support not available.",
             "msPOSTGRESQLJoinPrepare()");
  return MS_FAILURE;
}

int msPOSTGRESQLJoinNext(joinObj *join) {
  msSetError(MS_QUERYERR, "PostgreSQL support not available.",
             "msPOSTGRESQLJoinNext()");
  return MS_FAILURE;
}

int msPOSTGRESQLJoinClose(joinObj *join) {
  msSetError(MS_QUERYERR, "PostgreSQL support not available.",
             "msPOSTGRESQLJoinClose()");
  return MS_FAILURE;
}
#endif

Coverage Report

Created: 2025-11-16 06:25

Line	Count	Source
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		/* $Id$ */
31		#include <assert.h>
32		#include "mapserver.h"
33		#include "maptime.h"
34
35		#ifndef FLT_MAX
36		#define FLT_MAX 25000000.0
37		#endif
38
39		#ifdef USE_POSTGIS
40
41		#ifndef LITTLE_ENDIAN
42		#define LITTLE_ENDIAN 1
43		#endif
44		#ifndef BIG_ENDIAN
45		#define BIG_ENDIAN 2
46		#endif
47
48		#include "libpq-fe.h"
49		#include <string.h>
50		#include <ctype.h> /* tolower() */
51
52		typedef struct {
53		PGconn conn; / connection to db */
54		long row_num; /* what row is the NEXT to be read (for random access) */
55		PGresult query_result; / for fetching rows from the db */
56		int from_index;
57		char *to_column;
58		char *from_value;
59		int layer_debug; /* there's no debug on the join, so use the layer */
60		} msPOSTGRESQLJoinInfo;
61
62		/************************************************************************/
63		/* msPOSTGRESQLJoinConnect() */
64		/* */
65		/* Creates and populates the joininfo struct, including establishing */
66		/* a connection to the database. Since the join and layer won't always */
67		/* share connection details, there is currently no mechanism to use */
68		/* pooled connections with joins. */
69		/************************************************************************/
70
71		int msPOSTGRESQLJoinConnect(layerObj layer, joinObj join) {
72		char maskeddata, temp, sql, column;
73		char *conn_decrypted;
74		int i, test;
75		PGresult *query_result;
76		msPOSTGRESQLJoinInfo *joininfo;
77
78		if (join->joininfo)
79		return MS_SUCCESS;
80
81		joininfo = (msPOSTGRESQLJoinInfo *)malloc(sizeof(msPOSTGRESQLJoinInfo));
82		if (!joininfo) {
83		msSetError(MS_MEMERR, "Error allocating join info struct.",
84		"msPOSTGRESQLJoinConnect()");
85		return MS_FAILURE;
86		}
87		joininfo->conn = NULL;
88		joininfo->row_num = 0;
89		joininfo->query_result = NULL;
90		joininfo->from_index = 0;
91		joininfo->to_column = join->to;
92		joininfo->from_value = NULL;
93		joininfo->layer_debug = layer->debug;
94		join->joininfo = joininfo;
95
96		/*
97		* We need three things at a minimum, the connection string, a table
98		* name, and a column to join on.
99		*/
100		if (!join->connection) {
101		msSetError(MS_QUERYERR, "No connection information provided.",
102		"MSPOSTGRESQLJoinConnect()");
103		return MS_FAILURE;
104		}
105		if (!join->table) {
106		msSetError(MS_QUERYERR, "No join table name found.",
107		"msPOSTGRESQLJoinConnect()");
108		return MS_FAILURE;
109		}
110		if (!joininfo->to_column) {
111		msSetError(MS_QUERYERR, "No join to column name found.",
112		"msPOSTGRESQLJoinConnect()");
113		return MS_FAILURE;
114		}
115
116		/* Establish database connection */
117		conn_decrypted = msDecryptStringTokens(layer->map, join->connection);
118		if (conn_decrypted != NULL) {
119		joininfo->conn = PQconnectdb(conn_decrypted);
120		free(conn_decrypted);
121		}
122		if (!joininfo->conn \|\| PQstatus(joininfo->conn) == CONNECTION_BAD) {
123		maskeddata = (char *)malloc(strlen(layer->connection) + 1);
124		strcpy(maskeddata, join->connection);
125		temp = strstr(maskeddata, "password=");
126		if (temp) {
127		temp = (char *)(temp + 9);
128		while (temp != '\0' && temp != ' ') {
129		temp = '';
130		temp++;
131		}
132		}
133		msSetError(MS_QUERYERR,
134		"Unable to connect to PostgreSQL using the string %s.\n Error "
135		"reported: %s\n",
136		"msPOSTGRESQLJoinConnect()", maskeddata,
137		PQerrorMessage(joininfo->conn));
138		free(maskeddata);
139		if (!joininfo->conn) {
140		free(joininfo->conn);
141		}
142		free(joininfo);
143		join->joininfo = NULL;
144		return MS_FAILURE;
145		}
146
147		/* Determine the number and names of columns in the join table. */
148		sql = (char *)malloc(36 + strlen(join->table) + 1);
149		sprintf(sql, "SELECT * FROM %s WHERE false LIMIT 0", join->table);
150
151		if (joininfo->layer_debug) {
152		msDebug("msPOSTGRESQLJoinConnect(): executing %s.\n", sql);
153		}
154
155		query_result = PQexec(joininfo->conn, sql);
156		if (!query_result \|\| PQresultStatus(query_result) != PGRES_TUPLES_OK) {
157		msSetError(MS_QUERYERR, "Error determining join items: %s.",
158		"msPOSTGRESQLJoinConnect()", PQerrorMessage(joininfo->conn));
159		if (query_result) {
160		PQclear(query_result);
161		query_result = NULL;
162		}
163		free(sql);
164		return MS_FAILURE;
165		}
166		free(sql);
167		join->numitems = PQnfields(query_result);
168		join->items = malloc(sizeof(char ) (join->numitems));
169
170		/* We want the join-to column to be first in the list. */
171		test = 1;
172		for (i = 0; i < join->numitems; i++) {
173		column = PQfname(query_result, i);
174		if (strcmp(column, joininfo->to_column) != 0) {
175		join->items[i + test] = (char *)malloc(strlen(column) + 1);
176		strcpy(join->items[i + test], column);
177		} else {
178		test = 0;
179		join->items[0] = (char *)malloc(strlen(column) + 1);
180		strcpy(join->items[0], column);
181		}
182		}
183		PQclear(query_result);
184		query_result = NULL;
185		if (test == 1) {
186		msSetError(MS_QUERYERR, "Unable to find join to column: %s",
187		"msPOSTGRESQLJoinConnect()", joininfo->to_column);
188		return MS_FAILURE;
189		}
190
191		if (joininfo->layer_debug) {
192		for (i = 0; i < join->numitems; i++) {
193		msDebug("msPOSTGRESQLJoinConnect(): Column %d named %s\n", i,
194		join->items[i]);
195		}
196		}
197
198		/* Determine the index of the join from column. */
199		for (i = 0; i < layer->numitems; i++) {
200		if (strcasecmp(layer->items[i], join->from) == 0) {
201		joininfo->from_index = i;
202		break;
203		}
204		}
205
206		if (i == layer->numitems) {
207		msSetError(MS_JOINERR, "Item %s not found in layer %s.",
208		"msPOSTGRESQLJoinConnect()", join->from, layer->name);
209		return MS_FAILURE;
210		}
211
212		return MS_SUCCESS;
213		}
214
215		/************************************************************************/
216		/* */
217		/* msPOSTGRESQLJoinPrepare() */
218		/* Sets up the joininfo to be ready to join against the given shape. */
219		/* There's not much involved here, just freeing previous results and */
220		/* resources, and setting the next value to join to. */
221		/************************************************************************/
222
223		int msPOSTGRESQLJoinPrepare(joinObj join, shapeObj shape) {
224
225		/* We need a connection, and a shape with values to join to. */
226		msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
227		if (!joininfo) {
228		msSetError(MS_JOINERR, "Join has not been connected.",
229		"msPOSTGRESQLJoinPrepare()");
230		return MS_FAILURE;
231		}
232
233		if (!shape) {
234		msSetError(MS_JOINERR, "Null shape provided for join.",
235		"msPOSTGRESQLJoinPrepare()");
236		return MS_FAILURE;
237		}
238
239		if (!shape->values) {
240		msSetError(MS_JOINERR,
241		"Shape has no attributes. Kinda hard to join against.",
242		"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 accommodate this, */
277		/* we store the next row number and query results in the joininfo and */
278		/* process the next tuple on each call. */
279		/************************************************************************/
280		int msPOSTGRESQLJoinNext(joinObj *join) {
281		msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
282		int i, length, row_count;
283		char sql, columns;
284
285		/* We need a connection, and a join value. */
286		if (!joininfo \|\| !joininfo->conn) {
287		msSetError(MS_JOINERR, "Join has not been connected.\n",
288		"msPOSTGRESQLJoinNext()");
289		return MS_FAILURE;
290		}
291
292		if (!joininfo->from_value) {
293		msSetError(MS_JOINERR, "Join has not been prepared.\n",
294		"msPOSTGRESQLJoinNext()");
295		return MS_FAILURE;
296		}
297
298		/* Free the previous results. */
299		if (join->values) {
300		msFreeCharArray(join->values, join->numitems);
301		join->values = NULL;
302		}
303
304		/* We only need to execute the query if no results exist. */
305		if (!joininfo->query_result) {
306		/* Write the list of column names. */
307		length = 0;
308		for (i = 0; i < join->numitems; i++) {
309		length += 8 + strlen(join->items[i]) + 2;
310		}
311		if (length > 1024 * 1024) {
312		msSetError(MS_MEMERR, "Too many joins.\n", "msPOSTGRESQLJoinNext()");
313		return MS_FAILURE;
314		}
315
316		columns = (char *)malloc(length + 1);
317		if (!columns) {
318		msSetError(MS_MEMERR, "Failure to malloc.\n", "msPOSTGRESQLJoinNext()");
319		return MS_FAILURE;
320		}
321
322		columns[0] = 0;
323		for (i = 0; i < join->numitems; i++) {
324		strcat(columns, "\"");
325		strcat(columns, join->items[i]);
326		strcat(columns, "\"::text");
327		if (i != join->numitems - 1) {
328		strcat(columns, ", ");
329		}
330		}
331
332		/* Create the query string. */
333		const size_t nSize = 26 + strlen(columns) + strlen(join->table) +
334		strlen(join->to) + strlen(joininfo->from_value);
335		sql = (char *)malloc(nSize);
336		if (!sql) {
337		msSetError(MS_MEMERR, "Failure to malloc.\n", "msPOSTGRESQLJoinNext()");
338		return MS_FAILURE;
339		}
340		snprintf(sql, nSize, "SELECT %s FROM %s WHERE %s = '%s'", columns,
341		join->table, join->to, joininfo->from_value);
342		if (joininfo->layer_debug) {
343		msDebug("msPOSTGRESQLJoinNext(): executing %s.\n", sql);
344		}
345
346		free(columns);
347
348		joininfo->query_result = PQexec(joininfo->conn, sql);
349
350		if (!joininfo->query_result \|\|
351		PQresultStatus(joininfo->query_result) != PGRES_TUPLES_OK) {
352		msSetError(MS_QUERYERR, "Error executing queri %s: %s\n",
353		"msPOSTGRESQLJoinNext()", sql, PQerrorMessage(joininfo->conn));
354		if (joininfo->query_result) {
355		PQclear(joininfo->query_result);
356		joininfo->query_result = NULL;
357		}
358		free(sql);
359		return MS_FAILURE;
360		}
361		free(sql);
362		}
363		row_count = PQntuples(joininfo->query_result);
364
365		/* see if we're done processing this set */
366		if (joininfo->row_num >= row_count) {
367		return (MS_DONE);
368		}
369		if (joininfo->layer_debug) {
370		msDebug("msPOSTGRESQLJoinNext(): fetching row %ld.\n", joininfo->row_num);
371		}
372
373		/* Copy the resulting values into the joinObj. */
374		join->values = (char *)malloc(sizeof(char ) * join->numitems);
375		for (i = 0; i < join->numitems; i++) {
376		join->values[i] =
377		msStrdup(PQgetvalue(joininfo->query_result, joininfo->row_num, i));
378		}
379
380		joininfo->row_num++;
381
382		return MS_SUCCESS;
383		}
384
385		/************************************************************************/
386		/* msPOSTGRESQLJoinClose() */
387		/* */
388		/* Closes the connection and frees the resources used by the joininfo. */
389		/************************************************************************/
390
391		int msPOSTGRESQLJoinClose(joinObj *join) {
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 */
424	0	int msPOSTGRESQLJoinConnect(layerObj layer, joinObj join) {
425	0	msSetError(MS_QUERYERR, "PostgreSQL support not available.",
426	0	"msPOSTGRESQLJoinConnect()");
427	0	return MS_FAILURE;
428	0	}
429
430	0	int msPOSTGRESQLJoinPrepare(joinObj join, shapeObj shape) {
431	0	msSetError(MS_QUERYERR, "PostgreSQL support not available.",
432	0	"msPOSTGRESQLJoinPrepare()");
433	0	return MS_FAILURE;
434	0	}
435
436	0	int msPOSTGRESQLJoinNext(joinObj *join) {
437	0	msSetError(MS_QUERYERR, "PostgreSQL support not available.",
438	0	"msPOSTGRESQLJoinNext()");
439	0	return MS_FAILURE;
440	0	}
441
442	0	int msPOSTGRESQLJoinClose(joinObj *join) {
443	0	msSetError(MS_QUERYERR, "PostgreSQL support not available.",
444	0	"msPOSTGRESQLJoinClose()");
445	0	return MS_FAILURE;
446	0	}
447		#endif