1 /*
2 * Copyright (C) 2009 - 2011 Vivien Malerba <malerba@gnome-db.org>
3 * Copyright (C) 2010 David King <davidk@openismus.com>
4 * Copyright (C) 2010 Murray Cumming <murrayc@murrayc.com>
5 *
6 * This library is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2 of the License, or (at your option) any later version.
10 *
11 * This library is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 *
16 * You should have received a copy of the GNU Lesser General Public
17 * License along with this library; if not, write to the
18 * Free Software Foundation, Inc., 51 Franklin St, Fifth Floor,
19 * Boston, MA 02110-1301, USA.
20 */
21
22 #include <glib/gi18n-lib.h>
23 #include <libgda/gda-sql-builder.h>
24 #include "gda-postgres-reuseable.h"
25 #include "gda-postgres-parser.h"
26
27 #include <libgda/sqlite/keywords_hash.h>
28 #include "keywords_hash.c" /* this one is dynamically generated */
29
30 /*
31 * Reuseable interface entry point
32 */
33 static GdaProviderReuseableOperations
34 _gda_postgres_reuseable = {
35 _gda_postgres_reuseable_new_data,
36 _gda_postgres_reuseable_reset_data,
37 _gda_postgres_reuseable_get_g_type,
38 _gda_postgres_reuseable_get_reserved_keywords_func,
39 _gda_postgres_reuseable_create_parser,
40 {
41 ._info = _gda_postgres_meta__info,
42 ._btypes = _gda_postgres_meta__btypes,
43 ._udt = _gda_postgres_meta__udt,
44 .udt = _gda_postgres_meta_udt,
45 ._udt_cols = _gda_postgres_meta__udt_cols,
46 .udt_cols = _gda_postgres_meta_udt_cols,
47 ._enums = _gda_postgres_meta__enums,
48 .enums = _gda_postgres_meta_enums,
49 ._domains = _gda_postgres_meta__domains,
50 .domains = _gda_postgres_meta_domains,
51 ._constraints_dom = _gda_postgres_meta__constraints_dom,
52 .constraints_dom = _gda_postgres_meta_constraints_dom,
53 ._el_types = _gda_postgres_meta__el_types,
54 .el_types = _gda_postgres_meta_el_types,
55 ._collations = _gda_postgres_meta__collations,
56 .collations = _gda_postgres_meta_collations,
57 ._character_sets = _gda_postgres_meta__character_sets,
58 .character_sets = _gda_postgres_meta_character_sets,
59 ._schemata = _gda_postgres_meta__schemata,
60 .schemata = _gda_postgres_meta_schemata,
61 ._tables_views = _gda_postgres_meta__tables_views,
62 .tables_views = _gda_postgres_meta_tables_views,
63 ._columns = _gda_postgres_meta__columns,
64 .columns = _gda_postgres_meta_columns,
65 ._view_cols = _gda_postgres_meta__view_cols,
66 .view_cols = _gda_postgres_meta_view_cols,
67 ._constraints_tab = _gda_postgres_meta__constraints_tab,
68 .constraints_tab = _gda_postgres_meta_constraints_tab,
69 ._constraints_ref = _gda_postgres_meta__constraints_ref,
70 .constraints_ref = _gda_postgres_meta_constraints_ref,
71 ._key_columns = _gda_postgres_meta__key_columns,
72 .key_columns = _gda_postgres_meta_key_columns,
73 ._check_columns = _gda_postgres_meta__check_columns,
74 .check_columns = _gda_postgres_meta_check_columns,
75 ._triggers = _gda_postgres_meta__triggers,
76 .triggers = _gda_postgres_meta_triggers,
77 ._routines = _gda_postgres_meta__routines,
78 .routines = _gda_postgres_meta_routines,
79 ._routine_col = _gda_postgres_meta__routine_col,
80 .routine_col = _gda_postgres_meta_routine_col,
81 ._routine_par = _gda_postgres_meta__routine_par,
82 .routine_par = _gda_postgres_meta_routine_par,
83 ._indexes_tab = _gda_postgres_meta__indexes_tab,
84 .indexes_tab = _gda_postgres_meta_indexes_tab,
85 ._index_cols = _gda_postgres_meta__index_cols,
86 .index_cols = _gda_postgres_meta_index_cols
87 }
88 };
89
90 GdaProviderReuseableOperations *
_gda_postgres_reuseable_get_ops(void)91 _gda_postgres_reuseable_get_ops (void)
92 {
93 return &_gda_postgres_reuseable;
94 }
95
96 /*
97 * Postgres type identification
98 */
99 typedef struct {
100 gchar *name;
101 unsigned int oid; /* <=> to Postgres's Oid type */
102 GType type;
103 gchar *comments;
104 gchar *owner;
105 } GdaPostgresTypeOid;
106
107 static void
gda_postgres_type_oid_free(GdaPostgresTypeOid * typedata)108 gda_postgres_type_oid_free (GdaPostgresTypeOid *typedata)
109 {
110 g_free (typedata->name);
111 g_free (typedata->comments);
112 g_free (typedata->owner);
113 g_free (typedata);
114 }
115
116 #ifdef GDA_DEBUG
117 void
_gda_postgres_test_keywords(void)118 _gda_postgres_test_keywords (void)
119 {
120 V82test_keywords();
121 V83test_keywords();
122 V84test_keywords();
123 }
124 #endif
125
126 GdaProviderReuseable *
_gda_postgres_reuseable_new_data(void)127 _gda_postgres_reuseable_new_data (void)
128 {
129 GdaPostgresReuseable *reuseable;
130 reuseable = g_new0 (GdaPostgresReuseable, 1);
131 reuseable->types_oid_hash = NULL;
132 reuseable->types_dbtype_hash = NULL;
133 _gda_postgres_provider_meta_init (NULL);
134
135 ((GdaProviderReuseable*)reuseable)->operations = &_gda_postgres_reuseable;
136
137 return (GdaProviderReuseable*) reuseable;
138 }
139
140 void
_gda_postgres_reuseable_reset_data(GdaProviderReuseable * rdata)141 _gda_postgres_reuseable_reset_data (GdaProviderReuseable *rdata)
142 {
143 GdaPostgresReuseable *reuseable;
144 reuseable = (GdaPostgresReuseable*) rdata;
145
146 g_free (rdata->server_version);
147 if (reuseable->types_dbtype_hash)
148 g_hash_table_destroy (reuseable->types_dbtype_hash);
149 if (reuseable->types_oid_hash)
150 g_hash_table_destroy (reuseable->types_oid_hash);
151
152 /* don't free reuseable->avoid_types */
153 g_free (reuseable->avoid_types_oids);
154 g_free (reuseable->any_type_oid);
155 memset (reuseable, 0, sizeof (GdaPostgresReuseable));
156 }
157
158 static GdaDataModel *
execute_select(GdaConnection * cnc,GdaPostgresReuseable * rdata,const gchar * sql)159 execute_select (GdaConnection *cnc, GdaPostgresReuseable *rdata, const gchar *sql)
160 {
161 GdaSqlParser *parser;
162 GdaStatement *stmt;
163 GdaDataModel *model;
164 parser = _gda_postgres_reuseable_create_parser ((GdaProviderReuseable*) rdata);
165
166 stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL);
167 g_object_unref (parser);
168 g_assert (stmt);
169
170 model = gda_connection_statement_execute_select (cnc, stmt, NULL, NULL);
171 g_object_unref (stmt);
172
173 return model;
174 }
175
176 gboolean
_gda_postgres_compute_version(GdaConnection * cnc,GdaPostgresReuseable * rdata,GError ** error)177 _gda_postgres_compute_version (GdaConnection *cnc, GdaPostgresReuseable *rdata, GError **error)
178 {
179 GdaSqlBuilder *b;
180 GdaStatement *stmt;
181 GdaDataModel *model;
182
183 b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
184 const guint id_func = gda_sql_builder_add_function (b, "version", 0);
185 gda_sql_builder_add_field_value_id (b, id_func, 0);
186 stmt = gda_sql_builder_get_statement (b, NULL);
187 g_object_unref (b);
188 g_assert (stmt);
189
190 model = gda_connection_statement_execute_select (cnc, stmt, NULL, error);
191 g_object_unref (stmt);
192 if (!model)
193 return FALSE;
194
195 const GValue *cvalue;
196 cvalue = gda_data_model_get_value_at (model, 0, 0, NULL);
197 if (!cvalue) {
198 g_set_error (error, GDA_SERVER_PROVIDER_ERROR,
199 GDA_SERVER_PROVIDER_INTERNAL_ERROR, "%s",
200 _("Can't get version data from server"));
201 g_object_unref (model);
202 return FALSE;
203 }
204
205 const gchar *str;
206 str = g_value_get_string (cvalue);
207 ((GdaProviderReuseable*)rdata)->server_version = g_strdup (str);
208
209 /* analyse string */
210 const gchar *ptr;
211 rdata->version_float = 0;
212
213 /* go on the first digit of version number */
214 ptr = str;
215 while (*ptr && *ptr != ' ')
216 ptr++;
217 if (*ptr) {
218 ptr++;
219
220 /* scan version parts */
221 GdaProviderReuseable *prdata = (GdaProviderReuseable*) rdata;
222 sscanf (ptr, "%d.%d.%d", &(prdata->major), &(prdata->minor), &(prdata->micro));
223
224 /* elaborate the version number as a float */
225 rdata->version_float = ((gdouble) ((GdaProviderReuseable*)rdata)->major) +
226 ((gdouble) ((GdaProviderReuseable*)rdata)->minor) / 10.0 +
227 ((gdouble) ((GdaProviderReuseable*)rdata)->micro) / 100.0;
228 }
229
230 g_object_unref (model);
231
232 /*
233 g_print ("VERSIONS: [%f] [%d.%d.%d]\n", rdata->version_float,
234 ((GdaProviderReuseable*)rdata)->major,
235 ((GdaProviderReuseable*)rdata)->minor,
236 ((GdaProviderReuseable*)rdata)->micro);
237 */
238 return TRUE;
239 }
240
241 static GType
postgres_name_to_g_type(const gchar * name,const gchar * conv_func_name)242 postgres_name_to_g_type (const gchar *name, const gchar *conv_func_name)
243 {
244 /* default built in data types */
245 if (!strcmp (name, "bool"))
246 return G_TYPE_BOOLEAN;
247 else if (!strcmp (name, "int8"))
248 return G_TYPE_INT64;
249 else if (!strcmp (name, "int4") || !strcmp (name, "abstime"))
250 return G_TYPE_INT;
251 else if (!strcmp (name, "int2"))
252 return GDA_TYPE_SHORT;
253 else if (!strcmp (name, "float4"))
254 return G_TYPE_FLOAT;
255 else if (!strcmp (name, "float8"))
256 return G_TYPE_DOUBLE;
257 else if (!strcmp (name, "numeric"))
258 return GDA_TYPE_NUMERIC;
259 else if (!strncmp (name, "timestamp", 9))
260 return GDA_TYPE_TIMESTAMP;
261 else if (!strcmp (name, "date"))
262 return G_TYPE_DATE;
263 else if (!strncmp (name, "time", 4))
264 return GDA_TYPE_TIME;
265 else if (!strcmp (name, "point"))
266 return GDA_TYPE_GEOMETRIC_POINT;
267 else if (!strcmp (name, "oid"))
268 return GDA_TYPE_BLOB;
269 else if (!strcmp (name, "bytea"))
270 return GDA_TYPE_BINARY;
271
272 /* other data types, using the conversion function name as a hint */
273 if (!conv_func_name)
274 return G_TYPE_STRING;
275
276 if (!strncmp (conv_func_name, "int2", 4))
277 return GDA_TYPE_SHORT;
278 if (!strncmp (conv_func_name, "int4", 4))
279 return G_TYPE_INT;
280 if (!strncmp (conv_func_name, "int8", 4))
281 return G_TYPE_INT64;
282 if (!strncmp (conv_func_name, "float4", 6))
283 return G_TYPE_FLOAT;
284 if (!strncmp (conv_func_name, "float8", 6))
285 return G_TYPE_DOUBLE;
286 if (!strncmp (conv_func_name, "timestamp", 9))
287 return GDA_TYPE_TIMESTAMP;
288 if (!strncmp (conv_func_name, "time", 4))
289 return GDA_TYPE_TIME;
290 if (!strncmp (conv_func_name, "date", 4))
291 return G_TYPE_DATE;
292 if (!strncmp (conv_func_name, "bool", 4))
293 return G_TYPE_BOOLEAN;
294 if (!strncmp (conv_func_name, "oid", 3))
295 return GDA_TYPE_BLOB;
296 if (!strncmp (conv_func_name, "bytea", 5))
297 return GDA_TYPE_BINARY;
298 return G_TYPE_STRING;
299 }
300
301 void
_gda_postgres_compute_types(GdaConnection * cnc,GdaPostgresReuseable * rdata)302 _gda_postgres_compute_types (GdaConnection *cnc, GdaPostgresReuseable *rdata)
303 {
304 if (rdata->types_oid_hash)
305 return;
306
307 rdata->types_oid_hash = g_hash_table_new_full (g_int_hash, g_int_equal,
308 NULL, (GDestroyNotify) gda_postgres_type_oid_free);
309 rdata->types_dbtype_hash = g_hash_table_new (g_str_hash, g_str_equal);
310
311 GdaDataModel *model, *model_avoid, *model_anyoid = NULL;
312 gint ncols, nrows, i;
313 gchar *avoid_types = NULL;
314 GString *string;
315
316 if (rdata->version_float == 0)
317 _gda_postgres_compute_version (cnc, rdata, NULL);
318 if (rdata->version_float < 7.3) {
319 gchar *query;
320 avoid_types = "'SET', 'cid', 'oid', 'int2vector', 'oidvector', 'regproc', 'smgr', 'tid', 'unknown', 'xid'";
321 /* main query to fetch infos about the data types */
322 query = g_strdup_printf ("SELECT pg_type.oid, typname, usename, obj_description(pg_type.oid) "
323 "FROM pg_type, pg_user "
324 "WHERE typowner=usesysid AND typrelid = 0 AND typname !~ '^_' "
325 "AND typname not in (%s) "
326 "ORDER BY typname", avoid_types);
327 model = execute_select (cnc, rdata, query);
328 g_free (query);
329
330 /* query to fetch non returned data types */
331 query = g_strdup_printf ("SELECT pg_type.oid FROM pg_type WHERE typname in (%s)", avoid_types);
332 model_avoid = execute_select (cnc, rdata, query);
333 g_free (query);
334 }
335 else {
336 gchar *query;
337 avoid_types = "'any', 'anyarray', 'anyelement', 'cid', 'cstring', 'int2vector', 'internal', 'language_handler', 'oidvector', 'opaque', 'record', 'refcursor', 'regclass', 'regoper', 'regoperator', 'regproc', 'regprocedure', 'regtype', 'SET', 'smgr', 'tid', 'trigger', 'unknown', 'void', 'xid'";
338
339 /* main query to fetch infos about the data types */
340 query = g_strdup_printf (
341 "SELECT t.oid, t.typname, u.usename, pg_catalog.obj_description(t.oid), t.typinput "
342 "FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_user u ON (t.typowner=u.usesysid), pg_catalog.pg_namespace n "
343 "WHERE n.oid = t.typnamespace "
344 "AND pg_catalog.pg_type_is_visible(t.oid) "
345 /*--AND (n.nspname = 'public' OR n.nspname = 'pg_catalog')*/
346 "AND typname !~ '^_' "
347 "AND (t.typrelid = 0 OR "
348 "(SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) "
349 "AND t.typname not in (%s) "
350 "ORDER BY typname", avoid_types);
351 model = execute_select (cnc, rdata, query);
352 g_free (query);
353
354 /* query to fetch non returned data types */
355 query = g_strdup_printf ("SELECT t.oid FROM pg_catalog.pg_type t WHERE t.typname in (%s)",
356 avoid_types);
357 model_avoid = execute_select (cnc, rdata, query);
358 g_free (query);
359
360 /* query to fetch the oid of the 'any' data type */
361 model_anyoid = execute_select (cnc, rdata,
362 "SELECT t.oid FROM pg_catalog.pg_type t WHERE t.typname = 'any'");
363 }
364
365 if (rdata->version_float == 0)
366 _gda_postgres_compute_version (cnc, rdata, NULL);
367 if (!model || !model_avoid ||
368 ((rdata->version_float >= 7.3) && !model_anyoid)) {
369 if (model)
370 g_object_unref (model);
371 if (model_avoid)
372 g_object_unref (model_avoid);
373 if (model_anyoid)
374 g_object_unref (model_anyoid);
375 return;
376 }
377
378 /* Data types returned to the Gda client */
379 nrows = gda_data_model_get_n_rows (model);
380 ncols = gda_data_model_get_n_columns (model);
381 if (nrows == 0)
382 g_warning ("PostgreSQL provider did not find any data type (expect some mis-behaviours) please report the error to bugzilla.gnome.org");
383 for (i = 0; i < nrows; i++) {
384 const GValue *conv_func_name = NULL;
385 const GValue *values[4];
386 gint j;
387 gboolean allread = TRUE;
388 if (ncols >= 5)
389 conv_func_name = gda_data_model_get_value_at (model, 4, i, NULL);
390 for (j = 0; j < 4; j++) {
391 values[j] = gda_data_model_get_value_at (model, j, i, NULL);
392 if (!values [j]) {
393 allread = FALSE;
394 break;
395 }
396 }
397 if (allread && (G_VALUE_TYPE (values[1]) == G_TYPE_STRING)) {
398 GdaPostgresTypeOid *td;
399 td = g_new0 (GdaPostgresTypeOid, 1);
400 td->name = g_value_dup_string (values [1]);
401 td->oid = (guint) g_ascii_strtoull (g_value_get_string (values[0]), NULL, 10);
402 td->type = postgres_name_to_g_type (td->name,
403 conv_func_name ? g_value_get_string (conv_func_name) : NULL);
404 if (G_VALUE_TYPE (values[3]) == G_TYPE_STRING)
405 td->comments = g_value_dup_string (values [3]);
406 if (G_VALUE_TYPE (values[2]) == G_TYPE_STRING)
407 td->owner = g_value_dup_string (values [2]);
408
409 g_hash_table_insert (rdata->types_oid_hash, &(td->oid), td);
410 g_hash_table_insert (rdata->types_dbtype_hash, &(td->name), td);
411 }
412 }
413
414 /* Make a string of data types internal to postgres and not returned, for future queries */
415 string = NULL;
416 nrows = gda_data_model_get_n_rows (model_avoid);
417 for (i = 0; i < nrows; i++) {
418 const GValue *cvalue;
419
420 cvalue = gda_data_model_get_value_at (model_avoid, 0, i, NULL);
421 if (cvalue && (G_VALUE_TYPE (cvalue) == G_TYPE_STRING)) {
422 if (!string)
423 string = g_string_new (g_value_get_string (cvalue));
424 else {
425 g_string_append (string, ", ");
426 g_string_append (string, g_value_get_string (cvalue));
427 }
428 }
429 }
430 rdata->avoid_types = avoid_types;
431 if (string)
432 rdata->avoid_types_oids = g_string_free (string, FALSE);
433
434 g_object_unref (model);
435 g_object_unref (model_avoid);
436
437 /* make a string of the oid of type 'any' */
438 rdata->any_type_oid = NULL;
439 if (model_anyoid) {
440 if (gda_data_model_get_n_rows (model_anyoid) == 1) {
441 const GValue *cvalue;
442 cvalue = gda_data_model_get_value_at (model_anyoid, 0, 0, NULL);
443 if (cvalue && (G_VALUE_TYPE (cvalue) == G_TYPE_STRING))
444 rdata->any_type_oid = g_value_dup_string (cvalue);
445 }
446 g_object_unref (model_anyoid);
447 }
448 }
449
450 GType
_gda_postgres_type_oid_to_gda(GdaConnection * cnc,GdaPostgresReuseable * rdata,unsigned int postgres_oid)451 _gda_postgres_type_oid_to_gda (GdaConnection *cnc, GdaPostgresReuseable *rdata, unsigned int postgres_oid)
452 {
453 GdaPostgresTypeOid *type;
454 guint id;
455 id = postgres_oid;
456
457 _gda_postgres_compute_types (cnc, rdata);
458 type = g_hash_table_lookup (rdata->types_oid_hash, &id);
459 if (type)
460 return type->type;
461 else
462 return G_TYPE_STRING;
463 }
464
465 GType
_gda_postgres_reuseable_get_g_type(GdaConnection * cnc,GdaProviderReuseable * rdata,const gchar * db_type)466 _gda_postgres_reuseable_get_g_type (GdaConnection *cnc, GdaProviderReuseable *rdata, const gchar *db_type)
467 {
468 GdaPostgresTypeOid *type;
469 g_return_val_if_fail (db_type, GDA_TYPE_NULL);
470
471 _gda_postgres_compute_types (cnc, (GdaPostgresReuseable*)rdata);
472 type = g_hash_table_lookup (((GdaPostgresReuseable*)rdata)->types_dbtype_hash, db_type);
473 if (type)
474 return type->type;
475 else
476 return GDA_TYPE_NULL;
477 }
478
479
480 GdaSqlReservedKeywordsFunc
_gda_postgres_reuseable_get_reserved_keywords_func(GdaProviderReuseable * rdata)481 _gda_postgres_reuseable_get_reserved_keywords_func (GdaProviderReuseable *rdata)
482 {
483 if (rdata) {
484 switch (rdata->major) {
485 case 8:
486 if (rdata->minor == 2)
487 return V82is_keyword;
488 if (rdata->minor == 3)
489 return V83is_keyword;
490 if (rdata->minor == 4)
491 return V84is_keyword;
492 return V84is_keyword;
493 default:
494 return V84is_keyword;
495 break;
496 }
497 }
498 return V84is_keyword;
499 }
500
501 GdaSqlParser *
_gda_postgres_reuseable_create_parser(G_GNUC_UNUSED GdaProviderReuseable * rdata)502 _gda_postgres_reuseable_create_parser (G_GNUC_UNUSED GdaProviderReuseable *rdata)
503 {
504 return GDA_SQL_PARSER (g_object_new (GDA_TYPE_POSTGRES_PARSER, NULL));
505 }
506