MODULE SqlObxDB;
(**
	project	= "BlackBox"
	organization	= "www.oberon.ch"
	contributors	= "Oberon microsystems"
	version	= "System/Rsrc/About"
	copyright	= "System/Rsrc/About"
	license	= "Docu/BB-License"
	changes	= "
	- YYYYMMDD, nn, ...
	"
	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.
