def test_multiple_transactions_dml(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("INSERT INTO students VALUES ('Josh');")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("BEGIN TRANSACTION;")
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("INSERT INTO students VALUES ('Cam');")
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[("Cam", ), ("Josh", )])
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("COMMIT TRANSACTION;")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Cam", ), ("Josh", )])
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[("Cam", ), ("Josh", )])
def test_drop_table_removes_rows(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("INSERT INTO students VALUES ('Josh');")
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("DROP TABLE IF EXISTS students;")
conn_1.execute("CREATE TABLE students (name TEXT);")
self.check(conn_1, "SELECT * FROM students ORDER BY name;", [])
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2.execute("INSERT INTO students VALUES ('Zizhen');")
conn_2.execute("CREATE TABLE IF NOT EXISTS students (name TEXT);")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[('Zizhen', )])
conn_1.execute("INSERT INTO students VALUES ('Cam');")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[('Cam', ), ('Zizhen', )])
with self.assertRaises(Exception):
conn_2.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("DROP TABLE students;")
with self.assertRaises(Exception):
conn_2.execute("DROP TABLE students;")
def test_rollback_undo(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
conn_4 = connect("test.db", timeout=0.1, isolation_level=None)
conn_5 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("INSERT INTO students VALUES ('a');")
self.check(conn_1, "SELECT * FROM students ORDER BY name;", [('a', )])
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("INSERT INTO students VALUES ('b');")
self.check(conn_1, "SELECT * FROM students ORDER BY name;", [('a', ),
('b', )])
conn_1.execute("ROLLBACK TRANSACTION;")
self.check(conn_1, "SELECT * FROM students ORDER BY name;", [('a', )])
conn_2.execute("BEGIN TRANSACTION;")
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("INSERT INTO students VALUES ('c');")
self.check(conn_2, "SELECT * FROM students ORDER BY name;", [('a', )])
conn_2.execute("ROLLBACK TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
self.check(conn_2, "SELECT * FROM students ORDER BY name;", [('a', ),
('c', )])
def test_transaction_mode_more(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
conn_4 = connect("test.db", timeout=0.1, isolation_level=None)
conn_5 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_2.execute("BEGIN EXCLUSIVE TRANSACTION;")
with self.assertRaises(Exception):
conn_1.execute("INSERT INTO students VALUES ('Zizhen');")
conn_3.execute("BEGIN TRANSACTION;")
with self.assertRaises(Exception):
conn_1.execute("INSERT INTO students VALUES ('Zizhen');")
conn_2.execute("INSERT INTO students VALUES ('Zizhen');")
with self.assertRaises(Exception):
conn_4.execute("SELECT * FROM students ORDER BY name;")
conn_2.execute("COMMIT TRANSACTION;")
self.check(conn_5, "SELECT * FROM students ORDER BY name;",
[('Zizhen', )])
conn_5.execute("INSERT INTO students VALUES ('Josh');")
self.check(conn_5, "SELECT * FROM students ORDER BY name;",
[('Josh', ), ('Zizhen', )])
def test_multiple_tables(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
conn_4 = connect("test.db", timeout=0.1, isolation_level=None)
conn_5 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT, id INTEGER);")
conn_2.execute(
"CREATE TABLE grades (grade INTEGER, name TEXT, student_id INTEGER);"
)
conn_3.execute(
"INSERT INTO students (id, name) VALUES (42, 'Josh'), (7, 'Cam');")
conn_2.execute(
"INSERT INTO grades VALUES (99, 'CSE480', 42), (80, 'CSE450', 42), (70, 'CSE480', 9);"
)
conn_2.execute("BEGIN DEFERRED TRANSACTION;")
conn_1.execute("BEGIN IMMEDIATE TRANSACTION;")
conn_1.execute("INSERT INTO grades VALUES (10, 'CSE231', 1);")
self.check(
conn_2,
"SELECT grades.grade, grades.name, students.name FROM grades LEFT OUTER JOIN students ON grades.student_id = students.id ORDER BY grades.name, grades.grade;",
[(80, 'CSE450', 'Josh'), (70, 'CSE480', None),
(99, 'CSE480', 'Josh')])
self.check(
conn_1,
"SELECT grades.grade, grades.name, students.name FROM grades LEFT OUTER JOIN students ON grades.student_id = students.id ORDER BY grades.name, grades.grade;",
[(10, 'CSE231', None), (80, 'CSE450', 'Josh'),
(70, 'CSE480', None), (99, 'CSE480', 'Josh')])
conn_2.execute("COMMIT TRANSACTION;")
self.check(
conn_2,
"SELECT grades.grade, grades.name, students.name FROM grades LEFT OUTER JOIN students ON grades.student_id = students.id ORDER BY grades.name, grades.grade;",
[(80, 'CSE450', 'Josh'), (70, 'CSE480', None),
(99, 'CSE480', 'Josh')])
with self.assertRaises(Exception):
conn_3.execute("INSERT INTO students VALUES ('Zach', 732);")
conn_1.execute("ROLLBACK TRANSACTION;")
self.check(
conn_1,
"SELECT grades.grade, grades.name, students.name FROM grades LEFT OUTER JOIN students ON grades.student_id = students.id ORDER BY grades.name, grades.grade;",
[(80, 'CSE450', 'Josh'), (70, 'CSE480', None),
(99, 'CSE480', 'Josh')])
conn_1.execute("DROP TABLE IF EXISTS other;")
conn_3.execute("INSERT INTO students VALUES ('Zach', 732);")
self.check(
conn_4,
"SELECT name FROM students WHERE name > 'A' ORDER BY name;",
[('Cam', ), ('Josh', ), ('Zach', )])
def test_multiple_connections_later_changes(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT, grade REAL);")
conn_1.execute("INSERT INTO students VALUES ('Josh', 2.4);")
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2.execute(
"CREATE TABLE colors (r INTEGER, g INTEGER, b INTEGER);")
conn_2.execute("INSERT INTO colors VALUES (1, 2, 3);")
conn_2.execute("INSERT INTO colors VALUES (4, 5, 6);")
self.check(conn_2, "SELECT * FROM colors ORDER BY r;", [(1, 2, 3),
(4, 5, 6)])
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[('Josh', 2.4)])
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[('Josh', 2.4)])
conn_1.execute("INSERT INTO students VALUES ('Cam', 4.0);")
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
self.check(conn_3, "SELECT * FROM students ORDER BY name;",
[('Cam', 4.0), ('Josh', 2.4)])
def test_drop_table_if_exists(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("DROP TABLE students;")
conn_1.execute("DROP TABLE IF EXISTS students;")
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("DROP TABLE students;")
with self.assertRaises(Exception):
conn_2.execute("DROP TABLE students;")
def test_create_table_bare(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
with self.assertRaises(Exception):
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("CREATE TABLE other (name TEXT);")
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
with self.assertRaises(Exception):
conn_2.execute("CREATE TABLE other (name TEXT);")
conn_2.execute("CREATE TABLE more (name TEXT);")
def test_transaction_mode_exclusive(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
conn_4 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("BEGIN TRANSACTION;")
conn_2.execute("BEGIN EXCLUSIVE TRANSACTION;")
with self.assertRaises(Exception):
conn_3.execute("BEGIN EXCLUSIVE TRANSACTION;")
conn_3.execute("BEGIN TRANSACTION;")
conn_2.execute("COMMIT TRANSACTION;")
conn_4.execute("BEGIN EXCLUSIVE TRANSACTION;")
def join_with_null():
conn = project.connect("test19.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 students VALUES ('James', 96, 'CSE335'), ('Carol', 87, NULL), ('Jackie', 45, 'CSE323');"
)
conn.execute(
"INSERT INTO classes VALUES ('CSE480', 'Dr. Nahum'), ('CSE491', 'Dr. Josh'), ('CSE431', 'Dr. Ofria');"
)
conn.execute(
"INSERT INTO classes VALUES ('CSE331', 'Dr. Owens'), (NULL, 'Chair');")
check(
conn,
"SELECT students.name, students.grade, classes.course, classes.instructor FROM students LEFT OUTER JOIN classes ON students.class = classes.course ORDER BY students.name;",
[('Cam', 56, 'CSE480', 'Dr. Nahum'), ('Carol', 87, None, None),
('Dennis', 99, 'CSE480', 'Dr. Nahum'),
('Emily', 74, 'CSE431', 'Dr. Ofria'), ('Jackie', 45, None, None),
('James', 96, None, None), ('Jie', 52, 'CSE491', 'Dr. Josh'),
('Josh', 99, 'CSE480', 'Dr. Nahum'),
('Zizhen', 56, 'CSE491', 'Dr. Josh')])
def join():
conn = project.connect("test16.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(
conn,
"SELECT students.name, students.grade, classes.course, classes.instructor FROM students LEFT OUTER JOIN classes ON students.class = classes.course ORDER BY classes.instructor, students.name, students.grade;",
[('Jie', 52, 'CSE491', 'Dr. Josh'),
('Zizhen', 56, 'CSE491', 'Dr. Josh'),
('Cam', 56, 'CSE480', 'Dr. Nahum'),
('Dennis', 99, 'CSE480', 'Dr. Nahum'),
('Josh', 99, 'CSE480', 'Dr. Nahum'),
('Emily', 74, 'CSE431', 'Dr. Ofria')])
def update_where():
conn = project.connect("test14.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(conn, "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(conn, "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')])
conn.execute(
"UPDATE students SET notes = 'good student' WHERE name = 'Cam';")
check(conn, "SELECT * FROM students ORDER BY name;",
[('Cam', 524, 'good student'), ('Dennis', 45, 'Likes Networks'),
('Emily', 245, 'High Grade'), ('Jie', 455, 'High Grade'),
('Josh', 562, 'High Grade'), ('Zizhen', 4532, 'High Grade')])
def delete_where():
conn = project.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(conn, "SELECT * FROM table ORDER BY three;",
[(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 WHERE one IS NULL;")
check(conn, "SELECT * FROM table ORDER BY three;",
[(4.4, 235, 'Skyrim'), (3.4, 43, 'happiness'), (5.6, None, 'hunger'),
(43.24, 25, 'life'), (5345.6, 42, 'sadness'), (5.6, 42, 'thirst'),
(323.4, 433, 'warmth')])
conn.execute("DELETE FROM table WHERE two < 50;")
check(conn,
"SELECT * FROM table ORDER BY three;", [(4.4, 235, 'Skyrim'),
(5.6, None, 'hunger'),
(323.4, 433, 'warmth')])
def where_clause_qualified():
conn = project.connect("test10.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(conn, "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')])
check(conn, "SELECT * FROM table WHERE table.two > 50 ORDER BY three;",
[(4.4, 235, 'Skyrim'), (None, 235, 'want'), (323.4, 433, 'warmth')])
check(
conn,
"SELECT table.* FROM table WHERE two = 42 ORDER BY three, two, one;",
[(5345.6, 42, 'sadness'), (5.6, 42, 'thirst')])
check(
conn,
"SELECT * FROM table WHERE two IS NOT NULL ORDER BY table.three, two, one;",
[(4.4, 235, 'Skyrim'), (3.4, 43, 'happiness'), (43.24, 25, 'life'),
(5345.6, 42, 'sadness'), (5.6, 42, 'thirst'), (None, 235, 'want'),
(323.4, 433, 'warmth')])
def test_views(self):
conn = connect("test1.db")
conn.execute("CREATE TABLE students (name TEXT, grade REAL);")
conn.execute(
"CREATE VIEW stu_view AS SELECT * FROM students WHERE grade > 3.0 ORDER BY name;")
self.check(
conn,
"""SELECT name FROM stu_view ORDER BY grade;""",
[])
conn.execute("""INSERT INTO students VALUES
('Josh', 3.5),
('Dennis', 2.5),
('Cam', 1.5),
('Zizhen', 4.0)
;""")
self.check(
conn,
"""SELECT name FROM stu_view ORDER BY grade;""",
[('Josh',), ('Zizhen',)])
conn.execute("""INSERT INTO students VALUES
('Emily', 3.7),
('Alex', 2.5),
('Jake', 3.2)
;""")
self.check(
conn,
"""SELECT name FROM stu_view ORDER BY grade;""",
[('Jake',), ('Josh',), ('Emily',), ('Zizhen',)])
def test_regression(self):
conn = connect("test1.db")
conn.execute(
"CREATE TABLE students (name TEXT, grade REAL, course INTEGER);")
conn.execute("CREATE TABLE profs (name TEXT, course INTEGER);")
conn.execute("""INSERT INTO students VALUES ('Zizhen', 4.0, 450),
('Cam', 3.5, 480),
('Cam', 3.0, 450),
('Jie', 0.0, 231),
('Jie', 2.0, 331),
('Anne', 3.0, 231),
('Josh', 1.0, 231),
('Josh', 0.0, 480),
('Josh', 0.0, 331);""")
conn.execute("""INSERT INTO profs VALUES ('Josh', 480),
('Josh', 450),
('Rich', 231),
('Sebnem', 331);""")
self.check(
conn,
"""SELECT profs.name, students.grade, students.name
FROM students LEFT OUTER JOIN profs ON students.course = profs.course
WHERE students.grade > 0.0 ORDER BY students.name, profs.name, students.grade;""",
[('Rich', 3.0, 'Anne'),
('Josh', 3.0, 'Cam'),
('Josh', 3.5, 'Cam'),
('Sebnem', 2.0, 'Jie'),
('Rich', 1.0, 'Josh'),
('Josh', 4.0, 'Zizhen')])
def test_transaction_syntax(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2.execute("BEGIN TRANSACTION;")
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
with self.assertRaises(Exception):
conn_2.execute("BEGIN TRANSACTION;")
conn_2.execute("COMMIT TRANSACTION;")
with self.assertRaises(Exception):
conn_2.execute("COMMIT TRANSACTION;")
def insert_into_not_all_columns():
conn = project.connect("test5.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');")
check(conn, "SELECT * FROM table ORDER BY three, two, one;",
[(3.4, 43, 'happiness'), (11.4, None, 'sadness')])
def insert_into_different_order():
conn = project.connect("test4.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(conn, "SELECT * FROM table ORDER BY three, two, one;",
[(3.4, 43, 'happiness'), (11.4, 84, 'sadness')])
def escaping_strings():
conn = project.connect("test.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(conn, "SELECT * FROM table_1 ORDER BY _col2, col_1;",
[(36, "don't", 7), (33, 'hi', 4.5), (36, "hi 'josh'", 7)])
def test_transaction_mode_syntax(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
conn_1.execute("BEGIN DEFERRED TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
conn_1.execute("BEGIN IMMEDIATE TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
conn_1.execute("BEGIN EXCLUSIVE TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
def test_connection_interface(self):
conn = connect("test.db", timeout=0.1, isolation_level=None)
conn.execute(
"CREATE TABLE pets (name TEXT, species TEXT, age INTEGER);")
conn.execute(
"INSERT INTO pets VALUES ('RaceTrack', 'Ferret', 3), ('Ghost', 'Ferret', 2), ('Zoe', 'Dog', 7), ('Ebony', 'Dog', 17);"
)
self.check(conn, "SELECT * FROM pets ORDER BY pets.name;",
[('Ebony', 'Dog', 17), ('Ghost', 'Ferret', 2),
('RaceTrack', 'Ferret', 3), ('Zoe', 'Dog', 7)])
def test_rollback_syntax(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
conn_4 = connect("test.db", timeout=0.1, isolation_level=None)
conn_5 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("ROLLBACK TRANSACTION;")
with self.assertRaises(Exception):
conn_1.execute("COMMIT TRANSACTION;")
with self.assertRaises(Exception):
conn_1.execute("ROLLBACK TRANSACTION;")
conn_1.execute("BEGIN TRANSACTION;")
conn_1.execute("COMMIT TRANSACTION;")
with self.assertRaises(Exception):
conn_1.execute("ROLLBACK TRANSACTION;")
def qualified_names():
conn = project.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("INSERT INTO table_1 VALUES (36, 'don''t', 7);")
conn.execute("INSERT INTO table_1 VALUES (36, 'hi ''josh''', 7);")
check(
conn,
"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_transaction_modes_defferred_immediate(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_3 = connect("test.db", timeout=0.1, isolation_level=None)
conn_4 = connect("test.db", timeout=0.1, isolation_level=None)
conn_5 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("BEGIN TRANSACTION;")
conn_2.execute("BEGIN DEFERRED TRANSACTION;")
conn_3.execute("BEGIN IMMEDIATE TRANSACTION;")
with self.assertRaises(Exception):
conn_2.execute("UPDATE students SET name = 'Josh';")
conn_3.execute("UPDATE students SET name = 'Josh';")
with self.assertRaises(Exception):
conn_4.execute("BEGIN IMMEDIATE TRANSACTION;")
with self.assertRaises(Exception):
conn_5.execute("BEGIN EXCLUSIVE TRANSACTION;")
def select_star():
conn = project.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(
conn,
"SELECT col_1, *, col_3_, table_1._col2, * FROM table_1 ORDER BY table_1._col2, _col2, col_1;",
[(36, 36, "don't", 7, 7, "don't", 36, "don't", 7),
(33, 33, 'hi', 4.5, 4.5, 'hi', 33, 'hi', 4.5),
(36, 36, "hi 'josh'", 7, 7, "hi 'josh'", 36, "hi 'josh'", 7)])
def test_transaction_lock_interference_2(self):
conn_1 = connect("test.db", timeout=0.1, isolation_level=None)
conn_2 = connect("test.db", timeout=0.1, isolation_level=None)
conn_1.execute("CREATE TABLE students (name TEXT);")
conn_1.execute("INSERT INTO students VALUES ('Josh');")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("BEGIN TRANSACTION;")
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("INSERT INTO students VALUES ('Cam');")
self.check(conn_1, "SELECT * FROM students ORDER BY name;",
[("Cam", ), ("Josh", )])
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_2.execute("BEGIN TRANSACTION;")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
conn_1.execute("INSERT INTO students VALUES ('Zizhen');")
self.check(conn_2, "SELECT * FROM students ORDER BY name;",
[("Josh", )])
# Can't perform a write because conn_1 holds a reserved lock.
with self.assertRaises(Exception):
conn_2.execute("INSERT INTO students VALUES ('Emily');")
def where_clause_with_null_2():
conn = project.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 (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(conn,
"SELECT * FROM table WHERE one != 5.6 ORDER BY three, two, one;",
[(4.4, 235, 'Skyrim'), (3.4, 43, 'happiness'), (43.24, 25, 'life'),
(5345.6, 42, 'sadness')])
def insert_into_multiple_columns():
conn = project.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) VALUES (11.4, 'sadness'), (84.7, 'fear'), (94.7, 'weird');"
)
print("WEUFWOEIFHWOEIFNWOEIFNWOEFINWEOFIN")
conn.execute(
"INSERT INTO table (two, three) VALUES (13, 'warmth'), (34, 'coldness');"
)
check(conn, "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 integration_additional():
conn = project.connect("test22.db")
conn.execute(
"CREATE TABLE classes (department TEXT, class_num INTEGER, num_students INTEGER, offering TEXT);"
)
conn.execute("INSERT INTO classes VALUES ('CSE', 450, 300, 'Fall 2018');")
conn.execute("INSERT INTO classes VALUES ('CSE', 231, 700, 'Fall 2019');")
conn.execute(
"INSERT INTO classes VALUES ('MTH', 321, 201, 'Spring 2018');")
conn.execute("INSERT INTO classes VALUES ('CSE', 450, 220, 'Fall 2019');")
conn.execute("INSERT INTO classes VALUES ('ECE', 202, 200, 'Fall 2018');")
check(
conn,
"SELECT department, class_num, offering FROM classes WHERE num_students < 250 ORDER BY num_students;",
[('ECE', 202, 'Fall 2018'), ('MTH', 321, 'Spring 2018'),
('CSE', 450, 'Fall 2019')])
