Go-SQLite: A Simple In-Memory SQL Database in Go

This challenge focuses on implementing a basic, in-memory SQL database system using Go. You will build core functionalities for creating tables, inserting data, querying data, and updating records. This exercise is fundamental for understanding how databases work under the hood and how to interact with them programmatically.

Problem Description

Your task is to build an in-memory SQL database engine in Go. This engine should support a limited subset of SQL commands and operate entirely within the application's memory, meaning no persistent storage is required. The database will manage multiple tables, each with a defined schema.

Key Requirements:

• Table Creation: Implement a CREATE TABLE command that defines table name and its columns, including column names and their data types (support INT and TEXT for now).
• Data Insertion: Implement an INSERT INTO command to add rows to a specified table. The values provided must match the column types.
• Data Selection: Implement a SELECT command. This command should support selecting all columns (*) or specific columns from a table. It should also support a WHERE clause for filtering rows based on simple equality conditions (e.g., column_name = value).
• Data Updates: Implement an UPDATE command. This command should modify existing rows in a table based on a WHERE clause.
• Data Deletion: Implement a DELETE FROM command. This command should remove rows from a table based on a WHERE clause.
• Error Handling: Gracefully handle invalid SQL syntax, type mismatches, non-existent tables/columns, and other potential errors. Return descriptive error messages.
• In-Memory Storage: All data and schema definitions should reside in memory and be lost when the program terminates.

Expected Behavior:

The system should accept SQL-like string commands, parse them, and execute the corresponding operations on the in-memory data structures. The SELECT command should return a structured result (e.g., a slice of maps or a custom struct) representing the queried data.

Edge Cases to Consider:

• Empty table operations (inserting into, selecting from, updating, deleting from an empty table).
• WHERE clauses with no matching rows.
• Attempting to insert data of the wrong type.
• Inserting duplicate primary keys (if you choose to implement a simple primary key concept, though not strictly required for this challenge).
• Ambiguous column names in SELECT or WHERE clauses (assume unique column names per table for simplicity initially).

Examples

Example 1: Table Creation and Insertion

Input:

CREATE TABLE users (id INT, name TEXT);
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');

Output: (No direct output for CREATE/INSERT, but the internal state of the database should reflect the created table and inserted rows.)

Explanation: The CREATE TABLE command establishes a new table named users with two columns: id of type INT and name of type TEXT. Two rows are then inserted into this table.

Example 2: Simple Select

Input:

CREATE TABLE users (id INT, name TEXT);
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');
SELECT id, name FROM users;

Output:

[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"}
]

Explanation: After creating and populating the users table, the SELECT command retrieves the id and name columns for all rows. The output is a JSON array of objects, where each object represents a row.

Example 3: Select with WHERE Clause

Input:

CREATE TABLE products (id INT, name TEXT, price INT);
INSERT INTO products (id, name, price) VALUES (101, 'Laptop', 1200);
INSERT INTO products (id, name, price) VALUES (102, 'Mouse', 25);
INSERT INTO products (id, name, price) VALUES (103, 'Keyboard', 75);
SELECT name, price FROM products WHERE price > 50;

Output:

[
  {"name": "Laptop", "price": 1200},
  {"name": "Keyboard", "price": 75}
]

Explanation: The SELECT statement retrieves the name and price for products where the price is greater than 50. The WHERE clause filters the results to include only the Laptop and Keyboard.

Example 4: Update and Delete

Input:

CREATE TABLE employees (id INT, name TEXT, salary INT);
INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000);
UPDATE employees SET salary = 55000 WHERE name = 'Alice';
DELETE FROM employees WHERE id = 2;
SELECT * FROM employees;

Output:

[
  {"id": 1, "name": "Alice", "salary": 55000}
]

Explanation: Alice's salary is updated. Bob's record is then deleted. The final SELECT * shows only Alice's updated record.

Constraints

• Data types supported: INT (32-bit signed integer) and TEXT (string).
• SQL dialect: A simplified subset of SQL. You do not need to implement complex joins, subqueries, or advanced data types.
• WHERE clause: Support only equality (=) and inequality (>, <, >=, <=) operators for INT types, and equality (=) for TEXT types.
• INSERT INTO syntax: Assume column names are explicitly provided.
• Performance: For this challenge, focus on correctness. Extreme performance optimizations are not required, but the solution should be reasonably efficient for in-memory operations.
• Concurrency: Assume a single-threaded environment; no concurrent access to the database is expected.

Notes

• You'll need to design appropriate Go data structures to represent tables, columns, and rows.
• Consider how you will parse the SQL commands. You might want to use regular expressions for simple parsing, or explore more robust parsing libraries if you feel ambitious (though not required).
• For the SELECT output, a slice of map[string]interface{} is a flexible choice.
• Think about how to represent the database itself – perhaps a map[string]Table where the key is the table name.
• Error handling is crucial. Make sure to return meaningful errors for invalid operations.