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 (?, ?, ?, ?, ?, ?)",
[
