def test_db():
db.connect()
tables = [
db_models.User, db_models.Message, db_models.Photo, db_models.Cost,
db_models.Action
]
db.drop_tables(tables)
db.create_tables(tables)
yield db
db.drop_tables(tables)
db.close()
def generate() -> None:
"""
Generates some initial records in database
"""
from tests.factories import UserFactory, ActionFactory, MessageFactory, CostFactory
db.connect()
db.create_tables([db_models.User, db_models.Cost, db_models.Action, db_models.Photo, db_models.Message])
# will generate the whole database by chain of subfactories
UserFactory.create_batch(size=5)
ActionFactory.create_batch(size=5)
MessageFactory.create_batch(size=5)
CostFactory.create_batch(size=5)
click.secho("\nGeneration done!", fg='green')
db.close()
def change_password(self, new_password: str) -> str:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
UPDATE users SET password = MD5(%s) WHERE id = {self.id} LIMIT 1;
""", new_password)
conn.commit()
return self.token
def get_by_id(game_id: int) -> GameModel or None:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT * FROM games WHERE id = {game_id} LIMIT 1;
""")
result = cursor.fetchone()
return None if result is None else GameModel(result)
def get_by_id(user_id: int) -> User or None:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT * FROM users WHERE id = {user_id} LIMIT 1;
""")
data = cursor.fetchone()
return None if data is None else User(data)
def get_by_login(login: str) -> User or None:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute("""
SELECT * FROM users WHERE login = %s LIMIT 1;
""", [login])
data = cursor.fetchone()
return None if data is None else User(data)
def get_by_token(token: str) -> User or None:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute("""
SELECT * FROM users WHERE MD5(CONCAT(LOWER(login), password)) = %s LIMIT 1;
""", [token])
data = cursor.fetchone()
return None if data is None else User(data)
def get_user_games(user: u.User, count: int = 7) -> list:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT games.* FROM games JOIN players
WHERE players.game = games.id AND players.user = {user.id}
ORDER BY games.id DESC LIMIT {count};
""")
return [GameModel(data) for data in cursor.fetchall()]
def players(self) -> list:
if self.__cached_players is None:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT game, user FROM players WHERE game = {self.id};
""")
data = cursor.fetchall()
self.__cached_players = [u.get_by_id(obj[1]) for obj in data]
return self.__cached_players
def winner(self) -> u.User or None:
if self.__cached_winner is None:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT game, user FROM winners WHERE game = {self.id} LIMIT 1;
""")
result = cursor.fetchone()
self.__cached_winner = None if result is None else u.get_by_id(
result[1])
return self.__cached_winner
def register(login: str, password: str) -> User:
try:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute("""
INSERT INTO users (login, password) VALUES (%s, MD5(%s));
""", (login, password))
conn.commit()
return get_by_login(login)
except pymysql.IntegrityError:
raise UserError("Пользователь с данным логином уже существует")
def edit_settings(self,
about: str or None = None
):
self.about = self.about if about is None else about.strip()
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
UPDATE users SET
about = %s
WHERE id = {self.id} LIMIT 1;
""", [self.about])
conn.commit()
def add_player(self, user: u.User):
if get_active_game(user) is not None:
raise GameError('Вы уже участвуете в другой игре')
if self.__status != g.NOT_STARTED:
raise GameError('Игра уже окончена')
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
INSERT INTO players (game, user) VALUES ({self.id}, {user.id});
""")
conn.commit()
def get_active_game(user: u.User) -> GameModel or None:
if user.cached_game is not None:
return user.cached_game
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT game FROM players WHERE user = {user.id} ORDER BY id DESC LIMIT 1;
""")
result = cursor.fetchone()
if result is None:
return None
game = get_by_id(result[0])
if game is None:
return None
user.cached_game = game if game.can_play else None
return user.cached_game
def get_ended_games(time_now: int, mark_as_handling: bool = True) -> list:
with db.connect() as conn:
with conn.cursor() as cursor:
cursor.execute(f"""
SELECT * FROM games
WHERE status = {g.NOT_STARTED} AND end_time <= {time_now};
""")
result = [GameModel(data) for data in cursor.fetchall()]
if mark_as_handling:
with conn.cursor() as cursor:
cursor.execute(f"""
UPDATE games SET status = {g.HANDLING}
WHERE status = {g.NOT_STARTED} AND end_time <= {time_now};
""")
conn.commit()
return result
def end(self, best_player: u.User):
with db.connect() as conn:
with conn.cursor() as cursor:
cursor.execute(f"""
INSERT INTO winners (game, user) VALUES ({self.id}, {best_player.id});
""")
conn.commit()
players = self.players
for player in players:
player.end_game(best_player.id == player.id)
with conn.cursor() as cursor:
cursor.execute(f"""
UPDATE games SET status = {g.HANDLED}
WHERE id = {self.id} LIMIT 1;
""")
conn.commit()
def create(owner: u.User, add_self: bool, name: str, period: int,
start_time: int, private: bool, mode: int, view_distance: int,
**kwargs) -> GameModel:
end_time = start_time + period * 60
settings = kwargs
settings['view_distance'] = view_distance
serialized_settings = json.JSONEncoder().encode(settings)
with db.connect() as conn:
with conn.cursor() as cursor:
cursor.execute(
f"""
INSERT INTO games (name, start_time, end_time, period, owner, private, mode, settings)
VALUES (%s, {start_time}, {end_time}, {period}, {owner.id}, {private}, {mode}, %s)
""", [name, serialized_settings])
conn.commit()
with conn.cursor() as cursor:
cursor.execute(f"""
SELECT * FROM games WHERE
name = %s AND
start_time = {start_time} AND
owner = {owner.id}
ORDER BY id DESC LIMIT 1;
""")
game_data = cursor.fetchone()
if game_data is None:
raise GameError(
'Произошла неизвестная ошибка при создании и игры')
game = GameModel(game_data)
if add_self:
game.add_player(owner)
return game
def create_db():
db.connect()
db.create_tables([db_models.User, db_models.Cost, db_models.Action, db_models.Photo, db_models.Message])
db.close()
click.secho("\nCreate DB tables!", fg='green')
def close(self):
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
UPDATE games SET status = {g.CANCELLED_BY_OWNER} WHERE id = {self.id} LIMIT 1;
""")
conn.commit()
def remove(self):
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
DELETE FROM games WHERE id = {self.id} LIMIT 1;
""")
conn.commit()
def handled_with_errors(self):
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
UPDATE games SET status = {g.ENDED_WITH_ERRORS} WHERE id = {self.id} LIMIT 1;
""")
conn.commit()
def end_game(self, winner: bool):
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
UPDATE users SET played = played + 1, wins = wins + {int(winner)} WHERE id = {self.id} LIMIT 1;
""")
conn.commit()
def get_games(count: int = 7, status: int = g.NOT_STARTED) -> list:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute(f"""
SELECT * FROM games WHERE (private = false) AND (status = {status}) ORDER BY id DESC
""" + (f"LIMIT {count};" if count > 0 else ";"))
return [GameModel(data) for data in cursor.fetchall()]
def get_top10() -> list:
with db.connect() as conn, conn.cursor() as cursor:
cursor.execute("""
SELECT * FROM users ORDER BY wins DESC LIMIT 10;
""")
return [User(data) for data in cursor.fetchall()]
import asyncio
from app.database import db
if __name__ == "__main__":
loop = asyncio.get_event_loop()
try:
loop.run_until_complete(db.connect())
except Exception:
exit(1)
loop.run_until_complete(db.disconnect())
exit(0)
