~ubuntu-branches/ubuntu/saucy/mapserver/saucy-security
» Revision
31
: .pc/wfs_sql_injection.patch/mappostgis.c
« back to all changes in this revision
Viewing changes to .pc/wfs_sql_injection.patch/mappostgis.c
- browse files at revision 31
- compare with another revision
- download diff
- download tarball
- view history from revision 31
- Committer: Package Import Robot
- Author(s): Francesco Paolo Lovergine
- Date: 2011-12-23 14:02:06 UTC
- mfrom: (26.1.2 sid)
- Revision ID: package-import@ubuntu.com-20111223140206-n3h9t2hsa8hyslmu
Tags: 6.0.1-2
Added missed stuff for libmapscript-perl.
- files added:
- MIGRATION_GUIDE.txt
- mapscript/php
- mapscript/php/examples
- files removed:
- .pc
- .pc/debian-changes-5.6.6-1.1ubuntu1
- .pc/symbol_index_overflow
- .pc/wfs_sql_injection.patch
- mapscript/php3
- mapscript/php3/examples
- files modified:
- COMMITERS
- aclocal.m4 *
- tests/home.png *
added
removed
.pc/wfs_sql_injection.patch/mappostgis.c
1
/******************************************************************************
2
* $Id: mappostgis.c 10760 2010-11-24 17:29:47Z pramsey $
3
*
4
* Project: MapServer
5
* Purpose: PostGIS CONNECTIONTYPE support.
6
* Author: Paul Ramsey <pramsey@cleverelephant.ca>
7
* Dave Blasby <dblasby@gmail.com>
8
*
9
******************************************************************************
10
* Copyright (c) 2008 Paul Ramsey
11
* Copyright (c) 2002 Refractions Research
12
*
13
* Permission is hereby granted, free of charge, to any person obtaining a
14
* copy of this software and associated documentation files (the "Software"),
15
* to deal in the Software without restriction, including without limitation
16
* the rights to use, copy, modify, merge, publish, distribute, sublicense,
17
* and/or sell copies of the Software, and to permit persons to whom the
18
* Software is furnished to do so, subject to the following conditions:
19
*
20
* The above copyright notice and this permission notice shall be included in
21
* all copies of this Software or works derived from this Software.
22
*
23
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
24
* OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
25
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
26
* THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
27
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
28
* FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
29
* DEALINGS IN THE SOFTWARE.
30
****************************************************************************/
31
32
/*
33
** Some theory of operation:
34
**
35
** Build SQL from DATA statement and LAYER state. SQL is always of the form:
36
**
37
** SELECT [this, that, other], geometry, uid
38
** FROM [table|(subquery) as sub]
39
** WHERE [box] AND [filter]
40
**
41
** So the geometry always resides at layer->numitems and the uid always
42
** resides at layer->numitems + 1
43
**
44
** Geometry is requested as Base64 encoded WKB. The endian is always requested
45
** as the client endianness.
46
**
47
** msPostGISLayerWhichShapes creates SQL based on DATA and LAYER state,
48
** executes it, and places the un-read PGresult handle in the layerinfo->pgresult,
49
** setting the layerinfo->rownum to 0.
50
**
51
** msPostGISNextShape reads a row, increments layerinfo->rownum, and returns
52
** MS_SUCCESS, until rownum reaches ntuples, and it returns MS_DONE instead.
53
**
54
*/
55
56
/* GNU needs this for strcasestr */
57
#define _GNU_SOURCE
58
59
#include <assert.h>
60
#include <string.h>
61
#include "mapserver.h"
62
#include "maptime.h"
63
#include "mappostgis.h"
64
65
#ifndef FLT_MAX
66
#define FLT_MAX 25000000.0
67
#endif
68
69
#ifdef USE_POSTGIS
70
71
MS_CVSID("$Id: mappostgis.c 10760 2010-11-24 17:29:47Z pramsey $")
72
73
/*
74
** msPostGISCloseConnection()
75
**
76
** Handler registered witih msConnPoolRegister so that Mapserver
77
** can clean up open connections during a shutdown.
78
*/
79
void msPostGISCloseConnection(void *pgconn) {
80
PQfinish((PGconn*)pgconn);
81
}
82
83
/*
84
** msPostGISCreateLayerInfo()
85
*/
86
msPostGISLayerInfo *msPostGISCreateLayerInfo(void) {
87
msPostGISLayerInfo *layerinfo = malloc(sizeof(msPostGISLayerInfo));
88
layerinfo->sql = NULL;
89
layerinfo->srid = NULL;
90
layerinfo->uid = NULL;
91
layerinfo->pgconn = NULL;
92
layerinfo->pgresult = NULL;
93
layerinfo->geomcolumn = NULL;
94
layerinfo->fromsource = NULL;
95
layerinfo->endian = 0;
96
layerinfo->rownum = 0;
97
return layerinfo;
98
}
99
100
/*
101
** msPostGISFreeLayerInfo()
102
*/
103
void msPostGISFreeLayerInfo(layerObj *layer) {
104
msPostGISLayerInfo *layerinfo = NULL;
105
layerinfo = (msPostGISLayerInfo*)layer->layerinfo;
106
if ( layerinfo->sql ) free(layerinfo->sql);
107
if ( layerinfo->uid ) free(layerinfo->uid);
108
if ( layerinfo->srid ) free(layerinfo->srid);
109
if ( layerinfo->geomcolumn ) free(layerinfo->geomcolumn);
110
if ( layerinfo->fromsource ) free(layerinfo->fromsource);
111
if ( layerinfo->pgresult ) PQclear(layerinfo->pgresult);
112
if ( layerinfo->pgconn ) msConnPoolRelease(layer, layerinfo->pgconn);
113
free(layerinfo);
114
layer->layerinfo = NULL;
115
}
116
117
118
/*
119
** postgresqlNoticeHandler()
120
**
121
** Propagate messages from the database to the Mapserver log,
122
** set in PQsetNoticeProcessor during layer open.
123
*/
124
void postresqlNoticeHandler(void *arg, const char *message) {
125
layerObj *lp;
126
lp = (layerObj*)arg;
127
128
if (lp->debug) {
129
msDebug("%s\n", message);
130
}
131
}
132
133
/*
134
** TODO, review and clean
135
*/
136
static int force_to_points(unsigned char *wkb, shapeObj *shape)
137
{
138
/* we're going to make a 'line' for each entity (point, line or ring) in the geom collection */
139
140
int offset = 0;
141
int pt_offset;
142
int ngeoms;
143
int t, u, v;
144
int type, nrings, npoints;
145
lineObj line = {0, NULL};
146
147
shape->type = MS_SHAPE_NULL; /* nothing in it */
148
149
memcpy(&ngeoms, &wkb[5], 4);
150
offset = 9; /* where the first geometry is */
151
152
for(t=0; t < ngeoms; t++) {
153
memcpy(&type, &wkb[offset + 1], 4); /* type of this geometry */
154
155
if(type == 1) {
156
/* Point */
157
shape->type = MS_SHAPE_POINT;
158
line.numpoints = 1;
159
line.point = (pointObj *) malloc(sizeof(pointObj));
160
161
memcpy(&line.point[0].x, &wkb[offset + 5], 8);
162
memcpy(&line.point[0].y, &wkb[offset + 5 + 8], 8);
163
offset += 5 + 16;
164
msAddLine(shape, &line);
165
free(line.point);
166
} else if(type == 2) {
167
/* Linestring */
168
shape->type = MS_SHAPE_POINT;
169
170
memcpy(&line.numpoints, &wkb[offset+5], 4); /* num points */
171
line.point = (pointObj *) malloc(sizeof(pointObj) * line.numpoints);
172
for(u = 0; u < line.numpoints; u++) {
173
memcpy( &line.point[u].x, &wkb[offset+9 + (16 * u)], 8);
174
memcpy( &line.point[u].y, &wkb[offset+9 + (16 * u)+8], 8);
175
}
176
offset += 9 + 16 * line.numpoints; /* length of object */
177
msAddLine(shape, &line);
178
free(line.point);
179
} else if(type == 3) {
180
/* Polygon */
181
shape->type = MS_SHAPE_POINT;
182
183
memcpy(&nrings, &wkb[offset+5],4); /* num rings */
184
/* add a line for each polygon ring */
185
pt_offset = 0;
186
offset += 9; /* now points at 1st linear ring */
187
for(u = 0; u < nrings; u++) {
188
/* for each ring, make a line */
189
memcpy(&npoints, &wkb[offset], 4); /* num points */
190
line.numpoints = npoints;
191
line.point = (pointObj *) malloc(sizeof(pointObj)* npoints);
192
/* point struct */
193
for(v = 0; v < npoints; v++)
194
{
195
memcpy(&line.point[v].x, &wkb[offset + 4 + (16 * v)], 8);
196
memcpy(&line.point[v].y, &wkb[offset + 4 + (16 * v) + 8], 8);
197
}
198
/* make offset point to next linear ring */
199
msAddLine(shape, &line);
200
free(line.point);
201
offset += 4 + 16 *npoints;
202
}
203
}
204
}
205
206
return MS_SUCCESS;
207
}
208
209
/* convert the wkb into lines */
210
/* points-> remove */
211
/* lines -> pass through */
212
/* polys -> treat rings as lines */
213
214
static int force_to_lines(unsigned char *wkb, shapeObj *shape)
215
{
216
int offset = 0;
217
int pt_offset;
218
int ngeoms;
219
int t, u, v;
220
int type, nrings, npoints;
221
lineObj line = {0, NULL};
222
223
shape->type = MS_SHAPE_NULL; /* nothing in it */
224
225
memcpy(&ngeoms, &wkb[5], 4);
226
offset = 9; /* were the first geometry is */
227
for(t=0; t < ngeoms; t++) {
228
memcpy(&type, &wkb[offset + 1], 4); /* type of this geometry */
229
230
/* cannot do anything with a point */
231
232
if(type == 2) {
233
/* Linestring */
234
shape->type = MS_SHAPE_LINE;
235
236
memcpy(&line.numpoints, &wkb[offset + 5], 4);
237
line.point = (pointObj*) malloc(sizeof(pointObj) * line.numpoints );
238
for(u=0; u < line.numpoints; u++) {
239
memcpy(&line.point[u].x, &wkb[offset + 9 + (16 * u)], 8);
240
memcpy(&line.point[u].y, &wkb[offset + 9 + (16 * u)+8], 8);
241
}
242
offset += 9 + 16 * line.numpoints; /* length of object */
243
msAddLine(shape, &line);
244
free(line.point);
245
} else if(type == 3) {
246
/* polygon */
247
shape->type = MS_SHAPE_LINE;
248
249
memcpy(&nrings, &wkb[offset + 5], 4); /* num rings */
250
/* add a line for each polygon ring */
251
pt_offset = 0;
252
offset += 9; /* now points at 1st linear ring */
253
for(u = 0; u < nrings; u++) {
254
/* for each ring, make a line */
255
memcpy(&npoints, &wkb[offset], 4);
256
line.numpoints = npoints;
257
line.point = (pointObj*) malloc(sizeof(pointObj) * npoints);
258
/* point struct */
259
for(v = 0; v < npoints; v++) {
260
memcpy(&line.point[v].x, &wkb[offset + 4 + (16 * v)], 8);
261
memcpy(&line.point[v].y, &wkb[offset + 4 + (16 * v) + 8], 8);
262
}
263
/* make offset point to next linear ring */
264
msAddLine(shape, &line);
265
free(line.point);
266
offset += 4 + 16 * npoints;
267
}
268
}
269
}
270
271
return MS_SUCCESS;
272
}
273
274
/* point -> reject */
275
/* line -> reject */
276
/* polygon -> lines of linear rings */
277
static int force_to_polygons(unsigned char *wkb, shapeObj *shape)
278
{
279
int offset = 0;
280
int pt_offset;
281
int ngeoms;
282
int t, u, v;
283
int type, nrings, npoints;
284
lineObj line = {0, NULL};
285
286
shape->type = MS_SHAPE_NULL; /* nothing in it */
287
288
memcpy(&ngeoms, &wkb[5], 4);
289
offset = 9; /* were the first geometry is */
290
for(t = 0; t < ngeoms; t++) {
291
memcpy(&type, &wkb[offset + 1], 4); /* type of this geometry */
292
293
if(type == 3) {
294
/* polygon */
295
shape->type = MS_SHAPE_POLYGON;
296
297
memcpy(&nrings, &wkb[offset + 5], 4); /* num rings */
298
/* add a line for each polygon ring */
299
pt_offset = 0;
300
offset += 9; /* now points at 1st linear ring */
301
for(u=0; u < nrings; u++) {
302
/* for each ring, make a line */
303
memcpy(&npoints, &wkb[offset], 4); /* num points */
304
line.numpoints = npoints;
305
line.point = (pointObj*) malloc(sizeof(pointObj) * npoints);
306
for(v=0; v < npoints; v++) {
307
memcpy(&line.point[v].x, &wkb[offset + 4 + (16 * v)], 8);
308
memcpy(&line.point[v].y, &wkb[offset + 4 + (16 * v) + 8], 8);
309
}
310
/* make offset point to next linear ring */
311
msAddLine(shape, &line);
312
free(line.point);
313
offset += 4 + 16 * npoints;
314
}
315
}
316
}
317
318
return MS_SUCCESS;
319
}
320
321
/* if there is any polygon in wkb, return force_polygon */
322
/* if there is any line in wkb, return force_line */
323
/* otherwise return force_point */
324
325
static int dont_force(unsigned char *wkb, shapeObj *shape)
326
{
327
int offset =0;
328
int ngeoms;
329
int type, t;
330
int best_type;
331
332
best_type = MS_SHAPE_NULL; /* nothing in it */
333
334
memcpy(&ngeoms, &wkb[5], 4);
335
offset = 9; /* were the first geometry is */
336
for(t = 0; t < ngeoms; t++) {
337
memcpy(&type, &wkb[offset + 1], 4); /* type of this geometry */
338
339
if(type == 3) {
340
best_type = MS_SHAPE_POLYGON;
341
} else if(type ==2 && best_type != MS_SHAPE_POLYGON) {
342
best_type = MS_SHAPE_LINE;
343
} else if(type == 1 && best_type == MS_SHAPE_NULL) {
344
best_type = MS_SHAPE_POINT;
345
}
346
}
347
348
if(best_type == MS_SHAPE_POINT) {
349
return force_to_points(wkb, shape);
350
}
351
if(best_type == MS_SHAPE_LINE) {
352
return force_to_lines(wkb, shape);
353
}
354
if(best_type == MS_SHAPE_POLYGON) {
355
return force_to_polygons(wkb, shape);
356
}
357
358
return MS_FAILURE; /* unknown type */
359
}
360
361
/* find the bounds of the shape */
362
static void find_bounds(shapeObj *shape)
363
{
364
int t, u;
365
int first_one = 1;
366
367
for(t = 0; t < shape->numlines; t++) {
368
for(u = 0; u < shape->line[t].numpoints; u++) {
369
if(first_one) {
370
shape->bounds.minx = shape->line[t].point[u].x;
371
shape->bounds.maxx = shape->line[t].point[u].x;
372
373
shape->bounds.miny = shape->line[t].point[u].y;
374
shape->bounds.maxy = shape->line[t].point[u].y;
375
first_one = 0;
376
} else {
377
if(shape->line[t].point[u].x < shape->bounds.minx) {
378
shape->bounds.minx = shape->line[t].point[u].x;
379
}
380
if(shape->line[t].point[u].x > shape->bounds.maxx) {
381
shape->bounds.maxx = shape->line[t].point[u].x;
382
}
383
384
if(shape->line[t].point[u].y < shape->bounds.miny) {
385
shape->bounds.miny = shape->line[t].point[u].y;
386
}
387
if(shape->line[t].point[u].y > shape->bounds.maxy) {
388
shape->bounds.maxy = shape->line[t].point[u].y;
389
}
390
}
391
}
392
}
393
}
394
/* TODO Review and clean above! */
395
396
397
static int msPostGISRetrievePgVersion(PGconn *pgconn) {
398
#ifndef POSTGIS_HAS_SERVER_VERSION
399
int pgVersion = 0;
400
char *strVersion = NULL;
401
char *strParts[3] = { NULL, NULL, NULL };
402
int i = 0, j = 0, len = 0;
403
int factor = 10000;
404
405
if (pgconn == NULL) {
406
msSetError(MS_QUERYERR, "Layer does not have a postgis connection.", "msPostGISRetrievePgVersion()");
407
return(MS_FAILURE);
408
}
409
410
if (! PQparameterStatus(pgconn, "server_version") )
411
return(MS_FAILURE);
412
413
strVersion = strdup(PQparameterStatus(pgconn, "server_version"));
414
if( ! strVersion )
415
return MS_FAILURE;
416
strParts[j] = strVersion;
417
j++;
418
len = strlen(strVersion);
419
for( i = 0; i < len; i++ ) {
420
if( strVersion[i] == '.' ) {
421
strVersion[i] = '\0';
422
423
if( j < 3 ) {
424
strParts[j] = strVersion + i + 1;
425
j++;
426
}
427
else {
428
free(strVersion);
429
msSetError(MS_QUERYERR, "Too many parts in version string.", "msPostGISRetrievePgVersion()");
430
return MS_FAILURE;
431
}
432
}
433
}
434
435
for( j = 0; j < 3 && strParts[j]; j++ ) {
436
if( atoi(strParts[j]) ) {
437
pgVersion += factor * atoi(strParts[j]);
438
}
439
else {
440
free(strVersion);
441
msSetError(MS_QUERYERR, "Unable to parse version string.", "msPostGISRetrievePgVersion()");
442
return MS_FAILURE;
443
}
444
factor = factor / 100;
445
}
446
free(strVersion);
447
return pgVersion;
448
#else
449
return PQserverVersion(pgconn);
450
#endif
451
}
452
453
/*
454
** msPostGISRetrievePK()
455
**
456
** Find out that the primary key is for this layer.
457
** The layerinfo->fromsource must already be populated and
458
** must not be a subquery.
459
*/
460
int msPostGISRetrievePK(layerObj *layer) {
461
PGresult *pgresult = NULL;
462
char *sql = 0;
463
msPostGISLayerInfo *layerinfo = 0;
464
int length;
465
int pgVersion;
466
char *pos_sep;
467
char *schema = NULL;
468
char *table = NULL;
469
470
if (layer->debug) {
471
msDebug("msPostGISRetrievePK called.\n");
472
}
473
474
layerinfo = (msPostGISLayerInfo *) layer->layerinfo;
475
476
/* Attempt to separate fromsource into schema.table */
477
pos_sep = strstr(layerinfo->fromsource, ".");
478
if (pos_sep) {
479
length = strlen(layerinfo->fromsource) - strlen(pos_sep);
480
schema = (char*)malloc(length + 1);
481
strncpy(schema, layerinfo->fromsource, length);
482
schema[length] = '\0';
483
484
length = strlen(pos_sep);
485
table = (char*)malloc(length);
486
strncpy(table, pos_sep + 1, length - 1);
487
table[length - 1] = '\0';
488
489
if (layer->debug) {
490
msDebug("msPostGISRetrievePK(): Found schema %s, table %s.\n", schema, table);
491
}
492
}
493
494
if (layerinfo->pgconn == NULL) {
495
msSetError(MS_QUERYERR, "Layer does not have a postgis connection.", "msPostGISRetrievePK()");
496
return(MS_FAILURE);
497
}
498
pgVersion = msPostGISRetrievePgVersion(layerinfo->pgconn);
499
500
if (pgVersion < 70000) {
501
if (layer->debug) {
502
msDebug("msPostGISRetrievePK(): Major version below 7.\n");
503
}
504
return MS_FAILURE;
505
}
506
if (pgVersion < 70200) {
507
if (layer->debug) {
508
msDebug("msPostGISRetrievePK(): Version below 7.2.\n");
509
}
510
return MS_FAILURE;
511
}
512
if (pgVersion < 70300) {
513
/*
514
** PostgreSQL v7.2 has a different representation of primary keys that
515
** later versions. This currently does not explicitly exclude
516
** multicolumn primary keys.
517
*/
518
static char *v72sql = "select b.attname from pg_class as a, pg_attribute as b, (select oid from pg_class where relname = '%s') as c, pg_index as d where d.indexrelid = a.oid and d.indrelid = c.oid and d.indisprimary and b.attrelid = a.oid and a.relnatts = 1";
519
sql = malloc(strlen(layerinfo->fromsource) + strlen(v72sql));
520
sprintf(sql, v72sql, layerinfo->fromsource);
521
} else {
522
/*
523
** PostgreSQL v7.3 and later treat primary keys as constraints.
524
** We only support single column primary keys, so multicolumn
525
** pks are explicitly excluded from the query.
526
*/
527
if (schema && table) {
528
static char *v73sql = "select attname from pg_attribute, pg_constraint, pg_class, pg_namespace where pg_constraint.conrelid = pg_class.oid and pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = '%s' and pg_class.relnamespace = pg_namespace.oid and pg_namespace.nspname = '%s' and pg_constraint.conkey[2] is null";
529
sql = malloc(strlen(schema) + strlen(table) + strlen(v73sql));
530
sprintf(sql, v73sql, table, schema);
531
free(table);
532
free(schema);
533
} else {
534
static char *v73sql = "select attname from pg_attribute, pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = '%s' and pg_table_is_visible(pg_class.oid) and pg_constraint.conkey[2] is null";
535
sql = malloc(strlen(layerinfo->fromsource) + strlen(v73sql));
536
sprintf(sql, v73sql, layerinfo->fromsource);
537
}
538
}
539
540
if (layer->debug > 1) {
541
msDebug("msPostGISRetrievePK: %s\n", sql);
542
}
543
544
layerinfo = (msPostGISLayerInfo *) layer->layerinfo;
545
546
if (layerinfo->pgconn == NULL) {
547
msSetError(MS_QUERYERR, "Layer does not have a postgis connection.", "msPostGISRetrievePK()");
548
free(sql);
549
return(MS_FAILURE);
550
}
551
552
pgresult = PQexec(layerinfo->pgconn, sql);
553
if ( !pgresult || PQresultStatus(pgresult) != PGRES_TUPLES_OK) {
554
char *tmp1;
555
char *tmp2 = NULL;
556
557
tmp1 = "Error executing POSTGIS statement (msPostGISRetrievePK():";
558
tmp2 = (char*)malloc(sizeof(char)*(strlen(tmp1) + strlen(sql) + 1));
559
strcpy(tmp2, tmp1);
560
strcat(tmp2, sql);
561
msSetError(MS_QUERYERR, tmp2, "msPostGISRetrievePK()");
562
free(tmp2);
563
free(sql);
564
return(MS_FAILURE);
565
566
}
567
568
if (PQntuples(pgresult) < 1) {
569
if (layer->debug) {
570
msDebug("msPostGISRetrievePK: No results found.\n");
571
}
572
PQclear(pgresult);
573
free(sql);
574
return MS_FAILURE;
575
}
576
if (PQntuples(pgresult) > 1) {
577
if (layer->debug) {
578
msDebug("msPostGISRetrievePK: Multiple results found.\n");
579
}
580
PQclear(pgresult);
581
free(sql);
582
return MS_FAILURE;
583
}
584
585
if (PQgetisnull(pgresult, 0, 0)) {
586
if (layer->debug) {
587
msDebug("msPostGISRetrievePK: Null result returned.\n");
588
}
589
PQclear(pgresult);
590
free(sql);
591
return MS_FAILURE;
592
}
593
594
layerinfo->uid = (char*)malloc(PQgetlength(pgresult, 0, 0) + 1);
595
strcpy(layerinfo->uid, PQgetvalue(pgresult, 0, 0));
596
597
PQclear(pgresult);
598
free(sql);
599
return MS_SUCCESS;
600
}
601
602
603
/*
604
** msPostGISParseData()
605
**
606
** Parse the DATA string for geometry column name, table name,
607
** unique id column, srid, and SQL string.
608
*/
609
int msPostGISParseData(layerObj *layer) {
610
char *pos_opt, *pos_scn, *tmp, *pos_srid, *pos_uid, *data;
611
int slength;
612
msPostGISLayerInfo *layerinfo;
613
614
assert(layer != NULL);
615
assert(layer->layerinfo != NULL);
616
617
layerinfo = (msPostGISLayerInfo*)(layer->layerinfo);
618
619
if (layer->debug) {
620
msDebug("msPostGISParseData called.\n");
621
}
622
623
if (!layer->data) {
624
msSetError(MS_QUERYERR, "Missing DATA clause. DATA statement must contain 'geometry_column from table_name' or 'geometry_column from (sub-query) as sub'.", "msPostGISParseData()");
625
return MS_FAILURE;
626
}
627
data = layer->data;
628
629
/*
630
** Clean up any existing strings first, as we will be populating these fields.
631
*/
632
if( layerinfo->srid ) {
633
free(layerinfo->srid);
634
layerinfo->srid = NULL;
635
}
636
if( layerinfo->uid ) {
637
free(layerinfo->uid);
638
layerinfo->uid = NULL;
639
}
640
if( layerinfo->geomcolumn ) {
641
free(layerinfo->geomcolumn);
642
layerinfo->geomcolumn = NULL;
643
}
644
if( layerinfo->fromsource ) {
645
free(layerinfo->fromsource);
646
layerinfo->fromsource = NULL;
647
}
648
649
/*
650
** Look for the optional ' using unique ID' string first.
651
*/
652
pos_uid = strcasestr(data, " using unique ");
653
if (pos_uid) {
654
/* Find the end of this case 'using unique ftab_id using srid=33' */
655
tmp = strstr(pos_uid + 14, " ");
656
/* Find the end of this case 'using srid=33 using unique ftab_id' */
657
if (!tmp) {
658
tmp = pos_uid + strlen(pos_uid);
659
}
660
layerinfo->uid = (char*) malloc((tmp - (pos_uid + 14)) + 1);
661
strncpy(layerinfo->uid, pos_uid + 14, tmp - (pos_uid + 14));
662
(layerinfo->uid)[tmp - (pos_uid + 14)] = '\0'; /* null terminate it */
663
msStringTrim(layerinfo->uid);
664
}
665
666
/*
667
** Look for the optional ' using srid=333 ' string next.
668
*/
669
pos_srid = strcasestr(data, " using srid=");
670
if (!pos_srid) {
671
layerinfo->srid = (char*) malloc(1);
672
(layerinfo->srid)[0] = '\0'; /* no SRID, so return just null terminator*/
673
} else {
674
slength = strspn(pos_srid + 12, "-0123456789");
675
if (!slength) {
676
msSetError(MS_QUERYERR, "Error parsing PostGIS DATA variable. You specified 'USING SRID' but didnt have any numbers! %s", "msPostGISParseData()", data);
677
return MS_FAILURE;
678
} else {
679
layerinfo->srid = (char*) malloc(slength + 1);
680
strncpy(layerinfo->srid, pos_srid + 12, slength);
681
(layerinfo->srid)[slength] = '\0'; /* null terminate it */
682
msStringTrim(layerinfo->srid);
683
}
684
}
685
686
/*
687
** This is a little hack so the rest of the code works.
688
** pos_opt should point to the start of the optional blocks.
689
**
690
** If they are both set, return the smaller one.
691
*/
692
if (pos_srid && pos_uid) {
693
pos_opt = (pos_srid > pos_uid) ? pos_uid : pos_srid;
694
}
695
/* If one or none is set, return the larger one. */
696
else {
697
pos_opt = (pos_srid > pos_uid) ? pos_srid : pos_uid;
698
}
699
/* No pos_opt? Move it to the end of the string. */
700
if (!pos_opt) {
701
pos_opt = data + strlen(data);
702
}
703
704
/*
705
** Scan for the 'geometry from table' or 'geometry from () as foo' clause.
706
*/
707
pos_scn = strcasestr(data, " from ");
708
if (!pos_scn) {
709
msSetError(MS_QUERYERR, "Error parsing PostGIS DATA variable. Must contain 'geometry from table' or 'geometry from (subselect) as foo'. %s", "msPostGISParseData()", data);
710
return MS_FAILURE;
711
}
712
713
/* Copy the geometry column name */
714
layerinfo->geomcolumn = (char*) malloc((pos_scn - data) + 1);
715
strncpy(layerinfo->geomcolumn, data, pos_scn - data);
716
(layerinfo->geomcolumn)[pos_scn - data] = '\0';
717
msStringTrim(layerinfo->geomcolumn);
718
719
/* Copy the table name or sub-select clause */
720
layerinfo->fromsource = (char*) malloc((pos_opt - (pos_scn + 6)) + 1);
721
strncpy(layerinfo->fromsource, pos_scn + 6, pos_opt - (pos_scn + 6));
722
(layerinfo->fromsource)[pos_opt - (pos_scn + 6)] = '\0';
723
msStringTrim(layerinfo->fromsource);
724
725
/* Something is wrong, our goemetry column and table references are not there. */
726
if (strlen(layerinfo->fromsource) < 1 || strlen(layerinfo->geomcolumn) < 1) {
727
msSetError(MS_QUERYERR, "Error parsing PostGIS DATA variable. Must contain 'geometry from table' or 'geometry from (subselect) as foo'. %s", "msPostGISParseData()", data);
728
return MS_FAILURE;
729
}
730
731
/*
732
** We didn't find a ' using unique ' in the DATA string so try and find a
733
** primary key on the table.
734
*/
735
if ( ! (layerinfo->uid) ) {
736
if ( strstr(layerinfo->fromsource, " ") ) {
737
msSetError(MS_QUERYERR, "Error parsing PostGIS DATA variable. You must specifiy 'using unique' when supplying a subselect in the data definition.", "msPostGISParseData()");
738
return MS_FAILURE;
739
}
740
if ( msPostGISRetrievePK(layer) != MS_SUCCESS ) {
741
/* No user specified unique id so we will use the PostgreSQL oid */
742
/* TODO: Deprecate this, oids are deprecated in PostgreSQL */
743
layerinfo->uid = strdup("oid");
744
}
745
}
746
747
if (layer->debug) {
748
msDebug("msPostGISParseData: unique_column=%s, srid=%s, geom_column_name=%s, table_name=%s\n", layerinfo->uid, layerinfo->srid, layerinfo->geomcolumn, layerinfo->fromsource);
749
}
750
return MS_SUCCESS;
751
}
752
753
754
755
/*
756
** Decode a hex character.
757
*/
758
static unsigned char msPostGISHexDecodeChar[256] = {
759
/* not Base64 characters */
760
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
761
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
762
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
763
/* 0-9 */
764
0,1,2,3,4,5,6,7,8,9,
765
/* not Hex characters */
766
64,64,64,64,64,64,64,
767
/* A-F */
768
10,11,12,13,14,15,
769
/* not Hex characters */
770
64,64,64,64,64,64,64,64,64,
771
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
772
64,10,11,12,13,14,15,64,64,64,64,64,64,64,64,64,
773
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
774
/* not Hex characters (upper 128 characters) */
775
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
776
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
777
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
778
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
779
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
780
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
781
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
782
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
783
};
784
785
/*
786
** Decode hex string "src" (null terminated)
787
** into "dest" (not null terminated).
788
** Returns length of decoded array or 0 on failure.
789
*/
790
int msPostGISHexDecode(unsigned char *dest, const char *src, int srclen) {
791
792
if (src && *src && (srclen % 2 == 0) ) {
793
794
unsigned char *p = dest;
795
int i;
796
797
for ( i=0; i<srclen; i+=2 ) {
798
register unsigned char b1=0, b2=0;
799
register unsigned char c1 = src[i];
800
register unsigned char c2 = src[i + 1];
801
802
b1 = msPostGISHexDecodeChar[c1];
803
b2 = msPostGISHexDecodeChar[c2];
804
805
*p++ = (b1 << 4) | b2;
806
807
}
808
return(p-dest);
809
}
810
return 0;
811
}
812
813
/*
814
** Decode a base64 character.
815
*/
816
static unsigned char msPostGISBase64DecodeChar[256] = {
817
/* not Base64 characters */
818
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
819
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
820
64,64,64,64,64,64,64,64,64,64,64,
821
/* + */
822
62,
823
/* not Base64 characters */
824
64,64,64,
825
/* / */
826
63,
827
/* 0-9 */
828
52,53,54,55,56,57,58,59,60,61,
829
/* not Base64 characters */
830
64,64,64,64,64,64,64,
831
/* A-Z */
832
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,
833
/* not Base64 characters */
834
64,64,64,64,64,64,
835
/* a-z */
836
26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,
837
/* not Base64 characters */
838
64,64,64,64,64,
839
/* not Base64 characters (upper 128 characters) */
840
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
841
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
842
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
843
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
844
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
845
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
846
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,
847
64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
848
};
849
850
/*
851
** Decode base64 string "src" (null terminated)
852
** into "dest" (not null terminated).
853
** Returns length of decoded array or 0 on failure.
854
*/
855
int msPostGISBase64Decode(unsigned char *dest, const char *src, int srclen) {
856
857
if (src && *src) {
858
859
unsigned char *p = dest;
860
int i, j, k;
861
unsigned char *buf = calloc(srclen + 1, sizeof(unsigned char));
862
863
/* Drop illegal chars first */
864
for (i=0, j=0; src[i]; i++) {
865
unsigned char c = src[i];
866
if ( (msPostGISBase64DecodeChar[c] != 64) || (c == '=') ) {
867
buf[j++] = c;
868
}
869
}
870
871
for (k=0; k<j; k+=4) {
872
register unsigned char c1='A', c2='A', c3='A', c4='A';
873
register unsigned char b1=0, b2=0, b3=0, b4=0;
874
875
c1 = buf[k];
876
877
if (k+1<j) {
878
c2 = buf[k+1];
879
}
880
if (k+2<j) {
881
c3 = buf[k+2];
882
}
883
if (k+3<j) {
884
c4 = buf[k+3];
885
}
886
887
b1 = msPostGISBase64DecodeChar[c1];
888
b2 = msPostGISBase64DecodeChar[c2];
889
b3 = msPostGISBase64DecodeChar[c3];
890
b4 = msPostGISBase64DecodeChar[c4];
891
892
*p++=((b1<<2)|(b2>>4) );
893
if (c3 != '=') {
894
*p++=(((b2&0xf)<<4)|(b3>>2) );
895
}
896
if (c4 != '=') {
897
*p++=(((b3&0x3)<<6)|b4 );
898
}
899
}
900
free(buf);
901
return(p-dest);
902
}
903
return 0;
904
}
905
906
907
/*
908
** msPostGISBuildSQLBox()
909
**
910
** Returns malloc'ed char* that must be freed by caller.
911
*/
912
char *msPostGISBuildSQLBox(layerObj *layer, rectObj *rect, char *strSRID) {
913
914
char *strBox = NULL;
915
size_t sz;
916
917
if (layer->debug) {
918
msDebug("msPostGISBuildSQLBox called.\n");
919
}
920
921
if ( strSRID ) {
922
static char *strBoxTemplate = "GeomFromText('POLYGON((%.15g %.15g,%.15g %.15g,%.15g %.15g,%.15g %.15g,%.15g %.15g))',%s)";
923
/* 10 doubles + 1 integer + template characters */
924
sz = 10 * 22 + strlen(strSRID) + strlen(strBoxTemplate);
925
strBox = (char*)malloc(sz+1); /* add space for terminating NULL */
926
if ( sz <= snprintf(strBox, sz, strBoxTemplate,
927
rect->minx, rect->miny,
928
rect->minx, rect->maxy,
929
rect->maxx, rect->maxy,
930
rect->maxx, rect->miny,
931
rect->minx, rect->miny,
932
strSRID) )
933
{
934
msSetError(MS_MISCERR,"Bounding box digits truncated.","msPostGISBuildSQLBox");
935
return 0;
936
}
937
} else {
938
static char *strBoxTemplate = "GeomFromText('POLYGON((%.15g %.15g,%.15g %.15g,%.15g %.15g,%.15g %.15g,%.15g %.15g))')";
939
/* 10 doubles + template characters */
940
sz = 10 * 22 + strlen(strBoxTemplate);
941
strBox = (char*)malloc(sz+1); /* add space for terminating NULL */
942
if ( sz <= snprintf(strBox, sz, strBoxTemplate,
943
rect->minx, rect->miny,
944
rect->minx, rect->maxy,
945
rect->maxx, rect->maxy,
946
rect->maxx, rect->miny,
947
rect->minx, rect->miny) )
948
{
949
msSetError(MS_MISCERR,"Bounding box digits truncated.","msPostGISBuildSQLBox");
950
return 0;
951
}
952
953
}
954
955
return strBox;
956
957
}
958
959
/*
960
** msPostGISBuildSQLItems()
961
**
962
** Returns malloc'ed char* that must be freed by caller.
963
*/
964
char *msPostGISBuildSQLItems(layerObj *layer) {
965
966
char *strEndian = NULL;
967
char *strGeom = NULL;
968
char *strItems = NULL;
969
msPostGISLayerInfo *layerinfo = NULL;
970
971
if (layer->debug) {
972
msDebug("msPostGISBuildSQLItems called.\n");
973
}
974
975
assert( layer->layerinfo != NULL);
976
977
layerinfo = (msPostGISLayerInfo *)layer->layerinfo;
978
979
if ( ! layerinfo->geomcolumn ) {
980
msSetError(MS_MISCERR, "layerinfo->geomcolumn is not initialized.", "msPostGISBuildSQLItems()");
981
return 0;
982
}
983
984
/*
985
** Get the server to transform the geometry into our
986
** native endian before transmitting it to us..
987
*/
988
if (layerinfo->endian == LITTLE_ENDIAN) {
989
strEndian = "NDR";
990
} else {
991
strEndian = "XDR";
992
}
993
994
{
995
/*
996
** We transfer the geometry from server to client as a
997
** base64 encoded WKB byte-array. We will have to decode this
998
** data once we get it. Forcing 2D removes ordinates we don't
999
** need, saving time. Forcing collection reduces the number
1000
** of input types to handle.
1001
*/
1002
1003
#if TRANSFER_ENCODING == 64
1004
static char *strGeomTemplate = "encode(AsBinary(force_collection(force_2d(\"%s\")),'%s'),'base64') as geom,\"%s\"";
1005
#else
1006
static char *strGeomTemplate = "encode(AsBinary(force_collection(force_2d(\"%s\")),'%s'),'hex') as geom,\"%s\"";
1007
#endif
1008
strGeom = (char*)malloc(strlen(strGeomTemplate) + strlen(strEndian) + strlen(layerinfo->geomcolumn) + strlen(layerinfo->uid));
1009
sprintf(strGeom, strGeomTemplate, layerinfo->geomcolumn, strEndian, layerinfo->uid);
1010
}
1011
1012
if( layer->debug > 1 ) {
1013
msDebug("msPostGISBuildSQLItems: %d items requested.\n",layer->numitems);
1014
}
1015
1016
/*
1017
** Not requesting items? We just need geometry and unique id.
1018
*/
1019
if (layer->numitems == 0) {
1020
strItems = strdup(strGeom);
1021
}
1022
/*
1023
** Build SQL to pull all the items.
1024
*/
1025
else {
1026
int length = strlen(strGeom) + 2;
1027
int t;
1028
for ( t = 0; t < layer->numitems; t++ ) {
1029
length += strlen(layer->items[t]) + 3; /* itemname + "", */
1030
}
1031
strItems = (char*)malloc(length);
1032
strItems[0] = '\0';
1033
for ( t = 0; t < layer->numitems; t++ ) {
1034
strcat(strItems, "\"");
1035
strcat(strItems, layer->items[t]);
1036
strcat(strItems, "\",");
1037
}
1038
strcat(strItems, strGeom);
1039
}
1040
1041
free(strGeom);
1042
return strItems;
1043
}
1044
1045
/*
1046
** msPostGISBuildSQLSRID()
1047
**
1048
** Returns malloc'ed char* that must be freed by caller.
1049
*/
1050
char *msPostGISBuildSQLSRID(layerObj *layer) {
1051
1052
char *strSRID = NULL;
1053
msPostGISLayerInfo *layerinfo = NULL;
1054
1055
if (layer->debug) {
1056
msDebug("msPostGISBuildSQLSRID called.\n");
1057
}
1058
1059
assert( layer->layerinfo != NULL);
1060
1061
layerinfo = (msPostGISLayerInfo *)layer->layerinfo;
1062
1063
/* An SRID was already provided in the DATA line. */
1064
if ( layerinfo->srid && (strlen(layerinfo->srid) > 0) ) {
1065
strSRID = strdup(layerinfo->srid);
1066
if( layer->debug > 1 ) {
1067
msDebug("msPostGISBuildSQLSRID: SRID provided (%s)\n", strSRID);
1068
}
1069
}
1070
/*
1071
** No SRID in data line, so extract target table from the 'fromsource'.
1072
** Either of form "thetable" (one word) or "(select ... from thetable)"
1073
** or "(select ... from thetable where ...)".
1074
*/
1075
else {
1076
char *f_table_name;
1077
char *strSRIDTemplate = "find_srid('','%s','%s')";
1078
char *pos = strstr(layerinfo->fromsource, " ");
1079
if( layer->debug > 1 ) {
1080
msDebug("msPostGISBuildSQLSRID: Building find_srid line.\n", strSRID);
1081
}
1082
1083
if ( ! pos ) {
1084
/* target table is one word */
1085
f_table_name = strdup(layerinfo->fromsource);
1086
if( layer->debug > 1 ) {
1087
msDebug("msPostGISBuildSQLSRID: Found table (%s)\n", f_table_name);
1088
}
1089
} else {
1090
/* target table is hiding in sub-select clause */
1091
pos = strcasestr(layerinfo->fromsource, " from ");
1092
if ( pos ) {
1093
char *pos_paren;
1094
char *pos_space;
1095
pos += 6; /* should be start of table name */
1096
pos_paren = strstr(pos, ")"); /* first ) after table name */
1097
pos_space = strstr(pos, " "); /* first space after table name */
1098
if ( pos_space < pos_paren ) {
1099
/* found space first */
1100
f_table_name = (char*)malloc(pos_space - pos + 1);
1101
strncpy(f_table_name, pos, pos_space - pos);
1102
f_table_name[pos_space - pos] = '\0';
1103
} else {
1104
/* found ) first */
1105
f_table_name = (char*)malloc(pos_paren - pos + 1);
1106
strncpy(f_table_name, pos, pos_paren - pos);
1107
f_table_name[pos_paren - pos] = '\0';
1108
}
1109
} else {
1110
/* should not happen */
1111
return 0;
1112
}
1113
}
1114
strSRID = malloc(strlen(strSRIDTemplate) + strlen(f_table_name) + strlen(layerinfo->geomcolumn));
1115
sprintf(strSRID, strSRIDTemplate, f_table_name, layerinfo->geomcolumn);
1116
if ( f_table_name ) free(f_table_name);
1117
}
1118
return strSRID;
1119
}
1120
1121
/*
1122
** msPostGISBuildSQLFrom()
1123
**
1124
** Returns malloc'ed char* that must be freed by caller.
1125
*/
1126
char *msPostGISBuildSQLFrom(layerObj *layer, rectObj *rect) {
1127
char *fromsource =0;
1128
char *strFrom = 0;
1129
static char *boxToken = "!BOX!";
1130
static int boxTokenLength = 5;
1131
msPostGISLayerInfo *layerinfo;
1132
1133
if (layer->debug) {
1134
msDebug("msPostGISBuildSQLFrom called.\n");
1135
}
1136
1137
assert( layer->layerinfo != NULL);
1138
1139
layerinfo = (msPostGISLayerInfo *)layer->layerinfo;
1140
1141
if ( ! layerinfo->fromsource ) {
1142
msSetError(MS_MISCERR, "Layerinfo->fromsource is not initialized.", "msPostGISBuildSQLFrom()");
1143
return 0;
1144
}
1145
1146
fromsource = layerinfo->fromsource;
1147
1148
/*
1149
** If there's a '!BOX!' in our source we need to substitute the
1150
** current rectangle for it...
1151
*/
1152
if ( strstr(fromsource, boxToken) && rect ) {
1153
char *result = NULL;
1154
char *strBox;
1155
char *strSRID;
1156
1157
/* We see to set the SRID on the box, but to what SRID? */
1158
strSRID = msPostGISBuildSQLSRID(layer);
1159
if ( ! strSRID ) {
1160
return 0;
1161
}
1162
1163
/* Create a suitable SQL string from the rectangle and SRID. */
1164
strBox = msPostGISBuildSQLBox(layer, rect, strSRID);
1165
if ( ! strBox ) {
1166
msSetError(MS_MISCERR, "Unable to build box SQL.", "msPostGISBuildSQLFrom()");
1167
if (strSRID) free(strSRID);
1168
return 0;
1169
}
1170
1171
/* Do the substitution. */
1172
while ( strstr(fromsource,boxToken) ) {
1173
char *start, *end;
1174
char *oldresult = result;
1175
start = strstr(fromsource, boxToken);
1176
end = start + boxTokenLength;
1177
1178
result = (char*)malloc((start - fromsource) + strlen(strBox) + strlen(end) +1);
1179
1180
strncpy(result, fromsource, start - fromsource);
1181
strcpy(result + (start - fromsource), strBox);
1182
strcat(result, end);
1183
1184
fromsource = result;
1185
if (oldresult != NULL)
1186
free(oldresult);
1187
}
1188
1189
if (strSRID) free(strSRID);
1190
if (strBox) free(strBox);
1191
1192
}
1193
/*
1194
** Otherwise we can just take the fromsource "as is".
1195
*/
1196
strFrom = strdup(fromsource);
1197
1198
return strFrom;
1199
}
1200
1201
/*
1202
** msPostGISBuildSQLWhere()
1203
**
1204
** Returns malloc'ed char* that must be freed by caller.
1205
*/
1206
char *msPostGISBuildSQLWhere(layerObj *layer, rectObj *rect, long *uid) {
1207
char *strRect = 0;
1208
char *strFilter = 0;
1209
char *strUid = 0;
1210
char *strWhere = 0;
1211
char *strLimit = 0;
1212
size_t strRectLength = 0;
1213
size_t strFilterLength = 0;
1214
size_t strUidLength = 0;
1215
size_t strLimitLength = 0;
1216
int insert_and = 0;
1217
msPostGISLayerInfo *layerinfo;
1218
1219
if (layer->debug) {
1220
msDebug("msPostGISBuildSQLWhere called.\n");
1221
}
1222
1223
assert( layer->layerinfo != NULL);
1224
1225
layerinfo = (msPostGISLayerInfo *)layer->layerinfo;
1226
1227
if ( ! layerinfo->fromsource ) {
1228
msSetError(MS_MISCERR, "Layerinfo->fromsource is not initialized.", "msPostGISBuildSQLFrom()");
1229
return 0;
1230
}
1231
1232
/* Populate strLimit, if necessary. */
1233
if( layer->maxfeatures >= 0 ) {
1234
static char *strLimitTemplate = " limit %d";
1235
strLimit = malloc(strlen(strLimitTemplate) + 12);
1236
sprintf(strLimit, strLimitTemplate, layer->maxfeatures);
1237
strLimitLength = strlen(strLimit);
1238
}
1239
1240
/* Populate strRect, if necessary. */
1241
if ( rect && layerinfo->geomcolumn ) {
1242
char *strBox = 0;
1243
char *strSRID = 0;
1244
size_t strBoxLength = 0;
1245
static char *strRectTemplate = "%s && %s";
1246
1247
/* We see to set the SRID on the box, but to what SRID? */
1248
strSRID = msPostGISBuildSQLSRID(layer);
1249
if ( ! strSRID ) {
1250
return 0;
1251
}
1252
1253
strBox = msPostGISBuildSQLBox(layer, rect, strSRID);
1254
if ( strBox ) {
1255
strBoxLength = strlen(strBox);
1256
}
1257
else {
1258
msSetError(MS_MISCERR, "Unable to build box SQL.", "msPostGISBuildSQLWhere()");
1259
return 0;
1260
}
1261
1262
strRect = (char*)malloc(strlen(strRectTemplate) + strBoxLength + strlen(layerinfo->geomcolumn));
1263
sprintf(strRect, strRectTemplate, layerinfo->geomcolumn, strBox);
1264
strRectLength = strlen(strRect);
1265
if (strBox) free(strBox);
1266
if (strSRID) free(strSRID);
1267
}
1268
1269
/* Populate strFilter, if necessary. */
1270
if ( layer->filter.string ) {
1271
static char *strFilterTemplate = "(%s)";
1272
strFilter = (char*)malloc(strlen(strFilterTemplate) + strlen(layer->filter.string));
1273
sprintf(strFilter, strFilterTemplate, layer->filter.string);
1274
strFilterLength = strlen(strFilter);
1275
}
1276
1277
/* Populate strUid, if necessary. */
1278
if ( uid ) {
1279
static char *strUidTemplate = "\"%s\" = %ld";
1280
strUid = (char*)malloc(strlen(strUidTemplate) + strlen(layerinfo->uid) + 64);
1281
sprintf(strUid, strUidTemplate, layerinfo->uid, *uid);
1282
strUidLength = strlen(strUid);
1283
}
1284
1285
strWhere = (char*)malloc(strRectLength + 5 + strFilterLength + 5 + strUidLength + strLimitLength);
1286
*strWhere = '\0';
1287
if ( strRect ) {
1288
strcat(strWhere, strRect);
1289
insert_and++;
1290
free(strRect);
1291
}
1292
if ( strFilter ) {
1293
if ( insert_and ) {
1294
strcat(strWhere, " and ");
1295
}
1296
strcat(strWhere, strFilter);
1297
free(strFilter);
1298
insert_and++;
1299
}
1300
if ( strUid ) {
1301
if ( insert_and ) {
1302
strcat(strWhere, " and ");
1303
}
1304
strcat(strWhere, strUid);
1305
free(strUid);
1306
insert_and++;
1307
}
1308
if ( strLimit ) {
1309
strcat(strWhere, strLimit);
1310
free(strLimit);
1311
}
1312
1313
return strWhere;
1314
}
1315
1316
/*
1317
** msPostGISBuildSQL()
1318
**
1319
** Returns malloc'ed char* that must be freed by caller.
1320
*/
1321
char *msPostGISBuildSQL(layerObj *layer, rectObj *rect, long *uid) {
1322
1323
msPostGISLayerInfo *layerinfo = 0;
1324
char *strFrom = 0;
1325
char *strItems = 0;
1326
char *strWhere = 0;
1327
char *strSQL = 0;
1328
static char *strSQLTemplate = "select %s from %s where %s";
1329
1330
if (layer->debug) {
1331
msDebug("msPostGISBuildSQL called.\n");
1332
}
1333
1334
assert( layer->layerinfo != NULL);
1335
1336
layerinfo = (msPostGISLayerInfo *)layer->layerinfo;
1337
1338
strItems = msPostGISBuildSQLItems(layer);
1339
if ( ! strItems ) {
1340
msSetError(MS_MISCERR, "Failed to build SQL items.", "msPostGISBuildSQL()");
1341
return 0;
1342
}
1343
1344
strFrom = msPostGISBuildSQLFrom(layer, rect);
1345
if ( ! strFrom ) {
1346
msSetError(MS_MISCERR, "Failed to build SQL 'from'.", "msPostGISBuildSQL()");
1347
return 0;
1348
}
1349
1350
strWhere = msPostGISBuildSQLWhere(layer, rect, uid);
1351
if ( ! strWhere ) {
1352
msSetError(MS_MISCERR, "Failed to build SQL 'where'.", "msPostGISBuildSQL()");
1353
return 0;
1354
}
1355
1356
strSQL = malloc(strlen(strSQLTemplate) + strlen(strFrom) + strlen(strItems) + strlen(strWhere));
1357
sprintf(strSQL, strSQLTemplate, strItems, strFrom, strWhere);
1358
if (strItems) free(strItems);
1359
if (strFrom) free(strFrom);
1360
if (strWhere) free(strWhere);
1361
1362
return strSQL;
1363
1364
}
1365
1366
int msPostGISReadShape(layerObj *layer, shapeObj *shape) {
1367
1368
char *wkbstr = NULL;
1369
unsigned char *wkb = NULL;
1370
msPostGISLayerInfo *layerinfo = NULL;
1371
int result = 0;
1372
int wkbstrlen = 0;
1373
1374
if (layer->debug) {
1375
msDebug("msPostGISReadShape called.\n");
1376
}
1377
1378
assert(layer->layerinfo != NULL);
1379
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
1380
1381
/* Retrieve the geometry. */
1382
wkbstr = (char*)PQgetvalue(layerinfo->pgresult, layerinfo->rownum, layer->numitems );
1383
wkbstrlen = PQgetlength(layerinfo->pgresult, layerinfo->rownum, layer->numitems);
1384
1385
if ( ! wkbstr ) {
1386
msSetError(MS_QUERYERR, "String encoded WKB returned is null!", "msPostGISReadShape()");
1387
return MS_FAILURE;
1388
}
1389
1390
1391
wkb = calloc(wkbstrlen, sizeof(char));
1392
#if TRANSFER_ENCODING == 64
1393
result = msPostGISBase64Decode(wkb, wkbstr, wkbstrlen - 1);
1394
#else
1395
result = msPostGISHexDecode(wkb, wkbstr, wkbstrlen);
1396
#endif
1397
1398
if( ! result ) {
1399
free(wkb);
1400
return MS_FAILURE;
1401
}
1402
1403
switch (layer->type) {
1404
1405
case MS_LAYER_POINT:
1406
result = force_to_points(wkb, shape);
1407
break;
1408
1409
case MS_LAYER_LINE:
1410
result = force_to_lines(wkb, shape);
1411
break;
1412
1413
case MS_LAYER_POLYGON:
1414
result = force_to_polygons(wkb, shape);
1415
break;
1416
1417
case MS_LAYER_ANNOTATION:
1418
case MS_LAYER_QUERY:
1419
case MS_LAYER_CHART:
1420
result = dont_force(wkb, shape);
1421
break;
1422
1423
case MS_LAYER_RASTER:
1424
msDebug( "Ignoring MS_LAYER_RASTER in msPostGISReadShape.\n" );
1425
break;
1426
1427
case MS_LAYER_CIRCLE:
1428
msDebug( "Ignoring MS_LAYER_RASTER in msPostGISReadShape.\n" );
1429
break;
1430
1431
default:
1432
msDebug( "Unsupported layer type in msPostGISReadShape()!\n" );
1433
break;
1434
}
1435
1436
if (shape->type != MS_SHAPE_NULL) {
1437
int t;
1438
long uid;
1439
char *tmp;
1440
/* Found a drawable shape, so now retreive the attributes. */
1441
1442
shape->values = (char**) malloc(sizeof(char*) * layer->numitems);
1443
for ( t = 0; t < layer->numitems; t++) {
1444
int size = PQgetlength(layerinfo->pgresult, layerinfo->rownum, t);
1445
char *val = (char*)PQgetvalue(layerinfo->pgresult, layerinfo->rownum, t);
1446
int isnull = PQgetisnull(layerinfo->pgresult, layerinfo->rownum, t);
1447
if ( isnull ) {
1448
shape->values[t] = strdup("");
1449
}
1450
else {
1451
shape->values[t] = (char*) malloc(size + 1);
1452
memcpy(shape->values[t], val, size);
1453
shape->values[t][size] = '\0'; /* null terminate it */
1454
msStringTrimBlanks(shape->values[t]);
1455
}
1456
if( layer->debug > 4 ) {
1457
msDebug("msPostGISReadShape: PQgetlength = %d\n", size);
1458
}
1459
if( layer->debug > 1 ) {
1460
msDebug("msPostGISReadShape: [%s] \"%s\"\n", layer->items[t], shape->values[t]);
1461
}
1462
}
1463
1464
/* t is the geometry, t+1 is the uid */
1465
tmp = PQgetvalue(layerinfo->pgresult, layerinfo->rownum, t + 1);
1466
if( tmp ) {
1467
uid = strtol( tmp, NULL, 10 );
1468
}
1469
else {
1470
uid = 0;
1471
}
1472
if( layer->debug > 4 ) {
1473
msDebug("msPostGISReadShape: Setting shape->index = %d\n", uid);
1474
msDebug("msPostGISReadShape: Setting shape->tileindex = %d\n", layerinfo->rownum);
1475
}
1476
shape->index = uid;
1477
shape->tileindex = layerinfo->rownum;
1478
1479
if( layer->debug > 2 ) {
1480
msDebug("msPostGISReadShape: [index] %d\n", shape->index);
1481
}
1482
1483
shape->numvalues = layer->numitems;
1484
1485
find_bounds(shape);
1486
}
1487
1488
if( layer->debug > 2 ) {
1489
char *tmp = msShapeToWKT(shape);
1490
msDebug("msPostGISReadShape: [shape] %s\n", tmp);
1491
free(tmp);
1492
}
1493
1494
free(wkb);
1495
return MS_SUCCESS;
1496
}
1497
1498
#endif /* USE_POSTGIS */
1499
1500
1501
/*
1502
** msPostGISLayerOpen()
1503
**
1504
** Registered vtable->LayerOpen function.
1505
*/
1506
int msPostGISLayerOpen(layerObj *layer) {
1507
#ifdef USE_POSTGIS
1508
msPostGISLayerInfo *layerinfo;
1509
int order_test = 1;
1510
1511
assert(layer != NULL);
1512
1513
if (layer->debug) {
1514
msDebug("msPostGISLayerOpen called: %s\n", layer->data);
1515
}
1516
1517
if (layer->layerinfo) {
1518
if (layer->debug) {
1519
msDebug("msPostGISLayerOpen: Layer is already open!\n");
1520
}
1521
return MS_SUCCESS; /* already open */
1522
}
1523
1524
if (!layer->data) {
1525
msSetError(MS_QUERYERR, "Nothing specified in DATA statement.", "msPostGISLayerOpen()");
1526
return MS_FAILURE;
1527
}
1528
1529
/*
1530
** Initialize the layerinfo
1531
**/
1532
layerinfo = msPostGISCreateLayerInfo();
1533
1534
if (((char*) &order_test)[0] == 1) {
1535
layerinfo->endian = LITTLE_ENDIAN;
1536
} else {
1537
layerinfo->endian = BIG_ENDIAN;
1538
}
1539
1540
/*
1541
** Get a database connection from the pool.
1542
*/
1543
layerinfo->pgconn = (PGconn *) msConnPoolRequest(layer);
1544
1545
/* No connection in the pool, so set one up. */
1546
if (!layerinfo->pgconn) {
1547
char *conn_decrypted;
1548
if (layer->debug) {
1549
msDebug("msPostGISLayerOpen: No connection in pool, creating a fresh one.\n");
1550
}
1551
1552
if (!layer->connection) {
1553
msSetError(MS_MISCERR, "Missing CONNECTION keyword.", "msPostGISLayerOpen()");
1554
return MS_FAILURE;
1555
}
1556
1557
/*
1558
** Decrypt any encrypted token in connection string and attempt to connect.
1559
*/
1560
conn_decrypted = msDecryptStringTokens(layer->map, layer->connection);
1561
if (conn_decrypted == NULL) {
1562
return MS_FAILURE; /* An error should already have been produced */
1563
}
1564
layerinfo->pgconn = PQconnectdb(conn_decrypted);
1565
msFree(conn_decrypted);
1566
conn_decrypted = NULL;
1567
1568
/*
1569
** Connection failed, return error message with passwords ***ed out.
1570
*/
1571
if (!layerinfo->pgconn || PQstatus(layerinfo->pgconn) == CONNECTION_BAD) {
1572
char *index, *maskeddata;
1573
if (layer->debug)
1574
msDebug("msPostGISLayerOpen: Connection failure.\n");
1575
1576
maskeddata = strdup(layer->connection);
1577
index = strstr(maskeddata, "password=");
1578
if (index != NULL) {
1579
index = (char*)(index + 9);
1580
while (*index != '\0' && *index != ' ') {
1581
*index = '*';
1582
index++;
1583
}
1584
}
1585
1586
msSetError(MS_QUERYERR, "Database connection failed (%s) with connect string '%s'\nIs the database running? Is it allowing connections? Does the specified user exist? Is the password valid? Is the database on the standard port?", "msPostGISLayerOpen()", PQerrorMessage(layerinfo->pgconn), maskeddata);
1587
1588
free(maskeddata);
1589
free(layerinfo);
1590
return MS_FAILURE;
1591
}
1592
1593
/* Register to receive notifications from the database. */
1594
PQsetNoticeProcessor(layerinfo->pgconn, postresqlNoticeHandler, (void *) layer);
1595
1596
/* Save this connection in the pool for later. */
1597
msConnPoolRegister(layer, layerinfo->pgconn, msPostGISCloseConnection);
1598
}
1599
else {
1600
/* Connection in the pool should be tested to see if backend is alive. */
1601
if( PQstatus(layerinfo->pgconn) != CONNECTION_OK ) {
1602
/* Uh oh, bad connection. Can we reset it? */
1603
PQreset(layerinfo->pgconn);
1604
if( PQstatus(layerinfo->pgconn) != CONNECTION_OK ) {
1605
/* Nope, time to bail out. */
1606
msSetError(MS_QUERYERR, "PostgreSQL database connection gone bad (%s)", "msPostGISLayerOpen()", PQerrorMessage(layerinfo->pgconn));
1607
return MS_FAILURE;
1608
}
1609
1610
}
1611
}
1612
1613
/* Save the layerinfo in the layerObj. */
1614
layer->layerinfo = (void*)layerinfo;
1615
1616
return MS_SUCCESS;
1617
#else
1618
msSetError( MS_MISCERR,
1619
"PostGIS support is not available.",
1620
"msPostGISLayerOpen()");
1621
return MS_FAILURE;
1622
#endif
1623
}
1624
1625
/*
1626
** msPostGISLayerClose()
1627
**
1628
** Registered vtable->LayerClose function.
1629
*/
1630
int msPostGISLayerClose(layerObj *layer) {
1631
#ifdef USE_POSTGIS
1632
1633
if (layer->debug) {
1634
msDebug("msPostGISLayerClose called: %s\n", layer->data);
1635
}
1636
1637
if( layer->layerinfo ) {
1638
msPostGISFreeLayerInfo(layer);
1639
}
1640
1641
return MS_SUCCESS;
1642
#else
1643
msSetError( MS_MISCERR,
1644
"PostGIS support is not available.",
1645
"msPostGISLayerOpen()");
1646
return MS_FAILURE;
1647
#endif
1648
}
1649
1650
1651
/*
1652
** msPostGISLayerIsOpen()
1653
**
1654
** Registered vtable->LayerIsOpen function.
1655
*/
1656
int msPostGISLayerIsOpen(layerObj *layer) {
1657
#ifdef USE_POSTGIS
1658
1659
if (layer->debug) {
1660
msDebug("msPostGISLayerIsOpen called.\n");
1661
}
1662
1663
if (layer->layerinfo)
1664
return MS_TRUE;
1665
else
1666
return MS_FALSE;
1667
#else
1668
msSetError( MS_MISCERR,
1669
"PostGIS support is not available.",
1670
"msPostGISLayerIsOpen()");
1671
return MS_FAILURE;
1672
#endif
1673
}
1674
1675
1676
/*
1677
** msPostGISLayerFreeItemInfo()
1678
**
1679
** Registered vtable->LayerFreeItemInfo function.
1680
*/
1681
void msPostGISLayerFreeItemInfo(layerObj *layer) {
1682
#ifdef USE_POSTGIS
1683
if (layer->debug) {
1684
msDebug("msPostGISLayerFreeItemInfo called.\n");
1685
}
1686
1687
if (layer->iteminfo) {
1688
free(layer->iteminfo);
1689
}
1690
layer->iteminfo = NULL;
1691
#endif
1692
}
1693
1694
/*
1695
** msPostGISLayerInitItemInfo()
1696
**
1697
** Registered vtable->LayerInitItemInfo function.
1698
** Our iteminfo is list of indexes from 1..numitems.
1699
*/
1700
int msPostGISLayerInitItemInfo(layerObj *layer) {
1701
#ifdef USE_POSTGIS
1702
int i;
1703
int *itemindexes ;
1704
1705
if (layer->debug) {
1706
msDebug("msPostGISLayerInitItemInfo called.\n");
1707
}
1708
1709
if (layer->numitems == 0) {
1710
return MS_SUCCESS;
1711
}
1712
1713
if (layer->iteminfo) {
1714
free(layer->iteminfo);
1715
}
1716
1717
layer->iteminfo = malloc(sizeof(int) * layer->numitems);
1718
if (!layer->iteminfo) {
1719
msSetError(MS_MEMERR, "Out of memory.", "msPostGISLayerInitItemInfo()");
1720
return MS_FAILURE;
1721
}
1722
1723
itemindexes = (int*)layer->iteminfo;
1724
for (i = 0; i < layer->numitems; i++) {
1725
itemindexes[i] = i; /* Last item is always the geometry. The rest are non-geometry. */
1726
}
1727
1728
return MS_SUCCESS;
1729
#else
1730
msSetError( MS_MISCERR,
1731
"PostGIS support is not available.",
1732
"msPostGISLayerInitItemInfo()");
1733
return MS_FAILURE;
1734
#endif
1735
}
1736
1737
/*
1738
** msPostGISLayerWhichShapes()
1739
**
1740
** Registered vtable->LayerWhichShapes function.
1741
*/
1742
int msPostGISLayerWhichShapes(layerObj *layer, rectObj rect) {
1743
#ifdef USE_POSTGIS
1744
msPostGISLayerInfo *layerinfo = NULL;
1745
char *strSQL = NULL;
1746
PGresult *pgresult = NULL;
1747
1748
assert(layer != NULL);
1749
assert(layer->layerinfo != NULL);
1750
1751
if (layer->debug) {
1752
msDebug("msPostGISLayerWhichShapes called.\n");
1753
}
1754
1755
/* Fill out layerinfo with our current DATA state. */
1756
if ( msPostGISParseData(layer) != MS_SUCCESS) {
1757
return MS_FAILURE;
1758
}
1759
1760
/*
1761
** This comes *after* parsedata, because parsedata fills in
1762
** layer->layerinfo.
1763
*/
1764
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
1765
1766
/* Build a SQL query based on our current state. */
1767
strSQL = msPostGISBuildSQL(layer, &rect, NULL);
1768
if ( ! strSQL ) {
1769
msSetError(MS_QUERYERR, "Failed to build query SQL.", "msPostGISLayerWhichShapes()");
1770
return MS_FAILURE;
1771
}
1772
1773
if (layer->debug) {
1774
msDebug("msPostGISLayerWhichShapes query: %s\n", strSQL);
1775
}
1776
1777
pgresult = PQexec(layerinfo->pgconn, strSQL);
1778
1779
if ( layer->debug > 1 ) {
1780
msDebug("msPostGISLayerWhichShapes query status: %s (%d)\n", PQresStatus(PQresultStatus(pgresult)), PQresultStatus(pgresult));
1781
}
1782
1783
/* Something went wrong. */
1784
if (!pgresult || PQresultStatus(pgresult) != PGRES_TUPLES_OK) {
1785
msSetError(MS_QUERYERR, "Error (%s) executing query: %s", "msPostGISLayerWhichShapes()", PQerrorMessage(layerinfo->pgconn), strSQL);
1786
free(strSQL);
1787
if (pgresult) {
1788
PQclear(pgresult);
1789
}
1790
return MS_FAILURE;
1791
}
1792
1793
if ( layer->debug ) {
1794
msDebug("msPostGISLayerWhichShapes got %d records in result.\n", PQntuples(pgresult));
1795
}
1796
1797
/* Clean any existing pgresult before storing current one. */
1798
if(layerinfo->pgresult) PQclear(layerinfo->pgresult);
1799
layerinfo->pgresult = pgresult;
1800
1801
/* Clean any existing SQL before storing current. */
1802
if(layerinfo->sql) free(layerinfo->sql);
1803
layerinfo->sql = strSQL;
1804
1805
layerinfo->rownum = 0;
1806
1807
return MS_SUCCESS;
1808
#else
1809
msSetError( MS_MISCERR,
1810
"PostGIS support is not available.",
1811
"msPostGISLayerWhichShapes()");
1812
return MS_FAILURE;
1813
#endif
1814
}
1815
1816
/*
1817
** msPostGISLayerNextShape()
1818
**
1819
** Registered vtable->LayerNextShape function.
1820
*/
1821
int msPostGISLayerNextShape(layerObj *layer, shapeObj *shape) {
1822
#ifdef USE_POSTGIS
1823
msPostGISLayerInfo *layerinfo;
1824
1825
if (layer->debug) {
1826
msDebug("msPostGISLayerNextShape called.\n");
1827
}
1828
1829
assert(layer != NULL);
1830
assert(layer->layerinfo != NULL);
1831
1832
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
1833
1834
shape->type = MS_SHAPE_NULL;
1835
1836
/*
1837
** Roll through pgresult until we hit non-null shape (usually right away).
1838
*/
1839
while (shape->type == MS_SHAPE_NULL) {
1840
if (layerinfo->rownum < PQntuples(layerinfo->pgresult)) {
1841
int rv;
1842
/* Retrieve this shape, cursor access mode. */
1843
rv = msPostGISReadShape(layer, shape);
1844
if( shape->type != MS_SHAPE_NULL ) {
1845
(layerinfo->rownum)++; /* move to next shape */
1846
return MS_SUCCESS;
1847
} else {
1848
(layerinfo->rownum)++; /* move to next shape */
1849
}
1850
} else {
1851
return MS_DONE;
1852
}
1853
}
1854
1855
/* Found nothing, clean up and exit. */
1856
msFreeShape(shape);
1857
1858
return MS_FAILURE;
1859
#else
1860
msSetError( MS_MISCERR,
1861
"PostGIS support is not available.",
1862
"msPostGISLayerNextShape()");
1863
return MS_FAILURE;
1864
#endif
1865
}
1866
1867
/*
1868
** msPostGISLayerResultsGetShape()
1869
**
1870
** Registered vtable->LayerGetShape function. For pulling from a prepared and
1871
** undisposed result set. We ignore the 'tile' parameter, as it means nothing to us.
1872
*/
1873
int msPostGISLayerResultsGetShape(layerObj *layer, shapeObj *shape, int tile, long record) {
1874
#ifdef USE_POSTGIS
1875
1876
PGresult *pgresult = NULL;
1877
msPostGISLayerInfo *layerinfo = NULL;
1878
int result = MS_SUCCESS;
1879
int status;
1880
1881
assert(layer != NULL);
1882
assert(layer->layerinfo != NULL);
1883
1884
if (layer->debug) {
1885
msDebug("msPostGISLayerResultsGetShape called for record = %i\n", record);
1886
}
1887
1888
if( tile < 0 ) {
1889
msDebug("msPostGISLayerResultsGetShape called for record = %i\n", record);
1890
return msPostGISLayerResultsGetShape(layer, shape, tile, record);
1891
}
1892
1893
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
1894
1895
/* Check the validity of the open result. */
1896
pgresult = layerinfo->pgresult;
1897
if ( ! pgresult ) {
1898
msSetError( MS_MISCERR,
1899
"PostgreSQL result set is null.",
1900
"msPostGISLayerResultsGetShape()");
1901
return MS_FAILURE;
1902
}
1903
status = PQresultStatus(pgresult);
1904
if ( layer->debug > 1 ) {
1905
msDebug("msPostGISLayerResultsGetShape query status: %s (%d)\n", PQresStatus(status), status);
1906
}
1907
if( ! ( status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK) ) {
1908
msSetError( MS_MISCERR,
1909
"PostgreSQL result set is not ready.",
1910
"msPostGISLayerResultsGetShape()");
1911
return MS_FAILURE;
1912
}
1913
1914
/* Check the validity of the requested record number. */
1915
if( tile >= PQntuples(pgresult) ) {
1916
msDebug("msPostGISLayerResultsGetShape got request for (%d) but only has %d tuples.\n", tile, PQntuples(pgresult));
1917
msSetError( MS_MISCERR,
1918
"Got request larger than result set.",
1919
"msPostGISLayerResultsGetShape()");
1920
return MS_FAILURE;
1921
}
1922
1923
layerinfo->rownum = tile; /* Only return one result. */
1924
1925
/* We don't know the shape type until we read the geometry. */
1926
shape->type = MS_SHAPE_NULL;
1927
1928
/* Return the shape, cursor access mode. */
1929
result = msPostGISReadShape(layer, shape);
1930
1931
return (shape->type == MS_SHAPE_NULL) ? MS_FAILURE : MS_SUCCESS;
1932
#else
1933
msSetError( MS_MISCERR,
1934
"PostGIS support is not available.",
1935
"msPostGISLayerResultsGetShape()");
1936
return MS_FAILURE;
1937
#endif
1938
}
1939
1940
/*
1941
** msPostGISLayerGetShape()
1942
**
1943
** Registered vtable->LayerGetShape function. We ignore the 'tile'
1944
** parameter, as it means nothing to us.
1945
*/
1946
int msPostGISLayerGetShape(layerObj *layer, shapeObj *shape, int tile, long record) {
1947
#ifdef USE_POSTGIS
1948
PGresult *pgresult;
1949
msPostGISLayerInfo *layerinfo;
1950
int result, num_tuples;
1951
char *strSQL = 0;
1952
1953
assert(layer != NULL);
1954
assert(layer->layerinfo != NULL);
1955
1956
if (layer->debug) {
1957
msDebug("msPostGISLayerGetShape called for record = %i\n", record);
1958
}
1959
1960
/* Fill out layerinfo with our current DATA state. */
1961
if ( msPostGISParseData(layer) != MS_SUCCESS) {
1962
return MS_FAILURE;
1963
}
1964
1965
/*
1966
** This comes *after* parsedata, because parsedata fills in
1967
** layer->layerinfo.
1968
*/
1969
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
1970
1971
/* Build a SQL query based on our current state. */
1972
strSQL = msPostGISBuildSQL(layer, 0, &record);
1973
if ( ! strSQL ) {
1974
msSetError(MS_QUERYERR, "Failed to build query SQL.", "msPostGISLayerGetShape()");
1975
return MS_FAILURE;
1976
}
1977
1978
if (layer->debug) {
1979
msDebug("msPostGISLayerGetShape query: %s\n", strSQL);
1980
}
1981
1982
pgresult = PQexec(layerinfo->pgconn, strSQL);
1983
1984
/* Something went wrong. */
1985
if ( (!pgresult) || (PQresultStatus(pgresult) != PGRES_TUPLES_OK) ) {
1986
msSetError(MS_QUERYERR, "Error (%s) executing SQL: %s", "msPostGISLayerGetShape()", PQerrorMessage(layerinfo->pgconn), strSQL );
1987
1988
if (pgresult) {
1989
PQclear(pgresult);
1990
}
1991
free(strSQL);
1992
1993
return MS_FAILURE;
1994
}
1995
1996
/* Clean any existing pgresult before storing current one. */
1997
if(layerinfo->pgresult) PQclear(layerinfo->pgresult);
1998
layerinfo->pgresult = pgresult;
1999
2000
/* Clean any existing SQL before storing current. */
2001
if(layerinfo->sql) free(layerinfo->sql);
2002
layerinfo->sql = strSQL;
2003
2004
layerinfo->rownum = 0; /* Only return one result. */
2005
2006
/* We don't know the shape type until we read the geometry. */
2007
shape->type = MS_SHAPE_NULL;
2008
2009
num_tuples = PQntuples(pgresult);
2010
if (layer->debug) {
2011
msDebug("msPostGISLayerGetShape number of records: %d\n", num_tuples);
2012
}
2013
2014
if (num_tuples > 0) {
2015
/* Get shape in random access mode. */
2016
result = msPostGISReadShape(layer, shape);
2017
}
2018
2019
return (shape->type == MS_SHAPE_NULL) ? MS_FAILURE : ( (num_tuples > 0) ? MS_SUCCESS : MS_DONE );
2020
#else
2021
msSetError( MS_MISCERR,
2022
"PostGIS support is not available.",
2023
"msPostGISLayerGetShape()");
2024
return MS_FAILURE;
2025
#endif
2026
}
2027
2028
/*
2029
** msPostGISLayerGetItems()
2030
**
2031
** Registered vtable->LayerGetItems function. Query the database for
2032
** column information about the requested layer. Rather than look in
2033
** system tables, we just run a zero-cost query and read out of the
2034
** result header.
2035
*/
2036
int msPostGISLayerGetItems(layerObj *layer) {
2037
#ifdef USE_POSTGIS
2038
msPostGISLayerInfo *layerinfo;
2039
char *sql = 0;
2040
static char *strSQLTemplate = "select * from %s where false limit 0";
2041
PGresult *pgresult;
2042
int t;
2043
char *col;
2044
char found_geom = 0;
2045
int item_num;
2046
2047
assert(layer != NULL);
2048
assert(layer->layerinfo != NULL);
2049
2050
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
2051
2052
assert(layerinfo->pgconn);
2053
2054
if (layer->debug) {
2055
msDebug("msPostGISLayerGetItems called.\n");
2056
}
2057
2058
/* Fill out layerinfo with our current DATA state. */
2059
if ( msPostGISParseData(layer) != MS_SUCCESS) {
2060
return MS_FAILURE;
2061
}
2062
2063
layerinfo = (msPostGISLayerInfo*) layer->layerinfo;
2064
2065
/*
2066
** Both the "table" and "(select ...) as sub" cases can be handled with the
2067
** same SQL.
2068
*/
2069
sql = (char*) malloc(strlen(strSQLTemplate) + strlen(layerinfo->fromsource));
2070
sprintf(sql, strSQLTemplate, layerinfo->fromsource);
2071
2072
if (layer->debug) {
2073
msDebug("msPostGISLayerGetItems executing SQL: %s\n", sql);
2074
}
2075
2076
pgresult = PQexec(layerinfo->pgconn, sql);
2077
2078
if ( (!pgresult) || (PQresultStatus(pgresult) != PGRES_TUPLES_OK) ) {
2079
msSetError(MS_QUERYERR, "Error (%s) executing SQL: %s", "msPostGISLayerGetItems()", PQerrorMessage(layerinfo->pgconn), sql);
2080
if (pgresult) {
2081
PQclear(pgresult);
2082
}
2083
free(sql);
2084
return MS_FAILURE;
2085
}
2086
2087
free(sql);
2088
2089
layer->numitems = PQnfields(pgresult) - 1; /* dont include the geometry column (last entry)*/
2090
layer->items = malloc(sizeof(char*) * (layer->numitems + 1)); /* +1 in case there is a problem finding geometry column */
2091
2092
found_geom = 0; /* havent found the geom field */
2093
item_num = 0;
2094
2095
for (t = 0; t < PQnfields(pgresult); t++) {
2096
col = PQfname(pgresult, t);
2097
if ( strcmp(col, layerinfo->geomcolumn) != 0 ) {
2098
/* this isnt the geometry column */
2099
layer->items[item_num] = strdup(col);
2100
item_num++;
2101
} else {
2102
found_geom = 1;
2103
}
2104
}
2105
2106
PQclear(pgresult);
2107
2108
if (!found_geom) {
2109
msSetError(MS_QUERYERR, "Tried to find the geometry column in the database, but couldn't find it. Is it mis-capitalized? '%s'", "msPostGISLayerGetItems()", layerinfo->geomcolumn);
2110
return MS_FAILURE;
2111
}
2112
2113
return msPostGISLayerInitItemInfo(layer);
2114
#else
2115
msSetError( MS_MISCERR,
2116
"PostGIS support is not available.",
2117
"msPostGISLayerGetShape()");
2118
return MS_FAILURE;
2119
#endif
2120
}
2121
2122
/*
2123
** msPostGISLayerGetExtent()
2124
**
2125
** Registered vtable->LayerGetExtent function.
2126
**
2127
** TODO: Update to use proper PostGIS functions to pull
2128
** extent quickly and accurately when available.
2129
*/
2130
int msPostGISLayerGetExtent(layerObj *layer, rectObj *extent) {
2131
if (layer->debug) {
2132
msDebug("msPOSTGISLayerGetExtent called.\n");
2133
}
2134
2135
extent->minx = extent->miny = -1.0 * FLT_MAX ;
2136
extent->maxx = extent->maxy = FLT_MAX;
2137
2138
return MS_SUCCESS;
2139
2140
}
2141
2142
int msPostGISLayerSetTimeFilter(layerObj *lp, const char *timestring, const char *timefield)
2143
{
2144
char *tmpstimestring = NULL;
2145
char *timeresolution = NULL;
2146
int timesresol = -1;
2147
char **atimes, **tokens = NULL;
2148
int numtimes=0,i=0,ntmp=0,nlength=0;
2149
char buffer[512];
2150
2151
buffer[0] = '\0';
2152
2153
if (!lp || !timestring || !timefield)
2154
return MS_FALSE;
2155
2156
if (strstr(timestring, ",") == NULL &&
2157
strstr(timestring, "/") == NULL) /* discrete time */
2158
tmpstimestring = strdup(timestring);
2159
else
2160
{
2161
atimes = msStringSplit (timestring, ',', &numtimes);
2162
if (atimes == NULL || numtimes < 1)
2163
return MS_FALSE;
2164
2165
if (numtimes >= 1)
2166
{
2167
tokens = msStringSplit(atimes[0], '/', &ntmp);
2168
if (ntmp == 2) /* ranges */
2169
{
2170
tmpstimestring = strdup(tokens[0]);
2171
msFreeCharArray(tokens, ntmp);
2172
}
2173
else if (ntmp == 1) /* multiple times */
2174
{
2175
tmpstimestring = strdup(atimes[0]);
2176
}
2177
}
2178
msFreeCharArray(atimes, numtimes);
2179
}
2180
if (!tmpstimestring)
2181
return MS_FALSE;
2182
2183
timesresol = msTimeGetResolution((const char*)tmpstimestring);
2184
if (timesresol < 0)
2185
return MS_FALSE;
2186
2187
free(tmpstimestring);
2188
2189
switch (timesresol)
2190
{
2191
case (TIME_RESOLUTION_SECOND):
2192
timeresolution = strdup("second");
2193
break;
2194
2195
case (TIME_RESOLUTION_MINUTE):
2196
timeresolution = strdup("minute");
2197
break;
2198
2199
case (TIME_RESOLUTION_HOUR):
2200
timeresolution = strdup("hour");
2201
break;
2202
2203
case (TIME_RESOLUTION_DAY):
2204
timeresolution = strdup("day");
2205
break;
2206
2207
case (TIME_RESOLUTION_MONTH):
2208
timeresolution = strdup("month");
2209
break;
2210
2211
case (TIME_RESOLUTION_YEAR):
2212
timeresolution = strdup("year");
2213
break;
2214
2215
default:
2216
break;
2217
}
2218
2219
if (!timeresolution)
2220
return MS_FALSE;
2221
2222
/* where date_trunc('month', _cwctstamp) = '2004-08-01' */
2223
if (strstr(timestring, ",") == NULL &&
2224
strstr(timestring, "/") == NULL) /* discrete time */
2225
{
2226
if(lp->filteritem) free(lp->filteritem);
2227
lp->filteritem = strdup(timefield);
2228
if (&lp->filter)
2229
{
2230
/* if the filter is set and it's a string type, concatenate it with
2231
the time. If not just free it */
2232
if (lp->filter.type == MS_EXPRESSION)
2233
{
2234
strcat(buffer, "(");
2235
strcat(buffer, lp->filter.string);
2236
strcat(buffer, ") and ");
2237
}
2238
else
2239
freeExpression(&lp->filter);
2240
}
2241
2242
2243
strcat(buffer, "(");
2244
2245
strcat(buffer, "date_trunc('");
2246
strcat(buffer, timeresolution);
2247
strcat(buffer, "', ");
2248
strcat(buffer, timefield);
2249
strcat(buffer, ")");
2250
2251
2252
strcat(buffer, " = ");
2253
strcat(buffer, "'");
2254
strcat(buffer, timestring);
2255
/* make sure that the timestring is complete and acceptable */
2256
/* to the date_trunc function : */
2257
/* - if the resolution is year (2004) or month (2004-01), */
2258
/* a complete string for time would be 2004-01-01 */
2259
/* - if the resolluion is hour or minute (2004-01-01 15), a */
2260
/* complete time is 2004-01-01 15:00:00 */
2261
if (strcasecmp(timeresolution, "year")==0)
2262
{
2263
nlength = strlen(timestring);
2264
if (timestring[nlength-1] != '-')
2265
strcat(buffer,"-01-01");
2266
else
2267
strcat(buffer,"01-01");
2268
}
2269
else if (strcasecmp(timeresolution, "month")==0)
2270
{
2271
nlength = strlen(timestring);
2272
if (timestring[nlength-1] != '-')
2273
strcat(buffer,"-01");
2274
else
2275
strcat(buffer,"01");
2276
}
2277
else if (strcasecmp(timeresolution, "hour")==0)
2278
{
2279
nlength = strlen(timestring);
2280
if (timestring[nlength-1] != ':')
2281
strcat(buffer,":00:00");
2282
else
2283
strcat(buffer,"00:00");
2284
}
2285
else if (strcasecmp(timeresolution, "minute")==0)
2286
{
2287
nlength = strlen(timestring);
2288
if (timestring[nlength-1] != ':')
2289
strcat(buffer,":00");
2290
else
2291
strcat(buffer,"00");
2292
}
2293
2294
2295
strcat(buffer, "'");
2296
2297
strcat(buffer, ")");
2298
2299
/* loadExpressionString(&lp->filter, (char *)timestring); */
2300
loadExpressionString(&lp->filter, buffer);
2301
2302
free(timeresolution);
2303
return MS_TRUE;
2304
}
2305
2306
atimes = msStringSplit (timestring, ',', &numtimes);
2307
if (atimes == NULL || numtimes < 1)
2308
return MS_FALSE;
2309
2310
if (numtimes >= 1)
2311
{
2312
/* check to see if we have ranges by parsing the first entry */
2313
tokens = msStringSplit(atimes[0], '/', &ntmp);
2314
if (ntmp == 2) /* ranges */
2315
{
2316
msFreeCharArray(tokens, ntmp);
2317
for (i=0; i<numtimes; i++)
2318
{
2319
tokens = msStringSplit(atimes[i], '/', &ntmp);
2320
if (ntmp == 2)
2321
{
2322
if (strlen(buffer) > 0)
2323
strcat(buffer, " OR ");
2324
else
2325
strcat(buffer, "(");
2326
2327
strcat(buffer, "(");
2328
2329
strcat(buffer, "date_trunc('");
2330
strcat(buffer, timeresolution);
2331
strcat(buffer, "', ");
2332
strcat(buffer, timefield);
2333
strcat(buffer, ")");
2334
2335
strcat(buffer, " >= ");
2336
2337
strcat(buffer, "'");
2338
2339
strcat(buffer, tokens[0]);
2340
/* - if the resolution is year (2004) or month (2004-01), */
2341
/* a complete string for time would be 2004-01-01 */
2342
/* - if the resolluion is hour or minute (2004-01-01 15), a */
2343
/* complete time is 2004-01-01 15:00:00 */
2344
if (strcasecmp(timeresolution, "year")==0)
2345
{
2346
nlength = strlen(tokens[0]);
2347
if (tokens[0][nlength-1] != '-')
2348
strcat(buffer,"-01-01");
2349
else
2350
strcat(buffer,"01-01");
2351
}
2352
else if (strcasecmp(timeresolution, "month")==0)
2353
{
2354
nlength = strlen(tokens[0]);
2355
if (tokens[0][nlength-1] != '-')
2356
strcat(buffer,"-01");
2357
else
2358
strcat(buffer,"01");
2359
}
2360
else if (strcasecmp(timeresolution, "hour")==0)
2361
{
2362
nlength = strlen(tokens[0]);
2363
if (tokens[0][nlength-1] != ':')
2364
strcat(buffer,":00:00");
2365
else
2366
strcat(buffer,"00:00");
2367
}
2368
else if (strcasecmp(timeresolution, "minute")==0)
2369
{
2370
nlength = strlen(tokens[0]);
2371
if (tokens[0][nlength-1] != ':')
2372
strcat(buffer,":00");
2373
else
2374
strcat(buffer,"00");
2375
}
2376
2377
strcat(buffer, "'");
2378
strcat(buffer, " AND ");
2379
2380
2381
strcat(buffer, "date_trunc('");
2382
strcat(buffer, timeresolution);
2383
strcat(buffer, "', ");
2384
strcat(buffer, timefield);
2385
strcat(buffer, ")");
2386
2387
strcat(buffer, " <= ");
2388
2389
strcat(buffer, "'");
2390
strcat(buffer, tokens[1]);
2391
2392
/* - if the resolution is year (2004) or month (2004-01), */
2393
/* a complete string for time would be 2004-01-01 */
2394
/* - if the resolluion is hour or minute (2004-01-01 15), a */
2395
/* complete time is 2004-01-01 15:00:00 */
2396
if (strcasecmp(timeresolution, "year")==0)
2397
{
2398
nlength = strlen(tokens[1]);
2399
if (tokens[1][nlength-1] != '-')
2400
strcat(buffer,"-01-01");
2401
else
2402
strcat(buffer,"01-01");
2403
}
2404
else if (strcasecmp(timeresolution, "month")==0)
2405
{
2406
nlength = strlen(tokens[1]);
2407
if (tokens[1][nlength-1] != '-')
2408
strcat(buffer,"-01");
2409
else
2410
strcat(buffer,"01");
2411
}
2412
else if (strcasecmp(timeresolution, "hour")==0)
2413
{
2414
nlength = strlen(tokens[1]);
2415
if (tokens[1][nlength-1] != ':')
2416
strcat(buffer,":00:00");
2417
else
2418
strcat(buffer,"00:00");
2419
}
2420
else if (strcasecmp(timeresolution, "minute")==0)
2421
{
2422
nlength = strlen(tokens[1]);
2423
if (tokens[1][nlength-1] != ':')
2424
strcat(buffer,":00");
2425
else
2426
strcat(buffer,"00");
2427
}
2428
2429
strcat(buffer, "'");
2430
strcat(buffer, ")");
2431
}
2432
2433
msFreeCharArray(tokens, ntmp);
2434
}
2435
if (strlen(buffer) > 0)
2436
strcat(buffer, ")");
2437
}
2438
else if (ntmp == 1) /* multiple times */
2439
{
2440
msFreeCharArray(tokens, ntmp);
2441
strcat(buffer, "(");
2442
for (i=0; i<numtimes; i++)
2443
{
2444
if (i > 0)
2445
strcat(buffer, " OR ");
2446
2447
strcat(buffer, "(");
2448
2449
strcat(buffer, "date_trunc('");
2450
strcat(buffer, timeresolution);
2451
strcat(buffer, "', ");
2452
strcat(buffer, timefield);
2453
strcat(buffer, ")");
2454
2455
strcat(buffer, " = ");
2456
2457
strcat(buffer, "'");
2458
2459
strcat(buffer, atimes[i]);
2460
2461
/* make sure that the timestring is complete and acceptable */
2462
/* to the date_trunc function : */
2463
/* - if the resolution is year (2004) or month (2004-01), */
2464
/* a complete string for time would be 2004-01-01 */
2465
/* - if the resolluion is hour or minute (2004-01-01 15), a */
2466
/* complete time is 2004-01-01 15:00:00 */
2467
if (strcasecmp(timeresolution, "year")==0)
2468
{
2469
nlength = strlen(atimes[i]);
2470
if (atimes[i][nlength-1] != '-')
2471
strcat(buffer,"-01-01");
2472
else
2473
strcat(buffer,"01-01");
2474
}
2475
else if (strcasecmp(timeresolution, "month")==0)
2476
{
2477
nlength = strlen(atimes[i]);
2478
if (atimes[i][nlength-1] != '-')
2479
strcat(buffer,"-01");
2480
else
2481
strcat(buffer,"01");
2482
}
2483
else if (strcasecmp(timeresolution, "hour")==0)
2484
{
2485
nlength = strlen(atimes[i]);
2486
if (atimes[i][nlength-1] != ':')
2487
strcat(buffer,":00:00");
2488
else
2489
strcat(buffer,"00:00");
2490
}
2491
else if (strcasecmp(timeresolution, "minute")==0)
2492
{
2493
nlength = strlen(atimes[i]);
2494
if (atimes[i][nlength-1] != ':')
2495
strcat(buffer,":00");
2496
else
2497
strcat(buffer,"00");
2498
}
2499
2500
strcat(buffer, "'");
2501
strcat(buffer, ")");
2502
}
2503
strcat(buffer, ")");
2504
}
2505
else
2506
{
2507
msFreeCharArray(atimes, numtimes);
2508
return MS_FALSE;
2509
}
2510
2511
msFreeCharArray(atimes, numtimes);
2512
2513
/* load the string to the filter */
2514
if (strlen(buffer) > 0)
2515
{
2516
if(lp->filteritem)
2517
free(lp->filteritem);
2518
lp->filteritem = strdup(timefield);
2519
if (&lp->filter)
2520
{
2521
if (lp->filter.type == MS_EXPRESSION)
2522
{
2523
strcat(buffer, "(");
2524
strcat(buffer, lp->filter.string);
2525
strcat(buffer, ") and ");
2526
}
2527
else
2528
freeExpression(&lp->filter);
2529
}
2530
loadExpressionString(&lp->filter, buffer);
2531
}
2532
2533
free(timeresolution);
2534
return MS_TRUE;
2535
2536
}
2537
2538
return MS_FALSE;
2539
}
2540
2541
int msPostGISLayerInitializeVirtualTable(layerObj *layer) {
2542
assert(layer != NULL);
2543
assert(layer->vtable != NULL);
2544
2545
layer->vtable->LayerInitItemInfo = msPostGISLayerInitItemInfo;
2546
layer->vtable->LayerFreeItemInfo = msPostGISLayerFreeItemInfo;
2547
layer->vtable->LayerOpen = msPostGISLayerOpen;
2548
layer->vtable->LayerIsOpen = msPostGISLayerIsOpen;
2549
layer->vtable->LayerWhichShapes = msPostGISLayerWhichShapes;
2550
layer->vtable->LayerNextShape = msPostGISLayerNextShape;
2551
layer->vtable->LayerResultsGetShape = msPostGISLayerResultsGetShape;
2552
layer->vtable->LayerGetShape = msPostGISLayerGetShape;
2553
layer->vtable->LayerClose = msPostGISLayerClose;
2554
layer->vtable->LayerGetItems = msPostGISLayerGetItems;
2555
layer->vtable->LayerGetExtent = msPostGISLayerGetExtent;
2556
layer->vtable->LayerApplyFilterToLayer = msLayerApplyCondSQLFilterToLayer;
2557
/* layer->vtable->LayerGetAutoStyle, not supported for this layer */
2558
layer->vtable->LayerCloseConnection = msPostGISLayerClose;
2559
layer->vtable->LayerSetTimeFilter = msPostGISLayerSetTimeFilter;
2560
/* layer->vtable->LayerCreateItems, use default */
2561
/* layer->vtable->LayerGetNumFeatures, use default */
2562
2563
return MS_SUCCESS;
2564
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1