[libgda] Updated SqlBuilder example

[Vivien Malerba <vivien src gnome org>]

commit 943bb9eb78853939ca347b101debc66fcb13f42e
Author: Vivien Malerba <malerba gnome-db org>
Date:   Tue Oct 6 20:15:53 2009 +0200

    Updated SqlBuilder example

 samples/SqlBuilder/Makefile      |    8 +-
 samples/SqlBuilder/README        |   25 +++++-
 samples/SqlBuilder/example.c     |   33 +++++++-
 samples/SqlBuilder/example_cnc.c |  188 ++++++++++++++++++++++++++++++++++++++
 4 files changed, 250 insertions(+), 4 deletions(-)
---
diff --git a/samples/SqlBuilder/Makefile b/samples/SqlBuilder/Makefile
index ac54d74..fb261ba 100644
--- a/samples/SqlBuilder/Makefile
+++ b/samples/SqlBuilder/Makefile
@@ -1,12 +1,16 @@
 CFLAGS = -Wall -g -DGDA_DISABLE_DEPRECATED `pkg-config --cflags libgda-4.0`
 LDFLAGS = `pkg-config --libs libgda-4.0`
 
-all: example
+all: example example_cnc
 
 example: example.c
 	$(CC) -o example example.c $(CFLAGS) $(LDFLAGS)
 
+example_cnc: example_cnc.c
+	$(CC) -o example_cnc example_cnc.c $(CFLAGS) $(LDFLAGS)
+
 clean:
 	rm -f *~
 	rm -f *.o
-	rm -f example
+	rm -f example example_cnc
+	rm -f test.db
diff --git a/samples/SqlBuilder/README b/samples/SqlBuilder/README
index d7236dc..7e332c2 100644
--- a/samples/SqlBuilder/README
+++ b/samples/SqlBuilder/README
@@ -4,7 +4,9 @@ Libgda simple example
 Description:
 ------------
 
-The example in this directory illustrate how to use a GdaSqlBuilder to build statement from their structure
+The example in this directory illustrate how to use a GdaSqlBuilder to build statement from their structure.
+The "example_cnc" program differs from the "example" in that SQL rendering is done for an opened
+connection which has the GDA_CONNECTION_OPTIONS_SQL_IDENTIFIERS_CASE_SENSITIVE flag set.
 
 Compiling and running:
 ----------------------
@@ -14,6 +16,8 @@ To compile (make sure Libgda is installed prior to this):
 
 and to run:
 > ./example
+or
+> ./example_cnc
 
 Output:
 -------
@@ -22,3 +26,22 @@ Running should produce the following output:
 SQL: INSERT INTO customers (e, f, g) VALUES (##p1::string, 15, 'joe')
 SQL: UPDATE products SET ref='A0E''FESP' WHERE id = 14
 SQL: UPDATE products SET ref='A0E''FESP' WHERE id = ##theid::int
+SQL: DELETE FROM items WHERE id = ##theid::int
+SQL: SELECT c."date", name AS person FROM "select" AS c INNER JOIN orders 
+SQL: SELECT c."date", name AS person FROM "select" AS c INNER JOIN orders USING (id) 
+SQL: SELECT myfunc (a, 5, 'Joe') FROM mytable
+SQL: SELECT myfunc (a, 5, 'Joe'), MAX (myfunc (a, 5, 'Joe'), b, 10) FROM mytable
+SQL: UPDATE "select" SET "date"='2009-05-27' WHERE id = 14
+SQL: SELECT date (a, 5, 'Joe') FROM "date", MyTable
+
+and for example_cnc:
+SQL: INSERT INTO "customers" ("e", "f", "g") VALUES (##p1::string, 15, 'joe')
+SQL: UPDATE "products" SET "ref"='A0E''FESP' WHERE "id" = 14
+SQL: UPDATE "products" SET "ref"='A0E''FESP' WHERE "id" = ##theid::int
+SQL: DELETE FROM "items" WHERE "id" = ##theid::int
+SQL: SELECT "c"."date", "name" AS person FROM "select" AS c INNER JOIN "orders" 
+SQL: SELECT "c"."date", "name" AS person FROM "select" AS c INNER JOIN "orders" USING ("id") 
+SQL: SELECT myfunc ("a", 5, 'Joe') FROM "mytable"
+SQL: SELECT myfunc ("a", 5, 'Joe'), MAX (myfunc ("a", 5, 'Joe'), "b", 10) FROM "mytable"
+SQL: UPDATE "select" SET "date"='2009-05-27' WHERE "id" = 14
+SQL: SELECT date ("a", 5, 'Joe') FROM "date", "MyTable"
\ No newline at end of file
diff --git a/samples/SqlBuilder/example.c b/samples/SqlBuilder/example.c
index 6814684..b2425d2 100644
--- a/samples/SqlBuilder/example.c
+++ b/samples/SqlBuilder/example.c
@@ -114,8 +114,39 @@ main (int argc, char *argv[])
 	gda_sql_builder_add_field (b, 5, 0);
 
 	render_as_sql (b);
-	g_object_unref (b);	
+	g_object_unref (b);
+
+	/* testing identifiers which are SQL reserved keywords */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
+
+	gda_sql_builder_set_table (b, "select");
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "date"),
+				   gda_sql_builder_add_expr (b, 10, NULL, G_TYPE_STRING, "2009-05-27"));
+	gda_sql_builder_add_id (b, 1, "id");
+	gda_sql_builder_add_expr (b, 2, NULL, G_TYPE_INT, 14);
+	gda_sql_builder_add_cond (b, 3, GDA_SQL_OPERATOR_TYPE_EQ, 1, 2, 0);
+	gda_sql_builder_set_where (b, 3);
+
+	render_as_sql (b);
+	g_object_unref (b);
 
