357 lines
14 KiB
Python
357 lines
14 KiB
Python
import sqlite3
|
|
import config
|
|
from collections import OrderedDict
|
|
|
|
db_name = config.db_file
|
|
db_version = 1
|
|
ROLE_PLAYER = 10
|
|
ROLE_MASTER = 20
|
|
game_templates = OrderedDict([('fae', 'Fate Accelerated RPG'),
|
|
('fae-blank', 'Fate Accelerated RPG (empty character sheet)'),
|
|
('dnd', 'Dungeons & Dragons'),
|
|
('dnd-blank', 'Dungeons & Dragons (empty character sheet)')])
|
|
room_container = 'room'
|
|
rolls_container = 'rolls'
|
|
|
|
preferred_container_order = ['general', 'aspects', 'stunts', 'skills', 'spells', 'inventory', 'rolls']
|
|
preferred_key_order = {'general': ['description', 'fatepoints', 'stress2', 'stress4', 'stress6'], 'aspects': ['highconcept', 'trouble']}
|
|
|
|
|
|
def open_connection():
|
|
return sqlite3.connect(db_name)
|
|
def close_connection(db):
|
|
db.close()
|
|
|
|
def table_exists(db, table):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT count(*) FROM sqlite_master WHERE type='table' AND name=?''', (table,))
|
|
result = query.fetchone()
|
|
if result[0] == 0:
|
|
return False
|
|
else:
|
|
return True
|
|
|
|
class Group():
|
|
def __init__(self, gameid, groupid, groupname):
|
|
self.gameid = gameid
|
|
self.groupid = groupid
|
|
self.groupname = groupname
|
|
|
|
class Player():
|
|
def __init__(self, gameid, playerid, role, playername):
|
|
self.gameid = gameid
|
|
self.playerid = playerid
|
|
self.role = role
|
|
self.playername = playername
|
|
|
|
def init():
|
|
db = open_connection()
|
|
try:
|
|
print('Initializing database...')
|
|
c = db.cursor()
|
|
if not table_exists(db, 'Games'):
|
|
c.execute('''CREATE TABLE IF NOT EXISTS Games (gameid integer primary key autoincrement, version int, lastactivity datetime, gamename text, template text)''')
|
|
if not table_exists(db, 'Groups'):
|
|
c.execute('''CREATE TABLE IF NOT EXISTS Groups (gameid integer, groupid integer primary key, groupname text)''')
|
|
if not table_exists(db, 'Players'):
|
|
c.execute('''CREATE TABLE IF NOT EXISTS Players (gameid integer, playerid integer, role integer, playername text, PRIMARY KEY(gameid, playerid))''')
|
|
if not table_exists(db, 'Contents'):
|
|
c.execute('''CREATE TABLE IF NOT EXISTS Contents (gameid integer, playerid integer, container text, key text, value text, PRIMARY KEY(gameid, playerid, container, key))''')
|
|
except:
|
|
print('failed to initialize database')
|
|
raise
|
|
db.commit()
|
|
close_connection(db)
|
|
|
|
def convert_template(template):
|
|
"""
|
|
The blank versions of the character sheets use the same templates (same dices)
|
|
but treat treat the sheet creation differently (...by skipping it)
|
|
"""
|
|
if template == 'fae-blank':
|
|
return 'fae', True
|
|
if template == 'dnd-blank':
|
|
return 'dnd', True
|
|
return template, False
|
|
|
|
def new_game(db, admin, playername, gamename, groupid, groupname, template):
|
|
"""
|
|
Creates a new game.
|
|
DOES NOT CHECK that the group is not already in a game.
|
|
Returns:
|
|
None - the group is already in a game.
|
|
the gameid otherwise.
|
|
"""
|
|
if template not in game_templates:
|
|
raise
|
|
|
|
c = db.cursor()
|
|
try:
|
|
query = c.execute('''INSERT INTO Games(version, lastactivity, gamename, template) VALUES (?, datetime('now'), ?, ?)''', (db_version, gamename, template))
|
|
except sqlite3.IntegrityError:
|
|
# probably group is already in game
|
|
return None
|
|
gameid = c.lastrowid
|
|
try:
|
|
query = c.execute('''INSERT INTO Groups(gameid, groupid, groupname) VALUES (?, ?, ?)''', (gameid, groupid, groupname,))
|
|
except sqlite3.IntegrityError:
|
|
return None
|
|
add_player(db, admin, playername, gameid, ROLE_MASTER)
|
|
db.commit()
|
|
return gameid
|
|
|
|
def del_game(db, gameid):
|
|
c = db.cursor()
|
|
query = c.execute('''DELETE FROM Games WHERE gameid=?''', (gameid,))
|
|
query = c.execute('''DELETE FROM Groups WHERE gameid=?''', (gameid,))
|
|
query = c.execute('''DELETE FROM Players WHERE gameid=?''', (gameid,))
|
|
query = c.execute('''DELETE FROM Contents WHERE gameid=?''', (gameid,))
|
|
db.commit()
|
|
|
|
def get_template_from_gameid(db, gameid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT template FROM Games WHERE gameid=?''', (gameid,))
|
|
result = query.fetchone()
|
|
if result == None:
|
|
# invalid gameid or database not updated
|
|
raise
|
|
template = result[0]
|
|
return template
|
|
|
|
def get_template_from_groupid(db, groupid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT template FROM Games LEFT JOIN Groups ON Games.gameid = Groups.gameid WHERE groupid=?''', (groupid,))
|
|
result = query.fetchone()
|
|
if result == None:
|
|
# group not in a game
|
|
return None
|
|
template = result[0]
|
|
return template
|
|
|
|
def get_group_from_playerid(db, playerid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT Groups.gameid, groupid, groupname, playerid, role, playername FROM Groups LEFT JOIN Players ON Groups.gameid = Players.gameid WHERE playerid=?''', (playerid,))
|
|
result = query.fetchone()
|
|
if result is None:
|
|
return None, None
|
|
group = Group(result[0], result[1], result[2])
|
|
player = Player(result[0], result[3], result[4], result[5])
|
|
return group, player
|
|
|
|
def get_group_from_groupid(db, groupid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT gameid, groupid, groupname FROM Groups WHERE groupid=?''', (groupid,))
|
|
result = query.fetchone()
|
|
if result is None:
|
|
return None
|
|
group = Group(result[0], result[1], result[2])
|
|
return group
|
|
|
|
def get_player_role(db, userid, gameid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT role FROM Players WHERE playerid=? AND gameid=?''', (userid, gameid,))
|
|
result = query.fetchone()
|
|
if result is None:
|
|
return None
|
|
role = result[0]
|
|
return role
|
|
|
|
def get_player_name(db, gameid, userid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT playername FROM Players WHERE playerid=? AND gameid=?''', (userid, gameid,))
|
|
result = query.fetchone()
|
|
if result is None:
|
|
return None
|
|
name = result[0]
|
|
return name
|
|
|
|
def add_player(db, userid, username, gameid, role):
|
|
old_role = get_player_role(db, userid, gameid)
|
|
|
|
new_player_added = False
|
|
if old_role is None:
|
|
new_player_added = True
|
|
else:
|
|
if old_role >= role:
|
|
role = old_role
|
|
|
|
c = db.cursor()
|
|
query = c.execute('''INSERT OR REPLACE INTO Players(gameid, playerid, role, playername) VALUES (?, ?, ?, ?)''', (gameid, userid, role, username,))
|
|
db.commit()
|
|
return new_player_added
|
|
|
|
def get_masters_for_game(db, gameid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT playerid FROM Players WHERE role=? AND gameid=?''', (ROLE_MASTER, gameid, ))
|
|
ret = []
|
|
for player in query:
|
|
ret.append(player[0])
|
|
return ret
|
|
|
|
def get_all_players_from_game(db, gameid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT gameid, playerid, role, playername FROM Players WHERE gameid=?''', (gameid, ))
|
|
ret = []
|
|
for player in query:
|
|
p = Player(player[0], player[1], player[2], player[3])
|
|
ret.append(p)
|
|
return ret
|
|
|
|
def add_default_items(db, userid, gameid, template):
|
|
if template == 'fae':
|
|
update_item(db, gameid, userid, 'general', 'description', 'Describe your character in a few words.', False)
|
|
update_item(db, gameid, userid, 'general', 'fatepoints', '3', False)
|
|
update_item(db, gameid, userid, 'general', 'refresh', '3', False)
|
|
update_item(db, gameid, userid, 'general', 'stress2', 'Inactive', False)
|
|
update_item(db, gameid, userid, 'general', 'stress4', 'Inactive', False)
|
|
update_item(db, gameid, userid, 'general', 'stress6', 'Inactive', False)
|
|
update_item(db, gameid, userid, 'stunts', '1', 'Set this to your first stunt.', False)
|
|
update_item(db, gameid, userid, 'aspects', 'highconcept', 'Set this to your high concept.', False)
|
|
update_item(db, gameid, userid, 'aspects', 'trouble', 'Your character\'s trouble.', False)
|
|
update_item(db, gameid, userid, 'aspects', '1', 'Set this to your first aspect.', False)
|
|
update_item(db, gameid, userid, 'approaches', 'careful', '0', False)
|
|
update_item(db, gameid, userid, 'approaches', 'clever', '0', False)
|
|
update_item(db, gameid, userid, 'approaches', 'flashy', '0', False)
|
|
update_item(db, gameid, userid, 'approaches', 'forceful', '0', False)
|
|
update_item(db, gameid, userid, 'approaches', 'quick', '0', False)
|
|
update_item(db, gameid, userid, 'approaches', 'sneaky', '0', False)
|
|
elif template == 'dnd':
|
|
update_item(db, gameid, userid, 'general', 'class', 'Your class.', False)
|
|
update_item(db, gameid, userid, 'general', 'race', 'Your race.', False)
|
|
update_item(db, gameid, userid, 'general', 'alignment', 'Your alignment.', False)
|
|
update_item(db, gameid, userid, 'general', 'level', '1', False)
|
|
update_item(db, gameid, userid, 'attributes', 'strength', '14', False)
|
|
update_item(db, gameid, userid, 'attributes', 'dexterity', '14', False)
|
|
update_item(db, gameid, userid, 'attributes', 'constitution', '14', False)
|
|
update_item(db, gameid, userid, 'attributes', 'intelligence', '14', False)
|
|
update_item(db, gameid, userid, 'attributes', 'wisdom', '14', False)
|
|
update_item(db, gameid, userid, 'attributes', 'charisma', '14', False)
|
|
|
|
|
|
def number_of_games(db, user):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT count(*) FROM Players WHERE playerid=?''', (user,))
|
|
result = query.fetchone()
|
|
return result[0]
|
|
|
|
def get_game_from_group(db, groupid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT gameid FROM Groups WHERE groupid=?''', (groupid,))
|
|
result = query.fetchone()
|
|
if result is None:
|
|
return None
|
|
return result[0]
|
|
|
|
def get_game_info(db, gameid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT gamename, template FROM Games WHERE gameid=?''', (gameid,))
|
|
result = query.fetchone()
|
|
gamename = result[0]
|
|
template = result[1]
|
|
query = c.execute('''SELECT groupname FROM Groups WHERE gameid=?''', (gameid,))
|
|
groups = []
|
|
for group in query:
|
|
groups.append(group[0])
|
|
query = c.execute('''SELECT playername, role FROM Players WHERE gameid=?''', (gameid,))
|
|
players = {}
|
|
for player in query:
|
|
players[player[0]] = player[1]
|
|
return gamename, template, groups, players
|
|
|
|
def number_of_items(db, gameid, playerid):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT count(*) FROM Contents WHERE gameid=? AND playerid=?''', (gameid, playerid,))
|
|
result = query.fetchone()
|
|
return result[0]
|
|
|
|
def update_item(db, gameid, playerid, container, key, change, replace_only):
|
|
"""
|
|
change: can be a string, a number, or a relative change (e.g. '+1', '-1')
|
|
replace_only: will avoid adding a new key
|
|
|
|
Returns:
|
|
oldvalue: None if didn't exist, int if it was digits, or a string
|
|
newvalue: int if oldvalue was digits and change is a relative change, text otherwise
|
|
"""
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT value FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=?''', (gameid, playerid, container, key,))
|
|
result = query.fetchone()
|
|
if result is None:
|
|
oldvalue = None
|
|
if replace_only:
|
|
return oldvalue, None
|
|
else:
|
|
oldvalue = result[0]
|
|
|
|
if (oldvalue is None or oldvalue.isdigit()) and (change.isdigit() or (change[0] in ['+', '-'] and change[1:].isdigit())):
|
|
if oldvalue is None:
|
|
oldvalue = 0
|
|
else:
|
|
oldvalue = int(oldvalue)
|
|
if change[0] == '+':
|
|
newvalue = oldvalue + int(change[1:])
|
|
elif change[0] == '-':
|
|
newvalue = oldvalue - int(change[1:])
|
|
else:
|
|
newvalue = int(change)
|
|
else:
|
|
newvalue = change
|
|
|
|
query = c.execute('''INSERT OR REPLACE INTO Contents(gameid, playerid, container, key, value) VALUES (?, ?, ?, ?, ?)''', (gameid, playerid, container, key, newvalue,))
|
|
db.commit()
|
|
return oldvalue, newvalue
|
|
|
|
def to_number(thing):
|
|
"""
|
|
Converts something to an integer.
|
|
Returns the integer upon success, or 0 upon failure.
|
|
"""
|
|
try:
|
|
test = int(thing)
|
|
return test
|
|
except:
|
|
return 0
|
|
|
|
def add_to_list(db, gameid, playerid, container, description):
|
|
"""
|
|
description: the new item description
|
|
"""
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT key FROM Contents WHERE gameid=? AND playerid=? AND container=?''', (gameid, playerid, container,))
|
|
maximum = 0
|
|
for keys in query:
|
|
maximum = max(to_number(keys[0]), maximum)
|
|
new = maximum + 1
|
|
query = c.execute('''INSERT OR REPLACE INTO Contents(gameid, playerid, container, key, value) VALUES (?, ?, ?, ?, ?)''', (gameid, playerid, container, new, description,))
|
|
db.commit()
|
|
|
|
def get_item_value(db, gameid, playerid, container, key):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT value FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=?''', (gameid, playerid, container, key,))
|
|
result = query.fetchone()
|
|
if result == None:
|
|
return None
|
|
return result[0]
|
|
|
|
def delete_item(db, gameid, playerid, container, key):
|
|
c = db.cursor()
|
|
query = c.execute('''SELECT value FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=?''', (gameid, playerid, container, key,))
|
|
result = query.fetchone()
|
|
if result == None:
|
|
return None
|
|
oldvalue = result[0]
|
|
query = c.execute('''DELETE FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=?''', (gameid, playerid, container, key,))
|
|
db.commit()
|
|
return oldvalue
|
|
|
|
def get_items(db, gameid, playerid):
|
|
ret = {}
|
|
c = db.cursor()
|
|
contents = {}
|
|
query = c.execute('''SELECT container, key, value FROM Contents WHERE gameid=? AND playerid=?''', (gameid, playerid,))
|
|
for row in query.fetchall():
|
|
if row[0] not in contents:
|
|
contents[row[0]] = {}
|
|
contents[row[0]][row[1]] = row[2]
|
|
return contents
|