ultimate_tictactoe_server/create_database.py

36 lines
1.7 KiB
Python
Executable File

#!/usr/bin/env python3
# this script creates the necessary tables in the databse
import settings
from database_connection import DatabaseConnection
def create_tables():
DatabaseConnection(settings.db_host,
settings.db_port,
settings.db_user,
settings.db_pw,
settings.db_db,
settings.db_charset)
queries = [
"DROP TABLE IF EXISTS matches",
"DROP TABLE IF EXISTS sessions",
"DROP TABLE IF EXISTS friends",
"DROP TABLE IF EXISTS users",
"CREATE TABLE users (name varchar(16) NOT NULL, pw_hash varchar(128) NOT NULL, pw_salt varchar(32) NOT NULL, last_seen datetime NOT NULL, elo int NOT NULL, PRIMARY KEY (name)) CHARACTER SET " + settings.db_charset,
"CREATE TABLE matches (id varchar(32) NOT NULL, user_a varchar(16) NOT NULL, user_b varchar(16) NOT NULL, match_state varchar(4096) NOT NULL, active_user varchar(16), last_active datetime NOT NULL, ranked BOOLEAN NOT NULL) CHARACTER SET " + settings.db_charset,
"CREATE TABLE sessions (id varchar(32) NOT NULL, registered_user varchar(16), temp_user varchar(16), last_seen datetime NOT NULL, PRIMARY KEY (id), FOREIGN KEY(registered_user) REFERENCES users(name)) CHARACTER SET " + settings.db_charset,
"CREATE TABLE friends (user varchar(16) NOT NULL, friend varchar(16) NOT NULL, FOREIGN KEY(user) REFERENCES users(name), FOREIGN KEY(friend) REFERENCES users(name)) CHARACTER SET " + settings.db_charset
]
for query in queries:
DatabaseConnection.global_single_execution(query)
DatabaseConnection.global_close()
if __name__ == "__main__":
create_tables()