tinyows 1.2.2
ows_storage.c
Go to the documentation of this file.
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
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_geographic = true;
43 storage->table = buffer_init();
44 storage->pkey = NULL;
45 storage->pkey_sequence = NULL;
46 storage->pkey_default = NULL;
47 storage->attributes = array_init();
48 storage->not_null_columns = NULL;
49
50 return storage;
51}
52
53
55{
56 assert(storage);
57
58 if (storage->schema) buffer_free(storage->schema);
59 if (storage->table) buffer_free(storage->table);
60 if (storage->pkey) buffer_free(storage->pkey);
61 if (storage->pkey_sequence) buffer_free(storage->pkey_sequence);
62 if (storage->pkey_default) buffer_free(storage->pkey_default);
63 if (storage->geom_columns) list_free(storage->geom_columns);
64 if (storage->attributes) array_free(storage->attributes);
65 if (storage->not_null_columns) list_free(storage->not_null_columns);
66
67 free(storage);
68 storage = NULL;
69}
70
71
72#ifdef OWS_DEBUG
73void ows_layer_storage_flush(ows_layer_storage * storage, FILE * output)
74{
75 assert(storage);
76 assert(output);
77
78 if (storage->schema) {
79 fprintf(output, "schema: ");
80 buffer_flush(storage->schema, output);
81 fprintf(output, "\n");
82 }
83
84 if (storage->table) {
85 fprintf(output, "table: ");
86 buffer_flush(storage->table, output);
87 fprintf(output, "\n");
88 }
89
90 if (storage->geom_columns) {
91 fprintf(output, "geom_columns: ");
92 list_flush(storage->geom_columns, output);
93 fprintf(output, "\n");
94 }
95
96 fprintf(output, "srid: %i\n", storage->srid);
97 fprintf(output, "is_geographic: %i\n", storage->is_geographic?1:0);
98
99 if (storage->pkey) {
100 fprintf(output, "pkey: ");
101 buffer_flush(storage->pkey, output);
102 fprintf(output, "\n");
103 }
104
105 if (storage->pkey_sequence) {
106 fprintf(output, "pkey_sequence: ");
107 buffer_flush(storage->pkey_sequence, output);
108 fprintf(output, "\n");
109 }
110
111 if (storage->pkey_default) {
112 fprintf(output, "pkey_default: ");
113 buffer_flush(storage->pkey_default, output);
114 fprintf(output, "\n");
115 }
116
117 if (storage->attributes) {
118 fprintf(output, "attributes: ");
119 array_flush(storage->attributes, output);
120 fprintf(output, "\n");
121 }
122
123 if (storage->not_null_columns) {
124 fprintf(output, "not_null_columns: ");
125 list_flush(storage->not_null_columns, output);
126 fprintf(output, "\n");
127 }
128
129}
130#endif
131
132
133/*
134 * Retrieve not_null columns of a table related a given layer
135 */
137{
138 int i, nb_result;
139 buffer *sql, *b;
140 PGresult *res;
141
142 assert(o);
143 assert(l);
144 assert(l->storage);
145
146 sql = buffer_init();
147 buffer_add_str(sql, "SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n ");
148 buffer_add_str(sql, "WHERE n.nspname = '");
149 buffer_copy(sql, l->storage->schema);
150 buffer_add_str(sql, "' AND c.relname = '");
151 buffer_copy(sql, l->storage->table);
152 buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid ");
153 buffer_add_str(sql, "AND a.atttypid = t.oid AND a.attnotnull = 't'");
154
155 res = ows_psql_exec(o, sql->buf);
156 buffer_free(sql);
157
158 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
159 PQclear(res);
160 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "not_null columns");
161 return;
162 }
163
164 nb_result = PQntuples(res);
165 if (nb_result) l->storage->not_null_columns = list_init();
166 for (i = 0 ; i < nb_result ; i++) {
167 b = buffer_init();
168 buffer_add_str(b, PQgetvalue(res, i, 0));
170 }
171
172 PQclear(res);
173}
174
175
176/*
177 * Retrieve pkey column of a table related a given layer
178 * And if success try also to retrieve a related pkey sequence
179 */
181{
182 buffer *sql;
183 PGresult *res;
184
185 assert(o);
186 assert(l);
187 assert(l->storage);
188
189 sql = buffer_init();
190
191 buffer_add_str(sql, "SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n ");
192 buffer_add_str(sql, "WHERE c.table_schema=n.nspname AND n.nspname = '");
193 buffer_copy(sql, l->storage->schema);
194 buffer_add_str(sql, "' AND c.table_name = '");
195 buffer_copy(sql, l->storage->table);
196 buffer_add_str(sql, "' AND c.constraint_name = (");
197
198 buffer_add_str(sql, "SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n ");
199 buffer_add_str(sql, "WHERE r.oid = c.conrelid AND relname = '");
200 buffer_copy(sql, l->storage->table);
201 buffer_add_str(sql, "' AND r.relnamespace = n.oid AND n.nspname = '");
202 buffer_copy(sql, l->storage->schema);
203 buffer_add_str(sql, "' AND c.contype = 'p')");
204
205 res = ows_psql_exec(o, sql->buf);
206 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
207 PQclear(res);
208 buffer_free(sql);
209 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "pkey column");
210 return;
211 }
212
213 /* Layer could have no Pkey indeed... (An SQL view for example) */
214 if (l->pkey || PQntuples(res) == 1) {
215 l->storage->pkey = buffer_init();
216 if (l->pkey) {
217 /*TODO check the column (l->pkey) in the table */
218 buffer_copy(l->storage->pkey, l->pkey);
219 } else {
220 buffer_add_str(l->storage->pkey, PQgetvalue(res, 0, 0));
221 }
222 buffer_empty(sql);
223 PQclear(res);
224
225 /* Now try to find a sequence related to this Pkey */
226 buffer_add_str(sql, "SELECT pg_get_serial_sequence('");
227 buffer_copy(sql, l->storage->schema);
228 buffer_add_str(sql, ".\"");
229 buffer_copy(sql, l->storage->table);
230 buffer_add_str(sql, "\"', '");
231 buffer_copy(sql, l->storage->pkey);
232 buffer_add_str(sql, "');");
233
234 res = ows_psql_exec(o, sql->buf);
235 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
236 char message[256];
237 snprintf(message, sizeof(message),
238 "Unable to use pg_get_serial_sequence(%s, %s, %s).",
239 l->storage->schema->buf,
240 l->storage->table->buf,
241 l->storage->pkey->buf);
242 PQclear(res);
243 buffer_free(sql);
245 message, "pkey_sequence retrieve");
246 return;
247 }
248
249 /* Even if no sequence found, this function return an empty row
250 * so we must check that result string returned > 0 char
251 */
252 if ( l->pkey_sequence ||
253 (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) ) {
255 if ( l->pkey_sequence )
257 else
258 buffer_add_str(l->storage->pkey_sequence, PQgetvalue(res, 0, 0));
259 }
260
261 buffer_empty(sql);
262 PQclear(res);
263 /* Now try to find a DEFAULT value related to this Pkey */
264 buffer_add_str(sql, "SELECT column_default FROM information_schema.columns WHERE table_schema = '");
265 buffer_copy(sql, l->storage->schema);
266 buffer_add_str(sql, "' AND table_name = '");
267 buffer_copy(sql, l->storage->table);
268 buffer_add_str(sql, "' AND column_name = '");
269 buffer_copy(sql, l->storage->pkey);
270 buffer_add_str(sql, "' AND table_catalog = current_database();");
271
272 res = ows_psql_exec(o, sql->buf);
273 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
274 PQclear(res);
275 buffer_free(sql);
277 "Unable to SELECT column_default FROM information_schema.columns.",
278 "pkey_default retrieve");
279 return;
280 }
281
282 /* Even if no DEFAULT value found, this function return an empty row
283 * so we must check that result string returned > 0 char
284 */
285 if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) {
287 buffer_add_str(l->storage->pkey_default, PQgetvalue(res, 0, 0));
288 }
289 }
290
291 PQclear(res);
292 buffer_free(sql);
293}
294
295
296/*
297 * Retrieve columns name and type of a table related a given layer
298 */
300{
301 buffer *sql;
302 PGresult *res;
303 buffer *b, *t;
304 int i, end;
305 list_node *ln;
306
307 assert(o);
308 assert(l);
309 assert(l->storage);
310
311 sql = buffer_init();
312
313 buffer_add_str(sql, "SELECT a.attname AS field, t.typname AS type ");
314 buffer_add_str(sql, "FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = '");
315 buffer_copy(sql, l->storage->schema);
316 buffer_add_str(sql, "' AND c.relname = '");
317 buffer_copy(sql, l->storage->table);
318 buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attrelid = c.oid AND a.atttypid = t.oid");
319 if (l->include_items) {
320 buffer_add_str(sql, " AND a.attname IN (");
321 for (ln = l->include_items->first ; ln ; ln = ln->next) {
322 buffer_add_str(sql, "'");
323 buffer_copy(sql, ln->value);
324 buffer_add_str(sql, "', ");
325 }
326 if (l->include_items->first && l->storage->pkey) {
327 buffer_add_str(sql, "'");
328 buffer_copy(sql, l->storage->pkey );
329 buffer_add_str(sql, "',");
330 }
331
332 buffer_add_str(sql, " '');");
333 } else {
334 buffer_add_str(sql, " AND a.attnum > 0;");
335 }
336
337 res = ows_psql_exec(o, sql->buf);
338 buffer_free(sql);
339
340 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
341 PQclear(res);
342 ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "fill_attributes");
343 return;
344 }
345
346 for (i = 0, end = PQntuples(res); i < end; i++) {
347 b = buffer_init();
348 t = buffer_init();
349 buffer_add_str(b, PQgetvalue(res, i, 0));
350 buffer_add_str(t, PQgetvalue(res, i, 1));
351
352 /* If the column is a geometry, get its real geometry type */
353 if (buffer_cmp(t, "geometry")) {
354 PGresult *geom_res;
355 buffer *geom_sql = buffer_init();
356 buffer_add_str(geom_sql, "SELECT type from geometry_columns where f_table_schema='");
357 buffer_copy(geom_sql, l->storage->schema);
358 buffer_add_str(geom_sql,"' and f_table_name='");
359 buffer_copy(geom_sql, l->storage->table);
360 buffer_add_str(geom_sql,"' and f_geometry_column='");
361 buffer_copy(geom_sql, b);
362 buffer_add_str(geom_sql,"';");
363
364 geom_res = ows_psql_exec(o, geom_sql->buf);
365 buffer_free(geom_sql);
366
367 if (PQresultStatus(geom_res) != PGRES_TUPLES_OK || PQntuples(geom_res) == 0) {
368 PQclear(res);
369 PQclear(geom_res);
371 "Unable to access geometry_columns table, try Populate_Geometry_Columns()", "fill_attributes");
372 return;
373 }
374
375 buffer_empty(t);
376 buffer_add_str(t, PQgetvalue(geom_res, 0, 0));
377 PQclear(geom_res);
378 }
379
380 array_add(l->storage->attributes, b, t);
381 }
382 PQclear(res);
383
384}
385
386
387static void ows_layer_storage_fill(ows * o, ows_layer * l, bool is_geom)
388{
389 buffer * sql;
390 PGresult *res;
391 int i, end;
392
393 assert(o);
394 assert(l);
395 assert(l->storage);
396
397 sql = buffer_init();
398 if (is_geom) buffer_add_str(sql, "SELECT srid, f_geometry_column FROM geometry_columns");
399 else buffer_add_str(sql, "SELECT srid, f_geography_column FROM geography_columns");
400
401 buffer_add_str(sql, " WHERE f_table_schema='");
402 buffer_copy(sql, l->storage->schema);
403 buffer_add_str(sql, "' AND f_table_name='");
404 buffer_copy(sql, l->storage->table);
405 buffer_add_str(sql, "'");
406 if (l->include_items) {
407 buffer_add_str(sql, is_geom?" AND f_geometry_column IN ('":" AND f_geography_column IN ('");
408 list_implode(sql, "','", l->include_items);
409 buffer_add_str(sql, "')");
410 }
411 if (l->exclude_items) {
412 buffer_add_str(sql, is_geom?" AND f_geometry_column NOT IN ('":" AND f_geography_column NOT IN ('");
413 list_implode(sql, "','", l->exclude_items);
414 buffer_add_str(sql, "')");
415 }
416 buffer_add_str(sql, ";");
417
418 res = ows_psql_exec(o, sql->buf);
419 buffer_empty(sql);
420
421 if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) > 0) {
422
423 l->storage->srid = atoi(PQgetvalue(res, 0, 0));
424
425 for (i = 0, end = PQntuples(res); i < end; i++)
426 list_add_str(l->storage->geom_columns, PQgetvalue(res, i, 1));
427
428 buffer_add_str(sql, "SELECT * FROM spatial_ref_sys WHERE srid=");
429 buffer_add_str(sql, PQgetvalue(res, 0, 0));
430 buffer_add_str(sql, " AND proj4text like '%%units=m%%'");
431
432 PQclear(res);
433
434 res = ows_psql_exec(o, sql->buf);
435 buffer_free(sql);
436
437 if (PQntuples(res) != 1)
438 l->storage->is_geographic = true;
439 else
440 l->storage->is_geographic = false;
441
442 }
443 PQclear(res);
444
446 if( o->exit ) return;
448 if( o->exit ) return;
450}
451
452
453/*
454 * Used by --check command line option
455 */
456void ows_layers_storage_flush(ows * o, FILE * output)
457{
458 ows_layer_node *ln;
459
460 assert(o);
461 assert(o->layers);
462
463 for (ln = o->layers->first ; ln ; ln = ln->next) {
464 if (ln->layer->storage) {
465 fprintf(output, " - %s.%s (%i) -> %s [",
466 ln->layer->storage->schema->buf,
467 ln->layer->storage->table->buf,
468 ln->layer->storage->srid,
469 ln->layer->name_prefix->buf);
470
471 if (ln->layer->retrievable) fprintf(output, "R");
472 if (ln->layer->writable) fprintf(output, "W");
473 fprintf(output, "]\n");
474 }
475 }
476}
477
478
480{
481 PGresult *res, *res_g;
482 ows_layer_node *ln;
483 bool filled;
484 buffer *sql;
485 int i, end;
486
487 assert(o);
488 assert(o->layers);
489
490 sql = buffer_init();
491 buffer_add_str(sql, "SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns");
492 res = ows_psql_exec(o, sql->buf);
493 buffer_empty(sql);
494
495 buffer_add_str(sql, "SELECT DISTINCT f_table_schema, f_table_name FROM geography_columns");
496 res_g = ows_psql_exec(o, sql->buf);
497 buffer_free(sql);
498
499 for (ln = o->layers->first ; ln ; ln = ln->next) {
500 filled = false;
501
502 for (i = 0, end = PQntuples(res); i < end; i++) {
503 if ( buffer_cmp(ln->layer->storage->schema, (char *) PQgetvalue(res, i, 0))
504 && buffer_cmp(ln->layer->storage->table, (char *) PQgetvalue(res, i, 1))) {
505 ows_layer_storage_fill(o, ln->layer, true);
506 if( o->exit ) break;
507 filled = true;
508 }
509 }
510
511 for (i = 0, end = PQntuples(res_g); i < end; i++) {
512 if ( buffer_cmp(ln->layer->storage->schema, (char *) PQgetvalue(res_g, i, 0))
513 && buffer_cmp(ln->layer->storage->table, (char *) PQgetvalue(res_g, i, 1))) {
514 ows_layer_storage_fill(o, ln->layer, false);
515 if( o->exit ) break;
516 filled = true;
517 }
518 }
519
520 if (!filled) {
521 PGresult* res_t;
522
523 sql = buffer_init();
524 buffer_add_str(sql, "SELECT 1 FROM information_schema.tables WHERE table_schema='");
525 buffer_copy(sql, ln->layer->storage->schema);
526 buffer_add_str(sql,"' and table_name='");
527 buffer_copy(sql, ln->layer->storage->table);
528 buffer_add_str(sql,"'");
529 res_t = ows_psql_exec(o, sql->buf);
530 buffer_free(sql);
531 if (PQresultStatus(res_t) == PGRES_TUPLES_OK && PQntuples(res_t) > 0) {
532 filled = true;
533 }
534 PQclear(res_t);
535
536 if (filled) {
537 ows_layer_storage_fill(o, ln->layer, false);
538 if( o->exit ) break;
539 }
540 }
541
542 if (!filled) {
544 ln->layer->storage = NULL;
545 }
546 }
547
548 PQclear(res);
549 PQclear(res_g);
550}
void buffer_empty(buffer *buf)
Definition buffer.c:100
PGresult * ows_psql_exec(ows *o, const char *sql)
Definition ows_psql.c:56
void list_implode(buffer *buf, const char *separator, const list *l)
Definition list.c:461
void buffer_copy(buffer *dest, const buffer *src)
Definition buffer.c:350
bool buffer_cmp(const buffer *buf, const char *str)
Definition buffer.c:290
void list_free(list *l)
Definition list.c:54
void buffer_add_str(buffer *buf, const char *str)
Definition buffer.c:254
void list_flush(const list *l, FILE *output)
void ows_layer_storage_flush(ows_layer_storage *storage, FILE *output)
list * list_init()
Definition list.c:36
void buffer_flush(buffer *buf, FILE *output)
Definition buffer.c:112
void ows_error(ows *o, enum ows_error_code code, char *message, char *locator)
Definition ows_error.c:71
void array_free(array *a)
Definition array.c:53
void list_add(list *l, buffer *value)
Definition list.c:71
void list_add_str(list *l, char *value)
Definition list.c:102
void array_flush(const array *a, FILE *output)
void buffer_free(buffer *buf)
Definition buffer.c:83
buffer * buffer_init()
Definition buffer.c:61
void array_add(array *a, buffer *key, buffer *value)
Definition array.c:80
array * array_init()
Definition array.c:36
static void ows_storage_fill_attributes(ows *o, ows_layer *l)
static void ows_storage_fill_not_null(ows *o, ows_layer *l)
ows_layer_storage * ows_layer_storage_init()
Definition ows_storage.c:31
static void ows_layer_storage_fill(ows *o, ows_layer *l, bool is_geom)
static void ows_storage_fill_pkey(ows *o, ows_layer *l)
void ows_layers_storage_flush(ows *o, FILE *output)
void ows_layer_storage_free(ows_layer_storage *storage)
Definition ows_storage.c:54
void ows_layers_storage_fill(ows *o)
@ OWS_ERROR_REQUEST_SQL_FAILED
Definition ows_struct.h:166
char * buf
size to next realloc
Definition ows_struct.h:39
struct List_node * next
Definition ows_struct.h:45
buffer * value
Definition ows_struct.h:44
list_node * first
Definition ows_struct.h:50
ows_layer_node * first
Definition ows_struct.h:205
ows_layer * layer
Definition ows_struct.h:199
struct Ows_layer_node * next
Definition ows_struct.h:200
buffer * pkey_default
Definition ows_struct.h:103
buffer * pkey_sequence
Definition ows_struct.h:102
list * not_null_columns
Definition ows_struct.h:99
bool retrievable
Definition ows_struct.h:181
bool writable
Definition ows_struct.h:182
list * include_items
Definition ows_struct.h:188
buffer * name_prefix
Definition ows_struct.h:178
ows_layer_storage * storage
Definition ows_struct.h:195
buffer * pkey
Definition ows_struct.h:189
buffer * pkey_sequence
Definition ows_struct.h:190
list * exclude_items
Definition ows_struct.h:187
bool exit
Definition ows_struct.h:368
ows_layer_list * layers
Definition ows_struct.h:402

Generated for tinyows by doxygen 1.12.0