+	/* testing identifiers which are SQL reserved keywords */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_select_add_target (b, 1,
+					   gda_sql_builder_add_id (b, 0, "date"),
+					   NULL);
+	gda_sql_builder_select_add_target (b, 2,
+					   gda_sql_builder_add_id (b, 0, "MyTable"),
+					   NULL);
+	gda_sql_builder_add_function (b, 1, "date",
+				      gda_sql_builder_add_id (b, 0, "a"),
+				      gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 5),
+				      gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "Joe"),
+				      0);
+	gda_sql_builder_add_field (b, 1, 0);
+	render_as_sql (b);
+	
 	return 0;
 }
 
diff --git a/samples/SqlBuilder/example_cnc.c b/samples/SqlBuilder/example_cnc.c
new file mode 100644
index 0000000..696f141
--- /dev/null
+++ b/samples/SqlBuilder/example_cnc.c
@@ -0,0 +1,188 @@
+#include <libgda/libgda.h>
+
+void render_as_sql (GdaSqlBuilder *b);
+
+int
+main (int argc, char *argv[])
+{
+	gda_init ();
+
+	GdaSqlBuilder *b;
+
+	/* INSERT INTO customers (e, f, g) VALUES (##p1::string, 15, 'joe') */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
+
+	gda_sql_builder_set_table (b, "customers");
+
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "e"),
+				   gda_sql_builder_add_param (b, 0, "p1", G_TYPE_STRING, FALSE));
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "f"),
+				   gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 15));
+	gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0, "g"),
+				   gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "joe"));
+	
+	render_as_sql (b);
+	g_object_unref (b);
+
+
+	/* UPDATE products set ref='A0E''FESP' WHERE id = 14 */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
+
+	gda_sql_builder_set_table (b, "products");
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "ref"),
+				   gda_sql_builder_add_expr (b, 10, NULL, G_TYPE_STRING, "A0E'FESP"));
+	gda_sql_builder_add_id (b, 1, "id");
+	gda_sql_builder_add_expr (b, 2, NULL, G_TYPE_INT, 14);
+	gda_sql_builder_add_cond (b, 3, GDA_SQL_OPERATOR_TYPE_EQ, 1, 2, 0);
+	gda_sql_builder_set_where (b, 3);
+
+	render_as_sql (b);
+
+	/* reuse the same GdaSqlBuilder object to change the WHERE condition to: WHERE id = ##theid::int */
+	gda_sql_builder_set_where (b,
+				   gda_sql_builder_add_cond (b, 0, GDA_SQL_OPERATOR_TYPE_EQ,
+							 1,
+							 gda_sql_builder_add_param (b, 0, "theid", G_TYPE_INT, FALSE),
+							 0));
+	render_as_sql (b);
+	g_object_unref (b);
+
+	/* DELETE FROM items WHERE id = ##theid::int */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
+
+	gda_sql_builder_set_table (b, "items");
+	gda_sql_builder_add_id (b, 1, "id");
+	gda_sql_builder_add_param (b, 2, "theid", G_TYPE_INT, FALSE);
+	gda_sql_builder_add_cond (b, 3, GDA_SQL_OPERATOR_TYPE_EQ, 1, 2, 0);
+	gda_sql_builder_set_where (b, 3);
+
+	render_as_sql (b);
+	g_object_unref (b);
+	
+	/*
+	 * The next statement shows automatic quoting of reserved SQL keywords (DATE and SELECT here)
+	 *
+	 * SELECT c."date", name, date AS person FROM "select" as c, orders
+	 */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	
+	gda_sql_builder_add_id (b, 1, "select"); /* SELECT is an SQL reserved keyword */
+	gda_sql_builder_select_add_target (b, 1, 1, "c");
+	gda_sql_builder_select_add_target (b, 2,
+					   gda_sql_builder_add_id (b, 0, "orders"),
+					   NULL);
+	gda_sql_builder_select_join_targets (b, 5, 1, 2, GDA_SQL_SELECT_JOIN_INNER, 0);
+
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "c.date"), 0); /* DATE is an SQL reserved keyword */
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "name"),
+				   gda_sql_builder_add_id (b, 0, "person"));
+
+	render_as_sql (b);
+
+	/* reuse the same GdaSqlBuilder object to change the INNER join's condition */
+	gda_sql_builder_join_add_field (b, 5, "id");
+
+	render_as_sql (b);
+	g_object_unref (b);
+
+	/* SELECT myfunc (a, 5, 'Joe') FROM mytable */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_select_add_target (b, 0,
+					   gda_sql_builder_add_id (b, 0, "mytable"),
+					   NULL);
+	gda_sql_builder_add_function (b, 1, "myfunc",
+				      gda_sql_builder_add_id (b, 0, "a"),
+				      gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 5),
+				      gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "Joe"),
+				      0);
+	gda_sql_builder_add_field (b, 1, 0);
+	render_as_sql (b);
+
+	/* reuse the same GdaSqlBuilder object to have:
+	 * SELECT myfunc (a, 5, 'Joe'), MAX (myfunc (a, 5, 'Joe'), b, 10) FROM mytable */
+	guint args[] = {1, 3, 4};
+	gda_sql_builder_add_id (b, 3, "b");
+	gda_sql_builder_add_expr (b, 4, NULL, G_TYPE_INT, 10);
+
+	gda_sql_builder_add_function_v (b, 5, "MAX", args, 3);
+	gda_sql_builder_add_field (b, 5, 0);
+
+	render_as_sql (b);
+	g_object_unref (b);
+
+	/* testing identifiers which are SQL reserved keywords */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
+
+	gda_sql_builder_set_table (b, "select");
+	gda_sql_builder_add_field (b,
+				   gda_sql_builder_add_id (b, 0, "date"),
+				   gda_sql_builder_add_expr (b, 10, NULL, G_TYPE_STRING, "2009-05-27"));
+	gda_sql_builder_add_id (b, 1, "id");
+	gda_sql_builder_add_expr (b, 2, NULL, G_TYPE_INT, 14);
+	gda_sql_builder_add_cond (b, 3, GDA_SQL_OPERATOR_TYPE_EQ, 1, 2, 0);
+	gda_sql_builder_set_where (b, 3);
+
+	render_as_sql (b);
+	g_object_unref (b);
+
+	/* testing identifiers which are SQL reserved keywords */
+	b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+	gda_sql_builder_select_add_target (b, 1,
+					   gda_sql_builder_add_id (b, 0, "date"),
+					   NULL);
+	gda_sql_builder_select_add_target (b, 2,
+					   gda_sql_builder_add_id (b, 0, "MyTable"),
+					   NULL);
+	
+	gda_sql_builder_add_function (b, 1, "date",
+				      gda_sql_builder_add_id (b, 0, "a"),
+				      gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_INT, 5),
+				      gda_sql_builder_add_expr (b, 0, NULL, G_TYPE_STRING, "Joe"),
+				      0);
+	gda_sql_builder_add_field (b, 1, 0);
+	render_as_sql (b);
+	
+	return 0;
+}
+
+void
+render_as_sql (GdaSqlBuilder *b)
+{
+	GdaStatement *stmt;
+	GError *error = NULL;
+	static GdaConnection *cnc = NULL;
+	
+	if (!cnc) {
+		cnc = gda_connection_open_from_string ("SQLite", "DB_DIR=.;DB_NAME=test", NULL,
+						       GDA_CONNECTION_OPTIONS_SQL_IDENTIFIERS_CASE_SENSITIVE, NULL);
+		g_assert (cnc);
+	}
+
+	stmt = gda_sql_builder_get_statement (b, &error);
+	if (!stmt) {
+		g_print ("Statement error: %s\n",
+			 error && error->message ? error->message : "No detail");
+		if (error)
+			g_error_free (error);
+	}
+	else {
+		gchar *sql;
+		sql = gda_statement_to_sql_extended (stmt, cnc, NULL, GDA_STATEMENT_SQL_PARAMS_SHORT, NULL, &error);
+		if (!sql) {
+			g_print ("SQL rendering error: %s\n",
+				 error && error->message ? error->message : "No detail");
+			if (error)
+				g_error_free (error);
+		}
+		else {
+			g_print ("SQL: %s\n", sql);
+			g_free (sql);
+		}		
+		g_object_unref (stmt);
+	}
+}