Simple In-Memory SQL Database in Go

This challenge asks you to implement a simplified, in-memory SQL database in Go. The database will support basic CREATE TABLE, INSERT, SELECT, and potentially UPDATE operations on a single table. This exercise is valuable for understanding database fundamentals, data structures, and Go's capabilities for building custom data management systems.

Problem Description

You are to build a rudimentary SQL database in Go that operates entirely in memory. The database should support the following operations:

1. CREATE TABLE: Allows the user to define a table with a specified name and column definitions. Each column should have a name and a data type (currently only support TEXT and INTEGER).
2. INSERT: Allows the user to insert rows into a table. The values provided must match the data types defined in the table schema.
3. SELECT: Allows the user to retrieve data from a table. The SELECT statement should support selecting all columns (SELECT *) and specifying individual columns. It should also support a simple WHERE clause using equality comparisons (=) on any column.
4. UPDATE: Allows the user to update values in a table. The UPDATE statement should support updating specific columns with new values, and should include a WHERE clause to specify which rows to update.

The database should store table schemas and data in memory. Error handling is crucial; the program should gracefully handle invalid SQL syntax, unsupported data types, and attempts to access non-existent tables or columns.

Key Requirements:

• Implement a Database struct to manage tables.
• Implement a Table struct to represent a table with its schema and data.
• Parse SQL commands (CREATE TABLE, INSERT, SELECT, UPDATE) and execute them accordingly.
• Support TEXT and INTEGER data types.
• Implement a basic query engine for SELECT and UPDATE statements with WHERE clauses.
• Provide clear error messages for invalid SQL syntax or operations.

Expected Behavior:

The program should accept SQL commands as input (e.g., from standard input or a file) and execute them. The output should be the results of SELECT queries, or confirmation messages for CREATE TABLE, INSERT, and UPDATE operations. Error messages should be informative and indicate the nature of the problem.

Edge Cases to Consider:

• Empty tables.
• Tables with no columns.
• Invalid data types in INSERT statements.
• SELECT queries with no WHERE clause.
• UPDATE queries with no WHERE clause (handle carefully - potentially disallow or warn).
• SQL syntax errors (e.g., missing semicolons, incorrect keywords).
• Case sensitivity (consider making keywords case-insensitive).
• Duplicate column names within a table.

Examples

Example 1:

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

Output:

id: 1, name: Alice
id: 2, name: Bob

Explanation: The table users is created. Two rows are inserted. The SELECT * query retrieves all rows and columns.

Example 2:

Input:
CREATE TABLE products (product_id INTEGER, product_name TEXT, price INTEGER);
INSERT INTO products (product_id, product_name, price) VALUES (101, 'Laptop', 1200);
INSERT INTO products (product_id, product_name, price) VALUES (102, 'Mouse', 25);
SELECT product_name FROM products WHERE price = 25;

Output:

Mouse

Explanation: A products table is created. Two products are inserted. The SELECT query retrieves the product_name where the price is 25.

Example 3:

Input:
CREATE TABLE employees (employee_id INTEGER, employee_name TEXT, salary INTEGER);
INSERT INTO employees (employee_id, employee_name, salary) VALUES (1, 'Charlie', 50000);
INSERT INTO employees (employee_id, employee_name, salary) VALUES (2, 'David', 60000);
UPDATE employees SET salary = 55000 WHERE employee_id = 1;
SELECT * FROM employees;

Output:

id: 1, name: Charlie, salary: 55000
id: 2, name: David, salary: 60000

Explanation: An employees table is created. Two employees are inserted. The UPDATE statement changes Charlie's salary to 55000. The SELECT * query retrieves all rows and columns.

Constraints

• The database is in-memory only; no persistence is required.
• Only TEXT and INTEGER data types are supported.
• The WHERE clause supports only equality comparisons (=).
• The SQL parser should handle basic syntax errors.
• The maximum number of rows per table is 100.
• The maximum number of columns per table is 10.
• The maximum length of a TEXT field is 255 characters.

Notes

• Start with the CREATE TABLE and INSERT operations, then move on to SELECT and UPDATE.
• Consider using a simple string parsing approach for the SQL commands. Regular expressions might be helpful.
• Focus on correctness and clarity over performance.
• Error handling is a critical aspect of this challenge. Provide informative error messages to the user.
• You don't need to implement full SQL compliance; a simplified subset is sufficient.
• Think about how to represent the table schema and data efficiently in memory. A slice of structs might be a good starting point.