def test_Join_With_Where(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test18.db")
conn.execute(
"CREATE TABLE students (name TEXT, grade INTEGER, class TEXT);")
conn.execute("CREATE TABLE classes (course TEXT, instructor TEXT);")
conn.execute(
"INSERT INTO students VALUES ('Josh', 99, 'CSE480'), ('Dennis', 99, 'CSE480'), ('Jie', 52, 'CSE491');"
)
conn.execute(
"INSERT INTO students VALUES ('Cam', 56, 'CSE480'), ('Zizhen', 56, 'CSE491'), ('Emily', 74, 'CSE431');"
)
conn.execute(
"INSERT INTO classes VALUES ('CSE480', 'Dr. Nahum'), ('CSE491', 'Dr. Josh'), ('CSE431', 'Dr. Ofria');"
)
check(
"SELECT students.name, students.grade, classes.course, classes.instructor FROM students LEFT OUTER JOIN classes ON students.class = classes.course WHERE students.grade > 60 ORDER BY classes.instructor, students.name, students.grade;",
[('Dennis', 99, 'CSE480', 'Dr. Nahum'),
('Josh', 99, 'CSE480', 'Dr. Nahum'),
('Emily', 74, 'CSE431', 'Dr. Ofria')])
def test_Update_Where(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test15.db")
conn.execute(
"CREATE TABLE students (name TEXT, grade INTEGER, notes TEXT);")
conn.execute(
"INSERT INTO students VALUES ('Josh', 562, 'Likes Python'), ('Dennis', 45, 'Likes Networks'), ('Jie', 455, 'Likes Driving');"
)
conn.execute(
"INSERT INTO students VALUES ('Cam', 524, 'Likes Anime'), ('Zizhen', 4532, 'Likes Reading'), ('Emily', 245, 'Likes Environmentalism');"
)
check("SELECT * FROM students ORDER BY name;",
[('Cam', 524, 'Likes Anime'), ('Dennis', 45, 'Likes Networks'),
('Emily', 245, 'Likes Environmentalism'),
('Jie', 455, 'Likes Driving'), ('Josh', 562, 'Likes Python'),
('Zizhen', 4532, 'Likes Reading')])
conn.execute(
"UPDATE students SET notes = 'High Grade' WHERE grade > 100;")
check("SELECT * FROM students ORDER BY name;",
[('Cam', 524, 'High Grade'), ('Dennis', 45, 'Likes Networks'),
('Emily', 245, 'High Grade'), ('Jie', 455, 'High Grade'),
('Josh', 562, 'High Grade'), ('Zizhen', 4532, 'High Grade')])
def test_Distinct(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test16.db")
conn.execute(
"CREATE TABLE students (name TEXT, grade INTEGER, notes TEXT);")
conn.execute(
"INSERT INTO students VALUES ('Josh', 99, 'Likes Python'), ('Dennis', 99, 'Likes Networks'), ('Jie', 52, 'Likes Driving');"
)
conn.execute(
"INSERT INTO students VALUES ('Cam', 56, 'Likes Anime'), ('Zizhen', 56, 'Likes Reading'), ('Emily', 74, 'Likes Environmentalism');"
)
check("SELECT * FROM students ORDER BY name;",
[('Cam', 56, 'Likes Anime'), ('Dennis', 99, 'Likes Networks'),
('Emily', 74, 'Likes Environmentalism'),
('Jie', 52, 'Likes Driving'), ('Josh', 99, 'Likes Python'),
('Zizhen', 56, 'Likes Reading')])
check("SELECT DISTINCT grade FROM students ORDER BY grade;", [(52, ),
(56, ),
(74, ),
(99, )])
check(
"SELECT DISTINCT grade FROM students WHERE name < 'Emily' ORDER BY grade;",
[(56, ), (99, )])
def test_Delete(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test12.db")
conn.execute("CREATE TABLE table (one REAL, two INTEGER, three TEXT);")
conn.execute(
"INSERT INTO table VALUES (3.4, 43, 'happiness'), (5345.6, 42, 'sadness'), (43.24, 25, 'life');"
)
conn.execute(
"INSERT INTO table VALUES (323.4, 433, 'warmth'), (5.6, 42, 'thirst'), (4.4, 235, 'Skyrim');"
)
conn.execute(
"INSERT INTO table VALUES (NULL, NULL, 'other'), (5.6, NULL, 'hunger'), (NULL, 235, 'want');"
)
check("SELECT * FROM table ORDER BY three, two, one;",
[(4.4, 235, 'Skyrim'), (3.4, 43, 'happiness'),
(5.6, None, 'hunger'), (43.24, 25, 'life'),
(None, None, 'other'), (5345.6, 42, 'sadness'),
(5.6, 42, 'thirst'), (None, 235, 'want'),
(323.4, 433, 'warmth')])
conn.execute("DELETE FROM table;")
check("SELECT * FROM table ORDER BY three;", [])
def test_Where_Clause(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test9.db")
conn.execute("CREATE TABLE table (one REAL, two INTEGER, three TEXT);")
conn.execute(
"INSERT INTO table VALUES (3.4, 43, 'happiness'), (5345.6, 42, 'sadness'), (43.24, 25, 'life');"
)
conn.execute(
"INSERT INTO table VALUES (323.4, 433, 'warmth'), (5.6, 42, 'thirst'), (4.4, 235, 'Skyrim');"
)
check("SELECT * FROM table ORDER BY three, two, one;",
[(4.4, 235, 'Skyrim'),
(3.4, 43, 'happiness'), (43.24, 25, 'life'),
(5345.6, 42, 'sadness'), (5.6, 42, 'thirst'),
(323.4, 433, 'warmth')])
check("SELECT * FROM table WHERE two > 50 ORDER BY three, two, one;",
[(4.4, 235, 'Skyrim'), (323.4, 433, 'warmth')])
check("SELECT * FROM table WHERE two = 42 ORDER BY three, two, one;",
[(5345.6, 42, 'sadness'), (5.6, 42, 'thirst')])
check(
"SELECT * FROM table WHERE three IS NOT NULL ORDER BY three, two, one;",
[(4.4, 235, 'Skyrim'), (3.4, 43, 'happiness'), (43.24, 25, 'life'),
(5345.6, 42, 'sadness'), (5.6, 42, 'thirst'),
(323.4, 433, 'warmth')])
def test_Insert_Into_Multiple_Columns(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test8.db")
conn.execute("CREATE TABLE table (one REAL, two INTEGER, three TEXT);")
conn.execute("INSERT INTO table VALUES (3.4, 43, 'happiness');")
conn.execute(
"INSERT INTO table (one, three) VALUES (11.4, 'sadness'), (84.7, 'fear'), (94.7, 'weird');"
)
conn.execute(
"INSERT INTO table (two, three) VALUES (13, 'warmth'), (34, 'coldness');"
)
check("SELECT * FROM table ORDER BY three, two, one;",
[(None, 34, 'coldness'), (84.7, None, 'fear'),
(3.4, 43, 'happiness'), (11.4, None, 'sadness'),
(None, 13, 'warmth'), (94.7, None, 'weird')])
def test_Integration(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test21.db")
conn.execute(
"CREATE TABLE pets (name TEXT, species TEXT, age INTEGER);")
conn.execute(
"CREATE TABLE owners (name TEXT, age INTEGER, id INTEGER);")
conn.execute(
"INSERT INTO pets VALUES ('RaceTrack', 'Ferret', 3), ('Ghost', 'Ferret', 2), ('Zoe', 'Dog', 7), ('Ebony', 'Dog', 17);"
)
conn.execute(
"INSERT INTO pets (species, name) VALUES ('Rat', 'Ginny'), ('Dog', 'Balto'), ('Dog', 'Clifford');"
)
conn.execute("UPDATE pets SET age = 15 WHERE name = 'RaceTrack';")
check(
"SELECT species, *, pets.name FROM pets WHERE age > 3 ORDER BY pets.name;",
[('Dog', 'Ebony', 'Dog', 17, 'Ebony'),
('Ferret', 'RaceTrack', 'Ferret', 15, 'RaceTrack'),
('Dog', 'Zoe', 'Dog', 7, 'Zoe')])
conn.execute(
"INSERT INTO owners VALUES ('Josh', 29, 10), ('Emily', 27, 2), ('Zach', 25, 4), ('Doug', 34, 5);"
)
conn.execute("DELETE FROM owners WHERE name = 'Doug';")
check("SELECT owners.* FROM owners ORDER BY id;", [('Emily', 27, 2),
('Zach', 25, 4),
('Josh', 29, 10)])
conn.execute("CREATE TABLE ownership (name TEXT, id INTEGER);")
conn.execute(
"INSERT INTO ownership VALUES ('RaceTrack', 10), ('Ginny', 2), ('Ghost', 2), ('Zoe', 4);"
)
check(
"SELECT pets.name, pets.age, ownership.id FROM pets LEFT OUTER JOIN ownership ON pets.name = ownership.name WHERE pets.age IS NULL ORDER BY pets.name;",
[('Balto', None, None), ('Clifford', None, None),
('Ginny', None, 2)])
def test_Escaping_Strings(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test2.db")
conn.execute(
"CREATE TABLE table_1 (col_1 INTEGER, _col2 TEXT, col_3_ REAL);")
conn.execute("INSERT INTO table_1 VALUES (33, 'hi', 4.5);")
conn.execute("INSERT INTO table_1 VALUES (36, 'don''t', 7);")
conn.execute("INSERT INTO table_1 VALUES (36, 'hi ''josh''', 7);")
check("SELECT * FROM table_1 ORDER BY _col2, col_1;",
[(36, "don't", 7), (33, 'hi', 4.5), (36, "hi 'josh'", 7)])
def test_Insert_Into_Different_Order(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test6.db")
conn.execute("CREATE TABLE table (one REAL, two INTEGER, three TEXT);")
conn.execute("INSERT INTO table VALUES (3.4, 43, 'happiness');")
conn.execute(
"INSERT INTO table (one, three, two) VALUES (11.4, 'sadness', 84);"
)
check("SELECT * FROM table ORDER BY three, two, one;",
[(3.4, 43, 'happiness'), (11.4, 84, 'sadness')])
def test_Qualified_Names(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test3.db")
conn.execute(
"CREATE TABLE table_1 (col_1 INTEGER, _col2 TEXT, col_3_ REAL);")
conn.execute("INSERT INTO table_1 VALUES (33, 'hi', 4.5);")
conn.execute("INSERT INTO table_1 VALUES (36, 'don''t', 7);")
conn.execute("INSERT INTO table_1 VALUES (36, 'hi ''josh''', 7);")
check(
"SELECT col_1, col_3_, table_1._col2 FROM table_1 ORDER BY table_1._col2, _col2, col_1;",
[(36, 7, "don't"), (33, 4.5, 'hi'), (36, 7, "hi 'josh'")])
def test_Regression_Check(self):
def check(sql_statement, expected):
print("SQL: " + sql_statement)
result = conn.execute(sql_statement)
result_list = list(result)
print("expected:")
pprint(expected)
print("student: ")
pprint(result_list)
assert expected == result_list
conn = sqlite3_extension.connect("test1.db")
conn.execute(
"CREATE TABLE table_1 (col_1 INTEGER, _col2 TEXT, col_3_ REAL);")
conn.execute("INSERT INTO table_1 VALUES (33, 'hi', 4.5);")
conn.execute("CREATE TABLE table2 (col_1 INTEGER, other INTEGER);")
conn.execute("INSERT INTO table2 VALUES (15, 782);")
conn.execute("INSERT INTO table2 VALUES (615, 7582);")
check("SELECT * FROM table_1 ORDER BY _col2, col_1;",
[(33, 'hi', 4.5)])
check("SELECT * FROM table2 ORDER BY other, col_1;", [(15, 782),
(615, 7582)])
conn.execute("INSERT INTO table_1 VALUES (3, 'hi', 4.5);")
conn.execute("INSERT INTO table2 VALUES (165, 7282);")
conn.execute(
"INSERT INTO table_1 VALUES (54, 'string with spaces', 3.0);")
conn.execute(
"INSERT INTO table_1 VALUES (75842, 'string with spaces', 3.0);")
conn.execute(
"INSERT INTO table_1 VALUES (623, 'string with spaces', 3.0);")
check("SELECT * FROM table_1 ORDER BY _col2, col_1;",
[(3, 'hi', 4.5),
(33, 'hi', 4.5), (54, 'string with spaces', 3.0),
(623, 'string with spaces', 3.0),
(75842, 'string with spaces', 3.0)])
check("SELECT * FROM table2 ORDER BY other, col_1;", [(15, 782),
(165, 7282),
(615, 7582)])
