1 /*
2 Copyright (c) <2007-2012> <Barbara Philippot - Olivier Courtin>
3
4 Permission is hereby granted, free of charge, to any person obtaining a copy
5 of this software and associated documentation files (the "Software"), to deal
6 in the Software without restriction, including without limitation the rights
7 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8 copies of the Software, and to permit persons to whom the Software is
9 furnished to do so, subject to the following conditions:
10
11 The above copyright notice and this permission notice shall be included in
12 all copies or substantial portions of the Software.
13
14 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
19 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
20 IN THE SOFTWARE.
21 */
22
23 #include <stdlib.h>
24 #include <stdio.h>
25 #include <assert.h>
26 #include <string.h>
27
28 #include "ows.h"
29
30
ows_layer_storage_init()31 ows_layer_storage * ows_layer_storage_init()
32 {
33 ows_layer_storage * storage;
34
35 storage = malloc(sizeof(ows_layer_storage));
36 assert(storage);
37
38 /* default values: srid='-1' */
39 storage->schema = buffer_init();
40 storage->srid = -1;
41 storage->geom_columns = list_init();
42 storage->is_degree = true;
43 storage->table = buffer_init();
44 storage->pkey = NULL;
45 storage->pkey_sequence = NULL;
46 storage->pkey_default = NULL;
47 storage->pkey_column_number = -1;
48 storage->attributes = array_init();
49 storage->not_null_columns = NULL;
50
51 return storage;
52 }
53
54
ows_layer_storage_free(ows_layer_storage * storage)55 void ows_layer_storage_free(ows_layer_storage * storage)
56 {
57 assert(storage);
58
59 if (storage->schema) buffer_free(storage->schema);
60 if (storage->table) buffer_free(storage->table);
61 if (storage->pkey) buffer_free(storage->pkey);
62 if (storage->pkey_sequence) buffer_free(storage->pkey_sequence);
63 if (storage->pkey_default) buffer_free(storage->pkey_default);
64 if (storage->geom_columns) list_free(storage->geom_columns);
65 if (storage->attributes) array_free(storage->attributes);
66 if (storage->not_null_columns) list_free(storage->not_null_columns);
67
68 free(storage);
69 storage = NULL;
70 }
71
72
73 #ifdef OWS_DEBUG
ows_layer_storage_flush(ows_layer_storage * storage,FILE * output)74 void ows_layer_storage_flush(ows_layer_storage * storage, FILE * output)
75 {
76 assert(storage);
77 assert(output);
78
79 if (storage->schema) {
80 fprintf(output, "schema: ");
81 buffer_flush(storage->schema, output);
82 fprintf(output, "\n");
83 }
84
85 if (storage->table) {
86 fprintf(output, "table: ");
87 buffer_flush(storage->table, output);
88 fprintf(output, "\n");
89 }
90
91 if (storage->geom_columns) {
92 fprintf(output, "geom_columns: ");
93 list_flush(storage->geom_columns, output);
94 fprintf(output, "\n");
95 }
96
97 fprintf(output, "srid: %i\n", storage->srid);
98 fprintf(output, "is_degree: %i\n", storage->is_degree?1:0);
99
100 if (storage->pkey) {
101 fprintf(output, "pkey: ");
102 buffer_flush(storage->pkey, output);
103 fprintf(output, "\n");
104 }
105
106 fprintf(output, "pkey_column_number: %i\n", storage->pkey_column_number);
107
108 if (storage->pkey_sequence) {
109 fprintf(output, "pkey_sequence: ");
110 buffer_flush(storage->pkey_sequence, output);
111 fprintf(output, "\n");
112 }
113
114 if (storage->pkey_default) {
115 fprintf(output, "pkey_default: ");
116 buffer_flush(storage->pkey_default, output);
117 fprintf(output, "\n");
118 }
119
120 if (storage->attributes) {
121 fprintf(output, "attributes: ");
122 array_flush(storage->attributes, output);
123 fprintf(output, "\n");
124 }
125
126 if (storage->not_null_columns) {
127 fprintf(output, "not_null_columns: ");
128 list_flush(storage->not_null_columns, output);
129 fprintf(output, "\n");
130 }
131
132 }
133 #endif
134
135
136 /*
137 * Retrieve not_null columns of a table related a given layer
138 */
ows_storage_fill_not_null(ows * o,ows_layer * l)139 static void ows_storage_fill_not_null(ows * o, ows_layer * l)
140 {
141 int i, nb_result;
142 buffer *sql, *b;
143 PGresult *res;
144
145 assert(o);
146 assert(l);
147 assert(l->storage);
148
149 sql = buffer_init();
150 buffer_add_str(sql, "SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n ");
151 buffer_add_str(sql, "WHERE n.nspname = '");
152 buffer_copy(sql, l->storage->schema);
153 buffer_add_str(sql, "' AND c.relname = '");
154 buffer_copy(sql, l->storage->table);
155 buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid ");
156 buffer_add_str(sql, "AND a.atttypid = t.oid AND a.attnotnull = 't'");
157
158 res = ows_psql_exec(o, sql->buf);
159 buffer_free(sql);
160
161 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
162 PQclear(res);
163 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "not_null columns");
164 return;
165 }
166
167 nb_result = PQntuples(res);
168 if (nb_result) l->storage->not_null_columns = list_init();
169 for (i = 0 ; i < nb_result ; i++) {
170 b = buffer_init();
171 buffer_add_str(b, PQgetvalue(res, i, 0));
172 list_add(l->storage->not_null_columns, b);
173 }
174
175 PQclear(res);
176 }
177
178
179 /*
180 * Retrieve pkey column of a table related a given layer
181 * And if success try also to retrieve a related pkey sequence
182 */
ows_storage_fill_pkey(ows * o,ows_layer * l)183 static void ows_storage_fill_pkey(ows * o, ows_layer * l)
184 {
185 buffer *sql;
186 PGresult *res;
187
188 assert(o);
189 assert(l);
190 assert(l->storage);
191
192 sql = buffer_init();
193
194 buffer_add_str(sql, "SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n ");
195 buffer_add_str(sql, "WHERE n.nspname = '");
196 buffer_copy(sql, l->storage->schema);
197 buffer_add_str(sql, "' AND c.table_name = '");
198 buffer_copy(sql, l->storage->table);
199 buffer_add_str(sql, "' AND c.constraint_name = (");
200
201 buffer_add_str(sql, "SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n ");
202 buffer_add_str(sql, "WHERE r.oid = c.conrelid AND relname = '");
203 buffer_copy(sql, l->storage->table);
204 buffer_add_str(sql, "' AND r.relnamespace = n.oid AND n.nspname = '");
205 buffer_copy(sql, l->storage->schema);
206 buffer_add_str(sql, "' AND c.contype = 'p')");
207
208 res = ows_psql_exec(o, sql->buf);
209 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
210 PQclear(res);
211 buffer_free(sql);
212 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "pkey column");
213 return;
214 }
215
216 /* Layer could have no Pkey indeed... (An SQL view for example) */
217 if (l->pkey || PQntuples(res) == 1) {
218 l->storage->pkey = buffer_init();
219 if (l->pkey) {
220 /*TODO check the column (l->pkey) in the table */
221 buffer_copy(l->storage->pkey, l->pkey);
222 } else {
223 buffer_add_str(l->storage->pkey, PQgetvalue(res, 0, 0));
224 }
225 buffer_empty(sql);
226 PQclear(res);
227
228 /* Retrieve the Pkey column number */
229 buffer_add_str(sql, "SELECT a.attnum FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n");
230 buffer_add_str(sql, " WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND n.nspname='");
231 buffer_copy(sql, l->storage->schema);
232 buffer_add_str(sql, "' AND c.relname='");
233 buffer_copy(sql, l->storage->table);
234 buffer_add_str(sql, "' AND a.attname='");
235 buffer_copy(sql, l->storage->pkey);
236 buffer_add_str(sql, "'");
237 res = ows_psql_exec(o, sql->buf);
238 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
239 PQclear(res);
240 buffer_free(sql);
241 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to find pkey column number.", "pkey_column number");
242 return;
243 }
244
245 /* -1 because column number start at 1 */
246 l->storage->pkey_column_number = atoi(PQgetvalue(res, 0, 0)) - 1;
247 buffer_empty(sql);
248 PQclear(res);
249
250 /* Now try to find a sequence related to this Pkey */
251 buffer_add_str(sql, "SELECT pg_get_serial_sequence('");
252 buffer_copy(sql, l->storage->schema);
253 buffer_add_str(sql, ".\"");
254 buffer_copy(sql, l->storage->table);
255 buffer_add_str(sql, "\"', '");
256 buffer_copy(sql, l->storage->pkey);
257 buffer_add_str(sql, "');");
258
259 res = ows_psql_exec(o, sql->buf);
260 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
261 PQclear(res);
262 buffer_free(sql);
263 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
264 "Unable to use pg_get_serial_sequence.", "pkey_sequence retrieve");
265 return;
266 }
267
268 /* Even if no sequence found, this function return an empty row
269 * so we must check that result string returned > 0 char
270 */
271 if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) {
272 l->storage->pkey_sequence = buffer_init();
273 buffer_add_str(l->storage->pkey_sequence, PQgetvalue(res, 0, 0));
274 }
275
276 buffer_empty(sql);
277 PQclear(res);
278 /* Now try to find a DEFAULT value related to this Pkey */
279 buffer_add_str(sql, "SELECT column_default FROM information_schema.columns WHERE table_schema = '");
280 buffer_copy(sql, l->storage->schema);
281 buffer_add_str(sql, "' AND table_name = '");
282 buffer_copy(sql, l->storage->table);
283 buffer_add_str(sql, "' AND column_name = '");
284 buffer_copy(sql, l->storage->pkey);
285 buffer_add_str(sql, "' AND table_catalog = current_database();");
286
287 res = ows_psql_exec(o, sql->buf);
288 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
289 PQclear(res);
290 buffer_free(sql);
291 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
292 "Unable to SELECT column_default FROM information_schema.columns.",
293 "pkey_default retrieve");
294 return;
295 }
296
297 /* Even if no DEFAULT value found, this function return an empty row
298 * so we must check that result string returned > 0 char
299 */
300 if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) {
301 l->storage->pkey_default = buffer_init();
302 buffer_add_str(l->storage->pkey_default, PQgetvalue(res, 0, 0));
303 }
304 }
305
306 PQclear(res);
307 buffer_free(sql);
308 }
309
310
311 /*
312 * Retrieve columns name and type of a table related a given layer
313 */
ows_storage_fill_attributes(ows * o,ows_layer * l)314 static void ows_storage_fill_attributes(ows * o, ows_layer * l)
315 {
316 buffer *sql;
317 PGresult *res;
318 buffer *b, *t;
319 int i, end;
320 list_node *ln;
321
322 assert(o);
323 assert(l);
324 assert(l->storage);
325
326 sql = buffer_init();
327
328 buffer_add_str(sql, "SELECT a.attname AS field, t.typname AS type ");
329 buffer_add_str(sql, "FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = '");
330 buffer_copy(sql, l->storage->schema);
331 buffer_add_str(sql, "' AND c.relname = '");
332 buffer_copy(sql, l->storage->table);
333 buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attrelid = c.oid AND a.atttypid = t.oid");
334 if (l->include_items) {
335 buffer_add_str(sql, " AND a.attname IN (");
336 for (ln = l->include_items->first ; ln ; ln = ln->next) {
337 buffer_add_str(sql, "'");
338 buffer_copy(sql, ln->value);
339 buffer_add_str(sql, "', ");
340 }
341 if (l->include_items->first && l->storage->pkey) {
342 buffer_add_str(sql, "'");
343 buffer_copy(sql, l->storage->pkey );
344 buffer_add_str(sql, "',");
345 }
346
347 buffer_add_str(sql, " '');");
348 } else {
349 buffer_add_str(sql, " AND a.attnum > 0;");
350 }
351
352 res = ows_psql_exec(o, sql->buf);
353 buffer_free(sql);
354
355 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
356 PQclear(res);
357 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "fill_attributes");
358 return;
359 }
360
361 for (i = 0, end = PQntuples(res); i < end; i++) {
362 b = buffer_init();
363 t = buffer_init();
364 buffer_add_str(b, PQgetvalue(res, i, 0));
365 buffer_add_str(t, PQgetvalue(res, i, 1));
366
367 /* If the column is a geometry, get its real geometry type */
368 if (buffer_cmp(t, "geometry")) {
369 PGresult *geom_res;
370 buffer *geom_sql = buffer_init();
371 buffer_add_str(geom_sql, "SELECT type from geometry_columns where f_table_schema='");
372 buffer_copy(geom_sql, l->storage->schema);
373 buffer_add_str(geom_sql,"' and f_table_name='");
374 buffer_copy(geom_sql, l->storage->table);
375 buffer_add_str(geom_sql,"' and f_geometry_column='");
376 buffer_copy(geom_sql, b);
377 buffer_add_str(geom_sql,"';");
378
379 geom_res = ows_psql_exec(o, geom_sql->buf);
380 buffer_free(geom_sql);
381
382 if (PQresultStatus(geom_res) != PGRES_TUPLES_OK || PQntuples(geom_res) == 0) {
383 PQclear(res);
384 PQclear(geom_res);
385 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
386 "Unable to access geometry_columns table, try Populate_Geometry_Columns()", "fill_attributes");
387 return;
388 }
389
390 buffer_empty(t);
391 buffer_add_str(t, PQgetvalue(geom_res, 0, 0));
392 PQclear(geom_res);
393 }
394
395 array_add(l->storage->attributes, b, t);
396 }
397 PQclear(res);
398
399 }
400
401
ows_layer_storage_fill(ows * o,ows_layer * l,bool is_geom)402 static void ows_layer_storage_fill(ows * o, ows_layer * l, bool is_geom)
403 {
404 buffer * sql;
405 PGresult *res;
406 int i, end;
407
408 assert(o);
409 assert(l);
410 assert(l->storage);
411
412 sql = buffer_init();
413 if (is_geom) buffer_add_str(sql, "SELECT srid, f_geometry_column FROM geometry_columns");
414 else buffer_add_str(sql, "SELECT srid, f_geography_column FROM geography_columns");
415
416 buffer_add_str(sql, " WHERE f_table_schema='");
417 buffer_copy(sql, l->storage->schema);
418 buffer_add_str(sql, "' AND f_table_name='");
419 buffer_copy(sql, l->storage->table);
420 buffer_add_str(sql, "'");
421 if (l->include_items) {
422 buffer_add_str(sql, is_geom?" AND f_geometry_column IN ('":" AND f_geography_column IN ('");
423 list_implode(sql, "','", l->include_items);
424 buffer_add_str(sql, "')");
425 }
426 if (l->exclude_items) {
427 buffer_add_str(sql, is_geom?" AND f_geometry_column NOT IN ('":" AND f_geography_column NOT IN ('");
428 list_implode(sql, "','", l->exclude_items);
429 buffer_add_str(sql, "')");
430 }
431 buffer_add_str(sql, ";");
432
433 res = ows_psql_exec(o, sql->buf);
434 buffer_empty(sql);
435
436 if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) == 0) {
437 PQclear(res);
438
439 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
440 "All config file layers are not availables in geometry_columns or geography_columns",
441 "storage");
442 return;
443 }
444
445 l->storage->srid = atoi(PQgetvalue(res, 0, 0));
446
447 for (i = 0, end = PQntuples(res); i < end; i++)
448 list_add_str(l->storage->geom_columns, PQgetvalue(res, i, 1));
449
450 buffer_add_str(sql, "SELECT * FROM spatial_ref_sys WHERE srid=");
451 buffer_add_str(sql, PQgetvalue(res, 0, 0));
452 buffer_add_str(sql, " AND proj4text like '%%units=m%%'");
453
454 PQclear(res);
455
456 res = ows_psql_exec(o, sql->buf);
457 buffer_free(sql);
458
459 if (PQntuples(res) != 1)
460 l->storage->is_degree = true;
461 else
462 l->storage->is_degree = false;
463
464 PQclear(res);
465
466 ows_storage_fill_pkey(o, l);
467 ows_storage_fill_attributes(o, l);
468 ows_storage_fill_not_null(o, l);
469 }
470
471
472 /*
473 * Used by --check command line option
474 */
ows_layers_storage_flush(ows * o,FILE * output)475 void ows_layers_storage_flush(ows * o, FILE * output)
476 {
477 ows_layer_node *ln;
478
479 assert(o);
480 assert(o->layers);
481
482 for (ln = o->layers->first ; ln ; ln = ln->next) {
483 if (ln->layer->storage) {
484 fprintf(output, " - %s.%s (%i) -> %s [",
485 ln->layer->storage->schema->buf,
486 ln->layer->storage->table->buf,
487 ln->layer->storage->srid,
488 ln->layer->name_prefix->buf);
489
490 if (ln->layer->retrievable) fprintf(output, "R");
491 if (ln->layer->writable) fprintf(output, "W");
492 fprintf(output, "]\n");
493 }
494 }
495 }
496
497
ows_layers_storage_fill(ows * o)498 void ows_layers_storage_fill(ows * o)
499 {
500 PGresult *res, *res_g;
501 ows_layer_node *ln;
502 bool filled;
503 buffer *sql;
504 int i, end;
505
506 assert(o);
507 assert(o->layers);
508
509 sql = buffer_init();
510 buffer_add_str(sql, "SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns");
511 res = ows_psql_exec(o, sql->buf);
512 buffer_empty(sql);
513
514 buffer_add_str(sql, "SELECT DISTINCT f_table_schema, f_table_name FROM geography_columns");
515 res_g = ows_psql_exec(o, sql->buf);
516 buffer_free(sql);
517
518 for (ln = o->layers->first ; ln ; ln = ln->next) {
519 filled = false;
520
521 for (i = 0, end = PQntuples(res); i < end; i++) {
522 if ( buffer_cmp(ln->layer->storage->schema, (char *) PQgetvalue(res, i, 0))
523 && buffer_cmp(ln->layer->storage->table, (char *) PQgetvalue(res, i, 1))) {
524 ows_layer_storage_fill(o, ln->layer, true);
525 filled = true;
526 }
527 }
528
529 for (i = 0, end = PQntuples(res_g); i < end; i++) {
530 if ( buffer_cmp(ln->layer->storage->schema, (char *) PQgetvalue(res_g, i, 0))
531 && buffer_cmp(ln->layer->storage->table, (char *) PQgetvalue(res_g, i, 1))) {
532 ows_layer_storage_fill(o, ln->layer, false);
533 filled = true;
534 }
535 }
536
537 if (!filled) {
538 if (ln->layer->storage) ows_layer_storage_free(ln->layer->storage);
539 ln->layer->storage = NULL;
540 }
541 }
542
543 PQclear(res);
544 PQclear(res_g);
545 }
546