86 lines
3.5 KiB
SQL
86 lines
3.5 KiB
SQL
-- ============================================================================
|
|
-- 🗄️ SQL 14: ACID TRANSAKTIONEN, COMMIT, ROLLBACK & SAVEPOINTS 🗄️
|
|
-- ============================================================================
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 1. TABELLEN-SCHEMA & TESTDATEN
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS konten (
|
|
konto_id INTEGER PRIMARY KEY,
|
|
inhaber TEXT NOT NULL,
|
|
iban TEXT UNIQUE NOT NULL,
|
|
kontostand REAL NOT NULL CHECK (kontostand >= 0)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS transaktions_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
von_konto INTEGER NOT NULL,
|
|
an_konto INTEGER NOT NULL,
|
|
betrag REAL NOT NULL,
|
|
status TEXT NOT NULL,
|
|
zeitstempel TEXT NOT NULL,
|
|
FOREIGN KEY (von_konto) REFERENCES konten(konto_id),
|
|
FOREIGN KEY (an_konto) REFERENCES konten(konto_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS warenkorb_reservierung (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
kunde_id INTEGER NOT NULL,
|
|
artikel_id INTEGER NOT NULL,
|
|
menge INTEGER NOT NULL,
|
|
status TEXT NOT NULL
|
|
);
|
|
|
|
-- Testdaten (Gesamtsumme aller Konten = 1000 + 2500 + 500 = 4000 €)
|
|
INSERT INTO konten (konto_id, inhaber, iban, kontostand) VALUES
|
|
(1, 'Max Mustermann', 'DE89370400440532013000', 1000.00),
|
|
(2, 'Sarah Connor', 'DE89370400440532013001', 2500.00),
|
|
(3, 'Tim Berg', 'DE89370400440532013002', 500.00);
|
|
|
|
|
|
-- ============================================================================
|
|
-- 🎯 AUFGABEN
|
|
-- ============================================================================
|
|
|
|
-- 🎯 TODO 1: Erfolgreiche Banküberweisung mit COMMIT
|
|
-- Führe eine vollständige Transaktion durch:
|
|
-- 1. BEGIN TRANSACTION;
|
|
-- 2. Ziehe 300.00 € von Konto 1 ab.
|
|
-- 3. Schreibe 300.00 € auf Konto 2 gut.
|
|
-- 4. Trage den Transfer in transaktions_log ein:
|
|
-- von_konto: 1, an_konto: 2, betrag: 300.00, status: 'ERFOLGREICH', zeitstempel: '2024-03-01 10:00:00'
|
|
-- 5. COMMIT;
|
|
-- TODO: Transaktion implementieren
|
|
|
|
|
|
-- 🎯 TODO 2: Transaktionsabbruch mit ROLLBACK bei Fehler / Überziehung
|
|
-- Simuliere eine Transaktion, die fehlschlägt und rückgängig gemacht wird:
|
|
-- 1. BEGIN TRANSACTION;
|
|
-- 2. Schreibe testweise 1000.00 € von Konto 3 auf Konto 1 um.
|
|
-- 3. Stelle fest, dass Konto 3 nicht genug Deckung hätte oder simuliere einen Abbruch:
|
|
-- 4. ROLLBACK;
|
|
-- TODO: Transaktionsblock mit ROLLBACK schreiben
|
|
|
|
|
|
-- 🎯 TODO 3: Teil-Rollback mit SAVEPOINT
|
|
-- Führe eine mehrstufige Transaktion mit Zwischenspeicherpunkt aus:
|
|
-- 1. BEGIN TRANSACTION;
|
|
-- 2. Füge eine gültige Reservierung ein:
|
|
-- kunde_id: 1, artikel_id: 101, menge: 2, status: 'RESERVIERT'
|
|
-- 3. Erstelle einen SAVEPOINT namens `sp_zusatz_buchung`;
|
|
-- 4. Füge testweise eine ungültige Reservierung ein:
|
|
-- kunde_id: 1, artikel_id: 999, menge: 100, status: 'UNGÜLTIG'
|
|
-- 5. Setze die fehlerhafte Buchung zurück mit:
|
|
-- ROLLBACK TO SAVEPOINT sp_zusatz_buchung;
|
|
-- 6. Bestätige die Transaktion mit COMMIT;
|
|
-- TODO: SAVEPOINT Transaktion implementieren
|
|
|
|
|
|
-- 🎯 TODO 4: Konsistenz-Audit (Bilanzprüfung)
|
|
-- Schreibe eine SELECT-Abfrage, die:
|
|
-- - die Gesamtsumme des Bankvermögens (SUM(kontostand) AS bilanz_summe)
|
|
-- - die Anzahl der erfolgreichen Transaktionen im Log (COUNT(*) AS erfolgreiche_transfers)
|
|
-- ermittelt.
|
|
SELECT
|
|
(SELECT SUM(kontostand) FROM konten) AS bilanz_summe,
|
|
(SELECT COUNT(*) FROM transaktions_log WHERE status = 'ERFOLGREICH') AS erfolgreiche_transfers;
|