MODULE SqlObxDB;
(**

   project   = "BlackBox"
   organization   = "www.oberon.ch"
   contributors   = "Oberon microsystems"
   version   = "System/Rsrc/About"
   copyright   = "System/Rsrc/About"
   license   = "Docu/BB-License"
   changes   = ""
   issues   = ""

**)

   IMPORT Dialog, SqlDB;

   CONST

      protocol = "SqlOdbc";
      id = ""; password = "";
      datasource = "Test Database";
   VAR

      company*: RECORD
         id*: INTEGER;
         name*: ARRAY 32 OF CHAR;
         ceo*: ARRAY 32 OF CHAR;
         employees*: INTEGER
      END;
      
      dirty*: BOOLEAN;   (* company is dirty *)
      max*: RECORD

         value*: INTEGER
      END;
      searchId*: INTEGER;

      table*: SqlDB.Table;

      
   PROCEDURE Reset;

   BEGIN
      company.id := 0;
      company.name := "";
      company.ceo := "";
      company.employees := 0;
      dirty := FALSE;
      Dialog.Update(company)
   END Reset;
   
   PROCEDURE Open*;

      VAR d: SqlDB.Database; res: INTEGER;
   BEGIN
      SqlDB.OpenDatabase(protocol, id, password, datasource, SqlDB.async, SqlDB.hideErrors, d, res);
      IF d # NIL THEN
         table := d.NewTable()
      END
   END Open;
   PROCEDURE Closed* (): BOOLEAN;

   BEGIN
      RETURN table = NIL
   END Closed;
   
   PROCEDURE Insert*;

   BEGIN
      table.Exec("SELECT MAX(id) FROM Companies");
      table.Read(0, max);
      company.id := max.value + 1;   (* generate a unique key *)
      table.base.Exec("INSERT INTO Companies VALUES (:SqlObxDB.company)");
      table.base.Commit;
      (* now the contents of c is inconsistent with database *)
      table.Exec("SELECT * FROM Companies WHERE id = :SqlObxDB.company.id");
      table.Read(0, company); dirty := FALSE
   END Insert;
   PROCEDURE Update*;

   BEGIN
      table.base.Exec("DELETE FROM Companies WHERE id = :SqlObxDB.company.id");
      table.base.Exec("INSERT INTO Companies VALUES (:SqlObxDB.company)");
      table.base.Commit;
      (* now the contents of table is inconsistent with database *)
      table.Exec("SELECT * FROM Companies WHERE id = :SqlObxDB.company.id");
      table.Read(0, company); dirty := FALSE
   END Update;
   PROCEDURE Delete*;

   BEGIN
      table.base.Exec("DELETE FROM Companies WHERE id = :SqlObxDB.company.id");
      table.base.Commit;
      (* now the contents of table is inconsistent with database *)
      table.Clear;   (* company has become stale *)
      Reset   (* clear interactor *)
   END Delete;
   PROCEDURE Revert*;

   BEGIN
      IF company.id > 0 THEN
         table.Exec("SELECT * FROM Companies WHERE id = :SqlObxDB.company.id");
         table.Read(0, company); dirty := FALSE
      ELSE
         table.Clear;
         Reset   (* clear interactor *)
      END
   END Revert;
   PROCEDURE Find*;

   BEGIN
      table.Exec("SELECT * FROM Companies WHERE id = :SqlObxDB.searchId");
      table.Read(0, company); dirty := FALSE
   END Find;
   PROCEDURE SetTestData*;


      VAR d: SqlDB.Database;
   BEGIN
      d := table.base;
      d.Exec("DELETE FROM Companies WHERE id > 0");
      d.Exec("DELETE FROM Ownership WHERE owner > 0");
      (* single company *)
      d.Exec("INSERT INTO Companies VALUES (11, 'Test', 'Bill', 234)");
      (* two companies (tree) *)
      d.Exec("INSERT INTO Companies VALUES (12, 'Test', 'Bill', 234)");
      d.Exec("INSERT INTO Companies VALUES (13, 'Test company AG', 'John', 45)");
      d.Exec("INSERT INTO Ownership VALUES (12, 13, 100)");
      (* four companies (with ring) *)
      d.Exec("INSERT INTO Companies VALUES (14, 'Test', 'Bill', 234)");
      d.Exec("INSERT INTO Companies VALUES (15, 'Test company AG', 'John', 45)");
      d.Exec("INSERT INTO Companies VALUES (16, 'Test Services GmbH', 'Jim', 23000)");
      d.Exec("INSERT INTO Companies VALUES (17, 'Test Commands & Co.', 'Mary', 523)");
      d.Exec("INSERT INTO Ownership VALUES (14, 15, 50)");
      d.Exec("INSERT INTO Ownership VALUES (15, 17, 100)");
      d.Exec("INSERT INTO Ownership VALUES (16, 15, 50)");
      d.Exec("INSERT INTO Ownership VALUES (15, 17, 100)");
      (* four companies (tree) *)
      d.Exec("INSERT INTO Companies VALUES (18, 'Test', 'Bill', 234)");
      d.Exec("INSERT INTO Companies VALUES (19, 'Test company AG', 'John', 45)");
      d.Exec("INSERT INTO Companies VALUES (20, 'Test Services GmbH', 'Jim', 23000)");
      d.Exec("INSERT INTO Companies VALUES (21, 'Test Commands & Co.', 'Mary', 523)");
      d.Exec("INSERT INTO Ownership VALUES (18, 19, 20)");
      d.Exec("INSERT INTO Ownership VALUES (18, 20, 30)");
      d.Exec("INSERT INTO Ownership VALUES (18, 21, 50)");
      (* most complex example *)
      d.Exec("INSERT INTO Companies VALUES (1, 'Test', 'Bill', 234)");
      d.Exec("INSERT INTO Companies VALUES (2, 'Test company AG', 'John', 45)");
      d.Exec("INSERT INTO Companies VALUES (3, 'Test Services GmbH', 'Jim', 23000)");
      d.Exec("INSERT INTO Companies VALUES (4, 'Test Commands & Co.', 'Mary', 523)");
      d.Exec("INSERT INTO Companies VALUES (5, 'Test Views KG', 'Frank', 17)");
      d.Exec("INSERT INTO Companies VALUES (6, 'Test Genossenschaft', 'Hans', 2109)");
      d.Exec("INSERT INTO Companies VALUES (7, 'Test Mentoring, Inc.', 'Marlis', 128)");
      d.Exec("INSERT INTO Companies VALUES (8, 'Test Training Plc.', 'Paul', 4)");
      d.Exec("INSERT INTO Companies VALUES (9, 'Test Trainers SA', 'Jean', 87)");
      d.Exec("INSERT INTO Companies VALUES (10, 'Test Wrappers AB', 'Gordon', 912)");
      d.Exec("INSERT INTO Ownership VALUES (1, 2, 100)");
      d.Exec("INSERT INTO Ownership VALUES (1, 3, 100)");
      d.Exec("INSERT INTO Ownership VALUES (2, 4, 100)");
      d.Exec("INSERT INTO Ownership VALUES (2, 5, 100)");
      d.Exec("INSERT INTO Ownership VALUES (3, 6, 100)");
      d.Exec("INSERT INTO Ownership VALUES (3, 7, 100)");
      d.Exec("INSERT INTO Ownership VALUES (7, 8, 100)");
      d.Exec("INSERT INTO Ownership VALUES (5, 9, 49)");
      d.Exec("INSERT INTO Ownership VALUES (8, 9, 51)");
      d.Exec("INSERT INTO Ownership VALUES (9, 10, 100)");
      d.Commit
   END SetTestData;
BEGIN

   searchId := 1
END SqlObxDB.