使用Ada在Postgresql中创建插入和更新查询

Question

我已经让SELECT查询在Postgresql (运行在Slackware 14.2上的10.10版本)的设置上工作，但是在试图找出插入和更新时，我被困住了。阅读文档后，我尝试构建查询，但失败的原因可能是我缺乏理解力。

我所寻找的只是最简单的例子，允许我使用Gnatcoll。

假设我有一个表"xyzzy"，其中有两列:整数类型的"id“(自动递增和主键)和文本类型的”术语“。

我的第一个插句可能是：

INSERT INTO xyzzy (term) VALUES ('foo');

在此之后，更新如下：

UPDATE xyzzy SET term = 'bar' WHERE id = 1;

Postgres文档中的示例为SELECT提供了足够详细的内容；我只是很难理解插入和更新。

任何感激的帮助。

更新:我已经做了一些进一步的工作，现在可以使用以下方法进行基本的插入：GNATCOLL.SQL.Exec.Execute (My_DB, "INSERT INTO xyzzy (term) VALUES ('bar');");

下一步是找出如何使用SQL_Query类型正确地完成它。

Answer 1

给定一个名为PostgreSQL的现有mydb2数据库，该数据库的表创建了

persons.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE persons (
    person_uid   uuid DEFAULT uuid_generate_v4 () PRIMARY KEY,
    first_name   VARCHAR (20) NOT NULL,
    last_name    VARCHAR (20) NOT NULL
);

GRANT SELECT ON persons TO deedee;
GRANT INSERT ON persons TO deedee;
GRANT UPDATE ON persons TO deedee;
GRANT DELETE ON persons TO deedee;

然后在生成所需的Ada数据类型之后，通过

$ gnatcoll_postgres2ada -dbmodel dschema.txt

使用

dschema.txt

| TABLE             | persons |          || The contents of person |
| person_uid        | TEXT    | PK       || Auto-generated id      |
| first_name        | TEXT    | NOT NULL || First name             |
| last_name         | TEXT    | NOT NULL || Last name              |

下面的代码说明了如何使用SQL_Insert、SQL_Update和SQL_Delete。请注意，为了简单起见，我省略了错误检查(例如，请参见函数GNATCOLL.SQL.Exec.Success)。

main.adb

with Ada.Text_IO;            use Ada.Text_IO;
with GNATCOLL.Traces;        use GNATCOLL.Traces;
with GNATCOLL.SQL.Postgres;  use GNATCOLL.SQL.Postgres;
with GNATCOLL.SQL.Exec;      use GNATCOLL.SQL.Exec;
with GNATCOLL.VFS;           use GNATCOLL.VFS;
with GNATCOLL.SQL.Inspect;   use GNATCOLL.SQL.Inspect;
with GNATCOLL.SQL;           use GNATCOLL.SQL;
with Database;

procedure Main is
   
   DB_Descr : GNATCOLL.SQL.Exec.Database_Description;
   --  Datebase description.
   
   DB : GNATCOLL.SQL.Exec.Database_Connection;
   -- Database connection.
   
   procedure Dump_Table;
   --  Dumps the content of the table "persons" to standard output.
   
   ----------------
   -- Dump_Table --
   ----------------
   
   procedure Dump_Table is
      
      S : constant SQL_Query :=
        SQL_Select (Fields => Database.Persons.First_Name &  --  0
                              Database.Persons.Last_Name,    --  1
                    From   => Database.Persons);
      
      R : Forward_Cursor;
      
   begin

      Put_Line ("---------- TABLE ----------");
      
      --  Perform the actual query, show results if OK.
      R.Fetch (DB, S);
      if Success (DB) then
         while Has_Row (R) loop
            Put_Line (Value (R, 0) & " " & Value (R, 1));
            Next (R);
         end loop;

      else
         Put_Line ("FAILED");         
      end if;
      New_Line;
      
   end Dump_Table;      

begin
  
   -- Database description.
   DB_Descr := GNATCOLL.SQL.Postgres.Setup
     (Database => "mydb2",
      User     => "deedee",
      Host     => "localhost",
      Password => "xxxx");

   -- Database connection.
   DB := DB_Descr.Build_Connection;
   
   --  Insert two records.   
   declare
      I1 : constant SQL_Query :=
        SQL_Insert (Values => (Database.Persons.First_Name = "John") &
                              (Database.Persons.Last_Name  = "Doe"));
   
      I2 : constant SQL_Query :=
        SQL_Insert (Values => (Database.Persons.First_Name = "Jane") &
                              (Database.Persons.Last_Name  = "Doe"));
   begin
      Execute (Connection => DB, Query => I1);
      Execute (Connection => DB, Query => I2);
      Dump_Table;
   end;
   
   --  Update one of the records.
   declare
      U : constant SQL_Query := 
        SQL_Update (Table => Database.Persons,
                    Set   => (Database.Persons.First_Name = "Johnny"),
                    Where => (Database.Persons.First_Name = "John") and
                             (Database.Persons.Last_Name  = "Doe" ));
   begin      
      Execute (Connection => DB, Query => U);
      Dump_Table;
   end;
   
   --  Delete one of the records.
   declare
      D : constant SQL_Query := 
        SQL_Delete (From  => Database.Persons,
                    Where => (Database.Persons.First_Name = "Jane") and
                             (Database.Persons.Last_Name  = "Doe" ));
   begin      
      Execute (Connection => DB, Query => D);
      Dump_Table;
   end;  

   --  Commit changes to the database.
   Commit (DB);
   
   Free (DB);  --  for all connections you have opened
   Free (DB_Descr);

end Main;

输出

$ ./obj/main
---------- TABLE ----------
John Doe
Jane Doe

---------- TABLE ----------
Jane Doe
Johnny Doe

---------- TABLE ----------
Johnny Doe