Implementing Atomic Transactions with BEGIN and COMMIT

Databases are central to many applications, and ensuring data integrity is paramount. Transactions are a fundamental mechanism for achieving this by grouping multiple database operations into a single, indivisible unit of work. This challenge focuses on implementing a basic transaction mechanism using the BEGIN and COMMIT SQL commands.

Problem Description

Your task is to simulate a simplified SQL database environment that supports transactional operations. You will be given a sequence of commands that include table manipulation (CREATE TABLE, INSERT, UPDATE, DELETE), and transaction control (BEGIN, COMMIT, ROLLBACK - though ROLLBACK is not explicitly tested in this challenge, its absence implies a commitment to COMMIT).

The core requirement is to correctly manage the state of the database tables across these operations. When a BEGIN command is encountered, a new transaction scope is established. All subsequent operations until a COMMIT command are part of this transaction. If a COMMIT command is issued, all changes made within the current transaction are permanently applied to the database. If a transaction is not explicitly committed (e.g., the input sequence ends before a COMMIT), its changes should be discarded.

You need to implement a system that can:

1. Execute DDL (Data Definition Language) commands like CREATE TABLE.
2. Execute DML (Data Manipulation Language) commands like INSERT, UPDATE, and DELETE.
3. Manage transactional states:
   • Start a new transaction with BEGIN.
   • Commit the current transaction with COMMIT, making its changes persistent.
   • Effectively discard changes from an uncommitted transaction when the sequence ends.

The system should maintain the state of tables and reflect the changes as they would appear in a real SQL database after transactions are committed.

Examples

Example 1:

Input:

CREATE TABLE accounts (id INT, balance DECIMAL);
INSERT INTO accounts VALUES (1, 100.00);
BEGIN;
UPDATE accounts SET balance = 50.00 WHERE id = 1;
INSERT INTO accounts VALUES (2, 200.00);
COMMIT;
SELECT * FROM accounts;

Output:

[(1, 50.00), (2, 200.00)]

Explanation: The CREATE TABLE statement initializes the accounts table. The first INSERT adds a row. Then, BEGIN starts a transaction. Inside the transaction, the balance for account 1 is updated, and account 2 is inserted. COMMIT makes these changes permanent. The final SELECT shows the committed state.

Example 2:

Input:

CREATE TABLE products (id INT, name VARCHAR, price DECIMAL);
INSERT INTO products VALUES (101, 'Laptop', 1200.00);
BEGIN;
UPDATE products SET price = 1150.00 WHERE id = 101;
INSERT INTO products VALUES (102, 'Mouse', 25.00);
-- No COMMIT here before the end of input
SELECT * FROM products;

Output:

[(101, 'Laptop', 1200.00)]

Explanation: The table is created and an initial product is inserted. A transaction begins. An update and another insert occur within this transaction. However, since there is no COMMIT command before the input sequence ends, the changes made within the transaction (the updated price and the new mouse entry) are discarded. The SELECT statement reflects the state of the table before the uncommitted transaction started.

Example 3: (Multiple transactions)

Input:

CREATE TABLE inventory (item_id INT, quantity INT);
INSERT INTO inventory VALUES (1, 10);
BEGIN;
UPDATE inventory SET quantity = 8 WHERE item_id = 1;
COMMIT;
INSERT INTO inventory VALUES (2, 5);
BEGIN;
UPDATE inventory SET quantity = 7 WHERE item_id = 1;
INSERT INTO inventory VALUES (3, 3);
COMMIT;
SELECT * FROM inventory ORDER BY item_id;

Output:

[(1, 8), (2, 5), (3, 3)]

Explanation: The first transaction updates item 1's quantity and is committed. The second transaction inserts item 2, then updates item 1 again (to quantity 7 from the previously committed 8), and inserts item 3. This second transaction is also committed, leading to the final state.

Constraints

• The input will consist of a sequence of SQL-like commands.
• Commands will be limited to: CREATE TABLE table_name (column_definitions), INSERT INTO table_name VALUES (value1, value2, ...) , UPDATE table_name SET column = value WHERE condition, DELETE FROM table_name WHERE condition, BEGIN;, COMMIT;, SELECT * FROM table_name [ORDER BY column];.
• column_definitions will consist of column_name DATA_TYPE. Supported data types are INT, VARCHAR, DECIMAL.
• condition in UPDATE and DELETE will be a simple equality check on column = value or column_name = value.
• value can be an integer literal, a string literal enclosed in single quotes (e.g., 'Laptop'), or a decimal literal.
• SELECT statements will only be SELECT * FROM table_name or SELECT * FROM table_name ORDER BY column_name.
• The number of tables will be at most 5.
• The number of rows per table will be at most 1000.
• The number of commands in the input sequence will be at most 1000.
• The system does not need to handle complex JOINs or subqueries for SELECT statements.
• The system must correctly handle the transactional scope. Uncommitted changes at the end of the input sequence must be discarded.

Notes

• Think about how to represent the "current" state of the database versus the "tentative" state within a transaction.
• You'll need a mechanism to store table schemas and data.
• Consider how to manage multiple active transactions if the problem were extended, but for this challenge, assume a single outstanding transaction at any given time.
• The ORDER BY clause in SELECT is for ensuring consistent output order in tests, not a core transactional logic requirement.