introduce test cases to this project

[jack-kwok80]

introduce test cases to this project

Test results:
FAIL test/jointQuery.test.ts (18.901 s)
● Console

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the total order count group by color of cars"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the total order count group by color of cars and sex of customer"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the count of cars that are green"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

● Joint Query › show me the total order count group by color of cars

expect(received).toEqual(expected) // deep equality

- Expected  - 3
+ Received  + 3

- SELECT cars.color, SUM(order.count) as total_order_count FROM cars
+ SELECT color, COUNT(order_id) AS total_order_count FROM cars 
- JOIN order ON cars.car_id = order.car_id
+ JOIN order ON cars.id = order.car_id
-  GROUP BY cars.color;
+ GROUP BY color;

  15 |         const case1: TestCase = { inputText, sql};
  16 |         const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 17 |         expect(await result).toEqual(case1.sql);
     |                              ^
  18 |     });
  19 |     it("show me the total order count group by color of cars and sex of customer",async () => {
  20 |         const inputText = "show me the total order count group by color of cars and sex of customer";

  at test/jointQuery.test.ts:17:30
  at step (test/jointQuery.test.ts:33:23)
  at Object.next (test/jointQuery.test.ts:14:53)
  at fulfilled (test/jointQuery.test.ts:5:58)

● Joint Query › show me the total order count group by color of cars and sex of customer

expect(received).toEqual(expected) // deep equality

- Expected  - 1
+ Received  + 5

- SELECT COUNT(*) FROM cars;
+ SELECT color, sex, COUNT(order_id) AS total_order_count 
+ FROM cars 
+ INNER JOIN order ON cars.id = order.car_id 
+ INNER JOIN customer ON order.cus_id = customer.cus_id 
+ GROUP BY color, sex;

  22 |         const case1: TestCase = { inputText, sql};
  23 |         const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 24 |         expect(await result).toEqual(case1.sql);
     |                              ^
  25 |     });
  26 |     it("show me the count of cars that are green",async () => {
  27 |         const inputText = "show me the count of cars that are green";

  at test/jointQuery.test.ts:24:30
  at step (test/jointQuery.test.ts:33:23)
  at Object.next (test/jointQuery.test.ts:14:53)
  at fulfilled (test/jointQuery.test.ts:5:58)

● Joint Query › show me the count of cars that are green

expect(received).toEqual(expected) // deep equality

- Expected  - 5
+ Received  + 1

- SELECT cars.color, customer.sex, SUM(order.count) as total_order_count
- FROM cars
- JOIN order ON cars.car_id = order.car_id
- JOIN customer ON order.cus_id = customer.cus_id
- GROUP BY cars.color, customer.sex;
+ SELECT COUNT(*) FROM cars WHERE color = 'Green';

  29 |         const case1: TestCase = { inputText, sql};
  30 |         const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 31 |         expect(await result).toEqual(case1.sql);
     |                              ^
  32 |     });
  33 | });

  at test/jointQuery.test.ts:31:30
  at step (test/jointQuery.test.ts:33:23)
  at Object.next (test/jointQuery.test.ts:14:53)
  at fulfilled (test/jointQuery.test.ts:5:58)

FAIL test/singleQuery.test.ts (19.299 s)
● Console

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me all the cars that are red"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the count of cars"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the count of cars that are green"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the customer that age older than 20 years"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

console.log
  Translate this natural language query into SQL without changing the case of the entries given by me:
  
  "show me the total order count that date between '2023-03-01' AND '2023-03-31'"
  
  Use this table schema:
  
  CREATE TABLE cars (id INT, make TEXT, model TEXT, year INT, color TEXT);CREATE TABLE customer (cus_id INT, name TEXT, sex INT, age INT);CREATE TABLE order (order_id INT, car_id INT, cus_id INT, number INT, price DOUBLE, date DATE);
  
  SQL Query:

  at src/translateToSQL.ts:6:11

● Single Query › show me the total order count that date between '2023-03-01' AND '2023-03-31'

expect(received).toEqual(expected) // deep equality

Expected: "SELECT sum(count) FROM order WHERE date BETWEEN '2023-03-01' AND '2023-03-31';"
Received: "SELECT COUNT(order_id) FROM order WHERE date BETWEEN '2023-03-01' AND '2023-03-31';"

  43 |         const case1: TestCase = { inputText, sql};
  44 |         const result = translateToSQL(case1.inputText, process.env.OPENAI_API_KEY, tableSchema);
> 45 |         expect(await result).toEqual(case1.sql);
     |                              ^
  46 |     });
  47 | });

  at test/singleQuery.test.ts:45:30
  at step (test/singleQuery.test.ts:33:23)
  at Object.next (test/singleQuery.test.ts:14:53)
  at fulfilled (test/singleQuery.test.ts:5:58)

Test Suites: 2 failed, 2 total
Tests: 4 failed, 4 passed, 8 total
Snapshots: 0 total
Time: 20.693 s
Ran all test suites.