GNATCOLL.SQL JOIN's

gistfile1.adb

with Ada.Text_IO;
with Database;
with GNATCOLL.SQL;
with GNATCOLL.SQL.Exec;
with GNATCOLL.SQL.Postgres;
with GNATCOLL.Traces;

procedure DBtest is

   use Ada.Text_IO;
   use Database;
   use GNATCOLL.SQL;
   use GNATCOLL.Traces;

   Cursor           : Exec.Forward_Cursor;
   DB_Description   : Exec.Database_Description := 
                        Postgres.Setup (Database => "testingdb",
                                        User     => "postgres",
                                        Host     => "host",
                                        Password => "secret");
   DB_Connection    : Exec.Database_Connection :=
                        Exec.Get_Task_Connection (DB_Description);
   
   Tags             : constant SQL_Left_Join_Table := 
                        Join (Tag,
                              Contactentity_Tag,
                              Contactentity_Tag.Tag_Id = Tag.Tag_Id);

   Contacts         : constant SQL_Left_Join_Table :=
                        Left_Join (Contactentity,
                                   Tags,
                                   Contactentity.Ce_Id = 
                                     Contactentity_Tag.Ce_Id);

   Org_Contacts     : constant SQL_Left_Join_Table := 
                        Join (Contactentity_Organization,
                              Contacts,
                              Contactentity_Organization.Ce_Id =
                                Contactentity.Ce_Id);

   Org_And_Contacts : constant SQL_Left_Join_Table :=
                        Left_Join (Organization,
                                   Org_Contacts,
                                   Organization.Org_Id = 
                                     Contactentity_Organization.Org_Id);
   
   Query            : constant SQL_Query := 
                        SQL_Select (Fields =>
                                      Organization.Org_Id &
                                      Organization.Org_Name &
                                      Organization.Sip_Uri &
                                      Contactentity.Ce_Id &
                                      Contactentity.Ce_Name &
                                      Contactentity.Isperson &
                                      Tag.Tag_Id &
                                      Tag.Tag_Name,
                                    From   => Org_And_Contacts,
                                    Where  => Organization.Org_Id = 1);
   
   P                : constant Exec.Prepared_Statement := 
                        Exec.Prepare (Query, On_Server => True);
begin
   Parse_Config_File (".gnatdebug");
   --  Activate all GNATCOLL.SQL related log traces.
   
   Cursor.Fetch (DB_Connection, P);

   if DB_Connection.Success then
      while Exec.Has_Row (Cursor) loop
         New_Line;
         Put_Line (Cursor.Field_Name (0) & ": " & Cursor.Value (0));
         Put_Line (Cursor.Field_Name (1) & ": " & Cursor.Value (1));
         Put_Line (Cursor.Field_Name (2) & ": " & Cursor.Value (2));
         Put_Line (Cursor.Field_Name (3) & ": " & Cursor.Value (3));
         Put_Line (Cursor.Field_Name (4) & ": " & Cursor.Value (4));
         Put_Line (Cursor.Field_Name (5) & ": " & Cursor.Value (5));
         Put_Line (Cursor.Field_Name (6) & ": " & Cursor.Value (6));
         Put_Line (Cursor.Field_Name (7) & ": " & Cursor.Value (7));
         Cursor.Next;
      end loop;
   else
      Put_Line (DB_Connection.Error);
   end if;

   --  Free connection and description.
   Exec.Free (DB_Connection);
   Exec.Free (DB_Description);

end DBtest;