1 /*
2 * Copyright (C) 2006 Bas Driessen <bas.driessen@xobas.com>
3 * Copyright (C) 2006 - 2012 Vivien Malerba <malerba@gnome-db.org>
4 * Copyright (C) 2008 Murray Cumming <murrayc@murrayc.com>
5 * Copyright (C) 2010 David King <davidk@openismus.com>
6 *
7 * This library is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU Lesser General Public
9 * License as published by the Free Software Foundation; either
10 * version 2 of the License, or (at your option) any later version.
11 *
12 * This library is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 * Lesser General Public License for more details.
16 *
17 * You should have received a copy of the GNU Lesser General Public
18 * License along with this library; if not, write to the
19 * Free Software Foundation, Inc., 51 Franklin St, Fifth Floor,
20 * Boston, MA 02110-1301, USA.
21 */
22
23 #include "gda-sqlite-ddl.h"
24 #include <glib/gi18n-lib.h>
25 #include <libgda/gda-data-handler.h>
26
27
28 gchar *
_gda_sqlite_render_CREATE_TABLE(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,GError ** error)29 _gda_sqlite_render_CREATE_TABLE (GdaServerProvider *provider, GdaConnection *cnc,
30 GdaServerOperation *op, GError **error)
31 {
32 GString *string;
33 const GValue *value;
34 gboolean allok = TRUE;
35 gboolean hasfields = FALSE;
36 gint nrows;
37 gint i;
38 gboolean first;
39 GSList *pkfields = NULL; /* list of GValue* composing the pkey */
40 gint nbpkfields = 0;
41 gchar *sql = NULL;
42 gchar *conflict_algo = NULL;
43 gchar *tmp;
44
45 /* CREATE TABLE */
46 string = g_string_new ("CREATE ");
47 value = gda_server_operation_get_value_at (op, "/TABLE_DEF_P/TABLE_TEMP");
48 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
49 g_string_append (string, "TEMP ");
50 g_string_append (string, "TABLE ");
51
52 value = gda_server_operation_get_value_at (op, "/TABLE_DEF_P/TABLE_IFNOTEXISTS");
53 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
54 g_string_append (string, "IF NOT EXISTS ");
55
56 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/TABLE_DEF_P/TABLE_NAME");
57 g_string_append (string, tmp);
58 g_free (tmp);
59 g_string_append (string, " (");
60
61 /* FIELDS */
62 if (allok) {
63 GdaServerOperationNode *node;
64
65 node = gda_server_operation_get_node_info (op, "/FIELDS_A");
66 g_assert (node);
67
68 /* finding if there is a composed primary key */
69 nrows = gda_data_model_get_n_rows (node->model);
70 for (i = 0; i < nrows; i++) {
71 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_PKEY/%d", i);
72 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
73 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider,
74 "/FIELDS_A/@COLUMN_NAME/%d", i);
75 pkfields = g_slist_append (pkfields, tmp);
76 nbpkfields++;
77 }
78 }
79
80 /* manually defined fields */
81 first = TRUE;
82 for (i = 0; i < nrows; i++) {
83 gboolean pkautoinc = FALSE;
84 hasfields = TRUE;
85 if (first)
86 first = FALSE;
87 else
88 g_string_append (string, ", ");
89
90 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/FIELDS_A/@COLUMN_NAME/%d", i);
91 g_string_append (string, tmp);
92 g_free (tmp);
93 g_string_append_c (string, ' ');
94
95 if (nbpkfields == 1) {
96 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_AUTOINC/%d", i);
97 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
98 const gchar *tmp;
99 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_TYPE/%d", i);
100 tmp = g_value_get_string (value);
101 if (!g_ascii_strcasecmp (tmp, "gint") ||
102 !g_ascii_strcasecmp (tmp, "int")) {
103 g_string_append (string, "INTEGER PRIMARY KEY AUTOINCREMENT");
104 pkautoinc = TRUE;
105 }
106 }
107 }
108
109 if (!pkautoinc) {
110 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_TYPE/%d", i);
111 g_string_append (string, g_value_get_string (value));
112
113 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_SIZE/%d", i);
114 if (value && G_VALUE_HOLDS (value, G_TYPE_UINT)) {
115 g_string_append_printf (string, "(%d", g_value_get_uint (value));
116
117 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_SCALE/%d", i);
118 if (value && G_VALUE_HOLDS (value, G_TYPE_UINT))
119 g_string_append_printf (string, ",%d)", g_value_get_uint (value));
120 else
121 g_string_append (string, ")");
122 }
123
124 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_DEFAULT/%d", i);
125 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
126 const gchar *str = g_value_get_string (value);
127 if (str && *str) {
128 g_string_append (string, " DEFAULT ");
129 g_string_append (string, str);
130 }
131 }
132
133 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_NNUL/%d", i);
134 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
135 g_string_append (string, " NOT NULL");
136
137 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_UNIQUE/%d", i);
138 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
139 g_string_append (string, " UNIQUE");
140
141 if (nbpkfields == 1) {
142 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_PKEY/%d", i);
143 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
144 g_string_append (string, " PRIMARY KEY");
145
146 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_CONFLICT/%d", i);
147 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
148 const gchar *str = g_value_get_string (value);
149 if (str && *str) {
150 g_string_append (string, " ON CONFLICT ");
151 g_string_append (string, str);
152 }
153
154 }
155 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_AUTOINC/%d", i);
156 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value)) {
157 g_string_append (string, " AUTOINCREMENT");
158 }
159 }
160
161 }
162 else {
163 if (!conflict_algo) {
164 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_CONFLICT/%d", i);
165 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
166 const gchar *str = g_value_get_string (value);
167 if (str && *str)
168 conflict_algo = g_strdup (str);
169 }
170 }
171 }
172
173 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_CHECK/%d", i);
174 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
175 const gchar *str = g_value_get_string (value);
176 if (str && *str) {
177 g_string_append (string, " CHECK (");
178 g_string_append (string, str);
179 g_string_append_c (string, ')');
180 }
181 }
182
183 value = gda_server_operation_get_value_at (op, "/FIELDS_A/@COLUMN_COLLATE/%d", i);
184 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
185 const gchar *str = g_value_get_string (value);
186 if (str && *str) {
187 g_string_append (string, " COLLATE ");
188 g_string_append (string, str);
189 }
190 }
191 }
192 }
193 }
194
195 /* composed primary key */
196 if (nbpkfields > 1) {
197 GSList *list;
198
199 g_string_append (string, ", PRIMARY KEY (");
200 for (list = pkfields; list; list = list->next) {
201 if (list != pkfields)
202 g_string_append (string, ", ");
203 g_string_append (string, (gchar *) list->data);
204 }
205 g_string_append_c (string, ')');
206
207 if (conflict_algo) {
208 g_string_append (string, " ON CONFLICT ");
209 g_string_append (string, conflict_algo);
210 }
211 }
212 g_slist_foreach (pkfields, (GFunc) g_free, NULL);
213 g_slist_free (pkfields);
214
215 g_free (conflict_algo);
216 g_string_append (string, ")");
217
218 if (!hasfields) {
219 allok = FALSE;
220 g_set_error (error, GDA_SERVER_OPERATION_ERROR,
221 GDA_SERVER_OPERATION_INCORRECT_VALUE_ERROR,
222 "%s", _("Table to create must have at least one row"));
223 }
224
225 sql = string->str;
226 g_string_free (string, FALSE);
227
228 return sql;
229 }
230
231 gchar *
_gda_sqlite_render_DROP_TABLE(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)232 _gda_sqlite_render_DROP_TABLE (GdaServerProvider *provider, GdaConnection *cnc,
233 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
234 {
235 GString *string;
236 const GValue *value;
237 gchar *sql = NULL;
238 gchar *tmp;
239
240 /* DROP TABLE */
241 string = g_string_new ("DROP TABLE");
242
243 value = gda_server_operation_get_value_at (op, "/TABLE_DESC_P/TABLE_IFEXISTS");
244 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
245 g_string_append (string, " IF EXISTS");
246
247 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/TABLE_DESC_P/TABLE_NAME");
248 g_string_append_c (string, ' ');
249 g_string_append (string, tmp);
250 g_free (tmp);
251
252 sql = string->str;
253 g_string_free (string, FALSE);
254
255 return sql;
256 }
257
258 gchar *
_gda_sqlite_render_RENAME_TABLE(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)259 _gda_sqlite_render_RENAME_TABLE (GdaServerProvider *provider, GdaConnection *cnc,
260 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
261 {
262 GString *string;
263 gchar *sql = NULL;
264 gchar *tmp;
265
266 /* DROP TABLE */
267 string = g_string_new ("ALTER TABLE ");
268
269 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/TABLE_DESC_P/TABLE_NAME");
270 g_string_append (string, tmp);
271 g_free (tmp);
272
273 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/TABLE_DESC_P/TABLE_NEW_NAME");
274 g_string_append (string, " RENAME TO ");
275 g_string_append (string, tmp);
276 g_free (tmp);
277
278 sql = string->str;
279 g_string_free (string, FALSE);
280
281 return sql;
282 }
283
284 gchar *
_gda_sqlite_render_ADD_COLUMN(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)285 _gda_sqlite_render_ADD_COLUMN (GdaServerProvider *provider, GdaConnection *cnc,
286 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
287 {
288 GString *string;
289 const GValue *value;
290 gchar *sql = NULL;
291 gchar *tmp;
292
293 /* DROP TABLE */
294 string = g_string_new ("ALTER TABLE ");
295
296 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/COLUMN_DEF_P/TABLE_NAME");
297 g_string_append (string, tmp);
298 g_free (tmp);
299
300 g_string_append (string, " ADD COLUMN ");
301
302 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/COLUMN_DEF_P/COLUMN_NAME");
303 g_string_append (string, tmp);
304 g_free (tmp);
305
306 value = gda_server_operation_get_value_at (op, "/COLUMN_DEF_P/COLUMN_TYPE");
307 g_assert (value && G_VALUE_HOLDS (value, G_TYPE_STRING));
308 g_string_append_c (string, ' ');
309 g_string_append (string, g_value_get_string (value));
310
311 value = gda_server_operation_get_value_at (op, "/COLUMN_DEF_P/COLUMN_SIZE");
312 if (value && G_VALUE_HOLDS (value, G_TYPE_UINT)) {
313 g_string_append_printf (string, "(%d", g_value_get_uint (value));
314
315 value = gda_server_operation_get_value_at (op, "/COLUMN_DEF_P/COLUMN_SCALE");
316 if (value && G_VALUE_HOLDS (value, G_TYPE_UINT))
317 g_string_append_printf (string, ",%d)", g_value_get_uint (value));
318 else
319 g_string_append (string, ")");
320 }
321
322 value = gda_server_operation_get_value_at (op, "/COLUMN_DEF_P/COLUMN_DEFAULT");
323 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
324 const gchar *str = g_value_get_string (value);
325 if (str && *str) {
326 g_string_append (string, " DEFAULT ");
327 g_string_append (string, str);
328 }
329 }
330
331 value = gda_server_operation_get_value_at (op, "/COLUMN_DEF_P/COLUMN_NNUL");
332 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
333 g_string_append (string, " NOT NULL");
334
335 value = gda_server_operation_get_value_at (op, "/COLUMN_DEF_P/COLUMN_CHECK");
336 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
337 const gchar *str = g_value_get_string (value);
338 if (str && *str) {
339 g_string_append (string, " CHECK (");
340 g_string_append (string, str);
341 g_string_append_c (string, ')');
342 }
343 }
344
345 sql = string->str;
346 g_string_free (string, FALSE);
347
348 return sql;
349 }
350
351
352 gchar *
_gda_sqlite_render_CREATE_INDEX(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)353 _gda_sqlite_render_CREATE_INDEX (GdaServerProvider *provider, GdaConnection *cnc,
354 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
355 {
356 GString *string;
357 const GValue *value;
358 gchar *sql = NULL;
359 GdaServerOperationNode *node;
360 gint nrows, i;
361 gchar *tmp;
362
363 /* CREATE INDEX */
364 string = g_string_new ("CREATE ");
365
366 value = gda_server_operation_get_value_at (op, "/INDEX_DEF_P/INDEX_TYPE");
367 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING) &&
368 g_value_get_string (value) && *g_value_get_string (value)) {
369 g_string_append (string, g_value_get_string (value));
370 g_string_append_c (string, ' ');
371 }
372
373 g_string_append (string, "INDEX ");
374
375 value = gda_server_operation_get_value_at (op, "/INDEX_DEF_P/INDEX_IFNOTEXISTS");
376 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
377 g_string_append (string, " IF NOT EXISTS ");
378
379
380 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/INDEX_DEF_P/INDEX_NAME");
381 g_string_append (string, tmp);
382 g_free (tmp);
383
384 g_string_append (string, " ON ");
385
386 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/INDEX_DEF_P/INDEX_ON_TABLE");
387 g_string_append (string, tmp);
388 g_free (tmp);
389
390 /* fields or expressions the index is on */
391 g_string_append (string, " (");
392 node = gda_server_operation_get_node_info (op, "/INDEX_FIELDS_S");
393 g_assert (node);
394 nrows = gda_server_operation_get_sequence_size (op, "/INDEX_FIELDS_S");
395 for (i = 0; i < nrows; i++) {
396 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider,
397 "/INDEX_FIELDS_S/%d/INDEX_FIELD", i);
398 if (tmp) {
399 if (i != 0)
400 g_string_append (string, ", ");
401 g_string_append (string, tmp);
402 g_free (tmp);
403
404 value = gda_server_operation_get_value_at (op, "/INDEX_FIELDS_S/%d/INDEX_COLLATE", i);
405 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
406 const gchar *str = g_value_get_string (value);
407 if (str && *str) {
408 g_string_append (string, " COLLATE ");
409 g_string_append (string, str);
410 }
411 }
412
413
414 value = gda_server_operation_get_value_at (op, "/INDEX_FIELDS_S/%d/INDEX_SORT_ORDER", i);
415 if (value && G_VALUE_HOLDS (value, G_TYPE_STRING)) {
416 const gchar *str = g_value_get_string (value);
417 if (str && *str) {
418 g_string_append_c (string, ' ');
419 g_string_append (string, str);
420 }
421 }
422 }
423 }
424
425 g_string_append (string, ")");
426
427 sql = string->str;
428 g_string_free (string, FALSE);
429
430 return sql;
431 }
432
433 gchar *
_gda_sqlite_render_DROP_INDEX(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)434 _gda_sqlite_render_DROP_INDEX (GdaServerProvider *provider, GdaConnection *cnc,
435 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
436 {
437 GString *string;
438 const GValue *value;
439 gchar *sql = NULL;
440 gchar *tmp;
441
442 /* DROP INDEX */
443 string = g_string_new ("DROP INDEX ");
444
445 value = gda_server_operation_get_value_at (op, "/INDEX_DESC_P/INDEX_IFEXISTS");
446 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
447 g_string_append (string, "IF EXISTS ");
448
449 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/INDEX_DESC_P/INDEX_NAME");
450 g_string_append (string, tmp);
451 g_free (tmp);
452
453 sql = string->str;
454 g_string_free (string, FALSE);
455
456 return sql;
457 }
458
459 gchar *
_gda_sqlite_render_CREATE_VIEW(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)460 _gda_sqlite_render_CREATE_VIEW (GdaServerProvider *provider, GdaConnection *cnc,
461 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
462 {
463 GString *string;
464 const GValue *value;
465 gboolean allok = TRUE;
466 gchar *sql = NULL;
467 gchar *tmp;
468
469 string = g_string_new ("CREATE ");
470 value = gda_server_operation_get_value_at (op, "/VIEW_DEF_P/VIEW_TEMP");
471 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
472 g_string_append (string, "TEMP ");
473
474 g_string_append (string, "VIEW ");
475
476 value = gda_server_operation_get_value_at (op, "/VIEW_DEF_P/VIEW_IFNOTEXISTS");
477 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
478 g_string_append (string, "IF NOT EXISTS ");
479
480 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/VIEW_DEF_P/VIEW_NAME");
481 g_string_append (string, tmp);
482 g_free (tmp);
483
484 if (allok) {
485 value = gda_server_operation_get_value_at (op, "/VIEW_DEF_P/VIEW_DEF");
486 g_assert (value && G_VALUE_HOLDS (value, G_TYPE_STRING));
487 g_string_append (string, " AS ");
488 g_string_append (string, g_value_get_string (value));
489 }
490
491 if (allok) {
492 sql = string->str;
493 g_string_free (string, FALSE);
494 }
495 else {
496 sql = NULL;
497 g_string_free (string, TRUE);
498 }
499
500 return sql;
501 }
502
503 gchar *
_gda_sqlite_render_DROP_VIEW(GdaServerProvider * provider,GdaConnection * cnc,GdaServerOperation * op,G_GNUC_UNUSED GError ** error)504 _gda_sqlite_render_DROP_VIEW (GdaServerProvider *provider, GdaConnection *cnc,
505 GdaServerOperation *op, G_GNUC_UNUSED GError **error)
506 {
507 GString *string;
508 const GValue *value;
509 gchar *sql = NULL;
510 gchar *tmp;
511
512 string = g_string_new ("DROP VIEW");
513
514 value = gda_server_operation_get_value_at (op, "/VIEW_DESC_P/VIEW_IFEXISTS");
515 if (value && G_VALUE_HOLDS (value, G_TYPE_BOOLEAN) && g_value_get_boolean (value))
516 g_string_append (string, " IF EXISTS");
517
518 tmp = gda_server_operation_get_sql_identifier_at (op, cnc, provider, "/VIEW_DESC_P/VIEW_NAME");
519 g_string_append_c (string, ' ');
520 g_string_append (string, tmp);
521 g_free (tmp);
522
523 sql = string->str;
524 g_string_free (string, FALSE);
525
526 return sql;
527 }
528