[gnome-db] Fwd: More SqlBuilder stuff

[Vivien Malerba <vmalerba gmail com>]

---------- Forwarded message ----------
From: Vivien Malerba <vmalerba gmail com>
Date: 2009/10/7
Subject: Re: [gnome-db] More SqlBuilder stuff
To: Johannes Schmid <jhs jsschmid de>


2009/10/7 Johannes Schmid <jhs jsschmid de>:
> Hi Vivien!
>
> I was trying to convert a rather complicated SQL into GdaSqlBuilder
> statements but got stuck a bit:
>
>
> SELECT "albums"."album_id", "albums"."name", "albums"."artist_id",
> "relationship_artist"."name", "albums"."publisher_id",
> "relationship_publisher"."name",
> "albums"."year", "albums"."comments" FROM "albums" LEFT JOIN
> "publishers" AS "relationship_publisher"
> ON ("albums"."publisher_id" =
> "relationship_publisher"."publishers_id")
> LEFT JOIN "artists" AS "relationship_artist" ON ("albums"."artist_id" =
> "relationship_artist"."artist_id")  WHERE "albums"."album_id" = 1 */
>
> I got to the point of the first LEFT JOIN rather quickly but then I have
> no real idea how to continue. This is what I have so far (it's in C++, I
> can convert it to C if that's easier for you):
>
> http://de.pastebin.ca/1600971

here is the C version, sorry I don't have the time to try the C++ version:

       b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);

       /* selected fields */
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"albums.album_id"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"albums.name"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"albums.artist_id"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"relationship_artist.name"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"albums.publisher_id"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"relationship_publisher.name"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"albums.year"), 0);
       gda_sql_builder_add_field (b, gda_sql_builder_add_id (b, 0,
"albums.comments"), 0);

       /* targets */
       gda_sql_builder_select_add_target (b, 1,
                                          gda_sql_builder_add_id (b,
0, "albums"),
                                          NULL);
       gda_sql_builder_select_add_target (b, 2,
                                          gda_sql_builder_add_id (b,
0, "publishers"),
                                          "relationship_publisher");
       gda_sql_builder_add_cond (b, 3,
                                 GDA_SQL_OPERATOR_TYPE_EQ,
                                 gda_sql_builder_add_id (b, 0,
"albums.publisher_id"),
                                 gda_sql_builder_add_id (b, 0,
"relationship_publisher.publishers_id"),
                                 0);
       gda_sql_builder_select_join_targets (b, 0,
                                            1, 2, GDA_SQL_SELECT_JOIN_LEFT, 3);


       gda_sql_builder_select_add_target (b, 4,
                                          gda_sql_builder_add_id (b,
0, "artists"),
                                          "relationship_artists");
       gda_sql_builder_add_cond (b, 5,
                                 GDA_SQL_OPERATOR_TYPE_EQ,
                                 gda_sql_builder_add_id (b, 0,
"albums.artist_id"),
                                 gda_sql_builder_add_id (b, 0,
"relationship_artists.artist_id"),
                                 0);
       gda_sql_builder_select_join_targets (b, 0,
                                            1, 4, GDA_SQL_SELECT_JOIN_LEFT, 5);

       /* where condition */
       gda_sql_builder_add_cond (b, 6,
                                 GDA_SQL_OPERATOR_TYPE_EQ,
                                 gda_sql_builder_add_id (b, 0,
"albums.album_id"),
                                 gda_sql_builder_add_expr (b, 0, NULL,
G_TYPE_INT, 1),
                                 0);
       gda_sql_builder_set_where (b, 6);


Regards,

Vivien