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