def test_sqlite_simple_ddl(self): self.delete_db_file() db = DB() db.connect_sqlite(self.DB_FILE) db.exec("CREATE TABLE test1 (name VARCHAR, value VARCHAR)") db.exec("INSERT INTO test1 (name, value) VALUES (?, ?)", ["tyrbot", "1"]) db.get_connection().close() db.connect_sqlite(self.DB_FILE) self.assertEqual([{ 'name': 'tyrbot', 'value': '1' }], db.query("SELECT * FROM test1")) db.get_connection().close() self.delete_db_file()
def test_sqlite_transaction_rollback(self): self.delete_db_file() db = DB() db.connect_sqlite(self.DB_FILE) db.exec("CREATE TABLE test1 (name VARCHAR, value VARCHAR)") db.begin_transaction() db.exec("INSERT INTO test1 (name, value) VALUES (?, ?)", ["tyrbot", "1"]) db.rollback_transaction() db.get_connection().close() db.connect_sqlite(self.DB_FILE) self.assertEqual([], db.query("SELECT * FROM test1")) db.get_connection().close() self.delete_db_file()
def test_sqlite_transaction_rollback_using_with(self): self.delete_db_file() db = DB() db.connect_sqlite(self.DB_FILE) db.exec("CREATE TABLE test1 (name VARCHAR, value VARCHAR)") try: with db.transaction(): db.exec("INSERT INTO test1 (name, value) VALUES (?, ?)", ["tyrbot", "1"]) raise Exception("Testing") except Exception: pass db.get_connection().close() db.connect_sqlite(self.DB_FILE) self.assertEqual([], db.query("SELECT * FROM test1")) db.get_connection().close() self.delete_db_file()
exit(1) if not new_db.get_type(): print("Error! Specify connection method for new_db") exit(1) # TODO check python version # admin print("migrating data to admin table") data = old_db.query( "SELECT p.charid AS char_id, CASE WHEN adminlevel = 4 THEN 'admin' WHEN adminlevel = 3 THEN 'moderator' END AS access_level FROM admin_<myname> a JOIN players p ON a.name = p.name WHERE p.charid > 0" ) with new_db.transaction(): for row in data: new_db.exec("DELETE FROM admin WHERE char_id = ?", [row.char_id]) new_db.exec("INSERT INTO admin (char_id, access_level) VALUES (?, ?)", [row.char_id, row.access_level]) print("migrated %d records" % len(data)) # banlist_<myname> print("migrating data to ban_list table") data = old_db.query( "SELECT b.charid AS char_id, p.charid AS sender_char_id, time AS created_at, banend AS finished_at, reason FROM banlist_<myname> b JOIN players p ON b.admin = p.name WHERE p.charid > 0" ) with new_db.transaction(): for row in data: new_db.exec("DELETE FROM ban_list WHERE char_id = ?", [row.char_id]) new_db.exec( "INSERT INTO ban_list (char_id, sender_char_id, created_at, finished_at, reason, ended_early) VALUES (?, ?, ?, ?, ?, ?)", [