// SQLite Transactions Demo
// Demonstrates atomic operations and performance benefits of transactions.

uses System.Data;

var db := DataBase.Create('SQLite', [':memory:']);
db.Exec('CREATE TABLE Inventory (Item TEXT, Qty INTEGER)');

PrintLn('<h3>Transaction Management</h3>');

// 1. Successful Transaction
PrintLn('Executing Transaction A...');
db.BeginTransaction;
try
   db.Exec('INSERT INTO Inventory VALUES (?, ?)', ['Apples', 50]);
   db.Exec('INSERT INTO Inventory VALUES (?, ?)', ['Bananas', 30]);
   db.Commit;
   PrintLn('Transaction A committed.');
except
   on E: Exception do begin
      db.Rollback;
      PrintLn('Transaction A failed: ' + E.Message);
   end;
end;

// 2. Failed Transaction (Rollback)
PrintLn('<br>Executing Transaction B (will fail)...');
db.BeginTransaction;
try
   db.Exec('INSERT INTO Inventory VALUES (?, ?)', ['Oranges', 100]);
   
   // Force an error (Table "NonExistent" doesn't exist)
   db.Exec('INSERT INTO NonExistent VALUES (1)');
   
   db.Commit;
   PrintLn('Transaction B committed.');
except
   on E: Exception do begin
      db.Rollback;
      PrintLn('Transaction B rolled back due to error.');
   end;
end;

// Verify state
PrintLn('<br><h4>Final Inventory:</h4>');
var ds := db.Query('SELECT Item, Qty FROM Inventory');
while ds.Step do begin
   PrintLn(ds.AsString(0) + ': ' + IntToStr(ds.AsInteger(1)));
end;
PrintLn('<i>Note: Oranges are missing because the transaction was rolled back.</i>');