From 78aaaa9017122d6d25e69c53f1124815a52fd711 Mon Sep 17 00:00:00 2001 From: Andrea Zagli Date: Wed, 26 Jun 2013 11:18:30 +0200 Subject: [PATCH] Added function GdaEx::get_sql_from_hashtable (closes #689). --- .cproject | 56 ++++++++-------- .gitignore | 2 + .project | 79 +++++++++++++---------- src/gdaex.c | 158 +++++++++++++++++++++++++++++++++++++++++++++- src/gdaex.h | 14 ++++ tests/Makefile.am | 1 + tests/getsql.c | 152 ++++++++++++++++++++++++++++++++++++++++++++ 7 files changed, 398 insertions(+), 64 deletions(-) create mode 100644 tests/getsql.c diff --git a/.cproject b/.cproject index 064f4a1..3339b7f 100644 --- a/.cproject +++ b/.cproject @@ -3,44 +3,38 @@ - - + + + + + + - - - - - - - - - + + + + + + + - - - - @@ -49,12 +43,18 @@ - + + + + + + + - - - + + + diff --git a/.gitignore b/.gitignore index 7c0a49c..ed1272a 100644 --- a/.gitignore +++ b/.gitignore @@ -37,6 +37,7 @@ po/libgdaex.pot *.o *.la *.bak +intltool* libgdaex*tar* tests/test_prefix* tests/query_editor @@ -44,3 +45,4 @@ tests/grid tests/fill_liststore tests/*.exe tests/select +tests/getsql diff --git a/.project b/.project index 97b97f7..e31ce08 100644 --- a/.project +++ b/.project @@ -69,7 +69,7 @@ - 1322811819863 + 1372229253590 30 @@ -78,7 +78,7 @@ - 1322811819866 + 1372229253595 30 @@ -87,16 +87,7 @@ - 1322811819870 - - 22 - - org.eclipse.ui.ide.multiFilter - 1.0-name-matches-false-false-config.h - - - - 1322811819875 + 1372229253598 22 @@ -105,7 +96,7 @@ - 1322811819878 + 1372229253604 22 @@ -114,7 +105,7 @@ - 1322811819881 + 1372229253608 22 @@ -123,7 +114,7 @@ - 1322811819885 + 1372229253611 22 @@ -132,7 +123,7 @@ - 1322811819888 + 1372229253620 22 @@ -141,7 +132,7 @@ - 1322811819891 + 1372229253623 22 @@ -150,7 +141,7 @@ - 1322811819895 + 1372229253627 22 @@ -159,7 +150,7 @@ - 1322811819898 + 1372229253631 22 @@ -168,7 +159,7 @@ - 1322811819902 + 1372229253634 22 @@ -177,7 +168,7 @@ - 1322811819905 + 1372229253637 22 @@ -186,7 +177,7 @@ - 1322811819908 + 1372229253640 22 @@ -195,7 +186,7 @@ - 1322811819912 + 1372229253643 22 @@ -204,7 +195,7 @@ - 1322811819915 + 1372229253646 22 @@ -213,7 +204,7 @@ - 1322811819918 + 1372229253650 22 @@ -222,7 +213,7 @@ - 1322811819921 + 1372229253653 22 @@ -231,7 +222,7 @@ - 1322811819924 + 1372229253656 22 @@ -240,7 +231,7 @@ - 1322811819928 + 1372229253660 22 @@ -249,7 +240,7 @@ - 1322811819931 + 1372229253663 22 @@ -258,7 +249,7 @@ - 1322811819934 + 1372229253668 22 @@ -267,7 +258,7 @@ - 1322811819938 + 1372229253671 22 @@ -276,7 +267,7 @@ - 1322811819942 + 1372229253674 22 @@ -285,7 +276,7 @@ - 1322811820098 + 1372229253677 22 @@ -294,7 +285,7 @@ - 1322811820101 + 1372229253682 22 @@ -303,7 +294,7 @@ - 1322811820117 + 1372229253686 22 @@ -311,5 +302,23 @@ 1.0-name-matches-false-false-*.lo + + 1372229253690 + + 22 + + org.eclipse.ui.ide.multiFilter + 1.0-name-matches-false-false-*.dll + + + + 1372229253694 + + 22 + + org.eclipse.ui.ide.multiFilter + 1.0-name-matches-false-false-*.a + + diff --git a/src/gdaex.c b/src/gdaex.c index c843e5d..2cfdf44 100644 --- a/src/gdaex.c +++ b/src/gdaex.c @@ -33,6 +33,7 @@ #include #include +#include #include "gdaex.h" @@ -726,7 +727,7 @@ gboolean gdaex_data_model_is_empty (GdaDataModel *data_model) { return (!GDA_IS_DATA_MODEL (data_model) - || gda_data_model_get_n_rows (data_model) == 0); + || gda_data_model_get_n_rows (data_model) < 1); } /** @@ -1781,6 +1782,8 @@ gchar gchar *ret; const GValue *v; + ret = NULL; + v = gda_data_model_iter_get_value_at (iter, col); if (v == NULL) { @@ -3054,6 +3057,159 @@ gdaex_fill_liststore_from_datamodel (GdaEx *gdaex, gdaex_fill_liststore_from_datamodel_with_missing_func (gdaex, lstore, dm, cols_formatted, cols_format_func, NULL, NULL); } +const gchar +*gdaex_get_sql_from_hashtable (GdaEx *gdaex, + GdaExSqlType sqltype, + const gchar *table_name, + GHashTable *keys, + GHashTable *fields) +{ + gchar *ret; + gchar *_table_name; + + GError *error; + GdaSqlBuilder *b; + GdaStatement *stmt; + + GdaExPrivate *priv; + + GHashTableIter ht_iter_fields; + GHashTableIter ht_iter_keys; + + gpointer key_fields; + gpointer value_fields; + gpointer key_keys; + gpointer value_keys; + + GdaSqlBuilderId id_field; + GdaSqlBuilderId id_value; + GdaSqlBuilderId id_cond; + + g_return_val_if_fail (IS_GDAEX (gdaex), ""); + g_return_val_if_fail (table_name != NULL, ""); + + _table_name = g_strstrip (g_strdup (table_name)); + g_return_val_if_fail (g_strcmp0 (table_name, "") != 0, ""); + + priv = GDAEX_GET_PRIVATE (gdaex); + + b = NULL; + + ret = ""; + + switch (sqltype) + { + case GDAEX_SQL_SELECT: + b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT); + + gda_sql_builder_select_add_target_id (b, + gda_sql_builder_add_id (b, _table_name), + NULL); + + if (fields == NULL || g_hash_table_size (fields) < 0) + { + gda_sql_builder_select_add_field (b, "*", NULL, NULL); + } + else + { + g_hash_table_iter_init (&ht_iter_fields, fields); + while (g_hash_table_iter_next (&ht_iter_fields, &key_fields, &value_fields)) + { + gda_sql_builder_select_add_field (b, (const gchar *)key_fields, NULL, NULL); + } + } + break; + + case GDAEX_SQL_INSERT: + b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT); + gda_sql_builder_set_table (b, _table_name); + + if (fields != NULL && g_hash_table_size (fields) > 0) + { + g_hash_table_iter_init (&ht_iter_fields, fields); + while (g_hash_table_iter_next (&ht_iter_fields, &key_fields, &value_fields)) + { + gda_sql_builder_add_field_value_as_gvalue (b, (const gchar *)key_fields, (const GValue *)value_fields); + } + } + break; + + case GDAEX_SQL_UPDATE: + b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE); + gda_sql_builder_set_table (b, _table_name); + + if (fields == NULL || g_hash_table_size (fields) < 0) + { + g_warning (_("HashTable fields cannot be empty.")); + } + else + { + g_hash_table_iter_init (&ht_iter_fields, fields); + while (g_hash_table_iter_next (&ht_iter_fields, &key_fields, &value_fields)) + { + gda_sql_builder_add_field_value_as_gvalue (b, (const gchar *)key_fields, (const GValue *)value_fields); + } + + g_hash_table_iter_init (&ht_iter_keys, keys); + while (g_hash_table_iter_next (&ht_iter_keys, &key_keys, &value_keys)) + { + id_field = gda_sql_builder_add_id (b, (const gchar *)key_keys); + id_value = gda_sql_builder_add_expr_value (b, NULL, (const GValue *)value_keys); + id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_value, 0); + gda_sql_builder_set_where (b, id_cond); + } + } + break; + + case GDAEX_SQL_DELETE: + b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE); + gda_sql_builder_set_table (b, _table_name); + + if (keys != NULL && g_hash_table_size (fields) > 0) + { + g_hash_table_iter_init (&ht_iter_keys, keys); + while (g_hash_table_iter_next (&ht_iter_keys, &key_keys, &value_keys)) + { + id_field = gda_sql_builder_add_id (b, (const gchar *)key_keys); + id_value = gda_sql_builder_add_expr_value (b, NULL, (const GValue *)value_keys); + id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_value, 0); + gda_sql_builder_set_where (b, id_cond); + } + } + break; + } + + error = NULL; + stmt = gda_sql_builder_get_statement (b, &error); + if (error != NULL || stmt == NULL) + { + g_warning (_("Unable to get a GdaStatement from GdaSqlBuilder: %s"), + error->message != NULL ? error->message : _("no details")); + } + + error = NULL; + ret = gda_statement_to_sql_extended (stmt, + priv->gda_conn, + NULL, + 0, + NULL, + &error); + if (error != NULL) + { + g_warning (_("Unable to get an SQL statement from GdaStatement: %s"), + error->message != NULL ? error->message : _("no details")); + } + + if (b != NULL) + { + g_object_unref (b); + } + + g_free (_table_name); + + return ret; +} + gchar *gdaex_format_money (gdouble number, gint decimals, diff --git a/src/gdaex.h b/src/gdaex.h index d2bd54b..8cae732 100644 --- a/src/gdaex.h +++ b/src/gdaex.h @@ -228,6 +228,20 @@ void gdaex_fill_liststore_from_datamodel (GdaEx *gdaex, guint *cols_formatted, gchar *(*cols_format_func) (GdaDataModelIter *, guint)); +typedef enum +{ + GDAEX_SQL_SELECT, + GDAEX_SQL_INSERT, + GDAEX_SQL_UPDATE, + GDAEX_SQL_DELETE +} GdaExSqlType; + +const gchar *gdaex_get_sql_from_hashtable (GdaEx *gdaex, + GdaExSqlType sqltype, + const gchar *table_name, + GHashTable *keys, + GHashTable *fields); + gchar *gdaex_format_money (gdouble number, gint decimals, gboolean with_currency_symbol); diff --git a/tests/Makefile.am b/tests/Makefile.am index 90578fa..720f726 100644 --- a/tests/Makefile.am +++ b/tests/Makefile.am @@ -6,6 +6,7 @@ AM_CPPFLAGS = $(GDAEX_CFLAGS) \ -DTESTSDIR="\"@abs_builddir@\"" noinst_PROGRAMS = fill_liststore \ + getsql \ grid \ query_editor \ select \ diff --git a/tests/getsql.c b/tests/getsql.c new file mode 100644 index 0000000..02d397d --- /dev/null +++ b/tests/getsql.c @@ -0,0 +1,152 @@ +/* + * Copyright (C) 2013 Andrea Zagli + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. + */ + +#include +#include + +int +main (int argc, char **argv) +{ + GdaEx *gdaex; + + GHashTable *ht_keys; + GHashTable *ht_fields; + + GValue *gval; + GDate *gdate; + + gtk_init (&argc, &argv); + + gdaex = gdaex_new_from_string (g_strdup_printf ("SQLite://DB_DIR=%s;DB_NAME=grid.db", TESTSDIR)); + if (gdaex == NULL) + { + g_error ("Unable to connect to the db."); + } + + ht_keys = g_hash_table_new (g_str_hash, g_str_equal); + ht_fields = g_hash_table_new (g_str_hash, g_str_equal); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "id"); + g_hash_table_insert (ht_fields, "id", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "name"); + g_hash_table_insert (ht_fields, "name", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "surname"); + g_hash_table_insert (ht_fields, "surname", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "birthday"); + g_hash_table_insert (ht_fields, "birthday", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "address"); + g_hash_table_insert (ht_fields, "address", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "incoming"); + g_hash_table_insert (ht_fields, "incoming", gval); + + g_message ("%s", gdaex_get_sql_from_hashtable (gdaex, GDAEX_SQL_SELECT, "clients", ht_keys, ht_fields)); + g_hash_table_destroy (ht_keys); + g_hash_table_destroy (ht_fields); + + g_message ("%s", gdaex_get_sql_from_hashtable (gdaex, GDAEX_SQL_INSERT, "clients", ht_keys, ht_fields)); + + ht_keys = g_hash_table_new (g_str_hash, g_str_equal); + ht_fields = g_hash_table_new (g_str_hash, g_str_equal); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_INT); + g_value_set_int (gval, 44); + g_hash_table_insert (ht_fields, "id", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "my name is my name"); + g_hash_table_insert (ht_fields, "name", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "and this is the sur'name with '"); + g_hash_table_insert (ht_fields, "surname", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_DATE); + gdate = g_date_new_dmy (12, 5, 1966); + g_value_set_boxed (gval, gdate); + g_hash_table_insert (ht_fields, "birthday", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "5th avenue, 44"); + g_hash_table_insert (ht_fields, "address", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_DOUBLE); + g_value_set_double (gval, 1451.22); + g_hash_table_insert (ht_fields, "incoming", gval); + + g_message ("%s", gdaex_get_sql_from_hashtable (gdaex, GDAEX_SQL_INSERT, "clients", ht_keys, ht_fields)); + g_hash_table_destroy (ht_keys); + g_hash_table_destroy (ht_fields); + + ht_keys = g_hash_table_new (g_str_hash, g_str_equal); + ht_fields = g_hash_table_new (g_str_hash, g_str_equal); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "my new name"); + g_hash_table_insert (ht_fields, "name", gval); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_STRING); + g_value_set_string (gval, "and this is the new sur'name with '"); + g_hash_table_insert (ht_fields, "surname", gval); + + ht_keys = g_hash_table_new (g_str_hash, g_str_equal); + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_INT); + g_value_set_int (gval, 55); + g_hash_table_insert (ht_keys, "id", gval); + + g_message ("%s", gdaex_get_sql_from_hashtable (gdaex, GDAEX_SQL_UPDATE, "clients", ht_keys, ht_fields)); + g_hash_table_destroy (ht_keys); + g_hash_table_destroy (ht_fields); + + ht_keys = g_hash_table_new (g_str_hash, g_str_equal); + + gval = g_new0 (GValue, 1); + g_value_init (gval, G_TYPE_INT); + g_value_set_int (gval, 55); + g_hash_table_insert (ht_keys, "id", gval); + + g_message ("%s", gdaex_get_sql_from_hashtable (gdaex, GDAEX_SQL_DELETE, "clients", ht_keys, ht_fields)); + g_hash_table_destroy (ht_keys); + + return 0; +} -- 2.49.0