From 9e383d0015c2e5b917973aa851e881d1ff0c31af Mon Sep 17 00:00:00 2001 From: Andrea Zagli Date: Sat, 5 Mar 2011 13:08:22 +0100 Subject: [PATCH] Implemented where part (to finish for some types). --- src/queryeditor.c | 245 +++++++++++++++++++++++++++++++++++++++++-- src/queryeditor.h | 10 ++ tests/query_editor.c | 8 +- 3 files changed, 255 insertions(+), 8 deletions(-) diff --git a/src/queryeditor.c b/src/queryeditor.c index 2281536..a00b525 100644 --- a/src/queryeditor.c +++ b/src/queryeditor.c @@ -158,6 +158,8 @@ struct _GdaExQueryEditorPrivate GtkWidget *hbox; GtkWidget *not; GtkWidget *cb_where_type; + GtkWidget *txt1; + GtkWidget *txt2; GtkWidget *opt_asc; GtkWidget *opt_desc; }; @@ -493,6 +495,12 @@ const gchar if (gtk_tree_model_get_iter_first (GTK_TREE_MODEL (priv->lstore_show), &iter)) { + guint id_target1; + guint id_target2; + guint id_join1; + guint id_join2; + guint join_cond; + do { gtk_tree_model_get (GTK_TREE_MODEL (priv->lstore_show), &iter, @@ -505,16 +513,15 @@ const gchar if (field->decode_table2 != NULL) { - /* TODO alias for table2 must change based on tables count */ - guint id_target1 = gda_sql_builder_select_add_target_id (sqlbuilder, + id_target1 = gda_sql_builder_select_add_target_id (sqlbuilder, gda_sql_builder_add_id (sqlbuilder, table->name), NULL); - guint id_target2 = gda_sql_builder_select_add_target_id (sqlbuilder, + id_target2 = gda_sql_builder_select_add_target_id (sqlbuilder, gda_sql_builder_add_id (sqlbuilder, field->decode_table2), - "t2"); - guint id_join1 = gda_sql_builder_add_id (sqlbuilder, g_strconcat (field->table_name, ".", field->name, NULL)); - guint id_join2 = gda_sql_builder_add_id (sqlbuilder, g_strconcat ("t2.", field->decode_field2, NULL)); - guint join_cond = gda_sql_builder_add_cond (sqlbuilder, GDA_SQL_OPERATOR_TYPE_EQ, + NULL); + id_join1 = gda_sql_builder_add_id (sqlbuilder, g_strconcat (field->table_name, ".", field->name, NULL)); + id_join2 = gda_sql_builder_add_id (sqlbuilder, g_strconcat (field->decode_table2, ".", field->decode_field2, NULL)); + join_cond = gda_sql_builder_add_cond (sqlbuilder, GDA_SQL_OPERATOR_TYPE_EQ, id_join1, id_join2, 0); gda_sql_builder_select_join_targets (sqlbuilder, id_target1, id_target2, GDA_SQL_SELECT_JOIN_INNER, join_cond); @@ -530,6 +537,145 @@ const gchar } while (gtk_tree_model_iter_next (GTK_TREE_MODEL (priv->lstore_show), &iter)); } + if (gtk_tree_model_get_iter_first (GTK_TREE_MODEL (priv->tstore_where), &iter)) + { + gboolean not; + guint where_type; + GdaSqlOperatorType op; + GType type; + gchar *from_str; + gchar *to_str; + GDate *from_date; + GDateTime *from_datetime; + + guint id_field; + guint id_value1; + guint id_value2; + guint id_cond; + guint id_cond_iter; + + id_cond = 0; + do + { + id_value2 = 0; + + gtk_tree_model_get (GTK_TREE_MODEL (priv->tstore_where), &iter, + COL_WHERE_TABLE_NAME, &table_name, + COL_WHERE_NAME, &field_name, + COL_WHERE_CONDITION_NOT, ¬, + COL_WHERE_CONDITION_TYPE, &where_type, + COL_WHERE_CONDITION_FROM, &from_str, + COL_WHERE_CONDITION_TO, &to_str, + -1); + + if (to_str == NULL || g_strcmp0 (g_strstrip (to_str), "") == 0) + { + to_str = NULL; + } + + table = g_hash_table_lookup (priv->tables, table_name); + field = g_hash_table_lookup (table->fields, field_name); + + id_field = gda_sql_builder_add_id (sqlbuilder, g_strconcat (table->name, ".", field->name, NULL)); + + switch (field->type) + { + case GDAEX_QE_FIELD_TYPE_TEXT: + type = G_TYPE_STRING; + id_value1 = gda_sql_builder_add_expr (sqlbuilder, NULL, type, from_str); + if (to_str != NULL) + { + id_value2 = gda_sql_builder_add_expr (sqlbuilder, NULL, type, to_str); + } + break; + + case GDAEX_QE_FIELD_TYPE_INTEGER: + type = G_TYPE_INT; + id_value1 = gda_sql_builder_add_expr (sqlbuilder, NULL, type, strtol (from_str, NULL, 10)); + if (to_str != NULL) + { + id_value2 = gda_sql_builder_add_expr (sqlbuilder, NULL, type, strtol (to_str, NULL, 10)); + } + break; + + case GDAEX_QE_FIELD_TYPE_DOUBLE: + type = G_TYPE_DOUBLE; + id_value1 = gda_sql_builder_add_expr (sqlbuilder, NULL, type, g_strtod (from_str, NULL)); + if (to_str != NULL) + { + id_value2 = gda_sql_builder_add_expr (sqlbuilder, NULL, type, g_strtod (to_str, NULL)); + } + break; + + case GDAEX_QE_FIELD_TYPE_DATE: + type = G_TYPE_DATE; + /* TODO */ + break; + + case GDAEX_QE_FIELD_TYPE_DATETIME: + type = G_TYPE_DATE_TIME; + /* TODO */ + break; + }; + + switch (where_type) + { + case GDAEX_QE_WHERE_TYPE_EQUAL: + op = GDA_SQL_OPERATOR_TYPE_EQ; + break; + + case GDAEX_QE_WHERE_TYPE_LIKE: + /* TODO */ + op = GDA_SQL_OPERATOR_TYPE_LIKE; + break; + + case GDAEX_QE_WHERE_TYPE_ILIKE: + /* TODO */ + op = GDA_SQL_OPERATOR_TYPE_LIKE; + break; + + case GDAEX_QE_WHERE_TYPE_GREAT: + op = GDA_SQL_OPERATOR_TYPE_GT; + break; + + case GDAEX_QE_WHERE_TYPE_GREAT_EQUAL: + op = GDA_SQL_OPERATOR_TYPE_GEQ; + break; + + case GDAEX_QE_WHERE_TYPE_LESS: + op = GDA_SQL_OPERATOR_TYPE_LT; + break; + + case GDAEX_QE_WHERE_TYPE_LESS_EQUAL: + op = GDA_SQL_OPERATOR_TYPE_LEQ; + break; + + case GDAEX_QE_WHERE_TYPE_BETWEEN: + op = GDA_SQL_OPERATOR_TYPE_BETWEEN; + break; + } + + id_cond_iter = gda_sql_builder_add_cond (sqlbuilder, op, id_field, id_value1, id_value2); + if (not) + { + id_cond_iter = gda_sql_builder_add_cond (sqlbuilder, GDA_SQL_OPERATOR_TYPE_NOT, id_cond_iter, 0, 0); + } + if (id_cond == 0) + { + id_cond = id_cond_iter; + } + else + { + id_cond = gda_sql_builder_add_cond (sqlbuilder, GDA_SQL_OPERATOR_TYPE_AND, id_cond, id_cond_iter, 0); + } + } while (gtk_tree_model_iter_next (GTK_TREE_MODEL (priv->tstore_where), &iter)); + + if (id_cond != 0) + { + gda_sql_builder_set_where (sqlbuilder, id_cond); + } + } + if (gtk_tree_model_get_iter_first (GTK_TREE_MODEL (priv->lstore_order), &iter)) { do @@ -1006,10 +1152,13 @@ gdaex_query_editor_on_btn_save_clicked (GtkButton *button, gtk_tree_model_get (model, &iter_val, 0, &where_type, -1); + gtk_tree_store_set (priv->tstore_where, &iter, COL_WHERE_CONDITION_NOT, gtk_toggle_button_get_active (GTK_TOGGLE_BUTTON (priv->not)), COL_WHERE_CONDITION_TYPE, where_type, COL_WHERE_CONDITION_TYPE_VISIBLE, gdaex_query_editor_get_where_type_str_from_type (where_type), + COL_WHERE_CONDITION_FROM, gtk_entry_get_text (GTK_ENTRY (priv->txt1)), + COL_WHERE_CONDITION_TO, priv->txt2 != NULL ? gtk_entry_get_text (GTK_ENTRY (priv->txt2)) : "", -1); } } @@ -1261,6 +1410,8 @@ gdaex_query_editor_on_sel_where_changed (GtkTreeSelection *treeselection, gchar *field_name; gboolean not; guint where_type; + gchar *from; + gchar *to; GdaExQueryEditorTable *table; GdaExQueryEditorField *field; @@ -1285,6 +1436,8 @@ gdaex_query_editor_on_sel_where_changed (GtkTreeSelection *treeselection, COL_WHERE_NAME, &field_name, COL_WHERE_CONDITION_NOT, ¬, COL_WHERE_CONDITION_TYPE, &where_type, + COL_WHERE_CONDITION_FROM, &from, + COL_WHERE_CONDITION_TO, &to, -1); table = g_hash_table_lookup (priv->tables, table_name); @@ -1393,6 +1546,84 @@ gdaex_query_editor_on_sel_where_changed (GtkTreeSelection *treeselection, } gtk_table_attach (GTK_TABLE (tbl), priv->cb_where_type, 2, 3, 1, 2, 0, 0, 0, 0); + priv->txt2 = NULL; + switch (field->type) + { + /* TODO for now are all equals */ + case GDAEX_QE_FIELD_TYPE_TEXT: + priv->txt1 = gtk_entry_new (); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), from); + gtk_table_attach (GTK_TABLE (tbl), priv->txt1, 3, 4, 1, 2, 0, 0, 0, 0); + if (where_type == GDAEX_QE_WHERE_TYPE_BETWEEN) + { + lbl = gtk_label_new ("and"); + gtk_table_attach (GTK_TABLE (tbl), lbl, 4, 5, 1, 2, 0, 0, 0, 0); + priv->txt2 = gtk_entry_new (); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), to); + gtk_table_attach (GTK_TABLE (tbl), priv->txt2, 5, 6, 1, 2, 0, 0, 0, 0); + } + break; + case GDAEX_QE_FIELD_TYPE_INTEGER: + priv->txt1 = gtk_entry_new (); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), from); + gtk_table_attach (GTK_TABLE (tbl), priv->txt1, 3, 4, 1, 2, 0, 0, 0, 0); + if (where_type == GDAEX_QE_WHERE_TYPE_BETWEEN) + { + lbl = gtk_label_new ("and"); + gtk_table_attach (GTK_TABLE (tbl), lbl, 4, 5, 1, 2, 0, 0, 0, 0); + priv->txt2 = gtk_entry_new (); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), to); + gtk_table_attach (GTK_TABLE (tbl), priv->txt2, 5, 6, 1, 2, 0, 0, 0, 0); + } + break; + case GDAEX_QE_FIELD_TYPE_DOUBLE: + priv->txt1 = gtk_entry_new (); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), from); + gtk_table_attach (GTK_TABLE (tbl), priv->txt1, 3, 4, 1, 2, 0, 0, 0, 0); + if (where_type == GDAEX_QE_WHERE_TYPE_BETWEEN) + { + lbl = gtk_label_new ("and"); + gtk_table_attach (GTK_TABLE (tbl), lbl, 4, 5, 1, 2, 0, 0, 0, 0); + priv->txt2 = gtk_entry_new (); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), to); + gtk_table_attach (GTK_TABLE (tbl), priv->txt2, 5, 6, 1, 2, 0, 0, 0, 0); + } + break; + case GDAEX_QE_FIELD_TYPE_DATE: + priv->txt1 = gtk_entry_new (); + gtk_entry_set_max_length (GTK_ENTRY (priv->txt1), 10); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), from); + gtk_table_attach (GTK_TABLE (tbl), priv->txt1, 3, 4, 1, 2, 0, 0, 0, 0); + if (where_type == GDAEX_QE_WHERE_TYPE_BETWEEN) + { + lbl = gtk_label_new ("and"); + gtk_table_attach (GTK_TABLE (tbl), lbl, 4, 5, 1, 2, 0, 0, 0, 0); + priv->txt2 = gtk_entry_new (); + gtk_entry_set_max_length (GTK_ENTRY (priv->txt2), 10); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), to); + gtk_table_attach (GTK_TABLE (tbl), priv->txt2, 5, 6, 1, 2, 0, 0, 0, 0); + } + break; + case GDAEX_QE_FIELD_TYPE_DATETIME: + priv->txt1 = gtk_entry_new (); + gtk_entry_set_max_length (GTK_ENTRY (priv->txt1), 19); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), from); + gtk_table_attach (GTK_TABLE (tbl), priv->txt1, 3, 4, 1, 2, 0, 0, 0, 0); + if (where_type == GDAEX_QE_WHERE_TYPE_BETWEEN) + { + lbl = gtk_label_new ("and"); + gtk_table_attach (GTK_TABLE (tbl), lbl, 4, 5, 1, 2, 0, 0, 0, 0); + priv->txt2 = gtk_entry_new (); + gtk_entry_set_max_length (GTK_ENTRY (priv->txt2), 19); + gtk_entry_set_text (GTK_ENTRY (priv->txt1), to); + gtk_table_attach (GTK_TABLE (tbl), priv->txt2, 5, 6, 1, 2, 0, 0, 0, 0); + } + break; + default: + g_warning ("Field's type «%d» not valid.", field->type); + break; + }; + gtk_box_pack_start (GTK_BOX (priv->vbx_values), priv->hbox, FALSE, FALSE, 0); gtk_widget_show_all (priv->vbx_values_container); diff --git a/src/queryeditor.h b/src/queryeditor.h index 22764c6..74eeb4c 100644 --- a/src/queryeditor.h +++ b/src/queryeditor.h @@ -58,6 +58,15 @@ GdaExQueryEditor *gdaex_query_editor_new (GdaEx *gdaex); GtkWidget *gdaex_query_editor_get_dialog (GdaExQueryEditor *gdaex_query_editor); GtkWidget *gdaex_query_editor_get_widget (GdaExQueryEditor *gdaex_query_editor); +typedef enum + { + GDAEX_QE_FIELD_TYPE_TEXT, + GDAEX_QE_FIELD_TYPE_INTEGER, + GDAEX_QE_FIELD_TYPE_DOUBLE, + GDAEX_QE_FIELD_TYPE_DATE, + GDAEX_QE_FIELD_TYPE_DATETIME + } GdaExQueryEditorFieldType; + typedef enum { GDAEX_QE_WHERE_TYPE_EQUAL = 1, @@ -79,6 +88,7 @@ typedef struct gchar *description; gchar *alias; /* - tipo di campo (string, integer, double, date, datetime) */ + GdaExQueryEditorFieldType type; /* - sceglibile per la parte show (es. le chiavi esterne non ha senno che lo siano) */ gboolean for_show; /* - sempre presente nelle query, quindi non sceglibile per la parte show */ diff --git a/tests/query_editor.c b/tests/query_editor.c index 01f9976..0a3dd0e 100644 --- a/tests/query_editor.c +++ b/tests/query_editor.c @@ -58,6 +58,7 @@ main (int argc, char *argv[]) field = g_new0 (GdaExQueryEditorField, 1); field->name = g_strdup ("id"); field->name_visible = g_strdup ("ID"); + field->type = GDAEX_QE_FIELD_TYPE_INTEGER; field->for_show = TRUE; field->always_showed = TRUE; field->for_where = TRUE; @@ -69,6 +70,7 @@ main (int argc, char *argv[]) field->name = g_strdup ("name"); field->name_visible = g_strdup ("Name"); field->description = g_strdup ("The client's name"); + field->type = GDAEX_QE_FIELD_TYPE_TEXT; field->for_show = TRUE; field->for_where = TRUE; field->available_where_type = GDAEX_QE_WHERE_TYPE_ILIKE @@ -80,6 +82,7 @@ main (int argc, char *argv[]) field->name = g_strdup ("surname"); field->name_visible = g_strdup ("Surname"); field->description = g_strdup ("The client's surname"); + field->type = GDAEX_QE_FIELD_TYPE_TEXT; field->for_show = TRUE; field->for_where = TRUE; field->available_where_type = GDAEX_QE_WHERE_TYPE_LIKE @@ -91,9 +94,11 @@ main (int argc, char *argv[]) field->name = g_strdup ("age"); field->name_visible = g_strdup ("Age"); field->description = g_strdup ("The client's age"); + field->type = GDAEX_QE_FIELD_TYPE_INTEGER; field->for_show = TRUE; field->for_where = TRUE; - field->available_where_type = GDAEX_QE_WHERE_TYPE_GREAT + field->available_where_type = GDAEX_QE_WHERE_TYPE_EQUAL + | GDAEX_QE_WHERE_TYPE_GREAT | GDAEX_QE_WHERE_TYPE_GREAT_EQUAL | GDAEX_QE_WHERE_TYPE_LESS | GDAEX_QE_WHERE_TYPE_LESS_EQUAL @@ -105,6 +110,7 @@ main (int argc, char *argv[]) field->name = g_strdup ("id_cities"); field->name_visible = g_strdup ("City"); field->description = g_strdup ("The client's city"); + field->type = GDAEX_QE_FIELD_TYPE_INTEGER; field->for_show = TRUE; field->for_where = TRUE; field->decode_table2 = g_strdup ("cities"); -- 2.49.0